Thread: Explain verbose query with CTE
Hi,
Regards,
Bartek
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
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
Regards,
Bartek
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
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