Generazione della sequenza in ordine

C’è un modo per generare un qualche tipo di identificatore in ordine per i record di una tabella?

Supponiamo di avere due thread che eseguono query:

Discussione 1:

begin; insert into table1(id, value) values (nextval('table1_seq'), 'hello'); commit; 

Thread 2:

 begin; insert into table1(id, value) values (nextval('table1_seq'), 'world'); commit; 

È del tutto ansible (a seconda dei tempi) che un osservatore esterno veda la (2, “world”) registrazione prima di (1, “ciao”).

Va bene, ma voglio un modo per ottenere tutti i record nella ‘tabella1’ che è apparso dall’ultima volta che l’osservatore esterno lo ha controllato.

Quindi, c’è un modo per ottenere i record nell’ordine in cui sono stati inseriti? Forse OIDs può aiutare?

No. Dal momento che non esiste un ordine naturale di righe in una tabella di database, tutto ciò che devi gestire sono i valori nella tabella.

Bene, ci sono le colonne del sistema specifico Postgres cmin e ctid che potresti abusare in una certa misura.

L’ID tupla ( ctid ) contiene il numero del blocco file e la posizione nel blocco per la riga. Quindi questo rappresenta l’ordine fisico corrente sul disco. Le aggiunte successive avranno un ctid più grande, normalmente . La tua istruzione SELECT potrebbe assomigliare a questo

 SELECT *, ctid -- save ctid from last row in last_ctid FROM tbl WHERE ctid > last_ctid ORDER BY ctid 

ctid ha il tipo di dati tid . Esempio: '(0,9)'::tid

Tuttavia non è stabile come identificatore a lungo termine, poiché VACUUM o qualsiasi UPDATE simultaneo o altre operazioni possono modificare la posizione fisica di una tupla in qualsiasi momento. Per la durata di una transazione è comunque stabile. E se stai solo inserendo e nient’altro , dovrebbe funzionare localmente per il tuo scopo.

Vorrei aggiungere una colonna timestamp con default now() oltre alla colonna serial

Vorrei anche lasciare una colonna predefinita compilare la colonna id (una colonna serial o IDENTITY ). Ciò recupera il numero dalla sequenza in una fase successiva rispetto al recupero esplicito e quindi all’inserimento, riducendo quindi (ma non eliminando) la finestra per una condizione di competizione, ovvero la possibilità che un id inferiore venga inserito in un secondo momento. Istruzioni dettagliate:

  • Colonna della tabella di incremento automatico

Quello che vuoi è forzare le transazioni a commettere (rendendo visibili i loro inserti) nello stesso ordine in cui hanno fatto gli inserti. Per quanto riguarda gli altri clienti, gli inserti non sono accaduti fino a quando non sono stati commessi, dal momento che potrebbero tornare indietro e svanire.

Questo è vero anche se non si avvolgono gli inserti in un begin / commit esplicito. Il commit delle transazioni, anche se eseguito in modo implicito, non viene necessariamente eseguito nello stesso ordine in cui è stata inserita la riga. È sobject alle decisioni di ordinazione dello scheduler della CPU del sistema operativo, ecc.

Anche se PostgreSQL supportava letture sporche, ciò sarebbe comunque vero. Solo perché inizi tre inserti in un dato ordine non significa che finiranno in quell’ordine.

Non esiste un modo facile o affidabile per fare ciò che sembra voler preservare la concorrenza. Dovrai fare i tuoi inserimenti in ordine su un singolo lavoratore – o usare il blocco del tavolo come suggerisce Tometzky, che ha praticamente lo stesso effetto in quanto solo uno dei thread di inserimento può fare qualsiasi cosa in un dato momento.

È ansible utilizzare il blocco dell’advisory, ma l’effetto è lo stesso.

L’uso di un timestamp non aiuta, dal momento che non si sa se per due timestamp ci sia una riga con un timestamp tra i due che non è stato ancora eseguito il commit.

Non è ansible fare affidamento su una colonna Identity in cui si leggono le righe solo fino al primo “gap” perché le lacune sono normali nelle colonne generate dal sistema a causa dei rollback.

Penso che dovresti fare un passo indietro e vedere perché hai questo requisito e, dato questo requisito, perché utilizzi singoli inserimenti simultanei.

Forse starai meglio inserendo inserti in batch di blocchi singoli da una singola sessione?

Se intendi che ogni query, se vede la riga del world , deve vedere anche hello Fila, quindi dovresti fare:

 begin; lock table table1 in share update exclusive mode; insert into table1(id, value) values (nextval('table1_seq'), 'hello'); commit; 

Questa share update exclusive mode è la modalità di blocco più debole che è auto-esclusiva – solo una sessione può tenerla alla volta.

Siate consapevoli che questo non renderà questa sequenza gap-less – questo è un problema diverso.