Thread: 7.1 pg_dump fails for user-defined types (release stopper?)
Tricia Holben of Great Bridge just pointed out to me a rather nasty
problem that's exposed by trying to pg_dump and reload the regression
test database. The regression tests include
CREATE FUNCTION widget_in(opaque) RETURNS widget AS '/home/postgres/pgsql/src/test/regress/regress.sl' LANGUAGE
'c';
NOTICE: ProcedureCreate: type 'widget' is not yet defined
CREATE FUNCTION widget_out(opaque) RETURNS opaque AS '/home/postgres/pgsql/src/test/regress/regress.sl' LANGUAGE
'c';
CREATE TYPE widget ( internallength = 24, input = widget_in, output = widget_out, alignment = double
);
which is considered a correct approach to defining I/O procedures for
user-defined types; notice that the code goes out of its way to allow
type "widget" to be referenced before it is defined.
Unfortunately, since the shell pg_type entry for type widget is created
before the pg_proc entry for widget_in is, the OID assignment sequence
is: widget, widget_in, widget_out. When pg_dump dumps these objects in
OID order, it dumps the CREATE TYPE command first --- an ordering that
will fail upon reload.
7.0.* and before do not have this problem because they dump type
definitions after function definitions, regardless of OIDs.
I can think of a couple of ways to deal with this, the simplest being
to say "don't do that" --- ie, define widget_in with result type
"opaque" rather than "widget". That's pretty ugly and will likely
break people's 7.0 dump scripts all by itself. A more promising idea
is to hack function creation so that the OID assigned to the function
is lower than the OIDs assigned to any shell types created when the
function is defined. Or we could try to hack pg_dump to fix this,
but that doesn't seem appetizing.
There may be similar problems with other shell-catalog-entry cases;
haven't looked yet.
Is this a release stopper? I'm inclined to think it is.
regards, tom lane
> A more promising idea is to hack function creation > so that the OID assigned to the function is lower > than the OIDs assigned to any shell types created > when the function is defined. Or we could try to > hack pg_dump to fix this, but that doesn't seem > appetizing. Requiring OID ordering would open up a new can of worms. What happens if the user does a drop/create on the function after creating it? The function could potentially be recreated with a higher OID and then the user would be in the same situation. If the system requires (or works around) creation ordering when creating functions and types, ISTM that pg_dump should have to do the same. darrenk
Anything dependent upon the original function in your example is busted anyhow, regardless of a dump/reload: CREATE function test() returns int4 AS 'SELECT 1' LANGUAGE 'SQL'; CREATE SELECT test()test ------ 1 (1 row) CREATE VIEW test_view AS SELECT test(); CREATE SELECT * FROM test_view;test ------ 1 (1 row) DROP FUNCTION test(); DROP CREATE function test() returns int4 AS 'SELECT 1' LANGUAGE 'SQL'; CREATE SELECT * FROM test_view; ERROR: Function OID 387520 does not exist So dumping OID order is least of the users' problems. Its hard to come up with examples where an object dependent upon another in a *working* database has a lesser OID. So the regression suite really did its job in this case. Mike Mascari mascarm@mascari.com -----Original Message----- From: Darren King [SMTP:darrenk@insightdist.com] Sent: Friday, March 30, 2001 3:31 PM To: Pgsql-Hackers@Postgresql. Org Subject: RE: [HACKERS] 7.1 pg_dump fails for user-defined types (release stopper?) > A more promising idea is to hack function creation > so that the OID assigned to the function is lower > than the OIDs assigned to any shell types created > when the function is defined. Or we could try to > hack pg_dump to fix this, but that doesn't seem > appetizing. Requiring OID ordering would open up a new can of worms. What happens if the user does a drop/create on the function after creating it? The function could potentially be recreated with a higher OID and then the user would be in the same situation. If the system requires (or works around) creation ordering when creating functions and types, ISTM that pg_dump should have to do the same. darrenk
At 14:55 30/03/01 -0500, Tom Lane wrote: > >A more promising idea >is to hack function creation so that the OID assigned to the function >is lower than the OIDs assigned to any shell types created when the >function is defined. This seems hard; would it be better to have the CREATE TYPE use a new OID, and fixup the refs? >Or we could try to hack pg_dump to fix this, >but that doesn't seem appetizing. This *may* not be all that hard; there is a currently unused (always NULL) parameter on the pg_dump ArchiveEntry calls intended for extra dependencies. For UDTs, we could set the this to be the max OID that references the type (or a list of OIDs, if we had to), then modify the pg_restore sort code to check these values if not NULL. ie. (TOC2 > TOC1) iff (Max(TOC2.OID, TOC2.DEPS) > Max(TOC1.OID, TOC1.DEPS)) OR ( Max(TOC2.OID,TOC2.DEPS) = Max(TOC1.OID, TOC1.DEPS) And TOC1.OID = Max(TOC2.DEPS) ) Where DEPS is a list of OIDs the TOC entry depends on. (I *think* that's right...). Since this will only be used when the args is non-null, this code would only be activated in the current broken case. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
At 15:49 31/03/01 +1000, Philip Warner wrote: > > (TOC2 > TOC1) > iff (Max(TOC2.OID, TOC2.DEPS) > Max(TOC1.OID, TOC1.DEPS)) > OR ( Max(TOC2.OID, TOC2.DEPS) = Max(TOC1.OID, TOC1.DEPS) > And TOC1.OID = Max(TOC2.DEPS) > ) > > Where DEPS is a list of OIDs the TOC entry depends on. > >(I *think* that's right...). > This will of course not handle multi-level dependencies. But for the simple ordering we are talking about, I think it will work. It can be extended later when we want to walk a complete dependency tree. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/