Re: [HACKERS] delete from ..;vacuum crashes

t-ishii(at)sra(dot)co(dot)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