Fallout from 'Make type "name" collation-aware' and tg_table_name
От | Christoph Berg |
---|---|
Тема | Fallout from 'Make type "name" collation-aware' and tg_table_name |
Дата | |
Msg-id | YqCfLapinj6+mJ6i@msg.credativ.de обсуждение исходный текст |
Ответы |
Re: Fallout from 'Make type "name" collation-aware' and tg_table_name
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
Hi, this is more a heads-up than a bug report, but I'm still reporting it here because I think that variant of the problem has not been addressed in the releases notes. I was just debugging a client problem where a trigger was running fine with PG 11, but very slow with PG 13. The symptom is that some COLLATE "C" is appearing in the auto_explain output: Good trigger plan on PG 11: -> Index Scan using cache_revisions_zone_table_rev on cache_revisions (cost=0.15..8.17 rows=1 width=218) (actual time=0.009..0.009rows=0 loops=1) Index Cond: (((zone_id)::text = 'insert-test'::text) AND ((table_name)::text = 'provider'::text) AND (revision< 1)) Buffers: shared hit=1 Bad trigger plan on PG 13 (and PG 12): -> Seq Scan on cache_revisions (cost=0.00..15.60 rows=1 width=218) (actual time=0.007..0.007 rows=0 loops=1) Filter: ((revision < 1) AND ((table_name)::text = 'provider'::text COLLATE "C") AND ((zone_id)::text = 'insert-test'::textCOLLATE "C")) Rows Removed by Filter: 1 Buffers: shared hit=1 Git-bisecting leads to this commit introducing the change: commit 586b98fdf1aaef4a27744f8b988479aad4bd9a01 Author: Tom Lane <tgl@sss.pgh.pa.us> Date: Wed Dec 19 17:35:12 2018 -0500 Make type "name" collation-aware. The "name" comparison operators now all support collations, making them functionally equivalent to "text" comparisons, except for the different physical representation of the datatype. They do, in fact, mostly share the varstr_cmp and varstr_sortsupport infrastructure, which has been slightly enlarged to handle the case. To avoid changes in the default behavior of the datatype, set name's typcollation to C_COLLATION_OID not DEFAULT_COLLATION_OID, so that by default comparisons to a name value will continue to use strcmp semantics. (This would have been the case for system catalog columns anyway, because of commit 6b0faf723, but doing this makes it true for user-created name columns as well. In particular, this avoids locale-dependent changes in our regression test results.) In consequence, tweak a couple of places that made assumptions about collatable base types always having typcollation DEFAULT_COLLATION_OID. I have not, however, attempted to relax the restriction that user- defined collatable types must have that. Hence, "name" doesn't behave quite like a user-defined type; it acts more like a domain with COLLATE "C". (Conceivably, if we ever get rid of the need for catalog name columns to be fixed-length, "name" could actually become such a domain over text. But that'd be a pretty massive undertaking, and I'm not volunteering.) Discussion: https://postgr.es/m/15938.1544377821@sss.pgh.pa.us And indeed the "name" type is used in the trigger code, via tg_table_name: CREATE FUNCTION public.update_cache_revision() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $$ DECLARE v_zone_id TEXT; BEGIN v_zone_id := NEW.zone_id; PERFORM increase_revision(tg_table_name::TEXT, v_zone_id); RETURN NEW; END; $$; The "C" collation of name on tg_table_name is preserved by the ::text cast, and the code in increase_revision() then fails to use the btree index on cache_revisions (where the column is of type "text") because the collation does not match. The intent of the change seems to have been not to change the user-visible behavior, but here it's clearly changed when leaving the collation at "default" would have meant no change. Of course the fix is to attach 'collate "default"' to tg_table_name: PERFORM increase_revision(tg_table_name::TEXT collate "default", v_zone_id); The PG 12 release notes have this note: <para> Mark table columns of type <link linkend="datatype-character-special-table">name</link> as having <quote>C</quote> collation by default (Tom Lane, Daniel Vérité) </para> <para> The comparison operators for data type <type>name</type> can now use any collation, rather than always using <quote>C</quote> collation. To preserve the previous semantics of queries, columns of type <type>name</type> are now explicitly marked as having <quote>C</quote> collation. A side effect of this is that regular-expression operators on <type>name</type> columns will now use the <quote>C</quote> collation by default, not the database collation, to determine the behavior of locale-dependent regular expression patterns (such as <literal>\w</literal>). If you want non-C behavior for a regular expression on a <type>name</type> column, attach an explicit <literal>COLLATE</literal> clause. (For user-defined <type>name</type> columns, another possibility is to specify a different collation at table creation time; but that just moves the non-backwards-compatibility to the comparison operators.) </para> Of course it's a bit too late now to change that, but the impact is clearly wider than just regular expression operations. Christoph -- Senior Consultant, Tel.: +49 2166 9901 187 credativ GmbH, HRB Mönchengladbach 12080, USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Geoff Richardson, Peter Lilley Unser Umgang mit personenbezogenen Daten unterliegt folgenden Bestimmungen: https://www.credativ.de/datenschutz
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Fabien COELHOДата:
Сообщение: Re: psql 15beta1 does not print notices on the console until transaction completes
Следующее
От: Daniele VarrazzoДата:
Сообщение: Using PQexecQuery in pipeline mode produces unexpected Close messages