Обсуждение: [BUGS] BUG #14700: pg_restore doesn't declare schema in 'create function'statements

The following bug has been logged on the website:

Bug reference:      14700
Logged by:          Pavel Suderevsky
Email address:      psuderevsky@gmail.com
PostgreSQL version: 9.4.5
Operating system:   Centos 7.2
Description:

Hi,

I couldn't find this bug reported here, but maybe community is aware.

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

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

Reproduced with dumping in 9.4.5 and restoring it to 9.4.5 and 9.6.2.


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

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:

https://www.postgresql.org/message-id/flat/8c4600a6f10e7dd823a157eff9985068.squirrel%40webmail.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