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: 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
...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. +
Attachments:
/rtmp/difftext/x-diffDownload+9-0
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. +