Re: BUG #12330: ACID is broken for unique constraints

On Mon, Dec 29, 2014 at 3:53 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>> I tend to build out applications on top of functions and the
>> inability to set isolation mode inside a function confounds me
>> from using anything but 'read committed'.
>
> Hey, no problem -- just set default_transaction_isolation =
> 'serializable' in your postgresql.conf file and never override
> isolation level and you'll never have to use an explicit table lock
> or SELECT FOR UPDATE again. While you're at it, set
> default_transaction_read_only = on and only override it for
> transactions that (might) need to update and you'll have dodged the
> worst of the performance problems from serializable transactions.

ok...wow. That's pretty convincing, honestly. Your objective is
sensible and clear.

So basically there are/were two concrete concerns: compatibility
break and loss of reported detail. You've convinced me that
principled serialization code shouldn't be impacted negatively by
changing the returned sqlstate (although, perhaps, we're being a bit
too cavalier in terms of the amount of unprincipled code out there --
particularly looping pl/pgsql exception handlers).

Ideally, you'd sneak more detail about the specifics of the error into
errdetail or someplace as Jim is suggesting. That'd address the other
point. So, given those things, I'm mollified, FWIW.

As an aside, the main reason I don't run with serializable isn't
performance paranoia or OCD management of locking semantics in the
90%+ of code that doesn't need that treatment; it's because I build
code (well, chaining DML and such) in the server, always, and have no
way of managing isolation level inside of functions because by the
time the function body is invoked the snapshot is already generated.
I can make read committed transactions work with appropriate locking
but there is no way to downgrade serializable transactions in flight.
IOW, lack of proper stored procedures is what's holding me back.

merlin