Concerned to discover DOMAINs can be the same as built in types with no escaping

From: Justin Dearing <zippy1981(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Concerned to discover DOMAINs can be the same as built in types with no escaping
Date: 2016-06-24 02:19:47
Message-ID: CABsCM1ODXCD_g9LmVgo7k3MPo_wq8XJP-jOu07X5vQx=fb9D3Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

We happen to have in our schema the following domain.

CREATE DOMAIN public.name varchar(50) NOT NULL;

This was done before me. We assumed this was used in many tables in our
app. Then I wrote a function with a return clause like the following:

RETURNS (
id INT,
name name,
other_names name ARRAY
)

This worked fine until CI tried to build this on a postgres 9.3 box (we are
mostly 9.1, but are fixing that). Then it failed.

So then I discovered that there is a built in type called pg_catalog.name
as well as my public.name. Followed by the discovery that you can't have
arrays of domains. This query showed two rows

SELECT typnamespace, typname
FROM pg_catalog.pg_type typ
WHERE typname = 'name';

Then there was wailing and gnashing of teeth, and I made everything
explicitly varchar, and everything was all good, except I have to fix unit
tests. Oh and nothing is actually using our domain, as demonstrated by this
query:

SELECT attrelid::regclass AS table_name,
attname, atttypid::REGTYPE
FROM pg_catalog.pg_attribute
WHERE atttypid::REGTYPE IN ('name', 'public.name')
ORDER BY atttypid DESC, attrelid::regclass

Based on this, and some consultations with friends who know more about
postgres than I, I'd like to propose that domains not be allowed to be the
same name as built in types or at the very least give a warning. The fact
that I have to quote keywords, but not even need to quote built in types is
bothersome. Here are examples of queries and behaviors I expect

CREATE DOMAIN "INTO" char(5); -- Does work. Should work without a warning.
The error you get for doing it unquoted is sufficient IMHO
CREATE DOMAIN int CHAR(50); -- Does work. Id prefer it not to work.
Alternatively it could work but emit a warning.
CREATE DOMAIN public.int CHAR(50); -- Does work. I could see the argument
for it working, but would prefer it didn't work. Should still emit a
warning its overriding a base

Since I'm returning to postgres after close to a decade, I figured I'd ask
here for feedback before posting to the hackers list.

Regards,

Justin Dearing

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2016-06-24 05:44:17 9.6 beta2 win-x64 download links still point to beta1
Previous Message Sameer Kumar 2016-06-24 01:23:32 Re: What Causes Access Exclusive Lock?