Detect the side effect of 'using' clause and adding coulms

[salah jubeh <s_jubeh@xxxxxxxxx>]

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