Обсуждение: 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?

Martin Pitt <mpitt@debian.org> writes:
> I recently got the Debian bug report below, a server crash with huge
> IN clauses.

What this sounds like to me is someone increasing max_stack_depth
without understanding what he's doing.  You must make sure that
max_stack_depth is less than the "ulimit -s" setting the postmaster
is running under ... and not just a little bit less, you want a megabyte
or so of daylight in there.  Per the documentation:

max_stack_depth (integer)

      Specifies the maximum safe depth of the server's execution
      stack. The ideal setting for this parameter is the actual stack
      size limit enforced by the kernel (as set by ulimit -s or local
      equivalent), less a safety margin of a megabyte or so. The safety
      margin is needed because the stack depth is not checked in every
      routine in the server, but only in key potentially-recursive
      routines such as expression evaluation. Setting the parameter
      higher than the actual kernel limit will mean that a runaway
      recursive function can crash an individual backend process. The
      default setting is 2048 KB (two megabytes), which is
      conservatively small and unlikely to risk crashes. However, it may
      be too small to allow execution of complex functions.

So if the OP wants to use huge IN clauses, he has to both increase
max_stack_depth and adjust the ulimit that the postmaster runs under.

            regards, tom lane

Le samedi 07 octobre 2006 à 12:23 -0400, Tom Lane a écrit :
> Martin Pitt <mpitt@debian.org> writes:
> > I recently got the Debian bug report below, a server crash with huge
> > IN clauses.
>
> What this sounds like to me is someone increasing max_stack_depth
> without understanding what he's doing.  You must make sure that
> max_stack_depth is less than the "ulimit -s" setting the postmaster
> is running under ... and not just a little bit less, you want a megabyte
> or so of daylight in there.

i used ulimit -s 8192 from the start ... and that's my mistake. So
you're right, i've misread the documentation, especially that part :

>  Per the documentation:
>
> max_stack_depth (integer)
>
> less a safety margin of a megabyte or so.

may be this point should be emphazised in the documentation. Is 1MB of
margin really enough ? the doc should be clearer.

> So if the OP wants to use huge IN clauses, he has to both increase
> max_stack_depth and adjust the ulimit that the postmaster runs under.

In order to run my application, i had to raise postgres stack size up to
20MB. I feel like hammering the stack that hard is quite hazardous.

But after all, everything works fine :) Thank you for you help.
--
GNOME http://www.gnomefr.org/
LibGTop http://directory.fsf.org/libgtop.html