Re: coalesce in plpgsql, and other style questions - Mailing list pgsql-novice

On Tue, 2012-06-12 at 17:42 -0700, Ross Boylan wrote:
> Practically, it's just me so there shouldn't be any risk.  But I'd like
> to understand the general issue.  I thought transaction would take care
> of this, so that within a transaction the state of the database does not
> change from actions in other sessions.  Then if I commit and have
> conflict, the commit fails.

That is true in SERIALIZABLE mode in version 9.1 or later. Perhaps that
will eventually be the default.

But a UNIQUE constraint on host.name will fix your particular problem
nicely and efficiently, so you don't have to be on 9.1 (though I
encourage you to upgrade to 9.1.4 if you can). And it's just good
practice to declare such constraints unless there's a reason not to.

> I guess the sequence I'm using for did assures that did is unique across
> all transactions, and so the 2 transactions would not be in conflict,
> since they have different primary keys.

You are using sequences (nextval() operates on a sequence) and a
sequence will never produce the same value from nextval() (unless you
explicitly reset it). You have a UNIQUE constraint on did, but even if
you didn't, then the values should be unique. However, I still recommend
keeping the UNIQUE constraint on host.did unless there's a reason not
to.

> But is my whole model of how transactions are operating off?  I'm
> basically generalizing from Gemstone, an object database.

I think you have the right idea, and 9.1 SERIALIZABLE mode makes what
you say true. But, unfortunately, that is not true in 9.0 or before (nor
in lower isolation modes, like the READ COMMITTED default). If you're
interested in the details, please read the 8.4 explanation here:

http://www.postgresql.org/docs/8.4/static/transaction-iso.html

thoroughly, and then see the 9.1 version:

http://www.postgresql.org/docs/8.4/static/transaction-iso.html

Each of these isolation modes has a purpose, but in my opinion, you
should use SERIALIZABLE in version 9.1+ (a.k.a. "true serializability")
unless you understand the other modes and have a reason to use one of
the other ones. I say this because true serializability matches your
intuitive understanding, while the other modes have some subtle
behaviors that might surprise you.

> > > DECLARE
> BTW, is DECLARE necessary if there are no declarations?

Nope.

> > > BEGIN
> > >         select did, canonical into hostid, canonicalid from host
> > >         where name = hostname;
> > >         if FOUND then
> > >            return;
> > >            end if;
> > >         if canonicalname is not NULL then
> > >            select did  into canonicalid from host where name = canonicalname;
> > >            if not FOUND then
> > >               insert into host (name) values(canonicalname) returning did into canonicalid;
> > >               end if;
> > >            end if;
> > >         if hostname != canonical then
> >
> > Is canonical a proper variable here? It's not in the argument list, and
> > it's not DECLAREd. Did you mean canonicalname?
> canonical is a column name in the table.  Perhaps canonical_did would be
> more appropriate for it (and rename the output parameter
> out_canonical_did from canonicalid).

Does the function actually work like it is now? It looks like
"canonical" is being used outside of the scope of any query, so I don't
see how it comes from the table. Maybe I'm still confused.

Regards,
    Jeff Davis