Thread: Predictable order of SQL commands in pg_dump

Hello.

Utility pg_dump dumps the identical database schemas not always
identically: sometimes it changes an order of SQL statements.
E.g.:

1. Dump of database A:

ALTER TABLE xxx ADD CONSTRAINT ...;
ALTER TABLE yyy ADD CONSTRAINT ...;

2. Dump of database B which has identical structure as A ("pg_dump A |
psql -d B" was executed)

ALTER TABLE yyy ADD CONSTRAINT ...;
ALTER TABLE xxx ADD CONSTRAINT ...;

This behaviour is not good, because I cannot execute diff to visually
view what was changed between databases A and B. (I use this diff only
for visual detection, please do not refer I want to use this diff for
schema migration - I don't want it!).

Is it possible to make pg_dump more predictable in SQL ordering?
What order does it use by default?

"Dmitry Koterov" <dmitry@koterov.ru> writes:
> Utility pg_dump dumps the identical database schemas not always
> identically: sometimes it changes an order of SQL statements.

Please provide a concrete example.  The dump order for modern servers
(ie, since 7.3) is by object type, and within a type by object name,
except where another order is forced by dependencies.  And there is no
random component to the dependency solver ;-).  So it should be
behaving the way you want.
        regards, tom lane

Unfortunately, I cannot reproduce this with 100% effect.

But, time to time I execute diff utility for a database and notice
that two or more trigger or constraint definitions (or something else)
are permuted. Something like this:


+ALTER TABLE ONLY a
+    ADD CONSTRAINT "fk_b_Id" FOREIGN KEY (b_id) REFERENCES b(id) MATCH FULL;

-ALTER TABLE ONLY a
-    ADD CONSTRAINT fk_b_id FOREIGN KEY (b_id) REFERENCES b(id) MATCH FULL;

-ALTER TABLE ONLY a
+ALTER TABLE ONLY a    ADD CONSTRAINT fk_c_id FOREIGN KEY (c_id) REFERENCES c(id) MATCH FULL;


Or that:

CREATE TRIGGER t000_set_id
-    BEFORE INSERT OR DELETE OR UPDATE ON a
+    BEFORE INSERT OR DELETE OR UPDATE ON b    FOR EACH ROW    EXECUTE PROCEDURE i_trg();
CREATE TRIGGER t000_set_id
-    BEFORE INSERT OR DELETE OR UPDATE ON b
+    BEFORE INSERT OR DELETE OR UPDATE ON a    FOR EACH ROW    EXECUTE PROCEDURE i_trg();

You see, object names are the same, but ordering is mixed. Seems
pg_dump orders objects with no care about their dependencies? So, if
object names are the same, it dumps it in unpredictable order, no
matter on their contents...



On Sun, Sep 21, 2008 at 5:28 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Dmitry Koterov" <dmitry@koterov.ru> writes:
>> Utility pg_dump dumps the identical database schemas not always
>> identically: sometimes it changes an order of SQL statements.
>
> Please provide a concrete example.  The dump order for modern servers
> (ie, since 7.3) is by object type, and within a type by object name,
> except where another order is forced by dependencies.  And there is no
> random component to the dependency solver ;-).  So it should be
> behaving the way you want.
>
>                        regards, tom lane
>

"Dmitry Koterov" <dmitry@koterov.ru> writes:
>  CREATE TRIGGER t000_set_id
> -    BEFORE INSERT OR DELETE OR UPDATE ON a
> +    BEFORE INSERT OR DELETE OR UPDATE ON b
>      FOR EACH ROW
>      EXECUTE PROCEDURE i_trg();

>  CREATE TRIGGER t000_set_id
> -    BEFORE INSERT OR DELETE OR UPDATE ON b
> +    BEFORE INSERT OR DELETE OR UPDATE ON a
>      FOR EACH ROW
>      EXECUTE PROCEDURE i_trg();

> You see, object names are the same, but ordering is mixed.

Yeah, because the sort is just on object name.

For objects of the same type I suppose we could take a look at their
owning object's name too ...
        regards, tom lane

Great! Would it be implemented in a next version? Seems it would be
very helpful, especially for people who commit database structure to
CVS/SVN once per minute to track changes history (or similar)...


On Sun, Sep 21, 2008 at 11:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Dmitry Koterov" <dmitry@koterov.ru> writes:
>>  CREATE TRIGGER t000_set_id
>> -    BEFORE INSERT OR DELETE OR UPDATE ON a
>> +    BEFORE INSERT OR DELETE OR UPDATE ON b
>>      FOR EACH ROW
>>      EXECUTE PROCEDURE i_trg();
>
>>  CREATE TRIGGER t000_set_id
>> -    BEFORE INSERT OR DELETE OR UPDATE ON b
>> +    BEFORE INSERT OR DELETE OR UPDATE ON a
>>      FOR EACH ROW
>>      EXECUTE PROCEDURE i_trg();
>
>> You see, object names are the same, but ordering is mixed.
>
> Yeah, because the sort is just on object name.
>
> For objects of the same type I suppose we could take a look at their
> owning object's name too ...
>
>                        regards, tom lane
>