Jak rozłożyć ctid na numery stron i wierszy?

Każdy wiersz w tabeli ma kolumnę systemową ctid typu, tidktóra reprezentuje fizyczną lokalizację wiersza:

create table t(id serial);
insert into t default values;
insert into t default values;

select ctid
     , id
from t;

ctid | ID
: ---- | -:
(0,1) | 1
(0,2) | 2)

dbfiddle tutaj

Jaki jest najlepszy sposób na poruszanie się tylko numer strony jako od ctidw najbardziej odpowiedni typ (np integer, bigintlub numeric(1000,0))?

Jedyny sposób mogę myśleć jest bardzo brzydka.

SELECT (ctid::text::point)[0]::bigint AS page_number FROM t;

Twój skrzypce z moim rozwiązaniem.

@bma już podpowiedział coś podobnego w komentarzu. Tutaj jest ...

Uzasadnienie dla tego typu

ctidjest typu tid(identyfikator krotki), wywoływany ItemPointerw kodzie C. Według dokumentacji:

To jest typ danych kolumny systemowej ctid. Identyfikator krotki to para ( numer bloku , indeks krotki w bloku ), który identyfikuje fizyczną lokalizację wiersza w tabeli.

Odważny nacisk moje. I:

( ItemPointer, znany również jakoCTID )

Blok ma 8 KB w standardowych instalacjach. Maksymalny rozmiar stołu to 32 TB . Logicznie wynika, że ​​numery bloków muszą zawierać co najmniej maksimum (obliczenia ustalone zgodnie z komentarzem @Daniel):

SELECT (2^45 / 2^13)::int      -- = 2^32 = 4294967294

Które pasowałoby do niepodpisanego integer. Podczas dalszych badań odkryłem w kodzie źródłowym, że ...

bloki są numerowane kolejno, od 0 do 0xFFFFFFFE .

Odważny nacisk moje. Co potwierdza pierwsze obliczenia:

SELECT 'xFFFFFFFE'::bit(32)::int8 -- max page number: 4294967294

Postgres używa liczby całkowitej ze znakiem i dlatego jest nieco krótszy. Nie mogłem jeszcze ustalić, czy reprezentacja tekstowa jest przesunięta, aby uwzględnić liczbę całkowitą ze znakiem. Dopóki ktoś tego nie wyjaśni, wrócę do bigint, co w każdym razie działa.

Odlew

W Postgres 9.3 nie ma zarejestrowanej obsady dla tego tidtypu:

SELECT *
FROM   pg_cast
WHERE  castsource = 'tid'::regtype
OR     casttarget = 'tid'::regtype;

 castsource | casttarget | castfunc | castcontext | castmethod
------------+------------+----------+-------------+------------
(0 rows)

Nadal możesz przesyłać do text. W Postgres istnieje reprezentacja tekstowa :

Innym ważnym wyjątkiem jest to, że „automatyczne rzutowania konwersji we / wy”, wykonywane przy użyciu własnych funkcji we / wy typu danych w celu konwersji na tekst lub z innych typów ciągów, nie są jawnie reprezentowane pg_cast.

Reprezentacja tekstowa odpowiada reprezentacji punktu, który składa się z dwóch float8 liczb, a rzutowanie jest bezstratne.

Możesz uzyskać dostęp do pierwszej liczby punktów o indeksie 0. Przesyłaj do bigint . Voilá.

Występ

Przeprowadziłem szybki test na stole z 30 tys. Wierszy (najlepiej 5) na kilku alternatywnych wyrażeniach, które przyszły mi do głowy, w tym na Twoim oryginale:

SELECT (ctid::text::point)[0]::int                              --  25 ms
      ,right(split_part(ctid::text, ',', 1), -1)::int           --  28 ms
      ,ltrim(split_part(ctid::text, ',', 1), '(')::int          --  29 ms
      ,(ctid::text::t_tid).page_number                          --  31 ms
      ,(translate(ctid::text,'()', '{}')::int[])[1]             --  45 ms
      ,(replace(replace(ctid::text,'(','{'),')','}')::int[])[1] --  51 ms
      ,substring(right(ctid::text, -1), '^\d+')::int            --  52 ms
      ,substring(ctid::text, '^\((\d+),')::int                  -- 143 ms
FROM tbl;

intzamiast biginttutaj, w większości nieistotne dla celów testu. Nie powtarzałem się za bigint.
Rzutowanie t_tidopiera się na typie złożonym zdefiniowanym przez użytkownika, takim jak skomentował @Jake.
Istota tego: casting jest zwykle szybszy niż manipulacja sznurkiem. Wyrażenia regularne są drogie. Powyższe rozwiązanie jest najkrótsze i najszybsze.