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

Question

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?

Helsa

Answer 1

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.

Heavyhanded

Answer 2

The default function is "bound" at the time the default constraint is created. The view showing the unqualified name is simply abbreviating it.

This can be demonstrated by inserting rows before and after shadowing the function:

Set search_path to public,pg_catalog;

Create Temp Table foo (
    test date not null default now()
);

Insert Into foo default values;

Create Function public.now() Returns timestamp with time zone Language SQL As $$ 
     -- No idea why I chose this date.
     Select '1942-05-09'::timestamp with time zone;
$$;

Insert Into foo default values;

Select * from foo;

Note that both rows (inserted before and after function creation) contain today's date, not the fake date.

Furthermore, creating a table with the above function already in scope, then trying to delete the function, results in a dependency error:

Set search_path to public,pg_catalog;

Create Function public.now() Returns timestamp with time zone Language SQL As $$ 
    Select '1942-05-09'::timestamp with time zone;
$$;

Create Temp Table bar (
    test date not null default now()
);

Insert Into bar default values;

Select * from bar;
-- Single row containing the dummy date rather than today

Drop Function public.now();
-- ERROR:  cannot drop function now() because other objects depend on it

If the binding happened only on insert, there would be no such dependency.

Weathered

Answer 3

Don't bother all that. Postgres sometimes is writing weired stuff after things are compiled. Especially Views are often changed beyound recognition.

And: now() and pg_catalog.now() is normally the very same. See:

CREATE OR REPLACE FUNCTION now()
    RETURNS timestamp with time zone AS
'now'
    LANGUAGE internal STABLE STRICT
 COST 1;
 ALTER FUNCTION now()
    OWNER TO postgres;
 COMMENT ON FUNCTION now() IS 'current transaction time';

Don't worry.

Delectate

I can't ignore it, I want to take advantage of the difference. I explained better. – Helsa

Still don't understand. Better now() than null, right? Are you inventing a time machine? – Delectate