Pet Peeves?

Gregory Stark wrote:

I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
FOSDEM 2009 this year.

Hmm - three "niggles" things leap to mind.

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

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

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

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

--
Richard Huxton
Archonet Ltd

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

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

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

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

I have 2, closely related:

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

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

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

Terry

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

Gregory Stark wrote:

In response to Terry Fielder <terry@ashtonwoodshomes.com>:

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

SELECT * FROM pg_stat_activity;

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

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

On Thu, Jan 29, 2009 at 01:16:17PM +0000, Gregory Stark wrote:

I'm putting together a talk on "PostgreSQL Pet Peeves" for
discussion at FOSDEM 2009 this year. I have a pretty good idea what
some them are of course, but I would be interested to hear if people
have any complaints from personal experience. What would be most
interesting is if you can explain an example of when the problem
caused real inconvenience to you, since sometimes it's hard to see
from a theoretical description where the real harm lies.

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

Feel free to respond on-list or if you prefer in personal emails. I
do intend to use the ideas you give in my presentation so mark
anything you wouldn't be happy to see in a slide at a conference
some day.

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

* No deferrable UNIQUE constraints.

* No man pages for the internals.

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

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

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

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

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

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

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

Richard Huxton wrote:

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.

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

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

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

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

David Fetter <david@fetter.org> writes:

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

Uhm, what information are you referring to here?

* No man pages for the internals.

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

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

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

Remember to vote!

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

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

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

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

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

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

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

What's your list so far?

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

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!

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)

On Thu, Jan 29, 2009 at 05:18:17PM +0000, Dave Page wrote:

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

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

^^^^^^^^^^^^^

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

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

It's just a peeve. :)

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

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

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

Gregory Stark wrote:

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.

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

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

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

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

-Steve

Gregory Stark wrote:

Jason Long <mailing.list@supernovasoftware.com> writes:

Richard Huxton wrote:

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

I was just wishing for this the other day.

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

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

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

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

--
Richard Huxton
Archonet Ltd

Gregory Stark wrote:

I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at
FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
but I would be interested to hear if people have any complaints from personal
experience. What would be most interesting is if you can explain an example of
when the problem caused real inconvenience to you, since sometimes it's hard
to see from a theoretical description where the real harm lies.

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

Feel free to respond on-list or if you prefer in personal emails. I do intend
to use the ideas you give in my presentation so mark anything you wouldn't be
happy to see in a slide at a conference some day.

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

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

--
Guy Rouillier

On Thu, Jan 29, 2009 at 05:18:19PM +0000, Gregory Stark wrote:

David Fetter <david@fetter.org> writes:

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

Uhm, what information are you referring to here?

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

* No man pages for the internals.

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

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

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

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

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

Remember to vote!

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

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

Oh, and one more "associated project" peeve:

* PostGIS doesn't ship with core PostgreSQL.

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

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

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

David Fetter wrote:

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

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

--
Richard Huxton
Archonet Ltd

On Thu, 2009-01-29 at 17:43 +0000, Richard Huxton wrote:

David Fetter wrote:

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

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

If we are listing pet peeves :)

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

The fact that there is:

pg_dump
pg_dumpall
pg_restore

At all...

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

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

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

Our lack of partitioning :)

Joshua D. Drake

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