Date: Tue, 17 Dec 2002 01:12:22 +0100 (CET)
From: Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: pgsql-sql@postgresql.org,
	"pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: working around setQuerySnapshot limitations in functions
In-Reply-To: <1040055813.19398.49.camel@camel>
Message-ID: <Pine.LNX.4.44.0212170105120.21881-100000@server>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

Hi,

I'm no expert on this but I think that some cases can be work around using
foo updates instead of selects. See threads "LOCK TABLE oddness in PLpgSQL
function called via JDBC" and "SetQuerySnapshot, once again" in the
archives.
But I think there is no general work around using only plpgsql. I'd also
love to hear about one...

HTH,

kuba

> I have a function called move_tree() which moves a node from 1 parent to
> another (in a nested set tree) by computing some necessary values, then
> updating the nodes as needed.  The problem I have is that when the
> function is called multiple times simultaneously, each function does a
> setQuerySnapshot at the start of the function, and then cannot see the
> changes made by the other function calls. I had thought to do a lock on
> the table at the start of my function, but due to the fact
> setQuerySnapshot is called at the function start (before I can acquire a
> lock) I can't guarantee no changes have been made before the function
> tries to make changes. ISTM the only way around this is to do a lock on
> the table before calling the function. The major downside to that it
> requires me to put that logic inside any apps that use the function,
> rather than being able to keep it inside the database.  Does anyone see
> another work-around?
>
> Robert Treat
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>