Обсуждение: 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?

               http://archives.postgresql.org/

**********************************************
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
**********************************************