Explain verbose query with CTE

Started by Bartosz Dmytrakalmost 14 years ago5 messagesgeneral
Jump to latest
#1Bartosz Dmytrak
bdmytrak@gmail.com

Hi,
This e-mail is reposted form pgadmin support mailing list. This problem
looks like related with postgres not pgAdmin.

I faced strange problem (strange for me):
I have written code:

WITH t as (
INSERT INTO "tblD1" (id, "Data1")
VALUES ('a', 123)
RETURNING *)

UPDATE "tblBase"
SET "SomeData" = 123
WHERE id = 'a';

this code operates on simple tables:
CREATE TABLE "tblBase"(
id text NOT NULL,
"SomeData" integer,
CONSTRAINT "tblBase_pkey" PRIMARY KEY (id )
)
WITH (OIDS=FALSE);

and

CREATE TABLE "tblD1" (
id text NOT NULL,
"Data1" integer,
ser serial NOT NULL,
CONSTRAINT "tblD1_pkey" PRIMARY KEY (id ),
CONSTRAINT "tblD1_id_fkey" FOREIGN KEY (id)
REFERENCES "tblBase" (id) MATCH FULL
ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
)
WITH (OIDS=FALSE);

in table "tblBase" two record exist:

id ; "SomeData"
'3';345
'a';1

i tried to use Explain query functionality and then I received a message:
Query result with 0 rows will be returned.
ERROR: cache lookup failed for attribute 3 of relation 38264
********** Error **********
ERROR: cache lookup failed for attribute 3 of relation 38264
SQL state: XX000

interesting thing is that execution of this code works as expected, also
EXPLAIN and EXPLAIN ANALYZE gives proper response:
eg. EXPLAIN:
'Update on "tblBase" (cost=0.01..1.04 rows=1 width=38)'
' CTE t'
' -> Insert on "tblD1" (cost=0.00..0.01 rows=1 width=0)'
' -> Result (cost=0.00..0.01 rows=1 width=0)'
' -> Seq Scan on "tblBase" (cost=0.00..1.02 rows=1 width=38)'
' Filter: (id = 'a'::text)'

SELECT relname FROM pg_class WHERE oid = 38264;
gives "tblBase"

pg log lines look like this:
2012-04-18 22:44:25 CEST|myDatabase|postgres|pgAdmin III - Query
Tool|myHost(59562)|6828LOG: statement: EXPLAIN (ANALYZE off, VERBOSE on,
COSTS on, BUFFERS off )WITH t as (
INSERT INTO "tblDerived1" (id, "Data1")
VALUES ('a', 123)
RETURNING *)
UPDATE "tblBase"
SET "SomeData" = (SELECT ser FROM t)
WHERE id = (SELECT id FROM t)
2012-04-18 22:44:25 CEST|myDatabase|postgres|pgAdmin III - Query
Tool|myHost(59562)|6828ERROR: cache lookup failed for attribute 3 of
relation 38264
2012-04-18 22:44:25 CEST|myDatabase|postgres|pgAdmin III - Query
Tool|myHost(59562)|6828STATEMENT: EXPLAIN (ANALYZE off, VERBOSE on, COSTS
on, BUFFERS off )WITH t as (
INSERT INTO "tblDerived1" (id, "Data1")
VALUES ('a', 123)
RETURNING *)
UPDATE "tblBase"
SET "SomeData" = (SELECT ser FROM t)
WHERE id = (SELECT id FROM t)

I think maybe VERBOSE option is a problem, but not sure.

environment:
pgAdmin 1.14.2 (Mandriva Linux 64 bit)
PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-46), 64-bit

Thanks in advance for any help.

Regards,
Bartek

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bartosz Dmytrak (#1)
Re: Explain verbose query with CTE

Bartosz Dmytrak <bdmytrak@gmail.com> writes:

This e-mail is reposted form pgadmin support mailing list. This problem
looks like related with postgres not pgAdmin.
[ EXPLAIN VERBOSE yields a "cache lookup failed" error ]

Huh, yeah, that's a backend bug all right. I thought at first you might
have a catalog-corruption problem, but I can reproduce the failure here
from your example. Most likely EXPLAIN is getting confused about which
Var belongs to which table. Will look into it.

regards, tom lane

#3Bartosz Dmytrak
bdmytrak@gmail.com
In reply to: Tom Lane (#2)
Re: Explain verbose query with CTE

2012/4/20 Tom Lane <tgl@sss.pgh.pa.us>

Will look into it.

Thanks again for Your time :)

Regards,
Bartek

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bartosz Dmytrak (#1)
Re: Explain verbose query with CTE

Bartosz Dmytrak <bdmytrak@gmail.com> writes:

[ EXPLAIN VERBOSE fails for ]
WITH t as (
INSERT INTO "tblD1" (id, "Data1")
VALUES ('a', 123)
RETURNING *)
UPDATE "tblBase"
SET "SomeData" = 123
WHERE id = 'a';

I've applied a patch for this. Thanks for the report!

regards, tom lane

#5Bartosz Dmytrak
bdmytrak@gmail.com
In reply to: Tom Lane (#4)
Re: Explain verbose query with CTE

2012/4/26 Tom Lane <tgl@sss.pgh.pa.us>

I've applied a patch for this. Thanks for the report!

regards, tom lane

Thanks for Your time :)

Regards,
Bartek