schema diff: foreign keys: wrong schema

[fjf2002]

PgAdmin4 Schema Diff generates statements like:

ALTER TABLE IF EXISTS schema1.table1
    ADD CONSTRAINT xxx_fk FOREIGN KEY (yyy)
    REFERENCES schema1.table2 (zzz) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    DEFERRABLE;

... but the table2 resides in schema2, not schema1!

[akshay-joshi]

Hi @fjf2002

I have tested the above-mentioned case:
Testing scenario:

• Create 3 schema (schema1, schema2, schema3)
• Create a table named "table2" in schema2.
• Create a table named "table1" in schema1 with a foreign key constraint referring to the column of schema2.table2.
• Create an almost identical table named "table1" in schema3 except the difference is the foreign key constraint.
• Compare schema1 and schema3 using the Schema Diff tool.

Found no issues, please refer to the screenshot.

[fjf2002]

(cannot test currently due to #5507)

[fjf2002]

Working on your schema diff is quite cumbersome. It produces quite sophisticated, hard-to-reproduce errors, and I am not aware of automating that, resulting in mouse click orgies for me. Any suggestions to ease my work?

By the way, I always suspect the schema diff compares object ids, which can fail if I use different servers. Perhaps there is the problem?

Kind of minimal example:

Setup up two brand new postgres instances and a pgadmin instance (I did that via docker containers).

In the first postgres instance, execute

CREATE TABLE foo (i int);
CREATE TABLE bar (j int);

CREATE SCHEMA schema1;
CREATE SCHEMA schema2;
CREATE TABLE schema1.a (
    t text PRIMARY KEY
);
CREATE TABLE schema2.b (
    t text,
    CONSTRAINT fk FOREIGN KEY (t) REFERENCES schema1.a(t)
);

In the second postgres instance, execute

CREATE SCHEMA schema1;
CREATE SCHEMA schema2;
CREATE TABLE schema1.a (
    t text PRIMARY KEY
);
CREATE TABLE schema2.b (
    t text
);

The schema diff of the two servers will incorrectly suggest

ALTER TABLE IF EXISTS schema2.b
    ADD CONSTRAINT fk FOREIGN KEY (t)
    REFERENCES schema2.a (t) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;

On execution, this will result in:

ERROR:  relation "schema2.a" does not exist
SQL state: 42P01

[FaharAbbasRizvi]

This is resolved in the latest snapshot build:
https://www.postgresql.org/ftp/pgadmin/pgadmin4/snapshots/2022-11-29/