PostgreSQL a primární klíč s funkcí?

[M_D]

Bádám, zda primární klíč můž být u PgSQL založen na výsledku funkce, ale nedaří se (mám tady PgSQL9.5)?
Mějme tabulku ttt, kde jsou podstatné dva sloupce:
 smid    | integer not null  | ID zdroje
 casr    | tstzrange not null | Interval platnosti dat
jde udělat dvojsloupcový primární klíč nad obojím:
ALTER TABLE ttt  ADD CONSTRAINT "pk-ttt" PRIMARY KEY(smid, casr);
ale pro štastnější život by se mi hodil primární klíč, kde je zahrnut z toho casr jen počáteční čas, což asi nejde, končí jako syntax error?
ALTER TABLE ttt  ADD CONSTRAINT "pk-ttt" PRIMARY KEY(smid, lower(casr));
Normální index tak udělat jde:
CREATE UNIQUE INDEX "inx-ttt-scc" ON ttt(smid,lower(casr),lower_inc(casr));
Primární index se hodí mít a index dle (smid, casr) už tam je přítomen kvůli omezující podmínce na nepřekrývání se dat <CONSTRAINT "const-ttt-smid-casr" EXCLUDE USING gist (smid WITH =, casr WITH &&)>, takže se takto indexovaná data zbytečně duplikují a index založený jen na start (nebo end času) intervalu by se hodil u některých typů dotazů, kdy neumí využít index data z toho constraint.

[Reklama]

[alex6bbc]

>> ale pro štastnější život by se mi hodil primární klíč, kde je zahrnut z toho casr jen počáteční čas, což asi nejde, končí jako syntax error?

proc si neudelat takovy sloupec a pri vkladani dat si ho napocitat, pak to muze byt v klici jednoduse.

[Logik]

Je zvěrstvo a jeden ze základních antipatternů a obrovská prasečina a ....
(už jsem to snad pomluvil dost, kdyžtak si přidej ještě pár patřičných adjektiv.... :-)).mít primární klíč významový.

Přidej umělé ID jako identifikátor, nad kterým uděláš primární klíč.

[Miroslav Šilhavý]

Celý ten dotaz je nesmysl, omlouvám se.
Primární klíč identifikuje řádek. Pokud je pro identifikaci důležitá jen levá část intervalu, pak by měla být uložena zvlášť levá a zvlášť pravá část. Naopak tzrange z toho dopočítávat (index nad dopočtenou tzrange).

Postgresu funguje PRIMARY KEY a UNIQUE KEY nad NOT NULL sloupci technicky naprosto totožně.
Jediným rozdílem je, že PK může být maximálně jeden, a že pohlídá, aby byl nastavený NOT NULL.
Když si to pohlídáte, ničemu nevadí se na PK v tomto případě vykašlat (nemusíte si na to definovat ani jiný identifikátor, ale slušelo by se to).

Osobně bych ten tzrange rozpálil do dvou timestampů a udělal PK tak, jak jste ho popsal. Tzrange můžete použít jen v indexu, případně (kdyby to mělo nějaký smysl pro výkon - ale pochybuji), triggerovat ho do dalšího sloupce. V PG12 můžete využít generated column: https://www.postgresql.org/docs/12/ddl-generated-columns.html a vyhnout se triggeru.

[e3k]

nemam slov. pane vy ste cyborg?

[Reklama]

[M_D]

Uff, to je nakládačka. :-)

MŠ: rozdíl mezi "primary key" a "index unique" je v tom, že pokud máš nenulový počet uživatelů, co mají nutkavou potřebu do tabulek lézt nějakým tim table view čumítkem a tyto klikátka chtějí zkrátka primární klíč na tabulce. Bez něj buď odmítnou zobrazit nebo se podivně pošahají, když v tabulce je 500M+ řádků. To je jediný důvod, proč byla snaha přidat primární klíč. Aplikačně to celé rychle žije z indexu, který si vyrábí na pozadí ta omezující podmínka na nevkládání překrývajících se dat.
Nu, stejná skupina má občas snahu psát dotazy, kde do where nacpou něco jako "lower(casr) between T1 and T2", tak jsem chtěl zabít dvě mouchy jedním klíčem, že by jim to trochu zrychlilo, když nejsou schopni pochopit range operaci typu casr && (T1, T2]. :-)
Ono při větším počtu řádků už ty indexy začínají i něco žrát času i místa na disku, tak jsem nechtěl kvůli tomu čumítku přidat tupě jen bigserial nebo něco takového.
Samozřejmě to jde řešit, že místo casr:tstzrange použít na to 3 samostatné sloupce (casl:timestamp, casu:timestamp, casb:char(2)) a vyrobit si nad tím primary index pomocí (smid, casl, casb) a kontrola nepřekrývání pomocí "exclude using gist (smid with =, tstzrange(casl, casu, casb) with &&)" funguje, akorát pak některé operace to nechtělo brát dle toho indexu na pozadí. Dotaz typu "select * from tab where tstzrange(casl, casu, casb) && '[ T1, T2]'" nechtěl použít index toho exclude, což při použití normálního range sloupce fungovalo. A historicky je to děláno vše na range, tak jsem nechtěl do toho tak hluboce rejpat.

[Miroslav Šilhavý]

... mají nutkavou potřebu do tabulek lézt nějakým tim table view čumítkem a tyto klikátka chtějí zkrátka primární klíč na tabulce. Bez něj buď odmítnou zobrazit nebo se podivně pošahají, když v tabulce je 500M+ řádků. To je jediný důvod, proč byla snaha přidat primární klíč.

Ok, ale přesně na to by Váš PK nad funkcí nefungoval. Protože by "ty klikátka" měly jiný PK než sloupec, který chtějí zobrazovat. V tomto bodě jste si možná odpověděl sám.

... zbytek rozumím, proto necituji... Řešil bych to tím generated sloupcem, nebo v případě PG9.5 triggerem. (K triggeru by měl být ještě CHECK, aby nějaký chytrák nezaktualizoval ten vytvářený sloupec nezávisle od casl, casu.)

[Filip Jirsák]

Aha, teď konečně ten dotaz dává smysl.

pokud máš nenulový počet uživatelů, co mají nutkavou potřebu do tabulek lézt nějakým tim table view čumítkem a tyto klikátka chtějí zkrátka primární klíč na tabulce. Bez něj buď odmítnou zobrazit nebo se podivně pošahají, když v tabulce je 500M+ řádků. To je jediný důvod, proč byla snaha přidat primární klíč.

Chápu, takže vy tyhle uživatele chcete potrestat. Uznávám, dát jim jako primární klíč složený klíč, jehož polovina bude půlka jiného sloupečku, a ještě to bude datum a čas, to je velmi rafinované. Ještě bych něco udělal s tou první půlku, integer je moc fádní – co takhle nějaký geospatial typ?

Nevýhoda tohohle trestu je to, že trestáte i sám sebe. Pokud se trestat nechcete, o typech SERIAL/BIGSERIAL sám píšete.

Nu, stejná skupina má občas snahu psát dotazy, kde do where nacpou něco jako "lower(casr) between T1 and T2", tak jsem chtěl zabít dvě mouchy jedním klíčem, že by jim to trochu zrychlilo, když nejsou schopni pochopit range operaci typu casr && (T1, T2]. :-)

Pokud by v tom dotazu nebyla i podmínka na smid, ten index by se neuplatnil.

Samozřejmě to jde řešit, že místo casr:tstzrange použít na to 3 samostatné sloupce (casl:timestamp, casu:timestamp, casb:char(2)) a vyrobit si nad tím primary index pomocí (smid, casl, casb) a kontrola nepřekrývání pomocí "exclude using gist (smid with =, tstzrange(casl, casu, casb) with &&)" funguje, akorát pak některé operace to nechtělo brát dle toho indexu na pozadí. Dotaz typu "select * from tab where tstzrange(casl, casu, casb) && '[ T1, T2]'" nechtěl použít index toho exclude, což při použití normálního range sloupce fungovalo. A historicky je to děláno vše na range, tak jsem nechtěl do toho tak hluboce rejpat.

V aktuálních verzích PostgreSQL by to mělo jít řešit generovanými sloupci. Obávám se, že kombinace stará databáze + uživatelé, kteří neumí psát SQL dotazy + velký objem dat je smrtelná a pokud jednu z těch věcí nevyřešíte, volil bych umělý primární klíč i za cenu toho, že tím na disku vznikne nový velký index. Tu verzi databáze ani uživatele jste si asi nevybral, tak si nekomplikujte život indexem, který stejně nikdo neocení u bude dělat problémy vám i těm uživatelům s čumítky.

[Ondrej Nemecek]

Uff, to je nakládačka. :-)

MŠ: rozdíl mezi "primary key" a "index unique" je v tom, že pokud máš nenulový počet uživatelů, co mají nutkavou potřebu do tabulek lézt nějakým tim table view čumítkem a tyto klikátka chtějí zkrátka primární klíč na tabulce. Bez něj buď odmítnou zobrazit nebo se podivně pošahají, když v tabulce je 500M+ řádků. To je jediný důvod, proč byla snaha přidat primární klíč.
(...)

Dotaz typu "select * from tab where tstzrange(casl, casu, casb) && '[ T1, T2]'" nechtěl použít index toho exclude, což při použití normálního range sloupce fungovalo. A historicky je to děláno vše na range, tak jsem nechtěl do toho tak hluboce rejpat.

Nečetl jsem vlákno celé, ale žiju v přesvědčení, že Postgres má Funkční indexy?

[Filip Jirsák]

Nečetl jsem vlákno celé, ale žiju v přesvědčení, že Postgres má Funkční indexy?

Ano, PostgreSQL má funkční indexy. Ale omezení na unikátnost (unique constraint) lze definovat pouze pro sloupce, ne pro výrazy. A primární klíč je jen speciální varianta omezení na unikátnost. Tj. ten index jde definovat, i jako unikátní index, ale nemůžete ho použít pro omezení.

[luvar]

Mihol sa tu geospatial index, tak pre uplnost doplnim moju skusenost.

Usecase: V databaze su cesty vozidiel. Maju cas zaciatku a cas koncu. Casto sa vyhladavali cesty za nejake obdobie (teda, ze v danom obdobi bola vykonavana jazda). Okrajove pripady robili kusok bordel pri beznom dizajne selektu a indexoch. Napriklad cesty za minuly mesiac nezobrazili pri prvej verzii query (lamerska chyba) cesty, ktore trvali cely ten mesiac

Riesenie so znacnym zrychlenim, bolo pouzitie geospatial indexu, kde suradnica X bol zaciatok cesty a suradnica Y bola koniec cesty. Nasledne vyhladavanie bolo hladanie prieniku (intersect) obdlznikov (hladane obdobie verzus obdobie ciest). Bolo to na PostGis-e v postgresql 8.3 a funguje to genialne (zrychlenie cca 10 nasobne oproti zlozenemu indexu, kde sa druhy prvok indexu vlastne nedal pouzit) i pri poctoch riadkov v desiatkach milionov.

Podobne to riesili i tuna, spodna cast odpovede,: https://dba.stackexchange.com/a/39599

[Sam Samovic]

Bádám, zda primární klíč můž být u PgSQL založen na výsledku funkce, ale nedaří se (mám tady PgSQL9.5)?
Mějme tabulku ttt, kde jsou podstatné dva sloupce:
 smid    | integer not null  | ID zdroje
 casr    | tstzrange not null | Interval platnosti dat
jde udělat dvojsloupcový primární klíč nad obojím:
ALTER TABLE ttt  ADD CONSTRAINT "pk-ttt" PRIMARY KEY(smid, casr);
ale pro štastnější život by se mi hodil primární klíč, kde je zahrnut z toho casr jen počáteční čas, což asi nejde, končí jako syntax error?
ALTER TABLE ttt  ADD CONSTRAINT "pk-ttt" PRIMARY KEY(smid, lower(casr));
Normální index tak udělat jde:
CREATE UNIQUE INDEX "inx-ttt-scc" ON ttt(smid,lower(casr),lower_inc(casr));
Primární index se hodí mít a index dle (smid, casr) už tam je přítomen kvůli omezující podmínce na nepřekrývání se dat <CONSTRAINT "const-ttt-smid-casr" EXCLUDE USING gist (smid WITH =, casr WITH &&)>, takže se takto indexovaná data zbytečně duplikují a index založený jen na start (nebo end času) intervalu by se hodil u některých typů dotazů, kdy neumí využít index data z toho constraint.

No a co proste vytvorit dalsi sloupec do nej si ulozit co je treba a ten pak zaindexovat mily watsone ?