Horton

Thread

  1. select * from ..;vacuum crashes

    Tatsuo Ishii <t-ishii@sra.co.jp> — 1998-10-06T02:50:56Z

    (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;

and have a crash too.

(using current source tree + FreeBSD)

I think this should be added to the Open 6.4 items list.

>Here is a report regarding the backend-crash from a user in Japan.
>Included shell script should reproduce the phenomenon.
>Note that
>	select * from getting; vacuum;
>does cause a crash, while
>	select * from getting;
>	vacuum;
>not.
>--
>Tatsuo Ishii
>t-ishii@sra.co.jp
>
>========================================================================
>#!/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
>

  2. Re: [HACKERS] select * from ..;vacuum crashes

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-10-06T04:06:45Z

    > (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

  3. RE: [HACKERS] select * from ..;vacuum crashes

    Taral <taral@mail.utexas.edu> — 1998-10-06T04:13:24Z

    > begin;
> select * from getting;
> vacuum;
> end;

Question: Does the following really make sense?

begin transaction;
select * from table;
vacuum;
abort transaction;

Taral

  4. Re: [HACKERS] select * from ..;vacuum crashes

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-10-06T04:35:31Z

    > >       select * from getting; vacuum;
> > does crash the backend with included test data.
> > This time I have tried:
> > begin;
> > select * from getting;
> > vacuum;
> > end;

Oleg, can you try compiling with asserts enabled and see if you can
learn anything new about your vacuum problems? It's probably not related
to this report, but you never know for sure. I can send you this
original message if you did not receive it...

                  - Tom

  5. Re: [HACKERS] select * from ..;vacuum crashes

    Nick Bastin <nbastin@rbbsystems.com> — 1998-10-06T04:37:54Z

    Taral wrote:
> 
> > begin;
> > select * from getting;
> > vacuum;
> > end;
> 
> Question: Does the following really make sense?

I'm glad somebody asked this, since I was beginning to wonder if I was missing
something.  My vote would  be to disable vacuum in a transaction, since it
doesn't make a whole lot of sense anyhow.

--
Nick Bastin
RBB Systems, Inc.

  6. Re: [HACKERS] select * from ..;vacuum crashes

    Vadim Mikheev <vadim@krs.ru> — 1998-10-06T06:51:27Z

    Taral wrote:
> 
> > begin;
> > select * from getting;
> > vacuum;
> > end;
> 
> Question: Does the following really make sense?
> 
> begin transaction;
> select * from table;
> vacuum;
> abort transaction;

Using vacuum inside BEGIN/END is Bad Idea!!!
MUST be disabled: vacuum uses MANY transaction but
CommitTransactionCommand() used by vacuum does nothing
inside BEGIN/END (only increments command counter).

Vadim