Skip site navigation (1) Skip section navigation (2)

long sql "in" clause crashes server process (8.0 Release)

From: "Harald Massa" <ghum(at)gmx(dot)net>
To: pgsql-hackers-win32(at)postgresql(dot)org
Subject: long sql "in" clause crashes server process (8.0 Release)
Date: 2005-01-24 10:58:33
Message-ID: 30436.1106564313@www5.gmx.net (view raw or flat)
Thread:
Lists: pgsql-hackers-win32
On a table as

CREATE TABLE bcachekunde
(
  id_p int8 NOT NULL,
  name text,
  konzern text,
  sort int2,
  pname text,
  strasse text,
  plz text,
  ort text,
  betreuera text,
  jdstyp text,
  letztespeicherung timestamp,
  CONSTRAINT bcachekunde_pkey PRIMARY KEY (id_p)
) 

I issue the following SQL:

delete from bcachekunde where id_p in (1,2,3,4,6,10,12,13,14,16, [....10740
more ids...] 90041492, 90721705)

with the result:

Connection to the server gets lost.

In the server log:


2005-01-24 11:35:04 LOG:  server process (PID 3776) exited with unexpected
status 128
2005-01-24 11:35:04 LOG:  terminating any other active server processes
2005-01-24 11:35:05 WARNING:  terminating connection because of crash of
another server process
2005-01-24 11:35:05 DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2005-01-24 11:35:05 HINT:  In a moment you should be able to reconnect to
the database and repeat your command.
2005-01-24 11:35:05 LOG:  all server processes terminated; reinitializing
2005-01-24 11:35:05 LOG:  database system was interrupted at 2005-01-24
11:34:14 Westeuropäische Normalzeit
2005-01-24 11:35:05 LOG:  checkpoint record is at 0/CD25428
2005-01-24 11:35:05 LOG:  redo record is at 0/CD25428; undo record is at
0/0; shutdown TRUE
2005-01-24 11:35:05 LOG:  next transaction ID: 885303; next OID: 110250
2005-01-24 11:35:05 LOG:  database system was not properly shut down;
automatic recovery in progress
2005-01-24 11:35:05 LOG:  record with zero length at 0/CD25468
2005-01-24 11:35:05 LOG:  redo is not required
2005-01-24 11:35:05 LOG:  database system is ready
2005-01-24 11:36:51 LOG:  server process (PID 3916) exited with unexpected
status 128
2005-01-24 11:36:51 LOG:  terminating any other active server processes
2005-01-24 11:36:51 WARNING:  terminating connection because of crash of
another server process
2005-01-24 11:36:51 DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2005-01-24 11:36:51 HINT:  In a moment you should be able to reconnect to
the database and repeat your command.
2005-01-24 11:36:51 LOG:  all server processes terminated; reinitializing
2005-01-24 11:36:51 LOG:  database system was interrupted at 2005-01-24
11:35:05 Westeuropäische Normalzeit
2005-01-24 11:36:51 LOG:  checkpoint record is at 0/CD25468
2005-01-24 11:36:51 LOG:  redo record is at 0/CD25468; undo record is at
0/0; shutdown TRUE
2005-01-24 11:36:51 LOG:  next transaction ID: 885303; next OID: 110250
2005-01-24 11:36:51 LOG:  database system was not properly shut down;
automatic recovery in progress
2005-01-24 11:36:51 LOG:  record with zero length at 0/CD254A8
2005-01-24 11:36:51 LOG:  redo is not required
2005-01-24 11:36:51 LOG:  database system is ready


Yeah, I know, it is not a very orthodox SQL. 
Yes, I can split up that ids to make it work.

BUT... it is not nice that the process crashes. And it is rather worse, that
all the camarad processes are terminated. In German we call that
"sippenhaft" :))) [terminating any other active server processes]
(I know, Sun Tzu recommends killing all generals when some soldiers do not
operate properly... but hey, that is THOUSENDS of years ago :) )

There should be some try ... except be around or other precautions.
(especially since SQL often gets created automatically)

Harald

Responses

pgsql-hackers-win32 by date

Next:From: Harald MassaDate: 2005-01-24 11:02:10
Subject: Re: pg_autovacuum does not start on system startup - 8.0 Release -
Previous:From: Dave PageDate: 2005-01-24 10:56:27
Subject: Re: pg_autovacuum does not start on system startup - 8.0 Release -

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group