Обсуждение: Bug: citext not working in non-public schema

Researching a nasty bug discovered in our application led me to this bug 
report and its follow-ups: 
http://archives.postgresql.org/pgsql-bugs/2010-03/msg00058.php

This bit us hard (on PostgreSQL 8.4.4). We have a custom domain for 
email addresses based on citext, placed in the public schema, while each 
user of our application has their own private schemas. The search path 
is set to their private schemas, and the few queries which required 
explicit access to the type prefixes the type with the public schema, 
i.e. WHERE 'text@example.com'::public.email = email_column.

This *seems* to work, in that no error or warning is thrown, but 
comparisons are simply silently wrong. This led to a nasty bug in our 
application as it broke our expectation that the database should enforce 
this case-insensitive checks.

Any possibility of getting this fixed? Obliviously I would prefer citext 
to work as advertised across schemas. If not, an out-right error thrown 
would be much better and consistent than the current situation.

Regards,
-- anders

Anders Steinlein <anders@steinlein.no> writes:
> http://archives.postgresql.org/pgsql-bugs/2010-03/msg00058.php

> This bit us hard (on PostgreSQL 8.4.4). We have a custom domain for 
> email addresses based on citext, placed in the public schema, while each 
> user of our application has their own private schemas. The search path 
> is set to their private schemas, and the few queries which required 
> explicit access to the type prefixes the type with the public schema, 
> i.e. WHERE 'text@example.com'::public.email = email_column.

Put the citext stuff in a schema that *is* in everyone's search path.
It doesn't have to be "public", but it does have to be visible.

> Any possibility of getting this fixed? Obliviously I would prefer citext 
> to work as advertised across schemas. If not, an out-right error thrown 
> would be much better and consistent than the current situation.

The only way to have an error be thrown would be if you remove the
implicit cast from citext to text ... which is going to be a far worse
notational pain in the rear than fixing your search_path.
        regards, tom lane