Thread: Re: [GENERAL] Forcefully adding a CHECK constrained
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" <pgsql@j-davis.com>
To: "Catalin(ux) M. Boie" <catab@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
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" <pgsql@j-davis.com>
To: "Catalin(ux) M. Boie" <catab@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
"=?utf-8?B?Q2F0YWxpbih1eCkgTS4gQm9pZQ==?=" <catab@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
toread 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