Postgres Pro
Downloads
Home   >   mailing lists

Thread: lo function changed in PostgreSQL 8.1.1

lo function changed in PostgreSQL 8.1.1

From
"Premsun Choltanwanich"
Date:
Dear All,
 
I use '$libdir/lo' for manage my Large Object for PostgreSQL 8.0.4 . Now I try to up my PostgreSQL to new version as 8.1.1 but I think I got some error about lo (Large Object).
 
lo (Large Object) function that normally shown in function list now disappear.  I'm sure that I already check on Large Object box  when I install. I found some information on http://www.postgresql.org/docs/8.1/interactive/lo-interfaces.html#AEN26978 about lo function. PostgreSQL has many new lo function that I never use in version 8.0.4 and some function I have use is not found (lo_in , lo_out).
 
Could you please advise me how to manage lo (Large Object) in PostgreSQL 8.1.1? (Normally I use VB6 as develop tool.)
And I'm not sure about my old lo (Large Object) data. How can I restore it for use in PostgreSQL 8.1.1?
 
Thank you

Re: lo function changed in PostgreSQL 8.1.1

From
Richard Huxton
Date:
Premsun Choltanwanich wrote:
> Dear All,
>  
> I use '$libdir/lo' for manage my Large Object for PostgreSQL 8.0.4 . Now 
> I try to up my PostgreSQL to new version as 8.1.1 but I think I got some 
> error about lo (Large Object).
>  
> lo (Large Object) function that normally shown in function list now 
> disappear.  I'm sure that I already check on Large Object box  when I 
> install. I found some information on 
> http://www.postgresql.org/docs/8.1/interactive/lo-interfaces.html#AEN26978 about 
> lo function. PostgreSQL has many new lo function that I never use in 
> version 8.0.4 and some function I have use is not found (lo_in , lo_out).

I don't see any major difference between the functions listed for the 
8.0 and 8.1 manuals. Are you sure you're not talking about the 
contrib/lo type (see extract from the README.lo below)? If so, run the 
installer again and check you have ticked the box.

HTH

Overview

One of the problems with the JDBC driver (and this affects the ODBC driver
also), is that the specification assumes that references to BLOBS (Binary
Large OBjectS) are stored within a table, and if that entry is changed, the
associated BLOB is deleted from the database.

As PostgreSQL stands, this doesn't occur.  Large objects are treated as
objects in their own right; a table entry can reference a large object by
OID, but there can be multiple table entries referencing the same large
object OID, so the system doesn't delete the large object just because you
change or remove one such entry.

Now this is fine for new PostgreSQL-specific applications, but existing ones
using JDBC or ODBC won't delete the objects, resulting in orphaning - 
objects
that are not referenced by anything, and simply occupy disk space.


The Fix

I've fixed this by creating a new data type 'lo', some support 
functions, and
a Trigger which handles the orphaning problem.  The trigger essentially just
does a 'lo_unlink' whenever you delete or modify a value referencing a large
object.  When you use this trigger, you are assuming that there is only one
database reference to any large object that is referenced in a
trigger-controlled column!

The 'lo' type was created because we needed to differentiate between plain
OIDs and Large Objects. Currently the JDBC driver handles this dilemma 
easily,
but (after talking to Byron), the ODBC driver needed a unique type. They had
created an 'lo' type, but not the solution to orphaning.

You don't actually have to use the 'lo' type to use the trigger, but it 
may be
convenient to use it to keep track of which columns in your database 
represent
large objects that you are managing with the trigger.

--   Richard Huxton  Archonet Ltd

Re: lo function changed in PostgreSQL 8.1.1

From
"Premsun Choltanwanich"
Date:
Dear Richard,
 
I think I use contrib/lo type  for manage lo. What is wrong?
 
Now I have no idea for manage lo on PostgreSQL 8.1.1, I think I need a time to learn.
 
Could you give me suggestion?
 
Regards,
 
Premsun
 

>>> Richard Huxton <dev@archonet.com> 12/13/2005 19:22:01 >>>
Premsun Choltanwanich wrote:
> Dear All,
> 
> I use '$libdir/lo' for manage my Large Object for PostgreSQL 8.0.4 . Now
> I try to up my PostgreSQL to new version as 8.1.1 but I think I got some
> error about lo (Large Object).
> 
> lo (Large Object) function that normally shown in function list now
> disappear.  I'm sure that I already check on Large Object box  when I
> install. I found some information on
> http://www.postgresql.org/docs/8.1/interactive/lo-interfaces.html#AEN26978 about
> lo function. PostgreSQL has many new lo function that I never use in
> version 8.0.4 and some function I have use is not found (lo_in , lo_out).

I don't see any major difference between the functions listed for the
8.0 and 8.1 manuals. Are you sure you're not talking about the
contrib/lo type (see extract from the README.lo below)? If so, run the
installer again and check you have ticked the box.

HTH

Overview

One of the problems with the JDBC driver (and this affects the ODBC driver
also), is that the specification assumes that references to BLOBS (Binary
Large OBjectS) are stored within a table, and if that entry is changed, the
associated BLOB is deleted from the database.

As PostgreSQL stands, this doesn't occur.  Large objects are treated as
objects in their own right; a table entry can reference a large object by
OID, but there can be multiple table entries referencing the same large
object OID, so the system doesn't delete the large object just because you
change or remove one such entry.

Now this is fine for new PostgreSQL-specific applications, but existing ones
using JDBC or ODBC won't delete the objects, resulting in orphaning -
objects
that are not referenced by anything, and simply occupy disk space.


The Fix

I've fixed this by creating a new data type 'lo', some support
functions, and
a Trigger which handles the orphaning problem.  The trigger essentially just
does a 'lo_unlink' whenever you delete or modify a value referencing a large
object.  When you use this trigger, you are assuming that there is only one
database reference to any large object that is referenced in a
trigger-controlled column!

The 'lo' type was created because we needed to differentiate between plain
OIDs and Large Objects. Currently the JDBC driver handles this dilemma
easily,
but (after talking to Byron), the ODBC driver needed a unique type. They had
created an 'lo' type, but not the solution to orphaning.

You don't actually have to use the 'lo' type to use the trigger, but it
may be
convenient to use it to keep track of which columns in your database
represent
large objects that you are managing with the trigger.

--
   Richard Huxton
   Archonet Ltd

Re: lo function changed in PostgreSQL 8.1.1

From
Richard Huxton
Date:
Premsun Choltanwanich wrote:
> Dear Richard,
>  
> I think I use contrib/lo type  for manage lo. What is wrong?
>  
> Now I have no idea for manage lo on PostgreSQL 8.1.1, I think I need a time to learn.
>  
> Could you give me suggestion?

Go back and check you have ticked the option in the installer. I don't 
think it's been removed. The README.lo I quoted in my last email was 
from 8.1 source.

--   Richard Huxton  Archonet Ltd

Re: lo function changed in PostgreSQL 8.1.1

From
Tom Lane
Date:
"Premsun Choltanwanich" <Premsun@nsasia.co.th> writes:
> I think I use contrib/lo type  for manage lo. What is wrong?

You need to use the 8.1 version of contrib/lo, not any previous version.

You might find that the easiest way to manage this is to create an empty
database, load the 8.1 contrib/lo definitions by running lo.sql, then
restoring from your pg_dump backup.  You'll see some complaints about
duplicate function definitions but these can be ignored.
        regards, tom lane

Re: lo function changed in PostgreSQL 8.1.1

From
"Premsun Choltanwanich"
Date:
Thank you Tom. I will test it and reply the result as information for other. But it may a week for that cause many work in end of year.

>>> Tom Lane <tgl@sss.pgh.pa.us> 12/14/2005 22:06:33 >>>
"Premsun Choltanwanich" <Premsun@nsasia.co.th> writes:
> I think I use contrib/lo type  for manage lo. What is wrong?

You need to use the 8.1 version of contrib/lo, not any previous version.

You might find that the easiest way to manage this is to create an empty
database, load the 8.1 contrib/lo definitions by running lo.sql, then
restoring from your pg_dump backup.  You'll see some complaints about
duplicate function definitions but these can be ignored.

regards, tom lane

Re: lo function changed in PostgreSQL 8.1.1

From
"Premsun Choltanwanich"
Date:
From contrib/lo I found that it has something  difference between old and new version of PostgreSQL.  And I'm sure that I already tick on Large Object (lo) option when I install.
 
How can I manage on difference function?
 
 
 
:::New Version:::

DOMAIN lo AS pg_catalog.oid;
 
FUNCTION lo_oid(lo) RETURNS pg_catalog.oid AS 'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT IMMUTABLE;
 
FUNCTION lo_manage() RETURNS pg_catalog.trigger AS '$libdir/lo' LANGUAGE C;
 
 
 
 
:::Old Version:::
 
FUNCTION lo_in(cstring) RETURNS lo AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT;
 
FUNCTION lo_out(lo) RETURNS cstring AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT;
 
TYPE lo (  INTERNALLENGTH = 4, EXTERNALLENGTH = variable, INPUT = lo_in, OUTPUT = lo_out);
 
FUNCTION lo_oid(lo) RETURNS oid AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT;
 
FUNCTION oid(lo) RETURNS oid AS '$libdir/lo', 'lo_oid' LANGUAGE C IMMUTABLE STRICT;
 
CAST (lo as oid) WITH FUNCTION oid(lo) AS IMPLICIT;
 
FUNCTION lo(oid) RETURNS lo AS '$libdir/lo' LANGUAGE C IMMUTABLE STRICT;
 
CAST (oid as lo) WITH FUNCTION lo(oid) AS IMPLICIT;
 
FUNCTION lo_manage() RETURNS trigger AS '$libdir/lo' LANGUAGE C;


>>> Tom Lane <tgl@sss.pgh.pa.us> 12/14/2005 22:06:33 >>>
"Premsun Choltanwanich" <Premsun@nsasia.co.th> writes:
> I think I use contrib/lo type  for manage lo. What is wrong?

You need to use the 8.1 version of contrib/lo, not any previous version.

You might find that the easiest way to manage this is to create an empty
database, load the 8.1 contrib/lo definitions by running lo.sql, then
restoring from your pg_dump backup.  You'll see some complaints about
duplicate function definitions but these can be ignored.

regards, tom lane