BUG #6050: Dump and restore of view after a schema change: can't restore the view

From: "Daniel Cristian Cruz" <danielcristian(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6050: Dump and restore of view after a schema change: can't restore the view
Date: 2011-06-03 12:38:10
Message-ID: 201106031238.p53CcA8P075374@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 6050
Logged by: Daniel Cristian Cruz
Email address: danielcristian(at)gmail(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2011-06-03 14:59:00 Re: BUG #6050: Dump and restore of view after a schema change: can't restore the view
Previous Message Artiom Makarov 2011-06-03 07:00:13 Re: BUG #6048: TRUNCATE vs TRUNCATE CASCADE: misleading message