Início / dba / Perguntas / 65964
Accepted
Jack Douglas
Asked: 2014-05-28 02:05:13 +0800 CST

Como faço para decompor o ctid em números de página e linha?

  • 772

Cada linha em uma tabela tem uma coluna ctid de sistema do tipo tidque representa a localização física da linha:

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

select ctid
     , id
from t;

ctid | Eu iria
:---- | -:
(0,1) | 1
(0,2) | 2

dbfiddle aqui

Qual é a melhor maneira de obter apenas o número da página ctidno tipo mais apropriado (por exemplo integer, bigintou numeric(1000,0))?

A única maneira que consigo pensar é muito feia.

postgresql postgresql-9.4

1 respostas

  1. Best Answer
    SELECT (ctid::text::point)[0]::bigint AS block_number FROM t;

    db<>mexa aqui

    @bma sugeriu algo semelhante em seu comentário . Aqui está um ...

    Justificativa do tipo

    ctidé do tipo tid(identificador de tupla), chamado ItemPointerno código-fonte da linguagem C. O manual:

    Este é o tipo de dados da coluna do sistema ctid. Um ID de tupla é um par ( número do bloco , índice da tupla dentro do bloco ) que identifica a localização física da linha em sua tabela.

    Ênfase em negrito minha. E:

    ( ItemPointer, também conhecido como CTID)

    Um bloco tem 8 KB em instalações padrão. O tamanho máximo da tabela é de 32 TB . Segue-se logicamente que os números de bloco devem acomodar pelo menos um máximo de (fixado pelo comentário de @Daniel):

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

    O que caberia em um arquivo integer. Em uma investigação mais aprofundada, descobri no código-fonte que ...

    os blocos são numerados sequencialmente, de 0 a 0xFFFFFFFE .

    Ênfase em negrito minha. O que confirma o primeiro cálculo:

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

    Postgres usa inteiro assinado e, portanto, é um pouco curto. Ainda não consegui definir se a representação do texto é deslocada para acomodar o número inteiro com sinal. Até que alguém possa esclarecer isso, eu voltaria para bigint, que funciona de qualquer maneira.

    Elenco

    Não há elenco registrado para o tidtipo no Postgres 9.3 (ainda verdadeiro no Postgres 13):

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

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

    Você ainda pode transmitir para text. Existe uma representação de texto para cada tipo no Postgres :

    Outra exceção importante é que "transmissões automáticas de conversão de E/S", aquelas executadas usando as próprias funções de E/S de um tipo de dados para converter de ou para texto ou outros tipos de cadeia de caracteres, não são explicitamente representadas em pg_cast.

    A representação de texto corresponde à de um ponto, que consiste em dois float8números, cuja conversão é sem perdas.

    Você pode acessar o primeiro número de um ponto com índice 0. Cast to bigint. Voilá.

    atuação

    Fiz um teste rápido no Postgres 9.4 em uma tabela com 30k linhas (melhor de 5) com algumas expressões que me vieram à mente, incluindo a sua original:

    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;

    intem vez de bigint, principalmente irrelevante para o propósito do teste. Acabei repetindo o teste no Postgres 13 bigintem uma tabela com 50k linhas. Os resultados são basicamente os mesmos!

    A conversão se t_tidbaseia em um tipo composto definido pelo usuário, como @Jake comentou.
    A essência disso: a conversão tende a ser mais rápida do que a manipulação de strings. Expressões regulares são caras. A solução acima é a mais curta e rápida.

    • 28

relate perguntas