BUG #13875: Error explaining query

travis.smith@iriworldwide.com writes:

=# explain select count(1) from (SELECT DISTINCT
"IRI_IT_M_2782_7472_O"."ATTR_VALUE", "IRI_IT_M_2782_7472_O"."SORT_ORDER",
"IT_DIM_IRI_7472"."M_2796_KEY", "IT_DIM_IRI_7472"."M_2782_KEY" FROM
"IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
("IT_DIM_IRI_7472"."S_848_KEY" = 4527492) AND
("IT_DIM_IRI_7472"."M_2782_KEY" = "IRI_IT_M_2782_7472_O"."AVP_KEY") ORDER BY
"IT_DIM_IRI_7472"."M_2796_KEY" ASC, "IRI_IT_M_2782_7472_O"."SORT_ORDER" ASC,
"IT_DIM_IRI_7472"."M_2782_KEY" ASC ) t;

ERROR: variable not found in subplan target list

What I get is

ERROR: relation "IT_DIM_IRI_7472" does not exist
LINE 4: "IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
^

This does indeed sound like a bug, but without a self-contained
test case, there's not a lot we can do about it.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Tue, Jan 19, 2016 at 1:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

This does indeed sound like a bug, but without a self-contained
test case, there's not a lot we can do about it.

Yeah, I was flattening this test case before you sent this email,
coming with the attached:
create table aa1 (s1 int, m1 int, m2 int);
create table aa2 (key int, attr int, sort int);
explain select count(1) from (SELECT DISTINCT
aa2.attr, aa2.sort,
aa1.m1, aa1.m2 FROM
aa1, aa2 WHERE
(aa1.s1 = 4527492) AND
(aa1.m2 = aa2.key) ORDER BY
aa1.m1 ASC, aa2.sort ASC,
aa1.m2 ASC) t;
But this is proving to work. There is not much doable here without a
self-contained test case...
--
Michael

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

HI Tom,

Sounds good. I would like to help with the self-contained case. What do you need from me?

Thank you,
Travis

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, January 18, 2016 10:18 PM
To: Smith, Travis
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #13875: Error explaining query

travis.smith@iriworldwide.com writes:

=# explain select count(1) from (SELECT DISTINCT
"IRI_IT_M_2782_7472_O"."ATTR_VALUE",
"IRI_IT_M_2782_7472_O"."SORT_ORDER",
"IT_DIM_IRI_7472"."M_2796_KEY", "IT_DIM_IRI_7472"."M_2782_KEY" FROM
"IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
("IT_DIM_IRI_7472"."S_848_KEY" = 4527492) AND
("IT_DIM_IRI_7472"."M_2782_KEY" = "IRI_IT_M_2782_7472_O"."AVP_KEY")
ORDER BY "IT_DIM_IRI_7472"."M_2796_KEY" ASC,
"IRI_IT_M_2782_7472_O"."SORT_ORDER" ASC,
"IT_DIM_IRI_7472"."M_2782_KEY" ASC ) t;

ERROR: variable not found in subplan target list

What I get is

ERROR: relation "IT_DIM_IRI_7472" does not exist LINE 4: "IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
^

This does indeed sound like a bug, but without a self-contained test case, there's not a lot we can do about it.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Tue, Jan 19, 2016 at 7:22 AM, Smith, Travis <
Travis.Smith@iriworldwide.com> wrote:

HI Tom,

Sounds good. I would like to help with the self-contained case. What
do you need from me?

Thank you,
Travis

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, January 18, 2016 10:18 PM
To: Smith, Travis
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #13875: Error explaining query

travis.smith@iriworldwide.com writes:

=# explain select count(1) from (SELECT DISTINCT
"IRI_IT_M_2782_7472_O"."ATTR_VALUE",
"IRI_IT_M_2782_7472_O"."SORT_ORDER",
"IT_DIM_IRI_7472"."M_2796_KEY", "IT_DIM_IRI_7472"."M_2782_KEY" FROM
"IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
("IT_DIM_IRI_7472"."S_848_KEY" = 4527492) AND
("IT_DIM_IRI_7472"."M_2782_KEY" = "IRI_IT_M_2782_7472_O"."AVP_KEY")
ORDER BY "IT_DIM_IRI_7472"."M_2796_KEY" ASC,
"IRI_IT_M_2782_7472_O"."SORT_ORDER" ASC,
"IT_DIM_IRI_7472"."M_2782_KEY" ASC ) t;

ERROR: variable not found in subplan target list

What I get is

ERROR: relation "IT_DIM_IRI_7472" does not exist LINE 4:
"IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
^

​Ideally, something like:

WITH data_cte AS (
​VALUES (), (), (), ...​
)​
SELECT *
FROM data_cte

Where the main select causes the relevant error.

Otherwise a "psql" script file with the various and sundry CREATE TABLE,
CREATE INDEX, INSERT, SELECT statements that, when fed into "psql", cause
the error.

David J.

This table is using the Columnar store extension. This worked in 9.5 beta like a charm, I upgrade and now this. I can provide script and data. Any limit on file sizes?

Thank you,
Travis

From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Tuesday, January 19, 2016 8:35 AM
To: Smith, Travis
Cc: Tom Lane; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #13875: Error explaining query

On Tue, Jan 19, 2016 at 7:22 AM, Smith, Travis <Travis.Smith@iriworldwide.com<mailto:Travis.Smith@iriworldwide.com>> wrote:
HI Tom,

Sounds good. I would like to help with the self-contained case. What do you need from me?

Thank you,
Travis

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>]
Sent: Monday, January 18, 2016 10:18 PM
To: Smith, Travis
Cc: pgsql-bugs@postgresql.org<mailto:pgsql-bugs@postgresql.org>
Subject: Re: [BUGS] BUG #13875: Error explaining query

travis.smith@iriworldwide.com<mailto:travis.smith@iriworldwide.com> writes:

=# explain select count(1) from (SELECT DISTINCT
"IRI_IT_M_2782_7472_O"."ATTR_VALUE",
"IRI_IT_M_2782_7472_O"."SORT_ORDER",
"IT_DIM_IRI_7472"."M_2796_KEY", "IT_DIM_IRI_7472"."M_2782_KEY" FROM
"IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
("IT_DIM_IRI_7472"."S_848_KEY" = 4527492) AND
("IT_DIM_IRI_7472"."M_2782_KEY" = "IRI_IT_M_2782_7472_O"."AVP_KEY")
ORDER BY "IT_DIM_IRI_7472"."M_2796_KEY" ASC,
"IRI_IT_M_2782_7472_O"."SORT_ORDER" ASC,
"IT_DIM_IRI_7472"."M_2782_KEY" ASC ) t;

ERROR: variable not found in subplan target list

What I get is

ERROR: relation "IT_DIM_IRI_7472" does not exist LINE 4: "IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
^

​Ideally, something like:

WITH data_cte AS (
​VALUES (), (), (), ...​
)​
SELECT *
FROM data_cte

Where the main select causes the relevant error.

Otherwise a "psql" script file with the various and sundry CREATE TABLE, CREATE INDEX, INSERT, SELECT statements that, when fed into "psql", cause the error.

David J.

"Smith, Travis" <Travis.Smith@IRIWorldwide.com> writes:

This table is using the Columnar store extension.

Oh. In that case, the very first thing you should do is inquire whether
that extension is known compatible with 9.5.0. Better yet, try to
reproduce the problem without that extension.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

HI Tom,

I am doing that now. I was planning a reply, thankfully enough it appears a release was done recently.

Thank you,
Travis

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, January 19, 2016 11:09 AM
To: Smith, Travis
Cc: David G. Johnston; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #13875: Error explaining query

"Smith, Travis" <Travis.Smith@IRIWorldwide.com> writes:

This table is using the Columnar store extension.

Oh. In that case, the very first thing you should do is inquire whether that extension is known compatible with 9.5.0. Better yet, try to reproduce the problem without that extension.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Thanks guys. Problem resolved new extension released a few days ago. Sorry to bother.

Travis

From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Tuesday, January 19, 2016 8:35 AM
To: Smith, Travis
Cc: Tom Lane; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #13875: Error explaining query

On Tue, Jan 19, 2016 at 7:22 AM, Smith, Travis <Travis.Smith@iriworldwide.com<mailto:Travis.Smith@iriworldwide.com>> wrote:
HI Tom,

Sounds good. I would like to help with the self-contained case. What do you need from me?

Thank you,
Travis

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>]
Sent: Monday, January 18, 2016 10:18 PM
To: Smith, Travis
Cc: pgsql-bugs@postgresql.org<mailto:pgsql-bugs@postgresql.org>
Subject: Re: [BUGS] BUG #13875: Error explaining query

travis.smith@iriworldwide.com<mailto:travis.smith@iriworldwide.com> writes:

=# explain select count(1) from (SELECT DISTINCT
"IRI_IT_M_2782_7472_O"."ATTR_VALUE",
"IRI_IT_M_2782_7472_O"."SORT_ORDER",
"IT_DIM_IRI_7472"."M_2796_KEY", "IT_DIM_IRI_7472"."M_2782_KEY" FROM
"IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
("IT_DIM_IRI_7472"."S_848_KEY" = 4527492) AND
("IT_DIM_IRI_7472"."M_2782_KEY" = "IRI_IT_M_2782_7472_O"."AVP_KEY")
ORDER BY "IT_DIM_IRI_7472"."M_2796_KEY" ASC,
"IRI_IT_M_2782_7472_O"."SORT_ORDER" ASC,
"IT_DIM_IRI_7472"."M_2782_KEY" ASC ) t;

ERROR: variable not found in subplan target list

What I get is

ERROR: relation "IT_DIM_IRI_7472" does not exist LINE 4: "IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
^

​Ideally, something like:

WITH data_cte AS (
​VALUES (), (), (), ...​
)​
SELECT *
FROM data_cte

Where the main select causes the relevant error.

Otherwise a "psql" script file with the various and sundry CREATE TABLE, CREATE INDEX, INSERT, SELECT statements that, when fed into "psql", cause the error.

David J.