Обсуждение: BUG #5857: pg_restore --clean dropping type too soon

The following bug has been logged online:

Bug reference:      5857
Logged by:          Stuart Bishop
Email address:      stuart@stuartbishop.net
PostgreSQL version: 8.4.6
Operating system:   Ubuntu 10.10
Description:        pg_restore --clean dropping type too soon
Details:

"pg_restore --clean" appears to have an ordering problem, where a custom
type is being dropped before some functions that use that custom type as a
parameter, which fails.


$ psql -d foo -f /usr/share/postgresql/8.4/contrib/debversion.sql >
/dev/null
psql:/usr/share/postgresql/8.4/contrib/debversion.sql:28: NOTICE:  return
type debversion is only a shell
psql:/usr/share/postgresql/8.4/contrib/debversion.sql:34: NOTICE:  argument
type debversion is only a shell
psql:/usr/share/postgresql/8.4/contrib/debversion.sql:40: NOTICE:  return
type debversion is only a shell
psql:/usr/share/postgresql/8.4/contrib/debversion.sql:46: NOTICE:  argument
type debversion is only a shell

$ pg_dump --format=c foo | pg_restore --clean | grep debversion
[...]
DROP FUNCTION public.debversion(character);
DROP TYPE public.debversion CASCADE;
DROP FUNCTION public.debversionsend(debversion);
[...]



The relevant part of debversion.sql seems to be:

CREATE TYPE debversion;

CREATE OR REPLACE FUNCTION debversionin(cstring)
  RETURNS debversion
  AS 'textin'
  LANGUAGE 'internal'
  IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION debversionout(debversion)
  RETURNS cstring
  AS 'textout'
  LANGUAGE 'internal'
  IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION debversionrecv(internal)
  RETURNS debversion
  AS 'textrecv'
  LANGUAGE 'internal'
  STABLE STRICT;

CREATE OR REPLACE FUNCTION debversionsend(debversion)
  RETURNS bytea
  AS 'textsend'
  LANGUAGE 'internal'
  STABLE STRICT;

CREATE TYPE debversion (
    LIKE           = text,
    INPUT          = debversionin,
    OUTPUT         = debversionout,
    RECEIVE        = debversionrecv,
    SEND           = debversionsend,
    -- make it a non-preferred member of string type category
    CATEGORY       = 'S',
    PREFERRED      = false
);

"Stuart Bishop" <stuart@stuartbishop.net> writes:
> "pg_restore --clean" appears to have an ordering problem, where a custom
> type is being dropped before some functions that use that custom type as a
> parameter, which fails.

It's always worked that way, and is difficult to avoid because of the
circular dependencies between a type and its I/O functions.  If we were
to suppress the DROP FUNCTION commands for the I/O functions, we could
have a non-cosmetic failure: suppose the functions have been created
in the target DB, but not the type itself?  Then the DROP TYPE CASCADE
wouldn't remove the functions, and we'd have a conflict when the script
tries to create them again.

I don't think anyone's ever felt that it was essential for --clean to
not produce any "no such object" errors, since in general you'd get some
of those anyway unless the target DB exactly matches the source.  Maybe
an appropriate response is to document that this is expected.

            regards, tom lane

Tom Lane wrote:
> "Stuart Bishop" <stuart@stuartbishop.net> writes:
> > "pg_restore --clean" appears to have an ordering problem, where a custom
> > type is being dropped before some functions that use that custom type as a
> > parameter, which fails.
>
> It's always worked that way, and is difficult to avoid because of the
> circular dependencies between a type and its I/O functions.  If we were
> to suppress the DROP FUNCTION commands for the I/O functions, we could
> have a non-cosmetic failure: suppose the functions have been created
> in the target DB, but not the type itself?  Then the DROP TYPE CASCADE
> wouldn't remove the functions, and we'd have a conflict when the script
> tries to create them again.
>
> I don't think anyone's ever felt that it was essential for --clean to
> not produce any "no such object" errors, since in general you'd get some
> of those anyway unless the target DB exactly matches the source.  Maybe
> an appropriate response is to document that this is expected.

Applied doc patch attached.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index e78d275..7f12460 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -142,7 +142,8 @@ PostgreSQL documentation
       <listitem>
        <para>
         Output commands to clean (drop)
-        database objects prior to (the commands for) creating them.
+        database objects prior to outputing the commands for creating them.
+        (Restore might generate some harmless errors.)
        </para>

        <para>