BUG #17811: Replacing an underlying view breaks OLD/NEW tuple when accessing it via upper-level view

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: exclusion(at)gmail(dot)com
Subject: BUG #17811: Replacing an underlying view breaks OLD/NEW tuple when accessing it via upper-level view
Date: 2023-02-27 20:00:03
Message-ID: 17811-d31686b78f0dffc9@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17811
Logged by: Alexander Lakhin
Email address: exclusion(at)gmail(dot)com
PostgreSQL version: 15.2
Operating system: Ubuntu 22.04
Description:

I've discovered an issue with replacing a view when there is another
updatable view defined on top of it and the new underlying view has
more columns than the previous one.

First example with triggers:
CREATE TABLE t (id int, f1 text);
CREATE VIEW v1 AS SELECT id FROM t;
CREATE VIEW v2 AS SELECT * FROM v1;
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t;
CREATE FUNCTION v1_trig_fn() RETURNS trigger AS $$
BEGIN
RAISE NOTICE 'old.id: %, old.f1: %', old.id, old.f1;
UPDATE t SET id=new.id WHERE id=old.id;
RETURN new;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER v1_upd_trig INSTEAD OF UPDATE ON v1
FOR EACH ROW EXECUTE FUNCTION v1_trig_fn();
INSERT INTO t VALUES (1, 11), (2, 22);
SELECT * FROM t;
UPDATE v2 SET id = 3 WHERE id = 2;
SELECT * FROM t;

id | f1
----+----
1 | 11
2 | 22
(2 rows)

NOTICE: old.id: 2, old.f1: <NULL> -- ???
UPDATE 1
id | f1
----+----
1 | 11
3 | 22
(2 rows)

but if new v1 defined with security_barrier:
CREATE OR REPLACE VIEW v1 WITH (security_barrier = true) AS SELECT * FROM
t;
...
NOTICE: old.id: 2, old.f1: 22, old.f2: 222 -- OK

but on master (after 47bb9db75)
...
NOTICE: old.id: 2, old.f1: 22, old.f2: 222
WARNING: problem in alloc set MessageContext: req size > alloc size for
chunk 0x62900003ccb0 in block 0x62900003c200
WARNING: problem in alloc set MessageContext: req size > alloc size for
chunk 0x62900003ccb0 in block 0x62900003c200
(I've seen also runtime errors detected by asan.)

Second example with rules (REL_15_STABLE without asserts):
CREATE TABLE t(id int, f1 int, f2 int, f3 int, f4 int);
CREATE TABLE tc(id int, f int);

CREATE VIEW v1 AS SELECT 1 AS id;
CREATE VIEW v2 AS SELECT * FROM v1;
CREATE OR REPLACE VIEW v1 WITH (security_barrier = true) AS SELECT * FROM
t;

CREATE RULE v1_upd_rule AS ON UPDATE TO v1
DO INSTEAD (INSERT INTO tc VALUES (NEW.id, NEW.f1); UPDATE t SET id =
NEW.id WHERE id = OLD.id;);

INSERT INTO t VALUES (1, 11, 111, 1111, 11111), (2, 22, 222, 2222, 22222);

SELECT * FROM t;
SELECT * FROM v2;

UPDATE v2 SET id = 3 WHERE id = 2;
SELECT * FROM t;
SELECT * FROM tc;

id
----
1
2
(2 rows)

server closed the connection unexpectedly
...
Program terminated with signal SIGSEGV, Segmentation fault.

#0 0x0000555b19cd22b6 in bms_add_members (a=0x8, b=b(at)entry=0x555b1a645990)
at bitmapset.c:808
808 if (a->nwords < b->nwords)
(gdb) bt
#0 0x0000555b19cd22b6 in bms_add_members (a=0x8, b=b(at)entry=0x555b1a645990)
at bitmapset.c:808
#1 0x0000555b19d29273 in add_vars_to_targetlist (root=0x555b1a645088,
vars=0x555b1a645940,
where_needed=0x555b1a645990, create_new_ph=true) at initsplan.c:259
#2 0x0000555b19d2931d in build_base_rel_tlists (root=0x555b1a645088,
final_tlist=<optimized out>) at initsplan.c:192
#3 0x0000555b19d2be26 in query_planner (root=root(at)entry=0x555b1a645088,
qp_callback=qp_callback(at)entry=0x555b19d2c430 <standard_qp_callback>,
qp_extra=qp_extra(at)entry=0x7fffd186cd10)
at planmain.c:178
...

But with:
CREATE RULE v1_upd_rule AS ON UPDATE TO v1
DO INSTEAD (INSERT INTO tc VALUES (NEW.id, NEW.f3); UPDATE t SET id =
NEW.id WHERE id = OLD.id;);

...
id
----
1
2
(2 rows)

UPDATE 1
id | f1 | f2 | f3 | f4
----+----+-----+------+-------
1 | 11 | 111 | 1111 | 11111
3 | 22 | 222 | 2222 | 22222
(2 rows)

id | f
----+------
3 | 2222
(1 row)
(Maybe it succeeded accidentally.)

REL_15_STABLE with asserts:
TRAP: FailedAssertion("attno >= rel->min_attr && attno <= rel->max_attr",
File: "initsplan.c", Line: 249, PID: 266170)

Interestingly enough, but replacing an underlying view with incompatible
fields
is prohibited (thanks to a check in checkViewTupleDesc()):
CREATE VIEW v1 AS SELECT 1::text AS id;
CREATE VIEW v2 AS SELECT * FROM v1;
CREATE OR REPLACE VIEW v1 WITH (security_barrier = true) AS SELECT * FROM
t;
ERROR: cannot change data type of view column "id" from text to integer

Just as decreasing of a number of columns:
CREATE VIEW v1 AS SELECT 1 AS id, 11 AS f1, 111 AS f2, 1111 AS f3, 11111 AS
f4, 111111 AS f5;
CREATE VIEW v2 AS SELECT * FROM v1;
CREATE OR REPLACE VIEW v1 WITH (security_barrier = true) AS SELECT * FROM
t;
ERROR: cannot drop columns from view

Maybe replacing a view with increasing number of columns should be
prohibited too?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2023-02-27 20:21:16 Re: BUG #17810: Update from 13.09 to 13.10 breaks SQLs with VACUUM
Previous Message Tom Lane 2023-02-27 19:43:45 Re: BUG #17810: Update from 13.09 to 13.10 breaks SQLs with VACUUM