Re: [SQL] Bit by "commands ignored until end of transaction block" again
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 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 -- 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
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 -- 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
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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
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 signature.asc Description: Digital signature
Re: [SQL] using count in other column
Hello,
Le 23/07/09 11:59, bartjoosen a écrit :
> 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
>
The error message you meet is missing in your report although it could
surely help in accurate analysis. However I guess it is about using
alias (like "Monthly_count") for defining project columns (like the one
you tried and failed). Alias columns are used to rename displayed
columns; they cannot be used as terms of other projected columns but
they can be used within GROUP BY clauses. For example, the given GROUP
BY clause may be rewritten (simplier) as:
GROUP BY "Artnr_ID", "Artnr_ID", "Jaar", "Maand", "val1", "val2", "val3"
and even as:
GROUP BY "Artnr_ID", "Date_plan", "val1", "val2", "val3"
as "Jaar" and "Maand" refer to column "Date_plan" which is not passed to
aggregating functions but is effectively aggregated.
Hoping this is a track for you.
Regards.
--
nha / Lyon / France.
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using count in other column
Hello again,
Le 25/07/09 0:41, nha a écrit :
> Hello,
>
> Le 23/07/09 11:59, bartjoosen a écrit :
>> 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
>>
> The error message you meet is missing in your report although it could
> surely help in accurate analysis. However I guess it is about using
> alias (like "Monthly_count") for defining project columns (like the one
> you tried and failed). [...]
I forgot to mention a solution for using the value aliased by
"Monthly_count". An operational way is to reuse the whole aliased
expression, ie. count("tblArtnrs"."Artikelnr") here.
For example, the following query is wrong:
SELECT
"Artnr_ID",
to_char("Date_plan", '') AS "Jaar",
to_char("Date_plan", 'MM') AS "Maand",
count("tAr"."Artikelnr") AS "Monthly_count",
"val1", "val2", "val3",
"Monthly_count" + "val1" + "Monthly_count" * "val2" + "Monthly_count" *
"val3"
FROM (
(
"tblAnalyses" AS "tAn"
INNER JOIN "tblStudies" AS "tS" ON "tAn"."Studie_ID" = "tS"."Studie_ID"
)
INNER JOIN "tblFichenr" AS "tF" ON "tS"."ID_fichenr" = "tF"."ID"
)
INNER JOIN "tblArtnrs" AS "tAr" ON "tF"."ID_Art_nrs" = "tAr"."Artnr_ID"
GROUP BY "Artnr_ID", "Date_plan", "val1", "val2", "val3"
It can be rewritten as follows:
SELECT
"Artnr_ID",
to_char("Date_plan", '') AS "Jaar",
to_char("Date_plan", 'MM') AS "Maand",
count("tAr"."Artikelnr") AS "Monthly_count",
"val1", "val2", "val3",
count("Artikelnr") + "val1" + count("Artikelnr") * "val2" +
count("Artikelnr") * "val3"
FROM (
(
"tblAnalyses" AS "tAn"
INNER JOIN "tblStudies" AS "tS" ON "tAn"."Studie_ID" = "tS"."Studie_ID"
)
INNER JOIN "tblFichenr" AS "tF" ON "tS"."ID_fichenr" = "tF"."ID"
)
INNER JOIN "tblArtnrs" AS "tAr" ON "tF"."ID_Art_nrs" = "tAr"."Artnr_ID"
GROUP BY "Artnr_ID", "Date_plan", "val1", "val2", "val3"
Aliases have been used to make the overall query expression clearer and
shorter.
Regards.
--
nha / Lyon / France.
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need magical advice for counting NOTHING
Hello, Le 23/07/09 10:23, Glenn Maynard a écrit : > 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 > [...] > 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; > [...] In the same direction as Andreas Krestchmer and Glenn Maynard (thanks to the latter for DDL assumed statements), the following query should also suit: SELECT user_name, log_type, COUNT(log_type_fk) FROM (users CROSS JOIN log_type) LEFT JOIN log ON (user_id = user_fk AND log_type_id = log_type_fk) WHERE (ts IS BETWEEN sometime AND another) GROUP BY user_name, log_type ORDER BY user_name, log_type It is syntactically nearer the original query and includes no class operator. Here are the two main ideas: - Building all the possible couples of user name and log type by cross-joining users and log_type tables; - Counting rows in log table matching each couple (user, log_type) from the previous cross-join (LEFT JOIN ensures that each row of the table on the left is mined). While it is formally assumed that user_id and log_type_id are respectively keys for users and log_type tables, it is semantically admitted here that user_name identifies user_id in users table and log_type identifies log_type_id in log_type table. Regards. -- nha / Lyon / France. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
