pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedworking around setQuerySnapshot limitations in functions
2+ messages / 2 participants
[nested] [flat]
* working around setQuerySnapshot limitations in functions
@ 2002-12-16 16:23 Robert Treat <xzilla@users.sourceforge.net>
2002-12-17 00:12 ` Re: working around setQuerySnapshot limitations in functions Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
0 siblings, 1 reply; 2+ messages in thread
From: Robert Treat @ 2002-12-16 16:23 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: working around setQuerySnapshot limitations in functions
2002-12-16 16:23 working around setQuerySnapshot limitations in functions Robert Treat <xzilla@users.sourceforge.net>
@ 2002-12-17 00:12 ` Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
0 siblings, 0 replies; 2+ messages in thread
From: Jakub Ouhrabka @ 2002-12-17 00:12 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-sql@postgresql.org, "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
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
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-12-17 00:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-16 16:23 working around setQuerySnapshot limitations in functions Robert Treat <xzilla@users.sourceforge.net>
2002-12-17 00:12 ` Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
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