Bug in PostrgeSQL 8.0beta
Hello,
I downloaded and install postgresql-8.0.0beta3.tar.gz
I create the following table:
CREATE OR REPLACE FUNCTION "ayz"."my_lower" (text) RETURNS text AS'
BEGIN
return lower($1);
END;
'LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
CREATE TABLE public.art
(
art_id int4 NOT NULL,
art_name "varchar"(60),
art_costin "numeric"(15,2),
art_costout "numeric"(15,2),
rem "varchar"(60),
art_country_name "varchar"(25),
art_firm_name "varchar"(35),
art_price_name "varchar"(100) DEFAULT 'wwww'::character varying,
CONSTRAINT art_pkey PRIMARY KEY (art_id)
)
WITH OIDS;
CREATE INDEX art_art_name_idx
ON public.art
USING btree
(my_lower(art_name::text));
ALTER TABLE public.art CLUSTER ON art_art_name_idx;
Executing the following query return an error 'ERROR: unrecognized node type: 656'.
SELECT pg_get_expr(i.indexprs, i.indrelid) AS expr
FROM pg_index i
INNER JOIN pg_class c ON i.indrelid = c.oid
WHERE c.relname = 'art'
Expression tree is
({FUNCEXPR :funcid 4061872 :funcresulttype 25 :funcretset false :funcformat 0 :args ({RELABELTYPE :arg {VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 64 :varlevelsup 0 :varnoold 1 :varoattno 2} :resulttype 25 :resulttypmod -1 :relabelformat 1})})
Thanks,
Alexander
"Alexander Zhiltsov" <ayz@ems.ru> writes:
Executing the following query return an error 'ERROR: unrecognized node ty=
pe: 656'.
SELECT pg_get_expr(i.indexprs, i.indrelid) AS expr
FROM pg_index i
INNER JOIN pg_class c ON i.indrelid =3D c.oid
WHERE c.relname =3D 'art'
I'm not convinced this is really a bug, because pg_index.indexprs is not
an expression (it's a list of expressions) and so it's not clear that
pg_get_expr should be expected to work on it. What result were you
expecting to get?
regards, tom lane
On Fri, Oct 01, 2004 at 02:46:33PM +0600, Alexander Zhiltsov wrote:
Executing the following query return an error 'ERROR: unrecognized node type: 656'.
SELECT pg_get_expr(i.indexprs, i.indrelid) AS expr
FROM pg_index i
INNER JOIN pg_class c ON i.indrelid = c.oid
WHERE c.relname = 'art'Expression tree is
({FUNCEXPR :funcid 4061872 :funcresulttype 25 :funcretset false :funcformat 0 :args ({RELABELTYPE :arg {VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 64 :varlevelsup 0 :varnoold 1 :varoattno 2} :resulttype 25 :resulttypmod -1 :relabelformat 1})})
pg_get_expr() in PostgreSQL 8.0.0beta3 doesn't like the parentheses
that surround the expression. This query works for me:
SELECT pg_get_expr(trim(BOTH '()' FROM i.indexprs), i.indrelid) AS expr
FROM pg_index i
INNER JOIN pg_class c ON i.indrelid = c.oid
WHERE c.relname = 'art';
pg_get_expr() in PostgreSQL 7.4.5 works both ways.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
pg_get_expr() in PostgreSQL 8.0.0beta3 doesn't like the parentheses
that surround the expression. This query works for me:SELECT pg_get_expr(trim(BOTH '()' FROM i.indexprs), i.indrelid) AS expr
FROM pg_index i
INNER JOIN pg_class c ON i.indrelid = c.oid
WHERE c.relname = 'art';pg_get_expr() in PostgreSQL 7.4.5 works both ways.
Unfortunately 'trim(BOTH '()' FROM i.indexprs)' doesn't help for indices
that have more than one expression.
For example:
CREATE INDEX art_art_name_idx
ON public.art
USING btree
(my_lower(art_name::text), my_lower(art_firm_name::text));
Expression tree is
({FUNCEXPR :funcid 4061872 :funcresulttype 25 :funcretset false :funcformat
0 :args ({RELABELTYPE :arg {VAR :varno 1 :varattno 2 :vartype 1043
:vartypmod 64 :varlevelsup 0 :varnoold 1 :varoattno 2} :resulttype 25
:resulttypmod -1 :relabelformat 1})} {FUNCEXPR :funcid 4061872
:funcresulttype 25 :funcretset false :funcformat 0 :args ({RELABELTYPE :arg
{VAR :varno 1 :varattno 7 :vartype 1043 :vartypmod 39 :varlevelsup 0
:varnoold 1 :varoattno 7} :resulttype 25 :resulttypmod -1 :relabelformat
1})})
In this case pg_get_expr(trim(BOTH '()' FROM i.indexprs), i.indrelid)
returns only the first expression.
Server 7.4 returns the both that are combined by 'AND'.
Isn't it planned to implement the same behavior of the function in 8.0?
Alexander
----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Alexander Zhiltsov" <ayz@ems.ru>
Cc: <pgsql-bugs@postgresql.org>
Sent: Friday, October 01, 2004 9:23 PM
Subject: Re: [BUGS] Bug in PostrgeSQL 8.0beta
On Fri, Oct 01, 2004 at 02:46:33PM +0600, Alexander Zhiltsov wrote:
Executing the following query return an error 'ERROR: unrecognized node
type: 656'.
SELECT pg_get_expr(i.indexprs, i.indrelid) AS expr
FROM pg_index i
INNER JOIN pg_class c ON i.indrelid = c.oid
WHERE c.relname = 'art'Expression tree is
({FUNCEXPR :funcid 4061872 :funcresulttype 25 :funcretset false
:funcformat 0 :args ({RELABELTYPE :arg {VAR :varno 1 :varattno 2 :vartype
1043 :vartypmod 64 :varlevelsup 0 :varnoold 1 :varoattno 2} :resulttype 25
:resulttypmod -1 :relabelformat 1})})
Show quoted text
pg_get_expr() in PostgreSQL 8.0.0beta3 doesn't like the parentheses
that surround the expression. This query works for me:SELECT pg_get_expr(trim(BOTH '()' FROM i.indexprs), i.indrelid) AS expr
FROM pg_index i
INNER JOIN pg_class c ON i.indrelid = c.oid
WHERE c.relname = 'art';pg_get_expr() in PostgreSQL 7.4.5 works both ways.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Thank you for the response.
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
I'm not convinced this is really a bug, because pg_index.indexprs is not
an expression (it's a list of expressions) and so it's not clear that
pg_get_expr should be expected to work on it. What result were you
expecting to get?
Yes, probably it is not a bug, but only a difference in behavior of function
pg_get_expr in versions 7.4 and 8.0.
Server 7.4 returns readable representation of index expressions that are
combined by 'AND'.
Isn't it planned to implement the same behavior of the function in 8.0?
Alexander
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Alexander Zhiltsov" <ayz@ems.ru>
Cc: <pgsql-bugs@postgresql.org>
Sent: Friday, October 01, 2004 9:20 PM
Subject: Re: [BUGS] Bug in PostrgeSQL 8.0beta
"Alexander Zhiltsov" <ayz@ems.ru> writes:
Executing the following query return an error 'ERROR: unrecognized node
ty=
Show quoted text
pe: 656'.
SELECT pg_get_expr(i.indexprs, i.indrelid) AS expr
FROM pg_index i
INNER JOIN pg_class c ON i.indrelid =3D c.oid
WHERE c.relname =3D 'art'I'm not convinced this is really a bug, because pg_index.indexprs is not
an expression (it's a list of expressions) and so it's not clear that
pg_get_expr should be expected to work on it. What result were you
expecting to get?regards, tom lane
"Alexander Zhiltsov" <ayz@ems.ru> writes:
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
I'm not convinced this is really a bug, because pg_index.indexprs is not
an expression (it's a list of expressions) and so it's not clear that
pg_get_expr should be expected to work on it. What result were you
expecting to get?
Yes, probably it is not a bug, but only a difference in behavior of function
pg_get_expr in versions 7.4 and 8.0.
Server 7.4 returns readable representation of index expressions that are
combined by 'AND'.
Hmm, well that wasn't really the desired behavior either, since the index
expressions certainly aren't combined as though by AND.
I've fixed it so that 8.0 will return the expressions comma-separated,
which seems a reasonably sane behavior.
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
I've fixed it so that 8.0 will return the expressions comma-separated,
which seems a reasonably sane behavior.
Thank you.
Alexander
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Alexander Zhiltsov" <ayz@ems.ru>
Cc: <pgsql-bugs@postgresql.org>
Sent: Friday, October 08, 2004 2:39 AM
Subject: Re: [BUGS] Bug in PostrgeSQL 8.0beta
"Alexander Zhiltsov" <ayz@ems.ru> writes:
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
I'm not convinced this is really a bug, because pg_index.indexprs is
not
an expression (it's a list of expressions) and so it's not clear that
pg_get_expr should be expected to work on it. What result were you
expecting to get?Yes, probably it is not a bug, but only a difference in behavior of
function
Show quoted text
pg_get_expr in versions 7.4 and 8.0.
Server 7.4 returns readable representation of index expressions that are
combined by 'AND'.Hmm, well that wasn't really the desired behavior either, since the index
expressions certainly aren't combined as though by AND.I've fixed it so that 8.0 will return the expressions comma-separated,
which seems a reasonably sane behavior.regards, tom lane