BUG #1331: after update: vacuum blocks parallel to select - Mailing list pgsql-bugs

From PostgreSQL Bugs List
Subject BUG #1331: after update: vacuum blocks parallel to select
Date
Msg-id 20041129112606.EB3517385D6@www.postgresql.com
Whole thread Raw
Responses Re: BUG #1331: after update: vacuum blocks parallel to select
List pgsql-bugs
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.

pgsql-bugs by date:

Previous
From: Mehul Doshi-A20614
Date:
Subject: Re: Installation fails for postgresql-8.0.0-beta4 on Windo
Next
From: "PostgreSQL Bugs List"
Date:
Subject: BUG #1332: wrong results from age function