Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
Hello There is a use case from GoodData's engineer --- We have many user projects, each project has his own database with granted permissions. We use pg_dump with option "--clean" which extends SQL dump with syntax like: "DROP INDEX tab1_idx1;" "DROP TABLE tab1;" When we load such dump into database we get a lot of error messages saying: "INDEX tab1_idx1 doesn't exists;" "TABLE tab1 doesn't exists;" We need "--clean" parameter, because we often need to load dump into database which is not empty. On the other hand, we want to get rid of ERROR messages in case some of tables are missing. Typically we are need to solve one of following situations: 1, dump user project, restore the data in new project. Each project, even empty one contains at least table "const". During load of SQL dump, const table from dump needs to overwrite the one in database. 2, restore the data in project after fail of previous restoration E.g. connection lost during restoration, no disc space left on device, ... 3, restore the data in project from backup For some reason, data in the project are messed up and they need to be overwritten from backup Vlada Vladimír Vacula (GoodData) --- Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
On tis, 2011-11-15 at 22:27 -0500, Tom Lane wrote: > Now, --clean using DROP IF EXISTS would be targeted at, um, what case? > I guess the idea is to be able to load into a database that sort of > mostly shares the same schema as the one you dumped from, only it's > not the same (if it were the same, you'd not need IF EXISTS). What about a schema that is nominally the same, but some object is missing, because some earlier attempt to clean things up, or because it's an earlier version of the schema, or because of some batch jobs make things come and go. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
Robert Haas writes: > On Tue, Nov 15, 2011 at 10:36 AM, Alvaro Herrera > wrote: >>> I'm wondering why we need an option for this, though. Assuming we >>> make DROP IF EXISTS work anywhere that it doesn't already, why not >>> just always produce that rather than straight DROP? It seems >>> categorically better. >> I think there's a fuzzy idea that we should try to keep our dumps >> vaguely compatible with other systems. If we add DROP IF EXISTS >> unconditionally, there would be no way to make them run elsewhere. > Well, except in --clean mode, we don't emit DROP commands at all. And > since --clean doesn't even work well on PostgreSQL, I can't get too > excited about whether it will work everywhere else. What I find lacking here is a clear explication of what the use-case is; that is, this proposal seems like a solution in search of a problem. The default case for pg_dump is that you're going to load a bunch of objects into an empty database. You don't need any DROP commands, and this always works fine (or if it doesn't, there's a clear bug to be fixed in pg_dump). The --clean switch seems to be targeted at the case that you're trying to replace the contents of a database that has the same schema as the one you dumped from. The DROPs will work, more or less, barring nasty cases such as circular dependencies. (Maybe it will work even then, but I don't know how carefully we've tested such cases.) Now, --clean using DROP IF EXISTS would be targeted at, um, what case? I guess the idea is to be able to load into a database that sort of mostly shares the same schema as the one you dumped from, only it's not the same (if it were the same, you'd not need IF EXISTS). The problem with this is that if the schema isn't the same, it probably hasn't got the same dependencies, so there's rather little likelihood that pg_dump will correctly guess what order to issue the DROPs in ... and it certainly won't know about dependencies to the target objects from other objects that are in the destination database but weren't in the source. Possibly you could get around that by ignoring errors; but if you're willing to ignore errors, you don't need the IF EXISTS qualifiers. So before buying into this proposal, I want to see a clear demonstration of a common use-case where it actually does some good. I'm not convinced that there is one. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
Robert Haas writes: >> I wonder if that instead of trying to remain "somewhat compatible" to >> other systems we should instead have a mode specifically designed for >> that --one which didn't output SET or backslash commands, used inserts >> rather than COPY, etc-- and have the noncompatible mode offer nice >> features such as DROP IF EXISTS and the like. > > mysqldump has a --compatible=OTHER_DB_SYSTEM flag (postgresql is one > of the choices). That might not be a crazy way to approach the > problem, though possibly we'd want --compatible=FOO to be a shorthand > for a collection of behaviors that could alternatively be selected > individually via appropriately named long options > (--no-backslash-commands, --no-set-commands, etc.). I can't help but recalling Hannu's lightning talk at pgconf.eu in Amsterdam last month. What about implementing mysql protocol and syntax instead, so that users would just use mysqldump here, if that's the format they do want to play with. Not the same scale of work, but opening our infrastructure to multiple syntaxes and protocols could be something to aim for. Think memcache protocol backed by hstore or even plv8, too. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
On Tue, Nov 15, 2011 at 10:36 AM, Alvaro Herrera wrote: > > Excerpts from Robert Haas's message of mar nov 15 11:59:17 -0300 2011: >> On Tue, Nov 15, 2011 at 9:14 AM, Tom Lane wrote: >> > Pavel Stehule writes: >> >> there is a request on enhancing of pg_backup to produce a conditional >> >> DROPs. A reason for this request is more simple usage in very dynamic >> >> production - cloud BI solution. >> > >> >> pg_backup can have a new option "--conditional-drops" and then pg_dump >> >> will produce a DROP IF EXISTS statements instead DROP statements. >> > >> > That is not going to be possible unless we commit to having an IF EXISTS >> > option for every type of DROP statement, now and in the future. >> > Even then, it's not apparent to me that it solves any real use-case. >> > You're probably better off just using --clean and ignoring any errors. >> >> Ignoring errors sucks, though, because sometimes you want the whole >> thing to succeed or fail as a unit. >> >> I'm wondering why we need an option for this, though. Assuming we >> make DROP IF EXISTS work anywhere that it doesn't already, why not >> just always produce that rather than straight DROP? It seems >> categorically better. > > I think there's a fuzzy idea that we should try to keep our dumps > vaguely compatible with other systems. If we add DROP IF EXISTS > unconditionally, there would be no way to make them run elsewhere. Well, it seems to me there's a mixed signal here. - When operating with Postgres -> Postgres, the suggestions are stuff like "Oh, you can just ignore these errors" "Oh, you can just use sed to change things to play better" I think I'd rather have *some* option here of having there be some benefit to "PG->PG". I'd rather hear things like... "OK, if you're using some other database, you can just ignore these errors" "OK, if you're using some other database that doesn't know about DROP IF EXISTS, then you can just use sed to fix that" > Of course, our dumps already fail for a lot of reasons (for example SET > commands and COPY), but I think if you dump with inserts and COPY and > have the other server ignore errors found while processing the script, > the idea is that you should find that mostly it loads the tables and > data. I don't know how well this principle works for the DROP commands. Back in either 8.1 or 8.2, I think it was, we added in a pretty complete set of "DROP IF EXISTS" commands. While I am not keen on adding 250 options to pg_dump, I think it's not the most wonderful thing in the world to need to anticipate failures. I'd rather have *some* controls that do NOT involve needing to write a parser of a fragile combination of SQL as generated by pg_dump. > I wonder if that instead of trying to remain "somewhat compatible" to > other systems we should instead have a mode specifically designed for > that --one which didn't output SET or backslash commands, used inserts > rather than COPY, etc-- and have the noncompatible mode offer nice > features such as DROP IF EXISTS and the like. +1 on that, yeah. The part that'll be nasty-ish is the question of to what degree we'd like to be cross-PG-version compatible. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?" -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
On Tue, Nov 15, 2011 at 10:36 AM, Alvaro Herrera wrote: >> I'm wondering why we need an option for this, though. Assuming we >> make DROP IF EXISTS work anywhere that it doesn't already, why not >> just always produce that rather than straight DROP? It seems >> categorically better. > > I think there's a fuzzy idea that we should try to keep our dumps > vaguely compatible with other systems. If we add DROP IF EXISTS > unconditionally, there would be no way to make them run elsewhere. > > Of course, our dumps already fail for a lot of reasons (for example SET > commands and COPY), but I think if you dump with inserts and COPY and > have the other server ignore errors found while processing the script, > the idea is that you should find that mostly it loads the tables and > data. I don't know how well this principle works for the DROP commands. Well, except in --clean mode, we don't emit DROP commands at all. And since --clean doesn't even work well on PostgreSQL, I can't get too excited about whether it will work everywhere else. > I wonder if that instead of trying to remain "somewhat compatible" to > other systems we should instead have a mode specifically designed for > that --one which didn't output SET or backslash commands, used inserts > rather than COPY, etc-- and have the noncompatible mode offer nice > features such as DROP IF EXISTS and the like. mysqldump has a --compatible=OTHER_DB_SYSTEM flag (postgresql is one of the choices). That might not be a crazy way to approach the problem, though possibly we'd want --compatible=FOO to be a shorthand for a collection of behaviors that could alternatively be selected individually via appropriately named long options (--no-backslash-commands, --no-set-commands, etc.). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
Excerpts from Robert Haas's message of mar nov 15 11:59:17 -0300 2011: > On Tue, Nov 15, 2011 at 9:14 AM, Tom Lane wrote: > > Pavel Stehule writes: > >> there is a request on enhancing of pg_backup to produce a conditional > >> DROPs. A reason for this request is more simple usage in very dynamic > >> production - cloud BI solution. > > > >> pg_backup can have a new option "--conditional-drops" and then pg_dump > >> will produce a DROP IF EXISTS statements instead DROP statements. > > > > That is not going to be possible unless we commit to having an IF EXISTS > > option for every type of DROP statement, now and in the future. > > Even then, it's not apparent to me that it solves any real use-case. > > You're probably better off just using --clean and ignoring any errors. > > Ignoring errors sucks, though, because sometimes you want the whole > thing to succeed or fail as a unit. > > I'm wondering why we need an option for this, though. Assuming we > make DROP IF EXISTS work anywhere that it doesn't already, why not > just always produce that rather than straight DROP? It seems > categorically better. I think there's a fuzzy idea that we should try to keep our dumps vaguely compatible with other systems. If we add DROP IF EXISTS unconditionally, there would be no way to make them run elsewhere. Of course, our dumps already fail for a lot of reasons (for example SET commands and COPY), but I think if you dump with inserts and COPY and have the other server ignore errors found while processing the script, the idea is that you should find that mostly it loads the tables and data. I don't know how well this principle works for the DROP commands. I wonder if that instead of trying to remain "somewhat compatible" to other systems we should instead have a mode specifically designed for that --one which didn't output SET or backslash commands, used inserts rather than COPY, etc-- and have the noncompatible mode offer nice features such as DROP IF EXISTS and the like. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
On Tue, Nov 15, 2011 at 9:14 AM, Tom Lane wrote: > Pavel Stehule writes: >> there is a request on enhancing of pg_backup to produce a conditional >> DROPs. A reason for this request is more simple usage in very dynamic >> production - cloud BI solution. > >> pg_backup can have a new option "--conditional-drops" and then pg_dump >> will produce a DROP IF EXISTS statements instead DROP statements. > > That is not going to be possible unless we commit to having an IF EXISTS > option for every type of DROP statement, now and in the future. > Even then, it's not apparent to me that it solves any real use-case. > You're probably better off just using --clean and ignoring any errors. Ignoring errors sucks, though, because sometimes you want the whole thing to succeed or fail as a unit. I'm wondering why we need an option for this, though. Assuming we make DROP IF EXISTS work anywhere that it doesn't already, why not just always produce that rather than straight DROP? It seems categorically better. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
2011/11/15 Tom Lane : > Pavel Stehule writes: >> there is a request on enhancing of pg_backup to produce a conditional >> DROPs. A reason for this request is more simple usage in very dynamic >> production - cloud BI solution. > >> pg_backup can have a new option "--conditional-drops" and then pg_dump >> will produce a DROP IF EXISTS statements instead DROP statements. > > That is not going to be possible unless we commit to having an IF EXISTS > option for every type of DROP statement, now and in the future. > Even then, it's not apparent to me that it solves any real use-case. > You're probably better off just using --clean and ignoring any errors. > ook Regards Pavel Stehule > regards, tom lane > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
Pavel Stehule writes: > there is a request on enhancing of pg_backup to produce a conditional > DROPs. A reason for this request is more simple usage in very dynamic > production - cloud BI solution. > pg_backup can have a new option "--conditional-drops" and then pg_dump > will produce a DROP IF EXISTS statements instead DROP statements. That is not going to be possible unless we commit to having an IF EXISTS option for every type of DROP statement, now and in the future. Even then, it's not apparent to me that it solves any real use-case. You're probably better off just using --clean and ignoring any errors. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ToDo: pg_backup - using a conditional DROP
2011/11/15 Pavel Stehule : > Hello, > > there is a request on enhancing of pg_backup to produce a conditional > DROPs. A reason for this request is more simple usage in very dynamic > production - cloud BI solution. > > pg_backup can have a new option "--conditional-drops" and then pg_dump > will produce a DROP IF EXISTS statements instead DROP statements. > > Ideas, comments? > I think that if there is other way to get the same result in other way is better to use it without add new options. In this case, if you are on unix environment, I suppose that you can use external batch to manipulate the output file, like "sed" I suppose. Obviusly this is my personal opinion. Best Regards Torello > Regards > > Pavel Stehule > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ToDo: pg_backup - using a conditional DROP
Hello, there is a request on enhancing of pg_backup to produce a conditional DROPs. A reason for this request is more simple usage in very dynamic production - cloud BI solution. pg_backup can have a new option "--conditional-drops" and then pg_dump will produce a DROP IF EXISTS statements instead DROP statements. Ideas, comments? Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers