Re: [BUGS] lost statistics; analyze needs to execute twice
On Tue, Sep 1, 2009 at 00:02, Jaime Casanova wrote: > Hi, > > pgsql 8.3.7 and 8.4.0 > > when i issue an "immediate shutdown" the statistics on all tables disappear... That is by design. Whenever the server goes into crash recovery on startup, it will clean out the statistics. Since the statistics data is not kept crashsafe, there is no way to know if it's corrupt or not. > and when i try to recover them via an analyze; (on all tables on the > database) the result is nothing... > i have to exexute the analyze commands twice to compute the statistics pg_stat_* are not directly affected by ANALYZE. They collect runtime statistics about activity in the tables, ANALYZE collects statistics about what's *in* the tables (primarily stored in pg_statistics, not pg_stat_*). -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5025: Aggregate function with subquery in 8.3 and 8.4.
Sheng Y. Cheng wrote: > The Session 4.2.7. Aggregate Expressions in 8.3 document at > http://www.postgresql.org/docs/8.3/static/sql-expressions.html states "The > last form invokes the aggregate once for each input row regardless of null > or non-null values." I am wondering if the result I saw from 8.4.0 is a bug > fix for 8.3.1? Well, a COUNT(ts.*) is in fact not of the last form, but the first. "ts.*" is a whole-row reference to t2, like just "ts" would be (as in "COUNT(t2)"). But there indeed seems to be something wrong. The query can be reduced into: SELECT t1.f1, COUNT(ts) FROM t1 LEFT JOIN (SELECT f1 As f1 FROM t2 OFFSET 0) AS ts ON t1.f1 = ts.f1 GROUP BY t1.f1; With this you can reproduce the discrepancy in CVS HEAD alone - the query produces a different result if you remove the "OFFSET 0": postgres=# SELECT t1.f1, COUNT(ts) FROM t1 postgres-# LEFT JOIN postgres-# (SELECT f1 As f1 FROM t2 OFFSET 0) AS ts postgres-# ON t1.f1 = ts.f1 postgres-# GROUP BY t1.f1; f1 | count -+--- aaa | 0 bbb | 1 ccc | 0 (3 rows) postgres=# SELECT t1.f1, COUNT(ts) FROM t1 LEFT JOIN (SELECT f1 As f1 FROM t2 /* OFFSET 0 */) AS ts ON t1.f1 = ts.f1 GROUP BY t1.f1; f1 | count -+--- aaa | 1 bbb | 1 ccc | 1 (3 rows) Without the OFFSET, the subquery is "pulled up" into the top query, and that optimization makes the difference. PostgreSQL 8.4 is more aggressive at that optimization, which is why you saw different results on 8.3 and 8.4. The "pullup" code transforms the "ts" reference into a ROW constructor: postgres=# explain verbose SELECT t1.f1, COUNT(ts) FROM t1 LEFT JOIN (SELECT f1 As f1 FROM t2 /* OFFSET 0 */) AS ts ON t1.f1 = ts.f1 GROUP BY t1.f1; QUERY PLAN GroupAggregate (cost=181.86..362.51 rows=200 width=64) Output: t1.f1, count(ROW(t2.f1)) ... That transformation isn't 100% accurate. A ROW expression with all NULL columns is not the same thing as a NULL whole-row expression when it comes to STRICT functions - a strict function is invoked with the former, but not the latter, even though both return true for an IS NULL test. That let's us write the test case as: CREATE FUNCTION stricttest (a anyelement) RETURNS boolean AS $$ SELECT true; $$ LANGUAGE SQL STRICT; postgres=# SELECT t1.f1, stricttest(ts) FROM t1 LEFT JOIN (SELECT f1 As f1 FROM t2 OFFSET 0) AS ts ON t1.f1 = ts.f1; f1 | stricttest -+ aaa | bbb | t ccc | (3 rows) postgres=# SELECT t1.f1, stricttest(ts) FROM t1 LEFT JOIN (SELECT f1 As f1 FROM t2 /* OFFSET 0 */) AS ts ON t1.f1 = ts.f1; f1 | stricttest -+ aaa | t bbb | t ccc | t (3 rows) I can see two possible interpretations for this: 1. The subquery pull-up code is broken, the transformation of a whole-row reference to ROW(...) is not valid. 2. The semantics of STRICT with row arguments is broken. It should be made consistent with IS NULL. Strict function should not be called if the argument is a row value with all NULL columns. I'm not sure which interpretation is correct. Thoughts? The SQL spec probably has something to say about this. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] inconsistent composite type null handling in plpgsql out variable
On Mon, Aug 31, 2009 at 07:26:59PM +0200, Pavel Stehule wrote: > 2009/8/31 Sam Mason : > > The more awkward case (to me anyway) is that the standard says (1,NULL) > > IS NULL should evaluate to TRUE. > > what? > > only (NULL, NULL) IS NULL is true Bah, sorry you're right! I was rattling my favorite tin and getting mixed up with the behavior with IS NOT NULL, the negation of which would say this row is null. I.e: SELECT NOT (1,NULL) IS NOT NULL; evaluates to TRUE. I think the consensus is that we should continue to follow the spec on this, but I was getting confused as to which operator contains the EXISTS and FORALL operator. I.e. a value "v" IS NULL iff all elements of "v" are not 'the null value', whereas "v" IS NOT NULL iff an element of "v" is 'the null value'. > p.s. what isn't consistent (maybe - there are more possible > interpretations) is > > (NULL, NULL) IS DISTINCT FROM NULL is true Yup, I'd agree with Merlin that a ROW consisting entirely of 'null values' should itself be 'the null value' (to use the terminology from the copy of the SQL spec I'm reading). I think this should also work recursively: SELECT ROW(ROW(NULL)) IS DISTINCT FROM NULL; should return FALSE, in my understanding. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5025: Aggregate function with subquery in 8.3 and 8.4.
2009/9/1 Heikki Linnakangas : > I can see two possible interpretations for this: > > 1. The subquery pull-up code is broken, the transformation of a > whole-row reference to ROW(...) is not valid. > > 2. The semantics of STRICT with row arguments is broken. It should be > made consistent with IS NULL. Strict function should not be called if > the argument is a row value with all NULL columns. > > I'm not sure which interpretation is correct. Thoughts? The SQL spec > probably has something to say about this. I suppose ts.* that wasn't joined is NULL. Not "a row value with all NULL columns" but "a NULL row value". contrib_regression=# SELECT t1.f1, ts.* IS NULL, ts.* FROM t1 LEFT JOIN (SELECT f1 FROM t2 -- offset 0 ) AS ts ON t1.f1 = ts.f1 ; f1 | ?column? | f1 -+--+- aaa | t| bbb | f| bbb ccc | t| (3 rows) So the 1. ROW(...) construction seems not valid. > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > Regards, -- Hitoshi Harada -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] inconsistent composite type null handling in plpgsql out variable
2009/9/1 Sam Mason : > On Mon, Aug 31, 2009 at 07:26:59PM +0200, Pavel Stehule wrote: >> 2009/8/31 Sam Mason : >> > The more awkward case (to me anyway) is that the standard says (1,NULL) >> > IS NULL should evaluate to TRUE. >> >> what? >> >> only (NULL, NULL) IS NULL is true > > Bah, sorry you're right! I was rattling my favorite tin and getting > mixed up with the behavior with IS NOT NULL, the negation of which > would say this row is null. I.e: > > SELECT NOT (1,NULL) IS NOT NULL; > > evaluates to TRUE. I think the consensus is that we should continue to > follow the spec on this, but I was getting confused as to which operator > contains the EXISTS and FORALL operator. I.e. a value "v" IS NULL iff > all elements of "v" are not 'the null value', whereas "v" IS NOT NULL > iff an element of "v" is 'the null value'. > >> p.s. what isn't consistent (maybe - there are more possible >> interpretations) is >> >> (NULL, NULL) IS DISTINCT FROM NULL is true > > Yup, I'd agree with Merlin that a ROW consisting entirely of 'null > values' should itself be 'the null value' (to use the terminology from > the copy of the SQL spec I'm reading). I think this should also work > recursively: > > SELECT ROW(ROW(NULL)) IS DISTINCT FROM NULL; > > should return FALSE, in my understanding. it's question. You ask, is it (NULL, NULL) same as NULL. Without some reduction - ROW(NULL, NULL) is really different than NULL. Pavel > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5026: No buffer space available error. Does any other solution exist other than changing windows paramete
The following bug has been logged online: Bug reference: 5026 Logged by: Narendra Shah Email address: narendra.s...@elitecore.com PostgreSQL version: 8.4 Operating system: Windows 2003 Description:No buffer space available error. Does any other solution exist other than changing windows paramete Details: I am using postgres 8.4 and i error of "No buffer space available" when connecting with the database. I am using machine which is exceeding more than 100 connection from my application(iview-syslog server) to postgres. I have updated configuration for postgres in postgres.conf for max connection. But then also it is giving me the error as No buffer space available. And it is happening with only iviewdb named database. Other databases are working well and good. After searching on net i found the limitation is from windows itself. and i have fixed the error with the following registry hack. But do postgres have any other solution which is changeble from postgres itself ? No buffer space available Fix Editting the registry is not for beginners, if you don't know what you're doing I suggest you don't try this, basically it's use at your own risk. Anytime you want to edit the registry it is a good idea to back it up first. For information on how to backup and restore the registry in all versions of Windows click here. If you are using Windows 95/98/Me follow these steps: First step is to launch the registry editor. To do this go to Start, Run and type regedit. In the left pane navigate to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\VxD\MSTCP once there, look for an entry called MaxConnections. If it exists highlight it by clicking it and then right click it and select modify. Increase the value (recommended value is to double the current value). If the MaxConnections entry does not exist you must create it. To do this, right click in the right pane and choose new from the menu and select String Value. Give it the name MaxConnections. Then right click it and select modify and enter a value of 200. Restart your computer, if all goes well then you fixed the problem, if not, revert the changes by restoring the registry. (You may have to reboot to safe mode to do this). If you are running Windows NT/2000/XP follow these steps: First step is to launch the registry editor. To do this go to Start, Run and type regedit. In the left pane navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters once there, you must create the entry TcpNumConnections. To do this, right click in the right pane and choose new from the menu and select DWORD Value. Give it the name TcpNumConnections. Then right click it and select modify and enter a value of 200. Restart your computer, if all goes well then you fixed the problem, if not, revert the changes by restoring the registry. (You may have to reboot to safe mode to do this). Regards, Narendra Shah. Cyberoam, Elitecore Technologies Ltd. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5027: SQL query error?
The following bug has been logged online:
Bug reference: 5027
Logged by: Dmitry
Email address: mas...@hsdesign.ru
PostgreSQL version: 8.3.5
Operating system: ALT Linux
Description:SQL query error?
Details:
Hello,
I try to make user rating by this query:
"BEGIN;
ALTER SEQUENCE service.rate_seq RESTART 1;
UPDATE service.user u
SET rate = sr.rate
FROM (
SELECT user_id, nextval('service.rate_seq') as rate
FROM
(
SELECT user_id, score FROM service.user
ORDER BY score DESC
) a
) sr
WHERE u.user_id = sr.user_id;
END;"
all work fine, but if while this query is executing another process do
"UPDATE service.user SET score=score+1" i get wrong sequence value; for
example
if user count = 55000 i can get 512321 in sequence ;(
why? query error?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5025: Aggregate function with subquery in 8.3 and 8.4.
Heikki Linnakangas writes: > I can see two possible interpretations for this: > 1. The subquery pull-up code is broken, the transformation of a > whole-row reference to ROW(...) is not valid. I think the problem is probably that we need a PlaceHolderVar wrapper around the ROW() constructor. > 2. The semantics of STRICT with row arguments is broken. It should be > made consistent with IS NULL. Well, that's a different argument. The immediate problem is that this case doesn't behave consistently with pre-8.4 behavior, which was not an intended change --- so I think we'd better make it work like before. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
The following bug has been logged online: Bug reference: 5028 Logged by: Joseph Shraibman Email address: j...@selectacast.net PostgreSQL version: 8.3.7 Operating system: Linux Description:CASE returns ELSE value always when type is "char" Details: [local]:playpen=> SELECT version(); version --- PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-44) (1 row) Time: 1.658 ms [local]:playpen=> [local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' ELSE c.relkind playpen-> END from (select 'r'::"char" AS relkind) c; relkind | relkind -+- r | t (1 row) Time: 1.407 ms [local]:playpen=> [local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' playpen-> END from (select 'r'::"char" AS relkind) c; relkind | case -+--- r | table (1 row) Time: 0.426 ms [local]:playpen=> [local]:playpen=> [local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' ELSE c.relkind playpen-> END from (select 'r'::char AS relkind) c; relkind | relkind -+- r | table (1 row) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5025: Aggregate function with subquery in 8.3 and 8.4.
On Tue, Sep 1, 2009 at 4:11 AM, Heikki Linnakangas wrote: > 2. The semantics of STRICT with row arguments is broken. It should be > made consistent with IS NULL. Strict function should not be called if > the argument is a row value with all NULL columns. not just STRICT, but coalesce(), libpq 'is null' bit, plpgsql row variables, type input/output, joins, etc. see recent threads on -hackers and -bugs note that fixing this would break code in the field (like mine for example) :-). merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
On Tue, Sep 01, 2009 at 04:36:25PM +, Joseph Shraibman wrote: > Description:CASE returns ELSE value always when type is "char" I think it's just silently truncating the literal to a single character. > [local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table' > WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' > THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' ELSE > c.relkind > playpen-> END from (select 'r'::"char" AS relkind) c; > relkind | relkind > -+- > r | t Here, 'r' maps to the "char" literal 'table' which PG interprets as the value 't'--i.e. PG silently chops of the 'able'. The bug would seem to be in your code, but PG could maybe throw an error to tell you this is what is happening? A possible fix would be to have your ELSE clause as: c.relkind::text As that way the other branches would be interpreted as text and they wouldn't be getting chopped off along the way. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
It appears the problem is with the "char" being in the ELSE expression, not being in the CASE expression [local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' ELSE c.relkind::char playpen-> END from (select 'r'::"char" AS relkind) c; relkind | relkind -+- r | table (1 row) Time: 0.674 ms [local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' ELSE c.relkind::"char" playpen-> END from (select 'r'::char AS relkind) c; relkind | relkind -+- r | t (1 row) Joseph Shraibman wrote: The following bug has been logged online: Bug reference: 5028 Logged by: Joseph Shraibman Email address: j...@selectacast.net PostgreSQL version: 8.3.7 Operating system: Linux Description:CASE returns ELSE value always when type is "char" Details: [local]:playpen=> SELECT version(); version --- PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-44) (1 row) Time: 1.658 ms [local]:playpen=> [local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' ELSE c.relkind playpen-> END from (select 'r'::"char" AS relkind) c; relkind | relkind -+- r | t (1 row) Time: 1.407 ms [local]:playpen=> [local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' playpen-> END from (select 'r'::"char" AS relkind) c; relkind | case -+--- r | table (1 row) Time: 0.426 ms [local]:playpen=> [local]:playpen=> [local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' ELSE c.relkind playpen-> END from (select 'r'::char AS relkind) c; relkind | relkind -+- r | table (1 row) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
Sam Mason wrote: On Tue, Sep 01, 2009 at 04:36:25PM +, Joseph Shraibman wrote: Description:CASE returns ELSE value always when type is "char" I think it's just silently truncating the literal to a single character. [local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' ELSE c.relkind playpen-> END from (select 'r'::"char" AS relkind) c; relkind | relkind -+- r | t Ah, OK it seems I didn't pay close enough attention. Here, 'r' maps to the "char" literal 'table' which PG interprets as the value 't'--i.e. PG silently chops of the 'able'. So the type of what is in the ELSE clause determines the type of the output? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5029: Download Trouble
The following bug has been logged online: Bug reference: 5029 Logged by: Alan Rubin Email address: alanrubi...@yahoo.com PostgreSQL version: unknown Operating system: Windows XP Description:Download Trouble Details: i'm trying to download postgresql-8.2.9-1.zip from http://www.postgresql.org/ftp/binary/v8.2.9/win32/. i click the postgresql-8.2.9-1.zip link and land on http://wwwmaster.postgresql.org/download/mirrors-ftp/binary/v8.2.9/win32/pos tgresql-8.2.9-1.zip. the mirror page recognizes USA and states "Your download will start automatically from this mirror in 5 seconds unless you select another mirror in the list below." the page will continuously refresh but the download doesn't begin. i tried clicking on HTTP and FTP but both links give me a "The webpage cannot be found" in IE7 and in FF3 i get a "550 /pub/mirrors/postgresql/binary/v8.2.9/win32/postgresql-8.2.9-1.zip: No such file or directory" alert pop up for FTP and "The requested URL /pub/mirrors/postgresql/binary/v8.2.9/win32/postgresql-8.2.9-1.zip was not found on this server." when i click on HTTP. thoughts? thank you for your time -alan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5029: Download Trouble
Alan Rubin wrote: The following bug has been logged online: Bug reference: 5029 Logged by: Alan Rubin Email address: alanrubi...@yahoo.com PostgreSQL version: unknown Operating system: Windows XP Description:Download Trouble Details: i'm trying to download postgresql-8.2.9-1.zip from http://www.postgresql.org/ftp/binary/v8.2.9/win32/. i click the postgresql-8.2.9-1.zip link and land on http://wwwmaster.postgresql.org/download/mirrors-ftp/binary/v8.2.9/win32/pos tgresql-8.2.9-1.zip. the mirror page recognizes USA and states "Your download will start automatically from this mirror in 5 seconds unless you select another mirror in the list below." the page will continuously refresh but the download doesn't begin. i tried clicking on HTTP and FTP but both links give me a "The webpage cannot be found" in IE7 and in FF3 i get a "550 /pub/mirrors/postgresql/binary/v8.2.9/win32/postgresql-8.2.9-1.zip: No such file or directory" alert pop up for FTP and "The requested URL /pub/mirrors/postgresql/binary/v8.2.9/win32/postgresql-8.2.9-1.zip was not found on this server." when i click on HTTP. I to got 404 - File Not Found errors on all the US links and a random sampling of others. so its not just you. it appears only 8.2.11, .12, and .13 are still on the servers. http://ftp9.us.postgresql.org/pub/mirrors/postgresql/binary/ why do you want 8.2.9, thats a rather obsolete version. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5025: Aggregate function with subquery in 8.3 and 8.4.
I wrote: > I think the problem is probably that we need a PlaceHolderVar wrapper > around the ROW() constructor. I looked into this a bit more. The issue is that flattening of subqueries that are inside an outer join first puts PlaceHolderVars around non-nullable output expressions of the subquery, and then applies ResolveNew() to substitute those expressions for upper references to the subquery outputs. However, a whole-row Var referencing the subquery is expanded by ResolveNew into a ROW() expression over the subquery outputs. To preserve compatibility with pre-8.4 behavior, what we really need here is to have a PlaceHolderVar around the ROW(), not on the individual expressions inside it. While it's not that hard to put in the PlaceHolderVar, the easy way to do it would require passing the PlannerInfo "root" struct to ResolveNew, which goes well beyond my threshold of pain from a modularity standpoint --- ResolveNew isn't part of the planner and has no business using that struct. ResolveNew's API is a study in ugliness already, so I'm thinking it's time to bite the bullet and refactor it. The idea that comes to mind is to provide a callback function and "void *" context argument, which ResolveNew would call upon finding a Var that needs substitution. The existing guts of ResolveNew would move into a callback that is specific to rewriteHandler.c's uses, and we'd write a different one for the planner. The PlannerInfo link would be hidden within the "void *" argument so we'd avoid exposing it to rewriter code. Comments? I believe BTW that there are related issues in other places where we expand composites into RowExprs. But the other places have been doing that for awhile. I think that for 8.4 our goals should be limited to not changing the behavior compared to prior releases. If any consensus is reached on the general issue of how we want "row() is null" to behave, then it'll be time to reconsider the other stuff. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
Joseph Shraibman writes: > So the type of what is in the ELSE clause determines the type of the output? If all the other branches are unknown literals, yes. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
Tom Lane wrote: > Joseph Shraibman writes: >> So the type of what is in the ELSE clause determines the type of >> the output? > > If all the other branches are unknown literals, yes. What's the best place to look to get a handle on what the benefits are of treating character string literals as being of unknown type? (Other than the obvious backward compatibility issues.) -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
"Kevin Grittner" writes: > Tom Lane wrote: >> Joseph Shraibman writes: >>> So the type of what is in the ELSE clause determines the type of >>> the output? >> >> If all the other branches are unknown literals, yes. > What's the best place to look to get a handle on what the benefits are > of treating character string literals as being of unknown type? > (Other than the obvious backward compatibility issues.) I think the odds of changing that are not measurably different from zero. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
Tom Lane wrote: > "Kevin Grittner" writes: >> Tom Lane wrote: >>> Joseph Shraibman writes: So the type of what is in the ELSE clause determines the type of the output? >>> >>> If all the other branches are unknown literals, yes. > >> What's the best place to look to get a handle on what the benefits >> are of treating character string literals as being of unknown type? >> (Other than the obvious backward compatibility issues.) > > I think the odds of changing that are not measurably different from > zero. I figured that; I'm just trying to understand what seems to me like an odd wart on the type system. I figure I must be missing something important, so I'd kinda like to find out what that is. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] pg_ctl infinite loop and memory leak
To reproduce:
1. initdb -D data
2. cat /dev/null > data/postgresql.conf
3. pg_ctl -w -D data start
I attached a quick patch that seems to do the trick. It appears that
fgets() will always return non-NULL if the size passed in is 1 (i.e.
maxlength in the caller is 0).
The patch also changes the same readfile() function in initdb.c. I
assume it's not a practical problem there, but it should be fixed.
Thanks to Corry Haines (chaines at truviso dot com) for reporting the
problem.
Regards,
Jeff Davis
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 4b0b723..e544e3e 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -409,11 +409,10 @@ readfile(char *path)
rewind(infile);
nlines = 0;
- while (fgets(buffer, maxlength + 1, infile) != NULL)
- {
- result[nlines] = xstrdup(buffer);
- nlines++;
- }
+
+ if (maxlength > 0)
+ while (fgets(buffer, maxlength + 1, infile) != NULL)
+ result[nlines++] = xstrdup(buffer);
fclose(infile);
free(buffer);
diff --git a/src/bin/pg_ctl/pg_ctl.c b/src/bin/pg_ctl/pg_ctl.c
index 08e38e7..ede6e5b 100644
--- a/src/bin/pg_ctl/pg_ctl.c
+++ b/src/bin/pg_ctl/pg_ctl.c
@@ -329,8 +329,10 @@ readfile(const char *path)
/* now reprocess the file and store the lines */
rewind(infile);
nlines = 0;
- while (fgets(buffer, maxlength + 1, infile) != NULL)
- result[nlines++] = xstrdup(buffer);
+
+ if (maxlength > 0)
+ while (fgets(buffer, maxlength + 1, infile) != NULL)
+ result[nlines++] = xstrdup(buffer);
fclose(infile);
free(buffer);
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
On Tue, 2009-09-01 at 13:49 -0500, Kevin Grittner wrote: > I figured that; I'm just trying to understand what seems to me like an > odd wart on the type system. I figure I must be missing something > important, so I'd kinda like to find out what that is. If I understand your question, you're comparing: (a) leaving a literal as "unknown" until you've finished inferring types (current behavior) (b) casting every unknown to text immediately, and then trying to infer the types In general, option (b) eliminates information that might be useful for making good inferences about the correct operators to use, and also finding cases of ambiguity. For instance, consider the current behavior: 1. select now()::text < 'January 01, 2009'; -- true 2. select now() < 'January 01, 2009'; -- false 3. select now() < 'January 01, 2009'::text; ERROR: operator does not exist: timestamp with time zone < text Example #2 shows that we can infer the the RHS is of type timestamptz based on the type of the LHS. That's desirable behavior in any type-inferencing system -- without it you might as well just explicitly cast all literals. Example #3 is ambiguous: we have no way to know whether to choose "< (timestamptz, timestamptz)" or "< (text, text)", and an ERROR is desirable behavior to avoid confusing results. But you can't have both of those desirable behaviors unless you are somehow aware that "'January 01, 2009'" is something more malleable than "now()" in example #2. Calling the RHS "unknown" in example #2 gives us that information. Regards, Jeff Davis -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5025: Aggregate function with subquery in 8.3 and 8.4.
I blithely opined: > I believe BTW that there are related issues in other places where we > expand composites into RowExprs. But the other places have been doing > that for awhile. I think that for 8.4 our goals should be limited to > not changing the behavior compared to prior releases. So while I was testing my fix for this, I found out that that's more complicated than I thought. Consider these examples in the regression database: select t1.q2, count(t2.*) from int8_tbl t1 left join int8_tbl t2 on (t1.q2 = t2.q1) group by t1.q2 order by 1; select t1.q2, count(t2.*) from int8_tbl t1 left join (select * from int8_tbl) t2 on (t1.q2 = t2.q1) group by t1.q2 order by 1; select t1.q2, count(t2.*) from int8_tbl t1 left join (select * from int8_tbl offset 0) t2 on (t1.q2 = t2.q1) group by t1.q2 order by 1; select t1.q2, count(t2.*) from int8_tbl t1 left join (select q1, case when q2=1 then 1 else q2 end as q2 from int8_tbl) t2 on (t1.q2 = t2.q1) group by t1.q2 order by 1; If you believe that "t2.*" should go to NULL in a join-extended row, then the correct answer for all four of these is q2 | count ---+--- -4567890123456789 | 0 123 | 2 456 | 0 4567890123456789 | 6 (4 rows) However, the actual behavior of every release since 8.0 has been that the second case gives q2 | count ---+--- -4567890123456789 | 1 123 | 2 456 | 1 4567890123456789 | 6 (4 rows) ie, t2.* fails to go to NULL because it's expanded as ROW(t2.q1,t2.q2). The OFFSET 0 in the third case restores expected behavior by preventing flattening of the subquery, and up till 8.4 the CASE expression in the fourth case did too. With the fix I was just about to apply, all four cases give the first set of results. This clearly satisfies the principle of least astonishment, at least more nearly than what we have; but it equally clearly is *not* going to restore 8.4 to work just like 8.3. I'm inclined to apply the patch to 8.4 anyway, because it seems like a bug fix. I would consider patching further back except there's no chance of making it work in older branches, at least not without destabilizing them quite a bit (the PlaceHolderVar mechanism would have to be back-ported). It might be possible to fix the older branches by not flattening subqueries that have whole-row references; but even that would take nontrivial work, and it'd be sacrificing performance to fix a corner case no one has previously complained about. So I'm leaning to patching 8.4 and leaving the older branches alone. Thoughts? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_ctl infinite loop and memory leak
Jeff Davis writes: > I attached a quick patch that seems to do the trick. It appears that > fgets() will always return non-NULL if the size passed in is 1 (i.e. > maxlength in the caller is 0). Huh, interesting corner case. I'd be inclined to fix by initializing maxlength to 1 though. Where's the memory leak? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_ctl infinite loop and memory leak
On Tue, 2009-09-01 at 22:01 -0400, Tom Lane wrote: > Huh, interesting corner case. I'd be inclined to fix by initializing > maxlength to 1 though. > > Where's the memory leak? The xstrdup() on the zero-length string. Regards, Jeff Davis -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_ctl infinite loop and memory leak
Jeff Davis writes: > On Tue, 2009-09-01 at 22:01 -0400, Tom Lane wrote: >> Where's the memory leak? > The xstrdup() on the zero-length string. Oh, I see. But actually, it's also clobbering memory like crazy (since we'll run off the result[] array in no time). Surprising it doesn't crash from that. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] lost statistics; analyze needs to execute twice
Magnus Hagander writes: > On Tue, Sep 1, 2009 at 00:02, Jaime > Casanova wrote: >> when i issue an "immediate shutdown" the statistics on all tables >> disappear... > That is by design. Whenever the server goes into crash recovery on > startup, it will clean out the statistics. Since the statistics data > is not kept crashsafe, there is no way to know if it's corrupt or not. Yeah. I don't think we'll change that. "-m immediate" is not the recommended way to stop the server; it's more like the big red button that dumps Halon all over your equipment. You expect to have to clean up afterwards. >> and when i try to recover them via an analyze; (on all tables on the >> database) the result is nothing... >> i have to exexute the analyze commands twice to compute the statistics > pg_stat_* are not directly affected by ANALYZE. They collect runtime > statistics about activity in the tables, Yeah, but ANALYZE does update the stats collector stats too. I looked into what's actually happening here, and it's a bit interesting: 1. Stats collector tables are empty. 2. ANALYZE does its thing and sends a PgStat_MsgAnalyze message. 3. pgstat_recv_analyze *intentionally throws the data away*, on the grounds that if it were interesting there would already be a stats table entry for the table. 4. At completion of ANALYZE, the regular tabstat machinery sends off a tabstat message for the table, because guess what, ANALYZE did a scan of that table, and there are t_blocks_fetched counts to report. 5. pgstat_recv_tabstat happily creates a table entry. (The pg_statio counts in it are nonzero, even though the pg_stat counts aren't.) 6. Now, if you repeat the cycle, the stats collector will accept the second PgStat_MsgAnalyze message, because this time there's a stats table entry. This is a bit silly I guess --- we dropped the data but didn't actually save any stats-table space. I'm inclined to think that the don't-create-a-table-entry behavior in pgstat_recv_vacuum and pgstat_recv_analyze should just be dropped. I'm dubious that it ever worked as intended. To have it work right you'd need to suppress vacuum/analyze physical I/O from the tabstats counts, which doesn't seem like an amazingly good idea. Moreover, autovacuum is unlikely to issue vacuum or analyze against a table that hasn't already got a stats-table entry, so the filter doesn't seem likely to buy much if it did work. There might have been some value in the idea back when cron-driven database-wide VACUUM ANALYZE was the standard maintenance mechanism, but that's not the recommended thing anymore. Comments? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
