Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix setrefs.c code for Result Cache nodes

  1. BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1

    PG Bug reporting form <noreply@postgresql.org> — 2021-05-22T16:20:29Z

    The following bug has been logged on the website:

Bug reference:      17030
Logged by:          Hans Buschmann
Email address:      buschmann@nidsa.net
PostgreSQL version: 14beta1
Operating system:   Windows Server 2019 64 bit
Description:        

On Windows I installed the new pg14beta1 downloaded from EDB Website.
xxxdb=# select version ();
                            version
---------------------------------------------------------------
 PostgreSQL 14beta1, compiled by Visual C++ build 1914, 64-bit
(1 Zeile)

When testing our application I suddenly got the $subject error message:

ERROR:  cannot decompile join alias var in plan tree

This occurs on a quite complex CTE query of the style (here only a
simplified layout to give an idea): 

with
qxxx as (
select a,b,c
from taba
left join tabb
...)
select xmlelement (name sel,
xmlelement(name bbb
...
))
from (select * from qxxx order by colorder) as qxo
)

This particular query (generating an XML-tree) worked for about 5 years on
all newest minor versions from 9.6 to 13.3.

I know that this is not an reproduceble case, but I can supply the execution
plan from pg13.3 if needed.

Thanks for investigation

Hans Buschmann

  2. Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-05-22T21:09:15Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> When testing our application I suddenly got the $subject error message:
> ERROR:  cannot decompile join alias var in plan tree

That's surely a bug, but there's little we can do about it without
a self-contained test case.

			regards, tom lane

  3. Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1

    David Rowley <dgrowleyml@gmail.com> — 2021-05-23T12:47:59Z

    On Sun, 23 May 2021 at 09:09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > When testing our application I suddenly got the $subject error message:
> > ERROR:  cannot decompile join alias var in plan tree
>
> That's surely a bug, but there's little we can do about it without
> a self-contained test case.

It fails for me with a fresh database with:

explain select * from pg_attribute a where attname not in(select
column_name from information_schema.columns);

I suspect Result Cache is to blame here and that I've done something
wrong in setrefs.c.

I can look into this a bit more in the morning. It's not a good time
of day right now to debug setrefs.c

David

  4. Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1

    David Rowley <dgrowleyml@gmail.com> — 2021-05-23T12:58:10Z

    On Mon, 24 May 2021 at 00:47, David Rowley <dgrowleyml@gmail.com> wrote:
> I can look into this a bit more in the morning. It's not a good time
> of day right now to debug setrefs.c

It *may* just be a missing call to set_upper_references() for Result
Cache. i.e. the attached. But I will need to look harder in the
morning. Following setrefs.c gives me a headache at the best of times.

I've not addressed the join.sql regression test changes in the patch yet.

David

  5. Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1

    David Rowley <dgrowleyml@gmail.com> — 2021-05-24T03:47:23Z

    On Mon, 24 May 2021 at 00:58, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Mon, 24 May 2021 at 00:47, David Rowley <dgrowleyml@gmail.com> wrote:
> > I can look into this a bit more in the morning. It's not a good time
> > of day right now to debug setrefs.c
>
> It *may* just be a missing call to set_upper_references() for Result
> Cache. i.e. the attached. But I will need to look harder in the
> morning. Following setrefs.c gives me a headache at the best of times.

I've had a look at this today and I think the patch attached is what
needs to be done to fix the issue.

Hans, I see you're on Windows, but wondering if you have a build
environment around to test to see if this patch fixes the error for
your case?

David

  6. AW: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1

    Hans Buschmann <buschmann@nidsa.net> — 2021-05-24T08:52:43Z

    >Hans, I see you're on Windows, but wondering if you have a build
>environment around to test to see if this patch fixes the error for
>your case?

Thanks David for investigating.

I managed to manually patch setrefs.c according to your patchset on a Linux test installation.

The query/explain query (without the unneeded XML-stuff) runs fine now, no more error!

I will try to reactivate my Windows development environment, but the error seems not to be related to operating system version.

Many thanks for your quick reaction!

Hans Buschmann

  7. Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1

    David Rowley <dgrowleyml@gmail.com> — 2021-05-24T08:57:09Z

    On Mon, 24 May 2021 at 20:53, Hans Buschmann <buschmann@nidsa.net> wrote:
> I managed to manually patch setrefs.c according to your patchset on a Linux test installation.
>
> The query/explain query (without the unneeded XML-stuff) runs fine now, no more error!

Great.  I'll take another look at the patch in my morning (UTC+12) and
see about getting it in.

> Many thanks for your quick reaction!

Thanks for testing the patch.

David

  8. Re: BUG #17030: ERROR: cannot decompile join alias var in plan tree introduced in pg14beta1

    David Rowley <dgrowleyml@gmail.com> — 2021-05-25T00:52:40Z

    On Mon, 24 May 2021 at 20:57, David Rowley <dgrowleyml@gmail.com> wrote:
>
> > The query/explain query (without the unneeded XML-stuff) runs fine now, no more error!
>
> Great.  I'll take another look at the patch in my morning (UTC+12) and
> see about getting it in.

Pushed.

Thanks again for reporting.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cba5c70b956810c61b3778f7041f92fbb8065acb

David