From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
        pgsql-hackers@postgresql.org
Subject: Re: Transactional sequence stuff breaks pg_upgrade
In-reply-to: <20170612221118.3nkmgbtjvv6eclut@alap3.anarazel.de>
References: <19785.1497215827@sss.pgh.pa.us> <9761.1497225808@sss.pgh.pa.us>
 <20170612210221.vz2u373tcwu3g624@alap3.anarazel.de>
 <23291.1497302014@sss.pgh.pa.us>
 <20170612212517.j3xe6am7hlztl6fw@alap3.anarazel.de>
 <11213.1497303337@sss.pgh.pa.us>
 <20170612221118.3nkmgbtjvv6eclut@alap3.anarazel.de>
Comments: In-reply-to Andres Freund <andres@anarazel.de>
	message dated "Mon, 12 Jun 2017 15:11:18 -0700"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <16900.1497306601.0@sss.pgh.pa.us>
Date: Mon, 12 Jun 2017 18:31:04 -0400
Message-ID: <16932.1497306664@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <16900.1497306601.1@sss.pgh.pa.us>

Andres Freund <andres@anarazel.de> writes:
> On 2017-06-12 17:35:37 -0400, Tom Lane wrote:
>> I thought about trying to insert an assert in GetNewOidWithIndex to
>> ensure that no type OIDs are selected automatically during upgrade,
>> but it seemed a bit too complicated.  Might be a good idea to figure
>> it out though, unless you have an idea for removing that requirement.

> I think the only type oids assigned during pg_upgrade are the oids for
> toast type types, right?

Perhaps that was a problem once, but it isn't now; see 
binary_upgrade_set_next_toast_pg_type_oid().

In fact, we get through the pg_upgrade regression test with the attached
patch, and I really think we ought to commit it.

Having said that, I wouldn't mind trying to reduce the catalog overhead
for toast tables in v11 or beyond.

> a) Do not create a corresponding composite type for toast tables. Not
>    super pretty, but I doubt it'd be a huge issue.

I suspect there are places that assume all tables have type OIDs.

> b) Use *one* composite type for all of the toast tables.  That'd not be
>    entirely trivial because of pg_type.typrelid.

That might work, with some klugery.  Peter might have some insight about
this --- I'm not sure whether the CREATE TABLE OF TYPE syntax shares
a type OID across all the tables.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff;
	name="check-upgrade-type-and-relfilenodes-all-assigned.patch";
	charset="us-ascii"
Content-ID: <16900.1497306601.2@sss.pgh.pa.us>
Content-Description: check-upgrade-type-and-relfilenodes-all-assigned.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index 11ee536..92d943c 100644
*** a/src/backend/catalog/catalog.c
--- b/src/backend/catalog/catalog.c
***************
*** 38,43 ****
--- 38,44 ----
  #include "catalog/pg_shseclabel.h"
  #include "catalog/pg_subscription.h"
  #include "catalog/pg_tablespace.h"
+ #include "catalog/pg_type.h"
  #include "catalog/toasting.h"
  #include "miscadmin.h"
  #include "storage/fd.h"
*************** GetNewOidWithIndex(Relation relation, Oi
*** 340,345 ****
--- 341,354 ----
  	ScanKeyData key;
  	bool		collides;
=20=20
+ 	/*
+ 	 * We should never be asked to generate a new pg_type OID during
+ 	 * pg_upgrade; doing so would risk collisions with the OIDs it wants to
+ 	 * assign.  Hitting this assert means there's some path where we failed =
to
+ 	 * ensure that a type OID is determined by commands in the dump script.
+ 	 */
+ 	Assert(!IsBinaryUpgrade || RelationGetRelid(relation) !=3D TypeRelationI=
d);
+=20
  	InitDirtySnapshot(SnapshotDirty);
=20=20
  	/* Generate new OIDs until we find one not in the table */
*************** GetNewRelFileNode(Oid reltablespace, Rel
*** 391,396 ****
--- 400,412 ----
  	bool		collides;
  	BackendId	backend;
=20=20
+ 	/*
+ 	 * If we ever get here during pg_upgrade, there's something wrong; all
+ 	 * relfilenode assignments during a binary-upgrade run should be
+ 	 * determined by commands in the dump script.
+ 	 */
+ 	Assert(!IsBinaryUpgrade);
+=20
  	switch (relpersistence)
  	{
  		case RELPERSISTENCE_TEMP:

------- =_aaaaaaaaaa0
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

------- =_aaaaaaaaaa0--