[NOVICE] coalesce in plpgsql, and other style questions

#11

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

Quote:

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.

Quote:

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.

Quote:

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

--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[NOVICE] coalesce in plpgsql, and other style questions

mailing.database.pgsql-novice mailing.database.pgsql-novice

Re: [NOVICE] coalesce in plpgsql, and other style questions - 06-13-2012 , 08:18 PM