Hello Guys; Today, I have noticed that my dumps are not working due to bad practice in writing SQL queries.
In the past, I there was a scenario where I there are two tables, one of them is completely dependent on the other. i.e. the foreign key and the primary key constraint assigned to the same column. Please have a look on this is a fictional example, CREATE TABLE a ( a_id serial NOT NULL, a_name text, CONSTRAINT a_pkey PRIMARY KEY (a_id) ) WITH ( OIDS=FALSE ); INSERT INTO a VALUES (1, 'Big design up front'); INSERT INTO a VALUES (2, 'iterative and incremental'); INSERT INTO a VALUES (3, 'OR mappers are slow'); DROP TABLE IF EXISTS b CASCADE; CREATE TABLE b ( b_id serial NOT NULL, b_name text, CONSTRAINT b_pkey PRIMARY KEY (b_id), CONSTRAINT b_b_id_fkey FOREIGN KEY (b_id) REFERENCES a (a_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE ) WITH ( OIDS=FALSE ); INSERT INTO B VALUES (1, 'waterfall'); INSERT INTO B VALUES (2, 'XP'); Now I have a table which maps also A and B in many to many relation such as CREATE TABLE c ( c_id serial NOT NULL, a_id integer NOT NULL, b_id integer NOT NULL, CONSTRAINT c_pkey PRIMARY KEY (c_id), CONSTRAINT c_a_id_fkey FOREIGN KEY (a_id) REFERENCES a (a_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT c_b_id_fkey FOREIGN KEY (b_id) REFERENCES b (b_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH ( OIDS=FALSE ); INSERT INTO c VALUES (1,1,3); INSERT INTO c VALUES (2,2,3); INSERT INTO c VALUES (2,2,1); -- iterative and incremental waterfall model The problem I had is that, many queries are written using the 'using clause' such as REATE VIEW c_a_b AS SELECT * FROM C JOIN B USING (b_id) JOIN A USING (a_id); Up till now no problems, But, if I change the relationship between A and B by having another column called a_id in the B table which references the a (a_id) -Please see the code below- , I get problems in restore because I am joining using a filed which is ambiguous -Exists in two tables- . ALTER TABLE B ADD COlUMN a_id INTEGER; ALTER TABLE B DROP CONSTRAINT b_b_id_fkey; ALTER TABLE B ADD CONSTRAINT b_a_id_fkey FOREIGN KEY (a_id) REFERENCES a (a_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE; ---- This is the error pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag VIEW c_a_b postgres pg_restore: [Archivierer (DB)] could not execute query: ERROR: common column name "a_id" appears more than once in left table -------------------------------------------------------------------------------- I have a question: 1. How we can detect these errors, and how views are stored and manipulated in the database server . If I run SELECT * FROM a_b_c , everything will go fine. I discover this only by using dump and restore. Regards
