Odd behavior with domains

Hey,

So this came across my twitter feed:

https://pbs.twimg.com/media/ClqIJtmXEAA5IGt.png

I have verified the oddness with a newer version:

psql -U postgres
psql (9.5.3)
Type "help" for help.

postgres=# create domain text char(3);
CREATE DOMAIN
postgres=# create domain text char(2);
ERROR: type "text" already exists
postgres=# \dD
List of domains
Schema | Name | Type | Modifier | Check
--------+------+------+----------+-------
(0 rows)

postgres=# create domain textd char(2);
CREATE DOMAIN
postgres=# \dD
List of domains
Schema | Name | Type | Modifier | Check
--------+-------+--------------+----------+-------
public | textd | character(2) | |
(1 row)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jun 23, 2016 at 10:16 PM, Joshua D. Drake <jd@commandprompt.com>
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:

psql -U postgres
psql (9.5.3)
Type "help" for help.

postgres=# create domain text char(3);
CREATE DOMAIN
postgres=# create domain text char(2);
ERROR: type "text" already exists
postgres=# \dD
List of domains
Schema | Name | Type | Modifier | Check
--------+------+------+----------+-------
(0 rows)

postgres=# create domain textd char(2);
CREATE DOMAIN
postgres=# \dD
List of domains
Schema | Name | Type | Modifier | Check
--------+-------+--------------+----------+-------
public | textd | character(2) | |
(1 row)

It's there.

# create domain text char(3);
CREATE DOMAIN
labels_search=# \dD public.text
List of domains
Schema | Name | Type | Modifier | Check
--------+------+--------------+----------+-------
public | text | character(3) | |
(1 row)

I've noticed the same thing when creating types that mask an existing
catalog type.

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.

alvherre=# create domain text char(3);
CREATE DOMAIN
alvherre=# \dD
Listado de dominios
Esquema | Nombre | Tipo | Modificador | Check
---------+--------+------+-------------+-------
(0 filas)

alvherre=# set search_path to 'public', 'pg_catalog';
SET
alvherre=# \dD
Listado de dominios
Esquema | Nombre | Tipo | Modificador | Check
---------+--------+--------------+-------------+-------
public | text | character(3) | |
(1 fila)

alvherre=# reset search_path;
RESET
alvherre=# \dD
Listado de dominios
Esquema | Nombre | Tipo | Modificador | Check
---------+--------+------+-------------+-------
(0 filas)

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Joshua D. Drake" <jd@commandprompt.com> writes:

So this came across my twitter feed:
https://pbs.twimg.com/media/ClqIJtmXEAA5IGt.png

public.text can exist in parallel with pg_catalog.text.

Nothing to see here, move along.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I was the one that reported that on twitter. I have a more detailed message
on the general list that I sent before subscribing and probably needs to be
moderated (or if it went to /dev/null let me know).

On Thu, Jun 23, 2016 at 11:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

So this came across my twitter feed:
https://pbs.twimg.com/media/ClqIJtmXEAA5IGt.png

public.text can exist in parallel with pg_catalog.text.

It just doesn't seem right to me to be able to do:

CREATE DOMAIN int AS varchar(50);

Justin

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:

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?

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.

Sincerely,

JD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jun 24, 2016 at 1:08 PM, Joshua D. Drake <jd@commandprompt.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.

Joshua D. Drake wrote:

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.

The unadorned name "text" doesn't refer to the domain at this point,
since it's masked by the system type pg_catalog.text.

If you do "\dD public.*" you will see your "text" domain listed as well.

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 there is no system object named textd.

postgres=# drop domain text;
ERROR: "text" is not a domain

Right -- "text" is not a domain, it is pg_catalog.text.

postgres=# set search_path to 'public';
SET
postgres=# drop domain text;
ERROR: "text" is not a domain
postgres=#

Here you're still referring to pg_catalog.text, since as I said above
pg_catalog is put in front of the search path if you don't specify it
anywhere. You need to add pg_catalog to search_path *after* public.
So you can do either
set search_path to 'public', 'pg_catalog'
drop domain text;

or
drop domain public.text;

Note: If this is literally just the way it is, cool. It was just as I was
exploring this all seemed odd.

Yes, this is the way it is, and yes it is odd -- but as I said it's not
specific to domains:

alvherre=# create table pg_class (a int, b text);
CREATE TABLE
alvherre=# \d
No se encontraron relaciones.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jun 23, 2016 at 11:00 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

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.

Well, what's causing the apparent weirdness here is the fact that
pg_catalog, despite being implicitly at the front of the namespath
path, doesn't become the default creation schema as an
explicitly-named schema would. So you don't try to create things
there but anything that already exists there masks the stuff you do
create. And I think it's fair to say that's pretty weird to someone
who is unfamiliar with the way the system works.

We could do something like this:

NOTICE: existing type "pg_catalog"."text" will mask new type "public"."text"

We could even make that an ERROR by default, as long as we had some
GUC to disable the behavior for pg_dump. How often do you really
intentionally create an object that shadows an existing system object?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers