Thread: Pet Peeves?
I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at FOSDEM 2009 this year. I have a pretty good idea what some them are of course, but I would be interested to hear if people have any complaints from personal experience. What would be most interesting is if you can explain an example of when the problem caused real inconvenience to you, since sometimes it's hard to see from a theoretical description where the real harm lies. So, what do people say? Is Postgres perfect in your world or does it do some things which rub you the wrong way? Feel free to respond on-list or if you prefer in personal emails. I do intend to use the ideas you give in my presentation so mark anything you wouldn't be happy to see in a slide at a conference some day. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
Gregory Stark wrote: > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. Hmm - three "niggles" things leap to mind. 1. Case-folding on column-names. Quoting is a PITA sometimes when you're transferring from a different DBMS. Be nice to have a "true_case_insensitive=on" flag. 2. Non-deferred unique checks Merging two sets of data and renumbering pkeys is more fiddly than it needs to be. Doesn't happen often, but it's one of the few things we don't do "properly". 3. Date handling Sometimes I've got data with invalid dates and it would be great if it could replace all the bad ones with, say "0000-00-00". You could throw in non-updateable views, exact-match tsearch queries, per-statement triggers not having the old/new rowsets but that's more things PG doesn't do rather than things it does. -- Richard Huxton Archonet Ltd
On Thu, Jan 29, 2009 at 5:16 AM, Gregory Stark <stark@enterprisedb.com> wrote: > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? I would like to see the SQL92 feature for allowing sub-queries in CHECK constraints, instead of marking this feature as "intentionally omitted". -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
I have 2, closely related: 1) if I have multiple pids running queries, say all selects, I have no idea which pid is running which query and that ties to: 2) If I try to kill 1 postgres pid (e.g. to abort a bad query), the whole backend shuts down and rolls back. Can we get a way to look at and then kill a specific bad query? Maybe this is not missing functionality, it can do it and I just don't know how. Anybody want to wisen me up? :) Terry Terry Fielder terry@greatgulfhomes.com Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085 Gregory Stark wrote: > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, > but I would be interested to hear if people have any complaints from personal > experience. What would be most interesting is if you can explain an example of > when the problem caused real inconvenience to you, since sometimes it's hard > to see from a theoretical description where the real harm lies. > > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? > > Feel free to respond on-list or if you prefer in personal emails. I do intend > to use the ideas you give in my presentation so mark anything you wouldn't be > happy to see in a slide at a conference some day. > >
In response to Terry Fielder <terry@ashtonwoodshomes.com>: > > 1) if I have multiple pids running queries, say all selects, I have no > idea which pid is running which query SELECT * FROM pg_stat_activity; If the current_query column doesn't have the query in it, then you need to tweak your postgres.conf settings: http://www.postgresql.org/docs/8.3/static/monitoring-stats.html -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
On Thu, Jan 29, 2009 at 01:16:17PM +0000, Gregory Stark wrote: > > I'm putting together a talk on "PostgreSQL Pet Peeves" for > discussion at FOSDEM 2009 this year. I have a pretty good idea what > some them are of course, but I would be interested to hear if people > have any complaints from personal experience. What would be most > interesting is if you can explain an example of when the problem > caused real inconvenience to you, since sometimes it's hard to see > from a theoretical description where the real harm lies. > > So, what do people say? Is Postgres perfect in your world or does > it do some things which rub you the wrong way? > > Feel free to respond on-list or if you prefer in personal emails. I > do intend to use the ideas you give in my presentation so mark > anything you wouldn't be happy to see in a slide at a conference > some day. * No built-in ways to get the information psql gets. "See what psql is doing" isn't an option when somebody doesn't have psql on hand. * No deferrable UNIQUE constraints. * No man pages for the internals. * Letter options in psql, pg_dump[all], pg_restore aren't consistent and can easily steer you very wrong. I'm looking at you, -d. * CTEs not yet integrated into the adjacency lists in pg_catalog, etc. The following aren't problems with the PostgreSQL core engine itself, but they're nearby, so they catch ire: * Neither pgAdmin nor phpPgAdmin includes any facilities for extracting ERDs. * Neither of them let you set up Slony (or any other replication system) to start with. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Richard Huxton wrote:
I was just wishing for this the other day.Gregory Stark wrote:I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at FOSDEM 2009 this year.Hmm - three "niggles" things leap to mind. 1. Case-folding on column-names. Quoting is a PITA sometimes when you're transferring from a different DBMS. Be nice to have a "true_case_insensitive=on" flag.
2. Non-deferred unique checks Merging two sets of data and renumbering pkeys is more fiddly than it needs to be. Doesn't happen often, but it's one of the few things we don't do "properly". 3. Date handling Sometimes I've got data with invalid dates and it would be great if it could replace all the bad ones with, say "0000-00-00". You could throw in non-updateable views, exact-match tsearch queries, per-statement triggers not having the old/new rowsets but that's more things PG doesn't do rather than things it does.
On Thu, Jan 29, 2009 at 4:57 PM, David Fetter <david@fetter.org> wrote: > * Neither of them let you set up Slony (or any other replication > system) to start with. pgAdmin does (well, barring installation and setting up slon.conf): http://pgsnake.blogspot.com/2007/09/setting-up-slony-i-with-pgadmin.html -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
David Fetter <david@fetter.org> writes: > * No built-in ways to get the information psql gets. "See what psql > is doing" isn't an option when somebody doesn't have psql on hand. Uhm, what information are you referring to here? > * No man pages for the internals. Is it just that not all of the manual is actually exported into man pages? Or is there stuff you would like to see in the manual that isn't there? > * CTEs not yet integrated into the adjacency lists in pg_catalog, etc. I'm not sure what you're referring to here either. > Remember to vote! This may not be so timely any more, though I suppose there's always someone somewhere holding elections :) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
I'm a new user to PostgreSQL so mine's fresh from doing an install recently. In /etc/postgresql/8.3/main/pg_hba.conf # METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi", # "krb5", "ident", "pam" or "ldap". Note that "password" sends passwords # in clear text; "md5" is preferred since it sends encrypted passwords. So I chose md5 but it will not work, seems like a basic thing. So I am forced to use "trust". These are the kinds of things that wear down busy people trying use the software. Maybe this is a documentation enhancement or bug. You say: "I have a pretty good idea what some them are" What's your list so far? On Thu, 29 Jan 2009 13:16:17 +0000 Gregory Stark <stark@enterprisedb.com> wrote: > > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, > but I would be interested to hear if people have any complaints from personal > experience. What would be most interesting is if you can explain an example of > when the problem caused real inconvenience to you, since sometimes it's hard > to see from a theoretical description where the real harm lies. > > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? > > Feel free to respond on-list or if you prefer in personal emails. I do intend > to use the ideas you give in my presentation so mark anything you wouldn't be > happy to see in a slide at a conference some day. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's Slony Replication support! > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Jason Long <mailing.list@supernovasoftware.com> writes: > Richard Huxton wrote: > >> 1. Case-folding on column-names. >> Quoting is a PITA sometimes when you're transferring from a different >> DBMS. Be nice to have a "true_case_insensitive=on" flag. >> > I was just wishing for this the other day. I'm kind of wondering what behaviour you two are looking for and what "different DBMS" you're referring to. I'm assuming it's not the ANSI fold-to-uppercase behaviour you're looking for. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
On Thu, Jan 29, 2009 at 05:18:17PM +0000, Dave Page wrote:
> On Thu, Jan 29, 2009 at 4:57 PM, David Fetter <david@fetter.org> wrote:
>
> > * Neither of them let you set up Slony (or any other replication
> > system) to start with.
^^^^^^^^^^^^^
> pgAdmin does (well, barring installation and setting up slon.conf):
> http://pgsnake.blogspot.com/2007/09/setting-up-slony-i-with-pgadmin.html
It's exactly that setup that's the peeve. I don't think that this is
an easy problem to fix, as the general one needs filesystem access to
more than one machine, even machines without PostgreSQL installed, for
the case where the slons are on separate boxes.
It's just a peeve. :)
On the other hand, lack of a good set of startup tools has pretty much
tanked Slony adoption :(
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Gregory Stark wrote: > > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, > but I would be interested to hear if people have any complaints from personal > experience. What would be most interesting is if you can explain an example of > when the problem caused real inconvenience to you, since sometimes it's hard > to see from a theoretical description where the real harm lies. > > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? No foreign keys in inheritance trees No true clustered indexes Lazy vacuum is not able to release free pages in the middle of a table No concurrent reindex Cross-column stats problems No integrated job agent ;-) -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "La tristeza es un muro entre dos jardines" (Khalil Gibran)
Gregory Stark wrote:
Ah, I misread. I was wishing for the a way to make table and column names case sensitive without having to add quotes everywhere.Jason Long <mailing.list@supernovasoftware.com> writes:Richard Huxton wrote:1. Case-folding on column-names. Quoting is a PITA sometimes when you're transferring from a different DBMS. Be nice to have a "true_case_insensitive=on" flag.I was just wishing for this the other day.I'm kind of wondering what behaviour you two are looking for and what "different DBMS" you're referring to.
I'm assuming it's not the ANSI fold-to-uppercase behaviour you're looking for.
>> 3. Date handling >> Sometimes I've got data with invalid dates and it would be great if it >> could replace all the bad ones with, say "0000-00-00". >> Oh dear $DEITY, no. Part of the ethos of PostgreSQL is that it requires you to enter valid data. I don't see how auto-replacing one invalid date with a standardized different invalid date is a benefit. In a data-cleaning environment, I could see some limit benefit of some sort of explicit override on the copy command, say: \copy foo (a, b, c invalid=null, d invalid='') from foo.txt... At least you could get the good data into a table to analyze it. But overall, the data-cleaning role seems to me to belong more in the ETL arena. -Steve
Gregory Stark wrote: > Jason Long <mailing.list@supernovasoftware.com> writes: > >> Richard Huxton wrote: >> >>> 1. Case-folding on column-names. >>> Quoting is a PITA sometimes when you're transferring from a different >>> DBMS. Be nice to have a "true_case_insensitive=on" flag. >>> >> I was just wishing for this the other day. > > I'm kind of wondering what behaviour you two are looking for and what > "different DBMS" you're referring to. > > I'm assuming it's not the ANSI fold-to-uppercase behaviour you're looking for. It's anywhere you can get tables created quoted/unquoted and not that in the app. Or even on the command-line. It's a PITA to go round remembering to "quote" "every" column" because the table was created preserving case. Not the end of the world, but you did ask for "peeves". -- Richard Huxton Archonet Ltd
Gregory Stark wrote: > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, > but I would be interested to hear if people have any complaints from personal > experience. What would be most interesting is if you can explain an example of > when the problem caused real inconvenience to you, since sometimes it's hard > to see from a theoretical description where the real harm lies. > > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? > > Feel free to respond on-list or if you prefer in personal emails. I do intend > to use the ideas you give in my presentation so mark anything you wouldn't be > happy to see in a slide at a conference some day. > Back in March 2005, I started an email thread titled "Debugging deadlocks". Most of the experienced PGers participated in that thread. The basic issue at that time was that inserting a row into a table with a foreign key placed an exclusive row-level lock (SELECT FOR UPDATE) on the reference table (the table to which the foreign key refers). If you happen to do inserts on two different tables, each with a foreign key to the same reference table, deadlocks are pretty easy to create. This is especially true if the reference table has low cardinality, which is often the case. I don't know if this situation has been improved since that time. -- Guy Rouillier
On Thu, Jan 29, 2009 at 05:18:19PM +0000, Gregory Stark wrote: > David Fetter <david@fetter.org> writes: > > > * No built-in ways to get the information psql gets. "See what > > psql is doing" isn't an option when somebody doesn't have psql on > > hand. > > Uhm, what information are you referring to here? All the stuff that generates \d output is available only to psql. When somebody wants to make another client, or even expose some of that functionality, they pretty much have to roll it from scratch. > > * No man pages for the internals. > > Is it just that not all of the manual is actually exported into man > pages? Or is there stuff you would like to see in the manual that > isn't there? The configuration files (postgresql.conf and pg_hba.conf, at least) and the stuff in libpq and SPI would be a great start. > > * CTEs not yet integrated into the adjacency lists in pg_catalog, > > etc. > > I'm not sure what you're referring to here either. The DAG structures in pg_depend leap to mind. There's no view that shows the actual dependencies, except in the sense of, "Here's the edges. Figure it out for yourself." > > Remember to vote! > > This may not be so timely any more, though I suppose there's always > someone somewhere holding elections :) It's always time to vote on *something* :) Oh, and one more "associated project" peeve: * PostGIS doesn't ship with core PostgreSQL. I've come up with a strategy for fixing it. "Port JTS <http://sourceforge.net/projects/jts-topo-suite/> from Java to C and BSDL the result," is a pretty gigantic task, and it's just the start, but I'm pretty sure it's the right strategy for fixing the peeve. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter wrote: > * Letter options in psql, pg_dump[all], pg_restore aren't consistent > and can easily steer you very wrong. I'm looking at you, -d. Ah, good one - I keep doing that too. For the record "-d" is usually database-name, but for pg_dump it's "dump with inserts". Which is a zillion time slower than COPY for restoring. -- Richard Huxton Archonet Ltd
On Thu, 2009-01-29 at 17:43 +0000, Richard Huxton wrote: > David Fetter wrote: > > * Letter options in psql, pg_dump[all], pg_restore aren't consistent > > and can easily steer you very wrong. I'm looking at you, -d. > > Ah, good one - I keep doing that too. For the record "-d" is usually > database-name, but for pg_dump it's "dump with inserts". Which is a > zillion time slower than COPY for restoring. If we are listing pet peeves :) Up to 8.4, postgresql didn't accurately represent timestamps because they are stored as float by default The fact that there is: pg_dump pg_dumpall pg_restore At all... It should be pg_backup and that is it, with a nice -R flag for restore. The idea that it is "proper" to pipe a backup through psql to restore. Our date handling as a whole (extract,date_part) is wonky. There have been more than one blog post on this. Our lack of partitioning :) Joshua D. Drake -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
Steve Crawford <scrawford@pinpointresearch.com> writes: >>> 3. Date handling >>> Sometimes I've got data with invalid dates and it would be great if it >>> could replace all the bad ones with, say "0000-00-00". >>> > > Oh dear $DEITY, no. I think it would be best if we limited ourselves right now to discussing the problems themselves and not debating the pros and cons of possible solutions. I want to encourage people to post their peeves even if they know perfectly well the reasons why things are the way they are. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
In response to rhubbell <Rhubbell@iHubbell.com>: > > I'm a new user to PostgreSQL so mine's fresh from doing an install recently. > > In /etc/postgresql/8.3/main/pg_hba.conf > > # METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi", > # "krb5", "ident", "pam" or "ldap". Note that "password" sends passwords > # in clear text; "md5" is preferred since it sends encrypted passwords. > > > So I chose md5 but it will not work, seems like a basic thing. So I am > forced to use "trust". How on earth does failure of md5 to work force you to use trust? How about crypt or password (password is pretty darn simple to set up). -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
Gregory Stark wrote:
Fair enough.
My big pg_dump peeve: the inability to dump a function-definition with pg_dump.
Cheers,
Steve
Steve Crawford <scrawford@pinpointresearch.com> writes:3. Date handling Sometimes I've got data with invalid dates and it would be great if it could replace all the bad ones with, say "0000-00-00".Oh dear $DEITY, no.I think it would be best if we limited ourselves right now to discussing the problems themselves and not debating the pros and cons of possible solutions. I want to encourage people to post their peeves even if they know perfectly well the reasons why things are the way they are.
Fair enough.
My big pg_dump peeve: the inability to dump a function-definition with pg_dump.
Cheers,
Steve
On Jan 29, 2009, at 5:16 AM, Gregory Stark wrote: > > I'm putting together a talk on "PostgreSQL Pet Peeves" for > discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are > of course, > but I would be interested to hear if people have any complaints from > personal > experience. What would be most interesting is if you can explain an > example of > when the problem caused real inconvenience to you, since sometimes > it's hard > to see from a theoretical description where the real harm lies. > > So, what do people say? Is Postgres perfect in your world or does it > do some > things which rub you the wrong way? > > Feel free to respond on-list or if you prefer in personal emails. I > do intend > to use the ideas you give in my presentation so mark anything you > wouldn't be > happy to see in a slide at a conference some day. 1. Version upgrades require a dump and restore, which is painfully slow. 2. No distinction between parse errors and execution errors in psql. I want mistakes that can be detected at parse time (typos, invalid syntax, non-existent columns) to throw an message, and not affect the backend state. I want other errors to abort the transaction. ON_ERROR_ROLLBACK is better than nothing, but I'd like to distinguish the two cases. 3. Returning free space to the OS when there've been large changes to a large table. VACUUM FULL is too slow to be useful, CLUSTER requires an index, and can often be too slow. I tend to resort to CREATE TABLE AS or pg_dump, and manually handle the constraints, which is ugly. I'd be happy to have a vacuum equivalent that ran for days, shuffling empty pages to the end of the table, as long as it didn't interrupt service. 4. UPSERT. It's a really useful primitive, and we don't have it. 5. Backslash characters in strings. It's tricky to put a string in the database with a backslash in it, in a way that'll support different versions of the backend. 6. Where's my CPAN equivalent? Postgresql is extensible, but it's hard to find the extension you need, and often harder than it should be to install. 7. Table partitioning. It's nice, but rather limited. 8. pg_dump -d / -D. It's just a footgun. 9. psql vs pg_restore for restoring dumps. It's confusing to explain to people. 10. Backups in general. There are much better approaches than pg_dump, but explaining them to users is too complex. Cheers, Steve
On Thu, 29 Jan 2009, rhubbell wrote: > So I chose md5 but it will not work, seems like a basic thing. So I am > forced to use "trust". These are the kinds of things that wear down > busy people trying use the software. Maybe this is a documentation > enhancement or bug. I wrote up a first draft of something aimed at this particular area at http://wiki.postgresql.org/wiki/Client_Authentication I'm still not completely happy with how I describe what you need to switch to md5, but at least it points to all the right places people usually miss. If you just went through this recently or still have open issues, I'd be curious to get your feedback about that piece. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Thursday 29 January 2009 05:16:17 am Gregory Stark wrote: > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of > course, but I would be interested to hear if people have any complaints > from personal experience. What would be most interesting is if you can > explain an example of when the problem caused real inconvenience to you, > since sometimes it's hard to see from a theoretical description where the > real harm lies. > > So, what do people say? Is Postgres perfect in your world or does it do > some things which rub you the wrong way? > > Feel free to respond on-list or if you prefer in personal emails. I do > intend to use the ideas you give in my presentation so mark anything you > wouldn't be happy to see in a slide at a conference some day. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's Slony Replication support! I'm not sure if it's just my version (8.1) but I can't access two different database in the same statement as in insert into public.db1.table1 (select * from public.db2.table2) -- John Fabiani
On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote:
> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?
The one that has always bothered me is that there's no way to explicitly
set the value that is returned by PQcmdTuples(), i.e. the number of
affected rows.
This makes it very difficult to make truly transparent updatable views
in a complex case, e.g., if you're updating a remote table or something.
Regards,
Jeff Davis
On Thursday 29 January 2009, Terry Fielder <terry@ashtonwoodshomes.com> wrote: > and that ties to: > 2) If I try to kill 1 postgres pid (e.g. to abort a bad query), the > whole backend shuts down and rolls back. > Can we get a way to look at and then kill a specific bad query? select pg_cancel_backend(pid). Or kill pid from the shell, it only kills off one backend on 8.x anyway. -- Current Peeve: The mindset that the Internet is some sort of school for novice sysadmins and that everyone -not- doing stupid dangerous things should act like patient teachers with the ones who are. -- Bill Cole, NANAE
The biggest peeve I still have to fight is attached to the old "why aren't there any optimizer hints?" tree. PostgreSQL forces you to understand a non-trivial amount of how the query optimizer works before you can get it to do the right thing once you get beyond a small database, and nobody likes doing the "why isn't it using the index?!" dance. When you turn enable_seqscan off and it proceeds to do a seqscan anyway when there's seemingly a perfect index right there, it's pretty frustrating. I spent the better part of a weekend last year fighting a battle with a single "select * from t where type='x' and ts>='date1' and ts<='date2", running against a giant table with an index on (type,ts) that just stopped using that index for mysterious reasons. You do not want to seqscan a billion rows. The main thing I'd like to see here is more visibility into rejected plans, so that at least people could see those costs. If I could have seen whether the cost estimate on the index-based plan was going up or down as I tweaked parameters/updated stats, at least then I could quantify my progress toward returning to where the right plan was preferred one. The flip side is that as it is right now, it's also hard to answer the question "how close am I to having this plan fail?" until it already has. I know there's been some academic work in this area as part of classes on database internals, I'd like to see some of that turn into a production feature. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Umm, because md5 doesn't work and trust does work. On Thu, 29 Jan 2009 13:16:19 -0500 Bill Moran <wmoran@potentialtech.com> wrote: > In response to rhubbell <Rhubbell@iHubbell.com>: > > > > I'm a new user to PostgreSQL so mine's fresh from doing an install recently. > > > > In /etc/postgresql/8.3/main/pg_hba.conf > > > > # METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi", > > # "krb5", "ident", "pam" or "ldap". Note that "password" sends passwords > > # in clear text; "md5" is preferred since it sends encrypted passwords. > > > > > > So I chose md5 but it will not work, seems like a basic thing. So I am > > forced to use "trust". > > How on earth does failure of md5 to work force you to use trust? > > How about crypt or password (password is pretty darn simple to set up). > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Ok will have a look and get back to you, thanks. On Thu, 29 Jan 2009 13:39:08 -0500 (EST) Greg Smith <gsmith@gregsmith.com> wrote: > On Thu, 29 Jan 2009, rhubbell wrote: > > > So I chose md5 but it will not work, seems like a basic thing. So I am > > forced to use "trust". These are the kinds of things that wear down > > busy people trying use the software. Maybe this is a documentation > > enhancement or bug. > > I wrote up a first draft of something aimed at this particular area at > http://wiki.postgresql.org/wiki/Client_Authentication > > I'm still not completely happy with how I describe what you need to switch > to md5, but at least it points to all the right places people usually > miss. If you just went through this recently or still have open issues, > I'd be curious to get your feedback about that piece. > > -- > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Thursday 29 January 2009, Gregory Stark <stark@enterprisedb.com> wrote: > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of > course, but I would be interested to hear if people have any complaints > from personal experience. What would be most interesting is if you can > explain an example of when the problem caused real inconvenience to you, > since sometimes it's hard to see from a theoretical description where the > real harm lies. > > So, what do people say? Is Postgres perfect in your world or does it do > some things which rub you the wrong way? Lack of in-place upgrades for major version changes. I have others, but honestly this is the biggest that comes up over and over again. -- Current Peeve: The mindset that the Internet is some sort of school for novice sysadmins and that everyone -not- doing stupid dangerous things should act like patient teachers with the ones who are. -- Bill Cole, NANAE
On Thu, 29 Jan 2009, rhubbell wrote: > Umm, because md5 doesn't work and trust does work. Generally this is because you haven't yet set a password for the postgres user. You have to set a password for at least the postgres user via ALTER ROLE while you've still got it set to trust or ident before changing to md5. > On Thu, 29 Jan 2009 13:16:19 -0500 > Bill Moran <wmoran@potentialtech.com> wrote: > >> In response to rhubbell <Rhubbell@iHubbell.com>: >>> >>> I'm a new user to PostgreSQL so mine's fresh from doing an install recently. >>> >>> In /etc/postgresql/8.3/main/pg_hba.conf >>> >>> # METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi", >>> # "krb5", "ident", "pam" or "ldap". Note that "password" sends passwords >>> # in clear text; "md5" is preferred since it sends encrypted passwords. >>> >>> >>> So I chose md5 but it will not work, seems like a basic thing. So I am >>> forced to use "trust". >> >> How on earth does failure of md5 to work force you to use trust? >> >> How about crypt or password (password is pretty darn simple to set up). >> -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
On Thu, 29 Jan 2009 11:34:00 -0800 (PST) Jeff Frost <jeff@frostconsultingllc.com> wrote: > On Thu, 29 Jan 2009, rhubbell wrote: > > > Umm, because md5 doesn't work and trust does work. > > Generally this is because you haven't yet set a password for the postgres > user. You have to set a password for at least the postgres user via ALTER > ROLE while you've still got it set to trust or ident before changing to md5. Yeah, yeah, did all that, didn't work. Sorry, still a "Pet Peeve". (^; While you mention it, another "Pet Peeve" was the use of ident. Yikes. > > > On Thu, 29 Jan 2009 13:16:19 -0500 > > Bill Moran <wmoran@potentialtech.com> wrote: > > > >> In response to rhubbell <Rhubbell@iHubbell.com>: > >>> > >>> I'm a new user to PostgreSQL so mine's fresh from doing an install recently. > >>> > >>> In /etc/postgresql/8.3/main/pg_hba.conf > >>> > >>> # METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi", > >>> # "krb5", "ident", "pam" or "ldap". Note that "password" sends passwords > >>> # in clear text; "md5" is preferred since it sends encrypted passwords. > >>> > >>> > >>> So I chose md5 but it will not work, seems like a basic thing. So I am > >>> forced to use "trust". > >> > >> How on earth does failure of md5 to work force you to use trust? > >> > >> How about crypt or password (password is pretty darn simple to set up). > >> > > -- > Jeff Frost, Owner <jeff@frostconsultingllc.com> > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 916-647-6411 FAX: 916-405-4032
On Thu, Jan 29, 2009 at 09:51:42AM -0800, Joshua D. Drake wrote: > It should be pg_backup and that is it, with a nice -R flag for restore. I suppose you think that ssh_add -D is an intuitive interface too? ;-) A -- Andrew Sullivan ajs@crankycanuck.ca
In response to rhubbell <Rhubbell@iHubbell.com>: > On Thu, 29 Jan 2009 11:34:00 -0800 (PST) > Jeff Frost <jeff@frostconsultingllc.com> wrote: > > > On Thu, 29 Jan 2009, rhubbell wrote: > > > > > Umm, because md5 doesn't work and trust does work. > > > > Generally this is because you haven't yet set a password for the postgres > > user. You have to set a password for at least the postgres user via ALTER > > ROLE while you've still got it set to trust or ident before changing to md5. > > Yeah, yeah, did all that, didn't work. Sorry, still a "Pet Peeve". (^; > While you mention it, another "Pet Peeve" was the use of ident. Yikes. _My_ point was that a broken md5 (which is still under debate) doesn't force you to use trust. There are other auth options like crypt and password. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
On Thursday 29 January 2009, rhubbell <Rhubbell@ihubbell.com> wrote: > On Thu, 29 Jan 2009 11:34:00 -0800 (PST) > > Jeff Frost <jeff@frostconsultingllc.com> wrote: > > On Thu, 29 Jan 2009, rhubbell wrote: > > > Umm, because md5 doesn't work and trust does work. > > > > Generally this is because you haven't yet set a password for the > > postgres user. You have to set a password for at least the postgres > > user via ALTER ROLE while you've still got it set to trust or ident > > before changing to md5. > > Yeah, yeah, did all that, didn't work. Sorry, still a "Pet Peeve". (^; > While you mention it, another "Pet Peeve" was the use of ident. Yikes. > Well, it works for everyone else. So clearly you missed something. -- Current Peeve: The mindset that the Internet is some sort of school for novice sysadmins and that everyone -not- doing stupid dangerous things should act like patient teachers with the ones who are. -- Bill Cole, NANAE
Another "Pet Peeve": Where oh where is pg_config? Oh where oh where can it be? On Thu, 29 Jan 2009 13:16:17 +0000 Gregory Stark <stark@enterprisedb.com> wrote: > > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, > but I would be interested to hear if people have any complaints from personal > experience. What would be most interesting is if you can explain an example of > when the problem caused real inconvenience to you, since sometimes it's hard > to see from a theoretical description where the real harm lies. > > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? > > Feel free to respond on-list or if you prefer in personal emails. I do intend > to use the ideas you give in my presentation so mark anything you wouldn't be > happy to see in a slide at a conference some day. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's Slony Replication support! > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jan 29, 2009 at 8:10 PM, rhubbell <Rhubbell@ihubbell.com> wrote: > > Another "Pet Peeve": > > Where oh where is pg_config? Oh where oh where can it be? $PGDIR/bin ? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Steve Atkins <steve@blighty.com> writes: > 6. Where's my CPAN equivalent? Postgresql is extensible, but it's hard to find > the extension you need, and often harder than it should be to install. FWIW our CPAN equivalent is pgfoundry. I don't think we quite have the critical mass yet that Perl has to really make it a big success though. Making modules more, uh, modular, so they can be installed and uninstalled smoothly and preferably without special access privileges is a recognized issue though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
1. pg_dump -d - do i need to explain? 2. psql is not compatible with different (older) version of pg, in terms of working \x commands 3. lack of optimizer hints 4. lack of covering indexes 5. lack of jobs (like cron, not like something to do to be paid :) depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
Gregory Stark wrote:
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
* The capitalization that makes everyone (customers, execs, etc) I introduce
it to parse the name as Postgre-SQL.
* Last I checked, postgres keeps my laptop's hard drive spinning all the time
by doing some I/O even when "totally idle". For that matter, waking
up the CPUs a few times a second too.
* pgfoundry - which was once described on the mailinglists as
the "kiss of death" for projects. Yes, I understand why it is
what it is; but it's still a pet-peeve. I can't even put my finger
on what's annoying about it; but it is.
* Upgrades involving databases with extra modules like postgis.
$ sh utils/postgis_restore.pl lwpostgis.sql newdb olddb.dump -E=UNICODE
* Finding extensions I might want. For example:
- Surely someone wrote a good count(*)-replacement-trigger before.
Now where can I find one? Searching for "count" on pgfoundry
doesn't help me. Searching varlena genralbits find shows me
a simple one, but IIRC is lacking when it comes to concurrency.
Googling just gets me lots of "read the archives" postings in
the archives.
On Thu, Jan 29, 2009 at 02:22:28PM -0300, Alvaro Herrera wrote: > > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, > > but I would be interested to hear if people have any complaints from personal > > experience. What would be most interesting is if you can explain an example of > > when the problem caused real inconvenience to you, since sometimes it's hard > > to see from a theoretical description where the real harm lies. > > > > So, what do people say? Is Postgres perfect in your world or does it do some > > things which rub you the wrong way? > > No foreign keys in inheritance trees +1 Or, in other words, no indices across table (am I wrong ?). GNUmed uses table inheritance quite extensively and could save an additional key column per child table. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Jan 29, 2009, at 12:25 PM, Gregory Stark wrote: > Steve Atkins <steve@blighty.com> writes: > >> 6. Where's my CPAN equivalent? Postgresql is extensible, but it's >> hard to find >> the extension you need, and often harder than it should be to >> install. > > FWIW our CPAN equivalent is pgfoundry. I don't think we quite have the > critical mass yet that Perl has to really make it a big success > though. Kinda. It's much more like a freshmeat/sourceforge equivalent than a CPAN equivalent. There's no standard package format, no dependency or version handling, no possibility of automated installation. It also has a mixture of postgresql modules, client-side code that can access postgresql and even occasional projects that have nothing much to do with postgresql. > Making modules more, uh, modular, so they can be installed and > uninstalled > smoothly and preferably without special access privileges is a > recognized > issue though. Yup, that's one prerequisite for the rest of it, really. pgTap, which we have now, was probably another. Cheers, Steve
On Thu, Jan 29, 2009 at 09:51:42AM -0800, Joshua D. Drake wrote: > If we are listing pet peeves :) > > Up to 8.4, postgresql didn't accurately represent timestamps because Ah, speaking of timestamps: GNUmed could nicely use a timestamp with time zone which preserves the time zone that was used for insertion/update. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Thu, 29 Jan 2009, Ron Mayer wrote: > - Surely someone wrote a good count(*)-replacement-trigger before. > Now where can I find one? Searching for "count" on pgfoundry > doesn't help me. Searching varlena genralbits find shows me > a simple one, but IIRC is lacking when it comes to concurrency. There were two Varlena postings on this and one other good article. I got sick of not being to find them every time I wanted to and added links to them all at http://wiki.postgresql.org/wiki/Slow_Counting -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Jan 29, 2009, at 9:43 AM, David Fetter wrote: > On Thu, Jan 29, 2009 at 05:18:19PM +0000, Gregory Stark wrote: >> David Fetter <david@fetter.org> writes: >> >>> * No built-in ways to get the information psql gets. "See what >>> psql is doing" isn't an option when somebody doesn't have psql on >>> hand. >> >> Uhm, what information are you referring to here? > > All the stuff that generates \d output is available only to psql. > When somebody wants to make another client, or even expose some of > that functionality, they pretty much have to roll it from scratch. I'd say a good example close of this is the ability to generate full create statements for database objects via an SQL command. I.e. shelling out to pg_dump is not always a fun option. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
On Thursday 29 January 2009 9:19:15 am rhubbell wrote: > I'm a new user to PostgreSQL so mine's fresh from doing an install > recently. > > > In /etc/postgresql/8.3/main/pg_hba.conf > > # METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", > "sspi", # "krb5", "ident", "pam" or "ldap". Note that "password" sends > passwords # in clear text; "md5" is preferred since it sends encrypted > passwords. > > > So I chose md5 but it will not work, seems like a basic thing. So I am > forced to use "trust". These are the kinds of things that wear down > busy people trying use the software. Maybe this is a documentation > enhancement or bug. > You realize the authentication is done top to bottom? The first line that matches wins :) So if you have a matching "trust" line before your "md5" line then it will not use md5. For a better explanation: http://www.postgresql.org/docs/8.2/interactive/auth-pg-hba-conf.html "Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied." -- Adrian Klaver aklaver@comcast.net
Gregory Stark wrote: > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? The few things that used to really bug me have gone away between 8.1 and 8.3. The big one is that there are no longer issues with temp tables in PL/PgSQL functions or any of the other problems related to the lack of automatic plan invalidation for functions. I could go on forever about the things I LIKE about Pg. There are a few niggles I have noticed, though: - VACUUM FULL is rather slow and often leaves indexes badly bloated. This is a usability issue for new admins, too, who won't know they're usually better off using CLUSTER. That in its self suggests something's not right, IMO. - There's no REINDEX CONCURRENTLY. - There are no built-in ways for admins to easily discover, be alerted to, or manually check for index and table bloat. We NEED a pg_catalog.pg_bloat view IMO, as well as NOTICE level warnings from VACUUM when very bloated indexes and tables are discovered. This is a mainly a usability issue for new admins. - Bytea's literal format is wasteful and is painful to work with. Supporting something reasonably compact and commonly understood by most tools and libraries (like, say, base64) would be really nice. It'd also be useful for backup/restore. - The problems involved in restoring/upgrading a database to a newer major version when extensions like PostGIS are in use. Argh. - Table partitioning is effective, but somewhat clumsy, and would really benefit from some automatic management tools. - No column-level triggers and, thus, no way to attach a trigger to a domain type. - The need for a dump and restore for major version upgrades. I understand why, but ... -- Craig Ringer
PROCEDUREs *which compile into Procedure Cache* and have IN/OUT (Mode) parameters..
Martin Gainty
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
> Date: Fri, 30 Jan 2009 12:37:11 +0900
> From: craig@postnewspapers.com.au
> To: stark@enterprisedb.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Pet Peeves?
>
> Gregory Stark wrote:
>
> > So, what do people say? Is Postgres perfect in your world or does it do some
> > things which rub you the wrong way?
>
> The few things that used to really bug me have gone away between 8.1 and
> 8.3. The big one is that there are no longer issues with temp tables in
> PL/PgSQL functions or any of the other problems related to the lack of
> automatic plan invalidation for functions.
>
> I could go on forever about the things I LIKE about Pg.
>
>
> There are a few niggles I have noticed, though:
>
> - VACUUM FULL is rather slow and often leaves indexes badly bloated.
> This is a usability issue for new admins, too, who won't know they're
> usually better off using CLUSTER. That in its self suggests something's
> not right, IMO.
>
> - There's no REINDEX CONCURRENTLY.
>
> - There are no built-in ways for admins to easily discover, be alerted
> to, or manually check for index and table bloat. We NEED a
> pg_catalog.pg_bloat view IMO, as well as NOTICE level warnings from
> VACUUM when very bloated indexes and tables are discovered. This is a
> mainly a usability issue for new admins.
>
> - Bytea's literal format is wasteful and is painful to work with.
> Supporting something reasonably compact and commonly understood by most
> tools and libraries (like, say, base64) would be really nice. It'd also
> be useful for backup/restore.
>
> - The problems involved in restoring/upgrading a database to a newer
> major version when extensions like PostGIS are in use. Argh.
>
> - Table partitioning is effective, but somewhat clumsy, and would really
> benefit from some automatic management tools.
>
> - No column-level triggers and, thus, no way to attach a trigger to a
> domain type.
>
> - The need for a dump and restore for major version upgrades. I
> understand why, but ...
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Windows Live™ Hotmail®:…more than just e-mail. Check it out.
Martin Gainty
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
> Date: Fri, 30 Jan 2009 12:37:11 +0900
> From: craig@postnewspapers.com.au
> To: stark@enterprisedb.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Pet Peeves?
>
> Gregory Stark wrote:
>
> > So, what do people say? Is Postgres perfect in your world or does it do some
> > things which rub you the wrong way?
>
> The few things that used to really bug me have gone away between 8.1 and
> 8.3. The big one is that there are no longer issues with temp tables in
> PL/PgSQL functions or any of the other problems related to the lack of
> automatic plan invalidation for functions.
>
> I could go on forever about the things I LIKE about Pg.
>
>
> There are a few niggles I have noticed, though:
>
> - VACUUM FULL is rather slow and often leaves indexes badly bloated.
> This is a usability issue for new admins, too, who won't know they're
> usually better off using CLUSTER. That in its self suggests something's
> not right, IMO.
>
> - There's no REINDEX CONCURRENTLY.
>
> - There are no built-in ways for admins to easily discover, be alerted
> to, or manually check for index and table bloat. We NEED a
> pg_catalog.pg_bloat view IMO, as well as NOTICE level warnings from
> VACUUM when very bloated indexes and tables are discovered. This is a
> mainly a usability issue for new admins.
>
> - Bytea's literal format is wasteful and is painful to work with.
> Supporting something reasonably compact and commonly understood by most
> tools and libraries (like, say, base64) would be really nice. It'd also
> be useful for backup/restore.
>
> - The problems involved in restoring/upgrading a database to a newer
> major version when extensions like PostGIS are in use. Argh.
>
> - Table partitioning is effective, but somewhat clumsy, and would really
> benefit from some automatic management tools.
>
> - No column-level triggers and, thus, no way to attach a trigger to a
> domain type.
>
> - The need for a dump and restore for major version upgrades. I
> understand why, but ...
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Windows Live™ Hotmail®:…more than just e-mail. Check it out.
Martin Gainty wrote: > PROCEDUREs *which compile into Procedure Cache* and have IN/OUT (Mode) parameters.. Do you mean that as a feature request? If you intended to say something along the lines of: "I'd like stored procedures, invoked using 'CALL procname(params)' syntax, as distinct from SQL or PL/PgSQL functions" ... then I do agree that'd be handy. Their absence could easily become a peeve, especially if you use DB interfaces that expect to invoke stored procedures this way. Some seem to want you to jump through some hoops (switch to "native query" mode, declare functions to the interface, etc) to call server-side functions, but provide a convenient interface to CALL stored procedures. -- Craig Ringer
On Thu, Jan 29, 2009 at 12:41 PM, rhubbell <Rhubbell@ihubbell.com> wrote: > On Thu, 29 Jan 2009 11:34:00 -0800 (PST) > Jeff Frost <jeff@frostconsultingllc.com> wrote: > >> On Thu, 29 Jan 2009, rhubbell wrote: >> >> > Umm, because md5 doesn't work and trust does work. >> >> Generally this is because you haven't yet set a password for the postgres >> user. You have to set a password for at least the postgres user via ALTER >> ROLE while you've still got it set to trust or ident before changing to md5. > > Yeah, yeah, did all that, didn't work. Sorry, still a "Pet Peeve". (^; > While you mention it, another "Pet Peeve" was the use of ident. Yikes. So, maybe you could tell us what "didn't work" means in a more expanded manner, along with things like error messages? md5 works a charm for me, and it has since it came out, so I'm wondering what's so different in your setup that it doesn't.
On Fri, Jan 30, 2009 at 7:55 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Thu, Jan 29, 2009 at 12:41 PM, rhubbell <Rhubbell@ihubbell.com> wrote: >> On Thu, 29 Jan 2009 11:34:00 -0800 (PST) >> Jeff Frost <jeff@frostconsultingllc.com> wrote: >> >>> On Thu, 29 Jan 2009, rhubbell wrote: >>> >>> > Umm, because md5 doesn't work and trust does work. >>> >>> Generally this is because you haven't yet set a password for the postgres >>> user. You have to set a password for at least the postgres user via ALTER >>> ROLE while you've still got it set to trust or ident before changing to md5. >> >> Yeah, yeah, did all that, didn't work. Sorry, still a "Pet Peeve". (^; >> While you mention it, another "Pet Peeve" was the use of ident. Yikes. > > So, maybe you could tell us what "didn't work" means in a more > expanded manner, along with things like error messages? md5 works a > charm for me, and it has since it came out, so I'm wondering what's so > different in your setup that it doesn't. We've had hundreds of thousands (more likely millions by now) of downloads of the community and EDB installers which all use md5 out of the box, and I don't recall ever seeing anyone complain it doesn't work. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote: > So, what do people say? Is Postgres perfect in your world or does it > do some > things which rub you the wrong way? For me: Lack of column-level privileges. It just doesn't help scalability at all. You end up having different tables each with different permissions, or having to create a view with a ruleset attached, having to update the view for each definition change in the view. Lack of allowing subqueries in CHECK constraints and lack of allowing references to other columns in DEFAULT clauses (like columns that default to a calculated formula, but may be overridden if it violates a constraint) Now, last time I checked, Postgresql lacked an SQL extension to create crosstab queries. Even though contrib have some helper functions, they are not official and work only if the number of columns is actually predefined. For instance if you want to create a query to produce different paths to go through a graph, being represented by 1 arc per record, you will never be able to predict the final number of columns (path steps) needed for the crosstab without doing the whole query one extra time to get the max(step). Of course, there are things that PostgreSQL does great, like the user-defined types.
Octavio Alvarez <alvarezp@alvarezp.ods.org> writes: > Now, last time I checked, Postgresql lacked an SQL extension to create > crosstab queries. Even though contrib have some helper functions, they > are not official and work only if the number of columns is actually > predefined. For instance if you want to create a query to produce > different paths to go through a graph, being represented by 1 arc per > record, you will never be able to predict the final number of columns > (path steps) needed for the crosstab without doing the whole query one > extra time to get the max(step). Is it the hierarchical query ability you're looking for or pivot? The former we are actually getting in 8.4. AFAIK even in systems with pivot you still have to declare a fixed list of columns in advance anyways. Do you see a system where it works differently? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
Gregory Stark wrote: > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? * No offer of anything-but-CVS on pgfoundry.org * Lack of REINDEX CONCURRENTLY * Too many obscure configuration options: memory management, autovacuum management, freespace map, background writer, WAL. * Writing robust scripts with psql is difficult and bizarre. * Default postgresql.conf is too long and verbose. * Default logging configuration is too minimal. * Difficult to trace nested things, e.g., nested PL/pgSQL calls, cascaded foreign key actions. * No basic health checking: Answering the question, "Is this system configured reasonably and running smoothly" takes two days of training and half a day of work. * Too much accumulated historical garbage that is never cleared out.
Gregory Stark wrote: > Is it the hierarchical query ability you're looking for or pivot? > The former we are actually getting in 8.4. > > AFAIK even in systems with pivot you still have to > declare a fixed list of columns in advance anyways. > Do you see a system where it works differently? MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without the need to know in advance the number of columns: http://msdn.microsoft.com/en-us/library/bb208956.aspx As for Oracle, it wasn't possible until recently but now 11g has the PIVOT clause: http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f eatures/11g-pivot.html In contrast of these clauses, PG's contrib/tablefunc looks more limited and quite harder to use. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Fri, Jan 30, 2009 at 1:26 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > * Lack of REINDEX CONCURRENTLY +1 > * Too many obscure configuration options: memory management, autovacuum > management, freespace map, background writer, WAL. +100 > * Writing robust scripts with psql is difficult and bizarre. Well, to be fair - psql isn't a script shell for starters. > * Difficult to trace nested things, e.g., nested PL/pgSQL calls, cascaded > foreign key actions. +1 -- GJ
On Friday 30 January 2009 14:26:45 Peter Eisentraut wrote: > Gregory Stark wrote: > > So, what do people say? Is Postgres perfect in your world or does it do > > some things which rub you the wrong way? > > * No offer of anything-but-CVS on pgfoundry.org > > * Lack of REINDEX CONCURRENTLY > > * Too many obscure configuration options: memory management, autovacuum > management, freespace map, background writer, WAL. > > * Writing robust scripts with psql is difficult and bizarre. > > * Default postgresql.conf is too long and verbose. > > * Default logging configuration is too minimal. > > * Difficult to trace nested things, e.g., nested PL/pgSQL calls, > cascaded foreign key actions. > > * No basic health checking: Answering the question, "Is this system > configured reasonably and running smoothly" takes two days of training > and half a day of work. > > * Too much accumulated historical garbage that is never cleared out. * No builtin crosstab a-la http://www.postgresql.org/docs/8.3/interactive/tablefunc.html * No builtin time intervals a-la http://temporal.projects.postgresql.org/ * LIMIT and OFFSET don't accept non-constant (or non function) values -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand
On Fri, Jan 30, 2009 at 1:37 PM, Reg Me Please <regmeplease@gmail.com> wrote:
> * LIMIT and OFFSET don't accept non-constant (or non function) values
>
no longer true with 8.4
gjaskie=# create table foo(a serial, b varchar);
gjaskie=# insert into table foo(b) select generate_series(1,10000,
(random()*100)::int);
gjaskie=# insert into foo(b) select generate_series(1,10000,
(random()*100)::int);
gjaskie=# insert into foo(b) select generate_series(1,10000,
(random()*100)::int);
gjaskie=# insert into foo(b) select generate_series(1,10000,
(random()*100)::int);
gjaskie=# select count(*) from foo;
count
-------
2019
(1 row)
gjaskie=# select * from foo order by random() limit (select a*random()
from foo order by random() limit 1);
a | b
------+------
1491 | 8941
892 | 5347
1609 | 9649
1796 | 9985
190 | 1135
1445 | 8665
194 | 1159
47 | 277
911 | 5461
1941 | 6481
196 | 1171
1995 | 8911
25 | 145
1430 | 8575
533 | 3193
172 | 1027
1111 | 6661
948 | 5683
148 | 883
99 | 589
173 | 1033
1224 | 7339
973 | 5833
1358 | 8143
1532 | 9187
165 | 985
1996 | 8956
82 | 487
820 | 4915
1185 | 7105
534 | 3199
57 | 337
2019 | 9991
(33 rows)
gjaskie=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.3.0 20080428 (Red Hat 4.3.0-8), 32-bit
(1 row)
--
GJ
"Daniel Verite" <daniel@manitou-mail.org> writes: > Gregory Stark wrote: > >> Is it the hierarchical query ability you're looking for or pivot? >> The former we are actually getting in 8.4. >> >> AFAIK even in systems with pivot you still have to >> declare a fixed list of columns in advance anyways. >> Do you see a system where it works differently? > > MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without > the need to know in advance the number of columns: > http://msdn.microsoft.com/en-us/library/bb208956.aspx That's puzzling. I wonder what they do about clients requesting info about the results. Or for that matter such queries being used in subqueries or anywhere else where the surrounding code needs to know the type of results to expect. > As for Oracle, it wasn't possible until recently but now 11g has the PIVOT > clause: > http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f > eatures/11g-pivot.html From this the result columns do need to be explicitly listed in advance unless you're asking for the pivot to be into an xml blob which seems like a whole different feature really. > In contrast of these clauses, PG's contrib/tablefunc looks more limited and > quite harder to use. Incidentally, the work-around I've used in the past was to aggregate the rows into an array instead of separate columns. Definitely not the same of course, just a work-around. I think PIVOT is enticing too. It'll be interesting to see what happens in the standard with the divergence between MSSQL and Oracle. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
On 1/29/09, Gregory Stark <stark@enterprisedb.com> wrote: > > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, Here are couple of mine. Had to dig a bit. There may be some duplication with others: *) In place upgrade (everybody's #1) *) lack of *generally usable* standalone mode which leads to: *) libpq. needs rewrite: incorporate libpqtypes functionality, sane error system, many other things. would be nice to be able to use libpq in standalone as described above. *) update foo set foo = foo; doesn't work (currently thread on hackers) *) named parameters in plain sql functions don't work *) can't use insert/update returning in subquery (prob #3 requested after IPU and hot standy) *) stored procedures!! to some, this means multiset. to me, it means pl/pgsql without automatic transaction mgmt. ideally, we get both *) CTE expressions can't end in insert (slight variant of above) *) no easy way to have database feed constants into 'create replace function' would be nice to have above take string expression, not literal *) libpqtypes bumped to contrib :D *) would be nice to run some arbitrary sql when session dumps. only possible today with highly circuitous on_proc_exit that connects back to the database...ugh *) listen/notify needs reworking *) pl/sh should be raised to contrib at minimum. diamond in the rough and my #1 pet peeve is <drumroll>: *) having to answer questions about why count(*) is slow! merlin
Peter Eisentraut wrote: > * Difficult to trace nested things, e.g., nested PL/pgSQL calls, > cascaded foreign key actions. +1 That's a really good point. It'd be particularly nice to have some way to get EXPLAIN ANALYZE to report on details of the execution of called SQL functions. -- Craig Ringer
> You can however pull it from a -Fc backup with pg_restore. Just FYI. > > Joshua D. Drake > Or strip it from a pg_dump/pg_dumpall with sed. Or write your own function-dumper based on ideas gleaned from various notes/comments on the web (my approach). I had not thought of using the -Fc approach but it appears that that would require dumping the whole database then using pg_restore to pull the function definition from the dump. One other thing that would be nice to have for function-dumping whether in pg_dump or using the -Fc approach would be the ability to dump all functions of a given name instead of having to go one-by-one. It's pretty unusual for identically-named functions to have unrelated purposes. Cheers, Steve
Guy Rouillier wrote: > Back in March 2005, I started an email thread titled "Debugging > deadlocks". Most of the experienced PGers participated in that thread. > The basic issue at that time was that inserting a row into a table with a > foreign key placed an exclusive row-level lock (SELECT FOR UPDATE) on the > reference table (the table to which the foreign key refers). If you > happen to do inserts on two different tables, each with a foreign key to > the same reference table, deadlocks are pretty easy to create. This is > especially true if the reference table has low cardinality, which is > often the case. > > I don't know if this situation has been improved since that time. We fixed this in 8.1 IIRC. FKs now use "SELECT FOR SHARE", which only takes a shared lock not exclusive, and does away with most deadlocks of this ilk. Of course, there are other ways to get in deadlock still. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "I can see support will not be a problem. 10 out of 10." (Simon Wittber) (http://archives.postgresql.org/pgsql-general/2004-12/msg00159.php)
On Thu, Jan 29, 2009 at 01:16:17PM +0000, Gregory Stark wrote:
> So, what do people say? Is Postgres perfect in your world or does it
> do some things which rub you the wrong way?
* "anonymous records" are a tad annoying to use
SELECT (1,2,3) ORDER BY 1;
SELECT ARRAY[(1,2,3)];
SELECT MIN((1,2,3));
or maybe this is more obvious:
SELECT MIN(x) FROM (VALUES (1,2,3)) x;
* VALUES assumes it's returning a tuple, making the following too verbose:
SELECT * FROM (VALUES (1),(2),(3),(4)) x;
* Arrays of arrays don't work; we only get multi-dimensional arrays.
as a sub-point, the semantics of multi-dimensional arrays are too
complicated and it would be nice if at least the arity of the array
was included in its type
--
Sam http://samason.me.uk/
Gregory Stark wrote: >> MS-Access SQL has a TRANSFORM clause that allows for crosstab >> queries without the need to know in advance the number of columns: >> http://msdn.microsoft.com/en-us/library/bb208956.aspx > > That's puzzling. I wonder what they do about clients requesting info > about the results. Or for that matter such queries being used in > subqueries or anywhere else where the surrounding code needs to know > the type of results to expect. Using them in subqueries is just denied. Maybe there are also caveats with the API. >> As for Oracle, it wasn't possible until recently but now 11g has the PIVOT >> clause: >> http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f >> eatures/11g-pivot.html > > From this the result columns do need to be explicitly listed in advance unless > you're asking for the pivot to be into an xml blob which seems like a whole > different feature really. Ah yes, it's still not as helpful as it should ideally, just closer. I guess that when implementing PIVOT they would have allowed fully dynamic columns if that was at all possible, so it's probably not. Sure, if names/types of columns can be queried before the execute stage but in the case of PIVOT they had to be generated by the execute stage, that would lead to an impossible chicken-and-egg situation. Maybe that's the reason. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Thu, 29 Jan 2009 13:16:17 +0000 Gregory Stark <stark@enterprisedb.com> wrote: > > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, I'll ask again. You say "I have a pretty good idea what some them are of course" "of course" because you've heard them all? Or because you've suffered from them your self? Both? What's your list look like? Or maybe you want everyone else to do your work for you and don't have anything to share.
Nope, had to find it in another package called libpq-dev. That's on UbuntuHardy. Maybe it's a maintainer problem? What logic would lead someone to separate pg_config from everything else? Do people often just install the server and nothing else? Then what? You've got a server can you have a working db without any dev tools? Maybe it's typical to install dev tools off-server? BTW I ran into the need for pg_config upon installing DBD::Pg. Maybe DBD::Pg maintainer problem? Still a "Pet Peeve". (^: I guess I don't care who owns the pet. On Thu, 29 Jan 2009 20:19:47 +0000 Dave Page <dpage@pgadmin.org> wrote: > On Thu, Jan 29, 2009 at 8:10 PM, rhubbell <Rhubbell@ihubbell.com> wrote: > > > > Another "Pet Peeve": > > > > Where oh where is pg_config? Oh where oh where can it be? > > $PGDIR/bin ? > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com
Having to spell out params just to drop a procedure (especially when I'm trying things out, realize params need to be changed, and so can't just use "create or replace function"). Perhaps "drop function foobar" could drop the function if there is only 1 function named foobar? Perhaps "drop function foobar(*)" could drop all functions named foobar? -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
On Fri, Jan 30, 2009 at 6:09 PM, rhubbell <Rhubbell@ihubbell.com> wrote: > On Thu, 29 Jan 2009 13:16:17 +0000 > Gregory Stark <stark@enterprisedb.com> wrote: > >> >> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at >> FOSDEM 2009 this year. I have a pretty good idea what some them are of course, > > I'll ask again. You say "I have a pretty good idea what some them are of course" > > "of course" because you've heard them all? Or because you've suffered from them > your self? Both? Probably because he's been hacking on and supporting Postgres through these lists for many years. It's easy for those of us that have been regular participants for years and exchange email with the same people over and over to forget that people are joining these lists all the time and don't necessarily know what we all do. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On 1/30/09, Scott Ribe <scott_ribe@killerbytes.com> wrote: > Having to spell out params just to drop a procedure (especially when I'm > trying things out, realize params need to be changed, and so can't just use > "create or replace function"). use psql tab completion. merlin
hubert depesz lubaczewski wrote: > 1. pg_dump -d - do i need to explain? I wonder if we should just rename the -d behavior to something else and throw a proper error (or ignore the flag if it is before the dbname). -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On 1/30/09, Merlin Moncure <mmoncure@gmail.com> wrote: > On 1/29/09, Gregory Stark <stark@enterprisedb.com> wrote: > > > > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, > > > Here are couple of mine. Had to dig a bit. There may be some > duplication with others: > > *) In place upgrade (everybody's #1) > *) lack of *generally usable* standalone mode which leads to: > *) libpq. needs rewrite: incorporate libpqtypes functionality, sane > error system, many other things. would be nice to be able to use > libpq in standalone as described above. > *) update foo set foo = foo; doesn't work (currently thread on hackers) > *) named parameters in plain sql functions don't work > *) can't use insert/update returning in subquery (prob #3 requested > after IPU and hot standy) > *) stored procedures!! to some, this means multiset. to me, it means > pl/pgsql without automatic transaction mgmt. ideally, we get both > *) CTE expressions can't end in insert (slight variant of above) > *) no easy way to have database feed constants into 'create replace > function' would be nice to have above take string expression, not > literal > *) libpqtypes bumped to contrib :D > *) would be nice to run some arbitrary sql when session dumps. only > possible today with highly circuitous on_proc_exit that connects back > to the database...ugh > *) listen/notify needs reworking > *) pl/sh should be raised to contrib at minimum. diamond in the rough > > and my #1 pet peeve is <drumroll>: > *) having to answer questions about why count(*) is slow! ooh, I forgot my number personal #1 peeve (and 1a) . *) select (func()).* will execute func once per field in returned record. This is side effect of *) '*' being macro expanded into the plan. I've raised this a couple of times in hackers. I don't know if there's a clean solution to this behavior. merlin
Octavio Alvarez wrote: > On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote: > > So, what do people say? Is Postgres perfect in your world or does it > > do some > > things which rub you the wrong way? > > For me: > > Lack of column-level privileges. It just doesn't help scalability at > all. You end up having different tables each with different permissions, > or having to create a view with a ruleset attached, having to update the > view for each definition change in the view. Column-level privileges will be in Postgres 8.4. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
rhubbell <Rhubbell@iHubbell.com> writes: > Nope, had to find it in another package called libpq-dev. > That's on UbuntuHardy. Maybe it's a maintainer problem? > > What logic would lead someone to separate pg_config from everything else? > Do people often just install the server and nothing else? Then what? This is actually *required* by Debian/Ubuntu packaging rules. The development environment must be packaged separately from shared libraries like libpq or else major snafus arise when a new soversion of libpq comes out. You need to be able to have both versions installed simultaneously (in case you have programs which require both) but that won't work if they both contain things like header files or executables. > BTW I ran into the need for pg_config upon installing DBD::Pg. > Maybe DBD::Pg maintainer problem? Installing a package for DBD::Pg or building it? The former would indeed be a package bug. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
rhubbell <Rhubbell@iHubbell.com> writes: > What's your list look like? Or maybe you want everyone else to do your work > for you and don't have anything to share. Heh, fair enough. Perhaps I should just say you should come to FOSDEM though :) The usual bugaboos are things like having to do a dump/restore to upgrade, weak support for partitioning, no index-only-scans, etc. Things which have been mentioned several times. One thing which has *not* been mentioned which i find positively shocking is VACUUM. This was once our single biggest source of user complaints. Between Autovacuum improvements and HOT previously and the free space map in 8.4 the situation will be much improved. However there are still some common usage patterns where people run into problems. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
On Fri, 30 Jan 2009 20:46:22 +0000 Gregory Stark <stark@enterprisedb.com> wrote: > > rhubbell <Rhubbell@iHubbell.com> writes: > > > What's your list look like? Or maybe you want everyone else to do your work > > for you and don't have anything to share. > > Heh, fair enough. Perhaps I should just say you should come to FOSDEM though > :) Haha, yeah buy the book, come to the show, see the movie. Well I don't know what is FOSDEM. Will the talks be placed online at some point? Up to each contributor? I'll check out what's FOSDEM. > > The usual bugaboos are things like having to do a dump/restore to upgrade, > weak support for partitioning, no index-only-scans, etc. Things which have > been mentioned several times. > > One thing which has *not* been mentioned which i find positively shocking is > VACUUM. This was once our single biggest source of user complaints. Between > Autovacuum improvements and HOT previously and the free space map in 8.4 the > situation will be much improved. However there are still some common usage > patterns where people run into problems. I think it was mentioned. Anyway glad to see that there's vibrant discussion here. Usually a sign of a healthy community. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's On-Demand Production Tuning
Gregory Stark wrote:
PIVOT would prove very valuable to my application. :)"Daniel Verite" <daniel@manitou-mail.org> writes:Gregory Stark wrote:Is it the hierarchical query ability you're looking for or pivot? The former we are actually getting in 8.4. AFAIK even in systems with pivot you still have to declare a fixed list of columns in advance anyways. Do you see a system where it works differently?MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without the need to know in advance the number of columns: http://msdn.microsoft.com/en-us/library/bb208956.aspxThat's puzzling. I wonder what they do about clients requesting info about the results. Or for that matter such queries being used in subqueries or anywhere else where the surrounding code needs to know the type of results to expect.As for Oracle, it wasn't possible until recently but now 11g has the PIVOT clause: http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f eatures/11g-pivot.htmlFrom this the result columns do need to be explicitly listed in advance unless you're asking for the pivot to be into an xml blob which seems like a whole different feature really.In contrast of these clauses, PG's contrib/tablefunc looks more limited and quite harder to use.Incidentally, the work-around I've used in the past was to aggregate the rows into an array instead of separate columns. Definitely not the same of course, just a work-around. I think PIVOT is enticing too. It'll be interesting to see what happens in the standard with the divergence between MSSQL and Oracle.
Daniel Verite wrote: > Gregory Stark wrote: > >> Is it the hierarchical query ability you're looking for or pivot? >> The former we are actually getting in 8.4. >> AFAIK even in systems with pivot you still have to >> declare a fixed list of columns in advance anyways. >> Do you see a system where it works differently? > > MS-Access SQL has a TRANSFORM clause that allows for crosstab queries > without the need to know in advance the number of columns: > http://msdn.microsoft.com/en-us/library/bb208956.aspx > > As for Oracle, it wasn't possible until recently but now 11g has the > PIVOT clause: > http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f > eatures/11g-pivot.html > > In contrast of these clauses, PG's contrib/tablefunc looks more > limited and quite harder to use. > > Best regards, The PIVOT clause would kick ass.
On Fri, Jan 30, 2009 at 03:32:45PM -0500, Merlin Moncure wrote: > ooh, I forgot my number personal #1 peeve (and 1a) . > *) select (func()).* will execute func once per field in returned > record. This is side effect of > *) '*' being macro expanded into the plan. I've raised this a couple > of times in hackers. I don't know if there's a clean solution to this > behavior. +1, this is very counter intuitive behavior! -- Sam http://samason.me.uk/
Gregory Stark wrote: > > rhubbell <Rhubbell@iHubbell.com> writes: > > > What's your list look like? Or maybe you want everyone else to do your work > > for you and don't have anything to share. > > Heh, fair enough. Perhaps I should just say you should come to FOSDEM though > :) > > The usual bugaboos are things like having to do a dump/restore to upgrade, > weak support for partitioning, no index-only-scans, etc. Things which have > been mentioned several times. > > One thing which has *not* been mentioned which i find positively shocking is > VACUUM. This was once our single biggest source of user complaints. Between > Autovacuum improvements and HOT previously and the free space map in 8.4 the > situation will be much improved. However there are still some common usage > patterns where people run into problems. Yes, that is surprising, and something most database product struggle with. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Gregory Stark wrote: > One thing which has *not* been mentioned which i find positively shocking is > VACUUM. This was once our single biggest source of user complaints. Between > Autovacuum improvements and HOT previously and the free space map in 8.4 the > situation will be much improved. The other 2 features that made VACUUM much less painful for me was vacuum_cost_delay (8.0?) and allowing concurrent GiST indexes (8.1?). Before those features were in, VACUUM could choke even a very lightly loaded moderately large database for arbitrarily long times. I guess I'd still like some more convenient tuning of autovacuum (perhaps specifying X mbps disk I/O); but I'd say vacuum fell off my pet-peeve list around the 8.1 timeframe.
On Fri, 30 Jan 2009 20:38:06 +0000 Gregory Stark <stark@enterprisedb.com> wrote: > > rhubbell <Rhubbell@iHubbell.com> writes: > > > Nope, had to find it in another package called libpq-dev. > > That's on UbuntuHardy. Maybe it's a maintainer problem? > > > > What logic would lead someone to separate pg_config from everything else? > > Do people often just install the server and nothing else? Then what? > > This is actually *required* by Debian/Ubuntu packaging rules. > > The development environment must be packaged separately from shared libraries > like libpq or else major snafus arise when a new soversion of libpq comes out. > You need to be able to have both versions installed simultaneously (in case > you have programs which require both) but that won't work if they both contain > things like header files or executables. Weren't .so born from a need to save disk space? Maybe startup speed too. Now they're a PITA. > > > BTW I ran into the need for pg_config upon installing DBD::Pg. > > Maybe DBD::Pg maintainer problem? > > Installing a package for DBD::Pg or building it? The former would indeed be a > package bug. When I installed the package I did via CPAN so maybe this was my mistake. Not every CPAN package is packaged for debian so I often times don't bother checking if a perl module exists in debian I just do perl -MCPAN -e 'install (DBD::Pg)' or whatever pkg....
On Fri, 2009-01-30 at 15:32 -0500, Bruce Momjian wrote: > Octavio Alvarez wrote: > > On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote: > > > So, what do people say? Is Postgres perfect in your world or does > it > > > do some > > > things which rub you the wrong way? > > > > For me: > > > > Lack of column-level privileges. It just doesn't help scalability at > > all. You end up having different tables each with different > permissions, > > or having to create a view with a ruleset attached, having to update > the > > view for each definition change in the view. > > Column-level privileges will be in Postgres 8.4. That is one hell of great news! It is not marked as "Done" in the To-do list in the Wiki, though. http://wiki.postgresql.org/wiki/Todo#GRANT.2FREVOKE
rhubbell <Rhubbell@iHubbell.com> writes: >> Installing a package for DBD::Pg or building it? The former would indeed be a >> package bug. > > When I installed the package I did via CPAN so maybe this was my mistake. > Not every CPAN package is packaged for debian so I often times don't bother > checking if a perl module exists in debian I just do > perl -MCPAN -e 'install (DBD::Pg)' or whatever pkg.... Ah, well that's not a mistake, but you need to check what -dev packages the CPAN module you're building requires. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
On Fri, Jan 30, 2009 at 03:44:48PM -0800, rhubbell wrote: > On Fri, 30 Jan 2009 20:38:06 +0000 > Gregory Stark <stark@enterprisedb.com> wrote: > > > > > rhubbell <Rhubbell@iHubbell.com> writes: > > > > > Nope, had to find it in another package called libpq-dev. > > > That's on UbuntuHardy. Maybe it's a maintainer problem? > > > > > > What logic would lead someone to separate pg_config from everything else? > > > Do people often just install the server and nothing else? Then what? > > > > This is actually *required* by Debian/Ubuntu packaging rules. > > > > The development environment must be packaged separately from shared libraries > > like libpq or else major snafus arise when a new soversion of libpq comes out. > > You need to be able to have both versions installed simultaneously (in case > > you have programs which require both) but that won't work if they both contain > > things like header files or executables. > > Weren't .so born from a need to save disk space? Maybe startup speed too. > Now they're a PITA. Not really. You just need to ensure that you have the correct development environment for the version of PostgreSQL which you are targetting. While it might appear to be unnecessarily complex, you'll find that there's a very good reason for it. * Every library in Debian is split into separate runtime and development packages (and also documentation). * Users will only need the runtime. * Only developers and build dæmons will need to install the -dev packages). * Multiple -dev packages can and do exist for supporting multiple library versions, especially during transitions from one version to the next. They can't generally be installed simultaneously (conflicting files common to both such as pg_config), so you just install the one you require. This saves valuable diskspace on end-user systems as well as allowing for the creation of known sane build environments (look up how Debian uses Build-Depends for automated package building). > > > BTW I ran into the need for pg_config upon installing DBD::Pg. > > > Maybe DBD::Pg maintainer problem? > > > > Installing a package for DBD::Pg or building it? The former would indeed be a > > package bug. > > When I installed the package I did via CPAN so maybe this was my mistake. > Not every CPAN package is packaged for debian so I often times don't bother > checking if a perl module exists in debian I just do > perl -MCPAN -e 'install (DBD::Pg)' or whatever pkg.... It's always worth checking first (first line): % apt-cache search dbd | grep -i postgres libdbd-pg-perl - Perl DBI driver for the PostgreSQL database server libdbd-pg-ruby - Ruby/DBI PostgreSQL driver libdbd-pg-ruby1.8 - Ruby/DBI PostgreSQL driver for Ruby 1.8 libdbd-pgsql - PostgreSQL database server driver for libdbi postgresql-contrib-8.3 - additional facilities for PostgreSQL libaprutil1-dbd-pgsql - The Apache Portable Runtime Utility Library - PostgreSQL Driver libdbd-pg-ruby1.9 - Ruby/DBI PostgreSQL driver for Ruby 1.9 % apt-cache search 'dbd.*-perl' | grep -i postgres libdbd-pg-perl - Perl DBI driver for the PostgreSQL database server postgresql-contrib-8.3 - additional facilities for PostgreSQL Regards, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/ `- GPG Public Key: 0x25BFB848 Please GPG sign your mail.
Attachment
On Fri, Jan 30, 2009 at 02:43:13PM -0800, Ron Mayer wrote: > I guess I'd still like some more convenient tuning of autovacuum (perhaps > specifying X mbps disk I/O); but I'd say vacuum fell off my pet-peeve list > around the 8.1 timeframe. ah yes, that reminds me. If I know what my disk subsystem is capable of I'd like to be able to say: VACUUM, please don't use more than 10MB/s. You can do it now with a calculator and a lot of reading the docs but surely Pg can work this out for itself. Nicest would be ofcourse a niceness level, so that VACUUM slows itself down according to the amount of queries going on (to a minimum ofcourse). Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Attachment
Gregory Stark wrote: >> MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without >> the need to know in advance the number of columns: >> http://msdn.microsoft.com/en-us/library/bb208956.aspx > > That's puzzling. I wonder what they do about clients requesting info about the > results. Or for that matter such queries being used in subqueries or anywhere > else where the surrounding code needs to know the type of results to expect. > Well as subqueries you would expect an error. Not sure with access but I know that with RealBasic you can handle the variations. This allows you to build an "access style" front end that isn't fixed to one db structure. dim rs as recordset rs = db.sqlquery("select...") for x = 1 to rs.fieldcount if vartype(rs.idxfield(x).value) = kTypeDate then ... ... next you could also use rs.idxfield(x).stringvalue and parse it yourself -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
On Sat, 31 Jan 2009 15:28:31 +0100, Martijn van Oosterhout wrote: > Nicest would be ofcourse a niceness level, so that VACUUM slows itself > down according to the amount of queries going on (to a minimum ofcourse). Linux has IO priority support for this, see ionice. Starting with 2.6.28 the CFQ scheduler behaviour is fixed (no more excessive write delays or bad read interaction), so autovacuum should really run in a lower priority class. -h
On Fri, 2009-01-30 at 14:25 +0000, Gregory Stark wrote: > "Daniel Verite" <daniel@manitou-mail.org> writes: > > > Gregory Stark wrote: > > > >> Is it the hierarchical query ability you're looking for or pivot? > >> The former we are actually getting in 8.4. > >> > >> AFAIK even in systems with pivot you still have to > >> declare a fixed list of columns in advance anyways. > >> Do you see a system where it works differently? > > > > MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without > > the need to know in advance the number of columns: > > http://msdn.microsoft.com/en-us/library/bb208956.aspx > > That's puzzling. I wonder what they do about clients requesting info about the > results. Or for that matter such queries being used in subqueries or anywhere > else where the surrounding code needs to know the type of results to expect. It doesn't really matter. Since crosstabs are just a presentational variation to a query with aggregate functions and GROUP BY clauses, whenever you need the results in a crosstabbed recordset for further processing you will either (a) know in advanced the final number of columns --as in "I want the count for this, this and this attribute: 3 columns + row header"--, or (b) get the information from the original aggregated subquery --as in "I want the longest path to traverse the tree/graph"--. So in fact, PG would be perfectly OK in not giving the information, since it is not needed. However, it may be counterintuitive, so it should be perfectly well documented. Also, even if PG would manage to provide the precise result characteristics in advance by evaluating the whole crosstab, the information would not be trustworthy, since it may well change in the next second. I understand that this, being a presentational issue, might get me some "this is not a DBMS issue"-like kind of responses, but (a) I definitely trust PG speed and reliability more than PHP/Java/whatever language and (b) I prefer to put all the hard work on the DB (or I would end up doing JOINs myself).
Thanks, using the same apt commands, try to find pg_config. (^; On Sat, 31 Jan 2009 12:38:18 +0000 Roger Leigh <rleigh@codelibre.net> wrote: > On Fri, Jan 30, 2009 at 03:44:48PM -0800, rhubbell wrote: > > On Fri, 30 Jan 2009 20:38:06 +0000 > > Gregory Stark <stark@enterprisedb.com> wrote: > > > > > > > > rhubbell <Rhubbell@iHubbell.com> writes: > > > > > > > Nope, had to find it in another package called libpq-dev. > > > > That's on UbuntuHardy. Maybe it's a maintainer problem? > > > > > > > > What logic would lead someone to separate pg_config from everything else? > > > > Do people often just install the server and nothing else? Then what? > > > > > > This is actually *required* by Debian/Ubuntu packaging rules. > > > > > > The development environment must be packaged separately from shared libraries > > > like libpq or else major snafus arise when a new soversion of libpq comes out. > > > You need to be able to have both versions installed simultaneously (in case > > > you have programs which require both) but that won't work if they both contain > > > things like header files or executables. > > > > Weren't .so born from a need to save disk space? Maybe startup speed too. > > Now they're a PITA. > > Not really. You just need to ensure that you have the correct development > environment for the version of PostgreSQL which you are targetting. While > it might appear to be unnecessarily complex, you'll find that there's a > very good reason for it. > > * Every library in Debian is split into separate runtime and development > packages (and also documentation). > * Users will only need the runtime. > * Only developers and build dæmons will need to install the -dev > packages). > * Multiple -dev packages can and do exist for supporting multiple > library versions, especially during transitions from one version to > the next. They can't generally be installed simultaneously > (conflicting files common to both such as pg_config), so you just > install the one you require. > > This saves valuable diskspace on end-user systems as well as allowing > for the creation of known sane build environments (look up how Debian > uses Build-Depends for automated package building). > > > > > BTW I ran into the need for pg_config upon installing DBD::Pg. > > > > Maybe DBD::Pg maintainer problem? > > > > > > Installing a package for DBD::Pg or building it? The former would indeed be a > > > package bug. > > > > When I installed the package I did via CPAN so maybe this was my mistake. > > Not every CPAN package is packaged for debian so I often times don't bother > > checking if a perl module exists in debian I just do > > perl -MCPAN -e 'install (DBD::Pg)' or whatever pkg.... > > It's always worth checking first (first line): > > % apt-cache search dbd | grep -i postgres > libdbd-pg-perl - Perl DBI driver for the PostgreSQL database server > libdbd-pg-ruby - Ruby/DBI PostgreSQL driver > libdbd-pg-ruby1.8 - Ruby/DBI PostgreSQL driver for Ruby 1.8 > libdbd-pgsql - PostgreSQL database server driver for libdbi > postgresql-contrib-8.3 - additional facilities for PostgreSQL > libaprutil1-dbd-pgsql - The Apache Portable Runtime Utility Library - PostgreSQL Driver > libdbd-pg-ruby1.9 - Ruby/DBI PostgreSQL driver for Ruby 1.9 > > % apt-cache search 'dbd.*-perl' | grep -i postgres > libdbd-pg-perl - Perl DBI driver for the PostgreSQL database server > postgresql-contrib-8.3 - additional facilities for PostgreSQL > > > Regards, > Roger > > -- > .''`. Roger Leigh > : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ > `. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/ > `- GPG Public Key: 0x25BFB848 Please GPG sign your mail. >
On Sat, Jan 31, 2009 at 10:10:01AM -0800, rhubbell wrote: > Thanks, using the same apt commands, try to find pg_config. Well, those commands search package names and metadata (including descriptions), and pg_config isn't mentioned so you won't find anything. Given that pg_config matches the version of postgresql you have installed, it's included in libpq-dev where you would expect (this is what all other library packages containing -config scripts do, though most have nowadays switched to using pkg-config and providing an associated .pc file). % dpkg -S $(which pg_config) libpq-dev: /usr/bin/pg_config Regards, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/ `- GPG Public Key: 0x25BFB848 Please GPG sign your mail.
On Sat, Jan 31, 2009 at 6:10 PM, rhubbell <Rhubbell@ihubbell.com> wrote:
> Thanks, using the same apt commands, try to find pg_config
$ apt-file search bin/pg_config
libpq-dev: /usr/bin/pg_config
postgresql-server-dev-8.3: /usr/lib/postgresql/8.3/bin/pg_config
That is confusing actually.
However, the readme for DBD::Pg which cpan gives a convenient
interface has installation instructions which specifically list the
requirements:
cpan[4]> readme DBD::Pg
...[lots of crap about downloading stuff]...
REQUIREMENTS:
-------------
build, test, and install Perl 5 (at least 5.6.1)
build, test, and install the DBI module (at least 1.52)
build, test, and install PostgreSQL (at least 7.4)
build, test, and install Test::Simple (at least 0.47)
DBD::Pg needs to know where to find the libpq libraries: this is usually done
by checking the output of the pg_config executable. If pg_config is
not available,
then you may need to install the development package for PostgreSQL. To do this
on Debian and Ubuntu, use: apt-get install postgresql-dev; on RedHat
and CentOS,
use: yum install postgresql-devel. Note that the development libraries
are needed
even if you already have PostgreSQL up and running.
--
greg
On Sat, Jan 31, 2009 at 5:34 PM, Octavio Alvarez <alvarezp@alvarezp.ods.org> wrote: > > It doesn't really matter. Since crosstabs are just a presentational > variation to a query with aggregate functions and GROUP BY clauses, Why are crosstabs just a presentation issue any more than GROUP BY or ORDER BY? -- greg
On 2009-01-29, Steve Crawford <scrawford@pinpointresearch.com> wrote: > >>> 3. Date handling >>> Sometimes I've got data with invalid dates and it would be great if it >>> could replace all the bad ones with, say "0000-00-00". >>> 0000-00-00 doesn't fit in a date column. perhaps you could use null? write a function that casts and catches the exception caused when bad dates are attempted and returens null, use it to translate your data.
On 2009-01-30, Steve Crawford <scrawford@pinpointresearch.com> wrote: > >> You can however pull it from a -Fc backup with pg_restore. Just FYI. >> >> Joshua D. Drake >> > > Or strip it from a pg_dump/pg_dumpall with sed. Or write your own > function-dumper based on ideas gleaned from various notes/comments on > the web (my approach). > > I had not thought of using the -Fc approach but it appears that that > would require dumping the whole database then using pg_restore to pull > the function definition from the dump. not the whole database, you can use --schema-only this can save significant pipe bandwidth. Is it possible to get pg_restore to list just the named function? > One other thing that would be nice to have for function-dumping whether > in pg_dump or using the -Fc approach would be the ability to dump all > functions of a given name instead of having to go one-by-one. It's > pretty unusual for identically-named functions to have unrelated purposes. but sometimes you only want one of them. in summary it'd be nice to have an equivalent of pg_restore's "--function=NAME(args)" option on pg_dump and to have the "(args)" part optional.
On Sat, Jan 31, 2009 at 11:10 AM, rhubbell <Rhubbell@ihubbell.com> wrote: > Thanks, using the same apt commands, try to find pg_config. > (^; It's easy: /home/smarlowe$ pg_config The program 'pg_config' is currently not installed. You can install it by typing: sudo apt-get install libpq-dev bash: pg_config: command not found I'm running ubuntu 8.04.1.
On Sat, 2009-01-31 at 18:32 +0000, Greg Stark wrote: > On Sat, Jan 31, 2009 at 5:34 PM, Octavio Alvarez > <alvarezp@alvarezp.ods.org> wrote: > > > > It doesn't really matter. Since crosstabs are just a presentational > > variation to a query with aggregate functions and GROUP BY clauses, > > > Why are crosstabs just a presentation issue any more than GROUP BY or ORDER BY? If I understood your question correctly, it is because you can take any crosstab and convert it to a vertical list of values that generate it, with a maximum number of columns, and easily WHERE-able. For example, a accumulative percent-based grade report: +-------------+----+----+----+----+---------+ | Assignment | P1 | P2 | P3 | P4 | Average | +-------------+----+----+----+----+---------+ | Assignment1 | 95 | 90 | 99 | | 94.67 | | Assignment2 | 90 | 90 | 91 | | 90.33 | | Assignment3 | 85 | 80 | 95 | | 86.67 | +-------------+----+----+----+----+---------+ The data source is: +-------------+--------+-------+ | Assignment | Period | Value | +-------------+--------+-------+ | Assignment1 | P1 | 95 | | Assignment1 | P2 | 90 | | Assignment1 | P3 | 99 | | Assignment2 | P1 | 90 | | Assignment2 | P2 | 90 | | Assignment2 | P3 | 91 | | Assignment3 | P1 | 85 | | Assignment3 | P2 | 80 | | Assignment3 | P3 | 95 | +-------------+--------+-------+ ... even if P4 doesn't exist yet. You can have a crosstab with a dynamic number of columns where P4 doesn't appear as a column, or a pre-defined number of columns. And even if each value from the crosstab is an aggregate function like count() or max() the data source first is prepared as a select/where and maybe a group by and then transformed into a crosstab. In any case, the results are the same as GROUPing BY from the data source. +-------------+---------+ | Assignment | Average | +-------------+---------+ | Assignment1 | 94.67 | | Assignment2 | 90.33 | | Assignment3 | 86.67 | +-------------+---------+ A crosstab is not but a presentational transform of the data set. Any information you would eventually need can be taken from the original data source, one way or another. That's why dynamic-column crosstab are not a problem, and the DBMS should not worry about providing the information about the columns, maybe by simply not allowing the dynamic-column ones in subqueries.
Octavio Alvarez <alvarezp@alvarezp.ods.org> writes: > In any case, the results are the same as GROUPing BY from the data > source. > +-------------+---------+ > | Assignment | Average | > +-------------+---------+ > | Assignment1 | 94.67 | > | Assignment2 | 90.33 | > | Assignment3 | 86.67 | > +-------------+---------+ > > A crosstab is not but a presentational transform of the data set. Any > information you would eventually need can be taken from the original > data source, one way or another. That's why dynamic-column crosstab are > not a problem, and the DBMS should not worry about providing the > information about the columns, maybe by simply not allowing the > dynamic-column ones in subqueries. What about a WHERE clause like WHERE P1 > P2 -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
On Sat, 2009-01-31 at 23:36 +0000, Gregory Stark wrote: > Octavio Alvarez <alvarezp@alvarezp.ods.org> writes: > > > A crosstab is not but a presentational transform of the data set. Any > > information you would eventually need can be taken from the original > > data source, one way or another. That's why dynamic-column crosstab are > > not a problem, and the DBMS should not worry about providing the > > information about the columns, maybe by simply not allowing the > > dynamic-column ones in subqueries. > > What about a WHERE clause like > > WHERE P1 > P2 You could either: (1) do "FROM grades AS g1 INNER JOIN grades AS g2 ON g1.P1 > g2.P2", generating the record set before applying the crosstab transformation. (2) Since you are implying the existence of P1 and P2, use the fixed-number-of-columns crosstab case instead, for which PG would actually be able to give the column definitions without running the query, and after that, joining the results with some other query.
My two:
* lack of PK/unique indexes on inherited tables (workarounds possible but annoying)
* lack of auto-tuning or tuning tools (or perhaps my lack of awareness of them?)
-Reece
* lack of PK/unique indexes on inherited tables (workarounds possible but annoying)
* lack of auto-tuning or tuning tools (or perhaps my lack of awareness of them?)
-Reece
--Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 |
On Sat, 2009-01-31 at 15:54 -0800, Octavio Alvarez wrote:
> On Sat, 2009-01-31 at 23:36 +0000, Gregory Stark wrote:
> > Octavio Alvarez <alvarezp@alvarezp.ods.org> writes:
> >
> > What about a WHERE clause like
> >
> > WHERE P1 > P2
>
> You could either:
>
> (1) do "FROM grades AS g1 INNER JOIN grades AS g2 ON g1.P1 > g2.P2",
> generating the record set before applying the crosstab transformation.
Just to remove all the stupid things I said about the first solution to the WHERE P1 > P2 problem:
Your grades table would be defined as:
test=# \d grades
Table "public.grades"
Column | Type | Modifiers
--------+-------------------+-----------
st | character varying |
su | character varying |
p | bigint |
gr | bigint |
Indexes:
"grades_st_key" UNIQUE, btree (st, p, su)
st = student; su = subject; p = period; gr = grade
The non-crosstab query that gives you the recordset for the crosstab, would be:
SELECT p2_gt_p1.st, p2_gt_p1.su, grades.p, grades.gr
FROM (
SELECT g1.st, g1.su, g1.p, g1.gr, g2.p, g2.gr
FROM grades g1 INNER JOIN grades g2
ON g1.st = g2.st
AND g1.su = g2.su AND g2.p = 2
AND g1.p = 1 AND g2.gr > g1.gr
) AS p2_gt_p1
LEFT JOIN grades USING (st, su);
On Sat, 31 Jan 2009, Reece Hart wrote: > * lack of auto-tuning or tuning tools (or perhaps my lack of awareness > of them?) http://pgfoundry.org/projects/pgtune/ aims to provide a tool for 8.4, that's working but still needs documentation and some loose ends cleaned up. Its suggestions aren't good yet for Windows systems yet, that's the biggest bug left in there. That's aimed to automate the suggestions set out in http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server One day I'll make that work better with older versions too. It does basically the right thing for 8.3 already but could be smarter, it includes some parameters that aren't there in 8.2, and doesn't work at all on 8.1 or earlier. If you step outside of just free solutions, Enterprise DB's commercial server product does more complicated autotuning via their DynaTune feature. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Octavio Alvarez wrote: > On Fri, 2009-01-30 at 15:32 -0500, Bruce Momjian wrote: > > Octavio Alvarez wrote: > > > On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote: > > > > So, what do people say? Is Postgres perfect in your world or does > > it > > > > do some > > > > things which rub you the wrong way? > > > > > > For me: > > > > > > Lack of column-level privileges. It just doesn't help scalability at > > > all. You end up having different tables each with different > > permissions, > > > or having to create a view with a ruleset attached, having to update > > the > > > view for each definition change in the view. > > > > Column-level privileges will be in Postgres 8.4. > > That is one hell of great news! > > It is not marked as "Done" in the To-do list in the Wiki, though. > > http://wiki.postgresql.org/wiki/Todo#GRANT.2FREVOKE Oh, sorry, marked as done now: http://wiki.postgresql.org/wiki/Todo#GRANT.2FREVOKE -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
My short list is: * in-place upgrade * named parameters in SQL functions * native jobs * timestamptz that preserves original timezone (not offset but political timezone !!!! like America/New_York) * I hate: "select * from dblink(...) as WHY(I_NEED int4, TO_SPECIFY int4, THIS text)" * ability to call set-returning plpgsql function in SELECT, not in FROM * stat collector is really greedy by definition even when system is idle, when you have really really many relations * lost space when dropping toasted column - can be recovered only by reindexing table (are there other such a strange cases ??)
wstrzalka wrote: > * stat collector is really greedy by definition even when system is > idle, when you have really really many relations I think this will be fixed in 8.4. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Feb 2, 8:23 pm, br...@momjian.us (Bruce Momjian) wrote: > wstrzalka wrote: > > * stat collector is really greedy by definition even when system is > > idle, when you have really really many relations > > I think this will be fixed in 8.4. > That would by great news for "mine" cluster.
Gregory Stark wrote: > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, > but I would be interested to hear if people have any complaints from personal > experience. What would be most interesting is if you can explain an example of > when the problem caused real inconvenience to you, since sometimes it's hard > to see from a theoretical description where the real harm lies. > > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? > > Feel free to respond on-list or if you prefer in personal emails. I do intend > to use the ideas you give in my presentation so mark anything you wouldn't be > happy to see in a slide at a conference some day. > A few from the top of my head: - COPY command does not support collation. It's such a pita to massage huge files that have "," has a decimal separator. - COPY command does not have a "DO NOT ABORT ON ERROR". I do not mean constraint checking, I mean that the occasional backslash characters or rows with a badly encoded characters should not abort the import process. - No automatic CLUSTERing. - EXPLAIN does not work with functions. -- Luis Neves
On Thu, Jan 29, 2009 at 8:16 AM, Gregory Stark <stark@enterprisedb.com> wrote: > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? Things I'd particularly like to have that aren't entirely on the map yet: - In place upgrade - Stored procedures that can manage transactions (e.g. - contrast with present stored functions that forcibly live *inside* a transaction context; the point isn't functions vs procedures, but rather to have something that can do txn management) I'd expect that txn-managing procedures could then enable various sorts of usages involving other interesting bits: - Managing 2PC - Managing NOTIFY/LISTEN - Managing jobs (e.g. - "pgcron") - Using dblink()/SQL-MED to manage cross-DB work I'd expect these all to be the sorts of side-effects enabled by the one change... -- http://linuxfinances.info/info/linuxdistributions.html Calvin Trillin - "Health food makes me sick."
> - EXPLAIN does not work with functions. +1 and one more about explain - it would be great to have smth like: EXPLAIN ANALYZE FULL - that would show details about the plan chosen with detailed explanation and other plans considered. It would reduce a few posts a week in style: - 'why the query A doesn't use index B' with answer - '... planner .... estimated cost ....' I think it would help a lot of people if the community don't want to have hints.
Christopher Browne <cbbrowne@gmail.com> writes: > - Managing jobs (e.g. - "pgcron") A number of people have mentioned a job scheduler. I think a job scheduler entirely inside Postgres would be a terrible idea. However a cron daemon which used Postgres as a storage backend would be very cool. It could then provide SQL functions to manipulate the schedule and allow you to set jobs that call database functions using the existing connection instead of forcing you to write an external script. This is something someone could do with no special database background, the hard part is finding a cron source base which is flexible enough to extend to use a database backend. I'm under the impression most cron daemons are based on pretty old and ossified source bases and are burdened by a lot of legacy compatibility requirements. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
You realise you just described the very project you saw me write a presentation on today right? :-p On 2/2/09, Gregory Stark <stark@enterprisedb.com> wrote: > Christopher Browne <cbbrowne@gmail.com> writes: > >> - Managing jobs (e.g. - "pgcron") > > A number of people have mentioned a job scheduler. I think a job scheduler > entirely inside Postgres would be a terrible idea. > > However a cron daemon which used Postgres as a storage backend would be very > cool. It could then provide SQL functions to manipulate the schedule and > allow > you to set jobs that call database functions using the existing connection > instead of forcing you to write an external script. > > This is something someone could do with no special database background, the > hard part is finding a cron source base which is flexible enough to extend > to > use a database backend. I'm under the impression most cron daemons are based > on pretty old and ossified source bases and are burdened by a lot of legacy > compatibility requirements. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's 24x7 Postgres support! > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Gregory Stark wrote: > Christopher Browne <cbbrowne@gmail.com> writes: > >> - Managing jobs (e.g. - "pgcron") > > A number of people have mentioned a job scheduler. I think a job scheduler > entirely inside Postgres would be a terrible idea. PgFoundry already has a project called "Job Scheduler". -- Guy Rouillier
> - COPY command does not support collation. It's such a pita to massage > huge files that have "," has a decimal separator. copy with delimiter '#######' http://www.postgresql.org/docs/current/static/sql-copy.html -- Postgresql & php tutorials http://www.designmagick.com/
On Thu, Jan 29, 2009 at 5:43 PM, David Fetter <david@fetter.org> wrote: >> >> > * CTEs not yet integrated into the adjacency lists in pg_catalog, >> > etc. >> >> I'm not sure what you're referring to here either. > > The DAG structures in pg_depend leap to mind. There's no view that > shows the actual dependencies, except in the sense of, "Here's the > edges. Figure it out for yourself." I'm trying to write some recursive queries for pg_depend and pg_locks. I think if we come up with some nice ones we might want to add them to the system views. pg_depend is actually pretty boring, you would see the same stuff if you just did a DROP foo RESTRICT after all. I am finding that I'm really wanting depth first searches which would be easier to read. That would be interesting direction to head. pg_locks would be a *lot* more interesting imho. It's awfully hard to decipher the pg_locks table and find the important information buried in lots of extraneous minor locks which aren't blocking anything. However I'm finding it really hard to write anything useful for pg_locks. It seems we're missing a lot of basic info in pg_locks and basic infrastructure to make sense of it. Notably, there's no indication of which lock wait queue the ungranted locks are in. That means to find out what's blocking a lock would require comparing every other lock to it and deciding whether it conflicts. I haven't thought hard about the pros and cons of adding more info to pg_locks versus implementing redundant logic in SQL to mirror C code. Neither seems terribly enticing offhand. I wonder if anybody else has already implemented something like lock_conflicts()? -- greg
On Tue, Feb 03, 2009 at 05:48:51PM +0000, Greg Stark wrote: > On Thu, Jan 29, 2009 at 5:43 PM, David Fetter <david@fetter.org> wrote: > >> > >> > * CTEs not yet integrated into the adjacency lists in pg_catalog, > >> > etc. > >> > >> I'm not sure what you're referring to here either. > > > > The DAG structures in pg_depend leap to mind. There's no view that > > shows the actual dependencies, except in the sense of, "Here's the > > edges. Figure it out for yourself." > > I'm trying to write some recursive queries for pg_depend and pg_locks. > I think if we come up with some nice ones we might want to add them to > the system views. Would this be a good time to revisit the idea of a pg_system_views schema? > pg_depend is actually pretty boring, you would see the same stuff if > you just did a DROP foo RESTRICT after all. Ass-u-me'ing that you have DDL permissions, which the vast majority of roles should not. > I am finding that I'm really wanting depth first searches which > would be easier to read. That would be interesting direction to > head. Depth-first searches are pretty easy to arrange with arrays. :) > pg_locks would be a *lot* more interesting imho. It's awfully hard > to decipher the pg_locks table and find the important information > buried in lots of extraneous minor locks which aren't blocking > anything. > > However I'm finding it really hard to write anything useful for > pg_locks. It seems we're missing a lot of basic info in pg_locks > and basic infrastructure to make sense of it. > > Notably, there's no indication of which lock wait queue the > ungranted locks are in. That means to find out what's blocking a > lock would require comparing every other lock to it and deciding > whether it conflicts. Interesting :) > I haven't thought hard about the pros and cons of adding more info > to pg_locks versus implementing redundant logic in SQL to mirror C > code. Neither seems terribly enticing offhand. > > I wonder if anybody else has already implemented something like > lock_conflicts()? Dunno. Could such a thing live in userland, or would it have to be compiled in? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Here's a few more pet peeves. I'm not sure if any of these are known
bugs or just me being picky.
--Chris
--------------------------------------------------
1. Having to rewrite entire tables out to disk the first time I scan
them, for example:
CREATE TABLE t1 AS ...; -- writes 100 GB to disk
CREATE INDEX i1 ON t1 ...; -- rewrites 100 GB to disk
The main issue is setting the hint bits for each tuple, which IMO should
initially be set for "CREATE TABLE AS" statements. To work around this
for now, I modified heap_insert (in heapam.c) to mark tuples as
committed when inserting them into newly added pages without WAL:
/*
* Optimization for CREATE TABLE AS statements: mark tuples as committed
* to prevent rewriting them to disk upon first use. This is safe since
* the new relation is not visible until the transaction commits anyway.
*/
if (!use_wal && !use_fsm)
{
tup->t_data->t_infomask |= HEAP_XMIN_COMMITTED;
}
--------------------------------------------------
2. Having to use a subquery and/or "OFFSET 0" to prevent multiple calls
to immutable functions returning composite types, for example:
CREATE TYPE three AS
(i integer, j integer, k integer);
CREATE FUNCTION hello()
RETURNS three AS $$
DECLARE ret three;
BEGIN
RAISE NOTICE 'hello';
ret := ROW(1,2,3);
RETURN ret;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- function called three times
SELECT (hello()).*;
-- function called only once
SELECT (h).* FROM (
SELECT hello() AS h
) AS sub;
-- function called three times
SELECT (h).* FROM (
SELECT hello() AS h
FROM generate_series(1,3)
) AS sub;
-- function called only once
SELECT (h).* FROM (
SELECT hello() AS h
FROM generate_series(1,3)
OFFSET 0
) AS sub;
--------------------------------------------------
3. Poor estimates for n_distinct can result in out of memory errors.
For example, I have a user-defined aggregate over a user-defined data
type (both written in C). The aggregate doesn't take much memory, but
the data type can be relatively large (i.e. "STORAGE = extended"). My
table has five million distinct rows, but n_distinct is around 50,000
(increasing the stats target helps a little, but it's still way off).
As a result the planner choses "SeqScan + HashAgg" instead of "IndexScan
+ GroupAgg", and the query aborts when the hash table eventually runs
out of memory.
I currently work around this issue using "SET enable_hashagg TO off;"
when necessary.
What about postgreSQL's inability to re-order columns? Please don't point out that I shouldn't rely on things being in a certain order when I SELECT * FROM table. I'm well aware of that, I just generally have an aesthetic preference for a table's columns being in a certain order. Regards, Peter Geoghegan
On Feb 3, 2009, at 12:41 PM, Peter Geoghegan wrote: > What about postgreSQL's inability to re-order columns? > > Please don't point out that I shouldn't rely on things being in a > certain order when I SELECT * FROM table. I'm well aware of that, I > just generally have an aesthetic preference for a table's columns > being in a certain order. It's a pet peeve. You don't need to justify it. :) Cheers, Steve
On Mon, 2009-02-02 at 22:48 +0000, Gregory Stark wrote: > Christopher Browne <cbbrowne@gmail.com> writes: > > > - Managing jobs (e.g. - "pgcron") > > A number of people have mentioned a job scheduler. I think a job scheduler > entirely inside Postgres would be a terrible idea. You probably should explain why you think that rather than just rule it out, though I don't think we should be editing what people ask for. We already have autovacuum, which runs VACUUM and ANALYZE to a set schedule. We could have kept that outside core, but didn't. It's not too big a stretch to imagine we could redesign autovacuum as a GP scheduler, with autovacuum as just one/two regular scheduled jobs. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
On Tue, 3 Feb 2009, Greg Stark wrote: > Notably, there's no indication of which lock wait queue the ungranted > locks are in. That means to find out what's blocking a lock would > require comparing every other lock to it and deciding whether it > conflicts. The tool I find myself wanting here would parse pg_locks, find everything that wasn't granted, scan through looking for the source of contention as you describe, try to look up what any blockers are doing via pg_stat_activity, then report on its findings. That's not so difficult to do by hand that I've bothered automating it completely for the occasional time this pops up. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Tue, 2009-02-03 at 15:03 -0500, Chris Mayfield wrote: > 1. Having to rewrite entire tables out to disk the first time I scan > them, for example: > > CREATE TABLE t1 AS ...; -- writes 100 GB to disk > CREATE INDEX i1 ON t1 ...; -- rewrites 100 GB to disk > > The main issue is setting the hint bits for each tuple, which IMO should > initially be set for "CREATE TABLE AS" statements. To work around this > for now, I modified heap_insert (in heapam.c) to mark tuples as > committed when inserting them into newly added pages without WAL: I'll take this for 8.5. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
On Mon, Feb 2, 2009 at 5:48 PM, Gregory Stark <stark@enterprisedb.com> wrote: > Christopher Browne <cbbrowne@gmail.com> writes: > >> - Managing jobs (e.g. - "pgcron") > > A number of people have mentioned a job scheduler. I think a job scheduler > entirely inside Postgres would be a terrible idea. I think it's a terrible idea to put words in my mouth. I didn't propose including a job scheduler inside every Postgres instance, and find it disappointing that that is what is assumed, particularly when none of the other usage cases implied any such thing. -- http://linuxfinances.info/info/linuxdistributions.html Gilda Radner - "Adopted kids are such a pain - you have to teach them how to look like you."
Gregory Stark wrote: > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? As a further take on the auto-tuning others have mentioned, how about some auto-indexing? - Jeremy
On Tue, Feb 3, 2009 at 7:04 PM, David Fetter <david@fetter.org> wrote: > >> Notably, there's no indication of which lock wait queue the >> ungranted locks are in. That means to find out what's blocking a >> lock would require comparing every other lock to it and deciding >> whether it conflicts. > > Interesting :) It would probably be more interesting if what I wrote made sense. I think I mixed things up enoug that it doesn't though. I'll have to read through the locking code and figure out the right way to say it tomorrow. >> I haven't thought hard about the pros and cons of adding more info >> to pg_locks versus implementing redundant logic in SQL to mirror C >> code. Neither seems terribly enticing offhand. >> >> I wonder if anybody else has already implemented something like >> lock_conflicts()? > > Dunno. Could such a thing live in userland, or would it have to be > compiled in? Sure, it's just tedious and error-prone. You compare all the fields of pg_locks and implement the same rules our locking code follows. -- greg
On Tue, Feb 3, 2009 at 10:06 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > > On Tue, 2009-02-03 at 15:03 -0500, Chris Mayfield wrote: > >> 1. Having to rewrite entire tables out to disk the first time I scan >> them, for example: >> >> CREATE TABLE t1 AS ...; -- writes 100 GB to disk >> CREATE INDEX i1 ON t1 ...; -- rewrites 100 GB to disk >> >> The main issue is setting the hint bits for each tuple, which IMO should >> initially be set for "CREATE TABLE AS" statements. To work around this >> for now, I modified heap_insert (in heapam.c) to mark tuples as >> committed when inserting them into newly added pages without WAL: > > I'll take this for 8.5. This was proposed once already and some difficulties were identified. Do you remember what they were? -- greg
On Tue, 3 Feb 2009, Jeremy Harris wrote: > As a further take on the auto-tuning others have mentioned, > how about some auto-indexing? That's a significantly harder problem than auto-tuning. http://it.toolbox.com/blogs/database-soup/finding-useless-indexes-28796 is a good intro to a subset of that topic, figuring out which indexes you don't need. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Tue, Feb 3, 2009 at 9:27 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > > On Mon, 2009-02-02 at 22:48 +0000, Gregory Stark wrote: >> Christopher Browne <cbbrowne@gmail.com> writes: >> >> > - Managing jobs (e.g. - "pgcron") >> >> A number of people have mentioned a job scheduler. I think a job scheduler >> entirely inside Postgres would be a terrible idea. > > You probably should explain why you think that rather than just rule it > out, though I don't think we should be editing what people ask for. Sorry, I was even the one who said this should be an open invitation to gripe. The point I was making is that the original proposal could be interpreted to mean two different things. One of which I think is an actively bad idea and one of which I think is actually a good idea. I also didn't mean to imply Christopher meant one or the other -- sorry. > We already have autovacuum, which runs VACUUM and ANALYZE to a set > schedule. We could have kept that outside core, but didn't. > > It's not too big a stretch to imagine we could redesign autovacuum as a > GP scheduler, with autovacuum as just one/two regular scheduled jobs. Except autovacuum *isn't* a regularly scheduled job and doesn't run vacuum and analyze on a set schedule. It runs them on a highly dynamic schedule based on observations of activity in the database. It also has privileged access to the database, reading from all databases and receiving signals when various events occur. You cannot implement autovacuum's current behaviour in cron no matter how clever you make cron. A client reimplementing cron using the database as a backend would be a nice database-driven tool, not just for DBAs but for anyone needing a more flexible cron demon. It wouldn't even have to be Postgres-specific, any SQL database would surely be sufficient for the job. If I were writing it I would start in Perl but I hear the kids these days are all doing Python or Ruby or something. My only point was that this would be very different from Oracle-style job scheduler implemented *inside* the database using database-specific code and requiring database-specific code to interact with the outside world. That's just reimplementing the whole world using the database as a weird operating system which is someone else's game. As I said, I don't know which Christopher was thinking of, apparently not the latter based on his subsequent response. But in case anyone else was or simply hadn't realized there were two different conceptions of this I wanted to draw the distinction. -- greg
Greg Stark wrote: > My only point was that this would be very different from Oracle-style > job scheduler implemented *inside* the database using > database-specific code and requiring database-specific code to > interact with the outside world. That's just reimplementing the whole > world using the database as a weird operating system which is someone > else's game. And someone else might want to play that game inside PG ;). Seriously, we already have programs running inside the DB (stored procs), so why not jobs? I can think of several useful applications. I have an application with a high volume of inserts (60M + per day). Maybe I can conceive of some way to reorganize the previous day's data at 2 am each morning that will provide much better performance. Since all that activity is inside the database, why not schedule it inside the DB also? It's the same logic to justify stored procs. Sure, I can accomplish the same thing via cron and external scripts. But that's less secure, since I need to store my connection params in the script. And if I've got 5 different servers running cron jobs, then my schedule is distributed over those 5 boxes, which becomes a management issue. As has been pointed out here, the schedule could be kept in the DB, which would address that. Having a scheduler in the DB to run those jobs is just the next step. Different stokes, as they say. All about choice. -- Guy Rouillier
On Tue, Feb 3, 2009 at 8:58 PM, Guy Rouillier <guyr-ml1@burntmail.com> wrote: > Greg Stark wrote: >> >> My only point was that this would be very different from Oracle-style >> job scheduler implemented *inside* the database using >> database-specific code and requiring database-specific code to >> interact with the outside world. That's just reimplementing the whole >> world using the database as a weird operating system which is someone >> else's game. > > And someone else might want to play that game inside PG ;). Seriously, we > already have programs running inside the DB (stored procs), so why not jobs? > I can think of several useful applications. I have an application with a > high volume of inserts (60M + per day). Maybe I can conceive of some way to > reorganize the previous day's data at 2 am each morning that will provide > much better performance. Since all that activity is inside the database, > why not schedule it inside the DB also? It's the same logic to justify > stored procs. Yep, this allows enough separation from OS and db that I can give users permission to schedule jobs in the db without needing to have an account on my db server or a cronjob connection remotely and anonymously from who knows what machine.
Guy Rouillier wrote: > And someone else might want to play that game inside PG ;). In fact, given how extensible PG is in other ways, it's surprising there hasn't been more call for it. Perhaps the fact there there's presently no facility for stored procedures to easily manage transactions has something to do with it? An internal job scheduler with the ability to fire jobs on certain events as well as on a fixed schedule could be particularly handy in conjunction with true stored procedures that could explicitly manage transactions. -- Craig Ringer
Craig Ringer wrote: > An internal job scheduler with the ability to fire jobs on certain > events as well as on a fixed schedule could be particularly handy in > conjunction with true stored procedures that could explicitly manage > transactions. Craig, what kind of "events" are you thinking about? Triggers are already pieces of code that run upon "certain events", namely insert, update or delete events. What others do you have in mind? -- Guy Rouillier
Guy Rouillier wrote: > Craig Ringer wrote: >> An internal job scheduler with the ability to fire jobs on certain >> events as well as on a fixed schedule could be particularly handy in >> conjunction with true stored procedures that could explicitly manage >> transactions. > > Craig, what kind of "events" are you thinking about? Triggers are > already pieces of code that run upon "certain events", namely insert, > update or delete events. What others do you have in mind? That's a good point, actually. I can't think of much you can't do with a trigger (SECURITY DEFINER if necessary) on a table. Not thinking straight. -- Craig Ringer
On Tue, Feb 3, 2009 at 10:09 PM, Craig Ringer <craig@postnewspapers.com.au> wrote: > Guy Rouillier wrote: >> >> Craig Ringer wrote: >>> >>> An internal job scheduler with the ability to fire jobs on certain events >>> as well as on a fixed schedule could be particularly handy in conjunction >>> with true stored procedures that could explicitly manage transactions. >> >> Craig, what kind of "events" are you thinking about? Triggers are already >> pieces of code that run upon "certain events", namely insert, update or >> delete events. What others do you have in mind? > > That's a good point, actually. I can't think of much you can't do with a > trigger (SECURITY DEFINER if necessary) on a table. Not thinking straight. other than schedule it to go off at certain times, not much you can't do.
On Wed, 2009-02-04 at 03:00 +0000, Greg Stark wrote: > We already have autovacuum, which runs VACUUM and ANALYZE to a set > > schedule. We could have kept that outside core, but didn't. > > > > It's not too big a stretch to imagine we could redesign autovacuum > as a > > GP scheduler, with autovacuum as just one/two regular scheduled > jobs. > > Except autovacuum *isn't* a regularly scheduled job and doesn't run > vacuum and analyze on a set schedule. It runs them on a highly dynamic > schedule based on observations of activity in the database. It also > has privileged access to the database, reading from all databases and > receiving signals when various events occur. You cannot implement > autovacuum's current behaviour in cron no matter how clever you make > cron. So putting a scheduler inside the database allows it to do things it couldn't otherwise do. Sounds like a great argument for *inclusion*. AV runs every (configurable) 60 secs. What it does when it runs is its own business. It has a pool of slaves ready to do real transactional work and an infrastructure to preferentially cancel work if it interferes with users. It's clearly a great place to hang other code that (somebody) would like to run on a regular basis: * regular maintenance tasks * performance tuning * summary table creation/maintenance * adding partitions * health checks * etc We can keep adding processes every time we want a new function in the db, or we can add a generic facility. I've already added two special processes, so I'd rather not add too many more. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
On Wed, 2009-02-04 at 02:39 +0000, Greg Stark wrote: > On Tue, Feb 3, 2009 at 10:06 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > > > > On Tue, 2009-02-03 at 15:03 -0500, Chris Mayfield wrote: > > > >> 1. Having to rewrite entire tables out to disk the first time I scan > >> them, for example: > >> > >> CREATE TABLE t1 AS ...; -- writes 100 GB to disk > >> CREATE INDEX i1 ON t1 ...; -- rewrites 100 GB to disk > >> > >> The main issue is setting the hint bits for each tuple, which IMO should > >> initially be set for "CREATE TABLE AS" statements. To work around this > >> for now, I modified heap_insert (in heapam.c) to mark tuples as > >> committed when inserting them into newly added pages without WAL: > > > > I'll take this for 8.5. > > This was proposed once already and some difficulties were identified. > Do you remember what they were? Time, mainly. Technical issues were not insurmountable, just more subtle than I had originally thought. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
> > Craig, what kind of "events" are you thinking about? Triggers are > > already pieces of code that run upon "certain events", namely insert, > > update or delete events. What others do you have in mind? > > That's a good point, actually. I can't think of much you can't do with a > trigger (SECURITY DEFINER if necessary) on a table. Not thinking straight. I would find ON CONNECT/DISCONNECT triggers very useful. Probably this is more similar to database-wide assertions. Karsten -- Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger01
Gregory Stark wrote: > Steve Crawford <scrawford@pinpointresearch.com> writes: > >>>> 3. Date handling >>>> Sometimes I've got data with invalid dates and it would be great if it >>>> could replace all the bad ones with, say "0000-00-00". >>>> >> Oh dear $DEITY, no. > > I think it would be best if we limited ourselves right now to discussing the > problems themselves and not debating the pros and cons of possible solutions. It seems I need to point out that the 0000-00-00 thing was supposed to be a joke. -- Richard Huxton Archonet Ltd
On Feb 3, 2009, at 11:55 PM, Guy Rouillier wrote: > Craig Ringer wrote: >> An internal job scheduler with the ability to fire jobs on certain >> events as well as on a fixed schedule could be particularly handy >> in conjunction with true stored procedures that could explicitly >> manage transactions. > > Craig, what kind of "events" are you thinking about? Triggers are > already pieces of code that run upon "certain events", namely > insert, update or delete events. What others do you have in mind? What about LISTEN/NOTIFY events? That would be one way to create autonomous transactions. Cheers, M
Karsten Hilbert wrote: >>> Craig, what kind of "events" are you thinking about? Triggers are >>> already pieces of code that run upon "certain events", namely insert, >>> update or delete events. What others do you have in mind? >> That's a good point, actually. I can't think of much you can't do with a >> trigger (SECURITY DEFINER if necessary) on a table. Not thinking straight. > > I would find ON CONNECT/DISCONNECT triggers very useful. Probably > this is more similar to database-wide assertions. But a job scheduler would not help with that. Perhaps you intended your comment to fall into the "pet peeves" bucket rather than the "job scheduler" bucket. -- Guy Rouillier
On Feb 3, 2009, at 3:41 PM, Peter Geoghegan wrote: > What about postgreSQL's inability to re-order columns? > > Please don't point out that I shouldn't rely on things being in a > certain order when I SELECT * FROM table. I'm well aware of that, I > just generally have an aesthetic preference for a table's columns > being in a certain order. Somewhat related, it would be nice if columns had a unique identifier in the catalog rather than just a sequence number for the table. This would make it possible to distinguish between altering a column versus dropping/adding when comparing schemas or detecting DDL changes. John DeSoi, Ph.D.
On Wed, Feb 04, 2009 at 12:37:31PM -0500, Guy Rouillier wrote: > Karsten Hilbert wrote: >>>> Craig, what kind of "events" are you thinking about? Triggers are >>>> already pieces of code that run upon "certain events", namely >>>> insert, update or delete events. What others do you have in mind? >>> That's a good point, actually. I can't think of much you can't do >>> with a trigger (SECURITY DEFINER if necessary) on a table. Not >>> thinking straight. >> >> I would find ON CONNECT/DISCONNECT triggers very useful. Probably >> this is more similar to database-wide assertions. > > But a job scheduler would not help with that. Perhaps you intended your > comment to fall into the "pet peeves" bucket rather than the "job > scheduler" bucket. Yep, just like the Subject suggested ;-) The logic is a bit twisted but I was thinking "other kinds of events - oh, I'd like to be able to make something happen on this event". Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
I dream about db wide checks on tables, without need to write expensive triggers. Basically, something that would run a select query after insert/update/delete and based on result commit or rollback. unless there's something like that already in SQL (I am not aware of all features in sql2008 draft).
On Wed, 2009-02-04 at 14:09 +0900, Craig Ringer wrote: > Guy Rouillier wrote: > > Craig Ringer wrote: > >> An internal job scheduler with the ability to fire jobs on certain > >> events as well as on a fixed schedule could be particularly handy in > >> conjunction with true stored procedures that could explicitly manage > >> transactions. > > > > Craig, what kind of "events" are you thinking about? Triggers are > > already pieces of code that run upon "certain events", namely insert, > > update or delete events. What others do you have in mind? > > That's a good point, actually. I can't think of much you can't do with a > trigger (SECURITY DEFINER if necessary) on a table. Not thinking straight. True, but the trigger does its work while the user waits. If we have a 30 min task, we don't want to just tack that on to the end of a random insert. As A.M. says elsewhere, it would be good to have a trigger that fired a NOTIFY that was picked up by a scheduled job that LISTENs every 10 minutes for certain events. We need a place for code that is *not* directly initiated by a user's actions, yet works as part of a closed loop system. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote: > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, > but I would be interested to hear if people have any complaints from personal > experience. What would be most interesting is if you can explain an example of > when the problem caused real inconvenience to you, since sometimes it's hard > to see from a theoretical description where the real harm lies. > > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? > > Feel free to respond on-list or if you prefer in personal emails. I do intend > to use the ideas you give in my presentation so mark anything you wouldn't be > happy to see in a slide at a conference some day. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's Slony Replication support! > A couple of the things weighing in on my mind right now (I could be utterly wrong in all of them): - In place upgrade. Seriously, 3.5TB DBs make me cry... - Lack of good documentation for the binary copy input format for 'copy foo from stdin with binary'. Also, I don't *seem* to be able to use the same file that I copied out for copy in, which was a total surprise. - The fetch time with lots of byteas is really bad - selecting them out effectively *forces* scattered IO, even in what would normally be seq IO. It would be really nice if you did all the grabbing of rows that was required and then *at the end* fetched the appropriate bytea fields and re-aggregated them appropriately. This is a *HUGE* performance killer. - Bytea copy input format is *exceedingly* large - and the dual parser thing requiring two backslashes doesn't help!. W T F, I have got to be missing something. Consider the case where I want to write an int16_t. What should be sprintf(s, "\\%o", i); becomes sprintf(s, "\\\\%03o\\\\%03o", (i & 0x00FF), (i & 0xFF00)); - Query planning with heavy partitioning takes a huge hit, and this isn't helped by multiple cores on the same box. It would be very nice of subpartitions could simply be ignored if their parent partition wasn't required, but the planner still takes locks on them. - The ability to add a table to the inheritance structure without obtaining an acc ex lock would be really nice. - The ability to rebuild a table or index concurrently would be nice, especially if it automatically picked up interim changes and applied them before switching out and dropping the table. - Slony is really too slow to use for large quantities of data shipping. IIRC we had to move off of it when the DB was still sub 1 TB. - Lots of temp table creation/dropping plays havoc with the catalog tables and eventually requires a full maintenance window to resolve. - Creating an empty table with foreign keys requires an acc ex lock on all tables. Blargh. - It'd be nice if the query planner was more "stable" - sometimes the queries run fast, and then sometimes they randomly take 2 hours for a delete that normally runs in a couple of minutes. There's (alot) more, but I can't recall it all because I'm overall pretty happy with Postgres. -Mark
On Mon, Feb 2, 2009 at 4:54 PM, Christopher Browne <cbbrowne@gmail.com> wrote: > - Stored procedures that can manage transactions (e.g. - contrast with > present stored functions that forcibly live *inside* a transaction > context; the point isn't functions vs procedures, but rather to have > something that can do txn management) IMO, once the current crop of in-progress features are rolled up (in place upgrade, hot standby, etc)...this is one of two 'must have' features...the other being revamped listen/notify. merlin
On Wednesday 04 February 2009 20:36:24 Grzegorz Jaśkiewicz wrote: > I dream about db wide checks on tables, without need to write > expensive triggers. > Basically, something that would run a select query after > insert/update/delete and based on result commit or rollback. > unless there's something like that already in SQL (I am not aware of > all features in sql2008 draft). Sounds like ASSERTION, standard SQL feature. Certainly interesting.
On Wednesday 04 February 2009 19:39:42 John DeSoi wrote: > Somewhat related, it would be nice if columns had a unique identifier > in the catalog rather than just a sequence number for the table. This > would make it possible to distinguish between altering a column versus > dropping/adding when comparing schemas or detecting DDL changes. It would also make quite a bit of internal code much simpler if pg_attribute had OIDs. I'm not sure if the demand for that is high beyond you, though.
Mark Roberts wrote: > - It'd be nice if the query planner was more "stable" - sometimes the > queries run fast, and then sometimes they randomly take 2 hours for a > delete that normally runs in a couple of minutes. I was going to stay silent, because my pet peeves were already covered or had been fixed (btw, thanks to whomever fixed sqlstandard "quote escaping a quote" all those years ago :-) ). But Mark's suggestion is excellent. Plan stability / Storedplanner outlines / whatever you want to call it, is hugely valuable when data volumes change so frequently that theplanner never knows the "good" stats from the "bad", and also when upgrading to lessen the "OMG, I have to add set enable_nestloop=falseto 48 billion queries just to overcome new planner quirks" situations. $OTHER_BIG_RDBMS have had thisto varying degrees for a while (stored outlines/plan stability in Oracle; bind in DB2; whatever crap name MS gave theirhalf-arsed version), and when it's mature, the certainty around execution is a life-saver. And just to chime in on the already mentioned things: - in-place upgrades - replication engine in the core - true stored procedures - job scheduler in the core In all, a short list, which is an oblique way of saying thanks to everyone for the enormous strides that have been made inthe last few years :-) Ciao Fuzzy :-) ------------------------------------------------ Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/
On Wed, Feb 4, 2009 at 6:42 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > > As A.M. says elsewhere, it would be good to have a trigger that fired a > NOTIFY that was picked up by a scheduled job that LISTENs every 10 > minutes for certain events. > > We need a place for code that is *not* directly initiated by a user's > actions, yet works as part of a closed loop system. Can't you do this today in about three lines of your favourite scripting language? I used to do this in perl on Oracle and that was without anything like LISTEN/NOTIFY to optimize it. Web pages just inserted a record and went on about their business while a worker daemon scanned for new records and generated notification mails. The problem with trying to push everything into the database is that it ends up sucking your entire application into the database. That limits your choice of languages and tools, and also creates a huge bottleneck. -- greg
On Wed, Feb 4, 2009 at 9:09 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > On Wednesday 04 February 2009 20:36:24 Grzegorz Jaśkiewicz wrote: >> I dream about db wide checks on tables, without need to write >> expensive triggers. >> Basically, something that would run a select query after >> insert/update/delete and based on result commit or rollback. >> unless there's something like that already in SQL (I am not aware of >> all features in sql2008 draft). > > Sounds like ASSERTION, standard SQL feature. Certainly interesting. > I was sure something like that existed, but had no idea about name. It also shouldn't be very hard to add, me guesses. -- GJ
On Thu, 2009-02-05 at 11:08 +0000, Greg Stark wrote: > The problem with trying to push everything into the database is that > it ends up sucking your entire application into the database. That > limits your choice of languages and tools, and also creates a huge > bottleneck. No, it allows you to choose where to put things. There is no suction effect, any more than the existence of Python causes everything to be written in that language. Nobody said "everything" either, just a scheduling facility. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
also, how hard would it be to implement "CREATE ASSERTION", and where do you see it (and maybe Tom could anwer that one too). Would you say, it would be possible for someone with my knowledge of postgresql internals (vague), but with very good C to do it
On Feb 5, 2009, at 6:08 AM, Greg Stark wrote: > On Wed, Feb 4, 2009 at 6:42 PM, Simon Riggs <simon@2ndquadrant.com> > wrote: >> >> As A.M. says elsewhere, it would be good to have a trigger that >> fired a >> NOTIFY that was picked up by a scheduled job that LISTENs every 10 >> minutes for certain events. >> >> We need a place for code that is *not* directly initiated by a user's >> actions, yet works as part of a closed loop system. > > > Can't you do this today in about three lines of your favourite > scripting language? > > I used to do this in perl on Oracle and that was without anything like > LISTEN/NOTIFY to optimize it. Web pages just inserted a record and > went on about their business while a worker daemon scanned for new > records and generated notification mails. > > The problem with trying to push everything into the database is that > it ends up sucking your entire application into the database. That > limits your choice of languages and tools, and also creates a huge > bottleneck. In addition to the other response, one should also take security scenarios into consideration. If role X installs an event handler (timed or via notify), I would expect the callback to be run as that role X. This is currently impossible to safely do from outside the database because SET SESSION AUTHORIZATION can be trivially revoked with RESET SESSION AUTHORIZATION. Also, LISTEN/NOTIFY really need to allow for additional user info to be attached (it's on the TODO list) and they have further security problems because they are global broadcasts. I would expect an integrated event dispatch mechanism to handle the complexity of security as well as what to do in case of rollback. So, no, this is not something can be slapped together from outside the db. Cheers, M
Hi, I think too that having the possibility of scheduling database maintenance function right into the database would be a great feature. The first use case that comes to my mind is this */5 cron job which runs psql just to clean out old sessions and force a vacuum analyze. On Wednesday 04 February 2009 19:42:27 Simon Riggs wrote: > As A.M. says elsewhere, it would be good to have a trigger that fired a > NOTIFY that was picked up by a scheduled job that LISTENs every 10 > minutes for certain events. In another thread Hannu talked about a completely different need where integrating a ticker (PGQ) would help. It seems this is just another need for it, extended to the event producing and consuming facet of it. http://archives.postgresql.org/pgsql-hackers/2009-02/msg00117.php - New fork to keep no more visible MVCC row version with timestamping - Support for time travel facilities (SELECT ... AS OF 'yesterday'::date;) - Timestamping done after the fact thanks to the ticker (timestamptz/txid) > We need a place for code that is *not* directly initiated by a user's > actions, yet works as part of a closed loop system. Exactly. -- dim
Attachment
Grzegorz Jaśkiewicz wrote: > also, how hard would it be to implement "CREATE ASSERTION", and where > do you see it (and maybe Tom could anwer that one too). > Would you say, it would be possible for someone with my knowledge of > postgresql internals (vague), but with very good C to do it I think you could do it using the constraint trigger mechanism. But PostgreSQL internals knowledge will be more important than excellent C skills, I guess.
Grzegorz Jaśkiewicz escribió: > On Wed, Feb 4, 2009 at 9:09 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > > On Wednesday 04 February 2009 20:36:24 Grzegorz Jaśkiewicz wrote: > >> I dream about db wide checks on tables, without need to write > >> expensive triggers. > >> Basically, something that would run a select query after > >> insert/update/delete and based on result commit or rollback. > >> unless there's something like that already in SQL (I am not aware of > >> all features in sql2008 draft). > > > > Sounds like ASSERTION, standard SQL feature. Certainly interesting. > > > I was sure something like that existed, but had no idea about name. > It also shouldn't be very hard to add, me guesses. A trivial, stupid implementation is perhaps not too difficult. The problem is getting the smarts right, i.e. an optimized version. You certainly don't want to be executing a query against a large table for every INSERT on another one, for example; it's better if if you can figure out when to skip it. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Greetings! I've run into this problem a few times. There must be a solution, but I don't know what it is. I have a table that has three interesting columns: coil_id, charge, and coldspot_time. A charge can have several coils, so there are several records with differing coil_ids but the same charge. I want a list of the coils whose coldspot_times are the largest for their charge. I can easily get a list of the maximum coldspot times for each charge: Select charge, max(coldspot_time) From inventory Group by charge But how do I find out what coil has the maximum coldspot time? Select coil_id, charge, max(coldspot_time) From inventory Group by charge Doesn't work because SQL can't read my mind well enough to know which of the four coil_ids in the given charge is the one I want. Thank you very much! RobR
[ Rob, it would help if you didn't "reply" to unrelated messages. Decent mail programs automatically "thread" emails based on what you reply to and hence unrelated messages like yours tend to get lost. ] On Thu, Feb 05, 2009 at 02:43:07PM -0500, Rob Richardson wrote: > I want a list of > the coils whose coldspot_times are the largest for their charge. > Select coil_id, charge, max(coldspot_time) > From inventory > Group by charge The DISTINCT ON clause would help here, something like: SELECT DISTINCT ON (coil_id), coil_id, charge, MAX(largest_time) FROM inventory GROUP BY coil_id, charge ORDER BY coil_id, MAX(largest_time) DESC; -- Sam http://samason.me.uk/
"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
> I have a table that has three interesting columns: coil_id, charge, and
> coldspot_time. A charge can have several coils, so there are several
> records with differing coil_ids but the same charge. I want a list of
> the coils whose coldspot_times are the largest for their charge.
If you don't mind a Postgres-ism, the DISTINCT ON clause provides a
reasonably efficient approach for this type of problem. See the
"weather reports" example in the SELECT reference page.
You could also do something like
select coil_id, charge, coldspot_time
from inventory i
where coldspot_time = (select max(coldspot_time) from inventory i2
where i2.charge = i.charge);
which doesn't require any nonstandard features, but on the other hand
its performance will probably suck if charge groups are large (and
even if they aren't, it'll suck without an index on charge). Also note
that if there are several coils sharing the max in a charge group, this
will show you all of them, which might or might not be what you want.
(The DISTINCT ON method gets you only one maximum per group, but you
can sort on additional columns to prioritize which one to show.)
[ thinks for a bit... ] Another spec-compliant variant is
select coil_id, charge, coldspot_time
from inventory
where (charge, coldspot_time) in (select charge, max(coldspot_time)
from inventory group by charge);
which should work better than the previous for large charge groups.
This also gives you all the maxima per group.
I have a feeling that it could also be done via the "window functions"
feature due to be introduced in 8.4, but I'm not entirely sure how.
Anybody feeling handy with those?
regards, tom lane
Sam, Great! I had no idea DISTINCT ON existed. That made it much simpler. Here's what I used: select distinct on (inventory.charge) coil_id, inventory.charge, heating_coldspot_time_reached from inventory inner join charge on charge.charge = inventory.charge where base_type = '3' and heating_coldspot_time_reached > 0 and inventory.status = 'Done' and inventory.charge >= 1000 order by charge, heating_coldspot_time_reached desc And thanks for the tip about E-mail. I thought that if I hit the Reply button and then changed the subject line, it would be the same as sending a new message to the same destination. In this case, I had to change the destination also, so it looked like a brand new message. I didn't know I was bringing along old baggage behind the scenes. Thanks very much! RobR
Thanks very much, Tom. While the DISTINCT ON suggestion answered the question I asked very neatly and I am glad to add that concept to my arsenal, your standard-compliant query was what I actually needed. The DISTINCT ON query only gave me one coil if there were two coils in a charge that had the same coldspot time. Your standard-compliant query included them. There is a fourth column of interest, named coil_trf. The purpose of this exercise is to map coil_trf values to coldspot times for the coils in each charge that have the largest coldspot time. If two coils have the same coldspot time and none of the others in the charge have one as long, then I want both coils. The key concept here that I didn't know about was the use of more than one field in a list used with IN. Using actual field names from the database (except for coil_trf, which I haven't added yet because it comes from another table), here's the query I ended up with: select coil_id, inventory.charge, heating_coldspot_time_reached from inventory inner join charge on charge.charge = inventory.charge where base_type = '3' and heating_coldspot_time_reached > 0 and inventory.status = 'Done' and inventory.charge >= 1000 and (inventory.charge, heating_coldspot_time_reached) in (select inventory.charge, max(heating_coldspot_time_reached) from inventory inner join charge on charge.charge = inventory.charge where base_type = '3' and heating_coldspot_time_reached > 0 and inventory.status = 'Done' and inventory.charge >= 1000 group by inventory.charge) order by inventory.charge RobR
On Feb 2, 2009, at 12:46 PM, wstrzalka wrote: > On Feb 2, 8:23 pm, br...@momjian.us (Bruce Momjian) wrote: >> wstrzalka wrote: >>> * stat collector is really greedy by definition even when system is >>> idle, when you have really really many relations >> >> I think this will be fixed in 8.4. >> > > That would by great news for "mine" cluster. One workaround I came up with a while back for that is to edit the stat file name to be in a separate directory under global (like <datadir>/global/pg_stats/pgstat.stat) and mount a ramfs there. Of course, a custom compile isn't always an option but it removed a *ton* of IO on that db (had thousands upon thousands of tables). Also, if you do that you need to be sure to copy pgstat.stat to a permanent place periodically unless you want to risk losing all of your stats. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
Tom Lane <tgl@sss.pgh.pa.us> writes: > I have a feeling that it could also be done via the "window functions" > feature due to be introduced in 8.4, but I'm not entirely sure how. > Anybody feeling handy with those? There may be a better way but something like select * from (select *, rank() over (partition by charge order by coldspot_time desc) as r) where r = 1 ? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
Alvaro Herrera wrote: > A trivial, stupid implementation is perhaps not too difficult. The > problem is getting the smarts right, i.e. an optimized version. You > certainly don't want to be executing a query against a large table for > every INSERT on another one, for example; it's better if if you can > figure out when to skip it. If you do it using the constraint trigger mechanism, it would work very similar to foreign keys, I'd imagine. Instead of running a query against the primary key table, you'd run the check constraint expression.
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 >> What logic would lead someone to separate pg_config from everything else? >> Do people often just install the server and nothing else? Then what? > This is actually *required* by Debian/Ubuntu packaging rules. > The development environment must be packaged separately from shared libraries > like libpq or else major snafus arise when a new soversion of libpq comes out. > You need to be able to have both versions installed simultaneously (in case > you have programs which require both) but that won't work if they both contain > things like header files or executables. I'm not sure I follow this. What makes pg_config so different from psql? I can't imagine why it's not simply treated the same as pg_dump and psql. It's certainly annoying to have to install a whole seperate package just to have access to it. >> BTW I ran into the need for pg_config upon installing DBD::Pg. >> Maybe DBD::Pg maintainer problem? > Installing a package for DBD::Pg or building it? The former would indeed be a > package bug. AFAIK, no package has that problem. If there is one, someone raise a bug. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200902072126 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkmOQz8ACgkQvJuQZxSWSsh/1QCg/fTaxS2yT9tiyKEhb+NGLUkl uhkAn0jEHN6NxxynaeTNEQ8+3bHrtCv/ =RKHl -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > * Letter options in psql, pg_dump[all], pg_restore aren't consistent > and can easily steer you very wrong. I'm looking at you, -d. Amen! > So, what do people say? Is Postgres perfect in your world or does > it do some things which rub you the wrong way? You can check the archives for more things I've found annoying in the past, but might as well put out a quick list here for posterity: * We're using md5 as the default hash function. Lame. * We don't have any other builtin encryption/hash stuff other than md5. * Many more things should be built-in, rather than as contrib or worse. Make them 'installable' like procedural languages if you must, but they have to go in easier. * pgfoundry is still a mess. 'nuff said. * We lost years worth of community shared knowledge when planetpostgresql went down and the owner won't let people restore their links (e.g. a simple Apache redirect). * psql is not backwards compatible * In-place upgrade. Regular dump/restore could be better too. * The resistance to changing the name officially back to "Postgres". * The way patches must often be fought for before acceptance. * The community is scared of change. Exhibit A: git * Lack of pragmatism at times: real-world DBA work vs. ivory tower pontification. * Deferred constraints * psql doesn't do multi-line readline * The horrible defaults in postgresql.conf * The horrible commenting in postgresql.conf * The crappy 8kb GUC unit sizes * Slow pace of libpq: no binary, no partial result sets * Logging could be a lot more flexible and fine-grained. Imagine being able to have slow queries from database X go to a separate log file. * Horrible bike shedding and making simple solutions complicated. * Would like to see information_schema expanded. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200902072156 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkmOSfMACgkQvJuQZxSWSsgNIQCgtp9rDWLVyeTDjIvFw+lPWyju B9kAn0e3mvpaKcu19qZS1qdJdA+vLBfT =KYSj -----END PGP SIGNATURE-----
In the case of DBD::Pg it seems that it just uses the output of pg_config. It seems absurd that that information can't be stored in psql. There must be some good reason that it's not. Is it because psql is stripped? At least the build information (which pg_config spits out) could be stored in a text file that psql knows about and then psql --buildopts would give you that information. On Sun, 8 Feb 2009 02:28:40 -0000 "Greg Sabino Mullane" <greg@turnstep.com> wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: RIPEMD160 > > > >> What logic would lead someone to separate pg_config from everything else? > >> Do people often just install the server and nothing else? Then what? > > > This is actually *required* by Debian/Ubuntu packaging rules. > > > The development environment must be packaged separately from shared libraries > > like libpq or else major snafus arise when a new soversion of libpq comes out. > > You need to be able to have both versions installed simultaneously (in case > > you have programs which require both) but that won't work if they both contain > > things like header files or executables. > > I'm not sure I follow this. What makes pg_config so different from psql? I can't > imagine why it's not simply treated the same as pg_dump and psql. It's certainly > annoying to have to install a whole seperate package just to have access to it. > > >> BTW I ran into the need for pg_config upon installing DBD::Pg. > >> Maybe DBD::Pg maintainer problem? > > > Installing a package for DBD::Pg or building it? The former would indeed be a > > package bug. > > AFAIK, no package has that problem. If there is one, someone raise a bug. > > - -- > Greg Sabino Mullane greg@turnstep.com > PGP Key: 0x14964AC8 200902072126 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > -----BEGIN PGP SIGNATURE----- > > iEYEAREDAAYFAkmOQz8ACgkQvJuQZxSWSsh/1QCg/fTaxS2yT9tiyKEhb+NGLUkl > uhkAn0jEHN6NxxynaeTNEQ8+3bHrtCv/ > =RKHl > -----END PGP SIGNATURE----- > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Feb 7, 2009, at 7:09 PM, rhubbell wrote: > In the case of DBD::Pg it seems that it just uses the output of > pg_config. It seems absurd that that information can't be stored in > psql. There must be some good reason that it's not. > Is it because psql is stripped? > > At least the build information (which pg_config spits out) could be > stored > in a text file that psql knows about and then psql --buildopts would > give you that information. But what would you do with the information then? Most anything (including building DBD::Pg) that wants that data is going to need the developer package. Cheers, Steve
On Sat, Feb 7, 2009 at 7:57 PM, Greg Sabino Mullane <greg@turnstep.com> wrote: > * psql doesn't do multi-line readline I thought it started doing that in 8.2 or 8.3. At least on linux. > * Logging could be a lot more flexible and fine-grained. Imagine being able to > have slow queries from database X go to a separate log file. This, very much this. I would kill to be able to have each db in a cluster have its own logging, settings, shared_buffers, etc. The whole shared mostly environment makes scaling to > 1 db on a server a troublesome task
On Sat, 7 Feb 2009 19:30:37 -0800 Steve Atkins <steve@blighty.com> wrote: > > On Feb 7, 2009, at 7:09 PM, rhubbell wrote: > > > In the case of DBD::Pg it seems that it just uses the output of > > pg_config. It seems absurd that that information can't be stored in > > psql. There must be some good reason that it's not. > > Is it because psql is stripped? > > > > At least the build information (which pg_config spits out) could be > > stored > > in a text file that psql knows about and then psql --buildopts would > > give you that information. > > But what would you do with the information then? Most anything > (including > building DBD::Pg) that wants that data is going to need the developer > package. Oh, that's right DBD::Pg needs to compile against the pg dev bits. > > Cheers, > Steve > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 >> * psql doesn't do multi-line readline > I thought it started doing that in 8.2 or 8.3. At least on linux. It combines all lines into a single statement, which is handy, but things like this still trip it up: psql#> CREATE <return> psql-#> TAB <tab> > This, very much this. I would kill to be able to have each db in a > cluster have its own logging, settings, shared_buffers, etc. The > whole shared mostly environment makes scaling to > 1 db on a server a > troublesome task Just in case you weren't aware, some settings /can/ be changed per database with: ALTER DATABASE prod SET random_page_cost = 2; ALTER DATABASE fooz SET work_mem = '32 MB'; Not the logging and shared_buffers though... - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200902081214 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkmPE0IACgkQvJuQZxSWSsgk2gCeJoBSGZy6LgUZoSuNc5tGpG+y 5wAAoLndm/ggDECsNM2tFrYOsXA5TIw9 =6HJR -----END PGP SIGNATURE-----
drop user X casacde... say x has an access to database Y, you have to revoke it before dropping the user... takes ages.
Erik Jones escribió: > One workaround I came up with a while back for that is to edit the stat > file name to be in a separate directory under global (like > <datadir>/global/pg_stats/pgstat.stat) and mount a ramfs there. Of > course, a custom compile isn't always an option but it removed a *ton* > of IO on that db (had thousands upon thousands of tables). Also, if you > do that you need to be sure to copy pgstat.stat to a permanent place > periodically unless you want to risk losing all of your stats. Hmm, you don't really need to copy it periodically -- you need to do it on shutdown only. If the system crashes, the recovery code will delete the pgstats file anyway. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Richard Huxton wrote:
I guess I've spent so much time racing from colo to colo to office and answering midnight pager calls that my humor-detector got broken.
Cheers,
Steve
That's a relief! :)Gregory Stark wrote:Steve Crawford <scrawford@pinpointresearch.com> writes:3. Date handling Sometimes I've got data with invalid dates and it would be great if it could replace all the bad ones with, say "0000-00-00".Oh dear $DEITY, no.I think it would be best if we limited ourselves right now to discussing the problems themselves and not debating the pros and cons of possible solutions.It seems I need to point out that the 0000-00-00 thing was supposed to be a joke.
I guess I've spent so much time racing from colo to colo to office and answering midnight pager calls that my humor-detector got broken.
Cheers,
Steve
Gregory Stark wrote: > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. Perhaps you could post a conclusion to this, with some "worst of" statistics or something. I didn't see your talk, but I was getting a sense that the feedback seen on this list provided some good drivers for future development. (MySQL had a similar session at FOSDEM, btw.)
Peter Eisentraut wrote: > Gregory Stark wrote: >> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at >> FOSDEM 2009 this year. > > Perhaps you could post a conclusion to this, with some "worst of" > statistics or something. I didn't see your talk, but I was getting a > sense that the feedback seen on this list provided some good drivers for > future development. (MySQL had a similar session at FOSDEM, btw.) Apparently nobody saw the talk ... ??
On Wed, Feb 11, 2009 at 4:26 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Apparently nobody saw the talk ... ?? http://blog.hagander.net/archives/137-FOSDEM-is-done.html Acording to that page, one of Greg's talks didn't happen. I wasn't there, but was it the one ? -- GJ
On Wed, Feb 11, 2009 at 4:45 PM, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote: > On Wed, Feb 11, 2009 at 4:26 PM, Alvaro Herrera > <alvherre@commandprompt.com> wrote: > >> Apparently nobody saw the talk ... ?? > http://blog.hagander.net/archives/137-FOSDEM-is-done.html > > Acording to that page, one of Greg's talks didn't happen. I wasn't > there, but was it the one ? No - Pet Peeves happened. And could have gone on another hour as the following BSD speaker didn't show up - but we didn't know that of course. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Top of my list would be: 1. Inability to do a PITR for a single database in a cluster. 2. Lack of support for Large Objects in master-slave replication. 3. Queries that I write are not corrected by postgres ;) One last thing - it peeves me that many of the people on the forums are so bloody clever! It gives me an inferiority complex ;) Howard. Howard Cole http://www.selestial.com