ProHoster > Օրագիր > Վարչակազմը > Կլոնային գրառումների մաքրում աղյուսակից առանց PK-ի

Կլոնային գրառումների մաքրում աղյուսակից առանց PK-ի

Կան իրավիճակներ, երբ սեղան առանց հիմնական բանալիի կամ որևէ այլ եզակի ինդեքս, հսկողության պատճառով, ներառված են արդեն գոյություն ունեցող գրառումների ամբողջական կլոններ:

Կլոնային գրառումների մաքրում աղյուսակից առանց PK-ի

Օրինակ, ժամանակագրական չափման արժեքները գրվում են PostgreSQL-ում՝ օգտագործելով COPY հոսքը, այնուհետև հանկարծակի ձախողում է տեղի ունենում, և լրիվ նույնական տվյալների մի մասը նորից գալիս է:

Ինչպե՞ս ազատել տվյալների բազան ավելորդ կլոններից:

Երբ PK-ն օգնական չէ

Ամենահեշտ ճանապարհն առաջին հերթին նման իրավիճակի առաջացումը կանխելն է: Օրինակ՝ գլորել PRIMARY KEY: Բայց դա միշտ չէ, որ հնարավոր է առանց պահեստավորված տվյալների ծավալի մեծացման:

Օրինակ, եթե աղբյուրի համակարգի ճշգրտությունը ավելի բարձր է, քան դաշտի ճշգրտությունը տվյալների բազայում.

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}

Նկատեցի՞ք։ 00:00:02-ի փոխարեն հետհաշվարկը գրանցվել է տվյալների բազայում ts-ով մեկ վայրկյան առաջ, բայց կիրառական տեսանկյունից բավականին վավեր է մնացել (ի վերջո, տվյալների արժեքները տարբեր են):

Իհարկե, դուք կարող եք դա անել PK (մետրիկ, ts) - բայց այնուհետև մենք կստանանք ներդիրների հակասություններ վավեր տվյալների համար:

Կարող է անել PK (մետրիկ, ts, տվյալներ) - բայց դա մեծապես կբարձրացնի դրա ծավալը, որը մենք չենք օգտագործի:

Հետեւաբար, ամենաճիշտ տարբերակը սովորական ոչ եզակի ինդեքս պատրաստելն է (մետրիկ, ts) և խնդիրներով զբաղվել այն բանից հետո, եթե դրանք առաջանան:

«Կլոնիկ պատերազմը սկսված է».

Ինչ-որ դժբախտ պատահար է տեղի ունեցել, և այժմ մենք պետք է ոչնչացնենք կլոնի գրառումները աղյուսակից:

Կլոնային գրառումների մաքրում աղյուսակից առանց PK-ի

Եկեք մոդելավորենք սկզբնական տվյալները.

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

Այստեղ մեր ձեռքը երեք անգամ դողաց, Ctrl+V-ն խրվեց, իսկ հիմա...

Նախ, եկեք հասկանանք, որ մեր սեղանը կարող է շատ մեծ լինել, ուստի բոլոր կլոնները գտնելուց հետո մեզ խորհուրդ է տրվում բառացիորեն «մատը խոթել» ջնջելու համար: կոնկրետ գրառումներ՝ առանց դրանք նորից փնտրելու.

Եվ կա այդպիսի միջոց՝ սա հասցեագրումը ctid-ի կողմից, կոնկրետ գրառման ֆիզիկական նույնացուցիչը:

Այսինքն, առաջին հերթին մենք պետք է հավաքենք գրառումների ctid-ը աղյուսակի տողի ամբողջական բովանդակության համատեքստում։ Ամենապարզ տարբերակն է ամբողջ տողը տեքստի մեջ գցել.

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

Հնարավո՞ր է չնկարահանել:Սկզբունքորեն դա հնարավոր է շատ դեպքերում։ Քանի դեռ չեք սկսել օգտագործել այս աղյուսակի դաշտերը տեսակներ առանց հավասարության օպերատորի:

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

Այո, մենք անմիջապես տեսնում ենք, որ եթե զանգվածում մեկից ավելի մուտք կա, սրանք բոլորը կլոններ են: Եկեք պարզապես թողնենք դրանք.

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)

Նրանց համար, ովքեր սիրում են ավելի կարճ գրելԿարող եք նաև գրել այսպես.

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

Քանի որ սերիականացված տողի արժեքը ինքնին մեզ համար հետաքրքիր չէ, մենք այն պարզապես դուրս նետեցինք ենթհարցման վերադարձված սյունակներից:

Քիչ բան է մնացել անելու. ստիպեք ՋՆՋԵԼ օգտագործել մեր ստացած հավաքածուն.

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

Եկեք ստուգենք ինքներս մեզ.

Կլոնային գրառումների մաքրում աղյուսակից առանց PK-ի
[նայեք բացատրություն.tensor.ru-ին]

Այո, ամեն ինչ ճիշտ է. մեր 3 գրառումներն ընտրվել են ամբողջ աղյուսակի մեկ Seq Scan-ի համար, և Ջնջել հանգույցն օգտագործվել է տվյալների որոնման համար: միայնակ փոխանցում Tid Scan-ով:

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

Եթե ​​դուք մաքրել եք շատ գրառումներ, մի մոռացեք գործարկել VACUUM ANALYZE-ը.

Եկեք ստուգենք ավելի մեծ աղյուսակ և ավելի մեծ թվով կրկնօրինակներով.

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;

Կլոնային գրառումների մաքրում աղյուսակից առանց PK-ի
[նայեք բացատրություն.tensor.ru-ին]

Այսպիսով, մեթոդը հաջողությամբ աշխատում է, բայց այն պետք է օգտագործվի որոշակի զգուշությամբ: Որովհետև ջնջված յուրաքանչյուր գրառման համար կա մեկ տվյալների էջ, որը կարդացվում է Tid Scan-ում և մեկը՝ Delete-ում:

Source: www.habr.com

Добавить комментарий