PL/pgSQL: EXCEPTION NOSAVEPOINT

Matt Miller <mattm@epx.com> writes:

The idea is to allow a PL/pgSQL exception to not automatically rollback
the work done by the current block.

This fundamentally breaks the entire backend. You do not have the
option to continue processing after elog(ERROR); the (sub)transaction
rollback is necessary to clean up inconsistent state.

regards, tom lane

On Wed, 2005-08-03 at 16:25 -0400, Tom Lane wrote:

The idea is to allow a PL/pgSQL exception to not automatically
rollback the work done by the current block.

This fundamentally breaks the entire backend.

Yeah, but besides that, can you quick commit this to HEAD so I don't
have to keep track of it locally?

Just kidding.

You do not have the
option to continue processing after elog(ERROR); the (sub)transaction
rollback is necessary to clean up inconsistent state.

Okay, I'll look at this more closely. Can you give me an example of
what can go wrong?

Matt Miller <mattm@epx.com> writes:

On Wed, 2005-08-03 at 16:25 -0400, Tom Lane wrote:

You do not have the
option to continue processing after elog(ERROR); the (sub)transaction
rollback is necessary to clean up inconsistent state.

Okay, I'll look at this more closely. Can you give me an example of
what can go wrong?

Well, for example, failure to release locks and buffer pins held by an
abandoned query. Memory leaks. Row versions inserted into the database
that will be seen as good because they're marked as being generated by
the outer transaction, rather than coming from a subtransaction that can
be separately marked as aborted. Pretty much everything done by
AbortSubTransaction can be seen as cleanup...

The only way you could get the effect you are after would be to run a
new subtransaction for each executed query; which is not impossible
but the overhead would be appalling :-(

regards, tom lane

[redirected from -patches]

On Wed, 2005-08-03 at 16:25 -0400, Tom Lane wrote:

Matt Miller <mattm@epx.com> writes:

allow a PL/pgSQL exception to not automatically rollback
the work done by the current block.

This fundamentally breaks the entire backend. You do not have the
option to continue processing after elog(ERROR);

Okay, I think I'm beginning to see the naivete of that patch's
simplistic attempt to decouple backend error handling from transaction
management. But I still haven't found a way to meet my original need:

On Wed, 2005-08-03 at 19:58 +0000, Matt Miller wrote:

The benefit is that [PL/pgSQL] exception
handling can be used as a program flow control technique, without
invoking transaction management mechanisms. This also adds additional
means to enhanced Oracle PL/SQL compatibility.

Basically I'd like my Pl/pgSQL code to be able to utilize the try/catch
paradigm of error handling without the overhead of subtransactions and
without the effect of a rollback. If I catch the exception then
everything should be fine as far as the transaction is concerned. If
don't catch the exception, or if I re-raise it, then the enclosing block
can decide to rollback. This is more consistent with Oracle, and I have
hundreds of Oracle procs to convert across multiple databases.

I'm still thinking that some kind of hack to
src/pl/plpgsql/src/pl_exec.c is probably where I'm headed, but I'm open
to consider other approaches/advice.

[ redirected to -hackers, where it's actually on topic ]

Matt Miller <mattm@epx.com> writes:

[redirected from -patches]
On Wed, 2005-08-03 at 16:25 -0400, Tom Lane wrote:

This fundamentally breaks the entire backend. You do not have the
option to continue processing after elog(ERROR);

Okay, I think I'm beginning to see the naivete of that patch's
simplistic attempt to decouple backend error handling from transaction
management. But I still haven't found a way to meet my original need:

On Wed, 2005-08-03 at 19:58 +0000, Matt Miller wrote:

The benefit is that [PL/pgSQL] exception
handling can be used as a program flow control technique, without
invoking transaction management mechanisms. This also adds additional
means to enhanced Oracle PL/SQL compatibility.

Basically I'd like my Pl/pgSQL code to be able to utilize the try/catch
paradigm of error handling without the overhead of subtransactions and
without the effect of a rollback. If I catch the exception then
everything should be fine as far as the transaction is concerned.

The reason you aren't going to be able to manage this in the current
state of plpgsql is that plpgsql doesn't really have any interesting
computational ability "of its own". It can't even do 2+2 without
calling the main executor --- and recovering from elog(ERROR) without a
transaction rollback is not part of the executor's contract. So while
you could theoretically make a try/catch construct within plpgsql that
doesn't have subtransaction semantics, there'd basically be no way to
do anything useful within it.

You might take a look at the other PLs such as plperl; those have
behavior much closer to what you are looking for, since their
computational engine is separate from the SQL engine.

regards, tom lane

On Thu, 2005-09-01 at 18:28 -0400, Tom Lane wrote:

Matt Miller <mattm@epx.com> writes:

Basically I'd like my Pl/pgSQL code to be able to utilize the try/catch
paradigm of error handling without the overhead of subtransactions

[Pl/pgSQL] can't even do 2+2 without
calling the main executor --- and recovering from elog(ERROR) without a
transaction rollback is not part of the executor's contract.

Okay, so that's the crux regarding PL/pgSQL.

You might take a look at the other PLs such as plperl

That would defeat my goal of not rewriting all my Oracle code.

If I were fool enough to plan an attack on the main executor's exception
handling to try and disarm it of its subtransaction semantics, where
would I start? Where would I end? What would I do in between? Can New
Orleans be rebuilt above sea level?

Seriously, though, I'm willing to devote considerable time to this.
Rewriting all my Oracle code function-by-function could be painful, and
I would end up dragging other people around this company into it. I'm
still trying to hold on to my fantasy that I can hack Postgres (and
contrib/ora2pg) into submission. In the end I'm hoping that the move
from Oracle will be made easier for others.

Matt Miller <mattm@epx.com> writes:

If I were fool enough to plan an attack on the main executor's exception
handling to try and disarm it of its subtransaction semantics, where
would I start? Where would I end? What would I do in between? Can New
Orleans be rebuilt above sea level?

In general I don't think it even makes sense to think of making executor
rollback non-transactional. If the executor was running, say, an INSERT
or UPDATE that had already made some database changes before failing,
you certainly don't want those partial results to be seen as good.

ISTM what you are after is to provide some computational capability in
plpgsql that is restricted from doing any database access, and therefore
isn't subject to the above complaint. I'm not sure about a good way to
do this. Seems like your choices are to re-invent the wheel by
implementing a separate expression evaluator inside plpgsql, or to try
to create a locked-down, limited-capability option in execQual.c.
(The main problem with the latter route is to do it without adding any
overhead for normal execution, as otherwise you'll probably get shot
down on that basis.) In either case it's not immediately obvious how
you tell what is safe to allow --- Postgres' model that everything is
embedded within black-box functions doesn't help you here. The
IMMUTABLE/STABLE/VOLATILE marking of functions is close to what you
want, but not close enough.

Also, it might be possible to make the restriction "read-only database
access" instead of "no database access"; this would certainly fit a lot
better with the existing function volatility categories, but then you
still have the problem that aborting the executor is just not a
low-overhead control path. And you would have to go through just about
all of the existing subxact cleanup, such as releasing locks and buffer
pins acquired within the failing query.

regards, tom lane

Why don't you just use EnterpriseDB?

Chris

In general I don't think it even makes sense to think of making
executor rollback non-transactional.

Agreed.

I would not want to rollback some statements and not others within a
transaction. I would like a complete rollback to happen, but only when
the exception propogates out of the exception block unhandled, not when
the exception is first thrown. Maybe if I could get into the TRY
section of the PG_CATCH()/PG_TRY() construct without an intervening
elog(ERROR) then I'd have a chance ...

Seems like your choices are
...
implementing a separate expression evaluator
...
make the restriction "read-only database access"
...

Thanks for all these ideas. I'm just getting to know the PG code tree,
and I appreciate the guidance.

if I could get into the TRY section of the PG_CATCH()/PG_TRY()
construct without an intervening elog(ERROR) then I'd have a
chance ...

Sorry, I meant "the CATCH section of the PG_TRY()/PG_CATCH()
construct."

Rewriting all my Oracle code function-by-function could be painful
...
I'm still trying to hold on to my fantasy that I can hack Postgres (and
contrib/ora2pg) into submission.

Why don't you just use EnterpriseDB?

I looked at EnterpriseDB a few months ago. The installation errored.
It left stuff in /var/opt, which I consider non-standard for a Red Hat
machine. The whole product just didn't feel clean to me. I admit
that's a pretty limited and subjective evaluation, especially for a beta
product, but I was in the mode of broadly evaluating alternatives, so I
moved on. Maybe I need to look at it again.

Basically I feel more secure tracking the core project, even if I need
to maintain some of my own patches.

On Fri, Sep 02, 2005 at 02:34:50PM +0000, Matt Miller wrote:

Rewriting all my Oracle code function-by-function could be painful
...
I'm still trying to hold on to my fantasy that I can hack Postgres (and
contrib/ora2pg) into submission.

Why don't you just use EnterpriseDB?

I looked at EnterpriseDB a few months ago. The installation errored.
It left stuff in /var/opt, which I consider non-standard for a Red Hat
machine. The whole product just didn't feel clean to me. I admit
that's a pretty limited and subjective evaluation, especially for a beta
product, but I was in the mode of broadly evaluating alternatives, so I
moved on. Maybe I need to look at it again.

Well, the EnterpriseDB has much more support for Oracle syntax in
general, and PL/SQL in particular. If you didn't get past the
installation step, certainly you didn't have a feel of how the real
features work. May I suggest you at least get it working and try to
port your functions to it?

(Disclaimer: while I work for EDB, I haven't had any relationship to the
Oracle-PL/SQL layer yet, nor have I been involved at all in their
commercial offering.)

--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)

I looked at EnterpriseDB a few months ago. The installation errored.
It left stuff in /var/opt, which I consider non-standard for a Red Hat
machine. The whole product just didn't feel clean to me. I admit
that's a pretty limited and subjective evaluation, especially for a beta
product, but I was in the mode of broadly evaluating alternatives, so I
moved on. Maybe I need to look at it again.

Basically I feel more secure tracking the core project, even if I need
to maintain some of my own patches.

The EnterpriseDB guys have a final product now, and it's designed to
emulate Oracle as much as possible. I'd prefer that in production than
my own patches :)

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

The EnterpriseDB guys have a final product now, and it's designed to
emulate Oracle as much as possible.

The question at hand is whether "as much as possible" includes having
reinvented plpgsql's execution engine ... I have not seen their product,
but if they've gotten that far then they've accomplished a heck of a
lot in a very short time ...

regards, tom lane

Matt,

Seriously, though, I'm willing to devote considerable time to this.
Rewriting all my Oracle code function-by-function could be painful, and
I would end up dragging other people around this company into it.  I'm
still trying to hold on to my fantasy that I can hack Postgres (and
contrib/ora2pg) into submission.  In the end I'm hoping that the move
from Oracle will be made easier for others.

I'm happy to work with you on ora2pg, as long as we can use Perl. Joe
Conway has some useful oracle-table-bulkloading stuff I can probably talk
him out of.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

On Fri, 2005-09-02 at 12:29 -0700, Josh Berkus wrote:

still trying to hold on to my fantasy that I can hack Postgres (and
contrib/ora2pg) into submission.

I'm happy to work with you on ora2pg

Cool.

It looks like I should have referred to contrib/oracle, not
contrib/ora2pg, but you got my point.

The latest version I found of ora2pg is at
http://www.samse.fr/GPL/ora2pg/ora2pg-3.3.tar.gz This seems to be more
recent than the version at contrib/oracle. For example, this newer
version has tablespace support. Given this as a starting point, I've
made the attached changes. Mostly I've added a few new config options,
but I also made a correction to the existing EXCLUDE option, and I
corrected a couple spelling/English errors along the way.

A big thing that's lacking is conversion for stored procedures and
functions. My initial approach to this was to use Perl to post-process
the PL/SQL code dumped by the export, making it look more like proper
Pl/pgSQL (e.g. VARCHAR2->VARCHAR). I'm no Perl hacker, and when I came
across significant PL/SQL <--> PL/pgSQL differences (e.g. PL/pgSQL
exception == rollback), I added to my approach the idea of hacking
PL/pgSQL to make it look more like PL/SQL. Attacking the problem from
both ends like this, I imagined that Nirvana would be reached somewhere
in the middle.

The beginning of my Perl-based attempt to convert PL/SQL into PL/pgSQL
is a pretty simple stand-alone script. I can send it if you like, but
I'm a Perl newbie, so you can probably do much better. My attempts to
make PL/pgSQL look like PL/SQL have been posted to -hackers and -patches
over the last couple months.