From: Robert Treat <xzilla@users.sourceforge.net>
To: pgsql-sql@postgresql.org
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: working around setQuerySnapshot limitations in functions
Date: 16 Dec 2002 11:23:33 -0500
Message-ID: <1040055813.19398.49.camel@camel> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: xzilla@users.sourceforge.net, pgsql-general@postgresql.org
Subject: Re: working around setQuerySnapshot limitations in functions
In-Reply-To: <1040055813.19398.49.camel@camel>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox