Re: Odd new symptom - database locking up on a query

Additional information from the STATS being turned on:

(The static IN clause has between 250 and 350 IDs in it, each time. I don't
know which DELETE hangs first, but they all cascade into being hung, as
this shows.)

Thoughts?

Thanks,

Doug

datid | datname | procpid | usesysid
| usename |
current_query

---------+-------------+---------+----------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1576030 | bfkdev | 274 | 102 | bknowlton | <IDLE>
3926366 | pexicast_lg | 275 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(242101,242121,242141,242161,242181,242201,242221,242241,242261,242281,242301,242321,242341,242361,242381,242401,242421,242441,242461,242481,242501,242521,242541,242561,242581,242601,2
3926366 | pexicast_lg | 276 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(237123,237143,237163,237183,237203,237223,237243,237263,237283,237303,237323,237343,237363,237383,237403,237423,237443,237463,237483,237503,237523,237543,237563,237583,237603,237623,2
3926366 | pexicast_lg | 277 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(239084,239104,239124,239144,239164,239184,239204,239224,239244,239264,239284,239304,239324,239344,239364,239384,239404,239424,239444,239464,239484,239504,239524,239544,239564,239584,2
3926366 | pexicast_lg | 278 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(238525,238545,238565,238585,238605,238625,238645,238665,238685,238705,238725,238745,238765,238785,238805,238825,238845,238865,238885,238905,238925,238945,238965,238985,239005,239025,2
3926366 | pexicast_lg | 279 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(246006,246026,246046,246066,246086,246106,246126,246146,246166,246186,246206,246226,246246,246266,246286,246306,246326,246346,246366,246386,246406,246426,246446,246466,246486,246506,2
3926366 | pexicast_lg | 280 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(234787,234807,234827,234847,234867,234887,234907,234927,234947,234967,234987,235007,235027,235047,235067,235087,235107,235127,235147,235167,235187,235207,235227,235247,235267,235287,2
3926366 | pexicast_lg | 281 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(230268,230288,230308,230328,230348,230368,230388,230408,230428,230448,230468,230488,230508,230528,230548,230568,230588,230608,230628,230648,230668,230688,230708,230728,230748,230768,2
3926366 | pexicast_lg | 282 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(230249,230269,230289,230309,230329,230349,230369,230389,230409,230429,230449,230469,230489,230509,230529,230549,230569,230589,230609,230629,230649,230669,230689,230709,230729,230749,2
3926366 | pexicast_lg | 283 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(224030,224050,224070,224090,224110,224130,224150,224170,224190,224210,224230,224250,224270,224290,224310,224330,224350,224370,224390,224410,224430,224450,224470,224490,224510,224530,2
3926366 | pexicast_lg | 284 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(237971,237991,238011,238031,238051,238071,238091,238111,238131,238151,238171,238191,238211,238231,238251,238271,238291,238311,238331,238351,238371,238391,238411,238431,238451,238471,2
3926366 | pexicast_lg | 285 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(231832,231852,231872,231892,231912,231932,231952,231972,231992,232012,232032,232052,232072,232092,232112,232132,232152,232172,232192,232212,232232,232252,232272,232292,232312,232332,2
3926366 | pexicast_lg | 286 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(231953,231973,231993,232013,232033,232053,232073,232093,232113,232133,232153,232173,232193,232213,232233,232253,232273,232293,232313,232333,232353,232373,232393,232413,232433,232453,2
3926366 | pexicast_lg | 287 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(225634,225654,225674,225694,225714,225734,225754,225774,225794,225814,225834,225854,225874,225894,225914,225934,225954,225974,225994,226014,226034,226054,226074,226094,226114,226134,2
3926366 | pexicast_lg | 288 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(232755,232775,232795,232815,232835,232855,232875,232895,232915,232935,232955,232975,232995,233015,233035,233055,233075,233095,233115,233135,233155,233175,233195,233215,233235,233255,2
3926366 | pexicast_lg | 289 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(220436,220456,220476,220496,220516,220536,220556,220576,220596,220616,220636,220656,220676,220696,220716,220736,220756,220776,220796,220816,220836,220856,220876,220896,220916,220936,2
3926366 | pexicast_lg | 290 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(233837,233857,233877,233897,233917,233937,233957,233977,233997,234017,234037,234057,234077,234097,234117,234137,234157,234177,234197,234217,234237,234257,234277,234297,234317,234337,2
3926366 | pexicast_lg | 291 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(234178,234198,234218,234238,234258,234278,234298,234318,234338,234358,234378,234398,234418,234438,234458,234478,234498,234518,234538,234558,234578,234598,234618,234638,234658,234678,2
1576030 | bfkdev | 292 | 102 | bknowlton | <IDLE>
1576030 | bfkdev | 293 | 102 | bknowlton | <IDLE>
1576030 | bfkdev | 294 | 102 | bknowlton | <IDLE>
3926366 | pexicast_lg | 295 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(220719,220739,220759,220779,220799,220819,220839,220859,220879,220899,220919,220939,220959,220979,220999,221019,221039,221059,221079,221099,221119,221139,221159,221179,221199,221219,2
3926366 | pexicast_lg | 300 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(225020,225040,225060,225080,225100,225120,225140,225160,225180,225200,225220,225240,225260,225280,225300,225320,225340,225360,225380,225400,225420,225440,225460,225480,225500,225520,2
3926366 | pexicast_lg | 301 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(242101,242121,242141,242161,242181,242201,242221,242241,242261,242281,242301,242321,242341,242361,242381,242401,242421,242441,242461,242481,242501,242521,242541,242561,242581,242601,2
3926366 | pexicast_lg | 302 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(220822,220842,220862,220882,220902,220922,220942,220962,220982,221002,221022,221042,221062,221082,221102,221122,221142,221162,221182,221202,221222,221242,221262,221282,221302,221322,2
3926366 | pexicast_lg | 303 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(220719,220739,220759,220779,220799,220819,220839,220859,220879,220899,220919,220939,220959,220979,220999,221019,221039,221059,221079,221099,221119,221139,221159,221179,221199,221219,2
3926366 | pexicast_lg | 304 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(220436,220456,220476,220496,220516,220536,220556,220576,220596,220616,220636,220656,220676,220696,220716,220736,220756,220776,220796,220816,220836,220856,220876,220896,220916,220936,2
3926366 | pexicast_lg | 305 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(237123,237143,237163,237183,237203,237223,237243,237263,237283,237303,237323,237343,237363,237383,237403,237423,237443,237463,237483,237503,237523,237543,237563,237583,237603,237623,2
3926366 | pexicast_lg | 306 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(233837,233857,233877,233897,233917,233937,233957,233977,233997,234017,234037,234057,234077,234097,234117,234137,234157,234177,234197,234217,234237,234257,234277,234297,234317,234337,2
3926366 | pexicast_lg | 307 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(232755,232775,232795,232815,232835,232855,232875,232895,232915,232935,232955,232975,232995,233015,233035,233055,233075,233095,233115,233135,233155,233175,233195,233215,233235,233255,2
3926366 | pexicast_lg | 308 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(231953,231973,231993,232013,232033,232053,232073,232093,232113,232133,232153,232173,232193,232213,232233,232253,232273,232293,232313,232333,232353,232373,232393,232413,232433,232453,2
3926366 | pexicast_lg | 309 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(231832,231852,231872,231892,231912,231932,231952,231972,231992,232012,232032,232052,232072,232092,232112,232132,232152,232172,232192,232212,232232,232252,232272,232292,232312,232332,2
3926366 | pexicast_lg | 310 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(224030,224050,224070,224090,224110,224130,224150,224170,224190,224210,224230,224250,224270,224290,224310,224330,224350,224370,224390,224410,224430,224450,224470,224490,224510,224530,2
3926366 | pexicast_lg | 311 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(230249,230269,230289,230309,230329,230349,230369,230389,230409,230429,230449,230469,230489,230509,230529,230549,230569,230589,230609,230629,230649,230669,230689,230709,230729,230749,2
3926366 | pexicast_lg | 312 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(230268,230288,230308,230328,230348,230368,230388,230408,230428,230448,230468,230488,230508,230528,230548,230568,230588,230608,230628,230648,230668,230688,230708,230728,230748,230768,2
3926366 | pexicast_lg | 313 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(234787,234807,234827,234847,234867,234887,234907,234927,234947,234967,234987,235007,235027,235047,235067,235087,235107,235127,235147,235167,235187,235207,235227,235247,235267,235287,2
3926366 | pexicast_lg | 314 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(246006,246026,246046,246066,246086,246106,246126,246146,246166,246186,246206,246226,246246,246266,246286,246306,246326,246346,246366,246386,246406,246426,246446,246466,246486,246506,2
3926366 | pexicast_lg | 315 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(238525,238545,238565,238585,238605,238625,238645,238665,238685,238705,238725,238745,238765,238785,238805,238825,238845,238865,238885,238905,238925,238945,238965,238985,239005,239025,2
3926366 | pexicast_lg | 316 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(239084,239104,239124,239144,239164,239184,239204,239224,239244,239264,239284,239304,239324,239344,239364,239384,239404,239424,239444,239464,239484,239504,239524,239544,239564,239584,2
3926366 | pexicast_lg | 317 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(225634,225654,225674,225694,225714,225734,225754,225774,225794,225814,225834,225854,225874,225894,225914,225934,225954,225974,225994,226014,226034,226054,226074,226094,226114,226134,2
3926366 | pexicast_lg | 318 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(234178,234198,234218,234238,234258,234278,234298,234318,234338,234358,234378,234398,234418,234438,234458,234478,234498,234518,234538,234558,234578,234598,234618,234638,234658,234678,2
3926366 | pexicast_lg | 319 | 100 | tomcat | DELETE FROM
table_entries WHERE job_id=44 AND list_entry_id IN
(237971,237991,238011,238031,238051,238071,238091,238111,238131,238151,238171,238191,238211,238231,238251,238271,238291,238311,238331,238351,238371,238391,238411,238431,238451,238471,2
3926366 | pexicast_lg | 320 | 100 | tomcat | <IDLE>
3926366 | pexicast_lg | 321 | 100 | tomcat | <IDLE>
3926366 | pexicast_lg | 327 | 101 | dfields | <IDLE>
(45 rows)

At 10:34 PM 7/6/2002, Doug Fields wrote:
>Hello,
>
>I've encountered an odd new symptom which has me absolutely flabbergasted.
>
>I'm running about 20-25 parallel connections to my Debian/Woody PostgreSQL
>7.2.1 server (Dual P4X 2.4, RAID-1 log partition, RAID-5 data partition,
>8gb RAM, 64meg sort space, 256meg shared memory segment).
>
>At a certain point, a query hangs, then another, then another, until all
>my connections are blocked. My application completely comes to a halt.
>
>I've never seen this before, and don't know where to begin trying to solve
>it. Certainly nothing should cause these problems: the three queries being
>heavily used are simple:
>
>1) A single one-to-one Joined SELECT
>2) A simple DELETE with a static IN clause to delete a whole bunch of records
>3) A simple UPDATE with a similar static IN clause to update one field (to
>now()) of a whole bunch of primary keys
>
>Nothing appears in the logs. In fact, nothing seems amiss anywhere.
>
>So, I'm stumped. I'm going to turn on some of the statistics collection
>stuff (new in 7.2) but really don't know what to do.
>
>I welcome ideas. Some "top" and "ps" stuff is below.
>
>Cheers,
>
>Doug