Re: [HACKERS] Suggested easy TODO: pg_dump --from-list
Hey Dimitri, hackers, Okay, there is a some getddl utility. But as for me, it does not simplify the development. It is file-based pgAdmin with the exception that I can use, e.g. Emacs for editing rather than build-in editor of pgAdmin. But I can use Emacs from psql(1)... Without some restoreddl which able to compile the set of database objects from many files this tool (getddl) does nothing because another developer can change the database directly (from psql, pgAdmin, ...), commit his changes and when I restore the database from entire dump (rather than from set of files created by getddl) the result will be out of sync between files version control system and restored database. 2010/11/26 Dimitri Fontaine dimi...@2ndquadrant.fr Robert Haas robertmh...@gmail.com writes: One thing I've often wished for is the ability to dump a specific function See getddl from OmniTI, or the alternative version I kept forgetting to put online somewhere: https://labs.omniti.com/labs/pgtreats/wiki/getddl https://github.com/dimitri/getddl The OmniTI version will output a single file with all objects into a single file, and my fork will do that in a directory structure with a file per object or about (a single file containing all functions sharing the same name, e.g.). Both project goal is to make it easy to version (as in git) your DDL and check for changes. 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 -- // Dmitriy.
Re: [HACKERS] Suggested easy TODO: pg_dump --from-list
Dmitriy Igrishin dmit...@gmail.com writes: Without some restoreddl which able to compile the set of database objects from many files this tool (getddl) does nothing because another developer can change the database directly Indeed, getddl does not try to solve that issue. It's more a VCS and editor friendly export, to be able to see diffs that come from the real current database state rather than what your code tracking makes you think is in production already. 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] Suggested easy TODO: pg_dump --from-list
Robert Haas robertmh...@gmail.com writes: One thing I've often wished for is the ability to dump a specific function See getddl from OmniTI, or the alternative version I kept forgetting to put online somewhere: https://labs.omniti.com/labs/pgtreats/wiki/getddl https://github.com/dimitri/getddl The OmniTI version will output a single file with all objects into a single file, and my fork will do that in a directory structure with a file per object or about (a single file containing all functions sharing the same name, e.g.). Both project goal is to make it easy to version (as in git) your DDL and check for changes. 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] Suggested easy TODO: pg_dump --from-list
On Wed, Nov 24, 2010 at 1:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: Well, very little about pg_dump is very [E], IMNSHO. The question in my mind here is what format the list file will take I was thinking same format as pg_restore -l, only without the dumpIDs. Nope ... those strings are just helpful comments, they aren't really guaranteed to be unique identifiers. In any case, it seems unlikely that a user could expect to get the more complicated cases exactly right other than by consulting pg_dump | pg_restore -l output. Which makes the use-case kind of dubious to me. I don't say that this wouldn't be a useful feature, but you need a better spec than this. One thing I've often wished for is the ability to dump a specific function (usually right after after I accidentally rm the file the source code was in). pg_dump has -t to pick a table, but there's no analagous way to select an object that isn't a relation. I think the first step here would be to design a system that lets you use a command-line argument to dump an arbitrary object, and after that you could work on reading the object descriptors from a file rather than the command line. As a first attempt at syntax, I might suggest something along the lines of object type: object name, where the types and names might look to COMMENT ON for inspiration. -- 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] Suggested easy TODO: pg_dump --from-list
Hey hackers, Completely agree with Robert ! It would be nice to dump functions definitions, e.g. to make it possible keep them in git separately. I also want to propose to make it possible dump function definitions as CREATE OR REPLACE FUNCTION rather than just CREATE FUNCTION (as pg_dump dumps them now). It is would be useful as well as psql's \ef. 2010/11/24 Robert Haas robertmh...@gmail.com On Wed, Nov 24, 2010 at 1:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: Well, very little about pg_dump is very [E], IMNSHO. The question in my mind here is what format the list file will take I was thinking same format as pg_restore -l, only without the dumpIDs. Nope ... those strings are just helpful comments, they aren't really guaranteed to be unique identifiers. In any case, it seems unlikely that a user could expect to get the more complicated cases exactly right other than by consulting pg_dump | pg_restore -l output. Which makes the use-case kind of dubious to me. I don't say that this wouldn't be a useful feature, but you need a better spec than this. One thing I've often wished for is the ability to dump a specific function (usually right after after I accidentally rm the file the source code was in). pg_dump has -t to pick a table, but there's no analagous way to select an object that isn't a relation. I think the first step here would be to design a system that lets you use a command-line argument to dump an arbitrary object, and after that you could work on reading the object descriptors from a file rather than the command line. As a first attempt at syntax, I might suggest something along the lines of object type: object name, where the types and names might look to COMMENT ON for inspiration. -- 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 -- // Dmitriy.
Re: [HACKERS] Suggested easy TODO: pg_dump --from-list
On 11/24/2010 07:29 AM, Robert Haas wrote: As a first attempt at syntax, I might suggest something along the lines of object type: object name, where the types and names might look to COMMENT ON for inspiration. pg_dump already uses a list of object types (e.g. as seen in the output from pg_restore --list). Let's not invent something new if we don't need to. But we'll need more than that. We'll need enough for disambiguation, especially for functions which is your stated use case. So, something like this might work: FUNCTION:myschema.myfunc:integer,text,timestamp with time zone We could allow the object type to compare case insensitively. For extra credit, allow type aliases, and don't require argument types if no disambiguation is needed. cheers andrew -- 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] Suggested easy TODO: pg_dump --from-list
On Wed, Nov 24, 2010 at 8:41 AM, Andrew Dunstan and...@dunslane.net wrote: On 11/24/2010 07:29 AM, Robert Haas wrote: As a first attempt at syntax, I might suggest something along the lines of object type: object name, where the types and names might look to COMMENT ON for inspiration. pg_dump already uses a list of object types (e.g. as seen in the output from pg_restore --list). Let's not invent something new if we don't need to. But we'll need more than that. We'll need enough for disambiguation, especially for functions which is your stated use case. So, something like this might work: FUNCTION:myschema.myfunc:integer,text,timestamp with time zone Actually, that's pretty much exactly what I was proposing, except that I would've kept the existing convention for how to write a function's arguments: FUNCTION:myschema.myfunc(integer,text,timestamp with time zone) -- 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] Suggested easy TODO: pg_dump --from-list
On Wed, Nov 24, 2010 at 1:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: Nope ... those strings are just helpful comments, they aren't really guaranteed to be unique identifiers. In any case, it seems unlikely that a user could expect to get the more complicated cases exactly right other than by consulting pg_dump | pg_restore -l output. Which makes the use-case kind of dubious to me. In which case would the catalogId, i.e. (tableoid, oid) not be unique? Or do you rather mean that it does not necessarily refer to the same object if that object got somehow recreated or that it could be different on different installations of the same database? Thanks, Joachim -- 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] Suggested easy TODO: pg_dump --from-list
On 11/24/2010 09:05 AM, Joachim Wieland wrote: On Wed, Nov 24, 2010 at 1:15 AM, Tom Lanet...@sss.pgh.pa.us wrote: Nope ... those strings are just helpful comments, they aren't really guaranteed to be unique identifiers. In any case, it seems unlikely that a user could expect to get the more complicated cases exactly right other than by consulting pg_dump | pg_restore -l output. Which makes the use-case kind of dubious to me. In which case would the catalogId, i.e. (tableoid, oid) not be unique? Or do you rather mean that it does not necessarily refer to the same object if that object got somehow recreated or that it could be different on different installations of the same database? It would be unique, but a pain in the neck for users to get. Robert's idea will have more traction with users. cheers andrew -- 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] Suggested easy TODO: pg_dump --from-list
Joachim Wieland j...@mcknight.de writes: On Wed, Nov 24, 2010 at 1:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: Nope ... those strings are just helpful comments, they aren't really guaranteed to be unique identifiers. In any case, it seems unlikely that a user could expect to get the more complicated cases exactly right other than by consulting pg_dump | pg_restore -l output. Which makes the use-case kind of dubious to me. In which case would the catalogId, i.e. (tableoid, oid) not be unique? Catalog OID + object OID would be unique, but surely we don't want to make users deal in specifying the objects to be dumped with that. Actually, what occurs to me to wonder is whether the facility has to be guaranteed unique at all. If for instance you have a group of overloaded functions, is there really a big use-case for dumping just one and not the whole group? Even if you think there's some use for it, is it big enough to justify a quantum jump in the complexity of the feature? Here's a radically simplified proposal: provide a switch --object-name=pattern where pattern follows the same rules as in psql \d commands (just to use something users will already know). Dump every object, of any type, whose qualified name matches the pattern, ie the same objects that would be shown by \d (of the relevant type) using the pattern. Accept multiple occurrences of the switch and dump the union of the matched objects. (Now that I think about it, this is the same as the existing --table switch, just generalized to match any object type.) There would be some cases where this'd dump more than you really want, but I think it'd make up for that in ease-of-use. It's not clear to me that dumping a few extra objects is a big problem except for the case where the objects are large tables, and in that case if you aren't specifying a sufficiently exact pattern, it's your own fault not a limitation of the feature. BTW, what about dependencies? One of the main complaints we've heard about pg_restore's filtering features is that they are not smart about including things like the indexes of a selected table, or the objects it depends on (eg, functions referred to in CHECK constraints). I'm not sure that a pure name-based filter will be any more usable than pg_restore's filter, if there is no accounting for dependencies. The risk of not including dependencies at dump time is vastly higher than in pg_restore, too, since by the time you realize you omitted something critical it may be too late to go back and get another dump. 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] Suggested easy TODO: pg_dump --from-list
On Wed, Nov 24, 2010 at 9:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Actually, what occurs to me to wonder is whether the facility has to be guaranteed unique at all. If for instance you have a group of overloaded functions, is there really a big use-case for dumping just one and not the whole group? Even if you think there's some use for it, is it big enough to justify a quantum jump in the complexity of the feature? Well, I think that being able to dump one specific function is a pretty important use case. But I don't see why that's necessarily irreconcilable with your suggested syntax of --function=pattern, if we assume that the pattern is being matched against pg_proc.oid::regprocedure and define the matching rules such that foo(text) will not match sfoo(text). Nothing anyone has proposed sounds like a quantum jump in complexity over your proposal. BTW, what about dependencies? One of the main complaints we've heard about pg_restore's filtering features is that they are not smart about including things like the indexes of a selected table, or the objects it depends on (eg, functions referred to in CHECK constraints). I'm not sure that a pure name-based filter will be any more usable than pg_restore's filter, if there is no accounting for dependencies. I am 100% positive that it will be a big step forward. I think the dependency issue is vaguely interesting, but less important and orthogonal. This will be very useful for cherry-picking an object from one server or database and loading it into another, a need that comes up with some frequency. Sure, it'd be handy to be able to cherry-pick the dependencies automatically, but you don't always need or even want that - you may know that they are already present in the target DB. -- 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] Suggested easy TODO: pg_dump --from-list
On Wed, Nov 24, 2010 at 9:38 AM, Andrew Dunstan and...@dunslane.net wrote: It would be unique, but a pain in the neck for users to get. Robert's idea will have more traction with users. Whatever approach we use, we need to think about the use case where 1% of the objects should be dumped but should also make sure that you can more or less easily dump 99% of the objects. Roberts use case is the 1% use case. Especially for the 99% case however, pg_dump needs to create a full list of all available objects in whatever format as a proposal so that you could just save edit this list and then delete what you don't want instead of writing such a list from scratch. Joachim -- 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] Suggested easy TODO: pg_dump --from-list
Dmitriy Igrishin dmit...@gmail.com writes: I also want to propose to make it possible dump function definitions as CREATE OR REPLACE FUNCTION rather than just CREATE FUNCTION (as pg_dump dumps them now). It's intentional that pg_dump doesn't do that. Please don't think that pg_dump is a substitute for \ef. 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] Suggested easy TODO: pg_dump --from-list
Robert Haas robertmh...@gmail.com writes: On Wed, Nov 24, 2010 at 9:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Actually, what occurs to me to wonder is whether the facility has to be guaranteed unique at all. If for instance you have a group of overloaded functions, is there really a big use-case for dumping just one and not the whole group? Even if you think there's some use for it, is it big enough to justify a quantum jump in the complexity of the feature? Well, I think that being able to dump one specific function is a pretty important use case. But I don't see why that's necessarily irreconcilable with your suggested syntax of --function=pattern, if we assume that the pattern is being matched against pg_proc.oid::regprocedure and define the matching rules such that foo(text) will not match sfoo(text). Nothing anyone has proposed sounds like a quantum jump in complexity over your proposal. It *will* be manifestly harder to use if users have to spell the argument types just so. Consider int4 versus integer, varchar versus character varying (and not character varying(N)), etc etc. I think that leaving out the argument types is something we should very strongly consider here. BTW, what about dependencies? One of the main complaints we've heard about pg_restore's filtering features is that they are not smart about including things like the indexes of a selected table, or the objects it depends on (eg, functions referred to in CHECK constraints). I'm not sure that a pure name-based filter will be any more usable than pg_restore's filter, if there is no accounting for dependencies. I am 100% positive that it will be a big step forward. Apparently you haven't been reading pgsql-bugs and pgsql-novice for the last five or ten years. These are large problems in practice. 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] Suggested easy TODO: pg_dump --from-list
Joachim Wieland j...@mcknight.de writes: Whatever approach we use, we need to think about the use case where 1% of the objects should be dumped but should also make sure that you can more or less easily dump 99% of the objects. Roberts use case is the 1% use case. Especially for the 99% case however, pg_dump needs to create a full list of all available objects in whatever format as a proposal so that you could just save edit this list and then delete what you don't want instead of writing such a list from scratch. For that I'd suggest an --exclude=pattern switch. I'm really not happy with the idea of applying pg_restore's -l then -L workflow to dumps from a live database. It's workable for pg_restore because the dump file doesn't change underneath you between the two runs. But having to make a list for pg_dump seems like a foot-gun. Imagine a DBA who wants to exclude a large log table from his dumps, so he makes a -l-like list and removes that table, sets up the cron job to use that list, and forgets about it. Months later, he finds out that his backups don't contain $critical-object-added-later. A static external list of objects to be dumped just doesn't make sense to me. 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] Suggested easy TODO: pg_dump --from-list
On Wed, Nov 24, 2010 at 10:45 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Nov 24, 2010 at 9:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Actually, what occurs to me to wonder is whether the facility has to be guaranteed unique at all. If for instance you have a group of overloaded functions, is there really a big use-case for dumping just one and not the whole group? Even if you think there's some use for it, is it big enough to justify a quantum jump in the complexity of the feature? Well, I think that being able to dump one specific function is a pretty important use case. But I don't see why that's necessarily irreconcilable with your suggested syntax of --function=pattern, if we assume that the pattern is being matched against pg_proc.oid::regprocedure and define the matching rules such that foo(text) will not match sfoo(text). Nothing anyone has proposed sounds like a quantum jump in complexity over your proposal. It *will* be manifestly harder to use if users have to spell the argument types just so. Consider int4 versus integer, varchar versus character varying (and not character varying(N)), etc etc. I think that leaving out the argument types is something we should very strongly consider here. I don't see why this is an either/or question. Can't we make them optional? BTW, what about dependencies? One of the main complaints we've heard about pg_restore's filtering features is that they are not smart about including things like the indexes of a selected table, or the objects it depends on (eg, functions referred to in CHECK constraints). I'm not sure that a pure name-based filter will be any more usable than pg_restore's filter, if there is no accounting for dependencies. I am 100% positive that it will be a big step forward. Apparently you haven't been reading pgsql-bugs and pgsql-novice for the last five or ten years. These are large problems in practice. That seems like a cheap shot, since you already know that I haven't been reading any of the mailing lists for that long. I have, however, been using PostgreSQL for that long, and I've hit this problem myself. I don't say that being able to dump an exact object and nothing more will solve every use case, but I do say it's useful, at least to me. I've wanted it many times. -- 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] Suggested easy TODO: pg_dump --from-list
Robert Haas robertmh...@gmail.com writes: On Wed, Nov 24, 2010 at 10:45 AM, Tom Lane t...@sss.pgh.pa.us wrote: It *will* be manifestly harder to use if users have to spell the argument types just so. Consider int4 versus integer, varchar versus character varying (and not character varying(N)), etc etc. I think that leaving out the argument types is something we should very strongly consider here. I don't see why this is an either/or question. Can't we make them optional? That might work. 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
[HACKERS] Suggested easy TODO: pg_dump --from-list
Hackers, I just thought of a good feature to put on the [E] list for TODO, if people agree: a switch to pg_dump called --from-list which would take a filename and dump the list of objects in that file. Objections to putting this on the TODO? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- 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] Suggested easy TODO: pg_dump --from-list
On 11/23/2010 09:33 PM, Josh Berkus wrote: Hackers, I just thought of a good feature to put on the [E] list for TODO, if people agree: a switch to pg_dump called --from-list which would take a filename and dump the list of objects in that file. Objections to putting this on the TODO? Well, very little about pg_dump is very [E], IMNSHO. The question in my mind here is what format the list file will take. For example, how would we specify a function? Would we need to specify all the argument types (or at least the IN arguments)? It's not as easy as a list with pg_restore, which is just a list of TOC ids, and all the rest is just a comment in the list file. I certainly don't think we should put this on the list without at least having the idea fleshed out some more. cheers andrew -- 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] Suggested easy TODO: pg_dump --from-list
On Tue, Nov 23, 2010 at 10:24 PM, Andrew Dunstan and...@dunslane.net wrote: Well, very little about pg_dump is very [E], IMNSHO. The question in my mind here is what format the list file will take. For example, how would we specify a function? Would we need to specify all the argument types (or at least the IN arguments)? It's not as easy as a list with pg_restore, which is just a list of TOC ids, and all the rest is just a comment in the list file. I certainly don't think we should put this on the list without at least having the idea fleshed out some more. I think the list should be generated by pg_dump itself in a first run, by building a complete TOC and then dumping a pg_restore -l like list format (without dumpIds) where the user just deletes the objects that he doesn't want to get dumped. The list wouldn't contain dumpIds, but catalogIds and those should be sufficiently unique and easy to parse and compare. Joachim -- 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] Suggested easy TODO: pg_dump --from-list
. Well, very little about pg_dump is very [E], IMNSHO. The question in my mind here is what format the list file will take I was thinking same format as pg_restore -l, only without the dumpIDs. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- 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] Suggested easy TODO: pg_dump --from-list
Josh Berkus j...@agliodbs.com writes: Well, very little about pg_dump is very [E], IMNSHO. The question in my mind here is what format the list file will take I was thinking same format as pg_restore -l, only without the dumpIDs. Nope ... those strings are just helpful comments, they aren't really guaranteed to be unique identifiers. In any case, it seems unlikely that a user could expect to get the more complicated cases exactly right other than by consulting pg_dump | pg_restore -l output. Which makes the use-case kind of dubious to me. I don't say that this wouldn't be a useful feature, but you need a better spec than this. 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
