Postgres LISTEN / NOTIFY-低延迟，实时吗？ [英] Postgres LISTEN/NOTIFY - low latency, realtime?

问题描述

我正计划使用postgres LISTEN / NOTIFY方法获取表中记录的插入时间（实际事务提交时间）。为此，我计划执行以下操作。我在插入时间发出如下所示的通知。

I am planning to use postgres LISTEN/NOTIFY aproach to get insert time(actual transaction commit time) of records in a table. To achieve this, I plan to do the following. I issue a notification during insert time as shown below.

BEGIN;
  INSERT INTO table_name(id, ...) values (id,....);
  select pg_notify('test_channel', 'id - ' || id || ' trans start time - ' || now() || ' notify start time - ' || clock_timestamp()); 
END;

然后我打算使用 https://pythonhosted.org/psycopg2/advanced.html#asynchronous-notifications 接收这些通知。

And then I plan to use https://pythonhosted.org/psycopg2/advanced.html#asynchronous-notifications to receive those notifications.

我想找出的是事务提交发生的确切时间（记录可以读取）到微秒的确切时间

What I would like to find out is the exact time the transaction commit happens(the record is available to read) down to micro secods

我知道NOTIFY（pg_notify）实际上在事务提交后立即发送通知，但我无法弄清楚如何找出发生该事件的确切时间。我在NOTIFY中拥有的时钟时间戳记值不是实际事务提交时间。

I understand that NOTIFY(pg_notify) actually sends notification right after the commit of the transaction but I couldnt figure out how to find out the exact time when it happens. The clock timestamp value I have in NOTIFY, is not the acutal transaction commit time.

我想我听通知的时间将接近事务提交时间，但是我不确定它有多接近。首先，我的代码在侦听之间有一段时间（无论它有多小），其次，我不确定NOTIFY / LISTEN通信本身之间是否存在任何延迟。

I guess the time I listen to notification will be close to transaction commit time but I am not sure how close it is. First, there is some time between polls in my code while listening(however small it is) and second, I am not sure if there is any lag between NOTIFY/LISTEN communication itself.

有什么想法吗？

更新（问题的完整描述）：我们有读者使用检查点时间分批选择行，其中每个批处理都获得前一个批处理中最后一个时间戳之后的行，而我们缺少行。 （原因：时间戳记值基于INSERT发生的时间（00.00.00）。在负载较重的情况下，如果事务处理时间较长，则在10秒钟后插入事务（例如0.00.10），读者将错过该行（row1），如果它在那10秒钟内读取并发现一行其插入时间晚于row1的行（00.00.05），则问题的完整描述与本博客中所写的相似。 = http://blog.thefourthparty.com/stopping-time-in-postgresql/ rel = nofollow> http://blog.thefourthparty.com/stopping-time-in-postgresql/ ）

UPDATE(Complete description of problem): We have a reader selecting rows in batches using a "checkpoint" time, where each batch gets the rows after the last timestamp in the previous batch, and we are missing rows. (Reason: The timestamp value is based on the time INSERT happens(00.00.00). At heavy loads, if the transaction takes longer time, it gets inserted let say 10 sec later(00.00.10), the reader will miss this row(row1) if it reads during that 10 seconds and finds a row which had its INSERT time at a later time(00.00.05) than row1. The complete description of the problem is similar to the one written in this blog. http://blog.thefourthparty.com/stopping-time-in-postgresql/)

推荐答案

我想找出的是事务提交的确切时间（记录可以阅读）到微秒级

What I would like to find out is the exact time the transaction commit happens(the record is available to read) down to micro secods

方便地，PostgreSQL 9.5刚刚以支持提交时间戳的形式添加了它。请参见提交时间戳。请注意，您必须具有 track_commit_timestamp 启用了此功能，并且有关提交时间戳的信息不会永远保留，因此相当老的行只会得到空结果。

Handily, PostgreSQL 9.5 just added that, in the form of support for commit timestamps. See commit timestamps. Note that you must have track_commit_timestamp enabled to use this, and that information about commit timestamps isn't kept forever, so fairly old rows will just get a null result.

您可以在交易过程中的任何时候使用 txid_current（）获取交易ID。例如，也许使用 insert ...返回... 。然后，您可以在提交后的后续查询中查找提交时间戳。

You can get the transaction ID with txid_current() at any point during the transaction. Perhaps using insert ... returning ... for example. Then you can look up the commit timestamp in a subsequent query, after commit.

对于较早的版本，您应该只包括 clock_timestamp 在您的插入...返回... 子句中。是插入记录的时间，而不是提交时间，但这实际上是可以获取的最接近的时间。

For older versions, you should just include clock_timestamp in your insert ... returning ... clause. It will be the time the record was inserted, not the commit time, but that's really the closest it is possible to get.

我听通知的时间将接近事务提交时间，但我不确定它的接近时间。

I guess the time I listen to notification will be close to transaction commit time but I am not sure how close it is.

相当。这将取决于网络延迟，CPU调度延迟等。它肯定不会精确到微秒。

"fairly". It will depend on network latency, CPU scheduling lag, etc. It sure won't be microsecond-accurate.

例如，在Windows上，它最多可以精确到毫秒，但默认情况下，它可以精确到最近的15毫秒计时器刻度。

For example, on Windows it will be at best accurate to the millisecond, but by default it'll be accurate to the nearest 15-millisecond timer tick.

首先，在我的代码中，每次侦听之间有一段时间（尽管很小）

First, there is some time between polls in my code while listening(however small it is)

不要投票。 select（）套接字，这样一来，您就可以唤醒要读取的数据。在Linux上，最好使用 epoll（）系统调用。

Don't poll. select() the socket so you're woken the instant there's data to read. On Linux you'd ideally use the epoll() system call for this.

其次，我不确定NOTIFY / LISTEN通信本身之间是否存在任何延迟。

and second, I am not sure if there is any lag between NOTIFY/LISTEN communication itself.

有些，是的，因为有交易提交需要时间。因此，在发出 NOTIFY 到事件发送给侦听器之间，会有一些非零的时间。

Some, yes, because a transaction commit takes time. So there's some non-zero time between when you issue the NOTIFY and when the event is sent to listeners.

这篇关于Postgres LISTEN / NOTIFY-低延迟，实时吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！