Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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.) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 ... ?? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Wed, Feb 11, 2009 at 4:26 PM, Alvaro Herrera alvhe...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Wed, Feb 11, 2009 at 4:45 PM, Grzegorz Jaśkiewicz gryz...@gmail.com wrote: On Wed, Feb 11, 2009 at 4:26 PM, Alvaro Herrera alvhe...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
Richard Huxton wrote: Gregory Stark wrote: Steve Crawford scrawf...@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 -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 -00-00 thing was supposed to be a joke. That's a relief! :) 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
Re: [GENERAL] Pet Peeves?
-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 g...@turnstep.com PGP Key: 0x14964AC8 200902081214 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkmPE0IACgkQvJuQZxSWSsgk2gCeJoBSGZy6LgUZoSuNc5tGpG+y 5wAAoLndm/ggDECsNM2tFrYOsXA5TIw9 =6HJR -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
Re: [GENERAL] Pet Peeves?
drop user X casacde... say x has an access to database Y, you have to revoke it before dropping the user... takes ages. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
-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 g...@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
Re: [GENERAL] Pet Peeves?
-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 g...@turnstep.com PGP Key: 0x14964AC8 200902072156 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkmOSfMACgkQvJuQZxSWSsgNIQCgtp9rDWLVyeTDjIvFw+lPWyju B9kAn0e3mvpaKcu19qZS1qdJdA+vLBfT =KYSj -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
Re: [GENERAL] Pet Peeves?
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 - Greg Sabino Mullane g...@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 g...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Sat, Feb 7, 2009 at 7:57 PM, Greg Sabino Mullane g...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Sat, 7 Feb 2009 19:30:37 -0800 Steve Atkins st...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Wed, Feb 4, 2009 at 9:09 PM, Peter Eisentraut pete...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Thu, 2009-02-05 at 11:08 +, 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Wed, Feb 4, 2009 at 6:42 PM, Simon Riggs si...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Feb 5, 2009, at 6:08 AM, Greg Stark wrote: On Wed, Feb 4, 2009 at 6:42 PM, Simon Riggs si...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] Pet Peeves?
Grzegorz Jaśkiewicz escribió: On Wed, Feb 4, 2009 at 9:09 PM, Peter Eisentraut pete...@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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Wed, 2009-02-04 at 03:00 +, 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Wed, 2009-02-04 at 02:39 +, Greg Stark wrote: On Tue, Feb 3, 2009 at 10:06 PM, Simon Riggs si...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger01 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
Gregory Stark wrote: Steve Crawford scrawf...@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 -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 -00-00 thing was supposed to be a joke. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Thu, 2009-01-29 at 13:16 +, 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Mon, Feb 2, 2009 at 4:54 PM, Christopher Browne cbbro...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 sql standard quote escaping a quote all those years ago :-) ). But Mark's suggestion is excellent. Plan stability / Stored planner outlines / whatever you want to call it, is hugely valuable when data volumes change so frequently that the planner never knows the good stats from the bad, and also when upgrading to lessen the OMG, I have to add set enable_nestloop=false to 48 billion queries just to overcome new planner quirks situations. $OTHER_BIG_RDBMS have had this to varying degrees for a while (stored outlines/plan stability in Oracle; bind in DB2; whatever crap name MS gave their half-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 in the last few years :-) Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Thu, Jan 29, 2009 at 5:43 PM, David Fetter da...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Tue, Feb 03, 2009 at 05:48:51PM +, Greg Stark wrote: On Thu, Jan 29, 2009 at 5:43 PM, David Fetter da...@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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Mon, Feb 2, 2009 at 5:48 PM, Gregory Stark st...@enterprisedb.com wrote: Christopher Browne cbbro...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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? As a further take on the auto-tuning others have mentioned, how about some auto-indexing? - Jeremy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Mon, 2009-02-02 at 22:48 +, Gregory Stark wrote: Christopher Browne cbbro...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 gsm...@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
Re: [GENERAL] Pet Peeves?
On Tue, Feb 3, 2009 at 7:04 PM, David Fetter da...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Tue, Feb 3, 2009 at 10:06 PM, Simon Riggs si...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 gsm...@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
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Tue, Feb 3, 2009 at 8:58 PM, Guy Rouillier guyr-...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Tue, Feb 3, 2009 at 10:09 PM, Craig Ringer cr...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 +, 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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 ??) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Thu, Jan 29, 2009 at 8:16 AM, Gregory Stark st...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
- 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
Christopher Browne cbbro...@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
Re: [GENERAL] Pet Peeves?
You realise you just described the very project you saw me write a presentation on today right? :-p On 2/2/09, Gregory Stark st...@enterprisedb.com wrote: Christopher Browne cbbro...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
Gregory Stark wrote: Christopher Browne cbbro...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
- 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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves
rules are very very very very rarely useful. I wouldn't say that. There are many use cases where rules are just the thing. Plus they have an added performance benefit when dealing with multiple rows in a single statement. yes, in general - I wouldn't mind to see postgresql implement fully updatable views. There's being a very long discussion about that on -hackers, and patch was even in cvs-head for a bit, but got dropped. probably enabling triggers for views would be the only way to do it, me thinks. I don't know how oracle guys got around it. The Oracle solution is quite useful in a large set of cases. The basic idea is this: Since a view is arbitrarily complex, there is no way, in general, that the database can know how to update it. Therefore the concept of BEFORE or AFTER triggers doesn't really make sense (before or after something the database can't do anyway). So instead, the only kind of trigger they allow on a view is an INSTEAD OF row-level trigger. The contract of the trigger function is that it will be invoked once for each matching row in the view, and the database will assume that the trigger will do the necessary work to update that row. Thus Oracle assumes that the number of rows updated matches the number of times that it invoked the trigger function. Apart from this last part, this is like defining a rule CREATE RULE my_rule AS ON INSERT/UPDATE/DELETE TO my_view DO INSTEAD SELECT my_fn(old.*, new.*); Of course the problem with using a rule in this way is that the query is rewritten as a SELECT, and the client is told that no rows were updated. This is where the INSTEAD OF trigger comes in handy. Dean. _ Hotmail, Messenger, Photos and more - all with the new Windows Live. Get started! http://www.download.live.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves
- no ability to define triggers on views maybe because you can't perform insert/delete/update on them ? Actually I was thinking the value of triggers on views is precisely to allow you to perform insert/delete/update on them. I know you can do this with rules, but there are cases when a trigger is much more convienent to work with. Dean. _ Twice the fun—Share photos while you chat with Windows Live Messenger. Learn more. http://www.microsoft.com/uk/windows/windowslive/products/messenger.aspx -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves
The only one I can see that hasn't already been mentioned - no ability to define triggers on views Dean. _ Windows Live Messenger just got better .Video display pics, contact updates more. http://www.download.live.com/messenger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves
On Sun, Feb 1, 2009 at 10:20 AM, Dean Rasheed dean_rash...@hotmail.com wrote: - no ability to define triggers on views maybe because you can't perform insert/delete/update on them ? -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves
rules are very very very very rarely useful. yes, in general - I wouldn't mind to see postgresql implement fully updatable views. There's being a very long discussion about that on -hackers, and patch was even in cvs-head for a bit, but got dropped. probably enabling triggers for views would be the only way to do it, me thinks. I don't know how oracle guys got around it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves
Grzegorz Jaśkiewicz wrote on 01.02.2009 13:13: probably enabling triggers for views would be the only way to do it, me thinks. I don't know how oracle guys got around it. Oracle *does* have (INSTEAD OF) triggers on views. (and simple views are automatically updateable anyway) Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Sat, 2009-01-31 at 15:54 -0800, Octavio Alvarez wrote: On Sat, 2009-01-31 at 23:36 +, Gregory Stark wrote: Octavio Alvarez alvar...@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); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves
On Saturday 31 January 2009 8:47:28 pm Adam Rich wrote: On Thu, 29 Jan 2009 13:16:17 + Gregory Stark stark(at)enterprisedb(dot)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 see all the major ones have already been mentioned, so here's some minor ones. - lack of system-level and DDL triggers - inability to limit triggers to certain columns - inability to know the DML operation causing a trigger From: http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html TG_OP Data type text; a string of INSERT, UPDATE, or DELETE telling for which operation the trigger was fired. This is also available in plpythonu, I don't know about the other PL's. - date_part/extract returning floats instead of integer Maybe this what you are looking for ?: http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html Note: When timestamp values are stored as double precision floating-point numbers (currently the default), the effective limit of precision might be less than 6. timestamp values are stored as seconds before or after midnight 2000-01-01. Microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. When timestamp values are stored as eight-byte integers (a compile-time option), microsecond precision is available over the full range of values. However eight-byte integer timestamps have a more limited range of dates than shown above: from 4713 BC up to 294276 AD. The same compile-time option also determines whether time and interval values are stored as floating-point or eight-byte integers. In the floating-point case, large interval values degrade in precision as the size of the interval increases. - parts of the SQL statement (e.g. 'for update of') requiring table aliases when present instead of table names. - lack of queryable high-water marks useful for tuning - lack of an auto-tuner, for that matter. - inability to log (e.g. long-running queries) to a table - lack of custom session-level variables (without editing postgresql.conf) - lack of autonomous transactions -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves
On Saturday 31 January 2009 8:47:28 pm Adam Rich wrote:
On Thu, 29 Jan 2009 13:16:17 +
Gregory Stark stark(at)enterprisedb(dot)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 see all the major ones have already been mentioned, so here's some
minor ones.
- lack of system-level and DDL triggers
- inability to limit triggers to certain columns
- inability to know the DML operation causing a trigger
From:
http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html
TG_OP
Data type text; a string of INSERT, UPDATE, or DELETE telling for
which
operation the trigger was fired.
This is also available in plpythonu, I don't know about the other PL's.
Thanks, I knew this was available for python perl PLs, I wasn't aware
it was I plpgsql too. Still, it would be nice to have something akin to
oracle's IF(UPDATING('col_name')) THEN
- date_part/extract returning floats instead of integer
Maybe this what you are looking for ?:
http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html
Note: When timestamp values are stored as double precision floating-
point
numbers (currently the default), the effective limit of precision might
be less
than 6. timestamp values are stored as seconds before or after midnight
2000-01-01. Microsecond precision is achieved for dates within a few
years of
2000-01-01, but the precision degrades for dates further away. When
timestamp
values are stored as eight-byte integers (a compile-time option),
microsecond
precision is available over the full range of values. However eight-
byte
integer timestamps have a more limited range of dates than shown above:
from
4713 BC up to 294276 AD. The same compile-time option also determines
whether
time and interval values are stored as floating-point or eight-byte
integers.
In the floating-point case, large interval values degrade in precision
as the
size of the interval increases.
Nope, I mean if you use date_part to extract a piece of a date, you
get a float instead of an integer. It trips me up everytime I try
something like this:
select * from table
where (weekmask (1 date_part('DOW', $1))) 0
To my surprise, the operator fails because it requires an integer
argument, but date_part provides only a double floating point.
I realize this is documented as intended behavior, but why? Is there
any scenario where DOW (or day, year, hour, or *any* field really)
would be returning a fractional number?
- parts of the SQL statement (e.g. 'for update of') requiring table
aliases when present instead of table names.
- lack of queryable high-water marks useful for tuning
- lack of an auto-tuner, for that matter.
- inability to log (e.g. long-running queries) to a table
- lack of custom session-level variables (without editing
postgresql.conf)
- lack of autonomous transactions
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 gsm...@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
Re: [GENERAL] Pet Peeves
On Sat, 31 Jan 2009, Adam Rich wrote: - lack of queryable high-water marks useful for tuning What specific things would you consider important to track a high-water mark for that aren't already there? -- * Greg Smith gsm...@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
Re: [GENERAL] Pet Peeves?
rhubbell rhubb...@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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Fri, Jan 30, 2009 at 03:44:48PM -0800, rhubbell wrote: On Fri, 30 Jan 2009 20:38:06 + Gregory Stark st...@enterprisedb.com wrote: rhubbell rhubb...@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. signature.asc Description: Digital signature
Re: [GENERAL] Pet Peeves?
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 klep...@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. signature.asc Description: Digital signature
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Fri, 2009-01-30 at 14:25 +, Gregory Stark wrote: Daniel Verite dan...@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). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
Thanks, using the same apt commands, try to find pg_config. (^; On Sat, 31 Jan 2009 12:38:18 + Roger Leigh rle...@codelibre.net wrote: On Fri, Jan 30, 2009 at 03:44:48PM -0800, rhubbell wrote: On Fri, 30 Jan 2009 20:38:06 + Gregory Stark st...@enterprisedb.com wrote: rhubbell rhubb...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Sat, Jan 31, 2009 at 6:10 PM, rhubbell rhubb...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Sat, Jan 31, 2009 at 5:34 PM, Octavio Alvarez alvar...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On 2009-01-29, Steve Crawford scrawf...@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 -00-00. -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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On 2009-01-30, Steve Crawford scrawf...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Sat, Jan 31, 2009 at 11:10 AM, rhubbell rhubb...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Sat, 2009-01-31 at 18:32 +, Greg Stark wrote: On Sat, Jan 31, 2009 at 5:34 PM, Octavio Alvarez alvar...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
Octavio Alvarez alvar...@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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
On Sat, 2009-01-31 at 23:36 +, Gregory Stark wrote: Octavio Alvarez alvar...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
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 -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
Re: [GENERAL] Pet Peeves
On Thu, 29 Jan 2009 13:16:17 + Gregory Stark stark(at)enterprisedb(dot)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 see all the major ones have already been mentioned, so here's some minor ones. - lack of system-level and DDL triggers - inability to limit triggers to certain columns - inability to know the DML operation causing a trigger - date_part/extract returning floats instead of integer - parts of the SQL statement (e.g. 'for update of') requiring table aliases when present instead of table names. - lack of queryable high-water marks useful for tuning - lack of an auto-tuner, for that matter. - inability to log (e.g. long-running queries) to a table - lack of custom session-level variables (without editing postgresql.conf) - lack of autonomous transactions -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: md5 doesn't work (Was Re: [GENERAL] Pet Peeves?)
On Fri, Jan 30, 2009 at 7:55 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Jan 29, 2009 at 12:41 PM, rhubbell rhubb...@ihubbell.com wrote: On Thu, 29 Jan 2009 11:34:00 -0800 (PST) Jeff Frost j...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
