postgresql - 带触发器的 Postgres 分区管理。发行变更表

Question

短篇小说: 我有一个分区的 postgres 数据库，其中有一个表来跟踪分区和触发器。触发器需要更改分区表的约束(它们的 valid_date [daterange] 根据其他分区的 valid_date 更改)并且最好能够删除表，但这会导致错误，因为触发器链正在使用这些表'。

cannot ALTER TABLE "core_geometryrecord_8_2" because it is 
    being used by active queries in this session

完整问题: 我正在对我的模式和触发器架构进行详尽的解释，以备不时之需，或者有人想知道为什么我觉得需要一个“复杂”的系统，尽管这可能没有必要。

我正在重构一个大型几何数据数据库，以使其更容易、更快速地使用。旧系统具有包含相关几何图形的“几何图形表”(即美国的县)。数据实际上应该按“date_valid”和“geometrytable_id”分组。

我们决定使用对 date_valid(日期范围)和 geometrytable_id(外键)进行约束的 postgres 分区。

因为日期很敏感并且需要大量的簿记才能保持正确我第一次尝试使用触发器管理大部分数据库(我已经知道我需要一个分区表的插入触发器所以为什么不呢) .

我设计了一组触发器来管理分区列表，并在您插入或删除几何体时进行所有簿记。这是他们的工作。

• 在插入分区列表表时，创建分区表并应用约束。如果某个日期的分区有效与这个新日期重叠(调用更新触发器)，则将其修剪。如果在这个日期之后有一个分区有效，则将新的分区结束日期修剪为下一个分区的开始日期(保持连续的时间线)。
• 从分区列表中删除时，删除分区表。重新计算已删除分区(如果有)周围的 partition_list 条目(调用更新触发器)的日期范围，以使日期连续。
• 在 Update partition list table 上，DROP OLD DATE CONSTRAINT，将分区表中所有行的 date_valid 更改为分区的新 date_valid 值，然后使用新日期创建 DATE CONSTRAINT(约束是项目具有完全相同的date_valid 作为分区)。
• 在插入几何记录(分区表)时，找到应该使用的分区，或插入分区列表表(插入时触发)。将 NEW 插入正确的分区。
• 从几何记录中删除时，如果分区表现在为空，则从分区列表中删除分区条目(CALLS DELETE TRIGGER)。

如果我在链中的任何触发器中完全触及它，它就会因为我试图以任何方式更改表而生气。如果我直接插入到该表中，分区列表表的触发器可以完美地工作，但是从几何表(调用相同的触发器)中删除会翻转，即使它所做的只是一个选择(查看分区表是否为空)。插入分区几何表也会导致问题，因为我必须删除约束才能更改行的 date_valid。

必须有一种方法可以做到这一点，我就是不明白。 (我还必须使表的删除更加被动“将其标记为由 cron 作业删除”，因为我无法从源 self 要删除的表的触发器调用中删除该表。)

非常欢迎任何建议。我简直不敢相信从来没有人需要做这样的事情，所以我想我只是不知道自己在做什么 :)。

Answer 1

因此，在四处挖掘和试验之后，我弄清楚了我需要什么，并认为我会记录我的发现。

这里是有关基本 postgres 分区如何为任何感兴趣的人工作的文档页面:http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

要知道的关键点是，对于每个分区，您必须设置约束以指定可以在分区中找到的内容(拆分 ID 以便每个表中只有 200,000 条记录只需要每个表一个约束)。这使得当您查询主表时，它会根据所有子表的约束快速检查请求。只有查询落在表约束范围内的表才会被进一步查询。如果你做对了，只有一张表被真正查询到。

至于我学到了什么:

首先，我认为根本不可能从它自己的触发器中删除一个表。我对此的解决方案只是通过一些其他机制将其标记为丢弃(例如稍后丢弃的事物表)。这个问题是它一直存在，直到 Cron 作业运行。由于从其自身的触发器更改表也是不可能的，因此该表仍将是一个分区，只是标记为稍后删除。如果您的分区正确，这可能不会导致任何问题。在我的例子中，可能有两个分区重叠(一个标记为要删除，一个应该存在)。这样做的副作用是，对于指定可能在这些表中的任何一个中的事物的查询，将同时命中两个表。在 Cron 作业完成之前，这对我来说很好，所以我很好，但有些情况可能无法解决此问题。

其次，我意识到正在更改它们未写入的表的插入工作正常，但触发相同触发器链的删除和更新失败，因为 session 正在使用这些表。我认为这是疯狂的事情，但真正的问题是我是根据日期和外键进行分区的，但根据记录的 ID 进行了删除。这导致 postgres 检查所有表的所有约束，这使它无法了解要使用哪个表，因此它只检查了所有表。为了使删除工作正常进行，我所要做的就是指定我分区的信息，这样它就知道要查找哪个表，而不是扫描整组表来查找 id。

TLDR 如果您在任意(一组)列上对 postgres 表进行分区，请确保通过提供您在其上进行分区的列来删除或更新记录，以便 postgres 可以知道哪个表查看，而不必扫描整个集合。