losing my large objects with Postgresql 8.1.4 and 8.1.5
Some of my custom server functions/data types that work correctly
under Postgresql 8.0.1 are having trouble with lost large objects
under Postgresql 8.1.4 and Postgresql 8.1.5, but only in particular
usages. I'm hoping somebody familiar with the changes made to
Postgresql since 8.0.X can suggest what might be happening.
Details:
I have a datatype for storing large blocks of data. Because a block
of data may be multi gigabytes in size, I can't rely on the toaster
mechanism. Instead, each instance of my datatype contains a key to
another table that contains a list of oids for large objects (each
containing a portion of the block).
My functions are written in C using the SPI interface. One of the
functions creates an instance of the datatype, and another one
converts the datatype to text.
When I execute the following sequence of commands:
select MyTypeToText( BuildMyType('asdf'));
I get the following error
ERROR: large object 33016 does not exist
\lo_list (in psql) doesn't show any new large objects.
However, when I execute the following sequence of commands:
create table smith( a MyType);
insert into smith select BuildMyType('asdf');
select MyTypeToText(a) from smith;
the contents of the MyType object are displayed correctly, no error messages.
\lo_list shows an additional large object.
I have stepped through both the BuildMyType function and the
MyTypeToText function and seen that the
lob id that BuildMyType is using is the same one that MyTypeToText is
trying to use in the problem case.
I tried producing a reduced version of the MyType code to demonstrate
the problem but unfortunately it works perfectly.
Can anybody suggest what could cause this type of behavior?
Thank you,
**********************************************
Eric Davies, M.Sc.
Barrodale Computing Services Ltd.
Tel: (250) 472-4372 Fax: (250) 472-4373
Web: http://www.barrodale.com
Email: eric@barrodale.com
**********************************************
Mailing Address:
P.O. Box 3075 STN CSC
Victoria BC Canada V8W 3W2
Shipping Address:
Hut R, McKenzie Avenue
University of Victoria
Victoria BC Canada V8W 3W2
**********************************************
Eric Davies <Eric@barrodale.com> writes:
Some of my custom server functions/data types that work correctly
under Postgresql 8.0.1 are having trouble with lost large objects
under Postgresql 8.1.4 and Postgresql 8.1.5, but only in particular
usages.
When I execute the following sequence of commands:
select MyTypeToText( BuildMyType('asdf'));
I get the following error
ERROR: large object 33016 does not exist
\lo_list (in psql) doesn't show any new large objects.
MyTypeToText is probably referencing a start-of-statement snapshot,
in which the LO doesn't exist yet. This is a consequence of making
read-only accesses to LOs be MVCC-compliant. Probably your best bet
for working around it is to open the LO in read/write mode, even if
you don't intend to write it --- that will make the behavior like 8.0.
Relevant 8.1 release note:
Read-only large object descriptors now obey MVCC snapshot semantics
When a large object is opened with INV_READ (and not INV_WRITE),
the data read from the descriptor will now reflect a "snapshot" of
the large object's state at the time of the transaction snapshot in
use by the query that called lo_open(). To obtain the old behavior
of always returning the latest committed data, include INV_WRITE in
the mode flags for lo_open().
regards, tom lane
thank you Tom!
that did the trick!
I'm still in the dark why my test data type didn't exhibit the
problem, but I'm certainly a much happier camper now.
Eric.
At 05:40 PM 05/01/2007, Tom Lane wrote:
Eric Davies <Eric@barrodale.com> writes:
Some of my custom server functions/data types that work correctly
under Postgresql 8.0.1 are having trouble with lost large objects
under Postgresql 8.1.4 and Postgresql 8.1.5, but only in particular
usages.When I execute the following sequence of commands:
select MyTypeToText( BuildMyType('asdf'));
I get the following error
ERROR: large object 33016 does not exist
\lo_list (in psql) doesn't show any new large objects.MyTypeToText is probably referencing a start-of-statement snapshot,
in which the LO doesn't exist yet. This is a consequence of making
read-only accesses to LOs be MVCC-compliant. Probably your best bet
for working around it is to open the LO in read/write mode, even if
you don't intend to write it --- that will make the behavior like 8.0.Relevant 8.1 release note:
Read-only large object descriptors now obey MVCC snapshot semantics
When a large object is opened with INV_READ (and not INV_WRITE),
the data read from the descriptor will now reflect a "snapshot" of
the large object's state at the time of the transaction snapshot in
use by the query that called lo_open(). To obtain the old behavior
of always returning the latest committed data, include INV_WRITE in
the mode flags for lo_open().regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
**********************************************
Eric Davies, M.Sc.
Barrodale Computing Services Ltd.
Tel: (250) 472-4372 Fax: (250) 472-4373
Web: http://www.barrodale.com
Email: eric@barrodale.com
**********************************************
Mailing Address:
P.O. Box 3075 STN CSC
Victoria BC Canada V8W 3W2
Shipping Address:
Hut R, McKenzie Avenue
University of Victoria
Victoria BC Canada V8W 3W2
**********************************************