Thread: BUG #6080: information_schema.columns.column_default contains NULL inconsistently
BUG #6080: information_schema.columns.column_default contains NULL inconsistently
From
"Chris Bandy"
Date:
The following bug has been logged online: Bug reference: 6080 Logged by: Chris Bandy Email address: bandy.chris@gmail.com PostgreSQL version: 9.0.3 Operating system: Gentoo Description: information_schema.columns.column_default contains NULL inconsistently Details: While using the information_schema to examine my tables, I found that "columns"."column_default" does not consistently represent the DEFAULT constraint/definition of a column. I would expect a column without a DEFAULT definition to return a null value, while a column with a DEFAULT definition would return the defined expression as a character value. In the following log, columns "a", "b" and "c" appear identical though their definitions differ. -- Chris $ psql -P null='<null>' testing psql (9.0.3) Type "help" for help. testing=> select version(); version ---------------------------------------------------------------------------- --------------------------------------------------- PostgreSQL 9.0.3 on x86_64-pc-linux-gnu, compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.4.5 p1.2, pie-0.4.5) 4.4.5, 64-bit (1 row) testing=> create table tt (a varchar, b varchar default null, c varchar default null::varchar, d varchar(1) default null, e varchar(1) default null::varchar); CREATE TABLE testing=> \d tt Table "public.tt" Column | Type | Modifiers --------+----------------------+--------------------------------- a | character varying | b | character varying | c | character varying | d | character varying(1) | default NULL::character varying e | character varying(1) | default NULL::character varying testing=> select column_name, data_type, column_default from information_schema.columns where table_name = 'tt'; column_name | data_type | column_default -------------+-------------------+------------------------- a | character varying | <null> b | character varying | <null> c | character varying | <null> d | character varying | NULL::character varying e | character varying | NULL::character varying (5 rows)
Re: BUG #6080: information_schema.columns.column_default contains NULL inconsistently
From
Tom Lane
Date:
"Chris Bandy" <bandy.chris@gmail.com> writes: > While using the information_schema to examine my tables, I found that > "columns"."column_default" does not consistently represent the DEFAULT > constraint/definition of a column. > I would expect a column without a DEFAULT definition to return a null value, > while a column with a DEFAULT definition would return the defined expression > as a character value. > In the following log, columns "a", "b" and "c" appear identical though their > definitions differ. I don't see anything to fix here. The standard says that for a column without any explicit default value, COLUMN_DEFAULT should be null. But AFAICS there is room for implementation dependency in other cases. In the particular cases you show here, PG recognizes some of them as being equivalent to not having a default value, so for efficiency's sake it converts them to that form. I don't think we're bound to make every such case work like that, though. regards, tom lane
Re: BUG #6080: information_schema.columns.column_default contains NULL inconsistently
From
Chris Bandy
Date:
On Fri, Jul 1, 2011 at 10:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Chris Bandy" <bandy.chris@gmail.com> writes: > > While using the information_schema to examine my tables, I found that > > "columns"."column_default" does not consistently represent the DEFAULT > > constraint/definition of a column. > > > I would expect a column without a DEFAULT definition to return a null v= alue, > > while a column with a DEFAULT definition would return the defined expre= ssion > > as a character value. > > > In the following log, columns "a", "b" and "c" appear identical though = their > > definitions differ. > > I don't see anything to fix here. =C2=A0The standard says that for a colu= mn > without any explicit default value, COLUMN_DEFAULT should be null. That makes sense. > > But AFAICS there is room for implementation dependency in other cases. > In the particular cases you show here, PG recognizes some of them as > being equivalent to not having a default value, so for efficiency's sake > it converts them to that form. That makes sense, too. Perhaps I am naive, but a null is a null, right? Is the different presentation of defaults for "d" and "e" indicative of an *in*efficiency in PG? > > I don't think we're bound to make every > such case work like that, though. As it stands now, it is impossible to state a succinct/clear definition of the contents of "column_default" in PG: It contains a null value for columns with a default of null or contains a character expression of the default value with a type cast that, possibly, does not match the column type. > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0regards, tom lane One other case that I failed to include originally is below. The reported default includes the type length. -- Chris $ psql -P null=3D'<null>' testing psql (9.0.3) Type "help" for help. testing=3D> create table tt (f varchar(1) default null::varchar(1)); CREATE TABLE testing=3D> \d tt Table "public.tt" Column | Type | Modifiers --------+----------------------+------------------------------------ f | character varying(1) | default NULL::character varying(1) testing=3D> select column_name, data_type, column_default from information_schema.columns where table_name =3D 'tt'; column_name | data_type | column_default -------------+-------------------+---------------------------- f | character varying | NULL::character varying(1) (1 row)
Re: BUG #6080: information_schema.columns.column_default contains NULL inconsistently
From
Tom Lane
Date:
Chris Bandy <bandy.chris@gmail.com> writes: > On Fri, Jul 1, 2011 at 10:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> But AFAICS there is room for implementation dependency in other cases. >> In the particular cases you show here, PG recognizes some of them as >> being equivalent to not having a default value, so for efficiency's sake >> it converts them to that form. > That makes sense, too. Perhaps I am naive, but a null is a null, > right? Is the different presentation of defaults for "d" and "e" > indicative of an *in*efficiency in PG? Yeah, it's intentional though. What the printout is not telling you is that there's a hidden cast function invocation to enforce the length limit in the cases where the column has an explicit length limit. That is, under the hood the expression is really more like "varchar(NULL, 1)". The code that recognizes a default expression as being just constant NULL doesn't think this is a constant NULL. In principle it could recognize that, since the cast function is marked strict, but so far it has not seemed worth the trouble. regards, tom lane
Re: BUG #6080: information_schema.columns.column_default contains NULL inconsistently
From
Robert Haas
Date:
On Sun, Jul 3, 2011 at 12:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Chris Bandy <bandy.chris@gmail.com> writes: >> On Fri, Jul 1, 2011 at 10:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> But AFAICS there is room for implementation dependency in other cases. >>> In the particular cases you show here, PG recognizes some of them as >>> being equivalent to not having a default value, so for efficiency's sake >>> it converts them to that form. > >> That makes sense, too. Perhaps I am naive, but a null is a null, >> right? Is the different presentation of defaults for "d" and "e" >> indicative of an *in*efficiency in PG? > > Yeah, it's intentional though. =A0What the printout is not telling you > is that there's a hidden cast function invocation to enforce the length > limit in the cases where the column has an explicit length limit. =A0That > is, under the hood the expression is really more like "varchar(NULL, 1)". > The code that recognizes a default expression as being just constant > NULL doesn't think this is a constant NULL. =A0In principle it could > recognize that, since the cast function is marked strict, but so far > it has not seemed worth the trouble. Gee, does Noah's recent patch adding the notion of "transform functions" have any applicability to this problem? --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: BUG #6080: information_schema.columns.column_default contains NULL inconsistently
From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes: > On Sun, Jul 3, 2011 at 12:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> The code that recognizes a default expression as being just constant >> NULL doesn't think this is a constant NULL. In principle it could >> recognize that, since the cast function is marked strict, but so far >> it has not seemed worth the trouble. > Gee, does Noah's recent patch adding the notion of "transform > functions" have any applicability to this problem? Not really. If someone held a gun to my head and said "fix that", what I'd do is run eval_const_expressions() on the default expression and see if that resulted in a constant NULL. But it seems unlikely to be worth the cycles in most cases. Also, we'd then need some other test to address the issue explained in AddRelationNewConstraints: /* * If the expression is just a NULL constant, we do not bother to make * an explicit pg_attrdef entry, since the default behavior is * equivalent. * * Note a nonobvious property of this test: if the column is of a * domain type, what we'll get is not a bare null Const but a * CoerceToDomain expr, so we will not discard the default. This is * critical because the column default needs to be retained to * override any default that the domain might have. */ if (expr == NULL || (IsA(expr, Const) &&((Const *) expr)->constisnull)) continue; IOW, there are cases where "DEFAULT NULL" is *not* a no-op. regards, tom lane
Re: BUG #6080: information_schema.columns.column_default contains NULL inconsistently
From
Robert Haas
Date:
On Tue, Jul 5, 2011 at 12:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Sun, Jul 3, 2011 at 12:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> The code that recognizes a default expression as being just constant >>> NULL doesn't think this is a constant NULL. =A0In principle it could >>> recognize that, since the cast function is marked strict, but so far >>> it has not seemed worth the trouble. > >> Gee, does Noah's recent patch adding the notion of "transform >> functions" have any applicability to this problem? > > Not really. =A0If someone held a gun to my head and said "fix that", what > I'd do is run eval_const_expressions() on the default expression and see > if that resulted in a constant NULL. =A0But it seems unlikely to be worth > the cycles in most cases. =A0Also, we'd then need some other test to > address the issue explained in AddRelationNewConstraints: > > =A0 =A0 =A0 =A0/* > =A0 =A0 =A0 =A0 * If the expression is just a NULL constant, we do not bo= ther to make > =A0 =A0 =A0 =A0 * an explicit pg_attrdef entry, since the default behavio= r is > =A0 =A0 =A0 =A0 * equivalent. > =A0 =A0 =A0 =A0 * > =A0 =A0 =A0 =A0 * Note a nonobvious property of this test: if the column = is of a > =A0 =A0 =A0 =A0 * domain type, what we'll get is not a bare null Const bu= t a > =A0 =A0 =A0 =A0 * CoerceToDomain expr, so we will not discard the default= . =A0This is > =A0 =A0 =A0 =A0 * critical because the column default needs to be retaine= d to > =A0 =A0 =A0 =A0 * override any default that the domain might have. > =A0 =A0 =A0 =A0 */ > =A0 =A0 =A0 =A0if (expr =3D=3D NULL || > =A0 =A0 =A0 =A0 =A0 =A0(IsA(expr, Const) &&((Const *) expr)->constisnull)) > =A0 =A0 =A0 =A0 =A0 =A0continue; > > IOW, there are cases where "DEFAULT NULL" is *not* a no-op. Interesting. A possible reason to care about this is that it might convert a form of ALTER TABLE that requires a rewrite into one that doesn't, since we needn't rewrite the table if the column will be all-nulls. That's not enough of a benefit to motivate me to do the work myself, since all the examples thus-far shown involve writing the default in a way that's more complicated than necessary. But I'd have a hard time objecting if someone else wanted to run it down, since I'm pretty sure I've written an ALTER TABLE that way once or twice myself. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company