auditing table tagged posts

November 1, 2017 Posted in Blogs, Database

PostgreSQL, as one of the best open source object-relational DBMS, is quite popular today. However, it doesn’t provide built-in auditing function, which is required by most production systems.

Let’s see how we generate generic auditing function for a table in PostgreSQL schema. Then we should be able to generate auditing function for the entire schema easily.

Let’s assume we have an input parameter table name , and we have a pre-built auditing table, which contains all fields from source table, in line 5 below, plus all other auditing fields:

 CREATE TABLE schema_name.sample_audit
 (
 	audit_id bigint NOT NULL DEFAULT nextval('schema_name.sample_audit_id_seq'::regclass),
 	id bigint NOT NULL,
 	source table columns ........

February 28, 2017 Posted in Blogs, LiveSync Automation

We talked how to track database changes with one auditing table last month. If we want to replicate these changes into another database, then we might use multi-threading to gain the high performance in data ingestion. However, based on our previous design, we track changes to column level, which generates a serious concurrency control issue. Let’s think about, we have a data table, with 10 columns updates at once, then all these changes are captured and inserted into my_audit_table. If at the same time, another table gets updated, then there might have some cross inserting in my_audit_table, which makes the multi-threading read even harder.

Is there a better approach that we should go? In this case, it’s worth to re-design the audit_table. Let’s see how it looks like (in Oracle PL/SQL):

c...