pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDeadlock
9+ messages / 5 participants
[nested] [flat]
* Deadlock
@ 2007-08-01 17:43 Benjamin Krajmalnik <kraj@illumen.com>
2007-08-01 18:45 ` Re: Deadlock Milen A. Radev <milen@radev.net>
0 siblings, 1 reply; 9+ messages in thread
From: Benjamin Krajmalnik @ 2007-08-01 17:43 UTC (permalink / raw)
To: Benjamin Krajmalnik <kraj@illumen.com>; pgsql-admin
My aplogies - I forgot to set the subject of the problem I am having
when I got lazy and used "reply".
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of
> Benjamin Krajmalnik
> Sent: Wednesday, August 01, 2007 11:32 AM
> To: pgsql-admin
> Subject: Re: [ADMIN] stracing a connection
>
> I have ascheduled pgAgent job which runs monthly executing a
> stored procedure which handles some partitoned tables.
> Essentially, it truncated the data in a given partitio and
> then it changes its rules so it will be ready to accept the
> data for its respective next cycle.
>
> The stored procedure follows:
>
>
>
> CREATE OR REPLACE FUNCTION fn_cleardata()
> RETURNS void AS
> $BODY$
> declare
> year integer;
> month integer;
> endmonth integer;
> endyear integer;
> startoffset integer;
> endoffset integer;
> currentdate date;
> i integer;
> tablename varchar;
> startday integer;
> endday integer;
>
> begin
> currentdate := CURRENT_DATE;
> month := DATE_PART('month', currentdate)-2;
> year := DATE_PART('year', currentdate);
>
> if month <=0 then
> month := month+12;
> else
> year := year+1;
> end if;
>
> startoffset := ((month-1)*4)+1;
> endoffset := startoffset+3;
> startday := 1;
> endday := 9;
> endmonth = month;
> endyear = year;
>
> for i in startoffset..endoffset loop
> if i < 10 then
> tablename := 'tblksdata' || 0 || i;
> else
> tablename := 'tblksdata' || i;
> end if;
> EXECUTE 'TRUNCATE TABLE '||tablename;
> EXECUTE 'ALTER TABLE '||tablename||' DROP CONSTRAINT
> '||tablename||'_datecheck';
> EXECUTE 'ALTER TABLE '||tablename||' ADD CONSTRAINT
> '||tablename||'_datecheck
> CHECK (testtime >=
> '''||year||'-'||month||'-'||startday||' 00:00:00''::timestamp
> without time zone
> AND testtime <
> '''||endyear||'-'||endmonth||'-'||endday||'
> 00:00:00''::timestamp without time zone)';
> EXECUTE 'CREATE OR REPLACE RULE '||tablename||'_rl_insert AS
> ON INSERT TO tblksdata
> WHERE new.testtime >=
> '''||year||'-'||month||'-'||startday||'''::timestamp without time zone
> AND new.testtime <
> '''||endyear||'-'||endmonth||'-'||endday||'''::timestamp
> without time zone
> DO INSTEAD INSERT INTO '||tablename||'
> (testtime, replyval, statusid, kstestssysid)
> VALUES (new.testtime,
> new.replyval, new.statusid, new.kstestssysid)';
>
> startday := startday + 8;
> endday := endday + 8;
> if startday = 25 then
> endday = 1;
> endmonth = endmonth + 1;
> if endmonth > 12 then
> endyear := endyear + 1;
> endmonth := endmonth - 12;
> end if;
> end if;
> end loop;
> end
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION fn_cleardata() OWNER TO postgres;
>
>
> If I run it manually from pgAdmin during the day it runs fine
> without returning an error. When it runs scheduled, it is
> returning an error.
>
> At the same time, another function accessing the parent table
> may be running, calculating statistical data for the tests.
> The partition which is truncated and whose constraints and
> rule is being rewritten has data which is beyond the range of
> that in the statistical calculation function, yet I am
> getting a deadlock. The error in the pgAgent log
> follows:
>
>
> ERROR: deadlock detected
>
> DETAIL: Process 47642 waits for AccessExclusiveLock on
> relation 317009 of database 316900; blocked by process 46648.
>
> Process 46648 waits for RowExclusiveLock on relation 317071
> of database 316900; blocked by process 47642.
>
> CONTEXT: SQL statement "CREATE OR REPLACE RULE
> tblksdata21_rl_insert AS
>
> ON INSERT TO tblksdata
>
> WHERE new.testtime >= '2008-6-1'::timestamp without time zone
>
> AND new.testtime < '2008-6-9'::timestamp without time zone
>
> DO INSTEAD INSERT INTO tblksdata21 (testtime, replyval, statusid,
> kstestssysid)
>
> VALUES (new.testtime, new.replyval, new.statusid, new.kstestssysid)"
>
> PL/pgSQL function "fn_cleardata" line 43 at execute statement
>
> tblksdata is the parent table. The functio running against
> it is not updateing any of the data - it is simply
> calculating aggregates for specifc rows (max, min, stddev).
>
> Any suggestions would be appreciated.
> I am running PostgreSQL 8.1.4 on FreeBSD.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Deadlock
2007-08-01 17:43 Deadlock Benjamin Krajmalnik <kraj@illumen.com>
@ 2007-08-01 18:45 ` Milen A. Radev <milen@radev.net>
2007-08-01 19:44 ` Re: Deadlock Benjamin Krajmalnik <kraj@illumen.com>
0 siblings, 1 reply; 9+ messages in thread
From: Milen A. Radev @ 2007-08-01 18:45 UTC (permalink / raw)
To: pgsql-admin
Benjamin Krajmalnik написа:
> My aplogies - I forgot to set the subject of the problem I am having
> when I got lazy and used "reply".
[...]
Do not use "Reply" to start a new thread.
--
Milen A. Radev
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Deadlock
2007-08-01 17:43 Deadlock Benjamin Krajmalnik <kraj@illumen.com>
2007-08-01 18:45 ` Re: Deadlock Milen A. Radev <milen@radev.net>
@ 2007-08-01 19:44 ` Benjamin Krajmalnik <kraj@illumen.com>
0 siblings, 0 replies; 9+ messages in thread
From: Benjamin Krajmalnik @ 2007-08-01 19:44 UTC (permalink / raw)
To: Milen A. Radev <milen@radev.net>; pgsql-admin
In case you did not read my message, it started with "My apologies"
becaue I made the mistake and noticed it.
Please, do not try to lecture me. If you have something substanbtive to
reply concerning the issue I am having, then by all means do reply. I
did not know you were the self-proclaimed list-server police.
Have a nice day
^ permalink raw reply [nested|flat] 9+ messages in thread
* Deadlock
@ 2024-04-02 16:40 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 16:45 ` Re: Deadlock Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rajesh Kumar @ 2024-04-02 16:40 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi
If I receive an alert for deadlock, what should I do? Like what analysis
should I do? No way to find a root cause?
Under pg_locks, what I can analyse?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Deadlock
2024-04-02 16:40 Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-04-02 16:45 ` Holger Jakobs <holger@jakobs.com>
2024-04-02 17:09 ` Re: Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Holger Jakobs @ 2024-04-02 16:45 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 02.04.24 um 18:40 schrieb Rajesh Kumar:
> Hi
>
> If I receive an alert for deadlock, what should I do? Like what
> analysis should I do? No way to find a root cause?
> Under pg_locks, what I can analyse?
>
If you receive an error with a deadlock, the current transaction is
dead. You have to decide whether to try again.
Within the error message you find a hint which other (not aborted)
transaction was involved in the deadlock. So you have all the
information you need to find the culprit.
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
chat with me on Signal messenger
<https://signal.me/#eu/GpIP259bBne_JiBYJFvbZCLYJELhB-92nJfncACL1LUeBOphfXFFgLYYVXv_AGhg;
Attachments:
[image/png] TapMuhNyQVuzuVPA.png (551B, ../../58661f82-620c-8116-efe7-23266578289c@jakobs.com/3-TapMuhNyQVuzuVPA.png)
download | view image
[application/pgp-signature] OpenPGP_signature (202B, ../../58661f82-620c-8116-efe7-23266578289c@jakobs.com/4-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Deadlock
2024-04-02 16:40 Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 16:45 ` Re: Deadlock Holger Jakobs <holger@jakobs.com>
@ 2024-04-02 17:09 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 17:42 ` Re: Deadlock Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rajesh Kumar @ 2024-04-02 17:09 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Yes, I am trying to find out what message i should reply if I receive email
with deadlock notification.
I understand how deadlock occurs, hot to find them in logs, prevention of
deadlock(ex: oqueries in order) etc.
But since the deadlock_timeout is 1s, they don't probably hang around. So
post getting an alert, not sure what to respond to them
On Tue, 2 Apr 2024, 22:15 Holger Jakobs, <holger@jakobs.com> wrote:
> Am 02.04.24 um 18:40 schrieb Rajesh Kumar:
>
> Hi
>
> If I receive an alert for deadlock, what should I do? Like what analysis
> should I do? No way to find a root cause?
> Under pg_locks, what I can analyse?
>
> If you receive an error with a deadlock, the current transaction is dead.
> You have to decide whether to try again.
>
> Within the error message you find a hint which other (not aborted)
> transaction was involved in the deadlock. So you have all the information
> you need to find the culprit.
>
> Regards,
>
> Holger
> --
>
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
> chat with me on Signal messenger
> <https://signal.me/#eu/GpIP259bBne_JiBYJFvbZCLYJELhB-92nJfncACL1LUeBOphfXFFgLYYVXv_AGhg;
>
Attachments:
[image/png] TapMuhNyQVuzuVPA.png (551B, ../../CAJk5AtbDMDD5HKP796qoNPzKqE4qWgnr-ySE7ErED6ph6ebdtw@mail.gmail.com/3-TapMuhNyQVuzuVPA.png)
download | view image
[image/png] TapMuhNyQVuzuVPA.png (551B, ../../CAJk5AtbDMDD5HKP796qoNPzKqE4qWgnr-ySE7ErED6ph6ebdtw@mail.gmail.com/4-TapMuhNyQVuzuVPA.png)
download | view image
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Deadlock
2024-04-02 16:40 Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 16:45 ` Re: Deadlock Holger Jakobs <holger@jakobs.com>
2024-04-02 17:09 ` Re: Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-04-02 17:42 ` Holger Jakobs <holger@jakobs.com>
2024-04-02 19:00 ` Re: Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Holger Jakobs @ 2024-04-02 17:42 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Am 02.04.24 um 19:09 schrieb Rajesh Kumar:
> Yes, I am trying to find out what message i should reply if I receive
> email with deadlock notification.
>
> I understand how deadlock occurs, hot to find them in logs, prevention
> of deadlock(ex: oqueries in order) etc.
>
> But since the deadlock_timeout is 1s, they don't probably hang around.
> So post getting an alert, not sure what to respond to them
>
> On Tue, 2 Apr 2024, 22:15 Holger Jakobs, <holger@jakobs.com> wrote:
>
> Am 02.04.24 um 18:40 schrieb Rajesh Kumar:
>> Hi
>>
>> If I receive an alert for deadlock, what should I do? Like what
>> analysis should I do? No way to find a root cause?
>> Under pg_locks, what I can analyse?
>>
> If you receive an error with a deadlock, the current transaction
> is dead. You have to decide whether to try again.
>
> Within the error message you find a hint which other (not aborted)
> transaction was involved in the deadlock. So you have all the
> information you need to find the culprit.
>
> Regards,
>
> Holger
>
> --
>
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
> chat with me on Signal messenger
> <https://signal.me/#eu/GpIP259bBne_JiBYJFvbZCLYJELhB-92nJfncACL1LUeBOphfXFFgLYYVXv_AGhg;
>
I wonder what you are writing about. A database doesn't send email
notifications. That must be a feature of some application.
So you will have to deal the the application code or its developers.
Or is it a customer sending you a message that some deadlock happened on
their system? Still, you have to deal with the application code or the
log files of the database system.
Regards,
Holger
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../0ce31eb2-4209-dc00-adf9-4d2c05ec5187@jakobs.com/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Deadlock
2024-04-02 16:40 Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 16:45 ` Re: Deadlock Holger Jakobs <holger@jakobs.com>
2024-04-02 17:09 ` Re: Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 17:42 ` Re: Deadlock Holger Jakobs <holger@jakobs.com>
@ 2024-04-02 19:00 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 20:09 ` Re: Deadlock Dan Smith <j.daniel.smith1@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rajesh Kumar @ 2024-04-02 19:00 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Ok, do u mean to say that we do not have to do anything if I see an error
in log for deadlock lock?
On Tue, 2 Apr 2024, 23:12 Holger Jakobs, <holger@jakobs.com> wrote:
>
> Am 02.04.24 um 19:09 schrieb Rajesh Kumar:
>
> Yes, I am trying to find out what message i should reply if I receive
> email with deadlock notification.
>
> I understand how deadlock occurs, hot to find them in logs, prevention of
> deadlock(ex: oqueries in order) etc.
>
> But since the deadlock_timeout is 1s, they don't probably hang around. So
> post getting an alert, not sure what to respond to them
>
> On Tue, 2 Apr 2024, 22:15 Holger Jakobs, <holger@jakobs.com> wrote:
>
>> Am 02.04.24 um 18:40 schrieb Rajesh Kumar:
>>
>> Hi
>>
>> If I receive an alert for deadlock, what should I do? Like what analysis
>> should I do? No way to find a root cause?
>> Under pg_locks, what I can analyse?
>>
>> If you receive an error with a deadlock, the current transaction is dead.
>> You have to decide whether to try again.
>>
>> Within the error message you find a hint which other (not aborted)
>> transaction was involved in the deadlock. So you have all the information
>> you need to find the culprit.
>>
>> Regards,
>>
>> Holger
>> --
>>
>> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>>
>> chat with me on Signal messenger
>> <https://signal.me/#eu/GpIP259bBne_JiBYJFvbZCLYJELhB-92nJfncACL1LUeBOphfXFFgLYYVXv_AGhg;
>>
> I wonder what you are writing about. A database doesn't send email
> notifications. That must be a feature of some application.
>
> So you will have to deal the the application code or its developers.
>
> Or is it a customer sending you a message that some deadlock happened on
> their system? Still, you have to deal with the application code or the log
> files of the database system.
>
> Regards,
> Holger
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Deadlock
2024-04-02 16:40 Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 16:45 ` Re: Deadlock Holger Jakobs <holger@jakobs.com>
2024-04-02 17:09 ` Re: Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 17:42 ` Re: Deadlock Holger Jakobs <holger@jakobs.com>
2024-04-02 19:00 ` Re: Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-04-02 20:09 ` Dan Smith <j.daniel.smith1@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Dan Smith @ 2024-04-02 20:09 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
I think what is being said is that parsing the logs or looking for specific
events such as a deadlock is the responsibility of the monitoring or the
application interacting with the database, not the database server. The
database server is simply tasked with logging / providing notice to the
client in this instance. Ideally, as previously stated, the application
should be determining how to address the issue.
If memory serves, pg_stat_database
<https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW;
and pg_stat_database_conflicts
<https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-CONFLICTS-VIEW;
views
have some statistics and information regarding deadlocks which can be
checked.
As for monitoring: Percona Monitoring and Management
<https://www.percona.com/software/database-tools/percona-monitoring-and-management/postgresql-monitor...;,
pganalyze
<https://pganalyze.com/blog/postgresql-log-monitoring-101-deadlocks-checkpoints-blocked-queries;,
pgbadger <http://pgbadger.darold.net/; or any similar tool might prove to
be helpful. Ultimately, whatever alerting you configure from monitoring is
what should be sending emails about potential issues vs the database server
directly. There are probably extensions or walkthroughs on using Postgres
to trigger sending of emails in a manner similar to SQL Server but this
does not seem aligned with the database server's purpose (manage access,
storage, and retrieval of data). Hope this information helps you find a
suitable solution for your use case.
Best regards,
Dan Smith
On Tue, Apr 2, 2024 at 3:01 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:
> Ok, do u mean to say that we do not have to do anything if I see an error
> in log for deadlock lock?
>
>
>
> On Tue, 2 Apr 2024, 23:12 Holger Jakobs, <holger@jakobs.com> wrote:
>
>>
>> Am 02.04.24 um 19:09 schrieb Rajesh Kumar:
>>
>> Yes, I am trying to find out what message i should reply if I receive
>> email with deadlock notification.
>>
>> I understand how deadlock occurs, hot to find them in logs, prevention of
>> deadlock(ex: oqueries in order) etc.
>>
>> But since the deadlock_timeout is 1s, they don't probably hang around. So
>> post getting an alert, not sure what to respond to them
>>
>> On Tue, 2 Apr 2024, 22:15 Holger Jakobs, <holger@jakobs.com> wrote:
>>
>>> Am 02.04.24 um 18:40 schrieb Rajesh Kumar:
>>>
>>> Hi
>>>
>>> If I receive an alert for deadlock, what should I do? Like what analysis
>>> should I do? No way to find a root cause?
>>> Under pg_locks, what I can analyse?
>>>
>>> If you receive an error with a deadlock, the current transaction is
>>> dead. You have to decide whether to try again.
>>>
>>> Within the error message you find a hint which other (not aborted)
>>> transaction was involved in the deadlock. So you have all the information
>>> you need to find the culprit.
>>>
>>> Regards,
>>>
>>> Holger
>>> --
>>>
>>> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>>>
>>> chat with me on Signal messenger
>>> <https://signal.me/#eu/GpIP259bBne_JiBYJFvbZCLYJELhB-92nJfncACL1LUeBOphfXFFgLYYVXv_AGhg;
>>>
>> I wonder what you are writing about. A database doesn't send email
>> notifications. That must be a feature of some application.
>>
>> So you will have to deal the the application code or its developers.
>>
>> Or is it a customer sending you a message that some deadlock happened on
>> their system? Still, you have to deal with the application code or the log
>> files of the database system.
>>
>> Regards,
>> Holger
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-04-02 20:09 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-01 17:43 Deadlock Benjamin Krajmalnik <kraj@illumen.com>
2007-08-01 18:45 ` Milen A. Radev <milen@radev.net>
2007-08-01 19:44 ` Benjamin Krajmalnik <kraj@illumen.com>
2024-04-02 16:40 Deadlock Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 16:45 ` Holger Jakobs <holger@jakobs.com>
2024-04-02 17:09 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 17:42 ` Holger Jakobs <holger@jakobs.com>
2024-04-02 19:00 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-04-02 20:09 ` Dan Smith <j.daniel.smith1@gmail.com>
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