Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
Je veux écrire un trigger qui fasse un delete logique en non physique.
Mais je me heurte à un problème avec la variable now dans un sql dynamique (voir <-------------) J'ai essayé plusieurs syntaxes mais çà ne marche jamais.
Peut-être dois-je essayer un autre design ?
Merci pour votre attention
create or replace function public.get_pk_name(table_name name) returns varchar as $$
declare
stmt varchar;
pk_column_name varchar;
begin
stmt := 'select kc.column_name '
|| ' from information_schema.table_constraints tc'
....
if stmt is not null then execute stmt into pk_column_name; end if;
return pk_column_name;
end; $$ language 'plpgsql';
create or replace function public.mark_as_deleted() returns trigger as $$
declare
stmt varchar;
pk_column_name varchar;
pk_value integer;
begin
stmt := 'select public.get_pk_name('
|| ''''
|| TG_TABLE_NAME
|| ''''
|| ');';
if stmt is not null then execute stmt into pk_column_name; end if;
if pk_column_name is not null then
stmt := 'update public.'
|| quote_ident(TG_TABLE_NAME)
|| ' set deleted_when = now() ,deleted_by = '
|| ''''
|| current_user
|| ''''
|| ' where "'
|| pk_column_name
|| '" = '
|| old.pk_column_name <--------------------------------------------------------------------------------------------
|| ';';
if stmt is not null then execute stmt; end if;
end if;
end;
end; $$ language 'plpgsql'; Hors ligne
Déjà, il serait bon d'expliquer ce que vous entendez par delete logique et non physique.
Guillaume.
Hors ligne
Bonjour,
Par delete logique, je désigne le fait de marquer l'enregistrement par un champ 'deleted' et de ne pas l'effacer par un delete. Et l'application front-end (en Access VBA) a accès à des vues select ... where deleted is null.
La méthodologie du no delete a des partisans et des déracteurs. Une autre solution consisterait à copier les enregistrements effacés dans une autre base de données ayant les mêmes tables.
Au sujet de la question précédente, je m'en suis tiré avec :
stmt := 'update public.'
|| quote_ident(TG_TABLE_NAME)
|| ' set deleted_when = now() ,deleted_by = '
|| ''''
|| current_user
|| '''';
case TG_TABLE_NAME
when 'tblActions' then execute stmt using old."ActionID";
when 'tblBlankRows' then execute stmt using old."BlankRowID";
.../...Je n'ai pas trouvé le moyen de sélectionner la pk dans le tuple old comme décrit dans le post précédent.
Cette fonction est activée par un trigger before delete.
Merci pour votre attention,
Mchl
Hors ligne
En fait ça ne marche pas. Mon trigger efface tous les enregistrements de la table.
Je vais essayer en ajoutant à stmt ' where ' || nom_de_la_pk || ' = $1'.
A+
Hors ligne
Dans le SQL dynamique il faut utiliser la fonction format() avec les formats %L et %I pour se simplifier la syntaxe.
Par exemple
stmt := format('UPDATE public.%I SET deleted_when=now(), deleted_by=current_user WHERE %I=%L',
TG_TABLE_NAME, nom_de_la_colonne_pk, valeur_de_la_pk);Pour obtenir la valeur de la PK, si le trigger est multi-utilisé pour plusieurs tables et que la PK s'appelle différemment suivant les tables, c'est un problème.
L'ennui en plpgsql est que derrière OLD. ou NEW. on ne peut pas mettre une variable désignant un nom de colonne, c'est le côté statique de plpgsql (contrairement à plv8 ou plperl ou sûrement d'autres pl qui sont plus dynamiques mais ont d'autres inconvénients).
Mais est-ce que ce trigger a vraiment besoin de connaître le nom de la PK?
On peut se référer à une ligne via la pseudo-colonne CTID qui identifie sa position physique dans la table. Cette position peut changer d'une transaction à l'autre, mais pas en pleine transaction.
Pour moi une fonction comme ça devrait marcher de manière générique:
CREATE OR REPLACE FUNCTION softdelete()
RETURNS trigger LANGUAGE plpgsql
AS $function$
BEGIN
execute format('UPDATE %I.%I SET deleted_when=now(), deleted_by=current_user WHERE ctid=$1',
TG_TABLE_SCHEMA, TG_TABLE_NAME) USING OLD.ctid;
RETURN null;
END
$function$Le RETURN null est indispensable pour que le DELETE soit annulé étant donné qu'on veut le remplacer par un UPDATE.
Le lier à la table en évènement BEFORE DELETE:
CREATE TRIGGER softdelete BEFORE DELETE ON la_table FOR EACH ROW EXECUTE PROCEDURE softdelete();Dernière modification par dverite (02/03/2017 17:39:59)
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Elle peut tout à fait changer en pleine transaction. La preuve :
postgres=# begin;
BEGIN
postgres=# select id from t1 where ctid='(0,1)';
id
----
1
(1 row)Sur une autre session :
update t1 set id=32 where id=1;Retour sur la session précédente avec la transaction ouverte :
postgres=# select id from t1 where ctid='(0,1)';
id
----
(0 rows)Oups. S'appuyer sur les CTID, c'est se garantir de nombreux problèmes douloureux.
Guillaume.
Hors ligne
Génial ! Je vais tester ça dés que possible.
Un grand merci à vous,
Dernière modification par Michel Depiesse (02/03/2017 18:07:54)
Hors ligne
Merci à vous deux.
Donc, si je vous comprends bien, il n'y a pas moyen de faire un old.pk_name où pk_name est calculé par la fonction.
Encore une petite question.
L'inconvénient du soft delete est que les records fils (dont la fk pointe vers la pk du record effacé) ne sont pas marqués. Y a-t-il une solution simple ? Le on update on cascade ne semble pas convenir.
Merci pour votre attention et bonne fin de soirée
Hors ligne
Il y a certainement moyen mais c'est certainement très inutilement compliqué.
Pour les records fils, il faut faire soit-même l'UPDATE.
J'ai peut être loupé/oublié un message mais pourquoi faites-vous cela ? que vous le fassiez sur certaines tables, ça peut se comprendre pour certains cas. Mais là, j'ai l'impression que vous le faites pour toutes les tables... et là, j'avoue que ça m'interroge 
Guillaume.
Hors ligne
Oui effectivement Guillaume,dire que le CTID ne change pas dans une transaction c'est tellement un raccourci que c'est faux. En particulier en READ COMMITTED.
Mais dans le cas du trigger mentionné ici, je ne pense pas qu'il soit possible que OLD.ctid désigne une autre ligne que celle pour laquelle le trigger a été déclenché. Je ne pense pas non plus que cette ligne puisse disparaitre à ce moment car elle est verrouillée. Au pire l'UPDATE n'arriverait pas à l'atteindre si elle n'est plus visible.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Donc, si je vous comprends bien, il n'y a pas moyen de faire un old.pk_name où pk_name est calculé par la fonction.
C'est un problème de syntaxe et de capacité du langage. Si OLD.pk_name était possible il faudrait que pk_name soit une variable contenant un nom de colonne.
Mais OLD est une variable d'un type composite avec des champs du genre OLD.col1, OLD.col2 etc. et ces champs ne sont pas des variables, ce sont des eux-mêmes des identifiants que plpgsql attend en dur.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
@gleu
Merci pour votre réponse.
A/ Si on passe au paradigme no-delete, il y est logique, me semble-t-il, de munir chaque table d'un champ qui indique si le record a été effacé. Je ne vois pas ce qui suscite votre étonnement. Une autre solution consisterait à alimenter une base de données ayant les mêmes tables avec les records effacés de la base de données de production.
B/ En ce qui concerne la question du marquage des enregistrements ayant une clé de référence vers l'enregistrement effacé, il me semble que ce n'est pas un problème important : si l'application cliente ne voit pas l'enregistrement racine, elle ne cherchera pas à voir les enregistrements branches. Il faut prévoir un utilitaire qui liste les enregistrements déracinés et leur donne une terre d'asile.
C/ Le paradigme no-delete a ses détracteurs mais il me semble justifié car 1/ collecter des données et les enregister de manière structurée coûte cher, 2/ conserver ces données ne coûte quasiment rien et 3/ effacer des données qui n'auraient pas dû l'être coûte quelquefois très cher.
D/ Il est dommage que les tuples old et new ne veulent pas se soumettre aux mêmes règles que les autres objects.
@DanielVerite
Merci pour votre réponse
Vu la controverse qui vient de s'élever entre deux spécialistes, j'applique la loi de Murphy : "Si ça risque de ne pas marcher, ça ne marchera pas". smile
@gleu et @DanielVerite
Merci à tous deux pour votre aide et bon week-end
Hors ligne
Pages : 1