The following bug has been logged online:
Bug reference: 6050
Logged by: Daniel Cristian Cruz
Email address: danielcristian@gmail.com
PostgreSQL version: 9.0.4
Operating system: Linux
Description: Dump and restore of view after a schema change: can't
restore the view
Details:
Example:
DROP VIEW IF EXISTS cba;
DROP TABLE IF EXISTS c;
DROP TABLE IF EXISTS b;
DROP TABLE IF EXISTS a;
CREATE TABLE a (
id_a serial primary key,
v text
);
CREATE TABLE b (
id_b serial primary key,
id_a integer REFERENCES a (id_a),
v text
);
CREATE TABLE c (
id_c serial primary key,
id_b integer references b (id_b),
v text
);
INSERT INTO a (id_a, v) VALUES (DEFAULT, 'A');
INSERT INTO b (id_a, v) VALUES (CURRVAL('a_id_a_seq'), 'B');
INSERT INTO c (id_b, v) VALUES (CURRVAL('b_id_b_seq'), 'C');
CREATE VIEW cba AS
SELECT c.v AS vc, b.v AS vb, a.v AS va
FROM c
JOIN b USING (id_b)
JOIN a USING (id_a);
SELECT * FROM cba;
-- RELATION a -> b-> c became a -> b and a -> c because b is optional
-- SET the value of a -> c where a -> b is defined:
ALTER TABLE c ADD id_a integer;
UPDATE c
SET id_a = b.id_a
FROM b
WHERE b.id_b = c.id_b;
-- VIEW still works!?!?!?
-- Obvious that was a modeling mistake
SELECT * FROM cba;
-- But a pg_dump and a pg_restore of this database generates an error when
restoring the view
