Re: BUG #17803: Rule "ALSO INSERT ... SELECT ..." fails to substitute default values - Mailing list pgsql-bugs
From | Alexander Lakhin |
---|---|
Subject | Re: BUG #17803: Rule "ALSO INSERT ... SELECT ..." fails to substitute default values |
Date | |
Msg-id | 5e09da43-aaba-7ea7-0a51-a2eb981b058b@gmail.com Whole thread Raw |
In response to | Re: BUG #17803: Rule "ALSO INSERT ... SELECT ..." fails to substitute default values (Dean Rasheed <dean.a.rasheed@gmail.com>) |
Responses |
Re: BUG #17803: Rule "ALSO INSERT ... SELECT ..." fails to substitute default values
(Dean Rasheed <dean.a.rasheed@gmail.com>)
|
List | pgsql-bugs |
22.02.2023 15:29, Dean Rasheed wrote: > On Wed, 22 Feb 2023 at 12:00, Alexander Lakhin <exclusion@gmail.com> wrote: >> CREATE RULE vr AS ON INSERT TO v DO ALSO INSERT INTO t SELECT NEW.b; >> INSERT INTO v VALUES(10, -1), (20, DEFAULT); >> SELECT * FROM v; >> >> a | b >> ----+---- >> 10 | -1 >> 20 | -1 >> -1 | -1 >> | -1 >> > Yes, that's correct. Or at least that's the way it's always been. We > even have regression tests similar to that. Thanks for the explanation! I agree that there must be strong reasons to change a well-known and accepted behavior. Please look at another anomaly, probably not related to the initial one, but related to INSERT VALUES(...), (...), as I can see: CREATE TABLE t (a int, b int DEFAULT -1); CREATE VIEW v AS SELECT * FROM t; CREATE RULE vr AS ON INSERT TO v DO ALSO INSERT INTO t SELECT * FROM (SELECT a FROM t WHERE NEW.a = t.a) tt; INSERT INTO v VALUES (1), (2); Leads to: Core was generated by `postgres: law regression [local] INSERT '. Program terminated with signal SIGABRT, Aborted. warning: Section `.reg-xstate/1345067' in core file too small. #0 __pthread_kill_implementation (no_tid=0, signo=6, threadid=140349553121088) at ./nptl/pthread_kill.c:44 44 ./nptl/pthread_kill.c: No such file or directory. (gdb) bt #0 __pthread_kill_implementation (no_tid=0, signo=6, threadid=140349553121088) at ./nptl/pthread_kill.c:44 #1 __pthread_kill_internal (signo=6, threadid=140349553121088) at ./nptl/pthread_kill.c:78 #2 __GI___pthread_kill (threadid=140349553121088, signo=signo@entry=6) at ./nptl/pthread_kill.c:89 #3 0x00007fa5acf33476 in __GI_raise (sig=sig@entry=6) at ../sysdeps/posix/raise.c:26 #4 0x00007fa5acf197f3 in __GI_abort () at ./stdlib/abort.c:79 #5 0x000055643e045db9 in ExceptionalCondition (conditionName=0x55643e1fc08d "root->hasLateralRTEs", fileName=0x55643e1fbd1a "initsplan.c", lineNumber=2208) at assert.c:66 #6 0x000055643dd1211b in distribute_qual_to_rels (root=0x55643ed3d228, clause=0x55643ed5c130, jtitem=0x55643ed5d130, sjinfo=0x0, security_level=0, qualscope=0x55643ed5d2c0, ojscope=0x0, outerjoin_nonnullable=0x0, allow_equivalence=true, has_clone=false, is_clone=false, postponed_oj_qual_list=0x0) at initsplan.c:2208 #7 0x000055643dd11fcf in distribute_quals_to_rels (root=0x55643ed3d228, clauses=0x55643ed5c180, jtitem=0x55643ed5d130, sjinfo=0x0, security_level=0, qualscope=0x55643ed5d2c0, ojscope=0x0, outerjoin_nonnullable=0x0, allow_equivalence=true, has_clone=false, is_clone=false, postponed_oj_qual_list=0x0) at initsplan.c:2113 #8 0x000055643dd1073c in deconstruct_distribute (root=0x55643ed3d228, jtitem=0x55643ed5d130) at initsplan.c:1147 #9 0x000055643dd0f6ce in deconstruct_jointree (root=0x55643ed3d228) at initsplan.c:776 #10 0x000055643dd1476e in query_planner (root=0x55643ed3d228, qp_callback=0x55643dd1b11a <standard_qp_callback>, qp_extra=0x7ffc081eac20) at planmain.c:186 #11 0x000055643dd17319 in grouping_planner (root=0x55643ed3d228, tuple_fraction=0) at planner.c:1496 #12 0x000055643dd16998 in subquery_planner (glob=0x55643ed59468, parse=0x55643ec5e2c8, parent_root=0x0, hasRecursion=false, tuple_fraction=0) at planner.c:1065 #13 0x000055643dd14f36 in standard_planner (parse=0x55643ec5e2c8, query_string=0x55643ec5d568 "INSERT INTO v VALUES (1), (2);", cursorOptions=2048, boundParams=0x0) at planner.c:411 #14 0x000055643dd14c64 in planner (parse=0x55643ec5e2c8, query_string=0x55643ec5d568 "INSERT INTO v VALUES (1), (2);", cursorOptions=2048, boundParams=0x0) at planner.c:281 #15 0x000055643de60d18 in pg_plan_query (querytree=0x55643ec5e2c8, query_string=0x55643ec5d568 "INSERT INTO v VALUES (1), (2);", cursorOptions=2048, boundParams=0x0) at postgres.c:870 #16 0x000055643de60e75 in pg_plan_queries (querytrees=0x55643ed57828, query_string=0x55643ec5d568 "INSERT INTO v VALUES (1), (2);", cursorOptions=2048, boundParams=0x0) at postgres.c:962 #17 0x000055643de6128a in exec_simple_query (query_string=0x55643ec5d568 "INSERT INTO v VALUES (1), (2);") at postgres.c:1159 #18 0x000055643de66456 in PostgresMain (dbname=0x55643ec95968 "regression", username=0x55643ec5aaf8 "law") at postgres.c:4572 #19 0x000055643dd8a118 in BackendRun (port=0x55643ec860d0) at postmaster.c:4461 #20 0x000055643dd899a4 in BackendStartup (port=0x55643ec860d0) at postmaster.c:4189 #21 0x000055643dd85ce9 in ServerLoop () at postmaster.c:1779 #22 0x000055643dd85593 in PostmasterMain (argc=3, argv=0x55643ec58a30) at postmaster.c:1463 #23 0x000055643dc3f614 in main (argc=3, argv=0x55643ec58a30) at main.c:200 The more simple rule: CREATE RULE vr AS ON INSERT TO v DO ALSO INSERT INTO t SELECT a FROM t WHERE NEW.a = t.a; doesn't trigger the assertion failure, and "INSERT INTO v VALUES (1);" with the complex rule too. Maybe it's worth reporting it as another bug, and finish things with the current one? (Though I am still unsure, can we get list_length(pt->jointree->fromlist) != 1 in the new condition?) Best regards, Alexander
pgsql-bugs by date: