在 Postgres 中识别连接 ID
我有一个 Postgres 数据库 (9)，我正在为其编写触发器。我希望触发器设置记录的修改时间和用户 ID。在 Firebird 中，您有一个可以在触发器中使用的 CONNECTIONID，因此您可以在连接到数据库时向表中添加一个值（这是一个桌面应用程序，因此连接在应用程序的生命周期内是持久的），类似于this:
UserId | ConnectionId --------------------- 544 | 3775
然后在触发器中查找connectionid 3775属于userid 544，并使用544作为修改记录的用户。
我可以在 Postgres 中使用类似的东西吗？
I have a Postgres database (9) that I am writing a trigger for. I want the trigger to set the modification time, and user id for a record. In Firebird you have a CONNECTIONID that you can use in a trigger, so you could add a value to a table when you connect to the database (this is a desktop application, so connections are persistent for the lifetime of the app), something like this:
UserId | ConnectionId --------------------- 544 | 3775
and then look up in the trigger that connectionid 3775 belongs to userid 544 and use 544 as the user that modified the record.
Is there anything similar I can use in Postgres?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
你可以使用进程ID。可以通过以下方式检索：
使用此 pid，您还可以使用表 pg_stat_activity 来获取有关当前后端的更多信息，尽管您应该已经知道一切，因为您正在使用此后端。
或者更好。只需创建一个序列，并为每个连接从中检索一个值：
然后：
在每个连接中检索一个连接唯一 ID。
you could use the process id. It can be retrieved with:
With this pid you can also use the table pg_stat_activity to get more information about the current backend, althouht you already should know everything, since you are using this backend.
Or better. Just create a serial, and retrieve one value from it for each connection:
And then:
in each connection, to retrieve a connection unique id.
一种方法是使用
custom_variable_classes配置选项。它似乎被设计为允许配置附加模块，但也可用于在当前数据库会话中存储任意值。需要将以下内容添加到
postgresql.conf中：当您第一次连接到数据库时，您可以在自定义类中存储所需的任何信息，如下所示
：可以使用
current_setting()函数检索该值：向日志表添加条目可能如下所示：
One way is to use the
custom_variable_classesconfiguration option. It appears to be designed to allow the configuration of add-on modules, but can also be used to store arbitrary values in the current database session.Something along the lines of the following needs to be added to
postgresql.conf:When you first connect to the database you can store whatever information you require in the custom class, like so:
And later in on you can retrieve this value with the
current_setting()function:Adding an entry to a log table might look something like this:
虽然它可能适用于您的桌面用例，但请注意，进程 ID 号会翻转（32768 是常见上限），因此将它们用作识别用户的唯一密钥可能会遇到问题。如果您最终在跟踪用户->进程映射的表中得到了上一个会话的剩余数据，那么一旦滚动，这些数据可能会与分配了相同进程 ID 的新连接发生冲突。对于您的应用程序来说，只要确保积极清除旧的映射条目就足够了，也许在启动时考虑到您描述其操作的方式。
一般来说，为了避免此问题，您需要创建一个包含额外信息的连接密钥，例如会话何时启动：
它必须迭代计算时所有活动的连接，因此它确实添加了一个一点开销。从 PostgreSQL 8.4 开始，可以更有效地执行该操作：
但只有当您同时有大量活动连接时，这才真正重要。
While it may work for your desktop use case, note that process ID numbers do rollover (32768 is a common upper limit), so using them as a unique key to identify a user can run into problems. If you ever end up with leftover data from a previous session in the table that's tracking user->process mapping, that can collide with newer connections assigned the same process id once it's rolled over. It may be sufficient for your app to just make sure you aggressively clean out old mapping entries, perhaps at startup time given how you've described its operation.
To avoid this problem in general, you need to make a connection key that includes an additional bit of information, such as when the session started:
That has to iterate over all of the connections active at the time to compute, so it does add a bit of overhead. It's possible to execute that a bit more efficiently starting in PostgreSQL 8.4:
But that only really matters if you have a large number of connections active at once.
如果您需要，请使用
current_user数据库用户（通过阅读您的问题，我不确定这就是您想要的）。Use
current_userif you need the database user (I'm not sure that's what you want by reading your question).