From: Oliver Jowett <oliver@opencloud.com>
To: James Robinson <jlrobins@socialserve.com>
Cc: pgsql-jdbc@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Prepared Statements and large where-id-in constant blocks?
Date: Tue, 20 Apr 2004 10:21:02 +1200
Message-ID: <408450CE.1090009@opencloud.com> (raw)
In-Reply-To: <11531DB4-921A-11D8-8696-000A9566A412@socialserve.com>
References: <11531DB4-921A-11D8-8696-000A9566A412@socialserve.com>
James Robinson wrote:
> Howdy:
>
> Java middlewares like JBossCMP issue many queries in the general
> form of:
>
> SELECT t1.att1, t1.att2 ... t1.attN FROM t1 WHERE (t1.id = X) or
> (t1.id = Y) ....
>
> where there may be anywhere between 1 and thousands of "(id = N)" blocks
> ORed together.
> 2) Does the JDBC spec allow any hooks for passing in a set of ids as
> one single param? We'd need the SQL-template to be prepared to look
> something like:
>
> SELECT t1.attr1 FROM t1 where t1.id in ( ? )
This was discussed a while ago when fixing some SQL escaping issues.
Previously you could use setObject to get around the usual string
escaping and do the above if you constructed the IN set string yourself,
but the driver lost a lot of type information, and if the input was
hostile you were in trouble.
The JDBC spec doesn't seem to provide a portable way to fill an IN
clause with a single parameter. An extension for supporting IN lists
safely was discussed at the time, but nothing concrete came out of it.
The CMP layer could perhaps use the = ANY array syntax and setArray()
(note that setArray() in the current driver needs fixing, I have an old
patch to do this):
SELECT t1.attr1 FROM t1 where t1.id = ANY (?)
Alternatively, perhaps the CMP layer could generate a largish N-value IN
(?,?,?,...). Then it can reuse that single prepared query for all
queries with <= N values, filling the trailing parameters that aren't
needed with NULL or a dummy/duplicate value.
I don't know how these queries would perform compared to a
correctly-sized IN clause, though.
-O
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliver@opencloud.com, jlrobins@socialserve.com, pgsql-jdbc@postgresql.org
Subject: Re: Prepared Statements and large where-id-in constant blocks?
In-Reply-To: <408450CE.1090009@opencloud.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox