Re: [HACKERS] Planning aggregates which require sorted or distinct

Hi, Tom Lane wrote: select empno, rank() over (order by salary) as srank, rank() over (order by age) as arank from employees order by empno; Eeek. This seems like the worst sort of action-at-a-distance. How does rank() know what value it's supposed to report the rank of? All of these r

Re: [HACKERS] Planning aggregates which require sorted or distinct

On Sat, 2007-01-20 at 09:34 +1100, Gavin Sherry wrote: > On Sat, 19 Jan 2007, Karen Hill wrote: > > > Gavin Sherry wrote: > > > Recenly, I've been researching and putting together a proposal for window > > > functions. Good news. > > Implementing NULLS FIRST and NULLS LAST appears like another >

Re: [HACKERS] Planning aggregates which require sorted or distinct

On Sat, 2007-01-20 at 15:58 +1100, Gavin Sherry wrote: > On Fri, 19 Jan 2007, Tom Lane wrote: > > > Gavin Sherry <[EMAIL PROTECTED]> writes: > > > On Fri, 19 Jan 2007, Tom Lane wrote: > > >> Er, what primary key would that be exactly? And even if you had a key, > > >> I wouldn't call joining on i

Re: [HACKERS] Planning aggregates which require sorted or distinct

Simon Riggs wrote: > I'm working on modifying Tuplestore that will allow you to store the > last N tuples, rather than all previous input. This is specifically > designed to allow Merge Join to do Mark/Restore without materializing > the whole sort set. This can also be used to materialize Windows

Re: [HACKERS] Planning aggregates which require sorted or distinct

On Sat, 20 Jan 2007, Simon Riggs wrote: > On Sat, 2007-01-20 at 15:58 +1100, Gavin Sherry wrote: > > On Fri, 19 Jan 2007, Tom Lane wrote: > > > > > Gavin Sherry <[EMAIL PROTECTED]> writes: > > > > On Fri, 19 Jan 2007, Tom Lane wrote: > > > >> Er, what primary key would that be exactly? And even i

Re: [HACKERS] Planning aggregates which require sorted or distinct

On Sat, 20 Jan 2007, Tom Lane wrote: > Gavin Sherry <[EMAIL PROTECTED]> writes: > > We want to answer the following: for each employee: what is their rank in > > terms of salary and what is their rank in terms of age. This query > > answers that: > > > select empno, rank() over (order by salary) a

Re: [HACKERS] Planning aggregates which require sorted or distinct

"Alvaro Herrera" <[EMAIL PROTECTED]> writes: > Simon Riggs wrote: > >> I'm working on modifying Tuplestore that will allow you to store the >> last N tuples, rather than all previous input. This is specifically >> designed to allow Merge Join to do Mark/Restore without materializing >> the whole s

Re: [HACKERS] Planning aggregates which require sorted or distinct

Gavin, I'm also interested in the topic, but right now I am wondering if rank() function is a reserved name ? We're working on built-in tsearch2 for 8.3 release and we already have rank() function. Oleg On Sun, 21 Jan 2007, Gavin Sherry wrote: On Sat, 20 Jan 2007, Tom Lane wrote: Gavin She

Re: [HACKERS] Planning aggregates which require sorted or distinct

"Gavin Sherry" <[EMAIL PROTECTED]> writes: > Wow. What a coincidence! Windows are slightly more complex though. As you > probably know, there are two ways of specifying the window frame: by an > absolute number of rows (ROWS N PRECEDING, for example); or, by a 'range' > (RANGE N PRECEDING), where

Re: [HACKERS] Planning aggregates which require sorted or distinct

On Sat, 20 Jan 2007, Gregory Stark wrote: > However for RANGE UNBOUNDED PRECEDING we can apply a different plan. Keep the > state variable for each window aggregate around for the entire time. For each > record apply the state transition function then apply the FINAL function to > generate the res

Re: [HACKERS] Planning aggregates which require sorted or distinct

On Sat, 2007-01-20 at 23:54 +1100, Gavin Sherry wrote: > > > Yep. I was thinking about this all morning. I think I've over engineered > > > the problem in my head. Window function input just looks like a slightly > > > more complex distinct aggregate input. I'll think on it more though. > > > > I'

Re: [HACKERS] [BUGS] BUG #2907: pg_get_serial_sequence quoting

Adriaan van Os wrote: > Bruce Momjian wrote: > > > Strangely, this was reported before, but not until November of 2006: > > > > http://archives.postgresql.org/pgsql-general/2006-11/msg0.php > > That was a follow up on this thread >

Re: [HACKERS] Planning aggregates which require sorted or distinct

On Sat, 2007-01-20 at 13:36 +, Gregory Stark wrote: > "Alvaro Herrera" <[EMAIL PROTECTED]> writes: > > > Simon Riggs wrote: > > > >> I'm working on modifying Tuplestore that will allow you to store the > >> last N tuples, rather than all previous input. This is specifically > >> designed to al

Re: [HACKERS] savepoint improvements

On 1/19/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Merlin Moncure" <[EMAIL PROTECTED]> writes: > I suspect the savepoint command is almost never used > outside of oracle compatibility efforts. Last I heard, we implemented it because it is in the SQL standard. I have no idea (nor do I much care) wh

[HACKERS] XML regression test failure

$ make -C src/test/regress runtest [ ... ] test xml ... FAILED test stats... ok test tablespace ... ok 1 of 105 tests failed. The regression.diffs are attached. Note that this reproduces consistently, b

Re: [HACKERS] Windows buildfarm failures

Alvaro Herrera wrote: > Tom Lane wrote: >> Alvaro Herrera <[EMAIL PROTECTED]> writes: >>> Now, if some Windows-enabled person could step forward so that we can >>> suggest some tests to run, that would be great. Perhaps the solution to >>> the problem is to relax the conditions a little, so that t

Re: [HACKERS] Windows buildfarm failures

Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > Alvaro Herrera wrote: >> Hmm, that could explain it, but it's strange that only Windows machines >> are affected. Maybe it's a scheduler issue, and the Unix machines are >> able to let pgstat do some work but Windows are not. > maybe not only win

Re: [HACKERS] Windows buildfarm failures

Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > maybe not only windows boxes: > http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=zebra&dt=2007-01-20%2015:25:05 Wow, I just saw the stats failure on my own machine, for the first time ever. Conclusions: 1. Enabling autovac has definitely ra

[HACKERS] Simplifying pg_am representation of index sortability

With the just-committed changes in PathKey representation, the core backend is now pretty well invested in the assumption that index AMs that can deliver sorted results will use btree-compatible strategy numbers for their order-related operators. This doesn't seem to me to be any huge limitation (

Re: [HACKERS] XML regression test failure

Neil Conway wrote: > The regression.diffs are attached. Note that this reproduces > consistently, but only occurs if I use the "runtest" Makefile target > (i.e. run the regression tests in serial mode); "make check" works > fine, for example. You need to re-initdb. -- Peter Eisentraut http://deve

Re: [HACKERS] XML regression test failure

Peter Eisentraut <[EMAIL PROTECTED]> writes: > Neil Conway wrote: >> The regression.diffs are attached. Note that this reproduces >> consistently, but only occurs if I use the "runtest" Makefile target >> (i.e. run the regression tests in serial mode); "make check" works >> fine, for example. > Yo

Re: [HACKERS] Concurrent connections in psql

What are people's opinions on this patch? (It is at the URL below.) I like the capability, and am impressed it allowed testing that found some concurrency bugs in our server, but it is a large patch to psql. --- Gregory St

Re: [HACKERS] Indicate disabled triggers in \d

Patch applied by Neil. Thanks. --- Brendan Jurd wrote: > On 11/7/06, Brendan Jurd <[EMAIL PROTECTED]> wrote: > > As discussed briefly on pgsql-hackers, the current psql \d command > > does not make any distinction between

Re: [HACKERS] [PATCHES] setseed() doc

FYI, Neil has corrected this in CVS HEAD. --- Tom Lane wrote: > Neil Conway <[EMAIL PROTECTED]> writes: > > On Mon, 2006-09-04 at 15:19 -0400, Tom Lane wrote: > >> AFAICT it's just junk. It happens to be the input times > >

Re: [HACKERS] Function execution costs 'n all that

Neil Conway <[EMAIL PROTECTED]> writes: > On Mon, 2007-01-15 at 15:05 -0500, Tom Lane wrote: >> maybe we should just do the constant for starters and see how many >> people really want to write C-code estimators ... > +1 It seemed like that was the list's consensus, so I'll go off and do the simp

Re: [HACKERS] [BUGS] BUG #2907: pg_get_serial_sequence quoting

Bruce Momjian <[EMAIL PROTECTED]> writes: >> I presume the reason for that is that the first paramater can be >> qualified: >> select pg_get_serial_sequence('"public"."FOO"', 'Ff1'); > Would someone explain why qualification makes us lowercase the first > parameter by default? I don't understand

[HACKERS] ECPG regression tests seem rather fundamentally broken

AFAICT, every buildfarm machine that runs ecpg tests has been failing since Peter's patch here: http://archives.postgresql.org/pgsql-committers/2007-01/msg00241.php Now it looks to me like Peter was simply wrong: we do need to include libpq because libecpg depends on it. However, I tried revertin

[HACKERS] Compile error in tab-complete.c on OS X (bug 2838)

http://archives.postgresql.org/pgsql-bugs/2006-12/msg00220.php Anyone want to take action on this? Aubrac is currently failing because of it (http://pgbuildfarm.org/cgi-bin/show_history.pl?nm=aubrac&br=HEAD). I could switch to libedit, but ISTM it'd be better to have the default compile options wo

Re: [HACKERS] Compile error in tab-complete.c on OS X (bug 2838)

"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > http://archives.postgresql.org/pgsql-bugs/2006-12/msg00220.php > Anyone want to take action on this? It works on my OSX machines (PPC and Intel both) ... so I think it's up to you to figure out why it doesn't work on yours. rega

Re: [HACKERS] XML regression test failure

Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Neil Conway wrote: > >> The regression.diffs are attached. Note that this reproduces > >> consistently, but only occurs if I use the "runtest" Makefile > >> target (i.e. run the regression tests in serial mode); "make > >> check" wo