Re: [SQL] Bit by "commands ignored until end of transaction block" again

2009-07-26 Thread Glenn Maynard 
> The above situation only arises if you run in autocommit mode which is the 
> default for psql (which I have *never* understood).

This is the short answer, in practice--assume that either a
transaction is started or will be started by the SAVEPOINT command,
and that if a COMMIT is needed (as a result of the SAVEPOINT or which
was already needed), that the caller will do it.

(I hate non-autocommit.  It defies basic code design instincts, which
tell me that whoever starts a transaction should finish it.  I
shouldn't be issuing a non-autocommit SAVEPOINT/RELEASE, and then
assuming the caller will COMMIT the transaction that was started
automatically.  I'm stuck with it in Django.  Yuck, but oh well;
battling the framework's idioms isn't going to help anything.)

On Thu, Jul 23, 2009 at 4:06 AM, Richard Huxton wrote:
>> I'm writing a Python library call.  It has no idea whether the caller
>> happens to be inside a transaction already, and I don't want to
>> specify something like "always run this inside a transaction".
>> (Callers are equally likely to want to do either, and it's bad API to
>> force them to start a transaction--the fact that I'm using the
>> database at al should be transparent.)
>
> That last bit is never going to work. There always needs to be some basic
> level of understanding between systems and transactions really have to be
> part of that for talking to a RDBMS. There will have to be a piece of code
> responsible for managing transactions somewhere in the
> middleware/application layers.

It's never 100% transparent--the case of making calls during a
transaction and then rolling the whole thing back still needs to be
documented.  The point, though, is that this isn't a database-centric
operation, so it shouldn't have usage restrictions like "must always"
or "must never be inside a transaction".

> All you're doing here is moving the point of confusion around, surely? At
> some point you still need to know whether you can issue
> BEGIN/ROLLBACK/COMMIT etc.

Not at all--I don't need to use any of these commands.  I just do this:

SAVEPOINT s;
INSERT INTO table ...;
RELEASE SAVEPOINT s;

to guarantee that my code's effect on the database is atomic.

someone else wrote:
> So, what you're really asking for boils down to nestable transactions?

That's how I've thought of savepoints from day one.  When I use them
in Python code, I use a with_transaction wrapper, which transparently
uses a transaction or a savepoint.

-- 
Glenn Maynard

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Bit by "commands ignored until end of transaction block" again

2009-07-26 Thread Science 

Date: Thu, 23 Jul 2009 09:06:50 +0100
From: Richard Huxton 
To: Glenn Maynard 
Cc: [email protected]
Subject: Re: Bit by "commands ignored until end of transaction block"
again
Message-ID: <[email protected]>

Glenn Maynard wrote:

On Thu, Jul 23, 2009 at 2:41 AM, Richard Huxton wrote:

Ah [cue light-bulb effect], I think I understand. Your function isn't in the
database is it? Surely your application knows if it's issuing BEGIN..COMMIT?

I'm writing a Python library call.  It has no idea whether the caller
happens to be inside a transaction already, and I don't want to
specify something like "always run this inside a transaction".
(Callers are equally likely to want to do either, and it's bad API to
force them to start a transaction--the fact that I'm using the
database at al should be transparent.)


That last bit is never going to work. There always needs to be some 
basic level of understanding between systems and transactions really 
have to be part of that for talking to a RDBMS. There will have to be a 
piece of code responsible for managing transactions somewhere in the 
middleware/application layers.



You'll have people with torches and pitchforks after you if you change
RELEASE SAVEPOINT to mean COMMIT. I might even lend them my pitchfork.

RELEASE SAVEPOINT would only COMMIT the transaction *if* the savepoint
that it's releasing started it.  Every currently-valid case requires
that a transaction is already started, so no existing code would be
affected by this.

SAVEPOINT a; -- implicitly issues BEGIN because one wasn't started
RELEASE SAVEPOINT a; -- implicitly issues COMMIT because savepoint "a"
issued the BEGIN, not the user

[snip]

Of course, there are other details--it probably shouldn't allow
ROLLBACK or COMMIT on an implicit transaction block, for example.


All you're doing here is moving the point of confusion around, surely? 
At some point you still need to know whether you can issue 
BEGIN/ROLLBACK/COMMIT etc.



Could it generate: "SELECT ensure_cache_contains(key,data)"? Then ten lines
of plpgsql will neatly encapsulate the problem. That plpgsql can be
automatically generated easily enough too.

I don't think so, at least not without digging into internals.  Django
is built around knowing all data types, so it'd need to be givne types
explicitly--for example, to know whether a timestamp should be
formatted as a timestamp, date or time.  (I do have a couple other
columns here--timestamps for cache expiration, etc.)  I'll have to ask
Django-side if there's a public API to do this, but I don't think
there is.


Well, the types would be exactly the same as for your existing insert. 
All it's really doing is changing the template those values get 
substituted into. It presumably does mean patching the ORM (or 
subclassing from it anyway).



Ah, the joys of badly designed ORMs. The nice thing is that there seem to be
plenty of bad ones to choose from too. If your ORM doesn't handle
transactions well, the more you use it the more difficult your life will
become. I'd be tempted to tidy up your existing fixes and wrap Django's ORM
as cleanly as you can. That's assuming they're not interested in patches.

The ORM on a whole is decent, but there are isolated areas where it's
very braindamaged--this is one of them.  They have a stable-release
API-compatibility policy, which I think just gets them stuck with some
really bad decisions for a long time.


Presumably they targetted MySQL first, where there's a lot less use in 
multi-statement transactions with their different behaviour of their 
various storage-engines.




FWIW, the way the Rails ORM ActiveRecord (another fairly damaged ORM) 
handles this is by allowing you to open any number of transaction 
blocks, but only the outer transaction block commits (in Pg):


Property.transaction { # SQL => 'BEGIN'
  User.transaction {
Foo.transaction {
  Foo.connection.execute('--some sql code') # SQL => '--some sql code'
}
  }
} # SQL => 'COMMIT'

This is pretty kludgy but lets me start any arbitrary transaction 
without worrying if there's already another one running "on top" of me 
(which I gather is your worry in this case). Dunno if the approach could 
work using a class wrapper and the Django ORM, but I would have thought 
that the implementation shouldn't be too hard..


And apologies to the list for going a little off-track from Pg.

Steve

--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Bit by "commands ignored until end of transaction block" again

2009-07-26 Thread Craig Ringer 
On Sun, 2009-07-26 at 19:15 -0400, Science wrote:

> FWIW, the way the Rails ORM ActiveRecord (another fairly damaged ORM) 
> handles this is by allowing you to open any number of transaction 
> blocks, but only the outer transaction block commits (in Pg):
> 
> Property.transaction { # SQL => 'BEGIN'
>User.transaction {
>  Foo.transaction {
>Foo.connection.execute('--some sql code') # SQL => '--some sql code'
>  }
>}
> } # SQL => 'COMMIT'

What happens if, Foo.transaction does something that causes an error,
though, or issues a rollback? It's not creating savepoints, so if
Foo.transaction rolls back it throws out the work of User.transaction
and Property.transaction too.

Ugh.

That design would be quite good _IF_ it used savepoints:


Property.transaction { # SQL => 'BEGIN'
   User.transaction {  # SQL => SAVEPOINT User
 Foo.transaction { # SQL => SAVEPOINT Foo
   Foo.connection.execute('--some sql code') # SQL => '--some sql code'
 } # SQL => RELEASE SAVEPOINT Foo
   }   # SQL => RELEASE SAVEPOINT User
}  # SQL => 'COMMIT'

... so that inner transactions could ROLLBACK TO SAVEPOINT on error ,
and so that asking for a rollback would give you a ROLLBACK TO SAVEPOINT
if the transaction is a subtransaction.

-- 
Craig Ringer


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql