Thread: Re: [PATCHES] pg_dump primary keys
On Sat, 11 Dec 1999, Oleg Bartunov wrote: > I have a problem with pg_dump (6.5.3) if I use > create table foo ( > a text default foo_function() > ); > where foo_function() is my function. > pg_dump dumps create table first and create function > later. Obvioulsy restoring doesn't works and > I have to edit dump file. It's rather annoying. > Is it fixed in current tree ? What though if a function accesses a table? Which one goes first? Do we have to maintain a network of dependencies in pg_dump? Eventually we'll probably have to, with all the foreign key stuff coming up. Gloomy prospects. -Peter -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut wrote:
> What though if a function accesses a table? Which one goes first? Do we
> have to maintain a network of dependencies in pg_dump? Eventually we'll
> probably have to, with all the foreign key stuff coming up. Gloomy
> prospects.
No need to worry about FOREIGN KEY stuff here. These
functions are generic builtins not dumped at all.
But need to worry about all other functions of all languages.
They can be used in a table schema and OTOH their definition
might need a relation to exist (could have tuple type as
argument). Plus, for SQL language functions (only SQL, not
PL/pgSQL or any other language) their body is checked at
CREATE time for syntax, so relations they use are required.
This can only be solved by your mentioned dependency network.
BTW: All this was one reason to dump views as CREATE TABLE
and later CREATE RULE. Because views likely contain
functions.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
Peter Eisentraut wrote:
>> What though if a function accesses a table? Which one goes first? Do we
>> have to maintain a network of dependencies in pg_dump? Eventually we'll
>> probably have to, with all the foreign key stuff coming up. Gloomy
>> prospects.
Couldn't we solve this by the simple expedient of dumping all the
objects in the database in OID order?
Expecting pg_dump to parse function bodies to discover what
relations/types are mentioned doesn't look appetizing at all...
regards, tom lane
On 1999-12-11, Tom Lane mentioned: > Peter Eisentraut wrote: > >> What though if a function accesses a table? Which one goes first? Do we > >> have to maintain a network of dependencies in pg_dump? Eventually we'll > >> probably have to, with all the foreign key stuff coming up. Gloomy > >> prospects. > > Couldn't we solve this by the simple expedient of dumping all the > objects in the database in OID order? Wow, great idea! That might actually solve all (well, most) pg_dump related problems once and for all. Of course how you get all objects in the database in oid order is to be determined. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut <peter_e@gmx.net> writes:
>> Couldn't we solve this by the simple expedient of dumping all the
>> objects in the database in OID order?
> Wow, great idea! That might actually solve all (well, most) pg_dump
> related problems once and for all. Of course how you get all objects in
> the database in oid order is to be determined.
I think it would take some restructuring in pg_dump: instead of
processing each type of database object separately, it would have to
grab some info (at least the OIDs and types) for all the different
objects in the DB, then sort this info by OID, and finally get the
details and produce the output for each object in OID order.
This would still fail in some pathological cases involving ALTER --- for
example, make a table, later create a new datatype, and then ALTER TABLE
ADD COLUMN of that datatype. So the next refinement would be to examine
dependencies and do a topological sort rather than a simple sort by OID.
We'd still have to restructure pg_dump as above, though, and "examining
dependencies" is not exactly trivial for function bodies in unknown PL
languages...
If we had ALTER FUNCTION, which we don't but should, I think it would
actually be possible to create circular dependencies for which there is
*no* dump order that will work :-(. So I'm not sure it's worth the
trouble to add dependency extraction and a topological sort algorithm
to pg_dump rather than just sorting by OID. Dumping in OID order will
solve 99% of the problem with a fraction of the work.
regards, tom lane
Added to TODO list. > Peter Eisentraut wrote: > >> What though if a function accesses a table? Which one goes first? Do we > >> have to maintain a network of dependencies in pg_dump? Eventually we'll > >> probably have to, with all the foreign key stuff coming up. Gloomy > >> prospects. > > Couldn't we solve this by the simple expedient of dumping all the > objects in the database in OID order? > > Expecting pg_dump to parse function bodies to discover what > relations/types are mentioned doesn't look appetizing at all... > > regards, tom lane > > ************ > -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026