[GENERAL] Re: [GENERAL] Forcefully adding a CHECK constrained

2012-05-28 Thread Catalin(ux) M. Boie
Hello.

Now I understand why I was not clear.

From what I understood, NOT VALID feature will not allow for the CHECK to be 
used in queries. So, for partitioning, my goal, is critical that the CHECK 
condition to be used.

I hope I make myself clear now: I want a possibility to add a CHECK that will 
be used for partitioning without having to read all data for validation.

of course, as I said, I will implement the future if PostgreSQL developers 
think that is useful.

Thank you for your time.

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

- Reply message -
From: Jeff Davis pg...@j-davis.com
To: Catalin(ux) M. Boie ca...@embedromix.ro
Cc: pgsql-general@postgresql.org
Subject: [GENERAL] Forcefully adding a CHECK constrained
Date: Sun, May 27, 2012 19:46


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


Re: [GENERAL] Forcefully adding a CHECK constrained

2012-05-28 Thread Marti Raudsepp
On Tue, May 15, 2012 at 12:52 PM, Catalin(ux) M. BOIE
ca...@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Re: [GENERAL] Forcefully adding a CHECK constrained

2012-05-28 Thread Tom Lane
=?utf-8?B?Q2F0YWxpbih1eCkgTS4gQm9pZQ==?= ca...@embedromix.ro writes:
 I hope I make myself clear now: I want a possibility to add a CHECK that will 
 be used for partitioning without having to read all data for validation.

Basically, this is unlikely to be accepted, because it does not conform
with the project's worldview.

If you are intent on being smarter than the database is, you could
probably create a NOT VALID constraint and then manually poke the system
catalogs to mark it as valid.  Then, when (not if) you make a mistake,
you will have only yourself to blame.

A patch that would stand some chance of getting accepted would be one
that would act like CREATE INDEX CONCURRENTLY: create the constraint as
NOT VALID (so it's getting enforced against new rows), then in
background scan all the existing rows to make sure they meet the
constraint too, then finally mark the constraint VALID.  But we don't
consider that second step to be optional.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] Forcefully adding a CHECK constrained

2012-05-27 Thread 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[GENERAL] Re: [GENERAL] Forcefully adding a CHECK constrained

2012-05-27 Thread Catalin(ux) M. Boie
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?

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

- Reply message -
From: Jeff Davis pg...@j-davis.com
To: Catalin(ux) M. BOIE ca...@embedromix.ro
Cc: pgsql-general@postgresql.org
Subject: [GENERAL] Forcefully adding a CHECK constrained
Date: Sat, May 26, 2012 20:48


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


Re: [GENERAL] Forcefully adding a CHECK constrained

2012-05-26 Thread Jeff Davis
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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[GENERAL] Forcefully adding a CHECK constrained

2012-05-15 Thread Catalin(ux) M. BOIE

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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general