Arrays of domain returned to client as non-builtin oid describing thearray, not the base array type's oid - Mailing list pgsql-hackers

I was surprised yesterday in a difference between querying domains as scalars versus domains as arrays. As we're all generally aware, when a domain is queried and projected as a scalar in a result set, it is described over-the-wire as that column having the oid of the domain's base type, NOT the oid of the domain itself. This helps out many clients and their applications, but confuses a few who want to use domains as 'tagged types' to register new client-side type mappings against. Changing that behavior seems to asked every now and then and rejected due to breaking more than it would help. And can be worked around through making a whole new type sharing much of the config as the base type.

But when arrays of the domain are returned to the client, the column is described on the wire with the oid of the domain's array type, instead of the oid of the base type's array type. This seems inconsistent to me, even though it can be worked around in SQL by a cast of either the element type when building the array, or casting the resulting array type.

Example SQL:

create database test;

\c test

create domain required_text text

check (trim(value) = value and length(value) > 0) not null;

create table people
(
name required_text
);

insert into people values ('Joe'), ('Mary'), ('Jane');

And then client-side interaction using python/psycopg2 (sorry, am ignorant of how to get psql itself to show the protocol-level oids):

import psycopg2
con = psycopg2.connect('dbname=test')
cur = con.cursor()

# Scalar behaviours first: a query of the domain or the base type return the base type's oid:
>>> cur.execute('select name from people')
>>> cur.description
(Column(name='name', type_code=25, display_size=None, internal_size=-1, precision=None, scale=None, null_ok=None),)
>>> cur.execute('select name::text from people')
>>> cur.description
(Column(name='name', type_code=25, display_size=None, internal_size=-1, precision=None, scale=None, null_ok=None),)

Arrays of the base type (forced through explicit cast of either the element or the array):
>>> cur.execute('select array_agg(name::text) from people')
>>> cur.description
(Column(name='array_agg', type_code=1009, display_size=None, internal_size=-1, precision=None, scale=None, null_ok=None),)
>>> cur.execute('select array_agg(name)::text[] from people')
>>> cur.description
(Column(name='array_agg', type_code=1009, display_size=None, internal_size=-1, precision=None, scale=None, null_ok=None),)

Arrays of the domain, showing the new array type:

cur.execute('select array_agg(name) from people')
>>> cur.description
(Column(name='array_agg', type_code=2392140, display_size=None, internal_size=-1, precision=None, scale=None, null_ok=None),)

Interesting bits from my pg_type -- 2392140 is indeed the oid of the array type for the domain.

test=# select oid, typname, typcategory, typelem from pg_type where typname in ( '_text', '_required_text');
   oid   |    typname     | typcategory | typelem 
---------+----------------+-------------+---------
    1009 | _text          | A           |      25
 2392140 | _required_text | A           | 2392141

So -- do others find this inconsistent, or is it just me and I should work on having psycopg2 be able to learn the type mapping itself if I don't want to do SQL-side casts? I'll argue that if scalar projections erase the domain's oid, then array projections ought to as well.

Thanks!
James