schema diff: schema existence and schema owner (RM #7266)

[dpage]

Issue migrated from Redmine: https://redmine.postgresql.org/issues/7266 Originally created by Franz-Josef Färber at 2022-03-23 14:48:17 UTC.

Problem #1:

In the source DB execute:

CREATE SCHEMA test;

Schema diff will not suggest to create the schema in the target DB.

Problem #2:

In the source DB execute:

CREATE SCHEMA test;
ALTER SCHEMA test OWNER TO user1;

In the target DB execute:

CREATE SCHEMA test;
ALTER SCHEMA test OWNER TO user2;

Schema diff will not show schema owner differences. This also holds if the schemas contain database objects.

[dpage]

Image migrated from Redmine: https://redmine.postgresql.org/attachments/download/6693 Originally created by Akshay Joshi at 2022-03-24 12:45:23 UTC.

Filename: Schema.png

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/7266#note-2 Originally created by Akshay Joshi at 2022-03-24 12:48:43 UTC.

Hi Franz

Problem 1: There is no point in creating only schema without objects. Add some objects and select you will see the CREATE SCHEMA statement. Refer to the attached screenshot.

Problem 2: It's by design, we only compare the Objects under Databases and Schemas, not the parent. Initially, we have a comparison of only two schemas then later on we enhanced it to compare two databases. We will see if it is possible to fix that.

Redmine ticket header update:

Name	Old Value	New Value
Assigned To changed		Franz-Josef Färber
Attachment added		Schema.png

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/7266#note-3 Originally created by Franz-Josef Färber at 2022-03-25 06:34:53 UTC.

Hello Akshay,

well, we should define what exactly the schema diff should be and what its goals are.

In my opinion, it should show differences between source and target db, and especially enable us to make the target db identical to the source db (as far as schema is concerned - not data).

To be more precise, I define two dbs are "identical" in schema iff their dumps (pg_dump --schema-only) are identical. Can you agree?

Well, then we can state pg_dump also dumps empty schemas and their properties correctly, and so should the pgadmin schema diff in my opinion.

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/7266#note-4 Originally created by Akshay Joshi at 2022-03-25 08:54:36 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Sprint changed		Ready
Assigned To changed	Franz-Josef Färber
Tracker changed	Bug	Feature

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/7266#note-5 Originally created by Akshay Joshi at 2022-04-12 11:04:47 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Sprint changed	Ready	EDB Sprint 115
Assigned To changed		Akshay Joshi

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/7266#note-6 Originally created by Akshay Joshi at 2022-04-14 08:58:53 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Sprint changed	EDB Sprint 115	Ready
Assigned To changed	Akshay Joshi