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
Description: Binary data
-- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers