Re: [GENERAL] Cacti + PostgreSQL Graphing
Marc G. Fournier wrote: We are mainly a nagios / cacti environment, and I came across... Why start with Cacti then? There's way more Nagios integration available; the little check_pgsql that comes with it (I think it's still there), and the whole list at http://exchange.nagios.org/directory/Plugins/Databases/PostgresQL I'd think that starting with http://bucardo.org/wiki/Check_postgres for example would put you way ahead of anything that's available for Cacti. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automatic index advisor?
Bill Karwin wrote: When I searched for a solution for PostgreSQL, Gurjeet Singh's name came up as the designer of an index adviser patch for PG. But the latest information I found was in 2007. Sort of. That was originally written by Kai-Uwe Sattler: http://archives.postgresql.org/pgsql-patches/2006-10/msg00083.php Gurjeet talked a bit about his role in updating the whole thing at http://archives.postgresql.org/pgsql-patches/2007-04/msg00300.php and the result of that is that it needed some rework to be acceptable to the main project. Shortly afterwards Tom Lane updated the internal database hooks to allow a better integrated patch: http://archives.postgresql.org/pgsql-patches/2007-05/msg00421.php And as far as I know that's where it stopped at as far as community work goes. Gurjeet created the pgFoundry project but never uploaded any files compatible with the new hooks, and the Index Advisor project remained in the Wishlist for the 8.3 and 8.4 versions of the project without anyone seeing new patches submitted. If Gurjeet's patch is still under development, are there any other tools currently available for PostgreSQL that provide this kind of capability? It may still be under development inside Enterprise DB, I haven't seen any reference to it but wouldn't be surprised if it shows up one day as a part of the next release of their Postgres Plus Advanced Server commercial product product or something like that. He's still floating around and may chime in directly here. We have a commercial product that does tuning advisory work at http://www.tuningcloud.com/ including index suggestions, but this list isn't the place to launch into a full ad about that. Much like Parallel Query, this sort of project is just big enough to be hard to fund without a commercial sponsor, and once you have one of those and it all works it's really difficult to then give the result away when it's an easy thing to sell as an add-on. There's certainly a dollar figure that would allow taking Kai-Uwe and Gurjeet's work and funding the necessary improvements to finish off making that integrated directly into the database. So far, if anybody has done that, they're not talking about it and/or not releasing it as open-source that I know of. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can not match 0 on bytea
Hi! Data type of table1.c1 is bytea. That column stores binary data. The following matchings do not work. What is the right syntax? TIA CN --- select c1 ~ E'\000' from table1; select c1 LIKE E'%\000%' from table1; ERROR: invalid byte sequence for encoding UTF8: 0x00 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] regexp_replace: LF, CR, or tab
Hi! I am trying to replace characters '\r', '\n', or '\t' with space character ' '. As an example, I want string A\t\n\rB becomes AB. The following statement seems to be not working. What mistake have I made? TIA CN select regexp_replace(E'A\r\n\tB',E'[\r\n\t]',' '); regexp_replace A B (1 row) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: value out of range: underflow
2010/2/26 Anton Maksimenkov anton...@gmail.com:
When I try it in pgAdmin, first time it show me error:
--
ERROR: value out of range: underflow
CONTEXT: PL/pgSQL function myf_convert_phone18digits line 12 at assignment
--
SECOND (and consequences) time it works and just show result
771506
Oh, sorry to all. It was:
SELECT myf_convert_phone18digits('7715060');
I still interesting why it fail first time but executed at second.
2010/2/26 Thom Brown thombr...@gmail.com:
Are you sure you wish to perform calculations on a phone number? Why
not pad it out? Like: select rpad('2329382',18,'0') which would
return 2329382000
Damn, right! I'll use it.
--
antonvm
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can not match 0 on bytea
seil...@so-net.net.tw wrote: Data type of table1.c1 is bytea. That column stores binary data. The following matchings do not work. What is the right syntax? TIA CN --- select c1 ~ E'\000' from table1; select c1 LIKE E'%\000%' from table1; selection position(E'\\000'::bytea in c1) from table1; The value is 0 when there is no match and 0 otherwise. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] regexp_replace: LF, CR, or tab
2010/2/27 seil...@so-net.net.tw: Hi! I am trying to replace characters '\r', '\n', or '\t' with space character ' '. As an example, I want string A\t\n\rB becomes AB. The following statement seems to be not working. What mistake have I made? TIA CN select regexp_replace(E'A\r\n\tB',E'[\r\n\t]',' '); regexp_replace A B (1 row) Try: select regexp_replace(E'A\r\n\tB',E'[\r\n\t]',' ','g'); Osvaldo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can not match 0 on bytea
seil...@so-net.net.tw writes: Data type of table1.c1 is bytea. That column stores binary data. The following matchings do not work. What is the right syntax? TIA CN --- select c1 ~ E'\000' from table1; select c1 LIKE E'%\000%' from table1; ERROR: invalid byte sequence for encoding UTF8: 0x00 The reason that doesn't work is that E'\000' is initially a text literal, with the backslash sequence being processed by the string literal parser; and a zero byte isn't allowed in text. Try it with E'\\000'. What this gives rise to is a text constant containing the four characters \ 0 0 0, and then when that gets converted to bytea, another round of backslash processing will happen to produce the (legal) bytea constant with a single zero byte. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Partial foreign keys
Hello, Due to lack of support for partial (conditional) multi-column foreign keys in 8.3, can before-triggers be used to implement them in terms of data consistency and speed? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] trouble with to_char('L')
Hiroshi Inoue wrote: Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: Where are we on this issue? Oops I forgot it completely. I have a little improved version and would post it tonight. Ah, very good. Thanks. Attached is an improved version. FYI, I am working on this patch now and will post an updated version. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automatic index advisor?
On Feb 27, 2010, at 12:25 AM, Greg Smith wrote: So far, if anybody has done that, they're not talking about it and/ or not releasing it as open-source that I know of. Thanks Greg, it's good to get confirmation of this. I can certainly understand how implementing this feature to a point that would be useful would be a complex project. Regards, Bill Karwin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Hung postmaster (8.3.9)
Need some help. My PostgreSQL 8.3.6 and now 8.3.9 postmaster is hanging. No idea why, been running like a top for a year. Can't do select version(), even hung after system reboot. SIGINT/QUIT/TERM have no effect, only SIGKILL can stop it. This is Linux 2.6.18-92.1.22.el5 SMP x86_64 Here's the log after restarting from SIGKILL: 2010-02-27 20:11:10.426 CST [23134]LOG: database system was interrupted; last known up at 2010-02-27 20:08:46 CST 2010-02-27 20:11:10.426 CST [23134]DEBUG: checkpoint record is at 8BC/63F5FC0 2010-02-27 20:11:10.426 CST [23134]DEBUG: redo record is at 8BC/63F5FC0; shutdown TRUE 2010-02-27 20:11:10.426 CST [23134]DEBUG: next transaction ID: 0/488483782; next OID: 1619873999 2010-02-27 20:11:10.426 CST [23134]DEBUG: next MultiXactId: 130401682; next MultiXactOffset: 830996786 2010-02-27 20:11:10.426 CST [23134]LOG: database system was not properly shut down; automatic recovery in progress 2010-02-27 20:11:10.497 CST [23134]LOG: record with zero length at 8BC/63F6020 2010-02-27 20:11:10.497 CST [23134]LOG: redo is not required 2010-02-27 20:11:10.503 CST [23134]LOG: checkpoint starting: shutdown immediate 2010-02-27 20:11:10.581 CST [23134]LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.045 s, sync=0.000 s, total=0.083 s 2010-02-27 20:11:10.587 CST [23134]DEBUG: transaction ID wrap limit is 2435542396, limited by database ohsdba 2010-02-27 20:11:10.589 CST [23134]DEBUG: proc_exit(0) 2010-02-27 20:11:10.589 CST [23134]DEBUG: shmem_exit(0) 2010-02-27 20:11:10.592 CST [23134]DEBUG: exit(0) Here's the backtrace: $ gdb `which postgres` GNU gdb Fedora (6.8-37.el5) Copyright (C) 2008 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later http://gnu.org/licenses/gpl.html This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. Type show copying and show warranty for details. This GDB was configured as x86_64-redhat-linux-gnu... (gdb) attach 3776 Attaching to program: /opt/pgsql/installs/postgresql-8.3.9/bin/postgres, process 3776 Reading symbols from /lib64/libcrypt.so.1...done. Loaded symbols for /lib64/libcrypt.so.1 Reading symbols from /lib64/libdl.so.2...done. Loaded symbols for /lib64/libdl.so.2 Reading symbols from /lib64/libm.so.6...done. Loaded symbols for /lib64/libm.so.6 Reading symbols from /lib64/libc.so.6...done. Loaded symbols for /lib64/libc.so.6 Reading symbols from /lib64/ld-linux-x86-64.so.2...done. Loaded symbols for /lib64/ld-linux-x86-64.so.2 Reading symbols from /lib64/libnss_files.so.2...done. Loaded symbols for /lib64/libnss_files.so.2 0x00346f8c43a0 in __read_nocancel () from /lib64/libc.so.6 (gdb) p debug_query_string $1 = 0x0 (gdb) bt #0 0x00346f8c43a0 in __read_nocancel () from /lib64/libc.so.6 #1 0x00346f86c747 in _IO_new_file_underflow () from /lib64/libc.so.6 #2 0x00346f86d10e in _IO_default_uflow_internal () from /lib64/libc.so.6 #3 0x00346f8689cb in getc () from /lib64/libc.so.6 #4 0x00531ee8 in next_token (fp=0x5b90f20, buf=0x7fff59bef330 , bufsz=4096) at hba.c:128 #5 0x00532233 in tokenize_file (filename=0x5b8f3f0 global, file=0x5b90f20, lines=0x7fff59bef5c8, line_nums=0x7fff59bef5c0) at hba.c:232 #6 0x005322e9 in tokenize_file (filename=0x5b8f3d0 global/pg_auth, file=0x5b90ce0, lines=0x98b168, line_nums=0x98b170) at hba.c:358 #7 0x005327ff in load_role () at hba.c:959 #8 0x0057f300 in reaper (postgres_signal_arg=value optimized out) at postmaster.c:2145 #9 signal handler called #10 0x00346f8cb323 in __select_nocancel () from /lib64/libc.so.6 #11 0x0057cc33 in ServerLoop () at postmaster.c:1236 #12 0x0057dfdf in PostmasterMain (argc=6, argv=0x5b73fe0) at postmaster.c:1031 #13 0x005373de in main (argc=6, argv=value optimized out) at main.c:188 (gdb) quit The program is running. Quit anyway (and detach it)? (y or n) y Detaching from program: /opt/pgsql/installs/postgresql-8.3.9/bin/postgres, process 3776 Thanks in advance for any help. Ed -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hung postmaster (8.3.9)
Ed L. wrote: 2010-02-27 20:11:10.426 CST [23134]LOG: database system was not properly shut down; automatic recovery in progress 2010-02-27 20:11:10.497 CST [23134]LOG: record with zero length at 8BC/63F6020 2010-02-27 20:11:10.497 CST [23134]LOG: redo is not required If the server is hung at this point, it was doing something: recovery from a crash, and during that period it will not respond to statements until it's finished as you noted. That can take some time, could run into the minutes. And if you kill it, next time the process starts all over again having made no progress. I'd try starting the server again, confirm it's only after this point it pauses at, watching if it's doing something with top -c, and grab some backtraces of what it's doing then. In this sample you gave, the shutdown was too fast for the server have done very recovery before it was killed off. It may just be you need to let it sit there for a while to finish recovery and then it will be fine again, can't tell from the data you've provided so far. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hung postmaster (8.3.9)
On Saturday 27 February 2010 @ 22:18, Greg Smith wrote: Ed L. wrote: 2010-02-27 20:11:10.426 CST [23134]LOG: database system was not properly shut down; automatic recovery in progress 2010-02-27 20:11:10.497 CST [23134]LOG: record with zero length at 8BC/63F6020 2010-02-27 20:11:10.497 CST [23134]LOG: redo is not required If the server is hung at this point, it was doing something: recovery from a crash, and during that period it will not respond to statements until it's finished as you noted. That can take some time, could run into the minutes. And if you kill it, next time the process starts all over again having made no progress. I'd try starting the server again, confirm it's only after this point it pauses at, watching if it's doing something with top -c, and grab some backtraces of what it's doing then. In this sample you gave, the shutdown was too fast for the server have done very recovery before it was killed off. It may just be you need to let it sit there for a while to finish recovery and then it will be fine again, can't tell from the data you've provided so far. Good point from my example, but it hung like that for 8 hours, and seems to be ready to do so again. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
