Thread: BUG #5364: citext behavior when type not in public schema

BUG #5364: citext behavior when type not in public schema

From
"Markus Wichitill"
Date:
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)

Re: BUG #5364: citext behavior when type not in public schema

From
Robert Haas
Date:
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

Re: BUG #5364: citext behavior when type not in public schema

From
Bruce Momjian
Date:
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>


Re: BUG #5364: citext behavior when type not in public schema

From
Tom Lane
Date:
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

Re: BUG #5364: citext behavior when type not in public schema

From
Bruce Momjian
Date:
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. +

Re: BUG #5364: citext behavior when type not in public schema

From
Markus Wichitill
Date:
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.

Re: BUG #5364: citext behavior when type not in public schema

From
Bruce Momjian
Date:
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. +