Re: [HACKERS] What Would You Like To Do?

2011-09-24 Thread Bruce Momjian
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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] What Would You Like To Do?

2011-09-24 Thread Andrew Dunstan



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



--
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] What Would You Like To Do?

2011-09-15 Thread Kaare Rasmussen

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 pgAgent might actually offer the functionality.

   http://www.pgadmin.org/docs/1.4/pgagent.html

I would vote for inclusion of such a feature in PostgreSQL.

--
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] What Would You Like To Do?

2011-09-14 Thread Kaare Rasmussen

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?

--
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] What Would You Like To Do?

2011-09-14 Thread Hannu Krosing
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/


-- 
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] What Would You Like To Do?

2011-09-14 Thread Hannu Krosing
On Tue, 2011-09-13 at 17:02 -0300, Rodrigo Gonzalez wrote:
 On 09/13/2011 04:52 PM, Tom Lane wrote:
  Rodrigo Gonzalezrjgonz...@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 limitationsbut 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
 
 



-- 
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] What Would You Like To Do?

2011-09-14 Thread Joshua D. Drake


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

--
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] What Would You Like To Do?

2011-09-14 Thread David E. Wheeler
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 might actually offer the functionality.

  http://www.pgadmin.org/docs/1.4/pgagent.html

Best,

David


-- 
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] What Would You Like To Do?

2011-09-14 Thread Hannu Krosing
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
 



-- 
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] What Would You Like To Do?

2011-09-14 Thread Jaime Casanova
On Wed, Sep 14, 2011 at 9:14 AM, Joshua D. Drake j...@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

-- 
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] What Would You Like To Do?

2011-09-14 Thread Aidan Van Dyk
On Wed, Sep 14, 2011 at 12:09 PM, Jaime Casanova ja...@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,
ai...@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

-- 
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] What Would You Like To Do?

2011-09-14 Thread Jaime Casanova
On Wed, Sep 14, 2011 at 11:49 AM, Aidan Van Dyk ai...@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

-- 
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] What Would You Like To Do?

2011-09-14 Thread Josh Berkus
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

-- 
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] What Would You Like To Do?

2011-09-13 Thread Hannu Krosing
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 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, ...

 [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 primarily of listing some of the 
 stuff the community would love to see done, along with the names of the folks 
 and/or companies who, 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 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. Also, I'm highlighting PGXN and an example of 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
 
 
 



-- 
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] What Would You Like To Do?

2011-09-13 Thread David E. Wheeler
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


-- 
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] What Would You Like To Do?

2011-09-13 Thread Thom Brown
On 12 September 2011 05:21, David E. Wheeler da...@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 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.

 [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 primarily of listing some of the 
 stuff the community would love to see done, along with the names of the folks 
 and/or companies who, 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 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. Also, I'm highlighting PGXN and an example of 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

-- 
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] What Would You Like To Do?

2011-09-13 Thread Michael Nolan
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?

For example:

A fully integrated ability to query across multiple databases,possibly on
multiple servers, something Oracle has had for nearly two decades.

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.

The ability to restore a table from a backup file to a different table name
in the same database and schema.

A built-in report writer, capable of things like column totals.  (SqlPlus
has this, even though it isn't very pretty.)
--
Mike Nolan


Re: [HACKERS] What Would You Like To Do?

2011-09-13 Thread Joshua D. Drake


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

--
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] What Would You Like To Do?

2011-09-13 Thread Michael Nolan
On Tue, Sep 13, 2011 at 12:26 PM, Joshua D. Drake j...@commandprompt.comwrote:


 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.



 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?


 (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?
--
Mike Nolan


Re: [HACKERS] What Would You Like To Do?

2011-09-13 Thread Rodrigo Gonzalez

On 09/13/2011 03:51 PM, Michael Nolan wrote:

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.



Can't you do that with FDW that is present in 9.1?

Check http://wiki.postgresql.org/wiki/Foreign_data_wrappers



Re: [HACKERS] What Would You Like To Do?

2011-09-13 Thread Tom Lane
Rodrigo Gonzalez rjgonz...@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

-- 
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] What Would You Like To Do?

2011-09-13 Thread Joshua D. Drake


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

--
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] What Would You Like To Do?

2011-09-13 Thread Rodrigo Gonzalez

On 09/13/2011 04:52 PM, Tom Lane wrote:

Rodrigo Gonzalezrjgonz...@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 limitationsbut 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


--
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] What Would You Like To Do?

2011-09-13 Thread Kevin Grittner
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



-- 
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] What Would You Like To Do?

2011-09-13 Thread Michael Nolan
On Tue, Sep 13, 2011 at 2:55 PM, Joshua D. Drake j...@commandprompt.comwrote:


 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)

 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


Re: [HACKERS] What Would You Like To Do?

2011-09-13 Thread Alexander Korotkov
On Mon, Sep 12, 2011 at 8:21 AM, David E. Wheeler da...@kineticode.comwrote:

 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.


Re: [HACKERS] What Would You Like To Do?

2011-09-13 Thread Dann Corbit
From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@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 
j...@commandprompt.commailto:j...@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}



Re: [HACKERS] What Would You Like To Do?

2011-09-13 Thread David E. Wheeler
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

-- 
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] What Would You Like To Do?

2011-09-13 Thread David E . Wheeler
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


-- 
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] What Would You Like To Do?

2011-09-12 Thread Peter Eisentraut
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.



-- 
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] What Would You Like To Do?

2011-09-12 Thread Dimitri Fontaine
Hi,

David E. Wheeler da...@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

-- 
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] What Would You Like To Do?

2011-09-12 Thread David E . Wheeler
On Sep 12, 2011, at 6:01, Peter Eisentraut pete...@gmx.net wrote:

 Column-level collation support already exists.

Yeah, just realized that. I mention to say table or column-level encoding. 

Best,

David


-- 
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] What Would You Like To Do?

2011-09-12 Thread Stephen Frost
* David E. Wheeler (da...@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 primarily of listing some of the 
 stuff the community would love to see done, along with the names of the folks 
 and/or companies who, 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


signature.asc
Description: Digital signature


Re: [HACKERS] What Would You Like To Do?

2011-09-12 Thread Magnus Hagander
On Sep 13, 2011 2:37 AM, Stephen Frost sfr...@snowman.net wrote:

 * David E. Wheeler (da...@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 primarily of listing some of
the stuff the community would love to see done, along with the names of the
folks and/or companies who, 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..

Definitely interested in that, yes. We probably have some overlap in our
thoughts and plans, as discussed at the developer meeting in Ottawa.

Not specifically looking for funding either, but it would certainly increase
the number of hours available to work on it and as such make it happen
sooner...

/Magnus


Re: [HACKERS] What Would You Like To Do?

2011-09-12 Thread David E. Wheeler
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 developer meeting in Ottawa.
 
 Not specifically looking for funding either, but it would certainly increase 
 the number of hours available to work on it and as such make it happen sooner…

Yeah, that's the point.

Best,

David


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] What Would You Like To Do?

2011-09-11 Thread David E. Wheeler
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.

[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 primarily of listing some of the stuff 
the community would love to see done, along with the names of the folks and/or 
companies who, 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 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. Also, I'm highlighting PGXN and an example of 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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers