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.