Re: Validating CHECK constraints with SPI

<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>