Thread: What Would You Like To Do?
Hackers, Later this week I'm giving a [brief][] for an audience of what I hope will be corporate PostgreSQL users that covers howto get a feature developed for PostgreSQL. The idea here is that there are a lot of organizations out there with verydeep commitments to PostgreSQL, who really take advantage of what it has to offer, but also would love additional featuresPostgreSQL doesn't offer. Perhaps some of them would be willing to fund development of the featured they need. [brief]: http://postgresopen.org/2011/schedule/presentations/83/ Toward the end of the presentation, I'd like to make some suggestions and offer to do some match-making. I'm thinking primarilyof listing some of the stuff the community would love to see done, along with the names of the folks and/or companieswho, with funding, might make it happen. My question for you is: What do you want to work on? Here's my preliminary list: * Integrated partitioning support: Simon/2nd Quadrant * High-CPU concurrency: Robert/Enterprise DB * Multimaster replication and clustering: Simon/2nd Quadrant * Multi-table indexes: Heiki? Oleg & Teodor? * Column-leve collation support: Peter/Enterprise DB * Faster and more fault tolerant data loading: Andrew/PGX * Automated postgresql.conf Configuration: Greg/2nd Quadrant * Parallel pg_dump: Andrew/PGX * SET GLOBAL-style configuration in SQL: Greg/2nd Quadant * Track table and index caching to improve optimizer decisions: Robert/Enterprise DB Thanks to Greg Smith for adding a few bonus ideas I hadn't thought of. What else have you got? I don't think we necessarilyhave to limit ourselves to core features, BTW: projects like PostGIS and pgAdmin are also clearly popular, andnew projects of that scope (or improvements to those!) would no doubt be welcome. Also, I'm highlighting PGXN and an exampleof how this sort of thing might work. So, what do you want to work on? Let me know, I'll do as much match-making at the conference as I can. Best, David
On sön, 2011-09-11 at 21:21 -0700, David E. Wheeler wrote: > * Column-leve collation support: Peter/Enterprise DB Column-level collation support already exists.
Hi, "David E. Wheeler" <david@kineticode.com> writes: > Thanks to Greg Smith for adding a few bonus ideas I hadn't thought of. What > else have you got? I don't think we necessarily have to limit ourselves to > core features, BTW: projects like PostGIS and pgAdmin are also clearly > popular, and new projects of that scope (or improvements to those!) would no > doubt be welcome. You could add DDL Triggers from me (2ndQuadrant) and process-based parallel loading in pgloader (currently thread based, sucks). Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Sep 12, 2011, at 6:01, Peter Eisentraut <peter_e@gmx.net> wrote: > Column-level collation support already exists. Yeah, just realized that. I mention to say table or column-level encoding. Best, David
* David E. Wheeler (david@kineticode.com) wrote:
> Toward the end of the presentation, I'd like to make some suggestions and offer to do some match-making. I'm thinking
primarilyof listing some of the stuff the community would love to see done, along with the names of the folks and/or
companieswho, with funding, might make it happen. My question for you is: What do you want to work on?
I'm not looking for funding (probably couldn't take it if I was offered
it, heh), so I'm not sure if it should be included, but I'm still
planning to dig into revamping the logging system (if I can ever manage
to get out from under my current 'real job' workload :/). If others are
interested and have time to help, please let me know..
Thanks,
Stephen
<p>On Sep 13, 2011 2:37 AM, "Stephen Frost" <<a href="mailto:sfrost@snowman.net">sfrost@snowman.net</a>> wrote:<br/> ><br /> > * David E. Wheeler (<a href="mailto:david@kineticode.com">david@kineticode.com</a>) wrote:<br/> > > Toward the end of the presentation, I'd like to make some suggestions and offer to do some match-making.I'm thinking primarily of listing some of the stuff the community would love to see done, along with the namesof the folks and/or companies who, with funding, might make it happen. My question for you is: What do you want to workon?<br /> ><br /> > I'm not looking for funding (probably couldn't take it if I was offered<br /> > it, heh),so I'm not sure if it should be included, but I'm still<br /> > planning to dig into revamping the logging system(if I can ever manage<br /> > to get out from under my current 'real job' workload :/). If others are<br /> >interested and have time to help, please let me know..<p>Definitely interested in that, yes. We probably have some overlapin our thoughts and plans, as discussed at the developer meeting in Ottawa. <p>Not specifically looking for fundingeither, but it would certainly increase the number of hours available to work on it and as such make it happen sooner...<p>/Magnus <br />
On Sep 12, 2011, at 9:41 PM, Magnus Hagander wrote: > > I'm not looking for funding (probably couldn't take it if I was offered > > it, heh), so I'm not sure if it should be included, but I'm still > > planning to dig into revamping the logging system (if I can ever manage > > to get out from under my current 'real job' workload :/). If others are > > interested and have time to help, please let me know.. > > Definitely interested in that, yes. We probably have some overlap in our thoughts and plans, as discussed at the developermeeting in Ottawa. > > Not specifically looking for funding either, but it would certainly increase the number of hours available to work on itand as such make it happen sooner… Yeah, that's the point. Best, David
On Sun, 2011-09-11 at 21:21 -0700, David E. Wheeler wrote: > Hackers, > > Later this week I'm giving a [brief][] for an audience of what I > hope will be corporate PostgreSQL users that covers how to get a > feature developed for PostgreSQL. The idea here is that there are > a lot of organizations out there with very deep commitments to > PostgreSQL, who really take advantage of what it has to offer, > but also would love additional features PostgreSQL doesn't offer. > Perhaps some of them would be willing to fund development of the featured they need. Hannu Krosing / 2ndQuadrant * more enhancements to pl/python - use real function arguments, store modules in database, direct support for postgresqltypes, operators and functions, automatic startup command, automatic ORM from table definitions, ...* varioussupport functionality for replication and automatic growth of sharded databases - user defined tuple visibility functions, triggers for DDL and ON COMMIT/ON ROLLBACK, ...* putting time travel (which Oracle calls "flashback queries")back into postgreSQL* moving tuple visibility in a separate index-like structure which should be highly compressiblein most cases, as a way to enabling index-only scans, column oriented storage and effective table compression,... > [brief]: http://postgresopen.org/2011/schedule/presentations/83/ > > Toward the end of the presentation, I'd like to make some suggestions and offer to do some match-making. I'm thinking primarilyof listing some of the stuff the community would love to see done, along with the names of the folks and/or companieswho, with funding, might make it happen. My question for you is: What do you want to work on? > > Here's my preliminary list: > > * Integrated partitioning support: Simon/2nd Quadrant > * High-CPU concurrency: Robert/Enterprise DB > * Multimaster replication and clustering: Simon/2nd Quadrant > * Multi-table indexes: Heiki? Oleg & Teodor? > * Column-leve collation support: Peter/Enterprise DB > * Faster and more fault tolerant data loading: Andrew/PGX > * Automated postgresql.conf Configuration: Greg/2nd Quadrant > * Parallel pg_dump: Andrew/PGX > * SET GLOBAL-style configuration in SQL: Greg/2nd Quadant > * Track table and index caching to improve optimizer decisions: Robert/Enterprise DB > > Thanks to Greg Smith for adding a few bonus ideas I hadn't thought of. What else have you got? I don't think we necessarilyhave to limit ourselves to core features, BTW: projects like PostGIS and pgAdmin are also clearly popular, andnew projects of that scope (or improvements to those!) would no doubt be welcome. Also, I'm highlighting PGXN and an exampleof how this sort of thing might work. > > So, what do you want to work on? Let me know, I'll do as much match-making at the conference as I can. > > Best, > > David > > >
On Sep 13, 2011, at 9:43 AM, Hannu Krosing wrote: > Hannu Krosing / 2ndQuadrant > > * more enhancements to pl/python - use real function arguments, > store modules in database, direct support for postgresql types, > operators and functions, automatic startup command, > automatic ORM from table definitions, ... > * various support functionality for replication and automatic growth > of sharded databases - user defined tuple visibility functions, > triggers for DDL and ON COMMIT/ON ROLLBACK, ... > * putting time travel (which Oracle calls "flashback queries") back > into postgreSQL > * moving tuple visibility in a separate index-like structure which > should be highly compressible in most cases, as a way to enabling > index-only scans, column oriented storage and effective table > compression, ... Awesome, thanks! David
On 12 September 2011 05:21, David E. Wheeler <david@kineticode.com> wrote: > Hackers, > > Later this week I'm giving a [brief][] for an audience of what I hope will be corporate PostgreSQL users that covers howto get a feature developed for PostgreSQL. The idea here is that there are a lot of organizations out there with verydeep commitments to PostgreSQL, who really take advantage of what it has to offer, but also would love additional featuresPostgreSQL doesn't offer. Perhaps some of them would be willing to fund development of the featured they need. > > [brief]: http://postgresopen.org/2011/schedule/presentations/83/ > > Toward the end of the presentation, I'd like to make some suggestions and offer to do some match-making. I'm thinking primarilyof listing some of the stuff the community would love to see done, along with the names of the folks and/or companieswho, with funding, might make it happen. My question for you is: What do you want to work on? > > Here's my preliminary list: > > * Integrated partitioning support: Simon/2nd Quadrant > * High-CPU concurrency: Robert/Enterprise DB > * Multimaster replication and clustering: Simon/2nd Quadrant > * Multi-table indexes: Heiki? Oleg & Teodor? > * Column-leve collation support: Peter/Enterprise DB > * Faster and more fault tolerant data loading: Andrew/PGX > * Automated postgresql.conf Configuration: Greg/2nd Quadrant > * Parallel pg_dump: Andrew/PGX > * SET GLOBAL-style configuration in SQL: Greg/2nd Quadant > * Track table and index caching to improve optimizer decisions: Robert/Enterprise DB > > Thanks to Greg Smith for adding a few bonus ideas I hadn't thought of. What else have you got? I don't think we necessarilyhave to limit ourselves to core features, BTW: projects like PostGIS and pgAdmin are also clearly popular, andnew projects of that scope (or improvements to those!) would no doubt be welcome. Also, I'm highlighting PGXN and an exampleof how this sort of thing might work. > > So, what do you want to work on? Let me know, I'll do as much match-making at the conference as I can. I have a wish-list of features, but I don't know of anyone specific who could work on them. In addition to some you've mentioned they are: * Distributed queries * Multi-threaded query operations (single queries making use of more than 1 core in effect) * Stored procedures * Automatic failover re-subscription (okay, I don't know what you'd call this, but where you have several standbys, the primary fails, one standby is automatically promoted, and the remaining standbys automatically subscribe to the newly-promoted one without needing a new base backup) * ROLLUP and CUBE * pg_dumpall custom format (Guillaume mentioned this was on his to-do list previously) -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
The lists all seem to be focusing on the things that the developers would like to add to PostgreSQL, what about some thingsthat users or ISPs might like to have, and thus perhaps something that companies might actually see as worth funding?<br/><br />For example:<br /><br />A fully integrated ability to query across multiple databases,possibly on multipleservers, something Oracle has had for nearly two decades. <br /><br />Complete isolation at the user level, allowingan ISP to support multiple independent customers on a server without having to fiddle with multiple back ends eachrunning on a separate port, a feature that MySQL has had for as far back as I can recall, and one of the reasons ISPsare more likely to offer MySQL than PostgreSQL.<br /><br />The ability to restore a table from a backup file to a differenttable name in the same database and schema. <br /><br />A built-in report writer, capable of things like columntotals. (SqlPlus has this, even though it isn't very pretty.)<br /> --<br />Mike Nolan<br /><br /><br /><br /><br/>
On 09/13/2011 10:13 AM, Michael Nolan wrote: > The lists all seem to be focusing on the things that the developers > would like to add to PostgreSQL, what about some things that users or > ISPs might like to have, and thus perhaps something that companies might > actually see as worth funding? Well just my own two cents ... but it all depends on who is doing the funding. At this point 80% of the work CMD codes for Pg (or tertiary projects and modules) is funded by companies. So let's not assume that companies aren't funding things. They are. > > For example: > > A fully integrated ability to query across multiple databases,possibly > on multiple servers, something Oracle has had for nearly two decades. That isn't the approach to take. The fact that Oracle has it is not a guarantee that it is useful or good. If you need to query across databases (assuming within the same cluster) then you designed your database wrong and should have used our SCHEMA support (what Oracle calls Namespaces) instead. > > Complete isolation at the user level, allowing an ISP to support > multiple independent customers on a server without having to fiddle with > multiple back ends each running on a separate port, a feature that MySQL > has had for as far back as I can recall, and one of the reasons ISPs are > more likely to offer MySQL than PostgreSQL. Now this would definitely be nice. It is frustrating that we don't have per database users. > > The ability to restore a table from a backup file to a different table > name in the same database and schema. > This can be done but agreed it is not intuitive. > A built-in report writer, capable of things like column totals. > (SqlPlus has this, even though it isn't very pretty.) There are a billion and one tools that do this without us having to reinvent the wheel. Why would we support that? Sincerely, Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579
On Tue, Sep 13, 2011 at 12:26 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
But perhaps if a few 'commercial' features were on the wish list there would be more companies willing to fund development? The developers get a bit of what they want to work on, the production users get a bit of what they need, everybody's happy.
This is the difference between developers and real world users. Real world users may not have the ability, time or resources to redesign their databases just because that's the 'best' way to do something. Will it be the most efficient way to do it? Almost certainly not.
I've been involved in a few corporate mergers, and there was a short term need to do queries on the combined databases while the tiger team handling the IT restructuring figured out how (or whether) to merge the dabases together. (One of these happened to be an Oracle/Oracle situation, it was a piece of cake even though the two data centers were 750 miles apart and the table structures had almost nothing in common. Another was a two week headache, the third was even worse!)
In a perfect world, it would be nice if one could do combined queries linking a PostgreSQL database with an Oracle one, or a MySQL one, too. Because sometimes, that's what you gotta do. Even something that is several hundred times slower is going to be faster than merging the databases together. When I do this today, I have to write a program (in perl or php) that accesses both databases and merges it by hand.
Can you elaborate on tha a bit, please? The only way I've been able to do it is to edit the dump file to change the table name. That's not very practical with a several gigabyte dump file, even less so with one that is much larger. If this capability already exists, is it documented?
There are other databases out there, too, why reinvent the wheel by working on PostgreSQL? :-)
The question shoud be, would this be USEFUL?
--
Mike Nolan
Well just my own two cents ... but it all depends on who is doing the funding. At this point 80% of the work CMD codes for Pg (or tertiary projects and modules) is funded by companies. So let's not assume that companies aren't funding things. They are.
On 09/13/2011 10:13 AM, Michael Nolan wrote:The lists all seem to be focusing on the things that the developers
would like to add to PostgreSQL, what about some things that users or
ISPs might like to have, and thus perhaps something that companies might
actually see as worth funding?
But perhaps if a few 'commercial' features were on the wish list there would be more companies willing to fund development? The developers get a bit of what they want to work on, the production users get a bit of what they need, everybody's happy.
That isn't the approach to take. The fact that Oracle has it is not a guarantee that it is useful or good. If you need to query across databases (assuming within the same cluster) then you designed your database wrong and should have used our SCHEMA support (what Oracle calls Namespaces) instead.For example:
A fully integrated ability to query across multiple databases,possibly
on multiple servers, something Oracle has had for nearly two decades.
This is the difference between developers and real world users. Real world users may not have the ability, time or resources to redesign their databases just because that's the 'best' way to do something. Will it be the most efficient way to do it? Almost certainly not.
I've been involved in a few corporate mergers, and there was a short term need to do queries on the combined databases while the tiger team handling the IT restructuring figured out how (or whether) to merge the dabases together. (One of these happened to be an Oracle/Oracle situation, it was a piece of cake even though the two data centers were 750 miles apart and the table structures had almost nothing in common. Another was a two week headache, the third was even worse!)
In a perfect world, it would be nice if one could do combined queries linking a PostgreSQL database with an Oracle one, or a MySQL one, too. Because sometimes, that's what you gotta do. Even something that is several hundred times slower is going to be faster than merging the databases together. When I do this today, I have to write a program (in perl or php) that accesses both databases and merges it by hand.
This can be done but agreed it is not intuitive.
The ability to restore a table from a backup file to a different table
name in the same database and schema.
Can you elaborate on tha a bit, please? The only way I've been able to do it is to edit the dump file to change the table name. That's not very practical with a several gigabyte dump file, even less so with one that is much larger. If this capability already exists, is it documented?
There are a billion and one tools that do this without us having to reinvent the wheel. Why would we support that?(SqlPlus has this, even though it isn't very pretty.)A built-in report writer, capable of things like column totals.
There are other databases out there, too, why reinvent the wheel by working on PostgreSQL? :-)
The question shoud be, would this be USEFUL?
--
Mike Nolan
On 09/13/2011 03:51 PM, Michael Nolan wrote:
Check http://wiki.postgresql.org/wiki/Foreign_data_wrappers
For example:Can't you do that with FDW that is present in 9.1?That isn't the approach to take. The fact that Oracle has it is not a guarantee that it is useful or good. If you need to query across databases (assuming within the same cluster) then you designed your database wrong and should have used our SCHEMA support (what Oracle calls Namespaces) instead.
A fully integrated ability to query across multiple databases,possibly
on multiple servers, something Oracle has had for nearly two decades.
This is the difference between developers and real world users. Real world users may not have the ability, time or resources to redesign their databases just because that's the 'best' way to do something. Will it be the most efficient way to do it? Almost certainly not.
I've been involved in a few corporate mergers, and there was a short term need to do queries on the combined databases while the tiger team handling the IT restructuring figured out how (or whether) to merge the dabases together. (One of these happened to be an Oracle/Oracle situation, it was a piece of cake even though the two data centers were 750 miles apart and the table structures had almost nothing in common. Another was a two week headache, the third was even worse!)
In a perfect world, it would be nice if one could do combined queries linking a PostgreSQL database with an Oracle one, or a MySQL one, too. Because sometimes, that's what you gotta do. Even something that is several hundred times slower is going to be faster than merging the databases together. When I do this today, I have to write a program (in perl or php) that accesses both databases and merges it by hand.
Check http://wiki.postgresql.org/wiki/Foreign_data_wrappers
Rodrigo Gonzalez <rjgonzale@estrads.com.ar> writes:
>> In a perfect world, it would be nice if one could do combined queries
>> linking a PostgreSQL database with an Oracle one, or a MySQL one,
> Can't you do that with FDW that is present in 9.1?
FDW provides the structure within which that will eventually be
possible, but there's no Oracle or MySQL wrapper today ... and there are
a lot of FDW restrictions that need to be worked on, too.
regards, tom lane
On 09/13/2011 11:51 AM, Michael Nolan wrote: > > The ability to restore a table from a backup file to a different > table > name in the same database and schema. > > > This can be done but agreed it is not intuitive. > > > Can you elaborate on tha a bit, please? The only way I've been able to > do it is to edit the dump file to change the table name. That's not > very practical with a several gigabyte dump file, even less so with one > that is much larger. If this capability already exists, is it documented? You use the -Fc method, extract the TOC and edit just the TOC (so you don't have to edit a multi-gig file) > > (SqlPlus has this, even though it isn't very pretty.) > > A built-in report writer, capable of things like column totals. > > There are a billion and one tools that do this without us having to > reinvent the wheel. Why would we support that? > > > There are other databases out there, too, why reinvent the wheel by > working on PostgreSQL? :-) > > The question shoud be, would this be USEFUL? Personally, I don't think so but others may disagree. Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579
On 09/13/2011 04:52 PM, Tom Lane wrote: > Rodrigo Gonzalez<rjgonzale@estrads.com.ar> writes: >>> In a perfect world, it would be nice if one could do combined queries >>> linking a PostgreSQL database with an Oracle one, or a MySQL one, >> Can't you do that with FDW that is present in 9.1? > FDW provides the structure within which that will eventually be > possible, but there's no Oracle or MySQL wrapper today ... and there are > a lot of FDW restrictions that need to be worked on, too. > > regards, tom lane > They are both listed at wiki I know there are a lot of limitations....but OP message says "Even something that is several hundred times slower is going to be faster than merging the databases together. When I do this today, I have to write a program (in perl or php) that accesses both databases and merges it by hand." Am I wrong that this is currently possible using FDW? Thanks Rodrigo Gonzalez
Rodrigo Gonzalez wrote: > On 09/13/2011 04:52 PM, Tom Lane wrote: >> FDW provides the structure within which that will eventually be >> possible, but there's no Oracle or MySQL wrapper today ... > They are both listed at wiki And here: http://www.pgxn.org/tag/foreign%20data%20wrapper/ -Kevin
On Tue, Sep 13, 2011 at 2:55 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
You use the -Fc method, extract the TOC and edit just the TOC (so you don't have to edit a multi-gig file)
On 09/13/2011 11:51 AM, Michael Nolan wrote:
The ability to restore a table from a backup file to a different
table
name in the same database and schema.
This can be done but agreed it is not intuitive.
Can you elaborate on tha a bit, please? The only way I've been able to
do it is to edit the dump file to change the table name. That's not
very practical with a several gigabyte dump file, even less so with one
that is much larger. If this capability already exists, is it documented?
That is, at best, a bit obscure. I've wondered at times if the -f tar option would have any benefits here, though it appears to have significant downsides.
A downside of either method may be that I can't predict in advance when I will want to do a restore of a single table from a backup file,
so I'd have to always use that method of generating the file.
I did propose an extension to pg_restore a couple of months ago to add an option to re-name a table as it is restored, but that seemed to have generated no interest.
Maybe an external tool that reads a pg_dump file looking for a specific table and writes that portion of the dump file to a separate file, changing the table name would be easier? It'd probably have to handle most of or all of the different pg_dump formats, but that doesn't sound like an unachievable goal.
--
Mike Nolan
A downside of either method may be that I can't predict in advance when I will want to do a restore of a single table from a backup file,
so I'd have to always use that method of generating the file.
I did propose an extension to pg_restore a couple of months ago to add an option to re-name a table as it is restored, but that seemed to have generated no interest.
Maybe an external tool that reads a pg_dump file looking for a specific table and writes that portion of the dump file to a separate file, changing the table name would be easier? It'd probably have to handle most of or all of the different pg_dump formats, but that doesn't sound like an unachievable goal.
--
Mike Nolan
On Mon, Sep 12, 2011 at 8:21 AM, David E. Wheeler <david@kineticode.com> wrote:
So, what do you want to work on? Let me know, I'll do as much match-making at the conference as I can.
Here is my list:
* Additional approximate string matching functions and index access for them using gin/gist/spgist.
* Signature indexing with gist/spgist in various fields. For example, indexing of image signatures with similar images retreival.
* Statistics collection and selectivity estimation for geometric datatypes.
With best regards,
Alexander Korotkov.
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Michael Nolan
Sent: Tuesday, September 13, 2011 11:51 AM
To: Joshua D. Drake
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] What Would You Like To Do?
On Tue, Sep 13, 2011 at 12:26 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
On 09/13/2011 10:13 AM, Michael Nolan wrote:
The lists all seem to be focusing on the things that the developers
would like to add to PostgreSQL, what about some things that users or
ISPs might like to have, and thus perhaps something that companies might
actually see as worth funding?
Well just my own two cents ... but it all depends on who is doing the funding. At this point 80% of the work CMD codes for Pg (or tertiary projects and modules) is funded by companies. So let's not assume that companies aren't funding things. They are.
But perhaps if a few 'commercial' features were on the wish list there would be more companies willing to fund development? The developers get a bit of what they want to work on, the production users get a bit of what they need, everybody's happy.
For example:
A fully integrated ability to query across multiple databases,possibly
on multiple servers, something Oracle has had for nearly two decades.
That isn't the approach to take. The fact that Oracle has it is not a guarantee that it is useful or good. If you need to query across databases (assuming within the same cluster) then you designed your database wrong and should have used our SCHEMA support (what Oracle calls Namespaces) instead.
This is the difference between developers and real world users. Real world users may not have the ability, time or resources to redesign their databases just because that's the 'best' way to do something. Will it be the most efficient way to do it? Almost certainly not.
I've been involved in a few corporate mergers, and there was a short term need to do queries on the combined databases while the tiger team handling the IT restructuring figured out how (or whether) to merge the dabases together. (One of these happened to be an Oracle/Oracle situation, it was a piece of cake even though the two data centers were 750 miles apart and the table structures had almost nothing in common. Another was a two week headache, the third was even worse!)
In a perfect world, it would be nice if one could do combined queries linking a PostgreSQL database with an Oracle one, or a MySQL one, too. Because sometimes, that's what you gotta do. Even something that is several hundred times slower is going to be faster than merging the databases together. When I do this today, I have to write a program (in perl or php) that accesses both databases and merges it by hand.
>>
Microsoft uses Linked servers.
DB/2 uses DB/2 Connect
Informix uses Informix Connect
Etc.
At CONNX, our product suite provides this ability generically from any data source collection. It is obvious why such a thing is utterly mandatory for every large business. For example:
The business purchases a CRM system for customer relationship management like SAP.
The business purchases a HCM system for Human Capital Management like Peoplesoft.
The business purchases a Manufacturing system like MAXIM for their manufacturing systems.
Etc., etc., etc.
Some of these systems may have the same database type, but it is highly unlikely that every solution to a business problem in the entire organization uses the same underlying database.
People buy or build software systems to solve their business problems. There is a low probability that each and every business problem was solved by the same sets of tools from the same vendors.
Therefore, the ability to process queries across heterogeneous systems is a fundamental business need.
The larger the company the more database systems you will find. But even teeny-tiny organizations tend to have several different database systems needed to run their business.
<<
{snip}
On Sep 13, 2011, at 3:12 PM, Kevin Grittner wrote: > And here: > > http://www.pgxn.org/tag/foreign%20data%20wrapper/ A shorter URL with more results: http://www.pgxn.org/tag/fdw Best, David
On Sep 12, 2011, at 9:50 AM, Dimitri Fontaine wrote: >> Thanks to Greg Smith for adding a few bonus ideas I hadn't thought of. What >> else have you got? I don't think we necessarily have to limit ourselves to >> core features, BTW: projects like PostGIS and pgAdmin are also clearly >> popular, and new projects of that scope (or improvements to those!) would no >> doubt be welcome. > > You could add DDL Triggers from me (2ndQuadrant) and process-based > parallel loading in pgloader (currently thread based, sucks). Got it, thanks. What about additional extension features? Got a road map for that? Best, David
Hi > [brief]: http://postgresopen.org/2011/schedule/presentations/83/ You list Job scheduling as one item here, <snip> but not here > Here's my preliminary list: Could you expand your idea about this here?
On Tue, 2011-09-13 at 10:26 -0700, Joshua D. Drake wrote: > > Complete isolation at the user level, allowing an ISP to support > > multiple independent customers on a server without having to fiddle with > > multiple back ends each running on a separate port, a feature that MySQL > > has had for as far back as I can recall, and one of the reasons ISPs are > > more likely to offer MySQL than PostgreSQL. > > Now this would definitely be nice. It is frustrating that we don't have > per database users. Hmm, what does the GUC db_user_namespace do then ? it says "Enables per-database user names." -- ------- Hannu Krosing PostgreSQL Unlimited Scalability and Performance Consultant 2ndQuadrant Nordic PG Admin Book: http://www.2ndQuadrant.com/books/
On Tue, 2011-09-13 at 17:02 -0300, Rodrigo Gonzalez wrote: > On 09/13/2011 04:52 PM, Tom Lane wrote: > > Rodrigo Gonzalez<rjgonzale@estrads.com.ar> writes: > >>> In a perfect world, it would be nice if one could do combined queries > >>> linking a PostgreSQL database with an Oracle one, or a MySQL one, > >> Can't you do that with FDW that is present in 9.1? > > FDW provides the structure within which that will eventually be > > possible, but there's no Oracle or MySQL wrapper today ... and there are > > a lot of FDW restrictions that need to be worked on, too. > > > > regards, tom lane > > > They are both listed at wiki > I know there are a lot of limitations....but OP message says "Even > something that is several hundred times slower is going to be faster > than merging the databases together. When I do this today, I have to > write a program (in perl or php) that accesses both databases and merges > it by hand." > Am I wrong that this is currently possible using FDW? Yes, to some extent. And before FDW it was also possible (to some extent) using pl/python or pl/perl to turn remote tables into set returning functions (and if really needed then you could put a view over this function and almost get "a remote view") > Thanks > > Rodrigo Gonzalez > >
On 09/14/2011 05:12 AM, Hannu Krosing wrote: > > On Tue, 2011-09-13 at 10:26 -0700, Joshua D. Drake wrote: > >>> Complete isolation at the user level, allowing an ISP to support >>> multiple independent customers on a server without having to fiddle with >>> multiple back ends each running on a separate port, a feature that MySQL >>> has had for as far back as I can recall, and one of the reasons ISPs are >>> more likely to offer MySQL than PostgreSQL. >> >> Now this would definitely be nice. It is frustrating that we don't have >> per database users. > > Hmm, what does the GUC db_user_namespace do then ? > > it says "Enables per-database user names." Last I checked (it has been a few years) that was at best, a hack. I remember either I or David Fetter? Writing about some issues with it on list but it was a long time ago. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579
On Sep 14, 2011, at 5:49 AM, Kaare Rasmussen wrote: >> [brief]: http://postgresopen.org/2011/schedule/presentations/83/ > You list Job scheduling as one item here, > <snip> > but not here >> Here's my preliminary list: > Could you expand your idea about this here? It was something suggested to me on IRC a few months ago, but I don't know who would do it. Also, I think that pgAgent mightactually offer the functionality. http://www.pgadmin.org/docs/1.4/pgagent.html Best, David
On Wed, 2011-09-14 at 07:14 -0700, Joshua D. Drake wrote: > On 09/14/2011 05:12 AM, Hannu Krosing wrote: > > > > On Tue, 2011-09-13 at 10:26 -0700, Joshua D. Drake wrote: > > > >>> Complete isolation at the user level, allowing an ISP to support > >>> multiple independent customers on a server without having to fiddle with > >>> multiple back ends each running on a separate port, a feature that MySQL > >>> has had for as far back as I can recall, and one of the reasons ISPs are > >>> more likely to offer MySQL than PostgreSQL. > >> > >> Now this would definitely be nice. It is frustrating that we don't have > >> per database users. > > > > Hmm, what does the GUC db_user_namespace do then ? > > > > it says "Enables per-database user names." > > Last I checked (it has been a few years) that was at best, a hack. I > remember either I or David Fetter? Writing about some issues with it on > list but it was a long time ago. I have never used it myself, so I can't comment on it. OTOH it may be that it actually works if said ISP uses it carefully :) > -- > Command Prompt, Inc. - http://www.commandprompt.com/ > PostgreSQL Support, Training, Professional Services and Development > The PostgreSQL Conference - http://www.postgresqlconference.org/ > @cmdpromptinc - @postgresconf - 509-416-6579 >
On Wed, Sep 14, 2011 at 9:14 AM, Joshua D. Drake <jd@commandprompt.com> wrote: > > On 09/14/2011 05:12 AM, Hannu Krosing wrote: >> >> On Tue, 2011-09-13 at 10:26 -0700, Joshua D. Drake wrote: >> >>>> Complete isolation at the user level, allowing an ISP to support >>>> multiple independent customers on a server without having to fiddle with >>>> multiple back ends each running on a separate port, a feature that MySQL >>>> has had for as far back as I can recall, and one of the reasons ISPs are >>>> more likely to offer MySQL than PostgreSQL. >>> >>> Now this would definitely be nice. It is frustrating that we don't have >>> per database users. >> >> Hmm, what does the GUC db_user_namespace do then ? >> >> it says "Enables per-database user names." > > Last I checked (it has been a few years) that was at best, a hack. I > remember either I or David Fetter? Writing about some issues with it on list > but it was a long time ago. > last time i tried it (last year), it seems broken because i couldn't log in with any user anymore... but it could be that i did something wrong so i didn't report until i could confirm but i hadn't the time and i forgot it since then -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
On Wed, Sep 14, 2011 at 12:09 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > last time i tried it (last year), it seems broken because i couldn't > log in with any user anymore... but it could be that i did something > wrong so i didn't report until i could confirm but i hadn't the time > and i forgot it since then I haven't tried it on 9.0/9.1, but I used it on a 8.4 cluster, and "it worked", with all the caveats of needing all the user@database users created correctly, and the right use of quoting, and @ in logins, etc.... The biggest being the lack of md5... Definitely not "straight forward", and users are still "global", just suffixed with an "@database" to make then "unique" between database namespaces. But I found it useful when needing to hand out "seperate" usernames for different apps because they all needed to have their own search_path and other settings set before login (yes, dumb apps, mostly odbc), and be able to have the same "userid" for different databases, using different settings... a. -- Aidan Van Dyk Create like a god, aidan@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
On Wed, Sep 14, 2011 at 11:49 AM, Aidan Van Dyk <aidan@highrise.ca> wrote: > > But I found it useful when needing to hand out "seperate" usernames > for different apps because they all needed to have their own > search_path and other settings set before login (yes, dumb apps, > mostly odbc), and be able to have the same "userid" for different > databases, using different settings... > why "before" login? in 9.0+ you can use ALTER ROLE ... IN DATABASE, doesn't it help? pre 9.0 need the db_user_namespace GUC for that, though -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
DW, All: - Update newsysviews and bundle it as an extension, and maintain it for each new version - Add median() and quartile() windowing aggregates, or similar equivalents. - work on pgTune -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 2011-09-14 17:27, David E. Wheeler wrote: > On Sep 14, 2011, at 5:49 AM, Kaare Rasmussen wrote: > >>> [brief]: http://postgresopen.org/2011/schedule/presentations/83/ >> You list Job scheduling as one item here, >> <snip> >> but not here >>> Here's my preliminary list: >> Could you expand your idea about this here? > It was something suggested to me on IRC a few months ago, but I don't know who would do it. Also, I think that pgAgentmight actually offer the functionality. > > http://www.pgadmin.org/docs/1.4/pgagent.html I would vote for inclusion of such a feature in PostgreSQL.
Joshua D. Drake wrote: > > On 09/13/2011 11:51 AM, Michael Nolan wrote: > > > > > The ability to restore a table from a backup file to a different > > table > > name in the same database and schema. > > > > > > This can be done but agreed it is not intuitive. > > > > > > Can you elaborate on tha a bit, please? The only way I've been able to > > do it is to edit the dump file to change the table name. That's not > > very practical with a several gigabyte dump file, even less so with one > > that is much larger. If this capability already exists, is it documented? > > You use the -Fc method, extract the TOC and edit just the TOC (so you > don't have to edit a multi-gig file) How does that work in practice? You dump the TOC, edit it, restore the TOC schema definition, then how do you restore the data to the renamed table? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On 09/24/2011 09:51 AM, Bruce Momjian wrote: > Joshua D. Drake wrote: >> On 09/13/2011 11:51 AM, Michael Nolan wrote: >> >>> The ability to restore a table from a backup file to a different >>> table >>> name in the same database and schema. >>> >>> >>> This can be done but agreed it is not intuitive. >>> >>> >>> Can you elaborate on tha a bit, please? The only way I've been able to >>> do it is to edit the dump file to change the table name. That's not >>> very practical with a several gigabyte dump file, even less so with one >>> that is much larger. If this capability already exists, is it documented? >> You use the -Fc method, extract the TOC and edit just the TOC (so you >> don't have to edit a multi-gig file) > How does that work in practice? You dump the TOC, edit it, restore the > TOC schema definition, then how do you restore the data to the renamed > table? > How do you extract the TOC at all? There are no tools for manipulating the TOC that I know of, and I'm not sure we should provide any. It's not documented, it's a purely internal artefact. The closest thing we have to being able to manipulate it is --list/--use-list, and those are useless for this purpose. So this method description does not compute for me either. +1 for providing a way to restore an object to a different object name. cheers andrew