[HACKERS] Order by optimisations?

2005-07-13 Thread Christopher Kings-Lynne
Hi, Does PostgreSQL do the following optimisation: SELECT * FROM diary WHERE date = '2005-05-01' ORDER BY date; or in fact even better (for my situation) SELECT * FROM diary WHERE date BETWEEN '2005-05-01' AND '2005-05-01' ORDER BY date; Does it know that the input to the sort routine is

Re: [HACKERS] Vacuum summary?

2005-07-13 Thread Simon Riggs
On Tue, 2005-07-12 at 14:56 -0700, Joshua D. Drake wrote: It'd be relatively easy I think to extract the current FSM statistics in a function that could be invoked separately from VACUUM. Not sure how we ought to return 'em though --- the VACUUM way of a bunch of INFO messages is a bit

[HACKERS] A Guide to Constraint Exclusion (Partitioning)

2005-07-13 Thread Simon Riggs
A Guide to the Constraint Exclusion Feature === Simon Riggs 2ndQuadrant [EMAIL PROTECTED] INTRODUCTION Constraint Exclusion (CE) is an optimizer patch submitted for PostgreSQL 8.1. CE aims to greatly improve the performance for certain types of

Re: [HACKERS] suspicious pointer/integer coersion

2005-07-13 Thread Andrew Dunstan
This seems to have gone AWOL in the email ether, so I am resending. Original Message Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I have just noticed this code in plperl.c: hv_store(plperl_proc_hash, internal_proname, proname_len,

Re: [HACKERS] A Guide to Constraint Exclusion (Partitioning)

2005-07-13 Thread Richard Huxton
Sounds very useful - even for my small systems. Does it/would it work on an ordinary table (for those cases currently using UNION ALL)? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space

[HACKERS] SQLException: Cannot be less than zero ( BUG ? )

2005-07-13 Thread xavier.marquis
Hi, I use PostgreSQL 8.0.3 and following associated drivers : postgresql-8.0-311.jdbc2.jar postgresql-8.0-311.jdbc2ee.jar postgresql-8.0-311.jdbc3.jar I want to use CachedRowSetImpl to populate one row in thefollowing table : CREATE TABLE "Form2"(

Re: [PATCHES] [HACKERS] HEAD doesn't cope with libraries in non-default

2005-07-13 Thread Andrew Dunstan
This patch seems to have broken builds on Windows and other boxes (e.g. buildfarm's octopus, a FreeBSD box). Maybe this should be reverted until we find a more robust solution :-( cheers andrew Bruce Momjian wrote: Patch applied. Thanks.

Re: [HACKERS] A Guide to Constraint Exclusion (Partitioning)

2005-07-13 Thread Simon Riggs
On Wed, 2005-07-13 at 14:02 +0100, Richard Huxton wrote: Sounds very useful - even for my small systems. Does it/would it work on an ordinary table (for those cases currently using UNION ALL)? I'm looking into that aspect right now. I see no reason why it shouldn't work for UNION ALL

Re: [PATCHES] [HACKERS] HEAD doesn't cope with libraries in non-default

2005-07-13 Thread Tom Lane
Andrew Dunstan [EMAIL PROTECTED] writes: This patch seems to have broken builds on Windows and other boxes (e.g. buildfarm's octopus, a FreeBSD box). Maybe this should be reverted until we find a more robust solution :-( The only thing I see any evidence for is a broken version of gmake on

Re: [HACKERS] SQL99 - Nested Tables

2005-07-13 Thread Affan Salman
On 7/7/05, Darren Alcorn [EMAIL PROTECTED] wrote: I was interested as to if there were plans to develop SQL99 nested tables. I know with AJAX(tm) starting to grow in popularity that the XML features of SQL2003 would prove useful for EnterpriseDB. Although this discussion seems to be heading

Re: [HACKERS] SQL99 - Nested Tables

2005-07-13 Thread Affan Salman
On 7/7/05, David Fetter [EMAIL PROTECTED] wrote: It would need to be a fairly strong one, given that they basically violate the relational model. If what you're really interested in is XML data support, then I'd suggest focusing on that instead. XML data support would be best if

Re: [PATCHES] [HACKERS] HEAD doesn't cope with libraries in non-default

2005-07-13 Thread Andrew Dunstan
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: This patch seems to have broken builds on Windows and other boxes (e.g. buildfarm's octopus, a FreeBSD box). Maybe this should be reverted until we find a more robust solution :-( The only thing I see any evidence for is a

Re: [PATCHES] [HACKERS] HEAD doesn't cope with libraries in non-default

2005-07-13 Thread Tom Lane
Andrew Dunstan [EMAIL PROTECTED] writes: which looks very odd indeed, especially: -L -L../../../src/backend -L../../../src/port -L/c/tcl/lib C:/Perl/lib/CORE -lperl58 Ah, I see the problem: ifeq ($(PORTNAME), win32) perl_archlibexp := $(subst \,/,$(perl_archlibexp)) perl_privlibexp :=

Re: [PATCHES] [HACKERS] HEAD doesn't cope with libraries in non-default

2005-07-13 Thread Andrew Dunstan
Jim C. Nasby wrote: Turns out there was a cvs conflict. Doh! Ouch. I have repeatedly warned buildfarm owners not to make any changes or run builds in buildfarm's local CVS repo. Use a copy if necessary. Hmm... would probably be a good idea to have the script check for conflicts and

Re: [HACKERS] SQL99 - Nested Tables

2005-07-13 Thread Oleg Bartunov
On Wed, 13 Jul 2005, Affan Salman wrote: On 7/7/05, Darren Alcorn [EMAIL PROTECTED] wrote: I was interested as to if there were plans to develop SQL99 nested tables. I know with AJAX(tm) starting to grow in popularity that the XML features of SQL2003 would prove useful for EnterpriseDB.

[HACKERS] full_page_writes = on

2005-07-13 Thread Joshua D. Drake
Hello, Will this be an option when 8.1 releases? I know there has been some *ahem* disagreement. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and

[HACKERS] commit_delay and commit_siblings

2005-07-13 Thread Joshua D. Drake
Hello, I heard mention from Berkus that these may be going by the wayside. Any truth to that? Sincerely, Joshua D. Drake -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and

[HACKERS] pthread stack on FreeBSD WAS: HEAD doesn't cope with libraries in non-default

2005-07-13 Thread Jim C. Nasby
On Wed, Jul 13, 2005 at 01:24:17PM -0400, Andrew Dunstan wrote: In any case, I've cleared the conflict and I'm running a build right now. octopus is building again, and is back to the behavior I mentioned in http://archives.postgresql.org/pgsql-bugs/2005-07/msg00096.php. Is this something that

Re: [PATCHES] [HACKERS] HEAD doesn't cope with libraries in non-default

2005-07-13 Thread Andrew Dunstan
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: which looks very odd indeed, especially: -L -L../../../src/backend -L../../../src/port -L/c/tcl/lib C:/Perl/lib/CORE -lperl58 Ah, I see the problem: ifeq ($(PORTNAME), win32) perl_archlibexp := $(subst

Re: [HACKERS] windows regression failure - prepared xacts

2005-07-13 Thread Andrew Dunstan
I never got a reply to this, but I am still seeing it from time to time - twice today in fact. Any suggestions? cheers andrew Andrew Dunstan wrote: I am consistently seeing the regression failure shown below on my Windows machine. See

[HACKERS] checkpoint_segments 32 megs?

2005-07-13 Thread Joshua D. Drake
Hello, Tom made mention earlier this week that upping the checkpoint segments might be a bad idea because we are committing someone to 32 megs per segment. I thought it odd at the time but I figured it was an 8.1 thing. As I review the 8.1dev postgresql.conf the checkpoint_segments option still

Re: [HACKERS] commit_delay and commit_siblings

2005-07-13 Thread Tom Lane
Joshua D. Drake [EMAIL PROTECTED] writes: I heard mention from Berkus that these may be going by the wayside. Any truth to that? Tatsuo put up some benchmark numbers showing that they can be of use, so unless somebody refutes those results, I guess they stay. (I have doubts about the results

Re: [HACKERS] checkpoint_segments 32 megs?

2005-07-13 Thread Alvaro Herrera
On Wed, Jul 13, 2005 at 12:53:57PM -0700, Joshua D. Drake wrote: Tom made mention earlier this week that upping the checkpoint segments might be a bad idea because we are committing someone to 32 megs per segment. I thought it odd at the time but I figured it was an 8.1 thing. As I review

[HACKERS] Determine index's attribute number by scankey

2005-07-13 Thread Victor Yegorov
Hello. Is it possible to somehow determine index's attribute number that is target one for given scankey? I've checked nbtree AM code and found no evidence of such an ability. I need that, because I'm storing each indexed value only once in a form of index tuple, consisting of only 1 attribute.

Re: [HACKERS] windows regression failure - prepared xacts

2005-07-13 Thread Tom Lane
Andrew Dunstan [EMAIL PROTECTED] writes: I never got a reply to this, but I am still seeing it from time to time - twice today in fact. Any suggestions? I've been puzzled by that too. It seems to indicate that the syscache inval message that the COMMIT should send is either not getting sent

Re: [HACKERS] checkpoint_segments 32 megs?

2005-07-13 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes: The point is that in general, the 16 MiB figure is correct, but in pathological cases there can be up to two WAL segments on disk per checkpoint_segment, so 32 MiB. The reason for this is that after a checkpoint finishes, we recycle WAL files up to the

Re: [HACKERS] Determine index's attribute number by scankey

2005-07-13 Thread Tom Lane
Victor Yegorov [EMAIL PROTECTED] writes: Is it possible to somehow determine index's attribute number that is target one for given scankey? sk_attno? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to

Re: [HACKERS] checkpoint_segments 32 megs?

2005-07-13 Thread Simon Riggs
On Wed, 2005-07-13 at 17:57 -0400, Tom Lane wrote: I'm not certain how important that really is; it was part of Vadim's original design for WAL and no one ever particularly questioned it. Anybody setting checkpoint_segments high is likely to have a dedicated WAL disk anyway, which easily gives

Re: [HACKERS] windows regression failure - prepared xacts

2005-07-13 Thread Andrew Dunstan
further (anecdotal) data point: I have usually seen this after doing a number of builds. Rebooting seems to cure the problem (and that's happened today agin - I have just seen 2 builds work). Maybe some sort of strange shmem corruption? cheers andrew Tom Lane wrote: Andrew Dunstan

Re: [HACKERS] windows regression failure - prepared xacts

2005-07-13 Thread Tom Lane
Andrew Dunstan [EMAIL PROTECTED] writes: further (anecdotal) data point: I have usually seen this after doing a number of builds. Rebooting seems to cure the problem (and that's happened today agin - I have just seen 2 builds work). Maybe some sort of strange shmem corruption? Hmmm ...

[HACKERS] win32 _dosmaperr()

2005-07-13 Thread Qingqing Zhou
There were several reports of unable to read/write on Pg8.0.x win32 port: http://archives.postgresql.org/pgsql-bugs/2005-02/msg00181.php I encounter this several times and finally I catch the GetLastError() number. It is 32, ERROR_SHARING_VIOLATION The process cannot access the file

[HACKERS] Toward pg_upgrade

2005-07-13 Thread David Fetter
Folks, I'm sure I'm not the first to bring up this way of doing pg_upgrade, but perhaps I can help seed a fruitful discussion on the matter. As background, I'd like to go over our policy of, The code patch must be accompanied by any doc patches that it implies. I believe that this policy is

[HACKERS] test

2005-07-13 Thread David Fetter
My last one appears to have vanished. Did this get through? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 1: if posting/reading

Re: [HACKERS] Toward pg_upgrade

2005-07-13 Thread Neil Conway
David Fetter wrote: As background, I'd like to go over our policy of, The code patch must be accompanied by any doc patches that it implies. Although it is worth noting this policy is not religiously followed anyway (e.g. the recent roles patch). I think we basically assume that the person