Re: Unwanted effect of search_path on default-value entries in case of serial columns

>>>>> "Holger" == Holger Jakobs <holger(at)jakobs(dot)com> writes:

Holger> Hello everybody,

Holger> Recently we encountered a funny behaviour of PostgreSQL
Holger> regarding the default-value entry resulting from columns with
Holger> the pseudo type "serial". Actually, the string saved as the
Holger> name of the sequence depends on the search_path set when
Holger> creating the table.

[...]

Holger> -- Displaying the info about default values from system tables shows a
Holger> -- difference. ONLY for a3 the sequence name is qualified.
Holger> hj=# SELECT d.adsrc AS default_value

You should never look at the adsrc column for any reason, it's garbage.
(And it's being removed in pg12, not before time.)

What adsrc stores is the actual text of the original default expression.
The most obvious way that this is garbage is because it doesn't track
renaming of objects: if you rename a sequence, function, or whatever
that was mentioned in a default, then pg will automatically pick up the
change (because adbin, which is what actually gets evaluated, only
stores the OIDs and not names), but adsrc will reflect the old name.

Holger> Why does this impose a problem? For ordinary applications, it
Holger> actually doesn't.

Holger> But in our case, a program reading information from the system
Holger> tables compares the schemas of two databases.

Then the problem is that your program is looking at the garbage in
adsrc, rather than doing what it should do which is to use pg_get_expr
to deparse adbin back to an expression.

--
Andrew (irc:RhodiumToad)