Обсуждение: 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)
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 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
"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
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.
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
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