Обсуждение: Failed assertion root->hasLateralRTEs on initsplan.c
I get assertion failure on initsplan.c line 1325 while executing following query on HEAD (edc43458d797a5956f4bf39af18cf62abb0077db). It works fine without --enable-cassert. update subscriber set properties = hstore(a) from (select firstname, lastname from player where player.id = subscriber.id) as a; Backtrace: * thread #1: tid = 0x2e16ec, 0x00007fff85f0b866 libsystem_kernel.dylib`__pthread_kill + 10, queue = 'com.apple.main-thread, stop reason = signal SIGABRT frame #0: 0x00007fff85f0b866 libsystem_kernel.dylib`__pthread_kill+ 10 frame #1: 0x00007fff8450335c libsystem_pthread.dylib`pthread_kill + 92 frame#2: 0x00007fff82ffdbba libsystem_c.dylib`abort + 125 frame #3: 0x000000010e2b7510 postgres`ExceptionalCondition(conditionName=<unavailable>, errorType=<unavailable>, fileName=<unavailable>, lineNumber=<unavailable>) + 80 at assert.c:54 frame #4: 0x000000010e155ab6 postgres`distribute_qual_to_rels(root=<unavailable>, clause=0x00007fd5c382e208, is_deduced='\0', below_outer_join='\0', jointype=JOIN_INNER, qualscope=0x00007fd5c3835ee8, ojscope=<unavailable>, outerjoin_nonnullable=<unavailable>, deduced_nullable_relids=<unavailable>, postponed_qual_list=<unavailable>) + 1254 at initsplan.c:1325 frame #5: 0x000000010e154a66 postgres`deconstruct_recurse(root=0x00007fd5c382c248, jtnode=0x00007fd5c382cde0, below_outer_join='\0', qualscope=0x00007fff51c723f8, inner_join_rels=<unavailable>, postponed_qual_list=0x00007fff51c72400) + 870 at initsplan.c:781 frame #6: 0x000000010e1548ab postgres`deconstruct_recurse(root=0x00007fd5c382c248, jtnode=0x00007fd5c382bfd8, below_outer_join='\0', qualscope=0x00007fff51c72450, inner_join_rels=0x00007fff51c72448, postponed_qual_list=0x00007fff51c72440) + 427 at initsplan.c:732 frame #7: 0x000000010e1546a1 postgres`deconstruct_jointree(root=<unavailable>) + 81 at initsplan.c:655 frame #8: 0x000000010e156a1b postgres`query_planner(root=0x00007fd5c382c248, tlist=0x00007fd5c382e398, qp_callback=0x000000010e15a660, qp_extra=0x00007fff51c725f0) + 219 at planmain.c:145 frame #9: 0x000000010e1589d8 postgres`grouping_planner(root=0x00007fd5c382c248, tuple_fraction=<unavailable>) + 2888 at planner.c:1243 frame #10: 0x000000010e157adf postgres`subquery_planner(glob=0x00007fd5c4007e68, parse=0x00007fd5c4007a30, parent_root=<unavailable>, hasRecursion=<unavailable>, tuple_fraction=0, subroot=0x00007fff51c72900) + 3119 at planner.c:572 frame #11: 0x000000010e156cac postgres`standard_planner(parse=0x00007fd5c4007a30, cursorOptions=<unavailable>, boundParams=<unavailable>) + 236 at planner.c:210 frame #12: 0x000000010e1d6356 postgres`pg_plan_query(querytree=0x00007fd5c4007a30, cursorOptions=0, boundParams=0x0000000000000000) + 118 at postgres.c:759 frame #13: 0x000000010e1d979a postgres`PostgresMain [inlined] pg_plan_queries(cursorOptions=0, querytrees=<unavailable>, boundParams=<unavailable>) + 56 at postgres.c:818 frame #14: 0x000000010e1d9762 postgres`PostgresMain [inlined] exec_simple_query(query_string=0x00007fd5c4006038) + 21 at postgres.c:983 frame #15: 0x000000010e1d974d postgres`PostgresMain(argc=<unavailable>, argv=<unavailable>, dbname=0x00007fd5c301ac30, username=<unavailable>) + 8749 at postgres.c:4011 frame #16: 0x000000010e184c1f postgres`PostmasterMain [inlined] BackendRun + 7551 at postmaster.c:4085 frame #17: 0x000000010e184c00 postgres`PostmasterMain [inlined] BackendStartup at postmaster.c:3774 frame #18: 0x000000010e184c00 postgres`PostmasterMain [inlined] ServerLoop at postmaster.c:1585 frame #19: 0x000000010e184c00 postgres`PostmasterMain(argc=<unavailable>, argv=<unavailable>) + 7520 at postmaster.c:1240 frame #20: 0x000000010e11924f postgres`main(argc=1, argv=0x00007fd5c2c03ec0) + 783 at main.c:194 frame #21: 0x00007fff897165fd libdyld.dylib`start + 1 frame #22: 0x00007fff897165fdlibdyld.dylib`start + 1
Emre Hasegeli <emre@hasegeli.com> writes:
> I get assertion failure on initsplan.c line 1325 while executing following query
> on HEAD (edc43458d797a5956f4bf39af18cf62abb0077db). It works fine
> without --enable-cassert.
> update subscriber set properties = hstore(a) from (select firstname,
> lastname from player where player.id = subscriber.id) as a;
Hm, AFAICS this query should absolutely *not* work; the reference to
subscriber.id inside the sub-select is illegal. It might be legal with
LATERAL, but not otherwise. So I think this is a parser bug, and there's
nothing wrong with the planner's Assert. 9.2 and earlier throw the
error I'd expect, so probably something in the LATERAL patches broke
this case; will look.
The next question is if we should allow it with LATERAL. That would
essentially be treating "subscriber" as having implicitly appeared at the
start of the FROM list, which I guess is all right ... but does anyone
want to argue against it? I seem to recall some old discussions about
allowing the update target to be explicitly shown in FROM, in case you
wanted say to left join it against something else. Allowing this implicit
appearance might limit our options if we ever get around to trying to do
that. On the other hand, those discussions were a long time back, so
maybe it'll never happen anyway.
regards, tom lane
On Tue, Jan 7, 2014 at 1:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Emre Hasegeli <emre@hasegeli.com> writes: >> I get assertion failure on initsplan.c line 1325 while executing following query >> on HEAD (edc43458d797a5956f4bf39af18cf62abb0077db). It works fine >> without --enable-cassert. > >> update subscriber set properties = hstore(a) from (select firstname, >> lastname from player where player.id = subscriber.id) as a; > > Hm, AFAICS this query should absolutely *not* work; the reference to > subscriber.id inside the sub-select is illegal. It might be legal with > LATERAL, but not otherwise. So I think this is a parser bug, and there's > nothing wrong with the planner's Assert. 9.2 and earlier throw the > error I'd expect, so probably something in the LATERAL patches broke > this case; will look. > > The next question is if we should allow it with LATERAL. That would > essentially be treating "subscriber" as having implicitly appeared at the > start of the FROM list, which I guess is all right ... but does anyone > want to argue against it? I seem to recall some old discussions about > allowing the update target to be explicitly shown in FROM, in case you > wanted say to left join it against something else. Allowing this implicit > appearance might limit our options if we ever get around to trying to do > that. On the other hand, those discussions were a long time back, so > maybe it'll never happen anyway. I still think that would be a good thing to do, but I don't see a problem. The way I imagine it would work is: if the alias used for the update target also appears in the FROM clause, then we treat them as the same thing (after checking that they refer to the same table in both cases). Otherwise, we add the update target as an additional from-list item. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Jan 7, 2014 at 1:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The next question is if we should allow it with LATERAL. That would
>> essentially be treating "subscriber" as having implicitly appeared at the
>> start of the FROM list, which I guess is all right ... but does anyone
>> want to argue against it? I seem to recall some old discussions about
>> allowing the update target to be explicitly shown in FROM, in case you
>> wanted say to left join it against something else. Allowing this implicit
>> appearance might limit our options if we ever get around to trying to do
>> that. On the other hand, those discussions were a long time back, so
>> maybe it'll never happen anyway.
> I still think that would be a good thing to do, but I don't see a
> problem. The way I imagine it would work is: if the alias used for
> the update target also appears in the FROM clause, then we treat them
> as the same thing (after checking that they refer to the same table in
> both cases). Otherwise, we add the update target as an additional
> from-list item.
Um, well, no; this does make it harder. Consider
update t1 ... from lateral (select...) ss join (t1 left join ...)
You propose that we identify t1 in the sub-JOIN clause with the target
table. What if we have already resolved some outer references in
subselect ss as belonging to t1? Now we have an illegal reference
structure in the FROM clause, which is likely to lead to all sorts
of grief.
I'm sure we could forbid this combination of features, with some klugy
parse-time check or other, but it feels like we started from wrong
premises somewhere.
It might be better if we simply didn't allow lateral references to the
target table for now. We could introduce them in combination with the
other feature, in which case we could say that the lateral reference has
to be to an explicit reference to the target table in FROM, ie, if you
want a lateral reference to t1 in ss you must write
update t1 ... from t1 join lateral (select...) ss;
The fly in the ointment is that we've already shipped a couple of
9.3.x releases that allowed lateral references to the target table.
Even though this wasn't suggested or documented anywhere, somebody
might be relying on it already.
I'm inclined though to pull it back anyway, now that I've thought
about it some more.
regards, tom lane
On 9 January 2014 15:33, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Tue, Jan 7, 2014 at 1:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> The next question is if we should allow it with LATERAL. That would >>> essentially be treating "subscriber" as having implicitly appeared at the >>> start of the FROM list, which I guess is all right ... but does anyone >>> want to argue against it? I seem to recall some old discussions about >>> allowing the update target to be explicitly shown in FROM, in case you >>> wanted say to left join it against something else. Allowing this implicit >>> appearance might limit our options if we ever get around to trying to do >>> that. On the other hand, those discussions were a long time back, so >>> maybe it'll never happen anyway. > >> I still think that would be a good thing to do, but I don't see a >> problem. The way I imagine it would work is: if the alias used for >> the update target also appears in the FROM clause, then we treat them >> as the same thing (after checking that they refer to the same table in >> both cases). Otherwise, we add the update target as an additional >> from-list item. > > Um, well, no; this does make it harder. Consider > > update t1 ... from lateral (select...) ss join (t1 left join ...) > > You propose that we identify t1 in the sub-JOIN clause with the target > table. What if we have already resolved some outer references in > subselect ss as belonging to t1? Now we have an illegal reference > structure in the FROM clause, which is likely to lead to all sorts > of grief. > > I'm sure we could forbid this combination of features, with some klugy > parse-time check or other, but it feels like we started from wrong > premises somewhere. > > It might be better if we simply didn't allow lateral references to the > target table for now. We could introduce them in combination with the > other feature, in which case we could say that the lateral reference has > to be to an explicit reference to the target table in FROM, ie, if you > want a lateral reference to t1 in ss you must write > > update t1 ... from t1 join lateral (select...) ss; > > The fly in the ointment is that we've already shipped a couple of > 9.3.x releases that allowed lateral references to the target table. > Even though this wasn't suggested or documented anywhere, somebody > might be relying on it already. > > I'm inclined though to pull it back anyway, now that I've thought > about it some more. > While testing updatable s.b. views, I came up with the following test case which shows that supporting lateral references to the target table is more than just a matter of syntax. Consider the following example: create table t1(x int); create table t2() inherits(t1); create table t3(a int, b int); update t1 set x=b from lateral (select * from t3 where a=x offset 0) t3; In 9.3.2 and master, prior to this being disallowed, this raises the following error: ERROR: no relation entry for relid 1 because in inheritance_planner(), adjust_appendrel_attrs() uses QTW_IGNORE_RC_SUBQUERIES and so doesn't process subqueries in the rangetable, and so the reference to t1.x in the subquery isn't updated to point to the appropriate append_rel child relation. Of course, if adjust_appendrel_attrs() were made to process subqueries in the rangetable, it would then also have to be able to deal with not-yet-planned SubLinks that might appear there, as the updatable s.b. views patch does, although maybe there's a different way of handling this. Regards, Dean