sqlpostgresqldefaultsearch-path

When / how are default value expression functions bound with regard to search_path?

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()

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?

Solution

  • 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 column default value, in nodeToString() representation. Use pg_get_expr(adbin, adrelid) to convert it to an SQL expression.

    Changing the search_path may cause Postgres to display the name of the function schema-qualified since it would not be resolved correctly any more with the current 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.

    Override built-in functions

    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;

    Details in this related answer on dba.SE.