dalibo / emaj Goto Github PK

View Code? Open in Web Editor NEW

73.0 73.0 9.0 31.38 MB

E-Maj is a PostgreSQL extension that tracks updates on tables sets with rollback capabilities

License: Other

PHP 0.22% PLpgSQL 98.51% Perl 0.54% Shell 0.73%

emaj's People

Contributors

Stargazers

Watchers

Forkers

haubourg tilkow ng-pe datadevopscloud cybernetics nk23x mayeulk joao-pinheiro

emaj's Issues

Event triggers block non emaj user

With postgres version 9.3+, some event triggers that protect the emaj environment also block non emaj user for any table, sequence or schema drop (or a table alter resulting in a table rewrite with PG 9.5+).
To solve this, the functions associated to this faulting triggers must be declared SECURITY DEFINER, so that the roles that have been granted neither emaj_adm nor emaj_viewer role can access the emaj schema and internal tables. As a workaround, these roles can be granted emaj_viewer.

The log records exported by emaj_snap_log_group() include records prior the requested first mark

When calling emaj_snap_log_group('my_group', 'start_mark', 'stop_mark', , <copy_options>), changes on tables recorded before 'start_mark' are also exported.

Rewrite clients in python or perl

Both old emajParallelRollback and emajRollbackMonitor clients are written in php.
Generally, php is not installed on server by default, while perl and python are already here on most linux platforms hosting postgres instances. As both langages have the capability to access a postgres database with several connection in parallel, it may be a better choice.

Improve the pg_dump regression test

The test that uses pg_dump to dump an E-Maj environment and restore it in another cluster (in another postgres version) doesn't produce stable enough results. As a result, it is useless. Rethink it, starting with a proper E-Maj install + some tables group manipulation, then a save, then a restore and some final groups use.

Unsufficient permission when altering a logging group leading to the removal of a table from a group

When calling emaj_alter_group() for a tables group in logging state that generates the removal of an application table from the group, if the role that executes the function is not a superuser or the owner of the table, the execution fails.

Allow the alter group operation to process several groups at once

With this enhancement, it will be more efficient to move tables and/or sequences from one group to another. This will also help for some future features...

Pb with emaj_hist after restore

A database restored from a pgdump file may result in an almost empty emaj_hist table. In this case, the whole history saved would be lost. The problem exists since version 2.0.0.

add a table/sequence to a group in logging state

Add a table or a sequence into a tables group that is not stopped/restarted, so that it would be possible then to rollback the group to a mark set before the table/sequence addition. In this case, the table or sequence would be reset to their state at the time it has been added to the group.
To do this, the user would just insert a row into the emaj_group_def table and call the emaj_alter_group() function.

Use of remove_table function not delete on emaj_group_def

Hello I have a little issue with version 3.2.0

First I upgrade from 3.0.0 to 3.2.0 how ?
Just on download the new version locally and
I performed these commands

=> cp emaj.control /usr/pgsql-10/share/extension/.
=> cp sql/emaj--* /usr/pgsql-10/share/extension/.
with postgreql user on database
=> ALTER EXTENSION emaj UPDATE;

But now I want to use these functions
SELECT emaj.emaj_remove_table('newton', 'dhi_test_1');
SELECT emaj.emaj_assign_table('newton', 'dhi_test_1', 'newton_datalake');
on a existing group
but I see nothing on table emaj.emaj_group_def (seems not be updated after use of these functions)

Create a Makefile, transform the README file into .md format and improve the pgxn description file

Issue during auto drop unused log tables

this is the output when perform delete before mark statement

newton@newton_pic(retail01)=> SELECT emaj.emaj_delete_before_mark_group('newton_datalake', 'Mark-3');
ERROR: must be owner of relation newton_dhi_test_1_log_1
CONTEXT: SQL statement "DROP TABLE IF EXISTS emaj.newton_dhi_test_1_log_1 CASCADE"

start_stop step is randomly failed in regression tests

Hi Philippe,

The line below appears randomly when running start_stop step in the regression tests, which creates (or not) a difference when comparing ''expected/start_stop.out'' and ''results/start_stop.out''


!  hist_id |   hist_function    |       hist_event        |                  hist_object                  |         regexp_replace          | hist_u
ser 
! ---------+--------------------+-------------------------+-----------------------------------------------+---------------------------------+-------
----
!     1008 | DISABLE_PROTECTION | EVENT TRIGGERS DISABLED |                                               |                           | postgr

Best regards

Pb with emaj_schema content avec restore

If an E-Maj installation uses secondary E-Maj schema, a database restored from a pgdump file results in all secondary schemas lost in the emaj_schema table. As a result, functions like emaj_drop_group() or emaj_alter_group() would then abort. The problem exists since version 2.2.0.

COLS_ORDER option of emaj_gen_sql_dump_changes_group() doesn't work

In the recently added emaj_gen_sql_dump_changes_group() function, the COLS_ORDER=LOG_TABLE option returns an error.
The suggested TABLE_LOG value is accepted instead but has no impact when the consolidation level is set to PARTIAL or FULL.

The issue is reported by the French DGFiP state organization.

Add a FAQ chapter in the documentation

All is in the title ;-)

E-Maj rollback duration estimate aborts with large databases

Reported by CNAF on a 3.4.0 version.
The following error has been encountered when trying to execute an E-Maj rollback:

ERROR: interval out of range
CONTEXT: SQL statement "SELECT sum(rlbt_duration) * r_tbl.rlbp_estimated_quantity / sum(rlbt_quantity) FROM emaj.emaj_rlbk_stat
WHERE rlbt_step = 'RLBK_TABLE' AND rlbt_quantity > 0
AND rlbt_schema = r_tbl.rlbp_schema AND rlbt_table = r_tbl.rlbp_table
AND rlbt_quantity / r_tbl.rlbp_estimated_quantity < 10 AND r_tbl.rlbp_estimated_quantity / rlbt_quantity < 10"
PL/pgSQL function emaj._rlbk_planning(integer) line 299 at SQL statement
STATEMENT: SELECT emaj._rlbk_planning(43)

Probleme de migration de Postgresql 9.6 en 10 avec changement de version de emaj

Bonjour

J'ai fait une petite base de test avant les vacances sur un serveur postgresql9.6 (version emaj ?). De retour de vacances, j'ai migré cela sur un serveur postgresql 10 avec emaj 2.3.1. Je travaille sur debian 9.5. J'ai fait la migration à l'aide de la commande pg_dump -U postgres -f test_emaj.dump test_emaj et j'ai régénéré avec psql -f . Problème je ne retrouve pas mes petits (groupe, marque, ...) et donc je ne peux pas jouer pour retrouver des anciennes valeurs.. (NDR ; il y a des erreurs à la régénération)
Comment aurais je du faire pour conserver le tout

A+
MCQ

A sql script generated by emaj_gen_sql_group() fails on fkey check

The sql script generated by the emaj_gen_sql_group() functions family may fail at execution time if some recorded transactions used foreign keys temporarily set as DEFERRED. This may be the case if the script replays an E-Maj logged rollback operation that handled "deferrable initialy immediate" fkeys. As a workaround, you may add SET CONSTRAINTS ... DEFERRED statements inside the generated transaction.

files to copy - Windows installation procedures (documentation)

On Linux, one command for the installation is
cp sql/emaj* $(pg_config --sharedir)/extension/.
or on some systems (Debian 9 - stretch with PostgreSQL 10):
cp sql/emaj* /usr/share/postgresql/10/extension/.

However, those commands are NOT equivalent to what is said on the Windows documentation: "Copy the files emaj.control and sql/emaj–* into the share\extension folder of the PostgreSQL installation folder "
In effect, sql/emaj–* (with a dash) will not match files like sql/emaj_* (with underscore) but "cp sql/emaj* " will match those files (for instance: emaj_demo.sql )

I guess the Windows documentation in https://github.com/beaud76/emaj/blob/master/docs/en/install.rst should read:

"Copy the files emaj.control and sql/emaj* into the share\extension folder"
(without the dash)

emaj_dump_changes_group() with consolidation fails when an application column name contains a special character

When column names requires double quotes in SQL statements, calls to emaj_dump_changes_group() fail when the consolidation level is set to 'PARTIAL' or 'FULL'.

Add a "quick start" chapter to the documentation

Benchmarking the performance with large tables groups

It would be useful to have a benchmark that would appreciate the performance of main E-Maj functions on large to very large tables groups (1000 tables or more).
It could be a "simple" psql script with \timing set to on.

risk of corruption after an emaj_start_group() called inside a rolled back transaction

I have just found a serious bug that may corrupt the database under some rare circumstances. If a single transaction contains calls to emaj_stop_group(), and then emaj_start_group() and if this transaction aborts, any further attempt to rollback the group to a mark set before the aborted transaction would probably miss some tables, leading to corrupted table contents. Statistics and sql script generation on time ranges including the faulting transaction would also miss some tables.

Fix some vars in regression tests

PostgreSQL supported versions by emaj
Set LANG variable for comparison

Output of emaj_dump_changes_group() with recorded TRUNCATE

When SQL TRUNCATE statements are recorded in log tables, the view of data changes erroneously returned a line with NULL values. This only occurs in CONSOLIDATION=NONE mode.

Logical Replication stream doesn't fire the E-Maj log triggers

If E-Maj is used on tables fed by a logical replication stream, changes from this stream are not logged by E-Maj triggers. Only local changes from local connections are recorded.
This is due to the fact that log triggers are enabled as regular triggers instead of ALWAYS triggers.

destination folder - Linux installation procedures (documentation)

cp emaj.control $(pg_config --sharedir)/extension/.
The above does not always work, it might produce this message:

You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.

This is the result of using pg_config.
There are several issues with the (nice) following command: $(pg_config --sharedir)

There are sometimes several versions of pg_config (the one used might not be the one the user wants -- in case of several versions of PG installed in parallels).
It answers an error message when postgresql-server-dev-X.Y is not installed (but the extension works fine without this package).

I suggest a few words of text instead. For instance:

(proposed patch)

As a super-user, copy the files to the extension folder of the postgresql version you want to use. In some cases, pg_config --sharedir will tell you the folder name. If this is appropriate, you can use the following commands (as a super-user, or pre-pended with sudo):

cp emaj.control $(pg_config --sharedir)/extension/.
cp sql/emaj* $(pg_config --sharedir)/extension/.

If this is not appropriate, you have to specify explicitly the destination:

For instance, on Debian 9 (stretch) with PostgreSQL 10:

cp emaj.control /usr/share/postgresql/10/extension/.
cp sql/emaj* /usr/share/postgresql/10/extension/.

ALTER TABLE ADD COLUMN fails while the related table has been removed from its tables group

A user reported this issue:

a table belongs to a tables group,
in order to change its data structure, the table is properly removed from its tables group,
but the ALTER TABLE ADD COLUMN statement with an error message
"ERROR: E-Maj event trigger: Attempting to change the application table "xxx.yyy" structure. But the table belongs to the active tables group "zzz".
CONTEXT: PL/pgSQL function _event_trigger_table_rewrite_fnct() line 21 at RAISE"

Emaj_web fails: fields missing in Emaj tables and functions

Maybe I failed to get the install working with two compatible versions of emaj_web and emaj (=> clarify documentation).
Or, there might be some mismatch between emaj structure and emaj_web requirements (=> patch this).

I seems to me that, looking at the code, some of these fields might exists in some versions. Because I'm unsure, I group these issues into one github issue.

Seen on PostgreSQL version: 15.3. PostgreSQL and LAMP in two Docker containers. Scripted install.
E-Maj button in Emaj_web says: E-Maj environment consistency: Dignostics: No error detected

On fresh install (downloaded today) of emaj and emaj_web, I have several issues with emaj_web.
emaj.emaj_param says: emaj_version is '' (without the quotes). param_value_numeric, param_value_boolean, param_value_interval are null.

Trying to upgrade emaj extension to devel fails:
ALTER EXTENSION emaj UPDATE;
NOTICE: version "devel" of extension "emaj" is already installed

Below are the issues, separated by lines.

Issue 1

Roolback (with comment) and a few other things do not work with emaj_web

In emaj_web, making a logged rollback with a comment raises a "function does not exist" error:
SQL error:
ERROR: function emaj.emaj_logged_rollback_group(unknown, unknown, boolean, unknown) does not exist
LINE 1: SELECT * FROM emaj.emaj_logged_rollback_group('group_exo','R...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
In statement:
SELECT * FROM emaj.emaj_logged_rollback_group('group_exo','RLBK_RLBK_mark3_10.25.40.2022_DONE_10.28.54.4201_START',true,'comment logged rollback')

-- Same when launching this SQL command in pgadmin:
SELECT * FROM emaj.emaj_rollback_group('group_exo', 'EMAJ_LAST_MARK', false, 'test rollback');
ERROR: function emaj.emaj_rollback_group(unknown, unknown, boolean, unknown) does not exist
LINE 1: SELECT * FROM emaj.emaj_rollback_group('group_exo', 'EMAJ_LA...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-- Hint is not useful here. the comment input parameter is not implemented in the version I have for emaj.emaj_rollback_group
-- this works

but this works:

SELECT * FROM emaj.emaj_rollback_group('group_exo', 'EMAJ_LAST_MARK', false);
-- rlbk_message: 0 / 4 tables effectively processed.

I think, same issue with logged rollback (if comments are wanted)

Issue 2

Clicking the E-maj Rollbacks red icon on emaj_web

SQL error:
ERROR:  column "rlbk_start_datetime" does not exist
LINE 2:       to_char(rlbk_start_datetime,'YYYY/MM/DD HH24:MI:SS.US ...
                      ^
In statement:
SELECT rlbk_id, array_to_string(rlbk_groups,', ') as rlbk_groups_list, rlbk_status,
						to_char(rlbk_start_datetime,'YYYY/MM/DD HH24:MI:SS.US OF') AS rlbk_start_datetime,
						to_char(rlbk_end_datetime,'YYYY/MM/DD HH24:MI:SS.US OF') AS rlbk_end_datetime,
						to_char(rlbk_end_datetime - rlbk_start_datetime, 'DD HH24:MI:SS.US') as rlbk_duration,
						rlbk_mark, rlbk_is_logged, rlbk_nb_session, rlbk_comment
					FROM emaj.emaj_rlbk
					WHERE rlbk_status IN ('COMPLETED','COMMITTED','ABORTED')
					ORDER BY rlbk_id DESC
					LIMIT 1000

When trying to perform a rollback with emaj_web:

E-Maj: Rollback a tables group
Rollback execution report for the tables groups "group_exo" to the mark "del c"

SQL error:
ERROR:  function emaj.emaj_logged_rollback_group(unknown, unknown, boolean, unknown) does not exist
LINE 1: SELECT * FROM emaj.emaj_logged_rollback_group('group_exo','d...
                     ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
In statement:
SELECT * FROM emaj.emaj_logged_rollback_group('group_exo','del c',true,'undo del c')

Issue 3

On emaj_web, clicking Rollbacks E-Maj (red arrow) on the left also gives:

Erreur SQL :

ERROR:  column "rlbk_comment" does not exist
-- LINE 5:       rlbk_mark, rlbk_is_logged, rlbk_nb_session, rlbk_comme...
                                                          ^

-- Dans l'instruction :

SELECT rlbk_id, array_to_string(rlbk_groups,', ') as rlbk_groups_list, rlbk_status,
						to_char(rlbk_start_datetime,'YYYY/MM/DD HH24:MI:SS.US OF') AS rlbk_start_datetime,
						to_char(rlbk_end_datetime,'YYYY/MM/DD HH24:MI:SS.US OF') AS rlbk_end_datetime,
						to_char(rlbk_end_datetime - rlbk_start_datetime, 'DD HH24:MI:SS.US') as rlbk_duration,
						rlbk_mark, rlbk_is_logged, rlbk_nb_session, rlbk_comment
					FROM emaj.emaj_rlbk
					WHERE rlbk_status IN ('COMPLETED','COMMITTED','ABORTED')
					ORDER BY rlbk_id DESC
					LIMIT 1000

-- I fixed part of the above with this:


ALTER TABLE emaj.emaj_rlbk
ADD COLUMN IF NOT EXISTS rlbk_start_datetime timestamp with time zone,
ADD COLUMN IF NOT EXISTS rlbk_comment varchar;

Adding COLUMN rlbk_start_datetime helps to display the "Completed E-Maj rollbacks" table
-- I'll be happy to work on a patch here, if it helps.

Issue 4

-- But even after this hot-fix, Emaj_web's "Rollback start" is still empty.
-- and "Consolidable E-Maj logged rollbacks" table fails (the field is needed in the function too) with:

SQL error:

ERROR:  column "rlbk_comment" does not exist
LINE 7:      rlbk_completion_pct, rlbk_comment FROM emaj.emaj_rollba...
                                  ^

In statement:

SELECT rlbk_id, array_to_string(rlbk_groups,', ') AS rlbk_groups_list, rlbk_mark,
					to_char(rlbk_mark_datetime,'YYYY/MM/DD HH24:MI:SS.US OF') AS rlbk_mark_datetime,
					rlbk_is_logged,	rlbk_nb_session, rlbk_nb_table, rlbk_nb_sequence, rlbk_eff_nb_table, rlbk_status,
					to_char(rlbk_start_datetime,'YYYY/MM/DD HH24:MI:SS.US OF') AS rlbk_start_datetime,
					to_char(rlbk_elapse,'DD HH24:MI:SS.US') AS rlbk_current_elapse,
					to_char(rlbk_remaining, 'DD HH24:MI:SS.US') AS rlbk_remaining,
					rlbk_completion_pct, rlbk_comment FROM emaj.emaj_rollback_activity()
				ORDER BY rlbk_id DESC

Thank you.

problem with non-printable characters in sql script generation functions

When a text column contains non printable characters like \n, the emaj_gen_sql_group() function generates a script that do not work properly
(issue reported by Djamel H.)

Error message when restoring a db saved by pg_dump

When saving a database with E-Maj by pg_dump and restoring it, one gets an error saying an event trigger and its function already exist. This is "normal" as those objects created by the extension, are removed from it, and thus saved by pg_dump.
At least, this should be documented.

Doubt on a path in create_clusert.sh script

Hello Philippe,
I have the impression that 'postgresql' is too much after the 'share' directory in the path.

https://github.com/beaud76/emaj/blob/44fa362a0b70d612a9671064229d1b5b9e935822/tools/create_cluster.sh#L73

E-Maj Rollback remaining in LOCKING state after a server reboot

This issue is reported by CNAF.
For any reason, an E-Maj rollback was blocked in the very early stage of lock acquisition on all tables of the tables group. After a while, the server hosting the database has been rebooted.
After the reboot, the E-Maj rollback is always visible as being in LOCKING state.
Normally, the _cleanup_rollback_state() function is supposed to verify the real state of pending rollbacks and set them either to COMPLETED or ABORTED state. This function is called either automatically when a new mark is set or manually using the emaj_cleanup_rollback_state() function. But in this case, the function does not process this pending E-Maj rollback.
This is not a major issue as E-Maj continues to work well. But leaving such a rollback in this state is not very clean.

Inherited FK constraint prevents rollback

Maybe this should be added into the "usage limit" section of the documentation.

postgres@emaj=# create table foo(id int generated always as identity, p int primary key);
CREATE TABLE
postgres@emaj=# create table bar(id int generated always as identity,
foo_id int references foo(p),
insert_time timestamptz default now(),
primary key(id, insert_time)
) partition by range(insert_time);
CREATE TABLE

postgres@emaj=# create table bar_2021 partition of bar for values from ('2021-01-01') to ('2021-12-31');
CREATE TABLE

postgres@emaj=# insert into foo(p) values(5);
INSERT 0 1
postgres@emaj=# insert into bar(foo_id) values(5);
INSERT 0 1

postgres@emaj=# SELECT emaj.emaj_create_group('toto', true);
 emaj_create_group 
-------------------
                 1
(1 row)

postgres@emaj=# SELECT emaj.emaj_assign_table('public', 'bar_2021', 'toto');
NOTICE:  table "bar_2021_log" does not exist, skipping
NOTICE:  trigger "emaj_log_trg" for relation "public.bar_2021" does not exist, skipping
NOTICE:  trigger "emaj_trunc_trg" for relation "public.bar_2021" does not exist, skipping
 emaj_assign_table 
-------------------
                 1
(1 row)

postgres@emaj=# select * from bar;
 id | foo_id |          insert_time          
----+--------+-------------------------------
  1 |      5   |  2021-12-23 08:06:49.041451+01
(1 row)

postgres@emaj=# SELECT emaj.emaj_start_group('toto', 'M1');
WARNING:  _check_fk_groups: The foreign key "bar_foo_id_fkey" on the table "public.bar_2021" references the table "public.foo" that is outside the groups (toto).
 emaj_start_group 
------------------
                1
postgres@emaj=# insert into foo(p) values(7);
INSERT 0 1
postgres@emaj=# insert into bar(foo_id) values(7);
INSERT 0 1
postgres@emaj=# select * from bar;
 id | foo_id |          insert_time          
----+--------+-------------------------------
  1 |      5   |   2021-12-23 08:06:49.041451+01
  2 |      7   |   2021-12-23 08:10:22.538028+01
(2 rows)

postgres@emaj=# SELECT emaj.emaj_rollback_group('toto','M1');
ERROR:  cannot drop inherited constraint "bar_foo_id_fkey" of relation "bar_2021"
CONTEXT:  SQL statement "ALTER TABLE public.bar_2021 DROP CONSTRAINT bar_foo_id_fkey"
PL/pgSQL function emaj._handle_trigger_fk_tbl(text,text,text,text) line 48 at EXECUTE
SQL statement "SELECT emaj._handle_trigger_fk_tbl('DROP_FK', v_fullTableName, r_step.rlbp_object)"
PL/pgSQL function emaj._rlbk_session_exec(integer,integer) line 67 at PERFORM
SQL statement "SELECT emaj._rlbk_session_exec(v_rlbkId, 1)"
PL/pgSQL function emaj._rlbk_groups(text[],text,boolean,boolean,boolean) line 32 at PERFORM
SQL statement "SELECT *
        FROM emaj._rlbk_groups(array[p_groupName], p_mark, FALSE, FALSE, coalesce(p_isAlterGroupAllowed, FALSE))"
PL/pgSQL function emaj.emaj_rollback_group(text,text,boolean) line 7 at RETURN QUERY

Error 'relation "emaj_rlbk_old" already exists' at emaj extension update time

When updating the emaj extension, an error may be raised with the message 'relation "emaj_rlbk_old" already exists'. This happens when the upgrade chains several intermediate versions and several elementary upgrade scripts create temporary tables with the same name.

Question: Is it possible to store additional info when logging

In particular, I'm using https://www.graphile.org/postgraphile/, which passes user info via settings. so to get current user i can call something like:

current_setting('jwt.claims.contact_id')

would i be able to modify the logging tables/triggers to collect this info as well?

Drop the mark_id from the internal emaj_mark table

The mark_id column of the emaj_mark table was primarily created to ensure a safe way to order marks in time, the table's primary key being the mark name and its related group.
Later, a mark_time_id has been added to the table, pointing as a foreign key on to the emaj_time_stamp table.
So consider changing the code to use the mark_time_id for sorts everywhere in E-Maj functions and then drop the mark_id column if it becomes effectively not used.

Risk of undetected integrity violation on E-Maj rollback.

An issue has been discovered. It happens when 1) emaj is installed as an extension (which is the standard installation mode), 2) two tables belonging to the same tables group are linked by a Foreign Key, 3) one of these tables has been assigned after the other while the tables group was in logging state, 4) an E-Maj rollback is executed targeting a mark set before the second table assignment, the third parameter of the emaj_rollback_group() function call being set to TRUE to allow the rollback to cross the table assignment point in time. In such a case, both tables are not set back to the same point in time. But due to the way the foreign key is treated by the rollback operation in this case, an integrity violation may happen and not be detected.

This affects E-Maj versions between 4.0.0 and 4.1.0 and concerns logged and unlogged rollbacks.

Until this issue is fixed, do not allow E-Maj rollbacks to cross tables group structure changes (by keeping the third parameter of the function call to FALSE). Alternately, execute inside a single transaction the rollback operation followed by the FK drop and recreation.

Thanks!

Regards, Devrim