Permalink
Browse files

Change the behaviour of the statistics functions. emaj_log_stat_group…

…() and emaj_detailed_log_stat_group() now return begin and end marks name and timestamp. They also filter tables that belonged to the group during the period framed by the supplied first and last marks (and not only tables belonging to the group at the function execution time). Tables that have been removed during this time frame is reported with a counter representing the row changes for the period when the table belonged to the group. The report is not ordered by schema and table names, not taking into account the priority anymore. Create an additional index on emaj_mark to speedup statistics functions when there are a lot of marks. Delete from the documentation the exemple using the emaj_detailled_log_stat_group() function to get statistics for a single table.
  • Loading branch information...
beaud76 committed Feb 1, 2018
1 parent 20f766a commit b2a3e30b86ce7d8637ef49074ccf29b5f829e13b
Showing with 2,290 additions and 1,634 deletions.
  1. +5 −1 CHANGES.md
  2. +45 −39 docs/en/statFunctions.rst
  3. +45 −39 docs/fr/statFunctions.rst
  4. +281 −0 sql/emaj--2.2.2--next_version.sql
  5. +130 −40 sql/emaj--next_version.sql
  6. +1 −1 test/10/expected/adm1.out
  7. +46 −40 test/10/expected/adm2.out
  8. +22 −18 test/10/expected/alter_logging.out
  9. +1 −1 test/10/expected/check.out
  10. +31 −16 test/10/expected/install.out
  11. +2 −0 test/10/expected/install_upgrade.out
  12. +164 −155 test/10/expected/misc.out
  13. +34 −16 test/10/expected/upgrade_while_logging.out
  14. +10 −13 test/10/expected/viewer.out
  15. +46 −40 test/92/expected/adm2.out
  16. +22 −18 test/92/expected/alter_logging.out
  17. +1 −1 test/92/expected/check.out
  18. +31 −16 test/92/expected/install.out
  19. +2 −0 test/92/expected/install_upgrade.out
  20. +158 −149 test/92/expected/misc.out
  21. +10 −13 test/92/expected/viewer.out
  22. +46 −40 test/93/expected/adm2.out
  23. +22 −18 test/93/expected/alter_logging.out
  24. +1 −1 test/93/expected/check.out
  25. +31 −16 test/93/expected/install.out
  26. +2 −0 test/93/expected/install_upgrade.out
  27. +158 −149 test/93/expected/misc.out
  28. +34 −16 test/93/expected/upgrade_while_logging.out
  29. +10 −13 test/93/expected/viewer.out
  30. +46 −40 test/94/expected/adm2.out
  31. +22 −18 test/94/expected/alter_logging.out
  32. +1 −1 test/94/expected/check.out
  33. +31 −16 test/94/expected/install.out
  34. +2 −0 test/94/expected/install_upgrade.out
  35. +158 −149 test/94/expected/misc.out
  36. +10 −13 test/94/expected/viewer.out
  37. +46 −40 test/95/expected/adm2.out
  38. +22 −18 test/95/expected/alter_logging.out
  39. +1 −1 test/95/expected/check.out
  40. +31 −16 test/95/expected/install.out
  41. +2 −0 test/95/expected/install_upgrade.out
  42. +158 −149 test/95/expected/misc.out
  43. +34 −16 test/95/expected/upgrade_while_logging.out
  44. +10 −13 test/95/expected/viewer.out
  45. +1 −1 test/96/expected/adm1.out
  46. +46 −40 test/96/expected/adm2.out
  47. +22 −18 test/96/expected/alter_logging.out
  48. +1 −1 test/96/expected/check.out
  49. +31 −16 test/96/expected/install.out
  50. +2 −0 test/96/expected/install_upgrade.out
  51. +164 −155 test/96/expected/misc.out
  52. +10 −13 test/96/expected/viewer.out
  53. +13 −7 test/sql/adm2.sql
  54. +5 −4 test/sql/alter_logging.sql
  55. +28 −18 test/sql/misc.sql
  56. +2 −2 test/sql/viewer.sql
View
@@ -3,7 +3,11 @@ E-Maj - Change log
<NEXT_VERSION>
------
###Enhancements:###
-
+ * Change the behaviour of the statistics functions. emaj_log_stat_group()
+ and emaj_detailed_log_stat_group() now return begin and end marks name
+ and timestamp. They also filter tables that belonged to the group during
+ the period framed by the supplied first and last marks (and not only
+ tables belonging to the group at the function execution time).
###Bug fixes:###
View
@@ -21,17 +21,25 @@ Full global statistics about logs content are available with this SQL statement:
The function returns a set of rows, whose type is named *emaj.emaj_log_stat_type*, and contains the following columns:
-+--------------+--------+-------------------------------------------------------+
-| Column | Type | Description |
-+==============+========+=======================================================+
-| stat_group | TEXT | tables group name |
-+--------------+--------+-------------------------------------------------------+
-| stat_schema | TEXT | schema name |
-+--------------+--------+-------------------------------------------------------+
-| stat_table | TEXT | table name |
-+--------------+--------+-------------------------------------------------------+
-| stat_rows | BIGINT | number of updates recorded into the related log table |
-+--------------+--------+-------------------------------------------------------+
++--------------------------+-------------+-------------------------------------------------------+
+| Column | Type | Description |
++==========================+=============+=======================================================+
+| stat_group | TEXT | tables group name |
++--------------------------+-------------+-------------------------------------------------------+
+| stat_schema | TEXT | schema name |
++--------------------------+-------------+-------------------------------------------------------+
+| stat_table | TEXT | table name |
++--------------------------+-------------+-------------------------------------------------------+
+| stat_first_mark | TEXT | mark name of the period start |
++--------------------------+-------------+-------------------------------------------------------+
+| stat_first_mark_datetime | TIMESTAMPTZ | mark timestamp of the period start |
++--------------------------+-------------+-------------------------------------------------------+
+| stat_last_mark | TEXT | mark name of the period end |
++--------------------------+-------------+-------------------------------------------------------+
+| stat_last_mark_datetime | TIMESTAMPTZ | mark timestamp of the period end |
++--------------------------+-------------+-------------------------------------------------------+
+| stat_rows | BIGINT | number of updates recorded into the related log table |
++--------------------------+-------------+-------------------------------------------------------+
A *NULL* value or an empty string ('') supplied as start mark represents the oldest mark.
@@ -41,6 +49,8 @@ The keyword *'EMAJ_LAST_MARK'* can be used as mark name. It then represents the
The function returns one row per table, even if there is no logged update for this table. In this case, stat_rows columns value is 0.
+Most of the time, the *stat_first_mark*, *stat_first_mark_datetime*, *stat_last_mark* and *stat_last_mark_datetime* columns reference the start and end marks of the requested period. But they can contain other values when a table has been added or removed from the tables group during the requested time interval.
+
It is possible to easily execute more precise requests on these statistics. For instance, it is possible to get the number of database updates by application schema, with a statement like:
.. code-block:: sql
@@ -68,21 +78,29 @@ Scanning log tables brings a more detailed information, at a higher response tim
The function returns a set of rows, whose type is named *emaj.emaj_detailed_log_stat_type*, and contains the following columns:
-+--------------+-------------+--------------------------------------------------------------------------------------------------+
-| Column | Type | Description |
-+==============+=============+==================================================================================================+
-| stat_group | TEXT | tables group name |
-+--------------+-------------+--------------------------------------------------------------------------------------------------+
-| stat_schema | TEXT | schema name |
-+--------------+-------------+--------------------------------------------------------------------------------------------------+
-| stat_table | TEXT | table name |
-+--------------+-------------+--------------------------------------------------------------------------------------------------+
-| stat_role | VARCHAR(32) | connection role |
-+--------------+-------------+--------------------------------------------------------------------------------------------------+
-| stat_verb | VARCHAR(6) | type of the SQL verb that has performed the update, with values: *INSERT* / *UPDATE* / *DELETE*) |
-+--------------+-------------+--------------------------------------------------------------------------------------------------+
-| stat_rows | BIGINT | number of updates recorded into the related log table |
-+--------------+-------------+--------------------------------------------------------------------------------------------------+
++--------------------------+-------------+--------------------------------------------------------------------------------------------------+
+| Column | Type | Description |
++==========================+=============+==================================================================================================+
+| stat_group | TEXT | tables group name |
++--------------------------+-------------+--------------------------------------------------------------------------------------------------+
+| stat_schema | TEXT | schema name |
++--------------------------+-------------+--------------------------------------------------------------------------------------------------+
+| stat_table | TEXT | table name |
++--------------------------+-------------+--------------------------------------------------------------------------------------------------+
+| stat_first_mark | TEXT | mark name of the period start |
++--------------------------+-------------+--------------------------------------------------------------------------------------------------+
+| stat_first_mark_datetime | TIMESTAMPTZ | mark timestamp of the period start |
++--------------------------+-------------+--------------------------------------------------------------------------------------------------+
+| stat_last_mark | TEXT | mark name of the period end |
++--------------------------+-------------+--------------------------------------------------------------------------------------------------+
+| stat_last_mark_datetime | TIMESTAMPTZ | mark timestamp of the period end |
++--------------------------+-------------+--------------------------------------------------------------------------------------------------+
+| stat_role | VARCHAR(32) | connection role |
++--------------------------+-------------+--------------------------------------------------------------------------------------------------+
+| stat_verb | VARCHAR(6) | type of the SQL verb that has performed the update, with values: *INSERT* / *UPDATE* / *DELETE*) |
++--------------------------+-------------+--------------------------------------------------------------------------------------------------+
+| stat_rows | BIGINT | number of updates recorded into the related log table |
++--------------------------+-------------+--------------------------------------------------------------------------------------------------+
A *NULL* value or an empty string ('') supplied as start mark represents the oldest mark.
@@ -92,19 +110,7 @@ The keyword *'EMAJ_LAST_MARK'* can be used as mark name. It then represents the
Unlike :ref:`emaj_log_stat_group() <emaj_log_stat_group>`, the *emaj_detailed_log_stat_group()* function doesn't return any rows for tables having no logged updates inside the requested marks range. So *stat_rows* column never contains 0.
-It is possible to easily execute more precise requests on these statistics. For instance, it is possible to get the number of updates for a given table, here *mytbl1*, per SQL verb, using a statement like:
-
-.. code-block:: sql
-
- postgres=# SELECT stat_table, stat_verb, stat_rows
- FROM emaj.emaj_detailed_log_stat_group('myAppl1', NULL, NULL)
- WHERE stat_table='mytbl1';
- stat_table | stat_verb | stat_rows
- ------------+-----------+-----------
- mytbl1 | DELETE | 1
- mytbl1 | INSERT | 6
- mytbl1 | UPDATE | 2
- (3 rows)
+Most of the time, the *stat_first_mark*, *stat_first_mark_datetime*, *stat_last_mark* and *stat_last_mark_datetime* columns reference the start and end marks of the requested period. But they can contain other values when a table has been added or removed from the tables group during the requested time interval.
.. _emaj_estimate_rollback_group:
Oops, something went wrong.

0 comments on commit b2a3e30

Please sign in to comment.