Thread: [BUGS] BUG #14700: pg_restore doesn't declare schema in 'create function'statements
[BUGS] BUG #14700: pg_restore doesn't declare schema in 'create function'statements
From
psuderevsky@gmail.com
Date:
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
Re: [BUGS] BUG #14700: pg_restore doesn't declare schema in 'create function' statements
From
Tom Lane
Date:
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