Re: [HACKERS] TODO: Add pg_get_acldef(),
Joshua D. Drake wrote: > Tom Lane wrote: > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >> Trying to get back on point. What is the scope of work for the TODO > >> item? Forget everything else I brought up. What is the goal of the > >> existing TODO? > > > > I'm not sure that the TODO item has a reason to live at all, but surely > > the first item of work for it should be to figure out what its use-case > > is. If pg_dump isn't going to use these functions, what will? > > Well I can't think of a reason to use the functions as a way to deliver > CREATE statements. > > Anyone else have thoughts? They seem useful because they allow abstract access to system information without mucking with the system tables. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Joshua D. Drake wrote: > What is easier? > > test=# select column_name, data_type from columns where table_schema != > 'pg_catalog' and table_name = 'email'; \d email So, would you change psql's \d logic to use the new function? While answering that, consider that you'd lose the ability to query old servers that don't have the function. And before you argue that this is psql-specific, consider that the (e.g.) pgAdmin developers already use a query to display table information; what would be the purpose of changing that query? What use would be for users to be able to call a function in pgAdmin's query tool, when they can get the result more easily by using the specific show-me-the-table feature? Your query is wrong schema-wise anyway, because it'll mix the columns from any table named email in any schema (except the only one where it's pretty unlikely that there'll be a table named email). ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(),
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > One thing that I think should be clarified... why wouldn't pg_dump be > able to use these functions? Is it because of version compatability? This has already been gone over more than once in this thread, let alone the prior one, but here are some reasons: * Unless you want to abandon pg_dump support for prior server versions, these functions won't really help it. The pg_dump code is structured to assemble CREATE commands from spare parts; it would be a real mess to do things significantly differently for some server versions than others. It certainly wouldn't make pg_dump any smaller or simpler. * pg_dump doesn't always *want* a unified CREATE command, eg it has to be able to pull apart tables and their constraints and even sometimes their default expressions, both for performance and to break circular references. * The existing approach makes it possible for pg_dump to adjust syntax for newer releases when dumping from an older server; functions inside the older server couldn't do that. Similarly, the existing approach sometimes makes it possible for pg_dump to work around bugs in older servers. We've done both these things in the past and it's reasonable to assume we'll need to again. * Functions inside the backend are probably going to use SnapshotNow semantics for inspecting the catalogs. (Avoiding this would generally mean not depending on *any* existing backend infrastructure such as catcaches.) This is very bad from pg_dump's standpoint because pg_dump wants to deliver a consistent snapshot of the way things were when it started its transaction. Arguably, all the places where pg_dump already depends on backend functions are broken. (This isn't too bad for table- related stuff, because pg_dump acquires AccessShareLock which blocks table DDL changes before it does anything with a table, but I worry about it in connection with types and functions and so on.) Basically, going over to a scheme where most of pg_dump's smarts are in the backend would eliminate the strategy we often recommend of "use the newer pg_dump when dumping from your older server in preparation for a major version upgrade". The newer pg_dump couldn't do anything different from the older one, if both are mostly reliant on code inside the older server. We've had to recommend that approach often enough that I am not pleased by the prospect of giving up the option in future. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(),
Before we pull pg_dump to bits let's identify some actual benefit from doing so. If you look at the code you will see that it is more than somewhat complex. A large scale move like you are proposing would be very high risk, IMNSHO. From a person who deals with customer migrations daily perspective. Anything that is going to put the stability and integrity of pg_dump/pg_restore in *any* way, is a no op. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(),
Jim C. Nasby wrote: The only reason I've been able to think of for why pg_dump wouldn't use a *back end* function for this is because it would then be limited to dumping in the format provided by that backend, which could become an issue when upgrading. If that is in fact a problem, it might be useful to break the code that pg_dump uses for generating DDL into it's own library that others could include. But even if that is done, I still feel that these functions should be added to the backend. Jim, you referred to previous debate in one of your postings. There has been previous debate on this issue too. Before we pull pg_dump to bits let's identify some actual benefit from doing so. If you look at the code you will see that it is more than somewhat complex. A large scale move like you are proposing would be very high risk, IMNSHO. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > Trying to get back on point. What is the scope of work for the TODO > > item? Forget everything else I brought up. What is the goal of the > > existing TODO? > > I'm not sure that the TODO item has a reason to live at all, but surely > the first item of work for it should be to figure out what its use-case > is. If pg_dump isn't going to use these functions, what will? The original motivation for the functions was that we already have some functions like this in the backend, and they are used by pg_dump, so for completeness someone suggested they should be added, and then we can decide if pg_dump should use them, but we can revisit all of this. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(),
On Mon, Jun 12, 2006 at 08:49:00AM -0400, Andrew Dunstan wrote: > Yes ... except that I don't see any good reason to have these in a > contrib module and keep, say, pg_get_viewdef() in core. They belong > together, I think, and I don't think they represent so much bloat that > having them in core would be a huge problem. Either way, pg_dump should > not use them, I think. One reason pg_dump should not use them is that > creation might involve several things which it would want to split up > for reasons of efficiency and robustness, e.g. delaying creation of a > constraint until after data is loaded. I would argue that any case you come up with for why pg_dump shouldn't use them is most likely going to be an issue for people using these functions as well, so the functions should handle both. In this case, it would mean adding a 'without_constraints' option to pg_get_tabledef, and then the appropriate pg_get_tableconstaintdef() functions. The only reason I've been able to think of for why pg_dump wouldn't use a *back end* function for this is because it would then be limited to dumping in the format provided by that backend, which could become an issue when upgrading. If that is in fact a problem, it might be useful to break the code that pg_dump uses for generating DDL into it's own library that others could include. But even if that is done, I still feel that these functions should be added to the backend. Actually, putting the functionality into a library that's used by both pg_dump and these functions probably makes the most sense. The library would have to handle multiple server versions, but the functions would just pass in the current server version. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(),
Mark Kirkwood wrote: Jim C. Nasby wrote: Here's the relevant thread: http://archives.postgresql.org/pgsql-hackers/2005-12/msg00756.php The intention is to flesh out the existing pg_get_blahdef functions, such as pg_get_viewdef(). This clearly means that the functions should output a complete CREATE command. Ok, good point, if I'm writing some admin or data movement package, then these guys would be great! I guess a possible compromise for those who want to keep the core backend lean is to implement pg_get_blahdef (and friends) in a contrib module similar to (or part of) the adminpack stuff. This would mean that pg_dump would *not* use them - but if I've followed this thread properly, that may be fine. Yes ... except that I don't see any good reason to have these in a contrib module and keep, say, pg_get_viewdef() in core. They belong together, I think, and I don't think they represent so much bloat that having them in core would be a huge problem. Either way, pg_dump should not use them, I think. One reason pg_dump should not use them is that creation might involve several things which it would want to split up for reasons of efficiency and robustness, e.g. delaying creation of a constraint until after data is loaded. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(),
Jim C. Nasby wrote: On Mon, Jun 12, 2006 at 03:47:13PM +1200, Mark Kirkwood wrote: Keeping 'em separate makes sense to me: 1/ API (or info schema views) provides the required data (e.g column details for a table). 2/ client (e.g. pg_dump) decides what to do with it (e.g. construct a CREATE statement from the column details). Which means that every other client that wants to generate DDL statements has to go through a lot of work to get it right. And then they have to maintain it for every version of PostgreSQL. I think it makes a heck of a lot more sense to have a backend function to do this. Here's the relevant thread: http://archives.postgresql.org/pgsql-hackers/2005-12/msg00756.php The intention is to flesh out the existing pg_get_blahdef functions, such as pg_get_viewdef(). This clearly means that the functions should output a complete CREATE command. Ok, good point, if I'm writing some admin or data movement package, then these guys would be great! I guess a possible compromise for those who want to keep the core backend lean is to implement pg_get_blahdef (and friends) in a contrib module similar to (or part of) the adminpack stuff. This would mean that pg_dump would *not* use them - but if I've followed this thread properly, that may be fine. Best wishes Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(),
On Mon, Jun 12, 2006 at 03:47:13PM +1200, Mark Kirkwood wrote: > Joshua D. Drake wrote: > >Tom Lane wrote: > >>"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >>>Trying to get back on point. What is the scope of work for the TODO > >>>item? Forget everything else I brought up. What is the goal of the > >>>existing TODO? > >> > >>I'm not sure that the TODO item has a reason to live at all, but surely > >>the first item of work for it should be to figure out what its use-case > >>is. If pg_dump isn't going to use these functions, what will? > > > >Well I can't think of a reason to use the functions as a way to deliver > >CREATE statements. > > > >Anyone else have thoughts? > > Keeping 'em separate makes sense to me: > > 1/ API (or info schema views) provides the required data (e.g column > details for a table). > 2/ client (e.g. pg_dump) decides what to do with it (e.g. construct a > CREATE statement from the column details). Which means that every other client that wants to generate DDL statements has to go through a lot of work to get it right. And then they have to maintain it for every version of PostgreSQL. I think it makes a heck of a lot more sense to have a backend function to do this. Here's the relevant thread: http://archives.postgresql.org/pgsql-hackers/2005-12/msg00756.php The intention is to flesh out the existing pg_get_blahdef functions, such as pg_get_viewdef(). This clearly means that the functions should output a complete CREATE command. One thing that I think should be clarified... why wouldn't pg_dump be able to use these functions? Is it because of version compatability? Also (and I'm not pointing fingers at Bruce here), this is yet another case where if we had a TODO system that actually captured the relevant information, we all would have saved a bunch of time here debating how these functions should work. I really hope there will be a discussion about this at the anniversary. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(),
Joshua D. Drake wrote: Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: Trying to get back on point. What is the scope of work for the TODO item? Forget everything else I brought up. What is the goal of the existing TODO? I'm not sure that the TODO item has a reason to live at all, but surely the first item of work for it should be to figure out what its use-case is. If pg_dump isn't going to use these functions, what will? Well I can't think of a reason to use the functions as a way to deliver CREATE statements. Anyone else have thoughts? Keeping 'em separate makes sense to me: 1/ API (or info schema views) provides the required data (e.g column details for a table). 2/ client (e.g. pg_dump) decides what to do with it (e.g. construct a CREATE statement from the column details). Cheers Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: Trying to get back on point. What is the scope of work for the TODO item? Forget everything else I brought up. What is the goal of the existing TODO? I'm not sure that the TODO item has a reason to live at all, but surely the first item of work for it should be to figure out what its use-case is. If pg_dump isn't going to use these functions, what will? Well I can't think of a reason to use the functions as a way to deliver CREATE statements. Anyone else have thoughts? Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Trying to get back on point. What is the scope of work for the TODO > item? Forget everything else I brought up. What is the goal of the > existing TODO? I'm not sure that the TODO item has a reason to live at all, but surely the first item of work for it should be to figure out what its use-case is. If pg_dump isn't going to use these functions, what will? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Hello, Trying to get back on point. What is the scope of work for the TODO item? Forget everything else I brought up. What is the goal of the existing TODO? Is it to return the CREATE statements for each (where applicable)? Is it just to create backend versions of the the identical functions in pg_dump? Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Alvaro Herrera wrote: Joshua D. Drake wrote: Name and datatype was just an example. I am trying to get people to actually provide feedback (thank you). Andrew brought up that also including the constraints would be a good idea which I agree. You also need rules, triggers, inheritance, indexes, primary key specification, foreign keys, default values, CHECK constraints, storage configuration (i.e., "plain", "extended", etc), statistics configuration. Maybe I'm still missing something. How do you do all that with a single result set? My argument is to find a way to make it a little easier for application and API developers. Most of those people will not need the storage configuration or the statistics. Nor will they likely (although less powerful of an argument) need the foreign key information. Default values? Maybe. Check constraints o.k. It is certainly possible to build a function to return all of this in a result set. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: If all you want is column, datatype, why not just use info_schema, or newsysviews? Or even the base catalogs? Where do I look in the info_schema? How do I know exactly what I need? What is newsysviews? Exactly the same arguments can be made against any new functions we invent. OTOH, I do not think these arguments apply to selecting from information_schema; that is SQL standard, and if someone doesn't know what to do with it I don't think it's our fault. I am not blaming us :). I am just saying that certain functions can make life easier. What is easier? test=# select column_name, data_type from columns where table_schema != 'pg_catalog' and table_name = 'email'; column_name | data_type -+--- score | real Or: select pg_get_user_tabledesc(email); This is the basis of my argument. I don't really have anything to add. :) Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Joshua D. Drake wrote: > Name and datatype was just an example. I am trying to get people to > actually provide feedback (thank you). Andrew brought up that also > including the constraints would be a good idea which I agree. You also need rules, triggers, inheritance, indexes, primary key specification, foreign keys, default values, CHECK constraints, storage configuration (i.e., "plain", "extended", etc), statistics configuration. Maybe I'm still missing something. How do you do all that with a single result set? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: >> If all you want is column, datatype, why not just use info_schema, or >> newsysviews? Or even the base catalogs? > Where do I look in the info_schema? How do I know exactly what I need? > What is newsysviews? Exactly the same arguments can be made against any new functions we invent. OTOH, I do not think these arguments apply to selecting from information_schema; that is SQL standard, and if someone doesn't know what to do with it I don't think it's our fault. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
CREATE TABLE foo (id serial); I mean, I can do either but I would like to get a clear definition of what we are looking for here. Maybe: pg_get_tabledef is the actual SQL and pg_get_tabledesc() is the column, datatype output? I guess I don't see the advantage of putting pg_dump -s -t in the backend. If all you want is column, datatype, why not just use info_schema, or newsysviews? Or even the base catalogs? Where do I look in the info_schema? How do I know exactly what I need? What is newsysviews? Of course I know the answers to these but many people don't. Newsysviews is a no-op unless it is in the backend (will it be for 8.2?). Secondly in a email I just sent I did say we can add anything we want, but the CREATE TABLE statement doesn't seem that useful. I will create either or both I don't really care :). ISTM what would be of the most value is a way to get the actual DDL you need to create the table (which includes a heck of a lot more than just column names and data types). Name and datatype was just an example. I am trying to get people to actually provide feedback (thank you). Andrew brought up that also including the constraints would be a good idea which I agree. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Well, I certainly don't think a setof is adequate for pg_get_tabledef(). What about constraints? And what you are suggesting can probably be got by very simple queries against either the catalog or the information schema, and seems to me to have little value. Well it isn't simple queries because they aren't documented. It is a lot easier to say, select pg_get_tabledesc('foo') then a select with 3 different joins and a couple of where clauses (I actually don't think it is that bad. I have a query that does it.) What I am suggesting is that we have a standard way for APIs to get information that they need. The information doesn't need to be limited to just the name and type, we could add cosntraint info. I am not against that at all. Anyway, I suggest having both functions. One that will spit out the actual create information, and the other set that gives user space usable information. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Joshua D. Drake wrote: Well, the argument against changing pg_dump is that it would impact the ability to use the newer version of pg_dump with older backends (which would be lacking these functions). ISTM what would be best is to add the functions to the backend, and add a TODO or comments to pg_dump indicating that it should be changed to use these functions once 8.1 is no longer supported. Or you could make pg_dump's use of this code dependent on the server version it connected to. Off list I was speaking with AndrewD and he said that he would expect that if we called pg_get_tabledef() it should return the CREATE statement for the table. With all due respect to Andrew, why? At least in my mind these functions really belong to app developers.. e.g; CREATE TABLE foo (id serial); SELECT pg_get_tabledef(foo) would return id, serial Not: CREATE TABLE foo (id serial); I mean, I can do either but I would like to get a clear definition of what we are looking for here. Maybe: pg_get_tabledef is the actual SQL and pg_get_tabledesc() is the column, datatype output? I guess I don't see the advantage of putting pg_dump -s -t in the backend. Well, I certainly don't think a setof is adequate for pg_get_tabledef(). What about constraints? And what you are suggesting can probably be got by very simple queries against either the catalog or the information schema, and seems to me to have little value. As for whether or not it belongs in the backend, I don't have strong feelings - maybe we could add what I'm suggesting as some \ commands in psql - that would certainly be adequate for the purpose I had in mind, but might not suit users of higher end design tools. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
On Sat, Jun 10, 2006 at 08:20:15PM -0700, Joshua D. Drake wrote: > > > >Well, the argument against changing pg_dump is that it would impact the > >ability to use the newer version of pg_dump with older backends (which > >would be lacking these functions). > > > >ISTM what would be best is to add the functions to the backend, and add > >a TODO or comments to pg_dump indicating that it should be changed to > >use these functions once 8.1 is no longer supported. Or you could make > >pg_dump's use of this code dependent on the server version it connected > >to. > > Off list I was speaking with AndrewD and he said that he would expect > that if we called pg_get_tabledef() it should return the CREATE > statement for the table. > > With all due respect to Andrew, why? At least in my mind these functions > really belong to app developers.. e.g; > > CREATE TABLE foo (id serial); > > SELECT pg_get_tabledef(foo) would return > > id, serial > > Not: > > CREATE TABLE foo (id serial); > > I mean, I can do either but I would like to get a clear definition of > what we are looking for here. Maybe: > > pg_get_tabledef is the actual SQL and pg_get_tabledesc() is the column, > datatype output? > > I guess I don't see the advantage of putting pg_dump -s -t in the backend. If all you want is column, datatype, why not just use info_schema, or newsysviews? Or even the base catalogs? ISTM what would be of the most value is a way to get the actual DDL you need to create the table (which includes a heck of a lot more than just column names and data types). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Well, the argument against changing pg_dump is that it would impact the ability to use the newer version of pg_dump with older backends (which would be lacking these functions). ISTM what would be best is to add the functions to the backend, and add a TODO or comments to pg_dump indicating that it should be changed to use these functions once 8.1 is no longer supported. Or you could make pg_dump's use of this code dependent on the server version it connected to. Off list I was speaking with AndrewD and he said that he would expect that if we called pg_get_tabledef() it should return the CREATE statement for the table. With all due respect to Andrew, why? At least in my mind these functions really belong to app developers.. e.g; CREATE TABLE foo (id serial); SELECT pg_get_tabledef(foo) would return id, serial Not: CREATE TABLE foo (id serial); I mean, I can do either but I would like to get a clear definition of what we are looking for here. Maybe: pg_get_tabledef is the actual SQL and pg_get_tabledesc() is the column, datatype output? I guess I don't see the advantage of putting pg_dump -s -t in the backend. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Tom Lane said: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: >> O.k. so now what I am getting from this thread is, the functions exist >> now in pg_dump but we want to pull them out of pg_dump and push them >> into the backend? > > That's exactly what I *don't* want to do. If you can think of a > use-case for these functions outside of pg_dump, feel free to put them > in the backend, but pg_dump should continue to do things as it does > now. > ISTR we debated this some time ago and decided that it wasn't a good idea for pg_dump. I certainly agree with Tom about it. But I think there is almost certainly a good use case for these apart from pg_dump. I recall many years ago using IBMs QMF facility that would provide skeleton select for a table, and maybe it gave a create query too (it was about 20 years ago, so my memory is not perfect). I have sometimes wished we had such a thing for use in C&P query construction. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
On Sat, Jun 10, 2006 at 07:33:54PM -0400, Bruce Momjian wrote: > Tom Lane wrote: > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > > O.k. so now what I am getting from this thread is, the functions exist > > > now in pg_dump but we want to pull them out of pg_dump and push them > > > into the backend? > > > > That's exactly what I *don't* want to do. If you can think of a > > use-case for these functions outside of pg_dump, feel free to put them > > in the backend, but pg_dump should continue to do things as it does now. > > Oh, OK, I guess. pg_dump already uses some of those functions so I > figured it should use more, but you work in that area more than I do. Well, the argument against changing pg_dump is that it would impact the ability to use the newer version of pg_dump with older backends (which would be lacking these functions). ISTM what would be best is to add the functions to the backend, and add a TODO or comments to pg_dump indicating that it should be changed to use these functions once 8.1 is no longer supported. Or you could make pg_dump's use of this code dependent on the server version it connected to. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > O.k. so now what I am getting from this thread is, the functions exist > > now in pg_dump but we want to pull them out of pg_dump and push them > > into the backend? > > That's exactly what I *don't* want to do. If you can think of a > use-case for these functions outside of pg_dump, feel free to put them > in the backend, but pg_dump should continue to do things as it does now. Oh, OK, I guess. pg_dump already uses some of those functions so I figured it should use more, but you work in that area more than I do. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: O.k. so now what I am getting from this thread is, the functions exist now in pg_dump but we want to pull them out of pg_dump and push them into the backend? That's exactly what I *don't* want to do. If you can think of a use-case for these functions outside of pg_dump, feel free to put them in the backend, but pg_dump should continue to do things as it does now. O.k. well my thought was just to implement the functions for the backend. I wasn't even aware of the pg_dump dependency. They would be very useful for application developers in general. So how about this. I can implement them and submit them for hopeful inclusion and I will let hackers argue about whether or not they need to also be in pg_dump ;). If we can go down this route, can we go back to my original post so that I insure that I develop something that you guys want? Secondly, is this something that I can do with SQL and SETOF or do you want them in C? *** I can guess some of these: pg_get_tabledef() : Would take a table name and return the columns and associated types pg_get_acldef(): Would take an object name and return the associated roles and permissions for the object pg_get_typedefault(): This one I am unsure about pg_get_attrdef(): This one I am unsure about pg_get_domaindef(): Would take the name of a domain constraint and return the definition pg_get_functionef(): Would take the name of a function and return its soure. However, a function can have the same name with different arguments, so I am a little unsure? So could I get some further definition? *** Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > O.k. so now what I am getting from this thread is, the functions exist > now in pg_dump but we want to pull them out of pg_dump and push them > into the backend? That's exactly what I *don't* want to do. If you can think of a use-case for these functions outside of pg_dump, feel free to put them in the backend, but pg_dump should continue to do things as it does now. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Joshua D. Drake wrote: > > >> Maybe I am misunderstanding the TODO (which is entirely possible due to > >> the complete lack of documentation on the feature) but I *thought* all I > >> was going to do was create 6 functions that could be called to get > >> various useful information? > >> > >> For example, pg_get_tabledef() would be a very handy function to use for > >> just about any abstracted API. As it stands now most (like Pear) create > >> their own custom queries/functions to handle it but they are more often > >> then not very innefficient. > > > > I thought the TODO item was exactly what you described: > > > > * %Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), > > pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef() > > > > We have per-server-version checks in pg_dump, so I figured the idea was > > to use more of those functions if the exist, like we do now. It is true > > that you can't modify them for old versions as easily as you can if they > > are hardcoded in pg_dump, but we our existing functions seems to work > > fine. > > > > O.k. so now what I am getting from this thread is, the functions exist > now in pg_dump but we want to pull them out of pg_dump and push them > into the backend? That's what I thought we wanted. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Maybe I am misunderstanding the TODO (which is entirely possible due to the complete lack of documentation on the feature) but I *thought* all I was going to do was create 6 functions that could be called to get various useful information? For example, pg_get_tabledef() would be a very handy function to use for just about any abstracted API. As it stands now most (like Pear) create their own custom queries/functions to handle it but they are more often then not very innefficient. I thought the TODO item was exactly what you described: * %Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef() We have per-server-version checks in pg_dump, so I figured the idea was to use more of those functions if the exist, like we do now. It is true that you can't modify them for old versions as easily as you can if they are hardcoded in pg_dump, but we our existing functions seems to work fine. O.k. so now what I am getting from this thread is, the functions exist now in pg_dump but we want to pull them out of pg_dump and push them into the backend? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Joshua D. Drake wrote: > Tom Lane wrote: > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >> So could I get some further definition? > > > > There are two fairly strong reasons for NOT trying to push more logic > > into the backend from pg_dump: > > > > 1. It would remove the freedom we currently have to make pg_dump adapt > > dumps from old servers to match newer syntax/semantics. This has saved > > our bacon more than once in the past, so it shouldn't be given up > > lightly. > > > > 2. The backend functions invariably read the catalogs under SnapshotNow > > rules, making pg_dump unable to promise a consistent snapshot to the > > extent that it relies on them. > > > O.k. color me stupid but what does what you said above have in any way > to do with what the requirements for these functions are? > > Maybe I am misunderstanding the TODO (which is entirely possible due to > the complete lack of documentation on the feature) but I *thought* all I > was going to do was create 6 functions that could be called to get > various useful information? > > For example, pg_get_tabledef() would be a very handy function to use for > just about any abstracted API. As it stands now most (like Pear) create > their own custom queries/functions to handle it but they are more often > then not very innefficient. I thought the TODO item was exactly what you described: * %Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef() We have per-server-version checks in pg_dump, so I figured the idea was to use more of those functions if the exist, like we do now. It is true that you can't modify them for old versions as easily as you can if they are hardcoded in pg_dump, but we our existing functions seems to work fine. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: So could I get some further definition? There are two fairly strong reasons for NOT trying to push more logic into the backend from pg_dump: 1. It would remove the freedom we currently have to make pg_dump adapt dumps from old servers to match newer syntax/semantics. This has saved our bacon more than once in the past, so it shouldn't be given up lightly. 2. The backend functions invariably read the catalogs under SnapshotNow rules, making pg_dump unable to promise a consistent snapshot to the extent that it relies on them. O.k. color me stupid but what does what you said above have in any way to do with what the requirements for these functions are? Maybe I am misunderstanding the TODO (which is entirely possible due to the complete lack of documentation on the feature) but I *thought* all I was going to do was create 6 functions that could be called to get various useful information? For example, pg_get_tabledef() would be a very handy function to use for just about any abstracted API. As it stands now most (like Pear) create their own custom queries/functions to handle it but they are more often then not very innefficient. ? Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef()
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > So could I get some further definition? There are two fairly strong reasons for NOT trying to push more logic into the backend from pg_dump: 1. It would remove the freedom we currently have to make pg_dump adapt dumps from old servers to match newer syntax/semantics. This has saved our bacon more than once in the past, so it shouldn't be given up lightly. 2. The backend functions invariably read the catalogs under SnapshotNow rules, making pg_dump unable to promise a consistent snapshot to the extent that it relies on them. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster