Hi PostgreSQL developers, I recently got the Debian bug report below, a server crash with huge IN clauses.
Benoît did not give a concrete example of how to actually cause the
crash, just the recipe. I was able to reproduce the crash with
echo "select count(*) from foo where id in (`seq -s ',' 1 100000`)" | psql
test
with 8.1.4. I also tried it with 8.2 beta 1, which worked just fine (I
did not even need to tweak the max stack size). It also works fine
with 8.2b1 with s/count(*)/*/.
To be absolutely sure I wrote a small perl script which produces a
(bad) random permutation and tested again:
echo "select count(*) from foo where id in (`./perm.pl 100000`)" | psql test
still works with 8.2.
So, it's not the worst bug in the world and seemingly fixed in 8.2,
but depending on whether a local authenticated crash is considered a
security issue, it might be worth fixing in 8.1 (even if it's just a
small test for an upper bound for IN clauses?)
Thank you in advance,
Martin
----- Forwarded message from Benoît Dejean <[EMAIL PROTECTED]> -----
Subject: Bug#390730: postgresql-8.1: segfaults on huge litteral IN clauses
Reply-To: Benoît Dejean <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
From: Benoît Dejean <[EMAIL PROTECTED]>
To: Debian Bug Tracking System <[EMAIL PROTECTED]>
Date: Mon, 02 Oct 2006 21:11:52 +0200
X-Spam-Status: No, score=0.0 required=4.0 tests=BAYES_50 autolearn=no
version=3.0.3
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
----- End forwarded message -----
--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
In a world without walls and fences, who needs Windows and Gates?
signature.asc
Description: Digital signature
