Bug in Function-Transactions?

Josh Berkus wrote:

Folks,

I just encountered a very interesting bug in ver 7.2.1.

As an experiment, I tried running a VACCUUM ANALYZE on a table inside a
Function after the function had made a large number of updates to that
table. I assumed that this wouldn't work, but I thought I'd give it a
try.

It did not work. What it did was interesting.

Postgres terminated my back-end connection to the server when it
reached the VACUUM statement.

Next, I reconnected. I was quite surprised to discover that Postgres
had *not* rolled back the changes made by the function before it
crashed.

I'm testing to see if I can reproduce this issue on 7.3b1. I'll
e-mail you with a test database if I can.

You will not be able to reproduce it. 7.3 disabled VACUUM inside
functions for that exact reason.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

"Josh Berkus" <josh@agliodbs.com> writes:

Postgres terminated my back-end connection to the server when it
reached the VACUUM statement.

Next, I reconnected. I was quite surprised to discover that Postgres
had *not* rolled back the changes made by the function before it
crashed.

Yeah.  The problem here is precisely that VACUUM does internal commits
--- so it committed your function's earlier changes too.  When you
returned from the VACUUM, the function's execution context was gone
as a byproduct of post-commit cleanup.  Oops.  VACUUM is disallowed
inside functions as of 7.3 to prevent this problem.

I don't think you need to be too worried about database corruption
as a result of this experiment, fortunately.

regards, tom lane