dalibo / emaj Goto Github PK
View Code? Open in Web Editor NEWE-Maj is a PostgreSQL extension that tracks updates on tables sets with rollback capabilities
License: Other
E-Maj is a PostgreSQL extension that tracks updates on tables sets with rollback capabilities
License: Other
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'.
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)
I suggest a few words of text instead. For instance:
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/.
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.
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.
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.
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.
When calling emaj_snap_log_group('my_group', 'start_mark', 'stop_mark', , <copy_options>), changes on tables recorded before 'start_mark' are also exported.
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
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.
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)
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.
All is in the title ;-)
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.
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...
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)
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.
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)
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')
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.
-- 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.
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.
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.)
Hello Philippe,
I have the impression that 'postgresql' is too much after the 'share' directory in the path.
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.
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.
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.
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.
Hi,
I see that v15 support is committed. Can you please release a new version which includes that patch? v15 is due in 2 weeks.
Thanks!
Regards, Devrim
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.
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.
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.
A user reported this issue:
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.
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
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
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"
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)
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.
When an error occurs during the execution of the emajParallelRollback.php client, like when a supplied group name or mark name is incorrect, the program execution stops but no specific return code is set.
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.
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?
The E-maj regress.sh tool needs the Postgres regress binary that has to be created from the Postgres's sources.
The E-maj regress.sh tool could perform the test by itself.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.