Custom Domain; migration from 8.4 to 9.1 and COLLATE

This is a cross post from: http://dba.stackexchange.com/q/17609/2639

Just recently I upgraded from Postgresql 8.4.11 to 9.1.3. I used
pg_dump in the process. Now I'm getting an error:

> ERROR: no collation was derived for column "vin" with collatable type citext
> HINT: Use the COLLATE clause to set the collation explicitly.

This is a continuation of the [problem that I had
earlier](http://dba.stackexchange.com/q/17604/2639). It seems the fix
suggested in the Release Notes did not catch [custom
domains](http://www.postgresql.org/docs/9.1/interactive/sql-createdomain.html).
It seems as if the [CREATE
DOMAIN](http://www.postgresql.org/docs/8.3/interactive/sql-createdomain.html)
statement in 8.4 didn't even support the `COLLATE` clause.

This is how I created the `vin` type,

CREATE DOMAIN inventory.valid_vin AS citext
CHECK ( inventory.valid_vin( VALUE ) );

How do I best resolve this error?

--
Evan Carroll - me(at)evancarroll(dot)com
System Lord of the Internets
web: http://www.evancarroll.com
ph: 281.901.0011

Evan Carroll <me(at)evancarroll(dot)com> writes:
> This is a cross post from: http://dba.stackexchange.com/q/17609/2639
> Just recently I upgraded from Postgresql 8.4.11 to 9.1.3. I used
> pg_dump in the process. Now I'm getting an error:

>> ERROR: no collation was derived for column "vin" with collatable type citext
>> HINT: Use the COLLATE clause to set the collation explicitly.

Could we see the complete context for this?

regards, tom lane

> Could we see the complete context for this?

Sure.
dealermade=# CREATE OR REPLACE TEMP VIEW chrome_vinmatch_best_match AS
dealermade-# SELECT DISTINCT ON (v.vin) v.vin, vd.*
dealermade-# FROM inventory.view_in_stock_vehicles AS v
dealermade-# JOIN chrome_vinmatch.view_vin_decode AS vd
dealermade-# ON substring(v.vin FROM 0 FOR 9) =
substring(vd.pattern FROM 0 FOR 9)
dealermade-# AND v.vin LIKE vd.pattern
dealermade-# ORDER BY vin, length(pattern) DESC
dealermade-# ;
ERROR: no collation was derived for column "vin" with collatable type citext
HINT: Use the COLLATE clause to set the collation explicitly.

v.vin is the column with the custom DOMAIN.

--
Evan Carroll - me(at)evancarroll(dot)com
System Lord of the Internets
web: http://www.evancarroll.com
ph: 281.901.0011

Evan Carroll <me(at)evancarroll(dot)com> writes:
>> Could we see the complete context for this?
> Sure.
> dealermade=# CREATE OR REPLACE TEMP VIEW chrome_vinmatch_best_match AS
> dealermade-# SELECT DISTINCT ON (v.vin) v.vin, vd.*
> dealermade-# FROM inventory.view_in_stock_vehicles AS v
> dealermade-# JOIN chrome_vinmatch.view_vin_decode AS vd
> dealermade-# ON substring(v.vin FROM 0 FOR 9) =
> substring(vd.pattern FROM 0 FOR 9)
> dealermade-# AND v.vin LIKE vd.pattern
> dealermade-# ORDER BY vin, length(pattern) DESC
> dealermade-# ;
> ERROR: no collation was derived for column "vin" with collatable type citext
> HINT: Use the COLLATE clause to set the collation explicitly.

> v.vin is the column with the custom DOMAIN.

Hm, this example works fine for me in 9.1 branch tip, and I see no
relevant-looking patches in the commit logs since 9.1.3. What I suspect
is that you are being bit by the failure of 9.1.0 or 9.1.1 to set
pg_type.typcollation for the citext data type, as per this item in the
9.1.2 release notes:

Make contrib/citext's upgrade script fix collations of citext columns
and indexes (Tom Lane)

Existing citext columns and indexes aren't correctly marked as being
of a collatable data type during pg_upgrade from a pre-9.1
server. That leads to operations on them failing with errors such as
"could not determine which collation to use for string
comparison". This change allows them to be fixed by the same script
that upgrades the citext module into a proper 9.1 extension during
CREATE EXTENSION citext FROM unpackaged.

If you have a previously-upgraded database that is suffering from this
problem, and you already ran the CREATE EXTENSION command, you can
manually run (as superuser) the UPDATE commands found at the end of
SHAREDIR/extension/citext--unpackaged--1.0.sql. (Run pg_config
--sharedir if you're uncertain where SHAREDIR is.)

regards, tom lane

I did that, and I had to do that to get the error I pasted. I am hit
by that bug. I get this error after I fix that error. Here we my post
about the issue that you just mentioned:

* http://dba.stackexchange.com/q/17604/2639

BTW, The database version is 9.1.3. I'll try and work on a test that
generates this same error, not exactly sure why it is getting
generated though.

--
Evan Carroll - me(at)evancarroll(dot)com
System Lord of the Internets
web: http://www.evancarroll.com
ph: 281.901.0011

Evan Carroll <me(at)evancarroll(dot)com> writes:
> BTW, The database version is 9.1.3. I'll try and work on a test that
> generates this same error, not exactly sure why it is getting
> generated though.

Also see whether you can reproduce the error in a fresh database.
I continue to think the problem is an incorrect collation value in
some system catalog entry; if that's it, nobody will be able to
reproduce it. You might try checking to see that there are no
un-updated rows matching those fixup queries.

regards, tom lane

> Also see whether you can reproduce the error in a fresh database.
> I continue to think the problem is an incorrect collation value in
> some system catalog entry; if that's it, nobody will be able to
> reproduce it.  You might try checking to see that there are no
> un-updated rows matching those fixup queries.

I've been able to reproduce it in a fresh database. This is a PSQL
script 2a and 2b will fail.

\echo CREATING DOMAIN footype

CREATE DOMAIN footype AS citext;

\echo [1a] CREATING TABLE tablefoo_before (contains columns bar, type footype)

CREATE TABLE tablefoo_before ( bar footype );

\echo [1b] CREATING TEMP TABLE trash AS SELECT * FROM tablefoo_before

CREATE TEMP TABLE trash AS SELECT * FROM tablefoo_before ;

\echo RUNING PATCH TO UPDATE citext

UPDATE pg_catalog.pg_type SET typcollation = 100
WHERE oid = 'citext'::pg_catalog.regtype;

UPDATE pg_catalog.pg_attribute SET attcollation = 100
WHERE atttypid = 'citext'::pg_catalog.regtype;

\echo [2a] CREATING TABLE tablefoo_after (contains columns bar, type footype)

CREATE TABLE tablefoo_after ( bar footype );

\echo [2b] CREATING TEMP TABLE trash2 AS SELECT * FROM tablefoo_before

CREATE TEMP TABLE trash2 AS SELECT * FROM tablefoo_before ;

--
Evan Carroll - me(at)evancarroll(dot)com
System Lord of the Internets
web: http://www.evancarroll.com
ph: 281.901.0011

I think I can best get around this, if I issue a

> CREATE EXTENSION citext;

And, then load the database with the result of pg_dump. It seems to be
working, but there are some citext related statements from the dump
that fail because the stuff is already there in the DB when you issue
the CREATE EXTENSION.

--
Evan Carroll - me(at)evancarroll(dot)com
System Lord of the Internets
web: http://www.evancarroll.com
ph: 281.901.0011

Evan Carroll <me(at)evancarroll(dot)com> writes:
> I've been able to reproduce it in a fresh database. This is a PSQL
> script 2a and 2b will fail.

Doesn't reproduce for me. I guess one question is how you are loading
citext into the "fresh" database --- maybe you are inheriting a bum copy
from template1?

But anyway, looking at this example makes me realize that there is an
oversight in the recommended update script: it does not consider the
possibility that it needs to fix domains over citext. Try doing
the updates with target type name equal to each such domain you have.

regards, tom lane

> Doesn't reproduce for me.  I guess one question is how you are loading
> citext into the "fresh" database --- maybe you are inheriting a bum copy
> from template1?

That doesn't seem likely but it's possible. How can I tell?

Also, here is a copy of the complete script -- including the citext
creation statements from the dump, with the patch, with the bugged
statements.

https://gist.github.com/2656537

I'll reload the database the otherway and try to update the domain
with the same update statements.

--
Evan Carroll - me(at)evancarroll(dot)com
System Lord of the Internets
web: http://www.evancarroll.com
ph: 281.901.0011

Evan Carroll <me(at)evancarroll(dot)com> writes:
> Also, here is a copy of the complete script -- including the citext
> creation statements from the dump, with the patch, with the bugged
> statements.

Well, if that's how you're creating citext, then yeah it's broken.
As of 9.1 the citext type needs to be created with the attribute
"COLLATABLE = true". The suggested UPDATE statements are a means
of correcting a failure to do that after-the-fact, but they don't
cover any domains that have already been created on top of citext.

regards, tom lane