Re: Odd behavior with domains

On Fri, Jun 24, 2016 at 1:08 PM, Joshua D. Drake <jd(at)commandprompt(dot)com>
wrote:

> On 06/23/2016 08:00 PM, Alvaro Herrera wrote:
>
>> Joshua D. Drake wrote:
>>
>>> Hey,
>>>
>>> So this came across my twitter feed:
>>>
>>> https://pbs.twimg.com/media/ClqIJtmXEAA5IGt.png
>>>
>>> I have verified the oddness with a newer version:
>>>
>>
>> Well, it's not specifically related to domains -- it's related to the
>> fact that pg_catalog objects mask the domain you created in the public
>> schema, because pg_catalog is by default in front of all other schemas
>> unless you explicitely put it elsewhere.
>>
>
> Yes but what makes it weird is this:
>
> postgres=# create domain text char(3);
> CREATE DOMAIN
>
> -- cool, no problem
>
> postgres=# create domain text char(2);
> ERROR: type "text" already exists
>
> -- as expected
>
> postgres=# \dD
> List of domains
> Schema | Name | Type | Modifier | Check
> --------+------+------+----------+-------
> (0 rows)
>
> -- wait what? I just created this.
> -- I understand the search_path issue but:
>
>
​The fundamental problem is that for purposes of meta-command \d a domain
and a type are distinct object types. But as far as the type system goes
the distinction is lost. What \dD is telling us is that our newborn text
domain type is not visible to us​ - without telling us why (i.e., because
it is being shadowed by the text type).

Why do we even have "\dD"? "\dT" displays domains. Based upon that I'd
say \dD should display types regardless of search_path precedence and leave
\dT to display both domains and types with search_path considered.

> postgres=# create domain textd char(2);
> CREATE DOMAIN
> postgres=# \dD
> List of domains
> Schema | Name | Type | Modifier | Check
> --------+-------+--------------+----------+-------
> public | textd | character(2) | |
> (1 row)
>
> -- why would this show up without changing the search path if the
> -- previous one didn't?
>

Because this isn't being overshadowed by another non-domain type in the
system.

>
>
> postgres=# drop domain text;
> ERROR: "text" is not a domain
> postgres=# set search_path to 'public';
> SET
> postgres=# drop domain text;
> ERROR: "text" is not a domain
> postgres=#
>
> -- Now what?
>
> Note: If this is literally just the way it is, cool. It was just as I was
> exploring this all seemed odd.
>

You didn't specify pg_catalog explicitly and it is invalid to have a
search_path that doesn't include pg_catalog so PostgreSQL helps you out by
putting it in front of the one you specify.

SET search_path TO public, pg_catalog;
DROP DOMAIN text;

-- all good

Or just:

DROP DOMAIN public.text;

David J.