Package: postgresql-8.1
Version: 8.1.4-7
Severity: normal
Hi, when doing HUGE IN, i get a segfault on the server. By HUGE i mean
100k. At work, we have developped a (poor) db mapping which makes
intensive use of litteral IN. Like
select Foo from Bar where id in (1, 5, 3, 8);
where the in clause is too big, the server segfaults. Here's a simple
testcase :
CREATE TABLE foo (id SERIAL PRIMARY KEY, v INTEGER);
CREATE FUNCTION fill_foo(INTEGER) RETURNS INTEGER AS $$
DECLARE
n ALIAS FOR $1;
BEGIN
FOR i in 1..n LOOP
INSERT INTO foo (v) values(i);
END LOOP;
RETURN COUNT(*) FROM foo;
END;
$$ LANGUAGE plpgsql;
select * from fill_foo(1000000);
then i generated a huge (100k elements) and shuffled list of ids :
select * from foo
where id in (134391, 680297, 90974, 305561, 319569, 411883, ...
On first run, i got an error :
psql:./foo:1: ERREUR: dépassement de limite (en profondeur) de la pile
HINT: Augmenter le paramètre «max_stack_depth».)
so i increased max_stack_depth to 8192. That the only change is did to debian
standard configuration.
then :
LC_ALL=C psql -U benoit benoit -f ./foo
psql:./foo:1: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:./foo:1: connection to server was lost
and the server log is :
LOG: processus serveur (PID 19640) a été arrêté par le signal 11OB
LOG: Arrêt des autres processus serveur actifs
LOG: Tous les processus serveur se sont arrêtés, réinitialisation
LOG: le système de bases de données a été interrompu à 2006-10-02 20:36:59 CEST
LOG: l'enregistrement du point de vérification est à 0/7A3BCB8
LOG: ré-exécution de l'enregistrement à 0/7A3BCB8 ; l'annulation de
l'enregistrement est à 0/0 ; arrêt TRUE
LOG: prochain identifiant de transaction : 852 ; prochain OID : 16459
LOG: prochain MultiXactId: 1; prochain MultiXactOffset: 0
LOG: le système de bases de données n'a pas été arrêté proprement ;
restauration automatique en cours
LOG: enregistrement de longueur nulle sur 0/7A3BD00
LOG: la ré-exécution n'est pas requise
LOG: le système de bases de données est prêt
LOG: La limite de réinitialisation de l'ID de transaction est 2147484146,
limité par la base de données «postgres»
sorry, it's in french but the first line means that serveur
process got killed by signal 11. Only a child dies, the server remains
functionnal.
I can reproduce it on this sid/ppc and i386/etch.
I know this kind of huge IN clause is a bit silly but silly SQL shouldnot
crash my dear postgres :)
Thanks;
Offtopic : i understand that postgres does N bitmap scans when running a IN(1,
4, 3)
clause. This is painfully slow. Why don't postgres optimize this in order to
perform
a seq scan ?
benoit=> EXPLAIN ANALYZE SELECT * FROM foo WHERE id in (1, 2, 3);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=6.01..17.57 rows=3 width=8) (actual
time=31.979..31.990 rows=3 loops=1)
Recheck Cond: ((id = 1) OR (id = 2) OR (id = 3))
-> BitmapOr (cost=6.01..6.01 rows=3 width=0) (actual time=23.462..23.462
rows=0 loops=1)
-> Bitmap Index Scan on foo_pkey (cost=0.00..2.00 rows=1 width=0)
(actual time=23.367..23.367 rows=1 loops=1)
Index Cond: (id = 1)
-> Bitmap Index Scan on foo_pkey (cost=0.00..2.00 rows=1 width=0)
(actual time=0.037..0.037 rows=1 loops=1)
Index Cond: (id = 2)
-> Bitmap Index Scan on foo_pkey (cost=0.00..2.00 rows=1 width=0)
(actual time=0.024..0.024 rows=1 loops=1)
Index Cond: (id = 3)
Total runtime: 32.441 ms
benoit=> EXPLAIN ANALYZE SELECT * FROM foo WHERE id >= 1 and id <= 3;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Index Scan using foo_pkey on foo (cost=0.00..3.02 rows=1 width=8) (actual
time=0.054..0.074 rows=3 loops=1)
Index Cond: ((id >= 1) AND (id <= 3))
Total runtime: 0.232 ms
So it would be very cool if postgres was able to aggregate litterals IN clauses.
-- System Information:
Debian Release: testing/unstable
APT prefers unstable
APT policy: (500, 'unstable'), (1, 'experimental')
Architecture: powerpc (ppc)
Shell: /bin/sh linked to /bin/bash
Kernel: Linux 2.6.18-ben
Locale: LANG=fr_FR.UTF-8, LC_CTYPE=fr_FR.UTF-8 (charmap=UTF-8) (ignored: LC_ALL
set to fr_FR.UTF-8)
Versions of packages postgresql-8.1 depends on:
ii libc6 2.3.6.ds1-4 GNU C Library: Shared libraries
ii libcomerr2 1.39-1.1 common error description library
ii libkrb53 1.4.4-3 MIT Kerberos runtime libraries
ii libpam0g 0.79-3.2 Pluggable Authentication Modules l
ii libpq4 8.1.4-7 PostgreSQL C client library
ii libssl0.9.8 0.9.8c-2 SSL shared libraries
ii postgresql-client-8.1 8.1.4-7 front-end programs for PostgreSQL
ii postgresql-common 63 manager for PostgreSQL database cl
postgresql-8.1 recommends no packages.
-- debconf-show failed
