Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread Noah Yetter
The 9.3.5 release notes contain... - Fix pg_upgrade for cases where the new server creates a TOAST table but the old version did not (Bruce Momjian) This rare situation would manifest as relation OID mismatch errors. ...which I thought was this bug, hence my confusion. If anyone

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread Bruce Momjian
On Thu, Sep 4, 2014 at 11:37:27AM -0600, Noah Yetter wrote: The 9.3.5 release notes contain... • Fix pg_upgrade for cases where the new server creates a TOAST table but the old version did not (Bruce Momjian) This rare situation would manifest as relation OID mismatch 

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread David G Johnston
On Thu, Sep 4, 2014 at 2:39 PM, Bruce Momjian [via PostgreSQL] ml-node+s1045698n5817828...@n5.nabble.com wrote: On Thu, Sep 4, 2014 at 11:37:27AM -0600, Noah Yetter wrote: The 9.3.5 release notes contain... • Fix pg_upgrade for cases where the new server creates a TOAST table but

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread Noah Yetter
Isn't that exactly what the release note says? where the new server creates a TOAST table but the old version did not vs. where the new cluster needs a TOAST table that the old cluster didn't At any rate, I've additionally observed that the relation which is blowing up pg_upgrade is a VIEW in the

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread Bruce Momjian
On Thu, Sep 4, 2014 at 01:14:01PM -0600, Noah Yetter wrote: Isn't that exactly what the release note says?  where the new server creates a TOAST table but the old version did not  vs.  where the new cluster needs a TOAST table that the old cluster didn't Sorry, yes, I got confused. We have

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread Robert Haas
On Thu, Sep 4, 2014 at 3:35 PM, Bruce Momjian br...@momjian.us wrote: At any rate, I've additionally observed that the relation which is blowing up pg_upgrade is a VIEW in the source cluster but gets created as a TABLE in the upgraded cluster, which may better explain why it had no toast table

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread Bruce Momjian
On Thu, Sep 4, 2014 at 03:48:17PM -0400, Robert Haas wrote: On Thu, Sep 4, 2014 at 3:35 PM, Bruce Momjian br...@momjian.us wrote: At any rate, I've additionally observed that the relation which is blowing up pg_upgrade is a VIEW in the source cluster but gets created as a TABLE in the

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread Noah Yetter
Doing the upgrade with an installation built from REL9_3_STABLE at commit 52eed3d4267faf671dae0450d99982cb9ba1ac52 was successful. The view that I saw get re-created as a table doesn't have any circular references, or indeed any references to other views, nor do any other views reference it. But

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-04 Thread Bruce Momjian
On Thu, Sep 4, 2014 at 03:24:05PM -0600, Noah Yetter wrote: Doing the upgrade with an installation built from REL9_3_STABLE at commit 52eed3d4267faf671dae0450d99982cb9ba1ac52 was successful. The view that I saw get re-created as a table doesn't have any circular references, or indeed any

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-03 Thread Noah Yetter
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

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-03 Thread David G Johnston
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

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-09-03 Thread Bruce Momjian
On Wed, Sep 3, 2014 at 05:12:30PM -0600, Noah Yetter 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)]

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-08-07 Thread Bruce Momjian
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

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-08-05 Thread Bruce Momjian
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

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-16 Thread Bruce Momjian
On Mon, Jul 14, 2014 at 09:40:33PM +0200, Andres Freund wrote: On 2014-07-11 09:55:34 -0400, Bruce Momjian wrote: On Fri, Jul 11, 2014 at 09:48:06AM -0400, Bruce Momjian wrote: Uh, why does this need to be in ALTER TABLE? Can't this be part of table creation done by pg_dump? Uh,

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-16 Thread Andres Freund
On 2014-07-16 10:19:05 -0400, Bruce Momjian wrote: What would be nice is if I could mark just the tables that will need toast tables created in that later phase (those tables that didn't have a toast table in the old cluster, but need one in the new cluster). However, I can't see

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-16 Thread Robert Haas
On Wed, Jul 16, 2014 at 10:19 AM, Bruce Momjian br...@momjian.us wrote: On Mon, Jul 14, 2014 at 09:40:33PM +0200, Andres Freund wrote: On 2014-07-11 09:55:34 -0400, Bruce Momjian wrote: On Fri, Jul 11, 2014 at 09:48:06AM -0400, Bruce Momjian wrote: Uh, why does this need to be in ALTER

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-14 Thread Robert Haas
On Fri, Jul 11, 2014 at 9:55 AM, Bruce Momjian br...@momjian.us wrote: On Fri, Jul 11, 2014 at 09:48:06AM -0400, Bruce Momjian wrote: Uh, why does this need to be in ALTER TABLE? Can't this be part of table creation done by pg_dump? Uh, I think you need to read the thread. We have to

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-14 Thread Fabrízio de Royes Mello
On Mon, Jul 14, 2014 at 12:26 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jul 11, 2014 at 9:55 AM, Bruce Momjian br...@momjian.us wrote: On Fri, Jul 11, 2014 at 09:48:06AM -0400, Bruce Momjian wrote: Uh, why does this need to be in ALTER TABLE? Can't this be part of table

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-14 Thread Bruce Momjian
On Mon, Jul 14, 2014 at 11:26:19AM -0400, Robert Haas wrote: On Fri, Jul 11, 2014 at 9:55 AM, Bruce Momjian br...@momjian.us wrote: On Fri, Jul 11, 2014 at 09:48:06AM -0400, Bruce Momjian wrote: Uh, why does this need to be in ALTER TABLE? Can't this be part of table creation done by

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-14 Thread Andres Freund
On 2014-07-11 09:55:34 -0400, Bruce Momjian wrote: On Fri, Jul 11, 2014 at 09:48:06AM -0400, Bruce Momjian wrote: Uh, why does this need to be in ALTER TABLE? Can't this be part of table creation done by pg_dump? Uh, I think you need to read the thread. We have to delay the toast

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-11 Thread Bruce Momjian
On Fri, Jul 11, 2014 at 12:18:40AM -0400, Alvaro Herrera wrote: Bruce Momjian wrote: On Thu, Jul 10, 2014 at 06:38:26PM -0400, Bruce Momjian wrote: 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

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-11 Thread Bruce Momjian
On Fri, Jul 11, 2014 at 09:48:06AM -0400, Bruce Momjian wrote: Uh, why does this need to be in ALTER TABLE? Can't this be part of table creation done by pg_dump? Uh, I think you need to read the thread. We have to delay the toast creation part so we don't use an oid that will later be

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-10 Thread Robert Haas
On Wed, Jul 9, 2014 at 12:09 PM, Bruce Momjian br...@momjian.us wrote: To me, that sounds vastly more complicated and error-prone than forcing the TOAST tables to be added in a second pass as Andres suggested. But I just work here. Agreed. I am now thinking we could harness the code that

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-10 Thread Bruce Momjian
On Thu, Jul 10, 2014 at 10:46:30AM -0400, Robert Haas wrote: Agreed. I am now thinking we could harness the code that already exists to optionally add a TOAST table as part of ALTER TABLE ADD COLUMN. We would just need an entry point to call it from pg_upgrade, either via an SQL command

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-10 Thread Andres Freund
On 2014-07-10 16:33:40 -0400, Bruce Momjian wrote: On Thu, Jul 10, 2014 at 10:46:30AM -0400, Robert Haas wrote: Agreed. I am now thinking we could harness the code that already exists to optionally add a TOAST table as part of ALTER TABLE ADD COLUMN. We would just need an entry point

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-10 Thread Bruce Momjian
On Thu, Jul 10, 2014 at 11:11:19PM +0200, Andres Freund wrote: On 2014-07-10 16:33:40 -0400, Bruce Momjian wrote: On Thu, Jul 10, 2014 at 10:46:30AM -0400, Robert Haas wrote: Agreed. I am now thinking we could harness the code that already exists to optionally add a TOAST table as part

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-10 Thread Bruce Momjian
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

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-10 Thread Bruce Momjian
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

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-10 Thread Alvaro Herrera
Bruce Momjian wrote: On Thu, Jul 10, 2014 at 06:38:26PM -0400, Bruce Momjian wrote: 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

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-09 Thread Robert Haas
Uh, I guess we could write some code that iterates over all tables and finds the tables that should have TOAST tables, but don't (because binary-upgrade backend mode suppressed their creation), and adds them. However, that would be a lot of code and might be risky to backpatch. The error is

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-09 Thread Bruce Momjian
On Wed, Jul 9, 2014 at 10:13:17AM -0400, Robert Haas wrote: Uh, I guess we could write some code that iterates over all tables and finds the tables that should have TOAST tables, but don't (because binary-upgrade backend mode suppressed their creation), and adds them. However, that

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-07 Thread Robert Haas
On Fri, Jul 4, 2014 at 11:12 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Jul 4, 2014 at 12:01:37AM -0400, Bruce Momjian wrote: The most robust, but not trivial, approach seems to be to prevent toast table creation if there wasn't a set_next_toast_pg_class_oid(). Then, after all

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-07 Thread Bruce Momjian
On Fri, Jul 4, 2014 at 11:12:58PM -0400, Bruce Momjian wrote: On Fri, Jul 4, 2014 at 12:01:37AM -0400, Bruce Momjian wrote: The most robust, but not trivial, approach seems to be to prevent toast table creation if there wasn't a set_next_toast_pg_class_oid(). Then, after all relations

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-07 Thread Bruce Momjian
On Mon, Jul 7, 2014 at 11:24:51AM -0400, Robert Haas wrote: As far as the reusing of oids, we don't set the oid counter until after the restore, so any new unmatched toast table would given a very low oid. Since we restore in oid order, for an oid to be assigned that was used in the old

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-07 Thread Bruce Momjian
On Mon, Jul 7, 2014 at 01:44:59PM -0400, Bruce Momjian wrote: On Mon, Jul 7, 2014 at 11:24:51AM -0400, Robert Haas wrote: As far as the reusing of oids, we don't set the oid counter until after the restore, so any new unmatched toast table would given a very low oid. Since we restore

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-04 Thread Bruce Momjian
On Fri, Jul 4, 2014 at 12:01:37AM -0400, Bruce Momjian wrote: The most robust, but not trivial, approach seems to be to prevent toast table creation if there wasn't a set_next_toast_pg_class_oid(). Then, after all relations are created, iterate over all pg_class entries that possibly need

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-03 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: I have had trouble getting a table schema that is causing problems, but received a report via EDB support recently that had a simple schema (anonymized): ... needs_toast_table() computes the length of this table as 2024 bytes in 9.0, and 2064 bytes on

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-03 Thread Andres Freund
On 2014-07-03 17:09:41 -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I have had trouble getting a table schema that is causing problems, but received a report via EDB support recently that had a simple schema (anonymized): ... needs_toast_table() computes the length of

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-03 Thread Bruce Momjian
On Thu, Jul 3, 2014 at 05:09:41PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I have had trouble getting a table schema that is causing problems, but received a report via EDB support recently that had a simple schema (anonymized): ... needs_toast_table() computes the

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

2014-07-03 Thread Bruce Momjian
On Thu, Jul 3, 2014 at 11:55:40PM +0200, Andres Freund wrote: I don't think it's just that simple unfortunately. If pg_class entries get created that didn't exist on the old server there's a chance for oid conflicts. Consider SELECT