ProHoster > Blag > Riarachán > Antipatterns PostgreSQL: “Ní hé Infinity an teorainn!”, nó Beagán faoi atarlú

Antipatterns PostgreSQL: “Ní hé Infinity an teorainn!”, nó Beagán faoi atarlú

atarlaithe - meicníocht an-chumhachtach agus áisiúil má dhéantar na gníomhartha “doimhneacha” céanna ar shonraí gaolmhara. Ach is olc é an t-aischur neamhrialaithe a d'fhéadfadh ceachtar den dá cheann a bheith mar thoradh air fhorghníomhú gan teorainn próiseas, nó (a tharlaíonn níos minice) go "ithe" gach cuimhne atá ar fáil.

Antipatterns PostgreSQL: “Ní hé Infinity an teorainn!”, nó Beagán faoi atarlú
Oibríonn DBMS maidir leis seo ar na prionsabail chéanna - "Dúirt siad liom tochailt, mar sin déanaim tochailtNí hamháin gur féidir le d’iarratas próisis chomharsanachta a mhoilliú, agus acmhainní próiseálaí á n-úsáid i gcónaí, ach freisin “titim” ar an mbunachar sonraí iomlán, “ag ithe” gach cuimhne atá ar fáil. cosaint i gcoinne atarlú gan teorainn - freagracht an fhorbróra féin.

In PostgreSQL, tá an cumas chun fiosrúcháin athfhillteacha a úsáid trí WITH RECURSIVE le feiceáil san am i gcuimhne ar leagan 8.4, ach fós is féidir leat teacht ar iarratais “gan chosaint” a d’fhéadfadh a bheith leochaileach go rialta. Conas fáil réidh le fadhbanna den chineál seo duit féin?

Ná scríobh ceisteanna athfhillteacha

Agus scríobh cinn neamh-athfhillteach. Le meas, Do K.O.

Go deimhin, soláthraíonn PostgreSQL go leor feidhmiúlacht ar féidir leat a úsáid chun aon athfhillteach a chur i bhfeidhm.

Bain úsáid as cur chuige bunúsach difriúil i leith na faidhbe

Uaireanta is féidir leat breathnú ar an bhfadhb ón "taobh difriúil". Thug mé sampla de chás den sórt sin san alt "SQL HowTo: 1000 agus bealach amháin comhiomlánaithe" — tacar uimhreacha a iolrú gan feidhmeanna comhiomlána saincheaptha a úsáid:

WITH RECURSIVE src AS (
  SELECT '{2,3,5,7,11,13,17,19}'::integer[] arr
)
, T(i, val) AS (
  SELECT
    1::bigint
  , 1
UNION ALL
  SELECT
    i + 1
  , val * arr[i]
  FROM
    T
  , src
  WHERE
    i <= array_length(arr, 1)
)
SELECT
  val
FROM
  T
ORDER BY -- отбор финального результата
  i DESC
LIMIT 1;

Is féidir rogha ó shaineolaithe matamaitice a chur in ionad an iarratais seo:

WITH src AS (
  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
  exp(sum(ln(prime)))::integer val
FROM
  src;

Úsáid Generation_series in ionad lúba

Ligean le rá go bhfuil muid ag tabhairt aghaidhe ar an tasc a ghiniúint gach réimír féideartha do teaghrán 'abcdefgh':

WITH RECURSIVE T AS (
  SELECT 'abcdefgh' str
UNION ALL
  SELECT
    substr(str, 1, length(str) - 1)
  FROM
    T
  WHERE
    length(str) > 1
)
TABLE T;

An bhfuil tú cinnte go bhfuil gá agat le hathchúrsaí anseo?.. Má úsáideann tú LATERAL и generate_series, mar sin ní bheidh CTE uait fiú:

SELECT
  substr(str, 1, ln) str
FROM
  (VALUES('abcdefgh')) T(str)
, LATERAL(
    SELECT generate_series(length(str), 1, -1) ln
  ) X;

Athraigh struchtúr an bhunachair shonraí

Mar shampla, tá tábla teachtaireachtaí fóraim agat le naisc ón té a d’fhreagair, nó snáithe isteach líonra sóisialta:

CREATE TABLE message(
  message_id
    uuid
      PRIMARY KEY
, reply_to
    uuid
      REFERENCES message
, body
    text
);
CREATE INDEX ON message(reply_to);

Antipatterns PostgreSQL: “Ní hé Infinity an teorainn!”, nó Beagán faoi atarlú
Bhuel, is cosúil le hiarratas tipiciúil chun gach teachtaireacht ar ábhar amháin a íoslódáil:

WITH RECURSIVE T AS (
  SELECT
    *
  FROM
    message
  WHERE
    message_id = $1
UNION ALL
  SELECT
    m.*
  FROM
    T
  JOIN
    message m
      ON m.reply_to = T.message_id
)
TABLE T;

Ach ós rud é go dteastaíonn an t-ábhar iomlán uainn i gcónaí ón bhfréamhtheachtaireacht, cén fáth nach ndéanaimid cuir a ID le gach iontráil uathoibríoch?

-- добавим поле с общим идентификатором темы и индекс на него
ALTER TABLE message
  ADD COLUMN theme_id uuid;
CREATE INDEX ON message(theme_id);

-- инициализируем идентификатор темы в триггере при вставке
CREATE OR REPLACE FUNCTION ins() RETURNS TRIGGER AS $$
BEGIN
  NEW.theme_id = CASE
    WHEN NEW.reply_to IS NULL THEN NEW.message_id -- берем из стартового события
    ELSE ( -- или из сообщения, на которое отвечаем
      SELECT
        theme_id
      FROM
        message
      WHERE
        message_id = NEW.reply_to
    )
  END;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ins BEFORE INSERT
  ON message
    FOR EACH ROW
      EXECUTE PROCEDURE ins();

Antipatterns PostgreSQL: “Ní hé Infinity an teorainn!”, nó Beagán faoi atarlú
Anois is féidir ár bhfiosrúchán athfhillteach iomlán a laghdú go dtí seo:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Úsáid "teorainneacha" i bhfeidhm

Mura bhfuilimid in ann struchtúr an bhunachair sonraí a athrú ar chúis éigin, féachaimis cad is féidir linn a bheith ag brath air ionas nach n-eascróidh earráidí gan deireadh fiú má bhíonn earráid sna sonraí.

Cuntar doimhneacht athfhillteach

Níl le déanamh againn ach an cuntar a mhéadú faoi cheann amháin ag gach céim athchúrsála go dtí go sroichimid teorainn a mheasaimid ar léir a bheith neamhleor:

WITH RECURSIVE T AS (
  SELECT
    0 i
  ...
UNION ALL
  SELECT
    i + 1
  ...
  WHERE
    T.i < 64 -- предел
)

Pro: Nuair a dhéanaimid iarracht lúb a dhéanamh, ní dhéanfaimid níos mó ná teorainn sonraithe na n-atriallta “go domhain”.
CONS: Níl aon ráthaíocht ann nach ndéanfaimid an taifead céanna a phróiseáil arís - mar shampla, ag doimhneacht 15 agus 25, agus ansin gach +10. Agus níor gheall aon duine faic faoi “leithead”.

Go foirmiúil, ní bheidh a leithéid de atarlú gan teorainn, ach má thagann méadú easpónantúil ar líon na dtaifead ag gach céim, tá a fhios againn go léir go maith conas a chríochnaíonn sé...

Antipatterns PostgreSQL: “Ní hé Infinity an teorainn!”, nó Beagán faoi atarlúféach “Fadhb na ngrán ar chlár fichille”

Caomhnóir an "chosán"

Mar a chéile cuirimid na haitheantóirí oibiachta ar fad ar tháinig muid trasna orthu feadh an chosáin athchúrsála isteach in eagar, ar “conair” uathúil é:

WITH RECURSIVE T AS (
  SELECT
    ARRAY[id] path
  ...
UNION ALL
  SELECT
    path || id
  ...
  WHERE
    id <> ALL(T.path) -- не совпадает ни с одним из
)

Pro: Má tá timthriall sna sonraí, ní dhéanfaimid an taifead céanna a phróiseáil arís agus arís eile laistigh den chosán céanna.
CONS: Ach ag an am céanna, is féidir linn na taifid go léir a sheachbhóthar go litriúil gan muid féin a athrá.

Antipatterns PostgreSQL: “Ní hé Infinity an teorainn!”, nó Beagán faoi atarlúféach "Fadhb le Gluaiseacht Knight"

Teorainn Fad Conair

Chun staid an athchúlaithe a sheachaint “ag fánaíocht” ag doimhneacht dothuigthe, is féidir linn an dá mhodh roimhe seo a chur le chéile. Nó, mura dteastaíonn uainn tacaíocht a thabhairt do réimsí nach bhfuil gá leo, cuir meastachán ar fhad an chosáin leis an gcoinníoll chun leanúint ar aghaidh leis an athchúrsa:

WITH RECURSIVE T AS (
  SELECT
    ARRAY[id] path
  ...
UNION ALL
  SELECT
    path || id
  ...
  WHERE
    id <> ALL(T.path) AND
    array_length(T.path, 1) < 10
)

Roghnaigh modh a thaitníonn leat!

Foinse: will.com

Add a comment