Обсуждение: Re: Postgresql table reloading

On Fri, 4 May 2001, David Wall wrote:

> Sorry to bother you personally, but I read your "Updating columns in PostgreSQL tables effectively" page and had a
question.
>
> Will the temporary table scheme work if the table contains blobs (identified by OIDs)?  In my case, I don't need to
preservethe actual OID number (it's not a key), but I do need the blob that's behind the OID to be copied.  Will the
INSERT..SELECTFROM syntax carry the blobs over? 

(cross-posted to pgsql-general, so others can find this in the archives)

In the table, you're storing the OID of the large object itself, so if you
copy all the contents of the table, so far as I can see, yes, you'd still
have the reference to your large object.

(The table row of your current table has an OID, and when you create the
temporary table--which becomes the new table--it will have a different
OID, but that shouldn't matter, since all you care about is the LO oid.

However, I'm not a BLOB expert, and haven't used them in any real systems.

Do try this out on a test table before doing it for real.

HTH,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

> In the table, you're storing the OID of the large object itself, so if you
> copy all the contents of the table, so far as I can see, yes, you'd still
> have the reference to your large object.

Thanks.  I did see this in a quick test, but I wasn't sure if the data would
be removed when I dropped the original table (does the DB keep a reference
count for rows pointing to the blob?).  Yes, I'll give it a test first!

Thanks,
David