Re: Anyone working on pg_dump dependency ordering?

I'm thinking about attacking pg_dump's lack of knowledge about using
dependencies to determine a safe dump order. But if there's someone
out there actively working on the problem, I don't want to tread on
your toes ... anyone?

Also, if you've got uncommitted patches for pg_dump, please let me know.

regards, tom lane

> I'm thinking about attacking pg_dump's lack of knowledge about using
> dependencies to determine a safe dump order. But if there's someone
> out there actively working on the problem, I don't want to tread on
> your toes ... anyone?

I've done a whole lot of _thinking_, but basically no _doing_, so go
right ahead :)

I may as well let you know my thoughts:

There are two levels (sort of) of dependency. The first is that whole
classes of objects can be dependent on whole other classes. eg.
databases depend on users, or ALL FK's can be dumped after ALL tables,
etc.. It would make the dump more readable if you dumped those definite
dependencies in that order, rather than shuffling everything up.

The second level of dependency is when a bunch of object types can
depend on each other. The current solution for that is to sort by OID,
but this fails when it is possible to add a dependency to an object
after it has been created.

eg:

- Adding a column (with a type) to a table
- All the CREATE OR REPLACE commands
- etc.

Hence, a full db wide topological sort might not be necessary.

Lastly, I presume it's possible to create a system of circular
dependencies (eg create or replace view), which really cannot be solved
without a system of 'shells', similar to that needed to dump types and
their i/o functions.

Views seem to be by far the nastiest object. They can be dependent on
almost everything in the database.

> Also, if you've got uncommitted patches for pg_dump, please let me know.

Yes, my 'COMMENT ON' mega patch in the queue contains dumping of
conversions and comments on a bunch of objects.

BTW, if you commit that patch - you might want to change my comment on
type patch to put the "" around "any", and change the results file
appropriately. I noticed I accidentally included that in the patch, and
was about to mention it.

CHris

Christopher Kings-Lynne wrote:

>
>
> There are two levels (sort of) of dependency. The first is that whole
> classes of objects can be dependent on whole other classes. eg.
> databases depend on users, or ALL FK's can be dumped after ALL tables,
> etc.. It would make the dump more readable if you dumped those
> definite dependencies in that order, rather than shuffling everything up.
>
I agree that dumping should be done class-wise (Tables, Functions,
Views) whenever possible, but I don't agree on FKs dumped separately
from the table. IMHO indexes and constraints belong to the table, and
modifying the dump will be hard if a table's code is scattered all around.

Regards,
Andreas

On Sat, 22 Nov 2003, Andreas Pflug wrote:

> Christopher Kings-Lynne wrote:
>
> >
> >
> > There are two levels (sort of) of dependency. The first is that whole
> > classes of objects can be dependent on whole other classes. eg.
> > databases depend on users, or ALL FK's can be dumped after ALL tables,
> > etc.. It would make the dump more readable if you dumped those
> > definite dependencies in that order, rather than shuffling everything up.
> >
> I agree that dumping should be done class-wise (Tables, Functions,
> Views) whenever possible, but I don't agree on FKs dumped separately
> from the table. IMHO indexes and constraints belong to the table, and
> modifying the dump will be hard if a table's code is scattered all around.

You're going to potentially have the constraints scattered in any case due
to circular dependency chains. I'd think that having all the constraints
in one place would be easier than trying to go through the list of tables
that might be in a circular chain in order to find the constraints.

Stephan Szabo wrote:

>You're going to potentially have the constraints scattered in any case due
>to circular dependency chains. I'd think that having all the constraints
>in one place would be easier than trying to go through the list of tables
>that might be in a circular chain in order to find the constraints.
>
>
I still disagree. cyclic dependencies should be avoided anyhow. You'll
get an awful lot of trouble loading data in such a case. Some database
systems refuse to create such stuff right away (mssql).

Regards,
Andreas

On Sat, 2003-11-22 at 16:53, Andreas Pflug wrote:
> Stephan Szabo wrote:
>
> >You're going to potentially have the constraints scattered in any case due
> >to circular dependency chains. I'd think that having all the constraints
> >in one place would be easier than trying to go through the list of tables
> >that might be in a circular chain in order to find the constraints.
> >
> >
> I still disagree. cyclic dependencies should be avoided anyhow. You'll
> get an awful lot of trouble loading data in such a case. Some database
> systems refuse to create such stuff right away (mssql).

CREATE TABLE a (col integer primary key);
CREATE TABLE b (col integer primary key);
ALTER TABLE a ADD FOREIGN KEY (col) REFERENCES b INITIALLY DEFERRED;
ALTER TABLE b ADD FOREIGN KEY (col) REFERENCES a;

How does MSSQL deal with the above?

Rod Taylor wrote:

>
>
>CREATE TABLE a (col integer primary key);
>CREATE TABLE b (col integer primary key);
>ALTER TABLE a ADD FOREIGN KEY (col) REFERENCES b INITIALLY DEFERRED;
>ALTER TABLE b ADD FOREIGN KEY (col) REFERENCES a;
>
>How does MSSQL deal with the above?#
>
>
It depends. Restricting FKs are generated silently, while ON DELETE
CASCADE will throw a message and refuse to create. MSSQL doesn't know
about deferred FKs; no chance to enter spuriously inconsistent data.
Still, using cyclic references is IMHO bad design style. I can't accept
an exceptional case as reason to break *all* table's definition into
pieces. The CREATE TABLE syntax shows that I'm probably not the only one
thinking like this: it may include all constraint definitions as well.

There might be discussions whether its better to script
CREATE TABLE xxx ..;
ALTER TABLE xxx ADD PRIMARY KEY ....;
ALTER TABLE xxx ADD FOREIGN KEY ....;
or
CREATE TABLE xxx (...., PRIMARY KEY (..), FOREIGN KEY (..));

I'd opt for the second version (a little formatted, maybe :-)

Regards,
Andreas

> >CREATE TABLE a (col integer primary key);
> >CREATE TABLE b (col integer primary key);
> >ALTER TABLE a ADD FOREIGN KEY (col) REFERENCES b INITIALLY DEFERRED;
> >ALTER TABLE b ADD FOREIGN KEY (col) REFERENCES a;

> Still, using cyclic references is IMHO bad design style. I can't accept

They're extremely useful when you have normalized data and a very
expensive (but repeatable) process whose results you want to cache for
performance reasons. It enforces that original data and cache are both
added in a single transaction.

But you're right. Normally they're a bad idea.

> an exceptional case as reason to break *all* table's definition into
> pieces. The CREATE TABLE syntax shows that I'm probably not the only one
> thinking like this: it may include all constraint definitions as well.
>
> There might be discussions whether its better to script
> CREATE TABLE xxx ..;
> ALTER TABLE xxx ADD PRIMARY KEY ....;
> ALTER TABLE xxx ADD FOREIGN KEY ....;
> or
> CREATE TABLE xxx (...., PRIMARY KEY (..), FOREIGN KEY (..));
>
> I'd opt for the second version (a little formatted, maybe :-)

Well.. the second one will be much slower when the foreign keys verify.
Primary, unique constraints I'll buy in the create statement. Check
constraints and defaults are a little fuzzier.

Logic will be required to pull them out in the event they call functions
which depends on the table or we enable subqueries (assertion like
constraints) in them.

Rod Taylor wrote:

>>There might be discussions whether its better to script
>>CREATE TABLE xxx ..;
>>ALTER TABLE xxx ADD PRIMARY KEY ....;
>>ALTER TABLE xxx ADD FOREIGN KEY ....;
>>or
>>CREATE TABLE xxx (...., PRIMARY KEY (..), FOREIGN KEY (..));
>>
>>I'd opt for the second version (a little formatted, maybe :-)
>>
>>
>
>Well.. the second one will be much slower when the foreign keys verify.
>
>
Verifying zero rows in the freshly created table should be quite fast...

>Primary, unique constraints I'll buy in the create statement. Check
>constraints and defaults are a little fuzzier.
>
>Logic will be required to pull them out in the event they call functions
>which depends on the table or we enable subqueries (assertion like
>constraints) in them.
>
>
>
Yes, complicated constructions might prevent creating a table's objects
in a single step. It's probably possible to design an object that can't
be extracted automatically and restored at all (e.g. a view using itself).

I wonder if there have been discussions about other ways to
backup/restore. The current pg_dump/pg_restore way will rebuild all
indexes etc, resulting in a fresh and consistent database after restore,
enabling backend upgrades, but it's tricky to guarantee everything runs
smoothly. And it can be quite slow. In a case of a disaster recovery,
this is probably very unlucky.
I wonder if a mixture of pg_dump and physical cluster backup(currently
only possible if backend is shut down) could be implemented, i.e. a
BACKUP sql command. This command should stream out all data from the
physical files, taking a snapshot. When restoring, the command would
create a new database from the input data, by more or less copying the
data to files. This way, all (hopefully non-existent) inconsistencies in
the database would be restored as well (probably including non-vacuumed
rows), but because no checks are applied the restore process would be as
fast as possible. This would be possible only for the same
backend/architecture version, but in case of disaster recovery that's
enough.

Regards,
Andreas

Rod Taylor <pg(at)rbt(dot)ca> writes:
> Well.. the second one will be much slower when the foreign keys verify.
> Primary, unique constraints I'll buy in the create statement. Check
> constraints and defaults are a little fuzzier.

FK, primary, and unique constraints are already split out from the
CREATE TABLE for performance reasons. We could think about folding them
back in in a schema-only dump, but in a full dump I don't think it's
negotiable --- you really want to load the table data before you install
these constraints.

regards, tom lane

Tom Lane wrote:

>Rod Taylor <pg(at)rbt(dot)ca> writes:
>
>
>>Well.. the second one will be much slower when the foreign keys verify.
>>Primary, unique constraints I'll buy in the create statement. Check
>>constraints and defaults are a little fuzzier.
>>
>>
>
>FK, primary, and unique constraints are already split out from the
>CREATE TABLE for performance reasons. We could think about folding them
>back in in a schema-only dump, but in a full dump I don't think it's
>negotiable --- you really want to load the table data before you install
>these constraints.
>
>
IMHO here we have opposite requirements: The dump/restore process should
run as fast as possible, so constraints have to be generated separately,
but pg_dump is also used to reengineer and slightly modify the schema,
where it's helpful if definitions are grouped. I've been asked for
complete schema extraction features in pgAdmin3, and I replied 'use
pg_dump'. It seems that pg_dump tries to serve both requirements, being
a compromise where two dedicated tools could do it better.

Regards,
Andreas

> Verifying zero rows in the freshly created table should be quite fast...

It's hundreds of times faster to add an index to a full table than add
rows to a table with an index.

Chris

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> FK, primary, and unique constraints are already split out from the
> CREATE TABLE for performance reasons. We could think about folding them
> back in in a schema-only dump, but in a full dump I don't think it's
> negotiable --- you really want to load the table data before you install
> these constraints.

IMHO, not only data need to loaded before FK constraints are created but also
there has got to be a feature to allow creation of an FK constraint WITHOUT
doing the verification that all loaded/existing records satisfy the FK
constraint. The ability to create a FK constraint without verification of
existing records should exist by itself (maybe only to superuser) and also as
an option in pg_restore.

More details:
http://archives.postgresql.org/pgsql-admin/2003-11/msg00308.php
http://archives.postgresql.org/pgsql-admin/2003-11/msg00323.php

Thanks

__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

chriskl(at)familyhealth(dot)com(dot)au (Christopher Kings-Lynne) wrote in message news:<3FBEC8D3(dot)3040807(at)familyhealth(dot)com(dot)au>...
> Lastly, I presume it's possible to create a system of circular
> dependencies (eg create or replace view), which really cannot be solved
> without a system of 'shells', similar to that needed to dump types and
> their i/o functions.
>
> Views seem to be by far the nastiest object. They can be dependent on
> almost everything in the database.

Hello Group,

It might be an idea to keep track of all data-definition changes
during the lifetime of a database. Keep all the
SQL-definition-commands in a seperate systemtable somewhere. Then,
when the schema is dumped you'd have the choice of dumping:
- the most recent schema with database-determined ordering (as is the
case in the current situation)
- "replay" the "recorded" datadefinition of the past, so you know the
data definition is executed in a "sound" sequence.

Of course, data itself would be extracted seperate of the definitions
and there would also be the need to remove constrains while the data
is being loaded. This last thing could be done by parsing the
"recorded" data-definition-commands or by first blindly running the
"recorded" commands, then removing (or disabling) any constraints that
are there and finally replacing (or re-enabling) the constraints.

Negative side of this idea is obviously that you need to make changes
to a lot of internals of the postgresql database. AND you need to make
perfectly sure that the state of the database is always consistent
with the state of the recorded SQL.

Good Luck,
Arian.