Lists: | pgsql-bugs |
---|
From: | "Markus Wichitill" <mawic(at)gmx(dot)de> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5364: citext behavior when type not in public schema |
Date: | 2010-03-05 10:24:24 |
Message-ID: | 201003051024.o25AOOEZ074123@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5364
Logged by: Markus Wichitill
Email address: mawic(at)gmx(dot)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)
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Markus Wichitill <mawic(at)gmx(dot)de> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5364: citext behavior when type not in public schema |
Date: | 2010-03-12 16:21:43 |
Message-ID: | 603c8f071003120821s48568846la4be4d7cace9456c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On Fri, Mar 5, 2010 at 5:24 AM, Markus Wichitill <mawic(at)gmx(dot)de> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 5364
> Logged by: Markus Wichitill
> Email address: mawic(at)gmx(dot)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
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Markus Wichitill <mawic(at)gmx(dot)de>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5364: citext behavior when type not in public schema |
Date: | 2010-06-03 03:05:12 |
Message-ID: | 201006030305.o5335Cn15945@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Robert Haas wrote:
> On Fri, Mar 5, 2010 at 5:24 AM, Markus Wichitill <mawic(at)gmx(dot)de> wrote:
> >
> > The following bug has been logged online:
> >
> > Bug reference: ? ? ?5364
> > Logged by: ? ? ? ? ?Markus Wichitill
> > Email address: ? ? ?mawic(at)gmx(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Attachment | Content-Type | Size |
---|---|---|
/rtmp/diff | text/x-diff | 821 bytes |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Markus Wichitill <mawic(at)gmx(dot)de>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5364: citext behavior when type not in public schema |
Date: | 2010-06-03 03:58:33 |
Message-ID: | 16647.1275537513@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Bruce Momjian <bruce(at)momjian(dot)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
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Markus Wichitill <mawic(at)gmx(dot)de>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5364: citext behavior when type not in public schema |
Date: | 2010-06-03 14:13:20 |
Message-ID: | 201006031413.o53EDKH01650@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
From: | Markus Wichitill <mawic(at)gmx(dot)de> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5364: citext behavior when type not in public schema |
Date: | 2010-06-03 15:23:28 |
Message-ID: | 4C07C8F0.1060108@gmx.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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.
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Markus Wichitill <mawic(at)gmx(dot)de> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5364: citext behavior when type not in public schema |
Date: | 2010-06-03 15:25:45 |
Message-ID: | 201006031525.o53FPjt22549@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +