Skip site navigation (1) Skip section navigation (2)

MVCC and large objects

From: Scott Corscadden <scott(at)corscadden(dot)ca>
To: pgsql-hackers(at)postgresql(dot)org
Subject: MVCC and large objects
Date: 2012-10-02 13:41:50
Message-ID: C0B0DA1A-2C59-4D48-B4E4-CE3E539DDCAA@corscadden.ca (view raw or flat)
Thread:
Lists: pgsql-hackers
While I'm referring to the 8.4 release, it's a slightly more general question. We're moving away from using pg_largeobject as fast as we can, and while doing so I'm seeing interesting behaviour. I'd like to confirm that this is is expected, or perhaps I don't have tuning parameters set quite correctly. I believe we have standard autovacuum running.

* Table Foo has an oid "data" column, points to a valid BLOB. We modified this table to also have a "datafilepath" column.
* We wrote a throttleable "copier" process which walks rows, reads the BLOB data out to a file and then UPDATEs the "datafilepath" column with where it wrote it. We did not alter the BLOB data in any way. When asked for byte data, the higher level code will first return it from the datafilepath if it's there, and fall back on the lo otherwise. 

While the above was working away, we nearly missed the fact that the "public.pg_largeobject" table seemed to be growing commensurate with what we were exporting! As we were doing this as the primary disk was nearly out of space, it was fortunate I could pause this work. We were able to move the entire system and it's now continuing along, but my question:

Is this expected? I'm a little surprised. My theory is that MVCC seems to be including the pg_largeobject referenced as a part of the row, and even though we're not updating the BLOB at all, a snapshot is getting created. *Is this expected*?

Many thanks - single-link RTFM answers welcome, I have seen the MVCC through pictures, and I get it - just not how a BLOB fits into MVCC here.

./scc

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2012-10-02 14:19:17
Subject: Re: ALTER command reworks
Previous:From: Peter GeogheganDate: 2012-10-02 13:15:46
Subject: Re: Hash id in pg_stat_statements

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group