Обсуждение: 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@evancarroll.com System Lord of the Internets web: http://www.evancarroll.com ph: 281.901.0011
Evan Carroll <me@evancarroll.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@evancarroll.com System Lord of the Internets web: http://www.evancarroll.com ph: 281.901.0011
Evan Carroll <me@evancarroll.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@evancarroll.com System Lord of the Internets web: http://www.evancarroll.com ph: 281.901.0011
Evan Carroll <me@evancarroll.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@evancarroll.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@evancarroll.com System Lord of the Internets web: http://www.evancarroll.com ph: 281.901.0011
Evan Carroll <me@evancarroll.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@evancarroll.com System Lord of the Internets web: http://www.evancarroll.com ph: 281.901.0011
Evan Carroll <me@evancarroll.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