Na-ehichapụ ndekọ clone site na tebụl na-enweghị PK

Enwere ọnọdụ mgbe gaa na tebụl na-enweghị isi igodo ma ọ bụ ụfọdụ ndeksi pụrụ iche, n'ihi nleba anya, a na-agụnye clones zuru ezu nke ndekọ dị adị.

Dịka ọmụmaatụ, a na-ede ụkpụrụ nke metric oge n'ime PostgreSQL site na iji iyi iyi COPY, mgbe ahụ enwere ọdịda mberede, akụkụ nke data ahụ kpamkpam na-abịarute ọzọ.

Kedu ka esi kpochapụ nchekwa data nke clones na-enweghị isi?

Mgbe PK abụghị onye inyeaka

Ụzọ kachasị mfe bụ igbochi ọnọdụ dị otú ahụ na mbụ. Dịka ọmụmaatụ, tụgharịa igodo isi. Mana nke a anaghị ekwe omume mgbe niile n'ebughị ibu nke data echekwara.

Dịka ọmụmaatụ, ọ bụrụ na izi ezi nke usoro isi mmalite dị elu karịa izi ezi nke ubi na nchekwa data:

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}

Ị hụrụ? The Countdown instead of 00:00:02 e dekọrọ na nchekwa data na ts nke abụọ na mbụ, mana ọ nọgidere na-adị irè site na ngwa ngwa (mgbe niile, ụkpụrụ data dị iche!).

N'ezie ị nwere ike ime ya PK (metric, ts) - mana mgbe ahụ anyị ga-enweta esemokwu ntinye maka data ziri ezi.

Nwere ike ime PK (metric, ts, data) - mana nke a ga-abawanye oke olu ya, nke anyị agaghị eji.

Ya mere, nhọrọ kachasị mma bụ ịme ndenye aha na-abụghị nke pụrụ iche (metric, ts) ma dozie nsogbu mgbe eziokwu ahụ gasịrị ma ọ bụrụ na ha bilitere.

"Agha clonic amalitela"

Ụdị ihe mberede ụfọdụ mere, ma ugbu a, anyị ga-ebibi ihe ndekọ clone site na tebụl.

Ka anyị ṅomie data izizi:

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)
;

Ebe aka anyị mara jijiji ugboro atọ, Ctrl+V rapaara, ma ugbu a ...

Nke mbụ, ka anyị ghọta na tebụl anyị nwere ike buru oke ibu, yabụ mgbe anyị chọtara clones niile, ọ bụ ihe amamihe dị na ya ka anyị jiri “pịa mkpịsị aka anyị” ihichapụ. ndekọ dị iche iche na-achọgharịghị ha.

Na e nwere ụzọ dị otú ahụ - nke a na-ekwu okwu site na ctid, ihe nchọpụta anụ ahụ nke otu ndekọ aha.

Nke ahụ bụ, nke mbụ, anyị kwesịrị ịnakọta ctid nke ihe ndekọ na ọnọdụ nke ọdịnaya zuru ezu nke ahịrị tebụl. Nhọrọ kacha mfe bụ ịtụba ahịrị niile n'ime ederede:

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)"}

Ọ ga-ekwe omume ịghara nkedo?Na ụkpụrụ, ọ ga-ekwe omume n'ọtụtụ ọnọdụ. Ruo mgbe ịmalitere iji ubi na tebụl a ụdị na-enweghị onye ọrụ nha nhata:

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

Ee, anyị na-ahụ ozugbo na ọ bụrụ na enwere ihe karịrị otu ntinye n'usoro, ndị a niile bụ clones. Ka anyị hapụ ha:

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)

Maka ndị na-amasị ide nkenkeỊ nwekwara ike dee ya dị ka nke a:

SELECT
  unnest((array_agg(ctid))[2:])
FROM
  tbl T
GROUP BY
  T::text;

Ebe ọ bụ na uru nke eriri serialized n'onwe ya adịghị amasị anyị, naanị anyị tụpụrụ ya na ogidi ndị eweghachitere nke subquery.

Ọ dị ntakịrị ntakịrị ime - mee ka ihichapụ jiri setịpụ anyị nwetara:

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[]);

Ka anyị lelee onwe anyị:

[lee nkọwa.tensor.ru]

Ee, ihe niile ziri ezi: ahọpụtara ihe ndekọ 3 anyị maka naanị Seq Scan nke tebụl dum, a na-ejikwa ọnụ nhichapụ wee chọọ data. otu ngafe na Tid Scan:

->  Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1)
      TID Cond: (ctid = ANY ($0))

Ọ bụrụ na ị kpochapụrụ ọtụtụ ndekọ, echefula ịgba ọsọ VACUUM ANALYZE.

Ka anyị lelee tebụl buru ibu yana ọnụ ọgụgụ ka ukwuu nke oyiri:

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;

[lee nkọwa.tensor.ru]

Yabụ, usoro a na-arụ ọrụ nke ọma, mana a ga-eji nlezianya mee ya. N'ihi na ndekọ ọ bụla ehichapụrụ, enwere otu ibe data a na-agụ na Tid Scan, yana otu dị na Hichapụ.

isi: www.habr.com