Thread: BUG #14271: Please fix 13804 bug
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDI3MQpMb2dnZWQgYnk6ICAg ICAgICAgIEFudG9uIER5YWNoZW5rbwpFbWFpbCBhZGRyZXNzOiAgICAgIGFt ZGphY2hlbmtvQGdtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNS4z Ck9wZXJhdGluZyBzeXN0ZW06ICAgV2luZG93cwpEZXNjcmlwdGlvbjogICAg ICAgIAoKVGhlIGlzc3VlIG9mIHRoaXMgYnVnIGluIHRoZSBvdXRwdXQgb2Yg ZHVtcC4NCg0KQ3VycmVudCBvdXRwdXQ6DQpsaW5lIDQwOiBDUkVBVEUgU0NI RU1BIHB1YmxpYzsNCg0KRml4IG9mIHRoZSBwcm9ibGVtOg0KQ1JFQVRFIFND SEVNQSBJRiBOT1QgRVhJU1RTIHB1YmxpYzsNCg0KSWYgdGhlcmUgaXMgc29t ZSBwcm9ibGVtIHRvIGNoYW5nZSBkdW1wIGdlbmVyYXRvciB0byBkbyB0aGlz IHBsZWFzZSBkbyB0aGlzCmF0IGxlYXN0IGZvciAtLWlmLWV4aXN0IHBhcmFt ZXRlci4KCg==
On Mon, Aug 1, 2016 at 11:19 AM, <amdjachenko@gmail.com> wrote: > Current output: > line 40: CREATE SCHEMA public; > > Fix of the problem: > CREATE SCHEMA IF NOT EXISTS public; > > If there is some problem to change dump generator to do this please do this > at least for --if-exist parameter. Could you describe a little bit more in details the command of pg_dump that you are using? It would be also good to get an exact idea of the dump you are seeing, what you think it should do with a detailed example, and what are the objects on your database at the moment of taking the dump. -- Michael
On Sunday, July 31, 2016, Michael Paquier <michael.paquier@gmail.com> wrote: > On Mon, Aug 1, 2016 at 11:19 AM, <amdjachenko@gmail.com <javascript:;>> > wrote: > > Current output: > > line 40: CREATE SCHEMA public; > > > > Fix of the problem: > > CREATE SCHEMA IF NOT EXISTS public; > > > > If there is some problem to change dump generator to do this please do > this > > at least for --if-exist parameter. > > Could you describe a little bit more in details the command of pg_dump > that you are using? It would be also good to get an exact idea of the > dump you are seeing, what you think it should do with a detailed > example, and what are the objects on your database at the moment of > taking the dump. > > See also... https://www.postgresql.org/message-id/20160312230919.GA11080%40hermes.hilbert.loc I'd say there is room for both doc and code improvement here. Not exactly sure what. David J.
Michael Paquier <michael.paquier@gmail.com> writes:
> On Mon, Aug 1, 2016 at 11:19 AM, <amdjachenko@gmail.com> wrote:
>> Current output:
>> line 40: CREATE SCHEMA public;
> Could you describe a little bit more in details the command of pg_dump
> that you are using?
I experimented with this a bit and found that you only get that output
if you use -C and -c together. If you use -c alone, you do get a
"CREATE SCHEMA public", but first you get "DROP SCHEMA public",
so that seems okay. (I suppose it could be problematic if you don't
have permissions to do that drop, but that would be a different
complaint eh?)
After poking around awhile, it seems like the real problem is with this
logic in _printTocEntry() that special-cases the public schema:
/*
* Avoid dumping the public schema, as it will already be created ...
* unless we are using --clean mode, in which case it's been deleted and
* we'd better recreate it. Likewise for its comment, if any.
*/
if (!ropt->dropSchema)
{
if (strcmp(te->desc, "SCHEMA") == 0 &&
strcmp(te->tag, "public") == 0)
return;
/* The comment restore would require super-user privs, so avoid it. */
if (strcmp(te->desc, "COMMENT") == 0 &&
strcmp(te->tag, "SCHEMA public") == 0)
return;
}
That seems okay on its face, but the problem is that it has too simplistic
a notion of what --clean mode means. In particular, if we consult
RestoreArchive() to find out what dropSchema *really* does:
/*
* Drop the items at the start, in reverse order
*/
if (ropt->dropSchema)
{
for (te = AH->toc->prev; te != AH->toc; te = te->prev)
{
AH->currentTE = te;
/*
* In createDB mode, issue a DROP *only* for the database as a
* whole. Issuing drops against anything else would be wrong,
* because at this point we're connected to the wrong database.
* Conversely, if we're not in createDB mode, we'd better not
* issue a DROP against the database at all.
*/
if (ropt->createDB)
{
if (strcmp(te->desc, "DATABASE") != 0)
continue;
}
else
{
if (strcmp(te->desc, "DATABASE") == 0)
continue;
}
/* Otherwise, drop anything that's selected and has a dropStmt */
In other words, the combination of -C and -c is supposed to issue
"DROP DATABASE currentdb" then "CREATE DATABASE currentdb", but not
individual drops against objects contained in the DB. Therefore, we
should only expect that the public schema needs to be created if we are
in -c node and NOT in -C mode. If both are set then we're working in a
virgin database that should be expected to contain a public schema.
So it looks to me like an appropriate fix would be basically this
in _printTocEntry():
- if (!ropt->dropSchema)
+ if (!(ropt->dropSchema && !ropt->createDB))
plus some suitable adjustment of the comment. I'm too lazy/tired
to test this theory right now, though.
regards, tom lane
On Monday, August 1, 2016, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > So it looks to me like an appropriate fix would be basically this > in _printTocEntry(): > > - if (!ropt->dropSchema) > + if (!(ropt->dropSchema && !ropt->createDB)) > > plus some suitable adjustment of the comment. I'm too lazy/tired > to test this theory right now, though. > > I concurred on the original thread bug report thread. https://www.postgresql.org/message-id/CAKFQuwbYXU-z0uxnLWV_1awd16JiMnJnKnMQgYrUDJT4uqmmjQ%40mail.gmail.com David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Monday, August 1, 2016, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> So it looks to me like an appropriate fix would be basically this >> in _printTocEntry(): >> - if (!ropt->dropSchema) >> + if (!(ropt->dropSchema && !ropt->createDB)) > I concurred on the original thread bug report thread. > https://www.postgresql.org/message-id/CAKFQuwbYXU-z0uxnLWV_1awd16JiMnJnKnMQgYrUDJT4uqmmjQ%40mail.gmail.com Heh ... that's what I get for not bothering to go read the other thread. Clearly we dropped the ball on pushing that report through to a fix. regards, tom lane