Re: Serialization, Locking...implement processing Queue with a table

> Date: Sun, 16 Jun 2002 19:53:15 -0400
> So I've come around to agree with the position that Tatsuo and Hiroshi
> put forward in the thread mentioned above: plpgsql (and the other PL
> languages) need to do SetQuerySnapshot not only CommandCounterIncrement
> between user-supplied queries.
>
> Is anyone still unconvinced? If not, I'll try to fix it sometime soon.
>
> As that thread pointed out, there also seem to be some problems with
> plpgsql not doing enough CommandCounterIncrements when it's executing
> already-planned queries; I'll take a look at that issue at the same
> time.
> regards, tom lane

Does this mean that you will be making changes to support
READ COMMITTED mode acting like READ COMMITTED mode when inside
PL/PGSQL procedures? ... or am I pulling up an archive out of place?

I feel that my PL/PGSQL procedures are acting like they are pinned
inside a 'READ REPEATABLE' mode instead of READ COMMITTED. I'd prefer
to always have the transaction isolation level set to what I ask for
rather than this default behavior.

It seriously cripples the power of the PL/PGSQL language to have the
feature NOT operate this way. Neat thing about stored procedures is
being able to embed common logic inside the database and ensure consistency
in implementation across multiple client apps, client languages, as well
as minimize client/server traffic. If you still do not have concensus,
maybe someone just needs to ask for a new poll?

Dante

D. Dante Lorenso
dante(at)lorenso(dot)com
972-333-4139

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "D. Dante Lorenso" <dante(at)lorenso(dot)com>
Cc: "Manfred Koizar" <mkoi-pg(at)aon(dot)at>; <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, May 14, 2003 9:53 AM
Subject: Re: [GENERAL] Serialization, Locking...implement processing Queue
with a table

> "D. Dante Lorenso" <dante(at)lorenso(dot)com> writes:
> > Can someone please explain what is happening to the PL/PGSQL function
> > in regards to commit visibility and transactions?
>
> Since the function is executed as part of a single client SELECT
> command, the whole thing runs with a single snapshot; its view of
> other process' actions on the database is frozen at the start of that
> outer SELECT, even in READ COMMITTED mode.
>
> There have been past discussions about whether this is a good idea
> or not, but so far no consensus to change it, IIRC. (Look for
> "SetQuerySnapshot" in the archives, if you care.)
>
> But at any rate, because of that behavior it's really impossible to
> encapsulate the locking behavior entirely in a plpgsql function; the
> wait-and-retry loop *must* be on the client side. That leaves me
> doubting that there's much value in trying to encapsulate any of it.
> As Manfred noted, you may eventually want to add additional conditions
> to the SELECT, and that's just lots easier to do if the logic is all
> in one place rather than split between client and server code.
>
> I still like my serializable-mode solution better than any of the
> alternatives proposed so far. Manfred's latest try doesn't solve
> the race-condition problem, because the second SELECT will never
> find anything the first one didn't find. (It could if it were on
> the client side... but you don't need two selects at all with the
> serializable-mode solution.)
>
> regards, tom lane
>