Re: [HACKERS] GRANT ON ALL IN schema
Hi, Nikhil, This is still flagged as Needs Review. Are you still reviewing the latest version, or should this be set to ready for committer, or what? The review is complete from my side. There is this question about consistency between this patch and the Defaultacls patch. But am ok with this patch on its own. So ready for committer from my side. Regards, Nikhils -- 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] Review: Revise parallel pg_restore's scheduling heuristic
Kevin, It would be hard to schedule the requisite time on our biggest web machines, but I assume an 8 core 64GB machine would give meaningful results. Any sense what numbers of parallel jobs I should use for tests? I would be tempted to try 1 (with the -1 switch), 8, 12, and 16 -- maybe keep going if 16 beats 12. Personally, I wouldn't go over the number of cores. But if you do find some gain that way, I'd be very interested to know it. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] Lock Wait Statistics (next commitfest)
On Fri, Jul 17, 2009 at 3:38 AM, Mark Kirkwoodmar...@paradise.net.nz wrote: With respect to the sum of wait times being not very granular, yes - quite true. I was thinking it is useful to be able to answer the question 'where is my wait time being spent' - but it hides cases like the one you mention. What would you like to see? would max and min wait times be a useful addition, or are you thinking along different lines? track number of locks, sum of wait times, max(wait time). but actually i started to think that the best is just make use of log_lock_waits send the logs to csvlog and analyze there... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Higher TOAST compression.
On Fri, Jul 17, 2009 at 11:10 PM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: Laurent Laborde kerdez...@gmail.com wrote: What about SET STORAGE MAIN then ? To prevent out-of-line storage ? Well, that doesn't try as hard as you might think to keep from storing data out-of-line. It uses the same threshold as the default EXTENDED storage, but saves the out-of-line option for such columns as the last thing to try to get it within the threshold. It is because I wrote a very small patch to address that issue that I jumped in on your issue. If you wanted to try my patch here: http://archives.postgresql.org/message-id/4a363853022500027...@gw.wicourts.gov you could adjust both TOAST_TUPLES_PER_PAGE_MAIN and TOAST_TUPLES_PER_PAGE to suit your needs and set storage for columns to MAIN as needed. Be very cautious if you try this, because this patch has not yet been reviewed or accepted. I'll take a look at it, compile, and try that. (on a test server). Thank you :) -- Laurent Laborde Sysadmin @ http://www.over-blog.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 1/2 v3] [libpq] rework sigpipe-handling macros
Hi Tom, That code is not broken as it stands, and doesn't appear to really gain anything from the proposed change. Why should we risk any portability questions when the code isn't going to get either simpler or shorter? OK, after attempting a macro version of this, we end up with: #define DECLARE_SIGPIPE_INFO(var) struct sigpipe_info var; #define DISABLE_SIGPIPE(info, conn) \ ((SIGPIPE_MASKED(conn)) ? 0 : \ ((info)-got_epipe = false, \ pg_block_sigpipe((info)-oldsigmask, (info)-sigpipe_pending)) 0) - kinda ugly, uses the ?: and , operators to return the result of pg_block_sigpipe. We could avoid the comma with a block though. If we want to keep the current 'failaction' style of macro: #define DISABLE_SIGPIPE(info, conn, failaction) \ do { \ if (!SIGPIPE_MASKED(conn)) { \ (info)-got_epipe = false; \ if (pg_block_sigpipe((info)-oldsigmask, \ (info)-sigpipe_pending)) 0) { \ failaction; \ } \ } \ } while (0) We could ditch struct sigpipe info, but that means we need to declare three separate vars in DECLARE_SIGPIPE_INFO() instead of the one, and it doesn't clean up the macro code much. I'd rather reduce the amount of stuff that we declare behind the caller's back. Compared to the static-function version: static inline int disable_sigpipe(PGconn *conn, struct sigpipe_info *info) { if (sigpipe_masked(conn)) return 0; info-got_epipe = false; return pq_block_sigpipe(info-oldsigmask, info-sigpipe_pending) 0; } Personally, I think the static functions are a lot cleaner, and don't think we lose any portability from using these (since inline is #define- ed out on compilers that don't support it). On non-inlining compilers, we do gain an extra function call, but we're about to enter the kernel anyway, so this will probably be lost in the noise (especially if we save the sigpipe-masking syscalls). But in the end, it's not up to me - do you still prefer the macro approach? Cheers, Jeremy -- 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] WIP: Deferrable unique constraints
2009/7/20 Alvaro Herrera alvhe...@commandprompt.com: Dean Rasheed wrote: Thanks for the thorough review. I attach an new version of the patch, updated to HEAD, and with the index AM change discussed. Wow, this is a large patch. I didn't do a thorough review, but some quickies I noticed: * Please move the code that says that it should be in a new file to a new file. Ah yes, I forgot about that. Will do. utils/adt/constraints.c ? I'm thinking other deferred constraints (eg. CHECK) might get added to it one day. * Please don't mimic the silliness of RI_FKey_check of an unused return value. Just make it return void OK. and have the caller use the proper PG_RETURN_FOO macro. (Unless there's a specific reason to not do things that way) It looks like I can't use PG_RETURN_NULL() because that will break the trigger calling code, but I could use PG_RETURN_VOID(). But is that the recommended style? Section 35.4 of the docs suggests using PointerGetDatum(NULL). * I'm not sure about the changes in trigger.h and related elsewhere. Seems related to the new list in AfterTriggerSaveEvent, which is used in ways that seem to conflict with its header comment ... I wonder if we should be doing that in the executor itself instead. Yes I suppose the comment is a bit misleading. I put the check there because that's where the similar RI checks are, which already conflict with the header comment. The simplest solution is to just update the comment. This seemed like the least intrusive way to do this. It is also called from copy.c which duplicates some of the executor code. In any case it's inconsistent that the list is added to ExecARInsertTriggers but not to ExecARUpdateTrigger et al ... Yes it is added to ExecARUpdateTriggers(). - Dean -- 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] navigation menu for documents
On Monday 20 July 2009 02:32:01 Alvaro Herrera wrote: Peter Eisentraut wrote: On Friday 17 July 2009 15:58:27 Richard Huxton wrote: 1. Fixed navigation 2. Titles on navigation links. Yes, the DSSSL stylesheet could do that. Since it seems we can get both of these without interfering with anything else I vote +1 on getting them it. For some historical, arbitrary reason the header and footer links are different: (Header:) Prev Fast Backward Fast Forward Next (Footer:) Prev Home Next Title of Prev Title of Next I would like to combine all these elements in header and footer, so they both say: Prev Fast Backward Home Fast Forward Next TitleP TitleFBTitleFFTitleN I think this would be much more usable than just the tooltips. -- 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] Docbook toolchain interfering with patch review?
On Friday 17 July 2009 03:51:31 Alvaro Herrera wrote: Nowadays the doc building process has been sped up inmensely by Peter's recent changes. And my machine has sped up too, as well. Also, for the let's switch to XML crowd: $ make html make html 62.50s user 0.37s system 98% cpu 1:03.72 total $ make xslthtml make xslthtml 809.50s user 0.90s system 99% cpu 13:31.98 total I'm a bit shocked and outraged about that myself. -- 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] WIP: Deferrable unique constraints
Dean Rasheed dean.a.rash...@googlemail.com writes: * Please move the code that says that it should be in a new file to a new file. Ah yes, I forgot about that. Will do. utils/adt/constraints.c ? Please, no. Constraints are not a datatype. Do not copy the brain-dead decision that put ri_triggers there. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
On Mon, Jul 20, 2009 at 10:21:53AM -0400, Tom Lane wrote: Dean Rasheed dean.a.rash...@googlemail.com writes: * Please move the code that says that it should be in a new file to a �new file. Ah yes, I forgot about that. Will do. utils/adt/constraints.c ? Please, no. Constraints are not a datatype. Do not copy the brain-dead decision that put ri_triggers there. Does that mean ri_triggers should come out of there? 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 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] [PATCH] SE-PgSQL/tiny rev.2193
Robert Haas wrote: On Sat, Jul 18, 2009 at 7:10 AM, Martijn van Oosterhoutklep...@svana.org wrote: On Fri, Jul 17, 2009 at 03:59:29PM +0300, Peter Eisentraut wrote: I'm starting to think that there's just no hope of this matching up well enough with the way PostgreSQL already works to have a chance of being accepted. What I'm understanding here is the apparent requirement that the SEPostgreSQL implementation be done in a way that a generic SELinux policy that has been written for an operating system and file system can be applied to PostgreSQL without change and do something useful. I can see merits for or against that. But in any case, this needs to be clarified, if I understand this requirement correctly anyway. Indeed. My impression was also that there are existing SELinux rules and permission concepts already in use and people would like to apply them to PostgreSQL, which puts the translation layer inside postgres. However, from the postgres side there appears to be a push to make unique postgresql SELinux rules and requiring every user of SELinux to do the mapping of rights to postgresql specific terms. I think this is only semi-accurate. My impression is that a supposedly generic policy has been written for database objects and merged into model SE-Linux policy, but I think that was done largely in the hops of implementing SE-PostgreSQL. It would be one think if KaiGai showed up and said, see, there are three other databases that do this, now we want you to do it too, that would be one thing. But I don't think that's the case: I believe that we are the first, which makes the argument that we have to conform to the standard ways of doing this a lot weaker. FYI Trusted RUBIX already has SELinux integration in their DBMS: http://www.nsa.gov/research/selinux/list-archive/0807/26900.shtml They are using the same SELinux object classes that KaiGai developed for SE-Postgres. Specifically, creating SELinux permissions for CREATE LANGUAGE seems particularly useless since that's not a data protection issue. The same with aggregates, operator classes, etc. ISTM the goal of SELinux is not primarily to restrict DDL but mostly to protect the data. I'd actually be willing to buy that. If KaiGai wants to take the list of objects for which SE-PostgreSQL supports grantable permissions and the list of objects for which $COMPETITOR supports permissions and implement only the intersection of the two, I think that would be reasonable. What I don't think is reasonable is trying to implement, in the first version of the patch, 50 types of permissions that we never had before, or on the other hand such a trivial percentage of what we already do that it's totally useless. The reason for comprehensively protecting objects isn't necessarily about protecting the data in the database but for limiting information flow between clients of differing security levels. Eg., if someone top secret can create language and use that to pass information down to someone unclassified then postgres could be used as an information downgrader illegitimately. Personally I find the idea that SELinux permissions need to meet parity with the existing permission structure crazy, since it's not going to replace the existing permissions, just supplement them. Maybe it is crazy, but here are my concerns... 1. If the permissions work in a way that is very different than the existing permissions, then they need lots and lots of hooks all over the code. And nobody except KaiGai understands why those hooks are where they are instead of somewhere else. That's going to make it difficult to maintain. 2. If we add a lot of new kinds of permission checks that PostgreSQL has never done before, using a framework that none of the committers understand, how do we verify that they are well-designed (correct, secure, etc)? I am fairly well convinced that the design for row-level security was a really bad design. Whether I'm right or not, I think something like that is far too large a feature to add by the wayside in the context of another patch. 3. As far as I can tell, there is a lot of resistance from the committers who have looked at this patch (Tom, Peter, and maybe Bruce, though I think his review was not quite so unfavorable) to the idea of committing it at all. I don't think they're going to be willing to work extra-hard to implement security features that are only going to be useful to people using SE-Linux. I've said it before but I have firsthand knowledge of people wanting/waiting for SELinux integration into Postgres (or any open source DBMS) For what it's worth, this problem is not confined to the committers: SE-PostgreSQL is the only patch that I had people specifically tell me they didn't want to review because they just didn't care about it. Frankly, I don't care about it much either: ordinarily, the first and last thing I do with SE-Linux is shut it off. What is making me care even less is the fact
Re: [HACKERS] Higher TOAST compression.
Hi again ! I also take a look at another possibility to improve compression. There is two compression strategy : static const PGLZ_Strategy strategy_default_data = { 256,/* Data chunks less than 256 bytes are not * compressed */ 6144, /* Data chunks = 6K force compression, unless * compressed output is larger than input */ 20, /* Below 6K, compression rates below 20% mean * fallback to uncompressed */ 128,/* Stop history lookup if a match of 128 bytes * is found */ 10 /* Lower good match size by 10% at every * lookup loop iteration */ }; const PGLZ_Strategy *const PGLZ_strategy_default = strategy_default_data; static const PGLZ_Strategy strategy_always_data = { 0, /* Chunks of any size are compressed */ 0, 0, /* It's enough to save one single byte */ 128,/* Stop history lookup if a match of 128 bytes * is found */ 6 /* Look harder for a good match */ }; const PGLZ_Strategy *const PGLZ_strategy_always = strategy_always_data; 1) strategy_always_data seems to never be used. 2) the default strategy could be more aggressive (with a higher cpu cost) Additionally, we use a patched version that modify the default strategy. If i understand correctly, instead of being more aggresive on compression, it is *LESS* aggresive : static const PGLZ_Strategy strategy_default_data = { 32, /* Data chunks less than 32 bytes are not compressed */ 1024 * 1024,/* Data chunks over 1MB are not compressed either */ 25, /* Require 25% compression rate, or not worth it */ 1024, /* Give up if no compression in the first 1KB */ 128,/* Stop history lookup if a match of 128 bytes is found */ 10 /* Lower good match size by 10% at every loop iteration */ }; const PGLZ_Strategy *const PGLZ_strategy_default = strategy_default_data; Isn't it ? What about setting PGLZ_strategy_always as the default strategy (insane cpu cost ?) ? Or something in-between ? Thank you. -- Laurent Laborde Sysadmin @ http://www.over-blog.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] SE-PgSQL/tiny rev.2193
On Mon, Jul 20, 2009 at 10:52:44AM -0400, Joshua Brindle wrote: Specifically, creating SELinux permissions for CREATE LANGUAGE seems particularly useless since that's not a data protection issue. The same with aggregates, operator classes, etc. ISTM the goal of SELinux is not primarily to restrict DDL but mostly to protect the data. The reason for comprehensively protecting objects isn't necessarily about protecting the data in the database but for limiting information flow between clients of differing security levels. Eg., if someone top secret can create language and use that to pass information down to someone unclassified then postgres could be used as an information downgrader illegitimately. Consider the pl/pgsql language. The creation of the language must be protected, because it involves loading shared libraries and thus could be used to bypass the system. However, once loaded the language only uses the internal SQL interface and thus is subject to the restrictions imposed by the caller (except for setuid functions ofcourse). Would you agree if the language is transparent with respect to permissions that *usage* of the laguage doesn't need to be restricted. I'm asking because from my position it looks like KaiGai is being simultaneously told you patch is too big, make it smaller and your patch is not complete (with respect to some metric), make it bigger and we need to define a middle ground if we want to avoid the appearence of moving goalposts. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
Martijn van Oosterhout wrote: On Mon, Jul 20, 2009 at 10:52:44AM -0400, Joshua Brindle wrote: Specifically, creating SELinux permissions for CREATE LANGUAGE seems particularly useless since that's not a data protection issue. The same with aggregates, operator classes, etc. ISTM the goal of SELinux is not primarily to restrict DDL but mostly to protect the data. The reason for comprehensively protecting objects isn't necessarily about protecting the data in the database but for limiting information flow between clients of differing security levels. Eg., if someone top secret can create language and use that to pass information down to someone unclassified then postgres could be used as an information downgrader illegitimately. Consider the pl/pgsql language. The creation of the language must be protected, because it involves loading shared libraries and thus could be used to bypass the system. However, once loaded the language only uses the internal SQL interface and thus is subject to the restrictions imposed by the caller (except for setuid functions ofcourse). Would you agree if the language is transparent with respect to permissions that *usage* of the laguage doesn't need to be restricted. Using something is typically controlled because of information you may get from using it (for example, stat() on a file may not get you the data in the file but it gets you plenty of other information). I guess the question is, can the person creating the language leak information to people using the language and it sounds like they can. However, because language creation is controlled via superuser privilege (which is never ideal, we like to be able to break superusers up and give them only permission to do what they need to do, principle of least privilege) then it is probably a lot less important than many of the other things KaiGai is trying to get in. I'm asking because from my position it looks like KaiGai is being simultaneously told you patch is too big, make it smaller and your patch is not complete (with respect to some metric), make it bigger and we need to define a middle ground if we want to avoid the appearence of moving goalposts. Agreed, there are lots of mixed signals in this thread. For my uses the 'basic' support being pushed won't be enough to use this in secure applications, nothing less than full row-level access control would be. I'm all for the community taking smaller patches over time but if there is no intention of going all the way at the end then I'm not sure what you/we are gaining. -- 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] Higher TOAST compression.
Laurent Laborde kerdez...@gmail.com wrote: There is two compression strategy : static const PGLZ_Strategy strategy_default_data = { static const PGLZ_Strategy strategy_always_data = { 1) strategy_always_data seems to never be used. A quick grep sure makes it look that way. I will look closer later. 2) the default strategy could be more aggressive (with a higher cpu cost) What about setting PGLZ_strategy_always as the default strategy (insane cpu cost ?) ? Or something in-between ? That goes beyond what I was trying to do with my recent patch. What you propose may be useful, but there would need to be much discussion and benchmarking and it would be a new patch. If you have any benchmark information on relative speed and space savings, please post them. -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] Higher TOAST compression.
On Mon, Jul 20, 2009 at 6:04 PM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: What about setting PGLZ_strategy_always as the default strategy (insane cpu cost ?) ? Or something in-between ? That goes beyond what I was trying to do with my recent patch. What you propose may be useful, but there would need to be much discussion and benchmarking and it would be a new patch. If you have any benchmark information on relative speed and space savings, please post them. I will try that as soon as my spare production server (2x4core Xeon, 32GB RAM, 8 SAS Disk) is back to life. I wasn't thinking about the very aggressive (strategy_always) compression strategy for a default postgresql release. Not everyone is IObound :) But just as a default setting here at over-blog. (definitively IOBound with large articles and comment). Anyway, i will try and report different strategy here. Thank you again for your feedback. -- Laurent Laborde Sysadmin @ http://www.over-blog.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] Review: Revise parallel pg_restore's scheduling heuristic
Andrew Dunstan and...@dunslane.net wrote: To performance test this properly you might need to devise a test that will use a sufficiently different order of queueing items to show the difference. One thing I am particularly interested in is to see if queuing FK items for a table as soon as they become available, which is most likely to be when the referred to index is created, rather than possibly doing them all together (assuming they are named with the table name as a prefix) as TOC order would do, has a better performance or not. Hmmm I'm reevaluating my database choice. The 1.1TB database does not have foreign key constraints as a matter of policy. It is a replica of the county databases, and we want to replicate whatever we can of the county data -- failure for some reason of part of the data to replicate should not block replication of something else, to minimize differences. Is there still value in using such a database at all, or should I focus on databases in the 50GB to 90GB range with FK constraints defined? When you suggest devising a test to show a difference, in what way would it be likely that I would need to modify the real-life database to get such a test? Our FKs do start with TableName_. We don't have underscores in our table names, although we use similar naming for our indexes. -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] Review: Revise parallel pg_restore's scheduling heuristic
Robert Haas robertmh...@gmail.com wrote: it might be worth testing with default settings too. OK. I'll do that too, if time allows. -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] WIP: Deferrable unique constraints
2009/7/20 David Fetter da...@fetter.org: On Mon, Jul 20, 2009 at 10:21:53AM -0400, Tom Lane wrote: Dean Rasheed dean.a.rash...@googlemail.com writes: * Please move the code that says that it should be in a new file to a new file. Ah yes, I forgot about that. Will do. utils/adt/constraints.c ? Please, no. Constraints are not a datatype. Do not copy the brain-dead decision that put ri_triggers there. Does that mean ri_triggers should come out of there? The same argument could be applied to ruleutils.c, trigfuncs.c and perhaps one or two others. And if not there, where then? -- 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] Review: Revise parallel pg_restore's scheduling heuristic
Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: My plan here would be to have the dump on one machine, and run pg_restore there, and push it to a database on another machine through the LAN on a 1Gb connection. (This seems most likely to be what we'd be doing in real life.) you need to be careful here - in my latest round of benchmarking I had actually test with the workload generator on the same box because on fast boxes we can easily achive 100MB/s total load rate these days. At these load rates you are very close or over the pratical limits of GigE... Yeah, I was concerned about that. Problem was, with the 1.1TB database it is hard to fit a dump and an extra copy of the database onto the drives along with the production data for which they exist. We would likely face the same constraints with real data if using the parallel restore, since it requires an interim backup file (i.e., you can't stream directly from the source database). There's also the issue of reading from the same RAID you're targeting with the restore, which is sometimes not optimal. If I'm dropping down an order of magnitude or more in the databases I will use, I could put the backup file on a separate RAID on the same machine. This leaves a lot of options. I'm not sure which combinations of configuration, file placement, and job count yield the most useful results. -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] hot standby - merged up to CVS HEAD
On Wed, 2009-07-15 at 17:27 +0100, Simon Riggs wrote: On Tue, 2009-07-14 at 21:12 -0400, Robert Haas wrote: It's going to be very confusing if people submit their own versions of it. So now we have mine, Heikki's and Robert's. I'd like this to stop please, have a little faith and a little patience. Presumably Robert's rebasing patch is best place to start from now for later work. Robert, thank you for your work. Simon you need to realize that a lot of people really want this patch. I for one applaud Robert's work (and Heikki's). If you want a summer holiday, go for it. I certainly haven't been working that hard this summer. However, I certainly don't think that is any reason for people who are showing initiation and drive should stop. If Robert wants to work on this patch, more power to him. Perhaps he can solve something you can't. Perhaps it will be done before you are done with holiday. If not, then at least we have moved a little further in the process and in theory taken some workload off of you. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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 v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
Jeremy Kerr j...@ozlabs.org writes: Rather than testing single bits in a loop, change AllocSetFreeIndex to use the __builtin_clz() function to calculate the chunk index. This requires a new check for __builtin_clz in the configure script. Results in a ~2% performance increase on sysbench on PowerPC. I did some performance testing on this by extracting the AllocSetFreeIndex function into a standalone test program that just executed it a lot of times in a loop. And there's a problem: on x86_64 it is not much of a win. The code sequence that gcc generates for __builtin_clz is basically bsrl%eax, %eax xorl$31, %eax and it turns out that Intel hasn't seen fit to put a lot of effort into the BSR instruction. It's constant time, all right, but on most of their CPUs that constant time is like 8 or 16 times slower than an ADD; cf http://www.intel.com/Assets/PDF/manual/248966.pdf What I found on both a couple-years-old Xeon and a pretty new Macbook is that the __builtin_clz code is actually *slower* than the naive loop for the fewest-iterations case (request size up to 16 bytes) and about a breakeven at the next size category (up to 32). It doesn't start to look like a useful win until you get to sizes up to 1KB or so. Unfortunately PG spends a lot more time allocating little structs than big ones. I suppose that it's more of a win on PPC (where probably __builtin_clz is actually fast), but on the basis of these results it's going to be no gain and maybe a loss on Intel. The other problem is that this approach is gcc-specific, which seems particularly bad for something that's only going to be a win on non-Intel platforms; they're much more likely to be using non-gcc compilers. I wonder whether it would work better to do manual unrolling of the shift loop. That is, forget about the builtin and do something like while (size = 8) { idx += 4; size = 4; } while (size) { idx++; size = 1; } Obviously there's room for experimental optimization of the particular shift strides we use here, but remember that the total shift count is never going to exceed about 10, and will usually be quite a bit less. I did some quick experimentation along these lines and couldn't really persuade myself that unrolling was going to win much, but that's an Intel-specific result. Anyway, based on what I'm seeing here I can't convince myself that this is worth introducing a compiler dependency for. 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] WIP: Deferrable unique constraints
David Fetter da...@fetter.org writes: Please, no. Constraints are not a datatype. Do not copy the brain-dead decision that put ri_triggers there. Does that mean ri_triggers should come out of there? Not as long as we're on CVS --- it isn't worth the loss of history. I might think about it when/if we move to git. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
Martijn van Oosterhout klep...@svana.org writes: I'm asking because from my position it looks like KaiGai is being simultaneously told you patch is too big, make it smaller and your patch is not complete (with respect to some metric), make it bigger and we need to define a middle ground if we want to avoid the appearence of moving goalposts. Well, the assumption behind the make it smaller advice was that followon patches might re-add (some of) the advanced functionality once we had a basic connection to SELinux up and running. So yes, there's an expectation of the goals changing over time, and I don't see a reason to apologize for that. But on the whole the problem is that the community just isn't interested in this topic. Every so often somebody pops up and tells us if you build it they will come, but there is a complete lack of hard evidence that there's significant interest out there. Without that, I don't think we'll ever get to the point of wanting to put in the work to make it happen and then support it over time. This is a huge project, and taking it seriously will mean draining resources away from other high-priority issues. We need way more evidence of interest than has been presented. 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_stat_activity.application_name
On Fri, Jul 17, 2009 at 9:58 AM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: Peter Eisentraut pete...@gmx.net wrote: A facility to show it in the logs (via log_line_prefix probably) would also be useful. Agreed. ah! that seems a lot more useful. at least, i currently use logs to identify malformed or bad-performant queries with that i will know from what webpage they came -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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 v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
Tom Lane wrote: Jeremy Kerr j...@ozlabs.org writes: Rather than testing single bits in a loop, change AllocSetFreeIndex to use the __builtin_clz() function to calculate the chunk index. This requires a new check for __builtin_clz in the configure script. Results in a ~2% performance increase on sysbench on PowerPC. I did some performance testing on this by extracting the AllocSetFreeIndex function into a standalone test program that just executed it a lot of times in a loop. And there's a problem: on x86_64 it is not much of a win. The code sequence that gcc generates for __builtin_clz is basically bsrl%eax, %eax xorl$31, %eax and it turns out that Intel hasn't seen fit to put a lot of effort into the BSR instruction. It's constant time, all right, but on most of their CPUs that constant time is like 8 or 16 times slower than an ADD; cf http://www.intel.com/Assets/PDF/manual/248966.pdf hmm interesting - I don't have the exact numbers any more but that patch(or a previous version of it) definitly showed a noticable improvement when I tested with sysbench on a current generation Intel Nehalem... Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
Tom Lane wrote: Martijn van Oosterhoutklep...@svana.org writes: I'm asking because from my position it looks like KaiGai is being simultaneously told you patch is too big, make it smaller and your patch is not complete (with respect to some metric), make it bigger and we need to define a middle ground if we want to avoid the appearence of moving goalposts. Well, the assumption behind the make it smaller advice was that followon patches might re-add (some of) the advanced functionality once we had a basic connection to SELinux up and running. So yes, there's an expectation of the goals changing over time, and I don't see a reason to apologize for that. But on the whole the problem is that the community just isn't interested in this topic. Every so often somebody pops up and tells us if you build it they will come, but there is a complete lack of hard evidence that there's significant interest out there. Without that, I don't think we'll ever get to the point of wanting to put in the work to make it happen and then support it over time. This is a huge project, and taking it seriously will mean draining resources away from other high-priority issues. We need way more evidence of interest than has been presented. How many people are you looking for? Is there a number or are you waiting for a good feeling? The unfortunate part is that many of the people that would use it are unable to publicly say so. -- 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] SE-PgSQL/tiny rev.2193
Joshua Brindle escribió: The unfortunate part is that many of the people that would use it are unable to publicly say so. So they will be similarly unable to help with it. Such a black hole is not of much use, is it? Or are they getting a contract with some PG support company to which they'll shell out the bucks under NDA to ensure that the thing is delivered and maintained? I think that if this looked less like a one-man project it could have better support around here. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] fmgroids.h not installed by make install in VPATH
Peter Eisentraut wrote: On Wednesday 08 July 2009 02:09:20 Alvaro Herrera wrote: It seems our makefiles fail to install fmgroids.h by make install in a VPATH build. I think the solution is to treat fmgroids.h just like pg_config_os.h, i.e. add a line like this: $(INSTALL_DATA) utils/fmgroids.h '$(DESTDIR)/$(includedir_server)/utils' The fix looks right. Has it been applied? FWIW I intend to apply it to HEAD and 8.4 only; if you think it should be backpatched further back please say so. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MIN/MAX optimization for partitioned table
On Sat, Jul 18, 2009 at 12:04 PM, Greg Stark gsst...@mit.edu wrote: On Sat, Jul 18, 2009 at 6:35 PM, Alan Lia...@truviso.com wrote: Yeah, are you running into the same issue as well? I tried to figure out a way around the O(n^2) behavior, but there were no existing direct relationship between the child subpath and its associated AppendRelInfo. I think an AppenRelInfo dynahash would work and just have it hash by the child_relid. I don't see any place in my patch where I had to do anything like this. I do have to loop through all the appendrel elements and skip over the ones which don't belong to this appendrel which seems weird to me but it isn't n^2. Generating a normal Append node you're generating a brand new path for each child and attaching them to the append path. It looks like you're allowing the append rel to generate paths and then digging around looking at those paths. I wonder if that's the right approach. The other thing is it would be nice if we could avoid making separate subplans for each child and instead make one for the whole structure including the aggregate. It would at the very least make the explain output prettier, but I think it would avoid repeated execution of the aggregate in some cases as well. How would this plan look? I think the repeated execution of the aggregate comes from having to process the output of each child. So it's O(n) executions where n is the number of children, assuming each child has the appropriate index for this optimization. No I just mean instead of generating InitPlan 1 Limit Index Scan InitPlan 2 Limit Index Scan Aggregate Append Result Result I think it would be better to generate this: InitPlan 1 Aggregate Append Limit 1 IndexScan Limit 1 IndexScan Result The reason I think this is better is because if the subquery is executed multiple times it will just return the one precalculated result. In your plan it will have all the child minimums precalculated but will still have to re-execute the aggregate and append node. That's not terribly expensive but we might as well generate the simpler more efficient plan. The other optimization that I thought was that the optimizer might use the check constraints (in the range partitioning) on the column that I want to do a MIN/MAX on. Then it'll only have to execute the subplan in one child partition and the parent partition. But it was more of a wild idea on my part, not sure if that's possible. Yes, I think this is the long-term goal. That's the whole better representation of partitions plotline. To do this efficiently the planner should know what the partition key is and what the partitioning structure is. In an ideal world we would be able to collapse the whole structure and eliminate the append relation entirely. To do that we need some way to indicate that the parent relation is provably empty. I had in mind to mark it as read-only and the statistics as authoritative since that seems more useful than just being able to mark it empty. I think there are a lot of other interesting things we could do with statistics if we knew they were authoritative for a read-only table. (The read-only property we would need here is very strong. There would have to be a vacuum freeze and moreover we would have to know that all tuples were successfully frozen.) -- greg http://mit.edu/~gsstark/resume.pdf http://mit.edu/%7Egsstark/resume.pdf Attached is an updated patch that removes the O(n^2) behavior and the awkwardness of optimizing the seqscan path as the plan was about to be created. Now, the optimization is considered when appendrel is generating the paths. I also changed the plan as you had suggested. It now looks like this: postgres=# explain select max(b) from foo_archive; QUERY PLAN --- Aggregate (cost=1.22..1.23 rows=1 width=8) - Append (cost=0.00..1.13 rows=32 width=8) - Limit (cost=0.00..0.04 rows=1 width=8) - Index Scan Backward using foo_archive_idx on foo_archive (cost=0.00..73.35 rows=1940 width=8) - Limit (cost=0.00..0.03 rows=1 width=8) - Index Scan Backward using foo_archive_2007_01_01_idx on foo_archive_2007_01_01 foo_archive (cost=0.00..2723.24 rows=86399 width=8) - Limit (cost=0.00..0.03 rows=1 width=8) - Index Scan Backward using foo_archive_2007_01_02_idx on foo_archive_2007_01_02 foo_archive (cost=0.00..2719.26 rows=86400 width=8) - Limit (cost=0.00..0.03 rows=1 width=8) - Index Scan Backward using foo_archive_2007_01_03_idx on foo_archive_2007_01_03 foo_archive
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
On Monday 20 July 2009 21:05:38 Joshua Brindle wrote: How many people are you looking for? Is there a number or are you waiting for a good feeling? In my mind, the number of interested people is relatively uninteresting, as long as it is greater than, say, three. What is lacking here is a written specification. When it comes to larger features, this development group has a great deal of experience in implementing existing specifications, even relatively terrible ones like SQL or ODBC or Oracle compatibility. But the expected behavior has to be written down somewhere, endorsed by someone with authority. It can't just be someone's idea. Especially for features that are so complex, esoteric, invasive, and critical for security and performance. So I think if you want to get anywhere with this, scrap the code, and start writing a specification. One with references. And then let's consider that one. -- 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] WIP: Deferrable unique constraints
On Mon, Jul 20, 2009 at 01:00:12PM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: Please, no. Constraints are not a datatype. Do not copy the brain-dead decision that put ri_triggers there. Does that mean ri_triggers should come out of there? Not as long as we're on CVS --- it isn't worth the loss of history. I might think about it when/if we move to git. As far as you're concerned, what's blocking 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 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] [GENERAL] pg_migrator not setting values of sequences?
Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: Something is certainly wrong. Did we change sequence table format from 8.3 to 8.4? 8.3 does not have start_value. Looking at an invalidly-migrated sequence's columns: regression= \d serialtest_f2_foo Sequence public.serialtest_f2_foo Column | Type |Value ---+-+- sequence_name | name| serialtest_f2_foo last_value| bigint | 3 start_value | bigint | 1 increment_by | bigint | 9223372036854775807 max_value | bigint | 1 min_value | bigint | 1 cache_value | bigint | 0 log_cnt | bigint | 25387551686912 is_cycled | boolean | f is_called | boolean | Should pg_migrator just pull the misaligned values and do an ALTER SEQUENCE/seval() to fix it, or create a script to do that? I have applied the attached patch to pg_migrator that will properly handle migrating sequences; it should apply cleanly to pg_migrator 8.4.1 alpha 1. What I found during research is that pg_dump --schema-only already creates the sequence: CREATE SEQUENCE check_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; What it does not do is to call setval() to set the sequence value and 'is_called'. What I did was to _not_ migrate the sequence file, but rather create a script from the old cluster that uses setval() to set the sequence values. This can be safely run by pg_migrator unconditionally because we are not migrating the sequence files, even in link mode. This solves the sequence migration problem, with no changes to pg_dump. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ? tools ? log ? src/pg_migrator Index: src/info.c === RCS file: /cvsroot/pg-migrator/pg_migrator/src/info.c,v retrieving revision 1.18 diff -c -r1.18 info.c *** src/info.c 14 Jul 2009 02:34:59 - 1.18 --- src/info.c 20 Jul 2009 18:55:48 - *** *** 322,353 int i_oid = -1; int i_relfilenode = -1; int i_reltoastrelid = -1; ! res = executeQueryOrDie(ctx, conn, ! SELECT DISTINCT c.oid, n.nspname, c.relname, ! c.relfilenode, c.reltoastrelid, ! t.spclocation,n.nspname ! FROM (pg_catalog.pg_class c JOIN ! pg_catalog.pg_namespace n ! ON c.relnamespace = n.oid) ! LEFT OUTER JOIN pg_catalog.pg_tablespace t ! ON c.reltablespace = t.oid ! WHERE relnamespace NOT IN ! ( ! SELECT oid ! FROM pg_catalog.pg_namespace ! WHERE nspname IN ! ('pg_catalog', 'information_schema') ! ) ! AND c.oid = ! STRINGIFY(FirstNormalObjectId) ! AND ! (relkind = 'r' OR relkind = 't' OR ! relkind = 'i' OR relkind = 'S') ! GROUP BY c.oid, n.nspname, c.relname, c.relfilenode, ! c.reltoastrelid, t.spclocation, ! n.nspname ! ORDER BY n.nspname, c.relname;); ntups = PQntuples(res); --- 322,360 int i_oid = -1; int i_relfilenode = -1; int i_reltoastrelid = -1; + char query[QUERY_ALLOC]; ! snprintf(query, sizeof(query), ! SELECT DISTINCT c.oid, n.nspname, c.relname, ! c.relfilenode, c.reltoastrelid, ! t.spclocation,n.nspname ! FROM (pg_catalog.pg_class c JOIN ! pg_catalog.pg_namespace n ! ON c.relnamespace = n.oid) ! LEFT OUTER JOIN pg_catalog.pg_tablespace t ! ON c.reltablespace = t.oid ! WHERE relnamespace NOT IN ! ( ! SELECT oid ! FROM pg_catalog.pg_namespace ! WHERE nspname IN ! ('pg_catalog', 'information_schema') ! ) ! AND c.oid = ! STRINGIFY(FirstNormalObjectId) ! AND ! (relkind = 'r' OR relkind = 't' OR ! relkind = 'i'%s) ! GROUP BY c.oid, n.nspname, c.relname, c.relfilenode, ! c.reltoastrelid, t.spclocation, ! n.nspname ! ORDER BY n.nspname, c.relname;, ! /* see the comment at the top of v8_3_adjust_sequences() */ ! (GET_MAJOR_VERSION(ctx-old.pg_version) == 803 ! GET_MAJOR_VERSION(ctx-new.pg_version) 803) ? ! : OR relkind = 'S'); ! ! res = executeQueryOrDie(ctx, conn, query); ntups = PQntuples(res); Index: src/pg_migrator.c === RCS file: /cvsroot/pg-migrator/pg_migrator/src/pg_migrator.c,v retrieving revision 1.56 diff -c -r1.56 pg_migrator.c *** src/pg_migrator.c 3 Jul 2009 16:46:49 - 1.56 --- src/pg_migrator.c 20 Jul 2009 18:55:48 - *** *** 37,43
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
Peter Eisentraut wrote: On Monday 20 July 2009 21:05:38 Joshua Brindle wrote: How many people are you looking for? Is there a number or are you waiting for a good feeling? In my mind, the number of interested people is relatively uninteresting, as long as it is greater than, say, three. What is lacking here is a written specification. When it comes to larger features, this development group has a great deal of experience in implementing existing specifications, even relatively terrible ones like SQL or ODBC or Oracle compatibility. But the expected behavior has to be written down somewhere, endorsed by someone with authority. It can't just be someone's idea. Especially for features that are so complex, esoteric, invasive, and critical for security and performance. Who do you consider has authority? The security community has as many opinions as any other. There are papers written on mandatory access controls in rdbms's but they are mostly about multi-level security, which SELinux has but primarily uses type enforcement. The SELinux community are all on board with KaiGai's object model (the object classes and permissions and how they are enforced), there has been quite a bit of discussion about them over the years. Trusted RUBIX integrated SELinux using the object classes that KaiGai made for SEPostgres. So I think if you want to get anywhere with this, scrap the code, and start writing a specification. One with references. And then let's consider that one. Harsh. -- 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 v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: Tom Lane wrote: and it turns out that Intel hasn't seen fit to put a lot of effort into the BSR instruction. It's constant time, all right, but on most of their CPUs that constant time is like 8 or 16 times slower than an ADD; cf http://www.intel.com/Assets/PDF/manual/248966.pdf hmm interesting - I don't have the exact numbers any more but that patch(or a previous version of it) definitly showed a noticable improvement when I tested with sysbench on a current generation Intel Nehalem... Hmm. I may be overestimating the importance of the smaller size categories. To try to get some trustworthy numbers, I made a quick-hack patch (attachment #1) to count the actual numbers of calls to AllocSetFreeIndex, and measured the totals for a run of the regression tests on both a 32-bit machine and a 64-bit machine. On 32 I got these totals: 0 5190113 1 5663980 2 3573261 3 4476398 4 4246178 5 1100634 6 386501 7 601654 8 44884 9 52372 10 202801 and on 64 these: 0 2139534 1 5994692 2 5711479 3 3289034 4 4550931 5 2573389 6 487566 7 588470 8 155148 9 52750 10 202597 If you want to do the same in some other workload, feel free. I wouldn't trust the results from a single-purpose benchmark too much, though. I then put together a test harness that exercises AllocSetFreeIndex according to these distributions (attachments #2,#3). (Note: I found out that it's necessary to split the test into two files --- otherwise gcc will inline AllocSetFreeIndex and partially const-fold the work, leading to skewed results.) What I'm seeing with this harness on my x86 machines is that __builtin_clz is indeed a bit faster than a naive loop, but not by very much --- it saves maybe 25% of the runtime. It's better on an old PPC Mac; saves about 50%. Still, these are not impressive numbers for a microbenchmark that is testing *only* AllocSetFreeIndex. I'm still interested in the idea of doing a manual unroll instead of relying on a compiler-specific feature. However, some quick testing didn't find an unrolling that helps much. regards, tom lane *** src/backend/storage/ipc/ipc.c.orig Thu Jun 11 10:55:12 2009 --- src/backend/storage/ipc/ipc.c Mon Jul 20 13:56:06 2009 *** *** 183,188 --- 183,190 on_proc_exit_list[on_proc_exit_index].arg); on_proc_exit_index = 0; + + AllocSetPrintStats(); } /* -- *** src/backend/utils/mmgr/aset.c.orig Thu Jun 11 10:55:25 2009 --- src/backend/utils/mmgr/aset.c Mon Jul 20 13:56:19 2009 *** *** 255,260 --- 255,271 #define AllocAllocInfo(_cxt, _chunk) #endif + static unsigned long allocsizes[ALLOCSET_NUM_FREELISTS]; + + void + AllocSetPrintStats() + { + int i; + + for (i = 0; i ALLOCSET_NUM_FREELISTS; i++) + fprintf(stderr, category %2d count %lu\n, i, allocsizes[i]); + } + /* -- * AllocSetFreeIndex - * *** *** 277,282 --- 288,294 size = 1; } Assert(idx ALLOCSET_NUM_FREELISTS); + allocsizes[idx]++; } return idx; /* * usage: time ./testit N * N is a repeat count, set it large enough to get repeatable timings */ #include stdio.h #include stdlib.h #define ALLOC_MINBITS 3 /* smallest chunk size is 8 bytes */ #define ALLOCSET_NUM_FREELISTS 11 /* number of calls to AllocSetFreeIndex with each result category */ static const int numoccurs[ALLOCSET_NUM_FREELISTS] = { #ifdef USE_64BIT_COUNTS 2139534, 5994692, 5711479, 3289034, 4550931, 2573389, 487566, 588470, 155148, 52750, 202597 #else 5190113, 5663980, 3573261, 4476398, 4246178, 1100634, 386501, 601654, 44884, 52372, 202801 #endif }; extern int AllocSetFreeIndex(size_t size); int main(int argc, char **argv) { int repeat = atoi(argv[1]); while (repeat-- 0) { int k; for (k = 0; k ALLOCSET_NUM_FREELISTS; k++) { int n = numoccurs[k]; size_t siz = (1 (ALLOC_MINBITS + k)); while (n-- 0) { AllocSetFreeIndex(siz); } } } return 0; } #include stdio.h #include stdlib.h #define ALLOC_MINBITS 3 /* smallest chunk size is 8 bytes */ #define ALLOCSET_NUM_FREELISTS 11 #define BITS_PER_BYTE 8 /* -- * AllocSetFreeIndex - * * Depending on the size of an allocation compute which freechunk * list of the alloc set it belongs to. Caller must have verified *
Re: [HACKERS] MIN/MAX optimization for partitioned table
On Mon, Jul 20, 2009 at 7:40 PM, Alan Lia...@truviso.com wrote: Attached is an updated patch that removes the O(n^2) behavior and the awkwardness of optimizing the seqscan path as the plan was about to be created. Now, the optimization is considered when appendrel is generating the paths. I also changed the plan as you had suggested. It now looks like this: Hm, that's not quite the plan I described either. I had in mind to mirror the existing min/max optimization which put the whole thing in an InitPlan and put a Result node in place of the actual plan. Your original patch did that for each subplan but I thought it would be better to do it for the whole aggregate. However the more I think about it the more I don't understand why Tom arranged to do that for the min/max optimization anyways. For subqueries where that makes sense that would surely happen anyways such as in the first example below. And for joins where it's necessary the planner knows to put a Materialize node which sounds just as good. Here's what happens with your current patch in the case I was concerned about -- note that the planner automatically detects the case and turns the whole subplan into an initplan anyways: postgres=# explain select * from y where j = (select min(i) from x) ; QUERY PLAN -- Seq Scan on y (cost=40.12..80.12 rows=12 width=4) Filter: (j = $0) InitPlan 1 (returns $0) - Aggregate (cost=40.11..40.12 rows=1 width=4) - Append (cost=0.00..34.10 rows=2403 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi on x (cost=0.00..80.25 rows=2400 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi2 on x2 x (cost=0.00..80.25 rows=2400 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi3 on x3 x (cost=0.00..80.25 rows=2400 width=4) - Seq Scan on x1 x (cost=0.00..34.00 rows=2400 width=4) (12 rows) And here's another case where you wouldn't want multiple execution -- but the planner here figures out to materialize the result: postgres=# explain select * from y left outer join (select min(i) as i from x) as x on (i=j); QUERY PLAN -- Nested Loop Left Join (cost=40.13..128.13 rows=2400 width=8) Join Filter: ((min(public.x.i)) = y.j) - Seq Scan on y (cost=0.00..34.00 rows=2400 width=4) - Materialize (cost=40.13..40.14 rows=1 width=4) - Aggregate (cost=40.11..40.12 rows=1 width=4) - Append (cost=0.00..34.10 rows=2403 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi on x (cost=0.00..80.25 rows=2400 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi2 on x2 x (cost=0.00..80.25 rows=2400 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi3 on x3 x (cost=0.00..80.25 rows=2400 width=4) - Seq Scan on x1 x (cost=0.00..34.00 rows=2400 width=4) (13 rows) So I'm a bit puzzled why Tom's min/max optimization bothers with the whole Initplan/Result business itself anyways. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
Joshua Brindle wrote: How many people are you looking for? Is there a number or are you waiting for a good feeling? Is it individuals or organizations people are looking for? I see KaiGai wrote In addition, I (and NEC) can provide our capability to the PostgreSQL community to keep these security features work correctly. Does that imply that a larger part of NEC is interested? The unfortunate part is that many of the people that would use it are unable to publicly say so. Could they publicly say something softer. I see SELinux had a number of large organizations (NSA) and publicly traded companies (Secure Computing Corp, Network Associates, etc) pushing it and contributing to it. If people who could speak for those organizations were here saying ooh, and such features in a F/OSS database would be interesting too, that would probably convince a lot of people. Joshua - if you're still associated with Tresys - could someone who could speak for that company say what they think about this project? The seem quite in-the-loop on what SELinux customers want. -- 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 v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
I wrote: I'm still interested in the idea of doing a manual unroll instead of relying on a compiler-specific feature. However, some quick testing didn't find an unrolling that helps much. Hmm, actually this seems to work ok: idx++; size = 1; if (size != 0) { idx++; size = 1; if (size != 0) { idx++; size = 1; if (size != 0) { idx++; size = 1; while (size != 0) { idx++; size = 1; } } } } (this is with the initial if (size (1 ALLOC_MINBITS)) so that we know the starting value is nonzero) This seems to be about a wash or a small gain on x86_64, but on my PPC Mac laptop it's very nearly identical in speed to the __builtin_clz code. I also see a speedup on HPPA, for which my gcc is too old to know about __builtin_clz. Anyone want to see if they can beat that? Some testing on other architectures would help too. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
Ron Mayer wrote: Joshua Brindle wrote: How many people are you looking for? Is there a number or are you waiting for a good feeling? snip Joshua - if you're still associated with Tresys - could someone who could speak for that company say what they think about this project? The seem quite in-the-loop on what SELinux customers want. I am capable of speaking for Tresys in this matter. We are very interested in this work and our US DoD customers need the capabilities that this project adds (assuming row level access controls are a possibility). -- 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] SE-PgSQL/tiny rev.2193
Peter Eisentraut wrote: On Monday 20 July 2009 21:05:38 Joshua Brindle wrote: How many people are you looking for? Is there a number or are you waiting for a good feeling? In my mind, the number of interested people is relatively uninteresting, as long as it is greater than, say, three. What is lacking here is a written specification. When it comes to larger features, this development group has a great deal of experience in implementing existing specifications, even relatively terrible ones like SQL or ODBC or Oracle compatibility. But the expected behavior has to be written down somewhere, endorsed by someone with authority. It can't just be someone's idea. Especially for features that are so complex, esoteric, invasive, and critical for security and performance. So I think if you want to get anywhere with this, scrap the code, and start writing a specification. One with references. And then let's consider that one. At least, what is important is that SE-PgSQL performs with its security model correctly, not how it is implemented. In fast, I have modified its implementation and separated some of non-primary features several times. As I said before, its implementation is flexible as far as it can implement SELinux's security model correctly. If PostgreSQL community requires its design specifications from the viewpoints of developers, I don't have any reason not to provide it. One question is what items should be described in the specifications? I already provide a reference including a list of object classes and permissions. http://wiki.postgresql.org/wiki/SEPostgreSQL_References I guess you would like to see when/where/how SE-PgSQL checks what permissions, what criteria to make its decision should be used, and so on. -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] WIP: Deferrable unique constraints
David Fetter da...@fetter.org writes: On Mon, Jul 20, 2009 at 01:00:12PM -0400, Tom Lane wrote: I might think about it when/if we move to git. As far as you're concerned, what's blocking that? Lack of committer familiarity with git, lack of a bulletproof migration process, uncertainty about preferred multi-branch patching techniques, uncertainty about buildfarm integration, etc etc. Do I really need to repeat the previous discussions to you? AFAIR none of the open questions have gotten resolved with any clarity. 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] MIN/MAX optimization for partitioned table
On Mon, Jul 20, 2009 at 12:29 PM, Greg Stark gsst...@mit.edu wrote: On Mon, Jul 20, 2009 at 7:40 PM, Alan Lia...@truviso.com wrote: Attached is an updated patch that removes the O(n^2) behavior and the awkwardness of optimizing the seqscan path as the plan was about to be created. Now, the optimization is considered when appendrel is generating the paths. I also changed the plan as you had suggested. It now looks like this: Hm, that's not quite the plan I described either. I had in mind to mirror the existing min/max optimization which put the whole thing in an InitPlan and put a Result node in place of the actual plan. Your original patch did that for each subplan but I thought it would be better to do it for the whole aggregate. However the more I think about it the more I don't understand why Tom arranged to do that for the min/max optimization anyways. For subqueries where that makes sense that would surely happen anyways such as in the first example below. And for joins where it's necessary the planner knows to put a Materialize node which sounds just as good. Here's what happens with your current patch in the case I was concerned about -- note that the planner automatically detects the case and turns the whole subplan into an initplan anyways: postgres=# explain select * from y where j = (select min(i) from x) ; QUERY PLAN -- Seq Scan on y (cost=40.12..80.12 rows=12 width=4) Filter: (j = $0) InitPlan 1 (returns $0) - Aggregate (cost=40.11..40.12 rows=1 width=4) - Append (cost=0.00..34.10 rows=2403 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi on x (cost=0.00..80.25 rows=2400 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi2 on x2 x (cost=0.00..80.25 rows=2400 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi3 on x3 x (cost=0.00..80.25 rows=2400 width=4) - Seq Scan on x1 x (cost=0.00..34.00 rows=2400 width=4) (12 rows) And here's another case where you wouldn't want multiple execution -- but the planner here figures out to materialize the result: postgres=# explain select * from y left outer join (select min(i) as i from x) as x on (i=j); QUERY PLAN -- Nested Loop Left Join (cost=40.13..128.13 rows=2400 width=8) Join Filter: ((min(public.x.i)) = y.j) - Seq Scan on y (cost=0.00..34.00 rows=2400 width=4) - Materialize (cost=40.13..40.14 rows=1 width=4) - Aggregate (cost=40.11..40.12 rows=1 width=4) - Append (cost=0.00..34.10 rows=2403 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi on x (cost=0.00..80.25 rows=2400 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi2 on x2 x (cost=0.00..80.25 rows=2400 width=4) - Limit (cost=0.00..0.03 rows=1 width=4) - Index Scan using xi3 on x3 x (cost=0.00..80.25 rows=2400 width=4) - Seq Scan on x1 x (cost=0.00..34.00 rows=2400 width=4) (13 rows) So I'm a bit puzzled why Tom's min/max optimization bothers with the whole Initplan/Result business itself anyways. -- greg http://mit.edu/~gsstark/resume.pdf http://mit.edu/%7Egsstark/resume.pdf Yes, this is what I saw when I was testing this, and I think that it would be best to leave the decision of creating an initPlan/materialize to the optimization of the super-query. That's why I didn't bother to specifically put an InitPlan on top of the Aggregate in the new patch. Alan
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
Joshua Brindle wrote: Peter Eisentraut wrote: When it comes to larger features, this development group has a great deal of experience in implementing existing specifications, even relatively terrible ones like SQL or ODBC or Oracle compatibility. But the expected behavior has to be written down somewhere, endorsed by someone with authority. It can't just be someone's idea. Especially for features that are so complex, esoteric, invasive, and critical for security and performance. Who do you consider has authority? The security community has as many opinions as any other. There are papers written on mandatory access controls in rdbms's but they are mostly about multi-level security, which SELinux has but primarily uses type enforcement. The SELinux community are all on board with KaiGai's object model (the object classes and permissions and how they are enforced), there has been quite a bit of discussion about them over the years. Trusted RUBIX integrated SELinux using the object classes that KaiGai made for SEPostgres. Then document those in a reasonably formal sense. I don't think you can just say that the implementation is the spec. I should have thought that such a spec would actually appeal to the security community. So I think if you want to get anywhere with this, scrap the code, and start writing a specification. One with references. And then let's consider that one. Harsh. Yeah, it is a bit. But we're being asked to swallow a fairly large lump, so don't be surprised if we gag a bit. I haven't followed the entire history of this patch set closely, but we have over and over again emphasized the importance of getting community buyin before you start coding a large feature, and this is a *very* large feature. Reviewing the history briefly, it appears that KaiGai prepared an initial set of patches before ever approaching the Postgres community with it about 2 years ago. That is to some extent the source of the friction, I suspect. I'm also slightly surprised that some of the government and commercial players in this space aren't speaking up much. I should have thought this would generate some interest from players as disparate as RedHat and the NSA. 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
[HACKERS] git revisited
[changing subject, as we seem to have done] Tom Lane wrote: David Fetter da...@fetter.org writes: On Mon, Jul 20, 2009 at 01:00:12PM -0400, Tom Lane wrote: I might think about it when/if we move to git. As far as you're concerned, what's blocking that? Lack of committer familiarity with git, lack of a bulletproof migration process, uncertainty about preferred multi-branch patching techniques, uncertainty about buildfarm integration, etc etc. Do I really need to repeat the previous discussions to you? AFAIR none of the open questions have gotten resolved with any clarity. I have been derailed slightly by pressure of other work from making the buildfarm client git-capable. But it is still quite high on my TODO list. I'm not sure where we got to with doing some surgery on the CVS repo so that we can replicate all the tags and branches properly. Has someone fully identified what needs to be fixed so we can have all the tags? 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] SE-PostgreSQL?
David Fetter wrote: 2. Apart from Kohei-san and Stephen Frost, is anybody actually interested in having this feature at all? The features (both MAC, and row-level security), are interesting. * I've worked with organizations where MAC was a big deal. * I've had use cases where row-level security would be useful. * If this feature's a right step of getting PG into getting onto lists of EAL-certified databases like these: http://www.niap-ccevs.org/cc-scheme/vpl/?tech_name=DBMS it could make selling PG-backed solutions to some companies easier. I guess that'd count as a sales/PR feature? What I don't know is if this particular patch is the best step to getting any of those features. -- 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] fmgroids.h not installed by make install in VPATH
Peter Eisentraut wrote: On Wednesday 08 July 2009 02:09:20 Alvaro Herrera wrote: It seems our makefiles fail to install fmgroids.h by make install in a VPATH build. I think the solution is to treat fmgroids.h just like pg_config_os.h, i.e. add a line like this: $(INSTALL_DATA) utils/fmgroids.h '$(DESTDIR)/$(includedir_server)/utils' The fix looks right. Has it been applied? Applied and backpatched up to 8.2. (It didn't apply cleanly to 8.1). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] large object does not exist after pg_migrator
Bruce Momjian wrote: After a quick chat with Bruce it was determined that we don't freeze anything (it would be horrid for downtime if we did so in pg_migrator; and it would be useless if ran anywhere else). What we do is migrate pg_clog from the old cluster to the new. So never mind that hypothesis. Bruce noticed that the pg_dump/pg_migrator combo is failing to restore pg_largeobject's relfrozenxid. We're not sure how this is causing the errors Jamie is seeing, because what I think should happen is that scans of the table should fail with failures to open pg_clog files such-and-such, but not missing tuples ... Jamie, is it possible for you to apply the attached patch to the 8.4 server, install the new pg_dump, and run the test again to see if pg_largeobject is fixed? This patch properly sets the relfrozenxid in the system tables for each database. I have applied the attached patch to have pg_dump restore pg_largeobject.relfrozenxid in binary upgrade mode; backpatched to 8.4.X. This doesn't fix the reported problem, but it is still a bug. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/bin/pg_dump/pg_dump.c === RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.540 diff -c -c -r1.540 pg_dump.c *** src/bin/pg_dump/pg_dump.c 2 Jul 2009 21:34:32 - 1.540 --- src/bin/pg_dump/pg_dump.c 20 Jul 2009 20:51:34 - *** *** 34,39 --- 34,40 #include access/sysattr.h #include catalog/pg_cast.h #include catalog/pg_class.h + #include catalog/pg_largeobject.h #include catalog/pg_proc.h #include catalog/pg_trigger.h #include catalog/pg_type.h *** *** 1739,1744 --- 1740,1746 frozenxid); appendStringLiteralAH(creaQry, datname, AH); appendPQExpBuffer(creaQry, ;\n); + } appendPQExpBuffer(delQry, DROP DATABASE %s;\n, *** *** 1764,1769 --- 1766,1816 NULL, /* Dumper */ NULL); /* Dumper Arg */ + /* + * pg_largeobject comes from the old system intact, so set + * its relfrozenxid. + */ + if (binary_upgrade) + { + PGresult *lo_res; + PQExpBuffer loFrozenQry = createPQExpBuffer(); + PQExpBuffer loOutQry = createPQExpBuffer(); + int i_relfrozenxid; + + appendPQExpBuffer(loFrozenQry, SELECT relfrozenxid\n + FROM pg_catalog.pg_class\n + WHERE oid = %d;\n, + LargeObjectRelationId); + + lo_res = PQexec(g_conn, loFrozenQry-data); + check_sql_result(lo_res, g_conn, loFrozenQry-data, PGRES_TUPLES_OK); + + if (PQntuples(lo_res) != 1) + { + write_msg(NULL, dumpDatabase(): could not find pg_largeobject.relfrozenxid\n); + exit_nicely(); + } + + i_relfrozenxid = PQfnumber(lo_res, relfrozenxid); + + appendPQExpBuffer(loOutQry, \n-- For binary upgrade, set pg_largeobject relfrozenxid.\n); + appendPQExpBuffer(loOutQry, UPDATE pg_catalog.pg_class\n + SET relfrozenxid = '%u'\n + WHERE oid = %d;\n, + atoi(PQgetvalue(lo_res, 0, i_relfrozenxid)), + LargeObjectRelationId); + ArchiveEntry(AH, nilCatalogId, createDumpId(), + pg_largeobject, NULL, NULL, , + false, pg_largeobject, SECTION_PRE_DATA, + loOutQry-data, , NULL, + NULL, 0, + NULL, NULL); + + PQclear(lo_res); + destroyPQExpBuffer(loFrozenQry); + destroyPQExpBuffer(loOutQry); + } + /* Dump DB comment if any */ if (g_fout-remoteVersion = 80200) { -- 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 v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
On Mon, Jul 20, 2009 at 8:37 PM, Tom Lanet...@sss.pgh.pa.us wrote: Anyone want to see if they can beat that? Some testing on other architectures would help too. Hm, I took the three implementations so far (normal, unrolled, and clz) as well as the two from http://graphics.stanford.edu/~seander/bithacks.html#IntegerLogObvious and got some very strange results: normal: 1.494s clz: 2.214s unrolled: 2.966s lookup table: 0.001s float hack: 11.930s I can't see why the unrolled implementation is slower than the non-unrolled so I'm suspecting something's wrong with my #ifdefs but I don't see it. I do think the code I grabbed from the stanford page might be off-by-one for our purposes but I haven't looked closely at that. I also wonder if this microbenchmark is actually ok because it's testing the same value over and over so any branch-prediction will shine unrealistically well. -- greg http://mit.edu/~gsstark/resume.pdf /* * usage: time ./testit N * N is a repeat count, set it large enough to get repeatable timings */ #include stdio.h #include stdlib.h #define ALLOC_MINBITS 3 /* smallest chunk size is 8 bytes */ #define ALLOCSET_NUM_FREELISTS 11 /* number of calls to AllocSetFreeIndex with each result category */ static const int numoccurs[ALLOCSET_NUM_FREELISTS] = { #ifdef USE_64BIT_COUNTS 2139534, 5994692, 5711479, 3289034, 4550931, 2573389, 487566, 588470, 155148, 52750, 202597 #else 5190113, 5663980, 3573261, 4476398, 4246178, 1100634, 386501, 601654, 44884, 52372, 202801 #endif }; extern int AllocSetFreeIndex(size_t size); int main(int argc, char **argv) { int repeat = atoi(argv[1]); while (repeat-- 0) { int k; for (k = 0; k ALLOCSET_NUM_FREELISTS; k++) { int n = numoccurs[k]; size_t siz = (1 (ALLOC_MINBITS + k)); while (n-- 0) { AllocSetFreeIndex(siz); } } } return 0; } #include stdio.h #include stdlib.h #define ALLOC_MINBITS 3 /* smallest chunk size is 8 bytes */ #define ALLOCSET_NUM_FREELISTS 11 #define BITS_PER_BYTE 8 /* -- * AllocSetFreeIndex - * * Depending on the size of an allocation compute which freechunk * list of the alloc set it belongs to. Caller must have verified * that size = ALLOC_CHUNK_LIMIT. * -- */ int AllocSetFreeIndex(size_t size) { int idx = 0; if (size (1 ALLOC_MINBITS)) { size = (size - 1) ALLOC_MINBITS; #if HAVE_BUILTIN_CLZ idx = sizeof(unsigned int) * BITS_PER_BYTE - __builtin_clz((unsigned int)size); #elif TEST_FLOAT_HACK union { unsigned int u[2]; double d; } t; // temp t.u[__FLOAT_WORD_ORDER==LITTLE_ENDIAN] = 0x4330; t.u[__FLOAT_WORD_ORDER!=LITTLE_ENDIAN] = size; t.d -= 4503599627370496.0; idx = (t.u[__FLOAT_WORD_ORDER==LITTLE_ENDIAN] 20) - 0x3FF; #elif TEST_LOOKUP_TABLE #define LT(n) n, n, n, n, n, n, n, n, n, n, n, n, n, n, n, n static const char LogTable256[256] = { -1, 0, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, LT(4), LT(5), LT(5), LT(6), LT(6), LT(6), LT(6), LT(7), LT(7), LT(7), LT(7), LT(7), LT(7), LT(7), LT(7) }; unsigned int t, tt; // temporaries if (tt = size 16) { idx = (t = tt 8) ? 24 + LogTable256[t] : 16 + LogTable256[tt]; } else { idx = (t = size 8) ? 8 + LogTable256[t] : LogTable256[size]; } #elif TEST_UNROLL idx++; size = 1; if (size != 0) { idx++; size = 1; if (size != 0) { idx++; size = 1; if (size != 0) { idx++; size = 1; while (size != 0) { idx++; size = 1; } } } } #else do { idx++; size = 1; } while (size != 0); #endif } return idx; } -- 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] SE-PgSQL/tiny rev.2193
On Mon, Jul 20, 2009 at 3:44 PM, Joshua Brindlemet...@manicmethod.com wrote: Ron Mayer wrote: Joshua Brindle wrote: How many people are you looking for? Is there a number or are you waiting for a good feeling? snip Joshua - if you're still associated with Tresys - could someone who could speak for that company say what they think about this project? The seem quite in-the-loop on what SELinux customers want. I am capable of speaking for Tresys in this matter. We are very interested in this work and our US DoD customers need the capabilities that this project adds (assuming row level access controls are a possibility). This is very interesting, if by interested you mean that you are willing to commit time, money, and development resources to make it happen. If by interested you mean that you will use it if someone else does the work of implementing it, that's nice, but fundamentally doesn't help us very much. At least in my view, the problem with this patch set is that it's not committable, and it's not measurably closer to being committable than it was a year ago. I am not saying (and I don't necessarily think ANYONE is saying) this is a great patch, but I don't want to commit it because I hate SE-Linux. What I at least am saying is that this patch has serious problems that need to be fixed in order for it to be committed, and because KaiGai has not been able to fix those problems after over a year of back and forth, a new approach is needed here or we are dead in the water. I have attempted, on the relevant threads, to enumerate those problems as I see them. Mainly they have to do with hooks all over the code in strange and unmaintainable places, documentation that is written in poor English and is not easily understandable by people who are not already experts in SE-Linux, and a complete inability to get a patch that implements a useful subset of the total realm of SE-Linux desiderata that more or less matches up with what the existing PostgreSQL permissions structure already does. What we've established so far is that the following things should NOT be in the list of permissions that we attempt in the initial patch: - row-level security - complex DDL permissions But I think the following things probably should be: - tables - columns - sequences - functions - schemas - databases - tablespaces I'd be willing to negotiate on all but the first. I also agree with Peter's contention that a spec would be useful. If you could read a clear description of what the patch was going to do, then you could separate the problem of figuring out whether that was the right thing from the question of whether the patch actually did it. To restate my basic point: The problem with this patch is that there are plenty of people who are interested in *having* it, but the only person who seems to be interested in *writing* it is KaiGai, and that isn't working. ...Robert -- 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] Commitfest Code Sprint with PUGs
(cc'ing Webb Sprague because I believe this was actually his idea...don't want to take credit for it if so. :) ) On Fri, Jul 10, 2009 at 4:06 PM, Josh Berkusj...@agliodbs.com wrote: Gabrielle of PDXPUG volunteered that PUG to hold a code sprint in coordination with a commitfest sometime soon. For that event, the PDXPUG members would take on a dozen or so patches, compile and review them and submit the results. You also mentioned having someone In the Know (tm) available on IRC in case we have problems - I would like that if it's possible. Obviously PDXPUG won't be ready for July 15th. But could we plan this for the Sept. 15th CF? I poked around the wiki but am unclear on the timeline - would we have to be finished by Sept 15 or start on Sept 15? Also, do any other PUGs want to do this? I'll add whatever we learn to the wiki in the interest of encouraging others. gabrielle -- 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] SE-PgSQL/tiny rev.2193
On Mon, Jul 20, 2009 at 8:44 PM, Joshua Brindlemet...@manicmethod.com wrote: I am capable of speaking for Tresys in this matter. We are very interested in this work and our US DoD customers need the capabilities that this project adds (assuming row level access controls are a possibility). I'm kind of curious about how these features get used. What specific problems do they solve? -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] large object does not exist after pg_migrator
Jamie Fox wrote: Here's what I have found that got broken during pg_migrate: In two side by side databases (an 8.3.7 copy and 8.4.0 migrated with pg_migrator) the pg_largeobject table has the same number of rows. However, in the 8.4 database any select for an loid in pg_largeobject returns zero rows. If I select all loids to a file, and compare to select all loids from 8.3.7 they're the same. When I select != an loid it seems to exclude the one and return the rest, but all other comparisons , or = return zero rows. Or I'm completely batty. Dereferencing via lo_open of blob_data (an oid) in other tables fails in the 8.4 database with 'large object id does not exist'. Oh, so maybe it's pg_largeobject's index that's borked ... Did you try reindexing it? How are we transferring pg_largeobject, and are we transferring its index too? Hi - REINDEX INDEX pg_largeobject_loid_pn_index; This seems to have fixed the problem, lo_open of lob data is working again - now to see how vacuumlo likes it. I have applied the attached patch to pg_migrator to properly migrate the pg_largeobject index. I have added large object comment migration as a TODO item. This eliminates the last known bug in pg_migrator. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ? tools ? log ? src/pg_migrator Index: TODO === RCS file: /cvsroot/pg-migrator/pg_migrator/TODO,v retrieving revision 1.15 diff -c -r1.15 TODO *** TODO 2 Jun 2009 15:15:38 - 1.15 --- TODO 20 Jul 2009 21:55:58 - *** *** 1,3 --- 1,4 o support migration from Postgres 8.2 to 8.4? o create pg_dump custom format for rebuilds so it can be done in parallel o remove copy_dir code, or use it + o handle large object comments Index: src/pg_migrator.h === RCS file: /cvsroot/pg-migrator/pg_migrator/src/pg_migrator.h,v retrieving revision 1.64 diff -c -r1.64 pg_migrator.h *** src/pg_migrator.h 20 Jul 2009 18:57:12 - 1.64 --- src/pg_migrator.h 20 Jul 2009 21:55:58 - *** *** 202,207 --- 202,208 char *pg_version_str; /* string PG_VERSION of cluster */ Oid pg_database_oid; /* OID of pg_database relation */ Oid pg_largeobject_oid; /* OID of pg_largeobject relation */ + Oid pg_largeobject_index_oid; /* OID of pg_largeobject index */ char *libpath; /* pathname for cluster's pkglibdir */ } ClusterInfo; Index: src/relfilenode.c === RCS file: /cvsroot/pg-migrator/pg_migrator/src/relfilenode.c,v retrieving revision 1.26 diff -c -r1.26 relfilenode.c *** src/relfilenode.c 2 Jul 2009 23:30:27 - 1.26 --- src/relfilenode.c 20 Jul 2009 21:55:58 - *** *** 164,184 { DbInfo *new_db = newdb_arr-dbs[dbnum]; DbInfo *old_db = dbarr_lookup_db(olddb_arr, new_db-db_name); FileNameMap *mappings; int n_maps; pageCnvCtx *pageConverter = NULL; - assert(old_db); - n_maps = 0; mappings = gen_db_file_maps(ctx, old_db, new_db, n_maps, old_pgdata, new_pgdata); if (n_maps) { - char old_file[MAXPGPATH]; - char new_file[MAXPGPATH]; - print_maps(ctx, mappings, n_maps, new_db-db_name); #ifdef PAGE_CONVERSION --- 164,181 { DbInfo *new_db = newdb_arr-dbs[dbnum]; DbInfo *old_db = dbarr_lookup_db(olddb_arr, new_db-db_name); + char old_file[MAXPGPATH]; + char new_file[MAXPGPATH]; FileNameMap *mappings; int n_maps; pageCnvCtx *pageConverter = NULL; n_maps = 0; mappings = gen_db_file_maps(ctx, old_db, new_db, n_maps, old_pgdata, new_pgdata); if (n_maps) { print_maps(ctx, mappings, n_maps, new_db-db_name); #ifdef PAGE_CONVERSION *** *** 187,206 transfer_single_new_db(ctx, pageConverter, mappings, n_maps); pg_free(mappings); - - /* - * The pg_largeobject system table is treated as a user table. - * Since we already know its OID we simply link it - */ - snprintf(old_file, sizeof(old_file), %s/base/%u/%u, old_pgdata, - old_db-db_oid, ctx-old.pg_largeobject_oid); - snprintf(new_file, sizeof(new_file), %s/base/%u/%u, new_pgdata, - new_db-db_oid, ctx-new.pg_largeobject_oid); - - unlink(new_file); - transfer_relfile(ctx, pageConverter, old_file, new_file, pg_catalog, - pg_largeobject, pg_catalog, pg_largeobject); } } return msg; --- 184,214 transfer_single_new_db(ctx, pageConverter, mappings, n_maps); pg_free(mappings); } + + /* + * The pg_largeobject system table is treated as a user table. + * Since we already know its OID
Re: [HACKERS] WIP: Deferrable unique constraints
2009/7/20 Alvaro Herrera alvhe...@commandprompt.com: Seems related to the new list in AfterTriggerSaveEvent, which is used in ways that seem to conflict with its header comment ... Reading the comment for that function, I think it is quite misleading - mainly because the meaning of the word event mutates half-way through the function. A better description might be: /* -- * AfterTriggerSaveEvent() * * Called by ExecA[RS]...Triggers() to queue up the triggers that should * be fired for an event. * * NOTE: this is called whenever there are any triggers associated with * the event (even if they are disabled). This function decides which * triggers actually need to be queued. * -- */ - Dean -- 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 v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
Doh, I forgot this bit. Will rerun tests now. On Mon, Jul 20, 2009 at 8:25 PM, Tom Lanet...@sss.pgh.pa.us wrote: (Note: I found out that it's necessary to split the test into two files --- otherwise gcc will inline AllocSetFreeIndex and partially const-fold the work, leading to skewed results.) -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY WITH CSV FORCE QUOTE * -- REVIEW
Itagaki-san, On Apple OS 10.5: 1. new patch applied cleanly 2. new patch built cleanly 3. regression tests pass 4. Tested following operations: postgres=# COPY marchlog TO '/tmp/marchlog1.csv' with csv header; COPY 81097 postgres=# COPY marchlog TO '/tmp/marchlog2.csv' with csv header force quote *; COPY 81097 postgres=# COPY marchlog TO '/tmp/marchlog3.csv' with csv header force quote process_id; COPY 81097 postgres=# COPY marchlog TO '/tmp/marchlog4.csv' with csv force quote *; COPY 81097 postgres=# COPY marchlog TO '/tmp/marchlog5.csv' with force quote *; ERROR: COPY force quote available only in CSV mode STATEMENT: COPY marchlog TO '/tmp/marchlog5.csv' with force quote *; ERROR: COPY force quote available only in CSV mode postgres=# COPY reloadlog FROM '/tmp/marchlog2.csv' with csv header; COPY 81097 postgres-# \copy marchlog TO '/tmp/marchlog5.csv' with csv force quote *; postgres-# 5. Regression tests for FORCE QUOTE present. 6. Docs present; not sure how good they are, see prior discussion. Stuff someone else should do: a. review code b. review code format I am done with this review. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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 v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
Greg Stark gsst...@mit.edu writes: I also wonder if this microbenchmark is actually ok because it's testing the same value over and over so any branch-prediction will shine unrealistically well. Yeah, that is a good point --- and it would benefit the unrolled loop more than the other versions. We should probably revise the test harness so it mixes the size requests a bit. I'm not sure of a suitably simple way to do that, though. 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] COPY WITH CSV FORCE QUOTE *
On 7/16/09 1:55 PM, Andrew Dunstan wrote: Well, somebody had better suggest a syntax for it, preferably without adding yet another keyword. Actually, all that needs to happen is for NULL AS to accept '' as a string. Right now that produces: ERROR: CSV quote character must not appear in the NULL specification What's the issue with that? I can see how NULL AS '' would break things, but if I wanted NULL AS 'Josh' shouldn't I be able to have it? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] SE-PgSQL/tiny rev.2193
Greg Stark wrote: On Mon, Jul 20, 2009 at 8:44 PM, Joshua Brindlemet...@manicmethod.com wrote: I am capable of speaking for Tresys in this matter. We are very interested in this work and our US DoD customers need the capabilities that this project adds (assuming row level access controls are a possibility). I'm kind of curious about how these features get used. What specific problems do they solve? I would like to introduce a key word: data flow control (DFC). Most of mandatory access control system focuses on the direction of data, and tries to control it when user/client gives a request to object manager (such as OS-kernel, RDBMS, ...). MAC system assigns a security label on all the objects managed to identify its sensitivity level. It typically has hierarchical relationship, such as secret is more sensitive than classified, and classified is more than unclassified, for example. secret classified unclassified When user requires the object manager to read a certain object with a security label being equal or lower than user's security label, MAC system within the object manager allows it. In this case, the direction of data is from object to subject. (Object) ---(read)--- (Subject) When user requires the object manager to write a certain object with a security label being equal to user's security label, MAC system allows it. In this case, the direction of data is from subject to object. (Subject) ---(write)--- (Object) This constraint enables to prevent to leak a sensitive data to others with lower sensitive level. Note that subject never has data with higher than himself, and he cannot write his data to objects lower than himself (to prevent information leaks, by malicious internals) and higher than himself (to prevent manipulation). The security certification (ISO/IEC15408) also contains DFC as a part of functional requirements. (Please note that it does not requires DFC all the producets; it depends on the environment to be used.) Oracle Label Security is a product which provides DFC mechanism using row-level access controls based on security labels, and its security certification report mentions its DFC features and access control rules in the FDP_IFF section. SE-PostgreSQL also tries to apply such kind of DFC policies. In addition, its security policy is integrated with operating system. It enables to handle multiple object manager seamlessly. For example, we cannot prevent a user with classified security label to insert a sensitive information into database and unclassified user to see them later, without SE-PgSQL. BTW, Oracle Label Security is priced at about $13,000/CPU in Japan. I believe security sensitive customers feel it fair for their purpose. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] SE-PgSQL/tiny rev.2193
How many people are you looking for? Is there a number or are you waiting for a good feeling? The problem is not the number of people who like the patch, but the number of people who are willing to refactor and maintain it. Right now, if NEC decided to abandon Postgres, or if they decided that they don't like the changes we make in order to merge it, we'd have nobody to maintain it. Given the amount of money which the security community represents, it seems like at least a few of these people could become, or sponsor, code maintainers. I was hoping to support some of this effort through Sun when SEPostgres was introduced, but that didn't happen. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] COPY WITH CSV FORCE QUOTE *
Josh Berkus wrote: On 7/16/09 1:55 PM, Andrew Dunstan wrote: Well, somebody had better suggest a syntax for it, preferably without adding yet another keyword. Actually, all that needs to happen is for NULL AS to accept '' as a string. Right now that produces: ERROR: CSV quote character must not appear in the NULL specification What's the issue with that? I can see how NULL AS '' would break things, but if I wanted NULL AS 'Josh' shouldn't I be able to have it? See previous thread on -patches/-hackers allow CSV quote in NULL in July 2007. Quite apart from any other objection, doing what you suggest will not be column-by-column, like what I suggested. It's an all or nothing deal. 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] [PATCH] SE-PgSQL/tiny rev.2193
Robert Haas wrote: I have attempted, on the relevant threads, to enumerate those problems as I see them. Mainly they have to do with hooks all over the code in strange and unmaintainable places, documentation that is written in poor English and is not easily understandable by people who are not already experts in SE-Linux, and a complete inability to get a patch that implements a useful subset of the total realm of SE-Linux desiderata that more or less matches up with what the existing PostgreSQL permissions structure already does. What we've established so far is that the following things should NOT be in the list of permissions that we attempt in the initial patch: - row-level security - complex DDL permissions Is the complex DDL permissions mean something like db_xxx:{create}, db_xxx:{relabelfrom relabelto} and others? If so, I can agree to implement these checks at the later patch. However, please note that the initial patch cannot achieve actual security in this case, because it means anyone can change security label of objects. But I think the following things probably should be: - tables - columns - sequences - functions - schemas - databases - tablespaces I also think it is reasonable to apply access controls on the types of object classes at the initial pach. I'd be willing to negotiate on all but the first. I also agree with Peter's contention that a spec would be useful. If you could read a clear description of what the patch was going to do, then you could separate the problem of figuring out whether that was the right thing from the question of whether the patch actually did it. I can also agree with the suggestion. The specifications (from viewpoint of the developer) will introduces the fundamental principles to be implemented, and it will figure out what implementation is better. As I noted before, I've been flexible about how SE-PgSQL is implemented as far as it can perform SELinux's security model correctly. Please for a several days. I'll describe it. -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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 v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
On Tue, Jul 21, 2009 at 12:07 AM, Tom Lanet...@sss.pgh.pa.us wrote: Greg Stark gsst...@mit.edu writes: I also wonder if this microbenchmark is actually ok because it's testing the same value over and over so any branch-prediction will shine unrealistically well. Yeah, that is a good point --- and it would benefit the unrolled loop more than the other versions. We should probably revise the test harness so it mixes the size requests a bit. I'm not sure of a suitably simple way to do that, though. Well it was a bit of a pain but I filled an array with (1/1000 scaled down) values and then permuted them. I also went ahead and set the low-order bits to random values since the lookup table based algorithm might be affected by it. The results are a bit disappointing on my machine, only the CLZ and lookup table come out significantly ahead: $ ./testit 10 0: 620 1: 4949 2: 5378 3: 3560 4: 4426 5: 4218 6: 1098 7: 387 8: 599 9: 44 10: 52 clz 1.530s lookup table 1.720s float hack 4.424s unrolled 5.280s normal 5.369s -- greg http://mit.edu/~gsstark/resume.pdf /* * usage: time ./testit N * N is a repeat count, set it large enough to get repeatable timings */ #include stdio.h #include stdlib.h #include sys/time.h #define ALLOC_MINBITS 3 /* smallest chunk size is 8 bytes */ #define ALLOCSET_NUM_FREELISTS 11 /* number of calls to AllocSetFreeIndex with each result category */ static const int numoccurs[ALLOCSET_NUM_FREELISTS] = { #ifdef USE_64BIT_COUNTS 2139534, 5994692, 5711479, 3289034, 4550931, 2573389, 487566, 588470, 155148, 52750, 202597 #else 5190113, 5663980, 3573261, 4476398, 4246178, 1100634, 386501, 601654, 44884, 52372, 202801 #endif }; extern int AllocSetFreeIndex_clz(size_t size); extern int AllocSetFreeIndex_lt(size_t size); extern int AllocSetFreeIndex_fh(size_t size); extern int AllocSetFreeIndex_ur(size_t size); extern int AllocSetFreeIndex(size_t size); int main(int argc, char **argv) { int repeat = atoi(argv[1]); int k,l,m, min=1000, total=0; size_t *arr; struct timeval before, after; double elapsed; // for (k=0; k ALLOCSET_NUM_FREELISTS; k++) // if (numoccurs[k] min) // min = numoccurs[k]; for (k=0; k ALLOCSET_NUM_FREELISTS; k++) total += numoccurs[k]/min; arr = malloc(sizeof(size_t)*total); m = 0; for (k=0; k ALLOCSET_NUM_FREELISTS; k++) for (l=0; l numoccurs[k]/min; l++) { int p = 1 (ALLOC_MINBITS + k); p |= (p*2 - 1) random(); arr[m++] = p; } for (m=0; m total; m++) { int r = random()%total; if (r != m) { int tmp = arr[r]; arr[r] = arr[m]; arr[m] = tmp; } } repeat *= min; static int occurs[ALLOCSET_NUM_FREELISTS]; for (m=0; m total; m++) { int retval_clz = AllocSetFreeIndex_clz(arr[m]); int retval_fh = AllocSetFreeIndex_fh(arr[m]); int retval_lt = AllocSetFreeIndex_lt(arr[m]); int retval_ur = AllocSetFreeIndex_ur(arr[m]); int retval = AllocSetFreeIndex(arr[m]); if (retval != retval_clz || retval != retval_fh || retval != retval_lt || retval != retval_ur) printf(%08x: %4d %4d %4d %4d %4d\n, arr[m], retval, retval_clz, retval_fh, retval_lt, retval_ur); else occurs[retval]++; } for (k=0; kALLOCSET_NUM_FREELISTS; k++) printf(%4d: %8d\n, k, occurs[k]); gettimeofday(before, NULL); for(l=0; lrepeat; l++) for (m=0; m total; m++) AllocSetFreeIndex_clz(arr[m]); gettimeofday(after, NULL); elapsed = after.tv_sec - before.tv_sec; elapsed += (after.tv_usec - before.tv_usec)/100.0; printf(%20s %.3fs\n, clz, elapsed); gettimeofday(before, NULL); for(l=0; lrepeat; l++) for (m=0; m total; m++) AllocSetFreeIndex_lt(arr[m]); gettimeofday(after, NULL); elapsed = after.tv_sec - before.tv_sec; elapsed += (after.tv_usec - before.tv_usec)/100.0; printf(%20s %.3fs\n, lookup table, elapsed); gettimeofday(before, NULL); for(l=0; lrepeat; l++) for (m=0; m total; m++) AllocSetFreeIndex_fh(arr[m]); gettimeofday(after, NULL); elapsed = after.tv_sec - before.tv_sec; elapsed += (after.tv_usec - before.tv_usec)/100.0; printf(%20s %.3fs\n, float hack, elapsed); gettimeofday(before, NULL); for(l=0; lrepeat; l++) for (m=0; m total; m++) AllocSetFreeIndex_ur(arr[m]); gettimeofday(after, NULL); elapsed = after.tv_sec - before.tv_sec; elapsed += (after.tv_usec - before.tv_usec)/100.0; printf(%20s %.3fs\n, unrolled, elapsed); gettimeofday(before, NULL); for(l=0; lrepeat; l++) for (m=0; m total; m++) AllocSetFreeIndex(arr[m]); gettimeofday(after, NULL); elapsed = after.tv_sec - before.tv_sec; elapsed += (after.tv_usec - before.tv_usec)/100.0; printf(%20s %.3fs\n, normal, elapsed); return 0; } #include stdio.h #include stdlib.h #define ALLOC_MINBITS 3 /* smallest chunk size is 8 bytes */ #define
Re: [HACKERS] [PATCH v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
Greg Stark gsst...@mit.edu writes: Well it was a bit of a pain but I filled an array with (1/1000 scaled down) values and then permuted them. I also went ahead and set the low-order bits to random values since the lookup table based algorithm might be affected by it. The results are a bit disappointing on my machine, only the CLZ and lookup table come out significantly ahead: clz 1.530s lookup table 1.720s float hack 4.424s unrolled 5.280s normal 5.369s It strikes me that we could assume that the values are 64K and hence drop the first case in the lookup table code. I've added that variant and get these results on my machines: x86_64 (Xeon): clz 15.357s lookup table 16.582s small lookup table 16.705s float hack 25.138s unrolled 64.630s normal 79.025s PPC: clz 3.842s lookup table 7.298s small lookup table 8.799s float hack 19.418s unrolled 7.656s normal 8.949s HPPA: clz (n/a) lookup table 11.515s small lookup table 10.803s float hack 16.502s unrolled 17.632s normal 19.754s Not sure why the small lookup table variant actually seems slower than the original on two of these machines; it can hardly be slower in reality since it's strictly less code. Maybe some weird code-alignment issue? It also seems weird that the x86_64 is now showing a much bigger gap between clz and normal than before. I don't see how branch prediction would do much for the normal code. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
Hi Greg, Thanks for the benchmark app, thought I'd pitch in with some ppc results: clz 1.530s lookup table 1.720s float hack 4.424s unrolled 5.280s normal 5.369s POWER5+: clz 2.046s lookup table 2.188s float hack 7.786s unrolled 6.353s normal 7.033s POWER6: clz 1.063s lookup table 1.199s float hack 3.575s unrolled 2.290s normal 3.456s Cheers, Jeremy -- 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] SE-PgSQL/tiny rev.2193
2009/7/20 KaiGai Kohei kai...@ak.jp.nec.com: Robert Haas wrote: - row-level security - complex DDL permissions Is the complex DDL permissions mean something like db_xxx:{create}, db_xxx:{relabelfrom relabelto} and others? If so, I can agree to implement these checks at the later patch. However, please note that the initial patch cannot achieve actual security in this case, because it means anyone can change security label of objects. I'm not qualified to answer this question, and that's exactly why we need more documentation of what this patch tries to do and why (i.e. a spec). What I was specifically referring to is things like db_column:{drop}, which are much more specific than anything PostgreSQL currently offers to control DDL. I think we should make an attempt to get objects labelled in a reasonable way (I did not like the approach your latest patch took of just assigning everything a default label), which might include relabelling also, but certainly doesn't include things like distinguishing between different kinds of DDL operations. I'm not really up on SELinux terminology (this is another thing that needs to be covered in the documentation, and isn't) but maybe something like db_table:{owner}. I also agree with Peter's contention that a spec would be useful. If you could read a clear description of what the patch was going to do, then you could separate the problem of figuring out whether that was the right thing from the question of whether the patch actually did it. I can also agree with the suggestion. The specifications (from viewpoint of the developer) will introduces the fundamental principles to be implemented, and it will figure out what implementation is better. As I noted before, I've been flexible about how SE-PgSQL is implemented as far as it can perform SELinux's security model correctly. Please for a several days. I'll describe it. I really, really think you need to find someone to help you with the documentation. As I've said before, your English is a lot better than my Japanese, but the current documentation is just hard to read. More than that, writing good documentation is HARD, and there are not a ton of people who can do it well. It seems to me that the documentation for this project could require as much work as the actual code. And this is an excellent time to bring up the whole issue of community involvement again. Many advocacy-type folks and end-users and security folks have written in over the last year to say how much they want this feature, but as far as anyone can tell KaiGai is the only one doing any actual development to make it happen. Considering how the alternative is, or so we're told, to spend $13,000/year (per CPU?) for a similar Oracle feature, one might suppose that there would be developers lining up to volunteer to help make this happen, and companies making sponsorship dollars available to fund work by major PostgreSQL contributors to get this whipped into shape. No? Well, at the very least, one would hope that some of the people who say what they want this feature to do could help document it, since they presumably understand what it's supposed to do (if not, why are they so sure they want it?). But so far the only person who has done anything like this, as far as I'm aware, is me. And that's pretty funny when you consider that I've learned enough about SE-Linux to do exactly one thing: shut it off. What I want for documentation of this feature is something like the Database Roles and Privileges chapter of the current documentation. You can read this chapter from beginning to end and have a pretty good idea how to manage permissions in PostgreSQL. SE-Linux is complex enough that you might have to refer to other sources of information for certain topics (e.g. policy writing), because a full overview of those topics might exceed the scope of our documentation. But it should still be possible to start knowing nothing, read the chapter, and have a pretty good idea how all the pieces fit together, at least as far as PostgreSQL is concerned. I would go so far as to suggest that we use the willingness of someone from the community to volunteer to help KaiGai get this put together as a litmus test for support for this patch. If someone sent in a patch for Hot Standby or Streaming Rep tomorrow that was done except for the documentation, how long do you think it would take them to get some volunteers to help finish the docs? I'd bet less than a day. On the other hand, it appears to me that we have had more people put more time into __builtin_clz() over the last three months than SE-PostgreSQL. __builtin_clz() has had multiple people benchmarking it and testing it, giving feedback on the patch, etc. More often than not, nobody even responds to KaiGai's patch set; it looks to me like the last response that he got prior to when I started reviewing this for CommitFest 2009-07 was a note from David Wheeler admiring
Re: [HACKERS] Duplicate key value error
Dickson S. Guedes lis...@guedesoft.net wrote: Hi Takahiro, i'm the reviewer of your patch, and the following are my comments about it: Thank you for reviewing. An updated patch is attached. The patch was applied totalty clean to CVS HEAD and compiled in Ubuntu 8.04, Ubuntu 8.10 and AIX 5.3, but failed in follow tests: Would be good to modify the outputs to expect a new DETAIL: line. I adjusted expected output of regression test in the new patch. I'm thinking if could be better to shows Key (my_key)=(...) instead Key (...)=(...) -- well, i don't know how much people uses a key with more 512B and how often it is to they don't know wich key it is, (just reading a log, for example) to we consider this important. I modified the format logic to use StringInfo and don't cut off the message in 512 bytes. Key names and values will be never into '...'. I changed both both report_unique_violation() and ri_ReportViolation(). On the other hand there is a comment by Tom [1] about to refactor this so it's not btree-specific, but could be used by other index AMs [1] http://archives.postgresql.org/pgsql-hackers/2009-04/msg00234.php I exported the reporting function to itup.h. [include/access/itup.h] extern void report_unique_violation(Relation rel, IndexTuple itup); Regards, --- ITAGAKI Takahiro NTT Open Source Software Center report_dupkey-20090721.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] visibility maps and heap_prune
On Wed, Jul 15, 2009 at 11:44 PM, Alex Hunsakerbada...@gmail.com wrote: On Mon, Dec 8, 2008 at 06:56, Pavan Deolaseepavan.deola...@gmail.com wrote: Here is a patch which implements this. The PD_ALL_VISIBLE flag is set if all tuples in the page are visible to all transactions and there are no DEAD line pointers in the page. The second check is required so that VACUUM takes up the page. We could slightly distinguish the two cases (one where the page requires vacuuming only because of DEAD line pointers and the other where the page-tuples do not require any visibility checks), but I thought its not worth the complexity. Hi! I was round robin assigned to review this. So take my comments with the grain of salt (or novice HOT salt) they deserve. Pavan, are you planning to respond to Alex's comments and/or update this patch? ...Robert -- 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_restore --clean vs. large object
Jaime Casanova jcasa...@systemguards.com.ec wrote: i think this one could be applied, just as is... there is no need for docs, because the issue being fixed is not documented... maybe that should be in doc of older releases? Sure, it was an undocumented behavior. Should we need to add details of this patch to documentation? [pg_restore.sgml] -c --clean Clean (drop) database objects before recreating them. (8.5) Also drop large objects with same oids. (older) Large objects with same oids are not dropped. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] Sampling profiler updated
Dimitri Fontaine dfonta...@hi-media.com wrote: WARNING: condition stack overflow: 10 So I'm going to change patch state to Returned with Feedback as I guess we'll need to talk about the issue and find a way to solve it, and I don't think this state prevent from getting back to the patch in this same fest. Oops, I must fix it. I didn't test well the default stack depth (10). I'd better not have limitation of condition stack. BTW, I hope I have enough feedbacks from reviewers if the patch is Returned with Feedback. Are there any issues I need to fix or improve by the next commitfest? I feel we don't have enough discussion about the feature, like: * Is is useful enough? or are there any idea to be more useful? * Is it ok we have two versions of profiling? (this and dtrace probes) * Is the quality of the patch enough in terms of implmentation? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers