Subject: working around setQuerySnapshot limitations in functions
From: Robert Treat <xzilla@users.sourceforge.net>
To: pgsql-sql@postgresql.org
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 16 Dec 2002 11:23:33 -0500
Message-Id: <1040055813.19398.49.camel@camel>
Mime-Version: 1.0

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