Re: locking of referenced table during constraint

Scott Shattuck <ss(at)technicalpursuit(dot)com> writes:
> ...why don't they allow the selects to read through adding a
> constraint?

Hmm. We could probably allow that --- at least for some forms of
ALTER TABLE, a ShareRowExclusive lock ought to be good enough.
(That would allow SELECT and SELECT FOR UPDATE to run in parallel,
but not any actual data changes.) Offhand I think this would be okay
for trigger changes, since SELECT and SELECT FOR UPDATE are unaffected
by triggers. I'm less sure that it's safe for any other kind of ALTER.

> It becomes time critical when the table has 3 million user account
> entries and the lock blocks people from having their login name
> verified, causing what's supposed to be a 24x7 e-commerce site to
> essentially go offline to users for 5 minutes or more just so you can
> add a constraint to a new table with no rows. Sorry, but that sucks.

The only way ALTER TABLE ADD CONSTRAINT could take five minutes is if
you are putting a new constraint on a large existing table. I don't
really see how you can expect that to be a free operation --- the system
has to look through all the existing rows to verify the constraint is
met. Fooling with the schema of large production tables is not
something you're going to do without downtime in *any* DB.

regards, tom lane