delete from ..;vacuum crashes
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
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
Here is a report regarding the backend-crash from a user in Japan.
Included shell script should reproduce the phenomenon.
On which postgres version(s)?
Note that
select * from getting; vacuum;
does cause a crash, while
select * from getting;
vacuum;
not.
Specifically I see you are using
psql -c "select * from getting; vacuum;" $DBNAME
rather than entering the commands at the psql prompt. The -c option
works differently from entering multiple commands at psql's prompt.
In ordinary interactive use, psql will break what you type at
semicolon boundaries and send each SQL command to the backend
separately, even if you typed several commands on one line.
*But* the -c option doesn't work that way --- it just sends the
whole given string to the backend as one query.
The implication of this is that psql -c "select * from getting; vacuum;"
executes the select and the vacuum as part of a single transaction,
whereas any other way of doing it with psql will make the commands be
two separate transactions. I speculate that this has something to do
with the different behavior you see.
Exactly what the bug is is beyond my abilities, but perhaps that tidbit
will help someone more competent to find it.
regards, tom lane
Import Notes
Reply to msg id not found: YourmessageofFri02Oct1998124454+0900199810020344.MAA17792@srapc451.sra.co.jp | Resolved by subject fallback
At 2:14 AM 98.10.2 -0400, Tom Lane wrote:
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
Here is a report regarding the backend-crash from a user in Japan.
Included shell script should reproduce the phenomenon.On which postgres version(s)?
The latest cvs source tree (I have not tried this for 6.3.2).
The implication of this is that psql -c "select * from getting; vacuum;"
executes the select and the vacuum as part of a single transaction,
whereas any other way of doing it with psql will make the commands be
two separate transactions. I speculate that this has something to do
with the different behavior you see.
So basically the backend treats multiple SQL statements conjucted with ";"
as a single transaction?
If this is the cause of the problem, following SQLs should produce
the backend death too. I will try this.
begin;
select * from getting;
vacuum;
end;
--
Tatsuo Ishii
t-ishii@sra.co.jp
t-ishii@sra.co.jp (Tatsuo Ishii) writes:
So basically the backend treats multiple SQL statements conjucted with ";"
as a single transaction?
If they arrive in a single query string, as you can cause with psql -c
or with a direct PQexec() call.
Actually it's even more subtle than that: multiple statements in a
single query string act like a *single statement* as far as the
transaction mechanism is concerned. There's one StartTransactionCommand
in postgres.c before the string starts to execute, and one
CommitTransactionCommand after it's done.
It is possible that that is a bug, and that we need to take the start/
commit calls out of the postgres.c main loop and put them somewhere
down inside the parsing/execution code, at a point where the software
has parsed off a single SQL statement. As it stands, if there are any
statements in the system that assume there is a StartTransactionCommand
just before they begin and a CommitTransactionCommand just after they
finish, those statements will break when executed as part of a
multi-statement query. Question for the gurus: would you consider this
a bug in the particular statement (it shouldn't assume that), or a bug
in the outer layers (they should make that be true)?
In particular, since vacuum.c does some peculiar things with transaction
boundaries, it seems to me that it might be an example of such a
statement and that what I just described is the root cause of your bug.
But someone who knows the system better than me will have to figure out
just what's going on.
If this is the cause of the problem, following SQLs should produce
the backend death too. I will try this.
begin;
select * from getting;
vacuum;
end;
You should try it and let us know. But that is a different test case,
because there will be CommitTransactionCommand & StartTransactionCommand
between the select and the vacuum.
regards, tom lane
Import Notes
Reply to msg id not found: YourmessageofFri2Oct1998231610+0900199810021417.XAA10638@meshsv26.tk.mesh.ad.jp | Resolved by subject fallback