The following bug has been logged online:

Bug reference: 1331
Logged by: Michael Enke

Email address: michael.enke (AT) wincor-nixdorf (DOT) 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.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
This isn't a bug, it's simply vacuum waiting until it can acquire a
page-level lock.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

"Enke, Michael" <michael.enke (AT) wincor-nixdorf (DOT) com> writes:
Sure it can; but that doesn't mean there is no interlocking at all.

The issue you have is not so much that the select is running as that it
is *not* running, ie, it's stopped on a particular page. As soon as it
moves off the modified page, the vacuum will be able to make progress.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

But the description says: vacuum can run parallel to selects.
This is than not true.

Regards,
Michael

Tom Lane schrieb:
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org