Thread: Re: PL/pgSQL: EXCEPTION NOSAVEPOINT
[ 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 > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
> > 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.