prepareTreshold=0 vs. transaction control commands

From: Bernd Helmle <mailings(at)oopsware(dot)de>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: prepareTreshold=0 vs. transaction control commands
Date: 2012-04-05 08:54:38
Message-ID: A3F31011DB93D443CAA72041@apophis.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

During debugging database problems at a customer site with JDBC i discovered
the following behavior when passing prepareThreshold=0 to the connection DSN:

The SQL workflow is supposed as follows:

BEGIN;
SELECT 1;
COMMIT;

Stripped sample program attached. What i expected was the complete absence of
named prepared statements, however, examining the debug output i've seen the
following:

16:02:23.777 (1) FE=> Parse(stmt=S_1,query="BEGIN",oids={})
16:02:23.777 (1) FE=> Bind(stmt=S_1,portal=null)
16:02:23.777 (1) FE=> Execute(portal=null,limit=0)
16:02:23.777 (1) FE=> Parse(stmt=null,query="SELECT $1::int",oids={23})
16:02:23.778 (1) FE=> Bind(stmt=null,portal=null,$1=<'[B(at)7a982589'>)
16:02:23.778 (1) FE=> Describe(portal=null)
16:02:23.778 (1) FE=> Execute(portal=null,limit=0)
16:02:23.778 (1) FE=> Sync
16:02:23.779 (1) <=BE ParseComplete [S_1]
16:02:23.779 (1) <=BE BindComplete [null]
16:02:23.780 (1) <=BE CommandStatus(BEGIN)
16:02:23.780 (1) <=BE ParseComplete [null]
16:02:23.780 (1) <=BE BindComplete [null]
16:02:23.780 (1) <=BE RowDescription(1)
16:02:23.780 (1) Field(,INT4,4,T)
16:02:23.780 (1) <=BE DataRow(len=3)
16:02:23.781 (1) <=BE CommandStatus(SELECT 1)
16:02:23.789 (1) <=BE ReadyForQuery(T)
16:02:23.789 (1) simple execute,
handler=org(dot)postgresql(dot)jdbc2(dot)AbstractJdbc2Connection$TransactionCommandHandler(at)7c4c51,
maxRows=0, fetchSize=0, flags=22
16:02:23.789 (1) FE=> Parse(stmt=S_2,query="COMMIT",oids={})
16:02:23.790 (1) FE=> Bind(stmt=S_2,portal=null)
16:02:23.790 (1) FE=> Execute(portal=null,limit=1)
16:02:23.790 (1) FE=> Sync
16:02:23.790 (1) <=BE ParseComplete [S_2]
16:02:23.790 (1) <=BE BindComplete [null]
16:02:23.790 (1) <=BE CommandStatus(COMMIT)

Please note the named statement for BEGIN and COMMIT. The reason for this seems
to be that those transaction control commands aren't flagged with QUERY_ONESHOT
in case someone uses prepareThreshold=0 and always gets named. I couldn't get
an explanation for this from the code (AFAIU), so i wonder what the technical
reason behind this behavior is...anyone able to shed a light on this?

--
Thanks

Bernd

Attachment Content-Type Size
jdbc_test.java text/plain 1.0 KB

Browse pgsql-jdbc by date

  From Date Subject
Next Message vinu 2012-04-05 09:35:49 Re: Locking
Previous Message Kurt Seifried 2012-04-04 19:49:26 Re: [oss-security] CVE DISPUTE notification: postgresql-jdbc: SQL injection due improper escaping of JDBC statement parameters