Dlje časa pogrešana funkcija v PostgreSQL je bil "UPSERT" (skrajšano za "INSERT, ON CONFLICT UPDATE"), ki omogoča, da aplikacija z istim SQL stavkom obravnava nove in obstoječe zapise. To precej poenostavi logiko same aplikacije, saj je preverjanje konfliktne situacije izvedeno v podatkovni bazi, tako da se zagotovi atomičnost spremembe. Uporabniki MySQL to poznajo kot INSERT ON DUPLICATE KEY UPDATE.
Z različico 9.5 je podpora za JSON nadgrajena, da je sedaj možno podatke v JSONB poljih spreminjati s funkcijami v podatkovni bazi - dodajati, nastaviti in brisati elemente znotraj JSON objektov, tako da ni več potrebno JSON objekta spreminjati v zunanji aplikaciji. Dobrodošla je tudi funkcija json_pretty, ki JSON objekt izpiše na bolj čitljiv način.
Pomembna novost je možnost kontrole dostopa do podatkov na nivoju vrstic in stolpcev (t.i. row level security), ki omogoča omejitve posameznim uporabnikom, da lahko dostopajo zgolj do določenih vrstic oz. polj zapisov. To je uporabna lastnost, ko ima sistem opravka z občutljivimi podatki, npr. v zdravstvu.
PostgreSQL je dobil tudi nov način indeksiranja BRIN, kar je okrajšava za block range index. BRIN indeks povzame več podatkovnih blokov tabele v minimalno in maksimalno vrednost, tako da je zelo kompakten, za občutek: indeks nad 650 MB podatkov ima le 64KB. To pride posebej prav pri zelo velikih tabelah, pri katerih bi bil običajni btree indeks preobsežen, BRIN pa kljub velikemu obsegu podatkov še vedno omogoči učinkovit dostop do želenih zapisov. BRIN indeksi so zaradi načina delovanja posebej primerni za stolpce, katerih vrednost injektivno narašča ali pada oz. kjer obstaja nek z ozirom na dodajanje zapisov v tabelo "naraven" red.
PostgreSQL po novem z uporabo t.i. okrajšanih ključev (abbreviated keys) hitreje sortira vrednosti tipa text in numeric. Namesto primerjanja celotnega besedila se tako primerja le prvih nekaj znakov, kar operacijo precej pospeši. To je pomembno, ker se sortiranje veliko uporablja, tudi npr. pri izdelavi indeksov in agregaciji. Ena primerjava kaže, da izgradnja indeksa nad tekstovnim poljem v tabeli z 18 milijoni vrstic z različico 9.4 traja 10 minut in 19 sekund, z 9.5 pa samo 51 sekund, kar je kar 12-krat hitreje. Tipična pohitritev je nekje okrog 3-krat.
Za povezovanje na zunanje baze so na voljo t.i. foreign data wrappers, ki po novem omogočajo enostavnejši uvoz kar vseh v povezanem sistemu vidnih tabel z uporabo IMPORT FOREIGN SCHEMA. Po novem so lahko zunanje tabele vključene tudi v dedovanje. Nova pa je tudi podpora za izvajanje JOIN operacij na povezanem sistemu, kar lahko naredi poizvedbe učinkovitejše.
Analitikom bodo prav prišli novi SQL ukazi CUBE, ROLLUP in GROUPING SETS, ki omogočajo naprednejše grupiranje podatkov tudi po več dimenzijah hkrati z enim prehodom čez podatke in tako omogočajo učinkovitejšo izdelavo OLAP kock. Za vzorčenje tabel je na voljo nov ukaz TABLESAMPLE, ki omogoča, da analitik zajame le določen odstotek vseh vrstic, ter pri razvoju hitreje preiskuša svoje ideje.
Administratorji se bodo razveselili orodja pg_rewind, ki omogoča, da pri uporabi replikacije po menjavi primarnega strežnika stari primarni strežnik hitreje privedejo v vlogo sekundarnega, brez da bi ga morali obnoviti iz rezervne kopije.
Še nekatere omembe vredne novosti so:
Z verzijo 9.5 je možno spremeniti beleženje sprememb na tabeli z ukazom ALTER TABLE SET LOGGED/UNLOGGED. Tabele, ki se ne beležijo, so hitrejše, ampak ob sesutju strežnika izgubijo vse zapise.
Optimizacijo, kjer PostgreSQL vrne rezultate kar iz indeksa, če vsebuje vsa zahtevana polja, sedaj podpirajo tudi GiST indeksi.
V postgresql.conf je nov parameter cluster_name, ki služi kot identifikator v seznamu procesov, kar pride prav, če na strežniku teče več različnih instanc PostgreSQL.
Če se pri poizvedbi zatipkate v imenu stolpca, vam bo PostgreSQL predlagal podobne stolpce.
PostgreSQL se z novostmi s podporo JSONB in UPSERT širi v NoSQL sfere, hkrati pa ima različica 9.5 veliko dobrodošlih novosti za podporo analitiki: BRIN indekse, dodatne načine grupiranja ter vzorčenje podatkov. Večje novosti so s primeri opisane tudi na PostgreSQL wikiju.
Kako so kaj porihtali delo s transakcijami? To, da mi ustavi transakcijo, če pride recimo do nekritičnih napak (podvajanje ključev) je bil zame šovštoper. V taglavnih ostalih bazah je to rešeno kot se spodobi. Pa embedded verzija?
Transakcije majo še zmeraj isti "problem". Embedded verzije ni.
To je res škoda. Baza je drugače enterprise level, za transakcije pa mi izgleda, kot da bi Oracle financiral, da se ta osnovna funkcionalnost ne reši. :)
@trstenjak, nebivedu A imata kak primer kaj točno mislita, al pa če polinkata na Oracle dokumentacijo?
Embedded verzije sicer ni, obstaja pa zato SQLite, ki dela na skoraj vsakem OS.
Start transaction insert data OK ... insert data ERR: duplicate key (tukaj Postgresql prekine transacijo, ne moreš vsč vstavljati v bazo, pri ostalih bazah lahko nadaljuješ) ... insert data OK Commit transaction
Meni recimo napaka pri vstavljanju ne pomeni vedno, da je to res napaka, ampak bolj info, da zapis obstaja in bi znotraj transakcije izvedel kakšno drugo varianto vstavljanja. Za embedded pa je tudi tako, da se želim izogniti razvoju in testiranju na dveh bazah.
Kaj je to okol transakcij? Moti vas ker napaka pomeni napako al ker ne pomeni napake?
Oracle, MsSQL, Firebird in vsi ostali s transakcijami delajo na ta način, Samo Postgresql ima to rešeno po svoje. @Hruske, imaš prav, Autocommit odpade, to varianto s SavePoint pa sem dejansko z wrapperjem okoli vsakega klica uporabil. Ampak ni to to, zato me zanima, če bodo to že enkrat rešili. Če pišeš aplikacijo za več baz, moraš pri ostalih bazah menjati samo driver, tukaj pa še logiko.
trstenjak, torej, če te prav razumem, druge baze vržejo error, ampak transakcija potem ni v aborted stanju, zato lahko pač ponoviš (malo drugačen) query.
To se sliši kot to, da raje delaš error checking kot exception handling. Pomojem tega PG developerji ne smatrajo kot defekt, ker se zna komu zgodit, da pozabi preverjat za napake, potem pa ima baza napačne podatke, tega pa se vsaj PG developerji bojijo ko hudič križa.
MMG, tudi na MSSQL si lah vklopiš ta način dela s SET XACT_ABORT ON.
Kalkulator nove omrežnine 2024 - https://omreznina.karlas.si/Kalkulator
Mora delovat. Ob kakršnem koli konfkliktu ključa lahko rečeš, da se podatki posodobijo ali pa se poizvedba ignorira (ON CONFLICT IGNORE). Je pa zadeva nova tud zame, postgresql sem skompajlal pri sebi šele dva dni nazaj.
Pozabi mysql, ker je defektna baza. Oracle uniči vse, česar se dotakne.
Mogoče se vam zdi, da je podvojen ključ mala napaka. Dejansko je to zelo huda napaka, ker če bi to dovolil, bi bila baza v nekonsistentnem stanju.
To nisi najboljše razumel. Enkrat gre za konsistenco baze, jasno da baza ne sme dovoliti podvojenih ključev. Govorim samo o življenskem ciklu transakcije. Ni vsaka napaka takšna, da bi se morala transakcija prekiniti. Če iščem record v bazi in ga ne najde, potem ne želim, da mi prekine transakcijo. Za program je to samo info, kako nadaljevati logiko. Če imaš aplikacijo napisano za Oraclu, v njej 3000 queryev, potem ni šanse, da grem to prepisovati na novo. Postgresql mi je zelo všeč in sem želel aplikacijo migrirati, ampak se je tu ustavilo.
trstenjak, ti ne razumeš. Če narediš nedovoljeno operacijo je OK, če se transakcija prekine. Vstavljanje podvojenega ključa to je, ker če vstavljanje samo ignoriraš, so lahko kasnejši stavki kritični in povzročijo katastrofo. Transakcija mora biti konsistentna (A v ACID), torej vse ali nič.
Ideja zadaj je, da je bolje biti bolj defenziven in preprečiti kakšno katastrofo ali pa neko izredno situacijo. Recimo na banki, najprej vstavijo vrstico o bančni transkaciji v zapisnik, potem pa odštejejo denar. Kaj se zgodi, če spodleti vstavljanje v zapisnik (zaradi nekega podvojenega ključa) in se samo denar odšteje? Katastrofa!
Ideja je, da je bolje dodatna varnost kot podpiranje lenobe programerja, ki se mu ne da naštudirati upserta. Ker če nekdo uporabi UPSERT, potem je to namig bazi, da je bilo planirano s strani programerja, da se lahko tu zgodi napaka, pa gre transakcija še vseeno skozi.
@Tehnolog, Razumem, kaj želiš povedati. Me pa zanima, če si mnenja, da imajo posledično DB2, Oracle in številne druge baze to napačno implementirano, Postgresql pa edini pravilno?
Moti ga to, da ko znotraj transakcije baza naleti na "fatalno napako" recimo duplicate key. Ti aborta trenutno transakcijo.. in posledično s tem pade celotna transakcija.
si.Mail odprto-kodni odjemalec elektronske pošte. - http://www.simail.si
Uredite si svojo zbirko filmov, serij in iger - http://xcollect.sf.net
Očitno sm že fasal fah-idiotizem in se mi zdi takšno obnašanje povsem pravilno. Če je napaka, se transakcija mora prekinit in izpuhtet. Če za neko napako nočem da mi prekine pesmico, jo morm pač vnaprej ustrezno shendlat.
Moja praksa je da v takšnih primerih skodiram posamezne BEGIN..END bloke kjer se najprej proba update, in če ne poupdajta ničesar, naredi INSERT. Nekakšen doma spacan "UPSERT". Oraclov MERGE mi ni pisan na kožo.
Če oraclu kej zamerim, mu zamerim napake kjer zahteva eksplicitni rollback. Če maš dost štosno kodo, ki vedno naredi commit in se ne sekira če kej ne rata, takšna prekinitev obesi server. Pustmo kolk blesavo je vedno delat commit, blesavo je, da programerska napaka dobesedno obesi server. Problem.
DB2 po drugi strani pove kodo napake in se ti odločaš, kaj boš naredil ali boš vse prekinil ali pa boš nadaljeval in če se pravilno sprašuješ po kodi napake, ti ni treba prekinit transakcije. Recimo delaš transakcijo z insertom, ker ključ že obstaja ti vrže kodo duplicate key in namesto inserta potem narediš update.
Recimo primer obrnjene zadeve. Delam update vrstice in če je koda 100 (ni najden), naredim potem insert.
Ja, samo tukaj, ker transakcija ni uspela, ti ne "brejkne", pri postgresu pa je meni ven metalo vse skupaj in naredi break, če transakcija ne uspe. V bistvu za not-found (+100) še ne, ampak za duplicate-index (-803) pa.
Ja, samo tukaj, ker transakcija ni uspela, ti ne "brejkne", pri postgresu pa je meni ven metalo vse skupaj in naredi break, če transakcija ne uspe. V bistvu za not-found (+100) še ne, ampak za duplicate-index (-803) pa.
Če prav razumem, v postgresu torej ne moreš lovit DUP_VAL_ON_INDEX exceptionov?
Se enkrat si preberite tisti del o exception in o error handlingu, k ga je napisal hruske. Tle gre za dva razlicna nacina kako se tretira duplicate key error in kaj to pomeni za transakcijo. Gre samo za to na katerem nivoju bos reseval napako.
IMO, ce hoces narest insert in stvar crkne, potem naj crkne transakcija. Telovadba znotraj transakcije ti lahko samo zakomplicira zivljenje. Se vedno moras imeti handling za tiste transakcije, ki so pa _res_ crknile, po neki drugi definiciji. Tko sam drobis stvari, ene napake lovis na nivoju baze, druge pa na nivoju programa.
Ker ne poznam postgresqla....try cacth blokov nima, ki bi jih uporabil znotraj transakcije al dejansko tud v tem primeru aborta? Ker again zame je taka napaka dovolj dober razlog, da se ce ni exception handlan prekine transakcijo. Duplicate key je zame ena izmed top napak, ki mi pive, da je nekaj ze v osnovi narobe spisano. Jaz bi vtem primeru dejansko želel met abortano transakcijo. Zdej če bi mel kodo s 3000 vrsticami ni me nekaj znotraj transakcije ne bi motilo mas verjetno tud kaksne vgnezdene autonomne transakcije? Ne verjamem, da ni neke rešitve za ta problem(poleg tega, da se popravi kodo in prepreči podvajanje zapisov...pa magar z insert or update oz "upsert", če se ne zna drugače).
Ker ne poznam postgresqla....try cacth blokov nima, ki bi jih uporabil znotraj transakcije al dejansko tud v tem primeru aborta? Ker again zame je taka napaka dovolj dober razlog, da se ce ni exception handlan prekine transakcijo. Duplicate key je zame ena izmed top napak, ki mi pive, da je nekaj ze v osnovi narobe spisano. Jaz bi vtem primeru dejansko želel met abortano transakcijo. Zdej če bi mel kodo s 3000 vrsticami ni me nekaj znotraj transakcije ne bi motilo mas verjetno tud kaksne vgnezdene autonomne transakcije? Ne verjamem, da ni neke rešitve za ta problem(poleg tega, da se popravi kodo in prepreči podvajanje zapisov...pa magar z insert or update oz "upsert", če se ne zna drugače).
Vedno prekine transakcijo. Kaj pa CONSTRAINT? Logika omejitve vrednosti je zapisana v bazi in jo je nesmiselno implementirati še v aplikaciji. Ima pa iste posledice kot duplicate key, napako, ki prekine transakcijo. Ali naj pri importu zilijon zapisov zaradi teoretično ene napake ponavljam uvoz uro ali več in potem pri naslednji napaki CONSTRAINT zopet popravim in import na novo? Ne, napravil bi si log in potem po logu reševal problematične zapise. Zato meni ta logika prekinitev transakcije ne štima. In še enkrat, lastnosti in omejitve polj morajo biti shranjeni v bazi, v aplikaciji jih ne želim še enkrat programirati in vzdrževati.
Ali ni ravno v tem smisel transakcije? Če se tvoja koda zanaša na to, da bo to novi unikaten zapis in bo naprej v transakciji delal temu primerne operacije, je break in nato rollback transakcije edina prava pot.
Če pa že v naprej veš, da bi lahko že obstajal zapis pa pač uporabiš upsert oziroma ekvivalent.
trstenjak, pa saj sem ti jaz že razložil, pa še ales85 ti je razložil ponovno.
Transakcija je atomična, torej uspejo vsi inserti ali pa nobeden. Če en ne bi uspel zaradi zaradi nekega constrainta, potem se transkacija razveljavi. Naj me koklja brcne, če oracle baze ne delajo povsem na isti način.
trstenjak, pa saj sem ti jaz že razložil, pa še ales85 ti je razložil ponovno.
Transakcija je atomična, torej uspejo vsi inserti ali pa nobeden. Če en ne bi uspel zaradi zaradi nekega constrainta, potem se transkacija razveljavi. Naj me koklja brcne, če oracle baze ne delajo povsem na isti način.
Ne, ne dela na isti način. (pod pogojem da je zgornji opis PostgreSQL pravilen)
Tole:
create table t (a integer not null);
insert into t values (11);
insert into t values (null);
commit;
insert into t values (22);
commit;
select a from t;
Na Oracle bazi vrne dve vrstici, eno z 11 in eno z 22. Po opisih zgoraj pa bi PostgreSQL vrnil le 22, ker bi prvo transakcijo rollback-al.
Motiti se je človeško.
Motiti se pogosto je neumno.
Vztrajati pri zmoti je... oh, pozdravljen!
Ali pa vsaj daj AUTOCOMMIT na OFF. Dammit, ste na šihtih tud tko nesposobni?
Lol, kdo ima autocommit po defaultu na ON? Izgleda, da samo jako sposobni. Ostali delamo s transakcijami.
Večina baz ima AUTOCOMMIT na ON. Se pravi če eksplicitno ne štartaš transakcije, potem je vsak stavek svoja stransakcija. To pojasni rezultate, ki jih ima MrStein.
Tako da, ali izklopi AUTOCOMMIT ali pa začne transakcije z BEGIN, bi moral dobit rollback.
1.) BEGIN sploh ni SQL ukaz 2.) Ni AUTOCOMMIT 3.) Če vam je kaj sumljivo PROBAJTE SAMI, namesto da si sproti izmišljujete. Mislim, kot da bi se pogovarjal s petletniki... 4.) Ja, tako je kot sem napisal. Preverjeno in še enkrat preverjeno. Res, ni zarote odzadaj.
Motiti se je človeško.
Motiti se pogosto je neumno.
Vztrajati pri zmoti je... oh, pozdravljen!
The following SQL-statements are transaction initiating SQL-statements, i.e., if there is no current transaction, and a statement of this class is executed, a transaction is initiated: ... - <insert statement> ...
Ter:
An SQL-transaction is terminated by a <commit statement> or a <rollback statement>.
1.) BEGIN sploh ni SQL ukaz 2.) Ni AUTOCOMMIT 3.) Če vam je kaj sumljivo PROBAJTE SAMI, namesto da si sproti izmišljujete. Mislim, kot da bi se pogovarjal s petletniki... 4.) Ja, tako je kot sem napisal. Preverjeno in še enkrat preverjeno. Res, ni zarote odzadaj.
Ravno tako kot imaš SELECT stavek imaš tudi BEGIN stavek.
Torej Oracle ni ravno dober primer dobre baze. Ravnokar se videl, da se naredi COMMIT tudi ob prekinitvi povezave.
"A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed. The commit behavior when a user disconnects is application-dependent and configurable."
> SELECT tudi ni SQL ukaz? Je. Zakaj ne bi bil? Lahko bi malo bolj obrazložil svoje besede, da ne ping-pongamo 10 dni.
> Ravno tako kot imaš SELECT stavek imaš tudi BEGIN stavek. Nope. Obstajajo pa na raznih DBMS razni nestandardni ukazi ala START TRANSACTION, BEGIN WORK itd... Kar je vse nerelevantno, ker nima veze s podanim primerom.
> Torej Oracle ni ravno dober primer dobre baze. Ni bil podan kot primer dobre baze, ampak kot primer standardnega obnašanja, ki si ga deli še z marsikatero drugo bazo.
> Ravnokar se videl, da se naredi COMMIT tudi ob prekinitvi povezave. Kot si sam ugotovil, je to odvisno od klienta in je nastavljivo. (in tudi nima veze z obnašanjem transakcij, o katerem se pogovarjamo)
Motiti se je človeško.
Motiti se pogosto je neumno.
Vztrajati pri zmoti je... oh, pozdravljen!
The following SQL-statements are transaction initiating SQL-statements, i.e., if there is no current transaction, and a statement of this class is executed, a transaction is initiated: ... - <insert statement> ...
Ter:
An SQL-transaction is terminated by a <commit statement> or a <rollback statement>.
Zdaj če ene baze delajo drugače, potem pač delajo drugače (beri: nestandardno).
Iz istega vira:
When a constraint is checked other than at the end of an SQL- transaction, if it is not satisfied, then an exception condition is raised and the SQL-statement that caused the constraint to be checked has no effect other than entering the exception information into the diagnostics area. When a <commit statement> is executed, all constraints are effectively checked and, if any constraint is not satisfied, then an exception condition is raised and the transaction is terminated by an implicit <rollback statement>.
Po tem sodeč bi dejal, da če baza po commitu ohrani zapise, ki so se izvajali v transakciji, v kateri je prišlo do napake (pred ali po napaki), da se torej ne drži standarda.
"[...] the advance of civilization is nothing
but an exercise in the limiting of privacy."
- Isaac Asimov
