Re: [HACKERS] Asynchronous I/O Support

Hi, "bgwriter doing aysncronous I/O for the dirty buffers that it is supposed to sync" Another decent use-case? Regards, Nikhils EnterpriseDB   http://www.enterprisedb.com On 10/15/06, Luke Lonergan <[EMAIL PROTECTED]> wrote: Martijn,On 10/15/06 10:56 AM, "Martijn van Oosterhout"

Re: [HACKERS] Additional stats for Relations

Hi, So: heap_blks_reused (with Jim's semantics), heap_blks_extend, heap_blks_truncate are the "interesting" stats? Will try to work up a patch for this. Regards, Nikhils EnterpriseDB   http://www.enterprisedb.com On 10/15/06, Simon Riggs <[EMAIL PROTECTED]> wrote: On Sat, 2006-10-14 at 11:32 +

Re: [HACKERS] [GENERAL] Anyone using "POSIX" time zone offset capability?

> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes: Tom> The weird thing about this allegedly-POSIX notation is the combination Tom> of a symbolic name and a further offset from it. AIUI, it is not a further offset but rather (mostly-)redundant data specifying the exact offset from UTC¹ the text

Re: [HACKERS] pg_internal.init is hazardous to your health

On Tue, 17 Oct 2006, Tom Lane wrote: > Dirk Lutzebaeck and I just spent a tense couple of hours trying to > figure out why a large database Down Under wasn't coming up after being > reloaded from a base backup plus PITR recovery. The symptoms were that > the recovery went fine, but backend proces

Re: [HACKERS] [PERFORM] Hints proposal

On Friday 13 October 2006 12:46, Gregory Stark wrote: > Josh Berkus writes: > >> > I actually think the way to attack this issue is to discuss the kinds > >> > of errors the planner makes, and what tweaks we could do to correct > >> > them. Here's the ones I'm aware of: > >> > > >> > -- Incorrect

[HACKERS] pg_internal.init is hazardous to your health

Dirk Lutzebaeck and I just spent a tense couple of hours trying to figure out why a large database Down Under wasn't coming up after being reloaded from a base backup plus PITR recovery. The symptoms were that the recovery went fine, but backend processes would fail at startup or soon after with "

[HACKERS] 8.1.4 verified on Intel Mac OS 10.4.8

The subject line says it all.I just completed a build, test, and install of PostgreSQL 8.1.4 on an Intel Mac with OS 10.4.8.  Every single step executed flawlessly, and all 98 tests passed, running the regression tests.I've got a suggestion for the documentation (INSTALL file).  I may be the only p

Re: [HACKERS] Postgresql Caching

On Tue, 2006-10-17 at 16:51 -0700, Anon Mous wrote: > Hi > > I've loved reading all of your thoughts and comments. > > Yet, I'm left with the question: > >Can we can brainstorm a caching solution that is workable... > I think you're making this a little complicated. A lot of these problem

Re: [HACKERS] Postgresql Caching

Hi I've loved reading all of your thoughts and comments. Yet, I'm left with the question:    Can we can brainstorm a caching solution that is workable... I've seen some posts talking about some of the challenges. 1.) Only good for static data As it was proposed that is largely true. This does

Re: [HACKERS] Syntax bug? Group by?

Joe Sunday wrote: On Tue, Oct 17, 2006 at 02:41:25PM -0400, Mark Woodward wrote: The output column "ycis_id" is unabiguously a single value with regards to the query. Shouldn't PostgreSQL "know" this? AFAIR, I think I've used this exact type of query before either on PostgreSQL or another syste

Re: [HACKERS] Syntax bug? Group by?

Andrew Dunstan <[EMAIL PROTECTED]> writes: > Mark Woodward wrote: >> My question, is it a syntactic technicality that PostgreSQL asks for a >> "group by," or a bug in the parser? > AFAIK what you want is not per sql spec. It would in fact be a violation of spec. Consider the case where there are

Re: [HACKERS] Syntax bug? Group by?

On Tue, Oct 17, 2006 at 04:45:49PM -0400, Mark Woodward wrote: > Well, this started out as a "huh, that's funny, that should work, is that > a bug?" and is turning into a search through the SQL99 spec for a clear > answer. I've already worked around it, but to me, at least, it seems it > should wor

Re: [HACKERS] [GENERAL] query log corrupted-looking entries

"George Pavlov" <[EMAIL PROTECTED]> writes: >> Hmm. If the messages are less than PIPE_BUF bytes long (4096 bytes on >> Linux) then the writes are supposed to be atomic. > Some of them involve long messages (>4K), but there are many that do not > (like the ones I had posted at the start of this t

Re: [HACKERS] Syntax bug? Group by?

On Tue, Oct 17, 2006 at 02:41:25PM -0400, Mark Woodward wrote: > The output column "ycis_id" is unabiguously a single value with regards to > the query. Shouldn't PostgreSQL "know" this? AFAIR, I think I've used this > exact type of query before either on PostgreSQL or another system, maybe > Orac

Re: [HACKERS] Syntax bug? Group by?

> On Oct 17, 2006, at 15:19, Peter Eisentraut wrote: > >> Mark Woodward wrote: >>> Shouldn't this work? >>> >>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >>> >>> ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be >>> used in an aggregate function >> >> Thi

Re: [HACKERS] Syntax bug? Group by?

> Mark Woodward wrote: >> Shouldn't this work? >> >> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >> >> ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be >> used in an aggregate function > > This would require a great deal of special-casing, in particular >

Re: [HACKERS] Syntax bug? Group by?

> On Tue, Oct 17, 2006 at 02:41:25PM -0400, Mark Woodward wrote: > >> The output column "ycis_id" is unabiguously a single value with regards >> to >> the query. Shouldn't PostgreSQL "know" this? AFAIR, I think I've used >> this >> exact type of query before either on PostgreSQL or another system,

Re: [HACKERS] Syntax bug? Group by?

On Oct 17, 2006, at 15:19, Peter Eisentraut wrote: Mark Woodward wrote: Shouldn't this work? select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be used in an aggregate function This would require a great deal

Re: [HACKERS] Syntax bug? Group by?

Mark Woodward wrote: > Shouldn't this work? > > select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; > > ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be > used in an aggregate function This would require a great deal of special-casing, in particular knowledge of

Re: [HACKERS] Syntax bug? Group by?

Mark Woodward wrote: Stephen Frost wrote: select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; But back to the query the issue comes in that the ycis_id value is included with the return values requested (a single row value with aggregate values that isn't grouped) - if ycis_id

Re: [HACKERS] Syntax bug? Group by?

Mark Woodward wrote: Hi, Mark, Mark Woodward wrote: Shouldn't this work? select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be used in an aggregate function If I am asking for a specific column value, shou

Re: [HACKERS] Syntax bug? Group by?

> Mark Woodward wrote: select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >> >> I still assert that there will always only be one row to this query. >> This >> is an aggregate query, so all the rows with ycis_id = 15, will be >> aggregated. Since ycis_id is the identifyin

Re: [HACKERS] Syntax bug? Group by?

> Mark Woodward wrote: >>> Stephen Frost wrote: >>> >>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >>> But back to the query the issue comes in that the ycis_id value is >>> included with the return values requested (a single row value with >>> aggregate values th

Re: [HACKERS] Syntax bug? Group by?

> Hi, Mark, > > Mark Woodward wrote: >> Shouldn't this work? >> >> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >> >> ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be used >> in an aggregate function >> >> If I am asking for a specific column value, should

Re: [HACKERS] Asynchronous I/O Support

Have a look at this: [2]http://www-128.ibm.com/developerworks/linux/library/l-async/ This gives a good description of AIO. I'm doing some testing. Will notify, if I get any positive results. Please let me know, if you get any ideas after reading [2]. Regards, Raja On 10/17/06, Florian Weimer

Re: [HACKERS] Syntax bug? Group by?

> Hi, Mark, > > Mark Woodward wrote: >>> Stephen Frost wrote: >>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >>> But back to the query the issue comes in that the ycis_id value is >>> included with the return values requested (a single row value with >>> aggregate valu

Re: [HACKERS] Syntax bug? Group by?

Hi, Mark, Mark Woodward wrote: >> Stephen Frost wrote: >> >>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >> But back to the query the issue comes in that the ycis_id value is >> included with the return values requested (a single row value with >> aggregate values that isn

Re: [HACKERS] Syntax bug? Group by?

Mark Woodward wrote: Stephen Frost wrote: select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; But back to the query the issue comes in that the ycis_id value is included with the return values requested (a single row value with aggregate values that isn't grouped) -

Re: [HACKERS] Syntax bug? Group by?

Mark Woodward wrote: select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; I still assert that there will always only be one row to this query. This is an aggregate query, so all the rows with ycis_id = 15, will be aggregated. Since ycis_id is the identifying part of the qu

Re: [HACKERS] Syntax bug? Group by?

Hi, Mark, Mark Woodward wrote: > Shouldn't this work? > > select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; > > ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be used > in an aggregate function > > If I am asking for a specific column value, should I, technic

Re: [HACKERS] Asynchronous I/O Support

* Neil Conway: > [1] http://lse.sourceforge.net/io/aio.html Last Modified Mon, 07 Jun 2004 12:00:09 GMT But you are right -- it seems that io_submit still blocks without O_DIRECT. *sigh* -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ D

Re: [HACKERS] Syntax bug? Group by?

> Stephen Frost wrote: > >> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; > > But back to the query the issue comes in that the ycis_id value is > included with the return values requested (a single row value with > aggregate values that isn't grouped) - if ycis_id is not uniq

Re: [HACKERS] Asynchronous I/O Support

On 10/15/06, Luke Lonergan <[EMAIL PROTECTED]> wrote: Martijn, The killer use-case we've identified is for the scattered I/O associated with index + heap scans in Postgres. If we can issue ~5-15 I/Os in advance when the TIDs are widely separated it has the potential to increase the I/O speed by

Re: [HACKERS] Syntax bug? Group by?

Stephen Frost wrote: * Mark Woodward ([EMAIL PROTECTED]) wrote: If I am asking for a specific column value, should I, technically speaking, need to group by that column? Technically speaking, if you're asking for a specific tuple, should you be allowed to request an aggregation? Only with the

Re: [HACKERS] constraints in query plans

On Sun, 2006-10-15 at 20:36 -0700, Jeremy Drake wrote: > I just tried that, > CREATE INDEX test_domain_k_x1_x2_mp ON test_domain (k, x1, x2, mp); > > and dropped the others. That actually works properly. > jeremyd=# explain analyze select * from test_domain where k = 1255 and mp; >

Re: [HACKERS] Syntax bug? Group by?

On Tue, 17 Oct 2006 12:08:07 -0400 Stephen Frost <[EMAIL PROTECTED]> wrote: > * Mark Woodward ([EMAIL PROTECTED]) wrote: > > If I am asking for a specific column value, should I, technically > > speaking, need to group by that column? > > Technically speaking, if you're asking for a specific tuple

Re: [HACKERS] Additional stats for Relations

On Sat, 2006-10-14 at 11:32 +0530, NikhilS wrote: > On 10/13/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > I'm also not sure if this metric is what you actually want, > since a > single page can be returned many times from the FSM even > between > vacuums

Re: [HACKERS] Syntax bug? Group by?

* Mark Woodward ([EMAIL PROTECTED]) wrote: > If I am asking for a specific column value, should I, technically > speaking, need to group by that column? Technically speaking, if you're asking for a specific tuple, should you be allowed to request an aggregation? Thanks, S

Re: [HACKERS] hacking postgres hashjoin algorithm

On Sun, Oct 15, 2006 at 11:08:18PM -0400, HS wrote: > Hello there > > I am trying to play around with the hashjoin algorithm in postgres. > > I am using the statement like > Select count(*) from r,s where r.id=s.id; > > I looked at the function ExecHashJoin() in nodeHashjoin.c and cannot > find

[HACKERS] Syntax bug? Group by?

Shouldn't this work? select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be used in an aggregate function If I am asking for a specific column value, should I, technically speaking, need to group by that column? -

[HACKERS] hacking postgres hashjoin algorithm

Hello there I am trying to play around with the hashjoin algorithm in postgres. I am using the statement like Select count(*) from r,s where r.id=s.id; I looked at the function ExecHashJoin() in nodeHashjoin.c and cannot find where the algorithm is comparing if r.id equals s.id please advise

Re: [HACKERS] postgres database crashed

Markus Schaber <[EMAIL PROTECTED]> writes: > Ashish Goel wrote: >> The code works well if we don't maintain the referrences but when we >> include the referrences then the database crashes somewhere between >> 2500-3000 transactions. >> >> So could this problem be due to the multiple referrences be

Re: [HACKERS] Getting the type Oid in a CREATE TYPE output function

On 10/16/06, Weslee Bilodeau <[EMAIL PROTECTED]> wrote: Marko Kreen wrote: > The PGP functions happen to do it already - pgp_key_id(). Actually, Tom helped me realize I made a mistake, which I'm following his suggestion. Not tying keys to OIDs which change when backup/restored. Yeah, tying to

Re: [HACKERS] [GENERAL] Anyone using "POSIX" time zone offset capability?

Hi, "Sander Steffann" <[EMAIL PROTECTED]> writes: What the datetime.c code is doing is trying to find the zoneabbrev in a built-in timezone table, and then adding the two together. This is simply wacko. I think that if anyone has ever tried to use this notation they would have noticed this

Re: [HACKERS] [GENERAL] Anyone using "POSIX" time zone offset capability?

"Sander Steffann" <[EMAIL PROTECTED]> writes: >> What the datetime.c code is doing is trying to find the zoneabbrev >> in a built-in timezone table, and then adding the two together. >> This is simply wacko. > I think that if anyone has ever tried to use this notation they would have > noticed th

Re: [HACKERS] [GENERAL] Anyone using "POSIX" time zone offset capability?

Hi, The POSIX timezone notation as understood by the zic code includes the possibility of zoneabbrev[+-]hh[:mm[:ss]] but the meaning is that hh:mm:ss *is* the offset from GMT, and zoneabbrev is being defined as the abbreviation for that offset. What the datetime.c code is doing is trying to fi

Re: [HACKERS] Is python 2.5 supported?

Ühel kenal päeval, E, 2006-10-16 kell 14:46, kirjutas Jim C. Nasby: > Since installing python 2.5, tapir has been failing: > > http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=tapir&dt=2006-10-15%2020:20:16 > > Several of the failures appear to be a simple change in error reporting; > I haven't inve

Re: [HACKERS] postgres database crashed

Hi, Ashish, Ashish Goel wrote: > I am sorry but I forgot to mention that in the database schema we are > maintaining referrences to the main table xyz(int id, img image, fname > varhcar(50)) > > There are around 14 tables referrencing this table . The referrences are > being made to the column i