Giuseppe Sacco wrote:
> Another important fact is about large objects, if you happen to use
> them: their OID is not just unique to the database, but to the whole
> cluster. This means that when you move a database in a cluster from a
> production system to a database on a test cluster, you may get errors
> when same OID already exists in target cluster (even if it is used in a
> different database).
Well, I'm doing this frequently and it doesn't cause any error.
Demo with PG 9.1 (import a large object, dump-reload into another database,
verify that the copy shares the same OID):
$ createdb dbtest1
$ psql dbtest1
dbtest1=# \lo_import /path/to/picture.jpg
lo_import 2497765
dbtest1=# select distinct loid from pg_largeobject;
loid
---------
2497765
(1 row)
dbtest1=# \q
$ pg_dump dbtest1 >dbtest1.sql
$ createdb dbtest2
$ psql dbtest2
dbtest2=# \i dbtest1.sql
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
lo_create
-----------
2497765
(1 row)
ALTER LARGE OBJECT
SET
BEGIN
lo_open
---------
0
(1 row)
lowrite
---------
16384
(1 row)
lowrite
---------
5923
(1 row)
lo_close
----------
0
(1 row)
COMMIT
REVOKE
REVOKE
GRANT
GRANT
dbtest2=# select distinct loid from pg_largeobject;
loid
---------
2497765
(1 row)
So there's no error and this is the same OID in both databases. It gets
forced in the dump with lo_create().
It happens however, that when importing large objects with lo_import,
PostgreSQL returns OIDs that appear to be unique across databases.
Incidentally that allows to copy them easily between databases.
In the example above I'm importing into an empty db, so there's no potential
for conflict, but if the second database had previously imported large
objects too, they wouldn't conflict since they would have got unique OIDs.
So these OIDs tend to be unique but that does not imply that they must be
unique. Overall it's very convenient.
Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org