Postgresql-确定从级联删除中删除哪些记录 [英] Postgresql - Determining what records are removed from a cascading delete

问题描述

我有一个继承的相当大的postgreql数据库.我们有一个每月运行的工作，它备份现有数据库并创建一个新数据库，其中包含我们收到的更新的供应商数据.

I have a fairly large postgreql database that I've inherited. We have a job that runs ~monthly that backs up the existing database and creates a new database with updated vendor data that we receive.

当前存在一个小问题.无需讨论表设置的详细信息，数据建模的内容等，我相信可以通过简单的删除查询来解决该问题，因为表已设置为使用级联删除.

Currently there is a small issue with it. Without going into details of the table setup, what the data is modeling, etc, I believe it can be fixed with a simple delete query, as the tables are set-up to use cascading deletes.

但是，从供应商提供的来源生成此数据库大约需要9个小时，因此我总是不愿介绍新的更改.我目前有该数据库的副本，我打算首先在该数据库上运行查询，以使用户可以成功"运行该数据库.但是，通常sql的缺点之一是，进行删除时，输出通常仅是以下行的内容:

However, it takes about 9 hours to generate this database from the vendor's provided source, so I'm always hesitant to introduce new changes. I currently have a copy of this database that I intend to run the query on first, to make user it 'CAN' run successfully. However, one of the downsides of sql in general is that when deletes are made, the output is typically something alone the lines of:

查询成功返回:x受影响的行，y毫秒的执行时间.

在使用级联删除时，postgres中是否可以确定从哪些表中删除了哪些行?我想在我的副本上运行查询，看看被删除的内容是否符合我的期望，至少在被击中的表中是如此.这可能吗?

Is there a way in postgres to determine what rows were removed from which tables when using cascading deletes? I'd like to run the query on my copy and see if what is being dropped is what I expect, at least in what tables are being hit. Is this possible?

推荐答案

postgres中是否有一种方法可以确定使用级联删除时从哪些表中删除了哪些行?

Is there a way in postgres to determine what rows were removed from which tables when using cascading deletes?

不幸的是，没有简单的内置方法，但这是一个好主意.由于级联删除是由触发器在后台实现的，因此您要做的就是修改参照完整性触发器，以便级联删除在删除之前会在行信息中添加 LOG 级别的消息.这将需要更改PostgreSQL源代码并重新编译.

Unfortunately no simple built-in way, but it's a great idea. Since cascading deletes are implemented under the hood by triggers, all you'd have to do is modify the referential integrity triggers so that cascade deletes raise a LOG level message with with the row information before deleting it. This would require changes to the PostgreSQL source code and a recompile.

或者，您可以通过在级联约束所引用的每个表上创建新的 AFTER DELETE ... FOR EACH ROW 触发器来记录 all 删除操作.它可以很简单:

Alternately, you can log all deletes by creating a new AFTER DELETE ... FOR EACH ROW trigger on each table referred to by a cascade constraint. It can be as simple as:

CREATE OR REPLACE FUNCTION log_delete() RETURNS trigger AS $$
BEGIN
    RAISE LOG 'Deleting row % (statement is %)', OLD, current_query();
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

这样，尽管您确实需要在要进行级联删除的每个表上安装触发器，但无需修改数据库.

That way you don't need to modify the DB, though you do need to install the trigger on every table that's subject to cascade deletes.

对于奖励积分，您甚至可以通过查询 information_schema 的外键关系来自动创建，但这可能比其价值更麻烦.

For bonus points you can even automate its creation by querying the information_schema for foreign key relationships, but it's probably more hassle than its worth.

这篇关于Postgresql-确定从级联删除中删除哪些记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！