Обсуждение: Explain query
Hi,
Regards,
Bartek
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)'
I am not sure this is pgAdmin issue, but I think it is good start point.
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
On Wed, 2012-04-18 at 22:51 +0200, Bartosz Dmytrak wrote:
> Hi,
> 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)'
>
> I am not sure this is pgAdmin issue, but I think it is good start point.
>
> 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
>
I tried on PostgreSQL 9.2dev, and I get the same error without using
pgAdmin. So the problem is within PostgreSQL, not pgAdmin. I only have
the error if I use the VERBOSE option. EXPLAIN, and EXPLAIN ANALYZE
without verbose work great. So I guess you should complain on
pgsql-general.
BTW, your query is kinda weird to me. You declare a CTE named t that you
do not use. And it still gets executed. Kinda puzzling.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
2012/4/20 Guillaume Lelarge <guillaume@lelarge.info>
pgAdmin. So the problem is within PostgreSQL, not pgAdmin. I only haveI tried on PostgreSQL 9.2dev, and I get the same error without using
the error if I use the VERBOSE option. EXPLAIN, and EXPLAIN ANALYZE
without verbose work great. So I guess you should complain on
pgsql-general.
sure - thanks for Your time.
BTW, your query is kinda weird to me. You declare a CTE named t that you
do not use. And it still gets executed. Kinda puzzling.
this was only POC, I am going to use CTE (t) with update, but I faced this problem and then I simplified the query as much as possible.
Regards,
Bartek
On Fri, 2012-04-20 at 22:10 +0200, Bartosz Dmytrak wrote: > > 2012/4/20 Guillaume Lelarge <guillaume@lelarge.info> > I tried on PostgreSQL 9.2dev, and I get the same error without > using > pgAdmin. So the problem is within PostgreSQL, not pgAdmin. I > only have > the error if I use the VERBOSE option. EXPLAIN, and EXPLAIN > ANALYZE > without verbose work great. So I guess you should complain on > pgsql-general. > sure - thanks for Your time. > I've read your post on pgsql-general. I'm interested to see the answers. I might learn something new, which is cool :) -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com