[BUGS] Problem With Case Statement and Aggregate Functions
The following works as expected: select (SELECT CASE WHEN (1=2) THEN 0 ELSE sum(count) END) from ( select 1 as count union select 2 union select 3 ) as "temp"; The result is "6". The following also works as expected: select count(*) from ( select 1 as count union select 2 union select 3 ) as "temp"; The results is "3". However the following code doesn't work even though it is very similar to the first query (that is, and aggregate function within a case statement): select (SELECT CASE WHEN (1=2) THEN 0 ELSE COUNT(*) END) from ( select 1 as count union select 2 union select 3 ) as "temp"; The result is three rows of "1". So why does the "count" aggregate function within a case statement execute on a per row basis whereas the "sum" aggregate within a case statement will first group the rows? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #3267: Relfilenode
The following bug has been logged online: Bug reference: 3267 Logged by: Shyam Sunder Rai Email address: [EMAIL PROTECTED] PostgreSQL version: GreenplumDB Operating system: CentOS Description:Relfilenode Details: I am using our database that is based on Postgres 8.1.6. and it even supports clustering on linux machines. I am curious to know the relation between "relfilenode" and Query Executor. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Problem With Case Statement and Aggregate Functions
On Fri, 11 May 2007 14:47:04 +1000, Andrew Shea <[EMAIL PROTECTED]> wrote:
> The following works as expected:
>
> select (SELECT CASE WHEN (1=2) THEN 0 ELSE sum(count) END) from (
> select 1 as count union select 2 union select 3
> ) as "temp";
>
> The result is "6".
>
> The following also works as expected:
>
> select count(*) from (
> select 1 as count union select 2 union select 3
> ) as "temp";
>
> The results is "3".
>
>
> However the following code doesn't work even though it is very similar
> to the first query (that is, and aggregate function within a case
> statement):
>
> select (SELECT CASE WHEN (1=2) THEN 0 ELSE COUNT(*) END) from (
> select 1 as count union select 2 union select 3
> ) as "temp";
>
> The result is three rows of "1".
>
> So why does the "count" aggregate function within a case statement
> execute on a per row basis whereas the "sum" aggregate within a case
> statement will first group the rows?
The * from count(*) binds to the inner most select where it can draw
data.
Think of it like
select
(select count('1') from bar)
>from foo
foo and bar have nothing to do with each other so it turns into for each
row in foo count the number of records in bar.
klint.
+---+-+
: Klint Gore: "Non rhyming:
: EMail : [EMAIL PROTECTED] : slang - the:
: Snail : A.B.R.I.: possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---+-+
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] Is PostgresSQL supported in Windows Vista?
Jack Ho wrote: > Dear sir, > > Is PostgreSQL supported in Vista? If it is, what version is supported? > IIRC, all windows versions (which means 8.2 really - 8.0 and 8.1 have other problems) work fine on vista *except* that the installation program doesn't properly work. You can install it manually (or IIRC using the installer, but manually initializing the database etc), and then it will run fine. The installer should be fixed in 8.3. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3267: Relfilenode
Shyam Sunder Rai wrote: I am using our database that is based on Postgres 8.1.6. and it even supports clustering on linux machines. I am curious to know the relation between "relfilenode" and Query Executor. This mailing list and form is for PostgreSQL bug reports only. Please address any questions you have on the commercial variants of PostgreSQL directly to the vendor. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3267: Relfilenode
Shyam Sunder Rai wrote: The following bug has been logged online: Bug reference: 3267 Logged by: Shyam Sunder Rai Email address: [EMAIL PROTECTED] PostgreSQL version: GreenplumDB if you are using greenplumDB you should ask the greenplum support for help ... Operating system: CentOS Description:Relfilenode Details: I am using our database that is based on Postgres 8.1.6. and it even supports clustering on linux machines. I am curious to know the relation between "relfilenode" and Query Executor. I don't understand what you are asking here - what kind of "clustering" are you talking about ? Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #3268: pgpass.conf error
The following bug has been logged online: Bug reference: 3268 Logged by: Nilay Ceter Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.3-1 Operating system: windows XP Description:pgpass.conf error Details: I have been working with postgresql for our project ,first time I used postgreSQL 8.0 then I removed it and tried to use pgSQL 8.2.3-1.I did the setup as I had done before.But when I try to connect the server this error is being displayed : --- pgAdmin III --- An error has occurred: can't open file 'C:\Documents and Settings\Fatma DEMİRCİ\Application Data\postgresql\pgpass.conf' (error 3: sistem belirtilen yolu bulamıyor.) What should I do,I am in a critical stiuation,I would be very glad if you can help me. Best Regards... Nilay Ceter Software Engineer. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Problem With Case Statement and Aggregate Functions
Andrew Shea <[EMAIL PROTECTED]> writes: > However the following code doesn't work even though it is very similar > to the first query (that is, and aggregate function within a case > statement): > select (SELECT CASE WHEN (1=2) THEN 0 ELSE COUNT(*) END) from ( ^^ > select 1 as count union select 2 union select 3 > ) as "temp"; Lose the underlined SELECT and it will behave the way you expect. As-is the COUNT is an aggregate of that sub-select, not of the topmost select. To be considered an aggregate of the topmost select it has to reference a variable of that query level. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #3269: PSQL does not display error output
The following bug has been logged online: Bug reference: 3269 Logged by: Bojan Jovanovic Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: RHEL3 Description:PSQL does not display error output Details: Hello, We just upgraded to 8.2.4, and noticed that psql does not display error messages, e.g.: shp_production=# \set autocommit 'off' shp_production=# \set AUTOCOMMIT = 'off' PROMPT1 = '%/%R%# ' PROMPT2 = '%/%R%# ' PROMPT3 = '>> ' VERBOSITY = 'default' VERSION = 'PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-42)' DBNAME = '*' USER = '*' PORT = '5432' ENCODING = 'LATIN1' autocommit = 'off' shp_production=# select * from asdfafsdf; shp_production=# commit; ROLLBACK shp_production=# As you can see, the error message was not shown.. If we do the exact same thing from a remote 8.1.8, everything works just as expected: shp_bjovanovic=# select * from asdfafsdf; ERROR: relation "asdfafsdf" does not exist shp_bjovanovic=# Please let me know where the issue is! Thanks! Regards, bojan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #3269: PSQL does not display error output
"Bojan Jovanovic" <[EMAIL PROTECTED]> writes: > We just upgraded to 8.2.4, and noticed that psql does not display error > messages, e.g.: Works for me. Maybe you have client_min_messages set to a silly value? Or stderr directed away from the terminal? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3269: PSQL does not display error output
On Fri, May 11, 2007 at 01:06:02PM +, Bojan Jovanovic wrote: > We just upgraded to 8.2.4, and noticed that psql does not display error > messages, e.g.: [...] > shp_production=# select * from asdfafsdf; > shp_production=# commit; > ROLLBACK What's the output of "show client_min_messages"? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3270: limit < 16 optimizer behaviour
The following bug has been logged online: Bug reference: 3270 Logged by: Liviu Ionescu Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: Linux Description:limit < 16 optimizer behaviour Details: I have a table of about 15Mrows, and a query like this: SELECT historianid,storagedate,slotdate,status,value FROM historiandata JOIN rtunodes ON(historiandata.historianid=rtunodes.nodeid) JOIN rtus ON(rtunodes.rtuid=rtus.nodeid) WHERE realmid IN (1119,1422,698,1428) AND historianid in (2996) ORDER BY storagedate desc LIMIT 10 if there are no records with the given historianid, if limit is >= 16 the query is quite fast, otherwise it takes forever. my current fix was to always increase the limit to 16, but, although I know the optimizer behaviour depends on LIMIT, I still feel this looks like a bug; if the resultset has no records the value of the LIMIT should not matter. regards, Liviu Ionescu CREATE TABLE historiandata ( historianid int4 NOT NULL, status int2 NOT NULL DEFAULT 0, value float8, slotdate timestamptz NOT NULL, storagedate timestamptz NOT NULL DEFAULT now(), CONSTRAINT historiandata_pkey PRIMARY KEY (historianid, slotdate), CONSTRAINT historianid_fkey FOREIGN KEY (historianid) REFERENCES historians (nodeid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT ) WITHOUT OIDS; ALTER TABLE historiandata OWNER TO tomcat; -- Index: historiandata_historianid_index -- DROP INDEX historiandata_historianid_index; CREATE INDEX historiandata_historianid_index ON historiandata USING btree (historianid); -- Index: historiandata_slotdate_index -- DROP INDEX historiandata_slotdate_index; CREATE INDEX historiandata_slotdate_index ON historiandata USING btree (slotdate); -- Index: historiandata_storagedate_index -- DROP INDEX historiandata_storagedate_index; CREATE INDEX historiandata_storagedate_index ON historiandata USING btree (storagedate); CREATE TABLE rtunodes ( nodeid int4 NOT NULL, rtuid int4 NOT NULL, no_publicnodeid int4, name varchar(64) NOT NULL, isinvalid bool NOT NULL DEFAULT false, nodetype varchar(16), CONSTRAINT rtunodes_pkey PRIMARY KEY (nodeid), CONSTRAINT nodeid_fkey FOREIGN KEY (nodeid) REFERENCES nodes (nodeid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT rtuid_fkey FOREIGN KEY (rtuid) REFERENCES rtus (nodeid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT ) WITHOUT OIDS; ALTER TABLE rtunodes OWNER TO tomcat; CREATE TABLE rtus ( nodeid int4 NOT NULL, passwd varchar(10) NOT NULL, xml text, no_nextpublicnodeid int4 NOT NULL DEFAULT 1, rtudriverid int2, realmid int4 NOT NULL, enablegetlogin bool NOT NULL DEFAULT false, enablegetconfig bool NOT NULL DEFAULT false, businfoxml text, uniqueid varchar(32) NOT NULL, no_publicrtuid int4, loginname varchar(10) NOT NULL, protocolversion varchar(8) DEFAULT '0.0'::character varying, isinvalid bool DEFAULT false, CONSTRAINT rtus_pkey PRIMARY KEY (nodeid), CONSTRAINT nodeid_fkey FOREIGN KEY (nodeid) REFERENCES nodes (nodeid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT realmid_fkey FOREIGN KEY (realmid) REFERENCES realms (nodeid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT rtudriverid_fkey FOREIGN KEY (rtudriverid) REFERENCES rtudrivers (rtudriverid) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT rtus_loginname_unique UNIQUE (loginname), CONSTRAINT rtus_uniqueid_unique UNIQUE (uniqueid) ) WITHOUT OIDS; ALTER TABLE rtus OWNER TO tomcat; -- Index: rtus_realmid_index -- DROP INDEX rtus_realmid_index; CREATE INDEX rtus_realmid_index ON rtus USING btree (realmid); -- Index: rtus_rtudriverid_index -- DROP INDEX rtus_rtudriverid_index; CREATE INDEX rtus_rtudriverid_index ON rtus USING btree (rtudriverid); ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #3269: PSQL does not display error output
Here it is... Did not change anything from the default installation - just compiled it, and installed.. shp_production=# show client_min_messages shp_production-# ; client_min_messages - notice (1 row) shp_production=# Is this correct? How would STDERR get redirected from psql? Thanks! Regards, bojan On Fri, 11 May 2007, Michael Fuhr wrote: On Fri, May 11, 2007 at 01:06:02PM +, Bojan Jovanovic wrote: We just upgraded to 8.2.4, and noticed that psql does not display error messages, e.g.: [...] shp_production=# select * from asdfafsdf; shp_production=# commit; ROLLBACK What's the output of "show client_min_messages"? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3271: PREPARE/EXCUTE don't work
laurent faillie wrote: While trying to use Apache 2.2 database authentication, I discovered that I wasn't able to retrieve users. After some investigation, I found that PREPARE/EXECUTE are faulty. It can be reproduced in psql as bellow : www=> PREPARE authn_dbd_1 (varchar) AS select mdp from mariage.comptes where nom = '$1'; That $1 should be without the quotes, like this: PREPARE authn_dbd_1 (varchar) AS select mdp from mariage.comptes where nom = $1 Otherwise the query looks for a user named '$1'. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #3271: PREPARE/EXCUTE don't work
The following bug has been logged online:
Bug reference: 3271
Logged by: laurent faillie
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system: HP-UX 11.11 (v32 bits).
Description:PREPARE/EXCUTE don't work
Details:
Hi all,
While trying to use Apache 2.2 database authentication, I discovered that I
wasn't able to retrieve users. After some investigation, I found that
PREPARE/EXECUTE are faulty. It can be reproduced in psql as bellow :
www=> PREPARE authn_dbd_1 (varchar) AS select mdp from mariage.comptes where
nom = '$1';
PREPARE
www=> execute authn_dbd_1 ('Test');
mdp
-
(0 rows)
www=> select mdp from mariage.comptes where nom = 'Test';
mdp
---
xxx
(1 row)
The problem was also in 8.2.3.
Best regards,
Laurent
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] BUG #3270: limit < 16 optimizer behaviour
This should have been asked on the performance list, not filed as a bug. I doubt anyone will have a complete answer to your question without EXPLAIN ANALYZE output from the query. Have you ANALYZE'd the tables recently? Poor statistics is one possible cause of the issue you are having. On Fri, May 11, 2007 at 14:07:57 +, Liviu Ionescu <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 3270 > Logged by: Liviu Ionescu > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.2.4 > Operating system: Linux > Description:limit < 16 optimizer behaviour > Details: > > I have a table of about 15Mrows, and a query like this: > > SELECT historianid,storagedate,slotdate,status,value FROM historiandata > JOIN rtunodes ON(historiandata.historianid=rtunodes.nodeid) > JOIN rtus ON(rtunodes.rtuid=rtus.nodeid) > WHERE realmid IN (1119,1422,698,1428) > AND historianid in (2996) > ORDER BY storagedate desc > LIMIT 10 > > if there are no records with the given historianid, if limit is >= 16 the > query is quite fast, otherwise it takes forever. > > my current fix was to always increase the limit to 16, but, although I know > the optimizer behaviour depends on LIMIT, I still feel this looks like a > bug; if the resultset has no records the value of the LIMIT should not > matter. > > regards, > > Liviu Ionescu > > > > CREATE TABLE historiandata > ( > historianid int4 NOT NULL, > status int2 NOT NULL DEFAULT 0, > value float8, > slotdate timestamptz NOT NULL, > storagedate timestamptz NOT NULL DEFAULT now(), > CONSTRAINT historiandata_pkey PRIMARY KEY (historianid, slotdate), > CONSTRAINT historianid_fkey FOREIGN KEY (historianid) > REFERENCES historians (nodeid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT > ) > WITHOUT OIDS; > ALTER TABLE historiandata OWNER TO tomcat; > > > -- Index: historiandata_historianid_index > > -- DROP INDEX historiandata_historianid_index; > > CREATE INDEX historiandata_historianid_index > ON historiandata > USING btree > (historianid); > > -- Index: historiandata_slotdate_index > > -- DROP INDEX historiandata_slotdate_index; > > CREATE INDEX historiandata_slotdate_index > ON historiandata > USING btree > (slotdate); > > -- Index: historiandata_storagedate_index > > -- DROP INDEX historiandata_storagedate_index; > > CREATE INDEX historiandata_storagedate_index > ON historiandata > USING btree > (storagedate); > > > CREATE TABLE rtunodes > ( > nodeid int4 NOT NULL, > rtuid int4 NOT NULL, > no_publicnodeid int4, > name varchar(64) NOT NULL, > isinvalid bool NOT NULL DEFAULT false, > nodetype varchar(16), > CONSTRAINT rtunodes_pkey PRIMARY KEY (nodeid), > CONSTRAINT nodeid_fkey FOREIGN KEY (nodeid) > REFERENCES nodes (nodeid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT, > CONSTRAINT rtuid_fkey FOREIGN KEY (rtuid) > REFERENCES rtus (nodeid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT > ) > WITHOUT OIDS; > ALTER TABLE rtunodes OWNER TO tomcat; > > > > CREATE TABLE rtus > ( > nodeid int4 NOT NULL, > passwd varchar(10) NOT NULL, > xml text, > no_nextpublicnodeid int4 NOT NULL DEFAULT 1, > rtudriverid int2, > realmid int4 NOT NULL, > enablegetlogin bool NOT NULL DEFAULT false, > enablegetconfig bool NOT NULL DEFAULT false, > businfoxml text, > uniqueid varchar(32) NOT NULL, > no_publicrtuid int4, > loginname varchar(10) NOT NULL, > protocolversion varchar(8) DEFAULT '0.0'::character varying, > isinvalid bool DEFAULT false, > CONSTRAINT rtus_pkey PRIMARY KEY (nodeid), > CONSTRAINT nodeid_fkey FOREIGN KEY (nodeid) > REFERENCES nodes (nodeid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT, > CONSTRAINT realmid_fkey FOREIGN KEY (realmid) > REFERENCES realms (nodeid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT, > CONSTRAINT rtudriverid_fkey FOREIGN KEY (rtudriverid) > REFERENCES rtudrivers (rtudriverid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE RESTRICT, > CONSTRAINT rtus_loginname_unique UNIQUE (loginname), > CONSTRAINT rtus_uniqueid_unique UNIQUE (uniqueid) > ) > WITHOUT OIDS; > ALTER TABLE rtus OWNER TO tomcat; > > > -- Index: rtus_realmid_index > > -- DROP INDEX rtus_realmid_index; > > CREATE INDEX rtus_realmid_index > ON rtus > USING btree > (realmid); > > -- Index: rtus_rtudriverid_index > > -- DROP INDEX rtus_rtudriverid_index; > > CREATE INDEX rtus_rtudriverid_index > ON rtus > USING btree > (rtudriverid); > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our l
Re: [BUGS] BUG #3269: PSQL does not display error output
Bojan Jovanovic <[EMAIL PROTECTED]> writes: > How would STDERR get redirected from psql? The usual way, like "psql 2>/dev/null", but if you didn't know that then it's unlikely you did it. I have seen symptoms roughly like this one with really ancient SELinux policies (the first draft of the policy tried to suppress direct writes on /dev/tty from any Postgres executable, IIRC :-(). But I didn't think RHEL3 had SELinux. Do you have /usr/sbin/getenforce and if so what does it report? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
So the discussion died again with nothing being decided. I see we have several choices: 1. implement the standard, per Russell suggestion below 2. decide that the standard is braindead and just omit dumping the grantor when it's no longer available, but don't remove pg_auth_members.grantor 3. decide that the standard is braindead and remove pg_auth_members.grantor Which do people feel should be implemented? I can do whatever we decide; if no one has a strong opinion on the matter, my opinion is we do (2) which is the easiest. Russell Smith wrote: > My possible suggestion is; > 1. Implement the standard for revoking only your privileges by default. > 2. Allow the object owner to revoke privileges assigned by any role, as > if you drop and recreate the object you can achieve this anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
