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
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 |