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