I incrementally altered my database into a state where backups couldn't be restored.

I was able to put my database into a state where I couldn't restore a
backup without manually editing the backup file.

I make backups like so:
$pg_dump --disable-triggers -S postgres -U postgres dbname >
dbname.sql

I did an ALTER TABLE which added a column to a table. That table was
used in a join in a view. After adding the column, the SELECT that is
the body of my view would no longer work, even though the ALTER TABLE
succeeded and the view continued to work.

The problem was that when I would try to restore my database from a
backup made after the ALTER TABLE, the statement that restored the view
would fail. The error got lost in the output, and I didn't notice the
problem until I tried to query the view on the restored database.

Here's an example:

-- ===================================================
/*
DROP TABLE c CASCADE;
DROP TABLE b CASCADE;
DROP TABLE a CASCADE;
*/

CREATE TABLE a (
aid SERIAL PRIMARY KEY
);

CREATE TABLE b (
bid SERIAL PRIMARY KEY,
aid INTEGER REFERENCES a
);

CREATE TABLE c (
cid SERIAL PRIMARY KEY,
bid INTEGER REFERENCES b
);

CREATE VIEW v_a_b_c AS
SELECT *
FROM b
JOIN c USING (bid)
JOIN a USING (aid)
;

SELECT * FROM v_a_b_c;

ALTER TABLE c ADD COLUMN aid INTEGER REFERENCES a;

-- This will succeed:
SELECT * FROM v_a_b_c;

-- But, this will fail with
-- ERROR: common column name "aid" appears more than once in left table
-- SQL state: 42702
SELECT *
FROM b
JOIN c USING (bid)
JOIN a USING (aid)

-- It is now possible to make a backup with pg_dump that cannot be fully
restored.
-- When restoring, this will fail:
CREATE OR REPLACE VIEW v_a_b_c AS
SELECT *
FROM b
JOIN c USING (bid)
JOIN a USING (aid)
;

-- A workaround is to hand-edit the backup file and fix the broken
SELECT.

-- ===================================================

I am not subscribed to this list. If you need clarification, please
email me directly.

Adam Tomjack

Zuercher Technologies LLC
108 E. Missouri Ave. Suite 1
Pierre, SD 57501
(605) 224-4838