Thread: Odd behavior with domains

Odd behavior with domains

From

"Joshua D. Drake"

Date:

24 June 2016, 02:17:07

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)

Re: Odd behavior with domains

From

Corey Huinker

Date:

24 June 2016, 02:46:39

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.

Re: Odd behavior with domains

From

Alvaro Herrera

Date:

24 June 2016, 03:00:54

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 dominiosEsquema | Nombre | Tipo | Modificador | Check 
---------+--------+------+-------------+-------
(0 filas)

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

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

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

Re: Odd behavior with domains

From

Tom Lane

Date:

24 June 2016, 03:00:55

"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

Re: Odd behavior with domains

From

Justin Dearing

Date:

24 June 2016, 03:10:03

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

Re: Odd behavior with domains

From

"Joshua D. Drake"

Date:

24 June 2016, 17:09:00

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.

Re: Odd behavior with domains

From

"David G. Johnston"

Date:

24 June 2016, 17:32:00

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.

Re: Odd behavior with domains

From

Alvaro Herrera

Date:

24 June 2016, 17:35:38

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

Re: Odd behavior with domains

From

Robert Haas

Date:

24 June 2016, 19:04:42

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