用于连接父子审计表的 Postgres SQL

Question

我们使用 "1 审核表对于每个受监控的表” 设计；但是，在我们的示例中，emp(PARENT) 表有一个子表 emp_address，也需要对其进行监控，因此我们有 emp_audit 和 emp_addr_audit 表。

postgres 审计 SQL：如何连接 PARENT 和 CHILD 表以进行报告。

/* Employee table */    
create table emp (
 emp_id integer primary key,
 empnum  integer,
 empname varchar(50),
);

/* Address table */    
create table emp_addr (
 addr_id integer primary key,
 emp_id integer, -- references table emp
 line1 varchar(30),
);

/* Audit table for emp table */    
create table emp_audit (
 operation   character(1),
 emp_id integer,
 empnum  integer,
 empname varchar(50),
 updatetime timestamp,
 txid bigint
);

/* Audit table for emp_addr table */    
create table emp_addr_audit (
 operation   character(1),
 addr_id integer,
 emp_id integer,
 line1 varchar(30),
 updatetime timestamp,
 txid bigint
);

我们使用 hibernate(java) 来实现持久性，并且 hibernate 仅更新那些在更新操作中修改了列的表。鉴于此，我可能在 emp_addr_audit 表中拥有多行（例如 5 行），而 emp_audit 表中的 1 行。反之亦然。

每笔交易（修改）报告需要 1 行。 该报告将包含以下列

empname、line1、操作（插入/删除/更新）、更新时间

让我们考虑 2 个场景来了解所需内容：

1. 在初始事务中，仅创建 emp 属性。然后在单独的事务中，创建 emp_addr 中的相应行。因此，现在，emp_audit 表中有 1 行，emp_addr_audit 表中有 1 行。该报告将有 2 行（每笔交易各一行）。
2. emp 和 emp_addr 属性都是在单个事务中创建的。这将确保 emp_audit 中有 1 行，emp_addr_audit 中有 1 行。现在，报告将只有 1 行（因为两个表行都是在单个事务中创建的）。

场景：
事务#1：我在 emp 和 emp_addr 中插入一行。这会在 emp_audit 和 emp_addr_audit 中各生成一行。（插入）
事务#2：我更新了上面的 emp' 属性。这会在 emp_audit 中产生 UPDATE 行。
事务#3：我更新了上面的 emp_addr 的属性。这会导致 emp_addr_audit 中出现 UPDATE 行。

我尝试了以下 SQL #1，它返回了 3 行（如预期）；

SQL #1

SELECT emp.*, addr.*
 FROM  emp_audit emp 
 FULL OUTER JOIN emp_addr_audit addr USING(emp_id, txid);

但是，当我向 SQL 添加 where 子句时，它仅返回 2 行。丢失的行是事务 #3 的结果，其中仅 emp_addr 表行被更新，而 emp 表行未受影响。
SQL #2

SELECT emp.*, addr.*
 FROM  emp_audit emp 
        FULL OUTER JOIN emp_addr_audit addr USING(emp_id, txid);
WHERE  emp.empnum = 20;

什么 SQL仍然能够为我的 3 个事务获取 3 行，以便我仍然可以根据 empnum 进行过滤？

Answer 1

FULL JOIN 将从两个表创建一个表，并用 NULL 值填充空（不匹配）行。当您仅选择 emp.empnum = 20 的行时，它当然不会返回 emp.empnum 中具有 NULL 的最后一行。

也许您可以仅在 emp_id 上加入，然后执行一些 GROUP BY txid。但这不会那么容易。您可能必须始终对两个表进行更新。或者使用一些更高级的逻辑（例如：选择一个 emp_id 的所有事务，其中一侧为空，直到下一个完整行，并用最后一个完整行填充缺失的部分）。