create table t1(c1 timestamp);
CREATE TABLE
create table logbat(nmb text, ddb timestamp, dfb timestamp, perfok boolean, res boolean);
CREATE TABLE
create or replace function pg_temp.traitement(duree_en_s integer) returns void
as $$
begin
insert into t1 values(current_timestamp);
perform pg_sleep(duree_en_s);
end;
$$ language plpgsql;
CREATE FUNCTION
create or replace function pg_temp.traitement_ok() returns boolean
as $$
begin
return true;
end;
$$ language plpgsql;
CREATE FUNCTION
CREATE OR REPLACE PROCEDURE bilan(traitement text, duree_en_s integer, duree_normale interval)
AS $$
BEGIN
perform pg_temp.traitement(duree_en_s);
IF pg_temp.traitement_ok() THEN
INSERT INTO logbat(nmb, ddb, dfb, perfok, res)
SELECT
traitement,
clock_timestamp(),
current_timestamp,
case when clock_timestamp()-current_timestamp > duree_normale then false else true end,
true;
COMMIT;
ELSE
ROLLBACK;
INSERT INTO logbat(nmb, ddb, dfb, perfok, res)
SELECT
traitement,
clock_timestamp(),
current_timestamp,
case when clock_timestamp()-current_timestamp > duree_normale then false else true end,
false;
COMMIT;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE PROCEDURE
call bilan('essai',1, '1 minute'::interval);
CALL
table t1;
c1
----------------------------
2018-06-05 14:18:25.176019
(1 ligne)
table logbat;
nmb | ddb | dfb | perfok | res
-------+----------------------------+----------------------------+--------+-----
essai | 2018-06-05 14:18:26.179503 | 2018-06-05 14:18:25.176019 | t | t
(1 ligne)
call bilan('essai',2, '1 second'::interval);
CALL
table t1;
c1
----------------------------
2018-06-05 14:18:25.176019
2018-06-05 14:19:51.16889
(2 lignes)
table logbat;
nmb | ddb | dfb | perfok | res
-------+----------------------------+----------------------------+--------+-----
essai | 2018-06-05 14:18:26.179503 | 2018-06-05 14:18:25.176019 | t | t
essai | 2018-06-05 14:19:53.17168 | 2018-06-05 14:19:51.16889 | f | t
(2 lignes)
create or replace function pg_temp.traitement_ok() returns boolean
as $$
begin
return false;
end;
$$ language plpgsql;
CREATE FUNCTION
call bilan('essai',1, '2 seconds'::interval);
CALL
table t1;
c1
----------------------------
2018-06-05 14:18:25.176019
2018-06-05 14:19:51.16889
(2 lignes)
table logbat;
nmb | ddb | dfb | perfok | res
-------+----------------------------+----------------------------+--------+-----
essai | 2018-06-05 14:18:26.179503 | 2018-06-05 14:18:25.176019 | t | t
essai | 2018-06-05 14:19:53.17168 | 2018-06-05 14:19:51.16889 | f | t
essai | 2018-06-05 14:21:44.790985 | 2018-06-05 14:21:43.788711 | t | f
(3 lignes)
start transaction;
START TRANSACTION
call bilan('essai',1, '2 seconds'::interval);
ERREUR: invalid transaction termination
CONTEXTE : fonction PL/pgsql bilan(text,integer,interval), ligne 14 à ROLLBACK