pg_dump ordering

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

Can someone tell me know what has to be done to pg_dump to make it dump
things in the right order? Where should I start. The most important thing
is getting types dumped before tables that use the type.

What I'd like to see it do is grab the dependency data in pg_depend and
do a topological sort using that. This leaves some issues still to be
resolved ... like what to do when dumping a pre-7.3 database ... but I
think it's the core of a maintainable solution.

IIRC, you can find some further discussion in the archives.

regards, tom lane

What I'd like to see it do is grab the dependency data in pg_depend and
do a topological sort using that.

At the end though, we'd need to dump stuff not caught be the topsort, for
cases where pg_depend has been messed with.

This leaves some issues still to be
resolved ... like what to do when dumping a pre-7.3 database ... but I
think it's the core of a maintainable solution.

Problem is you'd need to sort tables by the youngest column in the table,
which is a pain. Because the main problem is this:

CREATE TABLE...

CREATE TYPE newtype

ALTER TABLE ADD COLUMN newtype

That always breaks...

CHris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

This leaves some issues still to be
resolved ... like what to do when dumping a pre-7.3 database ... but I
think it's the core of a maintainable solution.

Problem is you'd need to sort tables by the youngest column in the table,
which is a pain. Because the main problem is this:
CREATE TABLE...
CREATE TYPE newtype
ALTER TABLE ADD COLUMN newtype
That always breaks...

And it will continue to break, for dumps from pre-7.3 databases.
I think it's a mistake to spend much time on trying to solve that
problem; it'll just distract you from solving the presently-useful
case.

I don't want to see the behavior get a lot worse for old databases,
mind you; I'm just saying it doesn't have to get magically better.

regards, tom lane

At 11:44 AM 1/08/2003 +0800, Christopher Kings-Lynne wrote:

What I'd like to see it do is grab the dependency data in pg_depend and
do a topological sort using that.

At the end though, we'd need to dump stuff not caught be the topsort, for
cases where pg_depend has been messed with.

I have made a start of this (months ago), but have not had a chance to get
back to it. I you are interested I can send my design plans etc. Or I could
try to get back to it, depending on how interested you are in doing the work.

In terms of the dependency data, I was planning to dump dependencies as
well (a trivial skeleton exists); the ordering should happen at
restore-time (except dump should store it in useful-order on the assumption
that it will not be possible to re-order at restore-time). This is
important since we need to allow requests like:

"restore table xyz and it's dependencies from a full dump"

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Philip Warner <pjw@rhyme.com.au> writes:

In terms of the dependency data, I was planning to dump dependencies as
well (a trivial skeleton exists); the ordering should happen at
restore-time (except dump should store it in useful-order on the assumption
that it will not be possible to re-order at restore-time).

ISTM that once we have the dependency problem sorted out, the important
ordering will always happen during dump, and the facility for
re-ordering during restore will become vestigial. This is a good thing,
since there are many scenarios where you can't seek backwards.

This is important since we need to allow requests like:
"restore table xyz and it's dependencies from a full dump"

Right. What will be needed instead will be the ability to know when we
are passing over object X in the dump that we must restore it, because
the object Y that we were asked to restore depends directly or
indirectly on it. So all the dependency info must appear at the front.

regards, tom lane

At 11:07 PM 1/08/2003 -0400, Tom Lane wrote:

So all the dependency info must appear at the front.

Correct. It currently gets stored in the TOC, which is at the front, and is
read into memory at the start of the restore process.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/