From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | View updating and nextval() workaround - will this ever break? |
Date: | 2006-10-31 09:17:44 |
Message-ID: | 454714B8.4030605@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Basically, I'm wondering if anyone can see a problem with my standard
workaround to the macro-expansion-vs-nextval problem with view. I can't
see how PG changes might break it, but I might be using it in a
presentation to others so thought I'd best check.
BEGIN;
CREATE TABLE foo (f_id serial, f1 int4, PRIMARY KEY (f_id));
CREATE TABLE bar (f_id int4 NOT NULL REFERENCES foo, b1 int4, PRIMARY
KEY (f_id,b1));
-- This one suffers from the macro-expanding-nextval problem
--
CREATE VIEW foobar_bad AS SELECT f_id, f1, b1 FROM foo NATURAL JOIN bar;
CREATE RULE foobar_bad_ins AS ON INSERT TO foobar_bad
DO INSTEAD (
INSERT INTO foo (f_id, f1) VALUES (nextval('foo_f_id_seq'), NEW.f1);
INSERT INTO bar (f_id, b1) VALUES (currval('foo_f_id_seq'), NEW.b1);
);
-- This one doesn't
--
CREATE VIEW foobar_good AS SELECT f_id, f1, b1 FROM foo NATURAL JOIN bar;
CREATE FUNCTION foobar_ins_fn(p_f1 int4, p_b1 int4) RETURNS void AS $$
BEGIN
INSERT INTO foo (f_id, f1) VALUES (nextval('foo_f_id_seq'), p_f1);
INSERT INTO bar (f_id, b1) VALUES (currval('foo_f_id_seq'), p_b1);
END;
$$ LANGUAGE plpgsql;
CREATE RULE foobar_good_ins AS ON INSERT TO foobar_good
DO INSTEAD SELECT foobar_ins_fn(NEW.f1, NEW.b1);
-- Bad version...
INSERT INTO foobar_bad (f1,b1)
SELECT generate_series, generate_series+10 FROM generate_series(1,3);
SELECT * FROM foobar_bad;
DELETE FROM bar;
DELETE FROM foo;
-- Good version...
INSERT INTO foobar_good (f1,b1)
SELECT generate_series, generate_series+10 FROM generate_series(1,3);
SELECT * FROM foobar_bad;
DELETE FROM bar;
DELETE FROM foo;
COMMIT;
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2006-10-31 10:41:10 | Re: [HACKERS] WAL logging freezing |
Previous Message | Teodor Sigaev | 2006-10-31 07:55:00 | Re: Index greater than 8k |