Aia nā kūlana i ka wā i ka papaʻaina me ke kī mua ʻole a i ʻole kekahi papa kuhikuhi kūikawā ʻē aʻe, ma muli o ka nānā ʻana, ua hoʻokomo ʻia nā clones piha o nā moʻolelo i loaʻa.
No ka laʻana, ua kākau ʻia nā waiwai o kahi metric chronological i PostgreSQL me ka hoʻohana ʻana i kahi kahawai COPY, a laila aia ka hāʻule koke, a hiki hou mai kahi hapa o ka ʻikepili like.
Pehea e hoʻopau ai i ka waihona o nā clones pono ʻole?
Inā ʻaʻole kōkua ʻo PK
ʻO ke ala maʻalahi ka pale ʻana i kēlā ʻano kūlana i ka wā mua. No ka laʻana, ʻōwili PIMARY KEY. Akā ʻaʻole hiki kēia i nā manawa a pau me ka hoʻonui ʻole i ka nui o ka ʻikepili i mālama ʻia.
No ka laʻana, inā ʻoi aku ka kiʻekiʻe o ka pololei o ka ʻōnaehana kumu ma mua o ka pololei o ke kahua ma ka waihona:
metric | ts | data
--------------------------------------------------
cpu.busy | 2019-12-20 00:00:00 | {"value" : 12.34}
cpu.busy | 2019-12-20 00:00:01 | {"value" : 10}
cpu.busy | 2019-12-20 00:00:01 | {"value" : 11.2}
cpu.busy | 2019-12-20 00:00:03 | {"value" : 15.7}
Ua ʻike ʻoe? The countdown instead of 00:00:02 ua hoʻopaʻa ʻia i ka waihona me ts kekona ma mua, akā ua kūpaʻa mau ʻia mai kahi manaʻo noi (ma hope o nā mea āpau, ʻokoʻa nā koina ʻikepili!).
ʻOiaʻiʻo hiki iā ʻoe ke hana PK(metric, ts) - akā, e loaʻa iā mākou nā paio hoʻokomo no ka ʻikepili kūpono.
E hiki ai ke hana PK(metric, ts, ʻikepili) - akā e hoʻonui nui kēia i kāna leo, ʻaʻole mākou e hoʻohana.
No laila, ʻo ka koho kūpono loa ʻo ia ka hana ʻana i kahi papa kuhikuhi maʻamau ʻole (metric, ts) a hoʻoponopono i nā pilikia ma hope o ka ʻoiaʻiʻo inā e kū mai.
"Ua hoʻomaka ke kaua clonic"
Ua hiki mai kekahi ʻano pōʻino, a i kēia manawa pono mākou e luku i nā moʻolelo clone mai ka papaʻaina.
E hoʻohālike kākou i ka ʻikepili kumu:
CREATE TABLE tbl(k text, v integer);
INSERT INTO tbl
VALUES
('a', 1)
, ('a', 3)
, ('b', 2)
, ('b', 2) -- oops!
, ('c', 3)
, ('c', 3) -- oops!!
, ('c', 3) -- oops!!
, ('d', 4)
, ('e', 5)
;Eia ko makou lima haalulu ekolu manawa, Ctrl+V ua paa, a i keia manawa...
ʻO ka mea mua, e hoʻomaopopo kākou he nui loa kā mākou papaʻaina, no laila, ma hope o ka loaʻa ʻana o nā clones āpau, pono mākou e "poke i ko mākou manamana lima" e holoi. nā moʻolelo kikoʻī me ka huli ʻole ʻana iā lākou.
A aia kekahi ala - ʻo kēia , ka mea hōʻike kino o kahi moʻolelo kikoʻī.
ʻO ia, ʻo ka mea mua, pono mākou e hōʻiliʻili i ka ctid o nā moʻolelo i ka pōʻaiapili o ka ʻike piha o ka lālani papaʻaina. ʻO ka koho maʻalahi ke hoʻolei i ka laina holoʻokoʻa i loko o ka kikokikona:
SELECT
T::text
, array_agg(ctid) ctids
FROM
tbl T
GROUP BY
1;
t | ctids
---------------------------------
(e,5) | {"(0,9)"}
(d,4) | {"(0,8)"}
(c,3) | {"(0,5)","(0,6)","(0,7)"}
(b,2) | {"(0,3)","(0,4)"}
(a,3) | {"(0,2)"}
(a,1) | {"(0,1)"}
ʻAʻole hiki ke hoʻolei?Ma ke kumu, hiki i ka hapanui o nā hihia. A hiki i kou hoʻomaka ʻana e hoʻohana i nā kahua ma kēia pākaukau ʻano me ka mea hoʻohana like ʻole:
CREATE TABLE tbl(k text, v integer, x point);
SELECT
array_agg(ctid) ctids
FROM
tbl T
GROUP BY
T;
-- ERROR: could not identify an equality operator for type tbl
ʻAe, ʻike koke mākou inā ʻoi aku ma mua o hoʻokahi komo i loko o ka array, he mau clones kēia. E waiho wale iā lākou:
SELECT
unnest(ctids[2:])
FROM
(
SELECT
array_agg(ctid) ctids
FROM
tbl T
GROUP BY
T::text
) T;unnest
------
(0,6)
(0,7)
(0,4)No ka poʻe makemake e kākau pōkoleHiki iā ʻoe ke kākau e like me kēia:
SELECT
unnest((array_agg(ctid))[2:])
FROM
tbl T
GROUP BY
T::text;No ka mea ʻaʻole hoihoi iā mākou ka waiwai o ke kaula serialized, ua kiola wale mākou iā ia mai nā kolamu i hoʻihoʻi ʻia o ka subquery.
He wahi liʻiliʻi wale nō i koe - e hoʻohana iā DELETE i ka hoʻonohonoho i loaʻa iā mākou:
DELETE FROM
tbl
WHERE
ctid = ANY(ARRAY(
SELECT
unnest(ctids[2:])
FROM
(
SELECT
array_agg(ctid) ctids
FROM
tbl T
GROUP BY
T::text
) T
)::tid[]);E nānā kākou iā kākou iho:
ʻAe, pololei nā mea a pau: ua koho ʻia kā mākou mau moʻolelo 3 no ka Seq Scan wale nō o ka papa holoʻokoʻa, a ua hoʻohana ʻia ka node Delete e ʻimi i ka ʻikepili. holo hoʻokahi me Tid Scan:
-> Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1)
TID Cond: (ctid = ANY ($0))Inā ʻoe i holoi i nā moʻolelo he nui, .
E nānā kākou no ka papaʻaina nui aʻe a me ka nui o nā kope:
TRUNCATE TABLE tbl;
INSERT INTO tbl
SELECT
chr(ascii('a'::text) + (random() * 26)::integer) k -- a..z
, (random() * 100)::integer v -- 0..99
FROM
generate_series(1, 10000) i;
No laila, hana maikaʻi ke ʻano, akā pono e hoʻohana me ka akahele. No ka mea, no kēlā me kēia moʻolelo i holoi ʻia, hoʻokahi ʻaoʻao ʻikepili i heluhelu ʻia ma Tid Scan, a hoʻokahi ma Delete.
Source: www.habr.com