BUG #5857: pg_restore --clean dropping type too soon - Mailing list pgsql-bugs

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
);