Re: Vacuum Question - Mailing list pgsql-general

Ed Loehr wrote:
>
> On my way to some performance testing I ran into this repeatable crash
> scenario.  Just wondering if anyone else can reproduce the same or
> explain what is happening.  Could very well be something peculiar/buggy
> about the bash/perl commands...
>
> First, start up this rather obfuscated perl/bash command which just
> inserts random records forever...
>
> %  createdb pdb; psql -d pdb -c "create table foo(id serial, t timestamp
> not null default current_timestamp, f float, s varchar, i integer, l
> int8);"; perl -e 'for (my $j = 0; $j < 10000000; $j++ ) { print "--
> Insert #$j\n"; $f = rand(2000000); $i = int(rand(2000000)) % 16000; $l =
> int(rand(2000000000)); $s = chr(65 + int(rand(20))) x int(rand(128));
> $delim = chr(39); print "insert into foo (f,s,i,l) values ($f,
> $delim${s}$delim, $i, $l);\n"; }' | psql -d pdb
>
> Then, start this one in another bash window/terminal/whatever...
>
> % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze;
> select count(*) from foo;"; sleep 3; done
>
> This seems to consistently crash after the first vacuum ...

Oh, and the possibly relevant snippet from the server log:

20000606.01:13:27.116  [8659] query: insert into foo (f,s,i,l) values
(941636.60146296, 'HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH',
7223, 754988800);
20000606.01:13:27.164  [8679] DEBUG:  --Relation pg_aggregate--
20000606.01:13:27.164  [8679] DEBUG:  Pages 1: Changed 0, reaped 0, Empty
0, New 0; Tup 45: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 100,
MaxLen 107;
 Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
20000606.01:13:27.165  [8679] DEBUG:  Index pg_aggregate_name_type_index:
Pages 2; Tuples 45. CPU 0.00s/0.00u sec.
20000606.01:13:27.172  [8679] DEBUG:  --Relation pg_ipl--
20000606.01:13:27.172  [8679] DEBUG:  Pages 0: Changed 0, reaped 0, Empty
0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen
0; Re-u
sing: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u
sec.
20000606.01:13:27.175  [8679] DEBUG:  --Relation pg_inheritproc--
20000606.01:13:27.176  [8679] DEBUG:  Pages 0: Changed 0, reaped 0, Empty
0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen
0; Re-u
sing: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u
sec.
20000606.01:13:27.180  [8679] DEBUG:  --Relation pg_rewrite--
20000606.01:13:27.184  [8679] DEBUG:  Pages 1: Changed 0, reaped 0, Empty
0, New 0; Tup 5: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 903,
MaxLen 1236;
 Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
20000606.01:13:27.186  [8679] DEBUG:  Index pg_rewrite_rulename_index:
Pages 2; Tuples 5. CPU 0.00s/0.00u sec.
20000606.01:13:27.187  [8679] DEBUG:  Index pg_rewrite_oid_index: Pages
2; Tuples 5. CPU 0.00s/0.00u sec.
20000606.01:13:27.188  [8659] ERROR:  Function
'<FF><FF><FF><FF>^Ap(int4)' does not exist
        Unable to identify a function that satisfies the given argument
types
        You may need to add explicit typecasts

One day maybe I'll learn to include all the relevant stuff in one
post...nah.

Regards,
Ed Loehr