Re: "stored procedures" - use cases?

On Mon, Apr 25, 2011 at 12:07 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> Another point, as there appear to be diverging camps about
> supertransactional stored procedures vs. autonomous transactions, what
> would be the actual use cases of any of these features?  Let's collect
> some, so we can think of ways to make them work.

Some number of moons ago it would have been highly desirable to be
able to create daemon worker processes out of UDFs. In practice, many
such daemons want to do their own snapshot management (that is to say,
acquire new ones...) and there's no nice way to do that by extending
postgres. Instead, you cargo cult onto what autovacuum does and
release your own postgres binary use SPI from outside a snapshot.
Although it would be better still to have a worker pool type mechanic
(see the "async" discussion happening recently), being able to have
contribs or modules where one could run:

SELECT do_the_thing();

And block indefinitely doing cross-snapshot work would be pretty
useful, I feel. As a thought exercise, could one create:

SELECT autovacuum(tuning, parameters, one, through, n); as a C UDF
without bizarro snapshot mangling? (I believe we did play some tricks
to escape the snapshot even in this case, but they weren't very lucid
in the code, if memory serves).

In any case, I've encountered at least a few situations where I'd like
to be able to opt-out of getting one and exactly one snapshot in the
daemon/worker case.

--
fdr