Re: [HACKERS] select * from ..;vacuum crashes - Mailing list pgsql-hackers

> (I have changed the subject "delete from" to "select * from" )
>
> As I reported,
>
>     select * from getting; vacuum;
>
> does crash the backend with included test data.
>
> This time I have tried:
>
> begin;
> select * from getting;
> vacuum;
> end;

I am attaching the original test script that will crash the backend.
The backtrace is:

    exceptionP=0x8152500, detail=0x0, fileName=0x8113761 "heapam.c",
    lineNumber=1055) at assert.c:74
#6  0x805a3ea in heap_fetch (relation=0x8187310, snapshot=0x0, tid=0x82f1128,
    userbuf=0x8045430) at heapam.c:1055
#7  0x8081986 in vc_updstats (relid=141974, num_pages=0, num_tuples=0,
    hasindex=1 '\001', vacrelstats=0x8186890) at vacuum.c:1767
#8  0x807ef8d in vc_vacone (relid=141974, analyze=0, va_cols=0x0)
    at vacuum.c:579
#9  0x807e6f1 in vc_vacuum (VacRelP=0x0, analyze=0 '\000', va_cols=0x0)
    at vacuum.c:257
#10 0x807e5ce in vacuum (vacrel=0x0, verbose=0, analyze=0 '\000', va_spec=0x0)
    at vacuum.c:160
#11 0x80e2d07 in ProcessUtility (parsetree=0x8185950, dest=Debug)
    at utility.c:644
#12 0x80e0745 in pg_exec_query_dest (query_string=0x80455f8 "vacuum;\n",
    dest=Debug, aclOverride=0) at postgres.c:758
#13 0x80e0664 in pg_exec_query (query_string=0x80455f8 "vacuum;\n")
    at postgres.c:699
#14 0x80e1708 in PostgresMain (argc=4, argv=0x8047644, real_argc=4,
    real_argv=0x8047644) at postgres.c:1622
#15 0x809ae39 in main (argc=4, argv=0x8047644) at main.c:103
#16 0x804a96c in __start ()

Something in the heap fetch it does not like.  I am kind of lost in this
part of the code.

The Assert line is:

    Assert(ItemIdIsUsed(lp));

which is checking for:

    (bool) (((itemId)->lp_flags & LP_USED) != 0)

which is saying the disk identifer should be in use, but is not during
the vacuum, for some reason.

You must enable Assert to see the crash.

The cause may be because you are doing a vacuum INSIDE a transaction.  I
think that also explains the psql -e thing, because that does both
commands in the same transaction.

Perhaps we need to disable vacuum inside transactions.  Vadim?

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

#!/bin/sh

DBNAME=ptest

destroydb $DBNAME
createdb $DBNAME
psql -e $DBNAME <<EOF
create table header
(
    host    text    not null,
    port    int    not null,
    path    text    not null,
    file    text    not null,
    extra    text    not null,
    name    text    not null,
    value    text    not null
);
create index header_url_idx on header (host, port, path, file, extra);
create unique index header_uniq_idx on header (host, port, path, file, extra, name);

create table reference
(
    f_url    text    not null,
    t_url    text    not null
);
create index reference_from_idx on reference (f_url);
create index reference_to_idx on reference (t_url);
create unique index reference_uniq_idx on reference (f_url, t_url);

create table extension
(
    ext    text    not null,
    note    text
);
create unique index extension_ext_idx on extension (ext);

create table getting
(
    host    text    not null,
    port    int    not null,
    ip    text    not null,
    when    datetime    not null
);
create unique index getting_ip_idx on getting (ip);
EOF
#psql -c "delete from getting; vacuum;" $DBNAME
psql -c "select * from getting; vacuum;" $DBNAME
#psql -c "delete from getting;" $DBNAME
#psql -c "select * from getting;" $DBNAME
#psql -c "vacuum;" $DBNAME
#psql -c "vacuum; vacuum;" $DBNAME