Incohérences malgré transaction

[toch34]

Je poste à nouveau ce message dans cette section car il relève plus du langage SQL que de postgres, désolé pour ceux qui me liront 2 fois

J'utilise postgresql 8.3 et au fil du temps ma base se retrouve dans un état pas logique malgré l'utilisation des transactions.

J'ai trois tables (qui me posent problème) pour gérer des publicités, une contient le forfait et les deux autres comptent les pub vues par les utilisateurs et les visiteurs. Chaque fois qu'une pub est affichée j'incrémente un compteur dans la table qui gère le forfait et une ligne est créée dans une des tables des pubs vues (logué/utilisateur) et (non logué/visiteur).

Le problème est que le nombre incrémenté fini par être différent du count des lignes des 2 autres tables.

Voici les tables mises en cause :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE TABLE pub_forfait (
    id_pub_forfait SERIAL PRIMARY KEY,
    id_pub INTEGER NOT NULL REFERENCES pub ON DELETE CASCADE,
 
    ...
 
    nb_pub_vu integer CHECK( nb_pub_vu >= 0 ) NOT NULL,
    nb_pub integer CHECK( nb_pub >= 0 ) NOT NULL,
    fini BOOLEAN NOT NULL
);
 
CREATE TABLE pub_vu_non_log (
    id_pub_vu_non_log SERIAL PRIMARY KEY,
    id_pub_forfait INTEGER NOT NULL REFERENCES pub_forfait ON DELETE CASCADE,
    ...
);
 
CREATE TABLE pub_vu (
    id_pub_vu SERIAL PRIMARY KEY,
    id_pub_forfait INTEGER NOT NULL REFERENCES pub_forfait ON DELETE CASCADE,
    ...
 
);

exemple nouvelle pub vu non logué:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
BEGIN;
INSERT INTO pub_vu_non_log (
id_pub_vu_non_log,
id_pub_forfait,...
)
VALUES(
NEXTVAL('pub_vu_non_log_id_pub_vu_non_log_seq'), 'XX' ,...);
 
UPDATE pub_forfait SET nb_pub_vu = (nb_pub_vu + 1) WHERE id_pub_forfait = 'XX';
 
-- je sais que je pourrais compter les enr dans les pub_vu à la place
SELECT id_pub_forfait FROM pub_forfait 
WHERE id_pub_forfait = 'XX' 
AND nb_pub_vu >= nb_pub;
 
-- if nb row == 1
UPDATE pub_forfait SET fini = true WHERE id_pub_forfait = 'XX';
 
--if pas d'erreur
COMMIT;
--else
ROLLBACK;

J'imagine qu'il doit y avoir des manières plus élégantes de le faire si vous avez des suggestions... cependant ma question n'est pas là.

mon problème au final:

Code :

1
2
3
SELECT * FROM pub_vu_non_log WHERE id_pub_forfait = 'XX';
-- Additionné a 
SELECT * FROM pub_vu WHERE id_pub_forfait = 'XX';

est différent de :

Code :

SELECT nb_pub_vu FROM pub_forfait WHERE id_pub_forfait = 'XX';

Normalement la transaction devrait garder la cohérence que ça commite ou pas?
Merci de votre aide.

[toch34]

Personne n'a d'idée? ou c'est le fait que ce soit des pubs qui ne vous donne pas envie de répondre désolé pour l'aspect capitaliste de ma question!

Plus sérieusement es-ce qu'un accès simultané à ce script peut entraîner une erreur de comptage lors du row = row+1?
J'ai découvert le verrouillage explicite, es-ce que certains d'entre vous ont déjà utilisé de tels outils? et dans quels cas?

Je désespère ce compteur va finir par partir à la poubelle et laisser place à des gros count()...

Merci à Alain du 24 pour cette scrupuleuse correction grammaticale j'espère ne pas avoir fait de nouvelles fautes.

[aieeeuuuuu]

Bonjour,

Envoyé par toch34

Plus sérieusement es-ce qu'un accès simultané à ce script peut entraîner une erreur de comptage lors du row = row+1?

En fonction de votre niveau d'isolation des transactions, oui !

Des suppressions dans les tables aussi ! (si elles ne décrémentent pas les compteurs)

De même que des insertions qui ne passent pas par votre code (une autre appli ? insertions à la main ?...)
Ce code est-il dans un trigger "instead of insert" ?

Je désespère ce compteur va finir par partir à la poubelle et laisser place à des gros count()...

Vous avez l'air de considérer cette solution comme une solution de repli, elle me parait pourtant être la meilleure !

[toch34]

Merci aieeeuuuuu pour cette réponse.

Changer le niveau d'isolation de mes transactions ou mettre un trigger me parait complexe pour mon modeste niveau en sql, la fiabilité ne sera pas au rendez-vous!!

Est-il possible de le faire grâce à une seule requête?

Pour vous faire comprendre l'idée:

Code :

1
2
3
4
5
6
UPDATE pub_forfait SET fini = 1 
WHERE id_pub_forfait = 'XX'
AND 
nb_pub <= 
(SELECT COUNT(id_pub_vu) FROM pub_vu WHERE id_pub_forfait = 'XX') +
(SELECT COUNT(id_pub_vu_non_log) FROM pub_vu_non_log WHERE id_pub_forfait = 'XX')

Jointure? Having? si vous avez une idée...