Thread: problems with large-objects
Hi, did anyone of you ever saw some strange problems with large objects and the postgresql 6.51? Sometimes ago I want to switch from 6.4.2 to 6.5.1 and my tools, using the large objects through the perl-library refused to work. lo_import didn't work anymore. I traced it down through the libpq, where I found that a test with lo_lseek in the lo_open-function (file: fe-lobj.c) fails. Going deeper into the source and drawing a trace of debug-output across some files I switched tracing from the libpq to the backend. Here I found in be-fsstubs.c, that after the function lo_open sets a cookie using the function newLOfd, just this cookie will be cleared by lo_commit, which is called by CommitTransaction (in xact.c). The handle, handed out to the application by the library-function lo_open is now not longer valid and the next access through this handle fails. Did I understood something wrong in the source or is there a bug? I attach the test-application (in Perl, named db_test), the debug-output of the application (and the outputs in the libpq) (db_test2.log), the debug-output of postmaster (post2.log) and the files I patched with my debug-output (you will find them easily because in every line I changed there is the string 'WN'). May be anyone of you, especially from the developing-guys, find time to have a look into the attachments? Thanky you, Wolfgang
Attachment
> did anyone of you ever saw some strange problems with large objects and
> the postgresql 6.51?
I don't work with Large Objects, but I am pretty sure that the
behavior was changed/fixed for v6.5 to *require* all large object
manipulation to happen within a transaction. This was always a
requirement, but Postgres did not enforce it and had some potentially
bad behavior lurking as a result.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Wolfgang Hottgenroth <wn@kja-essen.de> writes:
> Sometimes ago I want to switch from 6.4.2 to 6.5.1 and my tools, using
> the large objects through the perl-library refused to work. lo_import
> didn't work anymore.
6.5.* only allows large objects to be kept open within a single
transaction; you have to wrap BEGIN...END around anything that involves
lo_open ... lo_read/write ... lo_close. In particular, since lo_import
and lo_export are just such sequences, they need to be called inside
a transaction.
This restriction has been documented as required all along, but older
Postgres releases didn't enforce it (they'd just fail once in a while
if you didn't do it :-().
It'd be nice if lo_import could start/finish a transaction for itself,
but I don't see any good way to do that without breaking things for
the case that you want to do several lo_imports in one transaction.
regards, tom lane
Hi Thomas, hi all, Thomas Lockhart wrote: > > > did anyone of you ever saw some strange problems with large objects and > > the postgresql 6.51? > > I don't work with Large Objects, but I am pretty sure that the > behavior was changed/fixed for v6.5 to *require* all large object > manipulation to happen within a transaction. This was always a > requirement, but Postgres did not enforce it and had some potentially > bad behavior lurking as a result. Have many thanks for that fast answer. It is new to me, that large-object-operations requires transactions. Did I read the manual to fast or isn't it written down there? I will try it. Wolfgang
Hi Tom, thank you for that fast answer. So I failed when reading the manual, because I never realized, that using transactions are a requirement when working with LO's. Wolfgang Tom Lane wrote: > > Wolfgang Hottgenroth <wn@kja-essen.de> writes: > > Sometimes ago I want to switch from 6.4.2 to 6.5.1 and my tools, using > > the large objects through the perl-library refused to work. lo_import > > didn't work anymore. > > 6.5.* only allows large objects to be kept open within a single > transaction; you have to wrap BEGIN...END around anything that involves > lo_open ... lo_read/write ... lo_close. In particular, since lo_import > and lo_export are just such sequences, they need to be called inside > a transaction. > > This restriction has been documented as required all along, but older > Postgres releases didn't enforce it (they'd just fail once in a while > if you didn't do it :-(). > > It'd be nice if lo_import could start/finish a transaction for itself, > but I don't see any good way to do that without breaking things for > the case that you want to do several lo_imports in one transaction. > > regards, tom lane
> So I failed when reading the manual,
> because I never realized, that using transactions are a requirement when
> working with LO's.
> > This restriction has been documented as required all along,
Well, just because we think we've documented it doesn't mean that it
is obvious. Now that you know, do the docs you were reading make this
clear and you just missed it, or should it be mentioned in more places
than it is currently? Let us know if you have suggestions for
improvements...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Hi again, Thomas Lockhart wrote: > > > So I failed when reading the manual, > > because I never realized, that using transactions are a requirement when > > working with LO's. > > > This restriction has been documented as required all along, > > Well, just because we think we've documented it doesn't mean that it > is obvious. Now that you know, do the docs you were reading make this > clear and you just missed it, or should it be mentioned in more places > than it is currently? Let us know if you have suggestions for > improvements... Of course I take an immediate look into the docs, and I found it: In the sample program of the Large-Object-chapter (Chapter 15). Is this the only place where it is documented? If this is the only place, then I think, it should be mentioned in more places. Or can you tell me, where I can read more about it? Wolfgang
> Of course I take an immediate look into the docs, and I found it: In the
> sample program of the Large-Object-chapter (Chapter 15). Is this the
> only place where it is documented? If this is the only place, then I
> think, it should be mentioned in more places. Or can you tell me, where
> I can read more about it?
I don't know. I just added a note to the top of the "Interfaces"
portion of that chapter which mentions this explicitly. Maybe that is
enough...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Tom Lane <tgl@sss.pgh.pa.us> writes:
> 6.5.* only allows large objects to be kept open within a single
> transaction; you have to wrap BEGIN...END around anything that involves
> lo_open ... lo_read/write ... lo_close. In particular, since lo_import
> and lo_export are just such sequences, they need to be called inside
> a transaction.
Note that the Perl interface to large objects is broken in both 6.4.2
and 6.5.1, in that a BLOB containing a NUL character will be truncated
at the first NUL when read back using the Perl lo_read method. I have
reported this problem both to this list and via various other channels
over the past few months, but I just checked the CVS repository and
there is still no fix there.
For what it is worth, in case anyone reading this wants to either fix
their own copies or better yet fix the main source repository, the
necessary patch is simply:
======cut=here========================================================
--- perl5/Pg.xs Sat Aug 14 12:46:35 1999
+++ perl5.fixed/Pg.xs Sun Oct 3 14:00:35 1999
@@ -1,6 +1,6 @@
/*-------------------------------------------------------
*
- * $Id: Pg.xs,v 1.12 1999/02/19 23:27:17 tgl Exp $
+ * $Id: Pg.xs,v 1.12 1999/02/19 23:27:17 tgl Exp $ (NUL bug patched)
*
* Copyright (c) 1997, 1998 Edmund Mergl
*
@@ -643,7 +643,7 @@
}
OUTPUT:
RETVAL
- buf
+ buf sv_setpvn((SV*)ST(2), buf, RETVAL);
int
lo_write(conn, fd, buf, len)
@@ -1029,7 +1029,7 @@
}
OUTPUT:
RETVAL
- buf
+ buf sv_setpvn((SV*)ST(2), buf, RETVAL);
int
======cut=here========================================================
For the curious, this makes Perl set the length of the returned
scalar to be the size returned by the library lo_read() call, instead
of using C's strlen() function to work out the length.
Hope this helps someone,
Doug.
On Sat, 2 Oct 1999, Thomas Lockhart wrote: > > Of course I take an immediate look into the docs, and I found it: In the > > sample program of the Large-Object-chapter (Chapter 15). Is this the > > only place where it is documented? If this is the only place, then I > > think, it should be mentioned in more places. Or can you tell me, where > > I can read more about it? > > I don't know. I just added a note to the top of the "Interfaces" > portion of that chapter which mentions this explicitly. Maybe that is > enough... We need to add a reference to this in the JDBC docs, as I'm answering quite a few "bug" reports where LargeObjects are not working. For jdbc, you need to use the setAutoCommit() method to false. ie: Connection mycon; ... mycon.setAutoCommit(false); ... now use Large Objects I'm thinking of adding a more descriptive error message when LO's fail. Peter -- Peter T Mount peter@retep.org.uk Main Homepage: http://www.retep.org.uk PostgreSQL JDBC Faq: http://www.retep.org.uk/postgresJava PDF Generator: http://www.retep.org.uk/pdf
> We need to add a reference to this in the JDBC docs, as I'm answering
> quite a few "bug" reports where LargeObjects are not working.
I'll add this in. I'm making slight changes to the markup, but am
reminded that *most* of the jdbc doc has not been marked up at all
yet. I wasn't sure how to handle the ascii class diagrams, and how
stable the doc is. If we go through the trouble of marking it up, can
you then make changes to it? Or did the text come from somewhere else,
where you maintain the original docos?
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California