Thread: pg_dump ordering
Hi, 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. Chris
"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 |/