loose keys after alter table set schema

Question

ran into a problem on PostgreSQL 12.5

ALTER TABLE old_shcema.my_table SET new_schema

first - everything is OK, everything is transferred except triggers, but if you try back

ALTER TABLE new_schema.my_table SET old_schema

then keys, foreign keys will be lost

what is the reason? Am I doing something wrong or is this a postgres bug?

1) Define *... foreign keys will be lost* . 2) In `psql` does `show search_path` include he new and old schemas. 3) Where did you install Postgres from? **Add answers as update to question**. — Adrian Klaver, Jun 23 '23 at 18:52
You still have not defined what is lost/missing. Provide example of before and after. Do this **as update to question as text(not images)**. — Adrian Klaver, Jun 23 '23 at 20:33
hmm, I've looked around and it looks like a DataGrip bug that generates the wrong DLL. When I read information about the table with "\d" it showed foreign keys. However, in DataGrip they get lost for some reason — Masta, Jun 23 '23 at 21:01

score 0 · Accepted Answer · answered Jun 23 '23 at 17:32

0

Doesn't do it here (PostgreSQL v14).

richardh=> CREATE SCHEMA a;
CREATE SCHEMA
richardh=> CREATE SCHEMA b;
CREATE SCHEMA
richardh=> CREATE TABLE a.t1 (id int PRIMARY KEY);
CREATE TABLE
richardh=> CREATE TABLE a.t2 (idref int NOT NULL REFERENCES a.t1);
CREATE TABLE
richardh=> ALTER TABLE a.t1 SET SCHEMA b;
ALTER TABLE
richardh=> \d a.t2
                   Table "a.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 idref  | integer |           | not null | 
Foreign-key constraints:
    "t2_idref_fkey" FOREIGN KEY (idref) REFERENCES b.t1(id)

richardh=> ALTER TABLE a.t1 SET SCHEMA a;
ERROR:  relation "a.t1" does not exist
richardh=> ALTER TABLE b.t1 SET SCHEMA a;
ALTER TABLE
richardh=> \d a.t2
                   Table "a.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 idref  | integer |           | not null | 
Foreign-key constraints:
    "t2_idref_fkey" FOREIGN KEY (idref) REFERENCES a.t1(id)

answered Jun 23 '23 at 17:32

Richard Huxton

21,516
3
39
51

i am using postgres 12.5 i repeated your steps and got the same error. FK are missing – Masta Jun 23 '23 at 17:49
Using Postgres 12.15 the above works for me. It would be good idea to upgrade to .15 as you are missing a lot of bug fixes, though I pretty sure your issue is not actually a bug. – Adrian Klaver Jun 23 '23 at 18:58
i cant upgrade postgres, this is prod version – Masta Jun 23 '23 at 20:29
`12.5 --> 12.15` is minor update not a major upgrade. It represents 10 bug/security fix updates. Not updating is a bigger issue then leaving it as is. – Adrian Klaver Jun 23 '23 at 20:31
yes, of course. i am agree with you. but version on prod is 12.7. forcing the business to update the database is a much more expensive solution than any other – Masta Jun 23 '23 at 20:34
1

Again it is a minor update not a major upgrade. You don't have to do dump/restore or pg_upgrade and Postgres is free. That being said testing on a dev database first is always a good idea. – Adrian Klaver Jun 23 '23 at 21:05
thank you. I will raise the issue of updating. though it's not about postgres. when i repeated the steps in psql i saw. that the table does not change. All keys remain in place. Everything is fine. This looks like a DataGrip bug from JetBrains. After I move tables from one schema to another, and then back, it stops displaying keys and foreign keys – Masta Jun 23 '23 at 21:13

loose keys after alter table set schema

1 Answers1