[HACKERS] [trivial patch] Ellipsis whitespace in SQL docs
Hi,
here's a trivial patch:
Changed several occurrences of ,... to , ... in SQL syntax to be
consistent with the rest.
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml
b/doc/src/sgml/ref/alter_default_privileges.sgml
index e1aa293..c27466f 100644
*** a/doc/src/sgml/ref/alter_default_privileges.sgml
--- b/doc/src/sgml/ref/alter_default_privileges.sgml
*** ALTER DEFAULT PRIVILEGES
*** 29,40
phrasewhere replaceable
class=parameterabbreviated_grant_or_revoke/replaceable is one of:/phrase
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
! [,...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
! [,...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
TO { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
--- 29,40
phrasewhere replaceable
class=parameterabbreviated_grant_or_revoke/replaceable is one of:/phrase
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
! [, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
! [, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
TO { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
*** GRANT { EXECUTE | ALL [ PRIVILEGES ] }
*** 44,57
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
! [,...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
! [,...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
FROM { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 44,57
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
! [, ...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
! [, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
FROM { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index 082e2e8..72ecc45 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*** PostgreSQL documentation
*** 22,44
refsynopsisdiv
synopsis
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
! [,...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] replaceable class=PARAMETERtable_name/replaceable [,
...]
| ALL TABLES IN SCHEMA replaceable
class=PARAMETERschema_name/replaceable [, ...] }
TO { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( replaceable
class=PARAMETERcolumn/replaceable [, ...] )
! [,...] | ALL [ PRIVILEGES ] ( replaceable
class=PARAMETERcolumn/replaceable [, ...] ) }
ON [ TABLE ] replaceable class=PARAMETERtable_name/replaceable [,
...]
TO { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
! [,...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE replaceable class=PARAMETERsequence_name/replaceable
[, ...]
| ALL SEQUENCES IN SCHEMA replaceable
class=PARAMETERschema_name/replaceable [, ...] }
TO { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
! GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE replaceabledatabase_name/replaceable [, ...]
TO { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
--- 22,44
refsynopsisdiv
synopsis
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
! [, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] replaceable class=PARAMETERtable_name/replaceable [,
...]
| ALL TABLES IN SCHEMA replaceable
class=PARAMETERschema_name/replaceable [, ...] }
TO { [ GROUP ] replaceable class=PARAMETERrole_name/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( replaceable
class=PARAMETERcolumn/replaceable [, ...] )
! [, ...] | ALL [
Re: [HACKERS] [trivial patch] Ellipsis whitespace in SQL docs
On 29.11.2010 10:43, Christoph Berg wrote: here's a trivial patch: Changed several occurrences of ,... to , ... in SQL syntax to be consistent with the rest. 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] pg_execute_from_file review
Itagaki Takahiro itagaki.takah...@gmail.com writes: I have some comments and questions about pg_execute_from_file.v5.patch. Thanks for reviewing it! Source code * OID=3627 is used by another function. Don't you expect 3927? Yes indeed. It took me some time to understand what's happening here, and it seems to be a case of git branches management from me. Here's the patch as I worked on it (that's much easier to test against the full branch, that's extension), then as it got cherry-picked into the branch I use to produce the patches: http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=commitdiff;h=b406fe35e36e6823e18f7c3157bc330b40b130d4 http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=commitdiff;h=b04eda8f8ee05c3bb5f4d6b693c5169aa7c3b9d1 I missed including a part of the following patch into the pg_execute_from_file branch: http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=commitdiff;h=7b3fe8384fb7636130e50f03a338f36495e15030 Sorry about that, will get fixed in v6 — already fixed in the git branch. * There is a compiler warning: genfile.c: In function ‘pg_execute_from_file_with_placeholders’: genfile.c:427: warning: unused variable ‘query_string’ Fixed (in the git branches), sorry about that. * I'd like to ask native speakers whether from is needed in names of pg_execute_from_file and pg_execute_from_query_string. Fair enough, will wait for some comments before producing a v6. Design and Implementation * pg_execute_from_file() can execute any files even if they are not in $PGDATA. OTOH, we restrict pg_read_file() to read such files. What will be our policy? Note that the contents of file might be logged or returned to the client on errors. * Do we have any reasons to have pg_execute_from_file separated from pg_read_file ? If we allow pg_read_file() to read files in $PGSHARE, pg_execute_from_file could be replaced with EXECUTE pg_read_file(). (Note that pg_execute_from_file is implemented to do so even now.) pg_execute_from_file started as a very different animal, and I can see about it using pg_read_file() now, if we also change restrictions. Also, note that before using SPI an execute failure didn't ouput the whole input file. * I hope pg_execute_from_file (and pg_read_file) had an option to specify an character encoding of the file. Especially, SJIS is still used widely, but it is not a valid server encoding. What we agreed on doing in the extension's main patch was to change the client_encoding before to call into pg_execute_from_file(). So I'd hope that changing that client side just before to call pg_execute_from_file would be enough. Is it? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] profiling connection overhead
Robert Haas robertmh...@gmail.com writes: Well, the lack of extensible XLOG support is definitely a big handicap to building a *production* index AM as an add-on. But it's not such a handicap for development. Realistically, it's hard for me to imagine that anyone would go to the trouble of building it as a loadable module first and then converting it to part of core later on. That'd hardly be less work. Well, it depends a lot on external factors. Like for example willing to use the code before to spend the necessary QA time that is needed for it to land in core. Two particular examples come to mind, which are tsearch and KNN GiST. The main problem with integrating into core, AFAIUI, are related to code maitenance, not at all with code stability and quality of the addon itself. It's just so much easier to develop an external module… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] SSI using rw-conflict lists
On 28.11.2010 23:44, Kevin Grittner wrote: (1) Should these be moved to ShmemVariableCache, or is it OK to leave them in this structure as long as I comment it adequately? If they're only used by predicate.c, it seems better to leave them where they are. (2) Would it be a good idea to create macros for referencing these fields? The current references are long and distracting to read, and would all need to change if the fields are moved to a different shared memory structure. The relevant commit diff is here: http://git.postgresql.org/gitweb?p=users/kgrittn/postgres.git;a=commitdiff;h=bc3aba45e192afcd7776c68e28438991a3406db6 Maybe invent a short name for PredLockTranList and/or the fields? I'm not in favor of wrapping those references with macros, it can make it harder to understand the code, and unless the macro name is much shorter, it doesn't save all that much screen space anyway. There's another issue involving clarity of code, versus correct behavior. When I got to the last few false positives, I found that they could only be eliminated by tracking one more datum for a transaction committed with a conflict out to another transaction. There was a field which was unused in the particular situations where we needed this new datum, so I made it work with the dual use of the existing field. (3) What would be the cleanest way to conditionally store values representing different things in one field of a structure, so that someone reading the code understands what's happening? The commit diff for this is here: http://git.postgresql.org/gitweb?p=users/kgrittn/postgres.git;a=commitdiff;h=9425d7fa551a1433bdbec1de5cfb1bfa9f43da22 I'd suggest a union field. We've done that e.g. in HeapTupleFields in access/htup.h. -- 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] Re: [COMMITTERS] pgsql: Remove outdated comments from the regression test files.
On 28.11.2010 06:59, Robert Haas wrote: On Sat, Nov 27, 2010 at 3:46 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haasrobertmh...@gmail.com writes: On Nov 27, 2010, at 2:49 PM, Bruce Momjianbr...@momjian.us wrote: Who's going to be the first to say that being git-centric can't ever be a bad thing? ;-) At least for me, referring to it that way makes finding the original patch an order of magnitude faster (git show hash). YMMV. [ shrug... ] You need to take the long view here. We're not working on the assumption that git is the last SCM this project will ever use. Even granting that it is, I don't think git hashes are adequately stable; loading the code into a different repository would likely result in new hashes. And AFAIK there is no mechanism that would fix hash references embedded in commit log messages (or the code). Well, if we ever did want to rewrite the entire development history (why?) I suppose we could rewrite SHA hashes in the commit messages at the same time. But I think one big advantage of git (or svn, or probably any other post-CVS VCS) is that it has unique IDs for commits. Referring to them as the commit by so-and-so on such-and-such a date just on the off chance that we might someday decide to replace those unique IDs with another set of unique IDs doesn't make much sense to me. It makes things more difficult now in the hope that, ten years from now when we switch systems again, it'll be easier to use unstructured text to construct a search command to root through the development history than it will be to map a git commit id onto a commit id in the new system. That's possible, but it's far from obvious. We are database professionals; we ought to believe in the value of unique keys. Let's do both: This fixes the bug introduced by the foobar patch from Sep 12th (git commitid a2c23897bc). I like to see the date of the referred patch in the commit message, to get an immediate idea of whether it was a 5-year old change or something from the previous day. But the commitid is also nice so you can immediately copy-paste that without reading through the old commit logs. -- 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] Re: [COMMITTERS] pgsql: Remove outdated comments from the regression test files.
On Mon, Nov 29, 2010 at 13:42, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 28.11.2010 06:59, Robert Haas wrote: On Sat, Nov 27, 2010 at 3:46 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haasrobertmh...@gmail.com writes: On Nov 27, 2010, at 2:49 PM, Bruce Momjianbr...@momjian.us wrote: Who's going to be the first to say that being git-centric can't ever be a bad thing? ;-) At least for me, referring to it that way makes finding the original patch an order of magnitude faster (git show hash). YMMV. [ shrug... ] You need to take the long view here. We're not working on the assumption that git is the last SCM this project will ever use. Even granting that it is, I don't think git hashes are adequately stable; loading the code into a different repository would likely result in new hashes. And AFAIK there is no mechanism that would fix hash references embedded in commit log messages (or the code). Well, if we ever did want to rewrite the entire development history (why?) I suppose we could rewrite SHA hashes in the commit messages at the same time. But I think one big advantage of git (or svn, or probably any other post-CVS VCS) is that it has unique IDs for commits. Referring to them as the commit by so-and-so on such-and-such a date just on the off chance that we might someday decide to replace those unique IDs with another set of unique IDs doesn't make much sense to me. It makes things more difficult now in the hope that, ten years from now when we switch systems again, it'll be easier to use unstructured text to construct a search command to root through the development history than it will be to map a git commit id onto a commit id in the new system. That's possible, but it's far from obvious. We are database professionals; we ought to believe in the value of unique keys. Let's do both: This fixes the bug introduced by the foobar patch from Sep 12th (git commitid a2c23897bc). I like to see the date of the referred patch in the commit message, to get an immediate idea of whether it was a 5-year old change or something from the previous day. But the commitid is also nice so you can immediately copy-paste that without reading through the old commit logs. +1. Having the git id is very useful, and putting the date in makes it no *less* informational than what we had before, if/when we move away from git and it's hashes. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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 BUG #5103: pg_ctl -w (re)start fails with custom unix_socket_directory
Excerpts from Quan Zongliang's message of sáb nov 27 06:03:12 -0300 2010: Hi, all I created a pg_ctl patch to fix: * BUG #5103: pg_ctl -w (re)start fails with custom unix_socket_directory Allow pg_ctl to work properly with configuration files located outside the PGDATA directory I think the way this should work is that you call postmaster with a new switch and it prints out its configuration, after reading the appropriate config file(s). That way it handles all the little details such as figuring out the correct config file, hadle include files, etc. This output would be presumably easier to parse and more trustworthy. Right now we have --describe-config, which is missing the values for each config option. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. 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] ToDo: enhanced diagnostic for plpgsql
Excerpts from Pavel Stehule's message of sáb nov 27 04:46:08 -0300 2010: Hello do you plan do some work on this job? Not currently :-( -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. 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] DropRelFileNodeBuffers API change (was Re: [BUGS] BUG #5599: Vacuum fails due to index corruption issues)
Excerpts from Tom Lane's message of dom ago 15 22:54:31 -0400 2010: I'm thinking that we need some sort of what-to-do-on-error flag passed into RelationTruncate, plus at least order-of-operations fixes in several other places, if not a wholesale refactoring of this whole call stack. But I'm running out of steam and don't have a concrete proposal to make right now. In any case, we've got more problems here than just the original one of forgetting dirty buffers too soon. I think this fell through the cracks. What are we going to do here? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. 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] Re: [COMMITTERS] pgsql: Remove outdated comments from the regression test files.
On Mon, Nov 29, 2010 at 7:45 AM, Magnus Hagander mag...@hagander.net wrote: Let's do both: This fixes the bug introduced by the foobar patch from Sep 12th (git commitid a2c23897bc). I like to see the date of the referred patch in the commit message, to get an immediate idea of whether it was a 5-year old change or something from the previous day. But the commitid is also nice so you can immediately copy-paste that without reading through the old commit logs. +1. Having the git id is very useful, and putting the date in makes it no *less* informational than what we had before, if/when we move away from git and it's hashes. That works for me. But should we make a practice of writing the ENTIRE SHA-ID rather than an abbreviated form, so that we could more easily replace 'em later if need be? I think that would be a good idea for other reasons anyway - it's always possible - though admittedly unlikely - that a later commit could introduce a conflict with the first 10 characters, but a conflict with the whole string is pretty much discountable. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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_execute_from_file review
On Mon, Nov 29, 2010 at 4:26 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: * I'd like to ask native speakers whether from is needed in names of pg_execute_from_file and pg_execute_from_query_string. Fair enough, will wait for some comments before producing a v6. Yes, you need the from there. -- Robert Haas Native English Speaker -- 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_execute_from_file review
On Mon, Nov 29, 2010 at 10:27 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Nov 29, 2010 at 4:26 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: * I'd like to ask native speakers whether from is needed in names of pg_execute_from_file and pg_execute_from_query_string. Fair enough, will wait for some comments before producing a v6. Yes, you need the from there. Eh, wait. You definitely need from in pg_execute_from_file(). But pg_execute_from_query_string() doesn't sound quite right. What does that function do, anyway? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Report: Linux huge pages with Postgres
On Sat, 27 Nov 2010 14:27:12 -0500 Tom Lane t...@sss.pgh.pa.us wrote: And the bottom line is: if there's any performance benefit at all, it's on the order of 1%. The best result I got was about 3200 TPS with hugepages, and about 3160 without. The noise in these numbers is more than 1% though. This is discouraging; it certainly doesn't make me want to expend the effort to develop a production patch. However, perhaps someone else can try to show a greater benefit under some other test conditions. Just a quick note: I can't hazard a guess as to why you're not getting better results than you are, but I *can* say that putting together a production-quality patch may not be worth your effort regardless. There is a nice transparent hugepages patch set out there which makes hugepages just happen when it seems to make sense and the system can support it. It eliminates the need for all administrative fiddling and for any support at the application level. This patch is invasive and has proved to be hard to merge. RHEL6 has it, though, and I believe it will get in eventually. I can point you at the developer involved if you'd like to experiment with this feature and see what it can do for you. jon Jonathan Corbet / LWN.net / cor...@lwn.net -- 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] Report: Linux huge pages with Postgres
Jonathan Corbet cor...@lwn.net writes: Just a quick note: I can't hazard a guess as to why you're not getting better results than you are, but I *can* say that putting together a production-quality patch may not be worth your effort regardless. There is a nice transparent hugepages patch set out there which makes hugepages just happen when it seems to make sense and the system can support it. It eliminates the need for all administrative fiddling and for any support at the application level. That would be cool, because the current kernel feature is about as unfriendly to use as it could possibly be ... 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_execute_from_file review
On 11/29/2010 10:30 AM, Robert Haas wrote: On Mon, Nov 29, 2010 at 10:27 AM, Robert Haasrobertmh...@gmail.com wrote: On Mon, Nov 29, 2010 at 4:26 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: * I'd like to ask native speakers whether from is needed in names of pg_execute_from_file and pg_execute_from_query_string. Fair enough, will wait for some comments before producing a v6. Yes, you need the from there. Eh, wait. You definitely need from in pg_execute_from_file(). But pg_execute_from_query_string() doesn't sound quite right. What does that function do, anyway? I'm not sure why you need either from. It just seems like a noise word. Maybe we could use pg_execute_query_file() and pg_execute_query_string(), which would be fairly clear and nicely symmetrical. cheers andrew -- 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_execute_from_file review
Andrew Dunstan and...@dunslane.net writes: I'm not sure why you need either from. It just seems like a noise word. Maybe we could use pg_execute_query_file() and pg_execute_query_string(), which would be fairly clear and nicely symmetrical. I'd go with that but need to tell: only pg_execute_query_file() is to be exposed at the SQL level, the other one is just a backend facility to share code between the variants with and without placeholders. If you wonder why have two variants, it's because you can't have default values (pg_node_tree) in pg_proc.h, and following Tom's advices. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] directory archive format for pg_dump
On 29.11.2010 07:11, Joachim Wieland wrote: On Mon, Nov 22, 2010 at 3:44 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: * wrap long lines * use extern in function prototypes in header files * inline some functions like _StartDataCompressor, _EndDataCompressor, _DoInflate/_DoDeflate that aren't doing anything but call some other function. So here is a new round of patches. It turned out that the feature to allow to also restore files from a different dump and with a different compression required some changes in the compressor API. And in the end I didn't like all the #ifdefs either and made a less #ifdef-rich version using function pointers. The downside now is that I have created quite a few one-line functions that Heikki doesn't like all that much, but I assume that they are okay in this case on the grounds that the public compressor interface is calling the private implementation of a certain compressor. Thanks, I'll take a look. BTW, I know you wanted to have support for other compression algorithms; I think the best way to achieve that is to make it possible to specify an external command to be used for compression. pg_dump would fork() and exec() that, and pipe the data to be compressed/decompressed to stdin/stdout of the external command. We're not going to add support for every new compression algorithm that's in vogue, but generic external command support should make happy those who want it. I'd be particularly excited about using something like pbzip2, to speed up the compression on multi-core systems. That should be a separate patch, but it's something to keep in mind with these refactorings. -- 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] [GENERAL] column-level update privs + lock table
2010/11/28 KaiGai Kohei kai...@ak.jp.nec.com: My comment was from a standpoint which wants consistent behavior between SELECT ... FOR and LOCK command. Again, nothing about this makes those consistent. If we concerned about this behavior, ExecCheckRTEPerms() might be a place where we also should fix. I don't understand what you're getting at here. I thought the author concerned about inconsistency between them. (Perhaps, I might misunderstood his motivation?) A user with single-column UPDATE privileges could obtain a ROW EXCLUSIVE lock by issuing an UPDATE statement, but currently cannot obtain the same lock using LOCK TABLE. It would be reasonable and consistent to allow such a user to take a ROW SHARE or ROW EXCLUSIVE lock using LOCK TABLE, but I'm not sure what the use case for that would be. It seems to me that if we're really worried about which locks users are allowed to take (and so far all of the worrying seems to lack a solid basis in any sort of usability argument) we'd need to invent some special-purpose permissions, perhaps one for each lock level. And we might also want custom permissions for ANALYZE and VACUUM and each subcommand of ALTER TABLE. The question is, how much of that has any real benefit? It's probably uncommon to want to dole out such fine-grained permissions, and our current permissions-granting infrastructure tops out at 16 individual permissions, so it would need some rework - particularly, to minimize slowdown of the common case where you DON'T care about any of these fiddly ultra-fine-grained user rights. For LOCK TABLE (or ANALYZE), it appears to be simple to allow users to lock the table in any mode you like by providing an appropriate SECURITY DEFINER function. So I think if people want a user who can update a single column of the table and also take an AccessExclusiveLock we can just recommend that they do it that way. This also works for ANALYZE. If you need a user who doesn't own a table to be able to VACUUM it, that's a bit trickier because VACUUM can only be invoked as a top-level command, not from within a function or already-open transaction. Perhaps we can fix this some day if we implement autonomous transactions, but for now it doesn't really seem worth losing a lot of sleep over. Just my opinion, of course... Do we need to answer: Yes, it is a specification, so you need to grant table level privileges, instead? I think that's the most reasonable answer. My vote is to just update the LOCK TABLE documentation to be more precise about what the rules are, and move on. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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_execute_from_file review
On Mon, Nov 29, 2010 at 10:37 AM, Andrew Dunstan and...@dunslane.net wrote: On 11/29/2010 10:30 AM, Robert Haas wrote: On Mon, Nov 29, 2010 at 10:27 AM, Robert Haasrobertmh...@gmail.com wrote: On Mon, Nov 29, 2010 at 4:26 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: * I'd like to ask native speakers whether from is needed in names of pg_execute_from_file and pg_execute_from_query_string. Fair enough, will wait for some comments before producing a v6. Yes, you need the from there. Eh, wait. You definitely need from in pg_execute_from_file(). But pg_execute_from_query_string() doesn't sound quite right. What does that function do, anyway? I'm not sure why you need either from. It just seems like a noise word. Maybe we could use pg_execute_query_file() and pg_execute_query_string(), which would be fairly clear and nicely symmetrical. Because you execute queries, not files. Or at least that's how I think about it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Report: Linux huge pages with Postgres
On Mon, Nov 29, 2010 at 10:30 AM, Jonathan Corbet cor...@lwn.net wrote: On Sat, 27 Nov 2010 14:27:12 -0500 Tom Lane t...@sss.pgh.pa.us wrote: And the bottom line is: if there's any performance benefit at all, it's on the order of 1%. The best result I got was about 3200 TPS with hugepages, and about 3160 without. The noise in these numbers is more than 1% though. This is discouraging; it certainly doesn't make me want to expend the effort to develop a production patch. However, perhaps someone else can try to show a greater benefit under some other test conditions. Just a quick note: I can't hazard a guess as to why you're not getting better results than you are, but I *can* say that putting together a production-quality patch may not be worth your effort regardless. There is a nice transparent hugepages patch set out there which makes hugepages just happen when it seems to make sense and the system can support it. It eliminates the need for all administrative fiddling and for any support at the application level. Neat! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] directory archive format for pg_dump
On Mon, Nov 29, 2010 at 10:49 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 29.11.2010 07:11, Joachim Wieland wrote: On Mon, Nov 22, 2010 at 3:44 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: * wrap long lines * use extern in function prototypes in header files * inline some functions like _StartDataCompressor, _EndDataCompressor, _DoInflate/_DoDeflate that aren't doing anything but call some other function. So here is a new round of patches. It turned out that the feature to allow to also restore files from a different dump and with a different compression required some changes in the compressor API. And in the end I didn't like all the #ifdefs either and made a less #ifdef-rich version using function pointers. The downside now is that I have created quite a few one-line functions that Heikki doesn't like all that much, but I assume that they are okay in this case on the grounds that the public compressor interface is calling the private implementation of a certain compressor. Thanks, I'll take a look. BTW, I know you wanted to have support for other compression algorithms; I think the best way to achieve that is to make it possible to specify an external command to be used for compression. pg_dump would fork() and exec() that, and pipe the data to be compressed/decompressed to stdin/stdout of the external command. We're not going to add support for every new compression algorithm that's in vogue, but generic external command support should make happy those who want it. I'd be particularly excited about using something like pbzip2, to speed up the compression on multi-core systems. That should be a separate patch, but it's something to keep in mind with these refactorings. That would also ease licensing concerns, since we wouldn't have to redistribute or bundle anything. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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_execute_from_file review
Andrew Dunstan and...@dunslane.net writes: I'm not sure why you need either from. It just seems like a noise word. Maybe we could use pg_execute_query_file() and pg_execute_query_string(), which would be fairly clear and nicely symmetrical. +1, but I think query is also a noise word here. Why not just pg_execute_file and pg_execute_string? 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_execute_from_file review
On 11/29/2010 10:51 AM, Robert Haas wrote: I'm not sure why you need either from. It just seems like a noise word. Maybe we could use pg_execute_query_file() and pg_execute_query_string(), which would be fairly clear and nicely symmetrical. Because you execute queries, not files. Or at least that's how I think about it. Well, to me pg_execute_query_file says execute the queries in this file. I'm not sure what else it could sensibly mean. But I think any of the suggestions will probably work OK. cheers andrew -- 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_execute_from_file review
On Mon, Nov 29, 2010 at 11:12:58AM -0500, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: I'm not sure why you need either from. It just seems like a noise word. Maybe we could use pg_execute_query_file() and pg_execute_query_string(), which would be fairly clear and nicely symmetrical. +1, but I think query is also a noise word here. Why not just pg_execute_file and pg_execute_string? regards, tom lane While we're bikeshedding, and since I started the thread that has become this topic, +1 for Tom's naming. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] pg_execute_from_file review
On 11/29/2010 11:12 AM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: I'm not sure why you need either from. It just seems like a noise word. Maybe we could use pg_execute_query_file() and pg_execute_query_string(), which would be fairly clear and nicely symmetrical. +1, but I think query is also a noise word here. Why not just pg_execute_file and pg_execute_string? Well, I put that in to make it clear that the file/string is expected to contain SQL and not, say, machine code. But I agree we could possibly do without it. cheers andrew -- 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_execute_from_file review
Andrew Dunstan and...@dunslane.net writes: On 11/29/2010 11:12 AM, Tom Lane wrote: +1, but I think query is also a noise word here. Why not just pg_execute_file and pg_execute_string? Well, I put that in to make it clear that the file/string is expected to contain SQL and not, say, machine code. But I agree we could possibly do without it. Well, if you want to make that clear, it should be pg_execute_sql_file etc. I still think query is pretty vague, if not actually counterproductive (because it's singular not plural, so someone might think the file can only contain one query). 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_execute_from_file review
On Mon, Nov 29, 2010 at 11:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: I'm not sure why you need either from. It just seems like a noise word. Maybe we could use pg_execute_query_file() and pg_execute_query_string(), which would be fairly clear and nicely symmetrical. +1, but I think query is also a noise word here. Why not just pg_execute_file and pg_execute_string? I'd pick pg_execute_from_file() and just plain pg_execute(), myself. pg_execute_file() could be read to mean you are going to execute the file itself (i.e. it's a program). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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_execute_from_file review
Robert Haas robertmh...@gmail.com writes: pg_execute_file() could be read to mean you are going to execute the file itself (i.e. it's a program). Well, if that's what it suggests to you, I don't see how adding from disambiguates anything. You could be executing machine code from a file, too. What did you think of pg_execute_sql_file? 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_execute_from_file review
On Mon, Nov 29, 2010 at 11:48 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: pg_execute_file() could be read to mean you are going to execute the file itself (i.e. it's a program). Well, if that's what it suggests to you, I don't see how adding from disambiguates anything. You could be executing machine code from a file, too. What did you think of pg_execute_sql_file? That, I like. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] profiling connection overhead
On Sun, Nov 28, 2010 at 11:51 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
Yeah, very true. What's a bit frustrating about the whole thing is
that we spend a lot of time pulling data into the caches that's
basically static and never likely to change anywhere, ever.
True. I wonder if we could do something like the relcache init file
for the catcaches.
Maybe. It's hard to know exactly what to pull in, though, nor is it
clear to me how much it would really save. You've got to keep the
thing up to date somehow, too.
I finally got around to doing some testing of
page-faults-versus-actually-memory-initialization, using the attached
test program, compiled with warnings, but without optimization.
Typical results on MacOS X:
first run: 297299
second run: 99653
And on Fedora 12 (2.6.32.23-170.fc12.x86_64):
first run: 509309
second run: 114721
I guess the word run is misleading (I wrote the program in 5
minutes); it's just zeroing the same chunk twice and measuring the
times. The difference is presumably the page fault overhead, which
implies that faulting is two-thirds of the overhead on MacOS X and
three-quarters of the overhead on Linux. This makes me pretty
pessimistic about the chances of a meaningful speedup here.
Maybe we could speed things up a bit if we got rid of the pg_attribute
entries for the system attributes (except OID).
I used to have high hopes for that idea, but the column privileges
patch broke it permanently.
http://archives.postgresql.org/pgsql-hackers/2010-07/msg00151.php
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
#include stdio.h
#include stdlib.h
#include string.h
#include sys/time.h
char bss[512*1024*1024];
void
print_times(char *tag, struct timeval *before, struct timeval *after)
{
int result = (after-tv_sec - before-tv_sec) * 100
+ ((int)after-tv_usec) - ((int)before-tv_usec);
printf(%s: %d\n, tag, result);
}
int
main(int argc, char **argv)
{
struct timeval t1;
struct timeval t2;
struct timeval t3;
if (gettimeofday(t1, NULL))
return 1;
memset(bss, 0, sizeof bss);
if (gettimeofday(t2, NULL))
return 1;
memset(bss, 0, sizeof bss);
if (gettimeofday(t3, NULL))
return 1;
print_times(first run, t1, t2);
print_times(second run, t2, t3);
return 0;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] large page query
Hi, I posted this email on the other postgres lists but did not get a reply. So as a last resort, I came here. I hope somebody can help. I am looking into the impact of large page sizes on the performance of commercial workloads e.g databases,webserver,virtual machines etc. I was wondering if I could get to know whether Postgres administrators configure the Postgres DBMS with large page support for shared memory regions, specifically on the Solaris 9 and 10 OSes. My understanding is that since large pages (4 MB) are suitable for applications allocating large shared memory regions (databases for instance), Postgres would most definitely use the large page support. Is it a functionality placed into Postgres by the developers or the administrator has to configure the database to use it ? So in a nutshell, the questions are 1) Does Postgres use large page support ? On solaris 10 and the ultrasparc III processor, a large page is 4 MB. It significantly reduces the page table size of the application and a 1000 entry TLB can cover the entire memory 4G. 2) On Solaris 9 and 10, does Postgres rely on the MPSS support provided by the Operating system and relegate the job of figuring out what to allocate as a large page and what not to, when to allocate a large page and when not to etc to the Operating system? Or is it the case that the Postgres developers have written it judiciously and Postgres itself knows what to and what not to allocate as a large page ? The reason i ask this question is because, i know for a JVM, solaris 10 allocates large pages for the heap memory (this is default behavior, no runtime parameters needed when one runs the JVM. The OS is smart enough to figure this out by probably looking at what is the app that is running ) 3) In light of all this, do we know the performance difference between Postgres configured with no large pages vs Postgres configured with large pages. Your replies are highly appreciated. Hamza
Re: [HACKERS] large page query
On Mon, Nov 29, 2010 at 12:05 PM, Hamza Bin Sohail hsoh...@purdue.edu wrote: 1) Does Postgres use large page support ? On solaris 10 and the ultrasparc III processor, a large page is 4 MB. It significantly reduces the page table size of the application and a 1000 entry TLB can cover the entire memory 4G. 2) On Solaris 9 and 10, does Postgres rely on the MPSS support provided by the Operating system and relegate the job of figuring out what to allocate as a large page and what not to, when to allocate a large page and when not to etc to the Operating system? Or is it the case that the Postgres developers have written it judiciously and Postgres itself knows what to and what not to allocate as a large page ? The reason i ask this question is because, i know for a JVM, solaris 10 allocates large pages for the heap memory (this is default behavior, no runtime parameters needed when one runs the JVM. The OS is smart enough to figure this out by probably looking at what is the app that is running ) 3) In light of all this, do we know the performance difference between Postgres configured with no large pages vs Postgres configured with large pages. To the best of my knowledge, PostgreSQL has no configuration options to use large page support on any architecture. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] PROPOSAL of xmlvalidate
On Sun, Nov 28, 2010 at 5:33 AM, Tomáš Pospíšil killt...@seznam.cz wrote: I have idea of creating system table for holding DTDs, XSDs, Relax-NGs (similar as on ORACLE). Is that good idea? I doubt it. Why would we want to do that? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] large page query
Hamza Bin Sohail hsoh...@purdue.edu wrote: I was wondering if I could get to know whether Postgres administrators configure the Postgres DBMS with large page support for shared memory regions You might be interested in a recent thread in the -hackers archives which starts with this post: http://archives.postgresql.org/message-id/28895.1290886...@sss.pgh.pa.us -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] PROPOSAL of xmlvalidate
2010/11/29 Robert Haas robertmh...@gmail.com: On Sun, Nov 28, 2010 at 5:33 AM, Tomáš Pospíšil killt...@seznam.cz wrote: I have idea of creating system table for holding DTDs, XSDs, Relax-NGs (similar as on ORACLE). Is that good idea? I doubt it. Why would we want to do that? If I understand, it allows a local copy of DTD, .. so then is possible to provide a fast DTD checking. Regards Pavel Stehule -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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_execute_from_file review
Robert Haas robertmh...@gmail.com writes: I'd pick pg_execute_from_file() and just plain pg_execute(), myself. For the record there's only one name exposed at the SQL level. Or do you want me to expand the patch to actually include a pg_execute() version of the function, that would execute the query in PG_GETARG_TEXT_P(0)? On Mon, Nov 29, 2010 at 11:48 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: What did you think of pg_execute_sql_file? That, I like. Ok, I call pg_execute_sql_file() the winner and will prepare a new patch later tonight, now is comute time. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] profiling connection overhead
On Monday 29 November 2010 17:57:51 Robert Haas wrote: On Sun, Nov 28, 2010 at 11:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Yeah, very true. What's a bit frustrating about the whole thing is that we spend a lot of time pulling data into the caches that's basically static and never likely to change anywhere, ever. True. I wonder if we could do something like the relcache init file for the catcaches. Maybe. It's hard to know exactly what to pull in, though, nor is it clear to me how much it would really save. You've got to keep the thing up to date somehow, too. I finally got around to doing some testing of page-faults-versus-actually-memory-initialization, using the attached test program, compiled with warnings, but without optimization. Typical results on MacOS X: first run: 297299 second run: 99653 And on Fedora 12 (2.6.32.23-170.fc12.x86_64): first run: 509309 second run: 114721 Hm. A quick test shows that its quite a bit faster if you allocate memory with: size_t s = 512*1024*1024; char *bss = mmap(0, s, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_POPULATE| MAP_ANONYMOUS, -1, 0); Andres -- 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] profiling connection overhead
On Mon, Nov 29, 2010 at 12:24 PM, Andres Freund and...@anarazel.de wrote: Hm. A quick test shows that its quite a bit faster if you allocate memory with: size_t s = 512*1024*1024; char *bss = mmap(0, s, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_POPULATE| MAP_ANONYMOUS, -1, 0); Numbers? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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_execute_from_file review
On Mon, Nov 29, 2010 at 12:21 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Robert Haas robertmh...@gmail.com writes: I'd pick pg_execute_from_file() and just plain pg_execute(), myself. For the record there's only one name exposed at the SQL level. Or do you want me to expand the patch to actually include a pg_execute() version of the function, that would execute the query in PG_GETARG_TEXT_P(0)? No, not particularly. On Mon, Nov 29, 2010 at 11:48 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: What did you think of pg_execute_sql_file? That, I like. Ok, I call pg_execute_sql_file() the winner and will prepare a new patch later tonight, now is comute time. Sounds good. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] PROPOSAL of xmlvalidate
On Mon, Nov 29, 2010 at 12:18 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2010/11/29 Robert Haas robertmh...@gmail.com: On Sun, Nov 28, 2010 at 5:33 AM, Tomáš Pospíšil killt...@seznam.cz wrote: I have idea of creating system table for holding DTDs, XSDs, Relax-NGs (similar as on ORACLE). Is that good idea? I doubt it. Why would we want to do that? If I understand, it allows a local copy of DTD, .. so then is possible to provide a fast DTD checking. But that could equally well be stored in a user table rather than a system table. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] profiling connection overhead
On Monday 29 November 2010 18:34:02 Robert Haas wrote:
On Mon, Nov 29, 2010 at 12:24 PM, Andres Freund and...@anarazel.de wrote:
Hm. A quick test shows that its quite a bit faster if you allocate memory
with:
size_t s = 512*1024*1024;
char *bss = mmap(0, s, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_POPULATE|
MAP_ANONYMOUS, -1, 0);
Numbers?
malloc alloc: 43
malloc memset1: 438763
malloc memset2: 98764
total: 537570
mmap alloc: 296065
mmap memset1: 99203
mmap memset2: 100608
total: 495876
But you don't actually need the memset1 in the mmap case as MAP_ANONYMOUS
memory is already zeroed. We could actually use that knowledge even without
MAP_POPULATE if we somehow keep track whether an allocated memory region is
still zeroed.
Taking that into account its:
malloc alloc: 47
malloc memset1: 437819
malloc memset2: 98317
total: 536183
mmap alloc: 292904
mmap memset1: 1
mmap memset2: 99284
total: 392189
I am somewhat reluctant to believe thats the way to go.
Andres
#include stdio.h
#include stdlib.h
#include string.h
#include sys/time.h
#include sys/mman.h
#include malloc.h
char bss[512*1024*1024];
void
print_times(char *tag, struct timeval *before, struct timeval *after)
{
int result = (after-tv_sec - before-tv_sec) * 100
+ ((int)after-tv_usec) - ((int)before-tv_usec);
printf(%s: %d\n, tag, result);
}
int
main(int argc, char **argv)
{
size_t s = 512*1024*1024;
struct timeval t1_1;
struct timeval t1_2;
struct timeval t1_3;
struct timeval t1_4;
struct timeval t2_1;
struct timeval t2_2;
struct timeval t2_3;
struct timeval t2_4;
if (gettimeofday(t1_1, NULL))
return 1;
mallopt(M_MMAP_MAX, 0);
char* bss1 = malloc(s);
if (gettimeofday(t1_2, NULL))
return 1;
memset(bss1, 0, s);
if (gettimeofday(t1_3, NULL))
return 1;
memset(bss1, 0, s);
if (gettimeofday(t1_4, NULL))
return 1;
if (gettimeofday(t2_1, NULL))
return 1;
char* bss2 = mmap(0, s, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_POPULATE|MAP_ANONYMOUS, -1, 0);
if (gettimeofday(t2_2, NULL))
return 1;
//memset(bss1, 0, s);
if (gettimeofday(t2_3, NULL))
return 1;
memset(bss1, 0, s);
if (gettimeofday(t2_4, NULL))
return 1;
print_times(malloc alloc, t1_1, t1_2);
print_times(malloc memset1, t1_2, t1_3);
print_times(malloc memset2, t1_3, t1_4);
print_times(total, t1_1, t1_4);
print_times(mmap alloc, t2_1, t2_2);
print_times(mmap memset1, t2_2, t2_3);
print_times(mmap memset2, t2_3, t2_4);
print_times(total, t2_1, t2_4);
return 0;
}
--
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] profiling connection overhead
On Mon, Nov 29, 2010 at 9:24 AM, Andres Freund and...@anarazel.de wrote: On Monday 29 November 2010 17:57:51 Robert Haas wrote: On Sun, Nov 28, 2010 at 11:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Yeah, very true. What's a bit frustrating about the whole thing is that we spend a lot of time pulling data into the caches that's basically static and never likely to change anywhere, ever. True. I wonder if we could do something like the relcache init file for the catcaches. Maybe. It's hard to know exactly what to pull in, though, nor is it clear to me how much it would really save. You've got to keep the thing up to date somehow, too. I finally got around to doing some testing of page-faults-versus-actually-memory-initialization, using the attached test program, compiled with warnings, but without optimization. Typical results on MacOS X: first run: 297299 second run: 99653 And on Fedora 12 (2.6.32.23-170.fc12.x86_64): first run: 509309 second run: 114721 Hm. A quick test shows that its quite a bit faster if you allocate memory with: size_t s = 512*1024*1024; char *bss = mmap(0, s, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_POPULATE| MAP_ANONYMOUS, -1, 0); Could you post the program? Are you sure you haven't just moved the page-fault time to a part of the code where it still exists, but just isn't being captured and reported? Cheers, Jeff -- 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] profiling connection overhead
Robert Haas robertmh...@gmail.com writes: I guess the word run is misleading (I wrote the program in 5 minutes); it's just zeroing the same chunk twice and measuring the times. The difference is presumably the page fault overhead, which implies that faulting is two-thirds of the overhead on MacOS X and three-quarters of the overhead on Linux. Ah, cute solution to the measurement problem. I replicated the experiment just as a cross-check: Fedora 13 on x86_64 (recent Nehalem): first run: 346767 second run: 103143 Darwin on x86_64 (not-so-recent Penryn): first run: 341289 second run: 64535 HPUX on HPPA: first run: 2191136 second run: 1199879 (On the last two machines I had to cut the array size to 256MB to avoid swapping.) All builds with gcc -O2. This makes me pretty pessimistic about the chances of a meaningful speedup here. Yeah, this is confirmation that what you are seeing in the original test is mostly about faulting pages in, not about the zeroing. I think it would still be interesting to revisit the micro-optimization of MemSet(), but it doesn't look like massive restructuring to avoid it altogether is going to be worthwhile. 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] PROPOSAL of xmlvalidate
2010/11/29 Robert Haas robertmh...@gmail.com: On Mon, Nov 29, 2010 at 12:18 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2010/11/29 Robert Haas robertmh...@gmail.com: On Sun, Nov 28, 2010 at 5:33 AM, Tomáš Pospíšil killt...@seznam.cz wrote: I have idea of creating system table for holding DTDs, XSDs, Relax-NGs (similar as on ORACLE). Is that good idea? I doubt it. Why would we want to do that? If I understand, it allows a local copy of DTD, .. so then is possible to provide a fast DTD checking. But that could equally well be stored in a user table rather than a system table. yes or now. If we have a some integrated rule for xml validation, but I can't to imagine a dependency on custom table. More - system table can be better cached. So it depends on level of integration to system. Probably it needs a deep discuss about SQL/XML and other questions. It can mean a not optional dependency on libxml2. Pavel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] PROPOSAL of xmlvalidate
On 11/29/2010 12:36 PM, Robert Haas wrote: On Mon, Nov 29, 2010 at 12:18 PM, Pavel Stehulepavel.steh...@gmail.com wrote: 2010/11/29 Robert Haasrobertmh...@gmail.com: On Sun, Nov 28, 2010 at 5:33 AM, Tomáš Pospíšilkillt...@seznam.cz wrote: I have idea of creating system table for holding DTDs, XSDs, Relax-NGs (similar as on ORACLE). Is that good idea? I doubt it. Why would we want to do that? If I understand, it allows a local copy of DTD, .. so then is possible to provide a fast DTD checking. But that could equally well be stored in a user table rather than a system table. Yeah. The trouble is you won't be able to use that reliably in a check constraint, which I imagine is one of the principal intended purposes. I'm not sure how we should go about that. cheers andrew -- 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] profiling connection overhead
Jeff Janes jeff.ja...@gmail.com writes: Are you sure you haven't just moved the page-fault time to a part of the code where it still exists, but just isn't being captured and reported? I'm a bit suspicious about that too. Another thing to keep in mind is that Robert's original program doesn't guarantee that the char array is maxaligned; though reasonable implementations of memset should be able to use the same inner loop anyway for most of the array. I did some experimentation here and couldn't find any real difference in runtime between the original program and substituting a malloc() call for the static array allocation. Rolling in calloc in place of malloc/memset made no particular difference either, which says that Fedora 13's glibc does not have any optimization for that case as I'd hoped. 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] profiling connection overhead
On Mon, Nov 29, 2010 at 12:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: (On the last two machines I had to cut the array size to 256MB to avoid swapping.) You weren't kidding about that not so recent part. :-) This makes me pretty pessimistic about the chances of a meaningful speedup here. Yeah, this is confirmation that what you are seeing in the original test is mostly about faulting pages in, not about the zeroing. I think it would still be interesting to revisit the micro-optimization of MemSet(), but it doesn't look like massive restructuring to avoid it altogether is going to be worthwhile. Yep. I think that what we've established here is that starting new processes all time time is just plain expensive, and we're going to have to start fewer of them if we want to make a meaningful improvement. My impression is that the process startup overhead is even higher on Windows, although I am not now nor have I ever been a Windows programmer. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] PROPOSAL of xmlvalidate
On Mon, Nov 29, 2010 at 12:56 PM, Andrew Dunstan and...@dunslane.net wrote: On 11/29/2010 12:36 PM, Robert Haas wrote: On Mon, Nov 29, 2010 at 12:18 PM, Pavel Stehulepavel.steh...@gmail.com wrote: 2010/11/29 Robert Haasrobertmh...@gmail.com: On Sun, Nov 28, 2010 at 5:33 AM, Tomáš Pospíšilkillt...@seznam.cz wrote: I have idea of creating system table for holding DTDs, XSDs, Relax-NGs (similar as on ORACLE). Is that good idea? I doubt it. Why would we want to do that? If I understand, it allows a local copy of DTD, .. so then is possible to provide a fast DTD checking. But that could equally well be stored in a user table rather than a system table. Yeah. The trouble is you won't be able to use that reliably in a check constraint, which I imagine is one of the principal intended purposes. I'm not sure how we should go about that. There is a whole category of things where you might want to write a check constraint that involves accessing data in some other table, and therefore it falls down because this makes the constraint non-immutable. But not infrequently one knows that, while in theory the other table could change, in practice it will not, or only in ways that won't cause the CHECK constraint to be violated. We need to think about a sensible way of handling this class of problems. Putting the data into a system table doesn't really accomplish anything; system tables aren't read-only either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] compile error via SIOCGLIFCONF from ip.c on hpux-11
PostgreSQL 9 is breaking for me on line 654 of ip.c. ip.c is checking
on presence SIOCGLIFCONF to determine if it's ok to use linux method
of polling if addrs etc over ioctl, which is not enough. hpux provides
this method in similar fashion, but the structs are named different,
and have different members.
for example, we have:
struct if_laddrreq {
chariflr_name[IF_NAMESIZE];/* if name, e.g. lan0 */
union {
struct sockaddr_ext iflru_addr;
struct sockaddr_ext iflru_dstaddr;
int iflru_flags;
#if defined(__STDC_EXT__) || defined(__LP64__)
uint64_t iflru_xflags;
#endif
int iflru_metric;
struct iflife iflru_lifetimes;
caddr_t iflru_data;
unsigned char __iflru_padding[32]; /* alignment for 32/64bit*/
} iflr_iflru;
which is similar to
struct ifreq {
char ifr_name[IFNAMSIZ]; /* Interface name */
union {
struct sockaddr ifr_addr;
struct sockaddr ifr_dstaddr;
struct sockaddr ifr_broadaddr;
struct sockaddr ifr_netmask;
struct sockaddr ifr_hwaddr;
short ifr_flags;
int ifr_ifindex;
int ifr_metric;
int ifr_mtu;
struct ifmapifr_map;
charifr_slave[IFNAMSIZ];
charifr_newname[IFNAMSIZ];
char * ifr_data;
};
};
(via http://unixhelp.ed.ac.uk/CGI/man-cgi?netdevice+7)
forcing the #if check of SIOCGLIFCONF to false allowed postgres to
continue and compile. Searching the archives turned up the
samehost/samenet patch...did that break this? was hpux checked, or is
this a version issue? (I'm testing on 11.23 Itanium).
merlin
--
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] PROPOSAL of xmlvalidate
Andrew Dunstan and...@dunslane.net writes: On 11/29/2010 12:36 PM, Robert Haas wrote: But that could equally well be stored in a user table rather than a system table. Yeah. The trouble is you won't be able to use that reliably in a check constraint, which I imagine is one of the principal intended purposes. Moving the same data to a system table doesn't fix that, unless you require that the system table be immutable ... which'd seem to make the idea useless. 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] compile error via SIOCGLIFCONF from ip.c on hpux-11
Merlin Moncure mmonc...@gmail.com writes: PostgreSQL 9 is breaking for me on line 654 of ip.c. ip.c is checking on presence SIOCGLIFCONF to determine if it's ok to use linux method of polling if addrs etc over ioctl, which is not enough. hpux provides this method in similar fashion, but the structs are named different, and have different members. This was complained of last month, and the situation has not changed: http://archives.postgresql.org/pgsql-general/2010-10/msg00408.php 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] Re: [COMMITTERS] pgsql: Remove outdated comments from the regression test files.
On Mon, Nov 29, 2010 at 16:26, Robert Haas robertmh...@gmail.com wrote: On Mon, Nov 29, 2010 at 7:45 AM, Magnus Hagander mag...@hagander.net wrote: Let's do both: This fixes the bug introduced by the foobar patch from Sep 12th (git commitid a2c23897bc). I like to see the date of the referred patch in the commit message, to get an immediate idea of whether it was a 5-year old change or something from the previous day. But the commitid is also nice so you can immediately copy-paste that without reading through the old commit logs. +1. Having the git id is very useful, and putting the date in makes it no *less* informational than what we had before, if/when we move away from git and it's hashes. That works for me. But should we make a practice of writing the ENTIRE SHA-ID rather than an abbreviated form, so that we could more easily replace 'em later if need be? I think that would be a good idea for other reasons anyway - it's always possible - though admittedly unlikely - that a later commit could introduce a conflict with the first 10 characters, but a conflict with the whole string is pretty much discountable. I think that's a good idea. And I suppose this is just going to be cutpaste in (almost?) every case anyway, so it doesn't really change the effort involved.. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Re: [COMMITTERS] pgsql: Remove outdated comments from the regression test files.
Magnus Hagander mag...@hagander.net writes: On Mon, Nov 29, 2010 at 16:26, Robert Haas robertmh...@gmail.com wrote: That works for me. But should we make a practice of writing the ENTIRE SHA-ID rather than an abbreviated form, so that we could more easily replace 'em later if need be? I think that's a good idea. Just as a data point, there is already one 7-hex-digit collision in our repository: Branch: master Release: REL6_1 [aaeef4dae] 1997-04-09 08:29:35 + Branch: master Release: REL6_1 [aaeef4d17] 1996-11-10 03:06:38 + I think it's quite foolish to depend on abbreviated hashes to be unique forever. 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] improving foreign key locks
Excerpts from Florian Pflug's message of sáb nov 27 01:29:39 -0300 2010: On Nov26, 2010, at 21:06 , Alvaro Herrera wrote: The problem with this idea is that it's not possible to implement it. How so? The implementation you proposed in your blog should work fine for this. XMAX_KEY_LOCK would signal that only fields from set (B) are locked, while XMAX_SHARE_LOCK (or however thats called, didn't check the code) would signal that all fields are locked. These are the only two field sets that we'd support, and for any set of columns the user specified we'd pick the smallest superset of the set we *do* support and use that (Thus, we obtain a key lock if only fields from a unique index where specified, and a share lock otherwise). The only difference is that instead of presenting this to the user as an entirely new lock type, we instead present it as a generalization of SHARE locks. The advantage being that *if* we ever figure out a way to support more fine-grained locking of fields, (say, locking only the fields contain in some *specific* index, maybe by storing locking the index tuple), we can do so completely transparent to the user. Oh, I see. Yeah, perhaps this could work. I'll have a look at both ends. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. 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] PROPOSAL of xmlvalidate
On 11/29/2010 01:30 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 11/29/2010 12:36 PM, Robert Haas wrote: But that could equally well be stored in a user table rather than a system table. Yeah. The trouble is you won't be able to use that reliably in a check constraint, which I imagine is one of the principal intended purposes. Moving the same data to a system table doesn't fix that, unless you require that the system table be immutable ... which'd seem to make the idea useless. Oh, yes, I agree. cheers andrew -- 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] compile error via SIOCGLIFCONF from ip.c on hpux-11
On Mon, Nov 29, 2010 at 1:38 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Merlin Moncure mmonc...@gmail.com writes:
PostgreSQL 9 is breaking for me on line 654 of ip.c. ip.c is checking
on presence SIOCGLIFCONF to determine if it's ok to use linux method
of polling if addrs etc over ioctl, which is not enough. hpux provides
this method in similar fashion, but the structs are named different,
and have different members.
This was complained of last month, and the situation has not changed:
http://archives.postgresql.org/pgsql-general/2010-10/msg00408.php
well, what should the fix be? checking on presence of SIOCGLIFCONF is
obviously weak sauce...it looks like some type of configure check is
needed I converted the function to a version that compiles clean on
hpux (note, I couldn't figure out how to set family, maybe that's not
required?). How do you test this feature?
merlin
int
pg_foreach_ifaddr(PgIfAddrCallback callback, void *cb_data)
{
struct if_laddrconf lifc;
struct if_laddrreq *lifr,
lmask;
struct sockaddr *addr,
*mask;
char *ptr,
*buffer = NULL;
size_t n_buffer = 1024;
pgsocketsock,
fd;
#ifdef HAVE_IPV6
pgsocketsock6;
#endif
int i,
total;
sock = socket(AF_INET, SOCK_DGRAM, 0);
if (sock == -1)
return -1;
while (n_buffer 1024 * 100)
{
n_buffer += 1024;
ptr = realloc(buffer, n_buffer);
if (!ptr)
{
free(buffer);
close(sock);
errno = ENOMEM;
return -1;
}
memset(lifc, 0, sizeof(lifc));
/* XXX how to set family? lifc.iflc_family = AF_UNSPEC; */
lifc.iflc_buf = buffer = ptr;
lifc.iflc_len = n_buffer;
if (ioctl(sock, SIOCGLIFCONF, lifc) 0)
{
if (errno == EINVAL)
continue;
free(buffer);
close(sock);
return -1;
}
/*
* Some Unixes try to return as much data as possible, with no
* indication of whether enough space allocated. Don't
believe we have
* it all unless there's lots of slop.
*/
if (lifc.iflc_len n_buffer - 1024)
break;
}
#ifdef HAVE_IPV6
/* We'll need an IPv6 socket too for the SIOCGLIFNETMASK ioctls */
sock6 = socket(AF_INET6, SOCK_DGRAM, 0);
if (sock6 == -1)
{
free(buffer);
close(sock);
return -1;
}
#endif
total = lifc.iflc_len / sizeof(struct lifreq);
lifr = lifc.iflc_req;
for (i = 0; i total; ++i)
{
addr = (struct sockaddr *) lifr[i].iflr_addr;
memcpy(lmask, lifr[i], sizeof(struct lifreq));
#ifdef HAVE_IPV6
fd = (addr-sa_family == AF_INET6) ? sock6 : sock;
#else
fd = sock;
#endif
if (ioctl(fd, SIOCGLIFNETMASK, lmask) 0)
mask = NULL;
else
mask = (struct sockaddr *) lmask.iflr_addr;
run_ifaddr_callback(callback, cb_data, addr, mask);
}
free(buffer);
close(sock);
#ifdef HAVE_IPV6
close(sock6);
#endif
return 0;
}
--
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] compile error via SIOCGLIFCONF from ip.c on hpux-11
Merlin Moncure mmonc...@gmail.com writes: How do you test this feature? Try src/tools/ifaddrs/test_ifaddrs.c. I think the only usage in the core code is testing samehost/samenet matches in pg_hba.conf. 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] compile error via SIOCGLIFCONF from ip.c on hpux-11
On Mon, Nov 29, 2010 at 2:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: How do you test this feature? Try src/tools/ifaddrs/test_ifaddrs.c. I think the only usage in the core code is testing samehost/samenet matches in pg_hba.conf. It looks like this is unfortunately more involved. The test of my adjustment above immediately failed with ENOENT, which you have to expect on hpux apparently. According to some googling (see: http://cvsweb.netbsd.org/bsdweb.cgi/src/dist/bind/lib/isc/unix/Attic/ifiter_ioctl.c?rev=1.1.1.4.4.1content-type=text/x-cvsweb-markup and others), the long method returns ip6 addresses and the short method returns ip4 addresses, and you have to do both on hpux always. Forcing the short method, the test worked, I got the loopback and the local address. So we have a couple of options here: hacking the foreach to use the short method on hpux is certainly better situation than we have now, or fixing this 'properly', means refactoring this file a bit and adding a configure test or leaning on an hpux macro. I have no way of testing the long form method I posed above -- does anybody have an ipv6 hpux box? merlin -- 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_execute_from_file review
Itagaki Takahiro itagaki.takah...@gmail.com writes:
I have some comments and questions about
pg_execute_from_file.v5.patch.
I believe v6 fixes it all, please find it attached.
Source code
* OID=3627 is used by another function. Don't you expect 3927?
* There is a compiler warning:
genfile.c: In function ‘pg_execute_from_file_with_placeholders’:
genfile.c:427: warning: unused variable ‘query_string’
Both fixes are in, sorry again.
* I'd like to ask native speakers whether from is needed in names
of pg_execute_from_file and pg_execute_from_query_string.
The function name now is pg_execute_sql_file(), per comments from
Andrew, Joshua, Robert and Tom, all qualified native English speakers,
among other qualities :)
Design and Implementation
* pg_execute_from_file() can execute any files even if they are not
in $PGDATA. OTOH, we restrict pg_read_file() to read such files.
What will be our policy? Note that the contents of file might be
logged or returned to the client on errors.
* Do we have any reasons to have pg_execute_from_file separated from
pg_read_file ? If we allow pg_read_file() to read files in $PGSHARE,
pg_execute_from_file could be replaced with EXECUTE pg_read_file().
(Note that pg_execute_from_file is implemented to do so even now.)
Thinking some more about it, there's still a reason to maintain them
separated: the API ain't the same, we're not proposing to read a sql
script file chunk after chunk, nor do we want users to have to check for
the file size before being able to call the function.
A problem with pg_read_file() as it stands is that it's returning text
rather than bytea, too, and if we choose to fix that rather than adding
some new functions, we will want to avoid having to separate the two
functions again.
* I hope pg_execute_from_file (and pg_read_file) had an option
to specify an character encoding of the file. Especially, SJIS
is still used widely, but it is not a valid server encoding.
So, what about client_encoding here, again?
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 14461,14466 postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
--- 14461,14475
entrytyperecord/type/entry
entryReturn information about a file/entry
/row
+ row
+entry
+ literalfunctionpg_execute_sql_file(parameterfilename/ typetext/
+ [, parametervariable/parameter typetext/type, parametervalue/parameter typetext/type
+ [, ...] ]) )/function/literal
+/entry
+entrytypevoid/type/entry
+entryExecutes the acronymSQL/ commands contained in a file, replacing given placeholders, if any./entry
+ /row
/tbody
/tgroup
/table
***
*** 14499,14504 SELECT (pg_stat_file('filename')).modification;
--- 14508,14529
/programlisting
/para
+indexterm
+ primarypg_execute_sql_file/primary
+/indexterm
+para
+ functionpg_execute_sql_file/ makes the server
+ execute acronymSQL/ commands to be found in a file. This function is
+ reserved to superusers.
+/para
+para
+ The script might contain placeholders that will be replaced by the
+ values given in the literalVARIADIC/literal arguments, which must be
+ a pair of variable names and values. No specific formating is required
+ as far as placeholder names are concerned, so that you can follow your
+ own policies.
+/para
+
para
The functions shown in xref linkend=functions-advisory-locks manage
advisory locks. For details about proper use of these functions, see
***
*** 14521,14526 SELECT (pg_stat_file('filename')).modification;
--- 14546,14552
entrytypevoid/type/entry
entryObtain exclusive advisory lock/entry
/row
+
row
entry
literalfunctionpg_advisory_lock(parameterkey1/ typeint/, parameterkey2/ typeint/)/function/literal
*** a/src/backend/utils/adt/genfile.c
--- b/src/backend/utils/adt/genfile.c
***
*** 7,12
--- 7,13
* Copyright (c) 2004-2010, PostgreSQL Global Development Group
*
* Author: Andreas Pflug pgad...@pse-consulting.de
+ * Dimitri Fontaine dimi...@2ndquadrant.fr
*
* IDENTIFICATION
* src/backend/utils/adt/genfile.c
***
*** 21,31
--- 22,34
#include dirent.h
#include catalog/pg_type.h
+ #include executor/spi.h
#include funcapi.h
#include mb/pg_wchar.h
#include miscadmin.h
#include postmaster/syslogger.h
#include storage/fd.h
+ #include utils/array.h
#include utils/builtins.h
#include utils/memutils.h
#include utils/timestamp.h
***
*** 264,266 pg_ls_dir(PG_FUNCTION_ARGS)
--- 267,440
SRF_RETURN_DONE(funcctx);
}
+
+ /*
+ * Support functions for
Re: [HACKERS] directory archive format for pg_dump
On 29.11.2010 07:11, Joachim Wieland wrote:
On Mon, Nov 22, 2010 at 3:44 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
* wrap long lines
* use extern in function prototypes in header files
* inline some functions like _StartDataCompressor, _EndDataCompressor,
_DoInflate/_DoDeflate that aren't doing anything but call some other
function.
So here is a new round of patches. It turned out that the feature to
allow to also restore files from a different dump and with a different
compression required some changes in the compressor API. And in the
end I didn't like all the #ifdefs either and made a less #ifdef-rich
version using function pointers.
Ok. The separate InitCompressorState() and AllocateCompressorState()
functions seem unnecessary. As the code stands, there's little
performance gain from re-using the same CompressorState, just
re-initializing it, and I can't see any other justification for them either.
I combined those, and the Free/Flush steps, and did a bunch of other
editorializations and cleanups. Here's an updated patch, also available
in my git repository at
git://git.postgresql.org/git/users/heikki/postgres.git, branch
pg_dump-dir. I'm going to continue reviewing this later, tomorrow
hopefully.
The downside now is that I have
created quite a few one-line functions that Heikki doesn't like all
that much, but I assume that they are okay in this case on the grounds
that the public compressor interface is calling the private
implementation of a certain compressor.
You could avoid the wrapper functions by calling the function pointers
directly, but I agree it seems neater the way you did it.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
*** a/src/bin/pg_dump/Makefile
--- b/src/bin/pg_dump/Makefile
***
*** 20,26 override CPPFLAGS := -I$(libpq_srcdir) $(CPPFLAGS)
OBJS= pg_backup_archiver.o pg_backup_db.o pg_backup_custom.o \
pg_backup_files.o pg_backup_null.o pg_backup_tar.o \
! dumputils.o $(WIN32RES)
KEYWRDOBJS = keywords.o kwlookup.o
--- 20,26
OBJS= pg_backup_archiver.o pg_backup_db.o pg_backup_custom.o \
pg_backup_files.o pg_backup_null.o pg_backup_tar.o \
! dumputils.o compress_io.o $(WIN32RES)
KEYWRDOBJS = keywords.o kwlookup.o
*** /dev/null
--- b/src/bin/pg_dump/compress_io.c
***
*** 0
--- 1,415
+ /*-
+ *
+ * compress_io.c
+ * Routines for archivers to write an uncompressed or compressed data
+ * stream.
+ *
+ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/bin/pg_dump/compress_io.c
+ *
+ *-
+ */
+
+ #include compress_io.h
+
+ static const char *modulename = gettext_noop(compress_io);
+
+ /* Routines that are private to a specific compressor (static functions) */
+ #ifdef HAVE_LIBZ
+ /* Routines that support zlib compressed data I/O */
+ static void InitCompressorZlib(CompressorState *cs, int compression);
+ static void DeflateCompressorZlib(ArchiveHandle *AH, CompressorState *cs,
+ bool flush);
+ static void ReadDataFromArchiveZlib(ArchiveHandle *AH, CompressorState *cs);
+ static size_t WriteDataToArchiveZlib(ArchiveHandle *AH, CompressorState *cs,
+ const void *data, size_t dLen);
+ static void EndCompressorZlib(ArchiveHandle *AH, CompressorState *cs);
+ static CompressorState *AllocateCompressorState(CompressorAction action,
+ int compression);
+
+ static CompressorFuncs cfs_zlib = {
+ InitCompressorZlib,
+ ReadDataFromArchiveZlib,
+ WriteDataToArchiveZlib,
+ EndCompressorZlib
+ };
+ #endif
+
+ /* Routines that support uncompressed data I/O */
+ static void InitCompressorNone(CompressorState *cs, int compression);
+ static void ReadDataFromArchiveNone(ArchiveHandle *AH, CompressorState *cs);
+ static size_t WriteDataToArchiveNone(ArchiveHandle *AH, CompressorState *cs,
+ const void *data, size_t dLen);
+ static void EndCompressorNone(ArchiveHandle *AH, CompressorState *cs);
+
+ static CompressorFuncs cfs_none = {
+ InitCompressorNone,
+ ReadDataFromArchiveNone,
+ WriteDataToArchiveNone,
+ EndCompressorNone
+ };
+
+ /* Allocate a new decompressor */
+ CompressorState *
+ AllocateInflator(int compression, ReadFunc readF)
+ {
+ CompressorState *cs;
+
+ cs = AllocateCompressorState(COMPRESSOR_INFLATE, compression);
+ cs-readF = readF;
+
+ return cs;
+ }
+
+ /* Allocate a new compressor */
+ CompressorState *
+ AllocateDeflator(int compression, WriteFunc writeF)
+ {
+ CompressorState *cs;
+
+ cs = AllocateCompressorState(COMPRESSOR_DEFLATE, compression);
+ cs-writeF = writeF;
+
+ return cs;
+ }
+
+ static CompressorState *
+ AllocateCompressorState(CompressorAction action, int compression)
+ {
Re: [HACKERS] SSI using rw-conflict lists
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Maybe invent a short name for PredLockTranList and/or the fields? Done: http://git.postgresql.org/gitweb?p=users/kgrittn/postgres.git;a=commitdiff;h=44c8f221100b87fc7c23425f53f2fd38d735b7d2 I'd suggest a union field. Done: http://git.postgresql.org/gitweb?p=users/kgrittn/postgres.git;a=commitdiff;h=c6194da36186f8b13785cf606cda2a77f4422afc Thanks for the feedback! By the way, the serializable branch on my git repo is back to stable now, if anyone wants to play with it. Before going on to finish the graceful degradation work per previous discussions, I'll try integrating the IDLE IN TRANSACTION cancellation from the current CF to provide feedback from a practical use case. If Andres's patch works in that context, it will allow SSI to provide a guarantee of progress even in the most contrived and pessimal workloads. -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] improving foreign key locks
Excerpts from Florian Pflug's message of vie nov 26 10:48:39 -0300 2010:
To me, the whole thing seems to be special case of allowing to not only lock
whole tuples FOR UPDATE or FOR SHARE, but also individual fields or sets of
fields. Except that for simplicity, only two sets are supported, which are
A) All fields
B) All fields which are included in some unique constraint, including
primary keys.
I'd therefore suggest to extend the FOR SHARE / FOR UPDATE syntax to be
SELECT FOR { SHARE | UPDATE } [ OF table1[.field1], ... ]
and obtain what you call a KEY LOCK if (for a particular table) the set of
fields is a subset of (B). Otherwise, we'd obtain a full SHARE lock. Thus
we'd always lock at least the fields the user told us to, but sometimes more
than those, for the sake of a more efficient implementation.
This would require some additions in ri_FetchConstraintInfo(). Right
now it does a single syscache lookup and then extracts a bunch of
attributes from there. If we're going to implement as you suggest, we'd
have to:
1. add a relcache lookup in there, and extract column names involved in
the FK.
2. store those column names in RI_ConstraintInfo; currently it's about
68 bytes, it'd grow to ~2116 bytes (current size plus RI_MAX_NUMKEYS *
NAMEDATALEN).
Additionally, we'd have to expend some more cycles at the parse analysis
phase (of the FOR SHARE OF x.col1, x.col2 query) to verify that those
columns belong into some non-partial unique index.
Is the performance gain sufficient to pay these costs?
--
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
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] improving foreign key locks
Excerpts from Alvaro Herrera's message of lun nov 29 18:00:55 -0300 2010: This would require some additions in ri_FetchConstraintInfo(). Right now it does a single syscache lookup and then extracts a bunch of attributes from there. If we're going to implement as you suggest, we'd have to: 1. add a relcache lookup in there, and extract column names involved in the FK. 2. store those column names in RI_ConstraintInfo; currently it's about 68 bytes, it'd grow to ~2116 bytes (current size plus RI_MAX_NUMKEYS * NAMEDATALEN). Additionally, we'd have to expend some more cycles at the parse analysis phase (of the FOR SHARE OF x.col1, x.col2 query) to verify that those columns belong into some non-partial unique index. Hmm, actually there's already a relcache lookup when we execute whatever action the FK needs to execute, so maybe we don't need to do any of this. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. 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] Re: [COMMITTERS] pgsql: Remove outdated comments from the regression test files.
On Mon, Nov 29, 2010 at 02:09:29PM -0500, Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: On Mon, Nov 29, 2010 at 16:26, Robert Haas robertmh...@gmail.com wrote: That works for me. �But should we make a practice of writing the ENTIRE SHA-ID rather than an abbreviated form, so that we could more easily replace 'em later if need be? I think that's a good idea. Just as a data point, there is already one 7-hex-digit collision in our repository: Branch: master Release: REL6_1 [aaeef4dae] 1997-04-09 08:29:35 + Branch: master Release: REL6_1 [aaeef4d17] 1996-11-10 03:06:38 + I think it's quite foolish to depend on abbreviated hashes to be unique forever. Good point. While a full-hash collision is of course possible, we have much more likely things that can mess us up than that :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] improving foreign key locks
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Alvaro Herrera's message of lun nov 29 18:00:55 -0300 2010: Additionally, we'd have to expend some more cycles at the parse analysis phase (of the FOR SHARE OF x.col1, x.col2 query) to verify that those columns belong into some non-partial unique index. Hmm, actually there's already a relcache lookup when we execute whatever action the FK needs to execute, so maybe we don't need to do any of this. Checking for existence of a unique index at parse analysis time is quite horrid anyway, because it means the validity of the query can change from parse time to execution time. We got stuck with some of that in relation to GROUP BY dependencies, but I don't want to buy into it anywhere that we're not forced to by the letter of the SQL spec. 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] improving foreign key locks
Excerpts from Tom Lane's message of lun nov 29 18:33:19 -0300 2010: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Alvaro Herrera's message of lun nov 29 18:00:55 -0300 2010: Additionally, we'd have to expend some more cycles at the parse analysis phase (of the FOR SHARE OF x.col1, x.col2 query) to verify that those columns belong into some non-partial unique index. Checking for existence of a unique index at parse analysis time is quite horrid anyway, because it means the validity of the query can change from parse time to execution time. We got stuck with some of that in relation to GROUP BY dependencies, but I don't want to buy into it anywhere that we're not forced to by the letter of the SQL spec. Hmm. Are you less opposed to the idea of some new nonstandard syntax in the locking clause, then? I propose SELECT FOR KEY LOCK, though anything that the RI code could use would be the same to me, of course. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. 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] [PATCH] V3: Idle in transaction cancellation
Andres Freund and...@anarazel.de wrote: Ok, I implemented that capability I applied all three patches with minor offsets, and it builds, but several regression tests fail. I backed out the patches in reverse order and confirmed that while the regression tests pass without any of these patches, they fail with just the first, the first and the second, or all three patches. If you're not seeing the same thing there, I'll be happy to provide the details. -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] V3: Idle in transaction cancellation
I wrote: I applied all three patches with minor offsets, and it builds, but several regression tests fail. Sorry, after sending that I realized I hadn't done a make distclean. After that it passes. Please ignore the previous post. -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] pg_execute_from_file review
Excerpts from Dimitri Fontaine's message of lun nov 29 17:03:06 -0300 2010: Itagaki Takahiro itagaki.takah...@gmail.com writes: * I hope pg_execute_from_file (and pg_read_file) had an option to specify an character encoding of the file. Especially, SJIS is still used widely, but it is not a valid server encoding. So, what about client_encoding here, again? I tried this in an earlier iteration of this patch, and it works fine (albeit with a Latin1 file in an UTF8 encoded database, but presumably this is no different from any other pair of client/server encodings; recoding took place correctly during execution). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. 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] dblink versus long connection strings
I have views that use the dblink(connStr text, sql text) call. They cannot use a two-step process. So postgres 9.0 has broken all of those views. Is there a straightforward solution to this? -- View this message in context: http://postgresql.1045698.n5.nabble.com/dblink-versus-long-connection-strings-tp3275575p3284620.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] profiling connection overhead
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Nov 27, 2010 at 11:18 PM, Bruce Momjian br...@momjian.us wrote: Not sure that information moves us forward. ?If the postmaster cleared the memory, we would have COW in the child and probably be even slower. Well, we can determine the answers to these questions empirically. Not really. Per Bruce's description, a page would become COW the moment the postmaster touched (either write or read) any variable on it. Since we have no control over how the loader lays out static variables, the actual behavior of a particular build would be pretty random and subject to unexpected changes caused by seemingly unrelated edits. I believe all linkers will put initialized data (data segment) before unitialized data (bss segment): http://en.wikipedia.org/wiki/Data_segment The only question is whether the linker has data and bss sharing the same VM page (4k), or whether a new VM page is used when starting the bss segment. Also, the referenced URL only purports to describe the behavior of HPUX, which is not exactly a mainstream OS. I think it requires a considerable leap of faith to assume that all or even most platforms work the way this suggests, and not in the dumber fashion Andres suggested. Has anybody here actually looked at the relevant Linux or BSD kernel code? I have years ago, but not recently. You can see the sections on Linux via objdump: $ objdump --headers /bin/ls /bin/ls: file format elf32-i386 Sections: Idx Name Size VMA LMA File off Algn ... 24 .data 012c 080611a0 080611a0 000191a0 2**5 CONTENTS, ALLOC, LOAD, DATA 25 .bss 0c40 080612e0 080612e0 000192cc 2**5 ALLOC Based on this output, a new 4k page is not started for the 'bss' segment. It basically uses 32-byte alignment. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] profiling connection overhead
Robert Haas wrote: In a close race, I don't think we should get bogged down in micro-optimization here, both because micro-optimizations may not gain much and because what works well on one platform may not do much at all on another. The more general issue here is what to do about our high backend startup costs. Beyond trying to recycle backends for new connections, as I've previous proposed and with all the problems it entails, the only thing that looks promising here is to try to somehow cut down on the cost of populating the catcache and relcache, not that I have a very clear idea how to do that. This has to be a soluble problem because other people have solved it. To some degree we're a victim of our own flexible and extensible architecture here, but I find it pretty unsatisfying to just say, OK, well, we're slow. Combining your last two sentences, I am not sure anyone with the flexibility we have has solved the cache populating problem. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] profiling connection overhead
Tom Lane wrote: BTW, this might be premature to mention pending some tests about mapping versus zeroing overhead, but it strikes me that there's more than one way to skin a cat. I still think the idea of statically allocated space sucks. But what if we rearranged things so that palloc0 doesn't consist of palloc-then-memset, but rather push the zeroing responsibility down into the allocator? In particular, I'm imagining that palloc0 with a sufficiently large space request --- more than a couple pages --- could somehow arrange to get space that's guaranteed zero already. And if the request isn't large, zeroing it isn't where our problem is anyhow. The most portable way to do that would be to use calloc insted of malloc, and hope that libc is smart enough to provide freshly-mapped space. It would be good to look and see whether glibc actually does so, of course. If not we might end up having to mess with sbrk for ourselves, and I'm not sure how pleasantly that interacts with malloc. Yes, I was going to suggest trying calloc(), either because we can get already-zeroed sbrk() memory, or because libc uses assembly language for zeroing memory, as some good libc's do. I know most kernels also use assembly for zeroing memory. Another question that would be worth asking here is whether the hand-baked MemSet macro still outruns memset on modern architectures. I think it's been quite a few years since that was last tested. Yes, MemSet was found to be faster than calling a C function, but new testing is certainly warranted. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] profiling connection overhead
Robert Haas wrote: On Sun, Nov 28, 2010 at 7:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: One possible way to get a real speedup here would be to look for ways to trim the number of catcaches. BTW, it's not going to help to remove catcaches that have a small initial size, as the pg_am cache certainly does. ?If the bucket zeroing cost is really something to minimize, it's only the caches with the largest nbuckets counts that are worth considering --- and we certainly can't remove those without penalty. Yeah, very true. What's a bit frustrating about the whole thing is that we spend a lot of time pulling data into the caches that's basically static and never likely to change anywhere, ever. I bet the number of people for whom (int4, int4) has any non-standard properties is somewhere between slim and none; and it might well be the case that formrdesc() is faster than reading the relcache init file, if we didn't need to worry about deviation from canonical. This is even more frustrating in the hypothetical situation where a backend can switch databases, because we have to blow away all of these cache entries that are 99.9% likely to be basically identical in the old and new databases. It is very tempting to look at optimizations here, but I am worried we might head down the flat-files solution that caused continual problems in the past. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] DELETE with LIMIT (or my first hack)
Hi, frequently i have accidents with DELETE/UPDATE commands. In fact, sometimes in the last 8 or 9 years (ok, a lot of times) I forget the entire WHERE clause or have a “not so perfectly“ WHERE clause, with an awful suprise. There’s no words to figure the horror ever time i see that the number of affected rows its not 1 or two how expected, but the entire table. So I planned to make a hack to make the “LIMIT” directive available to “DELETE” command. So, can anyone help-me in how to do this ? This its my plan: 1) change the lex grammar (wheres the file ?) 2) change the parser to accept the new grammar 3) change the executor to stop after “n” successful iterations. Is this correct ? Greets, -- Daniel Loureiro -- http://diffcoder.blogspot.com/
Re: [HACKERS] dblink versus long connection strings
On Tue, Nov 30, 2010 at 01:01, queej d...@authentrics.com wrote: I have views that use the dblink(connStr text, sql text) call. They cannot use a two-step process. So postgres 9.0 has broken all of those views. Is there a straightforward solution to this? Could you explain your views? I cannot get any warnings from dblink(connStr text, sql text) with long connStr. Also, I wonder two things: * dblink(connStr text, sql text) never raises warning logs even without the recent fix, because they don't register connection names. * Connection names could be truncated, but connection strings are never truncated. I'm not sure why connection strings are logged in your log. -- Itagaki Takahiro -- 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] DELETE with LIMIT (or my first hack)
On Mon, Nov 29, 2010 at 9:08 PM, Daniel Loureiro loureir...@gmail.com wrote: 3) change the executor to stop after “n” successful iterations. Is this correct ? no. it means you will delete the n first tuples that happen to be found, if you don't have a WHERE clause that means is very possible you delete something you don't want to... the correct solution is to use always try DELETE's inside transactions and only if you see the right thing happening issue a COMMIT besides i think this has been proposed and rejected before -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] [GENERAL] column-level update privs + lock table
On Mon, Nov 29, 2010 at 10:50 AM, Robert Haas robertmh...@gmail.com wrote: A user with single-column UPDATE privileges could obtain a ROW EXCLUSIVE lock by issuing an UPDATE statement, but currently cannot obtain the same lock using LOCK TABLE. It would be reasonable and consistent to allow such a user to take a ROW SHARE or ROW EXCLUSIVE lock using LOCK TABLE, but I'm not sure what the use case for that would be. Those limited privileges wouldn't be very useful for my purposes, at least. I'll try to explain my use-case below. It seems to me that if we're really worried about which locks users are allowed to take (and so far all of the worrying seems to lack a solid basis in any sort of usability argument) we'd need to invent some special-purpose permissions, perhaps one for each lock level. OK, so here's why I wanted column-level update + lock table privileges. I put together a database application related to table-synchronization -- basically performing remote table comparisons. This application needed to update only a single column in the source table (an updated timestamp), but it needed to be certain that the source table wasn't changing underneath it. I ended up just assigning full-table UPDATE privileges to this user, despite knowing that it only needed to update a single column. I would have liked to make this privilege restriction explicit in the database schema, but I can't. And we might also want custom permissions for ANALYZE and VACUUM and each subcommand of ALTER TABLE. The question is, how much of that has any real benefit? It's probably uncommon to want to dole out such fine-grained permissions, and our current permissions-granting infrastructure tops out at 16 individual permissions, so it would need some rework - particularly, to minimize slowdown of the common case where you DON'T care about any of these fiddly ultra-fine-grained user rights. For LOCK TABLE (or ANALYZE), it appears to be simple to allow users to lock the table in any mode you like by providing an appropriate SECURITY DEFINER function. So I think if people want a user who can update a single column of the table and also take an AccessExclusiveLock we can just recommend that they do it that way. I actually hadn't thought of that, for some reason. We used to similarly recommend that people handle TRUNCATE privileges with a security definer function. That doesn't mean GRANT TRUNCATE wasn't a sweet addition to 8.4. This also works for ANALYZE. If you need a user who doesn't own a table to be able to VACUUM it, that's a bit trickier because VACUUM can only be invoked as a top-level command, not from within a function or already-open transaction. Perhaps we can fix this some day if we implement autonomous transactions, but for now it doesn't really seem worth losing a lot of sleep over. Just my opinion, of course... Do we need to answer: Yes, it is a specification, so you need to grant table level privileges, instead? I think that's the most reasonable answer. My vote is to just update the LOCK TABLE documentation to be more precise about what the rules are, and move on. I still see little reason to make LOCK TABLE permissions different for column-level vs. table-level UPDATE privileges, but oh well. Josh -- 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] DELETE with LIMIT (or my first hack)
good point. But when you use a LIMIT in a SELECT statement you WANT n RANDOM tuples - its wrong to get RANDOM tuples ? So, in the same logic, its wrong to exclude n random tuples ? Besides, if you want DELETE just 1 tuple, why the executor have to scan the entire table, and not just stoping after find the 1 tuple ? Why the LIMIT clause should be used to speedup only SELECT statements ? if the programmer know the expected number of affected rows why not use it to speed up DELETE/UPDATE ? cheers, -- Daniel Loureiro http://diffcoder.blogspot.com/ 2010/11/30 Jaime Casanova ja...@2ndquadrant.com On Mon, Nov 29, 2010 at 9:08 PM, Daniel Loureiro loureir...@gmail.com wrote: 3) change the executor to stop after “n” successful iterations. Is this correct ? no. it means you will delete the n first tuples that happen to be found, if you don't have a WHERE clause that means is very possible you delete something you don't want to... the correct solution is to use always try DELETE's inside transactions and only if you see the right thing happening issue a COMMIT besides i think this has been proposed and rejected before -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL
Re: [HACKERS] ALTER TABLE ... IF EXISTS feature?
Yes, thanks, those are reasonable goals. --- Daniel Farina wrote: On Wed, Nov 24, 2010 at 7:21 PM, Bruce Momjian br...@momjian.us wrote: What are we adding a pl/pgsql dependency for? ?What is the benefit that will warrant requiring people who disable plpgsql to enable it for restores? There are two use cases I want to cover: 1) It should be possible to restore a dump made with --clean on an empty database without error, so it can be run in a transaction and the error code can be usefully monitored. 2) It should be possible a database be dumped and restored by a non-superuser, again, cleanly, as per 1. It was easy enough to change all the DROP ... statements to DROP ... IF EXISTS, but the ALTER statements have no equivalent, and thus the only way for a dump created with --clean to run without error is to ensure that all table and domain constraints exist prior to restore. The obvious mechanisms that have come to mind in this thread are: * An IF EXISTS variant of ALTER, at least for TABLE and DOMAIN (although it may be strange to only support it on a couple of types) * Use of anonymous-DO code blocks (the prototype uses this, and this depends on plpgsql) * Bizarre things I can imagine doing that involve creative queries that, as a side effect, might drop objects that I have not mentioned because I thought they were too gross to be given serious consideration. But it might be plpgsql-less, which would be nice. Note that in the case where one wants to dump/restore as a non-superuser that one may not be in a position to conveniently do a (DROP|CREATE) DATABASE statement to work around the problem. -- fdr -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [GENERAL] column-level update privs + lock table
On Mon, Nov 29, 2010 at 9:37 PM, Josh Kupershmidt schmi...@gmail.com wrote: It seems to me that if we're really worried about which locks users are allowed to take (and so far all of the worrying seems to lack a solid basis in any sort of usability argument) we'd need to invent some special-purpose permissions, perhaps one for each lock level. OK, so here's why I wanted column-level update + lock table privileges. I put together a database application related to table-synchronization -- basically performing remote table comparisons. This application needed to update only a single column in the source table (an updated timestamp), but it needed to be certain that the source table wasn't changing underneath it. Reasonable... but it doesn't seem unimaginable that someone could want the opposite behavior, either, for the reasons I stated upthread. And we might also want custom permissions for ANALYZE and VACUUM and each subcommand of ALTER TABLE. The question is, how much of that has any real benefit? It's probably uncommon to want to dole out such fine-grained permissions, and our current permissions-granting infrastructure tops out at 16 individual permissions, so it would need some rework - particularly, to minimize slowdown of the common case where you DON'T care about any of these fiddly ultra-fine-grained user rights. For LOCK TABLE (or ANALYZE), it appears to be simple to allow users to lock the table in any mode you like by providing an appropriate SECURITY DEFINER function. So I think if people want a user who can update a single column of the table and also take an AccessExclusiveLock we can just recommend that they do it that way. I actually hadn't thought of that, for some reason. We used to similarly recommend that people handle TRUNCATE privileges with a security definer function. That doesn't mean GRANT TRUNCATE wasn't a sweet addition to 8.4. Hmm, glad you like it (I wrote that). I'm just asking how far we should go before we decide we catering to use cases that are too narrow to warrant an extension of the permissions system. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] DELETE with LIMIT (or my first hack)
On Mon, Nov 29, 2010 at 9:55 PM, Daniel Loureiro loureir...@gmail.com wrote: good point. But when you use a LIMIT in a SELECT statement you WANT n RANDOM tuples no. at least IMHO the only sensible way that LIMIT is usefull is with an ORDER BY clause with make the results very well defined... -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] DELETE with LIMIT (or my first hack)
On Mon, Nov 29, 2010 at 9:08 PM, Daniel Loureiro loureir...@gmail.com wrote: frequently i have accidents with DELETE/UPDATE commands. In fact, sometimes in the last 8 or 9 years (ok, a lot of times) I forget the entire WHERE clause or have a “not so perfectly“ WHERE clause, with an awful suprise. There’s no words to figure the horror ever time i see that the number of affected rows its not 1 or two how expected, but the entire table. So I planned to make a hack to make the “LIMIT” directive available to “DELETE” command. So, can anyone help-me in how to do this ? This its my plan: 1) change the lex grammar (wheres the file ?) 2) change the parser to accept the new grammar 3) change the executor to stop after “n” successful iterations. Is this correct ? I don't think your use case sounds very compelling - as Jaime says, you could still easily blow away data that you have no easy way to get back - but I agree that DELETE (or UPDATE) is useful in combination with LIMIT. For example, suppose you want to roll your own replication solution for a table with no primary key. So you set up some triggers. Whenever you see an INSERT on the source table, you do a matching INSERT on the target table. When you see a DELETE on the source table, you do a DELETE on the target table that constrains all the columns to be equal and also includes LIMIT 1. Similarly for UPDATE. Then, your boss gives you a big raise and commends you for your awesome programming skills. Woot! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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_execute_from_file review
On Tue, Nov 30, 2010 at 05:03, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
I believe v6 fixes it all, please find it attached.
Design and Implementation
* pg_execute_from_file() can execute any files even if they are not
in $PGDATA. OTOH, we restrict pg_read_file() to read such files.
What will be our policy? Note that the contents of file might be
logged or returned to the client on errors.
* Do we have any reasons to have pg_execute_from_file separated from
pg_read_file ? If we allow pg_read_file() to read files in $PGSHARE,
pg_execute_from_file could be replaced with EXECUTE pg_read_file().
(Note that pg_execute_from_file is implemented to do so even now.)
Thinking some more about it, there's still a reason to maintain them
separated: the API ain't the same, we're not proposing to read a sql
script file chunk after chunk, nor do we want users to have to check for
the file size before being able to call the function.
A problem with pg_read_file() as it stands is that it's returning text
rather than bytea, too, and if we choose to fix that rather than adding
some new functions, we will want to avoid having to separate the two
functions again.
I think there are two topics here:
1. Do we need to restrict locations in which sql files are executable?
2. Which is better, pg_execute_sql_file() or EXECUTE pg_read_file() ?
There are no discussion yet for 1, but I think we need some restrictions
anyway. If we will be conservative, we would allow only files in $PGSHARE
or $PGSHARE/contrib. More aggressive approach might be something like
CREATE DIRECTORY command in Oracle Database:
http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/statements_5007.htm
For 2, I'd like to monofunctionalize pg_execute_sql_file() into
separated functions something like:
- FUNCTION pg_execute_sql(sql text)
- FUNCTION replace(str text, from1 text, to1 text, VARIADIC text)
- FUNCTION pg_read_binary_file(path text, offset bigint, size bigint)
(size == -1 means whole-file)
pg_read_binary_file() is the most important functions probably.
pg_execute_sql_file() can be rewritten as follows. We can also use
existing convert_from() to support encodings.
SELECT pg_execute_sql(
replace(
convert_from(
pg_read_binary_file('path', 0, -1),
'encoding'),
'key1', 'value1', 'key2', 'value2')
);
--
Itagaki Takahiro
--
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] DELETE with LIMIT (or my first hack)
On Mon, Nov 29, 2010 at 10:09 PM, Jaime Casanova ja...@2ndquadrant.com wrote: On Mon, Nov 29, 2010 at 9:55 PM, Daniel Loureiro loureir...@gmail.com wrote: good point. But when you use a LIMIT in a SELECT statement you WANT n RANDOM tuples no. at least IMHO the only sensible way that LIMIT is usefull is with an ORDER BY clause with make the results very well defined... That's not 100% true - it can sometimes be very useful when digging through a database to grab 50 rows from a table just to get a feel for what kind of stuff in there. Maybe it's stupid, but I find it handy. But even granting the premise, that's an argument for making DELETE support both ORDER BY and LIMIT, not for supporting neither of them. For example, suppose we're trying to govern an ancient Greek democracy: http://en.wikipedia.org/wiki/Ostracism DELETE FROM residents_of_athens ORDER BY ostracism_votes DESC LIMIT 1; I think the executor already pretty much knows how to do this. The planner might need some fiddling to hand over the correct instructions, not sure. But this might not even be super hard, though Daniel might want to pick something a little less ambitious for his very first project, because debugging planner and executor problems is not so easy. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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_execute_from_file review
On Tue, Nov 30, 2010 at 08:56, Alvaro Herrera alvhe...@commandprompt.com wrote: * I hope pg_execute_from_file (and pg_read_file) had an option to specify an character encoding of the file. Especially, SJIS is still used widely, but it is not a valid server encoding. So, what about client_encoding here, again? I tried this in an earlier iteration of this patch, and it works fine (albeit with a Latin1 file in an UTF8 encoded database, but presumably this is no different from any other pair of client/server encodings; recoding took place correctly during execution). client_encoding won't work at all because read_sql_queries_from_file() uses pg_verifymbstr(), that is verify the input with *server_encoding*. Even if we replace it with pg_verify_mbstr(client_encoding, ...) and pg_do_encoding_conversion(from client_encoding to server_encoding), it still won't work well when error messages are raised. The client expects the original client encoding, but messages are sent in the file encoding. It would be a security hole. -- Itagaki Takahiro -- 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] profiling connection overhead
Greg Stark wrote: On Mon, Nov 29, 2010 at 12:33 AM, Tom Lane t...@sss.pgh.pa.us wrote: The most portable way to do that would be to use calloc insted of malloc, and hope that libc is smart enough to provide freshly-mapped space. It would be good to look and see whether glibc actually does so, of course. ?If not we might end up having to mess with sbrk for ourselves, and I'm not sure how pleasantly that interacts with malloc. It's *supposed* to interact fine. The only thing I wonder is that I think malloc intentionally uses mmap for larger allocations but I'm not clear what the advantages are. Is it because it's a cheaper way to get zeroed bytes? Or just so that free has a hope of returning the allocations to the OS? Using mmap() so you can return large allocations to the OS is a neat trick, certainly. I am not sure who implements that. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] DELETE with LIMIT (or my first hack)
On 11/29/2010 10:19 PM, Robert Haas wrote: For example, suppose we're trying to govern an ancient Greek democracy: http://en.wikipedia.org/wiki/Ostracism DELETE FROM residents_of_athens ORDER BY ostracism_votes DESC LIMIT 1; I'm not sure this is a very good example. Assuming there isn't a tie, I'd do it like this: DELETE FROM residents_of_athens WHERE ostracism_votes = 6000 and ostracism_votes = (SELECT max(ostracism_votes) FROM residents_of_athens); I can't say I'd be excited by this feature. In quite a few years of writing SQL I don't recall ever wanting such a gadget. cheers andrew
[HACKERS] crash-safe visibility map, take three
Last week, I posted a couple of possible designs for making the visibility map crash-safe, which did not elicit much comment. Since this is an important prerequisite to index-only scans, I'm trying again. http://archives.postgresql.org/pgsql-hackers/2010-11/msg01474.php http://archives.postgresql.org/pgsql-hackers/2010-11/msg01529.php Here's a third possible design. Instead of representing each page with a single bit in the visibility map, use two bits. One bit indicates whether all tuples on the page are all-visible (same as the current bit) - call this VM_ALL_VISIBLE. The other bit is only used during VACUUM and indicates whether VACUUM is trying to set the all-visible bit - call this VM_BECOMING_ALL_VISIBLE. We observe the rule that any operation that clears PD_ALL_VISIBLE must clear both the VM_ALL_VISIBLE and VM_BECOMING_ALL_VISIBLE bits for that page in the visibility map. VACUUM precedes as follows: 1. Pin each visibility map page. If any VM_BECOMING_ALL_VISIBLE bits are set, take the exclusive content lock for long enough to clear them. 2. Scan the heap. When a page is observed to be all-visible, set VM_BECOMING_ALL_VISIBLE and PD_ALL_VISIBLE. 3. Loop over shared buffers and write out every page to the OS which belongs to the target relation, was marked all-visible in step 2, and is still dirty. Note that this may require a WAL flush. 4. fsync() the heap. 5. Pin each visibility map page. If any VM_BECOMING_ALL_VISIBLE bits are set, take the exclusive content lock, clear each such bit, set the corresponding VM_ALL_VISIBLE bits and XLOG the page. One might actually want to do steps 2-5 incrementally, in 1GB chunks, so that you don't fsync() too much of the relation all at once. If you tilt your head just right, the recurring problem in all of this is that the heap page and the visibility map page can go to disk in either order, and we have no way of knowing which one. A more radical solution to this problem (and, thus, a fourth possible design) would be to add a field to the buffer descriptor allowing one page to wire another page into shared buffers. If the value is 0, it's the number of a buffer it's currently wiring. If the value is 0, it's the number of other buffers that have wired this buffer. A buffer both wire another buffer and itself be wired at the same time. If the value is =0, everything's normal. To make this work, though, you'd have to complicate the checkpoint logic pretty considerably - make sure all the unwired buffers are written and fsync'd first, thus unwiring the remaining ones to be written and fsync'd in a second pass; and there are also possible problems with very small relations, where the number of wired buffers might grow to an uncomfortably high percentage of the total. Waving my hands at all this complexity, you could then make the heap pages wire the visibility map pages. I can't say I'm totally in love with any of these designs. Anyone else have any ideas, or any opinions about which one is best? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] DELETE with LIMIT (or my first hack)
On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan and...@dunslane.net wrote: On 11/29/2010 10:19 PM, Robert Haas wrote: For example, suppose we're trying to govern an ancient Greek democracy: http://en.wikipedia.org/wiki/Ostracism DELETE FROM residents_of_athens ORDER BY ostracism_votes DESC LIMIT 1; I'm not sure this is a very good example. Assuming there isn't a tie, I'd do it like this: DELETE FROM residents_of_athens WHERE ostracism_votes = 6000 and ostracism_votes = (SELECT max(ostracism_votes) FROM residents_of_athens); That might be a lot less efficient, though, and sometimes it's not OK to delete more than one record. Imagine, for example, wanting to dequeue the work item with the highest priority. Sure, you can use SELECT ... LIMIT to identify one and then DELETE it by some other key, but DELETE .. ORDER BY .. RETURNING .. LIMIT would be cool, and would let you do it with just one scan. I can't say I'd be excited by this feature. In quite a few years of writing SQL I don't recall ever wanting such a gadget. It's something I've wanted periodically, though not badly enough to do the work to make it happen. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] DELETE with LIMIT (or my first hack)
On Tue, Nov 30, 2010 at 05:09, Jaime Casanova ja...@2ndquadrant.com wrote: at least IMHO the only sensible way that LIMIT is usefull is with an ORDER BY clause with make the results very well defined... DELETE with LIMIT is also useful for deleting things in batches, so you can do large deletes on a live system without starving other users from I/O. In this case deletion order doesn't matter (it's more efficient to delete rows in physical table order) -- ORDER BY isn't necessary. Regards, Marti -- 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] profiling connection overhead
On Monday 29 November 2010 19:10:07 Tom Lane wrote: Jeff Janes jeff.ja...@gmail.com writes: Are you sure you haven't just moved the page-fault time to a part of the code where it still exists, but just isn't being captured and reported? I'm a bit suspicious about that too. Another thing to keep in mind is that Robert's original program doesn't guarantee that the char array is maxaligned; though reasonable implementations of memset should be able to use the same inner loop anyway for most of the array. Yes, I measured the time including mmap itself. I don't find it surprising its taking measurably shorter as it can just put up the mappings without explicitly faulting for each and every page. The benefit is too small to worry though, so ... The answer to Robert includes the timings + test program. Andres -- 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] crash-safe visibility map, take three
On 30.11.2010 06:57, Robert Haas wrote: I can't say I'm totally in love with any of these designs. Anyone else have any ideas, or any opinions about which one is best? Well, the design I've been pondering goes like this: At vacuum: 1. Write an intent XLOG record listing a chunk of visibility map bits that are not currently set, that we are going to try to set. A chunk of say 100 bits would be about right. 2. Scan the 100 heap pages as we currently do, setting the visibility map bits as we go. 3. After the scan, lock the visibility map page, check which of the bits that we set in step 2 are still set (concurrent updates might've cleared some), and write a final XLOG record listing the set bits. This step isn't necessary for correctness, BTW, but without it you lose all the set bits if you crash before next checkpoint. At replay, when we see the intent XLOG record, clear all the bits listed in it. This ensures that if we crashed and some of the visibility map bits were flushed to disk but the corresponding changes to the heap pages were not, the bits are cleared. When we see the final XLOG record, we set the bits. Some care is needed with checkpoints. Setting visibility map bits in step 2 is safe because crash recovery will replay the intent XLOG record and clear any incorrectly set bits. But if a checkpoint has happened after the intent XLOG record was written, that's not true. This can be avoided by checking RedoRecPtr in step 2, and writing a new intent XLOG record if it has changed since the last intent XLOG record was written. There's a small race condition in the way a visibility map bit is currently cleared. When a heap page is updated, it is locked, the update is WAL-logged, and the lock is released. The visibility map page is updated only after that. If the final vacuum XLOG record is written just after updating the heap page, but before the visibility map bit is cleared, replaying the final XLOG record will set a bit that should not have been set. -- 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
