Pet Peeves?

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

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

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

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

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

Hmm - three "niggles" things leap to mind.

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

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

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

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

--
Richard Huxton
Archonet Ltd

On Thu, Jan 29, 2009 at 5:16 AM, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:

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

I 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(at)greatgulfhomes(dot)com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085

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

In response to Terry Fielder <terry(at)ashtonwoodshomes(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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.
> 2. Non-deferred unique checks
> Merging two sets of data and renumbering pkeys is more fiddly than it
> needs to be. Doesn't happen often, but it's one of the few things we
> don't do "properly".
>
> 3. Date handling
> Sometimes I've got data with invalid dates and it would be great if it
> could replace all the bad ones with, say "0000-00-00".
>
>
> You could throw in non-updateable views, exact-match tsearch queries,
> per-statement triggers not having the old/new rowsets but that's more
> things PG doesn't do rather than things it does.
>
>

On Thu, Jan 29, 2009 at 4:57 PM, David Fetter <david(at)fetter(dot)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(at)fetter(dot)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(at)enterprisedb(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Jason Long <mailing(dot)list(at)supernovasoftware(dot)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(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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

Gregory Stark wrote:
> Jason Long <mailing(dot)list(at)supernovasoftware(dot)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.
>
>

>> 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(dot)list(at)supernovasoftware(dot)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(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

Steve Crawford <scrawford(at)pinpointresearch(dot)com> writes:

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

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

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

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

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

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

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

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

Gregory Stark wrote:
> Steve Crawford <scrawford(at)pinpointresearch(dot)com> writes:
>
>
>>>> 3. Date handling
>>>> Sometimes I've got data with invalid dates and it would be great if it
>>>> could replace all the bad ones with, say "0000-00-00".
>>>>
>>>>
>> Oh dear $DEITY, no.
>>
>
> I think it would be best if we limited ourselves right now to discussing the
> problems themselves and not debating the pros and cons of possible solutions.
>
> I want to encourage people to post their peeves even if they know perfectly
> well the reasons why things are the way they are.
>
>

Fair enough.

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

Cheers,
Steve

On Jan 29, 2009, at 5:16 AM, Gregory Stark wrote:

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

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

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

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

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

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

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

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

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

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

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

Cheers,
Steve

On Thu, 29 Jan 2009, rhubbell wrote:

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

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

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

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

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

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

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

--
John Fabiani

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

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

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

Regards,
Jeff Davis

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

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

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

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

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

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

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Umm, because md5 doesn't work and trust does work.
On Thu, 29 Jan 2009 13:16:19 -0500
Bill Moran <wmoran(at)potentialtech(dot)com> wrote:

> In response to rhubbell <Rhubbell(at)iHubbell(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Ok will have a look and get back to you, thanks.

On Thu, 29 Jan 2009 13:39:08 -0500 (EST)
Greg Smith <gsmith(at)gregsmith(dot)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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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

Lack of in-place upgrades for major version changes.

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

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

On Thu, 29 Jan 2009, rhubbell wrote:

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

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

> On Thu, 29 Jan 2009 13:16:19 -0500
> Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
>
>> In response to rhubbell <Rhubbell(at)iHubbell(dot)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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032

On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
Jeff Frost <jeff(at)frostconsultingllc(dot)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(at)potentialtech(dot)com> wrote:
> >
> >> In response to rhubbell <Rhubbell(at)iHubbell(dot)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(at)frostconsultingllc(dot)com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 916-647-6411 FAX: 916-405-4032

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

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

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca

In response to rhubbell <Rhubbell(at)iHubbell(dot)com>:

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

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

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

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

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

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

Another "Pet Peeve":

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

On Thu, 29 Jan 2009 13:16:17 +0000
Gregory Stark <stark(at)enterprisedb(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On Thu, Jan 29, 2009 at 8:10 PM, rhubbell <Rhubbell(at)ihubbell(dot)com> wrote:
>
> Another "Pet Peeve":
>
> Where oh where is pg_config? Oh where oh where can it be?

$PGDIR/bin ?

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

Steve Atkins <steve(at)blighty(dot)com> writes:

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

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

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

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

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

depesz

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

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

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

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

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

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

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

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

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

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

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

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

On Jan 29, 2009, at 12:25 PM, Gregory Stark wrote:

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

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

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

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

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

Cheers,
Steve

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

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

Ah, speaking of timestamps:

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

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

On Thu, 29 Jan 2009, Ron Mayer wrote:

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

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

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Jan 29, 2009, at 9:43 AM, David Fetter wrote:

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

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

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

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

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

For a better explanation:

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

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

--
Adrian Klaver
aklaver(at)comcast(dot)net

Gregory Stark wrote:

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

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

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

There are a few niggles I have noticed, though:

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

- There's no REINDEX CONCURRENTLY.

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

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

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

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

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

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

--
Craig Ringer

PROCEDUREs *which compile into Procedure Cache* and have IN/OUT (Mode) parameters..

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

> Date: Fri, 30 Jan 2009 12:37:11 +0900
> From: craig(at)postnewspapers(dot)com(dot)au
> To: stark(at)enterprisedb(dot)com
> CC: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
Windows Live™ Hotmail®:…more than just e-mail.
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_explore_012009

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

Do you mean that as a feature request?

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

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

--
Craig Ringer

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

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

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

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

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

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

For me:

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

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

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

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

Octavio Alvarez <alvarezp(at)alvarezp(dot)ods(dot)org> writes:

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

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

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

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

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

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

* Lack of REINDEX CONCURRENTLY

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

* Writing robust scripts with psql is difficult and bizarre.

* Default postgresql.conf is too long and verbose.

* Default logging configuration is too minimal.

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

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

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

Gregory Stark wrote:

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

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

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

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

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

On Fri, Jan 30, 2009 at 1:26 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> * Lack of REINDEX CONCURRENTLY
+1

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

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

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

--
GJ

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

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

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

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

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

On Fri, Jan 30, 2009 at 1:37 PM, Reg Me Please <regmeplease(at)gmail(dot)com> wrote:

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

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

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

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

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

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

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

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

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

--
GJ

Daniel Verite 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.

"Daniel Verite" <daniel(at)manitou-mail(dot)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!

Gregory Stark wrote:
> "Daniel Verite" <daniel(at)manitou-mail(dot)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. :)

Guy Rouillier wrote:

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

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

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

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

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

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

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

merlin

Peter Eisentraut wrote:

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

+1

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

--
Craig Ringer

> You can however pull it from a -Fc backup with pg_restore. Just FYI.
>
> Joshua D. Drake
>

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

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

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

Cheers,
Steve

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

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

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

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

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

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

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

Gregory Stark wrote:

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

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

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

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

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

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

On Thu, 29 Jan 2009 13:16:17 +0000
Gregory Stark <stark(at)enterprisedb(dot)com> wrote:

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

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

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

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

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

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

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

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

On Thu, 29 Jan 2009 20:19:47 +0000
Dave Page <dpage(at)pgadmin(dot)org> wrote:

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

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

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

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

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

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

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

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

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

use psql tab completion.

merlin

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

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

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

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

merlin

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

Column-level privileges will be in Postgres 8.4.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

rhubbell <Rhubbell(at)iHubbell(dot)com> writes:

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

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

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

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

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

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

rhubbell <Rhubbell(at)iHubbell(dot)com> writes:

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

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

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

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

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

On Fri, 30 Jan 2009 20:46:22 +0000
Gregory Stark <stark(at)enterprisedb(dot)com> wrote:

>
> rhubbell <Rhubbell(at)iHubbell(dot)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

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

+1, this is very counter intuitive behavior!

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

Gregory Stark wrote:
>
> rhubbell <Rhubbell(at)iHubbell(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

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

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

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

On Fri, 30 Jan 2009 20:38:06 +0000
Gregory Stark <stark(at)enterprisedb(dot)com> wrote:

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

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

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

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

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

That is one hell of great news!

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

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

rhubbell <Rhubbell(at)iHubbell(dot)com> writes:

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

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

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

On Fri, Jan 30, 2009 at 03:44:48PM -0800, rhubbell wrote:
> On Fri, 30 Jan 2009 20:38:06 +0000
> Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>
> >
> > rhubbell <Rhubbell(at)iHubbell(dot)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.

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

A few from the top of my head:

- COPY command does not support collation. It's such a pita to massage
huge files that have "," has a decimal separator.
- COPY command does not have a "DO NOT ABORT ON ERROR". I do not mean
constraint checking, I mean that the occasional backslash characters or
rows with a badly encoded characters should not abort the import process.
- No automatic CLUSTERing.
- EXPLAIN does not work with functions.

--
Luis Neves

On 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(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Gregory Stark wrote:

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

Well as subqueries you would expect an error.

Not sure with access but I know that with RealBasic you can handle the
variations. This allows you to build an "access style" front end that
isn't fixed to one db structure.

dim rs as recordset

rs = db.sqlquery("select...")

for x = 1 to rs.fieldcount
if vartype(rs.idxfield(x).value) = kTypeDate then
...
...
next

you could also use rs.idxfield(x).stringvalue and parse it yourself

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

On Sat, 31 Jan 2009 15:28:31 +0100, Martijn van Oosterhout wrote:

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

Linux has IO priority support for this, see ionice. Starting with 2.6.28
the CFQ scheduler behaviour is fixed (no more excessive write delays or
bad read interaction), so autovacuum should really run in a lower
priority class.

-h

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

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

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

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

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

Thanks, using the same apt commands, try to find pg_config.
(^;

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

On Sat, Jan 31, 2009 at 10:10:01AM -0800, rhubbell wrote:
> Thanks, using the same apt commands, try to find pg_config.

Well, those commands search package names and metadata (including
descriptions), and pg_config isn't mentioned so you won't find
anything. Given that pg_config matches the version of postgresql
you have installed, it's included in libpq-dev where you would
expect (this is what all other library packages containing -config
scripts do, though most have nowadays switched to using pkg-config
and providing an associated .pc file).

% dpkg -S $(which pg_config)
libpq-dev: /usr/bin/pg_config

Regards,
Roger

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

On Sat, Jan 31, 2009 at 6:10 PM, rhubbell <Rhubbell(at)ihubbell(dot)com> wrote:
> Thanks, using the same apt commands, try to find pg_config

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

That is confusing actually.

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

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

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

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

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

--
greg

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

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

--
greg

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

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

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

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

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

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

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

but sometimes you only want one of them.

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

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

It's easy:

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

I'm running ubuntu 8.04.1.

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

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

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

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

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

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

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

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

Octavio Alvarez <alvarezp(at)alvarezp(dot)ods(dot)org> writes:

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

What about a WHERE clause like

WHERE P1 > P2

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

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

You could either:

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

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

My two:

* lack of PK/unique indexes on inherited tables (workarounds possible
but annoying)

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

-Reece

--

Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

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

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

Your grades table would be defined as:

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

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

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

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

On Sat, 31 Jan 2009, Reece Hart wrote:

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

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

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

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

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

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

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

Oh, sorry, marked as done now:

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

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

My short list is:

* in-place upgrade
* named parameters in SQL functions
* native jobs
* timestamptz that preserves original timezone (not offset but
political timezone !!!! like America/New_York)
* I hate: "select * from dblink(...) as WHY(I_NEED int4, TO_SPECIFY
int4, THIS text)"
* ability to call set-returning plpgsql function in SELECT, not in
FROM
* stat collector is really greedy by definition even when system is
idle, when you have really really many relations
* lost space when dropping toasted column - can be recovered only by
reindexing table (are there other such a strange cases ??)

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

I think this will be fixed in 8.4.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Feb 2, 8:23 pm, br(dot)(dot)(dot)(at)momjian(dot)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.

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

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

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

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

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

> - EXPLAIN does not work with functions.
+1

and one more about explain - it would be great to have smth like:
EXPLAIN ANALYZE FULL - that would show details about the plan chosen
with detailed explanation and other plans considered.

It would reduce a few posts a week in style:
- 'why the query A doesn't use index B'
with answer
- '... planner .... estimated cost ....'

I think it would help a lot of people if the community don't want to
have hints.

Christopher Browne <cbbrowne(at)gmail(dot)com> writes:

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

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

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

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

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

You realise you just described the very project you saw me write a
presentation on today right?

:-p

On 2/2/09, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> Christopher Browne <cbbrowne(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

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

PgFoundry already has a project called "Job Scheduler".

--
Guy Rouillier

> - COPY command does not support collation. It's such a pita to massage
> huge files that have "," has a decimal separator.

copy with delimiter '#######'

http://www.postgresql.org/docs/current/static/sql-copy.html

--
Postgresql & php tutorials
http://www.designmagick.com/

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

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

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

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

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

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

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

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

--
greg

On Tue, Feb 03, 2009 at 05:48:51PM +0000, Greg Stark wrote:
> On Thu, Jan 29, 2009 at 5:43 PM, David Fetter <david(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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

Here's a few more pet peeves. I'm not sure if any of these are known
bugs or just me being picky.

--Chris

--------------------------------------------------

1. Having to rewrite entire tables out to disk the first time I scan
them, for example:

CREATE TABLE t1 AS ...; -- writes 100 GB to disk
CREATE INDEX i1 ON t1 ...; -- rewrites 100 GB to disk

The main issue is setting the hint bits for each tuple, which IMO should
initially be set for "CREATE TABLE AS" statements. To work around this
for now, I modified heap_insert (in heapam.c) to mark tuples as
committed when inserting them into newly added pages without WAL:

/*
* Optimization for CREATE TABLE AS statements: mark tuples as committed
* to prevent rewriting them to disk upon first use. This is safe since
* the new relation is not visible until the transaction commits anyway.
*/
if (!use_wal && !use_fsm)
{
tup->t_data->t_infomask |= HEAP_XMIN_COMMITTED;
}

--------------------------------------------------

2. Having to use a subquery and/or "OFFSET 0" to prevent multiple calls
to immutable functions returning composite types, for example:

CREATE TYPE three AS
(i integer, j integer, k integer);

CREATE FUNCTION hello()
RETURNS three AS $$
DECLARE ret three;
BEGIN
RAISE NOTICE 'hello';
ret := ROW(1,2,3);
RETURN ret;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- function called three times
SELECT (hello()).*;

-- function called only once
SELECT (h).* FROM (
SELECT hello() AS h
) AS sub;

-- function called three times
SELECT (h).* FROM (
SELECT hello() AS h
FROM generate_series(1,3)
) AS sub;

-- function called only once
SELECT (h).* FROM (
SELECT hello() AS h
FROM generate_series(1,3)
OFFSET 0
) AS sub;

--------------------------------------------------

3. Poor estimates for n_distinct can result in out of memory errors.

For example, I have a user-defined aggregate over a user-defined data
type (both written in C). The aggregate doesn't take much memory, but
the data type can be relatively large (i.e. "STORAGE = extended"). My
table has five million distinct rows, but n_distinct is around 50,000
(increasing the stats target helps a little, but it's still way off).
As a result the planner choses "SeqScan + HashAgg" instead of "IndexScan
+ GroupAgg", and the query aborts when the hash table eventually runs
out of memory.

I currently work around this issue using "SET enable_hashagg TO off;"
when necessary.

What about postgreSQL's inability to re-order columns?

Please don't point out that I shouldn't rely on things being in a
certain order when I SELECT * FROM table. I'm well aware of that, I
just generally have an aesthetic preference for a table's columns
being in a certain order.

Regards,
Peter Geoghegan

On Feb 3, 2009, at 12:41 PM, Peter Geoghegan wrote:

> What about postgreSQL's inability to re-order columns?
>
> Please don't point out that I shouldn't rely on things being in a
> certain order when I SELECT * FROM table. I'm well aware of that, I
> just generally have an aesthetic preference for a table's columns
> being in a certain order.

It's a pet peeve. You don't need to justify it. :)

Cheers,
Steve

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

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

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

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

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

On Tue, 3 Feb 2009, Greg Stark wrote:

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

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

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Tue, 2009-02-03 at 15:03 -0500, Chris Mayfield wrote:

> 1. Having to rewrite entire tables out to disk the first time I scan
> them, for example:
>
> CREATE TABLE t1 AS ...; -- writes 100 GB to disk
> CREATE INDEX i1 ON t1 ...; -- rewrites 100 GB to disk
>
> The main issue is setting the hint bits for each tuple, which IMO should
> initially be set for "CREATE TABLE AS" statements. To work around this
> for now, I modified heap_insert (in heapam.c) to mark tuples as
> committed when inserting them into newly added pages without WAL:

I'll take this for 8.5.

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

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

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

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

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

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

- Jeremy

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

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

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

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

--
greg

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

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

--
greg

On Tue, 3 Feb 2009, Jeremy Harris wrote:

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

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

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

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

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

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

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

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

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

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

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

--
greg

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

And someone else might want to play that game inside PG ;). Seriously,
we already have programs running inside the DB (stored procs), so why
not jobs? I can think of several useful applications. I have an
application with a high volume of inserts (60M + per day). Maybe I can
conceive of some way to reorganize the previous day's data at 2 am each
morning that will provide much better performance. Since all that
activity is inside the database, why not schedule it inside the DB also?
It's the same logic to justify stored procs.

Sure, I can accomplish the same thing via cron and external scripts.
But that's less secure, since I need to store my connection params in
the script. And if I've got 5 different servers running cron jobs, then
my schedule is distributed over those 5 boxes, which becomes a
management issue. As has been pointed out here, the schedule could be
kept in the DB, which would address that. Having a scheduler in the DB
to run those jobs is just the next step.

Different stokes, as they say. All about choice.

--
Guy Rouillier