Fwd: Bug#390730: postgresql-8.1: segfaults on huge litteral IN clauses

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?