O lea, ua matou iloiloina mataupu e faatatau i , ma solomuli solo . Ma mulimuli ane ua matou o'o atu i le vaega e sili ona manaia - o fa'asologa o manoa.
Ulutala
E pei ona uma ona matou fai atu, o laina taʻitasi e mafai ona i ai i le taimi e tasi i le tele o faʻamatalaga i totonu o faʻamaumauga. O le tasi lomiga e tatau ona ese mai le isi. Mo lenei faamoemoe, o lomiga taitasi e lua faailoga e iloa ai le "taimi" o le faatinoga o lenei lomiga (xmin ma xmax). I upusii - aua e le o le taimi lea e faʻaaogaina, ae o se faʻaopoopoga faʻapitoa faʻatau. Ma o lenei fa'atau o le numera o fefa'atauaiga.
(E pei ona masani ai, o le mea moni e sili atu ona faigata: o le numera o fefaʻatauaiga e le mafai ona faʻateleina i taimi uma ona o le faʻatapulaʻaina o le gafatia o le fata.
A faia se laina, ua seti le xmin i le numera o fefaʻatauaiga na tuʻuina atu le INSERT poloaiga, ma le xmax e tuʻu avanoa.
Pe a tape se laina, o le xmax tau o le lomiga o loʻo i ai nei e faailogaina i le numera o le fefaʻatauaiga na faia le DELETE.
Pe a suia se laina i se faʻatonuga UPDATE, e lua gaioiga e faia moni lava: DELETE ma INSERT. O le faʻasologa o loʻo iai nei o le laina seti xmax tutusa ma le numera o fefaʻatauaiga na faia le UPDATE. Ona faia lea o se lomiga fou o lea lava manoa; o lona tau xmin e fetaui ma le tau xmax o le lomiga muamua.
O le xmin ma le xmax fanua o loʻo aofia i le ulutala faʻasologa o laina. I le faaopoopo atu i nei fanua, o le ulutala o loʻo i ai isi, mo se faʻataʻitaʻiga:
- infomask o se fa'asologa o vaega e fa'amatala ai meatotino o lenei fa'asologa. E tele naua; O le a faasolosolo malie ona tatou mafaufau i mea autu.
- ctid o se so'oga i le isi, fa'afou fou o le laina tutusa. Mo le fa'afou fou, pito sili ona lata mai o se laina, o le ctid e fa'asino i lenei lava fa'amatalaga. O le numera e iai le fomu (x,y), o le x o le numera itulau, y o le numera fa'asino i le laina.
- null bitmap - Faailoga na koluma o se faʻamatalaga tuʻuina atu o loʻo i ai se tau aoga (NULL). NULL e le o se tasi o faʻamaumauga masani faʻamaumauga, o lea e tatau ona teu ese le uiga.
O se taunuuga, o le ulutala e fai si tele - a itiiti ifo 23 paita mo vaega taʻitasi o le laina, ma e masani ona sili atu ona o le NULL bitmap. Afai o le laulau e "vati" (o lona uiga, e iai ni nai koluma), e mafai ona sili atu nai lo faʻamatalaga aoga.
fafao
Se'i va'ava'ai toto'a pe fa'afefea ona fa'atinoina le fa'agaioiga o manoa maualalo, amata i le fa'aofiina.
Mo faʻataʻitaʻiga, seʻi o tatou faia se laulau fou e lua koluma ma se faʻailoga i luga o se tasi o latou:
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
Tatou fa'aofi se laina se tasi pe a uma ona amata se fefa'atauaiga.
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
O la matou numera o fefa'atauaiga o lo'o iai nei:
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
Sei o tatou vaavaai i mea o loo i totonu o le itulau. O le galuega heap_page_items o le fa'aopoopoga itulauinspect e mafai ai ona e maua fa'amatalaga e uiga i fa'ailoga ma laina laina:
=> SELECT * FROM heap_page_items(get_raw_page('t',0)) gx
-[ RECORD 1 ]-------------------
lp | 1
lp_off | 8160
lp_flags | 1
lp_len | 32
t_xmin | 3664
t_xmax | 0
t_field3 | 0
t_ctid | (0,1)
t_infomask2 | 2
t_infomask | 2050
t_hoff | 24
t_bits |
t_oid |
t_data | x0100000009464f4f
Manatua o le upu heap i PostgreSQL e faasino i laulau. O le isi lea fa'aoga uiga ese o le fa'aupuga - ua iloa le fa'aputuga , e leai se mea e tutusa ai ma le laulau. O i'i o lo'o fa'aogaina ai le upu i le uiga o le "ua fa'atasia mea uma," e fa'afeagai ma fa'asologa fa'atonu.
O loʻo faʻaalia e le galuega faʻamaumauga "e pei ona i ai", i se faatulagaga e faigata ona malamalama i ai. Ina ia iloa, o le a matou tuua naʻo se vaega o faʻamatalaga ma faʻamalamalamaina:
=> SELECT '(0,'||lp||')' AS ctid,
CASE lp_flags
WHEN 0 THEN 'unused'
WHEN 1 THEN 'normal'
WHEN 2 THEN 'redirect to '||lp_off
WHEN 3 THEN 'dead'
END AS state,
t_xmin as xmin,
t_xmax as xmax,
(t_infomask & 256) > 0 AS xmin_commited,
(t_infomask & 512) > 0 AS xmin_aborted,
(t_infomask & 1024) > 0 AS xmax_commited,
(t_infomask & 2048) > 0 AS xmax_aborted,
t_ctid
FROM heap_page_items(get_raw_page('t',0)) gx
-[ RECORD 1 ]-+-------
ctid | (0,1)
state | normal
xmin | 3664
xmax | 0
xmin_commited | f
xmin_aborted | f
xmax_commited | f
xmax_aborted | t
t_ctid | (0,1)
O le mea lenei na matou faia:
- Fa'aopoopo le zero ile numera fa'asino ina ia foliga tutusa ma t_ctid: (numera itulau, numera fa'asino).
- Fa'ailoa le tulaga o le faasinoala lp_flags. O le "masani" - o lona uiga o le faʻailoga e faasino tonu i le faʻasologa o le manoa. O le a tatou tilotilo i isi uiga mulimuli ane.
- Mai fa'amatalaga uma, e na'o le lua paga ua fa'ailoa mai i le taimi nei. O le xmin_committed ma le xmin_aborted bits o loʻo faʻaalia pe o le numera o fefaʻatauaiga xmin ua faia (faʻaumatia). E lua vaega tutusa e faasino ile numera o fefaʻatauaiga xmax.
O le a le mea ua tatou vaaia? A e faʻaofiina se laina, o le a faʻaalia se numera numera 1 i le itulau o le laulau, faʻasino i le muamua ma naʻo le faʻasologa o le laina.
I le faʻasologa o manoa, o le xmin fanua ua tumu i le numera o fefaʻatauaiga o loʻo iai nei. O loʻo galue pea le fefaʻatauaiga, o lea e le o seti uma le xmin_committed ma le xmin_aborted bits.
Ole row version ctid field e faasino ile laina tutusa. O lona uiga e le o iai se fa'amatalaga fou.
Ua fa'atumu le fanua xmax i se numera fa'atamu 0 ona e le'i tapeina lenei fa'asologa o le laina ma o lo'o iai nei. O fefa'atauaiga o le a le gauai atu i lenei numera ona o le xmax_aborted bit ua seti.
Sei o tatou faia se isi laasaga e faʻaleleia atili le faitau e ala i le faʻaopoopoina o faʻamatalaga faʻamatalaga i numera o fefaʻatauaiga. Ma seʻi o tatou faia se galuega, talu ai o le a tatou manaʻomia le talosaga e sili atu ma le tasi:
=> CREATE FUNCTION heap_page(relname text, pageno integer)
RETURNS TABLE(ctid tid, state text, xmin text, xmax text, t_ctid tid)
AS $$
SELECT (pageno,lp)::text::tid AS ctid,
CASE lp_flags
WHEN 0 THEN 'unused'
WHEN 1 THEN 'normal'
WHEN 2 THEN 'redirect to '||lp_off
WHEN 3 THEN 'dead'
END AS state,
t_xmin || CASE
WHEN (t_infomask & 256) > 0 THEN ' (c)'
WHEN (t_infomask & 512) > 0 THEN ' (a)'
ELSE ''
END AS xmin,
t_xmax || CASE
WHEN (t_infomask & 1024) > 0 THEN ' (c)'
WHEN (t_infomask & 2048) > 0 THEN ' (a)'
ELSE ''
END AS xmax,
t_ctid
FROM heap_page_items(get_raw_page(relname,pageno))
ORDER BY lp;
$$ LANGUAGE SQL;
I lenei fomu, e sili atu ona manino le mea o loʻo tupu i le ulutala o le laina laina:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
E tutusa, ae e matua itiiti lava auiliiliga, o faʻamatalaga e mafai ona maua mai le laulau lava ia, faʻaaoga pseudo-columns xmin ma xmax:
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
Fa'amautu
Afai o se fefaʻatauaiga ua maeʻa manuia, e tatau ona e manatua lona tulaga - ia maitauina o loʻo faia. Ina ia faia lenei mea, o se fausaga e taʻua o le XACT o loʻo faʻaaogaina (ma aʻo leʻi oʻo i le 10 version na taʻua o le CLOG (commit log) ma o lenei igoa e mafai lava ona maua i nofoaga eseese).
O le XACT e le o se laulau fa'amaumauga; o faila ia i le PGDATA/pg_xact directory. E lua a latou vaega mo fefa'atauaiga ta'itasi: fa'atino ma fa'amuta - pei lava o le fa'aulutala o le laina. O nei fa'amatalaga ua vaevaeina i ni faila mo na'o le fa'aogagofie; o le a matou toe fo'i atu i lenei mataupu pe a matou mafaufau e fa'aisa. Ma o le galue i nei faila e faʻatinoina itulau i lea itulau, pei o isi uma.
O lea la, pe a faia se fefaʻatauaʻiga i le XACT, ua setiina le faʻailoga mo lenei fefaʻatauaiga. Ma o mea uma ia e tupu i le taimi o le faia (e ui lava tatou te le o talanoa e uiga i le faʻamaumauga muamua).
A o'o atu se isi fefa'ataua'iga i le itulau o le laulau na matou va'ai i ai, e tatau ona taliina ni fesili.
- Ua mae'a le fefa'ataua'iga xmin? Afai e leai, o lona uiga e le tatau ona iloa le mea na faia o le manoa.
O lenei siaki e faia e ala i le vaʻavaʻai i se isi fausaga, o loʻo i totonu o le manatua faʻatasi o le faʻataʻitaʻiga ma e taʻua o ProcArray. O loʻo i ai se lisi o gaioiga faʻagaioiga uma, ma mo mea taʻitasi o loʻo faʻaalia le numera o ana fefaʻatauaiga o loʻo iai nei (gaoioiga). - Afai ua mae'a, e fa'afefea - e ala i le faia pe fa'aleaogaina? A fa'aleaogaina, e le tatau fo'i ona iloa le fa'asologa o le laina.
Ole mea tonu lea ole XACT. Ae, e ui lava o itulau mulimuli o le XACT o loʻo teuina i totonu o paʻu i le RAM, e taugata lava le siakiina o le XACT i taimi uma. O le mea lea, a maeʻa ona faʻamautuina le tulaga o fefaʻatauaiga, e tusia i le xmin_committed ma xmin_aborted bits o le manoa. Afai e seti se tasi o nei mea, ona iloa lea o le tulaga o fefaʻatauaiga xmin ma o le isi fefaʻatauaiga o le a le tatau ona maua le XACT.
Aisea e le o setiina ai nei vaega e le fefaʻatauaiga lava ia o loʻo faia le faʻaofi? Pe a tupu se faʻaofi, e leʻi iloa e le fefaʻatauaiga pe manuia. Ma i le taimi o le faia, ua le o toe manino po o fea laina na suia ai itulau. Atonu e tele naua itulau, ma e lē aogā le taulotoina. E le gata i lea, o nisi itulau e mafai ona tuliesea mai le faʻamau faʻapipiʻi i le disk; toe faitau i ai e sui ai fasipepa o le a matua faagesegese ai le commit.
O le pito i lalo o tupe teu o le a maeʻa suiga, soʻo se fefaʻatauaiga (e oʻo lava i le faia o se faitauga faigofie - SELECT) e mafai ona amata ona suia itulau faʻamatalaga i totonu o le pusa paʻu.
O lea la, tatou toe faaleleia le suiga.
=> COMMIT;
E leai se mea ua suia i luga o le itulau (ae matou te iloa o le tulaga o fefaʻatauaiga ua uma ona faamaumauina i le XACT):
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
O le taimi nei o le fefaʻatauaiga e maua muamua le itulau e tatau ona fuafua le tulaga o fefaʻatauaiga xmin ma tusi i faʻamatalaga faʻamatalaga:
=> SELECT * FROM t;
id | s
----+-----
1 | FOO
(1 row)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+-------+--------
(0,1) | normal | 3664 (c) | 0 (a) | (0,1)
(1 row)
Aveese
A tape se laina, o le numera o le tapeina o fefaʻatauaiga o loʻo i ai nei e tusia i le xmax fanua o le lomiga o loʻo i ai nei, ma le xmax_aborted bit ua kilia.
Manatua o le tau seti o le xmax e fetaui ma le fefaʻatauaʻiga gaioiga galue e pei o se loka laina. Afai e manaʻo se isi fefaʻatauaiga e faʻafou pe tape lenei laina, o le a faʻamalosia e faʻatali mo fefaʻatauaiga xmax e maeʻa. O le a tatou talanoa atili e uiga i le polokaina mulimuli ane. Mo le taimi nei, matou te maitauina o le numera o laina loka e le faʻatapulaʻaina. Latou te le faʻaogaina avanoa i le RAM ma le faʻatinoga o le polokalama e le afaina i la latou numera. E moni, o fefaʻatauaiga "umi" e iai isi faʻaletonu, ae sili atu i lena mulimuli ane.
Tatou tape le laina.
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
Matou te vaʻaia o le numera o fefaʻatauaiga o loʻo tusia i le xmax fanua, ae o faʻamatalaga faʻamatalaga e le o setiina:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
sologa
Fa'ato'ilalo suiga e tutusa lelei ma le fa'atinoina, na'o le XACT ua fa'atulaga le bit ua fa'amuta mo le fefa'atauaiga. O le faaleaogaina e vave lava e pei o le faia. E ui lava o le poloaiga e taʻua o ROLLBACK, e le toe faʻafoʻiina suiga: o mea uma na mafai e le fefaʻatauaiga ona suia i itulau faʻamatalaga e tumau pea e le suia.
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
A maua le itulau, o le a siaki le tulaga ma o le xmax_aborted faʻailoga faʻailoga o le a seti i le laina laina. O le numera xmax lava ia e tumau i luga o le itulau, ae leai se tasi e tilotilo i ai.
=> SELECT * FROM t;
id | s
----+-----
1 | FOO
(1 row)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+----------+--------
(0,1) | normal | 3664 (c) | 3665 (a) | (0,1)
(1 row)
Faʻafou
O le fa'afouga e galue e pei na muamua tapeina le fa'asologa o lo'o i ai nei o le laina ona fa'aofi lea o se mea fou.
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
O le fesili e maua ai le laina e tasi (faiga fou):
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
Ae i luga o le itulau matou te vaʻai i faʻasologa uma e lua:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+-------+--------
(0,1) | normal | 3664 (c) | 3666 | (0,2)
(0,2) | normal | 3666 | 0 (a) | (0,2)
(2 rows)
O le kopi faʻamalo ua faʻailogaina i le numera o fefaʻatauaiga o loʻo i ai nei i le xmax fanua. E le gata i lea, o lenei tau o loʻo tusia i luga o le tuai, talu ai na faʻaleaogaina le fefaʻatauaiga muamua. Ma o le xmax_aborted bit ua faʻamaonia ona o le tulaga o le fefaʻatauaiga o loʻo iai nei e leʻi iloa.
O le vaega muamua o le laina o loʻo faʻasino nei i le lona lua (t_ctid field) o le mea fou.
O le fa'asinomaga lona lua o lo'o fa'aalia i le itulau fa'asino ma le laina lona lua o lo'o fa'asino i le lomiga lona lua i le itulau o le laulau.
E pei lava o le tapeina, o le tau xmax i le vaega muamua o le laina o se faʻaaliga o loʻo loka le laina.
Ia, tatou fa'auma le fefa'ataua'iga.
=> COMMIT;
Faasino igoa
E o'o mai i le taimi nei ua na'o tatou talanoa e uiga i itulau laulau. O le a le mea e tupu i totonu o fa'ailoga?
O fa'amatalaga i itulau fa'asinomaga e matua'i eseese e fa'atatau i le ituaiga fa'asino tonu. Ma e oo lava i le tasi ituaiga o faasino igoa e eseese ituaiga o itulau. Mo se faʻataʻitaʻiga, o le B-tree o loʻo i ai se itulau metadata ma itulau "masani".
Ae ui i lea, o le itulau e masani ona i ai le tele o faʻailoga i laina ma laina lava latou (pei o se itulau laulau). E le gata i lea, i le pito o le itulau o loʻo i ai avanoa mo faʻamatalaga faʻapitoa.
O laina i fa'ailoga e mafai fo'i ona 'ese'ese fausaga fa'atatau i le ituaiga fa'asino. Mo se fa'ata'ita'iga, mo le B-la'au, o laina e feso'ota'i ma itulau laulaau o lo'o i ai le tau fa'asino igoa ma se fa'amatalaga (ctid) i le laina laulau fetaui. I se tulaga lautele, o le faasino igoa e mafai ona faʻatulagaina i se auala e matua ese lava.
O le mea pito sili ona taua o le leai o ni laina laina i fa'asino igoa o so'o se ituaiga. Ia, pe mafai foi ona tatou manatu o laina taʻitasi o loʻo faʻatusalia e le tasi le faʻaaliga. I se isi faaupuga, e leai ni xmin ma xmax fanua i le ulutala laina fa'asino. E mafai ona tatou fa'apea o feso'ota'iga mai le fa'asinomaga e ta'ita'i atu ai i fa'asologa uma o laulau o laina - o lea e mafai ai ona e iloa po'o le a le fa'aaliga o le a va'aia e le fefa'atauaiga i le va'ai i le laulau. (E pei lava o taimi uma, e le o le mea moni atoa lea. I nisi tulaga, o le faʻafanua vaʻaia e mafai ona faʻamalieina le faagasologa, ae o le a tatou vaʻavaʻai atili i lenei mea mulimuli ane.)
I le taimi lava e tasi, i le itulau faʻasino matou te maua ai faʻamatalaga i itu uma e lua, o le taimi nei ma le tuai:
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
Fefa'ataua'iga fa'apitoa
I le faʻataʻitaʻiga, PostgreSQL faʻaogaina faʻataʻitaʻiga e mafai ai ona "sefe" numera o fefaʻatauaiga.
Afai o se fefa'atauaiga na'o le faitauina o fa'amatalaga, e leai se a'afiaga i le va'aia o laina laina. O le mea lea, o le faʻagasologa o auaunaga muamua e tuʻuina atu se virtual xid i le fefaʻatauaiga. O le numera e aofia ai se ID faʻagasologa ma se numera faʻasologa.
O le tuʻuina atu o lenei numera e le manaʻomia ai le faʻamaopoopoina i le va o gaioiga uma ma o lea e vave tele. O le a tatou masani i se isi mafuaʻaga mo le faʻaogaina o numera numera pe a tatou talanoa e uiga i le malulu.
O numera faʻapitoa e le o amanaia i soʻo se auala i faʻamatalaga faʻamatalaga.
I taimi eseese i le taimi, atonu e iai ni fefaʻatauaiga faʻapitoa i totonu o le polokalama ma numera ua uma ona faʻaaogaina, ma e masani lava. Ae o lea numera e le mafai ona tusia i itulau o faʻamatalaga, aua o le isi taimi e maua ai le itulau e ono leiloa uma uiga.
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
Afai o se fefaʻatauaiga e amata ona suia faʻamatalaga, e tuʻuina atu i ai se numera moni, tulaga ese.
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
Fa'atauga Fa'atauga
Faasaoina togi
Fa'amatalaina ile SQL faasaoina togi (savepoint), lea e mafai ai ona e faalēaogāina se vaega o se fefaʻatauaiga e aunoa ma le faʻalavelave atoa. Ae e le fetaui lenei mea i le ata o loʻo i luga, talu ai o le fefaʻatauaiga o loʻo i ai le tulaga tutusa mo ona suiga uma, ma le tino e leai se faʻamatalaga e toe faʻafoʻi.
Mo le fa'atinoina o lenei fa'atinoga, o se fefa'ataua'iga ma se nofoaga fa'asao e vaevae i ni vaega eseese fefa'ataua'iga fa'anofo (subtransaction), o le tulaga lea e mafai ona pulea eseese.
O fefa'ataua'iga fa'atosina e iai a latou lava numera (maualuga atu nai lo le numera o fefa'atauaiga autu). O le tulaga o fefaʻatauaiga faʻapipiʻi o loʻo faʻamauina i le auala masani i le XACT, ae o le tulaga mulimuli e faʻalagolago i le tulaga o le fefaʻatauaʻiga autu: afai e faʻaleaogaina, ona faʻamalo uma ai lea o fefaʻatauaiga.
O fa'amatalaga e uiga i le fa'aputuga o fefa'atauaiga o lo'o teuina i faila i le PGDATA/pg_subtrans directory. O faila e mafai ona maua e ala i paʻu i le faʻataʻitaʻiga faʻasoa, faʻatulagaina i le auala lava e tasi e pei o le XACT buffers.
Aua le fenumia'i fefa'ataua'iga fa'aaufa'atasi ma fefa'ataua'iga tuto'atasi. O fefaʻatauaiga tutoʻatasi e le faʻalagolago le tasi i le isi i soʻo se auala, ae o fefaʻatauaiga faʻatau. E leai ni fefaʻatauaʻiga tutoʻatasi i PostgreSQL masani, ma, atonu, mo le mea sili: e manaʻomia tele, e seasea lava, ma o lo latou i ai i isi DBMS e faʻaosofia ai le faʻaleagaina, lea e mafatia ai tagata uma.
Tatou fa'amama le laulau, amata se fefa'atauaiga ma fa'aofi le laina:
=> TRUNCATE TABLE t;
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3669 | 0 | 2 | FOO
(1 row)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3669 | 0 (a) | (0,1)
(1 row)
Se'i o tatou tu'u se togi fa'asao ma fa'aofi se isi laina.
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
Manatua o le xxid_current() galuega e toe faʻafoʻi ai le numera autu o fefaʻatauaiga, ae le o le numera o fefaʻatauaiga.
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3669 | 0 | 2 | FOO
3670 | 0 | 3 | XYZ
(2 rows)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3669 | 0 (a) | (0,1)
(0,2) | normal | 3670 | 0 (a) | (0,2)
(2 rows)
Se'i tatou toe fo'i i tua i le mea e teu ai ma fa'aofi le laina lona tolu.
=> ROLLBACK TO sp;
=> INSERT INTO t(s) VALUES ('BAR');
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3669 | 0 | 2 | FOO
3671 | 0 | 4 | BAR
(2 rows)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+-------+--------
(0,1) | normal | 3669 | 0 (a) | (0,1)
(0,2) | normal | 3670 (a) | 0 (a) | (0,2)
(0,3) | normal | 3671 | 0 (a) | (0,3)
(3 rows)
I le itulau o loʻo faʻaauau pea ona matou vaʻai i le laina faʻaopoopo e le faʻaleaogaina o fefaʻatauaiga.
Matou te faaleleia suiga.
=> COMMIT;
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3669 | 0 | 2 | FOO
3671 | 0 | 4 | BAR
(2 rows)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+-------+--------
(0,1) | normal | 3669 (c) | 0 (a) | (0,1)
(0,2) | normal | 3670 (a) | 0 (a) | (0,2)
(0,3) | normal | 3671 (c) | 0 (a) | (0,3)
(3 rows)
Ole taimi nei e mafai ona e vaʻaia manino o fefaʻatauaiga taʻitasi e iai lona lava tulaga.
Manatua e le mafai ona fa'aoga manino fefa'ataua'iga i le SQL, o lona uiga, e le mafai ona e amataina se fefa'ataua'iga fou e aunoa ma le fa'amae'aina o le taimi nei. O lenei masini e fa'aagaoioia fa'apitoa pe a fa'aogaina mea fa'asao, fa'apea fo'i pe a fa'atautaia tu'usuga PL/pgSQL ma i le tele o isi mataupu e sili atu.
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
Mea sese ma le atomicity o gaioiga
O le a le mea e tupu pe a tupu se mea sese a'o faia se taotoga? Mo se faʻataʻitaʻiga, pei o lenei:
=> BEGIN;
=> SELECT * FROM t;
id | s
----+-----
2 | FOO
4 | BAR
(2 rows)
=> UPDATE t SET s = repeat('X', 1/(id-4));
ERROR: division by zero
Ua tupu se mea sese. O lea la ua manatu ua fa'amuta le fefa'ataua'iga ma e leai se gaioiga e fa'atagaina i totonu:
=> SELECT * FROM t;
ERROR: current transaction is aborted, commands ignored until end of transaction block
Ma e tusa lava pe e te taumafai e fai suiga, PostgreSQL o le a lipotia se faʻamaʻi:
=> COMMIT;
ROLLBACK
Aisea e le mafai ai ona faʻaauau se fefaʻatauaiga pe a maeʻa le toilalo? O le mea moni o se mea sese e mafai ona tulaʻi mai i se auala e mafai ai ona tatou maua le avanoa i se vaega o suiga - o le atomicity e le oʻo lava i le fefaʻatauaiga, ae o le tagata faʻatautaia o le a solia. E pei o la tatou faʻataʻitaʻiga, lea na mafai ai e le tagata faʻapipiʻi ona faʻafouina le laina se tasi aʻo leʻi faia le mea sese:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+-------+--------
(0,1) | normal | 3669 (c) | 3672 | (0,4)
(0,2) | normal | 3670 (a) | 0 (a) | (0,2)
(0,3) | normal | 3671 (c) | 0 (a) | (0,3)
(0,4) | normal | 3672 | 0 (a) | (0,4)
(4 rows)
E tatau ona fai mai o le psql o loʻo i ai se faiga e faʻatagaina ai le fefaʻatauaiga e faʻaauau pea pe a maeʻa le toilalo e pei o gaioiga a le tagata faʻaoga sese na toe foʻi i tua.
=> set ON_ERROR_ROLLBACK on
=> BEGIN;
=> SELECT * FROM t;
id | s
----+-----
2 | FOO
4 | BAR
(2 rows)
=> UPDATE t SET s = repeat('X', 1/(id-4));
ERROR: division by zero
=> SELECT * FROM t;
id | s
----+-----
2 | FOO
4 | BAR
(2 rows)
=> COMMIT;
E le faigata ona mate i lenei faiga, o le psql o loʻo tuʻuina moni lava se mea faʻasao i luma o poloaiga taʻitasi, ma i le tulaga o le toilalo e amata ai le toe foʻi i ai. O lenei faiga e le fa'aaogaina e ala i le fa'aletonu, talu ai o le fa'atulagaina o nofoaga fa'asao (tusa lava pe le toe fa'afo'i i ai) e aofia ai le tele o luga.
puna: www.habr.com