Re: Transaction Question - Mailing list pgsql-general

Hi Scott

Firstly, your name rings a bell. I'm sure you're a 4D developer - I was
one too (once, but no more sadly)...

You imply that it should be possible to create an embedded C function that
can access the DB in its own thread, and therefore its own transaction. Is
there a document that would explain how this is done?

Also do you know if you can embedded java functions - I'm more comfortable
with java than C these days?

I think that for this problem, I will use SEQUENCE objects who name is
stored within the WDVolume table. It will allow me to avoid the row level
lock bottleneck, and my counters won't be rolled back on a transaction
rollback. There are only going to be less than 100 Volumes - so this
approach will be fine.

I'm amazed that nested transactions are still not yet supported. Postgres
is a mature product (from my understanding), and I can see loads of
messages asking about nested transaction support.

Thanks for your feedback.

John

Scott Ribe said:
> You could do something with threads on the backend, invisible to your Java
> middleware. I don't have enough experience to feel confident about trying
> to
> evaluate the pros and cons of (possibly) different ways of doing this. But
> given that you can write functions in C and load them into Postgres so
> that
> they can be called from plpgsql, I think you could in essence add to
> Postgres a function which when called would hand off the sequence load &
> update on a separate thread/connection, wait for its commit and
> completion,
> and return the value. Of course you still have to be careful about
> concurrency issues with this approach, so that you don't wind up with the
> 2
> threads deadlocked.
>
> That may well strike you as a gross hack. I don't particularly like it
> either, but I think it would get the job done without requiring any
> changes
> to your current code base except for the rewrite of
> GetVolumeFileReference.
>
> BTW, in reference to other suggestions: I believe that a sequence name is
> indeed just a string, so you can build the name and pass it to sequence
> functions on the fly; I know that sequences do not roll back, once a value
> is issued it is "burned" regardless of whether the enclosing transaction
> commits or not. So you should be able to have a trigger that on insert of
> a
> WDVolume row creates a corresponding sequence, then use that sequence
> within
> GetVolumeFileReference. Whether this is a good idea depends I'm sure on
> how
> many WDVolumes you'll have. I feel confident that dozens or hundreds would
> be no problem; thousands I don't have any idea; millions I wouldn't try.
>
>
> --
> Scott Ribe
> scott_ribe@killerbytes.com
> http://www.killerbytes.com/
> (303) 665-7007 voice
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>