COPY可以与功能一起使用吗？ [英] Can COPY be used with a function?

问题描述

我的任务是分析一个PostgreSQL数据库。第一个要求是查看如何快速添加记录，并消除所有可能的外部瓶颈，以便找到我们的理论极限。

I've been tasked with profiling a postgresql database. The first requirement is to see how fast records can be added, with all possible external bottlenecks removed, in order to find our theoretical limit.

首先，我创建了一个csv文件带有样本数据，并通过COPY功能读取。现在，所有记录都通过函数 update_or_add（）添加。是否可以将COPY与 update_or_add（）一起使用，还是有我没有考虑过的更好的解决方案？

At first I created a csv file with sample data and read it in with the COPY function. Now, all records are added via a function update_or_add(). Is it possible to use COPY along with update_or_add() or is there a better solution that I haven't considered?

推荐答案

您最好的方法应该是概括化 update_or_add以使其适用于集合中的所有行，而不是对该行的每个行调用update_or_add（）。

Rather than "for each row call update_or_add() on the row", your preferred approach should be to generalize "update_or_add" to work on all rows in a set.

COPY 将来自外部源的数据转换为 TEMPORARY 或 UNLOGGED 表。然后编写一个查询，该查询将复制的表中的数据合并到您的主表中，就像 update_or_add（）那样，但同时适用于所有行。如果可以同时修改主表，则可能需要使用 LOCK TABLE main_table IN EXCLUSIVE MODE 锁定主表，以便其他事务只能 SELECT 进行合并。

COPY the data from the external source into a TEMPORARY or UNLOGGED table. Then write a query that merges the data from the copied table into your main table, like update_or_add() does but for all rows at once. If concurrent modification of the main table is possible this may require locking the main table with LOCK TABLE main_table IN EXCLUSIVE MODE so that other transactions can only SELECT from it while the merge is going on.

没有您的架构或不知道什么 update_or_add 很难说更多吗。大概是一个upsert / merge函数，在这种情况下，您可以通过锁定主表并进行整套合并来更有效地完成其工作。

Without your schema or knowing what update_or_add does it's hard to say more. At a guess it's an upsert/merge function, in which case you can do its job massively more efficiently by locking the main table and doing a whole-set merge.

这篇关于COPY可以与功能一起使用吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！