sev's questions

Eu poderia usar a mesma sequência para gerar as chaves primárias em duas tabelas separadas, por exemplo, como esta?

CREATE TABLE IF NOT EXISTS public."user_Registered"
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    email character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "PK_1" PRIMARY KEY (id),
    CONSTRAINT "UQ_1" UNIQUE (email)
)

CREATE TABLE IF NOT EXISTS public."user_Applicant"
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    email character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "PK_2" PRIMARY KEY (id),
    CONSTRAINT "UQ_2" UNIQUE (email)
)

No meu exemplo os usuários cadastrados têm muito mais dados do que apenas o email e não consigo armazenar isso na mesma tabela e preencher quando eles se cadastrarem. Então meu plano é salvá-los na tabela requerente e depois movê-los para a tabela cadastrada, mas quero preservar o ID.

Eu tenho as duas tabelas a seguir onde armazeno usuários e seus endereços. Tenho um problema relacionado com a ordem em que recebo e consigo guardar os dados do utilizador. A princípio, o usuário fornecerá apenas o endereço e, em seguida, será direcionado para um serviço externo. Para abrir este serviço, preciso fornecer meu ID de usuário interno. O serviço verifica seu endereço de e-mail e me envia a resposta.

CREATE TABLE IF NOT EXISTS public."user"
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    email character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY (id),
    CONSTRAINT "UQ_e12875dfb3b1d92d7d7c5377e22" UNIQUE (email)
)

CREATE TABLE IF NOT EXISTS public.adresses
(
    id integer NOT NULL DEFAULT nextval('address_id_seq'::regclass),
    address character varying COLLATE pg_catalog."default" NOT NULL,
    "userId" integer,
    CONSTRAINT "PK_bec464dd8d54c39c54fd32e2334" PRIMARY KEY (id),
    CONSTRAINT "FK_35472b1fe48b6330cd349709564" FOREIGN KEY ("userId")
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

• Posso gerar o ID do usuário a partir da sequência de outra forma que não seja inserir um e-mail na tabela e, no final, inserir o ID e o e-mail ao mesmo tempo?
• Posso salvar o endereço na tabela de endereços referenciando uma chave estrangeira que ainda não foi adicionada aos usuários?

No PostgreSQL, as sequências são projetadas para ter lacunas pelos motivos mencionados neste post https://stackoverflow.com/questions/9984196/postgresql-gapless-sequences . Minha pergunta é: as sequências não poderiam ser feitas quase sem intervalos verificando periodicamente (digamos uma vez por dia) quais números de sequência não são usados ​​e adicionando-os de volta à sequência a ser usada?

Suponho que atualmente o postgres implementa uma sequência como um contador que incrementa toda vez que é acessado. Em vez disso, pode ser implementado com uma lista de valores livres e um contador. A sequência funcionaria da seguinte forma:

# python style pseudo code

class Sequence:
    def __init__(self):
        self.free_numbers_set = []
        self.counter = 0

    def get_next_sequence_value(self):
        if not isempty(self.free_numbers_set):
            id = self.free_numbers_set.pop(0)
        else:
            id = self.counter
            id ++

        return id

    def add_free_numbers(self, column):
        for i in range(0:self.counter):
            if not i in column:
                free_numbers_set.append(i)
        free_numbers_set.sort()

Toda vez que precisamos de um novo id, executamos get_next_sequence_value()e periodicamente executamosadd_free_numbers(column)

Atualizações que abordam algumas das perguntas

1. Para colunas de identidade, obter os valores atualmente usados ​​seria trivial. Em outros casos caberia ao usuário fornecer uma lista de valores que já estão em uso.
2. A interação com o MVCC precisaria ser pensada. Pode-se implementá-lo de forma que, durante a atualização dos valores ausentes, a sequência seja padronizada para apenas incrementar o contador - isso evitaria conflitos.

Acredito que esta solução poderia fornecer sequências menos esparsas que permitiriam aos usuários usar tipos de id menores (int vs bigint) sendo compatível com versões anteriores com a implementação atual.

Eu tenho uma tabela PostgreSQL que estou tentando converter em uma hipertabela TimescaleDB.

A tabela tem a seguinte aparência:

CREATE TABLE public.data
(
    event_time timestamp with time zone NOT NULL,
    pair_id integer NOT NULL,
    entry_id bigint NOT NULL,
    event_data int NOT NULL,
    CONSTRAINT con1 UNIQUE (pair_id, entry_id ),
    CONSTRAINT pair_id_fkey FOREIGN KEY (pair_id)
        REFERENCES public.pairs (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

Quando tento converter esta tabela em uma hipertabela TimescaleDB usando o seguinte comando:

SELECT create_hypertable(
        'data',
        'event_time',
        chunk_time_interval => INTERVAL '1 hour',
        migrate_data => TRUE
);

Recebo o Erro:ERROR: cannot create a unique index without the column "event_time" (used in partitioning)

Pergunta 1: A partir desta postagem https://stackoverflow.com/questions/55312365/how-to-convert-a-simple-postgresql-table-to-hypertable-or-timescale-db-table-usi meu entendimento é que isso é porque eu especifiquei uma restrição exclusiva (con1) que não contém a coluna pela qual estou particionando - event_time. Isso é correto?

Pergunta 2: Como devo alterar minha tabela ou hipertabela para poder converter isso? Adicionei alguns dados sobre como pretendo usar os dados e a estrutura dos dados abaixo.

Propriedades de dados e uso:

• Pode haver várias entradas com o mesmo event_time - essas entradas teriam entry_id's que estão em sequência
  • Isso significa que, se eu tiver 2 entradas (event_time 2021-05-18::10:16, id 105, <some_data>) e (event_time 2021-05-18::10:16, id 107, <some_data>), então a entrada com id 106 também teria event_time 2021-05-18::10:16
• O entry_id não é gerado por mim e uso a restrição exclusiva con1 para garantir que não estou inserindo dados duplicados
• Vou consultar os dados principalmente em event_time, por exemplo, para criar gráficos e realizar outras análises
• Neste ponto, o banco de dados contém cerca de 4,6 bilhões de linhas, mas deve conter muito mais em breve
• Gostaria de aproveitar a velocidade e a boa compactação do TimescaleDB
• Eu não me importo muito com o desempenho da inserção

Soluções que tenho considerado:

• Empacote todos os eventos que têm o mesmo timestamp em uma matriz de alguma forma e mantenha-os em uma linha. Acho que isso teria desvantagens na compactação e forneceria menos flexibilidade na consulta dos dados. Além disso, eu provavelmente acabaria tendo que descompactar os dados em cada consulta.
• Remova a restrição exclusiva con1 - como faço para garantir que não adicionei a mesma linha duas vezes?
• Expanda a restrição exclusiva con1 para incluir event_time - isso não diminuiria de alguma forma o desempenho e, ao mesmo tempo, abriria para o erro em que insiro acidentalmente 2 linhas com entry_id e pair_id, mas event_time diferente? (Eu duvido que isso seja uma coisa provável de acontecer embora)