-
Notifications
You must be signed in to change notification settings - Fork 9
/
instPsqlPrev.out
5312 lines (5301 loc) · 302 KB
/
instPsqlPrev.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
-- instPsqlPrev.sql: Install previous version of E-Maj as simple psql script (for postgres version prior 9.1)
-- It installs the previous E-Maj version for migration test.
--
------------------------------------------------------------
-- install dblink
------------------------------------------------------------
-- this 8.4.8 version seems compatible with 8.2 to 9.0 pg version
-- for future use...
--\i ~/postgresql-8.4.8/contrib/dblink/dblink.sql
-----------------------------
-- 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 with previous version
-----------------------------
\i ../../../emaj-1.0.0/sql/emaj.sql
--
-- E-Maj : logs and rollbacks table updates : Version 1.0.0
--
-- 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.2
-- (warning, the test is alphanumeric => to be adapted when pg 10.0 will appear!)
IF substring (version() from E'PostgreSQL\\s(\\d+\\.\\d+)') < '8.2' 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 emaj.emaj_relation table and the postgres schemas table
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;
-- if there is an error (schema or table or column not there), continue
-- check the dblink contrib is 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.';
-- 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 the _txid_current() function
-- 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;
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;
-- 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;
-- create a SQL emaj_txid_function that encapsulates the standart txid_current function with postgres 8.3+
-- or just returns 0 with postgres 8.2
v_stmt = 'CREATE OR REPLACE FUNCTION emaj._txid_current() RETURNS BIGINT LANGUAGE SQL AS $$ SELECT ';
IF v_pgVersion < '8.3' THEN
v_stmt = v_stmt || '0::BIGINT;$$';
ELSE
v_stmt = v_stmt || 'txid_current();$$';
END IF;
EXECUTE v_stmt;
-- 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 technical sequences and tables --
-- --
-----------------------------------------
-- 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 emaj._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_state TEXT NOT NULL, -- 2 possibles states:
-- 'LOGGING' between emaj_start_group and emaj_stop_group
-- 'IDLE' 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)
rel_session INT, -- rollback session id
rel_rows BIGINT, -- number of rows to rollback, computed at rollback time
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_state TEXT, -- state of the mark, with 2 possible values:
-- 'ACTIVE' and 'DELETED'
mark_comment TEXT, -- optional user comment
mark_txid BIGINT -- id of the tx that has set the mark
DEFAULT emaj._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 statistics about previously executed rollback operations
-- and used to estimate rollback durations
CREATE TABLE emaj.emaj_rlbk_stat (
rlbk_operation TEXT NOT NULL, -- type of operation, can contains 'rlbk', 'del_log', 'cre_fk'
rlbk_schema TEXT NOT NULL, -- schema that owns the table or the foreign key
rlbk_tbl_fk TEXT NOT NULL, -- table or foreign key name
rlbk_datetime TIMESTAMPTZ NOT NULL, -- timestamp of the rollback that has generated the statistic
rlbk_nb_rows BIGINT NOT NULL, -- number of rows processed by the operation
rlbk_duration INTERVAL NOT NULL, -- duration of the elementary operation
PRIMARY KEY (rlbk_operation, rlbk_schema, rlbk_tbl_fk, rlbk_datetime)
);
COMMENT ON TABLE emaj.emaj_rlbk_stat IS
$$Contains statistics about previous E-Maj rollback durations.$$;
-- working storage table containing foreign key definition
-- (used at table rollback time to drop and later recreate foreign keys)
CREATE TABLE emaj.emaj_fk (
fk_groups TEXT[] NOT NULL, -- groups for which the rollback operation is performed
fk_session INT NOT NULL, -- session number (for parallel rollback purpose)
fk_name TEXT NOT NULL, -- foreign key name
fk_schema TEXT NOT NULL, -- schema name of the table that owns the foreign key
fk_table TEXT NOT NULL, -- name of the table that owns the foreign key
fk_action TEXT NOT NULL, -- action to perform at the end of the rollback operation
-- can contain 'create_fk' or 'set_fk_immediate"
fk_def TEXT , -- foreign key definition as reported by pg_get_constraintdef
PRIMARY KEY (fk_groups, fk_name, fk_schema, fk_table)
);
COMMENT ON TABLE emaj.emaj_fk IS
$$Contains temporary description of foreign keys suppressed by E-Maj rollback operations.$$;
------------------------------------
-- --
-- emaj 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._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','1.0.0');
-- Other parameters are optional. They are set by users if needed.
-- The history_retention parameter defines the time interval when a row remains in the emaj history table - default is 1 month
-- INSERT INTO emaj.emaj_param (param_key, param_value_interval) VALUES ('history_retention','1 month'::interval);
-- 5 parameters are used by the emaj_estimate_rollback_duration function as a default value 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);
-- The fixed_table_with_rollback_duration parameter defines the additional fixed rollback cost for any table that has effective rows to rollback
-- INSERT INTO emaj.emaj_param (param_key, param_value_interval) VALUES ('fixed_table_with_rollback_duration','2.5 millisecond'::interval);
------------------------------------
-- --
-- Low level Functions --
-- --
------------------------------------
CREATE OR REPLACE FUNCTION emaj._pg_version() RETURNS TEXT LANGUAGE sql IMMUTABLE as
$$
-- This functions 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._purge_hist() RETURNS VOID LANGUAGE sql as
$$
-- This function purges the emaj history by deleting all rows prior the 'history_retention' parameter
-- but not deleting rows generated by groups that are currently in logging state.
-- It is called at start group time and when oldest marks are deleted.
DELETE FROM emaj.emaj_hist WHERE hist_datetime <
(SELECT MIN(datetime) FROM
(
-- compute the oldest active mark for all groups
(SELECT MIN(mark_datetime) FROM emaj.emaj_mark WHERE mark_state = 'ACTIVE')
UNION
-- compute the timestamp of now minus the history_retention (1 month by default)
(SELECT current_timestamp -
coalesce((SELECT param_value_interval FROM emaj.emaj_param WHERE param_key = 'history_retention'),'1 MONTH'))
) AS tmst(datetime));
$$;
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._check_group_names_array(v_groupNames TEXT[])
RETURNS TEXT[] LANGUAGE plpgsql AS
$_check_group_names_array$
-- This function build a array of group 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.
-- Input: group names array
-- Output: validated group names array
DECLARE
v_gn TEXT[];
v_i INT;
BEGIN
IF array_upper(v_groupNames,1) >= 1 THEN
-- if there are elements, build the result array
FOR v_i in 1 .. array_upper(v_groupNames,1) LOOP
-- look for not NULL & not empty group name
IF v_groupNames[v_i] IS NULL OR v_groupNames[v_i] = '' THEN
RAISE WARNING '_check_group_names_array: a group name is NULL or empty.';
-- look for duplicate name
ELSEIF v_gn IS NOT NULL AND v_groupNames[v_i] = ANY (v_gn) THEN
RAISE WARNING '_check_group_names_array: duplicate group name %.',v_groupNames[v_i];
ELSE
-- OK, keep the name
v_gn = array_append (v_gn, v_groupNames[v_i]);
END IF;
END LOOP;
END IF;
-- check for NULL result
IF v_gn IS NULL THEN
RAISE WARNING '_check_group_names_array: No group name to process.';
END IF;
RETURN v_gn;
END;
$_check_group_names_array$;
CREATE OR REPLACE FUNCTION emaj._check_class(v_schemaName TEXT, v_className TEXT)
RETURNS TEXT LANGUAGE plpgsql AS
$_check_class$
-- This function verifies that an application table or sequence exists in pg_class
-- It also protects from a recursive use : tables or sequences from emaj schema cannot be managed by EMAJ
-- Input: the names of the schema and the class (table or sequence)
-- Output: the relkind of the class : 'r' for a table and 's' for a sequence
-- If the schema or the class is not known, the function stops.
DECLARE
v_relKind TEXT;
v_schemaOid OID;
BEGIN
IF v_schemaName = 'emaj' THEN
RAISE EXCEPTION '_check_class: object from schema % cannot be managed by EMAJ.', v_schemaName;
END IF;
SELECT oid INTO v_schemaOid FROM pg_catalog.pg_namespace WHERE nspname = v_schemaName;
IF NOT FOUND THEN
RAISE EXCEPTION '_check_class: schema % doesn''t exist.', v_schemaName;
END IF;
SELECT relkind INTO v_relKind FROM pg_catalog.pg_class
WHERE relnamespace = v_schemaOid AND relname = v_className AND relkind in ('r','S');
IF NOT FOUND THEN
RAISE EXCEPTION '_check_class: table or sequence % doesn''t exist.', v_className;
END IF;
RETURN v_relKind;
END;
$_check_class$;
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,
FOR v_i in 1 .. array_upper(v_groupNames,1) LOOP
-- ... if a mark with the same name already exists for the group, stop
PERFORM 0 FROM emaj.emaj_mark
WHERE mark_group = v_groupNames[v_i] AND mark_name = v_markName;
IF FOUND THEN
RAISE EXCEPTION '_check_new_mark: Group % already contains a mark named %.', v_groupNames[v_i], v_markName;
END IF;
END LOOP;
END IF;
RETURN v_markName;
END;
$_check_new_mark$;
CREATE OR REPLACE FUNCTION emaj._forbid_truncate_fnct() RETURNS TRIGGER AS
$_forbid_truncate_fnct$
-- The function is triggered by the execution of TRUNCATE SQL verb on tables of a rollbackable group
-- in logging mode.
-- It can only be called with postgresql in a version greater or equal 8.4
BEGIN
IF (TG_OP = 'TRUNCATE') THEN
RAISE EXCEPTION 'emaj._forbid_truncate_fnct: TRUNCATE is not allowed while updates on this table (%.%) are currently protected by E-Maj. Consider stopping the group before issuing a TRUNCATE.', TG_TABLE_SCHEMA, TG_TABLE_NAME;
END IF;
RETURN NULL;
END;
$_forbid_truncate_fnct$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE OR REPLACE FUNCTION emaj._log_truncate_fnct() RETURNS TRIGGER AS
$_log_truncate_fnct$
-- The function is triggered by the execution of TRUNCATE SQL verb on tables of an audit_only group
-- in logging mode.
-- It can only be called with postgresql in a version greater or equal 8.4
DECLARE
v_logSchema TEXT;
v_logTableName TEXT;
BEGIN
IF (TG_OP = 'TRUNCATE') THEN
SELECT rel_log_schema INTO v_logSchema FROM emaj.emaj_relation
WHERE rel_schema = TG_TABLE_SCHEMA AND rel_tblseq = TG_TABLE_NAME;
v_logTableName := quote_ident(v_logSchema) || '.' || quote_ident(TG_TABLE_SCHEMA || '_' || TG_TABLE_NAME || '_log');
EXECUTE 'INSERT INTO ' || v_logTableName || ' (emaj_verb) VALUES (''TRU'')';
END IF;
RETURN NULL;
END;
$_log_truncate_fnct$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE OR REPLACE FUNCTION emaj._create_log_schema(v_logSchemaName TEXT)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS
$_create_log_schema$
-- The function creates a log schema and gives the appropriate rights to emaj users
-- Input: log schema name
-- The function is created as SECURITY DEFINER so that secondary schemas can be owned by superuser
DECLARE
BEGIN
-- check that the schema doesn't already exist
PERFORM 0 FROM pg_catalog.pg_namespace WHERE nspname = v_logSchemaName;
IF FOUND THEN
RAISE EXCEPTION '_create_log_schema: schema % should not exist. Drop it manually, or modify emaj_group_def table''s content.',v_logSchemaName;
END IF;
-- create the schema and give the appropriate rights
EXECUTE 'CREATE SCHEMA ' || quote_ident(v_logSchemaName);
EXECUTE 'GRANT ALL ON SCHEMA ' || quote_ident(v_logSchemaName) || ' TO emaj_adm';
EXECUTE 'GRANT USAGE ON SCHEMA ' || quote_ident(v_logSchemaName) || ' TO emaj_viewer';
RETURN;
END;
$_create_log_schema$;
CREATE OR REPLACE FUNCTION emaj._drop_log_schema(v_logSchemaName TEXT, v_isForced BOOLEAN)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS
$_drop_log_schema$
-- The function drops a log schema
-- Input: log schema name, boolean telling whether the schema to drop may contain residual objects
-- The function is created as SECURITY DEFINER so that secondary schemas can be dropped in any case
DECLARE
BEGIN
-- check that the schema doesn't already exist
PERFORM 0 FROM pg_catalog.pg_namespace WHERE nspname = v_logSchemaName;
IF NOT FOUND THEN
RAISE EXCEPTION '_drop_log_schema: schema % doesn''t exist.',v_logSchemaName;
END IF;
IF v_isForced THEN
-- drop cascade when called by emaj_force_xxx_group()
EXECUTE 'DROP SCHEMA ' || quote_ident(v_logSchemaName) || ' CASCADE';
ELSE
-- otherwise, drop restrict with a trap on the potential error
BEGIN
EXECUTE 'DROP SCHEMA ' || quote_ident(v_logSchemaName);
EXCEPTION
-- trap the 2BP01 exception to generate a more understandable error message
WHEN DEPENDENT_OBJECTS_STILL_EXIST THEN -- SQLSTATE '2BP01'
RAISE EXCEPTION '_drop_log_schema: cannot drop schema %. It probably owns unattended objects. Use the emaj_verify_all() function to get details', quote_ident(v_logSchemaName);
END;
END IF;
RETURN;
END;
$_drop_log_schema$;
---------------------------------------------------
-- --
-- Elementary functions for tables and sequences --
-- --
---------------------------------------------------
CREATE OR REPLACE FUNCTION emaj._create_tbl(v_schemaName TEXT, v_tableName TEXT, v_logSchema TEXT, v_logDatTsp TEXT, v_logIdxTsp TEXT, v_isRollbackable BOOLEAN)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS
$_create_tbl$
-- This function creates all what is needed to manage the log and rollback operations for an application table
-- Input: schema name (mandatory even for the 'public' schema), table name, schema holding log objects, data and index tablespaces for the log table, boolean indicating whether the table belongs to a rollbackable group
-- Are created in the log schema:
-- - the associated log table, with its own sequence
-- - the function that logs the tables updates, defined as a trigger
-- - the rollback function (one per table and only if the group is rollbackable)
-- The function is defined as SECURITY DEFINER so that emaj_adm role can use it even if he is not the owner of the application table.
DECLARE
-- variables for the name of tables, functions, triggers,...
v_fullTableName TEXT;
v_dataTblSpace TEXT;
v_idxTblSpace TEXT;
v_logTableName TEXT;
v_logIdxName TEXT;
v_logFnctName TEXT;
v_rlbkFnctName TEXT;
v_exceptionRlbkFnctName TEXT;
v_logTriggerName TEXT;
v_truncTriggerName TEXT;
v_sequenceName TEXT;
-- variables to hold pieces of SQL
v_pkCondList TEXT;
v_colList TEXT;
v_valList TEXT;
v_setList TEXT;
-- other variables
v_attname TEXT;
v_relhaspkey BOOLEAN;
v_pgVersion TEXT := emaj._pg_version();
v_stmt TEXT := '';
v_triggerList TEXT := '';
r_column RECORD;
r_trigger RECORD;
-- cursor to retrieve all columns of the application table
col1_curs CURSOR (tbl regclass) FOR
SELECT attname FROM pg_catalog.pg_attribute
WHERE attrelid = tbl
AND attnum > 0
AND attisdropped = false
ORDER BY attnum;
-- cursor to retrieve all columns of table's primary key
-- (taking column names in pg_attribute from the table's definition instead of index definition is mandatory
-- starting from pg9.0, joining tables with indkey instead of indexrelid)
col2_curs CURSOR (tbl regclass) FOR
SELECT attname FROM pg_catalog.pg_attribute, pg_catalog.pg_index
WHERE pg_attribute.attrelid = pg_index.indrelid
AND attnum = ANY (indkey)
AND indrelid = tbl AND indisprimary
AND attnum > 0 AND attisdropped = false;
BEGIN
-- check the table has a primary key
SELECT true INTO v_relhaspkey FROM pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_constraint
WHERE relnamespace = pg_namespace.oid AND connamespace = pg_namespace.oid AND conrelid = pg_class.oid
AND contype = 'p' AND nspname = v_schemaName AND relname = v_tableName;
IF NOT FOUND THEN
v_relhaspkey = false;
END IF;
IF v_isRollbackable AND v_relhaspkey = FALSE THEN
RAISE EXCEPTION '_create_tbl: table % has no PRIMARY KEY.', v_tableName;
END IF;
-- prepare TABLESPACE clauses for data and index
IF v_logDatTsp IS NOT NULL THEN
v_dataTblSpace = 'TABLESPACE ' || quote_ident(v_logDatTsp);
ELSE
v_dataTblSpace = '';
END IF;
IF v_logIdxTsp IS NOT NULL THEN
v_idxTblSpace = 'TABLESPACE ' || quote_ident(v_logIdxTsp);
ELSE
v_idxTblSpace = '';
END IF;
-- build the different name for table, trigger, functions,...
v_fullTableName := quote_ident(v_schemaName) || '.' || quote_ident(v_tableName);
v_logTableName := quote_ident(v_logSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_log');
v_logIdxName := quote_ident(v_schemaName || '_' || v_tableName || '_log_idx');
v_logFnctName := quote_ident(v_logSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_log_fnct');
v_rlbkFnctName := quote_ident(v_logSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_rlbk_fnct');
v_exceptionRlbkFnctName=substring(quote_literal(v_rlbkFnctName) FROM '^(.*).$'); -- suppress last character
v_logTriggerName := quote_ident(v_schemaName || '_' || v_tableName || '_emaj_log_trg');
v_truncTriggerName := quote_ident(v_schemaName || '_' || v_tableName || '_emaj_trunc_trg');
v_sequenceName := quote_ident(v_logSchema) || '.' || quote_ident(emaj._build_log_seq_name(v_schemaName, v_tableName));
-- creation of the log table: the log table looks like the application table, with some additional technical columns
EXECUTE 'DROP TABLE IF EXISTS ' || v_logTableName;
EXECUTE 'CREATE TABLE ' || v_logTableName
|| ' ( LIKE ' || v_fullTableName || ') ' || v_dataTblSpace;
EXECUTE 'ALTER TABLE ' || v_logTableName
|| ' ADD COLUMN emaj_verb VARCHAR(3),'
|| ' ADD COLUMN emaj_tuple VARCHAR(3),'
|| ' ADD COLUMN emaj_gid BIGINT NOT NULL DEFAULT nextval(''emaj.emaj_global_seq''),'
|| ' ADD COLUMN emaj_changed TIMESTAMPTZ DEFAULT clock_timestamp(),'
|| ' ADD COLUMN emaj_txid BIGINT DEFAULT emaj._txid_current(),'
|| ' ADD COLUMN emaj_user VARCHAR(32) DEFAULT session_user,'
|| ' ADD COLUMN emaj_user_ip INET DEFAULT inet_client_addr()';
-- creation of the index on the log table
IF v_pgVersion >= '8.3' THEN
EXECUTE 'CREATE UNIQUE INDEX ' || v_logIdxName || ' ON '
|| v_logTableName || ' (emaj_gid, emaj_tuple DESC) ' || v_idxTblSpace;
ELSE
-- in 8.2, DESC clause doesn't exist. So the index cannot be used at rollback time.
-- It only enforces the uniqueness of (emaj_gid, emaj_tuple)
EXECUTE 'CREATE UNIQUE INDEX ' || v_logIdxName || ' ON '
|| v_logTableName || ' (emaj_gid, emaj_tuple) ' || v_idxTblSpace;
END IF;
-- remove the NOT NULL constraints of application columns.
-- They are useless and blocking to store truncate event for tables belonging to audit_only tables
FOR r_column IN
SELECT ' ALTER COLUMN ' || quote_ident(attname) || ' DROP NOT NULL' AS action
FROM pg_catalog.pg_attribute, pg_catalog.pg_class, pg_catalog.pg_namespace
WHERE relnamespace = pg_namespace.oid AND attrelid = pg_class.oid
AND nspname = v_logSchema AND relname = v_schemaName || '_' || v_tableName || '_log'
AND attnum > 0 AND attnotnull AND attisdropped = false AND attname NOT LIKE E'emaj\\_%'
LOOP
IF v_stmt = '' THEN
v_stmt = v_stmt || r_column.action;
ELSE
v_stmt = v_stmt || ',' || r_column.action;
END IF;
END LOOP;
IF v_stmt <> '' THEN
EXECUTE 'ALTER TABLE ' || v_logTableName || v_stmt;
END IF;
-- create the sequence associated to the log table
EXECUTE 'CREATE SEQUENCE ' || v_sequenceName;
-- creation of the log fonction that will be mapped to the log trigger later
-- The new row is logged for each INSERT, the old row is logged for each DELETE
-- and the old and the new rows are logged for each UPDATE.
EXECUTE 'CREATE OR REPLACE FUNCTION ' || v_logFnctName || '() RETURNS trigger AS $logfnct$'
|| 'BEGIN'
-- The sequence associated to the log table is incremented at the beginning of the function ...
|| ' PERFORM NEXTVAL(' || quote_literal(v_sequenceName) || ');'
-- ... and the global id sequence is incremented by the first/only INSERT into the log table.
|| ' IF (TG_OP = ''DELETE'') THEN'
|| ' INSERT INTO ' || v_logTableName || ' SELECT OLD.*, ''DEL'', ''OLD'';'
|| ' RETURN OLD;'
|| ' ELSIF (TG_OP = ''UPDATE'') THEN'
|| ' INSERT INTO ' || v_logTableName || ' SELECT OLD.*, ''UPD'', ''OLD'';'
|| ' INSERT INTO ' || v_logTableName || ' SELECT NEW.*, ''UPD'', ''NEW'', lastval();'
|| ' RETURN NEW;'
|| ' ELSIF (TG_OP = ''INSERT'') THEN'
|| ' INSERT INTO ' || v_logTableName || ' SELECT NEW.*, ''INS'', ''NEW'';'
|| ' RETURN NEW;'
|| ' END IF;'
|| ' RETURN NULL;'
|| 'END;'
|| '$logfnct$ LANGUAGE plpgsql SECURITY DEFINER;';
-- creation of the log trigger on the application table, using the previously created log function
-- But the trigger is not immediately activated (it will be at emaj_start_group time)
EXECUTE 'DROP TRIGGER IF EXISTS ' || v_logTriggerName || ' ON ' || v_fullTableName;
EXECUTE 'CREATE TRIGGER ' || v_logTriggerName
|| ' AFTER INSERT OR UPDATE OR DELETE ON ' || v_fullTableName
|| ' FOR EACH ROW EXECUTE PROCEDURE ' || v_logFnctName || '()';
EXECUTE 'ALTER TABLE ' || v_fullTableName || ' DISABLE TRIGGER ' || v_logTriggerName;
-- creation of the trigger that manage any TRUNCATE on the application table
-- But the trigger is not immediately activated (it will be at emaj_start_group time)
IF v_pgVersion >= '8.4' THEN
EXECUTE 'DROP TRIGGER IF EXISTS ' || v_truncTriggerName || ' ON ' || v_fullTableName;
IF v_isRollbackable THEN
-- For rollbackable groups, use the common _forbid_truncate_fnct() function that blocks the operation
EXECUTE 'CREATE TRIGGER ' || v_truncTriggerName
|| ' BEFORE TRUNCATE ON ' || v_fullTableName
|| ' FOR EACH STATEMENT EXECUTE PROCEDURE emaj._forbid_truncate_fnct()';
ELSE
-- For audit_only groups, use the common _log_truncate_fnct() function that records the operation into the log table
EXECUTE 'CREATE TRIGGER ' || v_truncTriggerName
|| ' BEFORE TRUNCATE ON ' || v_fullTableName
|| ' FOR EACH STATEMENT EXECUTE PROCEDURE emaj._log_truncate_fnct()';
END IF;
EXECUTE 'ALTER TABLE ' || v_fullTableName || ' DISABLE TRIGGER ' || v_truncTriggerName;
END IF;
--
-- create the rollback function, if the table belongs to a rollbackable group
--
IF v_isRollbackable THEN
-- First build some pieces of the CREATE FUNCTION statement
-- build the tables's columns list
-- and the SET clause for the UPDATE, from the same columns list
v_colList := '';
v_valList := '';
v_setList := '';
OPEN col1_curs (v_fullTableName);
LOOP
FETCH col1_curs INTO v_attname;
EXIT WHEN NOT FOUND;
IF v_colList = '' THEN
v_colList := quote_ident(v_attname);
v_valList := 'rec_log.' || quote_ident(v_attname);
v_setList := quote_ident(v_attname) || ' = rec_old_log.' || quote_ident(v_attname);
ELSE
v_colList := v_colList || ', ' || quote_ident(v_attname);
v_valList := v_valList || ', rec_log.' || quote_ident(v_attname);
v_setList := v_setList || ', ' || quote_ident(v_attname) || ' = rec_old_log.' || quote_ident(v_attname);
END IF;
END LOOP;
CLOSE col1_curs;
-- build "equality on the primary key" conditions, from the list of the primary key's columns
v_pkCondList := '';
OPEN col2_curs (v_fullTableName);
LOOP
FETCH col2_curs INTO v_attname;
EXIT WHEN NOT FOUND;
IF v_pkCondList = '' THEN
v_pkCondList := quote_ident(v_attname) || ' = rec_log.' || quote_ident(v_attname);
ELSE
v_pkCondList := v_pkCondList || ' AND ' || quote_ident(v_attname) || ' = rec_log.' || quote_ident(v_attname);
END IF;
END LOOP;
CLOSE col2_curs;
-- Then create the rollback function associated to the table
-- At execution, it will loop on each row from the log table in reverse order
-- It will insert the old deleted rows, delete the new inserted row
-- and update the new rows by setting back the old rows
-- The function returns the number of rollbacked elementary operations or rows
-- All these functions will be called by the emaj_rlbk_tbl function, which is activated by the
-- emaj_rollback_group function
EXECUTE 'CREATE OR REPLACE FUNCTION ' || v_rlbkFnctName || ' (v_lastGlobalSeq BIGINT)'
|| ' RETURNS BIGINT AS $rlbkfnct$'
|| ' DECLARE'
|| ' v_nb_rows BIGINT := 0;'
|| ' v_nb_proc_rows INTEGER;'
|| ' rec_log ' || v_logTableName || '%ROWTYPE;'
|| ' rec_old_log ' || v_logTableName || '%ROWTYPE;'
|| ' log_curs CURSOR FOR '
|| ' SELECT * FROM ' || v_logTableName
|| ' WHERE emaj_gid > v_lastGlobalSeq '
|| ' ORDER BY emaj_gid DESC, emaj_tuple;'
|| ' BEGIN'
|| ' OPEN log_curs;'
|| ' LOOP '
|| ' FETCH log_curs INTO rec_log;'
|| ' EXIT WHEN NOT FOUND;'
|| ' IF rec_log.emaj_verb = ''INS'' THEN'
-- || ' RAISE NOTICE ''emaj_gid = % ; INS'', rec_log.emaj_gid;'
|| ' DELETE FROM ONLY ' || v_fullTableName || ' WHERE ' || v_pkCondList || ';'
|| ' ELSIF rec_log.emaj_verb = ''UPD'' THEN'
-- || ' RAISE NOTICE ''emaj_gid = % ; UPD ; %'', rec_log.emaj_gid,rec_log.emaj_tuple;'
|| ' FETCH log_curs into rec_old_log;'
-- || ' RAISE NOTICE ''emaj_gid = % ; UPD ; %'', rec_old_log.emaj_gid,rec_old_log.emaj_tuple;'
|| ' UPDATE ONLY ' || v_fullTableName || ' SET ' || v_setList || ' WHERE ' || v_pkCondList || ';'
|| ' ELSIF rec_log.emaj_verb = ''DEL'' THEN'
-- || ' RAISE NOTICE ''emaj_gid = % ; DEL'', rec_log.emaj_gid;'
|| ' INSERT INTO ' || v_fullTableName || ' (' || v_colList || ') VALUES (' || v_valList || ');'
|| ' ELSE'
|| ' RAISE EXCEPTION ' || v_exceptionRlbkFnctName || ': internal error - emaj_verb = % is unknown, emaj_gid = %.'','
|| ' rec_log.emaj_verb, rec_log.emaj_gid;'
|| ' END IF;'
|| ' GET DIAGNOSTICS v_nb_proc_rows = ROW_COUNT;'
|| ' IF v_nb_proc_rows <> 1 THEN'
|| ' RAISE EXCEPTION ' || v_exceptionRlbkFnctName || ': internal error - emaj_verb = %, emaj_gid = %, # processed rows = % .'''
|| ' ,rec_log.emaj_verb, rec_log.emaj_gid, v_nb_proc_rows;'
|| ' END IF;'
|| ' v_nb_rows := v_nb_rows + 1;'
|| ' END LOOP;'
|| ' CLOSE log_curs;'
-- || ' RAISE NOTICE ''Table ' || v_fullTableName || ' -> % rollbacked rows.'', v_nb_rows;'
|| ' RETURN v_nb_rows;'
|| ' END;'
|| '$rlbkfnct$ LANGUAGE plpgsql;';
END IF;
-- check if the table has (neither internal - ie. created for fk - nor previously created by emaj) trigger,
-- This check is not done for postgres 8.2 because column tgconstraint doesn't exist
IF v_pgVersion >= '8.3' THEN
FOR r_trigger IN
SELECT tgname FROM pg_catalog.pg_trigger
WHERE tgrelid = v_fullTableName::regclass AND tgconstraint = 0 AND tgname NOT LIKE E'%emaj\\_%\\_trg'
LOOP
IF v_triggerList = '' THEN
v_triggerList = v_triggerList || r_trigger.tgname;
ELSE
v_triggerList = v_triggerList || ', ' || r_trigger.tgname;
END IF;
END LOOP;
-- if yes, issue a warning (if a trigger updates another table in the same table group or outside) it could generate problem at rollback time)
IF v_triggerList <> '' THEN
RAISE WARNING '_create_tbl: table % has triggers (%). Verify the compatibility with emaj rollback operations (in particular if triggers update one or several other tables). Triggers may have to be manualy disabled before rollback.', v_fullTableName, v_triggerList;
END IF;
END IF;
-- grant appropriate rights to both emaj roles
EXECUTE 'GRANT SELECT ON TABLE ' || v_logTableName || ' TO emaj_viewer';
EXECUTE 'GRANT ALL PRIVILEGES ON TABLE ' || v_logTableName || ' TO emaj_adm';
EXECUTE 'GRANT SELECT ON SEQUENCE ' || v_sequenceName || ' TO emaj_viewer';
EXECUTE 'GRANT ALL PRIVILEGES ON SEQUENCE ' || v_sequenceName || ' TO emaj_adm';
RETURN;
END;
$_create_tbl$;
CREATE OR REPLACE FUNCTION emaj._drop_tbl(v_schemaName TEXT, v_tableName TEXT, v_logSchema TEXT, v_isRollbackable BOOLEAN)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS
$_drop_tbl$
-- The function deletes all what has been created by _create_tbl function
-- Required inputs: schema name, table name, log schema and a boolean indicating whether the related group was created as rollbackable
-- The function is defined as SECURITY DEFINER so that emaj_adm role can use it even if he is not the owner of the application table.
DECLARE
v_pgVersion TEXT := emaj._pg_version();
v_fullTableName TEXT;
v_logTableName TEXT;
v_logFnctName TEXT;
v_rlbkFnctName TEXT;
v_logTriggerName TEXT;
v_truncTriggerName TEXT;
v_seqName TEXT;
v_fullSeqName TEXT;
BEGIN
v_fullTableName := quote_ident(v_schemaName) || '.' || quote_ident(v_tableName);
v_logTableName := quote_ident(v_logSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_log');
v_logFnctName := quote_ident(v_logSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_log_fnct');
v_rlbkFnctName := quote_ident(v_logSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_rlbk_fnct');
v_logTriggerName := quote_ident(v_schemaName || '_' || v_tableName || '_emaj_log_trg');
v_truncTriggerName := quote_ident(v_schemaName || '_' || v_tableName || '_emaj_trunc_trg');
v_seqName := emaj._build_log_seq_name(v_schemaName, v_tableName);
v_fullSeqName := quote_ident(v_logSchema) || '.' || quote_ident(v_seqName);
-- check the table exists before dropping its triggers
PERFORM 0 FROM pg_catalog.pg_class, pg_catalog.pg_namespace
WHERE relnamespace = pg_namespace.oid
AND nspname = v_schemaName AND relname = v_tableName AND relkind = 'r';
IF FOUND THEN
-- delete the log trigger on the application table
EXECUTE 'DROP TRIGGER IF EXISTS ' || v_logTriggerName || ' ON ' || v_fullTableName;
-- delete the truncate trigger on the application table
IF v_pgVersion >= '8.4' THEN
EXECUTE 'DROP TRIGGER IF EXISTS ' || v_truncTriggerName || ' ON ' || v_fullTableName;
END IF;
END IF;
-- delete log and rollback functions,
EXECUTE 'DROP FUNCTION IF EXISTS ' || v_logFnctName || '()';
IF v_isRollbackable THEN
EXECUTE 'DROP FUNCTION IF EXISTS ' || v_rlbkFnctName || '(bigint)';
END IF;
-- delete the sequence associated to the log table
EXECUTE 'DROP SEQUENCE IF EXISTS ' || v_fullSeqName;
-- delete the log table
EXECUTE 'DROP TABLE IF EXISTS ' || v_logTableName || ' CASCADE';
-- delete rows related to the log sequence from emaj_sequence table
DELETE FROM emaj.emaj_sequence WHERE sequ_schema = v_logSchema AND sequ_name = v_seqName;
-- delete rows related to the table from emaj_seq_hole table
DELETE FROM emaj.emaj_seq_hole WHERE sqhl_schema = quote_ident(v_schemaName) AND sqhl_table = quote_ident(v_tableName);
RETURN;
END;
$_drop_tbl$;
CREATE OR REPLACE FUNCTION emaj._create_seq(v_schemaName TEXT, v_seqName TEXT, v_groupName TEXT)
RETURNS void LANGUAGE plpgsql AS
$_create_seq$
-- The function checks whether the sequence is related to a serial column of an application table.
-- If yes, it verifies that this table also belong to the same group
-- Required inputs: schema name and sequence name
DECLARE