Обсуждение: BUG #6080: information_schema.columns.column_default contains NULL inconsistently

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)

"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

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)

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

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

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

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