Re: [PATCH] SQL assertions prototype

Josh Berkus <josh@agliodbs.com> wrote:
> On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
>> A fundamental problem with this is that it needs to handle isolation
>> reliable, so that the assertion cannot be violated when two concurrent
>> backends do things. Consider the example from the manual, which checks
>> that a table has at least one row. Now, if the table has two rows to
>> begin with, and in one backend you delete one row, and concurrently in
>> another backend you delete the other row, and then commit both
>> transactions, the assertion is violated.
>>
>> In other words, the assertions need to be checked in serializable mode.
>> Now that we have a real serializable mode, I think that's actually
>> feasible.
>
> Going back over this patch, I haven't seen any further discussion of the
> point Heikki raises above, which seems like a bit of a showstopper.
>
> Heikki, did you have specific ideas on how to solve this?  Right now my
> mind boggles.

It works fine as long as you set default_transaction_isolation =
'serializable' and never override that.  :-)  Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.

Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company