--eJnRUKwClWJh1Khz
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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 (AT) placenet (DOT) org> -----

Subject: Bug#390730: postgresql-8.1: segfaults on huge litteral IN clauses
Reply-To: Beno=EEt Dejean <benoit (AT) placenet (DOT) org>, 390730 (AT) bugs (DOT) debian.org
=46rom: Beno=EEt Dejean <benoit (AT) placenet (DOT) org>
To: Debian Bug Tracking System <submit (AT) bugs (DOT) 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?

--eJnRUKwClWJh1Khz
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)

iD8DBQFFJ8wgDecnbV4Fd/IRAmUlAJ9v4kTo76x5JkUlmcoOar9QYU0ZTwCg1S1T
xhaZTqqxBdVAP+HPfPtOGmU=
=0RXT
-----END PGP SIGNATURE-----

--eJnRUKwClWJh1Khz--

Martin Pitt <mpitt (AT) debian (DOT) org> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster