Skip site navigation (1) Skip section navigation (2)

Re: coalesce in plpgsql, and other style questions

From: Daniel Staal <DStaal(at)usa(dot)net>
To: Jeff Davis <pgsql(at)j-davis(dot)com>, PGSQL-Novice <pgsql-novice(at)postgresql(dot)org>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: coalesce in plpgsql, and other style questions
Date: 2012-06-14 01:18:56
Message-ID: 7C83FD0E2492C55BB46EB51B@mac-pro.magehandbook.com (view raw or flat)
Thread:
Lists: pgsql-novice
--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.
---------------------------------------------------------------

In response to

pgsql-novice by date

Next:From: e-letterDate: 2012-06-14 22:22:50
Subject: create table from regular expressions applied to rows of multiple tables
Previous:From: Tom LaneDate: 2012-06-13 22:53:47
Subject: Re: Temporary tables ?access possible.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group