Hi,
On 2019/02/08 6:42, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15623
> Logged by: Roger Curley
> Email address: rocurley@gmail.com
> PostgreSQL version: 11.1
> Operating system: Ubuntu 11.1
> Description:
>
> Steps to reproduce (run in psql shell):
> ```
> 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);
>
> INSERT INTO test_view VALUES (1, DEFAULT), (2, DEFAULT);
> INSERT INTO test VALUES (3, DEFAULT), (4, DEFAULT);
> INSERT INTO test_view VALUES (5, DEFAULT);
> SELECT * FROM test;
> ```
>
> Result:
> ```
> id | value
> ----+-------
> 1 |
> 2 |
> 3 | 0
> 4 | 0
> 5 | 0
> ```
>
> Expected Result:
> ```
> id | value
> ----+-------
> 1 | 0
> 2 | 0
> 3 | 0
> 4 | 0
> 5 | 0
> ```
> In particular, it's surprising that inserting 1 row into an updatable view
> uses the table default, while inserting 2 uses null.
Thanks for the report. Seems odd indeed.
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. This arrangement
seems to work correctly if the view specifies its own defaults for columns
(assuming that it's okay for the view's defaults to override the
underlying base table's). If there are no view-specified defaults, then
rewriteValuesRTE replaces the default placeholders in the input row by
NULL constants when called for the first time with the view as target
relation and the next invocation for the underlying table finds that it
has no work to do, so its defaults are not filled.
Attached find a patch that adjusts rewriteValuesRTE to not replace the
default placeholder if the view has no default value for a given column.
Also, adds a test in updatable_views.sql.
Thanks,
Amit