[Pgpool-general] Prepared statement does not exist

Discussion:

Nimesh Satam

2009-03-17 07:46:04 UTC

Hi,

We are using pgpool-II version 2.2 and postgres 8.3.3.

Can any body let us know why we get the following notes in the
database logs when we use pgpool.

u=postgres h=xx01.xxxx.xxxx.com ERROR: prepared statement "S_2" already exists
u=postgres h=xx01.xxxx.xxxx.com ERROR: prepared statement "S_3" does not exist
u=postgres h=xx01.xxxx.xxxx.com ERROR: prepared statement "S_1" does not exist
u=postgres h=xx01.xxxx.xxxx.com ERROR: prepared statement "S_4" does not exist

We dont see the below errors if we directly connect to the database.
Pgpool is used for load-balancing whiel slony is used for replication.
We see many such logs coming continuously. Can any body tell what can
be done to avoid them and what its impact is?

Regards,
Nimesh.

Tatsuo Ishii

2009-03-22 04:07:17 UTC

Permalink

Post by Nimesh Satam
Hi,
We are using pgpool-II version 2.2 and postgres 8.3.3.
Can any body let us know why we get the following notes in the
database logs when we use pgpool.
u=postgres h=xx01.xxxx.xxxx.com ERROR: prepared statement "S_2" already exists

I'm not sure for this part, but for followings I guess it happens
because you have something like:

reset_query_list = 'ABORT;DISCARD ALL'

Post by Nimesh Satam
u=postgres h=xx01.xxxx.xxxx.com ERROR: prepared statement "S_3" does not exist
u=postgres h=xx01.xxxx.xxxx.com ERROR: prepared statement "S_1" does not exist
u=postgres h=xx01.xxxx.xxxx.com ERROR: prepared statement "S_4" does not exist

pgpool tries to drop prepared statements when client disconnect to
pgpool *after* executing quries specified in reset_query_list which
have removed all prepared statements.

To solve the problem, replace "DISCARD ALL" with "DISCARD TEMPORARY"
or whatever you want to use. See DISCARD man pages for details.

Note that "DISCARD ALL" might cause performance degradation because it
helds exclusive lock on a system catalog.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

Post by Nimesh Satam
We dont see the below errors if we directly connect to the database.
Pgpool is used for load-balancing whiel slony is used for replication.
We see many such logs coming continuously. Can any body tell what can
be done to avoid them and what its impact is?
Regards,
Nimesh.
_______________________________________________
Pgpool-general mailing list
Pgpool-general at pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-general

Nimesh Satam

2009-03-22 14:58:24 UTC

Permalink

Tatsuo,

Thank you for your reply. Apologies for missing this out.

The reset_query_list is set to 'ABORT; RESET ALL; SET SESSION
AUTHORIZATION DEFAULT'.

Also something noticed was when we do a prepare it is put in a
transaction (BEGIN;..... COMMIT) an is fired on both the server.

Regards,
Nimesh.

Post by Tatsuo Ishii

I'm not sure for this part, but for followings I guess it happens
reset_query_list = 'ABORT;DISCARD ALL'

Post by Nimesh Satam
u=postgres h=xx01.xxxx.xxxx.com ERROR: ?prepared statement "S_3" does not exist
u=postgres h=xx01.xxxx.xxxx.com ERROR: ?prepared statement "S_1" does not exist
u=postgres h=xx01.xxxx.xxxx.com ERROR: ?prepared statement "S_4" does not exist

pgpool tries to drop prepared statements when client disconnect to
pgpool *after* executing quries specified in reset_query_list which
have removed all prepared statements.
To solve the problem, replace "DISCARD ALL" with "DISCARD TEMPORARY"
or whatever you want to use. See DISCARD man pages for details.
Note that "DISCARD ALL" might cause performance degradation because it
helds exclusive lock on a system catalog.
--
Tatsuo Ishii
SRA OSS, Inc. Japan