Re: Enhancement request for pg_dump

On 04/17/2016 01:58 PM, Adrian Klaver wrote:
> On 04/17/2016 01:10 PM, Sergei Agalakov wrote:
>> I don't see how these questions are related to the proposed pg_dump
>> improvement.
>> I suggest to improve pg_dump so it can be used instead of the third
>> party tools like DBSteward and SQLWorkbench/J etc.
>> to compare two different databases or existing dumps, and to identify
>> the differences. The use cases will be exactly
>> the same as for the third party tools. The positive difference will be
>> that pg_dump is a very reliable, always available and supports all the
>> latest PostgreSQL features.
>> Do you imply that there shouldn't be any reasons to compare different
>> databases to find the differences between them?
>
> To follow up my previous post and to illustrate some of the
> difficulties, from your original post:
>
> "One database may script grants like
>
> REVOKE ALL ON TABLE contracttype FROM PUBLIC;
> REVOKE ALL ON TABLE contracttype FROM madmin;
> GRANT ALL ON TABLE contracttype TO madmin;
> GRANT SELECT ON TABLE contracttype TO mro;
> GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE contracttype TO musers;
>
> and the other may change the order of grants like
>
> REVOKE ALL ON TABLE contracttype FROM PUBLIC;
> REVOKE ALL ON TABLE contracttype FROM madmin;
> GRANT ALL ON TABLE contracttype TO madmin;
> GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE contracttype TO musers;
> GRANT SELECT ON TABLE contracttype TO mro;
> "
>
>  From the perspective of the database both of the above lead to the same
> end result, so order is not important. Of course a diff is going to see
> it differently. The solution is then to impose an order, but how would
> that be determined? For instance what about:
>
> GRANT SELECT ON TABLE contracttype TO mro;
> GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE contracttype TO musers;
>
> vs
>
> GRANT SELECT ON TABLE contracttype TO mro;
> GRANT INSERT, SELECT, DELETE,UPDATE ON TABLE contracttype TO musers;
>
> Again diff will see them as not the same, but functionally they are the
> same. So who decides order and how far do you reach down into the
> statements?

Bad example. Some testing shows Postgres will reorder the GRANTS as:

SELECT,INSERT,DELETE,UPDATE

from whatever order they where entered as.

>
>
> As Bill wrote the issue is after the fact version control versus before
> the fact version control. Trying to match things up after various people
> have been turned loose at will on different instances of databases is
> much more difficult then having them go through a structured version
> control system first.
>
>
>>
>> Sergei
>>
>>> > On Apr 17, 2016, at 12:41 PM, Sergei Agalakov
>>> <Sergei(dot)Agalakov(at)getmyle(dot)com> wrote:
>>> >
>>> > I know about DBSteward. I don't like to bring PHP infrastructure
>>> only to be able to compare two dumps,
>>> > and to deal with potential bugs in the third party tools. The
>>> pg_dump in other hand is always here, and is always trusted.
>>> > SQLWorkbench/J also can compare two schemas, and requires only Java.
>>> Again, I trust pg_dump more.
>>> >http://www.sql-workbench.net/
>>> >
>>> > May be pg_dump was never INTENDED to generate the dump files with
>>> the determined order of the statements,
>>> > but it CAN do it with the minor changes, and be more useful to
>>> administrators. Why rely on the third party tools
>>> > for the tasks that can be done with the native, trusted tools?
>>> >
>>> > Sergei
>>> Does it matter if they differ if you cannot recreate the correct one
>>> exactly from source-controllled DDL?  Or know how they are supposed to
>>> differ if this is a migration point?
>>
>>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com