On 11/15/05, Tyler MacDonald
> Is there a way to ask DBI if it is already in a transaction?
Read the AutCommit flag. :-)
--
Often it does seem a pity that Noah and his party did not miss the
boat. (Mark Twain)
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org
Jochen Wiedmann
> > Is there a way to ask DBI if it is already in a transaction?
> Read the AutCommit flag. :-)
It says,
Enable transactions (by turning "AutoCommit" off) until the next
call to "commit" or "rollback". After the next "commit" or "roll-
back", "AutoCommit" will automatically be turned on again.
If "AutoCommit" is already off when "begin_work" is called then it
does nothing except return an error. If the driver does not support
transactions then when "begin_work" attempts to set "AutoCommit"
off the driver will trigger a fatal error.
However, when using the DBD::Pg driver, begin_work only triggers an error if
the driver was *initialized* with AutoCommit off, and if you begin_work with
AutoCommit on, the next query to $dbh->{AutoCommit} still returns true, and
transactions nest successfully. So is the DBD::Pg driver in error then? (In
other words, there's no support for nested transactions in DBI at all?)
- Tyler
Jochen Wiedmann
> > Is there a way to ask DBI if it is already in a transaction?
> Read the AutCommit flag. :-)
It says,
Enable transactions (by turning "AutoCommit" off) until the next
call to "commit" or "rollback". After the next "commit" or "roll-
back", "AutoCommit" will automatically be turned on again.
If "AutoCommit" is already off when "begin_work" is called then it
does nothing except return an error. If the driver does not support
transactions then when "begin_work" attempts to set "AutoCommit"
off the driver will trigger a fatal error.
However, when using the DBD::Pg driver, begin_work only triggers an error if
the driver was *initialized* with AutoCommit off, and if you begin_work with
AutoCommit on, the next query to $dbh->{AutoCommit} still returns true, and
transactions nest successfully. So is the DBD::Pg driver in error then? (In
other words, there's no support for nested transactions in DBI at all?)
- Tyler
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
On Mon, Nov 14, 2005 at 11:41:42PM -0800, Tyler MacDonald wrote:
> Jochen Wiedmann
> > > Is there a way to ask DBI if it is already in a transaction?
> > Read the AutCommit flag. :-)
>
> It says,
>
> Enable transactions (by turning "AutoCommit" off) until the next
> call to "commit" or "rollback". After the next "commit" or "roll-
> back", "AutoCommit" will automatically be turned on again.
> If "AutoCommit" is already off when "begin_work" is called then it
> does nothing except return an error. If the driver does not support
> transactions then when "begin_work" attempts to set "AutoCommit"
> off the driver will trigger a fatal error.
>
> However, when using the DBD::Pg driver, begin_work only triggers an error if
> the driver was *initialized* with AutoCommit off, and if you begin_work with
> AutoCommit on, the next query to $dbh->{AutoCommit} still returns true, and
> transactions nest successfully. So is the DBD::Pg driver in error then?
Looks like it.
> (In other words, there's no support for nested transactions in DBI at all?)
REAL nested transactions, i.e., creating savepoints and rolling back to
savepoints, are done via SQL. So the DBI supports nested transactions
just as much as it supports SELECT statements.
You refer later to the DBI::Transaction module in CPAN. I've looked at
it and it's not clear to me what it's trying to achieve (or even why or how)
but perhaps I'm being extra dense today.
I'll guess that what you're really after is to be able to call begin_work
again whilst an earlier begin_work is in effect and have the DBI keep a
counter of how deeply nested the begin_work calls are. Then commit would
decrement the counter and only commit at the outer most level.
sub foo {
begin_work
...
commit # doesn't actually commit here if called from bar()
}
sub bar {
begin_work
...
foo()
...
commit
}
If you really want that then it's straightforward to implement via a subclass.
Tim.
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Tim Bunce
> I'll guess that what you're really after is to be able to call begin_work
> again whilst an earlier begin_work is in effect and have the DBI keep a
> counter of how deeply nested the begin_work calls are. Then commit would
> decrement the counter and only commit at the outer most level.
>
> If you really want that then it's straightforward to implement via a
> subclass.
This has been done. I'm only using it in two other packages so far,
but both those and it's own unit tests seem to work well.
http://search.cpan.org/~CRAKRJACK/DBIx-Transaction-0.001/
I've got some ideas for enhancements too, but those are a bit more
vauge. One of them is that there's differences in transaction behaviour
across drivers when a query within a transaction fails. eg; under
PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest of
the transaction, whereas under MySQL and SQLite2 the transaction is allowed
to continue.
There's gotta be some way to wrap this behaviour cleanly so that
your application can expect the same behaviour regardless of the underlying
database layer... but this leads to another question :-)
Are all database drivers expected to supply one method to execute a
query? Eg; do "do", "execute", etc. all always funnel into one core method
that returns success, error, or exception (if RaiseError) is turned on? Or
if I wanted to create this functionality and expect it to work under
multiple database drivers, should I override multiple methods?
Thanks,
Tyler
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Tim Bunce
> I'll guess that what you're really after is to be able to call begin_work
> again whilst an earlier begin_work is in effect and have the DBI keep a
> counter of how deeply nested the begin_work calls are. Then commit would
> decrement the counter and only commit at the outer most level.
>
> If you really want that then it's straightforward to implement via a
> subclass.
This has been done. I'm only using it in two other packages so far,
but both those and it's own unit tests seem to work well.
http://search.cpan.org/~CRAKRJACK/DBIx-Transaction-0.001/
I've got some ideas for enhancements too, but those are a bit more
vauge. One of them is that there's differences in transaction behaviour
across drivers when a query within a transaction fails. eg; under
PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest of
the transaction, whereas under MySQL and SQLite2 the transaction is allowed
to continue.
There's gotta be some way to wrap this behaviour cleanly so that
your application can expect the same behaviour regardless of the underlying
database layer... but this leads to another question :-)
Are all database drivers expected to supply one method to execute a
query? Eg; do "do", "execute", etc. all always funnel into one core method
that returns success, error, or exception (if RaiseError) is turned on? Or
if I wanted to create this functionality and expect it to work under
multiple database drivers, should I override multiple methods?
Thanks,
Tyler
On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote:
> Tim Bunce
> > I'll guess that what you're really after is to be able to call begin_work
> > again whilst an earlier begin_work is in effect and have the DBI keep a
> > counter of how deeply nested the begin_work calls are. Then commit would
> > decrement the counter and only commit at the outer most level.
> >
> > If you really want that then it's straightforward to implement via a
> > subclass.
>
> This has been done. I'm only using it in two other packages so far,
> but both those and it's own unit tests seem to work well.
>
> http://search.cpan.org/~CRAKRJACK/DBIx-Transaction-0.001/
>
> I've got some ideas for enhancements too, but those are a bit more
> vauge. One of them is that there's differences in transaction behaviour
> across drivers when a query within a transaction fails. eg; under
> PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest of
> the transaction, whereas under MySQL and SQLite2 the transaction is allowed
> to continue.
PostgreSQL is non-standard (and inconvenient) in this respect.
> There's gotta be some way to wrap this behaviour cleanly so that
> your application can expect the same behaviour regardless of the underlying
> database layer...
There isn't, as far as I know, except to accept the 'lowest common
denominator'. In this case that means forcing a rollback if any
statement fails.
> but this leads to another question :-)
>
> Are all database drivers expected to supply one method to execute a
> query? Eg; do "do", "execute", etc. all always funnel into one core method
> that returns success, error, or exception (if RaiseError) is turned on? Or
> if I wanted to create this functionality and expect it to work under
> multiple database drivers, should I override multiple methods?
execute() is sufficient if the driver doesn't also supply it's own do()
because DBI's default do() calls execute(). But some drivers do supply
their own do() method (for good reasons).
Tim.
On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote:
> Tim Bunce
> > I'll guess that what you're really after is to be able to call begin_work
> > again whilst an earlier begin_work is in effect and have the DBI keep a
> > counter of how deeply nested the begin_work calls are. Then commit would
> > decrement the counter and only commit at the outer most level.
> >
> > If you really want that then it's straightforward to implement via a
> > subclass.
>
> This has been done. I'm only using it in two other packages so far,
> but both those and it's own unit tests seem to work well.
>
> http://search.cpan.org/~CRAKRJACK/DBIx-Transaction-0.001/
>
> I've got some ideas for enhancements too, but those are a bit more
> vauge. One of them is that there's differences in transaction behaviour
> across drivers when a query within a transaction fails. eg; under
> PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest of
> the transaction, whereas under MySQL and SQLite2 the transaction is allowed
> to continue.
PostgreSQL is non-standard (and inconvenient) in this respect.
> There's gotta be some way to wrap this behaviour cleanly so that
> your application can expect the same behaviour regardless of the underlying
> database layer...
There isn't, as far as I know, except to accept the 'lowest common
denominator'. In this case that means forcing a rollback if any
statement fails.
> but this leads to another question :-)
>
> Are all database drivers expected to supply one method to execute a
> query? Eg; do "do", "execute", etc. all always funnel into one core method
> that returns success, error, or exception (if RaiseError) is turned on? Or
> if I wanted to create this functionality and expect it to work under
> multiple database drivers, should I override multiple methods?
execute() is sufficient if the driver doesn't also supply it's own do()
because DBI's default do() calls execute(). But some drivers do supply
their own do() method (for good reasons).
Tim.
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Tim Bunce
> PostgreSQL is non-standard (and inconvenient) in this respect.
I chatted with Mischa (my work's resident DB guru) about this, and
according to him, the error behaviour when you attempt to SELECT from a
table that does not exist is "undetermined" in the SQL standard, so it
really is the individual DBMS' choice. I think that's actually worse; all of
these DBMs are behaving completely differently but still "correctly" on such
a basic SQL operation due to a lack of standard!
> There isn't, as far as I know, except to accept the 'lowest common
> denominator'. In this case that means forcing a rollback if any
> statement fails.
> execute() is sufficient if the driver doesn't also supply it's own do()
> because DBI's default do() calls execute(). But some drivers do supply
> their own do() method (for good reasons).
Fair enough. So what I've done, is modified DBIx::Transaction to
mark a transaction error if any query in the transaction returns false;
--snip--
sub execute {
my $self = shift;
my $rv = eval { DBI::st::execute($self, @_); };
if($@) {
$self->{Database}->inc_transaction_error;
die "$@\n";
}
if(!$rv) {
$self->{Database}->inc_transaction_error;
}
return $rv;
}
--snip--
(and similar logic for db::do()).
The package I'm working on that uses DBIx::Transaction now also
checks for the existance of a table before attempting to manipulate it.
I was considering using the "table_info" method for this, but
there's a problem there; I don't know how to ask DBI what database/catalog
name I am currently working in, and "undef" is documented as returning
tables in *every* database, not just the current one.
So what I've done instead is defaulted to this query to check for a
table:
SELECT 1 FROM information_schema.tables WHERE table_name = ?
... then for MySQL,
SHOW TABLES LIKE ?
... and SQLite2,
SELECT 1 FROM sqlite_master WHERE type ='table' AND name = ?
The default query does work for postgres, and I'm told I can expect
it to work with M$SQL, Sybase, and Oracle as well. Can you think of any DBI
drivers I should expect it to *not* work with?
DB::Introspector claims to help you do this, so I might just use it.
Although it does a lot more stuff that I don't need, and I only see MySQL,
Oracle, and Postgres subclasses for it. Is there a better module or method
out there?
- Tyler
Tyler MacDonald wrote:
> Tim Bunce
>>PostgreSQL is non-standard (and inconvenient) in this respect.
>
> I chatted with Mischa (my work's resident DB guru) about this, and
> according to him, the error behaviour when you attempt to SELECT from a
> table that does not exist is "undetermined" in the SQL standard, so it
> really is the individual DBMS' choice. I think that's actually worse; all of
> these DBMs are behaving completely differently but still "correctly" on such
> a basic SQL operation due to a lack of standard!
Hope you don't mind my chirping up ...
Specifying behaviour down to the detail level of executing app errors
would be enough to keep a bunch of DBMS vendors from EVER adopting
a standard. It's not like Perl. It's way more like COBOL. Hard enough to agree
on what to do when the app follows the rules.
The ODBC solution was to allow any DB to express its abilities in crazy detail,
through SQLGetInfo. What kinds of joins it supported, what kinds of transaction,
and then let the app do or not do what it had to ... Does DBI have something
like that?
>>There isn't, as far as I know, except to accept the 'lowest common
>>denominator'. In this case that means forcing a rollback if any
>>statement fails.
How about having DBI implement a consistent nested-transaction interface that
compliant DBMS's can support?
--
Some people think the glass is half-full.
Some people think the glass is half-empty.
Engineers think, "This glass is twice as big as it needs to be."
On Tue, Nov 29, 2005 at 04:05:26PM -0800, Tyler MacDonald wrote:
> Tim Bunce
> > PostgreSQL is non-standard (and inconvenient) in this respect.
>
> I chatted with Mischa (my work's resident DB guru) about this, and
> according to him, the error behaviour when you attempt to SELECT from a
> table that does not exist is "undetermined" in the SQL standard, so it
> really is the individual DBMS' choice. I think that's actually worse; all of
> these DBMs are behaving completely differently but still "correctly" on such
> a basic SQL operation due to a lack of standard!
I was thinking more generally: the failure of a statement within a
transaction (such as an insert getting a duplicate key error) usually
rolls-back just that statement and does not abort the whole transaction.
If that's not true for PostgreSQL then that's certainly inconvenient.
> > There isn't, as far as I know, except to accept the 'lowest common
> > denominator'. In this case that means forcing a rollback if any
> > statement fails.
>
> > execute() is sufficient if the driver doesn't also supply it's own do()
> > because DBI's default do() calls execute(). But some drivers do supply
> > their own do() method (for good reasons).
>
> Fair enough. So what I've done, is modified DBIx::Transaction to
> mark a transaction error if any query in the transaction returns false;
>
> --snip--
> sub execute {
> my $self = shift;
> my $rv = eval { DBI::st::execute($self, @_); };
I'd probably say:
my $rv = eval { $self->SUPER::execute(@_) };
> if($@) {
> $self->{Database}->inc_transaction_error;
> die "$@\n";
> }
> if(!$rv) {
> $self->{Database}->inc_transaction_error;
> }
> return $rv;
> }
> --snip--
>
> (and similar logic for db::do()).
>
> The package I'm working on that uses DBIx::Transaction now also
> checks for the existance of a table before attempting to manipulate it.
See note above. I think that's just one example of a more general issue.
> I was considering using the "table_info" method for this, but
> there's a problem there; I don't know how to ask DBI what database/catalog
> name I am currently working in, and "undef" is documented as returning
> tables in *every* database, not just the current one.
This is something I've been meaning to address for a while. I was
thinking of something like:
$schema_name = $dbh->current_schema
> So what I've done instead is defaulted to this query to check for a
> table:
>
> SELECT 1 FROM information_schema.tables WHERE table_name = ?
>
> ... then for MySQL,
>
> SHOW TABLES LIKE ?
>
> ... and SQLite2,
>
> SELECT 1 FROM sqlite_master WHERE type ='table' AND name = ?
>
> The default query does work for postgres, and I'm told I can expect
> it to work with M$SQL, Sybase, and Oracle as well. Can you think of any DBI
> drivers I should expect it to *not* work with?
Very few databases support information_schema.
The generic portable fallback is "select 1 from $table where 1=0" - if that
statement can be executed without error then the table exists.
> DB::Introspector claims to help you do this, so I might just use it.
> Although it does a lot more stuff that I don't need, and I only see MySQL,
> Oracle, and Postgres subclasses for it. Is there a better module or method
> out there?
I don't know off-hand.
Why not help save the world and help me add current_schema() to the DBI
and send implementations to the authors of drivers you're using?
Tim.
Tim Bunce wrote:
>
[...]
>
> Why not help save the world and help me add current_schema() to the DBI
> and send implementations to the authors of drivers you're using?
I'd like to remark that SQL/CLI has a more general function
GetSessionInfo( ConnectionHandle, InfoType, ... )
------------------------------- ------------ ----- -------------------------------
USER and CURRENT_USER CHARACTER(L) 47 CURRENT USER
CURRENT_DEFAULT_TRANSFORM_GROUP CHARACTER(L) 20004 CURRENT DEFAULT TRANSFORM GROUP
CURRENT_PATH CHARACTER(L) 20005 CURRENT PATH
CURRENT_ROLE CHARACTER(L) 20006 CURRENT ROLE
SESSION_USER CHARACTER(L) 20007 SESSION USER
SYSTEM_USER CHARACTER(L) 20008 SYSTEM USER
(similar to GetInfo()). Translated to DBI , this would be
$value = $dbh->get_session_info( $info_type );
Steffen
On Wed, Nov 30, 2005 at 04:25:41PM +0100, Steffen Goeldner wrote:
> Tim Bunce wrote:
>
> >
> [...]
> >
> > Why not help save the world and help me add current_schema() to the DBI
> > and send implementations to the authors of drivers you're using?
>
> I'd like to remark that SQL/CLI has a more general function
>
> GetSessionInfo( ConnectionHandle, InfoType, ... )
>
>
> ------------------------------- ------------ ----- -------------------------------
> USER and CURRENT_USER CHARACTER(L) 47 CURRENT USER
> CURRENT_DEFAULT_TRANSFORM_GROUP CHARACTER(L) 20004 CURRENT DEFAULT TRANSFORM GROUP
> CURRENT_PATH CHARACTER(L) 20005 CURRENT PATH
> CURRENT_ROLE CHARACTER(L) 20006 CURRENT ROLE
> SESSION_USER CHARACTER(L) 20007 SESSION USER
> SYSTEM_USER CHARACTER(L) 20008 SYSTEM USER
>
> (similar to GetInfo()). Translated to DBI , this would be
>
> $value = $dbh->get_session_info( $info_type );
Great. It should be straightforward implement by copying how get_info() works.
Any volunteers?
Tim.
Tim Bunce
> > sub execute {
> > my $self = shift;
> > my $rv = eval { DBI::st::execute($self, @_); };
>
> I'd probably say:
>
> my $rv = eval { $self->SUPER::execute(@_) };
Yeah, maybe... I adopted that idiom because this doesn't work:
--snip--
sub close_transaction {
my $self = shift;
my $method = shift;
my $code = $self->SUPER::can($method);
$self->{private_DBIx_Transaction_Level} = 0;
$self->clear_transaction_error;
$self->transaction_trace($method);
my $rv = $code->($self, @_);
return $rv;
}
--snip--
My commit() method would end up calling
$self->close_transaction('commit'), with would then call my commit() method
again, instead of DBI::db's... so I changed it to read
my $code = DBI::db->can($method);
And everything's happy again.
> Very few databases support information_schema.
*grumble*
> The generic portable fallback is "select 1 from $table where 1=0" - if that
> statement can be executed without error then the table exists.
... but only if you're not already in the middle of a transaction,
which means setting up state counters and having to refresh them constantly
if this is the sort of table that can come and go as it pleases... I suppose
I could do that in this particular application since it's *very* unlikely
that multiple people/apps will be engaging in schema management on the same
database in paralell, but:
> This is something I've been meaning to address for a while. I was
> thinking of something like:
>
> $schema_name = $dbh->current_schema
I really like this. I read your exchange with Steffen Goeldner and
that prompted me to take a look at the get_info method. I expected either
SQL_DATABASE_NAME (16) or SQL_CATALOG_NAME (10003) to tell me the current
database name, but I guess I was wrong; on both MySQL and Pg,
SQL_DATABASE_NAME returns nothing; under MySQL, SQL_CATALOG_NAME returns
'Y', under Pg, 'N'.
> Why not help save the world and help me add current_schema() to the DBI
> and send implementations to the authors of drivers you're using?
SQL_DATABASE_NAME is such a self-explanitory label that I have to
believe this is the right place to put the information we're after, but I
could be way off-base. I'd be willing to task myself with producing patches
for the Pg, MySQL, and SQLite2 drivers to either make use of that attribute,
or provide a current_schema (or maybe current_database? under Pg at least,
schemas and db's are different) method. Which way is the right way to go
here?
I also noticed that the SQLite2 DBD driver doesn't even return
SQL_DBMS_VERSION... hmmm, might have to add that too...
- Tyler
On Tue, Nov 29, 2005 at 07:44:05PM +0000, Tim Bunce wrote:
> On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote:
> > PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest of
> > the transaction, whereas under MySQL and SQLite2 the transaction is allowed
> > to continue.
>
> PostgreSQL is non-standard (and inconvenient) in this respect.
The inconvenience I'll grant, but the non-standard claim I think
needs some justification. When the database encounters an error in a
transaction, it is supposed to report an error. An error in a
transaction causes the whole transaction to fail: that's what the
atomicity rule of ACID means, I think. I actually am sort of
unconvinced that SQLite's transactions are real ones -- I just did
some playing around with it, and it seems that any error allows you
to commit anyway. Certainly, MySQL's support of transactions is
occasionally pretty dodgy, unless you use the strict mode.
But it's worth knowing that in Pg 8.1 and later, you can wrap such
things in a subtransaction and get out of it that way.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Andrew Sullivan
> The inconvenience I'll grant, but the non-standard claim I think
> needs some justification. When the database encounters an error in a
> transaction, it is supposed to report an error. An error in a
> transaction causes the whole transaction to fail: that's what the
> atomicity rule of ACID means, I think. I actually am sort of
> unconvinced that SQLite's transactions are real ones -- I just did
> some playing around with it, and it seems that any error allows you
> to commit anyway. Certainly, MySQL's support of transactions is
> occasionally pretty dodgy, unless you use the strict mode.
Either way the end result is that some database drivers poison a
transaction if there's any error, others are selective about which errors
are fatal and which are not, and still others just don't care at all.
The end goal of DBIx::Transaction is to hide these differences from
the application so that transactions behave in a consistent way despite what
driver or driver options you're using, so on that note I've uploaded
DBIx-Transaction-0.002 to PAUSE, which will take the "lowest common
denominator", having any erronious query poison the entire transaction.
> But it's worth knowing that in Pg 8.1 and later, you can wrap such
> things in a subtransaction and get out of it that way.
Nifty! :)
Cheers,
Tyler
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
On Wed, Nov 30, 2005 at 04:19:18PM -0500, Andrew Sullivan wrote:
> But it's worth knowing that in Pg 8.1 and later, you can wrap such
> things in a subtransaction and get out of it that way.
Shouldn't that be 8.0 and later? That's when savepoints were
introduced. Or are you referring to something else?
--
Michael Fuhr
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
On Wed, Nov 30, 2005 at 04:19:18PM -0500, Andrew Sullivan wrote:
> On Tue, Nov 29, 2005 at 07:44:05PM +0000, Tim Bunce wrote:
> > On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote:
> > > PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest of
> > > the transaction, whereas under MySQL and SQLite2 the transaction is allowed
> > > to continue.
> >
> > PostgreSQL is non-standard (and inconvenient) in this respect.
>
> The inconvenience I'll grant, but the non-standard claim I think
> needs some justification. When the database encounters an error in a
> transaction, it is supposed to report an error. An error in a
> transaction causes the whole transaction to fail: that's what the
> atomicity rule of ACID means, I think.
The fact that an individual statement has failed and returned an error
(such as a duplicate key on insert) does not mean that the whole
transaction has failed (ie been implicitly rolled back).
The application may choose to explicitly rollback after it is informed
that a statement has failed, or it could try an alternative action.
I believe that's the case for Oracle, DB2, and Ingres (showing my age)
but I don't have standards docs to hand - nor the time to read them :)
No doubt someone will quote the relevant parts. (And no doubt the
relevant parts will say "it depends" :)
Tim.
> I actually am sort of
> unconvinced that SQLite's transactions are real ones -- I just did
> some playing around with it, and it seems that any error allows you
> to commit anyway. Certainly, MySQL's support of transactions is
> occasionally pretty dodgy, unless you use the strict mode.
>
> But it's worth knowing that in Pg 8.1 and later, you can wrap such
> things in a subtransaction and get out of it that way.
>
> A
>
> --
> Andrew Sullivan | ajs@crankycanuck.ca
> I remember when computers were frustrating because they *did* exactly what
> you told them to. That actually seems sort of quaint now.
> --J.D. Baldwin
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
On Wed, Nov 30, 2005 at 04:19:18PM -0500, Andrew Sullivan wrote:
> On Tue, Nov 29, 2005 at 07:44:05PM +0000, Tim Bunce wrote:
> > On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote:
> > > PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest of
> > > the transaction, whereas under MySQL and SQLite2 the transaction is allowed
> > > to continue.
> >
> > PostgreSQL is non-standard (and inconvenient) in this respect.
>
> The inconvenience I'll grant, but the non-standard claim I think
> needs some justification. When the database encounters an error in a
> transaction, it is supposed to report an error. An error in a
> transaction causes the whole transaction to fail: that's what the
> atomicity rule of ACID means, I think.
The fact that an individual statement has failed and returned an error
(such as a duplicate key on insert) does not mean that the whole
transaction has failed (ie been implicitly rolled back).
The application may choose to explicitly rollback after it is informed
that a statement has failed, or it could try an alternative action.
I believe that's the case for Oracle, DB2, and Ingres (showing my age)
but I don't have standards docs to hand - nor the time to read them :)
No doubt someone will quote the relevant parts. (And no doubt the
relevant parts will say "it depends" :)
Tim.
> I actually am sort of
> unconvinced that SQLite's transactions are real ones -- I just did
> some playing around with it, and it seems that any error allows you
> to commit anyway. Certainly, MySQL's support of transactions is
> occasionally pretty dodgy, unless you use the strict mode.
>
> But it's worth knowing that in Pg 8.1 and later, you can wrap such
> things in a subtransaction and get out of it that way.
>
> A
>
> --
> Andrew Sullivan | ajs@crankycanuck.ca
> I remember when computers were frustrating because they *did* exactly what
> you told them to. That actually seems sort of quaint now.
> --J.D. Baldwin
On Wed, Nov 30, 2005 at 10:35:17AM -0800, Tyler MacDonald wrote:
>
> > This is something I've been meaning to address for a while. I was
> > thinking of something like:
> >
> > $schema_name = $dbh->current_schema
>
> I really like this. I read your exchange with Steffen Goeldner and
> that prompted me to take a look at the get_info method. I expected either
> SQL_DATABASE_NAME (16) or SQL_CATALOG_NAME (10003) to tell me the current
> database name, but I guess I was wrong; on both MySQL and Pg,
> SQL_DATABASE_NAME returns nothing; under MySQL, SQL_CATALOG_NAME returns
> 'Y', under Pg, 'N'.
See http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/odbc/htm/odbcsqlgetinfo.asp
> > Why not help save the world and help me add current_schema() to the DBI
> > and send implementations to the authors of drivers you're using?
>
> SQL_DATABASE_NAME is such a self-explanitory label that I have to
> believe this is the right place to put the information we're after, but I
> could be way off-base.
See above url. The wording isn't very clear:
: A character string with the name of the current database in use, if the
: data source defines a named object called "database". Note In ODBC
: 3.x, the value returned for this InfoType can also be returned by
: calling SQLGetConnectAttr with an Attribute argument of SQL_ATTR_CURRENT_CATALOG.
but since it's saying that SQL_DATABASE_NAME == SQL_ATTR_CURRENT_CATALOG
and a 'catalog' is usually not the same thing as a 'schema' (catalogs contain
schemas) it's clear that SQL_DATABASE_NAME isn't what you're after.
It may work for some databases and drivers (since the whole catalog vs
schema area is a bit of a mess) but it wouldn't be portable.
> I'd be willing to task myself with producing patches
> for the Pg, MySQL, and SQLite2 drivers to either make use of that attribute,
> or provide a current_schema (or maybe current_database? under Pg at least,
> schemas and db's are different) method. Which way is the right way to go here?
Generally DBI drivers should "do what the database vendors own ODBC
driver does" (or what the SQL 2003 standard mandates).
Though it seems GetSessionInfo isn't part of ODBC, it is in the SQL1999
and later standards.
I think the "right way to go here" is to add $dbh->get_session_info($type)
in a very similar way to how get_info() is handled. Patches welcome.
Tim.
Tim Bunce wrote:
> No doubt someone will quote the relevant parts. (And no doubt the
> relevant parts will say "it depends" :)
I believe, the "no doubt" part is showing your age, aka experience. :-)
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
Tim Bunce wrote:
> No doubt someone will quote the relevant parts. (And no doubt the
> relevant parts will say "it depends" :)
I believe, the "no doubt" part is showing your age, aka experience. :-)
Steffen Goeldner wrote:
> I'd like to remark that SQL/CLI has a more general function
>
> GetSessionInfo( ConnectionHandle, InfoType, ... )
>
>
> ------------------------------- ------------ ----- -------------------------------
> USER and CURRENT_USER CHARACTER(L) 47 CURRENT USER
> CURRENT_DEFAULT_TRANSFORM_GROUP CHARACTER(L) 20004 CURRENT DEFAULT TRANSFORM GROUP
> CURRENT_PATH CHARACTER(L) 20005 CURRENT PATH
> CURRENT_ROLE CHARACTER(L) 20006 CURRENT ROLE
> SESSION_USER CHARACTER(L) 20007 SESSION USER
> SYSTEM_USER CHARACTER(L) 20008 SYSTEM USER
CURRENT_CATALOG CHARACTER(L) 20009 CURRENT CATALOG
CURRENT_SCHEMA CHARACTER(L) 20010 CURRENT SCHEMA
(The last 2 InfoType were missing in the previous post, sorry!)
Steffen
On Wed, Nov 30, 2005 at 02:58:15PM -0700, Michael Fuhr wrote:
>
> Shouldn't that be 8.0 and later? That's when savepoints were
> introduced. Or are you referring to something else?
Doh. Indeed. I was _thinking_ os something else, but not referring
to something else.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org