Обсуждение: count syntax
What is the difference between the following? transatlantic=# select count(*) from trans; count -------- 453602 (1 row) transatlantic=# select count(trans.flowindex) from trans; count -------- 453602 (1 row) transatlantic=# select count(trans.*) from trans; ERROR: Memory exhausted in AllocSetAlloc(204) % limit cputime unlimited filesize unlimited datasize 1048576 kbytes stacksize 32768 kbytes coredumpsize unlimited memoryuse 2048532 kbytes memorylocked 2048532 kbytes maxproc 1044 openfiles 3404 Cheers, Patrick
Patrick Welche <prlw1@newn.cam.ac.uk> writes: > What is the difference between the following? > transatlantic=# select count(*) from trans; This is a SQL-spec-mandated special case meaning "count the number of rows". > transatlantic=# select count(trans.flowindex) from trans; This counts the number of non-null values of trans.flowindex. Also per spec. > transatlantic=# select count(trans.*) from trans; ERROR: Memory exhausted in AllocSetAlloc(204) This syntax is not legal per SQL spec. Postgres interprets it as a command to count the number of non-null values of "trans.*", which in this context is taken as being a whole-row variable. Unfortunately the present implementation of whole-row variables leaks memory ... I don't believe that a whole-row variable will ever be null, so the end result is the same as count(*). You may as well spell it in the spec-approved fashion. regards, tom lane
On Fri, Apr 11, 2003 at 12:44:38 +0100, Patrick Welche <prlw1@newn.cam.ac.uk> wrote: > What is the difference between the following? > > transatlantic=# select count(*) from trans; > count > -------- > 453602 > (1 row) The above counts all rows. > > transatlantic=# select count(trans.flowindex) from trans; > count > -------- > 453602 > (1 row) The above counts all rows where trans.flowindex is not null. > > transatlantic=# select count(trans.*) from trans; > ERROR: Memory exhausted in AllocSetAlloc(204) I don't know why you got an error here. > > % limit > cputime unlimited > filesize unlimited > datasize 1048576 kbytes > stacksize 32768 kbytes > coredumpsize unlimited > memoryuse 2048532 kbytes > memorylocked 2048532 kbytes > maxproc 1044 > openfiles 3404 > > > Cheers, > > Patrick > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Is this a TODO bug? --------------------------------------------------------------------------- Tom Lane wrote: > Patrick Welche <prlw1@newn.cam.ac.uk> writes: > > What is the difference between the following? > > > transatlantic=# select count(*) from trans; > > This is a SQL-spec-mandated special case meaning "count the number of rows". > > > transatlantic=# select count(trans.flowindex) from trans; > > This counts the number of non-null values of trans.flowindex. Also > per spec. > > > transatlantic=# select count(trans.*) from trans; > ERROR: Memory exhausted in AllocSetAlloc(204) > > This syntax is not legal per SQL spec. Postgres interprets it as a > command to count the number of non-null values of "trans.*", which in > this context is taken as being a whole-row variable. Unfortunately the > present implementation of whole-row variables leaks memory ... > > I don't believe that a whole-row variable will ever be null, so the > end result is the same as count(*). You may as well spell it in the > spec-approved fashion. > > regards, tom lane > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Is this a TODO bug? Isn't it there already? * Whole-row references leak memory regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Is this a TODO bug? > > Isn't it there already? > > * Whole-row references leak memory Oh, OK. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Tom Lane wrote: >> Isn't it there already? >> >> * Whole-row references leak memory > Oh, OK. You may have misunderstood me: that's a suggested entry. regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> Isn't it there already? > >> > >> * Whole-row references leak memory > > > Oh, OK. > > You may have misunderstood me: that's a suggested entry. Oh, I thought you were quoting from the FAQ. (It didn't sound familiar, but I figured someone else may have added it.) I searched for 'row' and couldn't find anything, so TODO added: * Prevent whole-row references from leaking memory, e.g. SELECT COUNT(tab.*) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
hi, I am working on postgres 7.3.2 and i get an error while creating a user. as, psql: FATAL: Missing or erroneous pg_hba.conf file, see postmaster log for details i dunno what goes wrong... i searched the archives and found that that may be a problem with "\n" replaced with "\r\n". But i am working on a linux 9.0 system fresh installed, and didnt edit the conf file... any help is appreciated Thanks & regards, Bijoy
bijoy@openlx.com writes: > psql: FATAL: Missing or erroneous pg_hba.conf file, see postmaster log for > details > i dunno what goes wrong... Take the message's advice and look for details in the postmaster's log. If you don't have a postmaster log, then either set the database to log to syslog or fix your start script to send postmaster's stderr someplace more useful than /dev/null. regards, tom lane
Yes, this is a known bug in hba.c and was fixed here: revision 1.98 date: 2003/04/13 04:07:17; author: tgl; state: Exp; lines: +11 -16 Second try at avoiding conflicts with system isblank(). The fix is in 7.3.3, just released. The easiest solution is to replace \r\n with \n, as you suggest. I don't understand how you got \r\n in that file on Linux, though. --------------------------------------------------------------------------- bijoy@openlx.com wrote: > hi, > > I am working on postgres 7.3.2 and i get an error while creating a user. > as, > psql: FATAL: Missing or erroneous pg_hba.conf file, see postmaster log for > details > i dunno what goes wrong... > i searched the archives and found that that may be a problem with > "\n" replaced with "\r\n". > But i am working on a linux 9.0 system fresh installed, > and didnt edit the conf file... > > any help is appreciated > Thanks & regards, > Bijoy > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
At 20:00 31.05.2003, Bruce Momjian said: --------------------[snip]-------------------- >\r\n with \n, as you suggest. I don't understand how you got \r\n in >that file on Linux, though. --------------------[snip]-------------------- Samba share perhaps? -- >O Ernest E. Vogelsinger (\) ICQ #13394035 ^ http://www.vogelsinger.at/