pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PostgreSQL 7.4.10 hanging on delete
8+ messages / 4 participants
[nested] [flat]

* PostgreSQL 7.4.10 hanging on delete
@ 2005-12-20 16:21 Jonathan Parkin <jonathan.parkin@legendplc.com>
  2005-12-20 16:37 ` Re: PostgreSQL 7.4.10 hanging on delete Tom Lane <tgl@sss.pgh.pa.us>
  2005-12-21 08:02 ` Re: PostgreSQL 7.4.10 hanging on delete Pandurangan R S <pandurangan.r.s@gmail.com>
  2005-12-21 09:54 ` Re: PostgreSQL 7.4.10 hanging on delete Andrea <andrea@compesa.com.br>
  0 siblings, 3 replies; 8+ messages in thread

From: Jonathan Parkin @ 2005-12-20 16:21 UTC (permalink / raw)
  To: pgsql-admin

I have a reasonably large, live, system-critical database.  A perl
script on another machine connects and issues a sequence of commands in
a transaction, the last of which is a delete.  The delete never returns
a response, and the connection never times out.  The postgres process
handling the delete is in a scheduled state, but stracing it produces no
output at all.

The table contains entries for a "parent" and it's "children".
Grandchildren and other descendents never exist in the table.  A before
delete trigger removes the children when the parent is deleted and the
delete is always issued for a parent.  Children have exactly one parent.

By tcpdumping the SQL connection the system consistently hangs on the
same delete for one of a set of rows. As the delete never commits the
transaction is never finished.   Other rows (parents and children) can
be deleted without issue.

Killing the perl process at the other end, and subsequently the
connection timing out on the perl-server end does not stop the postgres
process handling the delete, and it continues to produce no output when
straced.

Restarting the server, doing a VACUUM FULL ANALYZE and a REINDEX of all
tables in the database (including system tables) has no effect.

This was first noticed using PostgreSQL 7.4.7.  Upgrading to 7.4.10 has
not helped. Upgrading to 8.x may not be an option due to the systems
connecting to the database (this is being investigated).

My next logical step is to stop access to the database, take a dump of
it, remove it, and rebuild it.  Can anyone think of a reason why I
should not do this?

Can anyone think of anything else I should try?

I welcome all suggestions, no matter how obvious they may appear.

Thanks,

Jonathan

-- 
Best Regards

Jonathan Parkin
Developer

Legend Communications plc
T: 0844 390 2049
F: 0844 390 2001
E: jonathan.parkin@legendplc.com
W: http://www.legend.co.uk/

The information in this message is confidential and may be legally
privileged. Unauthorised disclosure, copying or distribution, either
whole or in part; or action taken in reliance on its content is
prohibited. If you are not the intended recipient, please notify Legend
Communications immediately.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PostgreSQL 7.4.10 hanging on delete
  2005-12-20 16:21 PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
@ 2005-12-20 16:37 ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-12-21 10:15   ` Re: PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
  2 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2005-12-20 16:37 UTC (permalink / raw)
  To: Jonathan Parkin <jonathan.parkin@legendplc.com>; +Cc: pgsql-admin

Jonathan Parkin <jonathan.parkin@legendplc.com> writes:
> I have a reasonably large, live, system-critical database.  A perl
> script on another machine connects and issues a sequence of commands in
> a transaction, the last of which is a delete.  The delete never returns
> a response, and the connection never times out.  The postgres process
> handling the delete is in a scheduled state, but stracing it produces no
> output at all.

Sounds like it's stuck in a tight loop.  Can you attach to the postgres
process with gdb and get a stack trace to find out where the loop is?

If this is a specific row causing the issue, then I'd wonder about
data corruption of some sort.  It might be worth looking at the
table with pg_filedump (from http://sources.redhat.com/rhdb/).

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PostgreSQL 7.4.10 hanging on delete
  2005-12-20 16:21 PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
  2005-12-20 16:37 ` Re: PostgreSQL 7.4.10 hanging on delete Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-21 10:15   ` Jonathan Parkin <jonathan.parkin@legendplc.com>
  2005-12-21 15:19     ` Re: PostgreSQL 7.4.10 hanging on delete Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Jonathan Parkin @ 2005-12-21 10:15 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

On Tue, 2005-12-20 at 16:37, Tom Lane wrote:
> Jonathan Parkin <jonathan.parkin@legendplc.com> writes:
> > I have a reasonably large, live, system-critical database.  A perl
> > script on another machine connects and issues a sequence of commands in
> > a transaction, the last of which is a delete.  The delete never returns
> > a response, and the connection never times out.  The postgres process
> > handling the delete is in a scheduled state, but stracing it produces no
> > output at all.
> 
> Sounds like it's stuck in a tight loop.  Can you attach to the postgres
> process with gdb and get a stack trace to find out where the loop is?

I've attached two back traces.  The first (process 23755) is unique
across the ten or so current hangign processes.  All the rest follow the
same trace as the second (process 25376).  I notice that first (process
23755) has the lowest-numbered PID of the hanging processes, suggesting
it began before the rest.

The "ignoring modified tuple in DELETE trigger" is reaching syslog and
being logged in /var/log/messages.  This normally occurs during
processing.  Syslog apears to be operating correctly, and HUPing it has
had no effect.

I should note that, other than the upgrade mentioned previously, no
changes have been made recently that I know of, and that the system has
previously appeared to be working correctly.  It is not clear when this
issue began.

> If this is a specific row causing the issue, then I'd wonder about
> data corruption of some sort.  It might be worth looking at the
> table with pg_filedump (from http://sources.redhat.com/rhdb/).

Thanks, I hadn't heard of that.  I'll see what I can turn up with it. 
Anything in particular I should be looking for?

Our current plan is to do a rebuild of the database tonight (UK time). 
This would hopefully eliminate any such data corruption.

-- 
Best Regards

Jonathan Parkin
Developer

Legend Communications plc
T: 0844 390 2049
F: 0844 390 2001
E: jonathan.parkin@legendplc.com
W: http://www.legend.co.uk/

The information in this message is confidential and may be legally
privileged. Unauthorised disclosure, copying or distribution, either
whole or in part; or action taken in reliance on its content is
prohibited. If you are not the intended recipient, please notify Legend
Communications immediately.

Attachments:

pgsql-traces (text/plain, 7.3K) download ### Start of trace one (Process 23755) #0 0x485ec845 in __pthread_sigsuspend () from /lib/i686/libpthread.so.0 #1 0x485ec318 in __pthread_wait_for_restart_signal () from /lib/i686/libpthread.so.0 #2 0x485edd30 in __pthread_alt_lock () from /lib/i686/libpthread.so.0 #3 0x485ead37 in pthread_mutex_lock () from /lib/i686/libpthread.so.0 #4 0x402e22fc in vsyslog () from /lib/i686/libc.so.6 #5 0x402e1e6f in syslog () from /lib/i686/libc.so.6 #6 0x081e4a52 in write_syslog (level=5, line=0x4033afac "") at elog.c:1013 #7 0x081e4d7f in send_message_to_server_log (edata=0x82aad80) at elog.c:1146 #8 0x081e3b0c in errfinish (dummy=0) at elog.c:341 #9 0x081e46a6 in elog_finish (elevel=-4, fmt=0x485ac920 "ignoring modified tuple in DELETE trigger") at elog.c:849 #10 0x485247d2 in PLy_trigger_handler (fcinfo=0xbfffddf0, proc=0x83b8860) at plpython.c:455 #11 0x485244d0 in plpython_call_handler (fcinfo=0xbfffddf0) at plpython.c:370 #12 0x080f253e in ExecCallTriggerFunc (trigdata=0xbfffdcbc, finfo=0x485df998, per_tuple_context=0xfffffffc) at trigger.c:1150 #13 0x080f2b7a in ExecBRDeleteTriggers (estate=0x83ad828, relinfo=0x83ad960, tupleid=0xfffffffc, cid=4294967292) at trigger.c:1377 #14 0x08105b7c in ExecDelete (slot=0x83adee0, tupleid=0xbfffe000, estate=0x83ad828) at execMain.c:1474 #15 0x08105545 in ExecutePlan (estate=0x83ad828, planstate=0x83adf00, operation=CMD_DELETE, numberTuples=0, direction=-4, dest=0x82768d4) at execMain.c:1262 #16 0x081046c8 in ExecutorRun (queryDesc=0x83ba1a8, direction=ForwardScanDirection, count=-4) at execMain.c:249 #17 0x08118464 in _SPI_pquery (queryDesc=0x83ad828, runit=0 '\0', useCurrentSnapshot=8 '\b', tcount=-4) at spi.c:1254 #18 0x08118356 in _SPI_execute_plan (plan=0xfffffffc, Values=0x83a5328, Nulls=0x83a9b48 " ", useCurrentSnapshot=0 '\0', tcount=0) at spi.c:1201 #19 0x08116832 in SPI_execp (plan=0x83ad828, Values=0x83a5328, Nulls=0xfffffffc <Address 0xfffffffc out of bounds>, tcount=0) at spi.c:241 #20 0x485281e9 in PLy_spi_execute_plan (ob=0x834d418, list=0x834c82c, limit=0) at plpython.c:2205 #21 0x4852802d in PLy_spi_execute (self=0x0, args=0x83bdd54) at plpython.c:2082 #22 0x48542624 in PyCFunction_Call () from /usr/lib/pgsql/plpython.so #23 0x4856841e in eval_frame () from /usr/lib/pgsql/plpython.so #24 0x48568e8e in PyEval_EvalCodeEx () from /usr/lib/pgsql/plpython.so #25 0x4856a3eb in fast_function () from /usr/lib/pgsql/plpython.so #26 0x48568363 in eval_frame () from /usr/lib/pgsql/plpython.so #27 0x48568e8e in PyEval_EvalCodeEx () from /usr/lib/pgsql/plpython.so #28 0x48565d85 in PyEval_EvalCode () from /usr/lib/pgsql/plpython.so #29 0x48525bd7 in PLy_procedure_call (proc=0x83b8860, kargs=0xfffffffc <Address 0xfffffffc out of bounds>, vargs=0xfffffffc) at plpython.c:881 #30 0x48524621 in PLy_trigger_handler (fcinfo=0xbfffe7c0, proc=0x83b8860) at plpython.c:420 #31 0x485244d0 in plpython_call_handler (fcinfo=0xbfffe7c0) at plpython.c:370 #32 0x080f253e in ExecCallTriggerFunc (trigdata=0xbfffe68c, finfo=0x485df998, per_tuple_context=0xfffffffc) at trigger.c:1150 #33 0x080f2b7a in ExecBRDeleteTriggers (estate=0x8336d78, relinfo=0x8336eb0, tupleid=0xfffffffc, cid=4294967292) at trigger.c:1377 #34 0x08105b7c in ExecDelete (slot=0x8337430, tupleid=0xbfffe9d0, estate=0x8336d78) at execMain.c:1474 #35 0x08105545 in ExecutePlan (estate=0x8336d78, planstate=0x8337450, operation=CMD_DELETE, numberTuples=0, direction=-4, dest=0x83282d0) at execMain.c:1262 #36 0x081046c8 in ExecutorRun (queryDesc=0x8344da0, direction=ForwardScanDirection, count=-4) at execMain.c:249 #37 0x0817b782 in ProcessQuery (parsetree=0xfffffffc, plan=0x8336d78, params=0x8, dest=0x0, completionTag=0x8344da0 "\004") at pquery.c:139 #38 0x0817c2f4 in PortalRunMulti (portal=0x832ba10, dest=0x83282d0, altdest=0x83282d0, completionTag=0xbfffeb30 "") at pquery.c:860 #39 0x0817bc63 in PortalRun (portal=0x832ba10, count=2147483647, dest=0x83282d0, altdest=0xfffffffc, completionTag=0xbfffeb30 "") at pquery.c:494 #40 0x08178543 in exec_simple_query ( query_string=0x8326358 ' ' <repeats 45 times>, "DELETE FROM dbbq1 WHERE id = 53831") at postgres.c:914 #41 0x0817ad31 in PostgresMain (argc=4, argv=0x82b8168, username=0x82b8138 "admin") at postgres.c:2918 #42 0x08154880 in BackendFork (port=0x82d2358) at postmaster.c:2564 #43 0x08154273 in BackendStartup (port=0x82d2358) at postmaster.c:2207 #44 0x081527d8 in ServerLoop () at postmaster.c:1119 #45 0x08151e9e in PostmasterMain (argc=5, argv=0x82b72a0) at postmaster.c:897 #46 0x08121bd3 in main (argc=5, argv=0xbffffb74) at main.c:214 #47 0x4021aa07 in __libc_start_main () from /lib/i686/libc.so.6 ### End of trace one (Process 23755) ### Start of trace two (Process 25376) #0 0x485ec845 in __pthread_sigsuspend () from /lib/i686/libpthread.so.0 #1 0x485ec318 in __pthread_wait_for_restart_signal () from /lib/i686/libpthread.so.0 #2 0x485edd30 in __pthread_alt_lock () from /lib/i686/libpthread.so.0 #3 0x485ead37 in pthread_mutex_lock () from /lib/i686/libpthread.so.0 #4 0x402e22fc in vsyslog () from /lib/i686/libc.so.6 #5 0x402e1e6f in syslog () from /lib/i686/libc.so.6 #6 0x081e4a52 in write_syslog (level=5, line=0x4033afac "") at elog.c:1013 #7 0x081e4d7f in send_message_to_server_log (edata=0x82aad80) at elog.c:1146 #8 0x081e3b0c in errfinish (dummy=0) at elog.c:341 #9 0x081e46a6 in elog_finish (elevel=-4, fmt=0x485ac920 "ignoring modified tuple in DELETE trigger") at elog.c:849 #10 0x485247d2 in PLy_trigger_handler (fcinfo=0xbfffe7c0, proc=0x83428b8) at plpython.c:455 #11 0x485244d0 in plpython_call_handler (fcinfo=0xbfffe7c0) at plpython.c:370 #12 0x080f253e in ExecCallTriggerFunc (trigdata=0xbfffe68c, finfo=0x485df998, per_tuple_context=0xfffffffc) at trigger.c:1150 #13 0x080f2b7a in ExecBRDeleteTriggers (estate=0x83adbe8, relinfo=0x83adda0, tupleid=0xfffffffc, cid=4294967292) at trigger.c:1377 #14 0x08105b7c in ExecDelete (slot=0x83ae320, tupleid=0xbfffe9d0, estate=0x83adbe8) at execMain.c:1474 #15 0x08105545 in ExecutePlan (estate=0x83adbe8, planstate=0x83ae340, operation=CMD_DELETE, numberTuples=0, direction=-4, dest=0x8328698) at execMain.c:1262 #16 0x081046c8 in ExecutorRun (queryDesc=0x8341c20, direction=ForwardScanDirection, count=-4) at execMain.c:249 #17 0x0817b782 in ProcessQuery (parsetree=0xfffffffc, plan=0x83adbe8, params=0x8, dest=0x0, completionTag=0x8341c20 "\004") at pquery.c:139 #18 0x0817c2f4 in PortalRunMulti (portal=0x832bdd8, dest=0x8328698, altdest=0x8328698, completionTag=0xbfffeb30 "") at pquery.c:860 #19 0x0817bc63 in PortalRun (portal=0x832bdd8, count=2147483647, dest=0x8328698, altdest=0xfffffffc, completionTag=0xbfffeb30 "") at pquery.c:494 #20 0x08178543 in exec_simple_query ( query_string=0x8326720 ' ' <repeats 45 times>, "DELETE FROM dbbq1 WHERE id = 54114") at postgres.c:914 #21 0x0817ad31 in PostgresMain (argc=4, argv=0x82b8168, username=0x82b8138 "admin") at postgres.c:2918 #22 0x08154880 in BackendFork (port=0x82d3008) at postmaster.c:2564 #23 0x08154273 in BackendStartup (port=0x82d3008) at postmaster.c:2207 #24 0x081527d8 in ServerLoop () at postmaster.c:1119 #25 0x08151e9e in PostmasterMain (argc=5, argv=0x82b72a0) at postmaster.c:897 #26 0x08121bd3 in main (argc=5, argv=0xbffffb74) at main.c:214 #27 0x4021aa07 in __libc_start_main () from /lib/i686/libc.so.6 ### End of trace two (Process 25376)
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: PostgreSQL 7.4.10 hanging on delete
  2005-12-20 16:21 PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
  2005-12-20 16:37 ` Re: PostgreSQL 7.4.10 hanging on delete Tom Lane <tgl@sss.pgh.pa.us>
  2005-12-21 10:15   ` Re: PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
@ 2005-12-21 15:19     ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-12-21 18:24       ` Re: PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2005-12-21 15:19 UTC (permalink / raw)
  To: Jonathan Parkin <jonathan.parkin@legendplc.com>; +Cc: pgsql-admin

Jonathan Parkin <jonathan.parkin@legendplc.com> writes:
> On Tue, 2005-12-20 at 16:37, Tom Lane wrote:
>> If this is a specific row causing the issue, then I'd wonder about
>> data corruption of some sort.  It might be worth looking at the
>> table with pg_filedump (from http://sources.redhat.com/rhdb/).

> Our current plan is to do a rebuild of the database tonight (UK time). 
> This would hopefully eliminate any such data corruption.

Given the stack traces, I no longer think it's a data corruption issue
--- somehow libc is screwing up.  Backends are single-threaded and there
is *no* reason for pthread_mutex_lock to block, ever.

I have a vague recollection that we saw something similar reported once
before, but it was awhile ago [ digs... ]  Here it is:
http://archives.postgresql.org/pgsql-admin/2003-07/msg00303.php
and later
http://archives.postgresql.org/pgsql-admin/2003-08/msg00064.php

We never did figure out what was up, but it is striking that the other
reporter was also using plpython.  It begins to look like Python is
somehow contributing to the problem.  What versions of python and
glibc are you running, exactly?

If you just want to get your production machine back to stability,
I'd recommend the workaround suggested in that thread, which is to
not use syslog-based logging.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PostgreSQL 7.4.10 hanging on delete
  2005-12-20 16:21 PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
  2005-12-20 16:37 ` Re: PostgreSQL 7.4.10 hanging on delete Tom Lane <tgl@sss.pgh.pa.us>
  2005-12-21 10:15   ` Re: PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
  2005-12-21 15:19     ` Re: PostgreSQL 7.4.10 hanging on delete Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-21 18:24       ` Jonathan Parkin <jonathan.parkin@legendplc.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Jonathan Parkin @ 2005-12-21 18:24 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

> Given the stack traces, I no longer think it's a data corruption issue
> --- somehow libc is screwing up.  Backends are single-threaded and there
> is *no* reason for pthread_mutex_lock to block, ever.
> 
> I have a vague recollection that we saw something similar reported once
> before, but it was awhile ago [ digs... ]  Here it is:
> http://archives.postgresql.org/pgsql-admin/2003-07/msg00303.php
> and later
> http://archives.postgresql.org/pgsql-admin/2003-08/msg00064.php
> 
> We never did figure out what was up, but it is striking that the other
> reporter was also using plpython.  It begins to look like Python is
> somehow contributing to the problem.  What versions of python and
> glibc are you running, exactly?

glibc-2.3.2
python-2.2.2
postgresql-python-7.4.10

Linux 2.4.20

> If you just want to get your production machine back to stability,
> I'd recommend the workaround suggested in that thread, which is to
> not use syslog-based logging.

Which indeed appears to have stopped this from being a problem.

Thank you very much for all the help.  If you'd like any more
information (e.g. on versions of things) I may not be around until the
new year, but I'll happily provide them once I'm back.

Thanks again,

Jon
-- 
Best Regards

Jonathan Parkin
Developer

Legend Communications plc
T: 0844 390 2049
F: 0844 390 2001
E: jonathan.parkin@legendplc.com
W: http://www.legend.co.uk/

The information in this message is confidential and may be legally
privileged. Unauthorised disclosure, copying or distribution, either
whole or in part; or action taken in reliance on its content is
prohibited. If you are not the intended recipient, please notify Legend
Communications immediately.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PostgreSQL 7.4.10 hanging on delete
  2005-12-20 16:21 PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
@ 2005-12-21 08:02 ` Pandurangan R S <pandurangan.r.s@gmail.com>
  2005-12-21 10:10   ` Re: PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
  2 siblings, 1 reply; 8+ messages in thread

From: Pandurangan R S @ 2005-12-21 08:02 UTC (permalink / raw)
  To: Jonathan Parkin <jonathan.parkin@legendplc.com>; +Cc: pgsql-admin

When the delete statements hangs, look into the pg_locks table, to
check if the delete statement is waiting because it has conflict with
rows locked by other transactions.

On 20 Dec 2005 16:21:53 +0000, Jonathan Parkin
<jonathan.parkin@legendplc.com> wrote:
> I have a reasonably large, live, system-critical database.  A perl
> script on another machine connects and issues a sequence of commands in
> a transaction, the last of which is a delete.  The delete never returns
> a response, and the connection never times out.  The postgres process
> handling the delete is in a scheduled state, but stracing it produces no
> output at all.
>
> The table contains entries for a "parent" and it's "children".
> Grandchildren and other descendents never exist in the table.  A before
> delete trigger removes the children when the parent is deleted and the
> delete is always issued for a parent.  Children have exactly one parent.
>
> By tcpdumping the SQL connection the system consistently hangs on the
> same delete for one of a set of rows. As the delete never commits the
> transaction is never finished.   Other rows (parents and children) can
> be deleted without issue.
>
> Killing the perl process at the other end, and subsequently the
> connection timing out on the perl-server end does not stop the postgres
> process handling the delete, and it continues to produce no output when
> straced.
>
> Restarting the server, doing a VACUUM FULL ANALYZE and a REINDEX of all
> tables in the database (including system tables) has no effect.
>
> This was first noticed using PostgreSQL 7.4.7.  Upgrading to 7.4.10 has
> not helped. Upgrading to 8.x may not be an option due to the systems
> connecting to the database (this is being investigated).
>
> My next logical step is to stop access to the database, take a dump of
> it, remove it, and rebuild it.  Can anyone think of a reason why I
> should not do this?
>
> Can anyone think of anything else I should try?
>
> I welcome all suggestions, no matter how obvious they may appear.
>
> Thanks,
>
> Jonathan
>
> --
> Best Regards
>
> Jonathan Parkin
> Developer
>
> Legend Communications plc
> T: 0844 390 2049
> F: 0844 390 2001
> E: jonathan.parkin@legendplc.com
> W: http://www.legend.co.uk/
>
> The information in this message is confidential and may be legally
> privileged. Unauthorised disclosure, copying or distribution, either
> whole or in part; or action taken in reliance on its content is
> prohibited. If you are not the intended recipient, please notify Legend
> Communications immediately.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>


--
Regards
Pandu



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PostgreSQL 7.4.10 hanging on delete
  2005-12-20 16:21 PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
  2005-12-21 08:02 ` Re: PostgreSQL 7.4.10 hanging on delete Pandurangan R S <pandurangan.r.s@gmail.com>
@ 2005-12-21 10:10   ` Jonathan Parkin <jonathan.parkin@legendplc.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Jonathan Parkin @ 2005-12-21 10:10 UTC (permalink / raw)
  To: Pandurangan R S <pandurangan.r.s@gmail.com>; +Cc: pgsql-admin

There are no conflicting locks; this occurs both concurrently with other
processes (dealing with other rows) and when the only two processes with
locks on the database are the one that hangs and the one I'm using to
examine pg_locks.

On Wed, 2005-12-21 at 08:02, Pandurangan R S wrote:
> When the delete statements hangs, look into the pg_locks table, to
> check if the delete statement is waiting because it has conflict with
> rows locked by other transactions.
> 
> On 20 Dec 2005 16:21:53 +0000, Jonathan Parkin
> <jonathan.parkin@legendplc.com> wrote:
> > I have a reasonably large, live, system-critical database.  A perl
> > script on another machine connects and issues a sequence of commands in
> > a transaction, the last of which is a delete.  The delete never returns
> > a response, and the connection never times out.  The postgres process
> > handling the delete is in a scheduled state, but stracing it produces no
> > output at all.

[Remainder of original message cut for brevity]

-- 
Best Regards

Jonathan Parkin
Developer

Legend Communications plc
T: 0844 390 2049
F: 0844 390 2001
E: jonathan.parkin@legendplc.com
W: http://www.legend.co.uk/

The information in this message is confidential and may be legally
privileged. Unauthorised disclosure, copying or distribution, either
whole or in part; or action taken in reliance on its content is
prohibited. If you are not the intended recipient, please notify Legend
Communications immediately.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: PostgreSQL 7.4.10 hanging on delete
  2005-12-20 16:21 PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
@ 2005-12-21 09:54 ` Andrea <andrea@compesa.com.br>
  2 siblings, 0 replies; 8+ messages in thread

From: Andrea @ 2005-12-21 09:54 UTC (permalink / raw)
  To: Jonathan Parkin <jonathan.parkin@legendplc.com>; pgsql-admin

Hi,
I dont´t know if i understand exactly your problem, but ...
When i tried to delete all of the rows of a table , it seems to never end.
So i created an index to each foreing key and  the command works very fast.
Good Luck!
Andréa Pereira
Brasil-Recife

----- Original Message -----
From: "Jonathan Parkin" <jonathan.parkin@legendplc.com>
To: "PostgreSQL Admin Mailing List" <pgsql-admin@postgresql.org>
Sent: Tuesday, December 20, 2005 2:21 PM
Subject: [ADMIN] PostgreSQL 7.4.10 hanging on delete


> I have a reasonably large, live, system-critical database.  A perl
> script on another machine connects and issues a sequence of commands in
> a transaction, the last of which is a delete.  The delete never returns
> a response, and the connection never times out.  The postgres process
> handling the delete is in a scheduled state, but stracing it produces no
> output at all.
>
> The table contains entries for a "parent" and it's "children".
> Grandchildren and other descendents never exist in the table.  A before
> delete trigger removes the children when the parent is deleted and the
> delete is always issued for a parent.  Children have exactly one parent.
>
> By tcpdumping the SQL connection the system consistently hangs on the
> same delete for one of a set of rows. As the delete never commits the
> transaction is never finished.   Other rows (parents and children) can
> be deleted without issue.
>
> Killing the perl process at the other end, and subsequently the
> connection timing out on the perl-server end does not stop the postgres
> process handling the delete, and it continues to produce no output when
> straced.
>
> Restarting the server, doing a VACUUM FULL ANALYZE and a REINDEX of all
> tables in the database (including system tables) has no effect.
>
> This was first noticed using PostgreSQL 7.4.7.  Upgrading to 7.4.10 has
> not helped. Upgrading to 8.x may not be an option due to the systems
> connecting to the database (this is being investigated).
>
> My next logical step is to stop access to the database, take a dump of
> it, remove it, and rebuild it.  Can anyone think of a reason why I
> should not do this?
>
> Can anyone think of anything else I should try?
>
> I welcome all suggestions, no matter how obvious they may appear.
>
> Thanks,
>
> Jonathan
>
> --
> Best Regards
>
> Jonathan Parkin
> Developer
>
> Legend Communications plc
> T: 0844 390 2049
> F: 0844 390 2001
> E: jonathan.parkin@legendplc.com
> W: http://www.legend.co.uk/
>
> The information in this message is confidential and may be legally
> privileged. Unauthorised disclosure, copying or distribution, either
> whole or in part; or action taken in reliance on its content is
> prohibited. If you are not the intended recipient, please notify Legend
> Communications immediately.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
> --
> Esta mensagem foi verificada pelo sistema de antivírus e
>  acredita-se estar livre de perigo.
>
>
>
> --
> Internal Virus Database is out-of-date.
> Checked by AVG Anti-Virus.
> Version: 7.0.300 / Virus Database: 265.6.5 - Release Date: 26/12/2004
>



-- 
Internal Virus Database is out-of-date.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.6.5 - Release Date: 26/12/2004


-- 
Esta mensagem foi verificada pelo sistema de antivírus e
 acredita-se estar livre de perigo.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2005-12-21 18:24 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-20 16:21 PostgreSQL 7.4.10 hanging on delete Jonathan Parkin <jonathan.parkin@legendplc.com>
2005-12-20 16:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-21 10:15   ` Jonathan Parkin <jonathan.parkin@legendplc.com>
2005-12-21 15:19     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-21 18:24       ` Jonathan Parkin <jonathan.parkin@legendplc.com>
2005-12-21 08:02 ` Pandurangan R S <pandurangan.r.s@gmail.com>
2005-12-21 10:10   ` Jonathan Parkin <jonathan.parkin@legendplc.com>
2005-12-21 09:54 ` Andrea <andrea@compesa.com.br>

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