Thread: BUG #5364: citext behavior when type not in public schema
The following bug has been logged online:
Bug reference: 5364
Logged by: Markus Wichitill
Email address: mawic@gmx.de
PostgreSQL version: 8.4.2
Operating system: Linux, Win7
Description: citext behavior when type not in public schema
Details:
Comparisons with columns of type citext silently work case-sensitively
without any error message, unless the search_path contains "public", even if
the type is not located in "public", but in the same schema as the table
using it.
I don't know if this is a bug or if it's specific to citext, but it's
surprising behavior.
shell> psql template1 pgsql
template1=# CREATE DATABASE db;
CREATE DATABASE
template1=# \c db
psql (8.4.2)
You are now connected to database "db".
db=# \i /usr/local/pgsql/share/contrib/citext.sql
SET
CREATE TYPE
[...]
db=# CREATE SCHEMA sch;
CREATE SCHEMA
db=# ALTER TYPE citext SET SCHEMA sch;
ALTER TYPE
db=# SET SCHEMA 'sch';
SET
db=# CREATE TABLE tbl (col citext);
CREATE TABLE
db=# INSERT INTO tbl (col) VALUES ('val');
INSERT 0 1
db=# SELECT col FROM tbl WHERE col = 'VaL';
col
-----
(0 rows)
db=# SET search_path = sch, public;
SET
db=# SELECT col FROM tbl WHERE col = 'VaL';
col
-----
val
(1 row)
On Fri, Mar 5, 2010 at 5:24 AM, Markus Wichitill <mawic@gmx.de> wrote: > > The following bug has been logged online: > > Bug reference: =A0 =A0 =A05364 > Logged by: =A0 =A0 =A0 =A0 =A0Markus Wichitill > Email address: =A0 =A0 =A0mawic@gmx.de > PostgreSQL version: 8.4.2 > Operating system: =A0 Linux, Win7 > Description: =A0 =A0 =A0 =A0citext behavior when type not in public schema > Details: > > Comparisons with columns of type citext silently work case-sensitively > without any error message, unless the search_path contains "public", even= if > the type is not located in "public", but in the same schema as the table > using it. Interestingly we recently got another report of this same problem. Tom did some analysis of it here: http://archives.postgresql.org/pgsql-bugs/2010-03/msg00017.php ...Robert
Robert Haas wrote: > On Fri, Mar 5, 2010 at 5:24 AM, Markus Wichitill <mawic@gmx.de> wrote: > > > > The following bug has been logged online: > > > > Bug reference: ? ? ?5364 > > Logged by: ? ? ? ? ?Markus Wichitill > > Email address: ? ? ?mawic@gmx.de > > PostgreSQL version: 8.4.2 > > Operating system: ? Linux, Win7 > > Description: ? ? ? ?citext behavior when type not in public schema > > Details: > > > > Comparisons with columns of type citext silently work case-sensitively > > without any error message, unless the search_path contains "public", even if > > the type is not located in "public", but in the same schema as the table > > using it. > > Interestingly we recently got another report of this same problem. > Tom did some analysis of it here: > > http://archives.postgresql.org/pgsql-bugs/2010-03/msg00017.php I have documented this citext limitation with the attached, applied patch. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: doc/src/sgml/citext.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/citext.sgml,v retrieving revision 1.2 diff -c -c -r1.2 citext.sgml *** doc/src/sgml/citext.sgml 12 Sep 2008 18:29:49 -0000 1.2 --- doc/src/sgml/citext.sgml 3 Jun 2010 03:02:48 -0000 *************** *** 205,210 **** --- 205,219 ---- will need two indexes if you want both types of searches to be fast. </para> </listitem> + + <listitem> + <para> + The schema containing the <type>citext</> operators must be + in the current <varname>search_path</> (typically <literal>public</>); + if it is not, a normal case-sensitive <type>text</> comparison + is performed. + </para> + </listitem> </itemizedlist> </sect2>
Bruce Momjian <bruce@momjian.us> writes:
> I have documented this citext limitation with the attached, applied
> patch.
Are you planning to insert similar verbiage into every other contrib
module's docs?
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > I have documented this citext limitation with the attached, applied > > patch. > > Are you planning to insert similar verbiage into every other contrib > module's docs? Uh, do they all have this odd behavior? Most people assume they would get an error in such cases, not case-sensitivity. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
On 03.06.2010 05:05, Bruce Momjian wrote: > The schema containing the <type>citext</> operators must be > in the current <varname>search_path</> (typically <literal>public</>); It's been a while, but the way I read my own example is that the schema containing the citext operators being in the current search_path isn't enough. "public" must be in the search_path, too, even if it's not really involved.
Markus Wichitill wrote: > On 03.06.2010 05:05, Bruce Momjian wrote: > > The schema containing the <type>citext</> operators must be > > in the current <varname>search_path</> (typically <literal>public</>); > > It's been a while, but the way I read my own example is that the schema > containing the citext operators being in the current search_path isn't > enough. "public" must be in the search_path, too, even if it's not > really involved. Uh, that doesn't make any sense because there is nothing special about 'public'. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +