http://permalink.gmane.org/gmane.comp.db.postgresql.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://permalink.gmane.org/gmane.comp.db.postgresql.general/163013 <pre> Take a look at this?: http://www.piware.de/2012/05/packages-for-postgresql-9-2-beta-1-now-available/ </pre> Adrian Klaver 2012-05-25T22:19:56 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163012 <pre>I'm trying to build postgres 9.2 beta1 on debian stable from sources, in a way that plays nicely with the debian cluster management stuff. If I configure with libdir=/usr/lib/postgres everything works but the shared libraries end up in the wrong place (ie just where I specified). If I configure with libdir=/usr/lib/postgresql/9.2, which is where I want the libraries to go, psql complains: $ /usr/lib/postgresql/9.2/bin/psql: symbol lookup error: /usr/lib/postgresql/9.2/bin/psql: undefined symbol: PQconnectdbParams Here is my configure command: $ ./configure --build=i486-linux-gnu --prefix=/usr --includedir=/usr/include/postgresql/9.2 --mandir=/usr/share/postgresql/9.2/man --infodir=/usr/share/postgresql/9.2/info --sysconfdir=/etc/postgresql-common --localstatedir=/var --libexecdir=/usr/lib/postgresql/9.2 --libdir=/usr/lib/postgresql/9.2 --srcdir=. -docdir=/usr/share/doc/postgresql-doc-9.2 --datadir=/usr/share/postgresql/9.2/ --bindir=/usr/lib/postgresql/9.2/bin --enable-nls --enable-integer-datetimes --</pre> Marc Munro 2012-05-25T22:05:37 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163011 <pre> Seems to be slower across the board regardless of what is being vacuumed (tables or indices). For example, i have a relatively small table (currently 395 rows, rarely has any inserts or deletes). vacuuming just the table (not indices) takes on average 4s with the khugepaged defragmenter on, and less than 1s with it off. vacuuming just the indices takes on average 2s with the khugepaged defragmenter on, and less than 1s with it off. The much larger tables (thousands to millions of rows), I see similar performance (although they take even longer to complete with &amp; without the khugepaged defragmenter enabled). </pre> Lonni J Friedman 2012-05-25T15:22:00 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163010 <pre> You're quite right. FWIW, it wasn't intended to be a long rant - I started out writing a point-by-point review of a few usability issues - though it certainly turned into a long rant as I started seeing more and more issues. It's my intention to break that down into specific problem areas and points, I just thought it was worth getting a few initial impressions too. -- Craig Ringer </pre> Craig Ringer 2012-05-25T11:15:49 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163009 <pre> Agreed on all points, I've had to help people using pgAdmin with many of the problems you point out. Note that the pgadmin-hackers list is also relevant. Together with an earlier study about common PostgreSQL pitfalls, I've created an article on the wiki: https://wiki.postgresql.org/wiki/Usability_reviews However, I suspect posting long rants like this won't get us very far. Instead, I think these problems should be posted one by one, with proposed solutions, so they can be discussed in detail without distractions. Once there seems to be a consensus for a solution, add a TODO list item -- I suspect most of these items are quite easy to solve in code, once it's clear *how* to address them. And the TODO list has been short of simple items recently. Regards, Marti </pre> Marti Raudsepp 2012-05-25T10:55:26 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163008 <pre>Hi all I just had the ... pleasure ... of using Windows with Pg again and was in a usability review frame of mind. I landed up trying to restore my database using PgAdmin-III, and was astonished at how difficult and painful it was. The issues weren't all PgAdmin-III either, there are a few Pg-on-Windows issues and a few plain warts in terms of PostgreSQL usability in general. It felt like my first experience with Oracle (ie: screaming, pain and confusion) not the smooth and pleasurable experience I've come to be so used to with Pg. I was sufficiently surprised by some of the issues that I've written up a post on the matter. I intended it to be a few usability notes, though it's turned into a bit more than that. I think it's really imporant to highlight these issues, because if this had been my first experience with PostgreSQL I would have walked away and never, ever, ever come back. It might be premature to post this before I've reviewed and re-edited the post, but hey, a few flames won't hu</pre> Craig Ringer 2012-05-25T09:56:47 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163007 <pre> Good to know all this... it might be worth adding it to the website or the wiki for the benefit of us blow-ins. :-) Ray. </pre> Raymond O'Donnell 2012-05-25T08:49:18 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163006 <pre>Thank you Laurenz, Even executing $ postgres -D /data/directory Used to start server in deamon mode, the reason was the postgres.conf was having "silent_mode = on" ( freebsd port used to do this ). Got this solved by sending "silent_mode=off" command line. With this my problem is solved. Trying to train my watch dog to look into postmaster.pid is also good idea but I do not want to work on that as it is working fine. Regards Karthik On 5/25/12 1:50 PM, "Albe Laurenz" &lt;laurenz.albe&lt; at &gt;wien.gv.at&gt; wrote: </pre> Karthik 2012-05-25T08:44:45 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163005 <pre>process. Is there a way to avoid process should be the process that was the older version used by us we have our watchdog failing to (postgres) (postgres) If you really want that, start the PostgreSQL server this way: $ postgres -D /data/directory &amp; But can't you teach your watchdog to read the postgresql.pid file? Yours, Laurenz Albe </pre> Albe Laurenz 2012-05-25T08:20:50 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163004 <pre>2012/5/25 Raghavendra &lt;raghavendra.rao&lt; at &gt;enterprisedb.com&gt;: I believe so some features - like advanced formatting will be accepted sometime. Macros are little bit different story - it is relative difficult implement them in current psql, because code is complex - there are unclean and unreadable support of readline and interactive mode. I had a idea of noninteractive console, that can support macros - but there are lot of issues still. I would to work on some better integration between client and server inlined procedures, and on simpler using psql from bash. Implementing own full macro language is not necessary. Probably better way is enable extending console statements with possibility to call external scripts. Regards Pavel </pre> Pavel Stehule 2012-05-25T03:45:29 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163003 <pre>Oh ok. Thank you for the info. Multi-Headers, Macros of psql 8.4 were very interesting features, sustaining them in 9.1 would have matured psql console alot. --Raghav </pre> Raghavendra 2012-05-25T01:33:09 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163002 <pre> Sorry, I don't know of any solution to this except perhaps creating all-committed clog files to match the missing file. </pre> Bruce Momjian 2012-05-25T01:24:54 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163001 <pre> Uh, the original Berkeley project name was postgres, pgsql is used as a short-hand for PostgreSQL, and psql is short for 'PostgreSQL SQL Monitor'. libpq originally supported Postgres QUEL: http://en.wikipedia.org/wiki/QUEL_query_languages </pre> Bruce Momjian 2012-05-25T01:21:30 http://permalink.gmane.org/gmane.comp.db.postgresql.general/163000 <pre>2012/5/24 Raghavendra &lt;raghavendra.rao&lt; at &gt;enterprisedb.com&gt;: no, this was prepared for 8.4. These features was just experiment and only smaller subset is in core now. Regards Pavel </pre> Pavel Stehule 2012-05-24T23:32:17 http://permalink.gmane.org/gmane.comp.db.postgresql.general/162999 <pre>We have lots of partition tables that inherit from a smaller number of parents.  Some, but not all of these tables also have indexes. The number actually varies depending on the data loaded.  For some other database instances, fortunately on Linux, the number is in the millions. I have been testing with passing FindFirstFile a pattern to match the temporary file names, rather than letting FindFirstFile/FindNextFile return all names and then having postgres do the pattern match itself.  So far, this looks very promising, with a stand-alone program that uses this technique cutting the runtime from 4 minutes down to less than a second. I have a fairly clean patch in the works that I will submit after I have verified it on Windows 2003, Windows 2008 and Linux. ________________________________ From: Magnus Hagander &lt;magnus&lt; at &gt;hagander.net&gt; To: Mark Dilger &lt;markdilger&lt; at &gt;yahoo.com&gt; Cc: Tom Lane &lt;tgl&lt; at &gt;sss.pgh.pa.us&gt;; deepak &lt;deepak.pn&lt; at &gt;gmail.com&gt;; Alban Hertroys &lt;haramrae&lt; at &gt;gmail.com&gt;; "pgsql-general&lt; at &gt;postgresql</pre> Mark Dilger 2012-05-24T21:40:06 http://permalink.gmane.org/gmane.comp.db.postgresql.general/162998 <pre> In case it helps, this systemtap run will report on the top few time-sampled call graphs of post* processes: # debuginfo-install -y postgresql # stap -V [...Fedora16...] Systemtap translator/driver (version 1.7/0.153 non-git sources) # cat post-prof.stp probe timer.profile { if (substr(execname(),0,4) != "post") next bt[sprint_ustack(ubacktrace())] &lt;&lt;&lt; 1 } probe end { foreach ([s] in bt- limit 50) { println("\n",&lt; at &gt;count(bt[s]),":",s) } } global bt[20000] # stap post-prof.stp -v -d /usr/bin/postgres -d /usr/bin/postmaster --ldd --all-modules --suppress-handler-errors [wait awhile during workload] ^C 1390:index_getnext+0x1f9 [postgres] IndexNext+0x56 [postgres] ExecScan+0x14e [postgres] ExecProcNode+0x228 [postgres] ExecLimit+0xb8 [postgres] ExecProcNode+0xd8 [postgres] standard_ExecutorRun+0x14a [postgres] PortalRunSelect+0x287 [postgres] PortalRun+0x248 [postgres] PostgresMain+0x754 [postgres] ServerLoop+0x799 [postgres] PostmasterMain+0x647 [postgres] main+0x71e [postgres] __libc_start_main+0</pre> Frank Ch. Eigler 2012-05-24T21:20:36 http://permalink.gmane.org/gmane.comp.db.postgresql.general/162997 <pre> Nah, just match up the files it touches with pg_class.relfilenode. regards, tom lane </pre> Tom Lane 2012-05-24T19:57:48 http://permalink.gmane.org/gmane.comp.db.postgresql.general/162996 <pre> Is there something specific I should be looking for in the strace output, or is this just a matter of correlating PID and FD to pg_class.relfilenode ? </pre> Lonni J Friedman 2012-05-24T19:37:37 http://permalink.gmane.org/gmane.comp.db.postgresql.general/162995 <pre> Can you correlate the performance hit with any specific part of autovacuum? In particular, I'm wondering if it matters whether vacuum is cleaning tables or indexes --- it alternates between the two, and the access patterns are a bit different. You could probably watch what the autovac process is doing with strace to see what it's accessing. regards, tom lane </pre> Tom Lane 2012-05-24T19:34:13 http://permalink.gmane.org/gmane.comp.db.postgresql.general/162994 <pre>On Wed, May 23, 2012 at 2:45 PM, Gavin Flower &lt;GavinFlower&lt; at &gt;archidevsys.co.nz&gt; wrote: No, not lots of subqueries or ORDERing, and most queries only touch a single table. However, I'm honestly not sure that I'm following where you're going with this. The problem isn't triggered by explicit queries. I can disable all external access, and simply wait for autovacuum to kick off, and the box starts to die. </pre> Lonni J Friedman 2012-05-24T19:28:14 http://permalink.gmane.org/gmane.comp.db.postgresql.general/162993 <pre> Thank you. And really a great patch to psql console lover's... :) http://postgres.cz/wiki/Enhanced-psql Is this also compatible to 9.1 ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ </pre> Raghavendra 2012-05-24T16:40:03 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.postgresql.general