Thread: 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 nullstudy_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@alvh.no-ip.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@dunslane.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@gmail.com> wrote: > On Thu, Mar 17, 2011 at 5:29 PM, Andrew Dunstan <andrew@dunslane.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@gmail.com> wrote: > > On Thu, Mar 17, 2011 at 5:29 PM, Andrew Dunstan <andrew@dunslane.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