Re: coalesce in plpgsql, and other style questions

--As of June 13, 2012 12:10:18 PM -0700, Jeff Davis is alleged to have said:

> Your statement was true in 9.0 and before, but in 9.1, SERIALIZABLE
> means *truly serializable*.

You're right; sorry. Bad me for relying on faulty memory and an old
manual. ;)

>> You need either a unique constraint
>
> I agree that a unique constraint is the right way to do it, because it's
> a declarative constraint.

This would be my preference as well, regardless of the rest. If you find
yourself trying to implement something that's solvable as an SQL constraint
with application-level code, you are doing something wrong.

>> Your basic assumption for how transactions work is close to how simple
>> databases work, that basically lock the whole table or database while
>> you are working on it. Which is simple and cheap to do - as long as you
>> aren't doing a whole lot in the database. If you have a lot of action
>> going on in the database, the lock resolution eventually overwhelms the
>> simplicity - which is the whole reason why there are databases like
>> Postgresql, which can maintain good performance and data integrity
>> without that locking.
>
> I strongly encourage you to do some experimentation on 9.1+ with
> serializable transactions (all transactions must be serializable for it
> to work).
>
> See if you can find any anomalies, or any performance degradation. The
> only expected performance degradation (aside from very strange cases) is
> that there will be serialization errors, and you'll need to retry those
> transactions. It does not cause any transactions to block that wouldn't
> otherwise.

I would in no way expect Postgresql to implement that change unless they
were sure it didn't cause any major issues. I'm impressed though that they
managed it in the first place. ;) My statement above was to address why
you would tend to find this type of behavior on low-end databases as the
default: Because it's cheap and easy to implement poorly. Just lock
everything. Implementing it well is another thing entirely.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------