Re: coalesce in plpgsql, and other style questions

On Wed, 2012-06-13 at 09:52 -0400, Daniel Staal wrote:
> What you appear to be thinking is that the transaction locks the
> tables, and then tries to do a resolution of the lock at the end of the
> transaction - serializable comes close to that, if everyone is modifying
> the same table, but not quite.  Even under serializable, you'd still be
> able to insert invalid data using the 'select, then insert if not in
> select' logic.  (The difference is that under serializable, you couldn't
> check to see if it had happened afterwards - at least not in the same
> transaction.)

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

Try it, and see. The 'select, then insert if not in select' logic will
throw a serialization error if another transaction races it, even if
there is no UNIQUE.

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

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

Think of it as a magic mode that turns SQL race conditions into errors.
You should still use appropriate locking and declarative constraints,
because those will allow more transactions to succeed (obviously, under
intense workloads you don't want a high rollback rate). And declarative
constraints also add to the readability/maintainability.

Regards,
    Jeff Davis