Обсуждение: Using incorrect default-value type
Hello,
I have found a strange behavior in postgreSQL when adding columns with defaults of a domain type in a schema different from public. This is the example to reproduce it:
CREATE SCHEMA schema_1;
CREATE DOMAIN schema_1.text AS text;
SET search_path TO schema_1, pg_catalog;
CREATE TABLE test
(
col1 text DEFAULT 'some value'
);
SELECT a.attname,
pg_get_expr(d.adbin, d.adrelid) AS default
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = 'schema_1."test"'::regclass
AND a.attnum > 0
ORDER BY a.attnum;
Last query will return:
col1 | "'some value'::pg_catalog.text"
I don't understand why it is using "pg_catalog.text", when it should be using "schema_1.text", or in this case the query should return just "text" since the search path is using "schema_1".
Furthermore, if I open pgAdmin and select "col1" in "test" table, SQL pane will show:
ALTER TABLE schema_1.test ALTER COLUMN col1 SET DEFAULT 'some value'::text;
but I believe, it should show:
ALTER TABLE schema_1.test ALTER COLUMN col1 SET DEFAULT 'some value'::schema_1.text;
Is this a bug or am I missing something?
Thanks,
Arturo
Hello,I have found a strange behavior in postgreSQL when adding columns with defaults of a domain type in a schema different from public. This is the example to reproduce it:CREATE SCHEMA schema_1;CREATE DOMAIN schema_1.text AS text;SET search_path TO schema_1, pg_catalog;CREATE TABLE test(col1 text DEFAULT 'some value');SELECT a.attname,pg_get_expr(d.adbin, d.adrelid) AS defaultFROM pg_attribute a LEFT JOIN pg_attrdef dON a.attrelid = d.adrelid AND a.attnum = d.adnumWHERE a.attrelid = 'schema_1."test"'::regclassAND a.attnum > 0ORDER BY a.attnum;Last query will return:col1 | "'some value'::pg_catalog.text"I don't understand why it is using "pg_catalog.text", when it should be using "schema_1.text", or in this case the query should return just "text" since the search path is using "schema_1".Furthermore, if I open pgAdmin and select "col1" in "test" table, SQL pane will show:ALTER TABLE schema_1.test ALTER COLUMN col1 SET DEFAULT 'some value'::text;but I believe, it should show:ALTER TABLE schema_1.test ALTER COLUMN col1 SET DEFAULT 'some value'::schema_1.text;Is this a bug or am I missing something?Thanks,Arturo
What you are doing (name overloading) seems inadvisable regardless of whether it should work are described. My guess is that the system searches for an actual type first and only if it fails to find a matching type then looks for a matching domain.
David J.
Arturo Pie Joa <arturotd08@yahoo.ca> writes: > I have found a strange behavior in postgreSQL when adding columns with > defaults of a domain type in a schema different from public. As the other respondent said, you're confusing the issue by using conflicting names. If you hadn't done that, you'd see this: regression=# create domain mytext as text; CREATE DOMAIN regression=# create table test (co1 mytext default 'some value'); CREATE TABLE regression=# \d+ test Table "public.test" Column | Type | Modifiers | Storage | Stats target | Description --------+--------+----------------------------+----------+--------------+------------- co1 | mytext | default 'some value'::text | extended | | Has OIDs: no which makes it at least slightly clearer what's going on: pg_get_expr is just reporting the base type of the constant. The constant does get cast to the domain type, but that's an implicit cast that's not shown in the decompiled output. You can convince yourself of that by looking directly at the pg_attrdef entry: regression=# select * from pg_attrdef where adrelid = 'test'::regclass; adrelid | adnum | adbin | adsrc ---------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------- 67889 | 1 | {COERCETODOMAIN :arg {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false:constisnull false :location 38 :constvalue 14 [ 0 0 0 14 115 111 109 101 32 118 97 108 117 101 ]} :resulttype 67888:resulttypmod -1 :resultcollid 100 :coercionformat 2 :location -1} | 'some value'::text (1 row) The constant has type 25 (text). The CoerceToDomain node does have mytype (with OID 67888 in this test) as result type, but it isn't shown because it has coercionformat 2 (COERCE_IMPLICIT_CAST). BTW, the reason it's done like this is that CoerceToDomain has to be done at runtime: if you were to do an ALTER DOMAIN ADD CONSTRAINT, it's not clear whether or not the constant would still be a valid value of the domain. So we can't just generate a Const node that claims that 'some text' is a value of type mytype. regards, tom lane
My last question is:
Is it ok to do name overloading in postgreSQL? or is that not recommended at all? Could I find some issues later by doing name overloading?
We are doing this because we are using different collations on each schema, and our ORM doesn't support custom domains, so we are just overloading "text".
Thanks,
Arturo
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Arturo Pie Joa <arturotd08@yahoo.ca>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Sunday, October 14, 2012 4:35:07 PM
Subject: Re: [GENERAL] Using incorrect default-value type
Arturo Pie Joa <arturotd08@yahoo.ca> writes:
> I have found a strange behavior in postgreSQL when adding columns with
> defaults of a domain type in a schema different from public.
As the other respondent said, you're confusing the issue by using
conflicting names. If you hadn't done that, you'd see this:
regression=# create domain mytext as text;
CREATE DOMAIN
regression=# create table test (co1 mytext default 'some value');
CREATE TABLE
regression=# \d+ test
Table "public.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------+----------------------------+----------+--------------+-------------
co1 | mytext | default 'some value'::text | extended | |
Has OIDs: no
which makes it at least slightly clearer what's going on: pg_get_expr is
just reporting the base type of the constant. The constant does get
cast to the domain type, but that's an implicit cast that's not shown in
the decompiled output. You can convince yourself of that by looking
directly at the pg_attrdef entry:
regression=# select * from pg_attrdef where adrelid = 'test'::regclass;
adrelid | adnum | adbin | adsrc
---------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------
67889 | 1 | {COERCETODOMAIN :arg {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 38 :constvalue 14 [ 0 0 0 14 115 111 109 101 32 118 97 108 117 101 ]} :resulttype 67888 :resulttypmod -1 :resultcollid 100 :coercionformat 2 :location -1} | 'some value'::text
(1 row)
The constant has type 25 (text). The CoerceToDomain node does have
mytype (with OID 67888 in this test) as result type, but it isn't shown
because it has coercionformat 2 (COERCE_IMPLICIT_CAST).
BTW, the reason it's done like this is that CoerceToDomain has to be
done at runtime: if you were to do an ALTER DOMAIN ADD CONSTRAINT,
it's not clear whether or not the constant would still be a valid value
of the domain. So we can't just generate a Const node that claims that
'some text' is a value of type mytype.
regards, tom lane
Arturo Pie Joa <arturotd08@yahoo.ca> writes: > Is it ok to do name overloading in postgreSQL? or is that�not�recommended at all? Could I find some issues later by doingname overloading? People use it all the time ... and get burnt some of the time. > We are doing this because we are using different collations on each schema, and our ORM doesn't support custom domains,so we are just overloading "text". Maybe you need a better ORM. But it's not clear to me why you'd need domains for this anyway --- can't you just attach a collation property to each table column? regards, tom lane