Thread: DatabaseMetaData and Transactions
Greetings. Having a problem with regards DatabaseMetaData retrievals within a Transaction. I have a large transaction running - within which database alterations need to occur. However, during this process I need to retrieve Table metadata (via the DatabaseMetaData.getTables() method). However, this does not seem possible in POSTGRES? I receive the following error: ERROR: current transaction is aborted, queries ignored until end of transaction block. I trace that directly to a call to the getTables method of the DatabaseMetaData implementation. Can anyone provide me with any advice here? Is this correct behaviour, and is there any way (other than caching table metadata prior to, and maintaining said cache during the transaction) to allow for meta data retrieval during a transaction? Thanks in advance! Regards, Carl
Carl, One of the queries is failing, have a look at the logs in postgres and report back to us what is failing. Dave On 6-Jun-05, at 9:38 AM, Carl Olivier wrote: > Greetings. > > Having a problem with regards DatabaseMetaData retrievals within a > Transaction. I have a large transaction running - within which > database > alterations need to occur. However, during this process I need to > retrieve > Table metadata (via the DatabaseMetaData.getTables() method). > However, this > does not seem possible in POSTGRES? I receive the following error: > > ERROR: current transaction is aborted, queries ignored until end of > transaction block. > > I trace that directly to a call to the getTables method of the > DatabaseMetaData implementation. > > Can anyone provide me with any advice here? Is this correct > behaviour, and > is there any way (other than caching table metadata prior to, and > maintaining said cache during the transaction) to allow for meta data > retrieval during a transaction? > > Thanks in advance! > > Regards, > > Carl > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >
Hi Dave.
Thanks - should have thought of looking in those logs! So much for our
exception handling/reporting at that point.
Anyway - now resolved, with my thanks.
I do however have an additional query!
In 7.3 (using the 7.3 JDBC driver) we were able to do the following:
=== SNIP ===
if (value == null)
{
stmt.setString( index, null );
}
=== END ===
Some context here. We have a method that constructs a PreparedStatement
taking the sql (eg):
UPDATE table SET cola = ?, colb = ?, colc = ?
Or
INSERT INTO table (cola, colb, colc) VALUES (?, ?, ?)
And an Object[] of values (in the correct order) for the sql being used in
the PreparedStatement.
Now - the reason we do a setString(index, null) and not a setNull(index,
Types.SOME_TYPE) is because this method is generic and does not always KNOW
the datatypes for the columns.
Now, using PostgreSQL 8 (and the new JDBC driver for 8) we ARE still able to
do that when using a 7..3 db server, but get the following error when using
a version 8 PGSQL server:
ERROR: column "created_by" is of type integer but expression is of type
character varying
Where created_by is being passed a NULL value (using the setString(index,
null) method).
Does anyone have any comments/suggestions/etc? Is there any way to get
around this? Or will we need to update our side to always pass in the
column meta data etc for use?
Thanks in advance,
Carl
-----Original Message-----
From: Dave Cramer [mailto:pg@fastcrypt.com]
Sent: Monday, June 06, 2005 4:23 PM
To: Carl Olivier
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] DatabaseMetaData and Transactions
Carl,
One of the queries is failing, have a look at the logs in postgres and
report back to us what is failing.
Dave
On 6-Jun-05, at 9:38 AM, Carl Olivier wrote:
> Greetings.
>
> Having a problem with regards DatabaseMetaData retrievals within a
> Transaction. I have a large transaction running - within which
> database alterations need to occur. However, during this process I
> need to retrieve
> Table metadata (via the DatabaseMetaData.getTables() method).
> However, this
> does not seem possible in POSTGRES? I receive the following error:
>
> ERROR: current transaction is aborted, queries ignored until end of
> transaction block.
>
> I trace that directly to a call to the getTables method of the
> DatabaseMetaData implementation.
>
> Can anyone provide me with any advice here? Is this correct
> behaviour, and is there any way (other than caching table metadata
> prior to, and maintaining said cache during the transaction) to allow
> for meta data retrieval during a transaction?
>
> Thanks in advance!
>
> Regards,
>
> Carl
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
On Tue, 7 Jun 2005, Carl Olivier wrote:
> In 7.3 (using the 7.3 JDBC driver) we were able to do the following:
>
> === SNIP ===
>
> if (value == null)
> {
> stmt.setString( index, null );
> }
>
> Now, using PostgreSQL 8 (and the new JDBC driver for 8) we ARE still able to
> do that when using a 7..3 db server, but get the following error when using
> a version 8 PGSQL server:
>
> ERROR: column "created_by" is of type integer but expression is of type
> character varying
>
> Where created_by is being passed a NULL value (using the setString(index,
> null) method).
>
If you don't know what the type is you shouldn't use setString, instead
try setObject(index, null) or setNull(index, Types.OTHER) which should
work as you desire.
Kris Jurka
You can work around this problem by disabling the V3 protocol, which is by default used when communicating with newer servers. This is a performance hit because the V3 protocol is faster than V2, but shouldn't be too bad. See docs here: http://jdbc.postgresql.org/documentation/head/connect.html#connection- parameters -- Mark Lewis On Tue, 2005-06-07 at 12:54 +0200, Carl Olivier wrote: ... > In 7.3 (using the 7.3 JDBC driver) we were able to do the following: > > === SNIP === > > if (value == null) > { > stmt.setString( index, null ); > } > > === END === > > Some context here. We have a method that constructs a PreparedStatement > taking the sql (eg): > > UPDATE table SET cola = ?, colb = ?, colc = ? > > Or > > INSERT INTO table (cola, colb, colc) VALUES (?, ?, ?) > > And an Object[] of values (in the correct order) for the sql being used in > the PreparedStatement. > > Now - the reason we do a setString(index, null) and not a setNull(index, > Types.SOME_TYPE) is because this method is generic and does not always KNOW > the datatypes for the columns. > > Now, using PostgreSQL 8 (and the new JDBC driver for 8) we ARE still able to > do that when using a 7..3 db server, but get the following error when using > a version 8 PGSQL server: > > ERROR: column "created_by" is of type integer but expression is of type > character varying > > Where created_by is being passed a NULL value (using the setString(index, > null) method). > > Does anyone have any comments/suggestions/etc? Is there any way to get > around this? Or will we need to update our side to always pass in the > column meta data etc for use? > ...
Carl Olivier wrote: > Now - the reason we do a setString(index, null) and not a setNull(index, > Types.SOME_TYPE) is because this method is generic and does not always KNOW > the datatypes for the columns. > ERROR: column "created_by" is of type integer but expression is of type > character varying > Does anyone have any comments/suggestions/etc? Is there any way to get > around this? Or will we need to update our side to always pass in the > column meta data etc for use? You're going to have to pass metadata down, or change your queries to explicitly cast the parameters in the SQL itself. The driver has exactly the same problem as your code does -- with the v3 protocol it needs to provide a type for the parameter, but if it's just provided as a string the only type it can assume is text.. -O
Oliver Jowett <oliver@opencloud.com> writes:
> You're going to have to pass metadata down, or change your queries to
> explicitly cast the parameters in the SQL itself. The driver has exactly
> the same problem as your code does -- with the v3 protocol it needs to
> provide a type for the parameter, but if it's just provided as a string
> the only type it can assume is text..
Is there any chance of a win in passing the type across to the backend
as "unknown", and seeing if the backend can infer something reasonable?
For example given
WHERE int4col = ?
it'd be reasonable to infer the type of the ? symbol as int4, and that
logic has been built into the backend since forever.
regards, tom lane
Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> writes: > >>You're going to have to pass metadata down, or change your queries to >>explicitly cast the parameters in the SQL itself. The driver has exactly >>the same problem as your code does -- with the v3 protocol it needs to >>provide a type for the parameter, but if it's just provided as a string >>the only type it can assume is text.. > > > Is there any chance of a win in passing the type across to the backend > as "unknown", and seeing if the backend can infer something reasonable? > For example given > WHERE int4col = ? > it'd be reasonable to infer the type of the ? symbol as int4, and that > logic has been built into the backend since forever. It's a possibility, but: a) really this is an application bug (admittedly a common one) .. the JDBC API provides type info for parameters, and if the application claims that a parameter is a String, why should the driver second-guess it? b) there were some cases (? IS NULL and some cases with function args, from memory?) which didn't work when an unknown OID was used, so to some extent you're exchanging one problem for another. We do pass the unknown OIDs for "untyped" (JDBC Types.OTHER) NULLs already. Passing unknown for setObject(column, value, Types.OTHER) seems reasonable given what we do with setNull().. but that doesn't solve the problem of applications using setString() for parameters that actually need to be some other type. -O
Just a quick note to say thanks to all that offered advice - all the reported problems have been solved! Regards, Carl -----Original Message----- From: Oliver Jowett [mailto:oliver@opencloud.com] Sent: Wednesday, June 08, 2005 5:09 AM To: Tom Lane Cc: Carl Olivier; pgsql-jdbc@postgresql.org Subject: Re: [JDBC] DatabaseMetaData and Transactions Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> writes: > >>You're going to have to pass metadata down, or change your queries to >>explicitly cast the parameters in the SQL itself. The driver has >>exactly the same problem as your code does -- with the v3 protocol it >>needs to provide a type for the parameter, but if it's just provided >>as a string the only type it can assume is text.. > > > Is there any chance of a win in passing the type across to the backend > as "unknown", and seeing if the backend can infer something reasonable? > For example given > WHERE int4col = ? > it'd be reasonable to infer the type of the ? symbol as int4, and that > logic has been built into the backend since forever. It's a possibility, but: a) really this is an application bug (admittedly a common one) .. the JDBC API provides type info for parameters, and if the application claims that a parameter is a String, why should the driver second-guess it? b) there were some cases (? IS NULL and some cases with function args, from memory?) which didn't work when an unknown OID was used, so to some extent you're exchanging one problem for another. We do pass the unknown OIDs for "untyped" (JDBC Types.OTHER) NULLs already. Passing unknown for setObject(column, value, Types.OTHER) seems reasonable given what we do with setNull().. but that doesn't solve the problem of applications using setString() for parameters that actually need to be some other type. -O