SEO

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 <benoit@placenet.org> -----

Subject: Bug#390730: postgresql-8.1: segfaults on huge litteral IN clauses
Reply-To: Benoît Dejean <benoit@placenet.org>, 390730@bugs.debian.org
From: Benoît 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=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?

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

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

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

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

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

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

iD8DBQBFJ+nEliyxJIUSPQoRAkFfAJ9r3wrpSVlM84Z+kFj9ze bBoCk+kACfbV+H
EON9Tcevpdy7MTm4gbg9gko=
=XNyX
-----END PGP SIGNATURE-----