pkg/28729: PostgreSQL consumes 100% CPU after brief heavy loading

[bsd%allegory.demon.co.uk@localhost]

>Number:         28729
>Category:       pkg
>Synopsis:       PostgreSQL consumes 100% CPU after brief heavy loading
>Confidential:   no
>Severity:       critical
>Priority:       high
>Responsible:    pkg-manager
>State:          open
>Class:          sw-bug
>Submitter-Id:   net
>Arrival-Date:   Tue Dec 21 01:30:01 +0000 2004
>Originator:     Bob Kemp
>Release:        NetBSD 2.99.10
>Organization:
        
>Environment:
        
        
System: NetBSD tenebres.loc 2.99.10 NetBSD 2.99.10 (TENEBRES) #0: Sun Nov 28 
01:08:29 GMT 2004 
rob%tenebres.loc@localhost:/usr/src/sys/arch/i386/compile/TENEBRES i386
Architecture: i386
Machine: i386
>Description:

    I supped on Sunday (Dec 19th) and rebuilt my postgresql
    installation.  The new installation loops indefinitely after
    receiving brief high load and consumes all available CPU.  It seems to
    be triggered by having several simultaneous requests.

    On versions of postgresql built from pkgsrc as at 2004-11-1 all is fine.
    On versions built from pkgsrc as at 2004-12-19 it loops.
    These both seem to be derived from the same version of postgresql however.

    All new connections block and waiting a few minutes doesn't help.
    The only way to stop postgresql is to kill the process.
    It doesn't respond to "/etc/rc.d/pgsql stop" and doesn't touch the disk.

    Neither rebuilding indexes nor restoring a trusted dump fix it.

    If you have problems reproducing it, let me know.  I need to
    be able to use the dbase most of the time but should be able
    to swap between the package versions.

    You're welcome to the dbase schema but the tables contain proprietary data.
    The schema has a lot of foreign keys but isn't otherwise particularly odd, 
IMHO.

    NOTE:   In email messages, please retain the subject line or reply from
            a netbsd.org address in order to avoid being entangled in my
            spam filters.

>How-To-Repeat:

    The command

        repeat 20 sh -c "MyCommand >/dev/null&"

    will do it consistently where MyCommand is a shell script that calls the
    PostgreSQL "psql" command to do an SQL "select" of a table.

    With the older package version, even "repeat 50" causes only a minor blip.
    With the current version, "repeat 20" is enough to (literally) put 
postgresql
    in a permanent spin.

>Fix:

    cd /usr/pkgsrc/databases
    cvs up -D 2004-11-1

    Not a very good long-term solution but it works for me :-)

>Unformatted: