Re: Predictable order of SQL commands in pg_dump

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
>