count syntax

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.

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/