Skip site navigation (1) Skip section navigation (2)

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

From: "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1331: after update: vacuum blocks parallel to select
Date: 2004-11-29 11:26:06
Message-ID: 20041129112606.EB3517385D6@www.postgresql.com (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1331
Logged by:          Michael Enke

Email address:      michael(dot)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(at)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.


Responses

pgsql-bugs by date

Next:From: PostgreSQL Bugs ListDate: 2004-11-29 11:37:50
Subject: BUG #1332: wrong results from age function
Previous:From: Mehul Doshi-A20614Date: 2004-11-29 08:21:55
Subject: Re: Installation fails for postgresql-8.0.0-beta4 on Windo

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group