historie a jedinecnost [Re: Platnost zaznamu v ciselniku jeste jednou]

[Radek Kanovsky]

On Thu, Jul 24, 2003 at 02:12:18PM +0200, Honza Pazdziora wrote:

> > Myslenkovy pochod byl takovy, ze v triggeru to bude jednodussi, protoze
> > databaze pri INSERTu sama zamyka tabulku. Ale v postgresu je tento
> > lock nedostatecny na to, aby zamezil dalsim insertum mezi triggerem
> > a vlastnim insertem.
> > 
> > Naopak se mne ted po obede jevi jednoduchy trigger jako nevyhovujici,
> > protoze pravdepodobne bude potreba zamknout tabulku BEFORE a odemknout
> > AFTER. Takovy cirkus uz je docela velka komplikace a lepsi bude to resit
> > v aplikaci.
> 
> Poslete prosim definice tech tabulek a triggeru, ktere se snazite nad
> tim mit. At uz resime ukol jak zamezit vlozeni prekryvajicich se
> intervalu nebo jak nastavit ten konec tak, aby se neprekryvaly, jsem
> presvedcen, ze odpovidajici reseni v PostreSQL najdeme.

Zatim vypada definice tabulky zhruba takto:

CREATE TABLE his_h (
    -- Polozky spolecne vsem historickym tabulkam

    -- ID entity
    id           INTEGER NOT NULL,

    -- Umely primarni klic kvuli jednoduchosti. V opacnem
    -- pripade by byl primarni klic tvoren atributy
    -- (id, modifikace, platnost_od).
    verze        SERIAL NOT NULL PRIMARY KEY,

    -- platnost zaznamu od - do. 3000-01-01 je povazovano za
    -- nekonecno (to uz tady nebudu) kvuli zjednoduseni dotazu
    platnost_od  DATE NOT NULL DEFAULT 'epoch'::date,
    platnost_do  DATE NOT NULL DEFAULT '3000-01-01'::date,
    platny       BOOLEAN NOT NULL DEFAULT 't',

    -- kdo a kdy tento zaznam vytvoril
    modifikace   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    modifikoval  VARCHAR(32) NOT NULL DEFAULT getpgusername(),

    -- Vlastni uzitecne atributy
    a            INTEGER,
    b            VARCHAR(32)
);

Tahle tabulka vlastne umozni udrzovani dvou historii. Historie hodnot
mne poskytne odpoved na dotazy "jakou hodnotu mel mit atribut A 10.6.2002?"
nebo "jako hodnotu bude mit atribut A 3.5.2006?" apod. Pozadovano je
tedy i planovani hodnot do budoucna. Uzivatel muze sam zadat platnost
zaznamu od urciteho data v budoucnosti. Tato historie je ulozena jenom v
platnych zaznamech (platny = 't'). Hodnoty platnost_od a platnost_do
platnych zaznamu pro jednu entitu musi vytvorit souvisly casovy usek
bez der.

Druhou historii je historie zmen, ze ktere zjistim, co kdo a kdy
editoval. Tato historie je ulozena ve vsech zaznamech (i neplatnych).
Update zaznamu totiz zpusobi zneplatneni puvodniho (platny -> 'f')
a vytvoreni nove verze zaznamu s novymi hodnotami. Jelikoz je mozne
provadet i zmeny do minula, tato historie se od historie hodnot
muze lisit. V platnych zaznamech tedy muze byt, ze atribut A
mel mit 31.12.2002 hodnotu 42, ale ve skutecnosti mohl mit tehdy
hodnotu 43. Vzdy se ale bude dat dohledat, kdo neco pokazil a kdy
to kdo opravil.

Nad timto je jednoduche view, ktere poskytuje aktualni hodnoty:

CREATE VIEW his AS SELECT * FROM his_h
  WHERE platny = 't' AND (NOW()::DATE BETWEEN platnost_od AND platnost_do);

Overeni jedinecnosti hodnoty atributu A by melo fungovat tak, ze kdyz
by nekdo chtel vlozit do databaze zaznam platny od 1.1.2000 do 1.1.2003
s hodnotou A = 42, musim projit vsechny platne zaznamy, kde A = 42
a zjistit, jestli se jejich doba platnosti neprekryva s pozadovanym
intervalem 1.1.2000-1.1.2003. Soucasne musim zakazat vlozeni jakehokoliv
noveho zaznamu s hodntou A = 42 do te doby, nez udelam vlastni INSERT.

> Rekl bych, ze nejvetsi kus, ktery bude potreba zamknout, jsou ty
> ovlivnene zaznamy. Ty budou zamceny budto implicitne nebo explicitne
> pomoci select for update. Nic dalsiho by treba byt nemelo.

Pokud opravuju urcity zaznam, tak delam "select ... for update" nad
vsemi platnymi verzemi zaznamu dane entity. Tohle mne stacilo, dokud
neprisla rec na UNIQUE hodnoty. Mohl bych takto zamknout i vsechny
zaznamy, ktere nekdy mely (nebo budou mit) hodnotu A=42, kterou se
snazim vlozit. Na tom by se zastavil i jiny INSERT ktery by chtel vlozit
A=42, ale jenom v pripade, ze tam uz nejaky zaznam s touto hodnotou je.
Pokud tam neni, tak "SELECT a FROM his_h WHERE a=42 FOR UPDATE" vlastne
nic nezamkne.

Zdravi

RadekK