[GENERAL] cp: cannot stat `/mnt/server/archivedir/00000001.history': No such file or directory
Hello List Member, I am trying to setup recovery from WAL, recovery is done properly but there are some alerting messages in the log file log file : * 2007-11-27 14:08:04 IST LOG: database system was interrupted at 2007-11-27 13:59:44 IST 2007-11-27 14:08:04 IST LOG: starting archive recovery 2007-11-27 14:08:04 IST LOG: restore_command = cp /mnt/server/archivedir/%f %p cp: cannot stat `/mnt/server/archivedir/0001.history': No such file or directory 2007-11-27 14:08:04 IST LOG: restored log file 00010053.0020.backup from archive 2007-11-27 14:08:05 IST LOG: restored log file 00010053 from archive 2007-11-27 14:08:05 IST LOG: checkpoint record is at 0/5320 2007-11-27 14:08:05 IST LOG: redo record is at 0/5320; undo record is at 0/0; shutdown FALSE 2007-11-27 14:08:05 IST LOG: next transaction ID: 0/13326; next OID: 69934 2007-11-27 14:08:05 IST LOG: next MultiXactId: 1; next MultiXactOffset: 0 2007-11-27 14:08:05 IST LOG: automatic recovery in progress 2007-11-27 14:08:05 IST LOG: redo starts at 0/5370 2007-11-27 14:08:06 IST LOG: restored log file 00010054 from archive 2007-11-27 14:08:06 IST LOG: restored log file 00010055 from archive 2007-11-27 14:08:07 IST LOG: restored log file 00010056 from archive cp: cannot stat `/mnt/server/archivedir/00010057': No such file or directory 2007-11-27 14:08:07 IST LOG: record with zero length at 0/5770 2007-11-27 14:08:07 IST LOG: redo done at 0/5720 cp: cannot stat `/mnt/server/archivedir/00010057': No such file or directory 2007-11-27 14:08:07 IST LOG: archive recovery complete 2007-11-27 14:08:08 IST LOG: database system is ready 2007-11-27 14:08:18 IST LOG: archived transaction log file 00010057 ** from above log it seems that server is looking for 0001.history which is not there , but this file is not created by the server insted it created 0001.backup (which document also suggest is the history file) Should i ignore this message? Secondly, output from 0001.backup file: ** START WAL LOCATION: 0/5320 (file 00010053) STOP WAL LOCATION: 0/5400 (file 00010054) CHECKPOINT LOCATION: 0/5320 START TIME: 2007-11-27 13:59:44 IST LABEL: /mnt/server/base_backup/20071127 STOP TIME: 2007-11-27 14:00:09 IST *** last WAL when server was shut down was 00010059 and file upto 00010056 where archived. I copied WAL 00010057,00010058 ,00010059 to another location. removed the data directory and restored the same from base backup, cleaned the pg_xlog and kept WAL 00010057,00010058 ,00010059 in it. Now from log it can be seen warning for WAL 00010057 Can anybody please explain me wht is going wrong? JUST for note:recover was complete. Thanks in advance With regards Ashish... - Find a mall, hotel or ATM in any part of India. Click here.
[GENERAL] Trigger problem
Hi list, I have a trigger to log what the users do on the database: DECLARE _query VARCHAR; DECLARE valid BOOL; DECLARE act VARCHAR; DECLARE tab VARCHAR; DECLARE field VARCHAR; BEGIN IF(TG_OP = 'DELETE') THEN act = 'DELETION of row with id: ' || OLD.id; ELSIF(TG_OP = 'UPDATE') THEN IF NEW.idOLD.id THEN field = 'id from: ' || OLD.id || ' TO ' || NEW.id; ELSIF NEW.entry_noOLD.entry_no THEN RAISE EXCEPTION 'Die generische Seriennummer kann von Ihnen nicht verändert werden!'; RETURN NULL; ELSIF NEW.gruppeOLD.gruppe THEN valid = NEW.gruppe 0 AND NEW.gruppe999 AND NEW.gruppe IS NOT NULL; field = 'field gruppe from: ' || OLD.gruppe || ' TO ' || NEW.gruppe; ELSIF NEW.kombiOLD.kombi THEN valid = (NEW.kombi=0) AND (NEW.kombi=2); field = 'field kombi from: ' || OLD.kombi || ' TO ' || NEW.kombi; END IF; act = 'UPDATE OF ' || field || ' with id: ' || OLD.id; END IF; tab = TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME; INSERT INTO history(aennam, action, table_name) VALUES(current_user, act, tab); RETURN NULL; END; Now the problem is that a tuple gets added to the table history, but the field action (whatever the user did) is 99% empty, whereas the others are filled and I don't see why... Any hint is greatly appreciated Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trigger problem
am Tue, dem 27.11.2007, um 10:38:09 +0100 mailte Christian Rengstl folgendes: Hi list, act = 'DELETION of row with id: ' || OLD.id; act = 'UPDATE OF ' || field || ' with id: ' || ... INSERT INTO history(aennam, action, table_name) VALUES(current_user, act, tab); Now the problem is that a tuple gets added to the table history, but the field action (whatever the user did) is 99% empty, whereas the others are filled and I don't see why... Any hint is greatly appreciated Maybe sometime the concateneted fields (e.g. field, OLD.id) contains NULL-values. If so, the complete string 'act' will be NULL. Solution: use coalesce(field,''). Hope that helps. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Trigger problem
Thanks for pinpointing that out. After including your solution it shows at least something like UPDATE OF with id: 123456567. Unfortunately after using coalesce, too, when building the field variable like: field = 'field gruppe from: ' || coalesce(OLD.gruppe, '') || ' TO ' || coalesce(NEW.gruppe, ''); there still seem to be null values. Of course, it can be that the user deletes a value and then there is a NULL value, say for NEW.gruppe for example, but shouldn't the coalesce in this case avoid that the whole field-string is empty? Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 On Tue, Nov 27, 2007 at 10:53 AM, in message [EMAIL PROTECTED], A. Kretschmer [EMAIL PROTECTED] wrote: am Tue, dem 27.11.2007, um 10:38:09 +0100 mailte Christian Rengstl folgendes: Hi list, act = 'DELETION of row with id: ' || OLD.id; act = 'UPDATE OF ' || field || ' with id: ' || ... INSERT INTO history(aennam, action, table_name) VALUES(current_user, act, tab); Now the problem is that a tuple gets added to the table history, but the field action (whatever the user did) is 99% empty, whereas the others are filled and I don't see why... Any hint is greatly appreciated Maybe sometime the concateneted fields (e.g. field, OLD.id) contains NULL- values. If so, the complete string 'act' will be NULL. Solution: use coalesce(field,''). Hope that helps. Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Linux v.s. Mac OS-X Performance
Hello, sorry for butting in, but I'm just curious... resolution? http://archives.postgresql.org/pgsql-general/2007-11/msg00946.php conclusion? Mac was still pretty slow in comparison Anyway, how does MacOS X (both 10.4 and 10.5) compare to Windows (2000, XP, Vista etc.) on the same hardware? And Linux to (Free-/Net-/whatever) BSD? No flamebait, I'm just wondering whether the performance gain is worth the learning effort required for Linux or BSD compared to the Mac. Sincerely, Wolfgang Keller ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] select using an index
Hi
Recently i created an index in a table using a function (not a column) as
following
create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));,
where idr is a function that returns a real number,
as a result i got the following table
mhc2db= \d precalc;
Table public.precalc
Column | Type| Modifiers
-+---+--
id | integer | not null default
nextval('precalc_id_seq'::regclass)
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
h2iab btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)),
now, i would like to perform a query using this index, something like
Select * from precalc where h2iab2
but obviously h2iab is not a column...
some of you knows what i should do?
thanks
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
Re: [GENERAL] select using an index
Pau Marc Munoz Torres írta:
Hi
Recently i created an index in a table using a function (not a
column) as following
create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));,
where idr is a function that returns a real number,
as a result i got the following table
mhc2db= \d precalc;
Table public.precalc
Column | Type| Modifiers
-+---+--
id | integer | not null default
nextval('precalc_id_seq'::regclass)
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
h2iab btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)),
now, i would like to perform a query using this index, something like
Select * from precalc where h2iab2
but obviously h2iab is not a column...
some of you knows what i should do?
thanks
Formulate your WHERE clause so it matches
your index's expression. E.g.
SELECT * FROM precalc WHERE
idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying) 2;
Best regards,
Zoltán Böszörményi
--
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] Invalid Pointer Operation while reading tuples
Hello, I'm receiving an error message Invalid pointer operation when I'm reading data from my database. I'm using PostGreSQL 8.2.5 with Windows 2003 Server Enterprise Edition. When I saw the psqlodbc log I found teh messages below: [11.281]CONN ERROR: func=PGAPI_SetConnectOption, desc='', errnum=206, errmsg='Illegal parameter value for SQL_TXN_ISOLATION' [0.250]STATEMENT ERROR: func=set_statement_option, desc='', errnum=30, errmsg='The option may be for MS SQL Server(Set)' Some parts of the log: [11.281]conn=02FB5D98, query='COMMIT' [11.281]CONN ERROR: func=PGAPI_SetConnectOption, desc='', errnum=206, errmsg='Illegal parameter value for SQL_TXN_ISOLATION' [11.281] [11.281]henv=02FB2F58, conn=02FB5D98, status=1, num_stmts=16 [11.281]sock=02FA3178, stmts=02DEBD38, lobj_type=17288 [11.281] Socket Info --- [11.281]socket=724, reverse=0, errornumber=0, errormsg='(NULL)' [11.281]buffer_in=48140048, buffer_out=48153296 [11.281]buffer_filled_in=18, buffer_filled_out=0, buffer_read_in=18 [0.250]conn=02DE3A70, query='close SQL_CUR02FA8CA8' [0.250]conn=02DE3A70, query='RELEASE _per_query_svp_' [0.250]conn=02DE3A70, query='close SQL_CUR02DE85C8' [0.250]conn=02DE3A70, query='RELEASE _per_query_svp_' [0.250]conn=02DE3A70, query='COMMIT' [0.250]STATEMENT ERROR: func=set_statement_option, desc='', errnum=30, errmsg='The option may be for MS SQL Server(Set)' [0.250] [0.250] hdbc=02DE3A70, stmt=02DEABE8, result= [0.250] prepare=0, internal=0 [0.250] bindings=, bindings_allocated=0 [0.250] parameters=, parameters_allocated=0 [0.250] statement_type=-2, statement='(NULL)' [0.250] stmt_with_params='(NULL)' [0.250] data_at_exec=-1, current_exec_param=-1, put_data=0 [0.250] currTuple=-1, current_col=-1, lobj_fd=-1 [0.250] maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1 [0.250] cursor_name='' [0.250] QResult Info --- [0.250]CONN ERROR: func=set_statement_option, desc='', errnum=0, errmsg='(NULL)' Can you help me? Thanks, Cláudia. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] autovacuum process blocks without reporting a deadlock
On Nov 24, 2007 6:20 AM, Tom Lane [EMAIL PROTECTED] wrote: What other indexes does that table have? regards, tom lane Hi, last night it happend again. In the log-snippet u can see all indexes of this table: [9293 / 2007-11-26 21:46:28 CET]CONTEXT: SQL statement UPDATE hst_timerecording SET id_timerecording_join = NULL WHERE id_timerecording_join = -1 PL/pgSQL function set_id_timerecording_join line 121 at SQL statement SQL statement UPDATE hst_timerecording SET sales_volume = NULL, sales_volume_commission = NULL WHERE business_day = $1 AND id_employee = $2 PL/pgSQL function compress_salaries_day line 168 at SQL statement SQL statement SELECT compress_salaries_day( $1 , NULL, NULL) PL/pgSQL function compress line 460 at perform [9293 / 2007-11-26 21:46:28 CET]LOCATION: exec_stmt_raise, pl_exec.c:2110 [9317 / 2007-11-26 21:46:34 CET]DEBUG: 0: index hst_timerecording_business_day_idx now contains 8640 row versions in 80 pages [9317 / 2007-11-26 21:46:34 CET]DETAIL: 4469 index row versions were removed. 13 index pages have been deleted, 9 are currently reusable. CPU 0.00s/0.00u sec elapsed 3.40 sec. [9317 / 2007-11-26 21:46:34 CET]LOCATION: lazy_vacuum_index, vacuumlazy.c:736 [9317 / 2007-11-26 21:46:39 CET]DEBUG: 0: index hst_timerecording_id_employee_idx now contains 8640 row versions in 95 pages [9317 / 2007-11-26 21:46:39 CET]DETAIL: 4469 index row versions were removed. 10 index pages have been deleted, 6 are currently reusable. CPU 0.00s/0.00u sec elapsed 4.22 sec. [9317 / 2007-11-26 21:46:39 CET]LOCATION: lazy_vacuum_index, vacuumlazy.c:736 [9317 / 2007-11-26 21:46:43 CET]DEBUG: 0: index hst_timerecording_id_timerecording_idx now contains 8640 row versions in 97 pages [9317 / 2007-11-26 21:46:43 CET]DETAIL: 4469 index row versions were removed. 11 index pages have been deleted, 5 are currently reusable. CPU 0.00s/0.00u sec elapsed 4.40 sec. [9317 / 2007-11-26 21:46:43 CET]LOCATION: lazy_vacuum_index, vacuumlazy.c:736 This are again the last log-entries of the blocking processes. Obviously the three indexes where successful vacuumed? I think this are the relevant pg_locks entries: relation7568577875686189 9017862 25467 AccessShareLock f relation7568577875686189 9009323 9317ShareUpdateExclusiveLock t relation7568577875686189 9009312 9293AccessShareLock t relation7568577875686189 9009312 9293RowExclusiveLockt relation7568577875686189 9009312 9293AccessExclusiveLock f relation7568577875686189 9012978 28370 AccessShareLock f 75686189 is the table hst_timerecording. for me it looks like the autovacuum is not releasing the blocking ShareUpdateExclusiveLock? I hope this infos could help. regards, t thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Connection idle broken
Hi, I have trouble with my java application. Since i change the network configuration, the postgresql idle connections broken after 10 minutes. (i set authentication_timeout = 600 in postgresql.conf). My network: 192.168.1.1 (postgresql server and gateway server) 192.168.0.1 (linksys wireless router) 192.168.0.x (clients) How i make my idle connections alive for long time? Any idea? Regards, Fernando Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] autovacuum process blocks without reporting a deadlock
On Nov 27, 2007 3:14 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: hat are the column headings? I find this difficult to read. Please post the whole of pg_locks. I may be missing something but I think we're missing part of the picture here. Autovacuum does not seem to be locking on anything. Unfortunately i logged the pg_locks-state not well formated. i added now the heading manually. maybe it is better or i have to wait till tomorrow morning. but what i think i can see, is that the process with pid 9317 is holding a ShareUpdateExclusiveLock locktype | database| relation | transaction | pid | mode | granted relation| 75685778| 75686189 |9009323| 9317 | ShareUpdateExclusiveLock | t and process with pid 9293 is waiting to get AccessExclusiveLock on the same relation: relation| 75685778| 75686189 |9009312| 9293 | AccessShareLock | f ... and 9317 is the autvacuumprocess? afterkiling it, erything is fine. i attached the full pg_lock. regards, thomas locktype | database | relation |transactionid | classid | objid | objsubid | transaction | pid | mode | granted relation7568577875686652 9009312 9293AccessShareLock t transactionid 9017862 9017862 25467 ExclusiveLock t relation7568577877862554 9009312 9293RowExclusiveLockt relation7568577875686561 9009312 9293AccessShareLock t relation7568577875686037 9012978 28370 AccessShareLock t relation7568577875686037 9012978 28370 RowExclusiveLockt relation7568577875686371 9012978 28370 AccessShareLock t relation7568577875686371 9012978 28370 RowExclusiveLockt relation7568577875686671 9009312 9293AccessShareLock t relation7568577875686189 9017862 25467 AccessShareLock f relation7568577875686044 9009312 9293AccessShareLock t relation7568577875686138 9009312 9293AccessShareLock t relation7568577875686138 9009312 9293RowExclusiveLockt relation7568577875685984 9017862 25467 AccessShareLock t relation7568577875686133 9009312 9293AccessShareLock t relation7568577875686652 9012978 28370 AccessShareLock t transactionid 9018078 9018078 10605 ExclusiveLock t relation7568577875685979 9009312 9293AccessShareLock t relation7568577875686031 9012978 28370 AccessShareLock t relation7568577875686031 9012978 28370 RowExclusiveLockt relation7568577875686011 9009312 9293AccessShareLock t relation7568577875686176 9009312 9293AccessShareLock t relation7568577877862554 9009323 9317ShareUpdateExclusiveLockt relation7568577875685833 9012978 28370 AccessShareLock t relation7568577875686189 9009323 9317ShareUpdateExclusiveLockt relation7568577875686727 9009312 9293AccessShareLock t relation7568577875686199 9017862 25467 AccessShareLock t relation7568577877862552 9009312 9293AccessShareLock t relation7568577877862552
Re: [GENERAL] autovacuum process blocks without reporting a deadlock
Thomas Chille wrote: I think this are the relevant pg_locks entries: relation7568577875686189 9017862 25467 AccessShareLock f relation7568577875686189 9009323 9317ShareUpdateExclusiveLock t relation7568577875686189 9009312 9293AccessShareLock t relation7568577875686189 9009312 9293RowExclusiveLockt relation7568577875686189 9009312 9293AccessExclusiveLock f relation7568577875686189 9012978 28370 AccessShareLock f 75686189 is the table hst_timerecording. for me it looks like the autovacuum is not releasing the blocking ShareUpdateExclusiveLock? What are the column headings? I find this difficult to read. Please post the whole of pg_locks. I may be missing something but I think we're missing part of the picture here. Autovacuum does not seem to be locking on anything. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 One man's impedance mismatch is another man's layer of abstraction. (Lincoln Yeoh) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] autovacuum process blocks without reporting a deadlock
Thomas Chille wrote: On Nov 27, 2007 3:14 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: hat are the column headings? I find this difficult to read. Please post the whole of pg_locks. I may be missing something but I think we're missing part of the picture here. Autovacuum does not seem to be locking on anything. Unfortunately i logged the pg_locks-state not well formated. i added now the heading manually. maybe it is better or i have to wait till tomorrow morning. but what i think i can see, is that the process with pid 9317 is holding a ShareUpdateExclusiveLock You missed that 9317 is also holding an ExclusiveLock. What are the vacuum_cost_delay and autovacuum_vacuum_cost_delay setting? I think what's happening here is that you're being bitten by the bug that made autovac sleep because of vacuum_delay, with the exclusive lock held trying to truncate the table. The solution is to upgrade to 8.1.10. Also I just noticed you're on 8.1.4. This is a bad idea because of another ancient autovacuum bug. As soon as you upgrade, allow connections to template0 (update pg_database, set datallowconn), connect to it and issue VACUUM FREEZE. Then disallow connections to it again. -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ La experiencia nos dice que el hombre peló millones de veces las patatas, pero era forzoso admitir la posibilidad de que en un caso entre millones, las patatas pelarían al hombre (Ijon Tichy) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Connection idle broken
On 11/27/07, Fernando Xavier [EMAIL PROTECTED] wrote: Hi, I have trouble with my java application. Since i change the network configuration, the postgresql idle connections broken after 10 minutes. (i set authentication_timeout = 600 in postgresql.conf). My network: 192.168.1.1 (postgresql server and gateway server) 192.168.0.1 (linksys wireless router) 192.168.0.x (clients) How i make my idle connections alive for long time? Is the Linksys doing NAT? Can you configure it with a longer timeout on inactive connections? -Doug Any idea? Regards, Fernando Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] autovacuum process blocks without reporting a deadlock
Alvaro Herrera wrote: Thomas Chille wrote: On Nov 27, 2007 3:14 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: hat are the column headings? I find this difficult to read. Please post the whole of pg_locks. I may be missing something but I think we're missing part of the picture here. Autovacuum does not seem to be locking on anything. Unfortunately i logged the pg_locks-state not well formated. i added now the heading manually. maybe it is better or i have to wait till tomorrow morning. but what i think i can see, is that the process with pid 9317 is holding a ShareUpdateExclusiveLock You missed that 9317 is also holding an ExclusiveLock. Sorry, I misread it. My analysis is bogus :-) I suggest you upgrade anyway because there are nasty bugs in the version you are using. I assume 77862554, 77862552 and 77862553 are indexes on the 75686189 table? -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 La experiencia nos dice que el hombre peló millones de veces las patatas, pero era forzoso admitir la posibilidad de que en un caso entre millones, las patatas pelarían al hombre (Ijon Tichy) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Connection idle broken
On Nov 27, 2007 8:33 AM, Fernando Xavier [EMAIL PROTECTED] wrote: Hi, I have trouble with my java application. Since i change the network configuration, the postgresql idle connections broken after 10 minutes. (i set authentication_timeout = 600 in postgresql.conf). My network: 192.168.1.1 (postgresql server and gateway server) 192.168.0.1 (linksys wireless router) 192.168.0.x (clients) How i make my idle connections alive for long time? You can try lowering your tcp_keepalive settings. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] postgres 8.3 beta 2 storage question
Hi, 1.How does postgres version 8.3 betat 2 handle varchar and numeric data types in terms of storage I understand for varchar it has 1byte overhead (instead of 4) if length128 How does it handle for numeric? The manual says The actual storage requirement is two bytes for each group of four decimal digits, plus five to eight bytes overhead. I dont understand how the overhead is measured here?...5 to 8 bytes? 2. Does postgres always inserts a complete record into a page unlike oracle whose record can span multiple pages? Does this waste space too? Thanks sharmila Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
Re: [GENERAL] autovacuum process blocks without reporting a deadlock
yes, u are right. this are the 3 involved indexes: hst_timerecording_business_day_idx on hst_timerecording hst_timerecording_id_employee_idxon hst_timerecording hst_timerecording_id_timerecording_idxon hst_timerecording lg t On Nov 27, 2007 4:07 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: Alvaro Herrera wrote: Thomas Chille wrote: On Nov 27, 2007 3:14 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: hat are the column headings? I find this difficult to read. Please post the whole of pg_locks. I may be missing something but I think we're missing part of the picture here. Autovacuum does not seem to be locking on anything. Unfortunately i logged the pg_locks-state not well formated. i added now the heading manually. maybe it is better or i have to wait till tomorrow morning. but what i think i can see, is that the process with pid 9317 is holding a ShareUpdateExclusiveLock You missed that 9317 is also holding an ExclusiveLock. Sorry, I misread it. My analysis is bogus :-) I suggest you upgrade anyway because there are nasty bugs in the version you are using. I assume 77862554, 77862552 and 77862553 are indexes on the 75686189 table? -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 La experiencia nos dice que el hombre peló millones de veces las patatas, pero era forzoso admitir la posibilidad de que en un caso entre millones, las patatas pelarían al hombre (Ijon Tichy) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ERROR: invalid restriction selectivity: 224359728.000000
error message in log: ERROR: invalid restriction selectivity: 0.49 [ blink... ] Surely it didn't really say that, because a moment's glance at the code shows that it's impossible: if (result 0.0 || result 1.0) elog(ERROR, invalid restriction selectivity: %f, result); If it really did say that, then you have either seriously flaky hardware or a broken compiler. In either case, us mere database weenies can't help much. regards, tom lane Really, it seems that is hardware trouble, beacause it works on virtual machine (KVM), which is buggy enough :( Thanks. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] autovacuum process blocks without reporting a deadlock
On Nov 27, 2007 4:52 PM, Tom Lane [EMAIL PROTECTED] wrote: You didn't happen to note what 9293 was doing did you? It's living fairly dangerously in any case by trying to acquire exclusive lock when it already holds a bunch of other lower-level locks; that's a recipe for deadlock if I ever saw one. regards, tom lane Ah ok, 9293 is a triggerd process and tries to ALTER TABLE ... DISABLE TRIGGER (other trigger) and so implicitly tries to acquire an AccessExclusiveLock and runs in a deadlock? So is better not to use ALTER TABLE ... in triggerfunctions, because there are always existing lower-level locks? regards, thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Error while compiling PostgreSQL with Java
Никоноров Григорий wrote: Thanks for your answer.When i [sic] download this JDBC4 Postgresql Driver, Version 8.2-506 driver what should i do next to configure PostgreSQL with it ? Place the JAR containing the driver in the classpath for your Java application. In the Java program be sure to load the org.postgresql.Driver class and establish the connection to the database via a URL such as jdbc:postgresql://yourhost:5432/yourdb. (JNDI is another approach which does these steps via a declarative approach.) If you had read any of the PG JDBC documentation http://jdbc.postgresql.org/documentation/82/index.html you might have found instructions there. Chapters 2 and 3 of the referenced link (from the very same site from which you got the driver) go into some detail. http://java.sun.com/ has some excellent information about JDBC in general. http://java.sun.com/docs/books/tutorial/jdbc/index.html SunIYF, PGIYF (in this case, jdbc.postgresql.org IYF) and GIYF. -- Lew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] \copy ... with null as '' csv doesn't get nulls
Ivan Sergio Borgonovo wrote:
I'd expect this:
\copy tablename from 'filename.csv' WITH NULL as '' CSV HEADER
whould import as NULL.
The input file is UTF-8 (not Unicode/UTF-16).
I checked the hexdump and the wannabe NULL are actually
2c 22 22 2c - ,,
all fields are varchar that admit NULL
but still I get empty strings ('') in spite of NULL.
Am I missing something or it is a well known feature?
I went to the docs for COPY and they say,
The default is \N (backslash-N) in text mode, and a empty value with no quotes
in CSV mode.
That with no quotes phrase caught my attention.
Try eliminating the double quotes in the CSV file. Wannabe NULL would then be
,, (consecutive commas)
in the CSV. From the docs, you don't even need the NULL AS clause in your
COPY statement.
--
Lew
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Why LIMIT and OFFSET are commutative
Andrus wrote:
Why Postgres does not throw error when SELECT ... LIMIT ... OFFSET is used ?
That's not sql and should cause error.
Good point, it is not standard SQL. Postgres varies from the standard in
several ways, some of them useful. (INSERT ... RETURNING is a useful one.)
No SQL RDBMS follows the standard, AFAIK. LIMIT / OFFSET is a common
enhancement, but being non-standard, appears in different forms in different
dialects.
However, the LIMIT / OFFSET idiom *is* Postgres SQL, it *is* documented and
therefore it should *not* cause an error when used. That would just be whacky.
Given that the idiom is documented and does work in Postgres's particular
dialect of SQL, it then must work exactly as described in Postgres's
documentation of its particular dialect of SQL. And, hey, presto! It does.
I agree that they should document the extra flexibility it provides in the
order of its clauses. I find it hard to assess such flexibility as a bad
thing; I tend to appreciate it.
They do hint at it in one place - Section VI, Chapter I, _SELECT_, the LIMIT
clause,
http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-LIMIT
The LIMIT clause consists of two independent sub-clauses:
LIMIT { /count/ | ALL }
OFFSET /start/
/count/ specifies the maximum number of rows to return,
while /start/ specifies the number of rows to skip
before starting to return rows. When both are specified,
/start/ rows are skipped before starting to count the
/count/ rows to be returned.
The word independent indicates at least the either may appear without the
other, and implies that the order of the clauses doesn't matter.
Note also that the behavior of the clause is *exactly* as documented. You
really have to stop resisting that, now, and accept it.
--
Lew
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Non-unique values problem after 'add column'
Hello! I've encountered a very strange problem with Postgresql 7.3. I have the following set of queries running in transaction, using libpqxx 2.6.9: ALTER TABLE my_table ADD new_column TEXT UPDATE my_table SET new_column = 'disable' ALTER TABLE my_table ALTER COLUMN new_column SET DEFAULT 'disable' CREATE UNIQUE INDEX my_index ON my_table (old_column) The last query fails with: ERROR: Cannot create unique index. Table contains non-unique values Running the same set of queries manually causes no problems. Moreover, running the last query in a separate transaction works as well. Why is a new column affecting unique index creation on an already existing one? Is there any workaround for this issue? Thank you, Dima Kagan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why LIMIT and OFFSET are commutative
Andrus wrote: Under what interpretation would the results differ? Results must differ for easy creation of LinQ-PostgreSQL driver. If results are always the same , PostgreSQL should not allow to use both order of clauses. Um, no, and your original post had nothing to do with LINQ. My answer was only about SQL. Your LINQ problems have to be solved in LINQ. Nicholas explains: Assuming the ordering is the same on each of them (because Skip and Take make no sense without ordering, LINQ to SQL will create an order for you, which irritates me to no end, but that's a separate thread), they will produce different results. Irrelevant. The SQL definitions of these clauses is the SQL definition. LINQ is a separate issue. Again, the *SQL* definition says, quoting the PG docs at http://www.postgresql.org/docs/8.2/interactive/queries-limit.html If both OFFSET and LIMIT appear, then OFFSET rows are skipped *before starting to count the LIMIT rows that are returned.* (emphasis added) That's SQL, my friend. OFFSET first, then LIMIT. Irrespective of the order in the query statement. It is what it is. SQL doesn't depend on LINQ for its semantics. -- Lew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why LIMIT and OFFSET are commutative
Andrus wrote: I found that SELECT * FROM foo ORDER BY bar OFFSET n LIMIT m and SELECT * FROM foo ORDER BY bar LIMIT m OFFSET n produce always same results. Why ? OFFSET and LIMIT operations are NOT commutative in general. Under what interpretation would the results differ? http://www.postgresql.org/docs/8.2/interactive/queries-limit.html If a limit count is given, no more than that many rows will be returned (but possibly less, if the query itself yields less rows). ... OFFSET says to skip that many rows before beginning to return rows. Why would the position of either clause matter, assuming the clause is in a legal position? In both your examples, the LIMIT is 'm', so you will get 'm' rows. In both your examples, the OFFSET is 'n', so you will skip 'n' rows before returning those 'm' rows. I see no inconsistency. Also, neither LIMIT nor OFFSET is a binary operator, so the term commutative has to be understood metaphorically at best. What exactly do you mean by commutative? -- Lew This post contained three requests for answers. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Why LIMIT and OFFSET are commutative
I found that SELECT * FROM foo ORDER BY bar OFFSET n LIMIT m and SELECT * FROM foo ORDER BY bar LIMIT m OFFSET n produce always same results. Why ? OFFSET and LIMIT operations are NOT commutative in general. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Help Needed
Hi, Hope you all are doing gr8! I need a help regarding PostgreSQL installation. I am using PostgreSQL for a web-based application using Tomcat. I want to install PostgreSQL automatically to user system(using some EXE) with a default database(which is required for my application to run). The main problem is how the default database can be created and restore of the data can be done automatically using some script or so. Any suggestion or idea are welcome. Thanks in advance. Regards, Amit
Re: [GENERAL] Why LIMIT and OFFSET are commutative
That's SQL, my friend. OFFSET first, then LIMIT. Irrespective of the order in the query statement. It is what it is. SQL doesn't depend on LINQ for its semantics. SQL requires strong order of all cases. Postgres syntax help about OFFSET / LIMIT also describes only single order. Actually Postgres allows order of OFFSET / LIMIT clauses swapped without any error. Why Postgres does not throw error when SELECT ... LIMIT ... OFFSET is used ? That's not sql and should cause error. Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] System Load analyze
Hi all, i have a system here with 2 2.4GHz Xeon Processors, 2GB RAM, ONE Disk on a Battery Backed Write Cache SCSI Controller and PostgreSQL 8.1.4 running with the data on a DRBD Device for High Availability. The used database is also replicated to two similar machines with slony1. Since the load average is between 1 (most of the time) and 10 (peeks) i am worried about the load and executed vmstat and iostat which show that 1000-6000 Blocks are writen per second. Please check the attached output for further details. top shows that the CPUs are at least 80% idle most of the time so i think there is an I/O bottleneck. I'm aware that this hardware setup is probably not sufficient but is would like to investigate how critical the situation is. thanks, Peter procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 0 398256 78328 140612 106355600 0 1472 2029 5081 4 3 92 0 0 2 398256 78268 140612 106357600 0 2304 1928 4216 0 2 98 0 1 2 398256 78100 140612 106357600 0 1464 1716 3994 1 3 96 0 0 0 398256 78704 140612 106359200 0 916 1435 3570 5 3 91 0 0 0 398256 77876 140612 106361600 0 0 305 1169 3 1 96 0 0 1 398256 79200 140612 106362800 0 2404 2787 6555 7 6 86 0 0 2 398256 79256 140612 106366000 0 1564 1640 3577 4 2 94 0 0 0 398256 79324 140612 106369200 0 1660 2322 5615 5 2 93 0 0 0 398256 78668 140612 106372400 0 1048 1390 4197 16 13 72 0 1 0 398256 79344 140612 106374800 0 1896 2416 5675 5 5 89 0 0 0 398256 79336 140612 106378400 0 1176 1720 4436 5 3 92 0 1 2 398256 79280 140612 106381200 0 1800 1697 3931 3 3 94 0 0 1 398256 79048 140612 106383200 0 1384 1733 4137 4 2 93 0 0 1 398256 78792 140612 106386800 0 1672 2163 5241 3 2 94 0 0 0 398256 77608 140612 106388000 0 1088 1638 3484 4 2 93 0 0 0 398256 79108 140612 106388400 0 1568 2103 5382 7 5 88 0 0 0 398256 79100 140612 106389200 0 1556 1394 3135 2 1 97 0 0 2 398256 79084 140612 106390000 0 1644 2072 4953 2 0 97 0 0 0 398256 79060 140612 106393200 0 1240 1714 3888 5 2 92 0 0 2 398256 79032 140612 106394000 0 1328 1694 4135 4 2 94 0 0 1 398256 78452 140612 106394400 0 620 925 2824 6 7 87 0 0 0 398256 79036 140612 106395600 0 1196 1293 2954 6 7 87 0 0 2 398256 79136 140612 106396400 0 1736 1959 4494 4 2 94 0 0 0 398256 79132 140612 106396400 0 4 260 1039 1 1 98 0 0 0 398256 79052 140612 106398000 0 2444 3084 6955 6 5 89 0 0 2 398256 79060 140612 106398800 0 948 1146 3616 3 1 96 0 0 1 398256 78268 140612 106405600 0 1908 1809 4086 6 5 88 0 0 1 398256 76728 140612 106405600 0 6256 6637 15472 5 5 90 0 0 2 398256 77000 140612 106406400 0 4916 5840 12107 1 4 95 0 0 2 398256 76956 140612 106406800 0 6468 7432 15211 1 3 96 0 0 6 398256 77388 140612 106407200 0 8116 7826 18265 1 8 91 0 0 2 398256 74312 140612 106407600 0 7032 6886 16136 2 7 91 0 0 2 398256 74264 140612 106407600 0 5680 7143 13411 0 5 95 0 0 2 398256 72980 140612 106414000 0 5396 6377 13251 6 6 88 0 0 3 398256 76972 140612 106414800 0 5652 6793 14079 4 9 87 0 0 2 398256 77836 140612 106414800 0 3968 5321 14187 10 8 82 0 1 0 398256 77280 140612 106414800 0 1608 3188 8974 21 12 67 0 1 0 398256 77832 140612 106415200 0 236 834 2625 7 5 87 0 0 0 398256 77464 140612 106415200 0 244 505 1378 2 4 94 0 1 0 398256 77828 140612 106416400 0 316 580 1954 7 2 91 0 0 0 398256 77804 140612 106418000 0 740 673 2248 2 2 96 0 0 0 398256 77000 140612 106418000 0 304 589 1739 1 3 96 0 0 0 398256 77000 140612 106418400 0 0 216 886 0 1 99 0 0 0 398256 75452 140612 106418400 0 432 755 2032 6 1 93 0 0 0 398256 76964 140616 106420000 0 1980 2722 6452 7 2 91 0 0 0 398256 77040 140616 106421200 0 1524 1536 3635 5 1 93 0 0 2 398256 77028 140616 106421600 0 1136 1489 3342 1 2 97 0 0 0 398256 76976 140616 106423600 0 1392 1831 4468 3 2 94 0 0 0 398256 76944 140616 106425600 0 932 1374 3318 7 8 85 0 0 0 398256 76856 140616 106427600 0 1392 1773 3985 5 4 91 0 1 4 398256 76536 140616 106444800 0 1432 1537 3950 4
[GENERAL] indexing tables using my owns functions
Hi every body Recently i wrote my own function into postgesql... and it works perfecly!!, now i would like use it to index a table like this guy do at the link ( http://www.faqs.org/docs/ppbook/r24254.htm) but i get the following error mhc2db= create index h2iab on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb')); ERROR: functions in index expression must be marked IMMUTABLE mhc2db= in this indexation p1,p4,p6,p7,p9 are variables that depends on the field. could anyone help me? -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] Error while compiling PostgreSQL with Java
Никоноров Григорий wrote: I have a problem. I try to install PostgreSQL from source with java. I Installed JDK, ANT properly but when i try to configure i have an error - Warning.Ignored options - --with-java. Please help me ! Kris Jurka wrote: Starting with the 8.0 release the JDBC driver was made an external project instead of bundled with the server source code. The JDBC driver is available from http://jdbc.postgresql.org/download.html Cross-posted to correct the OP's multi-post, so that pgsql.admin will also contain the response(s). -- Lew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why LIMIT and OFFSET are commutative
Under what interpretation would the results differ?
Results must differ for easy creation of LinQ-PostgreSQL driver.
If results are always the same , PostgreSQL should not allow to use both
order of clauses.
Nicholas explains:
Assuming the ordering is the same on each of them (because Skip and Take
make no sense without ordering, LINQ to SQL will create an order for you,
which irritates me to no end, but that's a separate thread), they will
produce different results.
Say your query will produce the ordered set {1, 2, 3}. Let n = 1, m =
2.
The first query:
var query = query.Skip(n).Take(m);
converted to SELECT ... OFFSET n LIMIT m
Will return the ordered set {2, 3}, while the second query:
var query = query.Take(m).Skip(n);
converted to SELECT ... LIMIT m OFFSET n
Will return the ordered set {2}.
The reason for this is that in the first query, the Skip method skips
one element, then takes the remaining two, while in the second query, the
first two elements are taken, and then the first one is skipped.
http://www.postgresql.org/docs/8.2/interactive/queries-limit.html
If a limit count is given, no more than that many rows will be returned
(but possibly less, if the query itself yields less rows).
...
OFFSET says to skip that many rows before beginning to return rows.
Why would the position of either clause matter, assuming the clause is in
a legal position?
In both your examples, the LIMIT is 'm', so you will get 'm' rows.
In both your examples, the OFFSET is 'n', so you will skip 'n' rows before
returning those 'm' rows.
I see no inconsistency.
Different results - the first gives results (zero-based) n to n+m-1.
The second gives results 0 to Min(n-1, m-1).
Also, neither LIMIT nor OFFSET is a binary operator, so the term
commutative has to be understood metaphorically at best. What exactly
do you mean by commutative?
I meant result should depend on the order of OFFSET and LIMIT clauses are
present in SELECT clause.
SELECT ... OFFSET n LIMIT m
SELECT ... LIMIT m OFFSET n
should return different results in sime cases.
Filtering and ordering are effectively orthogonal. LIMIT and OFFSET are
clearly *not* orthogonal.
Otherwise I see no way to implement efficient LinQ-PostgreSQL driver.
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Error while compiling PostgreSQL with Java
Peter Eisentraut wrote: Никоноров Григорий wrote: I have a problem. I try to install PostgreSQL from source with java. I Installed JDK, ANT properly but when i try to configure i have an error - Warning.Ignored options - --with-java. Please help me ! PostgreSQL has no such option. You may be looking for the JDBC driver. If so, see http://jdbc.postgresql.org/. Yes, this answer has been provided to the OP already. -- Lew ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Help Needed
Hi, Hope you all are doing gr8! I need a help regarding PostgreSQL installation. I am using PostgreSQL for a web-based application using Tomcat. I want to install PostgreSQL automatically to user system(using some EXE) with a default database(which is required for my application to run). The main problem is how the default database can be created and restore of the data can be done automatically using some script or so. Any suggestion or idea are welcome. Thanks in advance. Regards, Amit
[GENERAL] Stored Procedures vs Dynamic SQL generated by ORM ?
Hi All, Which is the best Stored Procedures or Dynamic SQL generated by ORM ? Thanks Madhu Alagu ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] replication in Postgres
Sorry, this makes no sense to me -- EnterpriseDB has no replication solution that I know of. slony is bundled with the database Postgres-r sounds very nice but moving our organisations data onto a system that it work in progress is very scary. You are already offloading your data to PostgreSQL which is a work in progress too ... ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] select from an index
Hi
Recently i created an index in a table using a function (not a column) as
following
create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));,
where idr is a function that returns a real number,
as a result i got the following table
mhc2db= \d precalc;
Table public.precalc
Column | Type| Modifiers
-+---+--
id | integer | not null default
nextval('precalc_id_seq'::regclass)
p1 | character(1) |
p4 | character(1) |
p6 | character(1) |
p7 | character(1) |
p9 | character(1) |
Indexes:
h2iab btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)),
now, i would like to perform a query using this index, something like
Select * from precalc where h2iab2
but obviously h2iab is not a column...
some of you knows what i should do?
thanks
--
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de Biotecnologia i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)
telèfon: 93 5812807
Email : [EMAIL PROTECTED]
Re: [GENERAL] Linux v.s. Mac OS-X Performance
Hello, sorry for butting in, but I'm just curious... resolution? http://archives.postgresql.org/pgsql-general/2007-11/msg00946.php conclusion? Mac was still pretty slow in comparison Anyway, how does MacOS X (both 10.4 and 10.5) compare to Windows (2000, XP, Vista etc.) on the same hardware? And Linux to (Free-/Net-/whatever) BSD? No flamebait, I'm just wondering whether the performance gain is worth the learning effort required for Linux or BSD compared to the Mac. Sincerely, Wolfgang Keller ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Unused item pointers - can someone explain?
On 23 Lis, 10:23, [EMAIL PROTECTED] (Peter Childs) wrote: Yes, however found x removable have just been found and are now unused, so on the next run this number will be added to the unused unless they get used again in the mean time. The number ie the unused is the number of tuples left in the free space map unused since the last vacuum. If its high it may be worth clustering or running vacuum full but only if you don't think you table will never or unlikely to grow (insert) or change (update) by less than that number of records before you next run vacuum. Generally only worry if the number is very very high (over 1). The best way of understanding the numbers is to run vacuum at regular intervals and compare the output. Peter. Thank you for your explanation - it's not easy to get help in this subject... But how it is possible that new unused. You wrote: The number ie the unused is the number of tuples left in the free space map unused since the last vacuum. This is important information for me but I still can't understand why this number keeps growing.Correct me if I wrong but if there is 17000 unused tuples in free space map, they should be used in first place for creating new tuples versions. This should cause that next 17000 operations (consists of INSERT,UPDATE,DELETE) would use fsm for row version creation instead of creating entirely new tuples at the end of table file. If I understand it correct number of unused item pointers should shrink between vacuums (but it still grows)... Regards Elmer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] select from an index
On Nov 27, 2007, at 3:15 AM, Pau Marc Munoz Torres wrote: Hi Recently i created an index in a table using a function (not a column) as following create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));, now, i would like to perform a query using this index, something like Select * from precalc where h2iab2 but obviously h2iab is not a column... some of you knows what i should do? select * from precalc where idr(p1,p4,p6,p7,p9,'H-2*IAb') 2 Cheers, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why LIMIT and OFFSET are commutative
Andrus [EMAIL PROTECTED] writes:
Under what interpretation would the results differ?
Results must differ for easy creation of LinQ-PostgreSQL driver.
If results are always the same , PostgreSQL should not allow to use both
order of clauses.
Nicholas explains:
Assuming the ordering is the same on each of them (because Skip and Take
make no sense without ordering, LINQ to SQL will create an order for you,
which irritates me to no end, but that's a separate thread), they will
produce different results.
Say your query will produce the ordered set {1, 2, 3}. Let n = 1, m =
2.
The first query:
var query = query.Skip(n).Take(m);
converted to SELECT ... OFFSET n LIMIT m
Will return the ordered set {2, 3}, while the second query:
var query = query.Take(m).Skip(n);
converted to SELECT ... LIMIT m OFFSET n
You should use subqueries if you want to do that. Take() and Skip() can take a
query and rewrite it as:
SELECT * FROM (old query) LIMIT n
or
SELECT * FROM (old query) OFFSET n
So you'll end up with a query like
SELECT * FROM (SELECT * FROM (query) LIMIT n) OFFSET n
or vice versa.
Or alternatively do the arithmetic. If there's already an offset in the query
structure when Skip() is called then add that amount to the offset. I'm
assuming your methods are called on some kind of object which can store
arbitrary state and not simply on a query string.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] autovacuum process blocks without reporting a deadlock
Thomas Chille [EMAIL PROTECTED] writes: Ah ok, 9293 is a triggerd process and tries to ALTER TABLE ... DISABLE TRIGGER (other trigger) and so implicitly tries to acquire an AccessExclusiveLock and runs in a deadlock? Well, you're certainly risking deadlock with that; and even if no actual deadlock happens, waiting for the exclusive lock needed to do the ALTER can cause the kind of lock queueing you had here. I'd suggest trying to think of a way to solve your problem that doesn't need trigger enabling/disabling. regards, tom lane ---(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: [GENERAL] System Load analyze
On Nov 24, 2007 10:57 AM, Peter Bauer [EMAIL PROTECTED] wrote: i have a system here with 2 2.4GHz Xeon Processors, 2GB RAM, ONE Disk on a Battery Backed Write Cache SCSI Controller and PostgreSQL 8.1.4 running with the data on a DRBD Device for High Availability. The used database is also replicated to two similar machines with slony1. Why are you running a version of PostgreSQL with known data eating bugs? If you care for your data, you will keep up to date on releases. 8.1.10 was released on 2007-09-17. 8.1.4 was released on 2006-05-23. That's 16 months of bug fixes you're missing. Go here: http://www.postgresql.org/docs/8.1/static/release.html and read up on the fixes you're missing. Then update. Or just update. OK, on the the issue at hand. Since the load average is between 1 (most of the time) and 10 (peeks) i am worried about the load and executed vmstat and iostat which show that 1000-6000 Blocks are writen per second. Please check the attached output for further details. top shows that the CPUs are at least 80% idle most of the time so i think there is an I/O bottleneck. I'm aware that this hardware setup is probably not sufficient but is would like to investigate how critical the situation is. Yes. Battery backed cache can only do so much, it's not magic pixie dust. Once it's full, the drive becomes the bottle neck. Real db servers have more than one disk drive. They usually have at least 4 or so, and often dozens to hundreds. Also, not all battery backed caching RAID controllers are created equal. procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 0 398256 78328 140612 106355600 0 1472 2029 5081 4 3 92 0 0 2 398256 78268 140612 106357600 0 2304 1928 4216 0 2 98 0 1 2 398256 78100 140612 106357600 0 1464 1716 3994 1 3 96 0 0 0 398256 78704 140612 106359200 0 916 1435 3570 5 3 91 0 0 0 398256 77876 140612 106361600 0 0 305 1169 3 1 96 0 See that dip in the last line above where the blocks in drop to 0, idle jumps to 96, and blocks out drops, and context switches drop? That's most likely where postgresql is checkpointing. Checkpointing is where it writes out all the dirty buffers to disk. If the bgwriter is not tuned aggresively enough, checkpoints happen and make the whole database slow down for a few seconds. If it's tuned too aggresively then the db spends too much CPU time tracking the dirty buffers and then writing them. If tuned just right, it will write out the dirty buffers just fast enough that a checkpoint is never needed. You tune the bgwriter to your machine and I/O subsystem. If you're planning on getting more hard drives, do that first. Then tune the bgwriter. btw, if this is vmstat 1 running, it's showing a checkpoint every 20 or so seconds I think 0 2 398256 79136 140612 106396400 0 1736 1959 4494 4 2 94 0 checkpoint here: 0 0 398256 79132 140612 106396400 0 4 260 1039 1 1 98 0 0 0 398256 79052 140612 106398000 0 2444 3084 6955 6 5 89 0 0 2 398256 79060 140612 106398800 0 948 1146 3616 3 1 96 0 0 1 398256 78268 140612 106405600 0 1908 1809 4086 6 5 88 0 0 1 398256 76728 140612 106405600 0 6256 6637 15472 5 5 90 0 0 2 398256 77000 140612 106406400 0 4916 5840 12107 1 4 95 0 0 2 398256 76956 140612 106406800 0 6468 7432 15211 1 3 96 0 0 6 398256 77388 140612 106407200 0 8116 7826 18265 1 8 91 0 0 2 398256 74312 140612 106407600 0 7032 6886 16136 2 7 91 0 0 2 398256 74264 140612 106407600 0 5680 7143 13411 0 5 95 0 0 2 398256 72980 140612 106414000 0 5396 6377 13251 6 6 88 0 0 3 398256 76972 140612 106414800 0 5652 6793 14079 4 9 87 0 0 2 398256 77836 140612 106414800 0 3968 5321 14187 10 8 82 0 1 0 398256 77280 140612 106414800 0 1608 3188 8974 21 12 67 0 1 0 398256 77832 140612 106415200 0 236 834 2625 7 5 87 0 0 0 398256 77464 140612 106415200 0 244 505 1378 2 4 94 0 1 0 398256 77828 140612 106416400 0 316 580 1954 7 2 91 0 0 0 398256 77804 140612 106418000 0 740 673 2248 2 2 96 0 0 0 398256 77000 140612 106418000 0 304 589 1739 1 3 96 0 20 rows later, checkpoint here: 0 0 398256 77000 140612 106418400 0 0 216 886 0 1 99 0 0 0 398256 75452 140612 106418400 0 432 755 2032 6 1 93 0 max_fsm_pages = 4 # min max_fsm_relations*16, 6 bytes each, APUS This seems a little low for a busy server. # - Background writer -
Re: [GENERAL] Why LIMIT and OFFSET are commutative
On Nov 26, 2007, at 5:29 AM, Andrus wrote:
Under what interpretation would the results differ?
Results must differ for easy creation of LinQ-PostgreSQL driver.
If results are always the same , PostgreSQL should not allow to use
both
order of clauses.
Nicholas explains:
Assuming the ordering is the same on each of them (because Skip
and Take
make no sense without ordering, LINQ to SQL will create an order
for you,
which irritates me to no end, but that's a separate thread), they will
produce different results.
Say your query will produce the ordered set {1, 2, 3}. Let n =
1, m =
2.
The first query:
var query = query.Skip(n).Take(m);
converted to SELECT ... OFFSET n LIMIT m
Will return the ordered set {2, 3}, while the second query:
var query = query.Take(m).Skip(n);
converted to SELECT ... LIMIT m OFFSET n
Will return the ordered set {2}.
The reason for this is that in the first query, the Skip method
skips
one element, then takes the remaining two, while in the second
query, the
first two elements are taken, and then the first one is skipped.
Nice. Yet another example of an Object-Relational impedance
mismatch. SQL is declarative, not procedural.
Erik Jones
Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate market in style.
Visit us online at http://www.myemma.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Non-unique values problem after 'add column'
On Nov 26, 2007 3:06 AM, [EMAIL PROTECTED] wrote: Hello! I've encountered a very strange problem with Postgresql 7.3. I have the following set of queries running in transaction, using libpqxx 2.6.9: 7.3 is getting pretty old. I can't say anything though, I've still got a 7.4 db hanging around somewhere too. :) ALTER TABLE my_table ADD new_column TEXT UPDATE my_table SET new_column = 'disable' ALTER TABLE my_table ALTER COLUMN new_column SET DEFAULT 'disable' CREATE UNIQUE INDEX my_index ON my_table (old_column) The last query fails with: ERROR: Cannot create unique index. Table contains non-unique values Running the same set of queries manually causes no problems. Moreover, running the last query in a separate transaction works as well. I'm guessing the difference here is NOT libpqxx, but the data in the db. Are you sure you're running this against the exact same data set? If so, you need to make a self-contained test that shows this happening only to libpqxx. I.e. table def, data, sql executed, etc. Often times, while making such a self-contained proof, you'll figure out what's really going on, and it usually isn't a bug in libpqxx at that point. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Stored Procedures vs Dynamic SQL generated by ORM ?
On Nov 27, 2007 1:57 AM, Alagu Madhu [EMAIL PROTECTED] wrote: Which is the best Stored Procedures or Dynamic SQL generated by ORM ? Depends on what you mean by best. Dynamic SQL costs more in the planning stage because the db has to plan out each statement before running it. However, it can consider the actual values in the queries when creating those plans, so you get individually optimized plans for each query. Generally speaking Stored Procs (UDFs in pgsql) use static query / execution plans created at the time the UDF was brought into being. You should also considered prepared SQL from the client side as well. Not sure if your ORM supports that or not. Not a fan of ORM, but it's a necessary evil sometimes. :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Stored Procedures vs Dynamic SQL generated by ORM ?
On Mon, Nov 26, 2007 at 11:57:37PM -0800, Alagu Madhu wrote: Which is the best Stored Procedures or Dynamic SQL generated by ORM ? This is roughly similar to the question, Is it better to travel by car, boat, or plane? It rather depends on what you're trying to accomplish. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(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: [GENERAL] postgres 8.3 beta 2 storage question
1.How does postgres version 8.3 betat 2 handle varchar and numeric data types in terms of storage I understand for varchar it has 1byte overhead (instead of 4) if length128 How does it handle for numeric? The manual says The actual storage requirement is two bytes for each group of four decimal digits, plus five to eight bytes overhead. which manual? ( not these two: ) http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-NUMERIC-DECIMAL http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL http://www.postgresql.org/docs/8.3/static/datatype-numeric.html 2. Does postgres always inserts a complete record into a page unlike oracle whose record can span multiple pages? Does this waste space too? records larger than one page are TOASTed http://www.postgresql.org/docs/current/static/storage-page-layout.html Thanks sharmila Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
Re: [GENERAL] \copy ... with null as '' csv doesn't get nulls
On Sun, 25 Nov 2007 13:22:48 -0500 Lew [EMAIL PROTECTED] wrote: I went to the docs for COPY and they say, The default is \N (backslash-N) in text mode, and a empty value with no quotes in CSV mode. That with no quotes phrase caught my attention. Try eliminating the double quotes in the CSV file. Wannabe NULL would then be ,, (consecutive commas) in the CSV. From the docs, you don't even need the NULL AS clause in your COPY statement. Exactly what I did because fortunately there weren't too many chances of weird stuff in 2000 records (sed -e 's/,/,/g'). Anyway with NULL AS '' and without it I can't still import NULL without pre-processing. I thought it may be missing total support of UTF-8 or if I did something wrong or it is actually a feature. thx -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Error while compiling PostgreSQL with Java
Thanks al for the answers !!! I solve my problem uninstalling source PostgreSQL and installed from binarys. I use JDBC driver from http://jdbc.postgresql.org/. Now all work fine. :) p.s. Unfortunately i dont solve problem how to compile postgreSQL with java but found very easy method to install PostgreSQL with full java support from binarys. Grigory. On Mon, 26 Nov 2007 00:36:01 +0300, Lew [EMAIL PROTECTED] wrote: Peter Eisentraut wrote: Никоноров Григорий wrote: I have a problem. I try to install PostgreSQL from source with java. I Installed JDK, ANT properly but when i try to configure i have an error - Warning.Ignored options - --with-java. Please help me ! PostgreSQL has no such option. You may be looking for the JDBC driver. If so, see http://jdbc.postgresql.org/. Yes, this answer has been provided to the OP already. -- Отправлено M2, революционной почтовой программой Opera: http://www.opera.com/mail/ ---(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: [GENERAL] Non-unique values problem after 'add column'
[EMAIL PROTECTED] writes: I've encountered a very strange problem with Postgresql 7.3. 7.3.what? FWIW, I cannot replicate your problem using 7.3.20. It doesn't sound like any bug I can remember, but certainly there have been a lot of bugs fixed over the past five years. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] indexing tables using my owns functions
On Mon, Nov 26, 2007 at 04:54:10PM +0100, Pau Marc Munoz Torres wrote: Hi every body Recently i wrote my own function into postgesql... and it works perfecly!!, now i would like use it to index a table like this guy do at the link ( http://www.faqs.org/docs/ppbook/r24254.htm) but i get the following error mhc2db= create index h2iab on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb')); ERROR: functions in index expression must be marked IMMUTABLE Where you declare the function, add the modifier IMMUTABLE. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Connection idle broken
On 11/27/07, Fernando Xavier [EMAIL PROTECTED] wrote: Hi, thanks for reply! No, my router don't have configurations for timeout connections.. Get a better router then. Something between your clients and the database server is timing out those connections, and it's most likely that box--NAT connections are timed out fairly aggressively by default on consumer routers (you didn't say whether you were using NAT or not, but it may be turned on by default). Relying on anything labeled Linksys for production work is a terrible idea. Also, please keep your replies on the mailing list so others can benefit from the discussion. -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Connection idle broken
On Nov 27, 2007 12:33 PM, Douglas McNaught [EMAIL PROTECTED] wrote: Get a better router then. Something between your clients and the database server is timing out those connections, and it's most likely that box--NAT connections are timed out fairly aggressively by default on consumer routers (you didn't say whether you were using NAT or not, but it may be turned on by default). Relying on anything labeled Linksys for production work is a terrible idea. Actually, if you use the linux based ones, or download dd-wrt or openwrt and install that, the linksys routers are quite stable, reliable, and performant. Myself, I prefer the pre-installed buffalo router that DD-WRT ships, but the software they make is quite amazing. ---(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
Res: [GENERAL] Connection idle broken
Right.. i'm sorry :-) i solved this problem according Scott Marlowe suggestion. I changed the postgresql.conf tcp_keepalives_idle = 300 tcp_keepalives_interval = 60 tcp_keepalives_count = 10 The connection don't broke more...Actually, the router linksys isn't good for network complex.. Thanks for all replies! :-) regards, Fernando - Mensagem original De: Douglas McNaught [EMAIL PROTECTED] Para: Fernando Xavier [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Enviadas: Terça-feira, 27 de Novembro de 2007 16:33:35 Assunto: Re: [GENERAL] Connection idle broken On 11/27/07, Fernando Xavier [EMAIL PROTECTED] wrote: Hi, thanks for reply! No, my router don't have configurations for timeout connections.. Get a better router then. Something between your clients and the database server is timing out those connections, and it's most likely that box--NAT connections are timed out fairly aggressively by default on consumer routers (you didn't say whether you were using NAT or not, but it may be turned on by default). Relying on anything labeled Linksys for production work is a terrible idea. Also, please keep your replies on the mailing list so others can benefit from the discussion. -Doug Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Connection idle broken
On 11/27/07, Scott Marlowe [EMAIL PROTECTED] wrote: On Nov 27, 2007 12:33 PM, Douglas McNaught [EMAIL PROTECTED] wrote: Get a better router then. Something between your clients and the database server is timing out those connections, and it's most likely that box--NAT connections are timed out fairly aggressively by default on consumer routers (you didn't say whether you were using NAT or not, but it may be turned on by default). Relying on anything labeled Linksys for production work is a terrible idea. Actually, if you use the linux based ones, or download dd-wrt or openwrt and install that, the linksys routers are quite stable, reliable, and performant. I'm sure they are. Custom software helps a lot. :) That said, my definition of production doesn't include hardware that's built as cheaply as possible for the consumer market. Others may differ, and the OPs need may very well be satisfied by a custom software load on a cheap consumer router. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] autovacuum process blocks without reporting a deadlock
Thomas Chille [EMAIL PROTECTED] writes: I think this are the relevant pg_locks entries: relation7568577875686189 9017862 25467 AccessShareLock f relation7568577875686189 9009323 9317ShareUpdateExclusiveLock t relation7568577875686189 9009312 9293AccessShareLock t relation7568577875686189 9009312 9293RowExclusiveLockt relation7568577875686189 9009312 9293AccessExclusiveLock f relation7568577875686189 9012978 28370 AccessShareLock f I don't think the vacuum is the problem here. The problem is process 9293, which for some reason is trying to get AccessExclusiveLock, and is blocked behind autovac, and everything else is stacking up behind it. You didn't happen to note what 9293 was doing did you? It's living fairly dangerously in any case by trying to acquire exclusive lock when it already holds a bunch of other lower-level locks; that's a recipe for deadlock if I ever saw one. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Tue, 2007-11-27 at 11:11 +0100, Wolfgang Keller wrote: Hello, sorry for butting in, but I'm just curious... resolution? http://archives.postgresql.org/pgsql-general/2007-11/msg00946.php conclusion? Mac was still pretty slow in comparison Anyway, how does MacOS X (both 10.4 and 10.5) compare to Windows (2000, XP, Vista etc.) on the same hardware? In general, you can expect any Unix based OS, which includes MacOS X, to perform noticeably better than Windows for PostgreSQL. //Magnus ---(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: [GENERAL] Connection idle broken
On Nov 27, 2007 12:50 PM, Douglas McNaught [EMAIL PROTECTED] wrote: On 11/27/07, Scott Marlowe [EMAIL PROTECTED] wrote: Actually, if you use the linux based ones, or download dd-wrt or openwrt and install that, the linksys routers are quite stable, reliable, and performant. I'm sure they are. Custom software helps a lot. :) That said, my definition of production doesn't include hardware that's built as cheaply as possible for the consumer market. Others may differ, and the OPs need may very well be satisfied by a custom software load on a cheap consumer router. Well, if I had a choice between a single internet connection on a big cisco, and a dual connect through a sonet ring through a pair of buffalo consumer grade routers with dd-wrt, I'd take the sonet ring. Course, we'd all rather have both the sonet ring and the big commercial routers... (am seriously amazed how many 24/7 ops are out there running with a sonet ring into the building that turns into a single point of failure after that. ) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Unknown winsock error 10061while dumping a big database
I recently upgraded a 8.0 database to 8.2 (server is running windows 2003 server) and now I cannot dump the database any more. At some point in the dump, pg_dump (run on a centos 3 linux) return : pg_dump: Dumping the contents of table c2674 failed: PQgetCopyData() failed. pg_dump: Error message from server: out of memory pg_dump: The command was: COPY public.c2674 (p2, p9, p3337, p18155, p18154, p17561, p4065, p689, p43711, p4083, p4020, p4029, p4218, p4074, p45127, p3857, p7, p6, p5, p32402, p5512, p18175, p42542, p17562, p4) TO stdout; In the server logs I only get : LOG: could not send data to client: Unknown winsock error 10061 I tried two time and the dump nearly stop at the same point. I'm trying now to dump only this tables (c2674), it's a 60 GB table in a ~100 GB database initialized in C locale. This table have several fields which can be pretty large (the biggest row is ~35MB). Any ideas ? Cyril VELTER ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Config settings for large restore
Hi, I'm just wondering what is considered the general wisdom on config setting for large pg_restore runs. I know to increase maintenance_work_mem and turn off autovacuum and stats collection. Shoule should checkpoint_segments and checkpoint_timeout be increased? Would twiddling shared_buffers help? What about the bgwriter (I'm thinking this should just follow the checkpoint and shared_buffers settings)? I realize that the actual setting will be hardware dependent, I'm just looking for general rules of thumb for what to tweak wrt increased restore speed/performance. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] spatial composition operators for PG
Are there any standard spatial composition operators people use for PG's polygon and path data types? That is, it seems there ought to be extenders of the signatures, polygon - polygon %+% (point, vertex-number) path - path %+% (point, node-number) where the right-hand-sided point is the new point, and the vertex-number or node-number tell where to put it. This function might be an update-in-place. I saw nothing like this in the PG docs, but I figured people probably made such things up. Any pointers? Anyone willing to share their favorites online some place? TIA, - Jan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] spatial composition operators for PG
Jan- check out http://postgis.refractions.net/ M-- - Original Message - From: Galkowski, Jan [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Tuesday, November 27, 2007 6:02 PM Subject: [GENERAL] spatial composition operators for PG Are there any standard spatial composition operators people use for PG's polygon and path data types? That is, it seems there ought to be extenders of the signatures, polygon - polygon %+% (point, vertex-number) path - path %+% (point, node-number) where the right-hand-sided point is the new point, and the vertex-number or node-number tell where to put it. This function might be an update-in-place. I saw nothing like this in the PG docs, but I figured people probably made such things up. Any pointers? Anyone willing to share their favorites online some place? TIA, - Jan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux v.s. Mac OS-X Performance
In general, you can expect any Unix based OS, which includes MacOS X, to perform noticeably better than Windows for PostgreSQL. Is that really true of BSD UNIXen??? I've certainly heard it's true of Linux. But with BSD you have the kernel funnel which can severely limit multitasking, regardless of whether threads or processes were used. Apple has been working toward finer-grained locking precisely because that was a serious bottleneck which limited OS X server performance. Or have I misunderstood and this was only the design of one particular flavor of BSD, not BSDen in general? -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux v.s. Mac OS-X Performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 27 Nov 2007 17:01:06 -0700 Scott Ribe [EMAIL PROTECTED] wrote: In general, you can expect any Unix based OS, which includes MacOS X, to perform noticeably better than Windows for PostgreSQL. Is that really true of BSD UNIXen??? I've certainly heard it's true of Linux. But with BSD you have the kernel funnel which can severely limit multitasking, regardless of whether threads or processes were used. Apple has been working toward finer-grained locking precisely because that was a serious bottleneck which limited OS X server performance. Or have I misunderstood and this was only the design of one particular flavor of BSD, not BSDen in general? Not much of a kernel guy here but my understanding is that MacOSX is basically NeXT version 10, which means... Mach... which is entirely different than say FreeBSD at the kernel level. Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHTLBoATb/zqfZUUQRAs6OAJ4yIYWauPpZybyVZJlF/RScFoZrawCeOYv7 osMbcJEVqqJfLGOo6uRJBMY= =hgrE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Another question about partitioning
Hello all, I have a table which is partitioned by range into 10 pieces with constraint exceptions. Constraint exceptions is enabled in server configuration too. For some reason, queries to the master table are still slower than direct queries against partitions. Is there any real reason for that, or I should look into misconfiguration ? Thanks! Best regards, Alex Vinogradovs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux v.s. Mac OS-X Performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/27/07 18:01, Scott Ribe wrote: In general, you can expect any Unix based OS, which includes MacOS X, to perform noticeably better than Windows for PostgreSQL. Is that really true of BSD UNIXen??? I've certainly heard it's true of Linux. But with BSD you have the kernel funnel which can severely limit multitasking, regardless of whether threads or processes were used. Apple has been working toward finer-grained locking precisely because that was a serious bottleneck which limited OS X server performance. Or have I misunderstood and this was only the design of one particular flavor of BSD, not BSDen in general? IIRC, FreeBSD got rid of the Giant Lock back in v5.x. There was a benchmark in Feb 2007 which demonstrated that FBSD 7.0 scaled *better* than Linux 2.6 after 4 CPUs. http://jeffr-tech.livejournal.com/5705.html Turns out that there was/is a bug in glibc's malloc(). Don't know if it's been fixed yet. - -- Ron Johnson, Jr. Jefferson LA USA %SYSTEM-F-FISH, my hovercraft is full of eels -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHTMAfS9HxQb37XmcRAg4NAJsFXVFa5NQtctsdrjbNCZ8GRAHMlwCeOfZr kBFOQUI6zGcTDiy793+JSIc= =/W4e -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Another question about partitioning
Alex Vinogradovs [EMAIL PROTECTED] writes: Hello all, I have a table which is partitioned by range into 10 pieces with constraint exceptions. Constraint exceptions is enabled in server configuration too. For some reason, queries to the master table are still slower than direct queries against partitions. Is there any real reason for that, or I should look into misconfiguration ? Well you have to look at the actual plans. Having to combine multiple partitions does have some cost to it and does interfere somewhat in the planner's ability to optimize plans so it might not be a win on individual queries if they were not doing big scans of unnecessary data previously. You might also consider using partial indexes instead of partitioning if your goal is just optimizing queries. The big advantage of partitioning is being able to add and drop entire partitions effectively instantaneously. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(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: [GENERAL] Linux v.s. Mac OS-X Performance
On Tue, 27 Nov 2007, Wolfgang Keller wrote: Anyway, how does MacOS X (both 10.4 and 10.5) compare to Windows (2000, XP, Vista etc.) on the same hardware? And Linux to (Free-/Net-/whatever) BSD? Apple hardware gets so expensive for some types of database configurations that such a comparision doesn't even make a lot of sense. For example, if you have an application that needs high database write throughput, to make that work well with PostgreSQL you must have a controller with a battery backed cache. If I have a PC, the entry-level solution in that category can be a random sub-$1000 system that runs Linux plus around $400 for a RAID card with BBC, and you've got multiple vendors to consider there (3Ware, Areca, LSI Logic, etc.) To do something similar with Apple hardware, you can get a Mac Pro and add their RAID card, at $3500 (early reports suggest even that may have serious problems, see http://forums.macrumors.com/showthread.php?t=384459 ). Or you can pick up an XServe RAID, but now you're talking $6350 because the smallest configuration is 1TB. The amount of server you can buy for $3500+ running Linux is going to be much more powerful than its Apple equivilant. Sure, you can run a trivial workload that features minimal writes even on a Mac Mini, but I don't see a lot of value to considering a platform where the jump to the cheapest serious server configuration is so big. Also, in previous generations, the Mach kernel core of Mac OS had some serious performance issues for database use even in read-heavy workloads: http://www.anandtech.com/mac/showdoc.aspx?i=2520p=5 There are claims this is improved in current systems (Leopard + Intel), but the margin was so big before I would need some pretty hard proof to believe they've even achieved parity with Linux/FreeBSD on the same hardware, and even then the performance/dollar is unlikely to be competative. I'm just wondering whether the performance gain is worth the learning effort required for Linux or BSD compared to the Mac. On both Windows (where you get limitations like not being able to set a large value for shared_buffers) and Mac OS X, PostgreSQL has enough performance issues that I feel using those plaforms can only be justified if platform compatibility is more important than performance to you. The minute performance becomes a serious concern, you'd be much better off with Linux, one of the BSDs that's not hobbled by using the Mach kernel, or one of the more serious UNIXes like Solaris. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Linux v.s. Mac OS-X Performance
Joshua D. Drake [EMAIL PROTECTED] writes: On Tue, 27 Nov 2007 17:01:06 -0700 Scott Ribe [EMAIL PROTECTED] wrote: In general, you can expect any Unix based OS, which includes MacOS X, to perform noticeably better than Windows for PostgreSQL. Is that really true of BSD UNIXen??? I've certainly heard it's true of Linux. But with BSD you have the kernel funnel which can severely limit multitasking, regardless of whether threads or processes were used. Apple has been working toward finer-grained locking precisely because that was a serious bottleneck which limited OS X server performance. Or have I misunderstood and this was only the design of one particular flavor of BSD, not BSDen in general? That was true of the traditional BSD 4.3 and 4.4 design. However when people refer to BSD these days they're referring to one of the major derivatives which have all undergone extensive further development. FreeBSD has crowed a lot about their finer-grained kernel locks too for example. Other variants of BSD tend to focus on other areas (like portability for example) so they may not be as far ahead but they've still undoubtedly made significant progress compared to 1993. Not much of a kernel guy here but my understanding is that MacOSX is basically NeXT version 10, which means... Mach... which is entirely different than say FreeBSD at the kernel level. I think (but I'm not sure) that the kernel in OSX comes from BSD. What they took from NeXT was the GUI design and object oriented application framework stuff. Basically all the stuff that Unix programmers still haven't quite figured out what it's good for. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Another question about partitioning
Alex Vinogradovs wrote: Hello all, I have a table which is partitioned by range into 10 pieces with constraint exceptions. Constraint exceptions is enabled in server configuration too. For some reason, queries to the master table are still slower than direct queries against partitions. Is there any real reason for that, or I should look into misconfiguration ? Thanks! Best regards, Alex Vinogradovs Is that true even if you type the query yourself in psql and ensure that the values for the partitioned columns are constants in the where clause? Can you post an explain of the sql? Paul ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Nov 27, 2007, at 8:36 PM, Gregory Stark wrote: I think (but I'm not sure) that the kernel in OSX comes from BSD. Kind of. Mach is still running underneath (and a lot of the app APIs use it directly) but there is a BSD 'personality' above it which (AIUI) is big parts of FreeBSD ported to run on Mach. So when you use the Unix APIs you're going through that. -Doug ---(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: [GENERAL] Linux v.s. Mac OS-X Performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/27/07 19:36, Gregory Stark wrote: Joshua D. Drake [EMAIL PROTECTED] writes: [snip] That was true of the traditional BSD 4.3 and 4.4 design. However when people refer to BSD these days they're referring to one of the major derivatives which have all undergone extensive further development. FreeBSD has crowed a lot about their finer-grained kernel locks too for example. Other variants of BSD tend to focus on other areas (like portability for example) so they may not be as far ahead but they've still undoubtedly made significant progress compared to 1993. NetBSD and OpenBSD are still pretty not-good at scaling up. But they're darned good at running on 68K Macs (NBSD) and semi-embedded stuff like low-end firewalling routers (OBSD). Not much of a kernel guy here but my understanding is that MacOSX is basically NeXT version 10, which means... Mach... which is entirely different than say FreeBSD at the kernel level. I think (but I'm not sure) that the kernel in OSX comes from BSD. What they took from NeXT was the GUI design and object oriented application framework stuff. Basically all the stuff that Unix programmers still haven't quite figured out what it's good for. Even AfterStep is written is plain C... - -- Ron Johnson, Jr. Jefferson LA USA %SYSTEM-F-FISH, my hovercraft is full of eels -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHTMqjS9HxQb37XmcRAmS+AKCyzxZ9b1jmcye8gEwlun7VrszhfgCfVC6B LEaSaGlorSQ5lX5eIIgx7dM= =NvJi -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Linux v.s. Mac OS-X Performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/27/07 19:35, Greg Smith wrote: [snip] to you. The minute performance becomes a serious concern, you'd be much better off with Linux, one of the BSDs that's not hobbled by using the Mach kernel, or one of the more serious UNIXes like Solaris. Wasn't there a time (2 years ago?) when PG ran pretty dog-like on SPARC? - -- Ron Johnson, Jr. Jefferson LA USA %SYSTEM-F-FISH, my hovercraft is full of eels -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHTMzQS9HxQb37XmcRAo91AJ0d1l1LW0REaUEyVwrkhAF7u6+EYgCaA1aG /qrqS5JebnStbMbO/QD+YA0= =U6ta -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Nov 27, 2007 8:05 PM, Ron Johnson [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/27/07 19:35, Greg Smith wrote: [snip] to you. The minute performance becomes a serious concern, you'd be much better off with Linux, one of the BSDs that's not hobbled by using the Mach kernel, or one of the more serious UNIXes like Solaris. Wasn't there a time (2 years ago?) when PG ran pretty dog-like on SPARC? Only under Solaris. With Linux or BSD on it it ran pretty well. I had a Sparc 20 running RH 7.2 back in the day (or whatever the last version of RH that ran on sparc was) that spanked an Ultra-2 running slowalrus with twice the memory and hard drives handily. Solaris has gotten much better since then, I'm sure. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Linux v.s. Mac OS-X Performance
Only under Solaris. With Linux or BSD on it it ran pretty well. I had a Sparc 20 running RH 7.2 back in the day (or whatever the last version of RH that ran on sparc was) that spanked an Ultra-2 running slowalrus with twice the memory and hard drives handily. Solaris has gotten much better since then, I'm sure. Ubuntu is supposed to be able to spin on a T1000/T2000 and they have come out with a magical beast called Solaris 10 and in Sun's infinite wisdom they have decided to abandon the /etc/init.d/ and friends way of startup for some complex XML way of doing things. But otherwise its quite good (ZFS and Cool Thread servers being among the other good things out of Sun's shop). Cheers, Aly. -- Aly Dharshi [EMAIL PROTECTED] Got TELUS TV ? 310-MYTV or http://www.telus.com/tv A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux v.s. Mac OS-X Performance
Kind of. Mach is still running underneath (and a lot of the app APIs use it directly) but there is a BSD 'personality' above it which (AIUI) is big parts of FreeBSD ported to run on Mach. Right. Also, to be clear, OS X is not a true microkernel architecture. They took the division of responsibilities from the Mach microkernel design, but Mach is compiled into the kernel and is not a separate process from the kernel. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux v.s. Mac OS-X Performance
There are claims this is improved in current systems (Leopard + Intel), but the margin was so big before... IIRC, it was later established that during those tests they had fsync enabled on OS X and disabled on Linux. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux v.s. Mac OS-X Performance
Doug McNaught [EMAIL PROTECTED] writes: On Nov 27, 2007, at 8:36 PM, Gregory Stark wrote: I think (but I'm not sure) that the kernel in OSX comes from BSD. Kind of. Mach is still running underneath (and a lot of the app APIs use it directly) but there is a BSD 'personality' above it which (AIUI) is big parts of FreeBSD ported to run on Mach. So when you use the Unix APIs you're going through that. The one bit of the OSX userland code that I've really had my nose rubbed in is libedit, and they definitely took that from NetBSD not FreeBSD. You sure you got your BSDen straight? Some random poking around at http://www.opensource.apple.com/darwinsource/10.5/ finds a whole lot of different-looking license headers. But it seems pretty clear that their userland is BSD-derived, whereas I've always heard that their kernel is Mach-based. I've not gone looking at the kernel though. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] is the default of lowercase enforced on all OS platforms?
Hi List; I know that the default case for postgres (psql) on Linux/*ix platforms is lowercase. I wonder is this also true on Windows platforms? Thanks in advance.. /Kevin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] is the default of lowercase enforced on all OS platforms?
Kevin Kempter wrote: Hi List; I know that the default case for postgres (psql) on Linux/*ix platforms is lowercase. I wonder is this also true on Windows platforms? Yes. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] client_encoding error
Whilst trying to migrate one of our tables to postgres we get the following error: invalid byte sequence for encoding EUC_JP : 0x9c32 HINT: This error can also happen if the byte sequence does not match the enccding expected by the server, which is cont rolled by client_encoding. Does anyone have any idea why this might be? The data cing into the table should be plain ASCII Glyn Astill ___ Want ideas for reducing your carbon footprint? Visit Yahoo! For Good http://uk.promotions.yahoo.com/forgood/environment.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] WAL Recovery
Hello List Members, I have PostgreSQL 8.2.4 on Suse Linux Whne I am doing recovery from WAL archives I see in the logger that 01.history file not found infact server has not created such file insted it created 01.backup file which contains the history which documents also suggest is history file. Should I ignore this message in logger? Secondly, the logger says for e.g file 57 not fond in archive area which is present in pg_xlog and was not archived when server went down file upto 56 are there in archive. can anybody please explain why server giving message for filer 57 not found in archive area. With Regards Ashish... - Explore your hobbies and interests. Click here to begin.
[GENERAL] I have a select statement on the issue.
I have a select statement on the issue.
Following is the process I operate in four steps totally:
Step 1:Create Table
CREATE TABLE test
(
code character varying(32) NOT NULL,
name character varying(32) NOT NULL DEFAULT ''::character varying,
qty integer NOT NULL DEFAULT 0,
CONSTRAINT pk_test PRIMARY KEY (code)
)
Step 2:Insert Data
insert into test(code,name,qty) values('1001','1001name','qty');
insert into test(code,name,qty) values('1002','1002name','qty');
insert into test(code,name,qty) values('1003','1003name','qty');
Step 3:Select Data
select * from test
Results:
code name qty
1001 1001name 1
1002 1002name 2
1003 1003name 3
Step 4:Update Date
update test set name='name' where code='1002'
Results:
code name qty
1001 1001name 1
1003 1003name 3
1002 name 2
Question:
1. Why the default output changes after I execute the update statement?
2. Qustion, sorting as main keys when query, how to do?
Thank you.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] System Load analyze
Am Dienstag 27 November 2007 schrieb Scott Marlowe: On Nov 24, 2007 10:57 AM, Peter Bauer [EMAIL PROTECTED] wrote: i have a system here with 2 2.4GHz Xeon Processors, 2GB RAM, ONE Disk on a Battery Backed Write Cache SCSI Controller and PostgreSQL 8.1.4 running with the data on a DRBD Device for High Availability. The used database is also replicated to two similar machines with slony1. Why are you running a version of PostgreSQL with known data eating bugs? If you care for your data, you will keep up to date on releases. 8.1.10 was released on 2007-09-17. 8.1.4 was released on 2006-05-23. That's 16 months of bug fixes you're missing. Go here: http://www.postgresql.org/docs/8.1/static/release.html and read up on the fixes you're missing. Then update. Or just update. OK, on the the issue at hand. Since the load average is between 1 (most of the time) and 10 (peeks) i am worried about the load and executed vmstat and iostat which show that 1000-6000 Blocks are writen per second. Please check the attached output for further details. top shows that the CPUs are at least 80% idle most of the time so i think there is an I/O bottleneck. I'm aware that this hardware setup is probably not sufficient but is would like to investigate how critical the situation is. Yes. Battery backed cache can only do so much, it's not magic pixie dust. Once it's full, the drive becomes the bottle neck. Real db servers have more than one disk drive. They usually have at least 4 or so, and often dozens to hundreds. Also, not all battery backed caching RAID controllers are created equal. procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 0 0 398256 78328 140612 106355600 0 1472 2029 5081 4 3 92 0 0 2 398256 78268 140612 106357600 0 2304 1928 4216 0 2 98 0 1 2 398256 78100 140612 106357600 0 1464 1716 3994 1 3 96 0 0 0 398256 78704 140612 10635920 0 0 916 1435 3570 5 3 91 0 0 0 398256 77876 140612 1063616 00 0 0 305 1169 3 1 96 0 See that dip in the last line above where the blocks in drop to 0, idle jumps to 96, and blocks out drops, and context switches drop? That's most likely where postgresql is checkpointing. Checkpointing is where it writes out all the dirty buffers to disk. If the bgwriter is not tuned aggresively enough, checkpoints happen and make the whole database slow down for a few seconds. If it's tuned too aggresively then the db spends too much CPU time tracking the dirty buffers and then writing them. If tuned just right, it will write out the dirty buffers just fast enough that a checkpoint is never needed. You tune the bgwriter to your machine and I/O subsystem. If you're planning on getting more hard drives, do that first. Then tune the bgwriter. btw, if this is vmstat 1 running, it's showing a checkpoint every 20 or so seconds I think 0 2 398256 79136 140612 106396400 0 1736 1959 4494 4 2 94 0 checkpoint here: 0 0 398256 79132 140612 106396400 0 4 260 1039 1 1 98 0 0 0 398256 79052 140612 106398000 0 2444 3084 6955 6 5 89 0 0 2 398256 79060 140612 106398800 0 948 1146 3616 3 1 96 0 0 1 398256 78268 140612 106405600 0 1908 1809 4086 6 5 88 0 0 1 398256 76728 140612 106405600 0 6256 6637 15472 5 5 90 0 0 2 398256 77000 140612 106406400 0 4916 5840 12107 1 4 95 0 0 2 398256 76956 140612 10640680 0 0 6468 7432 15211 1 3 96 0 0 6 398256 77388 140612 1064072 00 0 8116 7826 18265 1 8 91 0 0 2 398256 74312 140612 106407600 0 7032 6886 16136 2 7 91 0 0 2 398256 74264 140612 106407600 0 5680 7143 13411 0 5 95 0 0 2 398256 72980 140612 106414000 0 5396 6377 13251 6 6 88 0 0 3 398256 76972 140612 106414800 0 5652 6793 14079 4 9 87 0 0 2 398256 77836 140612 106414800 0 3968 5321 14187 10 8 82 0 1 0 398256 77280 140612 106414800 0 1608 3188 8974 21 12 67 0 1 0 398256 77832 140612 106415200 0 236 834 2625 7 5 87 0 0 0 398256 77464 140612 106415200 0 244 505 1378 2 4 94 0 1 0 398256 77828 140612 106416400 0 316 580 1954 7 2 91 0 0 0 398256 77804 140612 106418000 0 740 673 2248 2 2 96 0 0 0 398256 77000 140612 10641800 0 0 304 589 1739 1 3 96 0 20 rows later, checkpoint here: 0 0 398256 77000 140612 106418400 0 0 216 886 0 1 99 0 0 0 398256 75452 140612 106418400 0 432 755 2032 6 1 93 0 max_fsm_pages = 4 # min max_fsm_relations*16, 6 bytes
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Tue, Nov 27, 2007 at 05:01:06PM -0700, Scott Ribe wrote: In general, you can expect any Unix based OS, which includes MacOS X, to perform noticeably better than Windows for PostgreSQL. Is that really true of BSD UNIXen??? I've certainly heard it's true of Linux. But with BSD you have the kernel funnel which can severely limit multitasking, regardless of whether threads or processes were used. Yes, very much so. Windows lacks the fork() concept, which is what makes PostgreSQL much slower there. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Unused item pointers - can someone explain?
On 26/11/2007, Elmer [EMAIL PROTECTED] wrote: On 23 Lis, 10:23, [EMAIL PROTECTED] (Peter Childs) wrote: Yes, however found x removable have just been found and are now unused, so on the next run this number will be added to the unused unless they get used again in the mean time. The number ie the unused is the number of tuples left in the free space map unused since the last vacuum. If its high it may be worth clustering or running vacuum full but only if you don't think you table will never or unlikely to grow (insert) or change (update) by less than that number of records before you next run vacuum. Generally only worry if the number is very very high (over 1). The best way of understanding the numbers is to run vacuum at regular intervals and compare the output. Peter. Thank you for your explanation - it's not easy to get help in this subject... But how it is possible that new unused. You wrote: The number ie the unused is the number of tuples left in the free space map unused since the last vacuum. This is important information for me but I still can't understand why this number keeps growing.Correct me if I wrong but if there is 17000 unused tuples in free space map, they should be used in first place for creating new tuples versions. This should cause that next 17000 operations (consists of INSERT,UPDATE,DELETE) would use fsm for row version creation instead of creating entirely new tuples at the end of table file. If I understand it correct number of unused item pointers should shrink between vacuums (but it still grows)... Hmm that should have been number of tuples left in the free space map at the start of the vacuum. So if you run a second vacuum in quick succession the number under removable will have been added the the unused value to become the new unused value. If you have a growing table unused should be 0 (or shrinking) If you have a busy table thats not vacuumed often enough unused should be 0 but removable will be high (so long as you vacuum at regular intervals) If you have a shrinking table unused will grow. If you have a steady table that was larger at some point the past eg an update to the whole table. unused will be large If unused is the amount of bloat you have but is only useful depending on when you last vacuumed if you vacuumed twice within a couple of seconds the second number is not very useful. If I'm wrong I'm sure someone will correct me. Peter Childs
