"Alexander Zhiltsov" <ayz (AT) ems (DOT) ru> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Fri, Oct 01, 2004 at 02:46:33PM +0600, Alexander Zhiltsov wrote:

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/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Alexander Zhiltsov" <ayz (AT) ems (DOT) ru> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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 (AT) fuhr (DOT) org>
To: "Alexander Zhiltsov" <ayz (AT) ems (DOT) ru>
Cc: <pgsql-bugs (AT) postgresql (DOT) org>
Sent: Friday, October 01, 2004 9:23 PM
Subject: Re: [BUGS] Bug in PostrgeSQL 8.0beta


1043 :vartypmod 64 :varlevelsup 0 :varnoold 1 :varoattno 2} :resulttype 25
:resulttypmod -1 :relabelformat 1})})

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Thank you.


Alexander

----- Original Message -----
From: "Tom Lane" <tgl (AT) sss (DOT) pgh.pa.us>
To: "Alexander Zhiltsov" <ayz (AT) ems (DOT) ru>
Cc: <pgsql-bugs (AT) postgresql (DOT) org>
Sent: Friday, October 08, 2004 2:39 AM
Subject: Re: [BUGS] Bug in PostrgeSQL 8.0beta



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Thank you for the response.

"Tom Lane" <tgl (AT) sss (DOT) pgh.pa.us> wrote:

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 (AT) sss (DOT) pgh.pa.us>
To: "Alexander Zhiltsov" <ayz (AT) ems (DOT) ru>
Cc: <pgsql-bugs (AT) postgresql (DOT) org>
Sent: Friday, October 01, 2004 9:20 PM
Subject: Re: [BUGS] Bug in PostrgeSQL 8.0beta



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org