-- table 구조

-- h1, h2, h3, h4

-- a, b, c, 0

-- a, b, d, 0

-- a, b, c, 0

-- a, b, d, 0

-- a,c,c, 0

-- a,c,d, 0

-- a,c,e, 0

-- select * from table where h1 = 'a';

-- 했을때 7개가 나오는데요, pk 가 정의되지 않았는데 여기서 h3에 order by h3 asc limit 1한값을

-- update 할수 있을까요?

뭐 이정도를 가지고... SPI 의 아래 함수가 있습니다.

    int SPI_execute(const char * command, bool read_only, long count)

여기서 command 에 업데이트 쿼리를, count 에 1을 넣어주면 됩니다(read_only 는 false).

 ctid 라는 숨은 칼럼 값을 사용하면 됩니다. 

이 칼럼은 테이블에서 vacuum full 작업을 하거나, 해당 row의 어떤 칼럼이라도 어떻게든 변경 되면 (DB 용어로 기존 row를 죽이고 새 row를 만들고, 그것이 현재 마지막 트랜잭션에 의해 커밋된 자료임을 나타내는 작업) 그 값이 바뀝니다.

그렇기 때문에, ctid 칼럼을 사용할 때는 반드시 그 값을 구해서 사용해야 합니다. 

또한 가능 하다면, 트랜잭션 내 배타적 로우 잠금(for update 구문)을 사용하면 보다 안전 하겠죠. 

pgsqlkr=> create table t (h1 text, h2 text, h3 text, h4 int);
CREATE TABLE
Time: 242.462 ms
pgsqlkr=> copy t stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> a    b       c       0
>> a    b       c       0
>> a    b       c       0
>> \.
Time: 49726.475 ms
pgsqlkr=> select * from t;
 h1 | h2 | h3 | h4
----+----+----+----
 a  | b  | c  |  0
 a  | b  | c  |  0
 a  | b  | c  |  0
(3 rows)

Time: 0.448 ms
pgsqlkr=> select ctid,* from t;
 ctid  | h1 | h2 | h3 | h4
-------+----+----+----+----
 (0,1) | a  | b  | c  |  0
 (0,2) | a  | b  | c  |  0
 (0,3) | a  | b  | c  |  0
(3 rows)

Time: 0.253 ms
pgsqlkr=> update t set h4 = 1 where ctid = '(0,3)';
UPDATE 1
Time: 19.468 ms
pgsqlkr=> select * from t;
 h1 | h2 | h3 | h4
----+----+----+----
 a  | b  | c  |  0
 a  | b  | c  |  0
 a  | b  | c  |  1
(3 rows)

Time: 0.241 ms
pgsqlkr=> select ctid,* from t;
 ctid  | h1 | h2 | h3 | h4
-------+----+----+----+----
 (0,1) | a  | b  | c  |  0
 (0,2) | a  | b  | c  |  0
 (0,4) | a  | b  | c  |  1
(3 rows)

Time: 0.264 ms

오, ctid 를 사용한 방법이 있었네요. 기가 막힙니다!

주제넘지만 약간 보충하자면 ctid 는 해당 레코드의 위치를 나타내는 값인데,

  CREATE TABLE t(t1 int);

라는 테이블에서 차례로 1,2,3,4 를 입력하면 아래의 결과를 얻습니다.

d_cppschool=# select *, ctid from t;
 t1 | ctid 
----+-------
  1 | (0,1)
  2 | (0,2)
  3 | (0,3)
  4 | (0,4)
(4 rows)

이 중 4 인 레코드를 10 으로 업데이트하면

d_cppschool=# update t set t1=10 where t1=4;
UPDATE 1
d_cppschool=# select *, ctid from t;
 t1 | ctid 
----+-------
  1 | (0,1)
  2 | (0,2)
  3 | (0,3)
 10 | (0,5)
(4 rows)

라는 결과를 얻습니다. 이것은 상기님이 말씀하신 그대로입니다. 그런데 ctid 값이 (0,4) 에서 (0.5) 로 뛰어 있는 것을 볼 수 있는데, 이것은 상당한 의미를 가지고 있습니다. 즉 (0,4) 에 해당하는 공간이 놀고 있다는 것으로 테이블 파일의 레코드에 단편화가 발생했다는 것을 말해주고 있습니다. 이 간격이 촘촘할 수로 당연히 레코드 저장 효율이 높은 것입니다. 이것을 매우 촘촘하게 다시 만들고 싶으면 어떻게 하면 될까요.

간단합니다. VACUUM FULL 을 하면 됩니다.

d_cppschool=# VACUUM FULL t;
VACUUM
d_cppschool=# select *, ctid from t;
 t1 | ctid 
----+-------
  1 | (0,1)
  2 | (0,2)
  3 | (0,3)
 10 | (0,4)
(4 rows)

다시 촘촘해졌습니다. 물론 실무에서는 VACUUM FULL 을 함부로 쓰지는 않고, 테이블 레코드단편화도 이런 식으로 무식하게(?) 측정하지는 않습니다만, 그래도 의미가 상당하다는 것을 소개해 드리고자 합니다.

와우 !!! 기가 막힙니다. -ㅂ- ctid 라는 컬럼이 있는지 몰랐었습니다.

정말 유용할것 같습니다. - 상기님 매번 감사드립니다.

오 초보대왕님 베큠에 단편화 발생하는 부분에 이해가 ctid 구조를 가지고

되는지도 몰랐는데 감사드립니다. ㅋㅋ

근데 위에 함수로 하는 방법은 -ㅂ- 잘 모르겠네요 -_- _-_ -_- ㅋㅋ

아무쪼록 감사드립니다.