Re: [SQL] Bit by "commands ignored until end of transaction block" again
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 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 (csm...@[local]:5432) 17:04:21 [test] # savepoint xyz; ERROR: SAVEPOINT can only be used in transaction blocks (csm...@[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/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need magical advice for counting NOTHING
In response to A. Kretschmer : > test=*# select foo.user_name, foo.log_type, sum(case when log_type_fk is > not null then 1 else 0 end) from (select user_id, user_name, > log_type_id, log_type from users cross join log_type) foo full join log > on ((foo.user_id, foo.log_type_id)=(log.user_fk, log.log_type_fk)) group > by 1,2 order by 1,2; > user_name | log_type | sum > ---+--+- > user1 | type1| 1 > user1 | type2| 0 > user2 | type1| 0 > user2 | type2| 1 > (4 rows) Update: select foo.user_name, foo.log_type, count(log_type_fk) ... -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) 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
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 (csm...@[local]:5432) 17:04:21 [test] # savepoint xyz; ERROR: SAVEPOINT can only be used in transaction blocks (csm...@[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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) 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
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.) > 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Double aggregate problem
Hello, I have the following tables: CREATE TABLE tblvacature ( id serial PRIMARY KEY, account int NOT NULL REFERENCES tblaccount (id) ON DELETE CASCADE, title varchar(128), bedrijfsprofiel text, functieomschrijving text, functieeisen text, arbeidsvoorwaarden text overig text, sollicitatieinfo text, inserted timestamp DEFAULT now() ); CREATE TABLE tblvacaturesector ( vacature int NOT NULL REFERENCES tblvacature (id) ON DELETE CASCADE, sector int NOT NULL REFERENCES tblsector (id) ON DELETE CASCADE ); select * from tblvacaturesector where vacature = 11; vacature | sector --+ 11 | 5 11 | 2 CREATE TABLE tblvacatureprovincie ( vacature int NOT NULL REFERENCES tblvacature (id) ON DELETE CASCADE, provincie int NOT NULL REFERENCES tblprovincie (id) ON DELETE CASCADE ); select * from tblvacatureprovincie where vacature = 11; vacature | provincie --+--- 11 | 7 11 | 1 11 | 8 CREATE TABLE tblprovincie ( id serial PRIMARY KEY, land int NOT NULL REFERENCES tblland (id) ON DELETE RESTRICT, name varchar(128) ); select * from tblprovincie; id | land | name +--+- 1 |1 | Noord-Holland 2 |1 | Zuid-Holland 3 |1 | Groningen 4 |2 | Brabant 5 |1 | Utrecht 6 |2 | Antwerpen 7 |2 | Limburg 8 |2 | Oost-Vlaanderen CREATE TABLE tblsector ( id serial PRIMARY KEY, name varchar(128) ); select * from tblsector; id | name +- 4 | Aap 1 | Cool 5 | Eerder 2 | Gaafjes 6 | Later 3 | Netjes 11 | ICT I hope that's enough information? > -Oorspronkelijk bericht- > Van: Peter Eisentraut [mailto:pete...@gmx.net] > Verzonden: woensdag 22 juli 2009 20:05 > Aan: pgsql-sql@postgresql.org > CC: David Weilers > Onderwerp: Re: [SQL] Double aggregate problem > > On Wednesday 22 July 2009 19:16:21 David Weilers wrote: > > I have the following query: > > > > select v.id, array_to_string(array_accum(s.name),', ') as sector , > > array_to_string(array_accum(p.name),', ') as provincie from tblvacature > > v, tblaccount a , tblvacaturesector vs, tblsector s , > > tblvacatureprovincie vp, tblprovincie p where v.id = 11 and v.account = > > a.id and vs.vacature = v.id and s.id = vs.sector and vp.vacature = v.id > > and p.id = vp.provincie group by v.id, v.inserted order by v.inserted > > desc > > > > That currently produces the following output: > > No one is going to be able to reproduce that without the table definitions and > data. > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) 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
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. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need magical advice for counting NOTHING
On Thu, Jul 23, 2009 at 1:01 AM, Andreas wrote: > SELECT user_name, log_type_fk, COUNT(log_type_fk) > FROM log > JOIN users ON (user_id = user_fk) > WHERE (ts IS BETWEEN sometime AND another) > GROUP BY user_name, log_type_fk > ORDER BY user_name, log_type_fk create table users (user_id integer, user_name varchar); create table log_type (log_type_id integer, log_type integer); create table log (log_id integer, log_type_fk integer, user_fk integer); insert into log_type (log_type_id, log_type) values (1, 1); insert into log_type (log_type_id, log_type) values (2, 2); insert into users (user_id, user_name) values (1, 'a'); insert into users (user_id, user_name) values (2, 'b'); insert into log (log_id, log_type_fk, user_fk) values (1, 1, 1); insert into log (log_id, log_type_fk, user_fk) values (2, 2, 1); insert into log (log_id, log_type_fk, user_fk) values (3, 2, 1); insert into log (log_id, log_type_fk, user_fk) values (4, 1, 2); SELECT user_name, log_type.log_type, sum((log_type_fk IS NOT NULL)::integer) AS count FROM users JOIN log_type ON (true) LEFT JOIN log ON (user_id = user_fk AND log.log_type_fk = log_type.log_type) GROUP BY user_name, log_type.log_type ORDER BY user_name, log_type.log_type; user_name | log_type | count ---+--+--- a |1 | 1 a |2 | 2 a |3 | 0 b |1 | 1 b |2 | 0 b |3 | 0 -- Glenn Maynard -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) 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
* Glenn Maynard (gl...@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 signature.asc Description: Digital signature
Re: [SQL] Bit by "commands ignored until end of transaction block" again
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 akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] using count in other column
Hi,
I made up a query to make a count for each item for each month/year:
SELECT"Artnr_ID", to_char("Date_plan","") AS "Jaar",
to_char("Date_plan","MM") AS "Maand", Count("tblArtnrs"."Artikelnr") AS
"Monthly_count", "val1","val2","val3"
FROM (("tblAnalyses" INNER JOIN "tblStudies" ON "tblAnalyses"."Studie_ID" =
"tblStudies"."Studie_ID") INNER JOIN "tblFichenr" ON
"tblStudies"."ID_fichenr" = "tblFichenr"."ID") INNER JOIN "tblArtnrs" ON
"tblFichenr"."ID_Art_nrs" = "tblArtnrs"."Artnr_ID"
GROUP BY "tblArtnrs"."Artnr_ID", to_char("Date_plan","") ,
to_char("Date_plan","MM"), "val1","val2","val3";
Now I want to use the "Monthly_count" value for further calculations with
other columns.
I tried to use
"Monthly_count"+ "val1" + "Monthly_count" * "val2" + "Monthly_count" *"val3"
But "Monthly_count" is not recognised in my calculations.
How can this be solved?
Thanks
Bart
--
View this message in context:
http://www.nabble.com/using-count-in-other-column-tp24622738p24622738.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
