ααΌα
αααα ααΎαβααΆαβαα·α
αΆαααΆβααΎβαααα αΆβαααβααΆααααβααΉα
ααααααΆ
ααΌα αααααΎαααΆααα·ααΆααα½α ααα αΎα αα½αααΈαα½ααα’αΆα ααΆααααα»ααααααααΆαααααΆαα αααα»αααααααΆα αααΎααα αααα»αααΌαααααΆααα·ααααααα ααααβαα½αβααααΌαβααβααΆαβααααααβαα»αβαααααβααΈβααααβαα½αβαααααβαααβα αααααΆααβαααβααααβααα ααααβααΈαα½ααβααΆαβαααααΆβααΈαβαααβααααα "αααααααΆ" ααβαααααααΆαβααβααααβααα (xmin αα·α xmax)α αα αααα»ααααααα - αααααΆαααααΆαα·ααααααΆαααααααΆααΌα αααααααΌαααΆαααααΎαααααααα»ααααααΆαααΆααααΎαα‘αΎααα·αααα α αΎαααααααααααΊααΆαααααααα·ααααα·ααΆαα
(ααΌα
ααααααΆ ααΆααα·αααΆαααααααα»αααααΆαα αααααααα·ααααα·ααΆααα·αα’αΆα
ααΎαα‘αΎααααααααααα αααααΆααααααααΆααααΈαααΆαααααααααααααααα ααα»ααααααΎαααΉααα·αα·αααααΎαααααααΆααααα’α·αααΆαααααα±ααααΆααααα’α·ααα
αααααΎαααΆααααααΆααααααα)
αα
αααααααα½αααααα½αααααΌαααΆααααααΎα xmin ααααΌαααΆαααααααα
αααααααα·ααααα·ααΆααααααΆαα
ααααΆααααααααΆ INSERT α αΎα xmax ααααΌαααΆααα»αα
ααα
αα αααααααα½αααααα½αααααΌαααΆααα»α ααααα xmax αααααααα αα α»ααααααααααΌαααΆααααααΆαααααα ααα½αααααααα·ααααα·ααΆααααααΆαα’αα»ααααααΆααα»αα
αα αααααααα½ααα½αααααΌαααΆααααααααααααΆααααααααΆ UPDATE ααααα·ααααα·ααΆαααΈαααααΌαααΆαα’αα»αααααααΆααα·αααααΆααα DELETE αα·α INSERT α αααααα αα α»αααααααααα½ααααααααα xmax ααααΎααΉαα ααα½αααααα·ααααα·ααΆααααααΆαααααΎ UPDATE α αααααΆααααααααααααΈααααααα’ααααααΌα ααααΆααααΌαααΆααααααΎαα ααααα xmin ααααααΆααααααααΆααΉαααααα xmax αααααααα»αα
ααΆα xmin αα·α xmax ααααΌαααΆααα½ααααα αΌααα αααα»αααααααΆαααααα½αα ααααααααΈααΎααΆαααΆααααα ααααααΆααΆααααααααα α§ααΆα αααα
- infomask ααΊααΆαααααΈαααααΈααααααααααααααααααααααα·αααααααααα ααΆαα αααΎαααΆαααααα½ααα; ααΎαααΉααα·α αΆαααΆααααα·α αααα α
- ctid ααΊβααΆβαααβαα βααΆααβααααβααααΈβαααααΆααβααβαααααΆααβααΌα βααααΆα αααααΆααβααααβαα αα α»ααααααβαααα»αβααβααααβα’αααα ctid αααα βααΎβααααβαααβαααα½αβα―αα αααααΆααααααα (x,y) ααα x ααΆαααααααα y ααΆααααα·αα·αααααααα»αα’αΆααα
- null bitmap - αααααΆαααα½αααααΆααααααααααααααααΆααααααα±αααααααΆααααααααα (NULL) α NULL αα·ααααααΆααααααααααααα·ααααααααααααΆαα½ααα ααΌα αααααα»αααααααααααΌααααααααΆαα»ααααα‘ααααΈααααΆα
ααΆαααααα ααααααΆααΆαααα αααααΆαα - αααΆαα αα ααΆαα 23 αααααααΆααααααααΈαα½αααααααααΆαα α αΎαααΆααααααΆα αααΎαααΆαααααααααΆααα NULL bitmap α ααααα·αααΎααΆααΆαααΊ "ααΌα α ααα’αα" (αααααΊααΆααα½αααααΈαααΈ) ααααααααααΎαα’αΆα ααα½αααα αααΎαααΆαααααααΆααααααΆαααααααααα
αααα αΌα
α αΌαααΎααα·αα·αααααΎαα±ααααΆααααα αααΆααα’αααΈαααααααααααα·ααααα·ααΆαααααα’ααααααααα·αααΆαααααΌαααΆαα’αα»αααα αααα αΆααααααΎαααΆαα½αααΉαααΆααααα αΌαα
αααααΆααααΆααα·ααααα ααΎααααααΎαααΆααΆαααααΈαααααΆααα½αααααΈα αα·ααα·αα·ααααααΎαα½ααααα»αα αααααα½αααΆα
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
ααΌααααα αΌααα½ααα½ααααααΆααααΈα αΆααααααΎαααααα·ααααα·ααΆαα
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
αααααΊααΆαααααααα·ααααα·ααΆααα αα α»ααααααααααααΎαα
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
ααΌααααα‘ααααΎαααααΉαααΆααααααααα αα»αααΆα heap_page_items ααααααααααααα pageinspect α’αα»ααααΆαα±ααα’αααααα½αααΆαααααααΆαα’αααΈααααα·α αα·ααααααα½αα
=> 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
α
αααΆαααΆααΆααα heap αα
αααα»α PostgreSQL αααα
ααΎααΆααΆαα αααβααΆβααΆαβααααΎβααΆαααβα
αααααβαα½αβαααβααβααΆαααβααα - heap ααααΌαβααΆαβααβααααΆαα
αα»αααΆααααα αΆααα·αααααα βααΌα β ααΆααααααααααα·ααΆααααα ααΎααααΈαααααααα ααΎαααΉααα»ααααααααααααααααΆα α αΎαααααααΆαααΆα
=> 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)
αααααΆα’αααΈαααααΎαααΆαααααΎα
- αααααααααααΌααααα ααααα·αα·αααα ααΎααααΈα±ααααΆααΎααα ααΌα t_ctidα (αααααααα ααααα·αα·αααα)α
- αα·ααααΈαααααΆαααΆαααααααα·α lp_flags α αα ααΈαααααΆ "ααααααΆ" - αααααΆααααααΆααααα·α αα·αααΆαααα αα ααΎααααααααααα’ααααα ααΎαααΉαααΎαα’αααααααααααααααα ααααααααα
- αααα»αα ααααααααααΆαααΆααα’αα ααΆαααααΈαααΌααα»ααααααααααααΌαααΆααααααα’ααααααααΆααα αΌααααααααααααα αααΈα xmin_committed αα·α xmin_aborted αααα αΆαααΆααΎαααααααα·ααααα·ααΆα xmin ααααΌαααΆαααααααααΆα α·ααα (αααααα)α αααΈαααααααααααΆααΈααααα ααΎαααααααα·ααααα·ααΆα xmax α
ααΎααΎαααΎαα’αααΈ? αα αααα’ααααααα αΌααα½αααα αα·αα·ααααααα 1 ααΉααααα αΆααα αααα»ααααααααΆααΆα αααα ααα’α»ααα ααααααΈαα½α αα·ααααα½αααααααα½ααααα
αα αααα»αααααααααα’αααα ααΆα xmin ααααΌαααΆααααααααααααααααα·ααααα·ααΆααα αα α»ααααααα ααααα·ααααα·ααΆααα ααααααα ααΌα ααααααΆαα xmin_committed αα·α xmin_aborted bits αα·αααααΌαααΆααααααααα
ααΆα ctid ααααβαα½αβαααβαααα βαα βααΎβαα½αβαααβααΌα βααααΆα αααααΆααααααΆαα·αααΆαααααααααΈααΆααααααα
ααΆα xmax ααααΌαβααΆαβαααααβαααβαααβα’ααα ααβααα 0 αααααβααααβααβαα½αβαααβαααβαα·αβααααΌαβααΆαβαα»αβα ααβαα α αΎαβααΊβαα αα α»ααααααα ααααα·ααααα·ααΆαααΉααα·αααα α·ααααα»αααΆααα αααααααααααα αααααΆα xmax_aborted bit ααααΌαααΆααααααα
α αΌαααΎααααααα αΆααα½ααααααααααααααααα ααααΆααααααα’ααααααΆαα’αΆααααααααααααααααΆααααΈααα αααααααα·ααααα·ααΆαα α αΎαααααααααΎααα»αααΆααα½α αααααααΎαααΉαααααΌαααΆαααααΎα αααΎαααΆαααααα
=> 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;
αααα»αααααααααα ααΆααΆααααα αααΆααα’αααΈα’αααΈααααααα»αααΎαα‘αΎααα αααα»αααααααΆαααααααα½αα
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
ααααααααααΆ ααα»αααααα·αααΌααααα’α·ααα ααααααΆαα’αΆα ααα½αααΆαααΈααΆααΆαααααααα½αα―α αααααααΎ pseudo-columns xmin αα·α xmaxα
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
ααΆααα½ααα»α
ααααα·αααΎααααα·ααααα·ααΆαααααΌαααΆααααα ααααααααααα α’αααααααΌαα αα αΆαααααΆαααΆαααααααΆ - α αααΆαααΆααΆααααΌαααΆαααααααααΆα α·αααα ααΎααααΈααααΎααΌα αααα αα ααΆαααααααααααα α ααΆ XACT ααααΌαααΆαααααΎ (α αΎααα»ααααα 10 ααΆααααΌαααΆαααα α ααΆ CLOG (commit log) α αΎααααααααααα ααα’αΆα ααααΎααα ααααααααααααααααΆ)α
XACT αα·ααααααΆααΆααΆαααΆααΆα‘α»αααααααααααα ααΆαααααααΊααΆα―αααΆααα αααα»αααα―αααΆα PGDATA/pg_xact α αα½αααααΆαααΈααααΈααααααΆααααααα·ααααα·ααΆαααΈαα½ααα ααααααααΆα α·ααα αα·ααα»αα αα - ααΌα αα αααα»αααααααΆαααααα½αα ααααααΆααααααααΌαααΆααααα αααα ααΆα―αααΆαααΆα αααΎααααααΆααααΆαααΆααααα½αααααα»ααααα ααΎαααΉααααααααα αααα αΆααααα·ααα ααααααααΎααα·α αΆαααΆααΈααΆααααααα α αΎαααΆαααααΎααΆαααΆαα½αα―αααΆαααΆαααααααααΌαααΆαα’αα»αααααα½ααααααααααααααααΌα ααΆααΆαα½αα’ααααααααΆααα’ααα
ααΌα αααα αα ααααααααααα·ααααα·ααΆααα½αααααΌαααΆααααααααΉααααα αααα»α XACT αααΈααααααΆαααααααααΆα α·αααααααΌαααΆαααααααααααΆααααααα·ααααα·ααΆααααα α αΎααααααΆα’αααΈααΆααα’αααααααΎαα‘αΎαααα‘α»αααααααααααΉααα (αααααΈααΆααΎααα·αααΆαααα·ααΆαα’αααΈαααααα ααα»ααααΆααααα»ααα»αααααα)α
αα ααααααααααα·ααααα·ααΆααα½αααααααααα αΌααα ααΆαααααααααΆααΆααααααΎαααΎαααααΆαααΎα ααΆααΉαααααΌαααααΎααααα½αααΆα αααΎαα
- ααΎααααα·ααααα·ααΆα xmin ααΆααααα
ααα αΎαα¬αα
? ααΎαα·αααΌα
αααααα αααααααααΆααααααΎαααααααα’αααααα·ααα½αααααΌαααΆαααΎαααΎαααα
ααΆααααα½ααα·αα·ααααααααααΌαααΆαα’αα»αααααααααΎααα ααΆαααααααααα½ααααααααααααααΆαααΈααΆαααα αααα»αα’αααα αα αΆααα½αααααααα»α αΎαααααΌαααΆαααα α ααΆ ProcArray α ααΆααΆααααααΈααααααΎαααΆααααααααΆααα’αα α αΎααααααΆαααααααΈαα½ααααααααα·ααααα·ααΆααα αα α»αααααα (ααααα) ααααααΆααααΌαααΆαα ααα’α»ααααα αΆαα - ααααα·αααΎααΆααααα
αα ααΎααααΎααΌα
ααααα
- αααααΆαααααααααΆα
α·ααα α¬αα»αα
αα? ααααα·αβααΎβααΆαβαααααα αααβααααβαα½αβαααβααβαα·αβαα½αβααΎαβααΎαβαααα
αααααΊααΆα’αααΈααα XACT ααΊαααααΆααα ααα»αααααααααΈααΆαααααα α»αααααααααα XACT ααααΌαααΆααααααΆαα»ααααα»αααα·ααααααα’αΆαααααααα»α RAM ααααα ααΆαα ααααΆαααααααααααααα»αααΆααααα½ααα·αα·ααα XACT ααΆαααααα ααΌα αααααα ααααααααααΆαααΆαααααα·ααααα·ααΆαααααΌαααΆαααααα ααΆααααΌαααΆαααααααα ααΆαα xmin_committed αα·α xmin_aborted bits ααααααααααα’ααααα ααααα·αααΎαααΈααα½ααααα»αα αααααααΈαααΆαααααααααΌαααΆαααααα αααααααΆαααΆαααααααα·ααααα·ααΆα xmin ααααΌαααΆαα αΆαααα»αααΆααααΆαα α αΎαααααα·ααααα·ααΆααααααΆααααΉααα·αα αΆαααΆα αα αΌαααααΎ XACT ααα
α ααα»α’αααΈααΆαααΆαααΈαααΆααααααα·αααααΌαααΆαααααααααααααα·ααααα·ααΆααααα½αααΆααααΆαα? αα ααααααααΆααααα αΌαααΎαα‘αΎα ααααα·ααααα·ααΆααα·αααΆααααΉαααΆααΎααΆααΉαααααααα¬αααΆαααΆαααααα α αΎαβαα βαααβααααΎβααΆα ααΆβαα·αβα αααΆααβαααβααβααΆβαααααβααΆβααααΌαβααΆαβααααΆααβααααΌαα αααα ααααΆααΆααααααααααααα αααΎα α αΎαααΆαααααααααΆααΊαα·αααΆαααααααααααα ααΎαααΈααα ααααααα½αα ααα½αα’αΆα ααααΌαααΆαααααααα ααααΈααααΆαααααα»ααα·αααααααα ααΆαα ααΆαα’αΆααα½αααΆαααααααααΎααααΈααααΆααααααΌααααΈαααΉααααααααΆαααααααααΆα α·ααααααΆαααααΆααα
ααΆαααααΆααα α»αααααΆααααααααΊααΆαααααΆααααΈααΆαααααΆααααααΌα ααααα·ααααα·ααΆαααΆαα½α (ααΌααααΈαααα½αα’αα»ααααααΆαα’αΆαααΆαααα - SELECT) α’αΆα α αΆααααααΎαααααΆααααααΌαααααααα·αααααααα αααα»αααααΆαααααα»ααα·ααααααα
ααΌα αααα α αΌαβααΎαβαααααααΆαβααΆαβααααΆααααααΌαα
=> COMMIT;
ααααΆαα’αααΈααααΆααααααΌααα ααΎααααααα (ααα»ααααααΎαααΉαααΆααααΆαααΆαααααα·ααααα·ααΆαααααΌαααΆααααααααΆαα½α α αΎααα αααα»α XACT)α
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
α₯α‘αΌααααααααα·ααααα·ααΆααααα αΌαααααΎαααααααααΌαααΉαααααΌααααααααααΆαααΆαααααα·ααααα·ααΆα xmin α αΎααααααααΆαα αααΈαααααααΆαα
=> 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)
αα»α
αα αααααααα½αααααα½αααααΌαααΆααα»α αααααααααα·ααααα·ααΆααα»ααα αα α»ααααααααααΌαααΆαααααααα ααΆααααΆα xmax αααααααα αα α»αααααα α αΎα xmax_aborted bit ααααΌαααΆααααααα
α αααΆαααΆαααααααααααα xmax αααααααΌαααααΆααΉαααααα·ααααα·ααΆααααααααΎααα½ααΆααΆαα αΆαααααα½ααααα ααααα·αααΎααααα·ααααα·ααΆαααααααααα ααααααΎαα αα α»ααααααααΆα α¬αα»ααα½αααα ααΆααΉαααααΌααααααα±αααααα αΆαααααα·ααααα·ααΆα xmax ααΎααααΈαααα ααα ααΎαααΉααα·ααΆααααααααααα’αααΈααΆααααααααΆαααα ααααααααα αααααΆαααααααα ααΎαααααΆααααα αααΆαααΆα ααα½αααααΆαα αΆαααααα½ααααααΊααααΆαααααααααα αα½ααααα·αααααααααααααααα αααα»α RAM α αΎαααααΎαααΆααααααααααα·αααα½αααααΈααααααααα½αααααα αα·α ααααα·ααααα·ααΆα "ααΌα" ααΆααα»ααα·ααααα·αααααααα ααα»ααααααΆαα αααΎαααααα ααααααααα
ααααα»ααααααΆααα
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
ααΎαααΎαααΆαααααααα·ααααα·ααΆαααααΌαααΆαααααααααα»αααΆα xmax ααα»αααααααΈαααααααΆααα·αααααΌαααΆααααααααα
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
ααΆααα»αα αα
ααΆααα»αα ααααΆαααααΆααααααΌαααααΎαααΆαααααααααααΆαα ααΉαααΆααααααααΉαααααα ααΆααααα αααα»α XACT ααα»ααααα αααΈααααααΆααα»αα ααααααΌαααΆαααααααααααΆααααααα·ααααα·ααΆαα ααΆααα·αααααΎααΊααΏαααΌα ααΆαααααααααΆα α·αααα αααααΈααΆααΆααααααααΆααααΌαααΆαααα α ααΆ ROLLBACK ααΆαααααΆααααααΌααα·αααααΌαααΆααααααααααα·αααα α’αααΈααααααααΆααααααααα·ααααα·ααΆαααΆααααααααααααΎααααΈααααΆααααααΌααα αααα»αααααααα·αααααααα αααα·αααααΆααααααΌαα
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
αα αααααααααααααααΌαααΆαα αΌαααααΎ ααααΆαααΆαααΉαααααΌαααΆααα·αα·ααα α αΎααααΈααααα½α xmax_aborted ααΉαααααΌαααΆαααααααα ααΆαααααα½αα ααα xmax αααα½αααΆαα ααααΆααα ααΎααααα ααα»ααααααααΆαααααΆααααΆααααΉαααΎαααΆααα
=> 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)
ααααΎαα αα α»ααααααααΆα
ααΆαα’αΆαααααααααΎαααΆαααΌα αααααΆααΆααα»ααααααα αα α»αααααααααα½ααααααααΌα α αΎααααααΆαααααααα αΌαααααΈαα½αα
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
αααα½ααααααΎααα½ααα½α (ααααααααΈ)α
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
ααα»αααααα ααΎαααααααΎαααΎαααααααΆααααΈαα
=> 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)
αααααααααΆααα»αααααΌαααΆααααααΆααααααααααααα·ααααα·ααΆααα αα α»αααααααα αααα»αααΆα xmax α ααΆαααααα ααα ααααααααααααΌαααΆααααααααΎαααααα αΆαα α αΆααααΆααααΈααααα·ααααα·ααΆααα»αααααΌαααΆααα»αα ααα α αΎα xmax_aborted bit ααααΌαααΆαααααα αααααΆαααααΆαααΆαααααααα·ααααα·ααΆααα αα α»αααααααα·αααΆααααΉααα α‘αΎαα
ααααααααΌααααααααΆααα₯α‘αΌαααααααα αα ααΎααΈααΈα (t_ctid field) ααΆααααααααΈααΆααααα
αα·αα·ααααααΈααΈαααα α‘αΎααααα»αααααααα·αα·αααα α αΎααα½αααΈααΈααααααααααΈααΈααα αααα»ααααααααΆααΆαα
ααΌα ααααΆααΉαααΆααα»αααα ααααα xmax αα αααα»αααααααααΌααααα½ααααααΊααΆααΆαα ααα’α»ααααα αΆαααΆαα½ααααααααΌαααΆαα αΆααααα
α αΌαααΎααααα ααααααα·ααααα·ααΆαα
=> COMMIT;
ααααααααα
αα αΌαααααααααααα ααΎαααααΆαααααα·ααΆαα’αααΈαααααααΆααΆαααα»αααααα ααΎααΆαα’αααΈααΎαα‘αΎααα αααα»αααααααααα?
ααααααΆααα αααα»αααααααα·αα·αααααααααααα½ααααΆαααααΆααα’αΆαααααααΎααααααααΆααααΆαααααα·αα·ααααα α αΎαααΌααααΈαααααααααααα·αα·αααααα½αααααΆααααααααααααααααααααααΆαααα α§ααΆα ααα B-tree ααΆαααααααα·ααααααααααΆ αα·αααααα "αααααΆαα" α
ααααααΆαααΆααααα αααααααΆααααααΆααΆαα’αΆααααα ααα’α»ααα αα½αααα αα·ααα½ααααααααααα½αα―α (ααΌα ααΆαααααααΆααΆα)α ααΎαααΈαααααααα α α»ααααα αααααααααααΆααααααααααααΆαααα·αααααααα·αααα
αα½αβαααβαααα»αβαα·αα·ααααβααβα’αΆα βααΆαβαα ααΆααααααααβαα»αβααααΆβααααΆααβαααβα’αΆαααααβααΎβααααααβαααααααααα α§ααΆα ααα αααααΆααβαααααΆα B αα½αβαααβααΆααααβααΉαβαααααβααααΉαβααΆαβαααααβαααααΉαβααααΎβαα·αα·αααα αα·αβα―αααΆαβααα (ctid) αα βαα½αβααΆααΆαβαααβααααΌαβααααΆα ααΆααΌαα αααααααααα’αΆα ααααΌαααΆααααα αααΆααααααα»αααααΆααΆαααααα»αα
α ααα»α ααααΆαααααα»αααΊααΆαα·αααΆααααααα½ααα αααα»ααα·αα·ααααααααααααααΆαα½αα‘αΎαα α¬ααΎαα’αΆα ααααααααΆαααααΆααααΈαα½ααααααΌαααΆαααααΆααααααααααΆααααΆαααα½αα αα αααα»αααΆααααααααααα αα·αααΆαααΆα xmin αα·α xmax αα αααα»αααααααΆαα½αααααα·αα·ααααααα ααΎαα’αΆα ααααααααΆαααααααΆααααΈαα·αα·ααααααΆααα αααααααααΆααΆαααΆααα’αααααα½α - ααΌα ααααα’αααα’αΆα ααααααααααΆααΎααααααΆαα½ααααααααα·ααααα·ααΆαααΉαααΎααααααααΆααααααΎαααΆααΆαααα»αααααα (ααΌα ααΆαααα ααααα·ααααααΆααΆααα·αααΆαααααα»ααααααα αααα»αααααΈαααα αααααΈααΆαααΎαααΎαα’αΆα αααααΎαααααα·αααααΆαααααΎαααΆα ααα»ααααααΎαααΉααα·αα·αααααΎααααα’α·αααααααααααα ααααααααα)
αααα»ααααααΆαα½αααααΆααα αα αααα»αααααααα·αα·αααα ααΎαααααΎαα ααα’α»ααα ααααααΆααααΈα ααΆαααααααα αα α»αααααα αα·αααααα αΆααα
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
ααααα·ααααα·ααΆααα·αααα·α
αα αααα»αααΆαα’αα»αααα PostgreSQL ααααΎααΆααααααΎαααααα·αααααΆααααα’αα»ααααΆαα±ααααΆ "αααααΆαα»α" αααααααα·ααααα·ααΆαα
ααααα·αααΎααααα·ααααα·ααΆαα’αΆααααα·αααααα ααΆαα·αααΆαα₯αααα·ααααΎααΆαααΎαααΎααααααααα½ααααααα ααΌα αααα ααααΎαααΆαααααΆααααααααΌαα αα xid αα·αααα·αα ααααααααα·ααααα·ααΆαα αααααΆαααααααααΆααααααΎαααΆα αα·ααααααααΆααα
ααΆαα αααααααααα·ααααααΌαα±ααααΆαααΆαααααΎααααΆααααααααΆαααααΎαααΆαααΆααα’αααα ααΌα ααααα αΎαααΏαααΆααα ααΎαααΉαααααΆααα ααα»αααα½αααααααααΆααααΆαααααΎααααΆααααααα·αααα·α αα αααααΎααα·ααΆαα’αααΈααΆααααααα
ααααα·αααα·ααα·αααααΌαααΆααααααααα·α αΆαααΆααΆααααααααΆαααΆαα½ααα αααα»αααΌααααα·ααααααααα
αα α ααα»α αααααααααΆαα αααα»ααααααααΆ αααα ααααΆααΆαααααα·ααααα·ααΆααα·αααα·ααα αααα»αααααααααααΆαα½αααΉαααααααααΆαααααΎαα½α α αΎα α αΎααααααΊααΆααΏαααααααΆα ααα»ααααααααααααααα·αα’αΆα ααααααα αααα»αααααααα·ααααααααΆααα ααΈααααααα αααα αΌαααααΎααααααααααΆαα ααΆα’αΆα ααΆαααααα’ααααααααΆααα’ααα
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
ααααα·αααΎααααα·ααααα·ααΆαα αΆααααααΎαααααΆααααααΌααα·αααααα ααΆααααΌαααΆαααααααααααααα·ααααα·ααΆααααα½αααααα·αααααΆααα
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
ααααα·ααααα·ααΆααααααΆαα
ααααααα·αααα»
ααααααααα»α SQL ααααααα·αααα» (savepoint) αααα’αα»ααααΆαα±ααα’ααααα»αα αααααααααααααα·ααααα·ααΆαααααα·αααααΆαααΆααΆαααααα»αα ααα»ααααααααα·αααααΉαααααΆααααΆαααΆαααΎαα αααααΆαααααα·ααααα·ααΆαααΆαααααΆαααΆαααΌα ααααΆαααααΆααααΆαααααΆααααααΌαααΆααα’ααααααααΆ α αΎαααΆααΌααααααα·αααΆααα·ααααααααααΌαααΆαααααα·ααααα‘αααααα·αααα
ααΎααααΈβα’αα»ααααβαα»αααΆαβααα ααααα·ααααα·ααΆαβααΆαα½αβααΉαβααααααβαααααΆαα»αβααααΌαβααΆαβαααααβααΆβα αααΎαβαααβα‘ααβααΈβααααΆα ααααα·ααααα·ααΆααααααΆααααΆαα (subtransaction) ααααΆαααΆααααα’αΆα ααααααααααααα‘ααααΈααααΆα
ααααα·ααααα·ααΆααααααΆααααΆααααααααααα½α (αααααααΆαα ααα½αααααα·ααααα·ααΆαααααΆαα)α ααααΆαααΆαααααα·ααααα·ααΆααααααΆααααααΌαααΆααααααααΆααΆαααααααααααΆαα αααα»α XACT ααα»ααααααααΆαααΆαα α»ααααααα’αΆαααααααΎααααΆαααΆαααααααα·ααααα·ααΆαα ααααα ααααα·αααΎααΆααααΌαααΆααα»αα αα αααααααα·ααααα·ααΆααααααΆααααΆααααΆααα’ααααααααΌαααΆααα»αα αααααααα
ααααααΆαα’αααΈααααα·ααααα·ααΆααααα»αααααΌαααΆααααααΆαα»ααα αααα»αα―αααΆααα αααα»αααα―αααΆα PGDATA/pg_subtrans α α―αααΆαααααΌαααΆαα αΌαααααΎααΆααααααα·ααααααα’αΆαααααα αααα»αα’αααα αα αΆααααααΆαα αααααααααααααααα» αααααααΌαααΆααααα αααΆαααααααΌα ααααΆααΉαααα·ααααααα’αΆαααα XACT αααα
αα»αα ααα‘αααααα·ααααα·ααΆααααααΆααααΆαα½αααααα·ααααα·ααΆαααααααα ααααα·ααααα·ααΆααααααααα·αα’αΆαααααααΎααααΆαα αα·ααα ααααΆααααααααΆαααΆαα½αα‘αΎα ααα»ααααααααα·ααααα·ααΆααααααΆαααα»αα αα·αααΆαααααα·ααααα·ααΆααααααααα αααα»α PostgreSQL ααααααΆαα α αΎααααα ααααΆααα’αααα»αα αα½αααααααΌαααΆαααΆα αΆαααΆα αααΆαα ααααααΆαα α αΎαααααααΆααααααα½ααααα αααα»α DBMSs ααααααααααααα±ααααΆαααΆααααααααααΆα αααααα»ααααααααααααΆααα½αααααΆαααΊα αΆααα
ααααα»αααΆααΆα α αΆααααααΎαααααα·ααααα·ααΆα α αΎααααα αΌααα½ααααα
=> 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)
α₯α‘αΌαβααΎαβααΆααβα ααα»α βαααααΆβαα»α α αΎαβαααα αΌαβαααααΆααβαα½αβαααα
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
α αααΆαααΆα’αα»αααα txid_current() αααα‘αααααααααα·ααααα·ααΆαααααΆαα αα·αααααααααααα·ααααα·ααΆααααααΆαααΆααααΆαααααααα
=> 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)
α αΌαααΎααα·ααααααααα α ααα»α αααααΆαα»α α αΎααααα αΌααααααΆααααΈααΈα
=> 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)
αα αααα»ααααααααΎαααααααΎααα½αααααααααΆααααααααααααααα·ααααα·ααΆααααααΆααα»αα ααα
ααΎααα½ααα»αααΆαααααΆααααααΌαα
=> 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)
α₯α‘αΌαβααα α’αααβα’αΆα βααΎαβααΎαβαααΆαβα αααΆααβααΆβααααα·ααααα·ααΆαβαααβααΆααβααααΆβααΆαβααααΆαααΆαβααααΆααβαααα½αα
α αααΆαααΆααααα·ααααα·ααΆααααααΆαααΆααααΆααααααΆαα·αα’αΆα ααααΌαααΆαααααΎααααΆαααααΆαα αααΆαααα αααα»α SQL ααααα αααααΊα’ααααα·αα’αΆα α αΆααααααΎαααααα·ααααα·ααΆαααααΈααααα·ααααααααααα·ααααα·ααΆααα αα α»ααααααααΆαααα ααααααΆααααααααΌαααΆαααααΎα±αααααααααααααααααα αααααααΎα ααα»α αααααΆαα»α ααααΌα ααΆαα ααααααααααΆαααΆαααΎαααα PL/pgSQL αα·ααα αααα»αααααΈαααααα·αα’αααααααΆα αααΎααααα
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
ααα α»ααα·αα’αΆααΌαα·α ααααααα·ααααα·ααΆα
ααΎααΆαα’αααΈααΎαα‘αΎαααααα·αααΎααα α»αααΎαα‘αΎααα αααααααΎαααΆαααααα·ααααα·ααΆα? α§ααΆα αααααΌα αααα
=> 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;
ERROR: current transaction is aborted, commands ignored until end of transaction block
α αΎααααααΈααΆα’αααααααΆααΆαααααΎααΆαααααΆααααααΌαααααα PostgreSQL ααΉαααΆαααΆαααα’αααΈααΆαααααααα
=> COMMIT;
ROLLBACK
α ααα»α’αααΈααΆαααΆααααα·ααααα·ααΆααα·αα’αΆα αααααααααΆααααΈαααΆααα? ααΆααα·αααΊααΆααα α»αα’αΆα ααΎαα‘αΎαααΆααααααααααΎαα’αΆα α αΌααα αααααααααΆαααααΆααααααΌα - α’αΆααΌαα·α ααααΌααααΈααααααα·ααααα·ααΆαααααα ααα»ααααααααα·ααααα·ααααΉαααααΌαααΆαααααΆαα ααΌα αααα»αα§ααΆα αααααααααΎα αααααααα·ααααα·ααααΆααααααααααααΎααααΈααααΎαα αα α»ααααααααΆααα½ααα½ααα»ααααααΆαααα α»αα
=> 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)
ααΆααααΌααααα·ααΆαααΆ psql ααΆααααααα½αααααα ααα’αα»ααααΆαα±ααααααα·ααααα·ααΆααααααααααΆααααΈααΆααααΆαααααΌα ααΆαααααααΆαααααααααα·ααααα·αααααααΆαααα α»αααααΌαααΆαααααα·ααααα‘αααααα·αα
=> 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;
ααΆαα·ααα·ααΆααααα»αααΆαααααΆαααααΆαα αααα»αααααααα psql αα·αααΆααΆααα ααα»α αααααΆαα»αααααααααααα α αααααα»αααΆααααααααΆααΈαα½αα α αΎααααα»αααααΈααΆαααΆααααΆαααα αΆααααααΎαααΆααα·ααααα‘αααααα·αα ααααααααα·αααααΌαααΆαααααΎααΆαααααΆαααΎααα α αΆααααΆααααΈααΆααααααα ααα»α αααααΆαα»α (ααΌααααΈαααα·ααα·ααααααααα αααα½αααΆ) ααΆαααααααααΉαααΆαα αααΆαααΎαα
ααααα: www.habr.com