Обсуждение: SQL_DATA_AT_EXEC processing broken for large objects in nightly git
Hi, the nightly git of the psqlODBC driver appears to no longer support insertion into LO columns when using SQL_DATA_AT_EXEC mode. The latest released driver version - psqlodbc-09.03.0400 - works. I've tested this against both PostgreSQL 9.4.4 and 9.4.5 on openSUSE 13.2. An adapted variant of the large-object-test.c program that uses SQL_DATA_AT_EXEC mode is attached. When run as part of the regression test suite against psqlodbc-09.03.0400, both the large-object-data-at-exec-test.c test completes successfully, just like large-object-test.c, and produces the following output in test/results/large-object-data-at-exec.out: \! "./src/large-object-data-at-exec-test" connected inserting large object... reading it back... hex: 0102030405060708 disconnecting However, when run as part of the regression test suite against nightly git build of psqlodbc, the large-object-data-at-exec-test.c test fails, unlike large-object-test.c, and produces the following output in test/results/large-object-data-at-exec.out: connected inserting large object... SQLParamData failed (final call) HY000=unexpected NULL parameter value Regards, Vadym
Вложения
Hi, I was able to reproduce it here. Because I can't understand the current code at once, please give me some time to solve the problem. regards, Hiroshi inoue On 2015/10/15 21:00, Vadym Krevs wrote: > Hi, > > the nightly git of the psqlODBC driver appears to no longer support > insertion into LO columns when using SQL_DATA_AT_EXEC mode. The > latest released driver version - psqlodbc-09.03.0400 - works. I've > tested this against both PostgreSQL 9.4.4 and 9.4.5 on openSUSE 13.2. > > An adapted variant of the large-object-test.c program that uses > SQL_DATA_AT_EXEC mode is attached. When run as part of the regression > test suite against psqlodbc-09.03.0400, both the > large-object-data-at-exec-test.c test completes successfully, just > like large-object-test.c, and produces the following output in > test/results/large-object-data-at-exec.out: > > \! "./src/large-object-data-at-exec-test" > connected > inserting large object... > reading it back... > hex: 0102030405060708 > disconnecting > > However, when run as part of the regression test suite against nightly > git build of psqlodbc, the large-object-data-at-exec-test.c test > fails, unlike large-object-test.c, and produces the following output > in test/results/large-object-data-at-exec.out: > > connected > inserting large object... > SQLParamData failed (final call) > HY000=unexpected NULL parameter value > > Regards, > Vadym
Thank you - much appreciated. Regards, Vadym On 04/11/15 07:48, Inoue, Hiroshi wrote: > Hi, > > I was able to reproduce it here. > Because I can't understand the current code at once, please give me > some time > to solve the problem. > > regards, > Hiroshi inoue > > On 2015/10/15 21:00, Vadym Krevs wrote: >> Hi, >> >> the nightly git of the psqlODBC driver appears to no longer support >> insertion into LO columns when using SQL_DATA_AT_EXEC mode. The >> latest released driver version - psqlodbc-09.03.0400 - works. I've >> tested this against both PostgreSQL 9.4.4 and 9.4.5 on openSUSE 13.2. >> >> An adapted variant of the large-object-test.c program that uses >> SQL_DATA_AT_EXEC mode is attached. When run as part of the regression >> test suite against psqlodbc-09.03.0400, both the >> large-object-data-at-exec-test.c test completes successfully, just >> like large-object-test.c, and produces the following output in >> test/results/large-object-data-at-exec.out: >> >> \! "./src/large-object-data-at-exec-test" >> connected >> inserting large object... >> reading it back... >> hex: 0102030405060708 >> disconnecting >> >> However, when run as part of the regression test suite against >> nightly git build of psqlodbc, the large-object-data-at-exec-test.c >> test fails, unlike large-object-test.c, and produces the following >> output in test/results/large-object-data-at-exec.out: >> >> connected >> inserting large object... >> SQLParamData failed (final call) >> HY000=unexpected NULL parameter value >> >> Regards, >> Vadym >
On 11/04/2015 09:48 AM, Inoue, Hiroshi wrote: > Hi, > > I was able to reproduce it here. > Because I can't understand the current code at once, please give me some > time > to solve the problem. I also started looking at this last week, and came up with a patch that I think fixes it, but I got distracted before completing it and replying here. So here's what I came up with... - Heikki
Вложения
On 2015/11/04 20:04, Heikki Linnakangas wrote: > On 11/04/2015 09:48 AM, Inoue, Hiroshi wrote: >> Hi, >> >> I was able to reproduce it here. >> Because I can't understand the current code at once, please give me some >> time >> to solve the problem. > > I also started looking at this last week, and came up with a patch > that I think fixes it, but I got distracted before completing it and > replying here. So here's what I came up with... > The solution seems to generate 2 transactions for 1 insertion. The insertion seems to be rejected when ByteaAsLongVarBinary=1. Are they intentional?l regards, Hiroshi Inoue
On 11/04/2015 04:26 PM, Inoue, Hiroshi wrote: > > > On 2015/11/04 20:04, Heikki Linnakangas wrote: >> On 11/04/2015 09:48 AM, Inoue, Hiroshi wrote: >>> Hi, >>> >>> I was able to reproduce it here. >>> Because I can't understand the current code at once, please give me some >>> time >>> to solve the problem. >> >> I also started looking at this last week, and came up with a patch >> that I think fixes it, but I got distracted before completing it and >> replying here. So here's what I came up with... > > The solution seems to generate 2 transactions for 1 insertion. Hmm, you're right, although that doesn't seem to be a new issue. The 09.03.0400 driver does the same, see attached logs from log_statements=all. Would be nice to fix that, but it's a separate issue. > The insertion seems to be rejected when ByteaAsLongVarBinary=1. Oh. It works for me. How are you testing that? - Heikki
Вложения
On 2015/11/09 20:53, Heikki Linnakangas wrote: > On 11/04/2015 04:26 PM, Inoue, Hiroshi wrote: >> >> >> On 2015/11/04 20:04, Heikki Linnakangas wrote: >>> On 11/04/2015 09:48 AM, Inoue, Hiroshi wrote: >>>> Hi, >>>> >>>> I was able to reproduce it here. >>>> Because I can't understand the current code at once, please give me >>>> some >>>> time >>>> to solve the problem. >>> >>> I also started looking at this last week, and came up with a patch >>> that I think fixes it, but I got distracted before completing it and >>> replying here. So here's what I came up with... >> >> The solution seems to generate 2 transactions for 1 insertion. > > Hmm, you're right, although that doesn't seem to be a new issue. The > 09.03.0400 driver does the same, see attached logs from > log_statements=all. Would be nice to fix that, but it's a separate issue. Oh you are right. I will examine it. > > >> The insertion seems to be rejected when ByteaAsLongVarBinary=1. > > Oh. It works for me. How are you testing that? I define LO type and set ByteaAsLongVarBinary=1. The failure is #ERROR: column "large_data" is of type LO but expression is type "bytea" . regards, Hiroshi Inoue
Hi Heikki, The attached patch seems to fix all but I don't understand sqltype_to_bind_pgtype() well. Comments? regards, Hiroshi Inoue On 2015/11/11 20:24, Inoue, Hiroshi wrote: > On 2015/11/09 20:53, Heikki Linnakangas wrote: >> On 11/04/2015 04:26 PM, Inoue, Hiroshi wrote: >>> >>> >>> On 2015/11/04 20:04, Heikki Linnakangas wrote: >>>> On 11/04/2015 09:48 AM, Inoue, Hiroshi wrote: >>>>> Hi, >>>>> To >>>>> I was able to reproduce it here. >>>>> Because I can't understand the current code at once, please give >>>>> me some >>>>> time >>>>> to solve the problem. >>>> >>>> I also started looking at this last week, and came up with a patch >>>> that I think fixes it, but I got distracted before completing it and >>>> replying here. So here's what I came up with... >>> >>> The solution seems to generate 2 transactions for 1 insertion. >> >> Hmm, you're right, although that doesn't seem to be a new issue. The >> 09.03.0400 driver does the same, see attached logs from >> log_statements=all. Would be nice to fix that, but it's a separate >> issue. > > Oh you are right. I will examine it. > >> >> >>> The insertion seems to be rejected when ByteaAsLongVarBinary=1. >> >> Oh. It works for me. How are you testing that? > > I define LO type and set ByteaAsLongVarBinary=1. > The failure is #ERROR: column "large_data" is of type LO but > expression is type "bytea" . > > regards, > Hiroshi Inoue > > > >
Вложения
On 17/11/15 04:35, Inoue, Hiroshi wrote: > Hi Heikki, > > The attached patch seems to fix all but I don't understand > sqltype_to_bind_pgtype() well. > Comments? Ah, thanks, I see what the problem is now. I haven't looked at the extra-transaction part yet, but the rest looks good to me, so I committed my original patch and the sqltype_to_bind_pgtype() part of your patch. You must've figured this out already, but I'll summarize the issue for the sake of the archives: sqltype_to_bind_pgtype() was introduced in commit 1ccd04c70b8037b268eefcaae09ce131c8c33b59. Before that, query parameters were always sent to the server as 'unknown', and the server deduced the correct datatype. After that commit, we send a suitable PostgreSQL type OID to the server, depending on the SQL_* type code that application gave. For example, if you call SQLBindParameters(SQL_INTEGER), we send the query parameter to the server as 'int4'. That fixes cases like "SELECT '555' < ?", where it makes a big difference whether the server treats the parameter as text or integer. So when the application binds a parameter as SQL_LONGVARBINARY, we now send it to the server as 'bytea', if ByteaAsLongVarBinary=1, or as 'lo' otherwise. If you set ByteaAsLongVarBinary=1, the driver reports bytea fields as SQL_LONGVARBINARY, so it makes sense that we do the same in the other direction: when the application binds a parameter as SQL_LONGVARBINARY, send it as 'bytea' to the server. But unfortunately, it doesn't work if the column is in fact a large object. Your patch reverts the behaviour of SQL_LONGVARBINARY parameters back to sending them as 'unknown'. That seems reasonable. It would be nice to pass a datatype to the server, but there is no way to specify "bytea or a large object". If we choose either one, the other case will be wrong. Sending SQL_LONGVARBINARY parameters as 'unknown' should be OK in practice, there is not much danger for "SELECT '555' < ?" kind of ambiguity with binary parameters. - Heikki