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?

Search Discussions

  • Tom Lane at Sep 21, 2008 at 1:28 am

    "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 at Sep 21, 2008 at 7:22 pm
    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 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
  • Tom Lane at Sep 21, 2008 at 11:39 pm

    "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
  • Dmitry Koterov at Sep 22, 2008 at 12:42 am
    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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 21, '08 at 1:05a
activeSep 22, '08 at 12:42a
posts5
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Dmitry Koterov: 3 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2021 Grokbase