BUG #14700: pg_restore doesn't declare schema in 'create function' statements

psuderevsky@gmail.com writes:

When restoring only particular schema from dump with "pg_restore -n" it does
the following:
1. SET search_path = yourschema, pg_catalog
2. CREATE FUNCTION yourfunction()

Yes, that's intentional.

As a result, if you had forgotten creating yourschema before restoring then
no objects will be restored except your functions, they will be restored in
pg_catalog schema.
These statements must be performed with direct schema reference: CREATE
FUNCTION yourschema.yourfunction().

We're not doing that because it breaks other useful use-cases; in
particular, editing a dump to move things into another schema becomes
a lot harder. Even if we wanted to change pg_dump that way, it wouldn't
make things any safer for existing dump files.

There was a long discussion of whether to change anything in this area
a few years ago:

/messages/by-id/8c4600a6f10e7dd823a157eff9985068.squirrel@webmail.xs4all.nl

That trailed off without any real consensus about what to do. Personally
I still like the idea of refusing to allow creates if the first schema
listed in search_path doesn't exist, but I was having a hard time selling
that plan :-(

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs