什么时候/如何将默认值表达式函数与search_path绑定在一起？ [英] When / how are default value expression functions bound with regard to search_path?
问题描述
出于测试目的，我提供了自己的 now（）函数的实现，该函数为 public.now（）。使用 search_path 覆盖默认的 pg_catalog.now（）可以使用我自己的版本，但是我有一个默认表达式为 now（）的表。显示该表会产生类似于以下内容的内容：
For testing purposes, I provide my own implementation of the now() function which is public.now(). Using search_path to override the default pg_catalog.now() with my own version mostly works, but I have a table with a table with a default expression of now(). Showing the table produces something akin to the following:
start_date | date | not null default now()
但是，在保存和还原架构（到测试数据库）之后，相同的显示表会产生
However, after a schema save and restore (to a testing DB), the same show table produces
start_date | date | not null default pg_catalog.now()
我据此假设，最初默认表达式中的函数为未绑定到任何模式，并且search_path将用于查找正确的模式。但是，转储或还原操作似乎将功能绑定到当前功能。
I assume from this, initially the function in the default expression is not bound to any schema and the search_path will be used to find the correct one. However, dump or restore operation seems to "bind" the function to the current one.
我对函数的绑定状态的理解正确吗？
是否可以在转储/还原边界之间保持函数的非绑定性？
Is my understanding of the "bind state" of the function correct? Is there a way to keep the unbound-ness of the function across dump/restore boundaries?
推荐答案
默认值在创建时进行解析（早期绑定！）。在psql，pgAdmin或其他客户端中看到的是文本表示形式，但实际上是函数 now（） OID 创建列时的$ c>默认值存储在系统目录 pg_attrdef 。我引用：
Default values are parsed at creation time (early binding!). What you see in psql, pgAdmin or other clients is a text representation but, in fact, the OID of the function now() at the time of creating the column default is stored in the system catalog pg_attrdef. I quote:
adbin pg_node_tree The internal representation of the column default value
adsrc text A human-readable representation of the default value
当您更改默认情况下如何在内部创建表特定的模式，这会导致 search_path Postgres显示合格的函数名称，因为当前的 search_path 无法正确解析该函数的名称。
When you change the search_path, that causes Postgres to display the name of the function schema-qualified, since it would not be resolved correctly any more with the current search_path.
转储和还原与您的自定义 search_path 设置无关。他们明确地设置了它。因此，您所看到的与转储/还原周期无关。
Dump and restore are not concerned with your custom search_path setting. They set it explicitly. So what you see is not related to the the dump / restore cycle.
将 public 放在 search_path 中的 pg_catalog 之前是危险游戏。贫困用户（包括您自己）通常被允许在此处编写和创建可能无意中推翻系统功能的功能-结果具有任意（或恶意）后果。
Placing public before pg_catalog in the search_path is a game of hazard. Underprivileged users (including yourself) are often allowed to write there and create functions that may inadvertently overrule system functions - with arbitrary (or malicious) outcome.
您想要一个具有受限访问权限的专用架构以覆盖内置函数。改用类似这样的东西：
You want a dedicated schema with restricted access to override built-in functions. Use something like this instead:
SET search_path = override, pg_catalog, public;
这篇关于什么时候/如何将默认值表达式函数与search_path绑定在一起？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
