PostgreSQL違反唯一約束的插入操作會產品HEAP垃圾嗎？

PostgreSQL 是通過索引來保證唯一值約束的，（包括PKEY）。
但是，如果遇到唯一約束問題，HEAP和BTREE頁裡的資料會不會有垃圾呢?
SESSION A:
digoal=> create table pk_test(id int primary key);
CREATE TABLE
Time: 51.559 ms
digoal=> begin;
BEGIN
Time: 0.103 ms
digoal=> insert into pk_test values (1);
INSERT 0 1
Time: 0.565 ms

SESSION B:
digoal=> begin;
BEGIN
digoal=> insert into pk_test values (1);

SESSION C:
digoal=> begin;
BEGIN
digoal=> insert into pk_test values (1);

觀察有無行鎖：
顯然沒有，因為不是靠鎖來保證唯一。而且插入也不需要行鎖，因為未提交的記錄其他會話是看不到的，也不可能來查詢或更新，沒有加鎖的必要。
digoal=# create extension pgrowlocks;
CREATE EXTENSION
digoal=# select * from pgrowlocks(`pk_test`);
 locked_row | locker | multi | xids | modes | pids 
------------+--------+-------+------+-------+------
(0 rows)

觀察物件鎖等待：
digoal=# create or replace function f_lock_level(i_mode text) returns int as 
$$

declare
begin
  case i_mode
    when `INVALID` then return 0;
    when `AccessShareLock` then return 1;
    when `RowShareLock` then return 2;
    when `RowExclusiveLock` then return 3;
    when `ShareUpdateExclusiveLock` then return 4;
    when `ShareLock` then return 5;
    when `ShareRowExclusiveLock` then return 6;
    when `ExclusiveLock` then return 7;
    when `AccessExclusiveLock` then return 8;
    else return 0;
  end case;
end; 

$$
 language plpgsql strict;

digoal=# with t_wait as                     
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as 
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted) 
select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,r.query_start r_query_start,
now()-r.query_start r_locktime,r.query r_query,w.mode w_mode,w.pid w_pid,w.page w_page,
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query  
from t_wait w,t_run r where
  r.locktype is not distinct from w.locktype and
  r.database is not distinct from w.database and
  r.relation is not distinct from w.relation and
  r.page is not distinct from w.page and
  r.tuple is not distinct from w.tuple and
  r.classid is not distinct from w.classid and
  r.objid is not distinct from w.objid and
  r.objsubid is not distinct from w.objsubid and
  r.transactionid is not distinct from w.transactionid and
  r.pid <> w.pid
  order by f_lock_level(w.mode)+f_lock_level(r.mode) desc,r.xact_start;
可以看到B,C會話正在等待transactionid鎖，是由於約束檢測造成的。
-[ RECORD 1 ]-+--------------------------------
locktype      | transactionid
r_mode        | ExclusiveLock
r_user        | digoal
r_db          | digoal
relation      | 
r_pid         | 24785
r_page        | 
r_tuple       | 
r_xact_start  | 2015-06-04 12:27:53.475664+08
r_query_start | 2015-06-04 12:28:00.13671+08
r_locktime    | 00:00:43.79604
r_query       | insert into pk_test values (1);
w_mode        | ShareLock
w_pid         | 7536
w_page        | 
w_tuple       | 
w_xact_start  | 2015-06-04 12:28:19.256706+08
w_query_start | 2015-06-04 12:28:21.89269+08
w_locktime    | 00:00:22.04006
w_query       | insert into pk_test values (1);
-[ RECORD 2 ]-+--------------------------------
locktype      | transactionid
r_mode        | ExclusiveLock
r_user        | digoal
r_db          | digoal
relation      | 
r_pid         | 24785
r_page        | 
r_tuple       | 
r_xact_start  | 2015-06-04 12:27:53.475664+08
r_query_start | 2015-06-04 12:28:00.13671+08
r_locktime    | 00:00:43.79604
r_query       | insert into pk_test values (1);
w_mode        | ShareLock
w_pid         | 7411
w_page        | 
w_tuple       | 
w_xact_start  | 2015-06-04 12:28:10.211724+08
w_query_start | 2015-06-04 12:28:12.188666+08
w_locktime    | 00:00:31.744084
w_query       | insert into pk_test values (1);
結束會話a, B,C報錯。
SESSION A:
digoal=> end;
COMMIT
Time: 0.235 ms

SESSION B:
ERROR:  duplicate key value violates unique constraint "pk_test_pkey"
DETAIL:  Key (id)=(1) already exists.

SESSION C:
ERROR:  duplicate key value violates unique constraint "pk_test_pkey"
DETAIL:  Key (id)=(1) already exists.

接下來要觀察, session b,c到底有沒有將記錄插進去，從ctid可以看到B,C的兩條未提交的垃圾記錄已經插入了heap page。
所以需要垃圾回收。
digoal=> insert into pk_test values(2);
INSERT 0 1
digoal=> select ctid,* from pk_test ;
 ctid  | id 
-------+----
 (0,1) |  1
 (0,4) |  2
(2 rows)

但是index page是沒有被插入的，因為這個INDEX就是保證唯一性的，不可能在這裡出現重複。
digoal=# create extension pageinspect;
CREATE EXTENSION
digoal=# select * from bt_page_items(`digoal.pk_test_pkey`,1);
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,4) |      16 | f     | f    | 02 00 00 00 00 00 00 00
(2 rows)

接下來我們看看check約束，會不會造成垃圾資料？
digoal=> create table ck_test(id int check (id>10));
CREATE TABLE
digoal=> insert into ck_test values (1);
ERROR:  new row for relation "ck_test" violates check constraint "ck_test_id_check"
DETAIL:  Failing row contains (1).
digoal=> insert into ck_test values (11);
INSERT 0 1
digoal=> select ctid,* from ck_test ;
 ctid  | id 
-------+----
 (0,1) | 11
(1 row)

digoal=> insert into ck_test values (1);
ERROR:  new row for relation "ck_test" violates check constraint "ck_test_id_check"
DETAIL:  Failing row contains (1).
digoal=> insert into ck_test values (1);
ERROR:  new row for relation "ck_test" violates check constraint "ck_test_id_check"
DETAIL:  Failing row contains (1).
digoal=> insert into ck_test values (11);
INSERT 0 1
digoal=> select ctid,* from ck_test ;
 ctid  | id 
-------+----
 (0,1) | 11
 (0,2) | 11
(2 rows)
從ctid可以看到，check約束是在資料進入heap page前檢查的，所以不會產生垃圾。