Thread: libpq lo_open errors when first action in connection

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


I'm having some problems with lo_open inside of DBD::Pg (which
uses libpq) and need help in debugging the problem. The problem
is that a call to lo_open works perfectly, *unless* it is the
first thing that this connection to the database has done, in
which case it returns a -1 (which comes back to perl as undef).
To be more specific, I need to issue a "SELECT 123;" (or anything
else via PQexec) before the call will succeeed. All the parameters
to lo_open look normal and identical whether PQexec has been called
or not, and lo_creat always works just fine. It has to be PQexec*,
a prepare alone will not do the trick.

Here is a small sample code in Perl that demonstrates:

#!perl 
use DBI; use strict; use warnings; 
my $dbh = DBI->connect($ENV{DBI_DSN}, $ENV{DBI_USER}, '', {AutoCommit=>0, RaiseError=>1}); 
# If the below line is commented out, this test will fail:
$dbh->do("SELECT 123"); 
my $W = $dbh->{pg_INV_WRITE};
my $object = $dbh->func($W, 'lo_creat');
my $handle = $dbh->func($object, $W, 'lo_open');
printf "This test %s!\n", defined $handle ? "worked" : "did not work";
$dbh->disconnect;


Thanks,
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200505121009
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCg2nKvJuQZxSWSsgRAoqxAJ4+jdYo+BrxHb0smXzU2FzOzPODHwCg/hZB
oHcq8t479a5OKaURCl8Kl5U=
=rmbL
-----END PGP SIGNATURE-----

On Fri, May 13, 2005 at 02:39:25AM -0000, Greg Sabino Mullane wrote:

> I'm having some problems with lo_open inside of DBD::Pg (which
> uses libpq) and need help in debugging the problem. The problem
> is that a call to lo_open works perfectly, *unless* it is the
> first thing that this connection to the database has done, in
> which case it returns a -1 (which comes back to perl as undef).
> To be more specific, I need to issue a "SELECT 123;" (or anything
> else via PQexec) before the call will succeeed. All the parameters
> to lo_open look normal and identical whether PQexec has been called
> or not, and lo_creat always works just fine. It has to be PQexec*,
> a prepare alone will not do the trick.

The problem is that LO descriptors are only valid for the duration of
the transaction.  So your "select 123" opens a transaction, and
autocommit = 0 makes it not close until after the function is called.
If you don't send the query first, both function calls happen in
different transactions.

I think you should make the func() method open a transaction, just like
a query does.

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiándose", y el computador dirá "especifique el tipo de ciervo"
(Jason Alexander)

"Greg Sabino Mullane" <greg@turnstep.com> writes:
> I'm having some problems with lo_open inside of DBD::Pg (which
> uses libpq) and need help in debugging the problem.

lo_open doesn't work unless inside a transaction block (and even
if it did work, the result would be useless to you).

I believe the reason for the -1 result is this code inside libpq's
lo_open:
       /* have to do this to reset offset in shared fd cache */       /* but only if fd is valid */       if (fd >= 0
&&lo_lseek(conn, fd, 0L, SEEK_SET) < 0)           return -1;
 

Since you forgot to start a transaction block, the results of the
backend lo_open call are already dead and the lo_lseek fails withERROR:  invalid large-object descriptor: 0

This looks rather considerably like a hack to compensate for a very
long-gone bug ... can anyone see a reason not to take it out?
Of course taking it out wouldn't improve Greg's results much, since
he'd still get a failure at the next LO operation, but this sure
looks like it's incurring a useless network round trip...
        regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> The problem is that LO descriptors are only valid for the
> duration of the transaction.

Thanks, that's it all right. I forgot to update the lo_ stuff
when we switched the autocommit mojo around a while back.

> I think you should make the func() method open a transaction,
> just like a query does.

Yep, that's the basic solution - for lo_creat. Thanks again.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200505130807
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFChJjdvJuQZxSWSsgRAgh9AKDBRlAjbDt4zcfqnvTuEq9Z4cBfygCgwes6
hJ0De8jAcMYKOrSUH3uGoZk=
=/ooS
-----END PGP SIGNATURE-----