Re: [HACKERS] Odd behavior with domains

2016-06-24 Thread Robert Haas
On Thu, Jun 23, 2016 at 11:00 PM, Alvaro Herrera
 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


Re: [HACKERS] Odd behavior with domains

2016-06-24 Thread Alvaro Herrera
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 Herrerahttp://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


Re: [HACKERS] Odd behavior with domains

2016-06-24 Thread David G. Johnston
On Fri, Jun 24, 2016 at 1:08 PM, Joshua D. Drake 
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: [HACKERS] Odd behavior with domains

2016-06-24 Thread Joshua D. Drake

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


Re: [HACKERS] Odd behavior with domains

2016-06-23 Thread Justin Dearing
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  wrote:

> "Joshua D. Drake"  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: [HACKERS] Odd behavior with domains

2016-06-23 Thread Tom Lane
"Joshua D. Drake"  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


Re: [HACKERS] Odd behavior with domains

2016-06-23 Thread Alvaro Herrera
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 Herrerahttp://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


Re: [HACKERS] Odd behavior with domains

2016-06-23 Thread Corey Huinker
On Thu, Jun 23, 2016 at 10:16 PM, 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:
>
> 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.