On Fri, 8 Feb 2019 at 05:07, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
> Thanks for the report. Seems odd indeed.
Hmm, indeed. That seems to have been broken ever since updatable views
were added.
> Looking into this, the reason it works when inserting just one row vs.
> more than one row is that those two cases are handled by nearby but
> different pieces of code. The code that handles multiple rows seems buggy
> as seen in the above example. Specifically, I think the bug is in
> rewriteValuesRTE() which is a function to replace the default placeholders
> in the input rows by the default values as defined for the target
> relation. It is called twice when inserting via the view -- first for the
> view relation and then again for the underlying table.
Right, except when the view is trigger-updatable. In that case, we do
have to explicitly set the column value to NULL when
rewriteValuesRTE() is called for the view, because it won't be called
again for the underlying table -- it is the trigger's responsibility
to work how (or indeed if) to update the underlying table. IOW, you
need to also use view_has_instead_trigger() to check the view,
otherwise your patch breaks this case:
DROP TABLE IF EXISTS test CASCADE;
CREATE TABLE test (
id int PRIMARY KEY,
value int DEFAULT 0
);
CREATE VIEW test_view AS (SELECT * FROM test);
CREATE OR REPLACE FUNCTION test_view_ins() RETURNS trigger
AS
$$
BEGIN
INSERT INTO test VALUES (NEW.id, NEW.value);
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER test_view_trig INSTEAD OF INSERT ON test_view
FOR EACH ROW EXECUTE FUNCTION test_view_ins();
INSERT INTO test_view VALUES (1, DEFAULT), (2, DEFAULT);
ERROR: unrecognized node type: 142
While playing around with this, I noticed a related bug affecting the
new identity columns feature. I've not investigated it fully, but It
looks almost the same -- if the column is an identity column, and
we're inserting a multi-row VALUES set containing DEFAULTS, they will
get rewritten to NULLs which will then lead to an error if overriding
the generated value isn't allowed:
DROP TABLE IF EXISTS foo CASCADE;
CREATE TABLE foo
(
a int,
b int GENERATED ALWAYS AS IDENTITY
);
INSERT INTO foo VALUES (1,DEFAULT); -- OK
INSERT INTO foo VALUES (2,DEFAULT),(3,DEFAULT); -- Fails
I think fixing that should be tackled separately, because it may turn
out to be subtly different, but it definitely looks like another bug.
Regards,
Dean
