I recently ran into a big problems regarding blobs and rollback with postgreSQL 6.5.3
A table contains many entries and each entry includes a single
blob-oid. In a databaseprocessing perlscript I need to delete such a
blob and perform the lo_unlink and later I rollback the session without
commiting (and even no autocommiting) in between. In my opinion this
should have left the original blob unchanged.
But it seems that it is badly damaged: it is not deleted
and it is not functional and every future work causes strange effects.
(the strangest effect is, that whenever I try to work with this blob
inside a transaction I dont get an single error, but after commiting
the whole stuff there is no change in the database. It's like
accessing the blob prevents commiting)
This effects are completely different from the effects that occures by
just using an invalid blob_oid.
-------
this is what pgsql tells me at the prompt:
32609 is the oid of the 'damaged' blob:
32600 is no blob-oid at all
32545 is a valid blob_oid
# \lo_unlink 32609;
NOTICE: UserAbortTransactionBlock and not in in-progress state
ERROR: mdopen: couldn't open xinv32609: No such file or directory
# \lo_unlink 32600;
NOTICE: UserAbortTransactionBlock and not in in-progress state
oekoland=#
# \lo_unlink 32545;
NOTICE: UserAbortTransactionBlock and not in in-progress state
lo_unlink 32545
--------
if this is of interest, here is the raw perl-stuff that cause the problem:
...
$dbh->{AutoCommit} = 0;
...
show_blob; # work fine !!
...
$lobj_fd = $dbh->func($blob-oid, 'lo_unlink');
bloberror('lo_unlink failed') if not(defined($lobj_fd));
...
$dbh->rollback;
...
show_blob; # get an error !!
...
thanx for any help,
peter
--
mag. peter pilsl
pgp-key available
