[HACKERS] collector/autovacuum after crash (8.1beta3)
Hi All, At system crash or poweroff the autovacuum statistics will be lost, because this statistics only stored in RAM and saved/restored at service shutdown/startup. I think it should be saved periodically and not to be deleted after crash. -Sygma ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] pg_stat_activity EXISTS bug in 8.0.0beta3
I was found a bug in PostgreSQL 8.0.0beta3. It isn't in PostgreSQL 7.4.5. $ createdb test $ psql test test=# SELECT version (); version - PostgreSQL 8.0.0beta3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (1 row) test=# LISTEN a; test=# LISTEN b; test=# SELECT * FROM pg_listener l WHERE NOT EXISTS (select 1 from pg_stat_activity a where a.procpid=l.listenerpid); relname | listenerpid | notification -+-+-- b |2020 |0 (1 row) test=# LISTEN c; test=# LISTEN d; test=# LISTEN e; test=# LISTEN f; test=# SELECT * FROM pg_listener l WHERE NOT EXISTS (select 1 from pg_stat_activity a where a.procpid=l.listenerpid); relname | listenerpid | notification -+-+-- b |2020 |0 d |2020 |0 f |2020 |0 (3 rows) test=# SELECT * INTO temp_listener FROM pg_listener; SELECT test=# SELECT * FROM temp_listener l WHERE NOT EXISTS (select 1 from pg_stat_activity a where a.procpid=l.listenerpid); relname | listenerpid | notification -+-+-- b |2020 |0 d |2020 |0 f |2020 |0 (3 rows) (not a pg_listener bug) test=# SELECT * INTO temp_stat_activity FROM pg_stat_activity; SELECT test=# SELECT * FROM temp_listener l WHERE NOT EXISTS (select 1 from temp_stat_activity a where a.procpid=l.listenerpid); relname | listenerpid | notification -+-+-- (0 rows) (not an EXISTS bug) Note: If backend terminated abnormally or server powered down, the pg_listener's records cannot be deleted. I using a script at times: DELETE FROM pg_listener WHERE NOT EXISTS (select 1 from pg_stat_activity a where a.procpid=pg_listener.listenerpid); In PostgreSQL 7.4.5 it's working fine. TODO: Allow LISTEN/NOTIFY to store info in memory rather than tables is good idea. How soon may i expect this feature? :) -Sygma ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
Hi all, I was implement the transaction idle timeout function in PostgreSQL (version 7.4.5 and 8.0.0beta2) test=# SET trans_idle_timeout=10; SET test=# BEGIN; BEGIN (ps) ... postgres: sygma test [local] idle in transaction (3) postgres: sygma test [local] idle in transaction (2) postgres: sygma test [local] idle in transaction (1) postgres: sygma test [local] idle in transaction (aborted) test=# INSERT INTO test (a,b) VALUES (1,'a'); ERROR: current transaction is aborted, commands ignored until end of transaction block The code is very simple and stable. Changed files: include/storage/proc.h backend/libpq/pqcomm.c backend/libpq/be-secure.c backend/utils/misc/guc.c TODO: SSL connection, manual, psql help tab-competition Do you interest it? Regards, Sygma ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] numeric to text (7.3)
On Mon, 2 Dec 2002, Joel Burton wrote:
joel@joel=# select round('2.000'::numeric);
round
---
2
(1 row)
joel@joel=# select round('2.000'::numeric,2);
round
---
2.00
(1 row)
OK, but:
template1=# select round('2.001'::numeric);
round
---
2
(1 row)
template1=# select round('2.001'::numeric,2);
round
---
2.00
(1 row)
The good idea (in 7.2):
template1=# select text('2.000'::numeric);
text
--
2
(1 row)
template1=# select text('2.001'::numeric);
text
---
2.001
(1 row)
This feature is missing from 7.3..
(new round function is good idea (e.g. fround(numeric))
-Sygma
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] numeric to text (7.3)
On Mon, 2 Dec 2002, Rod Taylor wrote:
double precision | pg_catalog | round | double precision
numeric | pg_catalog | round | numeric
numeric | pg_catalog | round | numeric, integer
Looks like round still exists to me.
Rod, you don't understand me. :)
I needn't round, or the valueless zeroes too.
It's good (in older version of pSQL):
2.000::numeric - 2
2.001::numeric - 2.001
It's ugly (in 7.3):
2.000::numeric - 2.000
2.001::numeric - 2.001
or
round(2.000::numeric,2) - 2.00
round(2.001::numeric,2) - 2.00
Joel had got a good idea:
joel@joel=# select rtrim(rtrim('2.000'::numeric, '0'),'.');
rtrim
---
2
(1 row)
.. but i prefer the old text(numeric) function :)
Thanks!
-Sygma
---(end of broadcast)---
TIP 3: 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
[HACKERS] numeric to text (7.3)
Hello In PostgreSQL 7.2/7.1: template1=# select text(2.000::numeric); text -- 2 (1 row) In 7.3: template1=# select text(2.000::numeric); text --- 2.000 (1 row) The text(numeric) function doesn't round numbers. :( This is bug or feature? :) -Sygma ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
