Perguntas[sequence](dba)

tentando adicionar auto_increment a uma coluna existente

ALTER TABLE table_name ALTER COLUMN id_column SET DEFAULT nextval('table_id_column_seq');

tente 1:

CREATE SEQUENCE table_id_column_seq AS integer START 1 OWNED BY table.id_column;

Erro:

ERRO: a sequência deve ter o mesmo dono da tabela à qual está vinculada

tente 2:

CREATE SEQUENCE table_id_column_seq AS integer START 1 OWNED TO postgres;

Erro:

ERROR:  syntax error at or near "integer"
LINE 1: CREATE SEQUENCE table_id_column_seq integer START 1...
                                                     ^

Como deveria ser?

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?

Estou tentando descobrir como uma inserção de tabela se comporta quando a sequência correspondente transborda no Postgres. Para testar isso, criei uma tabela

CREATE TABLE t1(
    id SERIAL
);

Isso criou uma sequência correspondente

select pg_get_serial_sequence('t1', 'id');
 pg_get_serial_sequence
------------------------
 public.t1_id_seq
(1 row)

Editei a sequência manualmente e defini um valor máximo

alter sequence t1_id_seq MAXVALUE 5;

Neste ponto, eu esperava não poder inserir na tabela quando a sequência atingir seu valor máximo. Mas vi que ainda posso inserir na tabela.

insert into t1 select * from generate_series(10,100);
INSERT 0 91

E a sequência nextval é definida como 1

select * from nextval('t1_id_seq');
 nextval
---------
       1
(1 row)

A inserção da tabela não deve falhar quando a sequência estourar? Por que a sequência não está transbordando neste caso?

Eu tenho uma tabela simples como abaixo no PostgreSQL 13:

table name: newtable1 

field       type 
-----       ----    
Seq         bigserial   
code        varchar     

Seqé a chave primária (incremento automático)
Codeé um índice de chave único

Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing   --> Seq value is 1
Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing
Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing

Insert Into newtable (Code) Values ('002') On Conflict(Code) Do Nothing   --> Seq value is 4

Pergunta

Por que o valor Seq continua aumentando?
Existe alguma maneira de aumentar apenas o valor Seq se ele for inserido com sucesso?

Esperando que alguém possa esclarecer uma seção deste artigo "agregação de dados incrementais escaláveis" e a pergunta correspondente.

Assuma o Postgres 10 e superior.

No Postgres 10, você pode usar a função pg_sequence_last_value para verificar o número de sequência emitido mais recentemente. No entanto, não seria seguro simplesmente agregar todos os eventos até o valor de sequência mais recente. Ainda pode haver gravações em andamento na tabela de eventos que receberam valores de sequência inferiores, mas ainda não estão visíveis quando a agregação é executada.

Existem alternativas para pg_sequence_last_value que garantem o id seq correto?

Em uma tabela (chame-a de eventos) com um id BIGSERIAL, existe uma maneira preferencial de buscar o id seq mais recente para fins de agregação? Tudo bem se houver inserções pendentes em uma transação diferente (elas terão ids de sequência mais altas) e serão coletadas em execuções subsequentes.

O objetivo é usar um seq id para executar a agregação (rollups incrementais) em inserções já confirmadas.

Os três métodos a seguir garantem o retorno do último seq id para inserções confirmadas e evitam a armadilha mencionada no artigo relacionado a pg_sequence_last_value?

Existem outras técnicas que alguém poderia sugerir?

SELECT max(id) FROM events;

SELECT last_value FROM pg_sequences WHERE sequencename='events_id_seq';

SELECT id FROM events ORDER BY id DESC LIMIT 1;

Assumir read committednível de isolamento

Estou tentando criar uma sequência da seguinte maneira:

create sequence some_seq start with (select max(id) + 1 from some_table);

Mas estou recebendo o seguinte erro:

ERROR: syntax error at or near "("

Eu também tentei esta abordagem:

DO $$
DECLARE
    min_seq_value int8 := (select max(id) + 1 from some_table);
BEGIN
    create sequence some_seq start with min_seq_value;
END; $$;

Mas dá-me:

ERROR: syntax error at or near "min_seq_value"

Parece ser bem básico, mas não consigo fazê-lo funcionar. Alguma ideia?

Obrigada!

Estou criando um aplicativo de bate-papo e salvarei mensagens de bate-papo para todas as conversas e usuários em uma tabela indexada BRIN particionada (em convo ids). Quando meu servidor deseja armazenar novas mensagens, ele precisa saber o número de sequência da última mensagem de cada conversa. Pode haver muitas conversas, então não há problema em gerar dinamicamente uma sequência (CREATE SEQUENCE serial) para cada conversa no banco de dados?

Vou usar o banco de dados com um cluster de servidores web Nodejs e uma instância REDIS. Semanticamente o redis está entre o nodejs e o Postgres db. Minha segunda ideia é, em vez de sequências, eu faria uma tabela que contém o contador para cada convo (duas colunas) carregar o contador no redis e bloquear a linha quando houver bate-papo, incrementá-lo lá e escrevê-lo de volta regularmente.

Qual é melhor, algum desses é bom? Obrigado!

Editar

A tabela de mensagens é BRIN de várias colunas indexadas no convo id e no número da mensagem. E não há pk e nenhuma restrição exclusiva nas duas colunas. Pode haver lacunas e até duplicatas (o que é improvável devido à atomicidade do redis INCR e por ser a única instância) (outro ponto importante)

Tenho que detalhar mais. Recebi duas respostas me dizendo para simplesmente fazer uma sequência para cada mensagem no banco de dados. O problema com isso é a eficácia do índice brin a longo prazo. Por isso escrevi na minha pergunta, porque é importante. Se você não sabe como funciona o índice brin, procure-o. Essencialmente, é um índice de intervalos. Agora, se você fizer uma sequência em todas as mensagens, verá o que acontece. Para o brin, o número/id da mensagem não adicionará nenhuma informação, porque com o tempo todos os IDs de mensagem para todas as conversas estarão entre um número relativamente pequeno e o último valor da sequência. E isso, vai diminuir muito a velocidade de seleção, eu acho. É aplicativo de bate-papo. Terei que consultar as mensagens não enviadas no login. E não me diga para usar btree em vez disso nem responda obrigado :D

Eu pensei em UUID e isso não será útil pelo mesmo motivo acima.

Editar 2

Eu entendo agora que gerar sequências é uma má ideia. Então, o que resta é a segunda opção, ou talvez a sugestão de uma sequência, caso você possa argumentar como isso não importará para o desempenho da consulta.

Eu tenho um banco de dados que foi sequenciado correlacionado a ids exclusivos de tabela e "independente", mas com valores.

Preciso saber quais são realmente usadas, mas não sou o dono de todos os procedimentos executados no banco de dados.

Existe alguma maneira de saber quando uma sequência do Postgres foi usada pela última vez?

Parece que select nextval('table_name')realmente faz o incremento de valor.

Meu objetivo é "prever" o nextvalvalor em todas as tabelas do banco de dados sem realmente fazer qualquer incremento. Esta deve ser uma operação somente leitura.

Não consigo executar o select nextvalem uma conexão somente leitura, pois ele está realmente tentando fazer uma transação. Eu gostaria de poder consultar isso e monitorar as sequências em um banco de dados de réplica somente leitura.

Como você resolveria isso e atingiria a meta?