Subject: Re: [COMMITTERS] pgsql: Add pg_sequence system catalog
To: Stephen Frost <sfrost@snowman.net>
References: <E1cJKgv-0004eC-KE@gemulon.postgresql.org>
 <20170119160305.GS18360@tamriel.snowman.net>
Cc: pgsql-hackers@postgresql.org
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <1b722fdc-7f69-9033-2bed-34e318d61a74@2ndquadrant.com>
Date: Tue, 24 Jan 2017 10:08:21 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:45.0)
 Gecko/20100101 Thunderbird/45.6.0
MIME-Version: 1.0
In-Reply-To: <20170119160305.GS18360@tamriel.snowman.net>
Content-Type: multipart/mixed;
 boundary="------------2C647D954D59A34BF9262EFF"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------2C647D954D59A34BF9262EFF
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit

On 1/19/17 11:03 AM, Stephen Frost wrote:
> I'd suggest using our usual approach in pg_dump, which is matching based
> on the OID, like so:
> 
> WHERE c.oid = '%u'::oid
> 
> The OID is in: tbinfo->dobj.catId.oid
> 
> Also, you should move the selectSourceSchema() into the per-version
> branches and set it to 'pg_catalog' for PG10 and up, which would allow
> you to avoid having to qualify the table names, et al.

Does the attached patch look correct to you?

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--------------2C647D954D59A34BF9262EFF
Content-Type: text/x-patch;
 name="0001-pg_dump-Fix-some-schema-issues-when-dumping-sequence.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename*0="0001-pg_dump-Fix-some-schema-issues-when-dumping-sequence.pa";
 filename*1="tch"

From 96820dd460e9fe842a608d99e7a1da2f8b9ce67d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Tue, 24 Jan 2017 10:03:25 -0500
Subject: [PATCH] pg_dump: Fix some schema issues when dumping sequences

In the new code for selecting sequence data from pg_sequence, set the
schema to pg_catalog instead of the sequences own schema, and refer to
the sequence by OID instead of name, which was missing a schema
qualification.

Reported-by: Stephen Frost <sfrost@snowman.net>
---
 src/bin/pg_dump/pg_dump.c | 18 ++++++++++++------
 1 file changed, 12 insertions(+), 6 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index e3cca62bf7..b28b7e42d4 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15873,14 +15873,14 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 	PQExpBuffer delqry = createPQExpBuffer();
 	PQExpBuffer labelq = createPQExpBuffer();
 
-	/* Make sure we are in proper schema */
-	selectSourceSchema(fout, tbinfo->dobj.namespace->dobj.name);
-
 	snprintf(bufm, sizeof(bufm), INT64_FORMAT, PG_INT64_MIN);
 	snprintf(bufx, sizeof(bufx), INT64_FORMAT, PG_INT64_MAX);
 
 	if (fout->remoteVersion >= 100000)
 	{
+		/* Make sure we are in proper schema */
+		selectSourceSchema(fout, "pg_catalog");
+
 		appendPQExpBuffer(query,
 						  "SELECT seqstart, seqincrement, "
 						  "CASE WHEN seqincrement > 0 AND seqmax = %s THEN NULL "
@@ -15894,12 +15894,15 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 						  "seqcache, seqcycle "
 						  "FROM pg_class c "
 						  "JOIN pg_sequence s ON (s.seqrelid = c.oid) "
-						  "WHERE relname = ",
-						  bufx, bufm);
-		appendStringLiteralAH(query, tbinfo->dobj.name, fout);
+						  "WHERE c.oid = '%u'::oid",
+						  bufx, bufm,
+						  tbinfo->dobj.catId.oid);
 	}
 	else if (fout->remoteVersion >= 80400)
 	{
+		/* Make sure we are in proper schema */
+		selectSourceSchema(fout, tbinfo->dobj.namespace->dobj.name);
+
 		appendPQExpBuffer(query,
 						  "SELECT start_value, increment_by, "
 				   "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
@@ -15916,6 +15919,9 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
 	}
 	else
 	{
+		/* Make sure we are in proper schema */
+		selectSourceSchema(fout, tbinfo->dobj.namespace->dobj.name);
+
 		appendPQExpBuffer(query,
 						  "SELECT 0 AS start_value, increment_by, "
 				   "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
-- 
2.11.0


--------------2C647D954D59A34BF9262EFF
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

--------------2C647D954D59A34BF9262EFF--