postgresql 如何接收有关表更改的自动通知？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21117431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to receive automatic notifications about changes in tables?
提问by Richard
What I am using: PostgreSQLand Python. I am using Python to access PostgreSQL
我使用的是：PostgreSQL和Python。我正在使用 Python 访问 PostgreSQL
What I need: Receive a automatic notification, on Python, if anyone records something on a specific table on database.
我需要什么：如果有人在数据库的特定表上记录了某些内容，则在 Python 上接收自动通知。
I think that it is possible using a routine that go to that table, over some interval, and check changes. But it requires a loopand I would like something like an a assynchronous way.
我认为可以使用一个例程在某个时间间隔内转到该表并检查更改。但它需要一个循环，我想要一种类似异步方式的东西。
Is it possible?
是否可以？
回答by Craig Ringer
donmage is quite right - LISTENand NOTIFYare what you want. You'll still need a polling loop, but it's very lightweight, and won't cause detectable server load.
donmage 是非常正确的 -LISTEN并且NOTIFY是您想要的。您仍然需要一个轮询循环，但它非常轻量级，并且不会导致可检测的服务器负载。
If you want psycopg2to trigger callbacks at any timein your program, you can do this by spawning a thread and having that thread execute the polling loop. Check to see whether psycopg2 enforces thread-safe connection access; if it doesn't, you'll need to do your own locking so that your polling loop only runs when the connection is idle, and no other queries interrupt a polling cycle. Or you can just use a second connection for your event polling.
如果您想在程序psycopg2中随时触发回调，您可以通过生成一个线程并让该线程执行轮询循环来实现。检查 psycopg2 是否强制执行线程安全的连接访问；如果没有，您将需要进行自己的锁定，以便您的轮询循环仅在连接空闲时运行，并且没有其他查询中断轮询周期。或者您可以仅使用第二个连接进行事件轮询。
Either way, when the background thread that's polling for notify events receives one, it can invoke a Python callback function supplied by your main program, which might modify data structures / variables shared by the rest of the program. Beware, if you do this, that it can quickly become a nightmare to maintain.
无论哪种方式，当轮询通知事件的后台线程收到一个时，它可以调用主程序提供的 Python 回调函数，这可能会修改程序其余部分共享的数据结构/变量。当心，如果你这样做，它很快就会变成一场噩梦。
If you take that approach, I strongly suggest using the multithreading/ multiprocessingmodules. They will make your life massively easier, providing simple ways to exchange data between threads, and limiting modifications made by the listening thread to simple and well-controlled locations.
如果您采用这种方法，我强烈建议您使用multithreading/multiprocessing模块。它们将使您的生活更加轻松，提供在线程之间交换数据的简单方法，并将侦听线程所做的修改限制在简单且控制良好的位置。
If using threads instead of processes, it is important to understand that in cPython (i.e. "normal Python") you can't have a true callback interrupt, because only one thread may be executing in cPython at once. Read about the "global interpreter lock" (GIL) to understand more about this. Because of this limitation (and the easier, safer nature of shared-nothing by default concurrency) I often prefer multiprocessing to multithreading.
如果使用线程而不是进程，重要的是要了解在 cPython（即“普通 Python”）中不能有真正的回调中断，因为在 cPython 中一次可能只有一个线程正在执行。阅读“全局解释器锁”(GIL) 以了解更多相关信息。由于这个限制（以及默认情况下无共享并发的更简单、更安全的特性），我通常更喜欢多处理而不是多线程。
回答by Dmitri Goldring
The commands you are looking for are probably LISTEN/NOTIFY.
您正在寻找的命令可能是LISTEN/NOTIFY。
If you are using psycopg, check out the section Asynchronous Notifications.
如果您使用的是 psycopg，请查看异步通知部分。
[This does require polling though.]
[虽然这确实需要投票。]
回答by kylie.a
I believe what you are looking for is a trigger:
我相信你正在寻找的是一个触发器：
http://wiki.postgresql.org/wiki/A_Brief_Real-world_Trigger_Example
http://wiki.postgresql.org/wiki/A_Brief_Real-world_Trigger_Example
