Forums PostgreSQL.fr

rbidocho

Membre

Mise à jour d'une "grosse" table

Bonjour,

j'ai une table aaa qui contient quelques 25 millions de lignes.
je voudrais faire un update de toutes les valeurs d'une colonne (f) via une opération avec des valeurs qui sont issues de 2 tables dont l'une est celle que je veux mettre à jour.

mais déjà quand je fais la "simple" requête suivante :

SELECT 
  aaa.e/bbb.a as f
FROM 
  public.bbb, 
  public.aaa
WHERE 
  bbb.c = aaa.c AND
  bbb.s = aaa.s AND
  bbb.r = aaa.r AND
  bbb.n = aaa.n;

pgadmin me réponds :

out of memory for query result

********** Erreur **********

la table aaa contient :
1 pk
2 fk
2 index

donc voici ce que je compte faire :

ALTER TABLE aaa DROP CONSTRAINT aaa_pkey;
ALTER TABLE aaa DROP CONSTRAINT aaa_p_fkey;
ALTER TABLE aaa DROP CONSTRAINT aaa_c_fkey;
ALTER TABLE aaa DROP CONSTRAINT aaa_c_key;
DROP INDEX ind_fk_bbb;
DROP INDEX ind_fk_u_1;

UPDATE bbb
INNER JOIN aaa 
ON (bbb.n = aaa.n) 
AND (bbb.r = aaa.r) 
AND (bbb.s = aaa.s) 
AND (bbb.c = aaa.c) 
SET aaa.f = aaa.e/bbb.a;

ALTER TABLE aaa
  ADD CONSTRAINT aaa_pkey PRIMARY KEY(c, s, r, n, p);
ALTER TABLE aaa
  ADD CONSTRAINT aaa_p_fkey FOREIGN KEY (p)
      REFERENCES u (p) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE aaa
  ADD CONSTRAINT aaa_c_fkey FOREIGN KEY (c, s, r, n)
      REFERENCES bbb (c, s, r, n) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE aaa
  ADD CONSTRAINT aaa_c_key UNIQUE(c, s, r, n, p);
CREATE INDEX ind_fk_bbb
  ON aaa
  USING btree
  (c, s, r, n);
CREATE INDEX ind_fk_u_1
  ON aaa
  USING btree
  (p);

Est ce que cela ne va pas planter en plein milieu ???
Ou peut on faire ce genre de maj par paquet ???

merci de vos contributions

RB

ps : config -->
postgresql 8.3.7
windows server 2003 r2
dell server pe2950
Intel Xeon CPU E5310 @ 1.60GHz
8 Go de Ram

Marc Cousin

Membre

Re : Mise à jour d'une "grosse" table

Le out of memory for query result ne doit pas provenir de postgresql, mais du client, qui n'arrive pas à stocker le résultat.

La requête en dessous n'a aucune raison de planter...

---

Marc.

gleu

Administrateur

Re : Mise à jour d'une "grosse" table

Et pour répondre aux autres points...

UPDATE bbb
INNER JOIN aaa
ON (bbb.n = aaa.n)
AND (bbb.r = aaa.r)
AND (bbb.s = aaa.s)
AND (bbb.c = aaa.c)
SET aaa.f = aaa.e/bbb.a;

J'ai de très gros doutes sur la bonne syntaxe de cette requête. À mon avis, elle ne passera pas à cause du INNER JOIN. De plus, vous faites un "UPDATE bbb" mais c'est une colonne de aaa que vous mettez à jour. Bref, une construction très bizarre. J'aurais tendance à réécrire votre requête ainsi :

UPDATE aaa
SET aaa.f = aaa.e/bbb.a
FROM bbb
WHERE (bbb.n = aaa.n) 
AND (bbb.r = aaa.r) 
AND (bbb.s = aaa.s) 
AND (bbb.c = aaa.c);

ET je suppose du coup que vous n'avez aucune colonne bbb.a à 0, car sinon c'est une division par zéro, donc un échec de mise à jour, donc la requête entière est annulée.

Est ce que cela ne va pas planter en plein milieu ???

Si elle "plante" quelque soit la raison, cela fera un ROLLBACK des modifications déjà effectuées.

Ou peut on faire ce genre de maj par paquet ???

Oui, c'est possible, mais la façon de le faire dépend de la disponibilité d'une clé primaire ou d'une clé unique.

---

Guillaume.

rbidocho

Membre

Re : Mise à jour d'une "grosse" table

Bonjour,

merci pour ces éclaircissements.

il est vrai que ma requêtes était fausse et je m'en suis rendu compte.
de plus pour essayer d'alléger le problème j'ai fais la requête suivante plusieurs milliers de fois d'affiler

UPDATE aaa as yy 
SET fe=xx.ef
FROM 
	(SELECT  em.c, em.s, em.r, em.n, em.p, em.e, em.fe, em.e/ac.a as ef 
	FROM bbb as ac, aaa as em 
	WHERE ac.c = em.c AND 
		  ac.s = em.s AND 
		  ac.r = em.r AND 
		  ac.n = em.n AND 
		  ac.c='01001') as xx 
WHERE xx.c = yy.c AND 
	  xx.s = yy.s AND 
	  xx.r = yy.r AND 
	  xx.n = yy.n AND 
	  xx.p = yy.p AND 
	  xx.c='01001';
COMMIT;

en changeant à chaque fois le '01001' par les valeurs successives possibles (36590 valeurs possibles).

mais cela à planter quand même au bout de la 10466 ème requête avec un code d'erreur :

ERROR:  out of memory
DETAIL:  Failed on request of size 1048576.

en regardant de plus près, il est vrai que j'ai des valeurs de ac.a qui sont égales à 0 mais elles n'ont pas ac.c, ac.s, ac.r, ac.n en lien dans la table à mettre à jour. Donc normalement, je n'ai jamais la division par 0.

le fait d'avoir mis COMMIT fait que la table à été mis à jour au mois pour les 10465 premières requêtes (i.e. valeurs de em.c ou ac.c)

Si vous avez des idées sur l'erreur précédemment citées !!!

Merci

RB

rbidocho

Membre

Re : Mise à jour d'une "grosse" table

rebonjour,

l'erreur viendrait de pgadmin qui ne supporte pas de réaliser quelques 36600 requêtes !!!

j'ai juste effectué ces requêtes en 3 fois.

merci encore de vos aides

RB

gleu

Administrateur

Re : Mise à jour d'une "grosse" table

l'erreur viendrait de pgadmin qui ne supporte pas de réaliser quelques 36600 requêtes !!!

Pas très étonnant, il n'est pas vraiment fait pour ça.

---

Guillaume.