/
instPsql.out
6991 lines (6940 loc) · 388 KB
/
instPsql.out
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
-- instPsql.sql: Install E-Maj as simple psql script (for postgres version prior 9.1)
--
-----------------------------
-- install dblink
-----------------------------
-- this 8.4.14 version seems compatible with 8.3 to 9.2 pg version
\i ~/postgresql-8.4.14/contrib/dblink/dblink.sql
/* $PostgreSQL: pgsql/contrib/dblink/dblink.sql.in,v 1.18 2009/06/09 17:41:02 tgl Exp $ */
-- Adjust this setting to control where the objects get created.
SET search_path = public;
-- dblink_connect now restricts non-superusers to password
-- authenticated connections
CREATE OR REPLACE FUNCTION dblink_connect (text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_connect (text, text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT;
-- dblink_connect_u allows non-superusers to use
-- non-password authenticated connections, but initially
-- privileges are revoked from public
CREATE OR REPLACE FUNCTION dblink_connect_u (text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT SECURITY DEFINER;
CREATE OR REPLACE FUNCTION dblink_connect_u (text, text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT SECURITY DEFINER;
REVOKE ALL ON FUNCTION dblink_connect_u (text) FROM public;
REVOKE ALL ON FUNCTION dblink_connect_u (text, text) FROM public;
CREATE OR REPLACE FUNCTION dblink_disconnect ()
RETURNS text
AS '$libdir/dblink','dblink_disconnect'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_disconnect (text)
RETURNS text
AS '$libdir/dblink','dblink_disconnect'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_open (text, text)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_open (text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_open (text, text, text)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_open (text, text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_fetch (text, int)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_fetch (text, int, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_fetch (text, text, int)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_fetch (text, text, int, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_close (text)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_close (text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_close (text, text)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_close (text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink (text, text)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink (text, text, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink (text)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink (text, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_exec (text, text)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_exec (text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_exec (text)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_exec (text,boolean)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;
CREATE TYPE dblink_pkey_results AS (position int, colname text);
CREATE OR REPLACE FUNCTION dblink_get_pkey (text)
RETURNS setof dblink_pkey_results
AS '$libdir/dblink','dblink_get_pkey'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_build_sql_insert (text, int2vector, int, _text, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_insert'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_build_sql_delete (text, int2vector, int, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_delete'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_build_sql_update (text, int2vector, int, _text, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_update'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_current_query ()
RETURNS text
AS '$libdir/dblink','dblink_current_query'
LANGUAGE C;
CREATE OR REPLACE FUNCTION dblink_send_query(text, text)
RETURNS int4
AS '$libdir/dblink', 'dblink_send_query'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_is_busy(text)
RETURNS int4
AS '$libdir/dblink', 'dblink_is_busy'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_get_result(text)
RETURNS SETOF record
AS '$libdir/dblink', 'dblink_get_result'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_get_result(text, bool)
RETURNS SETOF record
AS '$libdir/dblink', 'dblink_get_result'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_get_connections()
RETURNS text[]
AS '$libdir/dblink', 'dblink_get_connections'
LANGUAGE C;
CREATE OR REPLACE FUNCTION dblink_cancel_query(text)
RETURNS text
AS '$libdir/dblink', 'dblink_cancel_query'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION dblink_error_message(text)
RETURNS text
AS '$libdir/dblink', 'dblink_error_message'
LANGUAGE C STRICT;
-----------------------------
-- for postgres cluster 8.3 and 9.1, temporarily rename tspemaj tablespace to test both cases
-----------------------------
CREATE or REPLACE FUNCTION public.emaj_tmp()
RETURNS VOID LANGUAGE plpgsql AS
$tmp$
DECLARE
BEGIN
IF substring (version() from E'PostgreSQL\\s(\\d+\\.\\d+)') IN ('8.3', '9.1') THEN
ALTER TABLESPACE tspemaj RENAME TO tspemaj_renamed;
END IF;
RETURN;
END;
$tmp$;
SELECT public.emaj_tmp();
emaj_tmp
----------
(1 row)
DROP FUNCTION public.emaj_tmp();
-----------------------------
-- emaj installation
-----------------------------
\i sql/emaj.sql
--
-- E-Maj : logs and rollbacks table updates : Version <NEXT_VERSION>
--
-- This software is distributed under the GNU General Public License.
--
-- This script installs E-Maj extension for PostgreSQL version prior 9.1
--
-- This script must be executed by a role having SUPERUSER privileges.
-- Before its execution:
-- -> the concerned cluster may contain a tablespace named "tspemaj" to hold E-Maj files,
-- for instance previously created by
-- CREATE TABLESPACE tspemaj LOCATION '/.../tspemaj',
-- -> the plpgsql language must have been created in the concerned database,
-- (-> the dblink contrib/extension must have been installed.)
\set ON_ERROR_STOP ON
\set QUIET ON
SET client_min_messages TO WARNING;
\echo 'E-Maj objects creation...'
E-Maj objects creation...
-- create, execute and drop a specific plpgsql function to check the environment
-- If all pre-requisites are not met, it generates an error that blocks the E-Maj installation.
CREATE OR REPLACE FUNCTION public.emaj_tmp_check_envir()
RETURNS VOID LANGUAGE plpgsql AS
$tmp$
DECLARE
v_stmt TEXT;
v_schemaList TEXT;
r_schema RECORD;
BEGIN
-- the creation of the function implicitely validates that plpgsql language is created!
-- check postgres version is >= 8.3
-- (warning, the test is alphanumeric => to be adapted when pg 10.0 will appear!)
IF substring (version() from E'PostgreSQL\\s(\\d+\\.\\d+)') < '8.3' THEN
RAISE EXCEPTION 'E-Maj installation: the current postgres version is too old for E-Maj.';
END IF;
-- check the current role is a superuser
PERFORM 0 FROM pg_catalog.pg_roles WHERE rolname = current_user AND rolsuper;
IF NOT FOUND THEN
RAISE EXCEPTION 'E-Maj installation: the current user (%) is not a superuser.', current_user;
END IF;
-- check there is no E-Maj secondary schema remaining (that could later lead to error)
v_schemaList = '';
BEGIN
-- try to join an old emaj.emaj_relation table and the postgres schemas table
-- (if there is an error (schema or table or column not there), continue)
FOR r_schema IN
SELECT DISTINCT rel_log_schema FROM emaj.emaj_relation
WHERE rel_log_schema <> 'emaj'
AND EXISTS (SELECT 0 FROM pg_catalog.pg_namespace WHERE nspname = rel_log_schema)
LOOP
v_schemaList = v_schemaList || r_schema.rel_log_schema || ', ';
END LOOP;
EXCEPTION WHEN OTHERS THEN -- do nothing
END;
IF v_schemaList <> '' THEN
-- some secondary schemas remaining
RAISE EXCEPTION 'E-Maj installation: some secondary schemas (%) need to be dropped before reinstalling E-Maj. You can use the uninstall.sql script or the emaj_drop_group() function or DROP SCHEMA statements.', substring(v_schemaList FROM 1 FOR char_length(v_schemaList) - 2);
END IF;
-- check the dblink contrib or extension is already installed
PERFORM 0 FROM pg_catalog.pg_proc WHERE proname = 'dblink';
IF NOT FOUND THEN
RAISE EXCEPTION 'E-Maj installation: dblink contrib/extension is not installed. Please install dblink first.';
END IF;
--
RETURN;
END;
$tmp$;
SELECT public.emaj_tmp_check_envir();
emaj_tmp_check_envir
----------------------
(1 row)
DROP FUNCTION public.emaj_tmp_check_envir();
-- OK, now create E-Maj objects in a single transaction
BEGIN TRANSACTION;
------------------------------------------
-- --
-- emaj schema and environment checking --
-- --
------------------------------------------
-- if an emaj schema already exists, drop it
DROP SCHEMA IF EXISTS emaj CASCADE;
-- creation of the schema 'emaj' containing all the needed objets
CREATE SCHEMA emaj;
COMMENT ON SCHEMA emaj IS
$$Contains all E-Maj related objects.$$;
-- create emaj roles
-- and set tspemaj as current_tablespace if exists
CREATE OR REPLACE FUNCTION emaj._tmp_create_some_components()
RETURNS VOID LANGUAGE plpgsql AS
$tmp$
DECLARE
v_pgVersion TEXT := substring (version() from E'PostgreSQL\\s(\\d+\\.\\d+)');
v_stmt TEXT;
r_fnct RECORD;
BEGIN
-- create emaj roles (NOLOGIN), if they do not exist
-- does 'emaj_adm' already exist ?
PERFORM 0 FROM pg_catalog.pg_roles WHERE rolname = 'emaj_adm';
-- if no, create it
IF NOT FOUND THEN
CREATE ROLE emaj_adm;
COMMENT ON ROLE emaj_adm IS
$$This role may be granted to other roles in charge of E-Maj administration.$$;
END IF;
-- give EXECUTE right to emaj_adm on all dblink_connect_u functions.
-- They are revoked by default, but are needed to monitor the rollback operations progress.
-- Note dblink may have been installed in several schemas and we don't know at installation time
-- what will be the search_path of future users.
FOR r_fnct IN
SELECT nspname FROM pg_catalog.pg_proc, pg_catalog.pg_namespace
WHERE pg_proc.pronamespace = pg_namespace.oid
AND proname = 'dblink_connect_u' AND pronargs = 2
LOOP
EXECUTE 'GRANT EXECUTE ON FUNCTION ' ||
quote_ident(r_fnct.nspname) || '.dblink_connect_u(text,text) TO emaj_adm';
END LOOP;
-- does 'emaj_viewer' already exist ?
PERFORM 0 FROM pg_catalog.pg_roles WHERE rolname = 'emaj_viewer';
-- if no, create it
IF NOT FOUND THEN
CREATE ROLE emaj_viewer;
COMMENT ON ROLE emaj_viewer IS
$$This role may be granted to other roles allowed to view E-Maj objects content.$$;
END IF;
-- if tspemaj tablespace exists,
-- use it as default_tablespace for emaj tables creation
-- and grant the create rights on it to emaj_adm
PERFORM 0 FROM pg_catalog.pg_tablespace WHERE spcname = 'tspemaj';
IF FOUND THEN
SET LOCAL default_tablespace TO tspemaj;
GRANT CREATE ON TABLESPACE tspemaj TO emaj_adm;
END IF;
--
RETURN;
END;
$tmp$;
SELECT emaj._tmp_create_some_components();
_tmp_create_some_components
-----------------------------
(1 row)
DROP FUNCTION emaj._tmp_create_some_components();
------------------------------------------------
-- --
-- emaj enum types, sequences and tables --
-- --
------------------------------------------------
-- enum of the possible values for the rollback status columns
CREATE TYPE emaj._rlbk_status_enum AS ENUM (
'PLANNING', -- the emaj rollback is in the initial planning phase
'LOCKING', -- the emaj rollback is acquiring locks on tables
'EXECUTING', -- the emaj rollback is in the main executing phase
'COMPLETED', -- the emaj rollback is completed but the status of its transaction is not yet known
'COMMITTED', -- the emaj rollback transaction is known as committed
'ABORTED' -- the emaj rollback transaction is known as aborted
);
-- enum of the possible values for the rollback steps
CREATE TYPE emaj._rlbk_step_enum AS ENUM (
'LOCK_TABLE', -- set a lock on a table
'DIS_LOG_TRG', -- disable a log trigger
'DROP_FK', -- drop a foreign key
'SET_FK_DEF', -- set a foreign key deferred
'RLBK_TABLE', -- rollback a table
'DELETE_LOG', -- delete rows from a log table
'SET_FK_IMM', -- set a foreign key immediate
'ADD_FK', -- recreate a foreign key
'ENA_LOG_TRG' -- enable a log trigger
);
-- the emaj_global_seq sequence provides a unique identifier for all rows inserted into all emaj log tables of the database.
-- It is used to order all these rows in insertion time order for rollback as well as other purposes.
-- (So this order is not based on system time that can be unsafe).
-- The sequence is created with the following (default) characteristics:
-- - increment = 1
-- - no cache (to keep the delivered nextval value in time order)
-- - no cycle (would the end of the sequence be reached, no new log row would be accepted)
CREATE SEQUENCE emaj.emaj_global_seq;
COMMENT ON SEQUENCE emaj.emaj_global_seq IS
$$Global sequence to identifiy all rows of emaj log tables.$$;
-- table containing Emaj parameters
CREATE TABLE emaj.emaj_param (
param_key TEXT NOT NULL, -- parameter key
param_value_text TEXT, -- value if type is text, otherwise NULL
param_value_int BIGINT, -- value if type is bigint, otherwise NULL
param_value_boolean BOOLEAN, -- value if type is boolean, otherwise NULL
param_value_interval INTERVAL, -- value if type is interval, otherwise NULL
PRIMARY KEY (param_key)
);
COMMENT ON TABLE emaj.emaj_param IS
$$Contains E-Maj parameters.$$;
-- table containing the history of all E-Maj events
CREATE TABLE emaj.emaj_hist ( -- records the history of
hist_id BIGSERIAL NOT NULL, -- internal id
hist_datetime TIMESTAMPTZ NOT NULL
DEFAULT clock_timestamp(), -- insertion time
hist_function TEXT NOT NULL, -- main E-Maj function generating the event
hist_event TEXT, -- type of event (often BEGIN or END)
hist_object TEXT, -- object supporting the event (often the group name)
hist_wording TEXT, -- additional comment
hist_user TEXT
DEFAULT session_user, -- the user who call the E-Maj function
hist_txid BIGINT
DEFAULT txid_current(), -- and its tx_id
PRIMARY KEY (hist_id)
);
COMMENT ON TABLE emaj.emaj_hist IS
$$Contains E-Maj events history.$$;
-- table containing the definition of groups' content. Filled and maintained by the user, it is used by emaj_create_group function.
CREATE TABLE emaj.emaj_group_def (
grpdef_group TEXT NOT NULL, -- name of the group containing this table or sequence
grpdef_schema TEXT NOT NULL, -- schema name of this table or sequence
grpdef_tblseq TEXT NOT NULL, -- table or sequence name
grpdef_priority INTEGER, -- priority level (tables are processed in ascending
-- order, with NULL last)
grpdef_log_schema_suffix TEXT, -- schema suffix for the log table, functions and sequence
-- (NULL for 'emaj' schema)
grpdef_log_dat_tsp TEXT, -- tablespace for the log table (NULL to use default value)
grpdef_log_idx_tsp TEXT, -- tablespace for the log index (NULL to use default value)
PRIMARY KEY (grpdef_group, grpdef_schema, grpdef_tblseq)
-- the group name is included in the pkey so that a table/sequence can be temporarily assigned to several groups
);
COMMENT ON TABLE emaj.emaj_group_def IS
$$Contains E-Maj groups definition, supplied by the E-Maj administrator.$$;
-- table containing the defined groups
-- rows are created at emaj_create_group time and deleted at emaj_drop_group time
CREATE TABLE emaj.emaj_group (
group_name TEXT NOT NULL,
group_is_logging BOOLEAN NOT NULL, -- are log triggers activated ?
-- true between emaj_start_group(s) and emaj_stop_group(s)
-- flase in other cases
group_nb_table INT, -- number of tables at emaj_create_group time
group_nb_sequence INT, -- number of sequences at emaj_create_group time
group_is_rollbackable BOOLEAN, -- false for 'AUDIT_ONLY' groups, true for 'ROLLBACKABLE' groups
group_creation_datetime TIMESTAMPTZ NOT NULL -- start time of the transaction that created the group
DEFAULT transaction_timestamp(),
group_last_alter_datetime TIMESTAMPTZ, -- date and time of the last emaj_alter_group() exec,
-- set to NULL at emaj_create_group() time
group_pg_version TEXT NOT NULL -- postgres version at emaj_create_group() time
DEFAULT substring (version() from E'PostgreSQL\\s([.,0-9,A-Z,a-z]*)'),
group_comment TEXT, -- optional user comment
PRIMARY KEY (group_name)
);
COMMENT ON TABLE emaj.emaj_group IS
$$Contains created E-Maj groups.$$;
-- table containing the relations (tables and sequences) of created tables groups
CREATE TABLE emaj.emaj_relation (
rel_schema TEXT NOT NULL, -- schema name containing the relation
rel_tblseq TEXT NOT NULL, -- table or sequence name
rel_group TEXT NOT NULL, -- name of the group that owns the relation
rel_priority INTEGER, -- priority level of processing inside the group
rel_log_schema TEXT, -- schema for the log table, functions and sequence
rel_log_dat_tsp TEXT, -- tablespace for the log table (NULL for sequences)
rel_log_idx_tsp TEXT, -- tablespace for the log index (NULL for sequences)
rel_kind TEXT, -- similar to the relkind column of pg_class table
-- ('r' = table, 'S' = sequence)
PRIMARY KEY (rel_schema, rel_tblseq),
FOREIGN KEY (rel_group) REFERENCES emaj.emaj_group (group_name) ON DELETE CASCADE
);
COMMENT ON TABLE emaj.emaj_relation IS
$$Contains the content (tables and sequences) of created E-Maj groups.$$;
-- index on emaj_relation used to speedup most functions working on groups with large E-Maj configuration
CREATE INDEX emaj_relation_idx1 ON emaj.emaj_relation (rel_group, rel_kind);
-- index on emaj_relation used to speedup _verify_schema() with large E-Maj configuration
CREATE INDEX emaj_relation_idx2 ON emaj.emaj_relation (rel_log_schema);
-- table containing the marks
CREATE TABLE emaj.emaj_mark (
mark_group TEXT NOT NULL, -- group for which the mark has been set
mark_name TEXT NOT NULL, -- mark name
mark_id BIGSERIAL NOT NULL, -- serial id used to order rows (not to rely on timestamps
-- that are not safe if system time changes)
mark_datetime TIMESTAMPTZ NOT NULL, -- precise timestamp of the mark creation, used as a reference
-- for other tables like emaj_sequence and all log tables
mark_global_seq BIGINT NOT NULL, -- emaj_global_seq last value at mark set (used to rollback tables)
mark_is_deleted BOOLEAN NOT NULL, -- boolean to indicate if the mark is deleted
mark_comment TEXT, -- optional user comment
mark_txid BIGINT -- id of the tx that has set the mark
DEFAULT txid_current(),
mark_last_sequence_id BIGINT, -- last sequ_id for the group at the end of the _set_mark_groups operation
mark_last_seq_hole_id BIGINT, -- last sqhl_id for the group at _set_mark_groups time
mark_log_rows_before_next BIGINT, -- number of log rows recorded for the group between the mark and the next one (NULL if last mark) - used to speedup marks lists display in phpPgAdmin plugin
PRIMARY KEY (mark_group, mark_name),
FOREIGN KEY (mark_group) REFERENCES emaj.emaj_group (group_name) ON DELETE CASCADE
);
COMMENT ON TABLE emaj.emaj_mark IS
$$Contains marks set on E-Maj tables groups.$$;
-- table containing the sequences characteristics log
-- (to record at mark time the state of application sequences and sequences used by log tables)
CREATE TABLE emaj.emaj_sequence (
sequ_id BIGSERIAL NOT NULL, -- serial id used to delete oldest or newest rows (not to rely
-- on timestamps that are not safe if system time changes)
sequ_schema TEXT NOT NULL, -- application or 'emaj' schema that owns the sequence
sequ_name TEXT NOT NULL, -- application or emaj sequence name
sequ_datetime TIMESTAMPTZ NOT NULL, -- timestamp the sequence characteristics have been recorded
-- the same timestamp as referenced in emaj_mark table
sequ_mark TEXT NOT NULL, -- name of the mark associated to the insertion timestamp
sequ_last_val BIGINT NOT NULL, -- sequence last value
sequ_start_val BIGINT NOT NULL, -- sequence start value, (0 with postgres 8.2)
sequ_increment BIGINT NOT NULL, -- sequence increment
sequ_max_val BIGINT NOT NULL, -- sequence max value
sequ_min_val BIGINT NOT NULL, -- sequence min value
sequ_cache_val BIGINT NOT NULL, -- sequence cache value
sequ_is_cycled BOOLEAN NOT NULL, -- sequence flag 'is cycled ?'
sequ_is_called BOOLEAN NOT NULL, -- sequence flag 'is called ?'
PRIMARY KEY (sequ_schema, sequ_name, sequ_datetime)
);
COMMENT ON TABLE emaj.emaj_sequence IS
$$Contains values of sequences at E-Maj set_mark times.$$;
-- table containing the holes in sequences log
-- these holes are due to rollback operations that do not adjust log sequences
-- the hole size = difference of sequence's current last_value and last value at the rollback mark
CREATE TABLE emaj.emaj_seq_hole (
sqhl_id BIGSERIAL NOT NULL, -- serial id used to delete oldest or newest rows (not to rely
-- on timestamps that are not safe if system time changes)
sqhl_schema TEXT NOT NULL, -- schema that owns the table
sqhl_table TEXT NOT NULL, -- application table for which a sequence hole is recorded
-- in the associated log table
sqhl_datetime TIMESTAMPTZ NOT NULL -- timestamp of the rollback operation that generated the hole
DEFAULT transaction_timestamp(),
sqhl_hole_size BIGINT NOT NULL, -- hole size computed as the difference of 2 sequence last-values
PRIMARY KEY (sqhl_schema, sqhl_table, sqhl_datetime)
);
COMMENT ON TABLE emaj.emaj_seq_hole IS
$$Contains description of holes in sequence values for E-Maj log tables.$$;
-- table containing rollback events
CREATE TABLE emaj.emaj_rlbk (
rlbk_id BIGSERIAL NOT NULL, -- rollback id
rlbk_groups TEXT[] NOT NULL, -- groups array to rollback
rlbk_mark TEXT NOT NULL, -- mark to rollback to
rlbk_mark_datetime TIMESTAMPTZ , -- timestamp of the mark as recorded into emaj_mark
rlbk_is_logged BOOLEAN NOT NULL, -- rollback type: true = logged rollback
rlbk_nb_session INT NOT NULL, -- number of requested sessions
rlbk_nb_table INT , -- total number of tables in groups
rlbk_nb_sequence INT , -- number of sequences to rollback
rlbk_eff_nb_table INT , -- number of tables with rows to rollback
rlbk_status emaj._rlbk_status_enum, -- rollback status
rlbk_begin_hist_id BIGINT , -- hist_id of the rollback BEGIN event in the emaj_hist
-- used to know if the rollback has been committed or not
rlbk_start_datetime TIMESTAMPTZ , -- clock timestamp of the rollback BEGIN event in emaj_hist
rlbk_end_datetime TIMESTAMPTZ, -- clock time the rollback has been completed,
-- NULL if rollback is in progress or aborted
rlbk_msg TEXT, -- result message
PRIMARY KEY (rlbk_id)
);
COMMENT ON TABLE emaj.emaj_rlbk IS
$$Contains description of rollback events.$$;
-- partial index on emaj_rlbk targeting in progress rollbacks (not yet committed or marked as aborted)
CREATE INDEX emaj_rlbk_idx1 ON emaj.emaj_rlbk (rlbk_status)
WHERE rlbk_status IN ('PLANNING', 'LOCKING', 'EXECUTING', 'COMPLETED');
-- index to help rollback tables purges
CREATE INDEX emaj_rlbk_idx2 ON emaj.emaj_rlbk (rlbk_start_datetime);
-- table containing rollback events sessions
CREATE TABLE emaj.emaj_rlbk_session (
rlbs_rlbk_id BIGINT NOT NULL, -- rollback id
rlbs_session INT NOT NULL, -- session number (from 1 to rlbk_nb_session)
rlbs_txid BIGINT NOT NULL, -- id of the tx that executes this rollback session
rlbs_start_datetime TIMESTAMPTZ NOT NULL, -- rollback session start timestamp
rlbs_end_datetime TIMESTAMPTZ, -- clock time the rollback session has been completed,
-- NULL if rollback is in progress
PRIMARY KEY (rlbs_rlbk_id, rlbs_session),
FOREIGN KEY (rlbs_rlbk_id) REFERENCES emaj.emaj_rlbk (rlbk_id)
);
COMMENT ON TABLE emaj.emaj_rlbk_session IS
$$Contains description of rollback events sessions.$$;
-- table containing the elementary steps of rollback operations
CREATE TABLE emaj.emaj_rlbk_plan (
rlbp_rlbk_id BIGINT NOT NULL, -- rollback id
rlbp_step emaj._rlbk_step_enum
NOT NULL, -- kind of elementary step in the rollback processing
rlbp_schema TEXT NOT NULL, -- schema object of the step
rlbp_table TEXT NOT NULL, -- table name
rlbp_fkey TEXT NOT NULL, -- foreign key name for step on foreign key, or ''
rlbp_batch_number INT, -- identifies a set of tables linked by foreign keys
rlbp_session INT, -- session number the step is affected to
rlbp_fkey_def TEXT, -- foreign key definition used to recreate it, or NULL
rlbp_estimated_quantity BIGINT, -- for RLBK_TABLE, estimated number of updates to rollback
-- for DELETE_LOG, estimated number of rows to delete
-- for fkeys, estimated number of keys to check
rlbp_estimated_duration INTERVAL, -- estimated elapse time for the step processing
rlbp_estimate_method INT, -- method used to compute the estimated duration
-- 1: use rollback stats with volume in same order of magnitude
-- 2: use all previous rollback stats
-- 3: use only parameters (from emaj_param or default values)
rlbp_start_time TIMESTAMPTZ, -- clock start time of the step, NULL is not yet started
rlbp_quantity BIGINT, -- for RLBK_TABLE, number of effectively rolled back updates
-- for DELETE_LOG, number of effectively deleted log rows
-- null for fkeys
rlbp_duration INTERVAL, -- real elapse time of the step, NULL is not yet completed
PRIMARY KEY (rlbp_rlbk_id, rlbp_step, rlbp_schema, rlbp_table, rlbp_fkey),
FOREIGN KEY (rlbp_rlbk_id) REFERENCES emaj.emaj_rlbk (rlbk_id)
);
COMMENT ON TABLE emaj.emaj_rlbk_plan IS
$$Contains description of elementary steps for rollback operations.$$;
-- table containing statistics about previously executed rollback operations
-- and used to estimate rollback durations
-- depending on the step, it contains 1 row per elementary step (like 'RLBK_TABLE' or 'DELETE_LOG'),
-- or 1 row per type of step for 1 rollback operation (like 'DROP_FK', or 'DIS_LOG_TRG')
CREATE TABLE emaj.emaj_rlbk_stat (
rlbt_step emaj._rlbk_step_enum
NOT NULL, -- kind of elementary step in the rollback processing
rlbt_schema TEXT NOT NULL, -- schema object of the step
rlbt_table TEXT NOT NULL, -- table name
rlbt_fkey TEXT NOT NULL, -- foreign key name for step on foreign key, or ''
rlbt_rlbk_id BIGINT NOT NULL, -- rollback id
rlbt_datetime TIMESTAMPTZ NOT NULL, -- timestamp of the rollback that has generated the statistic
rlbt_quantity BIGINT NOT NULL, -- depending on the step,
-- either number of rows processed by the elementary step
-- or number of executed steps
rlbt_duration INTERVAL NOT NULL, -- duration of the elementary step or sum of durations
PRIMARY KEY (rlbt_step, rlbt_schema, rlbt_table, rlbt_fkey, rlbt_rlbk_id)
);
COMMENT ON TABLE emaj.emaj_rlbk_stat IS
$$Contains statistics about previous E-Maj rollback durations.$$;
------------------------------------
-- --
-- emaj composite types --
-- --
------------------------------------
CREATE TYPE emaj.emaj_log_stat_type AS (
stat_group TEXT,
stat_schema TEXT,
stat_table TEXT,
stat_rows BIGINT
);
COMMENT ON TYPE emaj.emaj_log_stat_type IS
$$Represents the structure of rows returned by the emaj_log_stat_group() function.$$;
CREATE TYPE emaj.emaj_detailed_log_stat_type AS (
stat_group TEXT,
stat_schema TEXT,
stat_table TEXT,
stat_role VARCHAR(32),
stat_verb VARCHAR(6),
stat_rows BIGINT
);
COMMENT ON TYPE emaj.emaj_detailed_log_stat_type IS
$$Represents the structure of rows returned by the emaj_detailed_log_stat_group() function.$$;
CREATE TYPE emaj.emaj_rollback_activity_type AS (
rlbk_id BIGINT, -- rollback id
rlbk_groups TEXT[], -- groups array to rollback
rlbk_mark TEXT, -- mark to rollback to
rlbk_mark_datetime TIMESTAMPTZ, -- timestamp of the mark as recorded into emaj_mark
rlbk_is_logged BOOLEAN, -- rollback type: true = logged rollback
rlbk_nb_session INT, -- number of requested sessions
rlbk_nb_table INT, -- total number of tables in groups
rlbk_nb_sequence INT, -- number of sequences to rollback
rlbk_eff_nb_table INT, -- number of tables with rows to rollback
rlbk_status emaj._rlbk_status_enum, -- rollback status
rlbk_start_datetime TIMESTAMPTZ, -- clock timestamp of the rollback BEGIN event in emaj_hist
rlbk_elapse INTERVAL, -- elapse time since the begining of the execution
rlbk_remaining INTERVAL, -- estimated remaining time to complete the rollback
rlbk_completion_pct SMALLINT -- estimated percentage of the rollback operation
);
COMMENT ON TYPE emaj.emaj_rollback_activity_type IS
$$Represents the structure of rows returned by the emaj_rollback_activity() function.$$;
CREATE TYPE emaj._verify_groups_type AS (
ver_schema TEXT,
ver_tblseq TEXT,
ver_msg TEXT
);
COMMENT ON TYPE emaj._verify_groups_type IS
$$Represents the structure of rows returned by the internal _verify_groups() function.$$;
------------------------------------
-- --
-- 'Fixed' parameters --
-- --
------------------------------------
INSERT INTO emaj.emaj_param (param_key, param_value_text) VALUES ('emaj_version','<NEXT_VERSION>');
-- Other parameters are optional. They may be set by E-Maj administrators if needed.
-- The dblink_user_password parameter defines the role and its associated password, if any, to establish a dblink
-- connection for the monitoring of rollback operations.
-- INSERT INTO emaj.emaj_param (param_key, param_value_text) VALUES ('dblink_user_password','user=<user> password=<password>');
-- The history_retention parameter defines the time interval when a row remains in the emaj history and rollback tables - default is 1 year
-- INSERT INTO emaj.emaj_param (param_key, param_value_interval) VALUES ('history_retention','1 year'::interval);
-- 4 parameters are used by the emaj_estimate_rollback_group(s) functions as default values to compute the approximate duration of a rollback operation.
-- The avg_row_rollback_duration parameter defines the average duration needed to rollback a row.
-- INSERT INTO emaj.emaj_param (param_key, param_value_interval) VALUES ('avg_row_rollback_duration','100 microsecond'::interval);
-- The avg_row_delete_log_duration parameter defines the average duration needed to delete log rows.
-- INSERT INTO emaj.emaj_param (param_key, param_value_interval) VALUES ('avg_row_delete_log_duration','10 microsecond'::interval);
-- The avg_fkey_check_duration parameter defines the average duration needed to check a foreign key.
-- INSERT INTO emaj.emaj_param (param_key, param_value_interval) VALUES ('avg_fkey_check_duration','20 microsecond'::interval);
-- The fixed_table_rollback_duration parameter defines the fixed rollback cost for any table or sequence belonging to a group
-- INSERT INTO emaj.emaj_param (param_key, param_value_interval) VALUES ('fixed_table_rollback_duration','5 millisecond'::interval);
------------------------------------
-- --
-- Low level Functions --
-- --
------------------------------------
CREATE OR REPLACE FUNCTION emaj._pg_version()
RETURNS TEXT LANGUAGE sql IMMUTABLE AS
$$
-- This function returns as a string the 2 major parts of the current postgresql version (x.y)
SELECT substring (version() from E'PostgreSQL\\s(\\d+\\.\\d+)');
$$;
CREATE OR REPLACE FUNCTION emaj._get_mark_name(TEXT, TEXT)
RETURNS TEXT LANGUAGE sql AS
$$
-- This function returns a mark name if exists for a group, processing the EMAJ_LAST_MARK keyword.
-- input: group name and mark name
-- output: mark name or NULL
SELECT case
when $2 = 'EMAJ_LAST_MARK' then
(SELECT mark_name FROM emaj.emaj_mark WHERE mark_group = $1 ORDER BY mark_id DESC LIMIT 1)
else (SELECT mark_name FROM emaj.emaj_mark WHERE mark_group = $1 AND mark_name = $2)
end
$$;
CREATE OR REPLACE FUNCTION emaj._get_mark_datetime(TEXT, TEXT)
RETURNS TIMESTAMPTZ LANGUAGE sql AS
$$
-- This function returns the creation timestamp of a mark if exists for a group,
-- processing the EMAJ_LAST_MARK keyword.
-- input: group name and mark name
-- output: mark date-time or NULL
SELECT case
when $2 = 'EMAJ_LAST_MARK' then
(SELECT mark_datetime FROM emaj.emaj_mark WHERE mark_group = $1 ORDER BY mark_id DESC LIMIT 1)
else (SELECT mark_datetime FROM emaj.emaj_mark WHERE mark_group = $1 AND mark_name = $2)
end
$$;
CREATE OR REPLACE FUNCTION emaj._build_log_seq_name(TEXT, TEXT)
RETURNS TEXT LANGUAGE sql IMMUTABLE AS
$$
-- This function returns the log sequence name associated to an application schema and table.
-- input: application schema name and table name
-- output: log sequence name
SELECT $1 || '_' || $2 || '_log_seq'
$$;
CREATE OR REPLACE FUNCTION emaj._dblink_open_cnx(v_cnxName TEXT)
RETURNS INT LANGUAGE plpgsql AS
$_dblink_open_cnx$
-- This function tries to open a named dblink connection.
-- It uses as target: the current cluster (port), the current database and a role defined in the emaj_param table.
-- This role must be defined with a row having:
-- - param_key = 'dblink_user_password',
-- - param_value_text = 'user=<user> password=<password>' with the rules that apply to usual libPQ connect strings
-- The password can be omited if the connection doesn't require it.
-- The dblink_connect_u is used to open the connection so that emaj_adm but non superuser roles can access
-- cluster even when no password is required to log on.
-- Input: connection name
-- Output: integer status return.
-- 1 successful connection
-- 0 already opened connection
-- -1 dblink is not installed
-- -2 dblink functions are not visible for the session
-- -3 dblink functions are not accessible by the role
-- -4 no 'dblink_user_password' parameter is defined in the emaj_param table
-- -5 error at dblink_connect() call
DECLARE
v_UserPassword TEXT;
v_connectString TEXT;
v_status INT;
BEGIN
IF (SELECT count(*) FROM pg_catalog.pg_proc WHERE proname = 'dblink_connect_u') = 0 THEN
v_status = -1; -- dblink is not installed
ELSIF (SELECT count(*) FROM pg_catalog.pg_proc
WHERE proname = 'dblink_connect_u' AND pg_function_is_visible(oid)) = 0 THEN
v_status = -2; -- dblink is not visible in the search_path
ELSIF NOT has_function_privilege('dblink_connect_u(text, text)', 'execute') THEN
v_status = -3; -- current role has not the execute rights on dblink functions
ELSIF v_cnxName = ANY (dblink_get_connections()) THEN
-- dblink is usable, so search 'emaj' in dblink connections
v_status = 0; -- a connection named 'emaj' is already open
ELSE
-- so, get the 'dblink_user_password' parameter if exists, from emaj_param
SELECT param_value_text INTO v_UserPassword FROM emaj.emaj_param WHERE param_key = 'dblink_user_password';
IF NOT FOUND THEN
v_status = -4; -- no 'dblink_user_password' parameter is defined in the emaj_param table
ELSE
-- ... build the connect string
v_connectString = 'host=localhost port=' ||
(SELECT setting FROM pg_catalog.pg_settings WHERE name = 'port') ||
' dbname=' || current_database() || ' ' || v_userPassword;
-- ... and try to connect
BEGIN
PERFORM dblink_connect_u(v_cnxName,v_connectString);
v_status = 1; -- the connection is successful
EXCEPTION
WHEN OTHERS THEN
v_status = -5; -- the connection attempt failed
END;
END IF;
END IF;
-- for emaj* connection, record the dblink connection attempt in the emaj_hist table
IF substring(v_cnxName from 1 for 4) = 'emaj' THEN
INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object, hist_wording)
VALUES ('DBLINK','OPEN CNX',v_cnxName,'Status = ' || v_status);
END IF;
RETURN v_status;
END;
$_dblink_open_cnx$;
CREATE OR REPLACE FUNCTION emaj._dblink_is_cnx_opened(v_cnxName TEXT)
RETURNS BOOLEAN LANGUAGE plpgsql AS
$_dblink_is_cnx_opened$
-- This function examines if a named dblink connection is opened.
-- Input: connection name
-- Output: boolean indicating whether the dblink connection is opened.
DECLARE
BEGIN
-- test if dblink is installed and usable by the current user
IF (SELECT count(*) FROM pg_catalog.pg_proc WHERE proname = 'dblink_connect' AND pg_function_is_visible(oid)) > 0
AND has_function_privilege('dblink_connect(text, text)', 'execute') THEN
-- dblink is usable, so search connection name in opened dblink connections
IF v_cnxName = ANY (dblink_get_connections()) THEN
RETURN true;
END IF;
END IF;
RETURN false;
END;
$_dblink_is_cnx_opened$;
CREATE OR REPLACE FUNCTION emaj._dblink_close_cnx(v_cnxName TEXT)
RETURNS VOID LANGUAGE plpgsql AS
$_dblink_close_cnx$
-- This function closes a named dblink connection.
-- Input: connection name
DECLARE
BEGIN
IF emaj._dblink_is_cnx_opened(v_cnxName) THEN
-- the emaj connection exists, so disconnect
PERFORM dblink_disconnect(v_cnxName);
-- for emaj* connection, record the dblink disconnection in the emaj_hist table
IF substring(v_cnxName from 1 for 4) = 'emaj' THEN
INSERT INTO emaj.emaj_hist (hist_function, hist_event, hist_object)
VALUES ('DBLINK','CLOSE CNX',v_cnxName);
END IF;
END IF;
RETURN;
END;
$_dblink_close_cnx$;
CREATE OR REPLACE FUNCTION emaj._purge_hist()
RETURNS VOID LANGUAGE plpgsql AS
$_purge_hist$
-- This function purges the emaj history by deleting all rows prior the 'history_retention' parameter, but
-- not deleting event traces after the oldest active mark.
-- It also purges oldest rows from emaj_rlbk, emaj_rlbk_session and emaj_rlbk_plan tables, using the same rules.
-- It is called at start group time and when oldest marks are deleted.
DECLARE
v_datetimeLimit TIMESTAMPTZ;
v_nbPurgedHist BIGINT;
v_maxRlbkId BIGINT;
v_nbPurgedRlbk BIGINT;
v_wording TEXT := '';
BEGIN
-- compute the timestamp limit
SELECT MIN(datetime) FROM
( -- compute the oldest non deleted mark for all groups
(SELECT MIN(mark_datetime) FROM emaj.emaj_mark WHERE NOT mark_is_deleted)
UNION ALL -- compute the current timestamp of now minus the history_retention
(SELECT current_timestamp -
coalesce((SELECT param_value_interval FROM emaj.emaj_param WHERE param_key = 'history_retention'),'1 YEAR'))
) AS t(datetime) INTO v_datetimeLimit;
-- delete oldest rows from emaj_hist
DELETE FROM emaj.emaj_hist WHERE hist_datetime < v_datetimeLimit;
GET DIAGNOSTICS v_nbPurgedHist = ROW_COUNT;
IF v_nbPurgedHist > 0 THEN
v_wording = v_nbPurgedHist || ' emaj_hist rows deleted';
END IF;
-- get the greatest rollback identifier to purge
SELECT MAX(rlbk_id) INTO v_maxRlbkId
FROM emaj.emaj_rlbk WHERE rlbk_start_datetime < v_datetimeLimit;
-- and purge rollback tables
IF v_maxRlbkId IS NOT NULL THEN
DELETE FROM emaj.emaj_rlbk_plan WHERE rlbp_rlbk_id <= v_maxRlbkId;
DELETE FROM emaj.emaj_rlbk_session WHERE rlbs_rlbk_id <= v_maxRlbkId;
DELETE FROM emaj.emaj_rlbk WHERE rlbk_id <= v_maxRlbkId;
GET DIAGNOSTICS v_nbPurgedRlbk = ROW_COUNT;
v_wording = v_wording || ' ; ' || v_nbPurgedRlbk || ' rollback events deleted';
END IF;
-- record the purge into the history if there are significant data
IF v_wording <> '' THEN
INSERT INTO emaj.emaj_hist (hist_function, hist_wording)
VALUES ('PURGE_HISTORY', v_wording);
END IF;
RETURN;
END;
$_purge_hist$;
CREATE OR REPLACE FUNCTION emaj._check_names_array(v_names TEXT[], v_type TEXT)
RETURNS TEXT[] LANGUAGE plpgsql AS
$_check_names_array$
-- This function build a array of names similar to the supplied array, except that NULL
-- values, empty string and duplicate names are suppressed. Issue a warning if the result array is NULL.
-- The function is used to validate group names array or table and sequence names array.
-- Input: names array
-- type of element, used to format warning messages
-- Output: validated names array
DECLARE
v_outputNames TEXT[];
v_i INT;
BEGIN
IF array_upper(v_names,1) >= 1 THEN
-- if there are elements, build the result array
FOR v_i IN 1 .. array_upper(v_names,1) LOOP
-- look for not NULL & not empty name
IF v_names[v_i] IS NULL OR v_names[v_i] = '' THEN
RAISE WARNING '_check_names_array: a % name is NULL or empty.', v_type;
-- look for duplicate name
ELSEIF v_outputNames IS NOT NULL AND v_names[v_i] = ANY (v_outputNames) THEN
RAISE WARNING '_check_names_array: duplicate % name %.', v_type, v_names[v_i];
ELSE
-- OK, keep the name
v_outputNames = array_append (v_outputNames, v_names[v_i]);
END IF;
END LOOP;
END IF;
-- check for NULL result
IF v_outputNames IS NULL THEN
RAISE WARNING '_check_names_array: No % name to process.', v_type;
END IF;
RETURN v_outputNames;
END;
$_check_names_array$;
CREATE OR REPLACE FUNCTION emaj._check_group_content(v_groupName TEXT)
RETURNS VOID LANGUAGE plpgsql AS
$_check_group_content$
-- This function verifies that the content of tables group as defined into the emaj_group_def table is correct.
-- It is called by emaj_create_group() and emaj_alter_group() functions.
-- It checks that the referenced application tables and sequences,
-- - exist,
-- - is not located into an E-Maj schema (to protect against an E-Maj recursive use),
-- - do not already belong to another tables group.
-- Input: the name of the tables group to check
DECLARE
v_msg TEXT := '';
r_tblsq RECORD;
BEGIN
-- check that all application tables and sequences listed for the group really exist
FOR r_tblsq IN
SELECT grpdef_schema || '.' || grpdef_tblseq AS full_name
FROM emaj.emaj_group_def WHERE grpdef_group = v_groupName
EXCEPT
SELECT nspname || '.' || relname FROM pg_catalog.pg_class, pg_catalog.pg_namespace
WHERE relnamespace = pg_namespace.oid AND relkind IN ('r','S')
ORDER BY 1
LOOP
IF v_msg <> '' THEN
v_msg = v_msg || ', ';
END IF;
v_msg = v_msg || r_tblsq.full_name;
END LOOP;
IF v_msg <> '' THEN
RAISE EXCEPTION '_check_group_content: one or several tables or sequences do not exist (%).', v_msg;
END IF;
-- check no application schema listed for the group in the emaj_group_def table is an E-Maj schema
FOR r_tblsq IN
SELECT grpdef_schema || '.' || grpdef_tblseq AS full_name
FROM emaj.emaj_group_def
WHERE grpdef_group = v_groupName
AND grpdef_schema IN (
SELECT DISTINCT rel_log_schema FROM emaj.emaj_relation
UNION
SELECT 'emaj')
ORDER BY 1
LOOP
IF v_msg <> '' THEN
v_msg = v_msg || ', ';
END IF;
v_msg = v_msg || r_tblsq.full_name;
END LOOP;
IF v_msg <> '' THEN
RAISE EXCEPTION '_check_group_content: one or several tables or sequences belong to an E-Maj schema (%).', v_msg;
END IF;
-- check that no table or sequence of the new group already belongs to another created group
FOR r_tblsq IN
SELECT grpdef_schema || '.' || grpdef_tblseq || ' in ' || rel_group AS full_name
FROM emaj.emaj_group_def, emaj.emaj_relation
WHERE grpdef_schema = rel_schema AND grpdef_tblseq = rel_tblseq
AND grpdef_group = v_groupName AND rel_group <> v_groupName
ORDER BY 1
LOOP
IF v_msg <> '' THEN
v_msg = v_msg || ', ';
END IF;
v_msg = v_msg || r_tblsq.full_name;
END LOOP;
IF v_msg <> '' THEN
RAISE EXCEPTION '_check_group_content: one or several tables already belong to another group (%).', v_msg;
END IF;
RETURN;
END;
$_check_group_content$;
CREATE OR REPLACE FUNCTION emaj._check_new_mark(v_mark TEXT, v_groupNames TEXT[])
RETURNS TEXT LANGUAGE plpgsql AS
$_check_new_mark$
-- This function verifies that a new mark name supplied the user is valid.
-- It processes the possible NULL mark value and the replacement of % wild characters.
-- It also checks that the mark name do not already exist for any group.
-- Input: name of the mark to set, array of group names
-- The array of group names may be NULL to avoid the check against groups
-- Output: internal name of the mark
DECLARE
v_i INT;
v_markName TEXT := v_mark;
BEGIN
-- check the mark name is not 'EMAJ_LAST_MARK'
IF v_mark = 'EMAJ_LAST_MARK' THEN
RAISE EXCEPTION '_check_new_mark: % is not an allowed name for a new mark.', v_mark;
END IF;
-- process null or empty supplied mark name
IF v_markName = '' OR v_markName IS NULL THEN
v_markName = 'MARK_%';
END IF;
-- process % wild characters in mark name
v_markName = replace(v_markName, '%', to_char(current_timestamp, 'HH24.MI.SS.MS'));
-- if requested, check the existence of the mark in groups
IF v_groupNames IS NOT NULL THEN
-- for each group of the array,