subject:"\[HACKERS\] TODO list"

On Fri, Oct 16, 2015 at 02:50:04PM +0530, Amit Kapila wrote:
> On Fri, Oct 16, 2015 at 8:34 AM, Bruce Momjian  wrote:
> 
> I have spend the past few days updating our TODO list, removing
> completed and now-unnecessary items:
> 
>         https://wiki.postgresql.org/wiki/Todo
> 
> 
> 
> Thanks.  It can help encourage many new entrants to community.

I reduced the number of items from 466 to 410.

Probably the most controvertial change was to move on-disk bitmap
indexes to the "not wanted" section, though I kept the links in case we
change our minds.  I just can't see how they would be a win with GIN and
in-memory bitmaps.  (I don't think BRIN indexes help for on-disk bitmap
use-cases, do they?)

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

On Fri, Oct 16, 2015 at 12:00:11PM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > I have spend the past few days updating our TODO list, removing
> > completed and now-unnecessary items:
> > 
> > https://wiki.postgresql.org/wiki/Todo
> 
> Thanks.  We have "TodoDone" pages for items that were done in specific
> releases, but only for 8.4, 9.0 and 9.1.  I guess it was too much work
> to create those.  (See https://wiki.postgresql.org/wiki/Category:Todo ).

Oh, I had forgotten about that.  In some cases I knew the release that
completed the item, but in most cases the item had a different approach
which was not taken, or was superceeded by a better solution.

> I added a link to the latest posted version of bitmap indexes by Abhijit
> Menon-Sen, for completeness.

Thanks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

On Fri, Oct 16, 2015 at 12:02:03PM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> 
> > Are you suggesting I remove those links?  It is kind of odd to have
> > links to patches for features we don't want, or just keep it?
> 
> No, quite the contrary -- I think the links allow some other person
> research the issue including the history of patches and discussion, and
> decide for themselves whether the feature is right to be rejected.

I think on-disk bitmap indexes would only beat GIN indexes in a
read-only database on low-cardinality columns.  For example, if you had
a purchase_log table and wanted to know all the "blue" and "large" items
sold at a specific store, I can see on-disk bitmap indexes doing well
there.  If you added the product number, or needed read/write, I think
GIN would win.  I just don't think we have enough deployments who need
what on-disk bitmap are best at.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

2015-10-16 Thread Alvaro Herrera

Bruce Momjian wrote:
> I have spend the past few days updating our TODO list, removing
> completed and now-unnecessary items:
> 
>   https://wiki.postgresql.org/wiki/Todo

Thanks.  We have "TodoDone" pages for items that were done in specific
releases, but only for 8.4, 9.0 and 9.1.  I guess it was too much work
to create those.  (See https://wiki.postgresql.org/wiki/Category:Todo ).

I added a link to the latest posted version of bitmap indexes by Abhijit
Menon-Sen, for completeness.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

2015-10-16 Thread Alvaro Herrera

Bruce Momjian wrote:

> Are you suggesting I remove those links?  It is kind of odd to have
> links to patches for features we don't want, or just keep it?

No, quite the contrary -- I think the links allow some other person
research the issue including the history of patches and discussion, and
decide for themselves whether the feature is right to be rejected.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

2015-10-16 Thread Alvaro Herrera

Bruce Momjian wrote:

> Probably the most controvertial change was to move on-disk bitmap
> indexes to the "not wanted" section, though I kept the links in case we
> change our minds.  I just can't see how they would be a win with GIN and
> in-memory bitmaps.

Yeah, I recall we discussed bitmap indexes a lot and we found there
wasn't a lot of room to use them because GIN is just too good, it seems.
Also, the patches that were developed had a number of issues.  Anyone
wanting to develop bitmap indexes would probably be better off starting
from scratch.

> (I don't think BRIN indexes help for on-disk bitmap use-cases, do
> they?)

No, they don't.  I expect BRIN to be very bad in a limited domain (which
is where bitmap indexes are supposed to shine), except under specific
conditions.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

On Fri, Oct 16, 2015 at 11:43:10AM -0300, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> 
> > Probably the most controvertial change was to move on-disk bitmap
> > indexes to the "not wanted" section, though I kept the links in case we
> > change our minds.  I just can't see how they would be a win with GIN and
> > in-memory bitmaps.
> 
> Yeah, I recall we discussed bitmap indexes a lot and we found there
> wasn't a lot of room to use them because GIN is just too good, it seems.
> Also, the patches that were developed had a number of issues.  Anyone
> wanting to develop bitmap indexes would probably be better off starting
> from scratch.

Yes, that was my conclusion too.  We have played with the on-disk bitmap
idea for a long time, but GIN has gotten very good in that time.

Are you suggesting I remove those links?  It is kind of odd to have
links to patches for features we don't want, or just keep it?

> > (I don't think BRIN indexes help for on-disk bitmap use-cases, do
> > they?)
> 
> No, they don't.  I expect BRIN to be very bad in a limited domain (which
> is where bitmap indexes are supposed to shine), except under specific
> conditions.

Yes, that was my conclusion too.  Thanks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] TODO list updates

2015-10-15 Thread Bruce Momjian

I have spend the past few days updating our TODO list, removing
completed and now-unnecessary items:

https://wiki.postgresql.org/wiki/Todo

I encourage others to also update the list to make it more accurate. 
Thanks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] TODO list updated for 9.3

2012-07-03 Thread Bruce Momjian

I have removed the completed 9.2 TODO items so people can start updating
the TODO list completed items for 9.3.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updated

2011-07-12 Thread Alvaro Herrera

Excerpts from Bruce Momjian's message of lun jul 11 18:58:35 -0400 2011:
 I have updated the TODO wiki to remove the 9.1-completed items:
 
 http://wiki.postgresql.org/wiki/Todo
 
 This will allow us to now mark 9.2-completed items.

I have created TodoDone91 from the items marked TodoDone on the rev
before your edit, for reference.  We now have those pages for 8.4
through 9.1.

(Sorry if this is a dupe -- I thought I had sent this yesterday but I
don't see it anywhere)

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updated

2011-07-12 Thread Bruce Momjian

Alvaro Herrera wrote:
 Excerpts from Bruce Momjian's message of lun jul 11 18:58:35 -0400 2011:
  I have updated the TODO wiki to remove the 9.1-completed items:
  
  http://wiki.postgresql.org/wiki/Todo
  
  This will allow us to now mark 9.2-completed items.
 
 I have created TodoDone91 from the items marked TodoDone on the rev
 before your edit, for reference.  We now have those pages for 8.4
 through 9.1.
 
 (Sorry if this is a dupe -- I thought I had sent this yesterday but I
 don't see it anywhere)

I thought about doing that but wasn't sure anyone wanted it so I skipped
it.  Thanks.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] TODO list updated

2011-07-11 Thread Bruce Momjian

I have updated the TODO wiki to remove the 9.1-completed items:

http://wiki.postgresql.org/wiki/Todo

This will allow us to now mark 9.2-completed items.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo: list of active channels

2011-07-11 Thread Bruce Momjian

Pavel Stehule wrote:
 Hello
 
 I use a LISTEN/NOTIFY. Now I have to check, if second application that
 creates channels is active. It should be simple with system view of
 active channels.

I think you want pg_listening_channels().

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] ToDo: list of active channels

2011-06-30 Thread Pavel Stehule

Hello

I use a LISTEN/NOTIFY. Now I have to check, if second application that
creates channels is active. It should be simple with system view of
active channels.

Regards

Pavel Stehule

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-21 Thread Bruce Momjian

Robert Haas wrote:
 On Thu, Jan 20, 2011 at 4:40 PM, Simone Aiken
 sai...@quietlycompetent.com wrote:
  After playing with this in benchmarks and researching the weird results I
  got I'm going to advise dropping the todo for now unless something happens
  to change how postgres handles clustering.
 
 I agree, let's remove it.
 
 That having been said, analyzing TODO items to figure out which ones
 are worthless is a useful thing to do, so please feel free to keep at
 it.

OK, removed.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-20 Thread Robert Haas

On Wed, Jan 19, 2011 at 4:27 PM, Simone Aiken sai...@ulfheim.net wrote:
 In my experience size increases related to documentation are almost always
 worth it.  So I'm prejudiced right out of the gate.  I was wondering if
 every pg_ table gets copied out to every database ..  if there is already a
 mechanism for not replicating all of them we could utilize views or
 re-writes rules to merge a single copy of catalog comments in a separate
 table with each deployed database's pg_descriptions.

All of them get copied, except for a handful of so-called shared
catalogs.  Changing that would be difficult.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-20 Thread Simone Aiken

After playing with this in benchmarks and researching the weird results I
got I'm going to advise dropping the todo for now unless something happens
to change how postgres handles clustering.  You guys probably already
grokked this so I am just recording it for the list archives.  
  

The primary factor here is that postgres doesn't maintain clustered indexes.
Clustering is a one-time operation that clusters the table at this current
point in time.  Basically, there really isn't any such thing in postgres as
a clustered index.  There is an operation - Cluster - which takes an index
and a table as input and re-orders the table according to the index.   But
it is borderline fiction to call the index used clustered because the next
row inserted will pop in at the end of the table instead of slipping into
the middle of the table per the desired ordering.  

All the pg_table cluster candidates are candidates because they have a row
per table column and we expect that a query will want to get several of
these rows at once.  These rows are naturally clustered because the scripts
that create them insert their information into the catalog contiguously.
When you create a catalog table the pg_attribute rows for its columns are
inserted together.  When you then create all its triggers they too are put
into pg_triggers one after the other.  So calling the Cluster operation
after dbinit doesn't help anything.

Over time table alterations can fragment this information.   If a user loads
a bunch of tables, then alters them over time the columns added later on
will have their metadata stored separately from the columns created
originally. 

Which gets us to the down and dirty of how the Cluster function works.  It
puts an access exclusive lock on the entire table - blocking all attempts to
read and write to the table - creates a copy of the table in the desired
order, drops the original, and renames the copy.  Doing this to a catalog
table that is relevant to queries pretty much brings everything else in the
database to a halt while the system table is locked up.  And the brute force
logic makes this time consuming even if the table is perfectly ordered
already.  Additionally, snapshots taken of the table during the Cluster
operation make the table appear to be empty which introduces the possibility
of system table corruption if transactions are run concurrently with a
Cluster operation.

So basically, the Cluster operation in its current form is not something you
want running automatically on a bunch of system table as it is currently
implemented.  It gives your system the hiccups.  You would only want to run
it manually during downtime.  And you can do that just as easily with or
without any preparation during dbinit.


Thanks everyone,

-Simone Aiken





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-20 Thread Robert Haas

On Thu, Jan 20, 2011 at 4:40 PM, Simone Aiken
sai...@quietlycompetent.com wrote:
 After playing with this in benchmarks and researching the weird results I
 got I'm going to advise dropping the todo for now unless something happens
 to change how postgres handles clustering.

I agree, let's remove it.

That having been said, analyzing TODO items to figure out which ones
are worthless is a useful thing to do, so please feel free to keep at
it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-19 Thread Simone Aiken


 Robert
 
 I think the first 
 thing to do would be to try to come up with a reproducible test case 
 where clustering the tables improves performance.  


On that note, is there any standard way you guys do benchmarks?  


 Bruce

I think CLUSTER is a win when you are looking up multiple rows in the same
table, either using a non-unique index or a range search.  What places do
such lookups?  Having them all in adjacent pages would be a win ---
single-row lookups are usually not.


Mostly the tables that track column level data.  Typically you will want to
grab rows for multiple columns for a given table at once so it would be
helpful to have them be contiguous on disk. 

I could design a benchmark to display this by building a thousand tables one
column at a time using 'alter add column' to scatter the catalog rows for
the tables across many blocks.  So they'll be a range with column 1 for each
table and column 2 for each table and column three for each table.  Then
fill a couple data tables with a lot of data and set some noise makers to
loop through them over and over with full table scans ... filling up cache
with unrelated data and hopefully ageing out the cache of the pg_tables.
Then do some benchmark index lookup queries to see the retrieval time before
and after clustering the pg_ctalog tables to record a difference.

If the criteria is doesn't hurt anything and helps a little I think this
passes.  Esp since clusters aren't maintained automatically so adding them
has no negative impact on insert or update.  It'd just be a nice thing to do
if you know it can be done that doesn't harm anyone who doesn't know.


-Simone Aiken





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-19 Thread Robert Haas

On Tue, Jan 18, 2011 at 6:49 PM, Simone Aiken
sai...@quietlycompetent.com wrote:
 Pages like this one have column comments for the system tables:

 http://www.psql.it/manuale/8.3/catalog-pg-attribute.html

Oh, I see.  I don't think we want to go there.  We'd need some kind of
system for keeping the two places in sync.  And there'd be no easy way
to upgrade the in-database descriptions when we upgraded to a newer
minor release, supposing they'd changed in the meantime.  And some of
the descriptions are quite long, so they wouldn't fit nicely in the
amount of space you typically have available when you run \d+.  And it
would enlarge the size of an empty database by however much was
required to store all those comments, which could be an issue for
PostgreSQL instances that have many small databases.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-19 Thread Tom Lane

Robert Haas robertmh...@gmail.com writes:
 On Tue, Jan 18, 2011 at 6:49 PM, Simone Aiken
 sai...@quietlycompetent.com wrote:
 Pages like this one have column comments for the system tables:
 
 http://www.psql.it/manuale/8.3/catalog-pg-attribute.html

 Oh, I see.  I don't think we want to go there.  We'd need some kind of
 system for keeping the two places in sync.

I seem to recall some muttering about teaching genbki to extract such
comments from the SGML sources or perhaps the C header files.  I tend to
agree though that it would be a lot more work than it's worth.  And as
you say, pg_description entries aren't free.

Which brings up another point though.  I have a personal TODO item to
make the comments for operator support functions more consistent:
http://archives.postgresql.org/message-id/21407.1287157...@sss.pgh.pa.us
Should we consider removing those comments altogether, instead?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-19 Thread Alvaro Herrera

Excerpts from Robert Haas's message of mié ene 19 15:25:00 -0300 2011:

 Oh, I see.  I don't think we want to go there.  We'd need some kind of
 system for keeping the two places in sync.

Maybe autogenerate both the .sgml and the postgres.description files
from a single source.

 And there'd be no easy way
 to upgrade the in-database descriptions when we upgraded to a newer
 minor release, supposing they'd changed in the meantime.

I wouldn't worry about this issue.  We don't do many catalog changes in
minor releases anyway.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-19 Thread Robert Haas

On Wed, Jan 19, 2011 at 2:26 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Tue, Jan 18, 2011 at 6:49 PM, Simone Aiken
 sai...@quietlycompetent.com wrote:
 Pages like this one have column comments for the system tables:

 http://www.psql.it/manuale/8.3/catalog-pg-attribute.html

 Oh, I see.  I don't think we want to go there.  We'd need some kind of
 system for keeping the two places in sync.

 I seem to recall some muttering about teaching genbki to extract such
 comments from the SGML sources or perhaps the C header files.  I tend to
 agree though that it would be a lot more work than it's worth.  And as
 you say, pg_description entries aren't free.

 Which brings up another point though.  I have a personal TODO item to
 make the comments for operator support functions more consistent:
 http://archives.postgresql.org/message-id/21407.1287157...@sss.pgh.pa.us
 Should we consider removing those comments altogether, instead?

I could go either way on that.  Most of those comments are pretty
short, aren't they?  How much storage are they really costing us?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-19 Thread Tom Lane

Robert Haas robertmh...@gmail.com writes:
 On Wed, Jan 19, 2011 at 2:26 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Which brings up another point though. I have a personal TODO item to
 make the comments for operator support functions more consistent:
 http://archives.postgresql.org/message-id/21407.1287157...@sss.pgh.pa.us
 Should we consider removing those comments altogether, instead?

 I could go either way on that.  Most of those comments are pretty
 short, aren't they?  How much storage are they really costing us?

Well, on my machine pg_description is about 210K (per database) as of
HEAD.  90% of its contents are pg_proc entries, though I have no good
fix on how much of that is for internal-use-only functions.  A very
rough estimate from counting pg_proc and pg_operator entries suggests
that the answer might be about a third.  So if we do what was said in
the above-cited thread, ie move existing comments to pg_operator and
add boilerplate ones to pg_proc, we probably would pay 100K for it.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-19 Thread Robert Haas

On Wed, Jan 19, 2011 at 3:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Wed, Jan 19, 2011 at 2:26 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Which brings up another point though. I have a personal TODO item to
 make the comments for operator support functions more consistent:
 http://archives.postgresql.org/message-id/21407.1287157...@sss.pgh.pa.us
 Should we consider removing those comments altogether, instead?

 I could go either way on that.  Most of those comments are pretty
 short, aren't they?  How much storage are they really costing us?

 Well, on my machine pg_description is about 210K (per database) as of
 HEAD.  90% of its contents are pg_proc entries, though I have no good
 fix on how much of that is for internal-use-only functions.  A very
 rough estimate from counting pg_proc and pg_operator entries suggests
 that the answer might be about a third.  So if we do what was said in
 the above-cited thread, ie move existing comments to pg_operator and
 add boilerplate ones to pg_proc, we probably would pay 100K for it.

I guess that's not enormously expensive, but it's not insignificant
either.  On my machine, a template database is 5.5MB.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-19 Thread Tom Lane

Robert Haas robertmh...@gmail.com writes:
 On Wed, Jan 19, 2011 at 3:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Well, on my machine pg_description is about 210K (per database) as of
 HEAD.  90% of its contents are pg_proc entries, though I have no good
 fix on how much of that is for internal-use-only functions.  A very
 rough estimate from counting pg_proc and pg_operator entries suggests
 that the answer might be about a third.  So if we do what was said in
 the above-cited thread, ie move existing comments to pg_operator and
 add boilerplate ones to pg_proc, we probably would pay 100K for it.

 I guess that's not enormously expensive, but it's not insignificant
 either.  On my machine, a template database is 5.5MB.

The implementation I was thinking about was to have initdb run a SQL
command that would do something like

INSERT INTO pg_description
  SELECT oprcode, 'pg_proc'::regclass, 0, 'implementation of ' || oprname
  FROM pg_operator
  WHERE theres-not-already-a-description-of-the-oprcode-function

So it would be minimal work to either provide or omit the boilerplate
descriptions.  I think we can postpone the decision till we have a
closer fix on the number of entries we're talking about.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-19 Thread Simone Aiken



I seem to recall some muttering about teaching genbki to extract such
comments from the SGML sources or perhaps the C header files.  I tend to
agree though that it would be a lot more work than it's worth.  And as you
say, pg_description entries aren't free.


I know I can't do all of the work, any submission requires review etc, but
it is worth it to me provided it does no harm to the codebase.

So the only outstanding question is the impact of increased size.

In my experience size increases related to documentation are almost always
worth it.  So I'm prejudiced right out of the gate.  I was wondering if
every pg_ table gets copied out to every database ..  if there is already a
mechanism for not replicating all of them we could utilize views or
re-writes rules to merge a single copy of catalog comments in a separate
table with each deployed database's pg_descriptions.  

If all catalog descriptions were handled this way it would actually decrease
the size of a deployed database ( by 210K? ) by absorbing the
pg_descriptions that are currently being duplicated.   Since users shouldn't
be messing with them anyway and they are purely for humans to refer to - not
computers to calculate explain plans with -  there shouldn't be anything
inherently wrong with moving static descriptions out of user space.  In
theory at least.  


-Simone Aiken




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-18 Thread Alvaro Herrera

Excerpts from Simone Aiken's message of dom ene 16 02:11:26 -0300 2011:
 
 Hello Postgres Hackers,
 
 In reference to this todo item about clustering system table indexes, 
   
 ( http://archives.postgresql.org/pgsql-hackers/2004-05/msg00989.php ) 
 I have been studying the system tables to see which would benefit  from 
 clustering.  I have some index suggestions and a question if you have a 
 moment.

Wow, this is really old stuff.  I don't know if this is really of any
benefit, given that these catalogs are loaded into syscaches anyway.
Furthermore, if you cluster at initdb time, they will soon lose the
ordering, given that updates move tuples around and inserts put them
anywhere.  So you'd need the catalogs to be re-clustered once in a
while, and I don't see how you'd do that (except by asking the user to
do it, which doesn't sound so great).

I think you need some more discussion on the operational details before
engaging in the bootstrap bison stuff (unless you just want to play with
Bison for educational purposes, of course, which is always a good thing
to do).

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-18 Thread Alvaro Herrera

Excerpts from Simone Aiken's message of dom ene 16 02:11:26 -0300 2011:
 
 Hello Postgres Hackers,

BTW whatever you do, don't start a new thread by replying to an existing
message and just changing the subject line.  It will mess up the
threading for some readers, and some might not even see your message.
Compose a fresh message instead.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-18 Thread Robert Haas

On Tue, Jan 18, 2011 at 8:35 AM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Simone Aiken's message of dom ene 16 02:11:26 -0300 2011:

 Hello Postgres Hackers,

 In reference to this todo item about clustering system table indexes,
 ( http://archives.postgresql.org/pgsql-hackers/2004-05/msg00989.php )
 I have been studying the system tables to see which would benefit  from
 clustering.  I have some index suggestions and a question if you have a
 moment.

 Wow, this is really old stuff.  I don't know if this is really of any
 benefit, given that these catalogs are loaded into syscaches anyway.
 Furthermore, if you cluster at initdb time, they will soon lose the
 ordering, given that updates move tuples around and inserts put them
 anywhere.  So you'd need the catalogs to be re-clustered once in a
 while, and I don't see how you'd do that (except by asking the user to
 do it, which doesn't sound so great).

The idea of the TODO seems to have been to set the default clustering
to something reasonable.  That doesn't necessarily seem like a bad
idea even if we can't automatically maintain the cluster order, but
there's some question in my mind whether we'd get any measurable
benefit from the clustering.  Even on a database with a gigantic
number of tables, it seems likely that the relevant system catalogs
will stay fully cached and, as you point out, the system caches will
further blunt the impact of any work in this area.  I think the first
thing to do would be to try to come up with a reproducible test case
where clustering the tables improves performance.  If we can't, that
might mean it's time to remove this TODO.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-18 Thread Simone Aiken


On Jan 18, 2011, at 6:35 AM, Alvaro Herrera wrote:
 
 
 Wow, this is really old stuff.  I don't know if this is really of any
 benefit, given that these catalogs are loaded into syscaches anyway.


The benefit is educational primarily.  I was looking for a todo list item
that would expose me to the system tables.  Learning the data model
of a new system is always step 1 for me.  So that one was perfect as
it would have me study and consider each one to determine if there
was any benefit from clustering on its initial load into cache.  


 Furthermore, if you cluster at initdb time, they will soon lose the
 ordering, given that updates move tuples around and inserts put them
 anywhere.  So you'd need the catalogs to be re-clustered once in a
 while, and I don't see how you'd do that (except by asking the user to
 do it, which doesn't sound so great).


I did discover that last night.  I'm used to databases that keep up their
clustering.  One that falls apart over time is distinctly strange.  And the
way you guys do your re-clustering logic is overkill if just a few rows
are out of place.  On the upside, a call to mass re-clustering goes
and updates all the clustered indexes in the system and that includes
these tables.  Will have to study auto-vacuum as well to consider that.


  (unless you just want to play with
 Bison for educational purposes, of course, which is always a good thing
 to do).

Pretty much, yeah.  


- Simone Aiken

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-18 Thread Simone Aiken



On Tue, Jan 18, 2011 at 8:35 AM, Alvaro Herrera alvhe...@commandprompt.com
wrote:
 Excerpts from Simone Aiken's message of dom ene 16 02:11:26 -0300 2011:

 Hello Postgres Hackers,

 In reference to this todo item about clustering system table indexes, 
 ( http://archives.postgresql.org/pgsql-hackers/2004-05/msg00989.php ) 

 Wow, this is really old stuff.  I don't know if this is really of any 

If we can't, that might mean it's time to remove this TODO.

When I'm learning a new system I like to first learn how to use it,
second learn its data model, third start seriously looking at the code.
So that Todo is ideal for my learning method.  

If there is something else that would also involve studying all the system
tables it would also be great.  For example, I noticed we have column 
level comments on the web but not in the database itself.  This seems
silly.  Why not have the comments in the database and have the web
query the tables of template databases for the given versions?

That way \d+ pg_tablename would provide instant gratification for users.
And we all like our gratification to be instant.  They could be worked into
The .h files though as inserts to pg_description they wouldn't provide an
excuse to learn bison.

I'm open to other suggestions as well.

-Simone Aiken



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-18 Thread Bruce Momjian

Robert Haas wrote:
 On Tue, Jan 18, 2011 at 8:35 AM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
  Excerpts from Simone Aiken's message of dom ene 16 02:11:26 -0300 2011:
 
  Hello Postgres Hackers,
 
  In reference to this todo item about clustering system table indexes,
  ( http://archives.postgresql.org/pgsql-hackers/2004-05/msg00989.php )
  I have been studying the system tables to see which would benefit ?from
  clustering. ?I have some index suggestions and a question if you have a
  moment.
 
  Wow, this is really old stuff. ?I don't know if this is really of any
  benefit, given that these catalogs are loaded into syscaches anyway.
  Furthermore, if you cluster at initdb time, they will soon lose the
  ordering, given that updates move tuples around and inserts put them
  anywhere. ?So you'd need the catalogs to be re-clustered once in a
  while, and I don't see how you'd do that (except by asking the user to
  do it, which doesn't sound so great).
 
 The idea of the TODO seems to have been to set the default clustering
 to something reasonable.  That doesn't necessarily seem like a bad
 idea even if we can't automatically maintain the cluster order, but
 there's some question in my mind whether we'd get any measurable
 benefit from the clustering.  Even on a database with a gigantic
 number of tables, it seems likely that the relevant system catalogs
 will stay fully cached and, as you point out, the system caches will
 further blunt the impact of any work in this area.  I think the first
 thing to do would be to try to come up with a reproducible test case
 where clustering the tables improves performance.  If we can't, that
 might mean it's time to remove this TODO.

I think CLUSTER is a win when you are looking up multiple rows in the
same table, either using a non-unique index or a range search.  What
places do such lookups?  Having them all in adjacent pages would be a
win --- single-row lookups are usually not.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-18 Thread Robert Haas

On Tue, Jan 18, 2011 at 12:16 PM, Simone Aiken sai...@ulfheim.net wrote:
 When I'm learning a new system I like to first learn how to use it,
 second learn its data model, third start seriously looking at the code.
 So that Todo is ideal for my learning method.

Sure - my point is just that we usually have as a criteria for any
performance related patch that it actually does improve performance.
So, we'd need a test case.

 If there is something else that would also involve studying all the system
 tables it would also be great.  For example, I noticed we have column
 level comments on the web but not in the database itself.  This seems
 silly.  Why not have the comments in the database and have the web
 query the tables of template databases for the given versions?

Uh... I don't know what this means.

 I'm open to other suggestions as well.

Here are a few TODO items that look relatively easy to me (they may
not actually be easy when you dig in, of course):

Clear table counters on TRUNCATE
Allow the clearing of cluster-level statistics
Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME
Allow ALTER TABLE to change constraint deferrability and actions

Unfortunately we don't have a lot of easy TODOs.  People keep doing
the ones we think up...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-18 Thread Simone Aiken

-Original Message-
From: Robert Haas [mailto:robertmh...@gmail.com] 
Sent: Tuesday, January 18, 2011 2:53 PM
To: Simone Aiken
Cc: Alvaro Herrera; pgsql-hackers
Subject: Re: [HACKERS] ToDo List Item - System Table Index Clustering

Sure - my point is just that we usually have as a criteria for any
performance related patch that it actually does improve performance.

Sorry wasn't arguing your point.   Conceding it actually. =)  
I wasn't explaining why I chose it anyway to contest your statements,
but as an invitation for you to point me towards something more useful 
that fit what I was looking for in a task. 

 Uh... I don't know what this means.

Pages like this one have column comments for the system tables:

http://www.psql.it/manuale/8.3/catalog-pg-attribute.html

But in my database when I look for comments they aren't there:

qcc= \d+ pg_attribute
  Table pg_catalog.pg_attribute
Column |   Type   | Modifiers | Description
---+--+---+-
 attrelid  | oid  | not null  |
 attname   | name | not null  |
 atttypid  | oid  | not null  |
 attstattarget | integer  | not null  |
 attlen| smallint | not null  |
 attnum| smallint | not null  |
 attndims  | integer  | not null  |
 attcacheoff   | integer  | not null  |
 atttypmod | integer  | not null  |
 attbyval  | boolean  | not null  |
 attstorage| char   | not null  |
 attalign  | char   | not null  |
 attnotnull| boolean  | not null  |
 atthasdef | boolean  | not null  |
 attisdropped  | boolean  | not null  |
 attislocal| boolean  | not null  |
 attinhcount   | integer  | not null  |

So I have to fire up a web browser and start googling to learn 
about the columns.  Putting them in pg_description would be 
more handy, no?

-Simone Aiken

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-17 Thread Simone Aiken


Followup on System Table Index clustering ToDo -

It looks like to implement this I need to do the following:

1 - Add statements to indexing.h to cluster the selected indexes.
A do-nothing define at the top to suppress warnings and then
lines below for perl to parse out.

#define DECLARE_CLUSTER_INDEX(table,index) ...
( add the defines under the index declarations ).

2 - Alter genbki.pl to produce the appropriate statements in 
postgres.bki when it reads the new lines in indexing.h.
Will hold them in memory until the end of the file so they
will come in after 'Build Indices' is called.

CLUSTER tablename USING indexname

3 - Initdb will pipe the commands in postgres.bki to the
postgres executable running in --boot mode. Code
will need to be added to bootparse.y to recognize
this new command and resolve it into a call to

cluster_rel( tabOID, indOID, 0, 0, -1, -1 );


Speak now before I learn Bison ... actually I should probably
learn Bison anyway.  After ProC other pre-compilation languages
can't be that bad.

Sound all right?

Thanks,

-Simone Aiken


On Jan 15, 2011, at 10:11 PM, Simone Aiken wrote:

 
 Hello Postgres Hackers,
 
 In reference to this todo item about clustering system table indexes, 
   
 ( http://archives.postgresql.org/pgsql-hackers/2004-05/msg00989.php ) 
 I have been studying the system tables to see which would benefit  from 
 clustering.  I have some index suggestions and a question if you have a 
 moment.
 
 Cluster Candidates:
 
   pg_attribute:  Make the existing index ( attrelid, attnum ) clustered 
 to 
   order it by table and column.
   
   pg_attrdef:  Existing index ( adrelid, adnum ) clustered to order it
   by table and column.
 
   pg_constraint:  Existing index ( conrelid ) clustered to get table 
   constraints contiguous.
 
   pg_depend: Existing Index (refclassid, refobjid, refobjsubid) clustered
   to so that when the referenced object is changed its dependencies 
   arevcontiguous.
 
   pg_description: Make the existing index ( Objoid, classoid, objsubid ) 
   clustered to order it by entity, catalog, and optional column.  
   * reversing the first two columns makes more sense to me ... 
   catalog, object, column or since object implies catalog ( 
 right? ) 
   just dispensing with catalog altogether, but that would mean 
   creating a new index.
   
   pg_shdependent: Existing index (refclassid, refobjid) clustered for 
   same reason as pg_depend.
 
   pg_statistic: Existing index (starelid, staattnum) clustered to order 
   it by table and column.
 
   pg_trigger:  Make the existing index ( tgrelid, tgname ) clustered to 
   order it by table then name getting all the triggers on a table 
 together.
 
 Maybe Cluster:
 
   pg_rewrite: Not sure about this one ... The existing index ( ev_class,
   rulename ) seems logical to cluster to get all the rewrite rules for a
   given table contiguous but in the db's available to me virtually every
   table only has one rewrite rule.  
 
   pg_auth_members:  We could order it by role or by member of
   that role.  Not sure which would be more valuable.
 
 
 Stupid newbie question:
 
 
   is there a way to make queries on the system tables show me what 
   is actually there when I'm poking around?  So for example:
 
   Select * from pg_type limit 1;
 
   tells me that the typoutput is 'boolout'.  An english string rather 
 than 
   a number.  So even though the documentation says that column
   maps to pg_proc.oid I can't then write:
 
   Select * from pg_proc where oid = 'boolout';
 
   It would be very helpful if I wasn't learning the system but since I
   am I'd like to turn it off for now.  Fewer layers of abstraction.
 
 
 Thanks,
 
 Simone Aiken
 
 303-956-7188
 Quietly Competent Consulting
 
 
 
 
 
 -- 
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] ToDo List Item - System Table Index Clustering

2011-01-16 Thread Simone Aiken


Hello Postgres Hackers,

In reference to this todo item about clustering system table indexes,   
( http://archives.postgresql.org/pgsql-hackers/2004-05/msg00989.php ) 
I have been studying the system tables to see which would benefit  from 
clustering.  I have some index suggestions and a question if you have a 
moment.

Cluster Candidates:

pg_attribute:  Make the existing index ( attrelid, attnum ) clustered 
to 
order it by table and column.

pg_attrdef:  Existing index ( adrelid, adnum ) clustered to order it
by table and column.

pg_constraint:  Existing index ( conrelid ) clustered to get table 
constraints contiguous.

pg_depend: Existing Index (refclassid, refobjid, refobjsubid) clustered
to so that when the referenced object is changed its dependencies 
arevcontiguous.

pg_description: Make the existing index ( Objoid, classoid, objsubid ) 
clustered to order it by entity, catalog, and optional column.  
* reversing the first two columns makes more sense to me ... 
catalog, object, column or since object implies catalog ( 
right? ) 
just dispensing with catalog altogether, but that would mean 
creating a new index.

pg_shdependent: Existing index (refclassid, refobjid) clustered for 
same reason as pg_depend.

pg_statistic: Existing index (starelid, staattnum) clustered to order 
it by table and column.

pg_trigger:  Make the existing index ( tgrelid, tgname ) clustered to 
order it by table then name getting all the triggers on a table 
together.

Maybe Cluster:

pg_rewrite: Not sure about this one ... The existing index ( ev_class,
rulename ) seems logical to cluster to get all the rewrite rules for a
given table contiguous but in the db's available to me virtually every
table only has one rewrite rule.  

pg_auth_members:  We could order it by role or by member of
that role.  Not sure which would be more valuable.


Stupid newbie question:


is there a way to make queries on the system tables show me what 
is actually there when I'm poking around?  So for example:

Select * from pg_type limit 1;

tells me that the typoutput is 'boolout'.  An english string rather 
than 
a number.  So even though the documentation says that column
maps to pg_proc.oid I can't then write:

Select * from pg_proc where oid = 'boolout';

It would be very helpful if I wasn't learning the system but since I
am I'd like to turn it off for now.  Fewer layers of abstraction.


Thanks,

Simone Aiken

303-956-7188
Quietly Competent Consulting





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-16 Thread Nicolas Barbier

2011/1/16 Simone Aiken sai...@ulfheim.net:

        is there a way to make queries on the system tables show me what
        is actually there when I'm poking around?  So for example:

                Select * from pg_type limit 1;

        tells me that the typoutput is 'boolout'.  An english string rather 
 than
        a number.  So even though the documentation says that column
        maps to pg_proc.oid I can't then write:

                Select * from pg_proc where oid = 'boolout';

Type type of typoutput is regproc, which is really an oid with a
different output function. To get the numeric value, do:

Select typoutput::oid from pg_type limit 1;

Nicolas

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-16 Thread Tom Lane

Nicolas Barbier nicolas.barb...@gmail.com writes:
 2011/1/16 Simone Aiken sai...@ulfheim.net:
... So even though the documentation says that column
maps to pg_proc.oid I can't then write:
Select * from pg_proc where oid = 'boolout';

 Type type of typoutput is regproc, which is really an oid with a
 different output function. To get the numeric value, do:
 Select typoutput::oid from pg_type limit 1;

Also, you *can* go back the other way.  It's very common to write

   Select * from pg_proc where oid = 'boolout'::regproc

rather than looking up the OID first.  There are similar pseudotypes for
relation and operator names; see Object Identifier Types in the
manual.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ToDo List Item - System Table Index Clustering

2011-01-16 Thread Simone Aiken



 Select typoutput::oid from pg_type limit 1;


 Also, you *can* go back the other way.  It's very common to write
 
   Select * from pg_proc where oid = 'boolout'::regproc
 
 rather than looking up the OID first.  


  see Object Identifier Types in the manual.


Many thanks to you both, that helps tremendously.   

- Simone Aiken



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

2010-03-31 Thread Bruce Momjian

Tom Lane wrote:
 Robert Haas robertmh...@gmail.com writes:
  In reading through the TODO list, I noticed a few things that I think
  are done, may be done, or may be partially done.  See below.
  Thoughts?  ...Robert
 
  Add missing operators for geometric data types
  - this is at least partly done.  not sure if it is entirely done.
 
 I think you are thinking of Teodor's point_ops patch, but what that
 did was add GIST index support for some existing geometric operators.
 This TODO item suffers from the all-too-common sin of being uselessly
 vague; it doesn't identify what operators it thinks are missing.

The full TODO item text is:

Add missing operators for geometric data types

Some geometric types do not have the full suite of geometric
operators, e.g. box @ point

* point_ops for GiST 

but we now have the @ operator mentioned:

 pg_catalog | @   | box   | point  | boolean | 
contains?

so I have removed this TODO item.

  Add UNIQUE capability to non-btree indexes
  - This is one application of exclusion constraints, so I'd say this is done.
 
 I think exclusion constraints address this as much as it needs to be
 addressed for GIST and GIN, neither of which have equality as a core
 concept.  Hash, however, does have that.  So I'd vote for narrowing the
 entry to support UNIQUE natively in hash indexes and moving it to the
 hash-index section.

Agreed, moved.

  Clean up VACUUM FULL's klugy transaction management
  - I think this is moot with the new cluster-based VF.
 
 Right, that one's either done or no longer relevant depending on how you
 want to look at it.

Agreed, removed.

 There is another TODO item that I was just struck by while reading your
 response to Joseph Adams:
 
   Fix system views like pg_stat_all_tables to use set-returning
   functions, rather than views of per-column functions
 
 What is the point of this proposal?  The reason for the per-column function
 implementation was to allow people to slice and dice the underlying data
 their own way.  Changing to monolithic SRFs would make that harder, and
 I don't see that it's buying anything especially useful.  I'd vote for
 taking this off unless someone can explain why it's an improvement.

Agreed, removed.  I wasn't aware why we implemented this as slices.  I
now assume it was for performance reasons.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

2010-03-31 Thread Bruce Momjian

Robert Haas wrote:
 In reading through the TODO list, I noticed a few things that I think
 are done, may be done, or may be partially done.  See below.
 Thoughts?  ...Robert
 
 Add missing operators for geometric data types
 - this is at least partly done.  not sure if it is entirely done.
 
 Add OR REPLACE to CREATE LANGUAGE
 - Done.

TODO updated.

 Add PQescapeIdentifierConn()
 - Done, as PQescapeIdentifier().

TODO updated.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

2010-03-31 Thread Hitoshi Harada

2010/3/27 Tom Lane t...@sss.pgh.pa.us:
 Robert Haas robertmh...@gmail.com writes:
 In reading through the TODO list, I noticed a few things that I think
 are done, may be done, or may be partially done.  See below.
 Thoughts?  ...Robert

 Implement full support for window framing clauses.
 - Not sure if we made any progress on this or not.

 We made some progress for 9.0, but there's still a lot of unimplemented
 territory.

TODO updated.



-- 
Hitoshi Harada

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] TODO list updates

2010-03-26 Thread Robert Haas

In reading through the TODO list, I noticed a few things that I think
are done, may be done, or may be partially done.  See below.
Thoughts?  ...Robert

Add missing operators for geometric data types
- this is at least partly done.  not sure if it is entirely done.

Add OR REPLACE to CREATE LANGUAGE
- Done.

Implement full support for window framing clauses.
- Not sure if we made any progress on this or not.

Add PQescapeIdentifierConn()
- Done, as PQescapeIdentifier().

Add UNIQUE capability to non-btree indexes
- This is one application of exclusion constraints, so I'd say this is done.

[GIN] Support empty indexed values (such as zero-element arrays) properly
- I think this might be done but I'm not sure.

Clean up VACUUM FULL's klugy transaction management
- I think this is moot with the new cluster-based VF.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

2010-03-26 Thread Tom Lane

Robert Haas robertmh...@gmail.com writes:
 In reading through the TODO list, I noticed a few things that I think
 are done, may be done, or may be partially done.  See below.
 Thoughts?  ...Robert

 Add missing operators for geometric data types
 - this is at least partly done.  not sure if it is entirely done.

I think you are thinking of Teodor's point_ops patch, but what that
did was add GIST index support for some existing geometric operators.
This TODO item suffers from the all-too-common sin of being uselessly
vague; it doesn't identify what operators it thinks are missing.

 Implement full support for window framing clauses.
 - Not sure if we made any progress on this or not.

We made some progress for 9.0, but there's still a lot of unimplemented
territory.

 Add UNIQUE capability to non-btree indexes
 - This is one application of exclusion constraints, so I'd say this is done.

I think exclusion constraints address this as much as it needs to be
addressed for GIST and GIN, neither of which have equality as a core
concept.  Hash, however, does have that.  So I'd vote for narrowing the
entry to support UNIQUE natively in hash indexes and moving it to the
hash-index section.

 [GIN] Support empty indexed values (such as zero-element arrays) properly
 - I think this might be done but I'm not sure.

Not done, not even started.  See the referenced discussions, or the
limitations acknowledged here:
http://developer.postgresql.org/pgdocs/postgres/gin-limit.html

 Clean up VACUUM FULL's klugy transaction management
 - I think this is moot with the new cluster-based VF.

Right, that one's either done or no longer relevant depending on how you
want to look at it.


There is another TODO item that I was just struck by while reading your
response to Joseph Adams:

Fix system views like pg_stat_all_tables to use set-returning
functions, rather than views of per-column functions

What is the point of this proposal?  The reason for the per-column function
implementation was to allow people to slice and dice the underlying data
their own way.  Changing to monolithic SRFs would make that harder, and
I don't see that it's buying anything especially useful.  I'd vote for
taking this off unless someone can explain why it's an improvement.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

2010-03-26 Thread Robert Haas

On Fri, Mar 26, 2010 at 12:57 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 In reading through the TODO list, I noticed a few things that I think
 are done, may be done, or may be partially done.  See below.
 Thoughts?  ...Robert

 Add missing operators for geometric data types
 - this is at least partly done.  not sure if it is entirely done.

 I think you are thinking of Teodor's point_ops patch, but what that
 did was add GIST index support for some existing geometric operators.
 This TODO item suffers from the all-too-common sin of being uselessly
 vague; it doesn't identify what operators it thinks are missing.

Well, I'm thinking of that because that TODO item has a reference to
the point_ops patch.  I think we should remove this item and replace
it with any more specific suggestions someone cares to raise.

 Implement full support for window framing clauses.
 - Not sure if we made any progress on this or not.

 We made some progress for 9.0, but there's still a lot of unimplemented
 territory.

Perhaps it could be made more specific.

 Add UNIQUE capability to non-btree indexes
 - This is one application of exclusion constraints, so I'd say this is done.

 I think exclusion constraints address this as much as it needs to be
 addressed for GIST and GIN, neither of which have equality as a core
 concept.  Hash, however, does have that.  So I'd vote for narrowing the
 entry to support UNIQUE natively in hash indexes and moving it to the
 hash-index section.

As far as I know, exclusion constraints would work with hash opclasses
also.  Do you think there's an advantage to having something that is
hash-specific a la the btree-specific stuff we already have?

 [GIN] Support empty indexed values (such as zero-element arrays) properly
 - I think this might be done but I'm not sure.

 Not done, not even started.  See the referenced discussions, or the
 limitations acknowledged here:
 http://developer.postgresql.org/pgdocs/postgres/gin-limit.html

OK.

 Clean up VACUUM FULL's klugy transaction management
 - I think this is moot with the new cluster-based VF.

 Right, that one's either done or no longer relevant depending on how you
 want to look at it.

OK.

 There is another TODO item that I was just struck by while reading your
 response to Joseph Adams:

        Fix system views like pg_stat_all_tables to use set-returning
        functions, rather than views of per-column functions

 What is the point of this proposal?  The reason for the per-column function
 implementation was to allow people to slice and dice the underlying data
 their own way.  Changing to monolithic SRFs would make that harder, and
 I don't see that it's buying anything especially useful.  I'd vote for
 taking this off unless someone can explain why it's an improvement.

I assumed it would be faster and less likely to return inconsistent
results.  If that's not the case then I agree.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list updates

2010-03-26 Thread Tom Lane

Robert Haas robertmh...@gmail.com writes:
 As far as I know, exclusion constraints would work with hash opclasses
 also.

Yeah, they do.

 Do you think there's an advantage to having something that is
 hash-specific a la the btree-specific stuff we already have?

Sure: it'll be more efficient because of not needing an AFTER trigger.
Now of course this assumes that hash indexes ever grow up enough to play
in the big leagues, which is a pretty dubious proposition; but having
real unique-constraint support is one of the things people would want
from them if they ever did get to be credible production choices.
So this isn't something I'd put at the front of the TODO list for hash
indexes, but it ought to be in there somewhere.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list request: FK to unique expression indexes

2008-11-20 Thread Bruce Momjian

David E. Wheeler wrote:
 On Nov 19, 2008, at 9:12 AM, Josh Berkus wrote:
 
  Folks,
 
  Since it's too late to look at this for 8.4, can the following go on  
  the TODO list?
 
  Referential Integrity
 
  [] Allow creation of FKs targeting unique expression indexes on the  
  referenced table.  Syntax: REFERENCES reftable ( ( column  
  expression ) )
 
  Reason: current FK rules do not allow creating FKs to columns which  
  are defined as, for example, unique(lower(column)).  This forces  
  users to either abandon RI for that table, to store duplicate data,  
  or create superfluous indexes.
 
  Hmmm ... I suppose the above would require enabling expression  
  indexes for PKs as well, no?
 
 In 8.4 you should be able to get around this particular example using  
 citext.

Yes, good idea on citext.  

Allowing foreign keys to point to expression indexes seems to open a can
of worms and I am not sure there is enough demand to warrant it.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list request: FK to unique expression indexes

2008-11-20 Thread Robert Haas

 Allowing foreign keys to point to expression indexes seems to open a can
 of worms and I am not sure there is enough demand to warrant it.

It does open a can of worms.  I've often wanting something related,
which is the ability to make a foreign key references a PARTIAL index.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] TODO list request: FK to unique expression indexes

2008-11-19 Thread Josh Berkus


Folks,

Since it's too late to look at this for 8.4, can the following go on the 
TODO list?


Referential Integrity

[] Allow creation of FKs targeting unique expression indexes on the 
referenced table.  Syntax: REFERENCES reftable ( ( column expression ) )


Reason: current FK rules do not allow creating FKs to columns which are 
defined as, for example, unique(lower(column)).  This forces users to 
either abandon RI for that table, to store duplicate data, or create 
superfluous indexes.


Hmmm ... I suppose the above would require enabling expression indexes 
for PKs as well, no?


--Josh Berkus

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO list request: FK to unique expression indexes

2008-11-19 Thread David E. Wheeler


On Nov 19, 2008, at 9:12 AM, Josh Berkus wrote:


Folks,

Since it's too late to look at this for 8.4, can the following go on  
the TODO list?


Referential Integrity

[] Allow creation of FKs targeting unique expression indexes on the  
referenced table.  Syntax: REFERENCES reftable ( ( column  
expression ) )


Reason: current FK rules do not allow creating FKs to columns which  
are defined as, for example, unique(lower(column)).  This forces  
users to either abandon RI for that table, to store duplicate data,  
or create superfluous indexes.


Hmmm ... I suppose the above would require enabling expression  
indexes for PKs as well, no?


In 8.4 you should be able to get around this particular example using  
citext.


Best,

David


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)

2008-03-12 Thread Magnus Hagander

On Wed, Mar 12, 2008 at 10:27:06AM -0400, Bruce Momjian wrote:
 Alvaro Herrera wrote:
  Bruce Momjian wrote:
   Magnus Hagander wrote:
  
It's not. And I personally don't think it would be a problem.
But I think it'll be a lot easier to sell to those who prefer
textfiles in cvs (hello bruce!) if we can.
   
   I don't care who edits it myself, though I can say I get perhaps one
   patch a year to the TODO list file --- usually I just an email saying
   remove that item or something.
  
  Personally I've wished to enter something in the TODO list myself but
  refrained because it was your area.  Having a derived HTML page
  doesn't make me feel any better -- how should I generate it to ensure
  that my output is equal to yours?
 
 If you change the text file, I will see the CVS update and update the
 HTML --- I will never lose a change because my CVS sees your changes.

That seems like a lot of extra work that should be unnecessary.

I asked before for general reactions, so I will now turn that into a formal 
proposal:

Let's move the TODO list to the wiki. Bruce still retains ownership of it
and will certainly be doing most of the editing. But people who work on
individual items can add/remove items and details as needed directly on the
wiki as necessary. 

Those who still need daily updates can easily grab an RSS feed off the
wiki, or use the watch feature (disclaimer: I haven't used the watch
feature myself, but I'm told it should work).

Can we get a soundoff on this? Good idea, bad idea?


(if we decide to do it, we'll have to migrate what's there now, of course.
But I'm sure we can find volounteer(s) to help with that so Bruce doesn't
have to do all the work.)

//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)

2008-03-12 Thread Magnus Hagander

On Wed, Mar 12, 2008 at 11:03:13AM -0400, Bruce Momjian wrote:
Magnus Hagander wrote:
If you change the text file, I will see the CVS update and update the
HTML --- I will never lose a change because my CVS sees your changes.

That seems like a lot of extra work that should be unnecessary.

I asked before for general reactions, so I will now turn that into a formal
proposal:

Let's move the TODO list to the wiki. Bruce still retains ownership of it
and will certainly be doing most of the editing. But people who work on
individual items can add/remove items and details as needed directly on the
wiki as necessary.

Those who still need daily updates can easily grab an RSS feed off the
wiki, or use the watch feature (disclaimer: I haven't used the watch
feature myself, but I'm told it should work).

Can we get a soundoff on this? Good idea, bad idea?

(if we decide to do it, we'll have to migrate what's there now, of course.
But I'm sure we can find volounteer(s) to help with that so Bruce doesn't
have to do all the work.)

We need it to do a few things:

o We need to be able to pull a text and HTML copies for tarballs

That should be doable without too much work. I would, however, like to open
a second discussion on wether we actually *need* it. But that's a different
discussion than this - if we do need it, we can make that happen.

o Edits have to be quick and easy

That's the whole idea of a wiki. I don't personally love the markup
language, but for the simple kind of stuff that the TODO list deals with
(markup-wise), it's very easy to use.

o I have to be able to make new sections, and move existing
items around and between sections

Trivial - again, what a wiki does best. Look at for example
http://developer.postgresql.org/index.php/Replication%2C_Clustering%2C_and_Connection_Pooling
for a page taht has a bunch of different sections. It uses tables in some
and lists in some etc, but it shold give you an idea of how simple it is to
craete sections.

o I need to be able to add URLs for items

You maen links to external sites? Also trivial. Again, what a wiki is
designed for more or less.

o I need subsections and sub-subsections

Should be doable either as wiki sections or as bullet-lists.

//Magnus

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)

2008-03-12 Thread Tom Lane

Magnus Hagander [EMAIL PROTECTED] writes:
 On Wed, Mar 12, 2008 at 11:03:13AM -0400, Bruce Momjian wrote:
 We need it to do a few things:

Actually, the part of the current process that a wiki would fail to
reproduce is the emails that Bruce sends out about TODO changes.
Do we still want those, and if so what would we do about it?

Personally I think the current mails are overly verbose --- in 
particular, quoting (one of the) referenced email messages is
good for nothing except archive-bloat.  However going over to
nothing at all might be too far in the other direction.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)

2008-03-12 Thread Magnus Hagander

On Wed, Mar 12, 2008 at 11:36:52AM -0400, Tom Lane wrote:
 Magnus Hagander [EMAIL PROTECTED] writes:
  On Wed, Mar 12, 2008 at 11:03:13AM -0400, Bruce Momjian wrote:
  We need it to do a few things:
 
 Actually, the part of the current process that a wiki would fail to
 reproduce is the emails that Bruce sends out about TODO changes.
 Do we still want those, and if so what would we do about it?

Bruce still replies to all emails saying Added to TODO. So you get *two*
copies of it every time this time - once on -hackers, -general or whatever,
and one on -committers.

You will also be able to subscruibe to updates on the wiki. The preferrable
way (at least for me, and I'm sure for a lot of folks) using RSS, but also
using email.

//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)

2008-03-12 Thread Tom Lane

Bruce Momjian [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 Actually, the part of the current process that a wiki would fail to
 reproduce is the emails that Bruce sends out about TODO changes.
 Do we still want those, and if so what would we do about it?

 Magnus said you can subscribe to a changes email, or get an RSS feed of
 changes.

Right, so people who were specifically interested in following TODO
changes could still track it.  But the current system informs the
original submitter of a problem that it's been put on TODO, and that
wouldn't happen anymore.  Do we care?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)

2008-03-12 Thread Bruce Momjian

Tom Lane wrote:
 Magnus Hagander [EMAIL PROTECTED] writes:
  On Wed, Mar 12, 2008 at 11:03:13AM -0400, Bruce Momjian wrote:
  We need it to do a few things:
 
 Actually, the part of the current process that a wiki would fail to
 reproduce is the emails that Bruce sends out about TODO changes.
 Do we still want those, and if so what would we do about it?

Magnus said you can subscribe to a changes email, or get an RSS feed of
changes.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)

2008-03-12 Thread Bruce Momjian

Tom Lane wrote:
 Bruce Momjian [EMAIL PROTECTED] writes:
  Tom Lane wrote:
  Actually, the part of the current process that a wiki would fail to
  reproduce is the emails that Bruce sends out about TODO changes.
  Do we still want those, and if so what would we do about it?
 
  Magnus said you can subscribe to a changes email, or get an RSS feed of
  changes.
 
 Right, so people who were specifically interested in following TODO
 changes could still track it.  But the current system informs the
 original submitter of a problem that it's been put on TODO, and that
 wouldn't happen anymore.  Do we care?

I would still send out an email for TODO additions based on the original
email.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] TODO list has removed developer names

2007-01-24 Thread Bruce Momjian

I have removed the developer names from the bottom of the TODO list now
that URLs are used to reference discussions.  The URLs are much more
accurate than putting names on items.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] todo list management

2006-12-18 Thread Simon Riggs

On Sun, 2006-12-17 at 10:56 +0100, Lukas Kahwe Smith wrote:

 PostgreSQL 8.2 is out of the door. Unfortunately the plans for a more 
 detailed todo list have not come into reality yet to assist in for the 
 next 8.3 release. A couple people have replied to my earlier request to 
 form a little team willing to work on this, but unfortunately people 
 seem to have time constraints that prohibit them from working on this.
 
 I am still willing to spend time on being the person secretary of 
 people, but I do not know enough about the internals of PostgreSQL to do 
 this on my own, let alone start an initial list like I did for the PHP 
 project.
 
 My goal is to for now cover the tricky long running todo's

I'd start at the easy end and see what value you can bring. Don't wait
to have someone define roles and tasks for you, cos you'll wait a long
time.

All contributions are appreciated. It takes time to get into things.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[HACKERS] todo list management

2006-12-17 Thread Lukas Kahwe Smith


Hi,

PostgreSQL 8.2 is out of the door. Unfortunately the plans for a more 
detailed todo list have not come into reality yet to assist in for the 
next 8.3 release. A couple people have replied to my earlier request to 
form a little team willing to work on this, but unfortunately people 
seem to have time constraints that prohibit them from working on this.


I am still willing to spend time on being the person secretary of 
people, but I do not know enough about the internals of PostgreSQL to do 
this on my own, let alone start an initial list like I did for the PHP 
project.


My goal is to for now cover the tricky long running todo's, document 
their progress, the people involved as well as any discussions and more 
importantly their conclusions. This should make it easier for other 
people to help on these todo's, or even take over if for some reason the 
original people do not have time to complete the todo themselves. This 
should also help people like Bruce, because they can get an idea about 
the progress without necessarily having to track down the people 
involved and it should also make the progress more transparent to end users.


This is essentially what was started with the developers wiki already 
[1]. It seems the wiki is already seeing good use for the XML support 
and query progress indicator.


Again, I am here and willing to help. I do not want to hassle this list 
with a lengthy thread, so hopefully the result will just be someone 
pushing me into the right directions.


regards,
Lukas

[1] http://developer.postgresql.org/

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] TODO list comments


Great updates!  Let me comment on each one.

 I made a pass over the TODO list to see what was out of date.
 
  * Allow administrators to safely terminate individual sessions either
via an SQL function or SIGTERM 
  
Currently SIGTERM of a backend can lead to lock table corruption.
 
 This comment may be out of date.  Suggest
 
   Lock table corruption following SIGTERM of an individual backend
   has been reported in 8.0.  A possible cause is fixed in 8.1, but
   it is unknown whether other trouble spots exist.  This item is
   mainly a matter of doing adequate testing rather than of writing
   any new code.

Done.

 
  o Allow postgresql.conf values to be set so they can not be changed
by the user
 
 Is that really a good idea?  The ones that are unsafe are restricted already.

Well, a typical case would be log_statement, but I see that is
super-user now.  I guess we are OK, removed.  If we get more problems,
we can re-add something later.

  * %Remove Money type, add money formatting for decimal type
 
 There's a fair-size contingent that doesn't want Money removed
 completely, but just reimplemented as an I/O wrapper around type
 numeric.  Maybe that's even what you mean by the TODO item, but
 it's not clear.  Please at least mention the alternative.

Updated:

* Improve the MONEY data type

  Change the MONEY data type to use DECIMAL internally, with special
  locale-aware output formatting.


  o %Allow MIN()/MAX() on arrays
 
 This is done.

OK.

  o Modify array literal representation to handle array index lower bound
of other than one
 
 This too.

OK.

 
  o Add security checking for large objects
  
Currently large objects entries do not have owners. Permissions can
only be set at the pg_largeobject table level.
 
 This comment is wrong: trying to set the permissions on pg_largeobject
 would have no effect whatsoever on the lo_xxx functions, so there is not
 even a partial solution available now.

Oh, comment removed.

  o Auto-delete large objects when referencing row is deleted
 
 This should note that contrib/lo already offers a solution.

Done.

  * %Have views on temporary tables exist in the temporary namespace
  * Allow temporary views on non-temporary tables
 
 Both of these are done in 8.1.

OK.

  * %Allow RULE recompilation
 
 Eh?  Perhaps you meant automatically regenerate cached plans when
 needed, in which case it's redundant with the Dependency Checking
 entries.  Whatever it means, this doesn't seem a particularly simple
 item.

Agreed, updated to:

* Allow VIEW/RULE recompilation when the underlying tables change

  * %Allow TRUNCATE ... CASCADE/RESTRICT
 
 Huh?  What would that do?

I assume it is just like DELETE CASCADE, but it TRUNCATES rather than
DELETE.  Description added.

  * Make row-wise comparisons work per SQL spec
 
 This could probably be marked as a % item.

Done.

  o Currently the system uses the operating system COPY command to
create a new database. Add ON COMMIT capability to CREATE TABLE AS
SELECT
 
 This seems a bit garbled, and anyway the first part is done.

Yep, garbled.  I have removed the first part.

  o %Add ALTER DOMAIN TYPE
 
 To do what, exactly?  This is unclear.

I assume it would allow the underlying data type to be changed.  Updated
text:

o Add ALTER DOMAIN to modify the underlying data type

  o -Allow objects to be moved to different schemas
 
 This is only partly done --- the 8.1 patch didn't cover all object types.

Updated to:

o Add missing object types for ALTER ... SET SCHEMA


  o %Disallow dropping of an inherited constraint
  ...
  o %Prevent child tables from altering constraints like CHECK that were
inherited from the parent table
 
 These seem to be duplicates, or at least in need of merging.

Merged and updated:

o %Prevent child tables from altering or dropping constraints
  like CHECK that were inherited from the parent table

  o Handle references to temporary tables that are created, destroyed,
then recreated during a session, and EXECUTE is not used
  
This requires the cached PL/PgSQL byte code to be invalidated when
an object referenced in the function is changed.
 
 This is redundant with the Dependency Checking item about regenerating
 cached plans.

Removed and description added to dependency item:

* Track dependencies in function bodies and recompile/invalidate

  This is particularly important for references to temporary tables
  in PL/PgSQL because PL/PgSQL caches query plans.  The only workaround
  in PL/PgSQL is to use EXECUTE.

  o Add table function support to pltcl, plperl, plpython?
 
 Isn't this done for plperl?

Right, plperl removed.

  o Allow PL/pgSQL to name columns by ordinal position, e.g. rec.(3)
 
 This doesn't

Re: [HACKERS] TODO list comments

Alvaro Herrera wrote:
 On Thu, Aug 25, 2005 at 01:53:32PM -, Greg Sabino Mullane wrote:
 
  Tom Lane asked:
  
 o Improve psql's handling of multi-line queries
  
   Uh, what's wrong with it?  This item seems far too vague.
  
  I think perhaps this means adding multi-line support to
  the tab-completion? Only thing I can think of, cause other
  than that, multi-line queries work just fine.
 
 The saved history is also not cool about multiline queries.  If you
 enter them interactively (or by pasting), they are entered as several
 entries.  If you edit them with \e, they are entered as a single unit.

TODO updated:

o Improve psql's handling of multi-line queries

  Currently, while \e saves a single query as one entry, interactive
  queries are saved one line at a time.  Ideally all queries
  whould be saved like \e does.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] TODO list comments

Hannu Krosing wrote:
 On K, 2005-08-24 at 21:58 -0400, Tom Lane wrote:
   * %Allow TRUNCATE ... CASCADE/RESTRICT
  
  Huh?  What would that do?
 
 Maybe this was meant truncating of tables with dependent foreign keys ?
 
 AFAIR this was solved by allowing truncating several tables in one
 command even if they have FK relationships between themselves.

Yes, but I can imagine allowing a CASCADE behavior as well.

  This is only partly done --- the 8.1 patch didn't cover all object types.
  
 o %Disallow dropping of an inherited constraint
   ...
 o %Prevent child tables from altering constraints like CHECK that were
   inherited from the parent table
  
  These seem to be duplicates, or at least in need of merging.
 
 It should probably mention about weird inheritance behaviour of CREATE
 CONSTRAINT ON ONLY tablename - it is not propagated to existing child
 tables, but is inherited when creating new ones.

I am not sure on that one because the table does have the constraint at
the time the child is created.  Comments?

 Also, I don't think this should be done at all, at least not before we
 have proper partitioned table support ready. I could live with it
 creating a warning about not being future-compatible.

Right, TODO item removed.

 o Handle references to temporary tables that are created, destroyed,
   then recreated during a session, and EXECUTE is not used
   
   This requires the cached PL/PgSQL byte code to be invalidated when
   an object referenced in the function is changed.
  
  This is redundant with the Dependency Checking item about regenerating
  cached plans.
 
 Or maybe not completely, depending on how you do it. 

Well, I beefed up the item:

* Track dependencies in function bodies and recompile/invalidate

  This is particularly important for references to temporary tables
  in PL/PgSQL because PL/PgSQL caches query plans.  The only workaround
  in PL/PgSQL is to use EXECUTE.

 If temp table itself is created inside the same pl/pgsql function, then
 there could still be a way to do the planning/optimising only once and
 then substitute temp table oids when running the function. 
 
 The table structure in this case is quaranteed to be the same during
 each run of the function, it's just that the temp table and index oids
 should be treated as local variables.

Interesting approach but is it worth the added complexity?  One issue
this does bring up is that functions themselves might invalidate their
own cached query plan by dropping a table and receating it.  In those
cases, your solution would be the only valid one, or throw an error.

I added some more text:

* Track dependencies in function bodies and recompile/invalidate

  This is particularly important for references to temporary tables
  in PL/PgSQL because PL/PgSQL caches query plans.  The only workaround
  in PL/PgSQL is to use EXECUTE.  One complexity is that a function
  might itself drop and recreate dependent tables, causing it to
  invalidate its own query plan.

 Done this way, it gives real benefits in terms of cached query plans,
 instead of just preventing newcomers from shooting themselves in foot by
 not using EXECUTE.
 
   * Improve speed with indexes
   
 For large table adjustements during vacuum, it is faster to reindex
 rather than update the index.
  
  This applies only to VACUUM FULL, so it probably needs to be reworded.
 
 In case we implement concurrent/non-blocking CREATE INDEX at some point,
 this might be a good idea for lazy VACUUM as well.

Perhaps.

 And it may make more sense to do CLUSTER instead of VACUUM FULL in at
 least some of these cases.

Cluster modifies the heap while reindex does not.  This makes cluster a
much heavier operation.

 (btw. CLUSTER seems to be another function which my concurrent vacuuming
 patch should be extended to cover, at least on client side, like
 CREATE INDEX)

Not sure.

   * Auto-vacuum
   
 o %Suggest VACUUM FULL if a table is nearly empty
  
  It seems like a fairly bad idea for auto-vacuum to do a VACUUM FULL
  ever, given the locking effects.  And how is a background daemon going
  to suggest anything?  It could write to the postmaster log but it's
  entirely likely the user would never notice.
 
 With current implementations of commands, doing (some equivalent of)
 CLUSTER here seems a better idea than VACUUM FULL, as it also un-bloats
 indexes. Not sure of of transactional behaviour though.

Not sure, CLUSTER is still heavier.  That doesn't mean it shouldn't be
used, but the administrator should automatically consider CLUSTER in
place of VACUUM FULL for large updates.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square,

Re: [HACKERS] TODO list comments

Jim C. Nasby wrote:
 I *think* this is reffering to how pg_dump makes some assumptions about
 what things are system objects.
 
 http://archives.postgresql.org/pgsql-committers/2005-08/msg00203.php
 doesn't help a heck of a lot...
 
 Can we add an interface to the TODO list that contains search links back
 to the mailing lists?

Yes, that would be nice, though some times the threads are pretty long
and I try to digest the agreed-upon solution.  Where would we put the
URLs?  In the TODO file?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] TODO list comments

Andrew Dunstan wrote:
 
 
 Tom Lane wrote:
 
 Or perhaps use a different separator:
 
 junk=# select * from xyz;
  id |name|  address  | del_addr 
 ++---+--
   1 | Joe Bloggs | 1 Hindhead Villas,| 2 The Laurels,
 ++ Newport,  + Swinkley,
 ++ Gwent + XX3 5CX
 (1 row)
   
 
 
 That's a terrific idea, and, incidentally, just the sort of project that 
 might well suit a beginning hacker, since the code is pretty isolated.
 
 Not sure how hard this would be to program, or what sort of overhead it
 might impose to check for the case.  My recollection is that psql's
 table-layout code is pretty slow and ugly already ...
 
 
   
 
 
 If people want speed they shouldn't use psql as a client anyway. I don't 
 see this as much of an obstacle.

Added to TODO:

o Allow multi-line column values to align in the proper columns

  If the second output column value is 'a\nb', the 'b' should appear
  in the second display column, rather than the first column as it
  does now.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] TODO list comments

2005-08-26 Thread Jim C. Nasby

On Fri, Aug 26, 2005 at 03:44:18PM -0400, Bruce Momjian wrote:
 Jim C. Nasby wrote:
  I *think* this is reffering to how pg_dump makes some assumptions about
  what things are system objects.
  
  http://archives.postgresql.org/pgsql-committers/2005-08/msg00203.php
  doesn't help a heck of a lot...
  
  Can we add an interface to the TODO list that contains search links back
  to the mailing lists?
 
 Yes, that would be nice, though some times the threads are pretty long
 and I try to digest the agreed-upon solution.  Where would we put the
 URLs?  In the TODO file?

Yeah, the digestification is good, and I hope it continues. But it's
also good to be able to refer back to the original thread in it's
entirety. My thought was to make the TODO item itself a link to the
search (or ideally the thread itself). The advantage of just linking to
the search is that would allow a clever CGI to just parse through the
TODO and linkify the TODO items.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Softwarehttp://pervasive.com512-569-9461

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] TODO list comments

2005-08-25 Thread Michael Glaesemann



On Aug 25, 2005, at 10:58 AM, Tom Lane wrote:


* %Remove CREATE CONSTRAINT TRIGGER

  This was used in older releases to dump referential integrity
  constraints.



Do we really want to remove it, and thereby guarantee we can't load
dumps from those old releases?


Also, I believe CONSTRAINT TRIGGERS are the only way to provide  
transaction level (rather than statement level) referential  
integrity. I've used this in the past. The SQL command reference page  
mentions that it's not for general use, but it'd be a shame to remove  
it before there's an alternative way to provide transaction level  
referential integrity.


Michael Glaesemann
grzm myrealbox com



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] TODO list comments

2005-08-25 Thread Hannu Krosing

On K, 2005-08-24 at 21:58 -0400, Tom Lane wrote:
  * %Allow TRUNCATE ... CASCADE/RESTRICT
 
 Huh?  What would that do?

Maybe this was meant truncating of tables with dependent foreign keys ?

AFAIR this was solved by allowing truncating several tables in one
command even if they have FK relationships between themselves.


 This is only partly done --- the 8.1 patch didn't cover all object types.
 
  o %Disallow dropping of an inherited constraint
  ...
  o %Prevent child tables from altering constraints like CHECK that were
inherited from the parent table
 
 These seem to be duplicates, or at least in need of merging.

It should probably mention about weird inheritance behaviour of CREATE
CONSTRAINT ON ONLY tablename - it is not propagated to existing child
tables, but is inherited when creating new ones.

Also, I don't think this should be done at all, at least not before we
have proper partitioned table support ready. I could live with it
creating a warning about not being future-compatible.

  o Handle references to temporary tables that are created, destroyed,
then recreated during a session, and EXECUTE is not used
  
This requires the cached PL/PgSQL byte code to be invalidated when
an object referenced in the function is changed.
 
 This is redundant with the Dependency Checking item about regenerating
 cached plans.

Or maybe not completely, depending on how you do it. 

If temp table itself is created inside the same pl/pgsql function, then
there could still be a way to do the planning/optimising only once and
then substitute temp table oids when running the function. 

The table structure in this case is quaranteed to be the same during
each run of the function, it's just that the temp table and index oids
should be treated as local variables.

Done this way, it gives real benefits in terms of cached query plans,
instead of just preventing newcomers from shooting themselves in foot by
not using EXECUTE.

  * Improve speed with indexes
  
For large table adjustements during vacuum, it is faster to reindex
rather than update the index.
 
 This applies only to VACUUM FULL, so it probably needs to be reworded.

In case we implement concurrent/non-blocking CREATE INDEX at some point,
this might be a good idea for lazy VACUUM as well.

And it may make more sense to do CLUSTER instead of VACUUM FULL in at
least some of these cases.

(btw. CLUSTER seems to be another function which my concurrent vacuuming
patch should be extended to cover, at least on client side, like
CREATE INDEX)

  * Auto-vacuum
  
  o %Suggest VACUUM FULL if a table is nearly empty
 
 It seems like a fairly bad idea for auto-vacuum to do a VACUUM FULL
 ever, given the locking effects.  And how is a background daemon going
 to suggest anything?  It could write to the postmaster log but it's
 entirely likely the user would never notice.

With current implementations of commands, doing (some equivalent of)
CLUSTER here seems a better idea than VACUUM FULL, as it also un-bloats
indexes. Not sure of of transactional behaviour though.

-- 
Hannu Krosing [EMAIL PROTECTED]


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] TODO list comments

2005-08-25 Thread Robert Treat

On Wed, 2005-08-24 at 21:58, Tom Lane wrote:
  o Add pg_dumpall custom format dumps.
  
This is probably best done by combining pg_dump and pg_dumpall
into a single binary.
 
 This is probably obsoleted by events, too.  Now that we can dump blobs
 in text mode, I see no reason that we ever need to do this.
 pg_restore's only real reason to live is to support selective restore
 (ie, pulling out just a few objects from an existing dump) and I do not
 see that you need that for pg_dumpall dumps.
 

Being able to restore just the database users without restoring all
databases?  (There are other ways that could be accomplished, like
adding user information to pg_dump, but it's one scenario anyway) 

Actually the argument that you would have to do both a pg_dumpall of the
cluster and a pg_dump of each database in order to obtain this
functionality seems so user unfriendly it seems like something to persue
on those grounds alone (imho).


Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] TODO list comments

2005-08-25 Thread Matt Miller

On Thu, 2005-08-25 at 15:50 +0900, Michael Glaesemann wrote:
  * %Remove CREATE CONSTRAINT TRIGGER
 
  Do we really want to remove it,
 
 Also, I believe CONSTRAINT TRIGGERS are the only way to provide  
 transaction level (rather than statement level) referential  
 integrity.

Don't deferrable foreign keys give you transaction-level referential
integrity?  From the SET CONSTRAINTS doc:

Synopsis
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }
Description
SET CONSTRAINTS sets the behavior of constraint checking within the
current transaction. IMMEDIATE constraints are checked at the end of
each statement. DEFERRED constraints are not checked until transaction
commit.



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] TODO list comments

2005-08-25 Thread Alvaro Herrera

On Thu, Aug 25, 2005 at 01:53:32PM -, Greg Sabino Mullane wrote:

 Tom Lane asked:
 
o Improve psql's handling of multi-line queries
 
  Uh, what's wrong with it?  This item seems far too vague.
 
 I think perhaps this means adding multi-line support to
 the tab-completion? Only thing I can think of, cause other
 than that, multi-line queries work just fine.

The saved history is also not cool about multiline queries.  If you
enter them interactively (or by pasting), they are entered as several
entries.  If you edit them with \e, they are entered as a single unit.

It would be also nice to have M-# to work well -- currently it inserts a
#, which works in bash but is obviously wrong in psql.

-- 
Alvaro Herrera (alvherre[a]alvh.no-ip.org)
Los románticos son seres que mueren de deseos de vida

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] TODO list comments

2005-08-25 Thread Greg Sabino Mullane


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


Tom Lane asked:

   o Improve psql's handling of multi-line queries

 Uh, what's wrong with it?  This item seems far too vague.

I think perhaps this means adding multi-line support to
the tab-completion? Only thing I can think of, cause other
than that, multi-line queries work just fine.

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200508250952
https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEARECAAYFAkMNzTAACgkQvJuQZxSWSshB8gCgvOU3rZi1uwFnwXO2zVz6KjUG
TUwAn3VoHGbqGkP1bRItMgVFE3vPQkkf
=rA0w
-END PGP SIGNATURE-



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] TODO list comments

2005-08-25 Thread Tom Lane

Oliver Elphick olly@lfix.co.uk writes:
 It would be better to show the columns aligned (perhaps without showing
 separators for other columns so as not to give the impression that the
 other columns contain null or empty strings):

 junk=# select * from xyz;
  id |name|  address  | del_addr 
 ++---+--
   1 | Joe Bloggs | 1 Hindhead Villas,| 2 The Laurels,
  | Newport,  | Swinkley,
  | Gwent | XX3 5CX
 (1 row)

I think the above is unacceptable because it looks indistinguishable
from a valid but quite different dataset.  (No, the 1 row doesn't make
it better; as soon as there's more than one row you can't tell what you
have.  And leaving out the first | doesn't help if all the columns are
multiline.)

It might be OK without any separators on the added lines, though:

 id |name|  address  | del_addr 
++---+--
  1 | Joe Bloggs | 1 Hindhead Villas,| 2 The Laurels,
   Newport,Swinkley,
   Gwent   XX3 5CX
(1 row)

Or perhaps use a different separator:

junk=# select * from xyz;
 id |name|  address  | del_addr 
++---+--
  1 | Joe Bloggs | 1 Hindhead Villas,| 2 The Laurels,
++ Newport,  + Swinkley,
++ Gwent + XX3 5CX
(1 row)

Not sure how hard this would be to program, or what sort of overhead it
might impose to check for the case.  My recollection is that psql's
table-layout code is pretty slow and ugly already ...

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] TODO list comments

2005-08-25 Thread Andrew Dunstan




Tom Lane wrote:


Or perhaps use a different separator:

junk=# select * from xyz;
id |name|  address  | del_addr 
++---+--

 1 | Joe Bloggs | 1 Hindhead Villas,| 2 The Laurels,
   ++ Newport,  + Swinkley,
   ++ Gwent + XX3 5CX
(1 row)
 



That's a terrific idea, and, incidentally, just the sort of project that 
might well suit a beginning hacker, since the code is pretty isolated.



Not sure how hard this would be to program, or what sort of overhead it
might impose to check for the case.  My recollection is that psql's
table-layout code is pretty slow and ugly already ...


 



If people want speed they shouldn't use psql as a client anyway. I don't 
see this as much of an obstacle.


cheers

andrew

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] TODO list comments

2005-08-25 Thread Jim C. Nasby

On Wed, Aug 24, 2005 at 09:58:04PM -0400, Tom Lane wrote:
  * %Allow RULE recompilation
 
 Eh?  Perhaps you meant automatically regenerate cached plans when
 needed, in which case it's redundant with the Dependency Checking
 entries.  Whatever it means, this doesn't seem a particularly simple
 item.

Hrm... I read that as allowing CREATE OR REPLACE on rules, but of course
that already exists.

http://lnk.nu/search.postgresql.org/3mt.search

  * %Allow TRUNCATE ... CASCADE/RESTRICT
 
 Huh?  What would that do?

http://archives.postgresql.org/pgsql-hackers/2003-08/msg01045.php

  o %Add ALTER DOMAIN TYPE
 
 To do what, exactly?  This is unclear.

http://archives.postgresql.org/pgsql-hackers/2004-05/msg00985.php

  o Remove unnecessary abstractions in pg_dump source code
 
 Like which?

I *think* this is reffering to how pg_dump makes some assumptions about
what things are system objects.

http://archives.postgresql.org/pgsql-committers/2005-08/msg00203.php
doesn't help a heck of a lot...

Can we add an interface to the TODO list that contains search links back
to the mailing lists?
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Softwarehttp://pervasive.com512-569-9461

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] TODO list comments

2005-08-25 Thread Michael Glaesemann



On Aug 25, 2005, at 11:29 PM, Matt Miller wrote:



On Thu, 2005-08-25 at 15:50 +0900, Michael Glaesemann wrote:



* %Remove CREATE CONSTRAINT TRIGGER




Do we really want to remove it,




Also, I believe CONSTRAINT TRIGGERS are the only way to provide
transaction level (rather than statement level) referential
integrity.




Don't deferrable foreign keys give you transaction-level referential
integrity?  From the SET CONSTRAINTS doc:



Sorry, I misspoke. What I'm thinking of is not referential integrity  
in the sense of foreign keys, but assertions, which PostgreSQL does  
not yet support.


Say for example you have a table that contains time periods marked by  
a start_date and an end_date and you want there to be no gaps between  
the different time periods in the table for a given key. When doing  
updates, deletes, or inserts on this table, you'll need to check to  
make sure there are no gaps when the transaction is finished.  
However, there may indeed be gaps during the transaction as  
start_dates and end_dates are updated. Triggers can be written to  
enforce this kind of integrity, but they'll only work if they're  
deferrable.


Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[HACKERS] TODO list comments

2005-08-24 Thread Tom Lane

I made a pass over the TODO list to see what was out of date.

 * Allow administrators to safely terminate individual sessions either
   via an SQL function or SIGTERM 
 
   Currently SIGTERM of a backend can lead to lock table corruption.

This comment may be out of date.  Suggest

Lock table corruption following SIGTERM of an individual backend
has been reported in 8.0.  A possible cause is fixed in 8.1, but
it is unknown whether other trouble spots exist.  This item is
mainly a matter of doing adequate testing rather than of writing
any new code.

   o Allow postgresql.conf values to be set so they can not be changed
 by the user

Is that really a good idea?  The ones that are unsafe are restricted already.

 * %Remove Money type, add money formatting for decimal type

There's a fair-size contingent that doesn't want Money removed
completely, but just reimplemented as an I/O wrapper around type
numeric.  Maybe that's even what you mean by the TODO item, but
it's not clear.  Please at least mention the alternative.

   o %Allow MIN()/MAX() on arrays

This is done.

   o Modify array literal representation to handle array index lower bound
 of other than one

This too.

   o Add security checking for large objects
 
 Currently large objects entries do not have owners. Permissions can
 only be set at the pg_largeobject table level.

This comment is wrong: trying to set the permissions on pg_largeobject
would have no effect whatsoever on the lo_xxx functions, so there is not
even a partial solution available now.

   o Auto-delete large objects when referencing row is deleted

This should note that contrib/lo already offers a solution.

 * %Have views on temporary tables exist in the temporary namespace
 * Allow temporary views on non-temporary tables

Both of these are done in 8.1.

 * %Allow RULE recompilation

Eh?  Perhaps you meant automatically regenerate cached plans when
needed, in which case it's redundant with the Dependency Checking
entries.  Whatever it means, this doesn't seem a particularly simple
item.

 * %Allow TRUNCATE ... CASCADE/RESTRICT

Huh?  What would that do?

 * Make row-wise comparisons work per SQL spec

This could probably be marked as a % item.

   o Currently the system uses the operating system COPY command to
 create a new database. Add ON COMMIT capability to CREATE TABLE AS
 SELECT

This seems a bit garbled, and anyway the first part is done.

   o %Add ALTER DOMAIN TYPE

To do what, exactly?  This is unclear.

   o -Allow objects to be moved to different schemas

This is only partly done --- the 8.1 patch didn't cover all object types.

   o %Disallow dropping of an inherited constraint
 ...
   o %Prevent child tables from altering constraints like CHECK that were
 inherited from the parent table

These seem to be duplicates, or at least in need of merging.

   o Handle references to temporary tables that are created, destroyed,
 then recreated during a session, and EXECUTE is not used
 
 This requires the cached PL/PgSQL byte code to be invalidated when
 an object referenced in the function is changed.

This is redundant with the Dependency Checking item about regenerating
cached plans.

   o Add table function support to pltcl, plperl, plpython?

Isn't this done for plperl?

   o Allow PL/pgSQL to name columns by ordinal position, e.g. rec.(3)

This doesn't seem like an amazingly good idea; would prefer to see a way
to get the column name list and use names dynamically.  Numbers have all
the same problems as SELECT * ...

   o Add MOVE to PL/pgSQL

This should be generalized: upgrade plpgsql cursor support to have all
the FETCH and MOVE options of the main language.

   o Add support for polymorphic arguments and return types to plperl

I think all the PLs except plpgsql need this.

Also, all the PLs except plpgsql are well behind the curve on supporting
parameter names and OUT parameters.  Please add TODO item(s) for these.

 * Allow libpq to access SQLSTATE so pg_ctl can test for connection failure
 
   This would be used for checking if the server is up.

Huh?  What has SQLSTATE got to do with connection failure checking?

 * Have initdb set DateStyle based on locale?

Is this really a good idea?  Being standardized on ISO format seems like
a good thing to me, and encouraging people to adopt ambiguous formats as
default a very bad thing.  They can do it if they like, certainly, but
having initdb do it for them just seems like not the direction we want.

 * Add a schema option to createlang

This is superseded by events: createlang now puts the functions in
pg_catalog, and there doesn't seem any particularly good reason to
want to put them elsewhere.

   o Improve psql's handling of multi-line queries

Uh, what's wrong with it?  This item seems far too vague.

   o Add pg_dumpall

Re: [HACKERS] TODO list comments

2005-08-24 Thread Kris Jurka




On Wed, 24 Aug 2005, Tom Lane wrote:


* Fetch heap pages matching index entries in sequential order

  Rather than randomly accessing heap pages based on index entries, mark
  heap pages needing access in a bitmap and do the lookups in sequential
  order. Another method would be to sort heap ctids matching the index
  before accessing the heap rows.


This is done (see bitmap index scans).



Will the optimizer ever choose this plan when dealing with only one index?

Kris Jurka

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] TODO list comments

2005-08-24 Thread Tom Lane

Kris Jurka [EMAIL PROTECTED] writes:
 On Wed, 24 Aug 2005, Tom Lane wrote:
 This is done (see bitmap index scans).

 Will the optimizer ever choose this plan when dealing with only one index?

Certainly.  It's actually likely to prefer a bitmap scan whenever the
query is estimated to fetch more than one percent or so of the table
(although if you are demanding ORDER BY the index order, the crossover
point is higher, since a bitmap scan doesn't deliver sorted output).

Something that probably ought to be on the Open Items list for 8.1
is whether the cost estimation for bitmap vs plain indexscan is OK.
It's entirely likely that we need to do some tweaking to get the
planner to make the right choice.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] TODO list

Bruce Momjian said:
 Andrew Dunstan wrote:

 2 things.

 I submitted a patch for this 5 months ago, which is still waiting to
be  merged (hope it hasn't bitrotted in the meantime):

 . Allow log lines to include session-level information, like database
and user

 If nobody is working on this I am prepared to look at it:

 . Allow logging of only data definition(DDL), or DDL and modification
statements

 Yes, sorry I haven't gotten back to that, and yes the DDL idea is a good
one.

 For the log idea, I think we need to get a way to merge all the
 per-line info into one setup, so pid, timestamp, user, etc would all be
configurable using your setup.


I thought we had thrashed this out back in August. Certainly the only
thing I recall seeing after I submitted the patch was some stylistic
criticism from Neil, which I addressed in a revised patch.

Anyway, it is in principle doable. That's partly why I adopted a printf
style format string. There are some wrinkles, though:
. interaction with syslog pid/timestamp logging
. making sure the info is available when you need to log it - I had to
rearrange a few thing to avoid getting SEGVs, IIRC.

Also, the session duration logging part of the patch is orthogonal to the
issue.

cheers

andrew




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] TODO list

2004-01-06 Thread Bruce Momjian

Andrew Dunstan wrote:
 Bruce Momjian said:
  Andrew Dunstan wrote:
 
  2 things.
 
  I submitted a patch for this 5 months ago, which is still waiting to
 be  merged (hope it hasn't bitrotted in the meantime):
 
  . Allow log lines to include session-level information, like database
 and user
 
  If nobody is working on this I am prepared to look at it:
 
  . Allow logging of only data definition(DDL), or DDL and modification
 statements
 
  Yes, sorry I haven't gotten back to that, and yes the DDL idea is a good
 one.
 
  For the log idea, I think we need to get a way to merge all the
  per-line info into one setup, so pid, timestamp, user, etc would all be
 configurable using your setup.
 
 
 I thought we had thrashed this out back in August. Certainly the only
 thing I recall seeing after I submitted the patch was some stylistic
 criticism from Neil, which I addressed in a revised patch.
 
 Anyway, it is in principle doable. That's partly why I adopted a printf
 style format string. There are some wrinkles, though:
 . interaction with syslog pid/timestamp logging

Yes.  If you use syslog, just don't ask for pid/timestamp and let syslog
do it.  Of course, right now we are able to send non-pid/timestamp to
syslog _and_ send pid/timestamp to a log file, but that seems like a
rare operation that doesn't justify keeping the various log parameters
separate.

Also, I would like to see some kind of session identifier that is more
unique than pid, which wraps around.  Ideally we could have 10{pid},
then then the pid wraps around, 20{pid), or something like that.

 . making sure the info is available when you need to log it - I had to
 rearrange a few thing to avoid getting SEGVs, IIRC.

Of course some messages, like postmaster status messages, don't have
some of these fields, like username or host.  Is that going to cause
problems for tools that read our log files?

 Also, the session duration logging part of the patch is orthogonal to the
 issue.

You mean query duration?  Yes, it is orthoginal.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] TODO list



Bruce Momjian wrote:

Andrew Dunstan wrote:
 

I thought we had thrashed this out back in August. Certainly the only
thing I recall seeing after I submitted the patch was some stylistic
criticism from Neil, which I addressed in a revised patch.
Anyway, it is in principle doable. That's partly why I adopted a printf
style format string. There are some wrinkles, though:
. interaction with syslog pid/timestamp logging
   

Yes.  If you use syslog, just don't ask for pid/timestamp and let syslog
do it.  Of course, right now we are able to send non-pid/timestamp to
syslog _and_ send pid/timestamp to a log file, but that seems like a
rare operation that doesn't justify keeping the various log parameters
separate.


I'm OK with that as long as it is the consensus view. It does seem a 
little odd to remove functionality (however rare) for the sake of 
configuration neatness, though.

Also, I would like to see some kind of session identifier that is more
unique than pid, which wraps around.  Ideally we could have 10{pid},
then then the pid wraps around, 20{pid), or something like that.


This requires some thought. ISTM it wouldn't buy you much unless you 
made it persistent across server restarts, and possibly not even then. I 
don't see it as a reason to hold up these features, though. If someone 
wants to tackle this it could be plugged in to the loginfo feature very 
easily as an extra escape sequence.

 

. making sure the info is available when you need to log it - I had to
rearrange a few thing to avoid getting SEGVs, IIRC.
   

Of course some messages, like postmaster status messages, don't have
some of these fields, like username or host.  Is that going to cause
problems for tools that read our log files?


If users want a non-empty info string they will have to teach the tools 
to handle it anyway. The point was, however, that rolling up PID and 
timestamp into the printf-style format will require some significant 
work, because we wouldn't want to lose that info if the user/db weren't 
known, whereas the patch currently suppresses all log-info output if 
these are not present (i.e. when MyProcPort == NULL).

 

Also, the session duration logging part of the patch is orthogonal to the
issue.
   

You mean query duration?  Yes, it is orthoginal.
 



No, I meant the logging of the end of a session, including its duration, 
which was also in the patch.

cheers

andrew



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [HACKERS] TODO list

2004-01-06 Thread Jon Jensen

On Tue, 6 Jan 2004, Andrew Dunstan wrote:

 Also, I would like to see some kind of session identifier that is more
 unique than pid, which wraps around.  Ideally we could have 10{pid},
 then then the pid wraps around, 20{pid), or something like that.
 
 This requires some thought. ISTM it wouldn't buy you much unless you 
 made it persistent across server restarts, and possibly not even then.

And on OpenBSD (though no other platforms that I know of) the PID is a
random number, so there is no wrapping to begin with.

Jon

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] TODO list

2004-01-06 Thread Mario Weilguni

Am Tuesday 06 January 2004 21:30 schrieb Jon Jensen:
 On Tue, 6 Jan 2004, Andrew Dunstan wrote:
  Also, I would like to see some kind of session identifier that is more
  unique than pid, which wraps around.  Ideally we could have 10{pid},
  then then the pid wraps around, 20{pid), or something like that.
 
  This requires some thought. ISTM it wouldn't buy you much unless you
  made it persistent across server restarts, and possibly not even then.

 And on OpenBSD (though no other platforms that I know of) the PID is a
 random number, so there is no wrapping to begin with.

Linux = 2.6 has random pids too.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] TODO list

2004-01-06 Thread Bruce Momjian

Andrew Dunstan wrote:
 
 
 Bruce Momjian wrote:
 
 Andrew Dunstan wrote:
   
 
 I thought we had thrashed this out back in August. Certainly the only
 thing I recall seeing after I submitted the patch was some stylistic
 criticism from Neil, which I addressed in a revised patch.
 
 Anyway, it is in principle doable. That's partly why I adopted a printf
 style format string. There are some wrinkles, though:
 . interaction with syslog pid/timestamp logging
 
 
 
 Yes.  If you use syslog, just don't ask for pid/timestamp and let syslog
 do it.  Of course, right now we are able to send non-pid/timestamp to
 syslog _and_ send pid/timestamp to a log file, but that seems like a
 rare operation that doesn't justify keeping the various log parameters
 separate.
 
 
 
 
 I'm OK with that as long as it is the consensus view. It does seem a 
 little odd to remove functionality (however rare) for the sake of 
 configuration neatness, though.

Yes, agreed.  Let's explore it.  The rare functionality we would be
removing is for:

o  User uses syslog
o  User wants to log postmaster output to a file too
o  User wants timestamp info in the postmaster output file

And the downside is that they get duplicate timestamps in syslog.  

Now, if we don't merge timestamp into your new per-line log string, we
end up with a rather illogical and inflexible output format, only to
allow the rare case listed above.

Clearly this isn't a 100% clear decision, but it seems to lean in the
direction of removing the functionality with the goal of providing a
more logical logging API to the users.

 Also, I would like to see some kind of session identifier that is more
 unique than pid, which wraps around.  Ideally we could have 10{pid},
 then then the pid wraps around, 20{pid), or something like that.
 
 
 
 This requires some thought. ISTM it wouldn't buy you much unless you 
 made it persistent across server restarts, and possibly not even then. I 
 don't see it as a reason to hold up these features, though. If someone 
 wants to tackle this it could be plugged in to the loginfo feature very 
 easily as an extra escape sequence.

Yes, that was my idea --- let's get this in and we can then add a
session id, and your point about restarts is a good one.

 . making sure the info is available when you need to log it - I had to
 rearrange a few thing to avoid getting SEGVs, IIRC.
 
 
 
 Of course some messages, like postmaster status messages, don't have
 some of these fields, like username or host.  Is that going to cause
 problems for tools that read our log files?
 
 
 
 If users want a non-empty info string they will have to teach the tools 
 to handle it anyway. The point was, however, that rolling up PID and 
 timestamp into the printf-style format will require some significant 
 work, because we wouldn't want to lose that info if the user/db weren't 
 known, whereas the patch currently suppresses all log-info output if 
 these are not present (i.e. when MyProcPort == NULL).

Oh, good point.  That would suggest that maybe we are better off leaving
pid and timestamp as separate options and _not_ merge them into your new
string.  I am starting to think having your string print only
session-specific information is the best way.

I wonder if we should rename your GUC log_session_line or something like
that.

 Also, the session duration logging part of the patch is orthogonal to the
 issue.
 
 You mean query duration?  Yes, it is orthoginal.
 
 No, I meant the logging of the end of a session, including its duration, 
 which was also in the patch.

Oh, I missed that one.  It seems like a nice addition.  I see you added
it when they ask for log_connections.  Good idea.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] TODO list



Jon Jensen wrote:

On Tue, 6 Jan 2004, Andrew Dunstan wrote:

 

Also, I would like to see some kind of session identifier that is more
unique than pid, which wraps around.  Ideally we could have 10{pid},
then then the pid wraps around, 20{pid), or something like that.
 

This requires some thought. ISTM it wouldn't buy you much unless you 
made it persistent across server restarts, and possibly not even then.
   

And on OpenBSD (though no other platforms that I know of) the PID is a
random number, so there is no wrapping to begin with.
 

OK, so a sessionid based on prefix+pid won't work portably. If we 
*really* want to do it, a cluster-wide sequence generator would probably 
be the way to go, but I suspect that with the ability to log session 
termination explicitly (which I have already provided) much of the 
supposed extra utility would disappear anyway.

cheers

andrew

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] TODO list