Fwd: Bug#390730: postgresql-8.1: segfaults on huge litteral IN clauses - Mailing list pgsql-bugs
From | Martin Pitt |
---|---|
Subject | Fwd: Bug#390730: postgresql-8.1: segfaults on huge litteral IN clauses |
Date | |
Msg-id | 20061007154744.GB4819@piware.de Whole thread Raw |
Responses |
Re: Fwd: Bug#390730: postgresql-8.1: segfaults on huge litteral IN clauses
|
List | pgsql-bugs |
Hi PostgreSQL developers, I recently got the Debian bug report below, a server crash with huge IN clauses. Beno=EEt 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`)" | psq= l 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 t= est 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=EEt Dejean <benoit@placenet.org> ----- Subject: Bug#390730: postgresql-8.1: segfaults on huge litteral IN clauses Reply-To: Beno=EEt Dejean <benoit@placenet.org>, 390730@bugs.debian.org From: Beno=EEt Dejean <benoit@placenet.org> To: Debian Bug Tracking System <submit@bugs.debian.org> Date: Mon, 02 Oct 2006 21:11:52 +0200 X-Spam-Status: No, score=3D0.0 required=3D4.0 tests=3DBAYES_50 autolearn=3D= no=20 version=3D3.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=E9passement de limite (en profondeur) de la pile HINT: Augmenter le param=E8tre =ABmax_stack_depth=BB.) so i increased max_stack_depth to 8192. That the only change is did to debi= an standard configuration. then : LC_ALL=3DC 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 =E9t=E9 arr=EAt=E9 par le signal 11OB LOG: Arr=EAt des autres processus serveur actifs LOG: Tous les processus serveur se sont arr=EAt=E9s, r=E9initialisation LOG: le syst=E8me de bases de donn=E9es a =E9t=E9 interrompu =E0 2006-10-0= 2 20:36:59 CEST LOG: l'enregistrement du point de v=E9rification est =E0 0/7A3BCB8 LOG: r=E9-ex=E9cution de l'enregistrement =E0 0/7A3BCB8 ; l'annulation de = l'enregistrement est =E0 0/0 ; arr=EAt TRUE LOG: prochain identifiant de transaction : 852 ; prochain OID : 16459 LOG: prochain MultiXactId: 1; prochain MultiXactOffset: 0 LOG: le syst=E8me de bases de donn=E9es n'a pas =E9t=E9 arr=EAt=E9 proprem= ent ; restauration automatique en cours LOG: enregistrement de longueur nulle sur 0/7A3BD00 LOG: la r=E9-ex=E9cution n'est pas requise LOG: le syst=E8me de bases de donn=E9es est pr=EAt LOG: La limite de r=E9initialisation de l'ID de transaction est 2147484146= , limit=E9 par la base de donn=E9es =ABpostgres=BB 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 t= o perform a seq scan ?=20 benoit=3D> EXPLAIN ANALYZE SELECT * FROM foo WHERE id in (1, 2, 3); QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------- Bitmap Heap Scan on foo (cost=3D6.01..17.57 rows=3D3 width=3D8) (actual t= ime=3D31.979..31.990 rows=3D3 loops=3D1) Recheck Cond: ((id =3D 1) OR (id =3D 2) OR (id =3D 3)) -> BitmapOr (cost=3D6.01..6.01 rows=3D3 width=3D0) (actual time=3D23.4= 62..23.462 rows=3D0 loops=3D1) -> Bitmap Index Scan on foo_pkey (cost=3D0.00..2.00 rows=3D1 wid= th=3D0) (actual time=3D23.367..23.367 rows=3D1 loops=3D1) Index Cond: (id =3D 1) -> Bitmap Index Scan on foo_pkey (cost=3D0.00..2.00 rows=3D1 wid= th=3D0) (actual time=3D0.037..0.037 rows=3D1 loops=3D1) Index Cond: (id =3D 2) -> Bitmap Index Scan on foo_pkey (cost=3D0.00..2.00 rows=3D1 wid= th=3D0) (actual time=3D0.024..0.024 rows=3D1 loops=3D1) Index Cond: (id =3D 3) Total runtime: 32.441 ms benoit=3D> EXPLAIN ANALYZE SELECT * FROM foo WHERE id >=3D 1 and id <=3D 3; QUERY PLAN ---------------------------------------------------------------------------= ----------------------------------- Index Scan using foo_pkey on foo (cost=3D0.00..3.02 rows=3D1 width=3D8) (= actual time=3D0.054..0.074 rows=3D3 loops=3D1) Index Cond: ((id >=3D 1) AND (id <=3D 3)) Total runtime: 0.232 ms So it would be very cool if postgres was able to aggregate litterals IN cla= uses. -- 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=3Dfr_FR.UTF-8, LC_CTYPE=3Dfr_FR.UTF-8 (charmap=3DUTF-8) (ignor= ed: 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 libra= ry ii libkrb53 1.4.4-3 MIT Kerberos runtime libraries ii libpam0g 0.79-3.2 Pluggable Authentication Modul= es 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 Postgre= SQL=20 ii postgresql-common 63 manager for PostgreSQL databas= e cl postgresql-8.1 recommends no packages. -- debconf-show failed ----- End forwarded message ----- --=20 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?
pgsql-bugs by date: