BUG #1331: after update: vacuum blocks parallel to select

The following bug has been logged online:

Bug reference: 1331
Logged by: Michael Enke

Email address: michael.enke@wincor-nixdorf.com

PostgreSQL version: 7.4.5

Operating system: Linux SuSE 9.1 Kernel 2.6/RedHat 9 Kernel 2.4

Description: after update: vacuum blocks parallel to select

Details:

Hi group,
I found that vacuum blocks until a select cursor is closed but only if the
table was updated before.
After select/TA finished, vacuum goes through.
A second vacuum during parallel select doesn't block
until the next update is done.

Example code: create table, insert and update:
===============vac_blk.sql start=====================
drop table vac_blk;
create table vac_blk (
a int2
);
insert into vac_blk values (1);
update vac_blk set a=1 where a=1;

\echo now call vac_blk binary;
\echo if running, call vacuum vac_blk: will hang
==============vac_blk.sql stop========================

Example code: Do the select inside a TA:
==============vac_blk.pgc start=======================
exec sql include sqlca;

int main(int argc, char **argv) {

exec sql begin declare section;
int a;
char sqlstring[] = "select a from vac_blk";
exec sql end declare section;

exec sql connect to mydb@localhost:5432
user myuser identified by mypassword;

exec sql PREPARE select_vac_blk FROM :sqlstring;
exec sql DECLARE select_cur_vac_blk CURSOR FOR select_vac_blk;
exec sql OPEN select_cur_vac_blk;

exec sql FETCH select_cur_vac_blk INTO :a;
printf("have read a=%i, will now sleep 60s, go vacuum now!\n", a);
sleep(60);

exec sql disconnect all;
return 0;
}
==============vac_blk.pgc stop========================
it was compiled with
vac_blk: vac_blk.pgc
ecpg vac_blk.pgc -I/usr/include/pgsql
gcc -o vac_blk vac_blk.c -I/usr/include/pgsql -lecpg

If you call "vacuum vac_blk;" in psql after
creation, insertion and update to vac_blk table
and running vac_blk binary,
it hangs until the vac_blk program exits.
Than the vacuum finishes afterwards.

To reproduce this, you must first update the vac_blk
table. If no update, the select doesn't block the
vacuum.