Thread: Bug in PostrgeSQL 8.0beta

Bug in PostrgeSQL 8.0beta

From

"Alexander Zhiltsov"

Date:

01 October 2004, 17:27:46

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)
)=20
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 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'

Expression tree is
({FUNCEXPR :funcid 4061872 :funcresulttype 25 :funcretset false :funcformat=
 0 :args ({RELABELTYPE :arg {VAR :varno 1 :varattno 2 :vartype 1043 :vartyp=
mod 64 :varlevelsup 0 :varnoold 1 :varoattno 2} :resulttype 25 :resulttypmo=
d -1 :relabelformat 1})})

=20
Thanks,

Alexander

Re: Bug in PostrgeSQL 8.0beta

From

Tom Lane

Date:

01 October 2004, 19:20:20

"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

Re: Bug in PostrgeSQL 8.0beta

From

Michael Fuhr

Date:

01 October 2004, 19:23:31

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
:varattno2 :vartype 1043 :vartypmod 64 :varlevelsup 0 :varnoold 1 :varoattno 2} :resulttype 25 :resulttypmod -1
:relabelformat1})}) 

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/

Re: Bug in PostrgeSQL 8.0beta

From

Tom Lane

Date:

08 October 2004, 00:39:39

"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

Re: Bug in PostrgeSQL 8.0beta

From

"Alexander Zhiltsov"

Date:

10 October 2004, 22:45:49

> 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})})
>
> 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/

Re: Bug in PostrgeSQL 8.0beta

From

"Alexander Zhiltsov"

Date:

10 October 2004, 22:46:01

> "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
> > 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

Re: Bug in PostrgeSQL 8.0beta

From

"Alexander Zhiltsov"

Date:

10 October 2004, 22:47:03

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=
> > 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