Thread: [HACKERS] Bug when dumping "empty" operator classes
While hacking on pg_upgrade in downstream Greenplum I ran into an error which
seems like an old, and obscure, bug in pg_dump (unrelated to pg_upgrade).
pg_dump generates incorrect SQL for an operator class which has no operators or
procedures, and which has the same column and storage types. While it’s
arguably a pretty uninteresting operator class, it is allowed since we don’t
validate that all required operators/procedures are present.
The alter_generic test suite contains examples of opclasses which trigger this
behavior. The below operator class:
CREATE OPERATOR CLASS alt_opc1 FOR TYPE uuid USING hash AS STORAGE uuid;
..is dumped like this (after an ALTER .. RENAME, thus the new name):
CREATE OPERATOR CLASS alt_opc3
FOR TYPE uuid USING hash FAMILY alt_opc1 AS
;
The reason why this hasn’t been caught by the PostgreSQL pg_upgrade test is
because the schema in which the operator classes are created is dropped at the
end of the suite, removing the DROP cause pg_upgrade to fail with:
pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near ";"
LINE 3: ;
^
Command was: CREATE OPERATOR CLASS "alt_opc2"
FOR TYPE "macaddr" USING "hash" FAMILY "alt_opc2" AS
;
The attached patch adds a belts-and-suspenders check in dumpOpclass() which
appends the STORAGE clause in case nothing had been added. While adding
storage when it’s identical to the column type goes against the documentation,
it’s valid SQL and won’t break restore (and is handled by DefineOpClass()).
Validating the options fully would of course ensure that the dump always has
enough to render, but it also adds a lot of complexity (a quick look in the
archives doesn’t turn up many reports of it being a big problem). The DROP in
alter_generic is also removed to exercise the code path, being able to
pg_upgrade what is executed in regression seem like a good idea.
cheers ./daniel
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Daniel Gustafsson <daniel@yesql.se> writes:
> While hacking on pg_upgrade in downstream Greenplum I ran into an error which
> seems like an old, and obscure, bug in pg_dump (unrelated to pg_upgrade).
> pg_dump generates incorrect SQL for an operator class which has no operators or
> procedures, and which has the same column and storage types.
Good catch.
> The attached patch adds a belts-and-suspenders check in dumpOpclass() which
> appends the STORAGE clause in case nothing had been added.
Seems reasonable (the comment could use some wordsmithing maybe) ...
> ... The DROP in
> alter_generic is also removed to exercise the code path, being able to
> pg_upgrade what is executed in regression seem like a good idea.
... but that's a nonstarter. We can't have the regression tests leaving
global objects (users) lying around.
I'll commit and back-patch this without a test case. Possibly Frost will
be excited enough about it to add something to the pg_dump TAP tests,
but those tests are too opaque for me to want to do so.
regards, tom lane
> On 26 May 2017, at 17:08, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Daniel Gustafsson <daniel@yesql.se> writes: >> While hacking on pg_upgrade in downstream Greenplum I ran into an error which >> seems like an old, and obscure, bug in pg_dump (unrelated to pg_upgrade). >> pg_dump generates incorrect SQL for an operator class which has no operators or >> procedures, and which has the same column and storage types. > > Good catch. > >> The attached patch adds a belts-and-suspenders check in dumpOpclass() which >> appends the STORAGE clause in case nothing had been added. > > Seems reasonable (the comment could use some wordsmithing maybe) ... > >> ... The DROP in >> alter_generic is also removed to exercise the code path, being able to >> pg_upgrade what is executed in regression seem like a good idea. > > ... but that's a nonstarter. We can't have the regression tests leaving > global objects (users) lying around. Fair enough, > I'll commit and back-patch this without a test case. Possibly Frost will > be excited enough about it to add something to the pg_dump TAP tests, > but those tests are too opaque for me to want to do so. Thanks! cheers ./daniel
On Fri, May 26, 2017 at 8:14 AM, Daniel Gustafsson <daniel@yesql.se> wrote: >> On 26 May 2017, at 17:08, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I'll commit and back-patch this without a test case. Possibly Frost will >> be excited enough about it to add something to the pg_dump TAP tests, >> but those tests are too opaque for me to want to do so. > > Thanks! For the TAP tests I think that you are looking for something like the attached. Stephen, perhaps you could consider including this test in the suite? -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Greetings, * Michael Paquier (michael.paquier@gmail.com) wrote: > On Fri, May 26, 2017 at 8:14 AM, Daniel Gustafsson <daniel@yesql.se> wrote: > >> On 26 May 2017, at 17:08, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> I'll commit and back-patch this without a test case. Possibly Frost will > >> be excited enough about it to add something to the pg_dump TAP tests, > >> but those tests are too opaque for me to want to do so. > > For the TAP tests I think that you are looking for something like the > attached. Stephen, perhaps you could consider including this test in > the suite? Another blast from the past. I've updated this to the new pg_dump test-suite system but otherwise it's basically the same test. Barring concerns, I'll plan to push this later this weekend. Thanks! Stephen
Attachment
On Fri, Dec 07, 2018 at 08:11:42PM -0500, Stephen Frost wrote: > Another blast from the past. I've updated this to the new pg_dump > test-suite system but otherwise it's basically the same test. > > Barring concerns, I'll plan to push this later this weekend. Thanks Stephen for including this one. No objections from me, that looks good. -- Michael
Attachment
Greetings, * Stephen Frost (sfrost@snowman.net) wrote: > * Michael Paquier (michael.paquier@gmail.com) wrote: > > On Fri, May 26, 2017 at 8:14 AM, Daniel Gustafsson <daniel@yesql.se> wrote: > > >> On 26 May 2017, at 17:08, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > >> I'll commit and back-patch this without a test case. Possibly Frost will > > >> be excited enough about it to add something to the pg_dump TAP tests, > > >> but those tests are too opaque for me to want to do so. > > > > For the TAP tests I think that you are looking for something like the > > attached. Stephen, perhaps you could consider including this test in > > the suite? > > Another blast from the past. I've updated this to the new pg_dump > test-suite system but otherwise it's basically the same test. > > Barring concerns, I'll plan to push this later this weekend. This has now been pushed. Thanks! Stephen
Attachment
On Mon, Dec 10, 2018 at 01:20:18PM -0500, Stephen Frost wrote: > This has now been pushed. Thanks for taking care of it, Stephen! -- Michael