Date: Thu, 19 Jan 2017 11:03:05 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [COMMITTERS] pgsql: Add pg_sequence system catalog
Message-ID: <20170119160305.GS18360@tamriel.snowman.net>
References: <E1cJKgv-0004eC-KE@gemulon.postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="5wAPvwbAcQJrmmvx"
Content-Disposition: inline
In-Reply-To: <E1cJKgv-0004eC-KE@gemulon.postgresql.org>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--5wAPvwbAcQJrmmvx
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Peter,

* Peter Eisentraut (peter_e@gmx.net) wrote:
> Add pg_sequence system catalog

The query this added to dumpSequence() seems to think that sequence
names are unique across databases:

appendPQExpBuffer(query,
                  "SELECT seqstart, seqincrement, "
                  "CASE WHEN seqincrement > 0 AND seqmax = %s THEN NULL "
                  "     WHEN seqincrement < 0 AND seqmax = -1 THEN NULL "
                  "     ELSE seqmax "
                  "END AS seqmax, "
                  "CASE WHEN seqincrement > 0 AND seqmin = 1 THEN NULL "
                  "     WHEN seqincrement < 0 AND seqmin = %s THEN NULL "
                  "     ELSE seqmin "
                  "END AS seqmin, "
                  "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);

Note that tbinfo->dobj.name contains just the name, and trying to match
based on just 'relname' isn't going to work since sequences with the
same name can exist in difference schemas.

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.  As is, this
query will also fail if a user has created a 'pg_class' or
'pg_sequence' table in their schema.

Thanks!

Stephen

--5wAPvwbAcQJrmmvx
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJYgOM5AAoJEO1sijiDR2RV74IQALZ3Fa/VqgWKb0kasn5Dvvmk
+6ZBbPRUfl7gqdeXXW1kCKaYQnYWo0wydXaCnG45Zay2LtFihaPqOYqdCzoMt9KW
9vWcHa0VA1Xh75XyFHhV70Iit5SEsT+envotq3ZiDZsZZxXEYEwJQ7cSN4fw8eU7
lRfxsgvDFzWIHRkzlNivbNJvlfeTFDS86S3mDNR8NYpwog0ofIKFXFiQQoTQV/Zr
cFEgAjEYnrB/+8v4vPt/2Fuzh3r/NiBwEtrWMqi2Er9RQ4RjXIq4mknLmRLfMHlq
U1bl7Gfr8aAiou+RohfZElt6Cz0h5ZfiwYpAINfG3bDXjwe0na1AyZ3kYqSJ0X1d
LjZLnjH6NddzlqKIZu1DLxTFrAUqp30tyjJqXp02jE4+rq3EN1JzqN2N1KPq3gax
7lcH+tiMeKzmCrnJpc0sH5RVNWRZAgiwGBt8w+dbsGUBPmb8W6JtQh02VT8pqkxm
rKlIWJnGsxYHfHV7FLM2kDc/G4h4x6FyDxtipAJhX7Y4lsBi4TCO++YM6qPPUrbd
dRcRuwoGSFKd8Lwm4cXz+oAP99pxeh1GB0D5iNj/Ss6CiGzh4s6EFQ5ep8Vy9l3/
p7S/U73k3qF0KecqEKe81Z6iNJveSb9wiw4T3hEx1ixzfFPMC0m8G78v5+i2Ssor
I8cLpvSFvI+2nbi3ebFF
=vW7C
-----END PGP SIGNATURE-----

--5wAPvwbAcQJrmmvx--