Re: FK constraints "NOT VALID" by default?

I just ran this quick test in HEAD:

alvherre=# create table study (id int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «study_pkey» para la tabla «study»
CREATE TABLE
alvherre=# insert into study select a from generate_series(1, 1000000) as a;
INSERT 0 1000000
alvherre=# create table studyform (id int primary key, study_id int not null references study);
NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «studyform_pkey» para la tabla «studyform»
CREATE TABLE
alvherre=# insert into studyform select a, 1 + a * random() from generate_series(1, 100000) a;
INSERT 0 100000

and was very surprised to see that the foreign key is marked as NOT
VALID:

alvherre=# \d studyform
Tabla «public.studyform»
Columna │ Tipo │ Modificadores
──────────┼─────────┼───────────────
id │ integer │ not null
study_id │ integer │ not null
Índices:
"studyform_pkey" PRIMARY KEY, btree (id)
Restricciones de llave foránea:
"studyform_study_id_fkey" FOREIGN KEY (study_id) REFERENCES study(id) NOT VALID

Is this really intended?

--
Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>

On 03/17/2011 05:22 PM, Alvaro Herrera wrote:
> I just ran this quick test in HEAD:
>
>
>
> and was very surprised to see that the foreign key is marked as NOT
> VALID:
>
>
>
>
> Is this really intended?

I sure hope not.

cheers

andrew

On Thu, Mar 17, 2011 at 5:29 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> Is this really intended?
>
> I sure hope not.

That's a bug. Not sure if it's a psql bug or a backend bug, but it's
definitely a bug.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Mar 17, 2011 at 5:32 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Mar 17, 2011 at 5:29 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> Is this really intended?
>>
>> I sure hope not.
>
> That's a bug.  Not sure if it's a psql bug or a backend bug, but it's
> definitely a bug.

It's a backend bug. Prior to Simon's patch, there was an existing
skip_validation flag in the Constraint node that indicated whether or
not a validation pass was necessary - in a newly created table, for
example, we know that it's NOT necessary, because the table can't
contain any rows (and therefore there can't be any rows that violate
the constraint). The patch tries to make the very same flag indicate
whether the user wants the constraint to be added with the NOT VALID
attribute, which of course falls over because the Boolean only has two
values and there are three cases (validate it, don't validate it but
do mark it valid because the table is guaranteed to be empty, don't
validate it and mark it not valid).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, 2011-03-18 at 09:39 -0400, Robert Haas wrote:
> On Thu, Mar 17, 2011 at 5:32 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > On Thu, Mar 17, 2011 at 5:29 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> >>> Is this really intended?
> >>
> >> I sure hope not.
> >
> > That's a bug. Not sure if it's a psql bug or a backend bug, but it's
> > definitely a bug.
>
> It's a backend bug. Prior to Simon's patch, there was an existing
> skip_validation flag in the Constraint node that indicated whether or
> not a validation pass was necessary - in a newly created table, for
> example, we know that it's NOT necessary, because the table can't
> contain any rows (and therefore there can't be any rows that violate
> the constraint). The patch tries to make the very same flag indicate
> whether the user wants the constraint to be added with the NOT VALID
> attribute, which of course falls over because the Boolean only has two
> values and there are three cases (validate it, don't validate it but
> do mark it valid because the table is guaranteed to be empty, don't
> validate it and mark it not valid).

Thanks Robert. Yes, my bad. Will fix.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services