Обсуждение: Validating CHECK constraints with SPI
Hi all,
Since the table is locked to updates while the constraint is validating, this means you have to jump through hoops if you want to add a CHECK constraint to a large table in a production setting. This validation could be considerably faster if we enabled it to use relevant indexes or other constraints. Is there a reason not to make an SPI call here, instead?
This would make it possible to do something like:
postgres=# CREATE INDEX CONCURRENTLY foo_temp_idx ON my_table (id) WHERE foo = 'bar';
postgres=# SELECT COUNT(*) FROM my_table WHERE foo = 'bar'; -- Make sure this is 0.
postgres=# ALTER TABLE my_table ADD CONSTRAINT my_check CHECK (foo != 'bar');
postgres=# DROP INDEX foo_temp_idx;
The third step here would be fast, because it would be able to use foo_temp_idx under the hood. Additionally, it would be easy to get a sense for how long this operation will lock your table by timing the query in the second step. (I suppose the latter is true already if you do the same with enable_indexscan off, but that requires knowing that PostgreSQL is going to do the seq scan no matter what.)
Would y'all be open to a patch that made this change?
Best,
-Dan
Dan Robinson wrote: > Hi all, > > If I'm reading correctly in src/backend/commands/tablecmds.c, it looks like > PostgreSQL does a full table scan in validateCheckConstraint and in the > constraint validation portion of ATRewriteTable. > > Since the table is locked to updates while the constraint is validating, > this means you have to jump through hoops if you want to add a CHECK > constraint to a large table in a production setting. This validation could > be considerably faster if we enabled it to use relevant indexes or other > constraints. Is there a reason not to make an SPI call here, instead? I don't think SPI would help you here. But I think you would like to add the constraint as NOT VALID and then do an ALTER TABLE .. VALIDATE CONSTRAINT command afterwards. In 9.4, this doesn't require AccessExclusive lock on the table. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Dan Robinson <dan@drob.us> writes:
> Since the table is locked to updates while the constraint is validating,
> this means you have to jump through hoops if you want to add a CHECK
> constraint to a large table in a production setting. This validation could
> be considerably faster if we enabled it to use relevant indexes or other
> constraints. Is there a reason not to make an SPI call here, instead?
This seems like a lot of work for a gain that would only occur sometimes,
ie if the CHECK happened to correspond to a usable index condition.
I realize your point is that a clever DBA might intentionally create
such an index, but I don't think that people would bother in practice.
It's not any simpler, nor faster, than using the existing approach with
ALTER TABLE ADD CONSTRAINT NOT VALID followed by ALTER TABLE VALIDATE
CONSTRAINT.
regards, tom lane
<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Wed, Oct 29, 2014 at 7:17 AM, Alvaro Herrera <span dir="ltr"><<ahref="mailto:alvherre@2ndquadrant.com" target="_blank">alvherre@2ndquadrant.com</a>></span> wrote:<br/><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span>Dan Robinsonwrote:<br /> > Hi all,<br /> ><br /> > If I'm reading correctly in src/backend/commands/tablecmds.c, itlooks like<br /> > PostgreSQL does a full table scan in validateCheckConstraint and in the<br /> > constraint validationportion of ATRewriteTable.<br /> ><br /> > Since the table is locked to updates while the constraint is validating,<br/> > this means you have to jump through hoops if you want to add a CHECK<br /> > constraint to a largetable in a production setting. This validation could<br /> > be considerably faster if we enabled it to use relevantindexes or other<br /> > constraints. Is there a reason not to make an SPI call here, instead?<br /><br /></span>Idon't think SPI would help you here. But I think you would like to<br /> add the constraint as NOT VALID and thendo an ALTER TABLE .. VALIDATE<br /> CONSTRAINT command afterwards. In 9.4, this doesn't require<br /> AccessExclusivelock on the table.</blockquote></div><br />Interesting! I hadn't seen the patch that makes ALTER TABLE ...VALIDATE CONSTRAINT require only ShareUpdateExclusive. Very cool.</div><div class="gmail_extra"><br /></div><div class="gmail_extra">Yes,that makes this change totally unnecessary.</div><div class="gmail_extra"><br /></div><div class="gmail_extra">-Dan</div></div>
On Wed, Oct 29, 2014 at 10:24:26AM -0400, Tom Lane wrote: > Dan Robinson <dan@drob.us> writes: > > Since the table is locked to updates while the constraint is validating, > > this means you have to jump through hoops if you want to add a CHECK > > constraint to a large table in a production setting. This validation could > > be considerably faster if we enabled it to use relevant indexes or other > > constraints. Is there a reason not to make an SPI call here, instead? > > This seems like a lot of work for a gain that would only occur sometimes, > ie if the CHECK happened to correspond to a usable index condition. > I realize your point is that a clever DBA might intentionally create > such an index, but I don't think that people would bother in practice. Consider the case of adding a NOT NULL constraint. Most single-column btree indexes can quickly determine whether the column contains nulls, so the DBA may well get the benefit on the strength of an already-present index. > It's not any simpler, nor faster, than using the existing approach with > ALTER TABLE ADD CONSTRAINT NOT VALID followed by ALTER TABLE VALIDATE > CONSTRAINT. There will be no point in building a throwaway index for this, agreed.