Thread: Pet Peeves?

Pet Peeves?

From
Gregory Stark
Date:
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!

Re: Pet Peeves?

From
Richard Huxton
Date:
Gregory Stark wrote:
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year.

Hmm - three "niggles" things leap to mind.

1. Case-folding on column-names.
Quoting is a PITA sometimes when you're transferring from a different
DBMS. Be nice to have a "true_case_insensitive=on" flag.

2. Non-deferred unique checks
Merging two sets of data and renumbering pkeys is more fiddly than it
needs to be. Doesn't happen often, but it's one of the few things we
don't do "properly".

3. Date handling
Sometimes I've got data with invalid dates and it would be great if it
could replace all the bad ones with, say "0000-00-00".


You could throw in non-updateable views, exact-match tsearch queries,
per-statement triggers not having the old/new rowsets but that's more
things PG doesn't do rather than things it does.

--
  Richard Huxton
  Archonet Ltd

Re: Pet Peeves?

From
Richard Broersma
Date:
On Thu, Jan 29, 2009 at 5:16 AM, Gregory Stark <stark@enterprisedb.com> wrote:

> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?

I would like to see the SQL92 feature for allowing sub-queries in
CHECK constraints, instead of marking this feature as "intentionally
omitted".
--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Re: Pet Peeves?

From
Terry Fielder
Date:
I have 2, closely related:

1) if I have multiple pids running queries, say all selects, I have no
idea which pid is running which query

and that ties to:
2) If I try to kill 1 postgres pid (e.g. to abort a bad query), the
whole backend shuts down and rolls back.
Can we get a way to look at and then kill a specific bad query?

Maybe this is not missing functionality, it can do it and I just don't
know how.  Anybody want to wisen me up?  :)

Terry

Terry Fielder
terry@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085



Gregory Stark wrote:
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
> but I would be interested to hear if people have any complaints from personal
> experience. What would be most interesting is if you can explain an example of
> when the problem caused real inconvenience to you, since sometimes it's hard
> to see from a theoretical description where the real harm lies.
>
> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?
>
> Feel free to respond on-list or if you prefer in personal emails. I do intend
> to use the ideas you give in my presentation so mark anything you wouldn't be
> happy to see in a slide at a conference some day.
>
>

Re: Pet Peeves?

From
Bill Moran
Date:
In response to Terry Fielder <terry@ashtonwoodshomes.com>:
>
> 1) if I have multiple pids running queries, say all selects, I have no
> idea which pid is running which query

SELECT * FROM pg_stat_activity;

If the current_query column doesn't have the query in it, then you need
to tweak your postgres.conf settings:
http://www.postgresql.org/docs/8.3/static/monitoring-stats.html

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Re: Pet Peeves?

From
David Fetter
Date:
On Thu, Jan 29, 2009 at 01:16:17PM +0000, Gregory Stark wrote:
>
> I'm putting together a talk on "PostgreSQL Pet Peeves" for
> discussion at FOSDEM 2009 this year.  I have a pretty good idea what
> some them are of course, but I would be interested to hear if people
> have any complaints from personal experience.  What would be most
> interesting is if you can explain an example of when the problem
> caused real inconvenience to you, since sometimes it's hard to see
> from a theoretical description where the real harm lies.
>
> So, what do people say?  Is Postgres perfect in your world or does
> it do some things which rub you the wrong way?
>
> Feel free to respond on-list or if you prefer in personal emails.  I
> do intend to use the ideas you give in my presentation so mark
> anything you wouldn't be happy to see in a slide at a conference
> some day.

* No built-in ways to get the information psql gets.  "See what psql
  is doing" isn't an option when somebody doesn't have psql on hand.

* No deferrable UNIQUE constraints.

* No man pages for the internals.

* Letter options in psql, pg_dump[all], pg_restore aren't consistent
  and can easily steer you very wrong.  I'm looking at you, -d.

* CTEs not yet integrated into the adjacency lists in pg_catalog, etc.

The following aren't problems with the PostgreSQL core engine itself,
but they're nearby, so they catch ire:

* Neither pgAdmin nor phpPgAdmin includes any facilities for
  extracting ERDs.

* Neither of them let you set up Slony (or any other replication
  system) to start with.

--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: Pet Peeves?

From
Jason Long
Date:
Richard Huxton wrote:
Gregory Stark wrote: 
I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
FOSDEM 2009 this year.   
Hmm - three "niggles" things leap to mind.

1. Case-folding on column-names.
Quoting is a PITA sometimes when you're transferring from a different
DBMS. Be nice to have a "true_case_insensitive=on" flag. 
I was just wishing for this the other day.
2. Non-deferred unique checks
Merging two sets of data and renumbering pkeys is more fiddly than it
needs to be. Doesn't happen often, but it's one of the few things we
don't do "properly".

3. Date handling
Sometimes I've got data with invalid dates and it would be great if it
could replace all the bad ones with, say "0000-00-00".


You could throw in non-updateable views, exact-match tsearch queries,
per-statement triggers not having the old/new rowsets but that's more
things PG doesn't do rather than things it does.
 

Re: Pet Peeves?

From
Dave Page
Date:
On Thu, Jan 29, 2009 at 4:57 PM, David Fetter <david@fetter.org> wrote:

> * Neither of them let you set up Slony (or any other replication
>  system) to start with.

pgAdmin does (well, barring installation and setting up slon.conf):
http://pgsnake.blogspot.com/2007/09/setting-up-slony-i-with-pgadmin.html

--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Pet Peeves?

From
Gregory Stark
Date:
David Fetter <david@fetter.org> writes:

> * No built-in ways to get the information psql gets.  "See what psql
>   is doing" isn't an option when somebody doesn't have psql on hand.

Uhm, what information are you referring to here?

> * No man pages for the internals.

Is it just that not all of the manual is actually exported into man pages? Or
is there stuff you would like to see in the manual that isn't there?

> * CTEs not yet integrated into the adjacency lists in pg_catalog, etc.

I'm not sure what you're referring to here either.

> Remember to vote!

This may not be so timely any more, though I suppose there's always someone
somewhere holding elections :)

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

Re: Pet Peeves?

From
rhubbell
Date:
I'm a new user to PostgreSQL so mine's fresh from doing an install recently.


In /etc/postgresql/8.3/main/pg_hba.conf

# METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi",
# "krb5", "ident", "pam" or "ldap".  Note that "password" sends passwords
# in clear text; "md5" is preferred since it sends encrypted passwords.


So I chose md5 but it will not work, seems like a basic thing. So I am
forced to use "trust".  These are the kinds of things that wear down
busy people trying use the software. Maybe this is a documentation
enhancement or bug.

You say:
"I have a pretty good idea what some them are"

What's your list so far?


On Thu, 29 Jan 2009 13:16:17 +0000
Gregory Stark <stark@enterprisedb.com> wrote:

>
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
> but I would be interested to hear if people have any complaints from personal
> experience. What would be most interesting is if you can explain an example of
> when the problem caused real inconvenience to you, since sometimes it's hard
> to see from a theoretical description where the real harm lies.
>
> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?
>
> Feel free to respond on-list or if you prefer in personal emails. I do intend
> to use the ideas you give in my presentation so mark anything you wouldn't be
> happy to see in a slide at a conference some day.
>
> --
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>   Ask me about EnterpriseDB's Slony Replication support!
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Pet Peeves?

From
Gregory Stark
Date:
Jason Long <mailing.list@supernovasoftware.com> writes:

> Richard Huxton wrote:
>
>> 1. Case-folding on column-names.
>> Quoting is a PITA sometimes when you're transferring from a different
>> DBMS. Be nice to have a "true_case_insensitive=on" flag.
>>
> I was just wishing for this the other day.

I'm kind of wondering what behaviour you two are looking for and what
"different DBMS" you're referring to.

I'm assuming it's not the ANSI fold-to-uppercase behaviour you're looking for.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

Re: Pet Peeves?

From
David Fetter
Date:
On Thu, Jan 29, 2009 at 05:18:17PM +0000, Dave Page wrote:
> On Thu, Jan 29, 2009 at 4:57 PM, David Fetter <david@fetter.org> wrote:
>
> > * Neither of them let you set up Slony (or any other replication
> > system) to start with.
            ^^^^^^^^^^^^^
> pgAdmin does (well, barring installation and setting up slon.conf):
> http://pgsnake.blogspot.com/2007/09/setting-up-slony-i-with-pgadmin.html

It's exactly that setup that's the peeve.  I don't think that this is
an easy problem to fix, as the general one needs filesystem access to
more than one machine, even machines without PostgreSQL installed, for
the case where the slons are on separate boxes.

It's just a peeve. :)

On the other hand, lack of a good set of startup tools has pretty much
tanked Slony adoption :(

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: Pet Peeves?

From
Alvaro Herrera
Date:
Gregory Stark wrote:
>
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
> but I would be interested to hear if people have any complaints from personal
> experience. What would be most interesting is if you can explain an example of
> when the problem caused real inconvenience to you, since sometimes it's hard
> to see from a theoretical description where the real harm lies.
>
> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?

No foreign keys in inheritance trees

No true clustered indexes

Lazy vacuum is not able to release free pages in the middle of a table

No concurrent reindex

Cross-column stats problems

No integrated job agent ;-)

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"La tristeza es un muro entre dos jardines" (Khalil Gibran)

Re: Pet Peeves?

From
Jason Long
Date:
Gregory Stark wrote:
Jason Long <mailing.list@supernovasoftware.com> writes:
 
Richard Huxton wrote:
   
1. Case-folding on column-names.
Quoting is a PITA sometimes when you're transferring from a different
DBMS. Be nice to have a "true_case_insensitive=on" flag.      
I was just wishing for this the other day.   
I'm kind of wondering what behaviour you two are looking for and what
"different DBMS" you're referring to. 
Ah, I misread.  I was wishing for the a way to make table and column names case sensitive without having to add quotes everywhere.
I'm assuming it's not the ANSI fold-to-uppercase behaviour you're looking for.
 

Re: Pet Peeves?

From
Steve Crawford
Date:
>> 3. Date handling
>> Sometimes I've got data with invalid dates and it would be great if it
>> could replace all the bad ones with, say "0000-00-00".
>>

Oh dear $DEITY, no. Part of the ethos of PostgreSQL is that it requires
you to enter valid data. I don't see how auto-replacing one invalid date
with a standardized different invalid date is a benefit.

In a data-cleaning environment, I could see some limit benefit of some
sort of explicit override on the copy command, say:
\copy foo (a, b, c invalid=null, d invalid='') from foo.txt...

At least you could get the good data into a table to analyze it. But
overall, the data-cleaning role seems to me to belong more in the ETL arena.

-Steve


Re: Pet Peeves?

From
Richard Huxton
Date:
Gregory Stark wrote:
> Jason Long <mailing.list@supernovasoftware.com> writes:
>
>> Richard Huxton wrote:
>>
>>> 1. Case-folding on column-names.
>>> Quoting is a PITA sometimes when you're transferring from a different
>>> DBMS. Be nice to have a "true_case_insensitive=on" flag.
>>>
>> I was just wishing for this the other day.
>
> I'm kind of wondering what behaviour you two are looking for and what
> "different DBMS" you're referring to.
>
> I'm assuming it's not the ANSI fold-to-uppercase behaviour you're looking for.

It's anywhere you can get tables created quoted/unquoted and not that in
the app. Or even on the command-line. It's a PITA to go round
remembering to "quote" "every" column" because the table was created
preserving case.

Not the end of the world, but you did ask for "peeves".

--
  Richard Huxton
  Archonet Ltd

Re: Pet Peeves?

From
Guy Rouillier
Date:
Gregory Stark wrote:
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
> but I would be interested to hear if people have any complaints from personal
> experience. What would be most interesting is if you can explain an example of
> when the problem caused real inconvenience to you, since sometimes it's hard
> to see from a theoretical description where the real harm lies.
>
> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?
>
> Feel free to respond on-list or if you prefer in personal emails. I do intend
> to use the ideas you give in my presentation so mark anything you wouldn't be
> happy to see in a slide at a conference some day.
>

Back in March 2005, I started an email thread titled "Debugging
deadlocks".  Most of the experienced PGers participated in that thread.
  The basic issue at that time was that inserting a row into a table
with a foreign key placed an exclusive row-level lock (SELECT FOR
UPDATE) on the reference table (the table to which the foreign key
refers).  If you happen to do inserts on two different tables, each with
a foreign key to the same reference table, deadlocks are pretty easy to
create.  This is especially true if the reference table has low
cardinality, which is often the case.

I don't know if this situation has been improved since that time.

--
Guy Rouillier

Re: Pet Peeves?

From
David Fetter
Date:
On Thu, Jan 29, 2009 at 05:18:19PM +0000, Gregory Stark wrote:
> David Fetter <david@fetter.org> writes:
>
> > * No built-in ways to get the information psql gets.  "See what
> > psql is doing" isn't an option when somebody doesn't have psql on
> > hand.
>
> Uhm, what information are you referring to here?

All the stuff that generates \d output is available only to psql.
When somebody wants to make another client, or even expose some of
that functionality, they pretty much have to roll it from scratch.

> > * No man pages for the internals.
>
> Is it just that not all of the manual is actually exported into man
> pages?  Or is there stuff you would like to see in the manual that
> isn't there?

The configuration files (postgresql.conf and pg_hba.conf, at least)
and the stuff in libpq and SPI would be a great start.

> > * CTEs not yet integrated into the adjacency lists in pg_catalog,
> > etc.
>
> I'm not sure what you're referring to here either.

The DAG structures in pg_depend leap to mind.  There's no view that
shows the actual dependencies, except in the sense of, "Here's the
edges.  Figure it out for yourself."

> > Remember to vote!
>
> This may not be so timely any more, though I suppose there's always
> someone somewhere holding elections :)

It's always time to vote on *something* :)

Oh, and one more "associated project" peeve:

* PostGIS doesn't ship with core PostgreSQL.

I've come up with a strategy for fixing it.  "Port JTS
<http://sourceforge.net/projects/jts-topo-suite/> from Java to C and
BSDL the result," is a pretty gigantic task, and it's just the start,
but I'm pretty sure it's the right strategy for fixing the peeve.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: Pet Peeves?

From
Richard Huxton
Date:
David Fetter wrote:
> * Letter options in psql, pg_dump[all], pg_restore aren't consistent
>   and can easily steer you very wrong.  I'm looking at you, -d.

Ah, good one - I keep doing that too. For the record "-d" is usually
database-name, but for pg_dump it's "dump with inserts". Which is a
zillion time slower than COPY for restoring.

--
  Richard Huxton
  Archonet Ltd

Re: Pet Peeves?

From
"Joshua D. Drake"
Date:
On Thu, 2009-01-29 at 17:43 +0000, Richard Huxton wrote:
> David Fetter wrote:
> > * Letter options in psql, pg_dump[all], pg_restore aren't consistent
> >   and can easily steer you very wrong.  I'm looking at you, -d.
>
> Ah, good one - I keep doing that too. For the record "-d" is usually
> database-name, but for pg_dump it's "dump with inserts". Which is a
> zillion time slower than COPY for restoring.

If we are listing pet peeves :)

Up to 8.4, postgresql didn't accurately represent timestamps because
they are stored as float by default

The fact that there is:

pg_dump
pg_dumpall
pg_restore

At all...

It should be pg_backup and that is it, with a nice -R flag for restore.

The idea that it is "proper" to pipe a backup through psql to restore.

Our date handling as a whole (extract,date_part) is wonky. There have
been more than one blog post on this.

Our lack of partitioning :)

Joshua D. Drake


--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


Re: Pet Peeves?

From
Gregory Stark
Date:
Steve Crawford <scrawford@pinpointresearch.com> writes:

>>> 3. Date handling
>>> Sometimes I've got data with invalid dates and it would be great if it
>>> could replace all the bad ones with, say "0000-00-00".
>>>
>
> Oh dear $DEITY, no.

I think it would be best if we limited ourselves right now to discussing the
problems themselves and not debating the pros and cons of possible solutions.

I want to encourage people to post their peeves even if they know perfectly
well the reasons why things are the way they are.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

Re: Pet Peeves?

From
Bill Moran
Date:
In response to rhubbell <Rhubbell@iHubbell.com>:
>
> I'm a new user to PostgreSQL so mine's fresh from doing an install recently.
>
> In /etc/postgresql/8.3/main/pg_hba.conf
>
> # METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi",
> # "krb5", "ident", "pam" or "ldap".  Note that "password" sends passwords
> # in clear text; "md5" is preferred since it sends encrypted passwords.
>
>
> So I chose md5 but it will not work, seems like a basic thing. So I am
> forced to use "trust".

How on earth does failure of md5 to work force you to use trust?

How about crypt or password (password is pretty darn simple to set up).

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Re: Pet Peeves?

From
Steve Crawford
Date:
Gregory Stark wrote:
Steve Crawford <scrawford@pinpointresearch.com> writes:
 
3. Date handling
Sometimes I've got data with invalid dates and it would be great if it
could replace all the bad ones with, say "0000-00-00".          
Oh dear $DEITY, no.    
I think it would be best if we limited ourselves right now to discussing the
problems themselves and not debating the pros and cons of possible solutions.

I want to encourage people to post their peeves even if they know perfectly
well the reasons why things are the way they are.
 

Fair enough.

My big pg_dump peeve: the inability to dump a function-definition with pg_dump.

Cheers,
Steve

Re: Pet Peeves?

From
Steve Atkins
Date:
On Jan 29, 2009, at 5:16 AM, Gregory Stark wrote:

>
> I'm putting together a talk on "PostgreSQL Pet Peeves" for
> discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are
> of course,
> but I would be interested to hear if people have any complaints from
> personal
> experience. What would be most interesting is if you can explain an
> example of
> when the problem caused real inconvenience to you, since sometimes
> it's hard
> to see from a theoretical description where the real harm lies.
>
> So, what do people say? Is Postgres perfect in your world or does it
> do some
> things which rub you the wrong way?
>
> Feel free to respond on-list or if you prefer in personal emails. I
> do intend
> to use the ideas you give in my presentation so mark anything you
> wouldn't be
> happy to see in a slide at a conference some day.

1. Version upgrades require a dump and restore, which is painfully slow.

2. No distinction between parse errors and execution errors in psql. I
want mistakes that can be detected at parse time (typos, invalid
syntax, non-existent columns) to throw an message, and not affect the
backend state. I want other errors to abort the transaction.
ON_ERROR_ROLLBACK is better than nothing, but I'd like to distinguish
the two cases.

3. Returning free space to the OS when there've been large changes to
a large table. VACUUM FULL is too slow to be useful, CLUSTER requires
an index, and can often be too slow. I tend to resort to CREATE TABLE
AS or pg_dump, and manually handle the constraints, which is ugly. I'd
be happy to have a vacuum equivalent that ran for days, shuffling
empty pages to the end of the table, as long as it didn't interrupt
service.

4. UPSERT. It's a really useful primitive, and we don't have it.

5. Backslash characters in strings. It's tricky to put a string in the
database with a backslash in it, in a way that'll support different
versions of the backend.

6. Where's my CPAN equivalent? Postgresql is extensible, but it's hard
to find the extension you need, and often harder than it should be to
install.

7. Table partitioning. It's nice, but rather limited.

8. pg_dump -d / -D. It's just a footgun.

9. psql vs pg_restore for restoring dumps. It's confusing to explain
to people.

10. Backups in general. There are much better approaches than pg_dump,
but explaining them to users is too complex.

Cheers,
   Steve


Re: Pet Peeves?

From
Greg Smith
Date:
On Thu, 29 Jan 2009, rhubbell wrote:

> So I chose md5 but it will not work, seems like a basic thing. So I am
> forced to use "trust".  These are the kinds of things that wear down
> busy people trying use the software. Maybe this is a documentation
> enhancement or bug.

I wrote up a first draft of something aimed at this particular area at
http://wiki.postgresql.org/wiki/Client_Authentication

I'm still not completely happy with how I describe what you need to switch
to md5, but at least it points to all the right places people usually
miss.  If you just went through this recently or still have open issues,
I'd be curious to get your feedback about that piece.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: Pet Peeves?

From
johnf
Date:
On Thursday 29 January 2009 05:16:17 am Gregory Stark wrote:
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of
> course, but I would be interested to hear if people have any complaints
> from personal experience. What would be most interesting is if you can
> explain an example of when the problem caused real inconvenience to you,
> since sometimes it's hard to see from a theoretical description where the
> real harm lies.
>
> So, what do people say? Is Postgres perfect in your world or does it do
> some things which rub you the wrong way?
>
> Feel free to respond on-list or if you prefer in personal emails. I do
> intend to use the ideas you give in my presentation so mark anything you
> wouldn't be happy to see in a slide at a conference some day.
>
> --
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>   Ask me about EnterpriseDB's Slony Replication support!

I'm not sure if it's just my version (8.1) but I can't access two different
database in the same statement as in

insert into public.db1.table1 (select * from public.db2.table2)

--
John Fabiani

Re: Pet Peeves?

From
Jeff Davis
Date:
On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote:
> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?

The one that has always bothered me is that there's no way to explicitly
set the value that is returned by PQcmdTuples(), i.e. the number of
affected rows.

This makes it very difficult to make truly transparent updatable views
in a complex case, e.g., if you're updating a remote table or something.

Regards,
    Jeff Davis


Re: Pet Peeves?

From
Alan Hodgson
Date:
On Thursday 29 January 2009, Terry Fielder <terry@ashtonwoodshomes.com>
wrote:
> and that ties to:
> 2) If I try to kill 1 postgres pid (e.g. to abort a bad query), the
> whole backend shuts down and rolls back.
> Can we get a way to look at and then kill a specific bad query?

select pg_cancel_backend(pid). Or kill pid from the shell, it only kills off
one backend on 8.x anyway.


--
Current Peeve: The mindset that the Internet is some sort of school for
novice sysadmins and that everyone -not- doing stupid dangerous things
should act like patient teachers with the ones who are. -- Bill Cole, NANAE

Re: Pet Peeves?

From
Greg Smith
Date:
The biggest peeve I still have to fight is attached to the old "why aren't
there any optimizer hints?" tree.  PostgreSQL forces you to understand a
non-trivial amount of how the query optimizer works before you can get it
to do the right thing once you get beyond a small database, and nobody
likes doing the "why isn't it using the index?!" dance.

When you turn enable_seqscan off and it proceeds to do a seqscan anyway
when there's seemingly a perfect index right there, it's pretty
frustrating.  I spent the better part of a weekend last year fighting a
battle with a single "select * from t where type='x' and ts>='date1' and
ts<='date2", running against a giant table with an index on (type,ts) that
just stopped using that index for mysterious reasons.  You do not want to
seqscan a billion rows.

The main thing I'd like to see here is more visibility into rejected
plans, so that at least people could see those costs.  If I could have
seen whether the cost estimate on the index-based plan was going up or
down as I tweaked parameters/updated stats, at least then I could quantify
my progress toward returning to where the right plan was preferred one.
The flip side is that as it is right now, it's also hard to answer the
question "how close am I to having this plan fail?" until it already has.
I know there's been some academic work in this area as part of classes on
database internals, I'd like to see some of that turn into a production
feature.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: Pet Peeves?

From
rhubbell
Date:
Umm, because md5 doesn't work and trust does work.
On Thu, 29 Jan 2009 13:16:19 -0500
Bill Moran <wmoran@potentialtech.com> wrote:

> In response to rhubbell <Rhubbell@iHubbell.com>:
> >
> > I'm a new user to PostgreSQL so mine's fresh from doing an install recently.
> >
> > In /etc/postgresql/8.3/main/pg_hba.conf
> >
> > # METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi",
> > # "krb5", "ident", "pam" or "ldap".  Note that "password" sends passwords
> > # in clear text; "md5" is preferred since it sends encrypted passwords.
> >
> >
> > So I chose md5 but it will not work, seems like a basic thing. So I am
> > forced to use "trust".
>
> How on earth does failure of md5 to work force you to use trust?
>
> How about crypt or password (password is pretty darn simple to set up).
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Pet Peeves?

From
rhubbell
Date:
Ok will have a look and get back to you, thanks.

On Thu, 29 Jan 2009 13:39:08 -0500 (EST)
Greg Smith <gsmith@gregsmith.com> wrote:

> On Thu, 29 Jan 2009, rhubbell wrote:
>
> > So I chose md5 but it will not work, seems like a basic thing. So I am
> > forced to use "trust".  These are the kinds of things that wear down
> > busy people trying use the software. Maybe this is a documentation
> > enhancement or bug.
>
> I wrote up a first draft of something aimed at this particular area at
> http://wiki.postgresql.org/wiki/Client_Authentication
>
> I'm still not completely happy with how I describe what you need to switch
> to md5, but at least it points to all the right places people usually
> miss.  If you just went through this recently or still have open issues,
> I'd be curious to get your feedback about that piece.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Pet Peeves?

From
Alan Hodgson
Date:
On Thursday 29 January 2009, Gregory Stark <stark@enterprisedb.com> wrote:
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of
> course, but I would be interested to hear if people have any complaints
> from personal experience. What would be most interesting is if you can
> explain an example of when the problem caused real inconvenience to you,
> since sometimes it's hard to see from a theoretical description where the
> real harm lies.
>
> So, what do people say? Is Postgres perfect in your world or does it do
> some things which rub you the wrong way?

Lack of in-place upgrades for major version changes.

I have others, but honestly this is the biggest that comes up over and over
again.


--
Current Peeve: The mindset that the Internet is some sort of school for
novice sysadmins and that everyone -not- doing stupid dangerous things
should act like patient teachers with the ones who are. -- Bill Cole, NANAE

md5 doesn't work (Was Re: Pet Peeves?)

From
Jeff Frost
Date:
On Thu, 29 Jan 2009, rhubbell wrote:

> Umm, because md5 doesn't work and trust does work.

Generally this is because you haven't yet set a password for the postgres
user.  You have to set a password for at least the postgres user via ALTER
ROLE while you've still got it set to trust or ident before changing to md5.

> On Thu, 29 Jan 2009 13:16:19 -0500
> Bill Moran <wmoran@potentialtech.com> wrote:
>
>> In response to rhubbell <Rhubbell@iHubbell.com>:
>>>
>>> I'm a new user to PostgreSQL so mine's fresh from doing an install recently.
>>>
>>> In /etc/postgresql/8.3/main/pg_hba.conf
>>>
>>> # METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi",
>>> # "krb5", "ident", "pam" or "ldap".  Note that "password" sends passwords
>>> # in clear text; "md5" is preferred since it sends encrypted passwords.
>>>
>>>
>>> So I chose md5 but it will not work, seems like a basic thing. So I am
>>> forced to use "trust".
>>
>> How on earth does failure of md5 to work force you to use trust?
>>
>> How about crypt or password (password is pretty darn simple to set up).
>>

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 916-647-6411    FAX: 916-405-4032

Re: md5 doesn't work (Was Re: Pet Peeves?)

From
rhubbell
Date:
On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
Jeff Frost <jeff@frostconsultingllc.com> wrote:

> On Thu, 29 Jan 2009, rhubbell wrote:
>
> > Umm, because md5 doesn't work and trust does work.
>
> Generally this is because you haven't yet set a password for the postgres
> user.  You have to set a password for at least the postgres user via ALTER
> ROLE while you've still got it set to trust or ident before changing to md5.

Yeah, yeah, did all that, didn't work. Sorry, still a "Pet Peeve". (^;
While you mention it, another "Pet Peeve" was the use of ident. Yikes.

>
> > On Thu, 29 Jan 2009 13:16:19 -0500
> > Bill Moran <wmoran@potentialtech.com> wrote:
> >
> >> In response to rhubbell <Rhubbell@iHubbell.com>:
> >>>
> >>> I'm a new user to PostgreSQL so mine's fresh from doing an install recently.
> >>>
> >>> In /etc/postgresql/8.3/main/pg_hba.conf
> >>>
> >>> # METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi",
> >>> # "krb5", "ident", "pam" or "ldap".  Note that "password" sends passwords
> >>> # in clear text; "md5" is preferred since it sends encrypted passwords.
> >>>
> >>>
> >>> So I chose md5 but it will not work, seems like a basic thing. So I am
> >>> forced to use "trust".
> >>
> >> How on earth does failure of md5 to work force you to use trust?
> >>
> >> How about crypt or password (password is pretty darn simple to set up).
> >>
>
> --
> Jeff Frost, Owner     <jeff@frostconsultingllc.com>
> Frost Consulting, LLC     http://www.frostconsultingllc.com/
> Phone: 916-647-6411    FAX: 916-405-4032

Re: Pet Peeves?

From
Andrew Sullivan
Date:
On Thu, Jan 29, 2009 at 09:51:42AM -0800, Joshua D. Drake wrote:
> It should be pg_backup and that is it, with a nice -R flag for restore.

I suppose you think that ssh_add -D is an intuitive interface too?  ;-)

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Re: md5 doesn't work (Was Re: Pet Peeves?)

From
Bill Moran
Date:
In response to rhubbell <Rhubbell@iHubbell.com>:

> On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
> Jeff Frost <jeff@frostconsultingllc.com> wrote:
>
> > On Thu, 29 Jan 2009, rhubbell wrote:
> >
> > > Umm, because md5 doesn't work and trust does work.
> >
> > Generally this is because you haven't yet set a password for the postgres
> > user.  You have to set a password for at least the postgres user via ALTER
> > ROLE while you've still got it set to trust or ident before changing to md5.
>
> Yeah, yeah, did all that, didn't work. Sorry, still a "Pet Peeve". (^;
> While you mention it, another "Pet Peeve" was the use of ident. Yikes.

_My_ point was that a broken md5 (which is still under debate) doesn't
force you to use trust.  There are other auth options like crypt and
password.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Re: md5 doesn't work (Was Re: Pet Peeves?)

From
Alan Hodgson
Date:
On Thursday 29 January 2009, rhubbell <Rhubbell@ihubbell.com> wrote:
> On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
>
> Jeff Frost <jeff@frostconsultingllc.com> wrote:
> > On Thu, 29 Jan 2009, rhubbell wrote:
> > > Umm, because md5 doesn't work and trust does work.
> >
> > Generally this is because you haven't yet set a password for the
> > postgres user.  You have to set a password for at least the postgres
> > user via ALTER ROLE while you've still got it set to trust or ident
> > before changing to md5.
>
> Yeah, yeah, did all that, didn't work. Sorry, still a "Pet Peeve". (^;
> While you mention it, another "Pet Peeve" was the use of ident. Yikes.
>

Well, it works for everyone else. So clearly you missed something.

--
Current Peeve: The mindset that the Internet is some sort of school for
novice sysadmins and that everyone -not- doing stupid dangerous things
should act like patient teachers with the ones who are. -- Bill Cole, NANAE

Re: Pet Peeves?

From
rhubbell
Date:
Another "Pet Peeve":

Where oh where is pg_config? Oh where oh where can it be?


On Thu, 29 Jan 2009 13:16:17 +0000
Gregory Stark <stark@enterprisedb.com> wrote:

>
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
> but I would be interested to hear if people have any complaints from personal
> experience. What would be most interesting is if you can explain an example of
> when the problem caused real inconvenience to you, since sometimes it's hard
> to see from a theoretical description where the real harm lies.
>
> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?
>
> Feel free to respond on-list or if you prefer in personal emails. I do intend
> to use the ideas you give in my presentation so mark anything you wouldn't be
> happy to see in a slide at a conference some day.
>
> --
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>   Ask me about EnterpriseDB's Slony Replication support!
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Pet Peeves?

From
Dave Page
Date:
On Thu, Jan 29, 2009 at 8:10 PM, rhubbell <Rhubbell@ihubbell.com> wrote:
>
> Another "Pet Peeve":
>
> Where oh where is pg_config? Oh where oh where can it be?

$PGDIR/bin ?

--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Pet Peeves?

From
Gregory Stark
Date:
Steve Atkins <steve@blighty.com> writes:

> 6. Where's my CPAN equivalent? Postgresql is extensible, but it's hard  to find
> the extension you need, and often harder than it should be to  install.

FWIW our CPAN equivalent is pgfoundry. I don't think we quite have the
critical mass yet that Perl has to really make it a big success though.

Making modules more, uh, modular, so they can be installed and uninstalled
smoothly and preferably without special access privileges is a recognized
issue though.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

Re: Pet Peeves?

From
hubert depesz lubaczewski
Date:
1. pg_dump -d - do i need to explain?
2. psql is not compatible with different (older) version of pg, in terms
   of working \x commands
3. lack of optimizer hints
4. lack of covering indexes
5. lack of jobs (like cron, not like something to do to be paid :)

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Re: Pet Peeves?

From
Ron Mayer
Date:
Gregory Stark wrote:
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of course,


* The capitalization that makes everyone (customers, execs, etc) I introduce
  it to parse the name as Postgre-SQL.


* Last I checked, postgres keeps my laptop's hard drive spinning all the time
  by doing some I/O even when "totally idle".  For that matter, waking
  up the CPUs a few times a second too.

* pgfoundry - which was once described on the mailinglists as
  the "kiss of death" for projects.   Yes, I understand why it is
  what it is; but it's still a pet-peeve.   I can't even put my finger
  on what's annoying about it; but it is.

* Upgrades involving databases with extra modules like postgis.
  $ sh utils/postgis_restore.pl lwpostgis.sql newdb olddb.dump -E=UNICODE

* Finding extensions I might want.  For example:
  - Surely someone wrote a good count(*)-replacement-trigger before.
    Now where can I find one?  Searching for "count" on pgfoundry
    doesn't help me.   Searching varlena genralbits find shows me
    a simple one, but IIRC is lacking when it comes to concurrency.
    Googling just gets me lots of "read the archives" postings in
    the archives.


Re: Pet Peeves?

From
Karsten Hilbert
Date:
On Thu, Jan 29, 2009 at 02:22:28PM -0300, Alvaro Herrera wrote:

> > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> > FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
> > but I would be interested to hear if people have any complaints from personal
> > experience. What would be most interesting is if you can explain an example of
> > when the problem caused real inconvenience to you, since sometimes it's hard
> > to see from a theoretical description where the real harm lies.
> >
> > So, what do people say? Is Postgres perfect in your world or does it do some
> > things which rub you the wrong way?
>
> No foreign keys in inheritance trees
+1

Or, in other words, no indices across table (am I wrong ?).

GNUmed uses table inheritance quite extensively and could
save an additional key column per child table.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Pet Peeves?

From
Steve Atkins
Date:
On Jan 29, 2009, at 12:25 PM, Gregory Stark wrote:

> Steve Atkins <steve@blighty.com> writes:
>
>> 6. Where's my CPAN equivalent? Postgresql is extensible, but it's
>> hard  to find
>> the extension you need, and often harder than it should be to
>> install.
>
> FWIW our CPAN equivalent is pgfoundry. I don't think we quite have the
> critical mass yet that Perl has to really make it a big success
> though.

Kinda. It's much more like a freshmeat/sourceforge equivalent than a
CPAN equivalent. There's no standard package format, no dependency
or version handling, no possibility of automated installation.

It also has a mixture of postgresql modules, client-side code that can
access postgresql and even occasional projects that have nothing much
to do with postgresql.

> Making modules more, uh, modular, so they can be installed and
> uninstalled
> smoothly and preferably without special access privileges is a
> recognized
> issue though.


Yup, that's one prerequisite for the rest of it, really. pgTap, which
we have
now, was probably another.

Cheers,
   Steve


Re: Pet Peeves?

From
Karsten Hilbert
Date:
On Thu, Jan 29, 2009 at 09:51:42AM -0800, Joshua D. Drake wrote:

> If we are listing pet peeves :)
>
> Up to 8.4, postgresql didn't accurately represent timestamps because

Ah, speaking of timestamps:

GNUmed could nicely use a timestamp with time zone which
preserves the time zone that was used for insertion/update.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Pet Peeves?

From
Greg Smith
Date:
On Thu, 29 Jan 2009, Ron Mayer wrote:

>  - Surely someone wrote a good count(*)-replacement-trigger before.
>    Now where can I find one?  Searching for "count" on pgfoundry
>    doesn't help me.   Searching varlena genralbits find shows me
>    a simple one, but IIRC is lacking when it comes to concurrency.

There were two Varlena postings on this and one other good article.  I got
sick of not being to find them every time I wanted to and added links to
them all at http://wiki.postgresql.org/wiki/Slow_Counting

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: Pet Peeves?

From
Erik Jones
Date:
On Jan 29, 2009, at 9:43 AM, David Fetter wrote:

> On Thu, Jan 29, 2009 at 05:18:19PM +0000, Gregory Stark wrote:
>> David Fetter <david@fetter.org> writes:
>>
>>> * No built-in ways to get the information psql gets.  "See what
>>> psql is doing" isn't an option when somebody doesn't have psql on
>>> hand.
>>
>> Uhm, what information are you referring to here?
>
> All the stuff that generates \d output is available only to psql.
> When somebody wants to make another client, or even expose some of
> that functionality, they pretty much have to roll it from scratch.

I'd say a good example close of this is the ability to generate full
create statements for database objects via an SQL command.  I.e.
shelling out to pg_dump is not always a fun option.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






Re: Pet Peeves?

From
Adrian Klaver
Date:
On Thursday 29 January 2009 9:19:15 am rhubbell wrote:
> I'm a new user to PostgreSQL so mine's fresh from doing an install
> recently.
>
>
> In /etc/postgresql/8.3/main/pg_hba.conf
>
> # METHOD can be "trust", "reject", "md5", "crypt", "password", "gss",
> "sspi", # "krb5", "ident", "pam" or "ldap".  Note that "password" sends
> passwords # in clear text; "md5" is preferred since it sends encrypted
> passwords.
>
>
> So I chose md5 but it will not work, seems like a basic thing. So I am
> forced to use "trust".  These are the kinds of things that wear down
> busy people trying use the software. Maybe this is a documentation
> enhancement or bug.
>

You realize the authentication is done top to bottom? The first line that
matches wins :) So if you have a matching "trust" line before your "md5" line
then it will not use md5.

For a better explanation:

http://www.postgresql.org/docs/8.2/interactive/auth-pg-hba-conf.html

"Each record specifies a connection type, a client IP address range (if relevant
for the connection type), a database name, a user name, and the authentication
method to be used for connections matching these parameters. The first record
with a matching connection type, client address, requested database, and user
name is used to perform authentication. There is no "fall-through" or "backup":
if one record is chosen and the authentication fails, subsequent records are
not considered. If no record matches, access is denied."


--
Adrian Klaver
aklaver@comcast.net

Re: Pet Peeves?

From
Craig Ringer
Date:
Gregory Stark wrote:

> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?

The few things that used to really bug me have gone away between 8.1 and
8.3. The big one is that there are no longer issues with temp tables in
PL/PgSQL functions or any of the other problems related to the lack of
automatic plan invalidation for functions.

I could go on forever about the things I LIKE about Pg.


There are a few niggles I have noticed, though:

- VACUUM FULL is rather slow and often leaves indexes badly bloated.
This is a usability issue for new admins, too, who won't know they're
usually better off using CLUSTER. That in its self suggests something's
not right, IMO.

- There's no REINDEX CONCURRENTLY.

- There are no built-in ways for admins to easily discover, be alerted
to, or manually check for index and table bloat. We NEED a
pg_catalog.pg_bloat view IMO, as well as NOTICE level warnings from
VACUUM when very bloated indexes and tables are discovered. This is a
mainly a usability issue for new admins.

- Bytea's literal format is wasteful and is painful to work with.
Supporting something reasonably compact and commonly understood by most
tools and libraries (like, say, base64) would be really nice. It'd also
be useful for backup/restore.

- The problems involved in restoring/upgrading a database to a newer
major version when extensions like PostGIS are in use. Argh.

- Table partitioning is effective, but somewhat clumsy, and would really
benefit from some automatic management tools.

- No column-level triggers and, thus, no way to attach a trigger to a
domain type.

- The need for a dump and restore for major version upgrades. I
understand why, but ...

--
Craig Ringer

Re: Pet Peeves?

From
Martin Gainty
Date:
PROCEDUREs *which compile into Procedure Cache* and have IN/OUT (Mode) parameters..

Martin Gainty
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.




> Date: Fri, 30 Jan 2009 12:37:11 +0900
> From: craig@postnewspapers.com.au
> To: stark@enterprisedb.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Pet Peeves?
>
> Gregory Stark wrote:
>
> > So, what do people say? Is Postgres perfect in your world or does it do some
> > things which rub you the wrong way?
>
> The few things that used to really bug me have gone away between 8.1 and
> 8.3. The big one is that there are no longer issues with temp tables in
> PL/PgSQL functions or any of the other problems related to the lack of
> automatic plan invalidation for functions.
>
> I could go on forever about the things I LIKE about Pg.
>
>
> There are a few niggles I have noticed, though:
>
> - VACUUM FULL is rather slow and often leaves indexes badly bloated.
> This is a usability issue for new admins, too, who won't know they're
> usually better off using CLUSTER. That in its self suggests something's
> not right, IMO.
>
> - There's no REINDEX CONCURRENTLY.
>
> - There are no built-in ways for admins to easily discover, be alerted
> to, or manually check for index and table bloat. We NEED a
> pg_catalog.pg_bloat view IMO, as well as NOTICE level warnings from
> VACUUM when very bloated indexes and tables are discovered. This is a
> mainly a usability issue for new admins.
>
> - Bytea's literal format is wasteful and is painful to work with.
> Supporting something reasonably compact and commonly understood by most
> tools and libraries (like, say, base64) would be really nice. It'd also
> be useful for backup/restore.
>
> - The problems involved in restoring/upgrading a database to a newer
> major version when extensions like PostGIS are in use. Argh.
>
> - Table partitioning is effective, but somewhat clumsy, and would really
> benefit from some automatic management tools.
>
> - No column-level triggers and, thus, no way to attach a trigger to a
> domain type.
>
> - The need for a dump and restore for major version upgrades. I
> understand why, but ...
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Windows Live™ Hotmail®:…more than just e-mail. Check it out.

Re: Pet Peeves?

From
Craig Ringer
Date:
Martin Gainty wrote:
> PROCEDUREs *which compile into Procedure Cache* and have IN/OUT (Mode) parameters..

Do you mean that as a feature request?

If you intended to say something along the lines of: "I'd like stored
procedures, invoked using 'CALL procname(params)' syntax, as distinct
from SQL or PL/PgSQL functions" ... then I do agree that'd be handy.

Their absence could easily become a peeve, especially if you use DB
interfaces that expect to invoke stored procedures this way. Some seem
to want you to jump through some hoops (switch to "native query" mode,
declare functions to the interface, etc) to call server-side functions,
but provide a convenient interface to CALL stored procedures.

--
Craig Ringer

Re: md5 doesn't work (Was Re: Pet Peeves?)

From
Scott Marlowe
Date:
On Thu, Jan 29, 2009 at 12:41 PM, rhubbell <Rhubbell@ihubbell.com> wrote:
> On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
> Jeff Frost <jeff@frostconsultingllc.com> wrote:
>
>> On Thu, 29 Jan 2009, rhubbell wrote:
>>
>> > Umm, because md5 doesn't work and trust does work.
>>
>> Generally this is because you haven't yet set a password for the postgres
>> user.  You have to set a password for at least the postgres user via ALTER
>> ROLE while you've still got it set to trust or ident before changing to md5.
>
> Yeah, yeah, did all that, didn't work. Sorry, still a "Pet Peeve". (^;
> While you mention it, another "Pet Peeve" was the use of ident. Yikes.

So, maybe you could tell us what "didn't work" means in a more
expanded manner, along with things like error messages?  md5 works a
charm for me, and it has since it came out, so I'm wondering what's so
different in your setup that it doesn't.

Re: md5 doesn't work (Was Re: Pet Peeves?)

From
Dave Page
Date:
On Fri, Jan 30, 2009 at 7:55 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Jan 29, 2009 at 12:41 PM, rhubbell <Rhubbell@ihubbell.com> wrote:
>> On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
>> Jeff Frost <jeff@frostconsultingllc.com> wrote:
>>
>>> On Thu, 29 Jan 2009, rhubbell wrote:
>>>
>>> > Umm, because md5 doesn't work and trust does work.
>>>
>>> Generally this is because you haven't yet set a password for the postgres
>>> user.  You have to set a password for at least the postgres user via ALTER
>>> ROLE while you've still got it set to trust or ident before changing to md5.
>>
>> Yeah, yeah, did all that, didn't work. Sorry, still a "Pet Peeve". (^;
>> While you mention it, another "Pet Peeve" was the use of ident. Yikes.
>
> So, maybe you could tell us what "didn't work" means in a more
> expanded manner, along with things like error messages?  md5 works a
> charm for me, and it has since it came out, so I'm wondering what's so
> different in your setup that it doesn't.

We've had hundreds of thousands (more likely millions by now) of
downloads of the community and EDB installers which all use md5 out of
the box, and I don't recall ever seeing anyone complain it doesn't
work.

--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Pet Peeves?

From
Octavio Alvarez
Date:
On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote:
> So, what do people say? Is Postgres perfect in your world or does it
> do some
> things which rub you the wrong way?

For me:

Lack of column-level privileges. It just doesn't help scalability at
all. You end up having different tables each with different permissions,
or having to create a view with a ruleset attached, having to update the
view for each definition change in the view.

Lack of allowing subqueries in CHECK constraints and lack of allowing
references to other columns in DEFAULT clauses (like columns that
default to a calculated formula, but may be overridden if it violates a
constraint)

Now, last time I checked, Postgresql lacked an SQL extension to create
crosstab queries. Even though contrib have some helper functions, they
are not official and work only if the number of columns is actually
predefined. For instance if you want to create a query to produce
different paths to go through a graph, being represented by 1 arc per
record, you will never be able to predict the final number of columns
(path steps) needed for the crosstab without doing the whole query one
extra time to get the max(step).

Of course, there are things that PostgreSQL does great, like the
user-defined types.





Re: Pet Peeves?

From
Gregory Stark
Date:
Octavio Alvarez <alvarezp@alvarezp.ods.org> writes:

> Now, last time I checked, Postgresql lacked an SQL extension to create
> crosstab queries. Even though contrib have some helper functions, they
> are not official and work only if the number of columns is actually
> predefined. For instance if you want to create a query to produce
> different paths to go through a graph, being represented by 1 arc per
> record, you will never be able to predict the final number of columns
> (path steps) needed for the crosstab without doing the whole query one
> extra time to get the max(step).

Is it the hierarchical query ability you're looking for or pivot?
The former we are actually getting in 8.4.

AFAIK even in systems with pivot you still have to declare a fixed list of
columns in advance anyways. Do you see a system where it works differently?


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

Re: Pet Peeves?

From
Peter Eisentraut
Date:
Gregory Stark wrote:
> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?

* No offer of anything-but-CVS on pgfoundry.org

* Lack of REINDEX CONCURRENTLY

* Too many obscure configuration options: memory management, autovacuum
management, freespace map, background writer, WAL.

* Writing robust scripts with psql is difficult and bizarre.

* Default postgresql.conf is too long and verbose.

* Default logging configuration is too minimal.

* Difficult to trace nested things, e.g., nested PL/pgSQL calls,
cascaded foreign key actions.

* No basic health checking: Answering the question, "Is this system
configured reasonably and running smoothly" takes two days of training
and half a day of work.

* Too much accumulated historical garbage that is never cleared out.

Re: Pet Peeves?

From
"Daniel Verite"
Date:
    Gregory Stark wrote:

> Is it the hierarchical query ability you're looking for or pivot?
> The former we are actually getting in 8.4.
>
> AFAIK even in systems with pivot you still have to
> declare a fixed list of columns in advance anyways.
> Do you see a system where it works differently?

MS-Access SQL has a TRANSFORM clause that allows for crosstab queries
without the need to know in advance the number of columns:
http://msdn.microsoft.com/en-us/library/bb208956.aspx

As for Oracle, it wasn't possible until recently but now 11g has the
PIVOT clause:
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f
eatures/11g-pivot.html

In contrast of these clauses, PG's contrib/tablefunc looks more limited
and quite harder to use.

 Best regards,
--
 Daniel
 PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

Re: Pet Peeves?

From
Grzegorz Jaśkiewicz
Date:
On Fri, Jan 30, 2009 at 1:26 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> * Lack of REINDEX CONCURRENTLY
+1

> * Too many obscure configuration options: memory management, autovacuum
> management, freespace map, background writer, WAL.
+100

> * Writing robust scripts with psql is difficult and bizarre.
Well, to be fair - psql isn't a script shell for starters.


> * Difficult to trace nested things, e.g., nested PL/pgSQL calls, cascaded
> foreign key actions.
+1




--
GJ

Re: Pet Peeves?

From
Reg Me Please
Date:
On Friday 30 January 2009 14:26:45 Peter Eisentraut wrote:
> Gregory Stark wrote:
> > So, what do people say? Is Postgres perfect in your world or does it do
> > some things which rub you the wrong way?
>
> * No offer of anything-but-CVS on pgfoundry.org
>
> * Lack of REINDEX CONCURRENTLY
>
> * Too many obscure configuration options: memory management, autovacuum
> management, freespace map, background writer, WAL.
>
> * Writing robust scripts with psql is difficult and bizarre.
>
> * Default postgresql.conf is too long and verbose.
>
> * Default logging configuration is too minimal.
>
> * Difficult to trace nested things, e.g., nested PL/pgSQL calls,
> cascaded foreign key actions.
>
> * No basic health checking: Answering the question, "Is this system
> configured reasonably and running smoothly" takes two days of training
> and half a day of work.
>
> * Too much accumulated historical garbage that is never cleared out.

* No builtin crosstab a-la
http://www.postgresql.org/docs/8.3/interactive/tablefunc.html

* No builtin time intervals a-la http://temporal.projects.postgresql.org/

* LIMIT and OFFSET don't accept non-constant (or non function) values


--
Fahrbahn ist ein graues Band
weisse Streifen, grüner Rand

Re: Pet Peeves?

From
Grzegorz Jaśkiewicz
Date:
On Fri, Jan 30, 2009 at 1:37 PM, Reg Me Please <regmeplease@gmail.com> wrote:

> * LIMIT and OFFSET don't accept non-constant (or non function) values
>
no longer true with 8.4

gjaskie=# create table foo(a serial, b varchar);

gjaskie=# insert into table foo(b) select generate_series(1,10000,
(random()*100)::int);

gjaskie=# insert into  foo(b) select generate_series(1,10000,
(random()*100)::int);

gjaskie=# insert into  foo(b) select generate_series(1,10000,
(random()*100)::int);

gjaskie=# insert into  foo(b) select generate_series(1,10000,
(random()*100)::int);

gjaskie=# select count(*) from foo;
 count
-------
  2019
(1 row)

gjaskie=# select * from foo order by random() limit (select a*random()
from foo order by random() limit 1);
  a   |  b
------+------
 1491 | 8941
  892 | 5347
 1609 | 9649
 1796 | 9985
  190 | 1135
 1445 | 8665
  194 | 1159
   47 | 277
  911 | 5461
 1941 | 6481
  196 | 1171
 1995 | 8911
   25 | 145
 1430 | 8575
  533 | 3193
  172 | 1027
 1111 | 6661
  948 | 5683
  148 | 883
   99 | 589
  173 | 1033
 1224 | 7339
  973 | 5833
 1358 | 8143
 1532 | 9187
  165 | 985
 1996 | 8956
   82 | 487
  820 | 4915
 1185 | 7105
  534 | 3199
   57 | 337
 2019 | 9991
(33 rows)


gjaskie=# select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.3.0 20080428 (Red Hat 4.3.0-8), 32-bit
(1 row)




--
GJ

Re: Pet Peeves?

From
Gregory Stark
Date:
"Daniel Verite" <daniel@manitou-mail.org> writes:

>     Gregory Stark wrote:
>
>> Is it the hierarchical query ability you're looking for or pivot?
>> The former we are actually getting in 8.4.
>>
>> AFAIK even in systems with pivot you still have to
>> declare a fixed list of columns in advance anyways.
>> Do you see a system where it works differently?
>
> MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without
> the need to know in advance the number of columns:
> http://msdn.microsoft.com/en-us/library/bb208956.aspx

That's puzzling. I wonder what they do about clients requesting info about the
results. Or for that matter such queries being used in subqueries or anywhere
else where the surrounding code needs to know the type of results to expect.

> As for Oracle, it wasn't possible until recently but now 11g has the PIVOT
> clause:
> http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f
> eatures/11g-pivot.html

From this the result columns do need to be explicitly listed in advance unless
you're asking for the pivot to be into an xml blob which seems like a whole
different feature really.

> In contrast of these clauses, PG's contrib/tablefunc looks more limited and
> quite harder to use.

Incidentally, the work-around I've used in the past was to aggregate the rows
into an array instead of separate columns. Definitely not the same of course,
just a work-around.

I think PIVOT is enticing too. It'll be interesting to see what happens in the
standard with the divergence between MSSQL and Oracle.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Re: Pet Peeves?

From
Merlin Moncure
Date:
On 1/29/09, Gregory Stark <stark@enterprisedb.com> wrote:
>
>  I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
>  FOSDEM 2009 this year. I have a pretty good idea what some them are of course,

Here are couple of mine.  Had to dig a bit.  There may be some
duplication with others:

*) In place upgrade (everybody's #1)
*) lack of *generally usable* standalone mode which leads to:
*) libpq. needs rewrite: incorporate libpqtypes functionality, sane
error system, many other things.  would be nice to be able to use
libpq in standalone as described above.
*) update foo set foo = foo; doesn't work (currently thread on hackers)
*) named parameters in plain sql functions don't work
*) can't use insert/update returning in subquery (prob #3 requested
after IPU and hot standy)
*) stored procedures!! to some, this means multiset.  to me, it means
pl/pgsql without automatic transaction mgmt.  ideally, we get both
*) CTE expressions can't end in insert (slight variant of above)
*) no easy way to have database feed constants into 'create replace
function'  would be nice to have above take string expression, not
literal
*) libpqtypes bumped to contrib :D
*) would be nice to run some arbitrary sql when session dumps.  only
possible today with highly circuitous on_proc_exit that connects back
to the database...ugh
*) listen/notify needs reworking
*) pl/sh should be raised to contrib at minimum. diamond in the rough

and my #1 pet peeve is <drumroll>:
*) having to answer questions about why count(*) is slow!


merlin

Re: Pet Peeves?

From
Craig Ringer
Date:
Peter Eisentraut wrote:

> * Difficult to trace nested things, e.g., nested PL/pgSQL calls,
> cascaded foreign key actions.

+1

That's a really good point. It'd be particularly nice to have some way
to get EXPLAIN ANALYZE to report on details of the execution of called
SQL functions.

--
Craig Ringer

Re: Pet Peeves?

From
Steve Crawford
Date:
> You can however pull it from a -Fc backup with pg_restore. Just FYI.
>
> Joshua D. Drake
>

Or strip it from a pg_dump/pg_dumpall with sed. Or write your own
function-dumper based on ideas gleaned from various notes/comments on
the web (my approach).

I had not thought of using the -Fc approach but it appears that that
would require dumping the whole database then using pg_restore to pull
the function definition from the dump.

One other thing that would be nice to have for function-dumping whether
in pg_dump or using the -Fc approach would be the ability to dump all
functions of a given name instead of having to go one-by-one. It's
pretty unusual for identically-named functions to have unrelated purposes.

Cheers,
Steve


Re: Pet Peeves?

From
Alvaro Herrera
Date:
Guy Rouillier wrote:

> Back in March 2005, I started an email thread titled "Debugging
> deadlocks".  Most of the experienced PGers participated in that thread.
> The basic issue at that time was that inserting a row into a table with a
> foreign key placed an exclusive row-level lock (SELECT FOR UPDATE) on the
> reference table (the table to which the foreign key refers).  If you
> happen to do inserts on two different tables, each with a foreign key to
> the same reference table, deadlocks are pretty easy to create.  This is
> especially true if the reference table has low cardinality, which is
> often the case.
>
> I don't know if this situation has been improved since that time.

We fixed this in 8.1 IIRC.  FKs now use "SELECT FOR SHARE", which only
takes a shared lock not exclusive, and does away with most deadlocks of
this ilk.  Of course, there are other ways to get in deadlock still.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"I can see support will not be a problem.  10 out of 10."    (Simon Wittber)
      (http://archives.postgresql.org/pgsql-general/2004-12/msg00159.php)

Re: Pet Peeves?

From
Sam Mason
Date:
On Thu, Jan 29, 2009 at 01:16:17PM +0000, Gregory Stark wrote:
> So, what do people say? Is Postgres perfect in your world or does it
> do some things which rub you the wrong way?

 * "anonymous records" are a tad annoying to use
   SELECT (1,2,3) ORDER BY 1;
   SELECT ARRAY[(1,2,3)];
   SELECT MIN((1,2,3));
     or maybe this is more obvious:
     SELECT MIN(x) FROM (VALUES (1,2,3)) x;

 * VALUES assumes it's returning a tuple, making the following too verbose:

     SELECT * FROM (VALUES (1),(2),(3),(4)) x;

 * Arrays of arrays don't work; we only get multi-dimensional arrays.

   as a sub-point, the semantics of multi-dimensional arrays are too
   complicated and it would be nice if at least the arity of the array
   was included in its type

 --
   Sam  http://samason.me.uk/

Re: Pet Peeves?

From
"Daniel Verite"
Date:
    Gregory Stark wrote:

>> MS-Access SQL has a TRANSFORM clause that allows for crosstab
>> queries without the need to know in advance the number of columns:
>> http://msdn.microsoft.com/en-us/library/bb208956.aspx
>
> That's puzzling. I wonder what they do about clients requesting info
> about the results. Or for that matter such queries being used in
> subqueries or anywhere else where the surrounding code needs to know
> the type of results to expect.

Using them in subqueries is just denied. Maybe there are also caveats
with the API.

>> As for Oracle, it wasn't possible until recently but now 11g has the
PIVOT
>> clause:
>>
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f

>> eatures/11g-pivot.html
>
> From this the result columns do need to be explicitly listed in
advance unless
> you're asking for the pivot to be into an xml blob which seems like a
whole
> different feature really.

Ah yes, it's still not as helpful as it should ideally, just closer. I
guess that when implementing PIVOT they would have allowed fully
dynamic columns if that was at all possible, so it's probably not.
Sure, if names/types of columns can be queried before the execute stage
but in the case of PIVOT they had to be generated by the execute stage,
that would lead to an impossible chicken-and-egg situation. Maybe
that's the reason.

 Best regards,
--
 Daniel
 PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

Re: Pet Peeves?

From
rhubbell
Date:
On Thu, 29 Jan 2009 13:16:17 +0000
Gregory Stark <stark@enterprisedb.com> wrote:

>
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of course,

I'll ask again.  You say "I have a pretty good idea what some them are of course"

"of course" because you've heard them all? Or because you've suffered from them
your self?  Both?

What's your list look like?  Or maybe you want everyone else to do your work
for you and don't have anything to share.

Re: Pet Peeves?

From
rhubbell
Date:
Nope, had to find it in another package called libpq-dev.
That's on UbuntuHardy. Maybe it's a maintainer problem?

What logic would lead someone to separate pg_config from everything else?
Do people often just install the server and nothing else? Then what?
You've got a server can you have a working db without any dev tools?
Maybe it's typical to install dev tools off-server?

BTW I ran into the need for pg_config upon installing DBD::Pg.
Maybe DBD::Pg maintainer problem?

Still a "Pet Peeve". (^:
I guess I don't care who owns the pet.


On Thu, 29 Jan 2009 20:19:47 +0000
Dave Page <dpage@pgadmin.org> wrote:

> On Thu, Jan 29, 2009 at 8:10 PM, rhubbell <Rhubbell@ihubbell.com> wrote:
> >
> > Another "Pet Peeve":
> >
> > Where oh where is pg_config? Oh where oh where can it be?
>
> $PGDIR/bin ?
>
> --
> Dave Page
> EnterpriseDB UK:   http://www.enterprisedb.com

Re: Pet Peeves?

From
Scott Ribe
Date:
Having to spell out params just to drop a procedure (especially when I'm
trying things out, realize params need to be changed, and so can't just use
"create or replace function").

Perhaps "drop function foobar" could drop the function if there is only 1
function named foobar?

Perhaps "drop function foobar(*)" could drop all functions named foobar?

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice



Re: Pet Peeves?

From
Dave Page
Date:
On Fri, Jan 30, 2009 at 6:09 PM, rhubbell <Rhubbell@ihubbell.com> wrote:
> On Thu, 29 Jan 2009 13:16:17 +0000
> Gregory Stark <stark@enterprisedb.com> wrote:
>
>>
>> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
>> FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
>
> I'll ask again.  You say "I have a pretty good idea what some them are of course"
>
> "of course" because you've heard them all? Or because you've suffered from them
> your self?  Both?

Probably because he's been hacking on and supporting Postgres through
these lists for many years. It's easy for those of us that have been
regular participants for years and exchange email with the same people
over and over to forget that people are joining these lists all the
time and don't necessarily know what we all do.

--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Pet Peeves?

From
Merlin Moncure
Date:
On 1/30/09, Scott Ribe <scott_ribe@killerbytes.com> wrote:
> Having to spell out params just to drop a procedure (especially when I'm
>  trying things out, realize params need to be changed, and so can't just use
>  "create or replace function").

use psql tab completion.

merlin

Re: Pet Peeves?

From
Bruce Momjian
Date:
hubert depesz lubaczewski wrote:
> 1. pg_dump -d - do i need to explain?

I wonder if we should just rename the -d behavior to something else and
throw a proper error (or ignore the flag if it is before the dbname).

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Pet Peeves?

From
Merlin Moncure
Date:
On 1/30/09, Merlin Moncure <mmoncure@gmail.com> wrote:
> On 1/29/09, Gregory Stark <stark@enterprisedb.com> wrote:
>  >
>  >  I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
>  >  FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
>
>
> Here are couple of mine.  Had to dig a bit.  There may be some
>  duplication with others:
>
>  *) In place upgrade (everybody's #1)
>  *) lack of *generally usable* standalone mode which leads to:
>  *) libpq. needs rewrite: incorporate libpqtypes functionality, sane
>  error system, many other things.  would be nice to be able to use
>  libpq in standalone as described above.
>  *) update foo set foo = foo; doesn't work (currently thread on hackers)
>  *) named parameters in plain sql functions don't work
>  *) can't use insert/update returning in subquery (prob #3 requested
>  after IPU and hot standy)
>  *) stored procedures!! to some, this means multiset.  to me, it means
>  pl/pgsql without automatic transaction mgmt.  ideally, we get both
>  *) CTE expressions can't end in insert (slight variant of above)
>  *) no easy way to have database feed constants into 'create replace
>  function'  would be nice to have above take string expression, not
>  literal
>  *) libpqtypes bumped to contrib :D
>  *) would be nice to run some arbitrary sql when session dumps.  only
>  possible today with highly circuitous on_proc_exit that connects back
>  to the database...ugh
>  *) listen/notify needs reworking
>  *) pl/sh should be raised to contrib at minimum. diamond in the rough
>
>  and my #1 pet peeve is <drumroll>:
>  *) having to answer questions about why count(*) is slow!

ooh, I forgot my number personal #1 peeve (and 1a) .
*) select (func()).* will execute func once per field in returned
record.  This is side effect of
*) '*' being macro expanded into the plan.  I've raised this a couple
of times in hackers.  I don't know if there's a clean solution to this
behavior.

merlin

Re: Pet Peeves?

From
Bruce Momjian
Date:
Octavio Alvarez wrote:
> On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote:
> > So, what do people say? Is Postgres perfect in your world or does it
> > do some
> > things which rub you the wrong way?
>
> For me:
>
> Lack of column-level privileges. It just doesn't help scalability at
> all. You end up having different tables each with different permissions,
> or having to create a view with a ruleset attached, having to update the
> view for each definition change in the view.

Column-level privileges will be in Postgres 8.4.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Pet Peeves?

From
Gregory Stark
Date:
rhubbell <Rhubbell@iHubbell.com> writes:

> Nope, had to find it in another package called libpq-dev.
> That's on UbuntuHardy. Maybe it's a maintainer problem?
>
> What logic would lead someone to separate pg_config from everything else?
> Do people often just install the server and nothing else? Then what?

This is actually *required* by Debian/Ubuntu packaging rules.

The development environment must be packaged separately from shared libraries
like libpq or else major snafus arise when a new soversion of libpq comes out.
You need to be able to have both versions installed simultaneously (in case
you have programs which require both) but that won't work if they both contain
things like header files or executables.

> BTW I ran into the need for pg_config upon installing DBD::Pg.
> Maybe DBD::Pg maintainer problem?

Installing a package for DBD::Pg or building it? The former would indeed be a
package bug.


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

Re: Pet Peeves?

From
Gregory Stark
Date:
rhubbell <Rhubbell@iHubbell.com> writes:

> What's your list look like?  Or maybe you want everyone else to do your work
> for you and don't have anything to share.

Heh, fair enough. Perhaps I should just say you should come to FOSDEM though
:)

The usual bugaboos are things like having to do a dump/restore to upgrade,
weak support for partitioning, no index-only-scans, etc. Things which have
been mentioned several times.

One thing which has *not* been mentioned which i find positively shocking is
VACUUM. This was once our single biggest source of user complaints. Between
Autovacuum improvements and HOT previously and the free space map in 8.4 the
situation will be much improved. However there are still some common usage
patterns where people run into problems.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

Re: Pet Peeves?

From
rhubbell
Date:
On Fri, 30 Jan 2009 20:46:22 +0000
Gregory Stark <stark@enterprisedb.com> wrote:

>
> rhubbell <Rhubbell@iHubbell.com> writes:
>
> > What's your list look like?  Or maybe you want everyone else to do your work
> > for you and don't have anything to share.
>
> Heh, fair enough. Perhaps I should just say you should come to FOSDEM though
> :)

Haha, yeah buy the book, come to the show, see the movie.
Well I don't know what is FOSDEM. Will the talks be placed online
at some point? Up to each contributor? I'll check out what's FOSDEM.

>
> The usual bugaboos are things like having to do a dump/restore to upgrade,
> weak support for partitioning, no index-only-scans, etc. Things which have
> been mentioned several times.
>
> One thing which has *not* been mentioned which i find positively shocking is
> VACUUM. This was once our single biggest source of user complaints. Between
> Autovacuum improvements and HOT previously and the free space map in 8.4 the
> situation will be much improved. However there are still some common usage
> patterns where people run into problems.

I think it was mentioned.
Anyway glad to see that there's vibrant discussion here. Usually a sign of
a healthy community.

>
> --
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>   Ask me about EnterpriseDB's On-Demand Production Tuning

Re: Pet Peeves?

From
Jason Long
Date:
Gregory Stark wrote:
"Daniel Verite" <daniel@manitou-mail.org> writes:
 
	Gregory Stark wrote:
   
Is it the hierarchical query ability you're looking for or pivot?
The former we are actually getting in 8.4. 

AFAIK even in systems with pivot you still have to
declare a fixed list of columns in advance anyways.
Do you see a system where it works differently?     
MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without
the need to know in advance the number of columns:
http://msdn.microsoft.com/en-us/library/bb208956.aspx   
That's puzzling. I wonder what they do about clients requesting info about the
results. Or for that matter such queries being used in subqueries or anywhere
else where the surrounding code needs to know the type of results to expect.
 
As for Oracle, it wasn't possible until recently but now 11g has the PIVOT
clause:
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f 
eatures/11g-pivot.html   
From this the result columns do need to be explicitly listed in advance unless
you're asking for the pivot to be into an xml blob which seems like a whole
different feature really.
 
In contrast of these clauses, PG's contrib/tablefunc looks more limited and
quite harder to use.   
Incidentally, the work-around I've used in the past was to aggregate the rows
into an array instead of separate columns. Definitely not the same of course,
just a work-around. 

I think PIVOT is enticing too. It'll be interesting to see what happens in the
standard with the divergence between MSSQL and Oracle.
 
PIVOT would prove very valuable to my application. :)

Re: Pet Peeves?

From
Jason Long
Date:
Daniel Verite wrote:
>     Gregory Stark wrote:
>
>> Is it the hierarchical query ability you're looking for or pivot?
>> The former we are actually getting in 8.4.
>> AFAIK even in systems with pivot you still have to
>> declare a fixed list of columns in advance anyways.
>> Do you see a system where it works differently?
>
> MS-Access SQL has a TRANSFORM clause that allows for crosstab queries
> without the need to know in advance the number of columns:
> http://msdn.microsoft.com/en-us/library/bb208956.aspx
>
> As for Oracle, it wasn't possible until recently but now 11g has the
> PIVOT clause:
> http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f
> eatures/11g-pivot.html
>
> In contrast of these clauses, PG's contrib/tablefunc looks more
> limited and quite harder to use.
>
> Best regards,
The PIVOT clause would kick ass.

Re: Pet Peeves?

From
Sam Mason
Date:
On Fri, Jan 30, 2009 at 03:32:45PM -0500, Merlin Moncure wrote:
> ooh, I forgot my number personal #1 peeve (and 1a) .
> *) select (func()).* will execute func once per field in returned
> record.  This is side effect of
> *) '*' being macro expanded into the plan.  I've raised this a couple
> of times in hackers.  I don't know if there's a clean solution to this
> behavior.

+1, this is very counter intuitive behavior!


--
  Sam  http://samason.me.uk/

Re: Pet Peeves?

From
Bruce Momjian
Date:
Gregory Stark wrote:
>
> rhubbell <Rhubbell@iHubbell.com> writes:
>
> > What's your list look like?  Or maybe you want everyone else to do your work
> > for you and don't have anything to share.
>
> Heh, fair enough. Perhaps I should just say you should come to FOSDEM though
> :)
>
> The usual bugaboos are things like having to do a dump/restore to upgrade,
> weak support for partitioning, no index-only-scans, etc. Things which have
> been mentioned several times.
>
> One thing which has *not* been mentioned which i find positively shocking is
> VACUUM. This was once our single biggest source of user complaints. Between
> Autovacuum improvements and HOT previously and the free space map in 8.4 the
> situation will be much improved. However there are still some common usage
> patterns where people run into problems.

Yes, that is surprising, and something most database product struggle
with.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Pet Peeves?

From
Ron Mayer
Date:
Gregory Stark wrote:
> One thing which has *not* been mentioned which i find positively shocking is
> VACUUM. This was once our single biggest source of user complaints. Between
> Autovacuum improvements and HOT previously and the free space map in 8.4 the
> situation will be much improved.

The other 2 features that made VACUUM much less painful for me was
vacuum_cost_delay (8.0?) and allowing concurrent GiST indexes (8.1?).

Before those features were in, VACUUM could choke even a very
lightly loaded moderately large database for arbitrarily long times.



I guess I'd still like some more convenient tuning of autovacuum (perhaps
specifying X mbps disk I/O); but I'd say vacuum fell off my pet-peeve list
around the 8.1 timeframe.


Re: Pet Peeves?

From
rhubbell
Date:
On Fri, 30 Jan 2009 20:38:06 +0000
Gregory Stark <stark@enterprisedb.com> wrote:

>
> rhubbell <Rhubbell@iHubbell.com> writes:
>
> > Nope, had to find it in another package called libpq-dev.
> > That's on UbuntuHardy. Maybe it's a maintainer problem?
> >
> > What logic would lead someone to separate pg_config from everything else?
> > Do people often just install the server and nothing else? Then what?
>
> This is actually *required* by Debian/Ubuntu packaging rules.
>
> The development environment must be packaged separately from shared libraries
> like libpq or else major snafus arise when a new soversion of libpq comes out.
> You need to be able to have both versions installed simultaneously (in case
> you have programs which require both) but that won't work if they both contain
> things like header files or executables.

Weren't .so born from a need to save disk space? Maybe startup speed too.
Now they're a PITA.


>
> > BTW I ran into the need for pg_config upon installing DBD::Pg.
> > Maybe DBD::Pg maintainer problem?
>
> Installing a package for DBD::Pg or building it? The former would indeed be a
> package bug.

When I installed the package I did via CPAN so maybe this was my mistake.
Not every CPAN package is packaged for debian so I often times don't bother
checking if a perl module exists in debian I just do
perl -MCPAN -e 'install (DBD::Pg)' or whatever pkg....

Re: Pet Peeves?

From
Octavio Alvarez
Date:
On Fri, 2009-01-30 at 15:32 -0500, Bruce Momjian wrote:
> Octavio Alvarez wrote:
> > On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote:
> > > So, what do people say? Is Postgres perfect in your world or does
> it
> > > do some
> > > things which rub you the wrong way?
> >
> > For me:
> >
> > Lack of column-level privileges. It just doesn't help scalability at
> > all. You end up having different tables each with different
> permissions,
> > or having to create a view with a ruleset attached, having to update
> the
> > view for each definition change in the view.
>
> Column-level privileges will be in Postgres 8.4.

That is one hell of great news!

It is not marked as "Done" in the To-do list in the Wiki, though.

http://wiki.postgresql.org/wiki/Todo#GRANT.2FREVOKE




Re: Pet Peeves?

From
Gregory Stark
Date:
rhubbell <Rhubbell@iHubbell.com> writes:

>> Installing a package for DBD::Pg or building it? The former would indeed be a
>> package bug.
>
> When I installed the package I did via CPAN so maybe this was my mistake.
> Not every CPAN package is packaged for debian so I often times don't bother
> checking if a perl module exists in debian I just do
> perl -MCPAN -e 'install (DBD::Pg)' or whatever pkg....

Ah, well that's not a mistake, but you need to check what -dev packages the
CPAN module you're building requires.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

Re: Pet Peeves?

From
Roger Leigh
Date:
On Fri, Jan 30, 2009 at 03:44:48PM -0800, rhubbell wrote:
> On Fri, 30 Jan 2009 20:38:06 +0000
> Gregory Stark <stark@enterprisedb.com> wrote:
>
> >
> > rhubbell <Rhubbell@iHubbell.com> writes:
> >
> > > Nope, had to find it in another package called libpq-dev.
> > > That's on UbuntuHardy. Maybe it's a maintainer problem?
> > >
> > > What logic would lead someone to separate pg_config from everything else?
> > > Do people often just install the server and nothing else? Then what?
> >
> > This is actually *required* by Debian/Ubuntu packaging rules.
> >
> > The development environment must be packaged separately from shared libraries
> > like libpq or else major snafus arise when a new soversion of libpq comes out.
> > You need to be able to have both versions installed simultaneously (in case
> > you have programs which require both) but that won't work if they both contain
> > things like header files or executables.
>
> Weren't .so born from a need to save disk space? Maybe startup speed too.
> Now they're a PITA.

Not really.  You just need to ensure that you have the correct development
environment for the version of PostgreSQL which you are targetting.  While
it might appear to be unnecessarily complex, you'll find that there's a
very good reason for it.

* Every library in Debian is split into separate runtime and development
  packages (and also documentation).
* Users will only need the runtime.
* Only developers and build dæmons will need to install the -dev
  packages).
* Multiple -dev packages can and do exist for supporting multiple
  library versions, especially during transitions from one version to
  the next.  They can't generally be installed simultaneously
  (conflicting files common to both such as pg_config), so you just
  install the one you require.

This saves valuable diskspace on end-user systems as well as allowing
for the creation of known sane build environments (look up how Debian
uses Build-Depends for automated package building).

> > > BTW I ran into the need for pg_config upon installing DBD::Pg.
> > > Maybe DBD::Pg maintainer problem?
> >
> > Installing a package for DBD::Pg or building it? The former would indeed be a
> > package bug.
>
> When I installed the package I did via CPAN so maybe this was my mistake.
> Not every CPAN package is packaged for debian so I often times don't bother
> checking if a perl module exists in debian I just do
> perl -MCPAN -e 'install (DBD::Pg)' or whatever pkg....

It's always worth checking first (first line):

% apt-cache search dbd | grep -i postgres
libdbd-pg-perl - Perl DBI driver for the PostgreSQL database server
libdbd-pg-ruby - Ruby/DBI PostgreSQL driver
libdbd-pg-ruby1.8 - Ruby/DBI PostgreSQL driver for Ruby 1.8
libdbd-pgsql - PostgreSQL database server driver for libdbi
postgresql-contrib-8.3 - additional facilities for PostgreSQL
libaprutil1-dbd-pgsql - The Apache Portable Runtime Utility Library - PostgreSQL Driver
libdbd-pg-ruby1.9 - Ruby/DBI PostgreSQL driver for Ruby 1.9

% apt-cache search 'dbd.*-perl' | grep -i postgres
libdbd-pg-perl - Perl DBI driver for the PostgreSQL database server
postgresql-contrib-8.3 - additional facilities for PostgreSQL


Regards,
Roger

--
  .''`.  Roger Leigh
 : :' :  Debian GNU/Linux             http://people.debian.org/~rleigh/
 `. `'   Printing on GNU/Linux?       http://gutenprint.sourceforge.net/
   `-    GPG Public Key: 0x25BFB848   Please GPG sign your mail.

Attachment

Re: Pet Peeves?

From
Martijn van Oosterhout
Date:
On Fri, Jan 30, 2009 at 02:43:13PM -0800, Ron Mayer wrote:
> I guess I'd still like some more convenient tuning of autovacuum (perhaps
> specifying X mbps disk I/O); but I'd say vacuum fell off my pet-peeve list
> around the 8.1 timeframe.

ah yes, that reminds me. If I know what my disk subsystem is capable of
I'd like to be able to say: VACUUM, please don't use more than 10MB/s.
You can do it now with a calculator and a lot of reading the docs but
surely Pg can work this out for itself.

Nicest would be ofcourse a niceness level, so that VACUUM slows itself
down according to the amount of queries going on (to a minimum
ofcourse).

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Attachment

Re: Pet Peeves?

From
Shane Ambler
Date:
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

Re: Pet Peeves?

From
"Holger Hoffstaette"
Date:
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


Re: Pet Peeves?

From
Octavio Alvarez
Date:
On Fri, 2009-01-30 at 14:25 +0000, Gregory Stark wrote:
> "Daniel Verite" <daniel@manitou-mail.org> writes:
>
> >     Gregory Stark wrote:
> >
> >> Is it the hierarchical query ability you're looking for or pivot?
> >> The former we are actually getting in 8.4.
> >>
> >> AFAIK even in systems with pivot you still have to
> >> declare a fixed list of columns in advance anyways.
> >> Do you see a system where it works differently?
> >
> > MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without
> > the need to know in advance the number of columns:
> > http://msdn.microsoft.com/en-us/library/bb208956.aspx
>
> That's puzzling. I wonder what they do about clients requesting info about the
> results. Or for that matter such queries being used in subqueries or anywhere
> else where the surrounding code needs to know the type of results to expect.

It doesn't really matter. Since crosstabs are just a presentational
variation to a query with aggregate functions and GROUP BY clauses,
whenever you need the results in a crosstabbed recordset for further
processing you will either (a) know in advanced the final number of
columns --as in "I want the count for this, this and this attribute: 3
columns + row header"--, or (b) get the information from the original
aggregated subquery --as in "I want the longest path to traverse the
tree/graph"--.

So in fact, PG would be perfectly OK in not giving the information,
since it is not needed. However, it may be counterintuitive, so it
should be perfectly well documented.

Also, even if PG would manage to provide the precise result
characteristics in advance by evaluating the whole crosstab, the
information would not be trustworthy, since it may well change in the
next second.

I understand that this, being a presentational issue, might get me some
"this is not a DBMS issue"-like kind of responses, but (a) I definitely
trust PG speed and reliability more than PHP/Java/whatever language and
(b) I prefer to put all the hard work on the DB (or I would end up doing
JOINs myself).



Re: Pet Peeves?

From
rhubbell
Date:
Thanks, using the same apt commands, try to find pg_config.
(^;



On Sat, 31 Jan 2009 12:38:18 +0000
Roger Leigh <rleigh@codelibre.net> wrote:

> On Fri, Jan 30, 2009 at 03:44:48PM -0800, rhubbell wrote:
> > On Fri, 30 Jan 2009 20:38:06 +0000
> > Gregory Stark <stark@enterprisedb.com> wrote:
> >
> > >
> > > rhubbell <Rhubbell@iHubbell.com> writes:
> > >
> > > > Nope, had to find it in another package called libpq-dev.
> > > > That's on UbuntuHardy. Maybe it's a maintainer problem?
> > > >
> > > > What logic would lead someone to separate pg_config from everything else?
> > > > Do people often just install the server and nothing else? Then what?
> > >
> > > This is actually *required* by Debian/Ubuntu packaging rules.
> > >
> > > The development environment must be packaged separately from shared libraries
> > > like libpq or else major snafus arise when a new soversion of libpq comes out.
> > > You need to be able to have both versions installed simultaneously (in case
> > > you have programs which require both) but that won't work if they both contain
> > > things like header files or executables.
> >
> > Weren't .so born from a need to save disk space? Maybe startup speed too.
> > Now they're a PITA.
>
> Not really.  You just need to ensure that you have the correct development
> environment for the version of PostgreSQL which you are targetting.  While
> it might appear to be unnecessarily complex, you'll find that there's a
> very good reason for it.
>
> * Every library in Debian is split into separate runtime and development
>   packages (and also documentation).
> * Users will only need the runtime.
> * Only developers and build dæmons will need to install the -dev
>   packages).
> * Multiple -dev packages can and do exist for supporting multiple
>   library versions, especially during transitions from one version to
>   the next.  They can't generally be installed simultaneously
>   (conflicting files common to both such as pg_config), so you just
>   install the one you require.
>
> This saves valuable diskspace on end-user systems as well as allowing
> for the creation of known sane build environments (look up how Debian
> uses Build-Depends for automated package building).
>
> > > > BTW I ran into the need for pg_config upon installing DBD::Pg.
> > > > Maybe DBD::Pg maintainer problem?
> > >
> > > Installing a package for DBD::Pg or building it? The former would indeed be a
> > > package bug.
> >
> > When I installed the package I did via CPAN so maybe this was my mistake.
> > Not every CPAN package is packaged for debian so I often times don't bother
> > checking if a perl module exists in debian I just do
> > perl -MCPAN -e 'install (DBD::Pg)' or whatever pkg....
>
> It's always worth checking first (first line):
>
> % apt-cache search dbd | grep -i postgres
> libdbd-pg-perl - Perl DBI driver for the PostgreSQL database server
> libdbd-pg-ruby - Ruby/DBI PostgreSQL driver
> libdbd-pg-ruby1.8 - Ruby/DBI PostgreSQL driver for Ruby 1.8
> libdbd-pgsql - PostgreSQL database server driver for libdbi
> postgresql-contrib-8.3 - additional facilities for PostgreSQL
> libaprutil1-dbd-pgsql - The Apache Portable Runtime Utility Library - PostgreSQL Driver
> libdbd-pg-ruby1.9 - Ruby/DBI PostgreSQL driver for Ruby 1.9
>
> % apt-cache search 'dbd.*-perl' | grep -i postgres
> libdbd-pg-perl - Perl DBI driver for the PostgreSQL database server
> postgresql-contrib-8.3 - additional facilities for PostgreSQL
>
>
> Regards,
> Roger
>
> --
>   .''`.  Roger Leigh
>  : :' :  Debian GNU/Linux             http://people.debian.org/~rleigh/
>  `. `'   Printing on GNU/Linux?       http://gutenprint.sourceforge.net/
>    `-    GPG Public Key: 0x25BFB848   Please GPG sign your mail.
>

Re: Pet Peeves?

From
Roger Leigh
Date:
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.

Re: Pet Peeves?

From
Greg Stark
Date:
On Sat, Jan 31, 2009 at 6:10 PM, rhubbell <Rhubbell@ihubbell.com> wrote:
> Thanks, using the same apt commands, try to find pg_config

$ apt-file search bin/pg_config
libpq-dev: /usr/bin/pg_config
postgresql-server-dev-8.3: /usr/lib/postgresql/8.3/bin/pg_config

That is confusing actually.

However, the readme for DBD::Pg which cpan gives a convenient
interface has installation instructions which specifically list the
requirements:

cpan[4]> readme DBD::Pg
...[lots of crap about downloading stuff]...

REQUIREMENTS:
-------------

        build, test, and install Perl 5         (at least 5.6.1)
        build, test, and install the DBI module (at least 1.52)
        build, test, and install PostgreSQL     (at least 7.4)
        build, test, and install Test::Simple   (at least 0.47)

DBD::Pg needs to know where to find the libpq libraries: this is usually done
by checking the output of the pg_config executable. If pg_config is
not available,
then you may need to install the development package for PostgreSQL. To do this
on Debian and Ubuntu, use: apt-get install postgresql-dev; on RedHat
and CentOS,
use: yum install postgresql-devel. Note that the development libraries
are needed
even if you already have PostgreSQL up and running.

--
greg

Re: Pet Peeves?

From
Greg Stark
Date:
On Sat, Jan 31, 2009 at 5:34 PM, Octavio Alvarez
<alvarezp@alvarezp.ods.org> wrote:
>
> It doesn't really matter. Since crosstabs are just a presentational
> variation to a query with aggregate functions and GROUP BY clauses,


Why are crosstabs just a presentation issue any more than GROUP BY or ORDER BY?

--
greg

Re: Pet Peeves?

From
Jasen Betts
Date:
On 2009-01-29, Steve Crawford <scrawford@pinpointresearch.com> wrote:
>
>>> 3. Date handling
>>> Sometimes I've got data with invalid dates and it would be great if it
>>> could replace all the bad ones with, say "0000-00-00".
>>>

 0000-00-00 doesn't fit in a date column.
 perhaps you could use null?

 write a function that casts and catches the exception caused when bad
 dates are attempted and returens null, use it to translate your data.



Re: Pet Peeves?

From
Jasen Betts
Date:
On 2009-01-30, Steve Crawford <scrawford@pinpointresearch.com> wrote:
>
>> You can however pull it from a -Fc backup with pg_restore. Just FYI.
>>
>> Joshua D. Drake
>>
>
> Or strip it from a pg_dump/pg_dumpall with sed. Or write your own
> function-dumper based on ideas gleaned from various notes/comments on
> the web (my approach).
>
> I had not thought of using the -Fc approach but it appears that that
> would require dumping the whole database then using pg_restore to pull
> the function definition from the dump.

not the whole database, you can use --schema-only
this can save significant pipe bandwidth.

Is it possible to get pg_restore to list just the named function?

> One other thing that would be nice to have for function-dumping whether
> in pg_dump or using the -Fc approach would be the ability to dump all
> functions of a given name instead of having to go one-by-one. It's
> pretty unusual for identically-named functions to have unrelated purposes.

but sometimes you only want one of them.

in summary it'd be nice to have an equivalent of pg_restore's
"--function=NAME(args)" option on pg_dump and to have the "(args)" part
optional.


Re: Pet Peeves?

From
Scott Marlowe
Date:
On Sat, Jan 31, 2009 at 11:10 AM, rhubbell <Rhubbell@ihubbell.com> wrote:
> Thanks, using the same apt commands, try to find pg_config.
> (^;

It's easy:

/home/smarlowe$ pg_config
The program 'pg_config' is currently not installed.  You can install
it by typing:
sudo apt-get install libpq-dev
bash: pg_config: command not found

I'm running ubuntu 8.04.1.

Re: Pet Peeves?

From
Octavio Alvarez
Date:
On Sat, 2009-01-31 at 18:32 +0000, Greg Stark wrote:
> On Sat, Jan 31, 2009 at 5:34 PM, Octavio Alvarez
> <alvarezp@alvarezp.ods.org> wrote:
> >
> > It doesn't really matter. Since crosstabs are just a presentational
> > variation to a query with aggregate functions and GROUP BY clauses,
>
>
> Why are crosstabs just a presentation issue any more than GROUP BY or ORDER BY?

If I understood your question correctly, it is because you can take any
crosstab and convert it to a vertical list of values that generate it,
with a maximum number of columns, and easily WHERE-able.

For example, a accumulative percent-based grade report:
+-------------+----+----+----+----+---------+
| Assignment  | P1 | P2 | P3 | P4 | Average |
+-------------+----+----+----+----+---------+
| Assignment1 | 95 | 90 | 99 |    |  94.67  |
| Assignment2 | 90 | 90 | 91 |    |  90.33  |
| Assignment3 | 85 | 80 | 95 |    |  86.67  |
+-------------+----+----+----+----+---------+

The data source is:
+-------------+--------+-------+
| Assignment  | Period | Value |
+-------------+--------+-------+
| Assignment1 | P1     | 95    |
| Assignment1 | P2     | 90    |
| Assignment1 | P3     | 99    |
| Assignment2 | P1     | 90    |
| Assignment2 | P2     | 90    |
| Assignment2 | P3     | 91    |
| Assignment3 | P1     | 85    |
| Assignment3 | P2     | 80    |
| Assignment3 | P3     | 95    |
+-------------+--------+-------+

... even if P4 doesn't exist yet. You can have a crosstab with a dynamic
number of columns where P4 doesn't appear as a column, or a pre-defined
number of columns.

And even if each value from the crosstab is an aggregate function like
count() or max() the data source first is prepared as a select/where and
maybe a group by and then transformed into a crosstab.

In any case, the results are the same as GROUPing BY from the data
source.
+-------------+---------+
| Assignment  | Average |
+-------------+---------+
| Assignment1 |  94.67  |
| Assignment2 |  90.33  |
| Assignment3 |  86.67  |
+-------------+---------+

A crosstab is not but a presentational transform of the data set. Any
information you would eventually need can be taken from the original
data source, one way or another. That's why dynamic-column crosstab are
not a problem, and the DBMS should not worry about providing the
information about the columns, maybe by simply not allowing the
dynamic-column ones in subqueries.




Re: Pet Peeves?

From
Gregory Stark
Date:
Octavio Alvarez <alvarezp@alvarezp.ods.org> writes:

> In any case, the results are the same as GROUPing BY from the data
> source.
> +-------------+---------+
> | Assignment  | Average |
> +-------------+---------+
> | Assignment1 |  94.67  |
> | Assignment2 |  90.33  |
> | Assignment3 |  86.67  |
> +-------------+---------+
>
> A crosstab is not but a presentational transform of the data set. Any
> information you would eventually need can be taken from the original
> data source, one way or another. That's why dynamic-column crosstab are
> not a problem, and the DBMS should not worry about providing the
> information about the columns, maybe by simply not allowing the
> dynamic-column ones in subqueries.

What about a WHERE clause like

WHERE P1 > P2

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

Re: Pet Peeves?

From
Octavio Alvarez
Date:
On Sat, 2009-01-31 at 23:36 +0000, Gregory Stark wrote:
> Octavio Alvarez <alvarezp@alvarezp.ods.org> writes:
>
> > A crosstab is not but a presentational transform of the data set. Any
> > information you would eventually need can be taken from the original
> > data source, one way or another. That's why dynamic-column crosstab are
> > not a problem, and the DBMS should not worry about providing the
> > information about the columns, maybe by simply not allowing the
> > dynamic-column ones in subqueries.
>
> What about a WHERE clause like
>
> WHERE P1 > P2

You could either:

(1) do "FROM grades AS g1 INNER JOIN grades AS g2 ON g1.P1 > g2.P2",
generating the record set before applying the crosstab transformation.

(2) Since you are implying the existence of P1 and P2, use the
fixed-number-of-columns crosstab case instead, for which PG would
actually be able to give the column definitions without running the
query, and after that, joining the results with some other query.


Re: Pet Peeves?

From
Reece Hart
Date:
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: Pet Peeves?

From
Octavio Alvarez
Date:
On Sat, 2009-01-31 at 15:54 -0800, Octavio Alvarez wrote:
> On Sat, 2009-01-31 at 23:36 +0000, Gregory Stark wrote:
> > Octavio Alvarez <alvarezp@alvarezp.ods.org> writes:
> >
> > What about a WHERE clause like
> >
> > WHERE P1 > P2
>
> You could either:
>
> (1) do "FROM grades AS g1 INNER JOIN grades AS g2 ON g1.P1 > g2.P2",
> generating the record set before applying the crosstab transformation.

Just to remove all the stupid things I said about the first solution to the WHERE P1 > P2 problem:

Your grades table would be defined as:

test=# \d grades
         Table "public.grades"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 st     | character varying |
 su     | character varying |
 p      | bigint            |
 gr     | bigint            |
Indexes:
    "grades_st_key" UNIQUE, btree (st, p, su)

st = student; su = subject; p = period; gr = grade

The non-crosstab query that gives you the recordset for the crosstab, would be:

SELECT p2_gt_p1.st, p2_gt_p1.su, grades.p, grades.gr
FROM (
   SELECT g1.st, g1.su, g1.p, g1.gr, g2.p, g2.gr
   FROM grades g1 INNER JOIN grades g2
      ON g1.st = g2.st
         AND g1.su = g2.su AND g2.p = 2
         AND g1.p = 1 AND g2.gr > g1.gr
   ) AS p2_gt_p1
   LEFT JOIN grades USING (st, su);



Re: Pet Peeves?

From
Greg Smith
Date:
On Sat, 31 Jan 2009, Reece Hart wrote:

> * lack of auto-tuning or tuning tools (or perhaps my lack of awareness
> of them?)

http://pgfoundry.org/projects/pgtune/ aims to provide a tool for 8.4,
that's working but still needs documentation and some loose ends cleaned
up.  Its suggestions aren't good yet for Windows systems yet, that's the
biggest bug left in there.

That's aimed to automate the suggestions set out in
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

One day I'll make that work better with older versions too.  It does
basically the right thing for 8.3 already but could be smarter, it
includes some parameters that aren't there in 8.2, and doesn't work at all
on 8.1 or earlier.

If you step outside of just free solutions, Enterprise DB's commercial
server product does more complicated autotuning via their DynaTune
feature.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: Pet Peeves?

From
Bruce Momjian
Date:
Octavio Alvarez wrote:
> On Fri, 2009-01-30 at 15:32 -0500, Bruce Momjian wrote:
> > Octavio Alvarez wrote:
> > > On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote:
> > > > So, what do people say? Is Postgres perfect in your world or does
> > it
> > > > do some
> > > > things which rub you the wrong way?
> > >
> > > For me:
> > >
> > > Lack of column-level privileges. It just doesn't help scalability at
> > > all. You end up having different tables each with different
> > permissions,
> > > or having to create a view with a ruleset attached, having to update
> > the
> > > view for each definition change in the view.
> >
> > Column-level privileges will be in Postgres 8.4.
>
> That is one hell of great news!
>
> It is not marked as "Done" in the To-do list in the Wiki, though.
>
> http://wiki.postgresql.org/wiki/Todo#GRANT.2FREVOKE

Oh, sorry, marked as done now:

    http://wiki.postgresql.org/wiki/Todo#GRANT.2FREVOKE

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Pet Peeves?

From
wstrzalka
Date:
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 ??)


Re: Pet Peeves?

From
Bruce Momjian
Date:
wstrzalka wrote:
> * stat collector is really greedy by definition even when system is
> idle, when you have really really many relations

I think this will be fixed in 8.4.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Pet Peeves?

From
wstrzalka
Date:
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.

Re: Pet Peeves?

From
Luis Neves
Date:
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


Re: Pet Peeves?

From
Christopher Browne
Date:
On Thu, Jan 29, 2009 at 8:16 AM, Gregory Stark <stark@enterprisedb.com> wrote:
> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?

Things I'd particularly like to have that aren't entirely on the map yet:

- In place upgrade
- Stored procedures that can manage transactions (e.g. - contrast with
present stored functions that forcibly live *inside* a transaction
context; the point isn't functions vs procedures, but rather to have
something that can do txn management)

I'd expect that txn-managing procedures could then enable various
sorts of usages involving other interesting bits:
- Managing 2PC
- Managing NOTIFY/LISTEN
- Managing jobs (e.g. - "pgcron")
- Using dblink()/SQL-MED to manage cross-DB work

I'd expect these all to be the sorts of side-effects enabled by the
one change...
--
http://linuxfinances.info/info/linuxdistributions.html
Calvin Trillin  - "Health food makes me sick."

Re: Pet Peeves?

From
wstrzalka
Date:
> - 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.


Re: Pet Peeves?

From
Gregory Stark
Date:
Christopher Browne <cbbrowne@gmail.com> writes:

> - Managing jobs (e.g. - "pgcron")

A number of people have mentioned a job scheduler. I think a job scheduler
entirely inside Postgres would be a terrible idea.

However a cron daemon which used Postgres as a storage backend would be very
cool. It could then provide SQL functions to manipulate the schedule and allow
you to set jobs that call database functions using the existing connection
instead of forcing you to write an external script.

This is something someone could do with no special database background, the
hard part is finding a cron source base which is flexible enough to extend to
use a database backend. I'm under the impression most cron daemons are based
on pretty old and ossified source bases and are burdened by a lot of legacy
compatibility requirements.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

Re: Pet Peeves?

From
dpage@pgadmin.org
Date:
You realise you just described the very project you saw me write a
presentation on today right?

:-p

On 2/2/09, Gregory Stark <stark@enterprisedb.com> wrote:
> Christopher Browne <cbbrowne@gmail.com> writes:
>
>> - Managing jobs (e.g. - "pgcron")
>
> A number of people have mentioned a job scheduler. I think a job scheduler
> entirely inside Postgres would be a terrible idea.
>
> However a cron daemon which used Postgres as a storage backend would be very
> cool. It could then provide SQL functions to manipulate the schedule and
> allow
> you to set jobs that call database functions using the existing connection
> instead of forcing you to write an external script.
>
> This is something someone could do with no special database background, the
> hard part is finding a cron source base which is flexible enough to extend
> to
> use a database backend. I'm under the impression most cron daemons are based
> on pretty old and ossified source bases and are burdened by a lot of legacy
> compatibility requirements.
>
> --
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>   Ask me about EnterpriseDB's 24x7 Postgres support!
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Pet Peeves?

From
Guy Rouillier
Date:
Gregory Stark wrote:
> Christopher Browne <cbbrowne@gmail.com> writes:
>
>> - Managing jobs (e.g. - "pgcron")
>
> A number of people have mentioned a job scheduler. I think a job scheduler
> entirely inside Postgres would be a terrible idea.

PgFoundry already has a project called "Job Scheduler".

--
Guy Rouillier

Re: Pet Peeves?

From
Chris
Date:
> - 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/


Re: Pet Peeves?

From
Greg Stark
Date:
On Thu, Jan 29, 2009 at 5:43 PM, David Fetter <david@fetter.org> wrote:
>>
>> > * CTEs not yet integrated into the adjacency lists in pg_catalog,
>> > etc.
>>
>> I'm not sure what you're referring to here either.
>
> The DAG structures in pg_depend leap to mind.  There's no view that
> shows the actual dependencies, except in the sense of, "Here's the
> edges.  Figure it out for yourself."

I'm trying to write some recursive queries for pg_depend and pg_locks.
I think if we come up with some nice ones we might want to add them to
the system views.

pg_depend is actually pretty boring, you would see the same stuff if
you just did a DROP foo RESTRICT after all. I am finding that I'm
really wanting depth first searches which would be easier to read.
That would be interesting direction to head.

pg_locks would be a *lot* more interesting imho. It's awfully hard to
decipher the pg_locks table and find the important information buried
in lots of extraneous minor locks which aren't blocking anything.

However I'm finding it really hard to write anything useful for
pg_locks. It seems we're missing a lot of basic info in pg_locks and
basic infrastructure to make sense of it.

Notably, there's no indication of which lock wait queue the ungranted
locks are in. That means to find out what's blocking a lock would
require comparing every other lock to it and deciding whether it
conflicts.

I haven't thought hard about the pros and cons of adding more info to
pg_locks versus implementing redundant logic in SQL to mirror C code.
Neither seems terribly enticing offhand.

I wonder if anybody else has already implemented something like
lock_conflicts()?


--
greg

Re: Pet Peeves?

From
David Fetter
Date:
On Tue, Feb 03, 2009 at 05:48:51PM +0000, Greg Stark wrote:
> On Thu, Jan 29, 2009 at 5:43 PM, David Fetter <david@fetter.org> wrote:
> >>
> >> > * CTEs not yet integrated into the adjacency lists in pg_catalog,
> >> > etc.
> >>
> >> I'm not sure what you're referring to here either.
> >
> > The DAG structures in pg_depend leap to mind.  There's no view that
> > shows the actual dependencies, except in the sense of, "Here's the
> > edges.  Figure it out for yourself."
>
> I'm trying to write some recursive queries for pg_depend and pg_locks.
> I think if we come up with some nice ones we might want to add them to
> the system views.

Would this be a good time to revisit the idea of a pg_system_views
schema?

> pg_depend is actually pretty boring, you would see the same stuff if
> you just did a DROP foo RESTRICT after all.

Ass-u-me'ing that you have DDL permissions, which the vast majority of
roles should not.

> I am finding that I'm really wanting depth first searches which
> would be easier to read.  That would be interesting direction to
> head.

Depth-first searches are pretty easy to arrange with arrays. :)

> pg_locks would be a *lot* more interesting imho.  It's awfully hard
> to decipher the pg_locks table and find the important information
> buried in lots of extraneous minor locks which aren't blocking
> anything.
>
> However I'm finding it really hard to write anything useful for
> pg_locks.  It seems we're missing a lot of basic info in pg_locks
> and basic infrastructure to make sense of it.
>
> Notably, there's no indication of which lock wait queue the
> ungranted locks are in.  That means to find out what's blocking a
> lock would require comparing every other lock to it and deciding
> whether it conflicts.

Interesting :)

> I haven't thought hard about the pros and cons of adding more info
> to pg_locks versus implementing redundant logic in SQL to mirror C
> code.  Neither seems terribly enticing offhand.
>
> I wonder if anybody else has already implemented something like
> lock_conflicts()?

Dunno.  Could such a thing live in userland, or would it have to be
compiled in?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: Pet Peeves?

From
Chris Mayfield
Date:
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.

Re: Pet Peeves?

From
Peter Geoghegan
Date:
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

Re: Pet Peeves?

From
Steve Atkins
Date:
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


Re: Pet Peeves?

From
Simon Riggs
Date:
On Mon, 2009-02-02 at 22:48 +0000, Gregory Stark wrote:
> Christopher Browne <cbbrowne@gmail.com> writes:
>
> > - Managing jobs (e.g. - "pgcron")
>
> A number of people have mentioned a job scheduler. I think a job scheduler
> entirely inside Postgres would be a terrible idea.

You probably should explain why you think that rather than just rule it
out, though I don't think we should be editing what people ask for.

We already have autovacuum, which runs VACUUM and ANALYZE to a set
schedule. We could have kept that outside core, but didn't.

It's not too big a stretch to imagine we could redesign autovacuum as a
GP scheduler, with autovacuum as just one/two regular scheduled jobs.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


Re: Pet Peeves?

From
Greg Smith
Date:
On Tue, 3 Feb 2009, Greg Stark wrote:

> Notably, there's no indication of which lock wait queue the ungranted
> locks are in. That means to find out what's blocking a lock would
> require comparing every other lock to it and deciding whether it
> conflicts.

The tool I find myself wanting here would parse pg_locks, find everything
that wasn't granted, scan through looking for the source of contention as
you describe, try to look up what any blockers are doing via
pg_stat_activity, then report on its findings.  That's not so difficult to
do by hand that I've bothered automating it completely for the occasional
time this pops up.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: Pet Peeves?

From
Simon Riggs
Date:
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


Re: Pet Peeves?

From
Christopher Browne
Date:
On Mon, Feb 2, 2009 at 5:48 PM, Gregory Stark <stark@enterprisedb.com> wrote:
> Christopher Browne <cbbrowne@gmail.com> writes:
>
>> - Managing jobs (e.g. - "pgcron")
>
> A number of people have mentioned a job scheduler. I think a job scheduler
> entirely inside Postgres would be a terrible idea.

I think it's a terrible idea to put words in my mouth.

I didn't propose including a job scheduler inside every Postgres
instance, and find it disappointing that that is what is assumed,
particularly when none of the other usage cases implied any such
thing.
--
http://linuxfinances.info/info/linuxdistributions.html
Gilda Radner  - "Adopted kids are such a pain - you have to teach them
how to look like you."

Re: Pet Peeves?

From
Jeremy Harris
Date:
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

Re: Pet Peeves?

From
Greg Stark
Date:
On Tue, Feb 3, 2009 at 7:04 PM, David Fetter <david@fetter.org> wrote:
>
>> Notably, there's no indication of which lock wait queue the
>> ungranted locks are in.  That means to find out what's blocking a
>> lock would require comparing every other lock to it and deciding
>> whether it conflicts.
>
> Interesting :)

It would probably be more interesting if what I wrote made sense. I
think I mixed things up enoug that it doesn't though. I'll have to
read through the locking code and figure out the right way to say it
tomorrow.


>> I haven't thought hard about the pros and cons of adding more info
>> to pg_locks versus implementing redundant logic in SQL to mirror C
>> code.  Neither seems terribly enticing offhand.
>>
>> I wonder if anybody else has already implemented something like
>> lock_conflicts()?
>
> Dunno.  Could such a thing live in userland, or would it have to be
> compiled in?

Sure, it's just tedious and error-prone. You compare all the fields of
pg_locks and implement the same rules our locking code follows.

--
greg

Re: Pet Peeves?

From
Greg Stark
Date:
On Tue, Feb 3, 2009 at 10:06 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> On Tue, 2009-02-03 at 15:03 -0500, Chris Mayfield wrote:
>
>> 1. Having to rewrite entire tables out to disk the first time I scan
>> them, for example:
>>
>> CREATE TABLE t1 AS ...; -- writes 100 GB to disk
>> CREATE INDEX i1 ON t1 ...; -- rewrites 100 GB to disk
>>
>> The main issue is setting the hint bits for each tuple, which IMO should
>> initially be set for "CREATE TABLE AS" statements.  To work around this
>> for now, I modified heap_insert (in heapam.c) to mark tuples as
>> committed when inserting them into newly added pages without WAL:
>
> I'll take this for 8.5.

This was proposed once already and some difficulties were identified.
Do you remember what they were?

--
greg

Re: Pet Peeves?

From
Greg Smith
Date:
On Tue, 3 Feb 2009, Jeremy Harris wrote:

> As a further take on the auto-tuning others have mentioned,
> how about some auto-indexing?

That's a significantly harder problem than auto-tuning.
http://it.toolbox.com/blogs/database-soup/finding-useless-indexes-28796 is
a good intro to a subset of that topic, figuring out which indexes you
don't need.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: Pet Peeves?

From
Greg Stark
Date:
On Tue, Feb 3, 2009 at 9:27 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> On Mon, 2009-02-02 at 22:48 +0000, Gregory Stark wrote:
>> Christopher Browne <cbbrowne@gmail.com> writes:
>>
>> > - Managing jobs (e.g. - "pgcron")
>>
>> A number of people have mentioned a job scheduler. I think a job scheduler
>> entirely inside Postgres would be a terrible idea.
>
> You probably should explain why you think that rather than just rule it
> out, though I don't think we should be editing what people ask for.

Sorry, I was even the one who said this should be an open invitation to gripe.

The point I was making is that the original proposal could be
interpreted to mean two different things. One of which I think is an
actively bad idea and one of which I think is actually a good idea. I
also didn't mean to imply Christopher meant one or the other -- sorry.


> We already have autovacuum, which runs VACUUM and ANALYZE to a set
> schedule. We could have kept that outside core, but didn't.
>
> It's not too big a stretch to imagine we could redesign autovacuum as a
> GP scheduler, with autovacuum as just one/two regular scheduled jobs.

Except autovacuum *isn't* a regularly scheduled job and doesn't run
vacuum and analyze on a set schedule. It runs them on a highly dynamic
schedule based on observations of activity in the database. It also
has privileged access to the database, reading from all databases and
receiving signals when various events occur. You cannot implement
autovacuum's current behaviour in cron no matter how clever you make
cron.

A client reimplementing cron using the database as a backend would be
a nice database-driven tool, not just for DBAs but for anyone needing
a more flexible cron demon. It wouldn't even have to be
Postgres-specific, any SQL database would surely be sufficient for the
job. If I were writing it I would start in Perl but I hear the kids
these days are all doing Python or Ruby or something.

My only point was that this would be very different from Oracle-style
job scheduler implemented *inside* the database using
database-specific code and requiring database-specific code to
interact with the outside world. That's just reimplementing the whole
world using the database as a weird operating system which is someone
else's game.

As I said, I don't know which Christopher was thinking of, apparently
not the latter based on his subsequent response. But in case anyone
else was or simply hadn't realized there were two different
conceptions of this I wanted to draw the distinction.



--
greg

Re: Pet Peeves?

From
Guy Rouillier
Date:
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

Re: Pet Peeves?

From
Scott Marlowe
Date:
On Tue, Feb 3, 2009 at 8:58 PM, Guy Rouillier <guyr-ml1@burntmail.com> wrote:
> Greg Stark wrote:
>>
>> My only point was that this would be very different from Oracle-style
>> job scheduler implemented *inside* the database using
>> database-specific code and requiring database-specific code to
>> interact with the outside world. That's just reimplementing the whole
>> world using the database as a weird operating system which is someone
>> else's game.
>
> And someone else might want to play that game inside PG ;).  Seriously, we
> already have programs running inside the DB (stored procs), so why not jobs?
>  I can think of several useful applications.  I have an application with a
> high volume of inserts (60M + per day).  Maybe I can conceive of some way to
> reorganize the previous day's data at 2 am each morning that will provide
> much better performance.  Since all that activity is inside the database,
> why not schedule it inside the DB also?  It's the same logic to justify
> stored procs.

Yep, this allows enough separation from OS and db that I can give
users permission to schedule jobs in the db without needing to have an
account on my db server or a cronjob connection remotely and
anonymously from who knows what machine.

Re: Pet Peeves?

From
Craig Ringer
Date:
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

Re: Pet Peeves?

From
Guy Rouillier
Date:
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

Re: Pet Peeves?

From
Craig Ringer
Date:
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

Re: Pet Peeves?

From
Scott Marlowe
Date:
On Tue, Feb 3, 2009 at 10:09 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> Guy Rouillier wrote:
>>
>> Craig Ringer wrote:
>>>
>>> An internal job scheduler with the ability to fire jobs on certain events
>>> as well as on a fixed schedule could be particularly handy in conjunction
>>> with true stored procedures that could explicitly manage transactions.
>>
>> Craig, what kind of "events" are you thinking about?  Triggers are already
>> pieces of code that run upon "certain events", namely insert, update or
>> delete events.  What others do you have in mind?
>
> That's a good point, actually. I can't think of much you can't do with a
> trigger (SECURITY DEFINER if necessary) on a table. Not thinking straight.

other than schedule it to go off at certain times, not much you can't do.

Re: Pet Peeves?

From
Simon Riggs
Date:
On Wed, 2009-02-04 at 03:00 +0000, Greg Stark wrote:
> We already have autovacuum, which runs VACUUM and ANALYZE to a set
> > schedule. We could have kept that outside core, but didn't.
> >
> > It's not too big a stretch to imagine we could redesign autovacuum
> as a
> > GP scheduler, with autovacuum as just one/two regular scheduled
> jobs.
>
> Except autovacuum *isn't* a regularly scheduled job and doesn't run
> vacuum and analyze on a set schedule. It runs them on a highly dynamic
> schedule based on observations of activity in the database. It also
> has privileged access to the database, reading from all databases and
> receiving signals when various events occur. You cannot implement
> autovacuum's current behaviour in cron no matter how clever you make
> cron.

So putting a scheduler inside the database allows it to do things it
couldn't otherwise do. Sounds like a great argument for *inclusion*.

AV runs every (configurable) 60 secs. What it does when it runs is its
own business. It has a pool of slaves ready to do real transactional
work and an infrastructure to preferentially cancel work if it
interferes with users. It's clearly a great place to hang other code
that (somebody) would like to run on a regular basis:
* regular maintenance tasks
* performance tuning
* summary table creation/maintenance
* adding partitions
* health checks
* etc

We can keep adding processes every time we want a new function in the
db, or we can add a generic facility. I've already added two special
processes, so I'd rather not add too many more.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


Re: Pet Peeves?

From
Simon Riggs
Date:
On Wed, 2009-02-04 at 02:39 +0000, Greg Stark wrote:
> On Tue, Feb 3, 2009 at 10:06 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> >
> > On Tue, 2009-02-03 at 15:03 -0500, Chris Mayfield wrote:
> >
> >> 1. Having to rewrite entire tables out to disk the first time I scan
> >> them, for example:
> >>
> >> CREATE TABLE t1 AS ...; -- writes 100 GB to disk
> >> CREATE INDEX i1 ON t1 ...; -- rewrites 100 GB to disk
> >>
> >> The main issue is setting the hint bits for each tuple, which IMO should
> >> initially be set for "CREATE TABLE AS" statements.  To work around this
> >> for now, I modified heap_insert (in heapam.c) to mark tuples as
> >> committed when inserting them into newly added pages without WAL:
> >
> > I'll take this for 8.5.
>
> This was proposed once already and some difficulties were identified.
> Do you remember what they were?

Time, mainly. Technical issues were not insurmountable, just more subtle
than I had originally thought.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


Re: Pet Peeves?

From
"Karsten Hilbert"
Date:
> > Craig, what kind of "events" are you thinking about?  Triggers are
> > already pieces of code that run upon "certain events", namely insert,
> > update or delete events.  What others do you have in mind?
>
> That's a good point, actually. I can't think of much you can't do with a
> trigger (SECURITY DEFINER if necessary) on a table. Not thinking straight.

I would find ON CONNECT/DISCONNECT triggers very useful. Probably
this is more similar to database-wide assertions.

Karsten
--
Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger01

Re: Pet Peeves?

From
Richard Huxton
Date:
Gregory Stark wrote:
> Steve Crawford <scrawford@pinpointresearch.com> writes:
>
>>>> 3. Date handling
>>>> Sometimes I've got data with invalid dates and it would be great if it
>>>> could replace all the bad ones with, say "0000-00-00".
>>>>
>> Oh dear $DEITY, no.
>
> I think it would be best if we limited ourselves right now to discussing the
> problems themselves and not debating the pros and cons of possible solutions.

It seems I need to point out that the 0000-00-00 thing was supposed to
be a joke.

--
  Richard Huxton
  Archonet Ltd

Re: Pet Peeves?

From
"A.M."
Date:
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

Re: Pet Peeves?

From
Guy Rouillier
Date:
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

Re: Pet Peeves?

From
John DeSoi
Date:
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.





Re: Pet Peeves?

From
Karsten Hilbert
Date:
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

Re: Pet Peeves?

From
Grzegorz Jaśkiewicz
Date:
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).

Re: Pet Peeves?

From
Simon Riggs
Date:
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


Re: Pet Peeves?

From
Mark Roberts
Date:
On Thu, 2009-01-29 at 13:16 +0000, Gregory Stark wrote:
> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
> but I would be interested to hear if people have any complaints from personal
> experience. What would be most interesting is if you can explain an example of
> when the problem caused real inconvenience to you, since sometimes it's hard
> to see from a theoretical description where the real harm lies.
>
> So, what do people say? Is Postgres perfect in your world or does it do some
> things which rub you the wrong way?
>
> Feel free to respond on-list or if you prefer in personal emails. I do intend
> to use the ideas you give in my presentation so mark anything you wouldn't be
> happy to see in a slide at a conference some day.
>
> --
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>   Ask me about EnterpriseDB's Slony Replication support!
>

A couple of the things weighing in on my mind right now (I could be
utterly wrong in all of them):

- In place upgrade.  Seriously, 3.5TB DBs make me cry...
- Lack of good documentation for the binary copy input format for 'copy
foo from stdin with binary'.  Also, I don't *seem* to be able to use the
same file that I copied out for copy in, which was a total surprise.
- The fetch time with lots of byteas is really bad - selecting them out
effectively *forces* scattered IO, even in what would normally be seq
IO.  It would be really nice if you did all the grabbing of rows that
was required and then *at the end* fetched the appropriate bytea fields
and re-aggregated them appropriately.  This is a *HUGE* performance
killer.
- Bytea copy input format is *exceedingly* large - and the dual parser
thing requiring two backslashes doesn't help!.  W T F, I have got to be
missing something.
    Consider the case where I want to write an int16_t.  What should be
    sprintf(s, "\\%o", i);

    becomes

    sprintf(s, "\\\\%03o\\\\%03o", (i & 0x00FF), (i & 0xFF00));

- Query planning with heavy partitioning takes a huge hit, and this
isn't helped by multiple cores on the same box.  It would be very nice
of subpartitions could simply be ignored if their parent partition
wasn't required, but the planner still takes locks on them.
- The ability to add a table to the inheritance structure without
obtaining an acc ex lock would be really nice.
- The ability to rebuild a table or index concurrently would be nice,
especially if it automatically picked up interim changes and applied
them before switching out and dropping the table.
- Slony is really too slow to use for large quantities of data shipping.
IIRC we had to move off of it when the DB was still sub 1 TB.
- Lots of temp table creation/dropping plays havoc with the catalog
tables and eventually requires a full maintenance window to resolve.
- Creating an empty table with foreign keys requires an acc ex lock on
all tables.  Blargh.
- It'd be nice if the query planner was more "stable" - sometimes the
queries run fast, and then sometimes they randomly take 2 hours for a
delete that normally runs in a couple of minutes.

There's (alot) more, but I can't recall it all because I'm overall
pretty happy with Postgres.

-Mark


Re: Pet Peeves?

From
Merlin Moncure
Date:
On Mon, Feb 2, 2009 at 4:54 PM, Christopher Browne <cbbrowne@gmail.com> wrote:
> - Stored procedures that can manage transactions (e.g. - contrast with
> present stored functions that forcibly live *inside* a transaction
> context; the point isn't functions vs procedures, but rather to have
> something that can do txn management)

IMO, once the current crop of in-progress features are rolled up (in
place upgrade, hot standby, etc)...this is one of two 'must have'
features...the other being revamped listen/notify.

merlin

Re: Pet Peeves?

From
Peter Eisentraut
Date:
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.

Re: Pet Peeves?

From
Peter Eisentraut
Date:
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.

Re: Pet Peeves?

From
Grant Allen
Date:
Mark Roberts wrote:
> - It'd be nice if the query planner was more "stable" - sometimes the
> queries run fast, and then sometimes they randomly take 2 hours for a
> delete that normally runs in a couple of minutes.


I was going to stay silent, because my pet peeves were already covered or had been fixed (btw, thanks to whomever fixed
sqlstandard "quote escaping a quote" all those years ago :-) ).  But Mark's suggestion is excellent.  Plan stability /
Storedplanner outlines / whatever you want to call it, is hugely valuable when data volumes change so frequently that
theplanner never knows the "good" stats from the "bad", and also when upgrading to lessen the "OMG, I have to add set
enable_nestloop=falseto 48 billion queries just to overcome new planner quirks" situations.  $OTHER_BIG_RDBMS have had
thisto varying degrees for a while (stored outlines/plan stability in Oracle; bind in DB2; whatever crap name MS gave
theirhalf-arsed version), and when it's mature, the certainty around execution is a life-saver. 

And just to chime in on the already mentioned things:

- in-place upgrades
- replication engine in the core
- true stored procedures
- job scheduler in the core

In all, a short list, which is an oblique way of saying thanks to everyone for the enormous strides that have been made
inthe last few years :-) 

Ciao
Fuzzy
:-)

------------------------------------------------
Dazed and confused about technology for 20 years
http://fuzzydata.wordpress.com/

Re: Pet Peeves?

From
Greg Stark
Date:
On Wed, Feb 4, 2009 at 6:42 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> As A.M. says elsewhere, it would be good to have a trigger that fired a
> NOTIFY that was picked up by a scheduled job that LISTENs every 10
> minutes for certain events.
>
> We need a place for code that is *not* directly initiated by a user's
> actions, yet works as part of a closed loop system.


Can't you do this today in about three lines of your favourite
scripting language?

I used to do this in perl on Oracle and that was without anything like
LISTEN/NOTIFY to optimize it. Web pages just inserted a record and
went on about their business while a worker daemon scanned for new
records and generated notification mails.

The problem with trying to push everything into the database is that
it ends up sucking your entire application into the database. That
limits your choice of languages and tools, and also creates a huge
bottleneck.

--
greg

Re: Pet Peeves?

From
Grzegorz Jaśkiewicz
Date:
On Wed, Feb 4, 2009 at 9:09 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On Wednesday 04 February 2009 20:36:24 Grzegorz Jaśkiewicz wrote:
>> I dream about db wide checks on tables, without need to write
>> expensive triggers.
>> Basically, something that would run a select query after
>> insert/update/delete and based on result commit or rollback.
>> unless there's something like that already in SQL (I am not aware of
>> all features in sql2008 draft).
>
> Sounds like ASSERTION, standard SQL feature.  Certainly interesting.
>
I was sure something like that existed, but had no idea about name.
It also shouldn't be very hard to add, me guesses.




--
GJ

Re: Pet Peeves?

From
Simon Riggs
Date:
On Thu, 2009-02-05 at 11:08 +0000, Greg Stark wrote:

> The problem with trying to push everything into the database is that
> it ends up sucking your entire application into the database. That
> limits your choice of languages and tools, and also creates a huge
> bottleneck.

No, it allows you to choose where to put things. There is no suction
effect, any more than the existence of Python causes everything to be
written in that language.

Nobody said "everything" either, just a scheduling facility.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


Re: Pet Peeves?

From
Grzegorz Jaśkiewicz
Date:
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

Re: Pet Peeves?

From
"A.M."
Date:
On Feb 5, 2009, at 6:08 AM, Greg Stark wrote:

> On Wed, Feb 4, 2009 at 6:42 PM, Simon Riggs <simon@2ndquadrant.com>
> wrote:
>>
>> As A.M. says elsewhere, it would be good to have a trigger that
>> fired a
>> NOTIFY that was picked up by a scheduled job that LISTENs every 10
>> minutes for certain events.
>>
>> We need a place for code that is *not* directly initiated by a user's
>> actions, yet works as part of a closed loop system.
>
>
> Can't you do this today in about three lines of your favourite
> scripting language?
>
> I used to do this in perl on Oracle and that was without anything like
> LISTEN/NOTIFY to optimize it. Web pages just inserted a record and
> went on about their business while a worker daemon scanned for new
> records and generated notification mails.
>
> The problem with trying to push everything into the database is that
> it ends up sucking your entire application into the database. That
> limits your choice of languages and tools, and also creates a huge
> bottleneck.

In addition to the other response, one should also take security
scenarios into consideration. If role X installs an event handler
(timed or via notify), I would expect the callback to be run as that
role X. This is currently impossible to safely do from outside the
database because SET SESSION AUTHORIZATION can be trivially revoked
with RESET SESSION AUTHORIZATION. Also, LISTEN/NOTIFY really need to
allow for additional user info to be attached (it's on the TODO list)
and they have further security problems because they are global
broadcasts. I would expect an integrated event dispatch mechanism to
handle the complexity of security as well as what to do in case of
rollback.

So, no, this is not something can be slapped together from outside the
db.

Cheers,
M

Re: Pet Peeves?

From
Dimitri Fontaine
Date:
Hi,

I think too that having the possibility of scheduling database maintenance
function right into the database would be a great feature. The first use case
that comes to my mind is this */5 cron job which runs psql just to clean out
old sessions and force a vacuum analyze.

On Wednesday 04 February 2009 19:42:27 Simon Riggs wrote:
> As A.M. says elsewhere, it would be good to have a trigger that fired a
> NOTIFY that was picked up by a scheduled job that LISTENs every 10
> minutes for certain events.

In another thread Hannu talked about a completely different need where
integrating a ticker (PGQ) would help. It seems this is just another need for
it, extended to the event producing and consuming facet of it.

 http://archives.postgresql.org/pgsql-hackers/2009-02/msg00117.php
 - New fork to keep no more visible MVCC row version with timestamping
 - Support for time travel facilities (SELECT ... AS OF 'yesterday'::date;)
 - Timestamping done after the fact thanks to the ticker (timestamptz/txid)

> We need a place for code that is *not* directly initiated by a user's
> actions, yet works as part of a closed loop system.

Exactly.
--
dim

Attachment

Re: Pet Peeves?

From
Peter Eisentraut
Date:
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.


Re: Pet Peeves?

From
Alvaro Herrera
Date:
Grzegorz Jaśkiewicz escribió:
> On Wed, Feb 4, 2009 at 9:09 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> > On Wednesday 04 February 2009 20:36:24 Grzegorz Jaśkiewicz wrote:
> >> I dream about db wide checks on tables, without need to write
> >> expensive triggers.
> >> Basically, something that would run a select query after
> >> insert/update/delete and based on result commit or rollback.
> >> unless there's something like that already in SQL (I am not aware of
> >> all features in sql2008 draft).
> >
> > Sounds like ASSERTION, standard SQL feature.  Certainly interesting.
> >
> I was sure something like that existed, but had no idea about name.
> It also shouldn't be very hard to add, me guesses.

A trivial, stupid implementation is perhaps not too difficult.  The
problem is getting the smarts right, i.e. an optimized version.  You
certainly don't want to be executing a query against a large table for
every INSERT on another one, for example; it's better if if you can
figure out when to skip it.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Bringing other columns along with a GROUP BY clause

From
"Rob Richardson"
Date:
Greetings!

I've run into this problem a few times.  There must be a solution, but I
don't know what it is.

I have a table that has three interesting columns:  coil_id, charge, and
coldspot_time.  A charge can have several coils, so there are several
records with differing coil_ids but the same charge.  I want a list of
the coils whose coldspot_times are the largest for their charge.  I can
easily get a list of the maximum coldspot times for each charge:

Select charge, max(coldspot_time)
From inventory
Group by charge

But how do I find out what coil has the maximum coldspot time?

Select coil_id, charge, max(coldspot_time)
From inventory
Group by charge

Doesn't work because SQL can't read my mind well enough to know which of
the four coil_ids in the given charge is the one I want.

Thank you very much!

RobR

Re: Bringing other columns along with a GROUP BY clause

From
Sam Mason
Date:
[ Rob, it would help if you didn't "reply" to unrelated messages.
  Decent mail programs automatically "thread" emails based on what you
  reply to and hence unrelated messages like yours tend to get lost.  ]

On Thu, Feb 05, 2009 at 02:43:07PM -0500, Rob Richardson wrote:
> I want a list of
> the coils whose coldspot_times are the largest for their charge.

> Select coil_id, charge, max(coldspot_time)
> From inventory
> Group by charge

The DISTINCT ON clause would help here, something like:

  SELECT DISTINCT ON (coil_id), coil_id, charge, MAX(largest_time)
  FROM inventory
  GROUP BY coil_id, charge
  ORDER BY coil_id, MAX(largest_time) DESC;

--
  Sam  http://samason.me.uk/

Re: Bringing other columns along with a GROUP BY clause

From
Tom Lane
Date:
"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
> I have a table that has three interesting columns:  coil_id, charge, and
> coldspot_time.  A charge can have several coils, so there are several
> records with differing coil_ids but the same charge.  I want a list of
> the coils whose coldspot_times are the largest for their charge.

If you don't mind a Postgres-ism, the DISTINCT ON clause provides a
reasonably efficient approach for this type of problem.  See the
"weather reports" example in the SELECT reference page.

You could also do something like

select coil_id, charge, coldspot_time
from inventory i
where coldspot_time = (select max(coldspot_time) from inventory i2
                       where i2.charge = i.charge);

which doesn't require any nonstandard features, but on the other hand
its performance will probably suck if charge groups are large (and
even if they aren't, it'll suck without an index on charge).  Also note
that if there are several coils sharing the max in a charge group, this
will show you all of them, which might or might not be what you want.
(The DISTINCT ON method gets you only one maximum per group, but you
can sort on additional columns to prioritize which one to show.)

[ thinks for a bit... ]  Another spec-compliant variant is

select coil_id, charge, coldspot_time
from inventory
where (charge, coldspot_time) in (select charge, max(coldspot_time)
                                  from inventory group by charge);

which should work better than the previous for large charge groups.
This also gives you all the maxima per group.

I have a feeling that it could also be done via the "window functions"
feature due to be introduced in 8.4, but I'm not entirely sure how.
Anybody feeling handy with those?

            regards, tom lane

Re: Bringing other columns along with a GROUP BY clause

From
"Rob Richardson"
Date:
Sam,

Great!  I had no idea DISTINCT ON existed.  That made it much simpler.
Here's what I used:

select distinct on (inventory.charge) coil_id, inventory.charge,
heating_coldspot_time_reached
from inventory
inner join charge on charge.charge = inventory.charge
where base_type = '3' and heating_coldspot_time_reached > 0 and
inventory.status = 'Done' and inventory.charge >= 1000
order by charge, heating_coldspot_time_reached desc

And thanks for the tip about E-mail.  I thought that if I hit the Reply
button and then changed the subject line, it would be the same as
sending a new message to the same destination.  In this case, I had to
change the destination also, so it looked like a brand new message.  I
didn't know I was bringing along old baggage behind the scenes.

Thanks very much!

RobR

Re: Bringing other columns along with a GROUP BY clause

From
"Rob Richardson"
Date:
Thanks very much, Tom.  While the DISTINCT ON suggestion answered the
question I asked very neatly and I am glad to add that concept to my
arsenal, your standard-compliant query was what I actually needed.  The
DISTINCT ON query only gave me one coil if there were two coils in a
charge that had the same coldspot time.  Your standard-compliant query
included them.  There is a fourth column of interest, named coil_trf.
The purpose of this exercise is to map coil_trf values to coldspot times
for the coils in each charge that have the largest coldspot time.  If
two coils have the same coldspot time and none of the others in the
charge have one as long, then I want both coils.

The key concept here that I didn't know about was the use of more than
one field in a list used with IN.

Using actual field names from the database (except for coil_trf, which I
haven't added yet because it comes from another table), here's the query
I ended up with:

select coil_id, inventory.charge, heating_coldspot_time_reached
from inventory
inner join charge on charge.charge = inventory.charge
where base_type = '3' and heating_coldspot_time_reached > 0 and
inventory.status = 'Done' and inventory.charge >= 1000 and
(inventory.charge, heating_coldspot_time_reached) in
(select inventory.charge, max(heating_coldspot_time_reached)
from inventory
inner join charge on charge.charge = inventory.charge
where base_type = '3' and heating_coldspot_time_reached > 0 and
inventory.status = 'Done' and inventory.charge >= 1000
group by inventory.charge)
order by inventory.charge

RobR

Re: Pet Peeves?

From
Erik Jones
Date:
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






Re: Bringing other columns along with a GROUP BY clause

From
Gregory Stark
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:

> I have a feeling that it could also be done via the "window functions"
> feature due to be introduced in 8.4, but I'm not entirely sure how.
> Anybody feeling handy with those?

There may be a better way but something like

select * from (select *, rank() over (partition by charge order by coldspot_time desc) as r) where r = 1

?


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

Re: Pet Peeves?

From
Peter Eisentraut
Date:
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.

Re: Pet Peeves?

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


>> What logic would lead someone to separate pg_config from everything else?
>> Do people often just install the server and nothing else? Then what?

> This is actually *required* by Debian/Ubuntu packaging rules.

> The development environment must be packaged separately from shared libraries
> like libpq or else major snafus arise when a new soversion of libpq comes out.
> You need to be able to have both versions installed simultaneously (in case
> you have programs which require both) but that won't work if they both contain
> things like header files or executables.

I'm not sure I follow this. What makes pg_config so different from psql? I can't
imagine why it's not simply treated the same as pg_dump and psql. It's certainly
annoying to have to install a whole seperate package just to have access to it.

>> BTW I ran into the need for pg_config upon installing DBD::Pg.
>> Maybe DBD::Pg maintainer problem?

> Installing a package for DBD::Pg or building it? The former would indeed be a
> package bug.

AFAIK, no package has that problem. If there is one, someone raise a bug.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200902072126
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkmOQz8ACgkQvJuQZxSWSsh/1QCg/fTaxS2yT9tiyKEhb+NGLUkl
uhkAn0jEHN6NxxynaeTNEQ8+3bHrtCv/
=RKHl
-----END PGP SIGNATURE-----



Re: Pet Peeves?

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> * Letter options in psql, pg_dump[all], pg_restore aren't consistent
>   and can easily steer you very wrong.  I'm looking at you, -d.

Amen!

> So, what do people say?  Is Postgres perfect in your world or does
> it do some things which rub you the wrong way?

You can check the archives for more things I've found annoying in the past,
but might as well put out a quick list here for posterity:

* We're using md5 as the default hash function. Lame.

* We don't have any other builtin encryption/hash stuff other than md5.

* Many more things should be built-in, rather than as contrib or worse. Make them
'installable' like procedural languages if you must, but they have to go in
easier.

* pgfoundry is still a mess. 'nuff said.

* We lost years worth of community shared knowledge when planetpostgresql went down
and the owner won't let people restore their links (e.g. a simple Apache redirect).

* psql is not backwards compatible

* In-place upgrade. Regular dump/restore could be better too.

* The resistance to changing the name officially back to "Postgres".

* The way patches must often be fought for before acceptance.

* The community is scared of change. Exhibit A: git

* Lack of pragmatism at times: real-world DBA work vs. ivory tower pontification.

* Deferred constraints

* psql doesn't do multi-line readline

* The horrible defaults in postgresql.conf

* The horrible commenting in postgresql.conf

* The crappy 8kb GUC unit sizes

* Slow pace of libpq: no binary, no partial result sets

* Logging could be a lot more flexible and fine-grained. Imagine being able to
have slow queries from database X go to a separate log file.

* Horrible bike shedding and making simple solutions complicated.

* Would like to see information_schema expanded.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200902072156
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkmOSfMACgkQvJuQZxSWSsgNIQCgtp9rDWLVyeTDjIvFw+lPWyju
B9kAn0e3mvpaKcu19qZS1qdJdA+vLBfT
=KYSj
-----END PGP SIGNATURE-----



Re: Pet Peeves?

From
rhubbell
Date:
In the case of DBD::Pg it seems that it just uses the output of
pg_config. It seems absurd that that information can't be stored in
psql.  There must be some good reason that it's not.
Is it because psql is stripped?

At least the build information (which pg_config spits out) could be stored
in a text file that psql knows about and then psql --buildopts would
give you that information.

On Sun,  8 Feb 2009 02:28:40 -0000
"Greg Sabino Mullane" <greg@turnstep.com> wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
> >> What logic would lead someone to separate pg_config from everything else?
> >> Do people often just install the server and nothing else? Then what?
>
> > This is actually *required* by Debian/Ubuntu packaging rules.
>
> > The development environment must be packaged separately from shared libraries
> > like libpq or else major snafus arise when a new soversion of libpq comes out.
> > You need to be able to have both versions installed simultaneously (in case
> > you have programs which require both) but that won't work if they both contain
> > things like header files or executables.
>
> I'm not sure I follow this. What makes pg_config so different from psql? I can't
> imagine why it's not simply treated the same as pg_dump and psql. It's certainly
> annoying to have to install a whole seperate package just to have access to it.
>
> >> BTW I ran into the need for pg_config upon installing DBD::Pg.
> >> Maybe DBD::Pg maintainer problem?
>
> > Installing a package for DBD::Pg or building it? The former would indeed be a
> > package bug.
>
> AFAIK, no package has that problem. If there is one, someone raise a bug.
>
> - --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200902072126
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -----BEGIN PGP SIGNATURE-----
>
> iEYEAREDAAYFAkmOQz8ACgkQvJuQZxSWSsh/1QCg/fTaxS2yT9tiyKEhb+NGLUkl
> uhkAn0jEHN6NxxynaeTNEQ8+3bHrtCv/
> =RKHl
> -----END PGP SIGNATURE-----
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Pet Peeves?

From
Steve Atkins
Date:
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


Re: Pet Peeves?

From
Scott Marlowe
Date:
On Sat, Feb 7, 2009 at 7:57 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:

> * psql doesn't do multi-line readline

I thought it started doing that in 8.2 or 8.3.  At least on linux.


> * Logging could be a lot more flexible and fine-grained. Imagine being able to
> have slow queries from database X go to a separate log file.

This, very much this.  I would kill to be able to have each db in a
cluster have its own logging, settings, shared_buffers, etc.  The
whole shared mostly environment makes scaling to > 1 db on a server a
troublesome task

Re: Pet Peeves?

From
rhubbell
Date:
On Sat, 7 Feb 2009 19:30:37 -0800
Steve Atkins <steve@blighty.com> wrote:

>
> On Feb 7, 2009, at 7:09 PM, rhubbell wrote:
>
> > In the case of DBD::Pg it seems that it just uses the output of
> > pg_config. It seems absurd that that information can't be stored in
> > psql.  There must be some good reason that it's not.
> > Is it because psql is stripped?
> >
> > At least the build information (which pg_config spits out) could be
> > stored
> > in a text file that psql knows about and then psql --buildopts would
> > give you that information.
>
> But what would you do with the information then? Most anything
> (including
> building DBD::Pg) that wants that data is going to need the developer
> package.

Oh, that's right DBD::Pg needs to compile against the pg dev bits.

>
> Cheers,
>    Steve
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: Pet Peeves?

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


>> * psql doesn't do multi-line readline

> I thought it started doing that in 8.2 or 8.3.  At least on linux.

It combines all lines into a single statement, which is handy, but things
like this still trip it up:

psql#> CREATE <return>
psql-#> TAB <tab>

> This, very much this.  I would kill to be able to have each db in a
> cluster have its own logging, settings, shared_buffers, etc.  The
> whole shared mostly environment makes scaling to > 1 db on a server a
> troublesome task

Just in case you weren't aware, some settings /can/ be changed per
database with:

ALTER DATABASE prod SET random_page_cost = 2;
ALTER DATABASE fooz SET work_mem = '32 MB';

Not the logging and shared_buffers though...

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200902081214
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkmPE0IACgkQvJuQZxSWSsgk2gCeJoBSGZy6LgUZoSuNc5tGpG+y
5wAAoLndm/ggDECsNM2tFrYOsXA5TIw9
=6HJR
-----END PGP SIGNATURE-----



Re: Pet Peeves?

From
Grzegorz Jaśkiewicz
Date:
drop user X casacde...

say x has an access to database Y, you have to revoke it before
dropping the user... takes ages.

Re: Pet Peeves?

From
Alvaro Herrera
Date:
Erik Jones escribió:

> One workaround I came up with a while back for that is to edit the stat
> file name to be in a separate directory under global (like
> <datadir>/global/pg_stats/pgstat.stat) and mount a ramfs there.  Of
> course, a custom compile isn't always an option but it removed a *ton*
> of IO on that db (had thousands upon thousands of tables).  Also, if you
> do that you need to be sure to copy pgstat.stat to a permanent place
> periodically unless you want to risk losing all of your stats.

Hmm, you don't really need to copy it periodically -- you need to do it
on shutdown only.  If the system crashes, the recovery code will delete
the pgstats file anyway.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Pet Peeves?

From
Steve Crawford
Date:
Richard Huxton wrote:
Gregory Stark wrote: 
Steve Crawford <scrawford@pinpointresearch.com> writes:
   
3. Date handling
Sometimes I've got data with invalid dates and it would be great if it
could replace all the bad ones with, say "0000-00-00".            
Oh dear $DEITY, no.      
I think it would be best if we limited ourselves right now to discussing the
problems themselves and not debating the pros and cons of possible solutions.   
It seems I need to point out that the 0000-00-00 thing was supposed to
be a joke.
 
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: Pet Peeves?

From
Peter Eisentraut
Date:
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.)

Re: Pet Peeves?

From
Alvaro Herrera
Date:
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 ... ??

Re: Pet Peeves?

From
Grzegorz Jaśkiewicz
Date:
On Wed, Feb 11, 2009 at 4:26 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

> Apparently nobody saw the talk ... ??
http://blog.hagander.net/archives/137-FOSDEM-is-done.html

Acording to that page, one of Greg's talks didn't happen. I wasn't
there, but was it the one ?


--
GJ

Re: Pet Peeves?

From
Dave Page
Date:
On Wed, Feb 11, 2009 at 4:45 PM, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote:
> On Wed, Feb 11, 2009 at 4:26 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
>
>> Apparently nobody saw the talk ... ??
> http://blog.hagander.net/archives/137-FOSDEM-is-done.html
>
> Acording to that page, one of Greg's talks didn't happen. I wasn't
> there, but was it the one ?

No - Pet Peeves happened. And could have gone on another hour as the
following BSD speaker didn't show up - but we didn't know that of
course.

--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Pet Peeves?

From
Howard Cole
Date:
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