Re: savepoint improvements

On 1/19/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> > I suspect the savepoint command is almost never used
> > outside of oracle compatibility efforts.
>
> Last I heard, we implemented it because it is in the SQL standard.
> I have no idea (nor do I much care) whether it's oracle-compatible.
> Not exactly. You haven't even made clear what you think that means,
> let alone how it would be implemented. What context is foo() supposed
> to be executed in? What happens if it fails?

right. I understand this is a nonstandard extension so the bar is
pretty high here...well, my thought was that the subtransaction could
be rolled back and foo executed in the parent transaction. In the
very early implementation of NT you could push and pop transactions
from a stack via multiple begin/end. so, in those terms the
equivalent would be:

BEGIN;
BEGIN; -- savepoint x
COMMIT;
FOO(); -- called if x fails only
COMMIT;

if foo() fails, the whole transaction is failed because that pops the
outer transaction and with savepoints you can only be one level deep.

On 1/20/07, Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org> wrote:
> Isn't the problem that you try to use psql for scripting and it doesn't
> have usual scripting power like branching (if) or looping (while,for)
> that most scripting languages have. If there was say an \if command in
> psql you could do things like this:

To be honest, I'm not a huge fan of psql tricks (error recovery being
another example) but this could provide a solution. in your opnion,
how would you use \if to query the transaction state?

merlin