No laila, ua noʻonoʻo mākou i nā mea pili i , a hoʻi i hope . A i ka hopena ua hiki mākou i ka mea hoihoi loa - nā mana string.
Kāleka
E like me kā mākou i ʻōlelo mua ai, hiki i kēlā me kēia lālani ke noho i nā ʻano he nui i ka waihona. Pono e ʻokoʻa kekahi mana mai kekahi mana. I nā huaʻōlelo - no ka mea, ʻaʻole ia ka manawa e hoʻohana ʻia ai, akā he mea hoʻonui kūikawā. A ʻo kēia counter ka helu kālepa.
(E like me ka mea maʻamau, ʻoi aku ka paʻakikī o ka ʻoiaʻiʻo: ʻaʻole hiki ke hoʻonui ʻia ka helu kūʻai i nā manawa āpau ma muli o ka liʻiliʻi liʻiliʻi o ka helu helu.
Ke hana ʻia kahi lālani, hoʻonohonoho ʻia ʻo xmin i ka helu kālepa i hoʻopuka i ke kauoha INSERT, a waiho ʻia ʻo xmax.
Ke holoi ʻia kahi lālani, hōʻailona ʻia ka waiwai xmax o ka mana o kēia manawa me ka helu o ka hana i hana i ka DELETE.
Ke hoʻololi ʻia kahi lālani e kahi kauoha UPDATE, hana ʻia ʻelua mau hana: DELETE a INSERT. Hoʻonohonoho ka mana o ka lālani i ka xmax e like me ka helu o ka hana i hana i ka UPDATE. Hana ʻia kahi mana hou o ke kaula like; ua like kona waiwai xmin me ka waiwai xmax o ka mana mua.
Hoʻokomo ʻia nā kahua xmin a me xmax i ke poʻomanaʻo o ka lālani. Ma waho aʻe o kēia mau kahua, aia nā mea ʻē aʻe i ke poʻo, no ka laʻana:
- ʻO ka infomask he pūʻulu bits e wehewehe i nā waiwai o kēia mana. He nui loa o lakou; E noʻonoʻo mālie mākou i nā mea nui.
- ctid he loulou i ka mana hou aʻe o ka laina like. No ka mea hou loa, ka mana hou loa o kahi kaula, pili ka ctid i kēia mana pono'ī. Aia ke ʻano o ka helu (x,y), kahi ʻo x ka helu ʻaoʻao, ʻo y ka helu kuhikuhi ma ka laʻana.
- null bitmap - Hōʻailona i kēlā mau kolamu o kahi mana i hāʻawi ʻia i loaʻa kahi waiwai null (NULL). ʻAʻole ʻo NULL kekahi o nā koina ʻano ʻikepili maʻamau, no laila pono e mālama ʻia ka ʻano.
ʻO ka hopena, ʻoi aku ka nui o ke poʻo - ma ka liʻiliʻi he 23 bytes no kēlā me kēia mana o ka laina, a ʻoi aku ka nui ma muli o ka NULL bitmap. Inā "haiki" ka papaʻaina (ʻo ia hoʻi, he liʻiliʻi nā kolamu), ʻoi aku ka nui o ke poʻo ma mua o ka ʻike pono.
hookomo
E nānā pono kākou i ke ʻano o ka hana ʻana o ke kaula haʻahaʻa, e hoʻomaka me ka hoʻokomo.
No nā hoʻokolohua, e hana kākou i papaʻaina hou me nā kolamu ʻelua a me kahi kuhikuhi ma kekahi o lākou:
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
E hoʻokomo i hoʻokahi lālani ma hope o ka hoʻomaka ʻana i kahi kālepa.
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
Eia kā mākou helu kālepa i kēia manawa:
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
E nānā kākou i nā mea o ka ʻaoʻao. ʻO ka hana heap_page_items o ka hoʻonui pageinspect hiki iā ʻoe ke loaʻa ka ʻike e pili ana i nā kuhikuhi a me nā mana lālani:
=> 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
E hoʻomaopopo e pili ana ka huaʻōlelo heap ma PostgreSQL i nā papa. He hoʻohana ʻē aʻe kēia o ka huaʻōlelo - ʻike ʻia kahi puʻu , ʻaʻohe mea like me ka papaʻaina. Maʻaneʻi ua hoʻohanaʻia ka hua'ōlelo ma keʻano o "ua hui pūʻia nā mea a pau," e kū'ē i nā kuhikuhi kuhikuhi.
Hōʻike ka hana i ka ʻikepili "e like me", ma kahi ʻano paʻakikī ke hoʻomaopopo. No ka hoʻomaopopo ʻana, e waiho wale mākou i kahi hapa o ka ʻike a wehewehe iā ia:
=> 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)
Eia kā mākou i hana ai:
- Hoʻohui ʻia kahi ʻole i ka helu kuhikuhi i mea e like ai me t_ctid: (helu ʻaoʻao, helu helu kuhikuhi).
- Ua wehewehe i ke kūlana o ka lp_flags pointer. Eia ka "maʻamau" - ʻo ia hoʻi ke kuhikuhi maoli nei ka pointer i ke ʻano o ke kaula. E nānā mākou i nā manaʻo ʻē aʻe ma hope.
- ʻO nā ʻāpana ʻike āpau, ʻelua wale nō i ʻike ʻia i kēia manawa. Hōʻike nā xmin_committed a me xmin_aborted bits inā ua hana ʻia ka helu kālepa xmin (hoʻopau ʻia). ʻElua mau ʻāpana like e pili ana i ka helu kālepa xmax.
He aha kā mākou e ʻike nei? Ke hoʻokomo ʻoe i kahi lālani, e ʻike ʻia kahi helu helu 1 ma ka ʻaoʻao papaʻaina, e kuhikuhi ana i ka mana mua a wale nō o ka lālani.
I ka mana string, ua hoʻopiha ʻia ka xmin kahua me ka helu kālepa o kēia manawa. Ke hoʻomau nei ka hana, no laila ʻaʻole i hoʻonohonoho ʻia nā bits xmin_committed a me xmin_aborted.
ʻO ke kahua ctid mana o ka lālani e pili ana i ka lālani like. ʻO ia hoʻi, ʻaʻohe mana hou.
Hoʻopiha ʻia ka māla xmax me kahi helu dummy 0 no ka mea ʻaʻole i holoi ʻia kēia mana o ka lālani a aia i kēia manawa. ʻAʻole e hoʻolohe nā hana i kēia helu no ka mea ua hoʻonohonoho ʻia ka bit xmax_aborted.
E hana hou kāua i ka hoʻomaikaʻi ʻana i ka heluhelu ʻana ma o ka hoʻohui ʻana i nā ʻikepili ʻike i nā helu kālepa. A e hana mākou i kahi hana, no ka mea, pono mākou i ka noi ma mua o hoʻokahi:
=> 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;
Ma kēia ʻano, ʻoi aku ka maopopo o ka mea e hana nei ma ke poʻo o ka mana o ka lālani:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Hiki ke loaʻa ka ʻike mai ka pākaukau ponoʻī, me ka hoʻohana ʻana i nā pseudo-columns xmin a me xmax:
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
Hoʻopau
Inā hoʻopau maikaʻi ʻia kahi kālepa, pono ʻoe e hoʻomanaʻo i kona kūlana - e hoʻomaopopo ua hana ʻia. No ka hana ʻana i kēia, hoʻohana ʻia kahi hale i kapa ʻia ʻo XACT (a ma mua o ka mana 10 ua kapa ʻia ʻo CLOG (commit log) a hiki ke loaʻa kēia inoa ma nā wahi like ʻole).
ʻAʻole ʻo XACT he papa helu ʻōnaehana; ʻo ia nā faila ma ka papa kuhikuhi PGDATA/pg_xact. Loaʻa iā lākou ʻelua ʻāpana no kēlā me kēia kālepa: hoʻopaʻa ʻia a hoʻopau ʻia - e like me ke poʻo o ka mana o ka lālani. Hoʻokaʻawale ʻia kēia ʻike i kekahi mau faila no ka maʻalahi; e hoʻi mākou i kēia pilikia ke noʻonoʻo mākou i ka hau. A hana ʻia ka hana me kēia mau faila i kēlā me kēia ʻaoʻao, e like me nā mea ʻē aʻe.
No laila, ke hana ʻia kahi hana ma XACT, ua hoʻonohonoho ʻia ka bit i hoʻopaʻa ʻia no kēia kālepa. A ʻo kēia wale nō ka mea i ka wā e hana ai (ʻoiai ʻaʻole mākou e kamaʻilio e pili ana i ka log pre-recording).
Ke komo ʻē aʻe ke kālepa i ka ʻaoʻao papaʻaina a mākou i nānā ai, pono ia e pane i kekahi mau nīnau.
- Ua pau ka hana xmin? Inā ʻaʻole, ʻaʻole pono e ʻike ʻia ka mana i hana ʻia o ke kaula.
Hana ʻia kēia nānā ma ka nānā ʻana i kahi hale ʻē aʻe, aia ma ka hoʻomanaʻo like ʻana o ka laʻana a ua kapa ʻia ʻo ProcArray. Loaʻa iā ia kahi papa inoa o nā kaʻina hana a pau, a no kēlā me kēia mea i hōʻike ʻia ka helu o kāna kālepa o kēia manawa (active). - Inā hoʻopau ʻia, a laila pehea - ma ka hana ʻana a i ʻole ke kāpae ʻana? Inā kāpae ʻia, ʻaʻole pono e ʻike ʻia ka mana o ka lālani.
ʻO kēia ke kumu o XACT. Akā, ʻoiai ua mālama ʻia nā ʻaoʻao hope o XACT i nā buffers i RAM, ʻoi aku ka maikaʻi o ka nānā ʻana iā XACT i kēlā me kēia manawa. No laila, ke hoʻoholo ʻia ke kūlana kūʻai, kākau ʻia i nā bits xmin_committed a me xmin_aborted o ka mana string. Inā hoʻonoho ʻia kekahi o kēia mau bits, a laila ʻike ʻia ka mokuʻāina o ke kālepa xmin a ʻaʻole pono ke komo ʻana i ka XACT.
No ke aha ʻaʻole i hoʻonohonoho ʻia kēia mau ʻāpana e ke kālepa ponoʻī e hana i ka hoʻokomo? Ke hoʻokomo ʻia, ʻaʻole ʻike ke kālepa inā e kūleʻa ia. A i ka manawa o ka hana ʻana, ʻaʻole maopopo i nā laina i hoʻololi ʻia ai nā ʻaoʻao. Nui paha ia mau ʻaoʻao, a ʻaʻole pono ka hoʻopaʻanaʻau ʻana iā lākou. Eia kekahi, hiki ke kipaku ʻia kekahi mau ʻaoʻao mai ka pahu hoʻopaʻa i ka disk; ʻO ka heluhelu hou ʻana iā lākou e hoʻololi i nā bits e hoʻolohi nui i ka hana.
ʻO ka ʻaoʻao haʻahaʻa o ka mālama ʻana ʻo ia ma hope o nā hoʻololi ʻana, hiki i kēlā me kēia kālepa (ʻoiai e hana ana i kahi heluhelu maʻalahi - SELECT) hiki ke hoʻomaka e hoʻololi i nā ʻaoʻao ʻikepili i ka cache buffer.
No laila, e hoʻoponopono kākou i ka hoʻololi.
=> COMMIT;
ʻAʻohe mea i loli ma ka ʻaoʻao (akā ua ʻike mākou ua hoʻopaʻa ʻia ke kūlana kālepa ma XACT):
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
I kēia manawa, ʻo ke kālepa e komo mua i ka ʻaoʻao e hoʻoholo i ke kūlana kūʻai xmin a kākau iā ia i nā bits ʻike:
=> 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)
Hoʻopau
Ke holoi ʻia kahi lālani, kākau ʻia ka helu o ka hana holoi ʻana i kēia manawa i ka xmax kahua o ka mana o kēia manawa, a holoi ʻia ka bit xmax_aborted.
E hoʻomaopopo ʻo ka waiwai i hoʻonohonoho ʻia o xmax e pili ana i ka hana hoʻoikaika e hana ma ke ʻano he laka lālani. Inā makemake kekahi hana hou e hoʻopau a hoʻopau paha i kēia lālani, e koi ʻia e kali no ka hoʻopau ʻana o ka xmax. E kamaʻilio hou mākou e pili ana i ka pale ʻana ma hope. I kēia manawa, ʻike wale mākou ʻaʻole palena ʻole ka helu o nā laka lālani. ʻAʻole lākou e lawe i kahi i loko o ka RAM a ʻaʻole pilikia ka hana o ka ʻōnaehana i kā lākou helu. ʻOiaʻiʻo, he mau hemahema ʻē aʻe nā hana "lōʻihi", akā ʻoi aku ka nui ma hope.
E holoi kāua i ka laina.
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
ʻIke mākou ua kākau ʻia ka helu kālepa ma ka kahua xmax, akā ʻaʻole i hoʻonohonoho ʻia nā ʻikepili ʻike:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
ke kāpaeʻana
Hana like ka hoʻololi ʻana i ka hoʻololi ʻana me ka hana ʻana, ma XACT wale nō i hoʻonohonoho ʻia ka bit aborted no ke kālepa. ʻO ka wehe ʻana e like me ka wikiwiki e like me ka hana ʻana. ʻOiai ua kapa ʻia ke kauoha ʻo ROLLBACK, ʻaʻole hoʻololi ʻia nā hoʻololi: ʻaʻole i loli nā mea a pau i hoʻololi ʻia i nā ʻaoʻao ʻikepili.
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Ke komo ʻia ka ʻaoʻao, e nānā ʻia ke kūlana a e hoʻonohonoho ʻia ka bit hint xmax_aborted i ka mana o ka lālani. Noho ka helu xmax ma ka ʻaoʻao, akā ʻaʻohe mea e nānā iā ia.
=> 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)
Hoʻololi
Ke hana nei ka mea hou me he mea lā ua holoi mua ia i ka mana o kēia manawa o ka lālani a laila hoʻokomo i kahi mea hou.
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
Hoʻopuka ka nīnau i hoʻokahi laina (mana hou):
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
Akā ma ka ʻaoʻao ʻike mākou i nā mana ʻelua:
=> 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)
Hōʻailona ʻia ka mana i hoʻopau ʻia me ka helu kūʻai o kēia manawa ma ke kahua xmax. Eia kekahi, ua kākau ʻia kēia waiwai ma luna o ka mea kahiko, ʻoiai ua hoʻopau ʻia ke kālepa mua. A hoʻomaʻemaʻe ʻia ka bit xmax_aborted no ka mea ʻaʻole i ʻike ʻia ke kūlana o ke kālepa o kēia manawa.
ʻO ka mana mua o ka laina e pili ana i ka lua (t_ctid field) e like me ka mea hou.
Hōʻike ʻia kahi papa kuhikuhi ʻelua ma ka ʻaoʻao kuhikuhi a ʻo ka lālani ʻelua e kuhikuhi i ka mana ʻelua ma ka ʻaoʻao papa.
E like me ka holoi ʻana, ʻo ka waiwai xmax ma ka mana mua o ka lālani he hōʻailona ia ua laka ʻia ka lālani.
ʻAe, e hoʻopau kāua i ke kālepa.
=> COMMIT;
Papa Kuhikuhi
I kēia manawa ua kamaʻilio wale mākou e pili ana i nā ʻaoʻao papaʻaina. He aha ka hana i loko o nā kuhikuhi?
ʻOkoʻa ka ʻike ma nā ʻaoʻao kuhikuhi ma muli o ke ʻano kikoʻī o ka kuhikuhi. A he ʻano ʻano ʻaoʻao ʻē aʻe nā ʻano ʻaoʻao. No ka laʻana, loaʻa i kahi B-tree kahi ʻaoʻao metadata a me nā ʻaoʻao "maʻamau".
Eia nō naʻe, loaʻa i ka ʻaoʻao he mau kuhikuhi i nā lālani a me nā lālani ponoʻī (e like me ka ʻaoʻao papa). Eia kekahi, ma ka hope o ka ʻaoʻao he wahi no ka ʻikepili kūikawā.
Hiki ke loaʻa i nā lālani i loko o nā indexes nā hale ʻokoʻa loa ma muli o ke ʻano o ka index. No ka laʻana, no ka lāʻau B, aia nā lālani e pili ana i nā ʻaoʻao lau ka waiwai kī kuhikuhi a me kahi kuhikuhi (ctid) i ka lālani papaʻaina pili. Ma keʻano laulā, hiki ke hoʻonohonoho ʻia ka index ma kahi ʻano ʻokoʻa loa.
ʻO ka mea koʻikoʻi, ʻaʻohe mana o ka lālani ma nā ʻōlelo kuhikuhi o kēlā me kēia ʻano. ʻAe, a i ʻole hiki iā mākou ke manaʻo ua hōʻike ʻia kēlā me kēia laina e ka mana hoʻokahi. I nā huaʻōlelo ʻē aʻe, ʻaʻohe xmin a me xmax mau māla i ke poʻo lālani kuhikuhi. Hiki iā mākou ke manaʻo e alakaʻi nā loulou mai ka papa kuhikuhi i nā ʻano papaʻaina āpau o nā lālani - no laila hiki iā ʻoe ke noʻonoʻo i ka mana e ʻike ʻia e ke kālepa ma ka nānā ʻana i ka papaʻaina. (E like me nā manawa a pau, ʻaʻole kēia ʻo ka ʻoiaʻiʻo holoʻokoʻa. I kekahi mau hihia, hiki i ka palapala ʻike ʻike ke hoʻonui i ke kaʻina hana, akā e nānā hou mākou i kēia ma hope.)
I ka manawa like, ma ka ʻaoʻao kuhikuhi e ʻike mākou i nā kuhikuhi i nā mana ʻelua, ʻo ka mea o kēia manawa a me ka mea kahiko:
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
Nā kālepa pilikino
Ma ka hoʻomaʻamaʻa, hoʻohana ʻo PostgreSQL i nā optimizations e ʻae iā ia e "mālama" i nā helu kālepa.
Inā heluhelu wale kahi kālepa i ka ʻikepili, ʻaʻohe hopena i ka ʻike ʻia o nā mana lālani. No laila, hoʻopuka mua ke kaʻina hana i kahi xid virtual i ke kālepa. Aia ka helu i kahi ID kaʻina a me kahi helu kaʻina.
ʻAʻole pono ka hoʻopuka ʻana i kēia helu i ka synchronization ma waena o nā kaʻina hana a no laila wikiwiki loa. E kamaʻāina mākou i kekahi kumu no ka hoʻohana ʻana i nā helu virtual ke kamaʻilio mākou e pili ana i ka hau.
ʻAʻole noʻonoʻo ʻia nā helu virtual ma kekahi ʻano i nā snapshot data.
Ma nā wahi like ʻole o ka manawa, aia paha nā kālepa virtual i ka ʻōnaehana me nā helu i hoʻohana mua ʻia, a he mea maʻamau kēia. Akā ʻaʻole hiki ke kākau ʻia kēlā helu i loko o nā ʻaoʻao ʻikepili, no ka mea i ka manawa aʻe e kiʻi ʻia ai ka ʻaoʻao e nalowale paha ke ʻano.
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
Inā hoʻomaka kahi kālepa e hoʻololi i ka ʻikepili, hāʻawi ʻia i kahi helu kūʻai kūʻokoʻa maoli.
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
Nā Kūʻai Kūʻē
E mālama i nā helu
Wehewehe ʻia ma SQL mālama i nā helu (savepoint), ka mea e ʻae iā ʻoe e kāpae i kahi ʻāpana o kahi kālepa me ka ʻole e hoʻopau loa iā ia. Akā ʻaʻole kūpono kēia i ka kiʻi i luna, no ka mea, ua like ke kūlana o ke kālepa no kāna mau hoʻololi a pau, a ʻaʻohe ʻikepili i hoʻihoʻi ʻia.
No ka hoʻokō ʻana i kēia hana, ua hoʻokaʻawale ʻia kahi kālepa me kahi savepoint i mau ʻokoʻa nā hana i hoʻopaʻa ʻia (subtransaction), hiki ke hoʻokele kaʻawale ke kūlana.
Loaʻa i nā kālepa nested kā lākou helu ponoʻī (ʻoi aku ka kiʻekiʻe ma mua o ka helu o ka hana nui). Hoʻopaʻa ʻia ke kūlana o nā hana nested ma ke ʻano maʻamau ma XACT, akā ʻo ke kūlana hope e pili ana i ke kūlana o ka hana nui: inā e hoʻopau ʻia, a laila hoʻopau ʻia nā hana nested a pau.
Mālama ʻia ka ʻike e pili ana i ka nesting kālepa i nā faila ma ka papa kuhikuhi PGDATA/pg_subtrans. Loaʻa ʻia nā faila ma o nā buffers i ka hoʻomanaʻo like ʻana o ka instance, i hoʻonohonoho ʻia ma ke ʻano like me XACT buffers.
Mai huikau i nā hana pūnana me nā hana kūʻokoʻa. ʻAʻole hilinaʻi nā ʻoihana kūʻokoʻa i kekahi i kekahi ma kekahi ʻano, akā hana nā hana nested. ʻAʻohe hana kūʻokoʻa i ka PostgreSQL maʻamau, a, no ka mea maikaʻi loa: makemake nui ʻia lākou, kakaʻikahi loa, a ʻo ko lākou hele ʻana i nā DBMS ʻē aʻe e hoʻonāukiuki ai i nā mea a pau.
E hoʻomaʻemaʻe i ka papaʻaina, e hoʻomaka i kahi kālepa a hoʻokomo i ka lālani:
=> 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)
I kēia manawa, e hoʻokomo i kahi wahi mālama a hoʻokomo i kahi laina ʻē aʻe.
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
E hoʻomaopopo i ka hoʻihoʻi ʻana o ka hana nxid_current() i ka helu kālepa nui, ʻaʻole ka helu kālepa nested.
=> 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)
E hoʻi kāua i ka helu mālama a hoʻokomo i ka laina ʻekolu.
=> 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)
Ma ka ʻaoʻao ke hoʻomau nei mākou e ʻike i ka lālani i hoʻohui ʻia e ke kālepa nested i kāpae ʻia.
Hoʻoponopono mākou i nā loli.
=> 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)
I kēia manawa hiki iā ʻoe ke ʻike maopopo i kēlā me kēia nested transaction i kona kūlana ponoʻī.
E hoʻomanaʻo ʻaʻole hiki ke hoʻohana ʻia nā hana pūnana ma SQL, ʻo ia hoʻi, ʻaʻole hiki iā ʻoe ke hoʻomaka i kahi kālepa hou me ka hoʻopau ʻole i ka mea o kēia manawa. Hoʻohana ʻia kēia ʻano hana i ka wā e hoʻohana ai i nā wahi mālama, a i ka wā e lawelawe ai i nā ʻokoʻa PL/pgSQL a i kekahi mau hihia ʻē aʻe.
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
Nā hewa a me ka atomicity o nā hana
He aha ka hopena inā loaʻa kahi hewa i ka wā e hana ana i kahi hana? No ka laʻana, e like me kēia:
=> 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 hala kekahi hewa. I kēia manawa ua manaʻo ʻia ua hoʻopau ʻia ka hana a ʻaʻole ʻae ʻia nā hana i loko:
=> SELECT * FROM t;
ERROR: current transaction is aborted, commands ignored until end of transaction block
A inā ʻoe e hoʻāʻo e hana i nā loli, e hōʻike ʻo PostgreSQL i kahi abort:
=> COMMIT;
ROLLBACK
No ke aha ʻaʻole hiki ke hoʻomau ke kālepa ma hope o ka hāʻule ʻole? ʻO ka ʻoiaʻiʻo hiki ke ala mai kahi hewa i ke ala e hiki ai iā mākou ke komo i kahi ʻāpana o nā hoʻololi - ʻo ka atomicity ʻaʻole i ke kālepa, akā e uhaki ʻia ka mea hoʻohana. E like me kā mākou hiʻohiʻona, kahi i hiki ai i ka mea hoʻohana ke hoʻonui i kahi laina ma mua o ka hewa:
=> 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)
Pono e ʻōlelo ʻia he mode psql e hiki ai ke hoʻomau i ke kālepa ma hope o ka hāʻule ʻana me he mea lā ua ʻōwili ʻia nā hana a ka mea hoʻohana hewa.
=> 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;
ʻAʻole paʻakikī ke koho i kēia ʻano, hoʻokomo maoli ʻo psql i kahi helu mālama implicit ma mua o kēlā me kēia kauoha, a inā ʻaʻole i hoʻomaka ka rollback iā ia. ʻAʻole hoʻohana ʻia kēia ʻano ma ke ʻano paʻamau, no ka mea, ʻo ka hoʻonohonoho ʻana i nā wahi mālama (ʻoiai me ka ʻole o ka hoʻi ʻana iā lākou) e pili ana i ke poʻo nui.
Source: www.habr.com