Re: BUG #15623: Inconsistent use of default for updatable view

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