From: | salah jubeh <s_jubeh(at)yahoo(dot)com> |
---|---|
To: | pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | Detect the side effect of 'using' clause and adding coulms |
Date: | 2012-11-13 14:58:02 |
Message-ID: | 1352818682.23934.YahooMailNeo@web122201.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Benjamin Henrion | 2012-11-13 15:19:25 | Re: PG under OpenVZ? |
Previous Message | Kevin Grittner | 2012-11-13 14:28:26 | Re: Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage) |