Re: Batch INSERT failing with error 22P02
От | Kris Jurka |
---|---|
Тема | Re: Batch INSERT failing with error 22P02 |
Дата | |
Msg-id | Pine.BSO.4.64.0709181916350.449@leary.csoft.net обсуждение исходный текст |
Ответ на | Re: Batch INSERT failing with error 22P02 (Eric Faulhaber <ecf@goldencode.com>) |
Ответы |
Re: Batch INSERT failing with error 22P02
(Eric Faulhaber <ecf@goldencode.com>)
|
Список | pgsql-jdbc |
On Tue, 18 Sep 2007, Eric Faulhaber wrote: > Any idea what component is mixing up these types? As I understand it from > setting a breakpoint at the point the exception is thrown and tracking back > through the stack, the OID type (26) is reported by the back-end and is > compared with the type the driver expects for the given parameter. But then > I wonder why we would not see this error with the same back-end and the older > JDBC driver... Basically you have two text columns in your table (lot and serial_number) that alternate between backend type oids 1043 (varchar) and 26 (oid). This is most likely because hibernate sometimes calls setClob and sometimes it calls setString for these columns. Additionally you have something that is setting an unknown type in the driver that the server must resolve. This could be something like setNull(x, Types.OTHER), setObject(x, null), or by using the URL parameter stringtype=unspecified. An unspecified type is common when using timestamps, but you aren't using any in this table. So what's going on is, as you work through the rows you want to insert the types of these columns occasionally changes and a the driver must reparse a new server side prepared statement to handle the new type. Some time after the new server side statement has been setup we get an unspecified type and we ask the server to tell us what type we should really use. The server responds with a new type for something we don't think should have changed and we get an error. Consider this set of events: Server Has Current Data Action nothing yet 1043, 26 No server statement yet, parse 1043, 26 1043, 26 server matches, execute away 1043, 26 1043, 1043 server doesn't match, reparse 1043, 1043 1043, 0 0 matches, so don't reparse, but describe So we are passed an unknown type and we ask the server to resolve it. When we get the describe answer back we set it on the data, overwriting the 0 with the returned 1043. So that's all legal and expected behavior, but for some reason in your case. So one potential solution (and optimization) is to avoid the describe if we already have a prepared statement with type information filled in. We expect the backend to just echo back what the statement was prepared with, but what's the point when we have that already have that information in the driver? So this would likely avoid your problem, but it doesn't explain what's going on... I diffed the 8.1-408 release with 8.3dev for the core/v3 directory and the only relevant change is SimpleParameterList#bind. When deciding to overwrite an existing type we now add a check for NULL_OBJECT that 8.1 does not have: if (oid == Oid.UNSPECIFIED && paramTypes[index] != Oid.UNSPECIFIED && value == NULL_OBJECT) return; I know I haven't described this well, especially for people who aren't versed in the frontend/backend protocol and how the driver issues queries, but I must admit I have no idea why this is failing. There is a potential workaround, but it doesn't explain the root cause. I'll keep investigating though... 1) Are you using stringtype=unspecified in your URL? 2) Any idea why these two columns are sometimes interpreted as strings and sometimes as Clobs? It doesn't look to be the case with the other text fields in your table. 3) The log file you sent me says: "Using 2 threads for import". Could you elaborate on what the different threads are doing? Any chance they're using the same Connection and somehow interfering with each other? Kris Jurka
В списке pgsql-jdbc по дате отправления: