Skip site navigation (1) Skip section navigation (2)

Re: BUG #5126: convert_to preventing index scan

From: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
To: peter_e(at)gmx(dot)net (Peter Eisentraut), Roman Kapusta <roman(dot)kapusta(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5126: convert_to preventing index scan
Date: 2009-10-22 08:20:32
Message-ID: 87zl7jlu0f.fsf@news-spur.riddles.org.uk (view raw or flat)
Thread:
Lists: pgsql-bugs
>>>>> "Peter" == Peter Eisentraut <peter_e(at)gmx(dot)net> writes:

 >> I have table with bytea column, which is indexed (1)
 >> I want to use index during pattern matching (eg. dir like
 >> someDirectoryName
 >> || '/%'), but concatenation of two strings cause error (2)
 >> So I have to use function convert_to (converting text to bytea), but
 >> this
 >> has awful explain plan (3)

 Peter> You haven't told us how the convert_to function is defined.

convert_to is a builtin function. If there's a bug here, it's that
convert_to is defined as stable rather than immutable. (Sure it depends
on server_encoding, but that can't exactly change... if there's any
other reason why it's not immutable, I can't think what it is.)

Example (5) from the original message is the correct approach in any
case; as long as either operand of the || is explicitly passed as, or
cast to, a bytea, then it should work.

-- 
Andrew (irc:RhodiumToad)

In response to

Responses

pgsql-bugs by date

Next:From: SavitaDate: 2009-10-22 12:55:01
Subject: BUG #5134: initdb fails with return code 1
Previous:From: Geok Hua YapDate: 2009-10-22 07:23:24
Subject: BUG #5132: Invalid Function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group