Re: [HACKERS] machine-readable explain output
On Thursday 18 June 2009 06:01:13 to...@tuxteam.de wrote: > One of the common pitfalls of XML is that designers think first in terms > of the XML representation before being clear on the abstract structure > of what they want to represent The other aspect is that designing a useful XML format is pretty much hopeless without a clear idea about what processing is expected. Look at HTML and DocBook. Both of those might have been a result of a community effort to "design an XML format to publish text", but they clearly have different processing expectations. And there is a whole infrastructure around each of them to make that work (specifications, DTDs, documented processing expectations, CSS, stylesheets, etc.). Also note that neither HTML nor DocBook are for example designed so that a renderer can do something useful with elements that it doesn't know about. The current approach to the XML explain format is an attempt to be quite literally everything to everyone. The only concrete processing target that has been vaguely described is some kind of presumably visual display in pgAdmin. The simple element-per-plan-node approach could handle that just fine, for example. (The other requirements that have been mentioned are that it should be similar to the JSON format and it should read nicely, which have some merit, but aren't really going to help what I'm talking about here.) Also note that for example DocBook and HTML have versions 1, 2, 3, 4, 5. So I suggest that we do it that way: design something small for concrete applications, extend it later, but don't expect applications targeting the old versions to magically be able to process the new versions with full power. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GRANT ON ALL IN schema
On Wednesday 17 June 2009 20:27:20 Robert Haas wrote: > What to do about wildcards is a stickier wicket, and maybe we need to > decide that first, but I really don't think we should be discouraging > anyone from investigating this stuff and trying to come up with good > solutions. There will always be some people for whom a custom > PL/pgsql function that directly accesses the catalog tables is the > only workable answer, but we can make PostgreSQL a whole lot easier to > use by reducing the need to do that for simple cases. I'm all for investigating it. I just have my doubts that "grant on all tables in schema X" is a sufficiently general use case, even if you only concentrate on the simple cases. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, 2009-06-16 at 00:24 -0400, Robert Haas wrote: > There's not much point taking the length of the word Not sure why we need to be calculating the length here anyway. ISTM that there is no need to reconfirm the length of the data, since it is already checked to be that length at insert. Why is bcTruelen being called so many *more* times? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] typos in source comment
Hi, Here're the patches to fix typos in source comment. typo_postgres.patch should be backported to v8.2 and v8.3. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center typo_pgstandby.patch Description: Binary data typo_postgres.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GRANT ON ALL IN schema
--On Mittwoch, Juni 17, 2009 16:44:53 +0300 Peter Eisentraut wrote: I think you should design this with a bit wider scope. Instead of just "all tables in this schema", think "all tables satisfying some condition". It has been requested, for example, to be able to grant on all tables that match a pattern. My experience shows that having such a thing is often leading to "bad practices". People tend to grant everything to every login role instead of using an intelligent role privilege mechanism. MySQL for example has such wildcards (using '_' and '%' wildcard patterns), which often confuses people when having such characters in their table/database names (of course, i forgot to escape them more than once). The unpredictable results of messing up a complete schema when using a broken pattern expression is going to reduce the usefulness of such a feature, i think. Also since this patch introduces VIEWS as object with grantable privileges, I added GRANT ON VIEW foo syntax which is more or less synonymous to GRANT ON TABLE foo syntax. It felt weird to have GRANT ON ALL VIEWS but not GRANT ON VIEW. As far as GRANT is concerned, a view is a table, so I would omit the VIEW/VIEWS stuff completely. We have ALTER VIEW now, so why don't implement the same synonym for GRANT? -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] typos in source comment
Fujii Masao wrote: Here're the patches to fix typos in source comment. typo_postgres.patch should be backported to v8.2 and v8.3. Thanks, applied. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, 2009-06-16 at 10:23 -0400, Tom Lane wrote: > Speaking of which, what about some performance numbers? Like Heikki, > I'm quite suspicious of whether there is any real-world gain to be had > from this approach. It has been "lore" for some time that VARCHAR is cheaper than VARCHAR(n), so I'm looking forward to this improvement as a real-world gain. (If done right). I've looked at the code and the thing that bothers me is that I can't see where or why bcTruelen would be called more often for VARCHAR(n) than it would be for VARCHAR, on a Select/Sort only workload. Are we tuning the right thing? Is there some code we can completely avoid? If not, does this mean it is a generic effect? Does this imply that NUMERIC(n) is somehow worse than NUMERIC? etc.. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On 6/18/09, Simon Riggs wrote: > On Tue, 2009-06-16 at 10:23 -0400, Tom Lane wrote: > > Speaking of which, what about some performance numbers? Like Heikki, > > I'm quite suspicious of whether there is any real-world gain to be had > > from this approach. > > > It has been "lore" for some time that VARCHAR is cheaper than > VARCHAR(n), so I'm looking forward to this improvement as a real-world > gain. (If done right). > > I've looked at the code and the thing that bothers me is that I can't > see where or why bcTruelen would be called more often for VARCHAR(n) > than it would be for VARCHAR, on a Select/Sort only workload. I'd guess plain VARCHAR simply does not have blanks at the end, so Truelen is cheap. > Are we tuning the right thing? Is there some code we can completely > avoid? > > If not, does this mean it is a generic effect? Does this imply that > NUMERIC(n) is somehow worse than NUMERIC? etc.. Probably not. For numeric the (n) seems to be only checked input time. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Thu, 2009-06-18 at 15:14 +0300, Marko Kreen wrote: > On 6/18/09, Simon Riggs wrote: > > On Tue, 2009-06-16 at 10:23 -0400, Tom Lane wrote: > > > Speaking of which, what about some performance numbers? Like Heikki, > > > I'm quite suspicious of whether there is any real-world gain to be had > > > from this approach. > > > > > > It has been "lore" for some time that VARCHAR is cheaper than > > VARCHAR(n), so I'm looking forward to this improvement as a real-world > > gain. (If done right). > > > > I've looked at the code and the thing that bothers me is that I can't > > see where or why bcTruelen would be called more often for VARCHAR(n) > > than it would be for VARCHAR, on a Select/Sort only workload. > > I'd guess plain VARCHAR simply does not have blanks at the end, > so Truelen is cheap. If we were comparing CHAR(n) with VARCHAR then I could agree. But we are comparing VARCHAR(n) and VARCHAR. There is no blank padding with VARCHAR, and the two have identical on-disk representations so the cost of bcTruelen looks like it should be identical in each case. Is bcTruelen being called too many times, or is there, as Marko suggests, an explanation as to why calling bcTruelen costs more when we have a typmod set? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Simon Riggs wrote: On Thu, 2009-06-18 at 15:14 +0300, Marko Kreen wrote: On 6/18/09, Simon Riggs wrote: On Tue, 2009-06-16 at 10:23 -0400, Tom Lane wrote: > Speaking of which, what about some performance numbers? Like Heikki, > I'm quite suspicious of whether there is any real-world gain to be had > from this approach. It has been "lore" for some time that VARCHAR is cheaper than VARCHAR(n), so I'm looking forward to this improvement as a real-world gain. (If done right). I've looked at the code and the thing that bothers me is that I can't see where or why bcTruelen would be called more often for VARCHAR(n) than it would be for VARCHAR, on a Select/Sort only workload. I'd guess plain VARCHAR simply does not have blanks at the end, so Truelen is cheap. If we were comparing CHAR(n) with VARCHAR then I could agree. But we are comparing VARCHAR(n) and VARCHAR. There is no blank padding with VARCHAR, and the two have identical on-disk representations so the cost of bcTruelen looks like it should be identical in each case. the testcase discusses here is indeed CHAR(n) vs. VARCHAR. To reiterate - my numbers(8core/16 thread Nehalem xeon with 16 processes) are 46000qps for CHAR(n), 52000 qps for CHAR(n) with Jeremys patch(thout bcTrueLen is still on top in the profile) and 67000 qps for VARCHAR. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Thu, 2009-06-18 at 15:09 +0200, Stefan Kaltenbrunner wrote: > the testcase discusses here is indeed CHAR(n) vs. VARCHAR. OK, thanks for pointing out my error. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
Pavel Golub writes: > [ proposal involving ] > SWITCH TRANSACTION first; -- switch context > So, what do you think guys? No chance :-(. The amount of work that would be required is *vastly* out of proportion to any possible benefit. Use multiple connections. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator mention in documentation
On Saturday 06 June 2009 16:21:22 Bruce Momjian wrote: > Bruce Momjian wrote: > > Now that pg_migrator is in beta > > (http://pgfoundry.org/frs/?group_id=1000235), I was wondering if we want > > to mention pg_migrator anywhere in our docs or release notes? Josh > > Berkus is already mentioning it in the draft press release. > > Having gotten no replies I assume we don't want to mention pg_migrator > in the release notes or documentation, which is fine. It is mentioned > in the press release. Will you eventually want to ship pg_migrator in contrib? If so, then documentation will be added when that happens. If not, we should add a link somewhere where the upgrade process is described (installation instructions, I think). The makefile for pg_migrator currently assumes by default that it is located under contrib/. Which confuses me. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
* Simon Riggs: > On Thu, 2009-06-18 at 15:09 +0200, Stefan Kaltenbrunner wrote: > >> the testcase discusses here is indeed CHAR(n) vs. VARCHAR. > > OK, thanks for pointing out my error. But I think your point still makes sense. Is it really necessary to determine the unpadded length for a query such as this one? "SELECT c from sbtest where id between $1 and $2 order by c" (See the start of the thread.) -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Simon Riggs writes: > Why is bcTruelen being called so many *more* times? I think you have misunderstood the context. The char(n) code is defined to store trailing blanks (up to n) but to disregard the trailing blanks during comparisons. bcTrueLen is invoked during comparisons (not during storage) to figure out what the "valid" string length is for comparing. varchar considers any trailing blanks to be real, comparable data, so it simply hasn't got any equivalent code. It would be way nicer if we could strip trailing blanks on storage, and then figure a way to either reconstitute them on output (problem here is the output function doesn't have access to typmod) or language-lawyer our way to deciding we don't have to. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Tom Lane wrote: > It would be way nicer if we could strip trailing blanks on storage, > and then figure a way to either reconstitute them on output How about pushing it even farther back -- always keep them with trimmed trailing spaces and add trailing spaces as required in operator functions? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Thu, 2009-06-18 at 09:59 -0400, Tom Lane wrote: > Simon Riggs writes: > > Why is bcTruelen being called so many *more* times? > > I think you have misunderstood the context. err, no, I just misread the original text. Possibly a worse error :-? > It would be way nicer if we could strip trailing blanks on storage, > and then figure a way to either reconstitute them on output (problem > here is the output function doesn't have access to typmod) or > language-lawyer our way to deciding we don't have to. Is there a spare bit on the varhdr that can be set by datatypes? It would be useful to have a bit meaning "there is a typmod stored on this Datum", that would allow the output function to do some special processing. I notice we lose on tuple access also. CHAR(n) is fixed length, but is treated as variable length for offsets. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release notes "Recursive Joins"
On Sunday 14 June 2009 23:31:48 Erik Rijkers wrote: > http://www.postgresql.org/docs/8.4/static/release-8-4.html > > In the current 8.4rc1 release notes, it says (point 2): > > "Common Table Expressions and Recursive Joins" > > > It would be better to replace this by: > > "Common Table Expressions and Recursive Queries" > > > If only because the phrase "Recursive Join" is not used in the postgres > docs, nor is it in the SQL 2008 standard docs. Fixed. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] rc tarball built with older flex version?
I noticed that the rc1 tarball includes scanner files that are built with an older flex version that generates warnings with our default compilation flags. Since I have been running with -Werror by default for a great while now, this caught my attention while testing the tarball. Are we tracking this or are we just using whatever was installed on the host that created the snapshot? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] rc1 tarball contains partially outdated/missing man pages
I noticed that the rc1 tarball does not contain man pages for CREATE/ALTER/DROP FOREIGN DATA WRAPPER/SERVER/USER MAPPING. But it does contain man pages for TABLE and WITH, so it appears to be 8.4 material. Can someone check, or remind me how the man pages end up in the tarball? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Simon Riggs escribió: > I notice we lose on tuple access also. CHAR(n) is fixed length, but is > treated as variable length for offsets. Fixed character length != fixed byte length -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Uninstallation error
On Tuesday 16 June 2009 18:16:58 genie.j...@gmail.com wrote: > Hi, > > I've found the uninstallation error... > > > # make uninstall > > > > n/man7/truncate.7 /usr/local/pgsql/share/man/man7/unlisten.7 > /usr/local/pgsql/share/man/man7/update.7 > /usr/local/pgsql/share/man/man7/vacuum.7 > /usr/local/pgsql/share/man/man7/values.7 > rm: cannot remove `/usr/local/pgsql/share/man/man1/': Is a directory > rm: cannot remove `/usr/local/pgsql/share/man/man7/': Is a directory > make[1]: *** [uninstall] Error 1 > make[1]: Leaving directory `/usr/local/src/postgresql-8.4rc1/doc' > make: *** [uninstall] Error 2 > > > Maybe, it is solved by the change in the following. (adding -r option) > > doc/Makefile > 100c100 > < rm -f $(addprefix $(DESTDIR)$(mandir)/, $(shell gunzip -c > $(srcdir)/man.tar.gz | tar tf - | sed -e 's,man7/,man$(sqlmansectnum)/,' -e > 's/.7$$/.$(sqlmansect)/')) > --- > > > rm -rf $(addprefix $(DESTDIR)$(mandir)/, $(shell gunzip -c > > $(srcdir)/man.tar.gz | tar tf - | sed -e 's,man7/,man$(sqlmansectnum)/,' -e > 's/.7$$/.$(sqlmansect)/')) I have fixed this problem. Next time, please start a new thread for a new issue, so your issue doesn't get lost. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Uninstallation error
Peter Eisentraut escribió: > Next time, please start a new thread for a new issue, so your issue doesn't > get lost. Gmail has gotten the idiotic idea that changing the subject makes the message appear as a new thread :-( -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Uninstallation error
> I have fixed this problem. Thanks a lot for the fix ! >> Next time, please start a new thread for a new issue, so your issue doesn't >> get lost. > Gmail has gotten the idiotic idea that changing the subject makes the > message appear as a new thread :-( Sorry. This is the first posting in hackers-ML for me. I don't know why this issue occurs. Gmail is a little funny :~(~~~ Regards, Genie Japo -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
"Kevin Grittner" writes: > Tom Lane wrote: >> It would be way nicer if we could strip trailing blanks on storage, >> and then figure a way to either reconstitute them on output > How about pushing it even farther back -- always keep them with > trimmed trailing spaces and add trailing spaces as required in > operator functions? I think that's what I said. AFAIK there isn't any place where we'd need to add back the spaces except the output function. All the operators would be just as happy if the spaces weren't there. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator mention in documentation
On Sat, 6 Jun 2009, Bruce Momjian wrote: Having gotten no replies I assume we don't want to mention pg_migrator in the release notes or documentation, which is fine. I know this project has resistance to putting URL links in the documentation. One option I was thinking about was creating some dedicated pages on the Wiki to hold resource pages for things like pg_migrator, which are extremely useful but are still in flux at the time when the release goes out. We could create a "Version Upgrade Resources" page, link to pg_migrator there, and include links to other related resources there too. The documentation could just say "There are external tools available to upgrade between versions in some circumstances without a dump and reload, see http://wiki.postgresql.org/wiki/Version_Upgrade_Resources for more information". That seems like a much more sustainable and permanent way to handle tools like this than directly linking to a project page for them, and gives capabilities like locking the page in order to prevent vandalism. Two other areas which I'd like to see "packaged" formally for reference in the docs like this are a page listing replication tools and one listing postgresql.conf tuning resources. Some of the known big problems with PosgreSQL have useful solutions, just not integrated ones yet, and we really should make it easier to popularize linking to those external tools in a controlled way. I'd be happy to create proposed pages for each of these topics on the wiki if this idea seems reasonable to the community, there are rough articles there already for all three ideas I can pull from. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] rc1 tarball contains partially outdated/missing man pages
Peter Eisentraut writes: > Can someone check, or remind me how the man pages end up in the tarball? They're supposed to be built on the fly, and the file dates in the rc1 tarball do appear to match the time of tarball building. Do you get what you expect if you build the man pages locally? One thing I notice is that the "table" and "with" entries are not coming out as intended. The file names are all caps: -rw-r--r-- 1 tglusers 18 Jun 12 01:37 WITH.7 -rw-r--r-- 1 tglusers 18 Jun 12 01:37 TABLE.7 and the content surely isn't what was meant: $ more TABLE.7 .so man7/SELECT.7 $ more WITH.7 .so man7/SELECT.7 regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Tom Lane wrote: > "Kevin Grittner" writes: >> Tom Lane wrote: >>> It would be way nicer if we could strip trailing blanks on >>> storage, and then figure a way to either reconstitute them on >>> output > >> How about pushing it even farther back -- always keep them with >> trimmed trailing spaces and add trailing spaces as required in >> operator functions? > > I think that's what I said. AFAIK there isn't any place where we'd > need to add back the spaces except the output function. All the > operators would be just as happy if the spaces weren't there. Sorry, I misread it. If this can be done, it might ease the transition from other products for some people. In Sybase there were significant performance benefits to using char() instead of varchar() in certain circumstances. In our first test conversion from Sybase we mapped all domains and columns which were char(n) in Sybase to the same in PostgreSQL, which caused some pain. It was no big deal to remap those to varchar() and re-run, but on the face of it, it seems like there wouldn't be a significant performance hit for char() with this change, so it would be one less thing for people to stub their toes on during migration. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] rc tarball built with older flex version?
Peter Eisentraut writes: > I noticed that the rc1 tarball includes scanner files that are built with an > older flex version that generates warnings with our default compilation > flags. > Since I have been running with -Werror by default for a great while now, this > caught my attention while testing the tarball. Are we tracking this or are > we > just using whatever was installed on the host that created the snapshot? It's whatever is installed on svr1, but we don't change that often, and I'm particularly not inclined to change it post-RC. We don't recommend that people use -Werror to build, so I think we should just write this off as "not a bug". regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named transaction
On 06/18/2009 02:42 AM, Pavel Golub wrote: Now to implement customer's desire in PostgreSQL there are two ways: 1. Each script must be executed in the separate connection context 2. Each script must be executed inside critical section, in other words current scipt must block others until COMMIT or ROLLBACK I don't like both. What don't you like about 1) ? I don't know of any other databases that work this way. Using separate connections and connection pooling seems to be "the way to go" here. Personally, I found the "named transaction" concept a little skrewy unless: 1) SQL commands can be sent asynchronously as long as they are for different named transactions, even while other transactions are still running. 2) Each transaction runs in a different server-side thread. If this is what you want, it sounds like you are just trying to multiplex multiple queries and responses over the same TCP/IP connection. For the added complexity on both the client and the server, do you really think it is worth it? If you just want a connection multiplexor that is backed by a connection pool - I think that would be a lot easier to provide. :-) Cheers, mark -- Mark Mielke -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] rc1 tarball contains partially outdated/missing man pages
Peter Eisentraut writes: > I noticed that the rc1 tarball does not contain man pages for > CREATE/ALTER/DROP FOREIGN DATA WRAPPER/SERVER/USER MAPPING. Just eyeballing the files, I notice that those ref pages seem not to contain this boilerplate: 7 which is mostly(?) present in others. Maybe the cause? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] rc1 tarball contains partially outdated/missing man pages
I wrote: > One thing I notice is that the "table" and "with" entries are not coming > out as intended. The file names are all caps: > -rw-r--r-- 1 tglusers 18 Jun 12 01:37 WITH.7 > -rw-r--r-- 1 tglusers 18 Jun 12 01:37 TABLE.7 > and the content surely isn't what was meant: > $ more TABLE.7 > .so man7/SELECT.7 > $ more WITH.7 > .so man7/SELECT.7 BTW, as far as that particular point goes: maybe we could fix the tools issues underlying this, but I'm tempted to think that it's not worth the trouble, because making these man pages be aliases for SELECT is just the Wrong Thing anyway. I think we should just split them off and have them be documented as separate top-level reference pages. Doing that for TABLE is clearly fairly sensible; and as for WITH, the current approach is going to fail miserably anyhow as soon as we allow WITH to be prefixed to INSERT/UPDATE/DELETE. Which is something that was already requested, and I see no semantic or implementation reason not to allow (in 8.5 of course, not now). So I'm thinking we make a man page for WITH as such, and cross-reference it from SELECT, moving the current discussion of CTEs over. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 8.4 open item: copy performance regression?
Any objections if I add: http://archives.postgresql.org/pgsql-performance/2009-06/msg00215.php to the (currently empty) list of open items for 8.4? A 25-30% performance regression in our main bulk loading mechanism should at least be explained before the release... Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Thu, 2009-06-18 at 12:58 -0400, Tom Lane wrote: > "Kevin Grittner" writes: > > Tom Lane wrote: > >> It would be way nicer if we could strip trailing blanks on storage, > >> and then figure a way to either reconstitute them on output > > > How about pushing it even farther back -- always keep them with > > trimmed trailing spaces and add trailing spaces as required in > > operator functions? > > I think that's what I said. AFAIK there isn't any place where we'd > need to add back the spaces except the output function. All the > operators would be just as happy if the spaces weren't there. The overall problem is that we expect the Datum's of a datatype to know how to display themselves without any access to metadata. Another way of looking at this might be that we need a default FORMAT specifier associated with a column. Teradata used the FORMAT specifier on a column to allow you to specify a default format. That allowed you to specify leading/trailing zeros/spaces, decimal points and other characters. It would be good to have a generic way to specify formatting to a data type at the point you are manipulating it, rather than globally. That would be helpful because we currently have some very ugly things like the DateStyle parameter. That effects the Input and Output of data into Time/Date columns, forcing you to switch parameter values constantly as you load/select data. The current way makes it impossible to load data that has different date formats in different columns, for example. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 open item: copy performance regression?
Stefan Kaltenbrunner writes: > Any objections if I add: > http://archives.postgresql.org/pgsql-performance/2009-06/msg00215.php > to the (currently empty) list of open items for 8.4? I am unable to duplicate any slowdown on this test case. AFAICT 8.4 and 8.3 branch tip are about the same speed; if anything 8.4 is faster. Testing on x86_64 Fedora 10 ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Determining client_encoding from client locale
Tom Lane wrote: > Itagaki Takahiro writes: > > Peter Eisentraut wrote: > >> On Wednesday 17 June 2009 14:29:26 Heikki Linnakangas wrote: > >>> We currently require that you set client_encoding correctly, or you get > >>> garbage in psql and any other tool using libpq. How about setting > >>> client_encoding automatically to match the client's locale? We have > >>> pg_get_encoding_from_locale() function that we can use to extract the > >>> encoding from LC_CTYPE. We could call that in libpq. > > > +1 for psql, but -1 for libpq. > > What would make sense to me is for libpq to provide the *code* for this, > but then leave it up to the client application whether to actually call > it; if not the behavior stays the same as before. Aside from > Itagaki-san's objections, that eliminates backwards-compatibility issues > for other applications. Added to TODO: Add code to detect client encoding and locale from the operating system environment * http://archives.postgresql.org/pgsql-hackers/2009-06/msg01040.php -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Determining client_encoding from client locale
Bruce Momjian writes: > Tom Lane wrote: >> What would make sense to me is for libpq to provide the *code* for this, >> but then leave it up to the client application whether to actually call >> it; if not the behavior stays the same as before. Aside from >> Itagaki-san's objections, that eliminates backwards-compatibility issues >> for other applications. > Added to TODO: BTW, something that occurred to me later is that the details of this could easily be got wrong. If libpq is indeed told to get client_encoding from the client environment, it should arrange to do so *before* opening the connection, and send the encoding request as part of the startup packet. The alternative of providing a function to adjust the encoding for an already-opened connection is inferior for a couple of reasons: * extra network round trip required * we lose any chance at ensuring that connection failure messages come back in the client's desired encoding. (The latter business was already discussed a bit IIRC, but I'm too lazy to check the archives right now.) So that means that the API for this should probably involve some addition to the PQconnectdb parameter string, not a separate function. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator mention in documentation
Peter Eisentraut wrote: > On Saturday 06 June 2009 16:21:22 Bruce Momjian wrote: > > Bruce Momjian wrote: > > > Now that pg_migrator is in beta > > > (http://pgfoundry.org/frs/?group_id=1000235), I was wondering if we want > > > to mention pg_migrator anywhere in our docs or release notes? Josh > > > Berkus is already mentioning it in the draft press release. > > > > Having gotten no replies I assume we don't want to mention pg_migrator > > in the release notes or documentation, which is fine. It is mentioned > > in the press release. > > Will you eventually want to ship pg_migrator in contrib? If so, then > documentation will be added when that happens. It is really up to the community, not me, what they want to do with the code. > If not, we should add a link somewhere where the upgrade process is described > (installation instructions, I think). I think the question is what do we want to do for 8.4, if anything, and then we can figure out what to do for later releases. > The makefile for pg_migrator currently assumes by default that it is located > under contrib/. Which confuses me. You can compile pg_migrator by copying it to /contrib, or using PGXS; both work. Read the 15-step install instructions for details: (7) Build pg_migrator For pg_migrator source installs, keep in mind the compile must use the _new_ PostgreSQL source directory and be installed in the new Postgres install directory. The simplest build option is to point to the top of the PostgreSQL source tree by running something like: gmake top_builddir=/usr/src/pgsql install Replace '/usr/src/pgsql' with your source directory. pg_migrator also understands the 'prefix=' specification if you installed Postgres in a custom location. Another build option is to copy the pg_migrator directory into contrib/pg_migrator in the new PostgreSQL source tree and run a simple 'gmake install'. A third install method is to use PGXS (assuming the new 'pg_config' is in your $PATH): USE_PGXS=1 gmake prefix=/usr/local/pgsql.new install -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 open item: copy performance regression?
Tom Lane wrote: > Stefan Kaltenbrunner writes: >> Any objections if I add: >> http://archives.postgresql.org/pgsql-performance/2009-06/msg00215.php >> to the (currently empty) list of open items for 8.4? > > I am unable to duplicate any slowdown on this test case. AFAICT > 8.4 and 8.3 branch tip are about the same speed; if anything 8.4 > is faster. Testing on x86_64 Fedora 10 ... I just ran the specified test on: Linux ATHENA 2.6.16.60-0.31-smp #1 SMP Tue Oct 7 16:16:29 UTC 2008 x86_64 x86_64 x86_64 GNU/Linux Linux version 2.6.16.60-0.31-smp (ge...@buildhost) (gcc version 4.1.2 20070115 (SUSE Linux)) #1 SMP Tue Oct 7 16:16:29 UTC 2008 SUSE Linux Enterprise Server 10 (x86_64) VERSION = 10 PATCHLEVEL = 2 I did configure with a prefix and these options: --enable-integer-datetimes --enable-debug --disable-nls --with-libxml I did initdb with --no-locale 8.3.7 real0m24.249s real0m24.054s real0m24.361s 8.4rc1 real0m33.503s real0m34.198s real0m33.931s -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 open item: copy performance regression?
"Kevin Grittner" writes: > Tom Lane wrote: >> I am unable to duplicate any slowdown on this test case. > [ Kevin can ] It'd be useful first off to figure out if it's a CPU or I/O issue. Is there any visible difference in vmstat output? Also, try turning off autovacuum in both cases, just to see if that's related. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Thu, Jun 18, 2009 at 10:03 PM, Simon Riggs wrote: > > The overall problem is that we expect the Datum's of a datatype to know > how to display themselves without any access to metadata. Yes > Another way of looking at this might be that we need a default FORMAT > specifier associated with a column. Teradata used the FORMAT specifier > on a column to allow you to specify a default format. That allowed you > to specify leading/trailing zeros/spaces, decimal points and other > characters. I've suggested this in the past in the context of getting rid of the "cash" data type. However I'm not sure it solves this problem. In the case where we're just outputing a column we could arrange to have the typmod pretty easily. but what would you do if you had some complex expression which happened to result in a char(). Say something like: coalesce(nickname, name) where both nickname and name are, say, char(50). Or for that matter where they're two different length chars, like char(50) and char(100). We could add an integer prefix to the char() datatype with the "total" length and then just not include the spaces. But that would make it not binary compatible with text -- which would mean implementing a whole bunch of casts and operators. Perhaps. I think we already have rtrim as the cast in one direction and I think we already have operators which is the whole trigger for the bcTrueLen thing which started this thread. maybe it wouldn't really be that much of a pain. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 open item: copy performance regression?
Tom Lane wrote: > It'd be useful first off to figure out if it's a CPU or I/O issue. > Is there any visible difference in vmstat output? Also, try turning > off autovacuum in both cases, just to see if that's related. Both took slightly longer with autovacuum off, but probably just within the noise. It's hard to interpret the vmstat output, largely because I chose to run this on one of our biggest servers, which is not currently serving an application, per se, but as a replication target, and this being "off hours" is busy running the sync process to the source machines. This involves generating md5 sums on both sides for blocks of rows, which is pretty CPU-intensive. There is very little disk output from that right now, pretty light on the disk reads, but keeping a few CPUs pretty busy generating those md5 sums. I've got to go keep an appointment, but I'll come back and see if I can do more. For now, here's the raw vmstat, in case someone can pick out info I'm missing: -Kevin baseline: procs ---memory-- ---swap-- -io -system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 3 1240 1456928 70164 12435073600 10167800 6 1 89 4 0 4 0240 1451224 70164 12435689600 558047 5457 39514 15 3 74 8 0 2 1240 1445812 70172 12436100800 4988 543 5347 36680 15 3 73 9 0 3 1240 1440196 70172 12436614400 5928 501 3431 30250 14 3 76 8 0 3 1240 143 70172 12437231200 592047 6477 34794 16 2 73 9 0 1 3240 1428916 70172 12437642400 5268 468 5449 33003 15 2 73 10 0 1 1240 1422820 70172 12438259200 5036 107 3995 26131 12 2 75 11 0 1 3240 1418556 70172 12438772800 4052 156 4562 33519 12 2 78 8 0 1 3240 1412972 70180 12439286400 5544 676 5063 32300 14 2 73 11 0 1 3240 1406964 70180 12439903200 5796 119 4743 28285 12 2 74 13 0 0 3240 1400992 70180 12440417600 6112 116 4184 15183 9 1 75 15 0 4 0240 1396372 70180 12441033600 4604 119 4123 38639 14 2 74 10 0 3 1240 1391936 70180 12441651200 4572 332 5025 38534 15 3 73 9 0 2 3240 1387324 70188 12441856000 4796 397 4271 23039 11 2 80 8 0 2 1240 1380196 70188 12442575200 6864 209 5583 32509 16 2 73 9 0 1 3240 1374980 70188 12443294400 529252 5007 37758 15 2 74 9 0 0 0240 1372708 70188 12443500800 2600 132 3363 18199 9 1 86 4 0 2 2240 1367488 70188 12443912000 5152 430 5450 42153 18 3 74 6 0 8.3.7 test: procs ---memory-- ---swap-- -io -system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 0 0240 590064 70164 12521939200 10167800 6 1 89 4 0 4 1240 586336 70164 12522144800 362837 4990 39525 15 3 77 5 0 2 0240 581892 70164 12522659200 455650 5597 33057 16 2 76 5 0 3 1240 578616 70164 12522967200 350042 3902 36959 14 3 80 4 0 3 1240 573520 70164 12523481600 523282 5081 26713 15 2 75 8 0 1 3240 591688 70164 12521733600 4612 15890 5256 33309 13 3 76 8 0 3 1240 586944 70172 12522041600 4596 100 4640 23263 12 2 77 9 0 3 1240 595632 70160 12521528000 5512 5814 5314 26822 16 2 73 8 0 1 2240 581200 70152 12522968000 4684 39061 3604 26682 16 3 67 14 0 4 1240 596972 70152 12521118400 1508 53915 2729 20654 9 3 83 5 0 2 4240 589184 70152 12522043200 4140 44877 3809 82485 12 4 69 16 0 1 4240 597620 70116 12520916000 5744 54096 4597 26422 14 3 66 17 0 1 4240 585936 70116 12521944000 4136 44584 3661 35574 18 3 68 11 0 0 5240 601460 70096 12520300800 4968 54009 4669 26507 12 3 72 14 0 1 4240 592604 70080 12521022400 5064 44674 4190 40394 14 3 71 12 0 2 3240 604844 70076 12519686400 5528 49601 4664 39189 18 4 68 10 0 1 4240 590348 70072 12521434400 7716 49397 4949 41086 18 3 67 11 0 1 4240 600036 70060 12520099200 4152 53968 4641 31875 16 3 66 15 0 1 4240 587336 70052 12521230400 6420 44620 4427 47627 16 4 67 13 0 3 2240 582508 70044 12521540000 4524 32812 4510 256103 18 9 60 13 0 5 1240 603680 70020 12519280800 6584 33341 3844 28189 16 3 68 13 0 4 1240 591464 70016 12520617600 6180 54710 6664 37282 20 3 64 13 0 5 1240 586924 70020 12521233600 5748 44552 5351 39772 18 3 67 11 0 4 1240 602376 70004 12519385600 5840 54181 4824 34279 17 4 67 12 0 3 2
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Greg Stark writes: > We could add an integer prefix to the char() datatype > with the "total" length and then just not include the spaces. But that > would make it not binary compatible with text -- which would mean > implementing a whole bunch of casts and operators. Um, it's already not binary compatible with text. The space hit would be a bigger issue ... at least for cases where you don't save much of anything by eliminating the spaces, which would be exactly the cases where sane Postgres users use char(n) today. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 open item: copy performance regression?
On Thu, Jun 18, 2009 at 05:20:08PM -0400, Tom Lane wrote: > Stefan Kaltenbrunner writes: > > Any objections if I add: > > http://archives.postgresql.org/pgsql-performance/2009-06/msg00215.php > > to the (currently empty) list of open items for 8.4? > > I am unable to duplicate any slowdown on this test case. AFAICT > 8.4 and 8.3 branch tip are about the same speed; if anything 8.4 > is faster. Testing on x86_64 Fedora 10 ... > > regards, tom lane > What is not clear from Stefen's function listing is how the 8.4 server could issue 33% more XLogInsert() and CopyReadLine() calls than the 8.3.7 server using the same input file. That would account for the slow down but now why it is happening. Cheers, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 open item: copy performance regression?
"Kevin Grittner" wrote: > I've got to go keep an appointment Sorry about that. Back now. Anything else I can do to help with this? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 open item: copy performance regression?
Kenneth Marshall wrote: > What is not clear from Stefen's function listing is how the 8.4 > server could issue 33% more XLogInsert() and CopyReadLine() > calls than the 8.3.7 server using the same input file. I thought those were profiling numbers -- the number of times a timer checked what was executing and found it in that method. Which suggests that those two methods are probably slower now than in 8.3.7, at least in some environments. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] security checks for largeobjects?
The todo list says, as follows: * Binary Data o Add security checks for large objects http://wiki.postgresql.org/wiki/Todo#Binary_Data Is anyone working on? or interested in? Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
