Обсуждение: 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/