pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBit by "commands ignored until end of transaction block" again
19+ messages / 10 participants
[nested] [flat]
* Bit by "commands ignored until end of transaction block" again
@ 2009-07-23 04:58 Glenn Maynard <glenn@zewt.org>
0 siblings, 1 reply; 19+ messages in thread
From: Glenn Maynard @ 2009-07-23 04:58 UTC (permalink / raw)
To: pgsql-sql
Postgres kills a transaction when an error happens. This is a pain;
it assumes that all SQL errors are unexpected and fatal to the
transaction.
There's a very simple case where it's not: UNIQUE checks. I'm
generating a cache, with a simple flow:
- Search for the cache key; if it exists, return its value.
- If it didn't exist, create the data based on the key, insert it
into the table, and return it.
This has an obvious race: another thread looks up the same key and
creates it between the search and the insert. Both threads will
create the cached data, thread A will insert it into the table, and
thread B will get an integrity error when it tries to insert it, since
it duplicates the unique key.
Here, by far the simplest fix is simply to ignore the integrity error.
Both threads generated the same data; the failed insert is expected
and harmless. Postgres is turning this into a fatal error.
There's so much that could make this trivially easy:
- SQLite has the handy ON CONFLICT IGNORE, but Postgres has nothing
like that. (ON CONFLICT REPLACE is great, too.)
- Let me use SAVEPOINT outside of a transaction, with the effect of
starting a transaction with the savepoint and ending it when it's
committed. Then, I could use savepoints without needing to know
whether I'm already in a transaction or not; one would simply be
started and committed for me if necessary. (That's by far my biggest
issue with savepoints: they force me to either specify "a transaction
must be open when this function is called", or need to be able to
query whether one is running to decide whether to start a transaction
or a savepoint. My function's use of transactions should be invisible
to the caller.)
- Let me disable this error. I don't want it. (We're grownups; we
can decide for ourselves which errors are fatal.)
The first two are cleaner, since ignoring the error means I might
ignore some other integrity error from the same statement, but I can
live with that.
Lacking anything better, I'll probably end up dropping out of the ORM
and using some uglier SQL to work around this, but this is so trivial
that it's silly to have to do that. I can't do it within the ORM; it
doesn't have the vocabulary.
Any tricks I'm missing? It feels like Postgres is fighting me at
every turn with this one, and this isn't the first time I've had this
problem.
--
Glenn Maynard
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-23 05:31 Richard Huxton <dev@archonet.com>
parent: Glenn Maynard <glenn@zewt.org>
0 siblings, 1 reply; 19+ messages in thread
From: Richard Huxton @ 2009-07-23 05:31 UTC (permalink / raw)
To: Glenn Maynard <glenn@zewt.org>; +Cc: pgsql-sql
Glenn Maynard wrote:
> Postgres kills a transaction when an error happens. This is a pain;
> it assumes that all SQL errors are unexpected and fatal to the
> transaction.
>
> There's a very simple case where it's not: UNIQUE checks.
Ah, it's usually "syntax errors" wrt interactive sessions.
> I'm
> generating a cache, with a simple flow:
> - Search for the cache key; if it exists, return its value.
> - If it didn't exist, create the data based on the key, insert it
> into the table, and return it.
>
> This has an obvious race: another thread looks up the same key and
> creates it between the search and the insert. Both threads will
> create the cached data, thread A will insert it into the table, and
> thread B will get an integrity error when it tries to insert it, since
> it duplicates the unique key.
Yep.
> Here, by far the simplest fix is simply to ignore the integrity error.
> Both threads generated the same data; the failed insert is expected
> and harmless. Postgres is turning this into a fatal error.
Well, all errors are considered fatal. But same difference.
> There's so much that could make this trivially easy:
>
> - SQLite has the handy ON CONFLICT IGNORE, but Postgres has nothing
> like that. (ON CONFLICT REPLACE is great, too.)
True. Been proposed. Fiddly to implement for all use-cases if I remember
correctly.
> - Let me use SAVEPOINT outside of a transaction,
You are never outside a transaction. All queries are executed within a
transaction.
> with the effect of
> starting a transaction with the savepoint and ending it when it's
> committed. Then, I could use savepoints without needing to know
> whether I'm already in a transaction or not; one would simply be
> started and committed for me if necessary. (That's by far my biggest
> issue with savepoints: they force me to either specify "a transaction
> must be open when this function is called", or need to be able to
> query whether one is running to decide whether to start a transaction
> or a savepoint. My function's use of transactions should be invisible
> to the caller.)
I think this is the root of your problem - all queries are within a
transaction so either:
1. You have a transaction that wraps a single statement. If you get an
error then only that statement was affected.
2. You have an explicit BEGIN...COMMIT transaction which could use a
savepoint.
Nothing to stop you setting savepoints in #1 (although they're not much
use).
Typically, if you're in a plpgsql function you would just catch "unique"
exception codes from your insert. Or, update, see if any rows were
affected, if not try an insert and if that gives a duplicate go back and
try the update. You might want the second approach if 99% of the time
the cache is already populated.
> - Let me disable this error. I don't want it. (We're grownups; we
> can decide for ourselves which errors are fatal.)
You could always try submitting a patch. However, that's really what
savepoints do - let you decide whether an error can be worked around.
> The first two are cleaner, since ignoring the error means I might
> ignore some other integrity error from the same statement, but I can
> live with that.
>
> Lacking anything better, I'll probably end up dropping out of the ORM
> and using some uglier SQL to work around this, but this is so trivial
> that it's silly to have to do that. I can't do it within the ORM; it
> doesn't have the vocabulary.
The ORM can't control transactions, can't call functions or can't set
savepoints?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-23 06:04 Glenn Maynard <glenn@zewt.org>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 19+ messages in thread
From: Glenn Maynard @ 2009-07-23 06:04 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton<dev@archonet.com> wrote:
>> - Let me use SAVEPOINT outside of a transaction,
>
> You are never outside a transaction. All queries are executed within a
> transaction.
"Transaction block", then, if you insist.
> I think this is the root of your problem - all queries are within a
> transaction so either:
> 1. You have a transaction that wraps a single statement. If you get an error
> then only that statement was affected.
> 2. You have an explicit BEGIN...COMMIT transaction which could use a
> savepoint.
Savepoints can only be used inside transaction blocks. My function
has no idea whether it's being called inside a transaction block.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-23 06:41 Richard Huxton <dev@archonet.com>
parent: Glenn Maynard <glenn@zewt.org>
2 siblings, 1 reply; 19+ messages in thread
From: Richard Huxton @ 2009-07-23 06:41 UTC (permalink / raw)
To: Glenn Maynard <glenn@zewt.org>; +Cc: pgsql-sql
Glenn Maynard wrote:
> On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton<dev@archonet.com> wrote:
>>> - Let me use SAVEPOINT outside of a transaction,
>> You are never outside a transaction. All queries are executed within a
>> transaction.
>
> "Transaction block", then, if you insist.
>
>> I think this is the root of your problem - all queries are within a
>> transaction so either:
>> 1. You have a transaction that wraps a single statement. If you get an error
>> then only that statement was affected.
>> 2. You have an explicit BEGIN...COMMIT transaction which could use a
>> savepoint.
>
> Savepoints can only be used inside transaction blocks.
Says who? Wouldn't work in plpgsql as exception handling if that was the
case.
> My function
> has no idea whether it's being called inside a transaction block.
>
> From inside a transaction block, my function would need to call
> SAVEPOINT/RELEASE SAVEPOINT.
>
> If it's not in a transaction block, it needs to call BEGIN/COMMIT
> instead. SAVEPOINT will fail with "SAVEPOINT can only be used in
> transaction blocks".
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?
> This would be very simple and clean if the SAVEPOINT command
> transparently issued BEGIN if executed outside of a transaction block,
> marking the savepoint so it knows that when the savepoint is released
> or rolled back, the associated transaction block needs to be committed
> or rolled back, too. At that point, you could stop using
> BEGIN/COMMIT/ROLLBACK entirely, and just let savepoints do it, if you
> wanted--with this, the transaction commands are essentially redundant.
>
> I can't count the number of times I've wished for this.
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.
>> Typically, if you're in a plpgsql function you would just catch "unique"
>> exception codes from your insert. Or, update, see if any rows were affected,
>> if not try an insert and if that gives a duplicate go back and try the
>> update. You might want the second approach if 99% of the time the cache is
>> already populated.
>
> It's just a simple INSERT, generated from a Model.objects.create() in Django.
OK, it sounds like create() isn't the method to call then, since you
don't want to generate a simple INSERT.
>>> Lacking anything better, I'll probably end up dropping out of the ORM
>>> and using some uglier SQL to work around this, but this is so trivial
>>> that it's silly to have to do that. I can't do it within the ORM; it
>>> doesn't have the vocabulary.
>> The ORM can't control transactions, can't call functions or can't set
>> savepoints?
>
> It can't write the necessary SQL to say "insert this unless it already
> exists", namely:
>
> INSERT INTO cache (key, data) (
> SELECT i.key, "data",
> FROM
> (VALUES ("key")) AS i(key)
> LEFT JOIN cache prior_entry ON (prior_entry.key = "key")
> WHERE
> prior_entry.key IS NULL
> )
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 know nothing of Django, but perhaps it's possible to subclass Model
and add an "ensure" method that will call your plpgsql function?
> It--Django--also doesn't have a mature transaction/savepoint system;
> in fact, its transaction handling is an absolute mess. I've written
> helpers for my main codebase that simply says "wrap this in a
> transaction block if one isn't already started, otherwise wrap it in a
> savepoint". I don't want to use that code here, because it's nitty
> code: it needs to poke at Django internals to figure out whether it's
> in a transaction block or not, and dealing with other API
> compatibility issues.
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.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-23 06:46 Joshua Tolley <eggyknap@gmail.com>
parent: Glenn Maynard <glenn@zewt.org>
2 siblings, 2 replies; 19+ messages in thread
From: Joshua Tolley @ 2009-07-23 06:46 UTC (permalink / raw)
To: Glenn Maynard <glenn@zewt.org>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
On Thu, Jul 23, 2009 at 02:04:53AM -0400, Glenn Maynard wrote:
> On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton<dev@archonet.com> wrote:
> >> - Let me use SAVEPOINT outside of a transaction,
> >
> > You are never outside a transaction. All queries are executed within a
> > transaction.
>
> "Transaction block", then, if you insist.
>
> > I think this is the root of your problem - all queries are within a
> > transaction so either:
> > 1. You have a transaction that wraps a single statement. If you get an error
> > then only that statement was affected.
> > 2. You have an explicit BEGIN...COMMIT transaction which could use a
> > savepoint.
>
> Savepoints can only be used inside transaction blocks. My function
> has no idea whether it's being called inside a transaction block.
>
> From inside a transaction block, my function would need to call
> SAVEPOINT/RELEASE SAVEPOINT.
>
> If it's not in a transaction block, it needs to call BEGIN/COMMIT
> instead. SAVEPOINT will fail with "SAVEPOINT can only be used in
> transaction blocks".
Have you tried this? I expect if you give it a shot, you'll find you don't
actually have this problem. Really, everything is always in a transaction. If
you haven't explicitly opened one, PostgreSQL opens one for you before each
statement, and issues a COMMIT afterwards (or a ROLLBACK, if your statement
ran into an error). Statements within functions are always executed within the
same transaction, so you can issue SAVEPOINT commands anywhere in PL/pgSQL
functions without problems, because you're always in a transaction.
--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-23 07:06 Chris <dmagick@gmail.com>
parent: Joshua Tolley <eggyknap@gmail.com>
1 sibling, 2 replies; 19+ messages in thread
From: Chris @ 2009-07-23 07:06 UTC (permalink / raw)
To: Joshua Tolley <eggyknap@gmail.com>; +Cc: Glenn Maynard <glenn@zewt.org>; Richard Huxton <dev@archonet.com>; pgsql-sql
Joshua Tolley wrote:
> On Thu, Jul 23, 2009 at 02:04:53AM -0400, Glenn Maynard wrote:
>> On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton<dev@archonet.com> wrote:
>>>> - Let me use SAVEPOINT outside of a transaction,
>>> You are never outside a transaction. All queries are executed within a
>>> transaction.
>> "Transaction block", then, if you insist.
>>
>>> I think this is the root of your problem - all queries are within a
>>> transaction so either:
>>> 1. You have a transaction that wraps a single statement. If you get an error
>>> then only that statement was affected.
>>> 2. You have an explicit BEGIN...COMMIT transaction which could use a
>>> savepoint.
>> Savepoints can only be used inside transaction blocks. My function
>> has no idea whether it's being called inside a transaction block.
>>
>> From inside a transaction block, my function would need to call
>> SAVEPOINT/RELEASE SAVEPOINT.
>>
>> If it's not in a transaction block, it needs to call BEGIN/COMMIT
>> instead. SAVEPOINT will fail with "SAVEPOINT can only be used in
>> transaction blocks".
>
> Have you tried this? I expect if you give it a shot, you'll find you don't
> actually have this problem. Really, everything is always in a transaction.
Each statement is in it's own transaction, but the problem (as I
understand it) is that you're in this sort of situation:
psql -d dbname
..
# select now();
now
-------------------------------
2009-07-23 17:04:21.406424+10
(1 row)
Time: 2.434 ms
(csmith@[local]:5432) 17:04:21 [test]
# savepoint xyz;
ERROR: SAVEPOINT can only be used in transaction blocks
(csmith@[local]:5432) 17:04:25 [test]
You haven't explicitly started a transaction, therefore savepoints won't
work.
Django (it seems) just issues queries with no knowledge of (and no way
to support) them.
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-23 07:32 Thomas Kellerer <spam_eater@gmx.net>
parent: Chris <dmagick@gmail.com>
1 sibling, 0 replies; 19+ messages in thread
From: Thomas Kellerer @ 2009-07-23 07:32 UTC (permalink / raw)
To: pgsql-sql
Chris, 23.07.2009 09:06:
> psql -d dbname
> ..
> # select now();
> now
> -------------------------------
> 2009-07-23 17:04:21.406424+10
> (1 row)
>
> Time: 2.434 ms
> (csmith@[local]:5432) 17:04:21 [test]
> # savepoint xyz;
> ERROR: SAVEPOINT can only be used in transaction blocks
> (csmith@[local]:5432) 17:04:25 [test]
>
> You haven't explicitly started a transaction, therefore savepoints won't
> work.
>
> Django (it seems) just issues queries with no knowledge of (and no way
> to support) them.
The above situation only arises if you run in autocommit mode which is the default for psql (which I have *never* understood).
If you do a "\set AUTOCOMMIT off", then you can set a savepoint without using BEGIN. I have this in my psqlrc.conf and your example looks like this on my computer:
c:\Temp>psql training thomas
psql (8.4.0)
Type "help" for help.
training=> select now();
now
----------------------------
2009-07-23 09:30:55.791+02
(1 row)
training=> savepoint abc;
SAVEPOINT
training=> release abc;
RELEASE
training=>
I don't believe any serious ORM would run in autocommit mode, so that shouldn't be a problem.
Thomas
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-23 07:39 Glenn Maynard <glenn@zewt.org>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 19+ messages in thread
From: Glenn Maynard @ 2009-07-23 07:39 UTC (permalink / raw)
To: pgsql-sql
On Thu, Jul 23, 2009 at 2:41 AM, Richard Huxton<dev@archonet.com> 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.)
> 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
BEGIN;
SAVEPOINT a;
RELEASE SAVEPOINT a; -- will not commit, because savepoint "a" didn't
start the transaction
Of course, there are other details--it probably shouldn't allow
ROLLBACK or COMMIT on an implicit transaction block, for example.
> 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.
> 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.
--
Glenn Maynard
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-23 08:06 Richard Huxton <dev@archonet.com>
parent: Glenn Maynard <glenn@zewt.org>
2 siblings, 1 reply; 19+ messages in thread
From: Richard Huxton @ 2009-07-23 08:06 UTC (permalink / raw)
To: Glenn Maynard <glenn@zewt.org>; +Cc: pgsql-sql
Glenn Maynard wrote:
> On Thu, Jul 23, 2009 at 2:41 AM, Richard Huxton<dev@archonet.com> 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.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-23 10:55 Stephen Frost <sfrost@snowman.net>
parent: Glenn Maynard <glenn@zewt.org>
2 siblings, 0 replies; 19+ messages in thread
From: Stephen Frost @ 2009-07-23 10:55 UTC (permalink / raw)
To: Glenn Maynard <glenn@zewt.org>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
* Glenn Maynard (glenn@zewt.org) wrote:
> > The ORM can't control transactions, can't call functions or can't set
> > savepoints?
>
> It can't write the necessary SQL to say "insert this unless it already
> exists", namely:
If it can't cleanly handle failure cases like this one, then I think
your issue is with your ORM and not with PG. An INSERT failing on a
uniqueness violation is actually a rather big deal in a relational
database and not erroring on it goes quite against data integrity
considerations.
If your ORM could call a function instead, you could handle the insert
and error-check in the function, to make up for the lack of intelligence
in the ORM. Another option would be to have a 'fake' table, which has
no rows in it and just has an 'ON INSERT' trigger that calls a function
to handle this. That could also be a view with a do-instead rule, if
the ORM has to query the same table.
I would think the time would better be spent fixing the ORM though.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-23 14:20 Adrian Klaver <aklaver@comcast.net>
parent: Glenn Maynard <glenn@zewt.org>
2 siblings, 0 replies; 19+ messages in thread
From: Adrian Klaver @ 2009-07-23 14:20 UTC (permalink / raw)
To: pgsql-sql; +Cc: Glenn Maynard <glenn@zewt.org>
On Thursday 23 July 2009 12:39:23 am Glenn Maynard wrote:
>
> 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.
>
> --
> Glenn Maynard
None of the options listed in the URL below work?:
http://docs.djangoproject.com/en/dev/topics/db/transactions/#topics-db-transactions
This is the development version of the docs so may contain some new options. In
particular look at Savepoint rollback and Database-level autocommit.
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-24 09:43 Craig Ringer <craig@postnewspapers.com.au>
parent: Chris <dmagick@gmail.com>
1 sibling, 0 replies; 19+ messages in thread
From: Craig Ringer @ 2009-07-24 09:43 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: Joshua Tolley <eggyknap@gmail.com>; Glenn Maynard <glenn@zewt.org>; Richard Huxton <dev@archonet.com>; pgsql-sql
On Thu, 2009-07-23 at 17:06 +1000, Chris wrote:
> Joshua Tolley wrote:
> > On Thu, Jul 23, 2009 at 02:04:53AM -0400, Glenn Maynard wrote:
> >> On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton<dev@archonet.com> wrote:
> >>>> - Let me use SAVEPOINT outside of a transaction,
> >>> You are never outside a transaction. All queries are executed within a
> >>> transaction.
> >> "Transaction block", then, if you insist.
> >>
> >>> I think this is the root of your problem - all queries are within a
> >>> transaction so either:
> >>> 1. You have a transaction that wraps a single statement. If you get an error
> >>> then only that statement was affected.
> >>> 2. You have an explicit BEGIN...COMMIT transaction which could use a
> >>> savepoint.
> >> Savepoints can only be used inside transaction blocks. My function
> >> has no idea whether it's being called inside a transaction block.
> >>
> >> From inside a transaction block, my function would need to call
> >> SAVEPOINT/RELEASE SAVEPOINT.
> >>
> >> If it's not in a transaction block, it needs to call BEGIN/COMMIT
> >> instead. SAVEPOINT will fail with "SAVEPOINT can only be used in
> >> transaction blocks".
> >
> > Have you tried this? I expect if you give it a shot, you'll find you don't
> > actually have this problem. Really, everything is always in a transaction.
[snip]
> You haven't explicitly started a transaction, therefore savepoints won't
> work.
True. However, he's talking about code within a PL/PgSQL function. To a
PL/PgSQL function there is NO difference between:
begin;
select my_function();
commit;
and a standalone:
select my_function();
in both cases the statement executes in a transaction, and in both cases
individual statements within the function are within the same
transaction. That's why any function can EXCEPTION blocks, etc, which
rely on savepoints.
--
Craig Ringer
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-24 09:52 Craig Ringer <craig@postnewspapers.com.au>
parent: Glenn Maynard <glenn@zewt.org>
2 siblings, 0 replies; 19+ messages in thread
From: Craig Ringer @ 2009-07-24 09:52 UTC (permalink / raw)
To: Glenn Maynard <glenn@zewt.org>; +Cc: pgsql-sql
On Thu, 2009-07-23 at 03:39 -0400, Glenn Maynard 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.)
Personally, I'd think about moving the function into the database, using
PL/PgSQL or even PL/PythonU if you have to.
Why should DB use be transparent when you're modifying the DB? In one
case you immediately make a change. In another case, you schedule a
change to be applied if/when the current transaction commits, so the
change may or may not occur at some point in the future. That is, IMO, a
big difference.
Most applications with this sort of thing will have app-level
transaction APIs that contain and manage the DB-level ones anyway.
> RELEASE SAVEPOINT would only COMMIT the transaction *if* the savepoint
> that it's releasing started it.
So, what you're really asking for boils down to nestable transactions?
--
Craig Ringer
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-24 16:54 Alvaro Herrera <alvherre@commandprompt.com>
parent: Joshua Tolley <eggyknap@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Alvaro Herrera @ 2009-07-24 16:54 UTC (permalink / raw)
To: Joshua Tolley <eggyknap@gmail.com>; +Cc: Glenn Maynard <glenn@zewt.org>; Richard Huxton <dev@archonet.com>; pgsql-sql
Joshua Tolley escribió:
> Have you tried this? I expect if you give it a shot, you'll find you don't
> actually have this problem. Really, everything is always in a transaction. If
> you haven't explicitly opened one, PostgreSQL opens one for you before each
> statement, and issues a COMMIT afterwards (or a ROLLBACK, if your statement
> ran into an error). Statements within functions are always executed within the
> same transaction, so you can issue SAVEPOINT commands anywhere in PL/pgSQL
> functions without problems, because you're always in a transaction.
No, actually you can't call SAVEPOINT inside a PL/pgSQL function (or any
SPI user for that matter -- have you tried savepoints in LOLCODE?)
Inside PL/pgSQL the only way to use savepoints is with EXCEPTION blocks.
You are correct that you're always in a transaction, but in this context
not all transactions are equal :-(
(The problem, as we found out, is that the function must always have
control at the same level of transaction nestedness in SPI; you can't
just let the user define and release savepoints arbitrarily.)
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-24 18:09 Joshua Tolley <eggyknap@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 19+ messages in thread
From: Joshua Tolley @ 2009-07-24 18:09 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Glenn Maynard <glenn@zewt.org>; Richard Huxton <dev@archonet.com>; pgsql-sql
On Fri, Jul 24, 2009 at 12:54:31PM -0400, Alvaro Herrera wrote:
> Joshua Tolley escribió:
>
> > Have you tried this? I expect if you give it a shot, you'll find you don't
> > actually have this problem. Really, everything is always in a transaction. If
> > you haven't explicitly opened one, PostgreSQL opens one for you before each
> > statement, and issues a COMMIT afterwards (or a ROLLBACK, if your statement
> > ran into an error). Statements within functions are always executed within the
> > same transaction, so you can issue SAVEPOINT commands anywhere in PL/pgSQL
> > functions without problems, because you're always in a transaction.
>
> No, actually you can't call SAVEPOINT inside a PL/pgSQL function (or any
> SPI user for that matter -- have you tried savepoints in LOLCODE?)
> Inside PL/pgSQL the only way to use savepoints is with EXCEPTION blocks.
>
> You are correct that you're always in a transaction, but in this context
> not all transactions are equal :-(
>
> (The problem, as we found out, is that the function must always have
> control at the same level of transaction nestedness in SPI; you can't
> just let the user define and release savepoints arbitrarily.)
That makes sense -- and although I did try this before sending the email,
apparently I didn't try it well enough. :)
- Josh / eggyknap
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-26 21:21 Glenn Maynard <glenn@zewt.org>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 19+ messages in thread
From: Glenn Maynard @ 2009-07-26 21:21 UTC (permalink / raw)
To: pgsql-sql
> 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<dev@archonet.com> 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-26 23:15 Science <science@misuse.org>
0 siblings, 1 reply; 19+ messages in thread
From: Science @ 2009-07-26 23:15 UTC (permalink / raw)
To: pgsql-sql
> Date: Thu, 23 Jul 2009 09:06:50 +0100
> From: Richard Huxton <dev@archonet.com>
> To: Glenn Maynard <glenn@zewt.org>
> Cc: pgsql-sql@postgresql.org
> Subject: Re: Bit by "commands ignored until end of transaction block"
> again
> Message-ID: <4A681A1A.1090902@archonet.com>
>
> Glenn Maynard wrote:
>> On Thu, Jul 23, 2009 at 2:41 AM, Richard Huxton<dev@archonet.com> 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-27 00:00 Craig Ringer <craig@postnewspapers.com.au>
parent: Science <science@misuse.org>
0 siblings, 1 reply; 19+ messages in thread
From: Craig Ringer @ 2009-07-27 00:00 UTC (permalink / raw)
To: Science <science@misuse.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bit by "commands ignored until end of transaction block" again
@ 2009-07-27 12:40 Science <science@misuse.org>
parent: Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 19+ messages in thread
From: Science @ 2009-07-27 12:40 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-sql
Craig Ringer wrote:
> 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.
>
For all I know that's how it works these days. I haven't looked at the
code underneath this in a couple of years. It should be trivial to
implement in the way you describe based on how the Ruby codebase is set
up -- hopefully all this will help OP with the Django/Python version of
the same problem.
Steve
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2009-07-27 12:40 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-07-23 04:58 Bit by "commands ignored until end of transaction block" again Glenn Maynard <glenn@zewt.org>
2009-07-23 05:31 ` Richard Huxton <dev@archonet.com>
2009-07-23 06:04 ` Glenn Maynard <glenn@zewt.org>
2009-07-23 06:41 ` Richard Huxton <dev@archonet.com>
2009-07-23 07:39 ` Glenn Maynard <glenn@zewt.org>
2009-07-23 08:06 ` Richard Huxton <dev@archonet.com>
2009-07-26 21:21 ` Glenn Maynard <glenn@zewt.org>
2009-07-23 14:20 ` Re: Bit by "commands ignored until end of transaction block" again Adrian Klaver <aklaver@comcast.net>
2009-07-24 09:52 ` Craig Ringer <craig@postnewspapers.com.au>
2009-07-23 06:46 ` Joshua Tolley <eggyknap@gmail.com>
2009-07-23 07:06 ` Chris <dmagick@gmail.com>
2009-07-23 07:32 ` Re: Bit by "commands ignored until end of transaction block" again Thomas Kellerer <spam_eater@gmx.net>
2009-07-24 09:43 ` Craig Ringer <craig@postnewspapers.com.au>
2009-07-24 16:54 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-07-24 18:09 ` Joshua Tolley <eggyknap@gmail.com>
2009-07-23 10:55 ` Stephen Frost <sfrost@snowman.net>
2009-07-26 23:15 Re: Bit by "commands ignored until end of transaction block" again Science <science@misuse.org>
2009-07-27 00:00 ` Craig Ringer <craig@postnewspapers.com.au>
2009-07-27 12:40 ` Science <science@misuse.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox