From: | Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com> |
---|---|
To: | Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> |
Cc: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: PostgreSQL 8.4.8 bringing my website down every evening |
Date: | 2011-06-21 17:00:30 |
Message-ID: | BANLkTimmTrUY4KF94k0Bxi7CFOWY6qY3-g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2011/6/21 Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my>:
> At 04:13 AM 6/20/2011, Alexander Farber wrote:
>>
>> why add a begin/commit if I only
>> have SELECT statements
>> there (in the default mode) and
>> the data isn't critical to me
>> (just some player statistics and
>> notes by other players - i.e.
>> a statistic or note is ok to be lost
>> occasionally)?
>
> If you're not intending to write anything to the database you could try do a
> rollback instead of commit.
>
> I'm not familiar with your system, but if you are getting
>
>> SQLSTATE[25P02]: In failed sql transaction:
>> 7 ERROR: current transaction is aborted,
>> commands ignored until end of transaction block
I understood that the OP put a begin/commit around *each* request (so
one around prepare, and one around exec)
Php documentation contains useful information with examples.
Alexander, I suggest you to just add a begin before the 'prepare' and
a commit after the 'exec', not between each.
>
> Maybe you can do a rollback, begin, followed by your sql statements then
> rollback again.
>
> You would get warnings for a rollback followed by rollback, but they might
> not be fatal (not sure about your config).
>
> There might be a problem with one of the SQL queries and that's why it
> cannot be committed.
>
> Alternatively the program logic might be bypassing a COMMIT. A BEGIN
> followed by a BEGIN would cause an "already in transaction" error which
> would cause the "transaction is aborted" problem.
this is not correct
cedric=# begin ;
BEGIN
cedric=# begin ;
ATTENTION: une transaction est déjà en cours
BEGIN
cedric=# select 1;
?column?
----------
1
(1 ligne)
cedric=# commit;
COMMIT
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2011-06-21 18:45:25 | Re: LISTEN filtering |
Previous Message | Tom Lane | 2011-06-21 16:25:19 | Re: Partitioning and constraint exclusion |