Re: Pg_upgrade and toast tables bug discovered - Mailing list pgsql-hackers

Based upon the dates the noted patch is not in 9.3.5; which was released a
couple of weeks previous to it being committed.

David J.


nyetter wrote
> I'm not sure it's fixed.  I am attempting a pg_upgrade from 9.2.8 to 9.3.5
> and it dies like so:
> 
> (...many relations restoring successfully snipped...)
> pg_restore: creating SEQUENCE address_address_id_seq
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 1410; 1259 17670 SEQUENCE
> address_address_id_seq javaprod
> pg_restore: [archiver (db)] could not execute query: ERROR:  could not
> create file "base/16414/17670": File exists
> 
> Inspecting a copy of the source cluster, OID 17670 does indeed correspond
> to address_address_id_seq, but inspecting the partially-upgraded cluster
> that OID is taken by pg_toast_202359_index.  Again conferring with a copy
> of the source (9.2.8) cluster, the relation corresponding to filenode
> 202359 does not have a toast table.
> 
> (I know pg-hackers isn't the right place to discuss admin issues, but this
> thread is the only evidence of this bug I can find.  If anyone can suggest
> a workaround I would be infinitely grateful.)
> 
> 
> On Thu, Aug 7, 2014 at 12:57 PM, Bruce Momjian <

> bruce@

> > wrote:
> 
>> On Tue, Aug  5, 2014 at 07:31:21PM -0400, Bruce Momjian wrote:
>> > On Thu, Jul 10, 2014 at 06:38:26PM -0400, Bruce Momjian wrote:
>> > > On Thu, Jul 10, 2014 at 06:17:14PM -0400, Bruce Momjian wrote:
>> > > > Well, we are going to need to call internal C functions, often
>> bypassing
>> > > > their typical call sites and the assumption about locking, etc.
>> Perhaps
>> > > > this could be done from a plpgsql function.  We could add and drop
>> a
>> > > > dummy column to force TOAST table creation --- we would then only
>> need a
>> > > > way to detect if a function _needs_ a TOAST table, which was
>> skipped
>> in
>> > > > binary upgrade mode previously.
>> > > >
>> > > > That might be a minimalistic approach.
>> > >
>> > > I have thought some more on this.  I thought I would need to open
>> > > pg_class in C and do complex backend stuff, but I now realize I can
>> do
>> > > it from libpq, and just call ALTER TABLE and I think that always
>> > > auto-checks if a TOAST table is needed.  All I have to do is query
>> > > pg_class from libpq, then construct ALTER TABLE commands for each
>> item,
>> > > and it will optionally create the TOAST table if needed.  I just have
>> to
>> > > use a no-op ALTER TABLE command, like SET STATISTICS.
>> >
>> > Attached is a completed patch which handles oid conflicts in pg_class
>> > and pg_type for TOAST tables that were not needed in the old cluster
>> but
>> > are needed in the new one.  I was able to recreate a failure case and
>> > this fixed it.
>> >
>> > The patch need to be backpatched because I am getting more-frequent bug
>> > reports from users using pg_upgrade to leave now-end-of-life'ed 8.4.
>> > There is not a good work-around for pg_upgrade failures without this
>> > fix, but at least pg_upgrade throws an error.
>>
>> Patch applied through 9.3, with an additional Assert check. 9.2 code was
>> different enough that there was too high a risk for backpatching.
>>
>> --
>>   Bruce Momjian  <

> bruce@

> >        http://momjian.us
>>   EnterpriseDB                             http://enterprisedb.com
>>
>>   + Everyone has their own god. +
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (

> pgsql-hackers@

> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Pg-upgrade-and-toast-tables-bug-discovered-tp5810447p5817656.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.