Re: [GENERAL] Cognitive dissonance
John Gage wrote: Posters are correctly referred to the documentation as frequently as possible. In fact, very frequently. The frequency might decrease if the documentation were in plain text. It is easier to search a single plain text file than any other source, except perhaps the database itself. In reality searches are being done on the web, which combines the HTML version of the official documentation with blog posts, presentation materials, the wiki, and similar other resources. This is why I don't actually care about a text version of the docs; I've just gotten used to using Google to search the PostgreSQL documentation. The occasional time when I know I just want to search the manual instead, I can search the PDF version. Neither of those are great solutions, but they're good enough that it's not worth fighting to build a text version over as I see it. I'd use it if it were around, but there's little motivation for most of us to work on it. Postgres is getting pushed off the map at the low end by MySQL, now owned by Oracle. The dynamics are much more complicated than that. Big MySQL sites are switching to NoSQL; medium sized MySQL sites are switching to PostgreSQL to get rid of scaling and reliability issues (I personally have been seeing a lot of this from Rails installs lately); small to medium size Oracle shops are switching to PostgreSQL to lower licensing costs. The idea that plain-text documentation for the database would be a significant driver in any of these trends would be greatly exaggerating the significance of a technical detail important to a pretty small number of people. On my personal list of things that could be improved in the documentation, good plain text format is there, but there's a whole lot of things above it. -- 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] Cognitive dissonance
On 09/06/2010, John Gage jsmg...@numericable.fr wrote: 1) On a list that howls with complaints when posts are in html, it is surprising that there is resistance to the idea of documentation in plain text. 2) Posters are correctly referred to the documentation as frequently as possible. In fact, very frequently. The frequency might decrease if the documentation were in plain text. It is easier to search a single plain text file than any other source, except perhaps the database itself. 3) Postgres is getting pushed off the map at the low end by MySQL, now owned by Oracle.If Postgres ceased to exist, Ellison would be thrilled. I chose A2 Hosting (with whom I am very happy) for my website because they support Postgres. I'm writing cgi scripts in perl. I had to install the postgres driver for dbi. It was not pre- installed. There are about four buttons for MySQL on the cPanel and two farther over on the right for Postgres. An anecdote. I discovered the tsvector functionality a while back. I have used it to create indices for my text files and several other tasks. I recently was re-looking at my files and saw tsvector::text. I had forgotten that the double colon is one way to cast a type. Double colon is not in the html index of the documentation. I found it by searching my plain text version of the pdf file. In my opinion, the html documentation is useful for reading it like a novel or referencing it in these lists. On Jun 8, 2010, at 9:56 PM, Josh Kupershmidt wrote: Not that I see a whole lot of utility in this endeavor Personally I like to use html docs, and it would be good if the documentation were downloadable from the postgresql website in other formats, for convenience... But, what I use is this, which works pretty well: (e.g. to get the 8.1 dosc) mkdir postgresql cd postgresql wget -r -nH -l 10 -k -np http://www.postgresql.org/docs/8.1/interactive/index.html ... then after it all downloads: open the file docs/8.1/interactive/index.html in your web browser. e.g. links docs/8.1/interactive/index.html HTML is text, so you can search using grep e.g. grep -r ALTER TABLE .* ADD COLUMN docs/8.1 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/ Fax: +27865510467 -- 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] [BUGS] BUG #5492: Query performs slowly and sequence corrupted
Thank you very much for your answer below. Just to keep you in the picture, the first problem has been solved with a FULL VACUUMING of the database. With reference to the sequence, I experience this problem when I operate with pgAdmin III. It seems that the sequence START value is replaced every time I refresh a database object, i.e. the schema containing the mentioned sequence. If I open a session with the Query tool and try to update the sequence with SETVAL function the returned value is correct. When I return back to pgAdmin III and look at the sequence object I see that the START value is different from the return value I obtained from the SETVAL function. This is a nonsense. I trust now the problem is clearer than yesterday. Thank you very much in advance for your feedback. Kind regards Piergiorgio Buongiovanni Robert Haas robertmh...@gmail.com 09/06/2010 05.04 Per Greg Stark gsst...@mit.edu CC Piergiorgio Buongiovanni piergiorgio.buongiova...@netspa.it, pgsql-b...@postgresql.org Oggetto Re: [BUGS] BUG #5492: Query performs slowly and sequence corrupted On Mon, Jun 7, 2010 at 5:33 PM, Greg Stark gsst...@mit.edu wrote: On Mon, Jun 7, 2010 at 5:36 PM, Piergiorgio Buongiovanni piergiorgio.buongiova...@netspa.it wrote: I reused the previous command to re-set the sequence value to the right one, but I see that the START value is now 59100. I reused the previous command another time and the START value is now 30440. I think this is a bug. I have a lot of problems with this sequence. Sequences wouldn't directly affect retrieval times. But one way you could get both of these symptoms is by having an application which inserts many rows but aborts and rolls back the inserts without committing. Perhaps a large copy which is interrupted. That would fill the table with garbage dead records which could slow down retrieval depending on the access method and also increase the sequence value. If this is what happened, CLUSTER on the table might be enough to fix the problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Disclaimer -- This message contains information which may be confidential. Unless you are the addressee (or authorized to receive for the addressee), you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received the message in error, please contact the sender by e-mail and delete the message. Many thanks. Il presente messaggio contiene informazioni di carattere riservato. Qualora non foste il destinatario (o autorizzato dallo stesso al ricevimento) non usate, copiate o rivelate il presente messaggio o le informazioni contenute. Se avete ricevuto il messaggio per errore, Vi preghiamo di cancellarlo e avvisare il mittente tramite e-mail. Grazie.
Re: [GENERAL] Cognitive dissonance
Brian Modra schrieb: Personally I like to use html docs, and it would be good if the documentation were downloadable from the postgresql website in other formats, for convenience... But, what I use is this, which works pretty well: (e.g. to get the 8.1 dosc) mkdir postgresql cd postgresql wget -r -nH -l 10 -k -np http://www.postgresql.org/docs/8.1/interactive/index.html ... then after it all downloads: open the file docs/8.1/interactive/index.html in your web browser. e.g. links docs/8.1/interactive/index.html HTML is text, so you can search using grep e.g. grep -r ALTER TABLE .* ADD COLUMN docs/8.1 Thats the way i do too. A huge pdf is often not very helpful. In my personal case i programm often in a train, using my laptop. Searching a PDF with more than 1.000 pages really hits my battery. With html-files i could preselect the items to search. Also it's possible to import the html-files in a postgres-db and using fulltext-search. ;) Greetings, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] server crash = libpq poll() hangs forever (Linux)
Hi, we had a kernel panic crashing our DB server today and all libpq clients (C and Perl clients) got stuck in poll() for hours even after the server was back up, i.e. longer than the tcp timeout should be: #0 0x2b2283b31c8f in poll () from /lib/libc.so.6 #1 0x2b228446f4af in PQmblen () from /usr/lib/libpq.so.4 #2 0x2b228446f590 in pqWaitTimed () from /usr/lib/libpq.so.4 #3 0x2b228446ee72 in PQgetResult () from /usr/lib/libpq.so.4 #4 0x2b228446ef4e in PQgetResult () from /usr/lib/libpq.so.4 #5 0x2b2284341ffe in pg_st_prepare_statement () from /usr/local/lib/perl/5.8.8/auto/DBD/Pg/Pg.so #6 0x2b228434eb25 in pg_st_execute () [...] It seems that poll() never receives a connection closed notification under Linux (https://lists.linux-foundation.org/pipermail/bugme-new/2003-April/008335.html - very old report, I can't find any newer information), so I am unsure how to handle such a case gracefully. I guess I'm having the same problem as reported in http://www.mail-archive.com/pgsql-hack...@postgresql.org/msg105844.html but there's no real conclusion there. Any suggestions? Can libpq be configured to use epoll or select perhaps? Is the libpq (8.1.19-0etch1) too old? Server version is 8.4.4, using tcp (no SSL). Regards, Marinos -- 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] Cognitive dissonance
My tupp'th: Formatted text, whether PDF, HTML or (heaven forbid!) Word Documents, is easier to read than unformatted plain text, and those of us without the OP's very admirable proficiency in vi remain at the mercy of the various readers and their associated search functions. However, I sure that it's not too arduous a task to extract the text in these documents and strip them of their formatting? Or am I missing something? -- 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] Some insight on the proper SQL would be appreciated
Plenty of solutions here: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -Ognjen On 8.6.2010 18:29, Aaron Burnett wrote: Greetings, I hope this is the proper list for this, but I am a loss on how to achieve one particular set of results. I have a table which is a list of users who entered a contest. They can enter as many times as they want, but only 5 will count. So some users have one entry, some have as many as 15. How could I distill this down further to give me a list that shows each entry per user up to five entries per user? In other words, I need a separate line item for each entry from each user up to the maximum of 5 rows per user. Table looks like this: username | firstname | lastname | signedup --+---+-+--- - ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-03-13 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-05-07 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-06-06 ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch | 2010-03-12 ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | 2010-04-25 ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra| Elliott | 2010-05-09 ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay | Maher | 2010-04-20 fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn | Woodul | 2010-04-05 fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie| Montijo | 2010-04-03 feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva| Anderson| 2010-04-03 feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith| Astroff | 2010-06-05 fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer | Lavigne | 2010-02-09 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-03-20 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-03-27 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-03 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-10 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-17 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-25 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-05-01 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-05-08 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-05-16 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-05-22 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-05-30 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-06-06 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | 2010-03-12 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | 2010-03-15 But in John Smith's case where he has more than 5 entries, I would like query results to limit him to just 5 entries to look like this: username | firstname | lastname | signedup --+---+-+--- - ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-03-13 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-05-07 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-06-06 ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch | 2010-03-12 ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | 2010-04-25 ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra| Elliott | 2010-05-09 ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay | Maher | 2010-04-20 fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn | Woodul | 2010-04-05 fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie| Montijo | 2010-04-03 feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva| Anderson| 2010-04-03 feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith| Astroff | 2010-06-05 fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer | Lavigne | 2010-02-09 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-03-20 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-03-27 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-03 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-10 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-17 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | 2010-03-12 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | 2010-03-15 The username is unique for each user. pg version 8.25 on RHEL Any help in this would be greatly appreciated. Thank you. -- 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] Some insight on the proper SQL would be appreciated
On 8 June 2010 17:29, Aaron Burnett aburn...@bzzagent.com wrote: Greetings, I hope this is the proper list for this, but I am a loss on how to achieve one particular set of results. I have a table which is a list of users who entered a contest. They can enter as many times as they want, but only 5 will count. So some users have one entry, some have as many as 15. How could I distill this down further to give me a list that shows each entry per user up to five entries per user? In other words, I need a separate line item for each entry from each user up to the maximum of 5 rows per user. Table looks like this: username | firstname | lastname | signedup --+---+-+--- - ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-03-13 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-05-07 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-06-06 ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch | 2010-03-12 ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | 2010-04-25 ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra | Elliott | 2010-05-09 ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay | Maher | 2010-04-20 fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn | Woodul | 2010-04-05 fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie | Montijo | 2010-04-03 feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva | Anderson | 2010-04-03 feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith | Astroff | 2010-06-05 fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer | Lavigne | 2010-02-09 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-03-20 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-03-27 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-03 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-10 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-17 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-25 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-05-01 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-05-08 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-05-16 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-05-22 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-05-30 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-06-06 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | 2010-03-12 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | 2010-03-15 But in John Smith's case where he has more than 5 entries, I would like query results to limit him to just 5 entries to look like this: username | firstname | lastname | signedup --+---+-+--- - ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-03-13 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-05-07 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | 2010-06-06 ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch | 2010-03-12 ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | 2010-04-25 ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra | Elliott | 2010-05-09 ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay | Maher | 2010-04-20 fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn | Woodul | 2010-04-05 fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie | Montijo | 2010-04-03 feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva | Anderson | 2010-04-03 feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith | Astroff | 2010-06-05 fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer | Lavigne | 2010-02-09 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-03-20 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-03-27 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-03 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-10 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | 2010-04-17 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | 2010-03-12 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | 2010-03-15 The username is unique for each user. pg version 8.25 on RHEL Any help in this would be greatly appreciated. Thank you. Bit crude, but if you have an id column, try: SELECT username, firstname, lastname, signedup FROM entries WHERE id IN (SELECT id FROM entries limitedentries WHERE limitedentries.username = entries.username ORDER BY signedup limit 5) ORDER BY username, signedup Regards
[GENERAL] How to show the current schema or search path in the psql PROMP
Hi, I am trying to figure out how I can show the current search_path, or better the first search_path entry (the active schema) in the PROMPT variable for psql. Is there any way to do that? I couldn't find anything useful ... -- ★ Clemens 呉 Schwaighofer ★ IT Engineer/Web Producer/Planning ★ E-Graphics Communications SP Digital ★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ★ Tel: +81-(0)3-3545-7706 ★ Fax: +81-(0)3-3545-7343 ★ http://www.e-gra.co.jp This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. If you received this e-mail in error, any review, use, dissemination, distribution or copying of this e-mail is strictly prohibited. Please notify us immediately of the error via e-mail to disclai...@tbwaworld.com and please delete the e-mail from your system, retaining no copies in any media. We appreciate your cooperation. -- 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] Cognitive dissonance
Dave Coventry dgcoven...@gmail.com writes: Formatted text, whether PDF, HTML or (heaven forbid!) Word Documents, is easier to read than unformatted plain text, and those of us without the OP's very admirable proficiency in vi remain at the mercy of the various readers and their associated search functions. However, I sure that it's not too arduous a task to extract the text in these documents and strip them of their formatting? Or am I missing something? Info documentation format. Text based, super user aware, easy to browse and search, has an index. You can even produce postgres.info today, it's just not optimised to be very friendly, it's missing mainly convenient table support and index. Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to return an INT4 subtracting dates?
Hi, I need to return an int4 subtracting two dates, but returns me an interval. select end_date - now() as interger_number from hist_anuncios How to return an integer out of this? Best Regards,
Re: [GENERAL] How to return an INT4 subtracting dates?
Hello 2010/6/9 Andre Lopes lopes80an...@gmail.com: Hi, I need to return an int4 subtracting two dates, but returns me an interval. select end_date - now() as interger_number from hist_anuncios How to return an integer out of this? Best Regards, postgres=# select '2010-06-18'::date - CURRENT_DATE; ?column? -- 9 (1 row) Regards pavel Stehule -- 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] How to return an INT4 subtracting dates?
On 09/06/2010 11:57, Andre Lopes wrote: Hi, I need to return an int4 subtracting two dates, but returns me an interval. select end_date - now() as interger_number from hist_anuncios How to return an integer out of this? Hmmm, according to the docs, subtracting dates returns an integer: http://www.postgresql.org/docs/8.4/static/functions-datetime.html Try current_date, which gives you a date, instead of now, which gives you a timestamp. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Windows: Compiling and linking dynamically-loaded functions
Hello, within Section 34.9.6. of the PostgreSQL documentation ( http://www.postgresql.org/docs/8.4/static/xfunc-c.html) there is an excellent summary how to compile and link extensions on a variaty of Unix and Unix-like operating systems. How do I do the same on Windows, using Visual C Express ? Any hints or documentation URLs? best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - Using PostgreSQL is mostly about sleeping well at night.
Re: [GENERAL] Some insight on the proper SQL would be appreciated
In article 4c0f4ba8.3040...@gmail.com, Ognjen Blagojevic ognjen.d.blagoje...@gmail.com writes: Plenty of solutions here: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ This doesn't mention the incredibly powerful windowing functions of PostgreSQL = 8.4.0: SELECT username, firstname, lastname, signedup FROM ( SELECT username, firstname, lastname, signedup, row_number() OVER (PARTITION BY username ORDER BY signedup) FROM mytbl ) dummy WHERE row_number = 5 -- 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] server crash = libpq poll() hangs forever (Linux)
Marinos Yannikos m...@geizhals.at writes: It seems that poll() never receives a connection closed notification under Linux (https://lists.linux-foundation.org/pipermail/bugme-new/2003-April/008335.html - very old report, very old report is right. What makes you think that has anything to do with modern kernel versions? 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] vacuum process is hanging
Greetings, I've got an 8.1.10 instance running on Linux-i686. The system hosts 5 databases, all of which get vacuumed via a cronjob 3 times a day. All of a sudden, the vacuum job for 1 of the databases is hanging indefinitely. It normally finishes in under 5 minutes. There are no errors in the output, it just stops producing any output. The command that I'm running is vacuumdb -v -z -f -d inventory. I also tried removing the -z and -f options to see if that would at least get it to complete, but that had no impact. Here's the tail end of the output, leading up to the hang: INFO: vacuuming public.cuhc INFO: index cuhc_system_key now contains 0 row versions in 4 pages DETAIL: 1 index pages have been deleted, 1 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index cuhc_system_index now contains 0 row versions in 4 pages DETAIL: 1 index pages have been deleted, 1 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index cuhc_syststamp_index now contains 0 row versions in 4 pages DETAIL: 1 index pages have been deleted, 1 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index cuhc_syststamprebooted_index now contains 0 row versions in 4 pages DETAIL: 1 index pages have been deleted, 1 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: cuhc: found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming pg_toast.pg_toast_85894 INFO: index pg_toast_85894_index now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: pg_toast_85894: found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. Anyone have any suggestions on what to try to debug this? 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] vacuum process is hanging
Lonni J Friedman netll...@gmail.com writes: I've got an 8.1.10 instance running on Linux-i686. The system hosts 5 databases, all of which get vacuumed via a cronjob 3 times a day. All of a sudden, the vacuum job for 1 of the databases is hanging indefinitely. Is it actually blocked, or just busy? (strace'ing the vacuum process would be one pretty definitive way of telling.) 8.1.10 is mighty old, so frankly I'd suggest an update to 8.1.recent before you expend a whole lot of effort tracing the problem. 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
Re: [GENERAL] Cognitive dissonance
Excerpts from John Gage's message of mié jun 09 01:28:54 -0400 2010: I recently was re-looking at my files and saw tsvector::text. I had forgotten that the double colon is one way to cast a type. Double colon is not in the html index of the documentation. I just added an index entry for ::, thanks for pointing out that it was missing. If you notice other missing index entries, do not hesitate to point it out in this mailing list or pgsql-docs. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] vacuum process is hanging
On Wed, Jun 9, 2010 at 12:13 PM, Lonni J Friedman netll...@gmail.com wrote: hat I'm running is vacuumdb -v -z -f -d inventory. I also tried removing the -z and -f options to see if that would at least get it to complete, but that had no impact. Here's the tail end of the output, leading up to the hang: Check the pg_stat_activity view for any processes waiting on locks, or in idle in transaction state for a long time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Locale, LL_COLLATE and sorting case-insensatively
Is there a way to sort case-insensitively without using LOWER()? I thought that a combination of encoding and LC_COLLATE would achieve this, but everything I've tried so far has resulted in the dreaded caps-come-first(tm) behavior. A setting in the DB to force text fields to be sorted case insensitively would do a lot to simplify parts of our application. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] checkpoint spikes
Hi, we currently encounter an increasing load on our website. With the increasing load we see some problems on our database. so we checked what happens and we saw spikes in our load when checkpoints are about to finish. Our configuration: max_connections = 125 ssl = false shared_buffers = 500MB work_mem = 15MB maintenance_work_mem = 250MB synchronous_commit = off full_page_writes = off checkpoint_segments = 10 checkpoint_timeout = 10min checkpoint_completion_target = 0.9 random_page_cost = 2 effective_cache_size = 5000MB autovacuum = off (we put autovacuum to off because we suspected it). all the other parameters are set to default (beside log parameters and so on). Our machine has 12 GB RAM, i7-975 CPU and a SW-Raid-1 for datadir and another one for the rest of the server (including postgresql logfiles). Disk are Barracuda 7200.11 SATA 3Gb/s 1.5-TB and we are running debian lenny. these are our checkpoint log statements: 2010-06-09 17:24:27 CEST [6375]: [1-1] LOG: checkpoint starting: time 2010-06-09 17:28:09 CEST [6375]: [2-1] LOG: checkpoint complete: wrote 2861 buffers (4.5%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=193.057 s, sync=29.259 s, total=222.353 s 2010-06-09 17:34:27 CEST [6375]: [3-1] LOG: checkpoint starting: time 2010-06-09 17:39:09 CEST [6375]: [4-1] LOG: checkpoint complete: wrote 3247 buffers (5.1%); 0 transaction log file(s) added, 0 removed, 2 recycled; write=255.255 s, sync=26.911 s, total=282.177 s 2010-06-09 17:44:27 CEST [6375]: [5-1] LOG: checkpoint starting: time 2010-06-09 17:49:41 CEST [6375]: [6-1] LOG: checkpoint complete: wrote 2746 buffers (4.3%); 0 transaction log file(s) added, 0 removed, 2 recycled; write=280.743 s, sync=33.392 s, total=314.147 s 2010-06-09 17:54:27 CEST [6375]: [7-1] LOG: checkpoint starting: time 2010-06-09 17:58:59 CEST [6375]: [8-1] LOG: checkpoint complete: wrote 3118 buffers (4.9%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=253.293 s, sync=18.585 s, total=271.892 s 2010-06-09 18:04:27 CEST [6375]: [9-1] LOG: checkpoint starting: time 2010-06-09 18:08:46 CEST [6375]: [10-1] LOG: checkpoint complete: wrote 2695 buffers (4.2%); 0 transaction log file(s) added, 0 removed, 2 recycled; write=225.173 s, sync=33.789 s, total=258.972 s 2010-06-09 18:14:27 CEST [6375]: [11-1] LOG: checkpoint starting: time 2010-06-09 18:18:30 CEST [6375]: [12-1] LOG: checkpoint complete: wrote 2868 buffers (4.5%); 0 transaction log file(s) added, 0 removed, 2 recycled; write=215.561 s, sync=27.701 s, total=243.271 s What we saw is a rather long sync time. And exactly at this time, our responses become slow and the server load increases. this is from pg_stat_bgwriter: checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc ---+-++---+--+-+--- 3495 | 0 |9070242 | 10798927 | 14421 | 6412707 | 208340755 We tried to increase checkpoint_timeout to 20 minutes but it was getting worse: 2010-06-09 18:34:27 CEST [6375]: [13-1] LOG: checkpoint starting: time 2010-06-09 18:42:49 CEST [6375]: [14-1] LOG: checkpoint complete: wrote 2956 buffers (4.6%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=448.265 s, sync=54.087 s, total=502.377 s here we see a 54 sec sync time and a much higher load on sync time compared to the 10 minutes checkpoint_timeout. do you have any hints for us how to tune our configuration to avoid spikes? kind regards Janning -- 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] vacuum process is hanging
On Wed, Jun 9, 2010 at 10:13 AM, Lonni J Friedman netll...@gmail.com wrote: Greetings, I've got an 8.1.10 instance running on Linux-i686. The system hosts 5 databases, all of which get vacuumed via a cronjob 3 times a day. All of a sudden, the vacuum job for 1 of the databases is hanging indefinitely. It normally finishes in under 5 minutes. There are no errors in the output, it just stops producing any output. The command that I'm running is vacuumdb -v -z -f -d inventory. I also tried removing the -z and -f options to see if that would at least get it to complete, but that had no impact. Here's the tail end of the output, leading up to the hang: Well, -f isn't highly recommended anyway, and if you have to do it you probably need to schedule a reindex to run after it. Anyway, what does top say about the pg process running the vacuum? -- 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] server crash = libpq poll() hangs forever (Linux)
On 9 Juni, 16:37, t...@sss.pgh.pa.us (Tom Lane) wrote: Marinos Yannikos m...@geizhals.at writes: It seems that poll() never receives a connection closed notification under Linux (https://lists.linux-foundation.org/pipermail/bugme-new/2003-April/008...- very old report, very old report is right. What makes you think that has anything to do with modern kernel versions? Interesting. The bug report includes a short code snippet which compiles to a c program, that shows the bug is still present. I'm on b...@tova:~$ uname -a Linux tova 2.6.31-22-generic #60-Ubuntu SMP Thu May 27 00:22:23 UTC 2010 i686 GNU/Linux is it really so, that the bug is still valid, or does the code snippet show something else? /Björn -- 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] Queues Problem
2010/6/8 Oliver Kohll - Mailing Lists oliver.li...@gtwm.co.uk On 8 Jun 2010, at 20:12, uaca man uaca...@gmail.com wrote: 2) Think of the front end as changing states as the user interacts with it, then figure out what queries need to be made to correspond to the changes in state. [snip] That is exactly what we are doing for the most part and was our first bet with the buildings, however since building can affect pretty much anything, anywhere on the game changing states as the user interacts is getting beyond comprehension of a human mind(al least for my mind) Might a rules engine be useful? http://en.wikipedia.org/wiki/Business_rule_management_system Drools is one example. Regards Oliver Kohll oli...@agilebase.co.uk / +44(0)7814 828608 / skype:okohll www.agilebase.co.uk Oh Yeah! A BRMS might be just what I’m look for! I did not know that such thing existed, but, there is always a but!! I don’t think we have the buget to use one, will investigate further. Thanks
Re: [GENERAL] server crash = libpq poll() hangs forever (Linux)
Excerpts from björn lundin's message of mié jun 09 16:17:57 -0400 2010: On 9 Juni, 16:37, t...@sss.pgh.pa.us (Tom Lane) wrote: Marinos Yannikos m...@geizhals.at writes: It seems that poll() never receives a connection closed notification under Linux (https://lists.linux-foundation.org/pipermail/bugme-new/2003-April/008...- very old report, very old report is right. What makes you think that has anything to do with modern kernel versions? Interesting. The bug report includes a short code snippet which compiles to a c program, that shows the bug is still present. I'm on That test program uses UDP sockets. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Cognitive dissonance
Brian Modra wrote: Personally I like to use html docs, and it would be good if the documentation were downloadable from the postgresql website in other formats, for convenience... Good thing it is, then, albeit not in the most convenient format, i.e., DocBook. But then, from there you can generate pretty much any format you want, right? -- Lew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] calculating distance between longitude and latitude
Does postgresql have functions to calculate the distance between two sets of longitude and latitude. -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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] calculating distance between longitude and latitude
2010/6/9 Geoffrey li...@serioustechnology.com Does postgresql have functions to calculate the distance between two sets of longitude and latitude. -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson Hi, the simplest way is to use PostGis, all spatial functions are there. regards Szymon Guz
Re: [GENERAL] calculating distance between longitude and latitude
Does postgresql have functions to calculate the distance between two sets of longitude and latitude. You're looking for the earthdistance contrib module. With most Linux distros it's installed under /usr/share/postgresql/8.xx/contrib You may have to install a postgresql-contrib package depending on your distro. Typing locate earthdistance.sql should reveal the location if it's available already. To activate you'd just do a pgsql [database] /whereever/earthdistance.sql HTH Uwe -- 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] server crash = libpq poll() hangs forever (Linux)
=?ISO-8859-1?Q?bj=F6rn_lundin?= b.f.lun...@gmail.com writes: On 9 Juni, 16:37, t...@sss.pgh.pa.us (Tom Lane) wrote: very old report is right. What makes you think that has anything to do with modern kernel versions? Interesting. The bug report includes a short code snippet which compiles to a c program, that shows the bug is still present. I'm on Mph. Reading the bug report and the code snippet more closely, the complaint is totally irrelevant to libpq anyway. What he's complaining about is a case where another thread of a multithreaded application close()s the descriptor that a poll() is using. That is *not* related to the other end of the connection closing the connection, which is the case the OP was concerned about. 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] pgcon 2010 videos?
i've found some videos of conference at http://www.fosslc.org/drupal/category/event/pgcon2010 but some are missing. also, there is no mention of videos on pgcon page. anybody knows if missing videos will appear somewhere and why there is no links on pgcon site? Aljosa Mohorovic -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
