[pgsql-jp: 35957] VACUUM中にCOPYが終わらない

Kazuo Kojima kojima @ lucida.com
2005年 9月 7日 (水) 10:03:18 JST


$B>.Eg$H?=$7$^$9!#(B

$B:#2s!"=i$a$FEj9F$5$;$F$$$?$@$-$^$9!#(B

$B0JA0$K$bF1MM$JEj9F$O$"$C$?$N$G$9$,!"$o$?$7$,D>LL$7$F$$$kLdBj(B
$B$H$O!"0c$&$h$&$J$N$G$*CN7C$r$$$?$@$-$?$/Ej9F$7$^$7$?!#(B
PostgreSQL$B$OK\Ev$K=i?4<T$G$9$N$G$h$m$7$/$*4j$$$7$^$9!#(B

$B8=:_!"0J2<$N$h$&$J%O!<%I$J%7%9%F%`$NJ]<i$r$7$F$$$^$9!#(B

$B#1!%(BC$B!\!\$G:n @ .$5$l$?%W%m%0%i%`$G%G!<%?$r%F!<%V%k(BA$B$K3JG<$7$F$$$^$9!#(B
    $B-!!!Ls#1J,$4$H$K#7 @ i7o$N%G!<%?$r3JG<(B
    $B-"!!3JG<J}K!$O!"(BCOPY$B%3%^%s%I$r;HMQ!#(B
    $B-#!!(Blibpq$B$N(BPQexec$B$r;HMQ!#(B
    $B-$!!#2#4;~4V1?MQ(B
$B#2!%Kh8aA0#0;~$KA0F|$N%G!<%?$r:o=|$7$^$9!#!JLs#1 @ iK|7o!K(B
$B#3!%Kh8aA0#1;~$+$i(BVACUUM$B=hM}$r<B9T!J%?%9%/$G5/F0!K(B

$BLdBj$O!"%3%T!<=hM}$,=*$o$i$J$$$3$H$,$h$/5/$3$k$3$H$G$9!#(B
$B$7$+$b!"(BVACUUM$B=hM}$r9T$C$F$$$k$H$-$K$N$_5/$3$j$^$9!#(B

$BDL>o$O!"$"$k0lDj;~4V%3%T!<$N1~Ez$,$J$$$H6/@)E*$K%"%W%j$r:F5/F0(B
$B$7$F$$$^$9!#!J4F;k%"%W%j$G9T$C$F$$$^$9!K(B
$B$7$+$7!":F5/F0$r9T$o$J$$>uBV$K$7$F$*$-$^$7$?$i!"#3F|0J>e$=$N$^$^$G!"(B
$B8G$^$C$F$$$^$7$?!#(B

$B$=$N:]$K(BPSQL$B%3%^%s%I$G(Bpg_locks$B$rH/9T$7$?:]$K<hF@$7$?>pJs$r0J2<$K(B
$B5-:\$7$^$9!#(B
$B0JA0!"$3$N%a!<%j%s%0%j%9%H$G8+$D$1$?%3%^%s%I$r;HMQ$7$^$7$?!#(B

mstp_db=# select l.pid,c.relname,l.transaction,l.mode,l.granted from 
pg_locks l LEFT OUTER JOIN pg_
class c ON l.relation = c.oid;
 pid  |       relname       | transaction |           mode           | 
granted
----+---------------+----------+------------------+---------
 572 |                         |     1688237 | ExclusiveLock 
| t
2388 | tbl_a                 |                  | ShareUpdateExclusiveLock | 
t
  572| tbl_b                  |                  | AccessShareLock 
| t
  572| tbl_a                  |                  | RowExclusiveLock 
| t
 2388| tbl_a                 |                  | ShareUpdateExclusiveLock 
| t
 2388|                        |     1678055  | ExclusiveLock 
| t

PID:572$B$,(BCopy$B%3%^%s%I$rH/9T$7$F$$$k%W%m%;%9$G$9!#(B
tbl_b$B$O(Btbl_a$B$X$N%3%T!<=hM}$NA0$K9T$C$?=hM}$GF1$8%H%i%s%6%/%7%g%sFb$G=hM}$7$F$$$^$9!#(B
VACUUM$B$N%m%0$r8+$F$_$k$H!"(Btbl_a$B$N(BVACUUM$B=hM}Cf$KH/@8$7$F$$$k$h$&$G$9!#(B

$B%O!<%I%9%Z%C%/$G$9!#(B
CPU : P4 2.8GHz
MEM : 1.5GB

OS : Windows 2000Server SP4
PostgreSQL 7.4.1
Libpq 7.4
$B%F!<%V%k>pJs(B
    Column    |            Type             | Modifiers
-----------+-------------------+-----------
 item01       | numeric(2,0)                | not null
 item02       | numeric(10,0)              | not null
 itea03        | character varying(3)    | not null
 item04       | numeric(4,0)                | not null
 item05       | numeric(4,0)                | not null
 item06       | numeric(10,0)              |
 item07       | numeric(3,0)                |
 item08       | numeric(10,0)              |
 item09       | numeric(3,0)                |
 item10       | numeric(5,0)                | not null
 item11       | timestamp without time zone | not null
Indexes:
    "tbl_a_key" primary key, btree (item01, item02, item03, item04, item05, 
item11)

$B$3$N$h$&$J8=>]$N860x$H2sHrJ}K!$r$45}<u$$$?$@$1$J$$$G$7$g$&$+!)(B

$B$A$J$_$K!"(BVACUUM$B=hM}$O#8!A#9;~4VDxEY3]$+$j$^$9!#!JDL>o;~!K(B
$BEv8=>]$,H/@8$9$k$H!"(BVACUUM$B$b=*N;$7$^$;$s!#(B
$B$3$N%F!<%V%k$N(BINDEX$B$r:o=|$9$k$H!"(BVACUUM$B;~4V$OLs#1#3J,$G=*N;!"(B
$B$7$+$bEv8=>]$OH/@8$7$J$/$J$j$^$7$?!#(B

postmaster.log$B$K%(%i!<>pJs$,$^$C$?$/$N$i$:!"860x$,J,$+$j$^$;$s!#(B
$B$*5RMM$X$N @ bL@$N0Y$K$b860x!"BP=hK!$r65$($F$$$?$@$1$l$P$H;W$$$^$9!#(B

$BMpJ8$G<:Ni$7$^$9!#(B
$B$h$m$7$/$*4j$$$7$^$9!#(B





pgsql-jp メーリングリストの案内