[HACKERS] View updating and nextval() workaround - will this ever break?

[Richard Huxton]

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;

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [HACKERS] View updating and nextval() workaround - will this ever break?

[Tom Lane]

Richard Huxton dev@archonet.com writes:
 Basically, I'm wondering if anyone can see a problem with my standard 
 workaround to the macro-expansion-vs-nextval problem with view.

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

The main problem with this is that instead of an INSERT n command
completion response, you'll get back a useless SELECT result and then
INSERT 0 (because the original INSERT was suppressed by the INSTEAD
rule).  If your application can deal with that, it's OK, but some don't
like it ...

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match