Paras tapa täyttää uusi sarake isossa taulukossa?

• Miksi suuri INSERT hidastuu ja levyn käyttö räjähtää?

Alla oleva koodi käyttää -funktiota ylimääräisestä uuid-oss -moduulista .

• Lukitse taulukko samanaikaisilta muutoksilta SHARE -tilassa (sallien silti samanaikaisen lukemisen). Yritykset kirjoittaa taulukkoon odottavat ja lopulta epäonnistuvat. Katso alla.

• Kopioi koko taulukko täyttäessäsi uutta saraketta lennossa – mahdollisesti järjestä rivejä suotuisasti ollessasi siinä.
  Jos aiot järjestää rivejä uudelleen, muista asettaa work_mem tarpeeksi korkealle, jotta voit lajitella RAM-muistissa tai yhtä korkealla kuten sinulla on varaa (vain istuntosi, ei maailmanlaajuisesti).

• Sitten lisää rajoituksia, vieraita avaimia, indeksejä, liipaisimia jne. uuteen pöytä. Päivitettäessä taulukon suuria osia on paljon nopeampi luoda indeksit tyhjästä kuin lisätä rivejä iteratiivisesti. Aiheeseen liittyviä ohjeita käyttöoppaassa.

• Kun uusi taulukko on valmis, pudota vanha ja nimeä uusi jotta se olisi drop-in-korvaava. Vain tämä viimeinen vaihe saa vanhan pöydän lukituksen lopputapahtumaan – jonka pitäisi olla nyt hyvin lyhyt.
  Se edellyttää myös, että poistat objektin taulukkotyypistä riippuen (näkymät, toiminnot, jotka käyttävät taulukotyyppiä) allekirjoituksessa …) ja luo ne myöhemmin.

• Tee kaikki yhdessä tapahtumassa, jotta vältät puutteelliset tilat.

BEGIN; LOCK TABLE tbl IN SHARE MODE; SET LOCAL work_mem = "???? MB"; -- just for this transaction CREATE TABLE tbl_new AS SELECT uuid_generate_v1() AS tbl_uuid, <list of all columns in order> FROM tbl ORDER BY ??; -- optionally order rows favorably while being at it. ALTER TABLE tbl_new ALTER COLUMN tbl_uuid SET NOT NULL , ALTER COLUMN tbl_uuid SET DEFAULT uuid_generate_v1() , ADD CONSTRAINT tbl_uuid_uni UNIQUE(tbl_uuid); -- more constraints, indices, triggers? DROP TABLE tbl; ALTER TABLE tbl_new RENAME tbl; -- recreate views etc. if any COMMIT; 

Tämän pitäisi olla nopeinta. Minkä tahansa muun päivitysmenetelmän on myös kirjoitettava koko taulukko uudestaan vain kalliimmalla tavalla. Menet tällä reitillä vain, jos sinulla ei ole tarpeeksi vapaata tilaa levyllä tai sinulla ei ole varaa lukita koko taulukkoa tai luoda virheitä samanaikaisille kirjoitusyrityksille.

Mitä tapahtuu samanaikaisille kirjoituksille?

Muu tapahtuma (muissa istunnoissa) yrittää INSERT / UPDATE / DELETE samassa taulukossa, kun tapahtumasi on ottanut lukituksen SHARE, odottaa, kunnes lukitus vapautetaan tai aikakatkaisu alkaa, sen mukaan kumpi tulee ensin. Ne epäonnistui kummallakaan tavalla, koska taulukko, johon yritettiin kirjoittaa, on poistettu niiden alta.

Uudessa taulukossa on uusi table OID, mutta samanaikainen tapahtuma on jo ratkaissut taulukon nimen edellisen taulukon OID: ksi. Kun lukko lopulta vapautetaan, he yrittävät lukita taulukon itse ennen kuin kirjoitat siihen ja huomaavat, että se ” on poissa.Postgres vastaa:

ERROR: could not open relation with OID 123456

Missä 123456 on vanhan taulukon OID. Sinun on pyydettävä tämä poikkeus ja yritettävä kyselyjä uudelleen sovelluskoodissasi sen välttämiseksi.

Jos sinulla ei ole varaa siihen, sinun on säilytettävä alkuperäinen taulukko.

Olemassa olevan taulukon, vaihtoehto 1, säilyttäminen

Päivitys paikallaan (mahdollisesti päivityksen suorittaminen pienillä segmenteillä kerrallaan) ennen NOT NULL -rajoituksen lisäämistä . Uuden sarakkeen lisääminen NULL-arvoilla ja ilman NOT NULL rajoitusta on halpaa.
Koska Postgres 9.2 voit myös luoda CHECK rajoitteen NOT VALID :

Rajoitus jatkuu edelleen pakotetaan myöhempiin lisäyksiin tai päivityksiin

Tämän avulla voit päivittää rivejä peu à peu – useissa erillisissä tapahtumissa . Tämä välttää rivilukkojen pitämisen liian kauan ja se mahdollistaa myös kuolleiden rivien uudelleenkäytön. (Sinun on suoritettava VACUUM manuaalisesti, jos välissä ei ole tarpeeksi aikaa automaattipölyn käynnistämiseen.) Lisää lopuksi NOT NULL rajoitus ja poista NOT VALID CHECK rajoitus:

ALTER TABLE tbl ADD CONSTRAINT tbl_no_null CHECK (tbl_uuid IS NOT NULL) NOT VALID; -- update rows in multiple batches in separate transactions -- possibly run VACUUM between transactions ALTER TABLE tbl ALTER COLUMN tbl_uuid SET NOT NULL; ALTER TABLE tbl ALTER DROP CONSTRAINT tbl_no_null; 

Liittyvä vastaus NOT VALID tarkemmin:

• Poista käytöstä kaikki rajoitukset ja taulukon tarkistukset palauttaessasi kaatopaikkaa

Olemassa olevan taulukon säilyttäminen, vaihtoehto 2

Valmista uusi tila väliaikaisessa taulukossa , TRUNCATE alkuperäinen ja täytä temp-taulukosta. Kaikki yhdessä tapahtumassa . täytyy vielä ottaa SHARE -lukko ennen PR uusi taulukko estää samanaikaisten kirjoitusten menettämisen.

Yksityiskohdat näissä vastaavissa vastauksissa SO: ssa:

• Paras tapa poistetaan miljoonia rivejä tunnuksen mukaan
• Lisätäänkö uusi sarake ilman taulukon lukitusta?

Kommentit

• Upea vastaus! Tarkalleen etsimäni tiedot. Kaksi kysymystä 1. Onko sinulla ajatusta helposta tavasta testata, kuinka kauan tällainen toiminta kestää? 2. Jos kestää viittä minuuttia, mitä tapahtuu toiminnoille, jotka yrittävät päivittää riviä taulukossa noiden viiden minuutin aikana?
• @CollinPeters: 1. Leijona ' s osuus ajasta menee suuren pöydän kopiointiin – ja mahdollisesti indeksien ja rajoitusten luomiseen (se riippuu). Pudotus ja uudelleennimeäminen on halpaa. Testattavaksi voit suorittaa valmistellun SQL-komentosarjan ilman LOCK – DROP asti. Pystyin lausumaan vain villiä ja hyödyttömiä arvauksia. Mitä tulee 2. kohtaan, ota huomioon vastaukseni lisäys.
• @ErwinBrandstetter Jatka näkymien luontia, joten jos minulla on tusina näkymää, jotka käyttävät edelleen vanhaa taulukkoa (oid) taulukon uudelleennimeämisen jälkeen. Onko olemassa mitään tapaa suorittaa syvä korvaaminen sen sijaan, että koko näkymän päivitys / luominen suoritettaisiin uudelleen?
• @CodeFarmer: Jos vain nimeät taulukon uudelleen, näkymät toimivat edelleen uudelleen nimetyn taulukon kanssa. Jos haluat käyttää näkymiä uuden -taulukon sijasta, sinun on luotava ne uudelleen uuden taulukon perusteella. (Antaa myös vanhan taulukon poistamisen.) Ei (käytännön) kiertotapa.
• koska 9.2 postgres ei ' heitä could not open relation with OID 123456