Thread: Forcefully adding a CHECK constrained

Hello.

When I discovered the wonders of partitioning I quickly jumped on-board.
I had big tables used for statistics and a split was needed.

I created the parent, I linked the big table with this new parent and I
added other childs, per month.

Example:
new_stats - parent (empty)
     old_stats - child, no CHECK
     stats_2012_04 - child, with CHECK
     stats_2012_05 - child, with CHECK

The old_stats is so big that I cannot afford to add a check constraint.
But, I know that all values of the itime field are before 2012_04, so,
would be great if I could run something like:

ALTER TABLE old_stats ADD CONSTRAINT xxx CHECK (itime < 2012_04_timestamp)
FORCE;

Of course I can create an index concurrently and then add constraint using
"USING INDEX", but this means a scan of the big table.

I never looked at PostgreSQL sources, but the commit
Enable CHECK constraints to be declared NOT VALID
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=897795240cfaaed724af2f53ed2c50c9862f951f
inspired me to dive.
Is PostgreSQL's team willing to accept such a feature?

Thank you for your time!

(Please keep me on cc)

--
Catalin(ux) M. BOIE
http://kernel.embedromix.ro/

On Tue, 2012-05-15 at 12:52 +0300, Catalin(ux) M. BOIE wrote:
> The old_stats is so big that I cannot afford to add a check constraint.
> But, I know that all values of the itime field are before 2012_04, so,
> would be great if I could run something like:
>
> ALTER TABLE old_stats ADD CONSTRAINT xxx CHECK (itime < 2012_04_timestamp)
> FORCE;
>
> I never looked at PostgreSQL sources, but the commit
> Enable CHECK constraints to be declared NOT VALID
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=897795240cfaaed724af2f53ed2c50c9862f951f
> inspired me to dive.
> Is PostgreSQL's team willing to accept such a feature?

It looks like you already found the answer! Create the constraint using
NOT VALID, and then sometime later (when you can afford the full scan)
do a VALIDATE CONSTRAINT.

Unfortunately, this is only available in 9.2, which is still in beta.

http://www.postgresql.org/docs/9.2/static/sql-altertable.html

CHECK constraints don't use indexes, so CREATE INDEX CONCURRENTLY
doesn't help you.

Regards,
    Jeff Davis

On Sat, 2012-05-26 at 22:06 +0300, Catalin(ux) M. Boie wrote:
> Hello.
> Thanks for the answer.
>
> I really want to avoid reading the whole table. It is too expensive,
> and with the proposed feature will be not needed. I think is much
> faster to forcefully add the check if you know the range of data.
>
> What do you think?

Why not just create the CHECK constraint as NOT VALID, and never
validate it? It will still enforce the constraint, it just won't
validate it against your old data, which sounds like what you want.

Regards,
    Jeff Davis

On Tue, May 15, 2012 at 12:52 PM, Catalin(ux) M. BOIE
<catab@embedromix.ro> wrote:
> The old_stats is so big that I cannot afford to add a check constraint.
> But, I know that all values of the itime field are before 2012_04, so, would
> be great if I could run something like:

If you Really Really need it and if you're feeling adventurous, you
can attempt to create the constraint on another table and then
"rename" the constraint definition in pg_constraint and pg_depend
catalogs. But doing this WILL VOID YOUR WARRANTY! :)

It's fairly complicated, you have to take care to get every bit of
information right. In particular, if there are any deleted columns in
the old_stats table, the attribute numbers will not match, which can
cause errors or segfaults. Double check from pg_attribute.

And certainly do it in a test environment first; make sure with
pg_dump and restore that it understands the constraint correctly.

Regards,
Marti