Thread

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

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2004-11-29T11:26:06Z

   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.
   
   
   

2. Re: BUG #1331: after update: vacuum blocks parallel to select

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-29T15:59:25Z

   "PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
   > Description:        after update: vacuum blocks parallel to select
   
   This isn't a bug, it's simply vacuum waiting until it can acquire a
   page-level lock.
   
   			regards, tom lane
   
   

3. Re: BUG #1331: after update: vacuum blocks parallel to select

   Michael Enke <michael.enke@wincor-nixdorf.com> — 2004-11-29T16:19:20Z

   But the description says: vacuum can run parallel to selects.
   This is than not true.
   
   Regards,
   Michael
   
   Tom Lane schrieb:
   > "PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
   > 
   >>Description:        after update: vacuum blocks parallel to select
   > 
   > 
   > This isn't a bug, it's simply vacuum waiting until it can acquire a
   > page-level lock.
   > 
   > 			regards, tom lane
   
   

4. Re: BUG #1331: after update: vacuum blocks parallel to select

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-29T16:27:14Z

   "Enke, Michael" <michael.enke@wincor-nixdorf.com> writes:
   > But the description says: vacuum can run parallel to selects.
   > This is than not true.
   
   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