Fallout from 'Make type "name" collation-aware' and tg_table_name

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