Les fonctions PostgreSQL sont-elles transactionnelles ?

Question

Une fonction PostgreSQL telle que la suivante est-elle automatiquement transactionnelle ?

CREATE OR REPLACE FUNCTION refresh_materialized_view(name)
  RETURNS integer AS
$BODY$
 DECLARE
     _table_name ALIAS FOR $1;
     _entry materialized_views%ROWTYPE;
     _result INT;
 BEGIN          

     EXECUTE 'TRUNCATE TABLE ' || _table_name;

     UPDATE materialized_views
     SET    last_refresh = CURRENT_TIMESTAMP
     WHERE  table_name = _table_name;

     RETURN 1;
END
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

En d'autres termes, si une erreur se produit au cours de l'exécution de la fonction, les modifications apportées seront-elles retourné ? Si ce n'est pas le comportement par défaut, comment puis-je faire en sorte que la fonction transactionnel ?

Answer 1

Mise à jour de PostgreSQL 12 : il existe un support limité pour le niveau supérieur PROCEDURE qui peuvent faire du contrôle de transaction . Il n'est toujours pas possible de gérer les transactions dans les fonctions normales appelables en SQL, de sorte que ce qui suit reste vrai, sauf en cas d'utilisation des nouvelles procédures de haut niveau.

---

Les fonctions font partie de la transaction à partir de laquelle elles sont appelées. Leurs effets sont annulés si la transaction est annulée. Leur travail est validé si la transaction est validée. Tout BEGIN ... EXCEPT à l'intérieur de la fonction fonctionnent comme des points de sauvegarde (et les utilisent) comme la fonction SAVEPOINT et ROLLBACK TO SAVEPOINT Instructions SQL.

La fonction réussit dans son intégralité ou échoue dans son intégralité, à l'exception des cas suivants BEGIN ... EXCEPT la gestion des erreurs. Si une erreur est soulevée dans la fonction et n'est pas traitée, la transaction qui appelle la fonction est interrompue. Les transactions interrompues ne peuvent pas valider, et si elles tentent de le faire, la fonction COMMIT est traité comme ROLLBACK comme pour toute autre transaction erronée. Observez :

regress=# BEGIN;
BEGIN
regress=# SELECT 1/0;
ERROR:  division by zero
regress=# COMMIT;
ROLLBACK

Voyez comment la transaction, qui est dans l'état d'erreur en raison de la division par zéro, revient en arrière sur COMMIT ?

Si vous appelez une fonction sans une transaction explicite qui l'entoure, les règles sont exactement les mêmes que pour toute autre instruction Pg :

BEGIN;
SELECT refresh_materialized_view(name);
COMMIT;

(où COMMIT échouera si le SELECT a soulevé une erreur).

PostgreSQL ne supporte pas (encore) les transactions autonomes dans les fonctions, où la procédure/fonction pourrait commiter/reculer indépendamment de la transaction appelante. Ceci peut être simulé en utilisant une nouvelle session via dblink .

MAIS Les choses qui ne sont pas transactionnelles ou qui sont imparfaitement transactionnelles existent dans PostgreSQL. S'il a un comportement non-transactionnel dans un environnement normal, il est possible de le faire. BEGIN; do stuff; COMMIT; il a un comportement non transactionnel dans une fonction également. Par exemple, nextval et setval , TRUNCATE etc.

Answer 2

Comme ma connaissance de PostgreSQL est moins approfondie que celle de Craig Ringer, je vais essayer de donner une réponse plus courte : Oui.

Si vous exécutez une fonction qui contient une erreur, aucune des étapes n'aura d'impact dans la base de données.

De même, si vous exécutez une requête dans PgAdmin la même chose se produit.

Par exemple, si vous exécutez dans une requête :

update your_table yt set column1 = 10 where yt.id=20;

select anything_that_do_not_exists;

La mise à jour dans la rangée, id = 20 de your_table ne seront pas enregistrées dans la base de données.

MISE À JOUR Sep - 2018

Pour clarifier le concept, j'ai fait un petit exemple avec la fonction non-transactionnelle nextval.

Tout d'abord, créons une séquence :

create sequence test_sequence start 100;

Ensuite, on exécute :

update your_table yt set column1 = 10 where yt.id=20; select nextval('test_sequence'); select anything_that_do_not_exists;

Maintenant, si nous ouvrons une autre requête et exécutons

select nextval('test_sequence');

Nous obtiendrons 101 parce que la première valeur (100) a été utilisée dans la dernière requête (c'est-à-dire parce que les séquences ne sont pas transactionnelles) bien que la mise à jour n'ait pas été validée.

Answer 3

https://www.postgresql.org/docs/current/static/plpgsql-structure.html

Il est important de ne pas confondre l'utilisation de BEGIN/END pour le regroupement des instructions dans PL/pgSQL avec les commandes SQL de même nom pour le contrôle des transactions. Les BEGIN/END de PL/pgSQL ne servent qu'à regrouper les instructions ; ils ne permettent pas de démarrer ou de terminer une transaction. Les fonctions et les procédures de déclenchement sont toujours exécutées au sein d'une transaction établie par une requête externe - elles ne peuvent pas démarrer ou valider cette transaction, puisqu'il n'y aurait pas de contexte dans lequel elles pourraient s'exécuter. Cependant, un bloc contenant une clause EXCEPTION forme effectivement une sous-transaction qui peut être annulée sans affecter la transaction externe. Pour en savoir plus, voir la section 39.6.6.

Answer 4

Au niveau de la fonction, elle n'est pas transnationale. En d'autres termes, chaque déclaration dans la fonction appartient à une seule transaction, qui est la valeur par défaut de db auto commit. L'auto commit est vrai par défaut. Mais de toute façon, vous devez appeler la fonction en utilisant

select schemaName.functionName()

L'instruction ci-dessus 'select schemaName.functionName()' est une transaction unique, appelons la transaction T1, et donc toutes les instructions de la fonction appartiennent à la transaction T1. De cette façon, la fonction est dans une seule transaction.

Answer 5

En outre, l'ATOMIC Transaction comprend également des déclencheurs.