View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000081Pgpool-IIBugpublic2013-11-06 23:192013-11-11 19:57
Reporterbrailateo Assigned Tot-ishii  
PrioritynormalSeveritymajorReproducibilityrandom
Status resolvedResolutionopen 
PlatformLinuxOSUbuntu LinuxOS Version13.10 x86-64
Summary0000081: Random failure on first INSERT after a BEGIN TRANSACTION
DescriptionPgPool2 version 3.2.4 amd64 version on a Ubuntu Linux Server 13.10 x86-64
PgPool2 configured with a single server, for connection pooling, on port 5433
Trying to make 3 o4 4 inserts into a transaction, 60% of the time, the first INSERT INTO fails:
log says:
pid 7859: pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 7968 statement: insert into ceva values('patru28'); message: current transaction is aborted, commands ignored until end of transaction block
Steps To Reproduceteo@utid:~$ psql -p 5433 -h 172.16.0.17 cs_utid_1
cs_utid_1=# begin;
BEGIN
cs_utid_1=# insert into ceva values('patru28');
ERROR: current transaction is aborted, commands ignored until end of transaction block
cs_utid_1=# rollback;
ROLLBACK

teo@utid:~$ psql -p 5433 -h 172.16.0.17 cs_utid_1
cs_utid_1=# begin;
BEGIN
cs_utid_1=# insert into ceva values('patru28');
INSERT 0 1
cs_utid_1=# commit;
COMMIT
TagsNo tags attached.

Activities

brailateo

2013-11-06 23:19

reporter  

pgpool.conf (26,996 bytes)

t-ishii

2013-11-07 13:06

developer   ~0000360

Can you show me PostgreSQL around BEGIN...INSERT? I expect PostgreSQL reports some errors.

brailateo

2013-11-07 16:11

reporter   ~0000361

I discovered that when using this kind of transactions, from a PHP program:

BEGIN TRANSACTION
INSERT INTO f3_master(csv_fk,serie,ip_addr,cod_exploatatie,data_notificare,declarant,calitate) VALUES (31947,'U3331947','172.16.1.8','RO1627640070','2013-10-22','Moraru VASILE-LICA','A') RETURNING id AS id_master;
INSERT INTO f3_detail(id_master,data,cod_crotalie,tip_eveniment_fk) VALUES (27285,'2013-10-22','RO391000009356',53);
INSERT INTO f3_detail(id_master,data,cod_crotalie,tip_eveniment_fk) VALUES (27285,'2013-10-22','RO396000009319',53);
INSERT INTO f3_detail(id_master,data,cod_crotalie,tip_eveniment_fk) VALUES (27285,'2013-10-22','RO397000009922',53);
COMMIT

But I tried then to replicate the behaviour with more simple tables and using just psql -p 5433, so I created a simple table with one varchar column, no index at all and the behaviour was just the same.
It seems that only the first attempt, after database connection would give repeatedly that error, see below.

teo@utid:~$ psql -h 172.16.0.17 -p 5433 cs_utid_1
psql (9.1.10)
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
INSERT 0 1
COMMIT
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
INSERT 0 1
COMMIT
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
INSERT 0 1
COMMIT
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
INSERT 0 1
COMMIT
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
INSERT 0 1
COMMIT
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
INSERT 0 1
COMMIT
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
INSERT 0 1
COMMIT
cs_utid_1=# \q

teo@utid:~$ psql -h 172.16.0.17 -p 5433 cs_utid_1
psql (9.1.10)
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
INSERT 0 1
COMMIT
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
INSERT 0 1
COMMIT
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
INSERT 0 1
COMMIT
cs_utid_1=# begin; insert into ceva values('patru181');commit;
BEGIN
INSERT 0 1
COMMIT

brailateo

2013-11-07 16:15

reporter   ~0000362

Strange thing, if I am sending the transaction in the command line of psql, with -c , it works all the time:

teo@utid:~$ psql -h 172.16.0.17 -p 5433 cs_utid_1 -c "begin; insert into ceva values('patru181');commit"
COMMIT
teo@utid:~$
teo@utid:~$ psql -h 172.16.0.17 -p 5433 cs_utid_1 -c "begin; insert into ceva values('patru181');commit"
COMMIT
teo@utid:~$
teo@utid:~$ psql -h 172.16.0.17 -p 5433 cs_utid_1 -c "begin; insert into ceva values('patru181');commit"
COMMIT
teo@utid:~$
teo@utid:~$ psql -h 172.16.0.17 -p 5433 cs_utid_1 -c "begin; insert into ceva values('patru181');commit"
COMMIT
teo@utid:~$
teo@utid:~$ psql -h 172.16.0.17 -p 5433 cs_utid_1 -c "begin; insert into ceva values('patru181');commit"
COMMIT
teo@utid:~$
teo@utid:~$ psql -h 172.16.0.17 -p 5433 cs_utid_1 -c "begin; insert into ceva values('patru181');commit"
COMMIT
teo@utid:~$
teo@utid:~$ psql -h 172.16.0.17 -p 5433 cs_utid_1 -c "begin; insert into ceva values('patru181');commit"
COMMIT
teo@utid:~$
teo@utid:~$ psql -h 172.16.0.17 -p 5433 cs_utid_1 -c "begin; insert into ceva values('patru181');commit"
COMMIT

t-ishii

2013-11-08 08:39

developer   ~0000363

Show me the PostgreSQL log please.

brailateo

2013-11-08 16:44

reporter   ~0000364

When:
teo@utid:~$ psql -p 5433 cs_utid_1
psql (9.1.10)
Type "help" for help.

cs_utid_1=# begin; insert into ceva values('log_pg1');commit;
BEGIN
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
cs_utid_1=# begin; insert into ceva values('log_pg2');commit;
BEGIN
INSERT 0 1
COMMIT

PostgreSQL LOG says

2013-11-08 09:41:23 EET LOG: connection received: host=172.16.0.17 port=43798
2013-11-08 09:41:23 EET LOG: connection authorized: user=teo database=cs_utid_1
2013-11-08 09:41:28 EET LOG: checkpoint starting: time
2013-11-08 09:41:33 EET ERROR: function "pgpool_regclass(cstring)" does not exist
2013-11-08 09:41:33 EET STATEMENT: SELECT count(*) FROM pg_catalog.pg_proc AS p WHERE p.proname = 'pgpool_regclass' AND has_function_privilege('teo', 'pgpool_regclass(cstring)', 'execute')
2013-11-08 09:41:33 EET ERROR: current transaction is aborted, commands ignored until end of transaction block
2013-11-08 09:41:33 EET STATEMENT: SELECT oid FROM pg_class WHERE relname = 'ceva'
2013-11-08 09:41:33 EET ERROR: current transaction is aborted, commands ignored until end of transaction block
2013-11-08 09:41:33 EET STATEMENT: insert into ceva values('log_pg1');

t-ishii

2013-11-09 08:10

developer   ~0000366

2013-11-08 09:41:33 EET ERROR: function "pgpool_regclass(cstring)" does not exist

This is the source of the problem. You need to install pgpool-regclass C function which is part of pgpool-II. You can blame Ubuntu for it (surely packaging problem).

Anyway, you can install it from pgpool-II source code. See bug # 80 to know how to install pgpool-regclass from pgpool-II source code.

brailateo

2013-11-11 19:33

reporter   ~0000369

Thanks t-ishii, now it works! Usually I compile and install by myself the last version of the software that I use but now I just was in a hurry to test something and tried the default package from Ubuntu. I will inform them also, in order to deliver a full functional package of pgpool2 into 14.04 release, that will be a Long Term Support.
Thanks again for your support and for you very useful package, pgpool2,
Constantin Teodorescu

t-ishii

2013-11-11 19:57

developer   ~0000370

Glad to hear that!

Issue History

Date Modified Username Field Change
2013-11-06 23:19 brailateo New Issue
2013-11-06 23:19 brailateo File Added: pgpool.conf
2013-11-07 13:04 t-ishii Assigned To => t-ishii
2013-11-07 13:04 t-ishii Status new => feedback
2013-11-07 13:06 t-ishii Note Added: 0000360
2013-11-07 16:11 brailateo Note Added: 0000361
2013-11-07 16:11 brailateo Status feedback => assigned
2013-11-07 16:15 brailateo Note Added: 0000362
2013-11-08 08:39 t-ishii Note Added: 0000363
2013-11-08 16:44 brailateo Note Added: 0000364
2013-11-09 08:10 t-ishii Note Added: 0000366
2013-11-09 08:11 t-ishii Status assigned => feedback
2013-11-11 19:33 brailateo Note Added: 0000369
2013-11-11 19:33 brailateo Status feedback => assigned
2013-11-11 19:57 t-ishii Note Added: 0000370
2013-11-11 19:57 t-ishii Status assigned => resolved