[HACKERS] poor performance with Context Switch Storm at TPC-W.

2006-07-11 Thread Katsuhiko Okano
Hi,All.

The problem has occurred in my customer.
poor performance with Context Switch Storm occurred
with the following composition.
Usually, CS is about 5000, WIPS=360.
when CSStorm occurrence, CS is about 10, WIPS=60 or less.
(WIPS = number of web interactions per second)

It is under investigation using the patch
which collects a LWLock.

I suspected conflict of BufMappingLock.
but, collected results are seen,
occurrence of CSStorm and the increase of BufMappingLock counts
seem not to correspond.
Instead, SubtransControlLock and SubTrans were increasing.
I do not understand what in the cause of CSStorm.



[DB server]*1
Intel Xeon 3.0GHz*4(2CPU * H/T ON)
4GB Memory
Red Hat Enterprise Linux ES release 4(Nahant Update 3)
Linux version 2.6.9-34.ELsmp
PostgreSQL8.1.3 (The version 8.2(head-6/15) was also occurred)
shared_buffers=131072
temp_buffers=1000 
max_connections=300

[AP server]*2
200 connection pooling.
TPC-W model workload

[Clinet]*4
TPC-W model workload



(1)
The following discussion were read.
http://archives.postgresql.org/pgsql-hackers/2006-05/msg01003.php
From: Tom Lane tgl ( at ) sss ( dot ) pgh ( dot ) pa ( dot ) us 
To: josh ( at ) agliodbs ( dot ) com 
Subject: Re: Further reduction of bufmgr lock contention 
Date: Wed, 24 May 2006 15:25:26 -0400 

If there is a patch for investigation or a technique,
would someone show it to me?


(2)
It seems that much sequential scan has occurred at CSStorm.
When reading a tuple, do the visible satisfy check.
it seems to generate the subtransaction for every transaction.
How much is a possibility that 
the LWLock to a subtransaction cause CSStorm?


best regards.

Katsuhiko Okano
okano katsuhiko _at_ oss ntt co jp

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


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Thomas Hallgren
I'd like to submit PL/Java into core for 8.2 if possible. Personally, I see the following 
action items to make it happen:


1. A hackers discussion to resolve any issues with the submission.

Provided that #1 has a positive outcome:

2. The PL/Java CVS must be moved from gborg and become part of the PostgreSQL CVS (can this 
be done with version history intact?).

3. The regression tests need some work in order to fit in with the build farm.
4. Documentation must be ripped from the PL/Java Wiki and transformed into the format used 
by PostgreSQL.

5. I'll need committer rights to the PL/Java part in order to maintain it.
6. The pljava-dev mailing list, currently at gborg, must (perhaps) be moved also. An 
alternative is to remove it and instead refer to jdbc, general, and hackers.


Given guidance, I'll do the steps #3 and #4.

External dependencies:
Platforms where PL/Java is ported must either support GCJ 4.0 or higher or have a Java 
Runtime Environment 1.4.2 or higher installed.


Regards,
Thomas Hallgren

Bruce Momjian wrote:

There are roughly three weeks left until the feature freeze on August 1.
If people are working on items, they should be announced before August
1, and the patches submitted by August 1.  If the patch is large, it
should be discussed now and an intermediate patch posted to the lists
soon.

FYI, we don't have many major features ready for 8.2.

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

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

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




---(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] poor performance with Context Switch Storm at TPC-W.

2006-07-11 Thread Qingqing Zhou

Katsuhiko Okano [EMAIL PROTECTED] wrote

 The problem has occurred in my customer.
 poor performance with Context Switch Storm occurred
 with the following composition.
 Usually, CS is about 5000, WIPS=360.
 when CSStorm occurrence, CS is about 10, WIPS=60 or less.

 Intel Xeon 3.0GHz*4(2CPU * H/T ON)
 4GB Memory

Do you have bgwriter on and what's the parameters? I read a theory somewhere
that bgwriter scan a large portion of memory and cause L1/L2 thrushing, so
with HT on, the other backends sharing the physical processor with it also
get thrashed ... So try to turn bgwriter off or turn HT off see what's the
difference.

Regards,
Qingqing



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

   http://archives.postgresql.org


[HACKERS] Cleanup of include files

2006-07-11 Thread Bruce Momjian
I am in the process of cleaning up the include files.  Each include file
should compile on its own, and we should only include files we need. 
Also, PostgreSQL include files should be in alphabetical order.

I do this cleanup every few years.

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

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

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

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


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Tom Lane
Thomas Hallgren [EMAIL PROTECTED] writes:
 I'd like to submit PL/Java into core for 8.2 if possible. Personally, I see 
 the following 
 action items to make it happen:

What about licensing issues?  Does PL/Java work with any entirely-open-source
JVMs?  If not, what is the legal situation for distributing PG+PL/Java?

I'm also a bit concerned about size.  By my count, lines of source code:

plpgsql 19890
plperl  4902
plpython4163
pltcl   4498
pljava 1.3.038711

IOW pljava is (already) bigger than the other four PLs put together.

I'm inclined to think that pljava is best off staying as a separate
project.

regards, tom lane

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


Re: [HACKERS] Warm-Standby using WAL archiving / Seperate

2006-07-11 Thread Andrew Rawnsley

Just having a standby mode that survived shutdown/startup would be a nice
start...

I also do the blocking-restore-command technique, which although workable,
has a bit of a house-of-cards feel to it sometimes.



On 7/10/06 5:40 PM, Florian G. Pflug [EMAIL PROTECTED] wrote:

 Merlin Moncure wrote:
 On 7/10/06, Florian G. Pflug [EMAIL PROTECTED] wrote:
 This methods seems to work, but it is neither particularly fool-proof nor
 administrator friendly. It's not possible e.g. to reboot the slave
 without postgres
 abortint the recovery, and therefor processing all wals generated
 since the last
 backup all over again.
 
 Monitoring this system is hard too, since there is no easy way to
 detect errors
 while restoring a particular wal.
 
 what I would really like to see is to have the postmaster start up in
 a special read only mode where it could auto-restore wal files placed
 there by an external process but not generate any of its own.  This
 would be a step towards a pitr based simple replication method.
 
 I didn't dare to ask for being able to actually _access_ a wal-shipping
 based slaved (in read only mode) - from how I interpret the code, it's
 a _long_ way to get that working. So I figured a stand-alone executable
 that just recovers _one_ archived wal would at least remove that
 administrative
 burden that my current solution brings. And it would be easy to monitor
 the Y



---(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] A couple thoughts about btree fillfactor

2006-07-11 Thread Kenneth Marshall
On Mon, Jul 10, 2006 at 12:36:34PM -0400, Tom Lane wrote:
 Now that the index options infrastructure is in, I am having a couple of
 second thoughts about the specific behavior that's been implemented,
 particularly for btree fillfactor.
 
 1. ...  I'm thinking
 we could change the nbtsort.c code to work like stop filling page
 when fillfactor is exceeded AND there are at least two entries already.
 Then any old fillfactor would work.
 
 2. ...  There's a case to be made for making
 leaf and non-leaf fillfactors accessible as separate knobs, but I'm
 inclined just to use a fixed value of 70 for non-leaf factor
 
 3. What should the minimum fillfactor be?  The patch as submitted
 set the minimum to 50% for all relation types.  I'm inclined to
 think we should allow much lower fillfactors, maybe down to 10%.
 A really low fillfactor could be a good idea in a heavily updated
 table --- at least, I don't think we have any evidence to prove
 that it's not sane to want a fillfactor below 50%.
 
 Comments?
 
   regards, tom lane

I would like to place my vote for supporting fillfactors less than
50%. Like you mentioned, a heavily updated table could be forced to
a page split before VACUUM freed the items and made them available
for reuse. I also think that points 1 and 2 are reasonable.

Ken

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


Re: [HACKERS] poor performance with Context Switch Storm at TPC-W.

2006-07-11 Thread Alvaro Herrera
Katsuhiko Okano wrote:

 I suspected conflict of BufMappingLock.
 but, collected results are seen,
 occurrence of CSStorm and the increase of BufMappingLock counts
 seem not to correspond.
 Instead, SubtransControlLock and SubTrans were increasing.
 I do not understand what in the cause of CSStorm.

Please see this thread:
http://archives.postgresql.org/pgsql-hackers/2005-11/msg01547.php
(actually it's a single message AFAICT)

This was applied on the 8.2dev code, so I'm surprised that 8.2dev
behaves the same as 8.1.

Does your problem have any relationship to what's described there?

I also wondered whether the problem may be that the number of SLRU
buffers we use for subtrans is too low.  But the number was increased
from the default 8 to 32 in 8.2dev as well.  Maybe you could try
increasing that even further; say 128 and see if the problem is still
there.  (src/include/access/subtrans.h, NUM_SUBTRANS_BUFFERS).

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

   http://archives.postgresql.org


[HACKERS] Max size of a btree index entry

2006-07-11 Thread Tom Lane
Currently, we restrict btree index tuples to a size that ensures three of
them will fit on a page.  The motivation for this is the following two
considerations:

1. In a non-rightmost page, we need to include a high key, or page
boundary key, that isn't one of the useful data keys.

2. In a non-leaf page, there had better be at least two child pages
(downlink entries), else we have failed to subdivide the page's key
range at all, and thus there would be a nonterminating recursion.

However: a non-leaf page actually has one more pointer than key,
eg a page with three children needs only two data keys:

 entire key range assigned to page --

-- range 1 --  boundary key -- range 2 --  boundary key -- range 3 --
 |   |   |
 v   v   v
child page 1   child page 2 child page 3

We implement this by having the first data tuple on a non-leaf page
contain only a downlink TID and no key data, ie it's just the header.

So it appears to me that we could allow the maximum size of a btree
entry to be just less than half a page, rather than just less than
a third of a page --- the worst-case requirement for a non-leaf page
is not three real tuples, but one tuple header and two real tuples.
On a leaf page we might manage to fit only one real data item, but
AFAICS that doesn't pose any correctness problems.

Obviously a tree containing many such pages would be awfully inefficient
to search, but I think a more common case is that there are a few wide
entries in an index of mostly short entries, and so pushing the hard
limit up a little would add some flexibility with little performance
cost in real-world cases.

Have I missed something?  Is this worth changing?

regards, tom lane

---(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] pgsql-patches considered harmful

2006-07-11 Thread Andrew Dunstan

Marc G. Fournier wrote:


If this is chosen as the preferred path, we could get the list bot to
add Reply-To: pghackers in pgsql-patches postings to help push
discussions there.  I'd vote for doing the same in pgsql-committers,
which also gets its share of non-null discussion content.


that is a very easy and quick change ... but wasn't doing that brought 
up before and alot of ppl were against that?


If nobody objects within, say, the next 24 hours ... ?  I'll enabled 
that one both ...




Don't be surprised if there are objections - this is one of those things 
like emacs vs vi that stirs up religious debate.


cheers

andrew

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


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Satoshi Nagayasu
Tom,

Tom Lane wrote:
 IOW pljava is (already) bigger than the other four PLs put together.

 I'm inclined to think that pljava is best off staying as a separate
 project.

I was very confused some recent PL/Java versions can't be compiled
because of PostgreSQL internal changes.

If people think pl/java is important for PostgreSQL,
pl/java should be included in PG core tree,
and should have its regression tests.

I think PL should be integrated with core tightly.

Thanks.
-- 
NAGAYASU Satoshi [EMAIL PROTECTED]
Phone: +81-3-3523-8122


---(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] poor performance with Context Switch Storm at TPC-W.

2006-07-11 Thread Josh Berkus

Katsuhiko,

Have you tried turning HT off?   HT is not generally considered (even by 
Intel) a good idea for database appplications.


--Josh

---(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] Three weeks left until feature freeze

2006-07-11 Thread Andrew Dunstan

Thomas Hallgren wrote:


5. I'll need committer rights to the PL/Java part in order to maintain 
it.



Does our CVS setup cater for seggregated rights like this? Or would that 
be done on a trust basis?


cheers

andrew

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


Re: [HACKERS] CTIDs invalidations and dropping columns.

2006-07-11 Thread Martijn van Oosterhout
On Tue, Jul 11, 2006 at 01:50:40AM +0300, Tzahi Fadida wrote:
 As i understand rowids, i.e ctids, are supposed to allow for fast access to 
 the tables. I don't see the rational, for example, when casting some 
 attributes, to blank the ctid. So it is not exactly the same, but it still 
 came from the same tuple. What will happen if for read only SPI queries
 it will not be blank?

Did you read the email Tom sent? 

I worked out the exact issue with your example btw. It's because of the
DROP COLUMN. After dropping the column the tuples on disk have 3
columns and you only asked for 2, so an extra step has to be taken.
This extra step copies the two values, creating a new tuple, which has
no CTID.

If you're tying yourself this tightly to the backend, maybe you should
just use index_beginscan/heap_beginscan/etc which return actual tuples.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Josh Berkus

Tom,


What about licensing issues?  Does PL/Java work with any entirely-open-source
JVMs?  If not, what is the legal situation for distributing PG+PL/Java?


Actually, Sun has re-licensed the JRE to make it OSS-compatible (it's 
now available for Debian, for example)  They're doing a Java licensing 
session at  OSCON if you have any specific questions, or I can ping the 
Java Licensing Guru directly.  But even if other JRE's aren't supported, 
licensing shouldn't be an obstacle.




I'm also a bit concerned about size.  By my count, lines of source code:

plpgsql 19890
plperl  4902
plpython4163
pltcl   4498
pljava 1.3.038711

IOW pljava is (already) bigger than the other four PLs put together.


That is odd.  Thomas?



I'm inclined to think that pljava is best off staying as a separate
project.


I disagree.  One of the things I'm asked by every single tech market 
analyst, after replication  clustering, is whether we have support for 
procedural Java.  So it's something large-scale users want.  If PL/Tcl 
belongs in the back end, then so does PL/Java.


--Josh Berkus

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


Re: [HACKERS] Max size of a btree index entry

2006-07-11 Thread Josh Berkus

Tom,


Obviously a tree containing many such pages would be awfully inefficient
to search, but I think a more common case is that there are a few wide
entries in an index of mostly short entries, and so pushing the hard
limit up a little would add some flexibility with little performance
cost in real-world cases.

Have I missed something?  Is this worth changing?


Not sure.  I don't know that the difference between 2.7K and 3.9K would 
have ever made a difference to me in any real-world case.


If we're going to tinker with this code, it would be far more valuable 
to automatically truncate b-tree entries at, say, 1K so that they could 
be efficiently indexed.


Of course, a quick archives search of -SQL, -Newbie and -General would 
indicate how popular of an issue this is.


--Josh Berkus

---(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] Three weeks left until feature freeze

2006-07-11 Thread David Fetter
On Tue, Jul 11, 2006 at 11:21:54PM +0900, Satoshi Nagayasu wrote:
 Tom,
 
 Tom Lane wrote:
  IOW pljava is (already) bigger than the other four PLs put
  together.
 
  I'm inclined to think that pljava is best off staying as a
  separate project.
 
 I was very confused some recent PL/Java versions can't be compiled
 because of PostgreSQL internal changes.
 
 If people think pl/java is important for PostgreSQL, pl/java should
 be included in PG core tree, and should have its regression tests.
 
 I think PL should be integrated with core tightly.

It's good to integrate things with the core as needed.  What plans do
we have to integrate PL/J?

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

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

   http://archives.postgresql.org


Re: [HACKERS] pgsql-patches considered harmful

2006-07-11 Thread Bruce Momjian
Andrew Dunstan wrote:
 Marc G. Fournier wrote:
 
  If this is chosen as the preferred path, we could get the list bot to
  add Reply-To: pghackers in pgsql-patches postings to help push
  discussions there.  I'd vote for doing the same in pgsql-committers,
  which also gets its share of non-null discussion content.
 
  that is a very easy and quick change ... but wasn't doing that brought 
  up before and alot of ppl were against that?
 
  If nobody objects within, say, the next 24 hours ... ?  I'll enabled 
  that one both ...
 
 
 Don't be surprised if there are objections - this is one of those things 
 like emacs vs vi that stirs up religious debate.

If we change Reply-To:, does it prevent replies to the original author? 
If so, that seems like a problem, particularly if they are not
subscribed to the patches list.

-- 
  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] Three weeks left until feature freeze

2006-07-11 Thread Bruce Momjian
Andrew Dunstan wrote:
 Thomas Hallgren wrote:
 
  5. I'll need committer rights to the PL/Java part in order to maintain 
  it.
 
 
 Does our CVS setup cater for seggregated rights like this? Or would that 
 be done on a trust basis?

Trust.

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

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

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


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Dave Page
 

 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of 
 Andrew Dunstan
 Sent: 11 July 2006 15:27
 To: Thomas Hallgren
 Cc: pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] Three weeks left until feature freeze
 
 Thomas Hallgren wrote:
 
  5. I'll need committer rights to the PL/Java part in order 
 to maintain 
  it.
 
 
 Does our CVS setup cater for seggregated rights like this? Or 
 would that 
 be done on a trust basis?

No, I don't believe you can do this with CVS at all. We'd need something
like SVN/WebDAV to be able to grant write access just to specific parts
of the tree to different people.

Regards, Dave.

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

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


Re: [HACKERS] pgsql-patches considered harmful

2006-07-11 Thread Bruce Momjian
Andrew Dunstan wrote:
 Bruce Momjian wrote:
  Don't be surprised if there are objections - this is one of those things 
  like emacs vs vi that stirs up religious debate.
  
 
  If we change Reply-To:, does it prevent replies to the original author? 
  If so, that seems like a problem, particularly if they are not
  subscribed to the patches list.
 

 
 Depends on the MUA. See both sides of the debate here: 
 http://marc.merlins.org/netrants/listreplyto.html . We use reply-to for 
 the pgfoundry admins list, but that's a closed list. For open lists that 
 often accept non-member posts it is much more of a problem, not least 
 for the reason you suggest.

Let's add the author and the hackers list to the reply-to.

-- 
  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] Three weeks left until feature freeze

2006-07-11 Thread Alvaro Herrera
Bruce Momjian wrote:
 Andrew Dunstan wrote:
  Thomas Hallgren wrote:
  
   5. I'll need committer rights to the PL/Java part in order to maintain 
   it.
  
  
  Does our CVS setup cater for seggregated rights like this? Or would that 
  be done on a trust basis?
 
 Trust.

And pgsql-committers archives ;-)

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

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


Re: [HACKERS] CTIDs invalidations and dropping columns.

2006-07-11 Thread Tzahi Fadida
On Tuesday 11 July 2006 17:27, Martijn van Oosterhout wrote:
 On Tue, Jul 11, 2006 at 01:50:40AM +0300, Tzahi Fadida wrote:
  As i understand rowids, i.e ctids, are supposed to allow for fast access
  to the tables. I don't see the rational, for example, when casting some
  attributes, to blank the ctid. So it is not exactly the same, but it
  still came from the same tuple. What will happen if for read only SPI
  queries it will not be blank?

 Did you read the email Tom sent?

yes, if it is potentially broken, i think i should better use the attribute 
CTID even if there would be a performance drop.


 I worked out the exact issue with your example btw. It's because of the
 DROP COLUMN. After dropping the column the tuples on disk have 3
 columns and you only asked for 2, so an extra step has to be taken.
 This extra step copies the two values, creating a new tuple, which has
 no CTID.

10x. i c what you mean.


 If you're tying yourself this tightly to the backend, maybe you should
 just use index_beginscan/heap_beginscan/etc which return actual tuples.

I am considering it, however, it will also be accompanied with areas that 
SPI/engine previously handled that i will have to manage myself. 
I'll have to experiment a bit and see what is more important the Reuse of spi/ 
or the performance gains of heap_beginscan...


 Have a nice day,

-- 
Regards,
Tzahi.
--
Tzahi Fadida
Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info
WARNING TO SPAMMERS:  see at 
http://members.lycos.co.uk/my2nis/spamwarning.html

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


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Csaba Nagy
  Does our CVS setup cater for seggregated rights like this? Or 
  would that 
  be done on a trust basis?
 
 No, I don't believe you can do this with CVS at all. We'd need something
 like SVN/WebDAV to be able to grant write access just to specific parts
 of the tree to different people.

It is possible using CVS, by carefully managing file system permissions
and assigning different permissions to the OS users of the different
committers. I guess it's also possible using commit scripts... but I
don't think it worths the effort as long as there is a regular backup of
the CVS tree...

Cheers,
Csaba.



---(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] Three weeks left until feature freeze

2006-07-11 Thread Thomas Hallgren

Josh Berkus wrote:

Tom,

What about licensing issues?  Does PL/Java work with any 
entirely-open-source

JVMs?  If not, what is the legal situation for distributing PG+PL/Java?


Actually, Sun has re-licensed the JRE to make it OSS-compatible (it's 
now available for Debian, for example)  They're doing a Java licensing 
session at  OSCON if you have any specific questions, or I can ping 
the Java Licensing Guru directly.  But even if other JRE's aren't 
supported, licensing shouldn't be an obstacle.


I don't see any license issue at all regardless. PL/Java is satisfied 
with GCJ 4.0 or higher and compiling with that doesn't affect the binary 
more then using gcc does. No JVM is required when using GCJ.




I'm also a bit concerned about size.  By my count, lines of source code:

plpgsql19890
plperl4902
plpython4163
pltcl4498
pljava 1.3.038711

IOW pljava is (already) bigger than the other four PLs put together.


That is odd.  Thomas?


It's not that odd really:

1. the mapping is strongly typed, i.e. each scalar type in PostgreSQL 
has a set of functions that maps it to the correct primitive in Java 
(int4 is a java int, double precision is a double etc.). PL/Java will 
resort to string coercion only when no other option is left.
2. a type mapping is provided for *all* types. Scalar, composite, 
pseudo, array types, and result sets.
3. new Java mappings can be created on the fly. Both for scalar and 
composite types.
4. you can create new scalar types in PostgreSQL that uses IO functions 
written in Java.
5. the Java code contains it's own API documentation (standard java-doc 
comments on classes and methods).
6. the code is written to conform to standard interfaces such as the 
JDBC interfaces (from a #lines perspective, perhaps not always the most 
optimal way of doing it but it does bring a bunch of other advantages).
7. extensive error handling is included that allow try/catch semantics 
when checkpoints are used.
8. extreme measures has been taken to ensure that the backend is never 
exposed to more then one thread at a time.

...
(from the top of my head, there are probably more reasons)

IMHO, this is yet another reason to actually include it in core. I'm not 
an expert on the other PL's but my guess is that PL/Java is far more 
sensitive to API changes in the backend core.


Regards,
Thomas Hallgren





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


Re: [HACKERS] pgsql-patches considered harmful

2006-07-11 Thread Andrew Dunstan

Bruce Momjian wrote:
Don't be surprised if there are objections - this is one of those things 
like emacs vs vi that stirs up religious debate.



If we change Reply-To:, does it prevent replies to the original author? 
If so, that seems like a problem, particularly if they are not

subscribed to the patches list.

  


Depends on the MUA. See both sides of the debate here: 
http://marc.merlins.org/netrants/listreplyto.html . We use reply-to for 
the pgfoundry admins list, but that's a closed list. For open lists that 
often accept non-member posts it is much more of a problem, not least 
for the reason you suggest.


cheers

andrew


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

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


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Alvaro Herrera
Josh Berkus wrote:

 I'm inclined to think that pljava is best off staying as a separate
 project.
 
 I disagree.  One of the things I'm asked by every single tech market 
 analyst, after replication  clustering, is whether we have support for 
 procedural Java.  So it's something large-scale users want.  If PL/Tcl 
 belongs in the back end, then so does PL/Java.

We've discussed this before, regarding PL/php IIRC.  The conclusions the
last time around, as far as I remember, was that we wanted the PLs to be
in the same CVS repo, but able to be compiled separately from the whole
source tree.  So we could sort of rip PL/Perl et al from the actual
backend code, leaving only enough infrastructure to be able to build
them easily (PGXS plus a bunch of stuff, I imagine).  PL modules would
follow the backend branches so that there would be no need for pesky
#ifdef PGSQL_VERSION_THIS_OR_THAT stuff; but they would actually be
separate.

The main motivation was that when somebody wants to change an interface
in the backend that's used by PLs, it's useful to change all of them at
the same time instead of waiting until release time comes and the things
does not compile anymore and nobody remembers when or where they were
broken.

I think this would also allow PL/R to be included as well despite the
license, because while it would be in the same repo and editable
together with the backend, it would continue to be a separate project
and thus not contaminate the backend with GPL stuff.

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

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


Re: [HACKERS] More nuclear options

2006-07-11 Thread Robert Treat
On Monday 10 July 2006 17:06, Josh Berkus wrote:
 All,

 At the request of Dave Page, here's the semi-final list after looking at
 the code:

 To be killed:
   adddepends
   tips
   mSQL-interface


To be honest I don't know why people are against throwing the code on 
pgfoundry with a hefty readme saying that the code is unmaintained and what 
it's build status is on various versions.  This may not seem too useful, but 
if someone were to need this code, or we ever hope to get someone to update 
it and/or maintain it, thier not going to find it in the contrib modules in 
some small corner of the the ftp archive, but they might have a chance of 
finding it on pgfoundry. 

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

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


Re: [HACKERS] pgsql-patches considered harmful

2006-07-11 Thread Greg Stark

Andrew Dunstan [EMAIL PROTECTED] writes:

 Marc G. Fournier wrote:
 
  If nobody objects within, say, the next 24 hours ... ?  I'll enabled that 
  one
  both ...
 
 Don't be surprised if there are objections - this is one of those things like
 emacs vs vi that stirs up religious debate.

Indeed. The usual issue is that if someone hits personal reply their
personal note to the author will go to the mailing list. Some lists have
problems with people sending personal replies inappropriately but I doubt
that's the case for -patches or -committers. 

I have the additional complaint that this doesn't actually solve most of my
original complaints and might reduce the pressure to find a better solution.
The patches announcements themselves would still be basically invisible within
the community.

Even if someone isn't going to read or apply the actual patch I think there is
an enormous benefit to be gained from having everyone at least know it went
by. Much as I'm sure not everyone reads every line of every message on
-hackers but they are aware of what topics are under discussion.

-- 
greg


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


Re: [HACKERS] More nuclear options

2006-07-11 Thread Steve Singer

On Mon, 10 Jul 2006, Josh Berkus wrote:


To be migrated to pgFoundry:
dbmirror (need owner)


I'll volunteer for this if no one else steps forward. I'm not planning on 
making any significant chances to dbmirror at this point stage but I can 
look after for the pgfoundry project.




dbase (owner?)
fulltextindex (owner?)
mac (LER)
userlock (Merlin)

--Josh Berkus

---(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




---(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] pgsql-patches considered harmful

2006-07-11 Thread Stephen Frost
* Greg Stark ([EMAIL PROTECTED]) wrote:
 I have the additional complaint that this doesn't actually solve most of my
 original complaints and might reduce the pressure to find a better solution.
 The patches announcements themselves would still be basically invisible within
 the community.

I'm with Greg on this one.  I felt his original complaint made alot of
sense and this doesn't really deal with it.  I'd much rather see
-patches go away or maybe become an alias to -hackers.  If the patch is
too big then perhaps either compress it or provide a link to it when
it's submitted.  If hosting for patches is an issue then perhaps provide
a way for patches to be hosted on a PG server.  Honestly, I'd be happy
to put up any PG patches sent to me on a well connected server.  I'm not
sure how easy it'd be to automate that though (and prevent
spammers/etc), but perhaps people have some suggestions?

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Stephen Frost
* Josh Berkus (josh@agliodbs.com) wrote:
 Actually, Sun has re-licensed the JRE to make it OSS-compatible (it's 
 now available for Debian, for example)  They're doing a Java licensing 
 session at  OSCON if you have any specific questions, or I can ping the 
 Java Licensing Guru directly.  But even if other JRE's aren't supported, 
 licensing shouldn't be an obstacle.

Uhh..  Let's not go overboard here on exactly what Debian has done with
Sun's JVM.  Technically, Sun's JVM is *not* part of Debian.  The license
is (and even this is hotly debated...) acceptable enough for Debian's
ftp-masters to allow the Sun JVM to be distributed off Debian servers as
part of the 'non-free' archive.  This *certainly* doesn't make it
OSS-compatible by any stretch (it isn't) and it's not acceptable for 
inclusion in Debian proper.

I'm actually rather upset to see Sun making such blatently incorrect
statements.  Josh, I truely hope that you weren't actually involved in
the Sun JVM-in-Debian work and so were unaware of the very important
distinction between Distributed by Debian and in Debian/main.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] More nuclear options

2006-07-11 Thread Josh Berkus

Robert,

To be honest I don't know why people are against throwing the code on 
pgfoundry with a hefty readme saying that the code is unmaintained and what 
it's build status is on various versions


... because we don't want to litter pgFoundry with dead, broken projects 
which nobody uses and which confuse users and crowd the namespace. 
Quality  quantity.


In a year nobody has spoken up for those specific projects.   Who's 
going to maintain them?  Who's going to use them?


--Josh Berkus




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


Re: [HACKERS] More nuclear options

2006-07-11 Thread Josh Berkus

Robert,

Given the current number of projects that have no code / files / anything 
associated with them on pgfoundry/gborg right now, this argument rings a 
little hollow. 


If you're so keen to add to the problem, you can have my spot as 
pgfoundry admin.


Otherwise, the rule that the pgfoundry admins agreed on is that if a 
project had no code and no activity in 6 months we would contact the 
owner, and if no response kill it.  That we've been lagging behind on 
this is a manpower issue (and to some degree a technical issue with GForge).


People do get pointed at adddepends even today...  certainly no one will do 
anything with these projects if you nuke them, but I like giving people 
options...  your call though.  



I've already added adddepends to pgFoundry (as Old PG Upgrade), since 
people spoke up for it.  I will assign one of them as admin of the 
project (not sure who yet).


However, in the last year nobody has spoken up for the other three, not 
even you.


--Josh

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

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


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Josh Berkus

David,


It's good to integrate things with the core as needed.  What plans do
we have to integrate PL/J?


None, if the PL/J team doesn't speak up.  So far I have yet to see a 
request for PL/J or even a release notice.


--Josh

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


Re: [HACKERS] More nuclear options

2006-07-11 Thread Robert Treat
On Tuesday 11 July 2006 12:55, Josh Berkus wrote:
 Robert,

  To be honest I don't know why people are against throwing the code on
  pgfoundry with a hefty readme saying that the code is unmaintained and
  what it's build status is on various versions

 ... because we don't want to litter pgFoundry with dead, broken projects
 which nobody uses and which confuse users and crowd the namespace.
 Quality  quantity.


Given the current number of projects that have no code / files / anything 
associated with them on pgfoundry/gborg right now, this argument rings a 
little hollow. 

 In a year nobody has spoken up for those specific projects.   Who's
 going to maintain them?  Who's going to use them?


People do get pointed at adddepends even today...  certainly no one will do 
anything with these projects if you nuke them, but I like giving people 
options...  your call though.  

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

---(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] Three weeks left until feature freeze

2006-07-11 Thread Josh Berkus

Snowman,


Uhh..  Let's not go overboard here on exactly what Debian has done with
Sun's JVM.  Technically, Sun's JVM is *not* part of Debian.  The license
is (and even this is hotly debated...) acceptable enough for Debian's
ftp-masters to allow the Sun JVM to be distributed off Debian servers as
part of the 'non-free' archive.  This *certainly* doesn't make it
OSS-compatible by any stretch (it isn't) and it's not acceptable for 
inclusion in Debian proper.


I think I can tell which side of the debate you were on.


I'm actually rather upset to see Sun making such blatently incorrect
statements.  Josh, I truely hope that you weren't actually involved in
the Sun JVM-in-Debian work and so were unaware of the very important
distinction between Distributed by Debian and in Debian/main.


Keep your pants on, geez.  I'm actually rather appalled that you could 
get so unjustifiably bent out of shape at me *after* we met.  Goes to 
show you that not everything is improved by personal acquaintance.


Let's get some stuff clear:

1) I do not speak for Sun execept on specific occasions arranged by Sun 
PR.  Not ever.


2) If you re-read my message, it says:
... (it's now available for Debian, for example) ...
not ANYTHING about main or distributed or non-free or whatever.

So, I think you owe me an apology.

--Josh


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


Re: [HACKERS] More nuclear options

2006-07-11 Thread Merlin Moncure

On 7/10/06, Josh Berkus josh@agliodbs.com wrote:

All,
userlock (Merlin)


Ok, I will update the project description and maintain it.  userlock
is a great feature, and I tried contacting the original author to get
him to relicense the project but could never get a hold of him.  To be
honest, the current userlock contrib module is just very thin wrappers
over backend functions with little/no actual value.  I think the user
lock functality really belongs in the core project somehow.

The other proposed features to userlock, namely enhancement to certain
aspects of how they are handled in the backend were largely taken care
of by tom for postgresql 8.1.

By the way, some time back I started another project on gborg,
postisam, but never received permission from my former employer to
release the code.  so if that is still there it can be nuked as well.

merlin

---(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] Three weeks left until feature freeze

2006-07-11 Thread Tom Lane
Andrew Dunstan [EMAIL PROTECTED] writes:
 Thomas Hallgren wrote:
 5. I'll need committer rights to the PL/Java part in order to maintain 
 it.

 Does our CVS setup cater for seggregated rights like this? Or would that 
 be done on a trust basis?

No, and yes.  However, I don't have a problem with giving Thomas
committer access --- I'm just dubious about having PL/Java in the
core distro at all.

regards, tom lane

---(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] pgsql-patches considered harmful

2006-07-11 Thread Greg Sabino Mullane

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message



One thing that came up in the discussion here was the idea of a
weekly (or other time period) digest of patches posts, stripped
of attachments, but with a link to the patches email, which will
have both the attachment and follow-up posts for those that are
interested. Proof of concept below my sig.

--
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200607111416
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8


Weekly PostgreSQL patches summary:


http://archives.postgresql.org/pgsql-patches/2006-07/msg00018.php
From: Neil Conway [EMAIL PROTECTED]
Subject: [PATCHES] CREATE TRIGGER locking
Date: 2:25 AM on Tuesday, July 04, 2006

Last year, I questioned why CREATE TRIGGER acquires an
AccessExclusiveLock on its target table:

http://archives.postgresql.org/pgsql-hackers/2005-03/msg00764.php

Acquiring an ExclusiveLock should be sufficient: we can safely allow
concurrent SELECTs on the table. (The -hackers thread discusses both
CREATE TRIGGER and ALTER TABLE ADD FK; the latter might require some
more consideration, so I'll tackle that later.)

This patch implements this change, and updates the documentation.

Barring any objections, I'll apply this in a day or two.

-Neil


---(end of broadcast)---



http://archives.postgresql.org/pgsql-patches/2006-07/msg00021.php

From: Tom Lane [EMAIL PROTECTED]
Subject: [PATCHES] Draft patch for bug: ALTER TYPE ... USING(NULL) / NOT NULL 
violation
Date: 6:37 PM on Tuesday, July 04, 2006

Attached is a rather hurried patch for Alexander Pravking's report that
ALTER TABLE fails to check pre-existing NOT NULL constraints properly:
http://archives.postgresql.org/pgsql-bugs/2006-07/msg00015.php

It seems to work but I'm out of time to do more with it, and am leaving
for Toronto in the morning.  Anyone want to look it over, generate
back-patches as appropriate, and apply?

regards, tom lane


---(end of broadcast)---



http://archives.postgresql.org/pgsql-patches/2006-07/msg00031.php

From: Greg Stark [EMAIL PROTECTED]
Subject: [PATCHES] BTree tid operators and opclass
Date: 6:53 PM on Thursday, July 06, 2006

Here's a small patch to add the full suite of btree operators for tids and the
corresponding btree opclass. This came up a while back on -hackers and a few
people were interested in it at the time. I just had a need for it again so I
added it.

I'm not sure how to allocate OIDs. I just looked for the greatest one in the
various .h files and started from there. It leads to some strange
discontinuities since there were existing = and  operators.



--
greg

---(end of broadcast)---



http://archives.postgresql.org/pgsql-patches/2006-07/msg00035.php

From: Magnus Hagander [EMAIL PROTECTED]
Subject: [PATCHES] Win32 DEF file error
Date: 11:30 AM on Monday, July 10, 2006

The Win32 DEF files that are generated for libpq contain the attribute
DESCRIPTION, which is actually only allowed for device drivers. The
compilers ignore it with a warning - if we remove them, we get rid of
the warning.

(ref
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore/
html/_core_description.asp)

//Magnus

---(end of broadcast)---



http://archives.postgresql.org/pgsql-patches/2006-07/msg00037.php

From: Dave Page dpage@vale-housing.co.uk
Subject: [PATCHES] Minor ipv6/Win32 fix
Date: 7:42 PM on Monday, July 10, 2006

The attached patch reverses ws2tcpip.h and winsock2.h to avoid an
undefined symbol error when building under VC2k5.

Regards, Dave

---(end of broadcast)---



http://archives.postgresql.org/pgsql-patches/2006-07/msg00038.php

From: James Gates [EMAIL PROTECTED]
Subject: [PATCHES] Patch to configure to enable PostgreSQL build with 
Kerberos 5 on Solaris 11
Date: 7:50 PM on Monday, July 10, 2006

Included below are extracts from an earlier email thread (on
pgsql-ports) discussing the problem.

Attached are the context diffs for configure.in.

This change has no impact unless the --with-krb5 option is used with
configure. If the option *is* used, configure will now only search for
function krb5_sendauth(), instead of looking for both krb5_encrypt() and
krb5_sendauth().

I've tested (i.e. built using --with-krb5) with version 8.1.4 on Solaris
11 only. This change should have no negative impact for builds on other
platforms since:

a) The check for krb5_sendauth() remains, which is sufficient to
determine the presence of Kerberos 5

and

b) None of the PostgreSQL code uses krb5_encrypt() anyway


James Gates wrote:
  Prior to Solaris 11 (Nevada), the full Kerberos 5 API was never exposed
  (only the gss interface), so building PostgreSQL with the --with-krb5
  option 

Re: [HACKERS] pgsql-patches considered harmful

2006-07-11 Thread Greg Stark
Bruce Momjian [EMAIL PROTECTED] writes:

 Let's add the author and the hackers list to the reply-to.

I think reply-to is just a single address. It may work in some mailers though.

Regardless the issue is that someone may send a personal message and be
surprised when it's broadcast. You can always resent a message accidentally
sent personally but you can't unsend one that should not have seen wider
distribution.

-- 
greg


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


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Josh Berkus

Stephen,


You seemed to use the recent change in status of Sun's JVM (at least in
part with regard to Debian...) as justification of your statement that
it's OSS-compatible..


Are you going to be at OSCON?  Sun's hosting a BOF to discuss exactly 
this issue.


--Josh

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


Re: [HACKERS] More nuclear options

2006-07-11 Thread Robert Treat
On Tuesday 11 July 2006 14:05, Josh Berkus wrote:
 Robert,

  Given the current number of projects that have no code / files / anything
  associated with them on pgfoundry/gborg right now, this argument rings a
  little hollow.

 If you're so keen to add to the problem, you can have my spot as
 pgfoundry admin.


No need to fly off the handle there Josh. 

 Otherwise, the rule that the pgfoundry admins agreed on is that if a
 project had no code and no activity in 6 months we would contact the
 owner, and if no response kill it.  That we've been lagging behind on
 this is a manpower issue (and to some degree a technical issue with
 GForge).

No code, or no active code development?  Seems there is an important 
difference that plays in here...   looking a m-SQL it has code and could be a 
starting point for someone who was looking for that.  I'll grant that tips 
doesn't look like much more than an article stub... it should probably be 
moved to the new techdocs rather than pgfoundry. 


  People do get pointed at adddepends even today...  certainly no one will
  do anything with these projects if you nuke them, but I like giving
  people options...  your call though.

 I've already added adddepends to pgFoundry (as Old PG Upgrade), since
 people spoke up for it.  I will assign one of them as admin of the
 project (not sure who yet).

 However, in the last year nobody has spoken up for the other three, not
 even you.


Perhaps no one knew they needed to speak up... perhaps people couldn't even 
find them in contrib... how many people still ask if we have full text 
indexing? contrib isn't exactly the most visible place... 

All I am saying is that it couldn't hurt to put the information out there...   
we're not hurting for disk space and none of this stuff appears inherently 
wrong, just outdated, but it might still prove useful for some people.   

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

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


[HACKERS] Implied Functional Index use

2006-07-11 Thread Simon Riggs
Currently, functional indexes can be used by a query that explicitly
mentions the exact phrasing of the functional index within the WHERE
clause.

IMHO it is feasible to extend the range of WHERE clauses for which
functional indexes can be used by using implication, much in the same
way that we use transitive closure in join queries now. This has some
powerful and important uses, just one of which is index prefix
compression (so read on).

If we have a subclause within a WHERE clause of the form

col1 = k1 AND col2 = k2 AND ... colN = kN

this implies that the following clause is also true:

col1 = k1 AND col2 = k2 AND ... colN = kN
AND F(col1, col2 ... colN) = F(k1, k2 ... kN)

iff:
- the function F is IMMUTABLE, since that definition implies that the
output is always the same for any set of constant inputs.
- the operator that connects each col1,k1 pair must be defined as true
equality (note that in the above example what looks like the same
equality operator is in fact context dependent on the datatypes)
- the datatypes of each col1, k1 pair must match

If we have a Functional Index F(col1, col2...colN) then we can use the
second implied form of the subclause to recognise that the index can 
be useful for the query. This would then allow a query plan that uses an
IndexScan on the functional index with a re-check filter of the original
query clause.

An example might be a query using the clause
surname = 'RIGGS' 
would allow us to use an index defined as
metaphone(surname)
even though the original application was written before we added the
index. Note that the index would be significantly smaller than an index
on the full surname, as well as avoiding some of the hotspots caused by
certain most-frequent-values in the data.

An alternative example might be to define an index on
substr(text_col, 1, 100)
which still allows searching for longer strings without the need to
store the complete string in the index. This is effectively index prefix
compression, which is not directly possible with pgsql because of the
requirements of datatype encapsulation. (Note that this avoids the need
to have very long index keys also).

One difficulty to this is defining which operators represent
true-equality. There isn't a definition of this currently for operators
and we cannot assume that an operator called = has the required
properties, even if that is true for most built-in types. An example of
true-equality and its difficulties is for the FLOAT type minus-zero (-0)
and plus-zero (+0) have different byte representations yet when compared
with the standard FLOAT comparison operator +0 and -0 would be
considered equal. If we were to put each value through a hash-like
function such as md5() then we would clearly get different answers.

To take advantage of this, I propose to
- add code to be called from allpaths.c: when we check functional
indexes, if they exist and yet are not usable because of a lack of
explicit clauses we will check to see if there any clauses that can be
used to imply the correct use of the functional index. This is in many
ways similar to existing constraint exclusion code.
- add a new boolean to pg_operator to allow us to define which operators
offer true equality
- add a new keyword EQUALITY to CREATE/ALTER OPERATOR (which I think
implies HASHES and MERGES if they are not mentioned explicitly).

No promises for 8.2, but does anyone have further input?

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


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

   http://archives.postgresql.org


Re: [HACKERS] More nuclear options

2006-07-11 Thread Josh Berkus

Robert,

No need to fly off the handle there Josh. 


I was hoping that you'd take me up on it in a rash moment.


No code, or no active code development?  


No code was the rule we discussed.  Other stuff would be a matter for 
discussion.  The idea was that pgfoundry was supposed to be confined to 
real projects and not a repository of failed ideas.


Seems there is an important
difference that plays in here...   looking a m-SQL it has code and could be a 
starting point for someone who was looking for that.


So are you telling me you want to be responsible for it?

  I'll grant that tips
doesn't look like much more than an article stub... it should probably be 
moved to the new techdocs rather than pgfoundry. 


That was what I started to do.  Unfortunately, the README is 
instrucitons for some SQL and code files which are missing.   I don't 
see any value in Techdocs for instructions that can't be followed.



Perhaps no one knew they needed to speak up... perhaps people couldn't even 
find them in contrib... how many people still ask if we have full text 
indexing? contrib isn't exactly the most visible place... 

All I am saying is that it couldn't hurt to put the information out there...   
we're not hurting for disk space and none of this stuff appears inherently 
wrong, just outdated, but it might still prove useful for some people.   



Again, it's the same question.  If *you* want to be the maintainer, I'll 
put it on pgfoundry.  Otherwise, you're asking me to be responsible for 
the code because you don't want to throw it away.


--Josh Berkus

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

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


Re: [HACKERS] Warm-Standby using WAL archiving / Seperate

2006-07-11 Thread Simon Riggs
On Mon, 2006-07-10 at 19:34 +0200, Florian G. Pflug wrote:

 This methods seems to work, but it is neither particularly fool-proof nor
 administrator friendly. It's not possible e.g. to reboot the slave without 
 postgres
 abortint the recovery, and therefor processing all wals generated since the 
 last
 backup all over again.

Just submitted a patch to allow restartable recovery, which addresses
this concern.

 Monitoring this system is hard too, since there is no easy way to detect 
 errors
 while restoring a particular wal.

What do you mean? 

If there is an ERROR in the WAL file, it stops.
If the restore of the WAL file fails, it retries a few times before
giving up.

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


---(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] Three weeks left until feature freeze

2006-07-11 Thread Joshua D. Drake
On Tuesday 11 July 2006 13:49, Andrew Dunstan wrote:
 Joshua D. Drake wrote:
 ... and before you say it, No. I do not wear a tie.

 Maybe you need to ... ;-)


/me bows before the gods who thoust commit.


 cheers

 andrew

-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



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

   http://archives.postgresql.org


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Andrew Dunstan

Joshua D. Drake wrote:


... and before you say it, No. I do not wear a tie.

 




Maybe you need to ... ;-)


cheers

andrew

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

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


[HACKERS] newbie patch-attempt: selecting large result sets in psql

2006-07-11 Thread Chris Mair
Hi :)

powered by the great talk for newbie hackers by Neil Conway  Gavin
Sherry [1] at the anniversary summit I sneaked into the Code Sprint
and started working a bit on a Todo from Neil's Code Sprint Page:

  Add a psql option to have it submit SELECT queries via a cursor,
  to avoid excessive memory consumption when manipulating large
  result sets

Gavin gave me a sketch of a patch that added a new command line
option to psql that would have it use cursors for selects.

One of the problems with this was that a user would expect psql to
work as usual (including all format and output option stuff) and
to do this properly most of the psql output code would need to be
refactored.

Thinking about it, we had the idea to just introduce a new \ operator
that would output the results of a select (using cursors) in an
indipendent code path.  Who's selecting gigabytes of stuff into a
HTML table anyway?

So, I've introduced a new \u command that from a user perspective
is identical to \g (it executes the query buffer), just that it
uses cursors and honours only the field separator.

For example you could just do

select a, c, g
from big
\u bigout.dat

The patch is here [2], is working, but needs some performance
improvment and double checking. Which I won't do before getting
some feedback that what I'm doing does make any sense at all ;)

Bye, Chris


[1] http://neilconway.org/talks/hacking/
[2] http://www.1006.org/tmp/psql_cursor-3.patch




---(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] Three weeks left until feature freeze

2006-07-11 Thread Stephen Frost
* Josh Berkus (josh@agliodbs.com) wrote:
 I think I can tell which side of the debate you were on.

The debate was regarding Sun's JVM being distributed by Debian at all...
There wasn't any debate regarding it's free vs. non-free status so far
as I'm aware.  I don't believe there was ever any intention to include
Sun's JVM in Debian/main with it's current license.  If you meant
something other than free/non-free by 'OSS-compatible' then you might
have wanted to make that clear since generally that refers to the OSI
Open Source Definition (and/or the DFSG, they're pretty similar tho).

I've not heard 'OSS-compatible' used to refer to 'can run under Linux'
or 'can run on Debian' before.  That'd include things like Oracle.

 I'm actually rather upset to see Sun making such blatently incorrect
 statements.  Josh, I truely hope that you weren't actually involved in
 the Sun JVM-in-Debian work and so were unaware of the very important
 distinction between Distributed by Debian and in Debian/main.
 
 Keep your pants on, geez.  I'm actually rather appalled that you could 
 get so unjustifiably bent out of shape at me *after* we met.  Goes to 
 show you that not everything is improved by personal acquaintance.

Actually, I felt that I pretty clearly gave you the benefit of the
doubt...  For many people it's an unfortunate and pretty likely
assumption based on things written on /., etc.  It wasn't an attack on
you but rather the frustrated realization that the concerns of many in
Debian regarding Sun's JVM inclusion in non-free may have been justified.

 2) If you re-read my message, it says:
 ... (it's now available for Debian, for example) ...
 not ANYTHING about main or distributed or non-free or whatever.

If you hadn't intended to refer to Debian's inclusion of the Sun JVM in
non-free then I'm rather confused since there has been a trivial-to-use
package available in non-free for a long time to download the Sun JVM
from Sun and create debs to install it with.  So, it's been available
*for* Debian for a long time, but was only recently put into non-free..
It was also available prior to that, though a pain to install..

You seemed to use the recent change in status of Sun's JVM (at least in
part with regard to Debian...) as justification of your statement that
it's OSS-compatible..

That's exactly the misrepresentation which I was addressing.

 So, I think you owe me an apology.

I apologize for being harsher than I should have.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] More nuclear options

2006-07-11 Thread mark
On Tue, Jul 11, 2006 at 03:53:26PM -0400, Josh Berkus wrote:
 All I am saying is that it couldn't hurt to put the information out 
 there...   we're not hurting for disk space and none of this stuff appears 
 inherently wrong, just outdated, but it might still prove useful for some 
 people.   
 Again, it's the same question.  If *you* want to be the maintainer, I'll 
 put it on pgfoundry.  Otherwise, you're asking me to be responsible for 
 the code because you don't want to throw it away.

To present a somewhat external opinion - I've looked at pgfoundry in
the past and been both confused and disappointed. Code that doesn't
compile, with no maintainer gives me a dirty taste in my mouth, and
my inner voice says what the heck is this crap?

There are several open source projects that give me this taste. Please
help PostgreSQL not be on this list by pruning dead projects, or
poor quality projects from the public image. It's EMBARASSING! :-)

Thanks.

Cheers,
mark

-- 
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] 
__
.  .  _  ._  . .   .__.  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
   and in the darkness bind them...

   http://mark.mielke.cc/


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

   http://archives.postgresql.org


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Thomas Hallgren

Hi Hannu,

Hannu Krosing wrote:

Maybe this functionality could be lifted out of PL/Java and made
available to all PL-s ?

At least at some API level.

  
I think that what could be shared are the ideas and the semantics. The 
API's that the backend currently expose will give you what's needed to 
do the specialized implementations.


The actual PL mappings are all different because their respective 
executor is different. Some languages are typed, others are not. Some 
languages support classes and objects, others don't. Other, more 
esoteric details like the use of a garbage collector or traditional 
alloc/free semantics also affects the actual implementation.


I'm afraid there's not much in the PL/Java type system that could be 
generalized and shared. Perhaps if we had other languages with very 
similar capabilities (like C# for instance) but even then I have some 
doubts. The good news in my opinion is that if PL/Java would make it to 
the core it could make a good reference implementation for other equally 
advanced language mappings.


Regards,
Thomas Hallgren






---(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] Three weeks left until feature freeze

2006-07-11 Thread Joshua D. Drake
On Tuesday 11 July 2006 09:59, Tom Lane wrote:
 Andrew Dunstan [EMAIL PROTECTED] writes:
  Thomas Hallgren wrote:
  5. I'll need committer rights to the PL/Java part in order to maintain
  it.
 
  Does our CVS setup cater for seggregated rights like this? Or would that
  be done on a trust basis?

 No, and yes.  However, I don't have a problem with giving Thomas
 committer access --- I'm just dubious about having PL/Java in the
 core distro at all.

Personally I would like to see in core, but it is not from a technical 
perspective. It is purely from a marketing perspective (doubt that carries 
much weight here ;)).

Having pl/Java helps PostgreSQL in the minds of all those tie wearing decision 
making freaks... and before you say it, No. I do not wear a tie.

Sincerely,

Joshua D. Drake




   regards, tom lane

 ---(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

-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(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] pgsql-patches considered harmful

2006-07-11 Thread Marc G. Fournier

On Tue, 11 Jul 2006, Bruce Momjian wrote:


Andrew Dunstan wrote:

Marc G. Fournier wrote:


If this is chosen as the preferred path, we could get the list bot to
add Reply-To: pghackers in pgsql-patches postings to help push
discussions there.  I'd vote for doing the same in pgsql-committers,
which also gets its share of non-null discussion content.


that is a very easy and quick change ... but wasn't doing that brought
up before and alot of ppl were against that?

If nobody objects within, say, the next 24 hours ... ?  I'll enabled
that one both ...



Don't be surprised if there are objections - this is one of those things
like emacs vs vi that stirs up religious debate.


If we change Reply-To:, does it prevent replies to the original author?
If so, that seems like a problem, particularly if they are not
subscribed to the patches list.


The Reply-To: header is added to other heads ... in Pine, at least, I have 
the option to honor, or disregard, the Reply-To ... I generally honor it, 
but there is nothing stop'ng someone from disregarding it, and sending to 
the original poster ...



Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED]  MSN . [EMAIL PROTECTED]
Yahoo . yscrappy   Skype: hub.orgICQ . 7615664

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


Re: [HACKERS] More nuclear options

2006-07-11 Thread Josh Berkus

Robert,

I really don't see how this will actually cause you any extra effort, but if 
you want to plug my name on there after you move it, that's fine with me.  



I meant maintain it, not just leave it there to age like a bad cheese. 
 If it's going to be dead code, it can do so in the FTP /old section 
and the CVS archives easily enough.


--Josh

---(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] Three weeks left until feature freeze

2006-07-11 Thread Marc G. Fournier

On Tue, 11 Jul 2006, Josh Berkus wrote:

I disagree.  One of the things I'm asked by every single tech market 
analyst, after replication  clustering, is whether we have support for 
procedural Java.  So it's something large-scale users want.  If PL/Tcl 
belongs in the back end, then so does PL/Java.


There should be a Procedural Language section on pgfoundry for all of the 
PLs, IMHO, and a README in contrib within core that points to it 
(README.procedural_languages, if nothing else) ...



Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED]  MSN . [EMAIL PROTECTED]
Yahoo . yscrappy   Skype: hub.orgICQ . 7615664

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

  http://archives.postgresql.org


Re: [HACKERS] Implied Functional Index use

2006-07-11 Thread Tom Lane
Simon Riggs [EMAIL PROTECTED] writes:
 ...
 - add a new boolean to pg_operator to allow us to define which operators
 offer true equality
 ...

This would be useful for other purposes too, as we keep coming up
against what's the equality operator for this datatype problems.
However, the restriction to true equality, such that we can assume
x = y implies f(x) = f(y) for every immutable function f on the datatype
(note this need not necessarily mean bitwise equality --- it depends on
what operations the datatype provides), seems like a problem.  For
instance, the ordinary = operators on float and numeric are NOT true
equality, nor do we provide any true equality in this sense for these
common datatypes.  We could hardly get away with using this concept
to drive foreign-key comparisons, if it doesn't work for float or
numeric.

We could invent some more-complex concept involving well, this is
equality, but there are some functions for which f(x) might differ
from f(y) anyway and then mark the presumably-few functions that
could produce divergent results --- examples are sgn() for float8
and anything dependent on dscale for numeric.  This seems ugly and
error prone however.

Anyone have a better idea?

regards, tom lane

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

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


Re: [HACKERS] More nuclear options

2006-07-11 Thread Robert Treat
On Tuesday 11 July 2006 16:33, Josh Berkus wrote:
 Robert,

  I really don't see how this will actually cause you any extra effort, but
  if you want to plug my name on there after you move it, that's fine with
  me.

 I meant maintain it, not just leave it there to age like a bad cheese.
   If it's going to be dead code, it can do so in the FTP /old section
 and the CVS archives easily enough.


I'm not going to actively maintain it, my intrest is just in exposing the 
information so that others might find it.  Putting it on foundry gives it a 
chance at finding an active maintainer... leaving it in the archives of CVS 
will guarantee you don't get one.  Since most people seem comfortable with 
that, so be it. 

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

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


Re: [HACKERS] Warm-Standby using WAL archiving / Seperate

2006-07-11 Thread Hannu Krosing
Ühel kenal päeval, T, 2006-07-11 kell 08:38, kirjutas Andrew Rawnsley:
 Just having a standby mode that survived shutdown/startup would be a nice
 start...

I think that Simon Riggs did some work on this at the code sprint
yesterday.

 I also do the blocking-restore-command technique, which although workable,
 has a bit of a house-of-cards feel to it sometimes.
 

-- 

Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com



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

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


Re: [HACKERS] pgsql-patches considered harmful

2006-07-11 Thread David Fetter
On Tue, Jul 11, 2006 at 06:28:31PM -, Greg Sabino Mullane wrote:
 
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1
 NotDashEscaped: You need GnuPG to verify this message
 
 One thing that came up in the discussion here was the idea of a
 weekly (or other time period) digest of patches posts, stripped of
 attachments, but with a link to the patches email, which will have
 both the attachment and follow-up posts for those that are
 interested. Proof of concept below my sig.

I've done a little bit of this in the form of short summaries in the
Weekly News, and I'd be delighted to do more of it.  I'd need some
help, though :)

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

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


Re: [HACKERS] GIN index creation extremely slow ?

2006-07-11 Thread Teodor Sigaev

Try again, today's patch solves the problem.

Stefan Kaltenbrunner wrote:

on IRC somebody mentioned that it took 34h to greate a GIN index (on a
tsvector) on a ~3 Million column table (wikipedia dump) with a
reasonable speced box (AMD 3400+).
After getting hold of a dump of said table (around 4,1GB in size) I
managed to get the following timings:

test=# CREATE INDEX idxFTI_idx ON wikipedia USING gist(vector);
CREATE INDEX
Time: 416122.896 ms

so about 7 minutes - sounds very reasonable

test=# CREATE INDEX idxFTI2_idx ON wikipedia USING gin(vector);
CREATE INDEX
Time: 52681605.101 ms

ouch - that makes for a whoppy 14,6hours(!). During that time the box is
completely CPU bottlenecked and during virtually no IO at all - (varing
maintainance_work_mem does not seem to make any noticable difference).

That box is a fast Dual Opteron 2.6Ghz with 8GB RAM and a 4 disk RAID10
for the WAL and 12 disks for the data running a very recent -HEAD
checkout ...

It looks like we still don't have any docs for GIN in the tree so I
don't know if those timings are expected or not ...


Stefan

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


--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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


Re: [HACKERS] Max size of a btree index entry

2006-07-11 Thread Hannu Krosing
Ühel kenal päeval, T, 2006-07-11 kell 10:46, kirjutas Josh Berkus:
 Tom,
 
  Obviously a tree containing many such pages would be awfully inefficient
  to search, but I think a more common case is that there are a few wide
  entries in an index of mostly short entries, and so pushing the hard
  limit up a little would add some flexibility with little performance
  cost in real-world cases.
  
  Have I missed something?  Is this worth changing?
 
 Not sure.  I don't know that the difference between 2.7K and 3.9K would 
 have ever made a difference to me in any real-world case.

One (hopefully) soon-to-be real-world case is index-only queries.

We discussed one approach with Luke and he expressed interest in getting
actually done in not too distant future.

 If we're going to tinker with this code, it would be far more valuable 
 to automatically truncate b-tree entries at, say, 1K so that they could 
 be efficiently indexed.

That would not work, if we want to get all data from indexes.

Maybe compressing the keys (like we do for TOAST) would be a better
solution.

 Of course, a quick archives search of -SQL, -Newbie and -General would 
 indicate how popular of an issue this is.

It may become populat again, when we will be able to do index-only
scans. 

-- 

Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com


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

   http://archives.postgresql.org


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Hannu Krosing
Ühel kenal päeval, T, 2006-07-11 kell 17:34, kirjutas Thomas Hallgren:
 Josh Berkus wrote:
  Tom,
 

  IOW pljava is (already) bigger than the other four PLs put together.
 
  That is odd.  Thomas?
 
 It's not that odd really:
 
 1. the mapping is strongly typed, i.e. each scalar type in PostgreSQL 
 has a set of functions that maps it to the correct primitive in Java 
 (int4 is a java int, double precision is a double etc.). PL/Java will 
 resort to string coercion only when no other option is left.
 2. a type mapping is provided for *all* types. Scalar, composite, 
 pseudo, array types, and result sets.
 3. new Java mappings can be created on the fly. Both for scalar and 
 composite types.
 4. you can create new scalar types in PostgreSQL that uses IO functions 
 written in Java.

Maybe this functionality could be lifted out of PL/Java and made
available to all PL-s ?

At least at some API level.

-- 

Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.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] More nuclear options

2006-07-11 Thread Hannu Krosing
Ühel kenal päeval, T, 2006-07-11 kell 14:05, kirjutas Josh Berkus:
 Robert,
 
  Given the current number of projects that have no code / files / anything 
  associated with them on pgfoundry/gborg right now, this argument rings a 
  little hollow. 
 
 If you're so keen to add to the problem, you can have my spot as 
 pgfoundry admin

Why not just make *one* project, called DeadProjects and keep one
tarball + one README.TXT per directory under it, so that in the unlikely
event that someone (pg_necromancer ?) does want to resurrect a dead
project he/she/it has a place to get the code from.

-- 

Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com




---(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] More nuclear options

2006-07-11 Thread Joshua D. Drake

 Given the current number of projects that have no code / files / anything
 associated with them on pgfoundry/gborg right now, this argument rings a
 little hollow.

I would say that:

 Given the current number of projects that have no code / files / anything
 associated with them on pgfoundry/gborg right now, this argument rings a
 little hollow.

Strengthens JoshB's argument, not lessens it. That is also an argument for 
Gforge admins, not hackers :)


  In a year nobody has spoken up for those specific projects.   Who's
  going to maintain them?  Who's going to use them?

 People do get pointed at adddepends even today...  certainly no one will do
 anything with these projects if you nuke them, but I like giving people
 options...  your call though.

They will always be able to pull down the source from a previous release.

Sincerely,

Joshua D. Drake



-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



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

   http://archives.postgresql.org


Re: [HACKERS] pgsql-patches considered harmful

2006-07-11 Thread Marc G. Fournier

On Tue, 11 Jul 2006, Greg Stark wrote:

I have the additional complaint that this doesn't actually solve most of 
my original complaints and might reduce the pressure to find a better 
solution. The patches announcements themselves would still be basically 
invisible within the community.


How do you deal with the case where someone posts a patch, but it isn't an 
attachment?  Its part of the actual text?



Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED]  MSN . [EMAIL PROTECTED]
Yahoo . yscrappy   Skype: hub.orgICQ . 7615664

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

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


Re: [HACKERS] More nuclear options

2006-07-11 Thread Robert Treat
On Tuesday 11 July 2006 15:53, Josh Berkus wrote:
I'll grant that tips
  doesn't look like much more than an article stub... it should probably be
  moved to the new techdocs rather than pgfoundry.

 That was what I started to do.  Unfortunately, the README is
 instrucitons for some SQL and code files which are missing.   I don't
 see any value in Techdocs for instructions that can't be followed.


The information for the sql / code is embedded within the readme. It probably 
should be broken out into multiple files.  That might make it project worthy 
rather than article worthy. 

  Perhaps no one knew they needed to speak up... perhaps people couldn't
  even find them in contrib... how many people still ask if we have full
  text indexing? contrib isn't exactly the most visible place...
 
  All I am saying is that it couldn't hurt to put the information out
  there... we're not hurting for disk space and none of this stuff appears
  inherently wrong, just outdated, but it might still prove useful for some
  people.

 Again, it's the same question.  If *you* want to be the maintainer, I'll
 put it on pgfoundry.  Otherwise, you're asking me to be responsible for
 the code because you don't want to throw it away.


I really don't see how this will actually cause you any extra effort, but if 
you want to plug my name on there after you move it, that's fine with me.  

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

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


Re: [HACKERS] lastval exposes information that currval does not

2006-07-11 Thread Jan Wieck

On 7/9/2006 8:32 AM, Martijn van Oosterhout wrote:


On Sat, Jul 08, 2006 at 05:47:33PM -0400, Jim Nasby wrote:

On Jul 6, 2006, at 11:02 AM, Phil Frost wrote:
I hope the above example is strong enough to elicit a comment from a
qualified developer. If it is not, consider that stored procedures
contain prepared statements, and many client applications cache  
prepared
statements as well. Thus, revoking usage on a schema is about as  
good as

nothing until all sessions have ended. It also means that any function
which operates with OIDs can potentially bypass the schema usage  
check.


The docs probably should elaborate that once something's been looked  
up you no longer need permissions on the schema it resides in.


I'm not sure this is really unexpected behaviour. On UNIX it is clearly
defined that file permissions are checked only on open. Once you've
opened it, changing permissions on the file won't affect you. If
someone passes you a read/write descriptor to a file, you can
read/write it even if you didn't have permissions to open the
file/socket/whatever yourself.


This isn't the case and I do agree with Phil on this. The fact that 
another security definer function did access an object during the 
session should not give the user the ability to access it in the manner 
shown in his example. lastval() without arguments should not remember 
the sequence by its oid only, but also remember the sequences schema and 
to a proper ACL check on that as well.


Just think of it if SELECT without a FROM clause would automatically 
assume the same rangetable as the last SELECT in the session. If that 
were the case, would you guy's defend the position that SELECT * then 
should spit out the full content of the last table accessed by the 
security definer function just called, even if the user doesn't have 
schema permission? I doubt!



Jan



I'm not sure it makes sense to be able to revoke someone's permissions
on an object they've already accessed. From a transactional point of
view, the revoke should at the very least not affect transactions
started prior to the revokation. Some things are shared across an
entire session, and the rule extends to them. Is this a bug? Maybe, but
it is debatable.

Have a nice day,



--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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


Re: [HACKERS] poor performance with Context Switch Storm at TPC-W.

2006-07-11 Thread Katsuhiko Okano
hello.


 Do you have bgwriter on and what's the parameters? I read a theory somewhere
 that bgwriter scan a large portion of memory and cause L1/L2 thrushing, so
 with HT on, the other backends sharing the physical processor with it also
 get thrashed ... So try to turn bgwriter off or turn HT off see what's the
 difference.

bgwriter is ON.
at postgresql.conf:
 # - Background writer -
 
 bgwriter_delay = 200  # 10-1 milliseconds between rounds
 bgwriter_lru_percent = 1.0# 0-100% of LRU buffers scanned/round
 bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
 bgwriter_all_percent = 0.333  # 0-100% of all buffers scanned/round
 bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round


I tried turn H/T OFF, but CSStorm occurred.
Usually, CS is about 5000.
when CSStrom occurrence, CS is about 7.
(CS is a value smaller than the case where H/T is ON.
I think that it is because the performance of CPU fell.)


Regards

Katsuhiko Okano
okano katsuhiko _at_ oss ntt co jp

---(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] More nuclear options

2006-07-11 Thread Christopher Kings-Lynne
I've already added adddepends to pgFoundry (as Old PG Upgrade), since 
people spoke up for it.  I will assign one of them as admin of the 
project (not sure who yet).


How is addepends in any way old pg upgrade??


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


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Christopher Kings-Lynne

No, I don't believe you can do this with CVS at all. We'd need something
like SVN/WebDAV to be able to grant write access just to specific parts
of the tree to different people.


You just use an on-commit script like cvsacl.


---(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] More nuclear options

2006-07-11 Thread Joshua D. Drake
 Again, it's the same question.  If *you* want to be the maintainer, I'll
 put it on pgfoundry.  Otherwise, you're asking me to be responsible for
 the code because you don't want to throw it away.

Josh, 

How about a general call for maintainers? Post it to general, hackers and 
advocacy (maybe even the front of PgFoundry and or PostgreSQL.Org?) that 
asks if anyone would like to take over maintainership of the handful?

Have a closing date for it, e.g; leave it open for a week and then if no one 
steps up --- its over and we nuke them with prejudice.

Sincerely,

Joshua D. Drake



 --Josh Berkus

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

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

-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.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


[HACKERS] pre_load_libraries

2006-07-11 Thread Marc Munro
I am trying to create an initialisation function that is called using
the  preload_libraries option.

The purpose of this is to set up shared memory for Veil, independant of
postgres' own shared memory.  Simple init functions work fine, but as
soon as I place calls to ShemAlloc, or LWLockAssign, the server startup
simply halts.

Am I being unreasonable in trying to call these functions at this point
of the server startup, or is this just some stupid bug in my code?

I wish to call ShmemAlloc in order to simply create a shared reference
to the Veil shared memory segments that I will set up separately.

Thanks
__
Marc


signature.asc
Description: This is a digitally signed message part


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Joshua D. Drake

 I'm afraid there's not much in the PL/Java type system that could be
 generalized and shared. Perhaps if we had other languages with very
 similar capabilities (like C# for instance) but even then I have some
 doubts. The good news in my opinion is that if PL/Java would make it to
 the core it could make a good reference implementation for other equally
 advanced language mappings.

What is the actual concern with having PL/Java in core, versus say PL/Perl? 

Sincerely,

Joshua D. Drake



 Regards,
 Thomas Hallgren






 ---(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

-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



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

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


Re: [HACKERS] row() is [not] null infelicities

2006-07-11 Thread Joe Conway

Tom Lane wrote:

Greg Stark [EMAIL PROTECTED] writes:


The SQL spec has some detailed discussion of some strange null behaviours.


BTW, Teodor Sigaev pointed out today that we are also doing array
comparisons (array_eq, array_cmp) wrong.


Seems to me like at least array_eq is correct (from SQL2003):

4.10 Collection types
Let A1 and A2 be arrays of EDT. A1 and A2 are identical if and only if
A1 and A2 have the same cardinality n and if, for all i in the range 1
(one) ≤ i ≤ n, the element at ordinal position i in A1 is *identical* to
the element at ordinal position i in A2.

9.8 Determination of identical values
Function
Determine whether two instances of values are identical, that is to say, 
are occurrences of the same value.

General Rules
1) Let V1 and V2 be two values specified in an application of this 
Subclause.
NOTE 211 — This Subclause is invoked implicitly wherever the word 
identical is used of two values.

2) Case:
a) If V1 and V2 are both null, then V1 is *identical* to V2.


regression=# select array[1,null,3] = array[1,null,3];
 ?column?
--
 t
(1 row)


In the recent extension to make arrays support NULL entries, I had made these 
functions
treat NULL as greater than all non-nulls, per btree sort order.
But this seems wrong and also counter to spec: if an array comparison
finds a NULL before determining its result, it should return NULL,
same as a row comparison would do.  The problem with this is that it
breaks btree indexing of array columns (... and I think btree indexing
of rowtypes has a problem too ...).  btree wants to have a well-defined
ordering of any two non-null values.  Ideas?


Interestingly, I see this in SQL2003:

9.12 Ordering operations
Function
Specify the prohibitions and restrictions by data type on operations 
that involve ordering of data.

[...]
3) The declared type of an operand of an ordering operation shall not be
LOB-ordered, array-ordered, multisetordered, reference-ordered,
UDT-EC-ordered, or UDT-NC-ordered.


4.1.4 Comparison and ordering
[...]
— T is a collection type and the element type of T is S-ordered.
[...]
The notion of S-ordered is applied in the following definitions:
[...]
— A type T is array-ordered if T is ARR-ordered, where ARR is the set of 
array types.


Does that say arrays can't be ordered? Or does it say that the ordering 
follows the say rules as the array element type? If it is the latter, 
aren't we already doing the right thing?


regression=# (select 1 union all select null union all select 3) order by 1;
 ?column?
--
1
3

(3 rows)

regression=# (select array[1,1] union all select array[1,null] union all 
select array[1,3]) order by 1;

  array
--
 {1,1}
 {1,3}
 {1,NULL}
(3 rows)



A nearby issue is that the spec seems to want IS [NOT] DISTINCT FROM
to drill down into array and row values, ie, comparing arrays with
these functions needs to consider null entries as comparable instead
of forcing a null result.  AFAICS this will require special-casing
array and row types in IS [NOT] DISTINCT FROM ... anyone see a better
way?


Yup, that's the way I read it too. Of course, that seems to work too:

regression=# select array[1,null,3] is distinct from array[1,null,3];
 ?column?
--
 f
(1 row)

regression=# select array[1,null,3] is not distinct from array[1,null,3];
 ?column?
--
 t
(1 row)

Hmm, did I miss a commit message since you posted this?

Joe

---(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] [PATCHES] putting CHECK_FOR_INTERRUPTS in qsort_comparetup()

2006-07-11 Thread Greg Stark

Charles Duffy [EMAIL PROTECTED] writes:

 Their work_mem setting was rather large (100). We determined that when it
 received SIGINT, the backend was always inside qsort(), so it wouldn't
 call ProcessInterrupts() again until it finished this large in-memory
 sort. Upon entering tuplesort_performsort(), state-memtupcount was
 29247.

It occurs to me that this kind of thing is something dtrace could help with.
It might even be able to do something clever like time between consecutive
CHECK_FOR_INTERRUPT calls grouped by the function that postgres spent the most
time in between those points. If not that then something like grouped by the
first function call in the intervening period is probably pretty
straightforward.

Of course this is complicated by CHECK_FOR_INTERRUPTS being a macro... perhaps
a probe could be added in that macro. In fact I suspect many of the locations
we'll need manually added probes will be macros.

-- 
greg


---(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] passing parameters to CREATE INDEX

2006-07-11 Thread ITAGAKI Takahiro
Teodor Sigaev [EMAIL PROTECTED] wrote:

 pluggable parameters for index.
 I think, we may 
 can add to pg_opclass's definition method/parameter name and create some API 
 (may be, index specific) to propagate parameter's to module's interface 
 functions to index.

How abount adding a new option hander to GiST/GIN support functions?

Presently, amoptions() are defined as
   bytea *amoptions (ArrayType *reloptions, bool validate).
If there is a support function like this form in operator classes,
we can propagate options. Eventually, calling sequence will be
  DefineIndex() - amoptions() - tsvectoroptions().

We also need to change gist/ginoptions() in order to determine what operator
class is used. This is a bit messy problem. Options are parsed before support
functions are initialized, so that I don't know in what form we should pass the
operator class to amoptions().


Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center



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


Re: [HACKERS] newbie patch-attempt: selecting large result sets in

2006-07-11 Thread Neil Conway
On Tue, 2006-07-11 at 21:19 +0200, Chris Mair wrote:
 One of the problems with this was that a user would expect psql to
 work as usual (including all format and output option stuff) and
 to do this properly most of the psql output code would need to be
 refactored.

Even if the refactoring were done, I think having a separate interface
would be a good idea, because it makes it more obvious that queries
submitted via cursors behave differently (e.g. in the case of a network
failure in the midst of executing the query).

 The patch is here [2], is working, but needs some performance
 improvment and double checking.

BTW, the patch leaks the content of buf (you need to
termPQExpBuffer()).

-Neil



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


Re: [HACKERS] Three weeks left until feature freeze

2006-07-11 Thread Alvaro Herrera
Marc G. Fournier wrote:
 On Tue, 11 Jul 2006, Josh Berkus wrote:
 
 I disagree.  One of the things I'm asked by every single tech market 
 analyst, after replication  clustering, is whether we have support for 
 procedural Java.  So it's something large-scale users want.  If PL/Tcl 
 belongs in the back end, then so does PL/Java.
 
 There should be a Procedural Language section on pgfoundry for all of the 
 PLs, IMHO, and a README in contrib within core that points to it 
 (README.procedural_languages, if nothing else) ...

This is not a smart idea, because it will mean API changes in the
backend cannot fix the PLs right away (and also because it's harder to
track each core branch, something which having the actual language in
the same repo makes trivial AFAIK).  I don't understand why you keep
proposing this after having the idea shot down over and over.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(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