Re: Savepoint support proposal

[David Nicol]

On Wed, 9 Mar 2005 19:34:51 -0800, Jonathan Leffler
<[EMAIL PROTECTED]> wrote:

> every statement.  Besides, I can't stop userrs from using $dbh->do()
> to execute BEGIN WORK, COMMIT WORK or ROLLBACK WORK.  Or from
> preparing and executing them.

there's an idea -- restrict what is allowed in $dbh->do under some
kind of strict mode.

Basically a non-starter due to the obvious slight performance penalty,
but it might
allow weak enforcement of a site-wide database policy.  This is
politics not engineering
however, an obvious better solution is not to give to the keys to
unlicensed drivers
(please forgive the pun)

Re: Savepoint support proposal

[Steffen Goeldner]

FTR: ADO supports nested transactions:

  


The level of nesting may be limited, e.g. Access and Foxpro
support 5 levels, Oracle and SQL Server only 1.
With some bookkeeping, it should be possible to emulate
savepoints on top of nested transactions. Of course, it
is a bit weird if the database supports savepoints, ADO
maps this to nested transactions and DBD::ADO maps it
back to savepoints - that's layering.


Steffen

Re: Savepoint support proposal

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


> I already got bitten by autocommit (badly; I'm keeping quiet here) - I
> already *have* to do a load of analysis and other futzing around with
> every statement.  Besides, I can't stop userrs from using $dbh->do()
> to execute BEGIN WORK, COMMIT WORK or ROLLBACK WORK.  Or from
> preparing and executing them.

Well, it's not quite that bad. You can make a good effort and catch 99%
of it. DBD::Pg does this by checking the string to be executed:

if ($string =~ /^\s*(BEGIN|COMMIT|ROLLBACK|END|ABORT)\b/) {
die "Please use DBI functions for transaction handling\n";
}

The actual code is in C and a lot uglier than that, but that's the gist
of it. Makes it very hard for the users to get around using the DBI
methods for transaction control. We'd do the same for SAVEPOINT of course.

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200503102215
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-BEGIN PGP SIGNATURE-

iD8DBQFCMQ2UvJuQZxSWSsgRAhtIAJ9bEjoMthhte56DeP5/4MVf/lyxsACg/lk8
A/g9oUYvRFL3B8/yaecbFVo=
=CZji
-END PGP SIGNATURE-

Re: Savepoint support proposal

[Tim Bunce]

On Thu, Mar 10, 2005 at 10:54:37AM -, Martin J. Evans wrote:
> 
> On 10-Mar-2005 Tim Bunce wrote:
> > On Thu, Mar 10, 2005 at 08:54:54AM -, Martin J. Evans wrote:
> >> 
> >> although ODBC does not define what happens if one of the commits on a
> >> connection fails (this alone probably makes commiting on an environment
> >> unpopular if used at all).
> > 
> > Exactly. This (commit_all etc) won't make it into the DBI for that reason.
> > Drivers are, of course, fee to add whatever they want.
> > 
> > Tim.
> 
> Just for clarification. I was not putting this up as a proposal, it was just a
> comment on things you can't do in SQL in one go.

Sure, I know, just thought I'd use it to point out my position on
'transactional' features that can't be done right.

> If I were looking into this I would be looking at XA not this half-hearted 
> attempt in ODBC.

Yeap. That's pretty much what I said in my first message on this thread :)

Tim.

Re: Savepoint support proposal

[Paul DuBois]

For MySQL, the savepoint info is:

- Available as of MySQL 4.1.1 for InnoDB transactions.
- Set a savepoint with:
  SAVEPOINT savepoint_name
- Rollback syntax accepts optional TO SAVEPOINT clause:
  ROLLBACK [TO SAVEPOINT savepoint_name]

Re: Savepoint support proposal

[Martin J. Evans]

On 10-Mar-2005 Tim Bunce wrote:
> On Thu, Mar 10, 2005 at 08:54:54AM -, Martin J. Evans wrote:
>> 
>> although ODBC does not define what happens if one of the commits on a
>> connection fails (this alone probably makes commiting on an environment
>> unpopular if used at all).
> 
> Exactly. This (commit_all etc) won't make it into the DBI for that reason.
> Drivers are, of course, fee to add whatever they want.
> 
> Tim.

Just for clarification. I was not putting this up as a proposal, it was just a
comment on things you can't do in SQL in one go. If I were looking into this I
would be looking at XA not this half-hearted attempt in ODBC.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development

Re: Savepoint support proposal

[Tim Bunce]

On Thu, Mar 10, 2005 at 08:54:54AM -, Martin J. Evans wrote:
> 
> although ODBC does not define what happens if one of the commits on a
> connection fails (this alone probably makes commiting on an environment
> unpopular if used at all).

Exactly. This (commit_all etc) won't make it into the DBI for that reason.
Drivers are, of course, fee to add whatever they want.

Tim.

Re: Savepoint support proposal

[Tim Bunce]

On Thu, Mar 10, 2005 at 04:45:06AM -, Greg Sabino Mullane wrote:
> 
> > My position is that I'm open to being persuaded, but you'll have
> > to put in the leg work. The best place for that at this stage is
> > dbi-users as I'd like you to be able to show wider support for the
> > proposals from the user community.
> 
> That could be tough - savepoints were just recently added to PostgreSQL,
> and I don't know enough about the typical users of the other RDBMSs.
> However, all I would like at a minimum is the acceptance of
> $dbh->savepoint and friends as a future addition, so that the next version
> of DBD::Pg can use them instead of $dbh->pg_savepoint.

It's far better for an application to use pg_savepoint() than for the
DBI to prematurely standardize on an API that has to change later.

In general I don't add 'esoteric' features to the DBI API until at
least two or three separate drivers have added driver-private APIs
for the same feature. That way we have a range of practical experience
to drawn on.

> It's late. I will try and write up an API and a dbi-users proposal soon.

Feel free to bounce it off me before sending to the list so save
one round of debate on the first version.

Tim.

Re: Savepoint support proposal

[Martin J. Evans]

On 10-Mar-2005 Jonathan Leffler wrote:
> On Wed, 09 Mar 2005 08:55:21 - (GMT), Martin J. Evans
> <[EMAIL PROTECTED]> wrote:
>> Just a small point.
>> 
>> I could be wrong (I cannot look it up right now) but I think in ODBC
>> SQLEndTran
>> can issue a rollback or commit on ALL connections. Effectively, it issues
>> the
>> rollback/commit on each connection in the environment.
>> That would be a lot harder to do with $dbh->do since you'd have to do it
>> once
>> per connection.
> 
> That would have to be issued on a driver handle, wouldn't it?  And the
> driver would have to know about all its children...
> 
> $dbh->{Driver}->rollback_all
> $dbh->{Driver}->commit_all

I think so. DBD::ODBC only allocates one environment (as recommened in ODBC) but
then multiple connection handles as they are requested under that environment. A
call to:

SQLEndTran(environment handle, SQL_COMMIT)

is sort of a short hand for:

SQLEndTran(connection handle1, SQL_COMMIT)
SQLEndTran(connection handle2, SQL_COMMIT)

although ODBC does not define what happens if one of the commits on a
connection fails (this alone probably makes commiting on an environment
unpopular if used at all). With DBI, you do $dbh->commit which is equivalent to
SQLEndTran(connection handle) and DBI does not know about the ODBC environment
handle so you'd have to do (presently):

$dbh1->commit
$dbh2->commit

I don't think there is any big point here, I was just following the discussion
and was pointing out there is something you can do with ODBC in a single
call that you cannot mimic with SQL.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development

Re: Savepoint support proposal

[H.Merijn Brand]

On Wed, 9 Mar 2005 19:34:51 -0800, Jonathan Leffler
<[EMAIL PROTECTED]> wrote:

> On Wed, 9 Mar 2005 09:47:20 +0100, H.Merijn Brand <[EMAIL PROTECTED]>
> wrote:
> > On Tue, 8 Mar 2005 12:12:23 -0800, Jonathan Leffler
> > <[EMAIL PROTECTED]> wrote:
> > > On Tue, 8 Mar 2005 11:01:48 +, Tim Bunce <[EMAIL PROTECTED]>
> > > wrote:
> > > > On Mon, Mar 07, 2005 at 11:47:32PM -, Greg Sabino Mullane wrote:
> > > > > > While I don't have any particular objections, doesn't
> > > > > > Pg (and most other DBMSs supporting savepoints) have
> > > > > > SQL syntax to implement them ? If so, isn't just
> > > > > >
> > > > > > $dbh->do()
> > > > > >
> > > > > > sufficient ?
> > > > >
> > > > > Sure, but they also have syntax to implement BEGIN and ROLLBACK.
> > >
> > > It isn't wholly clear why we have $dbh->commit an $dbh->rollback; both
> > > could be simulated with $dbh->do on most databases.  However, there is
> > > some minor performance advantage on some systems.  For example, IBM
> > > Informix Dynamic Server (IDS) recognizes those statements in ESQL/C
> > > and the SQLI protocol used to transfer commands between client code
> > > (such as Perl + DBI) and the server sends a two byte command for
> > > ROLLBACK instead of sending the string.  Further, the two byte command
> > > does not have to be parsed and executed - so there is a small, but
> > > barely measurable, performance benefit to using the $dbh->rollback
> > > statement.
> > 
> > Uhh, there is much more difference!
> > I can workaround a lot of database problems in the commit/rollback calls
> > in my E/SQL code that I could not do unless I would be analyzing every do
> > call
> 
> I already got bitten by autocommit (badly; I'm keeping quiet here) - I
> already *have* to do a load of analysis and other futzing around with
> every statement.  Besides, I can't stop userrs from using $dbh->do()
> to execute BEGIN WORK, COMMIT WORK or ROLLBACK WORK.  Or from
> preparing and executing them.

True, but at least we can offer a clean and consistent interface, that when
used according to the DBI rules offers what it is expected to offer: a stable
and reliable interface to the database in question

We also cannot stop users from calling external scripts/programs that ruin our
dataset or shutdown the database while we are working in it.

-- 
H.Merijn BrandAmsterdam Perl Mongers (http://amsterdam.pm.org/)
using Perl 5.6.2, 5.8.0, 5.8.5, & 5.9.2  on HP-UX 10.20, 11.00 & 11.11,
 AIX 4.3 & 5.2, SuSE 9.1 & 9.2, and Cygwin. http://www.cmve.net/~merijn
Smoking perl: http://www.test-smoke.org,perl QA: http://qa.perl.org
 reports  to: [EMAIL PROTECTED],perl-qa@perl.org

Re: Savepoint support proposal

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


> I'd be interested to see how varied the syntax is between databases.

I honestly don't know, but I am pretty sure most are standard, with
some (DB2?) having extensions, but still supporting the same syntax.

> I'd also be interested to see a proposal for an API along with some
> real-world examples.

No problem, I can do that.

> My position is that I'm open to being persuaded, but you'll have
> to put in the leg work. The best place for that at this stage is
> dbi-users as I'd like you to be able to show wider support for the
> proposals from the user community.

That could be tough - savepoints were just recently added to PostgreSQL,
and I don't know enough about the typical users of the other RDBMSs.
However, all I would like at a minimum is the acceptance of
$dbh->savepoint and friends as a future addition, so that the next version
of DBD::Pg can use them instead of $dbh->pg_savepoint. It still seems like
an extension of the begin() and rollback() methods to me. I see
begin() and friends as being useful as methods because:

- - It standardizes the call across different databases, avoiding any
hard-coding of SQL.

- - It forces the user to go through the interface, allowing easier tracking of
the transaction state, and allowing better errors/warning, such as when
trying to use begin() on a system that does not support it, or if not
currently inside of a transaction.

Savepoints would have the same advantages as above, plus the added perk of
being able to push the list of savepoints into the database handle. It
also may have implications for the disconnect() method.

It's late. I will try and write up an API and a dbi-users proposal soon.

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200503092341
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-BEGIN PGP SIGNATURE-

iD8DBQFCL9CavJuQZxSWSsgRAgCRAJ0fDia03V56yXNR+yMTvaOBolKl4QCggxyy
LFvMNCPTAYzWR+9WN4+gekI=
=tQpc
-END PGP SIGNATURE-

Re: [dbi] Re: Savepoint support proposal

[Jonathan Leffler]

On Wed, 09 Mar 2005 15:34:24 -0800, Dean Arnold <[EMAIL PROTECTED]> wrote:
> Dan Scott wrote:
> > On Wed, 9 Mar 2005 13:29:40 -0800, Darren Duncan
> > <[EMAIL PROTECTED]> wrote:
> >
> >>At 8:55 AM + 3/9/05, Martin J. Evans wrote:
> >>
> >>>Just a small point.
> >>>I could be wrong (I cannot look it up right now) but I think in ODBC
> >>>SQLEndTran
> >>>can issue a rollback or commit on ALL connections. 
> >
> > Can someone provide a scenario where you would want to issue a global
> > rollback or commit?
> >
> > Perhaps (probably) I'm suffering from a limited imagination here, but
> > it seems like you would want to rollback or commit on a connection by
> > connection basis.

> 2PC. But DBI doesn't support that (yet).

There's a bit more to 2PC than just that, unfortunately.  In
particular, the participant transactions have to be told by the
coordinate that a commit is imminent, so they can indicate whether
they can commit.  (It's easy if they can't; they roll back and say
"can't commit".)  Assuming they can commit, they log a record
indicating that they're in a state where they can commit or rollback -
and the transaction will not then be rolled back by accident until the
coordinator has issued a decision.  When the coordinator records its
decision, it broadcasts that to the participants, who then act
accordingly.  If a participant crashes between the time when it
indicates that it can commit and it gets a decision from the
coordinator, it contacts the coordinator after recovering (and
reclaiming all the locks it held) and decides whether to commit or
rollback.  There are some wrinkles here (presumed commit, presumed
abort, etc) and complications (heuristic rollback when a commit should
have occurred), but the key point which a naive implementation based
an raw non-2PC transaction support is the recovery in crash scenarios
- which is critical.

-- 
Jonathan Leffler <[EMAIL PROTECTED]>  #include 
Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

Re: [dbi] Re: Savepoint support proposal

[Jonathan Leffler]

On Wed, 09 Mar 2005 08:55:21 - (GMT), Martin J. Evans
<[EMAIL PROTECTED]> wrote:
> Just a small point.
> 
> I could be wrong (I cannot look it up right now) but I think in ODBC 
> SQLEndTran
> can issue a rollback or commit on ALL connections. Effectively, it issues the
> rollback/commit on each connection in the environment.
> That would be a lot harder to do with $dbh->do since you'd have to do it once
> per connection.

That would have to be issued on a driver handle, wouldn't it?  And the
driver would have to know about all its children...

$dbh->{Driver}->rollback_all
$dbh->{Driver}->commit_all

-- 
Jonathan Leffler <[EMAIL PROTECTED]>  #include 
Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

Re: Savepoint support proposal

[Jonathan Leffler]

On Wed, 9 Mar 2005 09:47:20 +0100, H.Merijn Brand <[EMAIL PROTECTED]> wrote:
> On Tue, 8 Mar 2005 12:12:23 -0800, Jonathan Leffler
> <[EMAIL PROTECTED]> wrote:
> > On Tue, 8 Mar 2005 11:01:48 +, Tim Bunce <[EMAIL PROTECTED]> wrote:
> > > On Mon, Mar 07, 2005 at 11:47:32PM -, Greg Sabino Mullane wrote:
> > > > > While I don't have any particular objections, doesn't
> > > > > Pg (and most other DBMSs supporting savepoints) have
> > > > > SQL syntax to implement them ? If so, isn't just
> > > > >
> > > > > $dbh->do()
> > > > >
> > > > > sufficient ?
> > > >
> > > > Sure, but they also have syntax to implement BEGIN and ROLLBACK.
> >
> > It isn't wholly clear why we have $dbh->commit an $dbh->rollback; both
> > could be simulated with $dbh->do on most databases.  However, there is
> > some minor performance advantage on some systems.  For example, IBM
> > Informix Dynamic Server (IDS) recognizes those statements in ESQL/C
> > and the SQLI protocol used to transfer commands between client code
> > (such as Perl + DBI) and the server sends a two byte command for
> > ROLLBACK instead of sending the string.  Further, the two byte command
> > does not have to be parsed and executed - so there is a small, but
> > barely measurable, performance benefit to using the $dbh->rollback
> > statement.
> 
> Uhh, there is much more difference!
> I can workaround a lot of database problems in the commit/rollback calls in my
> E/SQL code that I could not do unless I would be analyzing every do call

I already got bitten by autocommit (badly; I'm keeping quiet here) - I
already *have* to do a load of analysis and other futzing around with
every statement.  Besides, I can't stop userrs from using $dbh->do()
to execute BEGIN WORK, COMMIT WORK or ROLLBACK WORK.  Or from
preparing and executing them.

-- 
Jonathan Leffler <[EMAIL PROTECTED]>  #include 
Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

Re: [dbi] Re: Savepoint support proposal

[Dean Arnold]

Dan Scott wrote:
On Wed, 9 Mar 2005 13:29:40 -0800, Darren Duncan
<[EMAIL PROTECTED]> wrote:
At 8:55 AM + 3/9/05, Martin J. Evans wrote:
Just a small point.
I could be wrong (I cannot look it up right now) but I think in ODBC
SQLEndTran
can issue a rollback or commit on ALL connections. Effectively, it issues the
rollback/commit on each connection in the environment.
That would be a lot harder to do with $dbh->do since you'd have to do it once
per connection.
If you wanted a global action in DBI, then reasonable syntax could be
to issue the rollback/commit on the DBI class name rather than on a
connection handle object.  Either that, or restructure DBI so that
the first thing a user does is create an "environment handle" object,
off of which they call connect(), which incidentally saves us having
to use any package globals. -- Darren Duncan

Can someone provide a scenario where you would want to issue a global
rollback or commit?
Perhaps (probably) I'm suffering from a limited imagination here, but
it seems like you would want to rollback or commit on a connection by
connection basis.
Dan
2PC. But DBI doesn't support that (yet).
However, some of these arguments are starting to
bring me around to the original notion. If DBI
ever gets a full 2PC capability, such capability
may be desirable (or at least, the creation of
"commit groups", as an earlier poster intimated.)
Dean Arnold
Presicient Corp.

Re: [dbi] Re: Savepoint support proposal

[Dan Scott]

On Wed, 9 Mar 2005 13:29:40 -0800, Darren Duncan
<[EMAIL PROTECTED]> wrote:
> At 8:55 AM + 3/9/05, Martin J. Evans wrote:
> >Just a small point.
> >I could be wrong (I cannot look it up right now) but I think in ODBC
> >SQLEndTran
> >can issue a rollback or commit on ALL connections. Effectively, it issues the
> >rollback/commit on each connection in the environment.
> >That would be a lot harder to do with $dbh->do since you'd have to do it once
> >per connection.
> 
> If you wanted a global action in DBI, then reasonable syntax could be
> to issue the rollback/commit on the DBI class name rather than on a
> connection handle object.  Either that, or restructure DBI so that
> the first thing a user does is create an "environment handle" object,
> off of which they call connect(), which incidentally saves us having
> to use any package globals. -- Darren Duncan

Can someone provide a scenario where you would want to issue a global
rollback or commit?

Perhaps (probably) I'm suffering from a limited imagination here, but
it seems like you would want to rollback or commit on a connection by
connection basis.

Dan

RE: [dbi] Re: Savepoint support proposal

[Darren Duncan]

At 8:55 AM + 3/9/05, Martin J. Evans wrote:
Just a small point.
I could be wrong (I cannot look it up right now) but I think in ODBC 
SQLEndTran
can issue a rollback or commit on ALL connections. Effectively, it issues the
rollback/commit on each connection in the environment.
That would be a lot harder to do with $dbh->do since you'd have to do it once
per connection.
If you wanted a global action in DBI, then reasonable syntax could be 
to issue the rollback/commit on the DBI class name rather than on a 
connection handle object.  Either that, or restructure DBI so that 
the first thing a user does is create an "environment handle" object, 
off of which they call connect(), which incidentally saves us having 
to use any package globals. -- Darren Duncan

RE: [dbi] Re: Savepoint support proposal

[Martin J. Evans]

On 08-Mar-2005 Jonathan Leffler wrote:
> I'm keeping this on dbi-dev pro tem.  Greg - I suggest you ask the
> question on dbi-users.
> 
> 
> On Tue, 8 Mar 2005 11:01:48 +, Tim Bunce <[EMAIL PROTECTED]> wrote:
>> On Mon, Mar 07, 2005 at 11:47:32PM -, Greg Sabino Mullane wrote:
>> > > While I don't have any particular objections, doesn't
>> > > Pg (and most other DBMSs supporting savepoints) have
>> > > SQL syntax to implement them ? If so, isn't just
>> > >
>> > > $dbh->do()
>> > >
>> > > sufficient ?
>> >
>> > Sure, but they also have syntax to implement BEGIN and ROLLBACK.
> 
> It isn't wholly clear why we have $dbh->commit an $dbh->rollback; both
> could be simulated with $dbh->do on most databases.  However, there is
> some minor performance advantage on some systems.  For example, IBM
> Informix Dynamic Server (IDS) recognizes those statements in ESQL/C
> and the SQLI protocol used to transfer commands between client code
> (such as Perl + DBI) and the server sends a two byte command for
> ROLLBACK instead of sending the string.  Further, the two byte command
> does not have to be parsed and executed - so there is a small, but
> barely measurable, performance benefit to using the $dbh->rollback
> statement.

Just a small point.

I could be wrong (I cannot look it up right now) but I think in ODBC SQLEndTran
can issue a rollback or commit on ALL connections. Effectively, it issues the 
rollback/commit on each connection in the environment.
That would be a lot harder to do with $dbh->do since you'd have to do it once
per connection.



Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development

Re: Savepoint support proposal

[H.Merijn Brand]

On Tue, 8 Mar 2005 12:12:23 -0800, Jonathan Leffler
<[EMAIL PROTECTED]> wrote:

> I'm keeping this on dbi-dev pro tem.  Greg - I suggest you ask the
> question on dbi-users.
> 
> 
> On Tue, 8 Mar 2005 11:01:48 +, Tim Bunce <[EMAIL PROTECTED]> wrote:
> > On Mon, Mar 07, 2005 at 11:47:32PM -, Greg Sabino Mullane wrote:
> > > > While I don't have any particular objections, doesn't
> > > > Pg (and most other DBMSs supporting savepoints) have
> > > > SQL syntax to implement them ? If so, isn't just
> > > >
> > > > $dbh->do()
> > > >
> > > > sufficient ?
> > >
> > > Sure, but they also have syntax to implement BEGIN and ROLLBACK.
> 
> It isn't wholly clear why we have $dbh->commit an $dbh->rollback; both
> could be simulated with $dbh->do on most databases.  However, there is
> some minor performance advantage on some systems.  For example, IBM
> Informix Dynamic Server (IDS) recognizes those statements in ESQL/C
> and the SQLI protocol used to transfer commands between client code
> (such as Perl + DBI) and the server sends a two byte command for
> ROLLBACK instead of sending the string.  Further, the two byte command
> does not have to be parsed and executed - so there is a small, but
> barely measurable, performance benefit to using the $dbh->rollback
> statement.

Uhh, there is much more difference!
I can workaround a lot of database problems in the commit/rollback calls in my
E/SQL code that I could not do unless I would be analyzing every do call

> > > I see the savepoints as an extension of the same sort of
> > > transaction processing the DBI dos there. Certainly, drivers are
> > > welcome to implementit any way they want (including allowing
> > > savepoints via "do") but the advantages include being able to
> > > track the savepoints closely, even allowing things like returning
> > > an array of the current savepoints to the application. It also
> > > abstracts the savepoint manipulation away from knowing the exact
> > > SQL for your particular DBMS, one of the goals of DBI after all. :)
> > 
> > I'd be interested to see how varied the syntax is between databases.

-- 
H.Merijn BrandAmsterdam Perl Mongers (http://amsterdam.pm.org/)
using Perl 5.6.2, 5.8.0, 5.8.3, & 5.9.2  on HP-UX 10.20, 11.00 & 11.11,
  AIX 4.3, SuSE 9.0 pro 2.4.21 & Win2k. http://www.cmve.net/~merijn
Smoking perl: smokers@perl.org, perl QA: http://qa.perl.org
  reports to: [EMAIL PROTECTED],perl-qa@perl.org

Re: Savepoint support proposal

[Jonathan Leffler]

I'm keeping this on dbi-dev pro tem.  Greg - I suggest you ask the
question on dbi-users.


On Tue, 8 Mar 2005 11:01:48 +, Tim Bunce <[EMAIL PROTECTED]> wrote:
> On Mon, Mar 07, 2005 at 11:47:32PM -, Greg Sabino Mullane wrote:
> > > While I don't have any particular objections, doesn't
> > > Pg (and most other DBMSs supporting savepoints) have
> > > SQL syntax to implement them ? If so, isn't just
> > >
> > > $dbh->do()
> > >
> > > sufficient ?
> >
> > Sure, but they also have syntax to implement BEGIN and ROLLBACK.

It isn't wholly clear why we have $dbh->commit an $dbh->rollback; both
could be simulated with $dbh->do on most databases.  However, there is
some minor performance advantage on some systems.  For example, IBM
Informix Dynamic Server (IDS) recognizes those statements in ESQL/C
and the SQLI protocol used to transfer commands between client code
(such as Perl + DBI) and the server sends a two byte command for
ROLLBACK instead of sending the string.  Further, the two byte command
does not have to be parsed and executed - so there is a small, but
barely measurable, performance benefit to using the $dbh->rollback
statement.

> > I see the savepoints as an extension of the same sort of
> > transaction processing the DBI dos there. Certainly, drivers are
> > welcome to implementit any way they want (including allowing
> > savepoints via "do") but the advantages include being able to
> > track the savepoints closely, even allowing things like returning
> > an array of the current savepoints to the application. It also
> > abstracts the savepoint manipulation away from knowing the exact
> > SQL for your particular DBMS, one of the goals of DBI after all. :)
> 
> I'd be interested to see how varied the syntax is between databases.

The SQL 2003 (1999) syntax is in section 16 of ISO/IEC 9075-2:2003:

 16 Transaction management

16.1 

Start an SQL-transaction and set its characteristics.

  ::=  START TRANSACTION [
 [ {   }...] ]

  ::=   |  | 

  ::=  READ ONLY | READ WRITE

  ::=  ISOLATION LEVEL 

  ::=  READ UNCOMMITTED | READ COMMITTED |
REPEATABLE READ | SERIALIZABLE

  ::=  DIAGNOSTICS SIZE 

  ::=  

16.2 

Set the characteristics of the next SQL-transaction for the SQL-agent.
NOTE 402 - This statement has no effect on any SQL-transactions
subsequent to the next SQL-transaction.

  ::=  SET [ LOCAL ] 

  ::=  TRANSACTION 
[ {   }... ]

16.3 

If an SQL-transaction is currently active, then set the constraint
mode for that SQL-transaction in the current SQL-session. If no
SQL-transaction is currently active, then set the constraint mode for
the next SQL-transaction in the current SQL-session for the SQL-agent.
NOTE 404 â This statement has no effect on any SQL-transactions
subsequent to this SQL-transaction.

  ::=  SET CONSTRAINTS  { DEFERRED | IMMEDIATE }

  ::=  ALL |  [ { 
 }... ]

16.4 

Establish a savepoint.

  ::=  SAVEPOINT 

  ::=  

16.5 

Destroy a savepoint.

  ::=  RELEASE SAVEPOINT 

16.6 

Terminate the current SQL-transaction with commit.

  ::=  COMMIT [ WORK ] [ AND [ NO ] CHAIN ]

16.7 

Terminate the current SQL-transaction with rollback, or rollback all
actions affecting SQL-data and/or schemas since the establishment of a
savepoint.

  ::=  ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ]
[  ]

  ::=  TO SAVEPOINT  

(If you want to see the hyperlinked HTML from which I cut'n'pasted the
syntax, tell me.  Tools and all available on request.)

As it happens, IDS does not yet support savepoints; it's on my list of
things I'm looking at for a future release.  But if we implement them,
it will be using the standard syntax first, and any IBM proprietary
variations second.

> I'd also be interested to see a proposal for an API along with some
> real-world examples.
> 
> My position is that I'm open to being persuaded, but you'll have
> to put in the leg work. The best place for that at this stage is
> dbi-users as I'd like you to be able to show wider support for the
> proposals from the user community.
> 
> Tim.
> 


-- 
Jonathan Leffler <[EMAIL PROTECTED]>  #include 
Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

Re: Savepoint support proposal

[Tim Bunce]

On Mon, Mar 07, 2005 at 11:47:32PM -, Greg Sabino Mullane wrote:
> 
> > While I don't have any particular objections, doesn't
> > Pg (and most other DBMSs supporting savepoints) have
> > SQL syntax to implement them ? If so, isn't just
> >
> > $dbh->do()
> >
> > sufficient ?
>   
> Sure, but they also have syntax to implement BEGIN and ROLLBACK.
> I see the savepoints as an extension of the same sort of
> transaction processing the DBI dos there. Certainly, drivers are
> welcome to implementit any way they want (including allowing
> savepoints via "do") but the advantages include being able to
> track the savepoints closely, even allowing things like returning
> an array of the current savepoints to the application. It also
> abstracts the savepoint manipulation away from knowing the exact
> SQL for your particular DBMS, one of the goals of DBI after all. :)

I'd be interested to see how varied the syntax is between databases.

I'd also be interested to see a proposal for an API along with some
real-world examples.

My position is that I'm open to being persuaded, but you'll have
to put in the leg work. The best place for that at this stage is
dbi-users as I'd like you to be able to show wider support for the
proposals from the user community.

Tim.

Re: Savepoint support proposal

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

  
> While I don't have any particular objections, doesn't
> Pg (and most other DBMSs supporting savepoints) have
> SQL syntax to implement them ? If so, isn't just
>
> $dbh->do()
>
> sufficient ?
  
Sure, but they also have syntax to implement BEGIN and ROLLBACK.
I see the savepoints as an extension of the same sort of
transaction processing the DBI dos there. Certainly, drivers are
welcome to implementit any way they want (including allowing
savepoints via "do") but the advantages include being able to
track the savepoints closely, even allowing things like returning
an array of the current savepoints to the application. It also
abstracts the savepoint manipulation away from knowing the exact
SQL for your particular DBMS, one of the goals of DBI after all. :)

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200503070020
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-BEGIN PGP SIGNATURE-

iD8DBQFCK+S/vJuQZxSWSsgRAsWoAKCpoHsJrjzd9z/tcUMiST1W3CRywgCeP3oy
7VN7ahnLVwVLCFU+PF/IXrI=
=KDYY
-END PGP SIGNATURE-

Re: Savepoint support proposal

[Tim Bunce]

On Sun, Mar 06, 2005 at 08:41:01PM -0800, Dean Arnold wrote:
> Greg Sabino Mullane wrote:
> >
> >We are working on implementing savepoint support in DBD::Pg, and
> >someone pointed out (quite rightly) that perhaps there should be
> >DBI method support for savepoints, as they are a standard SQL
> >construct used by more than just PostgreSQL. So I would like to
> >propose that DBI implement a savepoint, release, and rollbackto
> >method, similar to the existing begin, commit, and rollback
> >methods for database handles. In short, these would be simple
> >wrappers, with the actual implementation left to the DBDs. I can
> >whip up a prototype and more detailed docs, but wanted to see if
> >there was any input or objections before I did.
> >
> >Thanks,
> >- --
> >Greg Sabino Mullane [EMAIL PROTECTED]
> 
> While I don't have any particular objections, doesn't
> Pg (and most other DBMSs supporting savepoints) have
> SQL syntax to implement them ? If so, isn't just
> 
> $dbh->do()
> 
> sufficient ?
> 
> my 2 cents,

Mine too.

The only significant reason I can see for a specific API for savepoints
would be to support distributed transaction management (ala XA).
If that is the only significant reason then any API proposals should
be made in that context.

Tim.

Re: Savepoint support proposal

[Dean Arnold]

Greg Sabino Mullane wrote:
We are working on implementing savepoint support in DBD::Pg, and
someone pointed out (quite rightly) that perhaps there should be
DBI method support for savepoints, as they are a standard SQL
construct used by more than just PostgreSQL. So I would like to
propose that DBI implement a savepoint, release, and rollbackto
method, similar to the existing begin, commit, and rollback
methods for database handles. In short, these would be simple
wrappers, with the actual implementation left to the DBDs. I can
whip up a prototype and more detailed docs, but wanted to see if
there was any input or objections before I did.
Thanks,
- --
Greg Sabino Mullane [EMAIL PROTECTED]
While I don't have any particular objections, doesn't
Pg (and most other DBMSs supporting savepoints) have
SQL syntax to implement them ? If so, isn't just
$dbh->do()
sufficient ?
my 2 cents,
Dean Arnold
Presicient Corp.

Savepoint support proposal

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


We are working on implementing savepoint support in DBD::Pg, and
someone pointed out (quite rightly) that perhaps there should be
DBI method support for savepoints, as they are a standard SQL
construct used by more than just PostgreSQL. So I would like to
propose that DBI implement a savepoint, release, and rollbackto
method, similar to the existing begin, commit, and rollback
methods for database handles. In short, these would be simple
wrappers, with the actual implementation left to the DBDs. I can
whip up a prototype and more detailed docs, but wanted to see if
there was any input or objections before I did.

Thanks,
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200503060449
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iD8DBQFCKtJXvJuQZxSWSsgRAu0YAKDF+UCOI5wXNjEAlvIYpzd2EwtmawCffaHa
vEgSlMJmZLq9B2PzXHe4+BM=
=yFW+
-END PGP SIGNATURE-