Re: [HACKERS] 8.4 release planning
On Mon, Jan 26, 2009 at 8:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: So, some feedback to make this decision more difficult: Users: care about HS more than anything else in the world. I don't think this is correct. There are certainly a lot of users who would like an in-core replication solution, but HS by itself is not that --- you also need (near) real-time log shipping, which we have already decided to punt to 8.5. That being the case, I think the argument that HS is a must-have feature for 8.4 is actually rather weak. I don't buy that. Sure, sync-rep would be the icing on the cake, but HS with a small archive_timeout (even of the order of 10 or 15 minutes) would have been extremely useful on a number of systems I used to run. -- Dave Page EnterpriseDB UK: 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] Table Partitioning Feature
Hi Emmanuel, On 1/26/09, Emmanuel Cecchet m...@frogthinker.org wrote: Hi Amit, I overlooked the fact that you dropped composite partitions and subpartitions template from the proposal presented in http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php. Is it because this is too hard to support? or you don't see any immediate need for it? We do intend to implement composite partitioning, but the delivery dates are not yet decided. I feel that simple forms of sub-partitioning can be realized using composite partitioning, hence the implementation of sub-partitioning is not planned. Thanks, Amit Thanks, Emmanuel Hi Emmanuel, Please find my comments in-lined: On 1/23/09, *Emmanuel Cecchet* m...@frogthinker.org mailto: m...@frogthinker.org wrote: Amit, You might want to put this on the http://wiki.postgresql.org/wiki/Table_partitioning wiki page. Sure. How does your timeline look like for this implementation? The implementation is planned as follows: - Partition table commands ++ An intermediate patch in Feb end ++ Final patch in mid March - Global Index: Mid March - Optimizer changes for partitioned table: May I would be happy to contribute C triggers to your implementation. From what I understood in http://archives.postgresql.org/pgsql-hackers/2008-01/msg00269.php, you already have an implementation that parses the grammar and generates rules as if someone had written them. Is this code available? We have just started with the implementation, i will post the grammar rules next week. Regarding the use of triggers to push/move data to partitions, what if someone declares triggers on partitions? Especially if you have subpartitions, let's consider the case where there is a trigger on the parent, child and grandchild. If I do an insert in the parent, the user trigger on the parent will be executed, then the partition trigger that decides to move to the grandchild. Are we going to bypass the child trigger? We are not supporting sub-partitioning - There is just one level of partitioning. If we also want fast COPY operations on partitioned table, we could have an optimized implementation that could bypass triggers and move the tuple directly to the appropriate child table. We will definitely consider to implement fast COPY after we are done with the planned tasks. Thanks, Amit Thanks for this big contribution, Emmanuel Hi, We are implementing table partitioning feature to support - the attached commands. The syntax conforms to most of the suggestion mentioned in http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php, barring the following: -- Specification of partition names is optional. System will be able to generate partition names in such cases. -- sub partitioning We are using pgsql triggers to push/move data to appropriate partitions, but we will definitely consider moving to C language triggers as suggested by manu. - Global non-partitioned indexes (that will extend all the partitions). - Foreign key support for tables referring to partitioned tables. Please feel free to post your comments and suggestions. Thanks, Amit Persistent Systems --Emmanuel Cecchet FTO @ Frog Thinker Open Source Development Consulting -- Web: http://www.frogthinker.org email: m...@frogthinker.org mailto:m...@frogthinker.org Skype: emmanuel_cecchet -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development Consulting -- Web: http://www.frogthinker.org email: m...@frogthinker.org Skype: emmanuel_cecchet
Re: [HACKERS] 8.4 release planning
On Mon, 2009-01-26 at 19:21 -0500, Tom Lane wrote: Then why has *nobody* stepped up to review the design, much less the whole patch? The plain truth is that no one appears to care enough to expend any real effort. I've spent some time looking at it and have made all the comments I wished to make. The design seems clear and fit for purpose, having read KaiGai's excellent Wiki description of how it all fits together and also read some PDF links Bruce sent out. But I've not had time to look at the whole patch and my contacts have not had sufficient time to do anything meaningful with it either. If we can minimise the impact on normal running and it doesn't have any implications for robustness, it should be OK. Surely we should give it a quick review to see if it has any gotchas. If not, and KaiGai is willing to commit to supporting it, then should be good to go. KaiGai isn't a home hacker, he's a lead developer for a major multinational, so we should be able to take his word if he says he will continue to contribute fixes if problems are found. If we don't commit to him and his company then they won't commit to us either. The process works like this: software gets developed, then it gets certified. If its not certified, then Undercover Elephant will not be used by the secret people. We can't answer the will it be certified? question objectively yet. If we have someone willing to write the software and put it forward for certification then we should trust that it probably will pass certification and if it doesn't we will see further patches to allow that to happen. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Dave Page wrote: On Mon, Jan 26, 2009 at 8:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: So, some feedback to make this decision more difficult: Users: care about HS more than anything else in the world. I don't think this is correct. There are certainly a lot of users who would like an in-core replication solution, but HS by itself is not that --- you also need (near) real-time log shipping, which we have already decided to punt to 8.5. That being the case, I think the argument that HS is a must-have feature for 8.4 is actually rather weak. I don't buy that. Sure, sync-rep would be the icing on the cake, but HS with a small archive_timeout (even of the order of 10 or 15 minutes) would have been extremely useful on a number of systems I used to run. +1 I have customers who want exactly this - a simple to administer, query-able slave that does DDL transparently and is up to date within a controllable time frame. Bluntly, it looks like a killer feature. regards Mark -- 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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
--On Montag, Januar 26, 2009 20:03:41 -0800 Josh Berkus j...@agliodbs.com wrote: Jaime, Bernd, having said that, i don't think that inventing new syntax is the way to go... a reloption seems better (thinking a little more, it could be a problem if the user changes the reloptions of an already created view) There's also the issue with backup/restore: we need some kind of syntax for restoring a read-only view which doesn't depend on command ordering. So we need a ALTER VIEW SET READ ONLY or similar. Hence my proposal with CREATE [OR REPLACE] [READ ONLY|UPDATABLE] VIEW This can easily be extended to ALTER VIEW SET [READ ONLY|UPDATABLE]. Besides other issues already mentioned, this looks more logical to me, since this is going to change the behavior of a view completely. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch to add Windows 7 support
The attached patch adds support for the Windows 7 beta which we've had a few reports of incompatibility with. When we startup using pg_ctl on Windows, we create a job object (a logical grouping of processes on Windows) to which we apply various security options. One of these (JOB_OBJECT_UILIMIT_HANDLES) is used to prevent our processes seeing handles belonging to processes outside of our job, however, when we run under the service control manager, this causes the postmaster to exit immeditately for no apparent reason. I'm not entirely sure what has change in the SCM to cause this yet (Windows 7 documentation is somewhat thin on the ground at the moment), but the patch avoids theporblem by only setting JOB_OBJECT_UILIMIT_HANDLES on earlier OSs. Tested on CVS head, but should probably be backpatched to 8.3 to avoid more bug reports. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com windows7.diff 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] 8.4 release planning
On Jan 27, 2009, at 2:41 AM, Mark Kirkwood wrote: Dave Page wrote: On Mon, Jan 26, 2009 at 8:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: So, some feedback to make this decision more difficult: Users: care about HS more than anything else in the world. I don't think this is correct. There are certainly a lot of users who would like an in-core replication solution, but HS by itself is not that --- you also need (near) real-time log shipping, which we have already decided to punt to 8.5. That being the case, I think the argument that HS is a must-have feature for 8.4 is actually rather weak. I don't buy that. Sure, sync-rep would be the icing on the cake, but HS with a small archive_timeout (even of the order of 10 or 15 minutes) would have been extremely useful on a number of systems I used to run. +1 I have customers who want exactly this - a simple to administer, query-able slave that does DDL transparently and is up to date within a controllable time frame. Bluntly, it looks like a killer feature. regards +1 So, I am just a lurker here. I mostly follow hackers to find out if any new features are coming out that will make it worth upgrading, and to keep up on any backwards compatibly changes that I should be aware of. I am on 8.1 and it performs well and no features added since then have seemed worth downing the whole system to do the upgrade for. However, a simple to administer, query-able slave that does DDL transparently and is up to date within a controllable time frame is something that would undoubtably make it worth the upgrade. Whatever version this feature makes it into will probably be the one I will upgrade to. Of course this is just one developer giving you anecdotal evidence and there are obviously many concerns other than just how in demand it is, but I just wanted to register my vote that this is a very sought after feature and it is hard for me to imagine a situation (especially for a 24x7 web application) where having an easy to admin hot standby server wouldn't help your local DBA sleep better at night. Rick -- 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 to add Windows 7 support
Dave Page wrote: The attached patch adds support for the Windows 7 beta which we've had a few reports of incompatibility with. When we startup using pg_ctl on Windows, we create a job object (a logical grouping of processes on Windows) to which we apply various security options. One of these (JOB_OBJECT_UILIMIT_HANDLES) is used to prevent our processes seeing handles belonging to processes outside of our job, however, when we run under the service control manager, this causes the postmaster to exit immeditately for no apparent reason. I'm not entirely sure what has change in the SCM to cause this yet (Windows 7 documentation is somewhat thin on the ground at the moment), but the patch avoids theporblem by only setting JOB_OBJECT_UILIMIT_HANDLES on earlier OSs. Tested on CVS head, but should probably be backpatched to 8.3 to avoid more bug reports. No objections here, but seems we should revisit this after Windows 7 is released, and revert if they've fixed the underlying problem during the beta period. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to add Windows 7 support
On Tue, Jan 27, 2009 at 11:04 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Dave Page wrote: The attached patch adds support for the Windows 7 beta which we've had a few reports of incompatibility with. When we startup using pg_ctl on Windows, we create a job object (a logical grouping of processes on Windows) to which we apply various security options. One of these (JOB_OBJECT_UILIMIT_HANDLES) is used to prevent our processes seeing handles belonging to processes outside of our job, however, when we run under the service control manager, this causes the postmaster to exit immeditately for no apparent reason. I'm not entirely sure what has change in the SCM to cause this yet (Windows 7 documentation is somewhat thin on the ground at the moment), but the patch avoids theporblem by only setting JOB_OBJECT_UILIMIT_HANDLES on earlier OSs. Tested on CVS head, but should probably be backpatched to 8.3 to avoid more bug reports. No objections here, but seems we should revisit this after Windows 7 is released, and revert if they've fixed the underlying problem during the beta period. Agreed. -- Dave Page EnterpriseDB UK: 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] 8.4 release planning
* Tom Lane (t...@sss.pgh.pa.us) wrote: SEPostgres seems qualitatively different to me, though. I think PG people have avoided reviewing it because (a) they weren't interested in it and (b) they knew they were unqualified to review it. Meanwhile it's emerging that the selinux people don't feel qualified to review it either. I'm not quite sure what to do about that. But throw it in there on faith doesn't sound like an appealing answer, and I've got no idea how long it will take to work out a non-faith-based answer. Erm, I have to say here that this strikes me as rather unfair. Perhaps I'm wrong, but I suspect KaiGai feels pretty good about the patch and his qualifications in both the PG realm and the SELinux realm. He's asking the PG folks to review it because that's the process that the PG community (through the CommitFest, etc) has laid out for getting a patch included upstream. I'm confident KaiGai isn't going to just disappear into the ether if the patch is committed. Sure, it'd be nice if 4 or 5 other SELinux developers got in and understood the PG code well enough to implement such a patch, but I think the combination of KaiGai (overall), a seperate SELinux hacker (for the security design and SELinux side of it), and a PG committer (for where the hooks are placed and how), reviewing the patch and being comfortable with it is quite sufficient for a high quality result. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Patch to add Windows 7 support
On Tuesday 27 January 2009 12:34:56 Dave Page wrote: I'm not entirely sure what has change in the SCM to cause this yet (Windows 7 documentation is somewhat thin on the ground at the moment), but the patch avoids theporblem by only setting JOB_OBJECT_UILIMIT_HANDLES on earlier OSs. Doesn't this effectively mean, we relax the security settings because we don't understand why we are getting errors? Sounds fishy. -- 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 to add Windows 7 support
Peter Eisentraut wrote: On Tuesday 27 January 2009 12:34:56 Dave Page wrote: I'm not entirely sure what has change in the SCM to cause this yet (Windows 7 documentation is somewhat thin on the ground at the moment), but the patch avoids theporblem by only setting JOB_OBJECT_UILIMIT_HANDLES on earlier OSs. Does the SCM just terminate the process, or does it at some point give us an error code? Doesn't this effectively mean, we relax the security settings because we don't understand why we are getting errors? Sounds fishy. In theory, yes. However, it only affects USER handles, which is basically windows, buttons etc. So what it basically means is that a hacked PostgreSQL process can be used to send messages, for example, to other windows running in the same session. Which only affects things if pg is started from the commandline, and not as a service. I don't think it's enough that we need to care about it really. I'm thinking we could perhaps even just never set that, and not bother with the version check... But perhaps we should set it only when launching as a service, and not when running from the commandline? //Magnus -- 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 to add Windows 7 support
On Tue, Jan 27, 2009 at 11:26 AM, Peter Eisentraut pete...@gmx.net wrote: On Tuesday 27 January 2009 12:34:56 Dave Page wrote: I'm not entirely sure what has change in the SCM to cause this yet (Windows 7 documentation is somewhat thin on the ground at the moment), but the patch avoids theporblem by only setting JOB_OBJECT_UILIMIT_HANDLES on earlier OSs. Doesn't this effectively mean, we relax the security settings because we don't understand why we are getting errors? Sounds fishy. Yes, essentially. I have a suspicion that Microsoft have tightened the security of that option, such that if we use it we can no longer see the handle to the service control manager (which it owns of course), but I have no way to prove that. However; - We only use job objects on = XP. On Windows 2000/NT4, we don't use them at all so we don't set any of the related security options on those platforms. - I don't believe this option gives us much additional security. It doesn't secure PostgreSQL in any way, it prevents PostgreSQL from seeing the user handles owned by other jobs in the same session. To make any use of those, the PostgreSQL installation would have to be severely compromised anyway, which would give other, easier paths into the system, besides which, when running as a service we're in our own session anyway. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to add Windows 7 support
On Tue, Jan 27, 2009 at 11:38 AM, Magnus Hagander mag...@hagander.net wrote: Peter Eisentraut wrote: On Tuesday 27 January 2009 12:34:56 Dave Page wrote: I'm not entirely sure what has change in the SCM to cause this yet (Windows 7 documentation is somewhat thin on the ground at the moment), but the patch avoids theporblem by only setting JOB_OBJECT_UILIMIT_HANDLES on earlier OSs. Does the SCM just terminate the process, or does it at some point give us an error code? Just the ubiquitous DLL initialization error. I don't think it's enough that we need to care about it really. I'm thinking we could perhaps even just never set that, and not bother with the version check... That was how I originally coded it, but figured we might as well set it if we can - it's not like it's expensive to do. But perhaps we should set it only when launching as a service, and not when running from the commandline? We could. I'm not sure there's a great deal of need - most people will run as a service, and it won't make any difference for those that start the postmaster directly. -- Dave Page EnterpriseDB UK: 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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
On Saturday 24 January 2009 02:17:13 Tom Lane wrote: 2. You don't want those rules, so you delete them, leaving you with the traditional behavior where attempted inserts etc on the view fail. This was never meant to be supported. If you don't want updates on the rules to succeed, don't grant privileges. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Space reservation v02
I attached second version of space reservation patch. You can see first version here: http://archives.postgresql.org/pgsql-hackers/2008-12/msg00886.php I thought about Heikki'es comments and I removed all catalog changes, because there are not necessary to be in pg_class. Instead of pg_preugrade script should create own schema (pg_upgrade) and tables on its needs. This patch implement settings like relation options. Tom had objection about this approach. I can rewrite it and extend pg_class instead. However before I will do it I would like to know opinion about rest of the patch. And last thing is most important. Space reservation MUST TO be implemented if we want to have 8.4-8.5 upgrade. Else we will be at the begging... Zdenek diff -Nrc pgsql_spacereserve.7b2d095bfec6/src/backend/access/common/reloptions.c pgsql_spacereserve/src/backend/access/common/reloptions.c *** pgsql_spacereserve.7b2d095bfec6/src/backend/access/common/reloptions.c 2009-01-23 14:53:17.553968280 +0100 --- pgsql_spacereserve/src/backend/access/common/reloptions.c 2009-01-23 14:53:17.785452704 +0100 *** *** 86,91 --- 86,108 }, GIST_DEFAULT_FILLFACTOR, GIST_MIN_FILLFACTOR, 100 }, + { + { + rs_perpage, + Page reserved space per page for in-place upgrade in bytes, + RELOPT_KIND_ALL + }, + 0, 0, BLCKSZ/4 + }, + { + { + rs_pertuple, + Page reserved space per tuple for in-place upgrade in bytes, + RELOPT_KIND_ALL + }, + 0, 0, BLCKSZ/16 + }, + /* list terminator */ { { NULL } } }; *** *** 592,598 /* Build a list of expected options, based on kind */ for (i = 0; relOpts[i]; i++) ! if (relOpts[i]-kind == kind) numoptions++; if (numoptions == 0) --- 609,615 /* Build a list of expected options, based on kind */ for (i = 0; relOpts[i]; i++) ! if (relOpts[i]-kind == kind || relOpts[i]-kind == RELOPT_KIND_ALL) numoptions++; if (numoptions == 0) *** *** 605,611 for (i = 0, j = 0; relOpts[i]; i++) { ! if (relOpts[i]-kind == kind) { reloptions[j].gen = relOpts[i]; reloptions[j].isset = false; --- 622,628 for (i = 0, j = 0; relOpts[i]; i++) { ! if (relOpts[i]-kind == kind || relOpts[i]-kind == RELOPT_KIND_ALL) { reloptions[j].gen = relOpts[i]; reloptions[j].isset = false; *** *** 868,874 /* ! * Option parser for anything that uses StdRdOptions (i.e. fillfactor only) */ bytea * default_reloptions(Datum reloptions, bool validate, relopt_kind kind) --- 885,891 /* ! * Option parser for anything that uses StdRdOptions (i.e. fillfactor) */ bytea * default_reloptions(Datum reloptions, bool validate, relopt_kind kind) *** *** 877,883 StdRdOptions *rdopts; intnumoptions; relopt_parse_elt tab[] = { ! {fillfactor, RELOPT_TYPE_INT, offsetof(StdRdOptions, fillfactor)} }; options = parseRelOptions(reloptions, validate, kind, numoptions); --- 894,902 StdRdOptions *rdopts; intnumoptions; relopt_parse_elt tab[] = { ! {fillfactor, RELOPT_TYPE_INT, offsetof(StdRdOptions, fillfactor)}, ! {rs_perpage, RELOPT_TYPE_INT, offsetof(StdRdOptions, rs_perpage)}, ! {rs_pertuple, RELOPT_TYPE_INT, offsetof(StdRdOptions, rs_pertuple)} }; options = parseRelOptions(reloptions, validate, kind, numoptions); diff -Nrc pgsql_spacereserve.7b2d095bfec6/src/backend/access/gin/ginentrypage.c pgsql_spacereserve/src/backend/access/gin/ginentrypage.c *** pgsql_spacereserve.7b2d095bfec6/src/backend/access/gin/ginentrypage.c 2009-01-23 14:53:17.557611451 +0100 --- pgsql_spacereserve/src/backend/access/gin/ginentrypage.c 2009-01-23 14:53:17.785727884 +0100 *** *** 314,320 itupsz = MAXALIGN(IndexTupleSize(itup)) + sizeof(ItemIdData); } ! if (PageGetFreeSpace(page) + itupsz = MAXALIGN(IndexTupleSize(btree-entry)) + sizeof(ItemIdData)) return true; return false; --- 314,323 itupsz = MAXALIGN(IndexTupleSize(itup)) + sizeof(ItemIdData); } ! if (PageGetFreeSpace(page, ! RelationGetReservedSpacePerPage(btree-index), ! RelationGetReservedSpacePerTuple(btree-index)) + itupsz ! = MAXALIGN(IndexTupleSize(btree-entry)) + sizeof(ItemIdData)) return true; return false; diff -Nrc pgsql_spacereserve.7b2d095bfec6/src/backend/access/gist/gist.c pgsql_spacereserve/src/backend/access/gist/gist.c *** pgsql_spacereserve.7b2d095bfec6/src/backend/access/gist/gist.c 2009-01-23 14:53:17.566159107 +0100 --- pgsql_spacereserve/src/backend/access/gist/gist.c 2009-01-23 14:53:17.786021437 +0100 *** *** 299,305 * XXX: If we want to change fillfactors between node and leaf, fillfactor * = (is_leaf ? state-leaf_fillfactor : state-node_fillfactor) */ ! if (gistnospace(state-stack-page, state-itup, state-ituplen, is_leaf ? InvalidOffsetNumber :
Re: [HACKERS] binary array and record recv
On Tuesday 18 December 2007 18:30:22 Tom Lane wrote: Arguably, pg_dump from an older version should make sure that the auto rules should NOT get created, else it is failing to preserve an older view's behavior. We extend properties of objects all the time. That is why we make new releases. No one is required to use the new properties. Should pg_dump also make sure that tables imported from an older version are not usable for recursive unions or window functions, thus preserving the older table's behavior? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Simon Riggs wrote: On Mon, 2009-01-26 at 19:21 -0500, Tom Lane wrote: Then why has *nobody* stepped up to review the design, much less the whole patch? The plain truth is that no one appears to care enough to expend any real effort. I've spent some time looking at it and have made all the comments I wished to make. The design seems clear and fit for purpose, having read KaiGai's excellent Wiki description of how it all fits together and also read some PDF links Bruce sent out. Thanks for your comment, although you also have a tough work. But I've not had time to look at the whole patch and my contacts have not had sufficient time to do anything meaningful with it either. If we can minimise the impact on normal running and it doesn't have any implications for robustness, it should be OK. Surely we should give it a quick review to see if it has any gotchas. If not, and KaiGai is willing to commit to supporting it, then should be good to go. KaiGai isn't a home hacker, he's a lead developer for a major multinational, so we should be able to take his word if he says he will continue to contribute fixes if problems are found. If we don't commit to him and his company then they won't commit to us either. Needless to say, I will continue to support the feature. I cannot understand why is it necessary to disappear from here. At least, a binary with --enable-selinux passes all regression test with/without pgace_feature=selinux. The benchmark results I have is a bit legacy, so it is necessary to record it again, but I don't think it gives significant implications on normal running (pgace_feature=none). (Yes, it indeed gives us performance loss with selinux-enabled, but we assume performance is not the first priority in this case.) The process works like this: software gets developed, then it gets certified. If its not certified, then Undercover Elephant will not be used by the secret people. We can't answer the will it be certified? question objectively yet. If we have someone willing to write the software and put it forward for certification then we should trust that it probably will pass certification and if it doesn't we will see further patches to allow that to happen. -- 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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
On Tuesday 27 January 2009 05:39:48 Jaime Casanova wrote: a view should be updatable by default if the query expression is updatable... what we need is something to make a view READ ONLY even if it should be updatable by spec... A view is read-only if you don't grant any write permissions on it. -- 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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
On Tuesday 27 January 2009 00:21:08 Jaime Casanova wrote: On Mon, Jan 26, 2009 at 5:18 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Bernd Helmle maili...@oopsware.de writes: Or what about CREATE [OR REPLACE] [UPDATABLE] VIEW ... ? This looks closer to TEMP|TEMPORARY VIEW, which we already have. But per spec, UPDATABLE should be the default (if not now, then eventually). Are you proposing CREATE [OR REPLACE] [[NOT] UPDATABLE] VIEW ... ? Seems confusing. UNUPDATABLE? :-) BTW, how do we handle cases where the query cannot be updatable, e.g. aggregates? Do we throw a warning? yes. we detect that and send a warning saying that there not be any rules No, you get a notice *if* the view is updatable. You don't get anything if the view is not. -- 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] Compiler warnings fix
ITAGAKI Takahiro wrote: Here is a patch to surpress compiler warnings in pg_locale.c and pg_regress.c. There are following warnings if nls is enabled: pg_locale.c: In function `pg_perm_setlocale': pg_locale.c:161: warning: assignment discards qualifiers from pointer target type and if nls is disabled: pg_locale.c:615: warning: 'IsoLocaleName' defined but not used There is also a warning in pg_regress.c: pg_regress.c: In function `wait_for_tests': pg_regress.c:1367: warning: passing arg 2 of `GetExitCodeProcess' from incompatible pointer type Applied (as two separate patches since I missed that there were two files initially). I agree with other comments that #ifdef:ing on LC_MESSAGES may not be the greatest-looking solution, but the code that calls it is ifdef:ed that way, so mimicking that seems like the right thing to do at this time. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
On Tuesday 27 January 2009 00:42:32 Ron Mayer wrote: If it were just as easy for us to pull from a all 'pending-patches' for-commit-fest-nov that pass regression tests branch, I'd happily pull from that instead. Considering that most patches don't come with regression tests, this would accomplish very little. And even those patches that did come with regression tests (e.g., updatable views) need a design analysis much more than running an automated test suite. Ultimately, it does come down to human work. -- 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] mingw check hung
Andrew Dunstan wrote: Andrew Dunstan wrote: Something happened about 80 hours ago that caused my mingw buildfarm member (gcc 3.4.2 on Win XP Pro SP2) to hang at the check stage. It looks like it's hung in initdb. I wonder if it could be this commit: Log Message: --- Make win32 builds always do SetEnvironmentVariable() when doing putenv(). Also, if linked against other versions than the default MSVCRT library (for example the MSVC build which links against MSVCRT80), also update the cache in the default MSVCRT at the same time. I note that the change is not apparently limited to MSVC builds. The MSVC animal that runs on the same machine appears unaffected. I see one other mingw buildfarm member that is having problems that started a few days ago (yak) and another that looks like it is a few hours overdue to report, so it might also be hung (vaquita). Further to this: I see that vaquita has now reported in, and is happy. Also, I can run happily on my Vista box (vaquita is also a Vista box). I therefore suspect that we have a problem specifically with XP (both dawn_bat and yak are XP boxes). Have you managed to get gdb running on that box, and if so, can you try to grab a stacktrace? If not, try a stacktrace from process explorer. It doesn't actually work with mingw, but it gives you a hint based on DLL exports... //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] log_duration_sample config option patch
Hello. The attached patch has made it more feasible for us to gather profiling data on a production system for analysis with pgFouine. It has been written against PostgreSQL 8.3.5 and tested on Linux. Comments welcome. timo From a4d5b489f462ad31b62774ec98af08f184cb0754 Mon Sep 17 00:00:00 2001 From: Timo Savola timo.sav...@dynamoid.com Date: Thu, 22 Jan 2009 13:02:10 +0200 Subject: [PATCH] log_duration_sample config option When used with the log_duration or the log_min_duration_statement option, it specifies the percentage of statements to log using random sampling. --- src/backend/tcop/postgres.c | 30 +++--- src/backend/utils/misc/guc.c | 12 src/include/utils/guc.h |2 ++ 3 files changed, 41 insertions(+), 3 deletions(-) diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 12ebce4..14f472a 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -22,6 +22,9 @@ #include unistd.h #include signal.h #include fcntl.h +#include byteswap.h +#include endian.h +#include sys/time.h #include sys/socket.h #ifdef HAVE_SYS_SELECT_H #include sys/select.h @@ -2001,6 +2004,8 @@ check_log_statement(List *stmt_list) int check_log_duration(char *msec_str, bool was_logged) { + int retval = 0; + if (log_duration || log_min_duration_statement = 0) { long secs; @@ -2028,13 +2033,32 @@ check_log_duration(char *msec_str, bool was_logged) snprintf(msec_str, 32, %ld.%03d, secs * 1000 + msecs, usecs % 1000); if (exceeded !was_logged) -return 2; +retval = 2; else -return 1; +retval = 1; + } + + if (retval 0 log_duration_sample 1.0) + { + static uint64_t seed; + + if (seed == 0) + { +struct timeval tv; + +gettimeofday(tv, NULL); +seed = (uint64_t) tv.tv_sec * 100 + tv.tv_usec; +#if BYTE_ORDER != LITTLE_ENDIAN +bswap_64(seed); +#endif + } + + if (erand48((unsigned short *) seed) log_duration_sample) +retval = 0; } } - return 0; + return retval; } /* diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index b1c6e85..1e76cd5 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -225,6 +225,8 @@ int tcp_keepalives_idle; int tcp_keepalives_interval; int tcp_keepalives_count; +double log_duration_sample = 1.0; + /* * These variables are all dummies that don't do anything, except in some * cases provide the value for SHOW to display. The real state is elsewhere @@ -1884,6 +1886,16 @@ static struct config_real ConfigureNamesReal[] = 0.5, 0.0, 1.0, NULL, NULL }, + { + {log_duration_sample, PGC_SUSET, LOGGING_WHEN, + gettext_noop(Sets the sampling frequency (probability) used for + logging statement durations.), + gettext_noop(1.0 prints all queries.) + }, + log_duration_sample, + 1.0, 0.0, 1.0, NULL, NULL + }, + /* End-of-list marker */ { {NULL, 0, 0, NULL, NULL}, NULL, 0.0, 0.0, 0.0, NULL, NULL diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h index 49b88f5..fde6a26 100644 --- a/src/include/utils/guc.h +++ b/src/include/utils/guc.h @@ -145,6 +145,8 @@ extern int tcp_keepalives_idle; extern int tcp_keepalives_interval; extern int tcp_keepalives_count; +extern double log_duration_sample; + extern void SetConfigOption(const char *name, const char *value, GucContext context, GucSource source); -- 1.5.6.5 -- 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] binary array and record recv
On Tuesday 27 January 2009 14:07:26 Peter Eisentraut wrote: On Tuesday 18 December 2007 18:30:22 Tom Lane wrote: Arguably, pg_dump from an older version should make sure that the auto rules should NOT get created, else it is failing to preserve an older view's behavior. We extend properties of objects all the time. That is why we make new releases. No one is required to use the new properties. Should pg_dump also make sure that tables imported from an older version are not usable for recursive unions or window functions, thus preserving the older table's behavior? This curiously came through with a wrong subject. The point had already been made, though. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Index Scan cost expression
While trying to figure out an appropriate cost expression function for Thick indexes, i learned that we are using Mackert and Lohman formula (described in their paper Index Scans Using a Finite LRU Buffer: A Validated I/O Model, ACM Transactions on Database Systems). The paper's result is as follows: # Heap Pages fetched from disk for x index probes = min(2TDx/(2T+Dx), T)when T = b 2TDx/(2T+Dx)when T b and Dx = 2Tb/(2T-b) b + (Dx - 2Tb/(2T-b))*(T-b)/T when T b and Dx 2Tb/(2T-b) where, T = # pages in table N = # tuples in table D = avg. number of an index value is repeated in the table. (duplication factor), and b buffer/cache size Please note that the above result only computes _heap_ page reads. The above expression is used by index_pages_fetched() function to compute index scan cost. The function however doesn't account for cost of index page scans. On average an index probe will require (h-1) page reads from disk, where h is the height of the B-tree (when # index probes # index key values). I can post the details of the derivation of this result, if required. I am planning to use a similar expression for Thick indexes cost expressions. Upon taking a cursory look at the cost functions of other operators, I realized that available memory (effective_cache_size) is not considered for estimating the costs of hash/sort/NLjoin/etc. Why is that the case? Regards, Amit Persistent Systems -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Commitfest infrastructure (was Re: [HACKERS] 8.4 release planning)
Robert Haas escribió: I think that it would probably be pretty easy to write a webapp to replace the CommitFest web page that basically did the same thing but with a bit more structure around it - with database tables like commitfest, patch, patch_version, patch_comment, and patch_review. I think I might even be willing to write such a webapp if someone would be willing to provide the infrastructure. The CommitFest web page was really useful this time around, but it's not conducive to any kind of automated pull. Hey, if you're willing to do it, we're certainly accepting proposals. The current wiki-based CommitFest is supposed to be just a stop-gap. It was started not only to support 8.4 development, but also as a test of the Commitfest idea itself. This has proven so successful that it's clear we should be going somewhere with it. As for somewhere to host it, we certainly have some servers; not tons, but probably enough. Some of them even have Postgres running on it. -- 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
[HACKERS] pg_upgrade project status
The current project is not in good shape. Feature freeze is coming and nothing is committed. Currently there are three patches in the game: 1) Space reservation http://archives.postgresql.org/pgsql-hackers/2008-12/msg00886.php http://archives.postgresql.org/pgsql-hackers/2009-01/msg02031.php This patch is mandatory for page online conversion and MUST TO be part of postgreSQL 8.4. if not ... then we will be at the beginning next year. I sent updated version today. 2) pg_upgrade.sh http://archives.postgresql.org/pgsql-hackers/2008-12/msg00248.php Pg_upgrade.sh is shell script for catalog conversion. It works for 8.3-8.4 upgrade. It will be useful while we will not have better solution. Disadvantage is that it is korn shell script. The idea is to rewrite it in PERL which is more portable, but I'm not PERL expert and currently there is no workable solution. And what is deadline for it? We can delivery it later with 8.4.1, but best time for testing is during betas and RC period. 3) preupgrade script http://archives.postgresql.org/pgsql-hackers/2008-12/msg01273.php I sent WIP version of this script, which shows how reservation space feature will be used. This part is not important now. It will be important for 8.4-8.5 upgrade and cannot be finished until 8.5beta. Thats all Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Commitfest infrastructure (was Re: [HACKERS] 8.4 release planning)
On Tue, Jan 27, 2009 at 1:42 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: As for somewhere to host it, we certainly have some servers; not tons, but probably enough. Some of them even have Postgres running on it. We can certainly host an app under postgresql.org. The bigger issue will be speccing it to meet the requirements of the community without getting bogged down in bike shedding. -- Dave Page EnterpriseDB UK: 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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
--On Dienstag, Januar 27, 2009 14:04:05 +0200 Peter Eisentraut pete...@gmx.net wrote: a view should be updatable by default if the query expression is updatable... what we need is something to make a view READ ONLY even if it should be updatable by spec... A view is read-only if you don't grant any write permissions on it. What i'm seeing here is a very divergent understanding what a read-only view is: old-school PostgreSQL-Users would expect a read-only view to have no write action installed. If we want to follow the standard closely, they need to be installed automatically, changing this behavior, hence the wish to have a syntax to restore the old behavior (e.g. for pg_dump). I'm unsure what the correct approach looks like, but it seems we need a compromise. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Commitfest infrastructure (was Re: [HACKERS] 8.4 release planning)
Dave Page wrote: On Tue, Jan 27, 2009 at 1:42 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: As for somewhere to host it, we certainly have some servers; not tons, but probably enough. Some of them even have Postgres running on it. We can certainly host an app under postgresql.org. The bigger issue will be speccing it to meet the requirements of the community without getting bogged down in bike shedding. I have started some very trivial work around this a while ago with the intent to get something simple up and working before too much bike shedding is done. I'll contact Robert off-list to discuss that. If somebody else - who actively works with what we have now!! - is interested in that discussion, let me know. Will obviously take it on-list before any decisions are made. So far I'm just talking about discussing a prototype. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Commitfest infrastructure (was Re: [HACKERS] 8.4 release planning)
On 1/27/09, Alvaro Herrera alvhe...@commandprompt.com wrote: Robert Haas escribió: I think that it would probably be pretty easy to write a webapp to replace the CommitFest web page that basically did the same thing but with a bit more structure around it - with database tables like commitfest, patch, patch_version, patch_comment, and patch_review. I think I might even be willing to write such a webapp if someone would be willing to provide the infrastructure. The CommitFest web page was really useful this time around, but it's not conducive to any kind of automated pull. Hey, if you're willing to do it, we're certainly accepting proposals. The current wiki-based CommitFest is supposed to be just a stop-gap. It was started not only to support 8.4 development, but also as a test of the Commitfest idea itself. This has proven so successful that it's clear we should be going somewhere with it. As for somewhere to host it, we certainly have some servers; not tons, but probably enough. Some of them even have Postgres running on it. Such app already exists: http://ozlabs.org/~jk/projects/patchwork/ So it's a matter of just setting it up. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hot standby, recovery infrastructure
Regarding this comment: + /* +* Prior to 8.4 we wrote a Shutdown Checkpoint at the end of recovery. +* This could add minutes to the startup time, so we want bgwriter +* to perform it. This then frees the Startup process to complete so we can +* allow transactions and WAL inserts. We still write a checkpoint, but +* it will be an online checkpoint. Online checkpoints have a redo +* location that can be prior to the actual checkpoint record. So we want +* to derive that redo location *before* we let anybody else write WAL, +* otherwise we might miss some WAL records if we crash. +*/ Does this describe a failure case or something that would cause corruption? The tone of the message implies so, but I don't see anything wrong with deriving the redo location for the first checkpoint the usual way. I belive the case of missing some WAL records refers to the possibility that someone connects to the database and does a WAL logged change before the first checkpoint starts. But if we then crash before the checkpoint finishes, we'll start crash recovery from the previous restartpoint/checkpoint as usual, and replay that WAL record as well. And if the first checkpoint finishes, the redo ptr of that checkpoint is after that WAL record, and those changes are safely on disk. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
On Sunday 25 January 2009 19:06:50 Tom Lane wrote: Particularly with regard to hot standby, which by any sane reading was not close to being committable on 1 November (a fortiori from the fact that it's *still* not committable despite large amounts of later work). I'm also feeling that we are not in a position to commit SE-Postgres in a timely fashion; which is not KaiGai-san's fault, rather that of the community which has taken nearly zero interest in that patch. If we want to ensure that 8.5 development opens soon, what we have to do is reject those two patches, revert updatable views, and finish up the other stuff (which is all small and could likely be dealt with in a week or two). Updatable views is reverted. I agree that we should reject the rest and prepare a release. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Bruce Momjian píše v po 26. 01. 2009 v 23:02 -0500: OK, time for me to chime in. I think the outstanding commit-fest items can be broken down into four sections: o Log streaming o Hot standby o SE-PostgreSQL o Others You omit pg_upgrade. Does it mean that this project is already killed for 8.4? Thanks Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
On Tuesday 27 January 2009 02:21:41 Tom Lane wrote: Then why has *nobody* stepped up to review the design, much less the whole patch? The plain truth is that no one appears to care enough to expend any real effort. But this patch is far too large and invasive to accept on the basis that only one guy understands it and will/might continue to maintain it. As one of the earlier reviewers, I think the design is OK, but the way the implementation is presented was not acceptable, and very little has been accomplished in terms of reacting to our comments. For example, where is the SQL row security feature, which should have been designed, implemented, and committed separately, in the opinion of most commentaries. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCHES] [HACKERS] Infrastructure changes for recovery
(replying to a very old message, since I just bumped into this in review) Alvaro Herrera wrote: Simon Riggs wrote: On Fri, 2008-09-12 at 14:14 -0400, Alvaro Herrera wrote: Simon Riggs wrote: --- 5716,5725 CheckpointStats.ckpt_sync_end_t, sync_secs, sync_usecs); ! elog(LOG, %s complete: wrote %d buffers (%.1f%%); %d transaction log file(s) added, %d removed, %d recycled; write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s, +(checkpoint ? checkpoint : restartpoint), CheckpointStats.ckpt_bufs_written, (double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers, CheckpointStats.ckpt_segs_added, Very minor nit: this really needs a rework. All I changed was the word restartpoint... its otherwise identical to existing message. I'd rather not change that. The new message is not translatable, the original was. Doesn't really matter since it's an elog(), not ereport(). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
On Tue, Jan 27, 2009 at 2:01 PM, Peter Eisentraut pete...@gmx.net wrote: Updatable views is reverted. I agree that we should reject the rest and prepare a release. That will send a fine message to those companies that have sponsored development work - that we will arbitrarily reject large patches that have been worked on following the procedures that we require. We must at least have the solid belief (of a committer that that has done a proper review) that a patch cannot be polished in an appropriate timeframe, or another justifiable reason for rejecting rather than vague handwaving, guesswork and estimates based on email traffic. If we do not, we will rapidly find that no company wants to sponsor features for PostgreSQL in the future for fear that their money will be wasted even if they jump through all the right hoops. -- Dave Page EnterpriseDB UK: 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
[HACKERS] log_duration_sample config option patch
Sorry for the attachment; here's the patch inline. timo From a4d5b489f462ad31b62774ec98af08f184cb0754 Mon Sep 17 00:00:00 2001 From: Timo Savola timo.sav...@dynamoid.com Date: Thu, 22 Jan 2009 13:02:10 +0200 Subject: [PATCH] log_duration_sample config option When used with the log_duration or the log_min_duration_statement option, it specifies the percentage of statements to log using random sampling. --- src/backend/tcop/postgres.c | 30 +++--- src/backend/utils/misc/guc.c | 12 src/include/utils/guc.h |2 ++ 3 files changed, 41 insertions(+), 3 deletions(-) diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 12ebce4..14f472a 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -22,6 +22,9 @@ #include unistd.h #include signal.h #include fcntl.h +#include byteswap.h +#include endian.h +#include sys/time.h #include sys/socket.h #ifdef HAVE_SYS_SELECT_H #include sys/select.h @@ -2001,6 +2004,8 @@ check_log_statement(List *stmt_list) int check_log_duration(char *msec_str, bool was_logged) { + int retval = 0; + if (log_duration || log_min_duration_statement = 0) { longsecs; @@ -2028,13 +2033,32 @@ check_log_duration(char *msec_str, bool was_logged) snprintf(msec_str, 32, %ld.%03d, secs * 1000 + msecs, usecs % 1000); if (exceeded !was_logged) - return 2; + retval = 2; else - return 1; + retval = 1; + } + + if (retval 0 log_duration_sample 1.0) + { + static uint64_t seed; + + if (seed == 0) + { + struct timeval tv; + + gettimeofday(tv, NULL); + seed = (uint64_t) tv.tv_sec * 100 + tv.tv_usec; +#if BYTE_ORDER != LITTLE_ENDIAN + bswap_64(seed); +#endif + } + + if (erand48((unsigned short *) seed) log_duration_sample) + retval = 0; } } - return 0; + return retval; } /* diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index b1c6e85..1e76cd5 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -225,6 +225,8 @@ int tcp_keepalives_idle; inttcp_keepalives_interval; inttcp_keepalives_count; +double log_duration_sample = 1.0; + /* * These variables are all dummies that don't do anything, except in some * cases provide the value for SHOW to display. The real state is elsewhere @@ -1884,6 +1886,16 @@ static struct config_real ConfigureNamesReal[] = 0.5, 0.0, 1.0, NULL, NULL }, + { + {log_duration_sample, PGC_SUSET, LOGGING_WHEN, + gettext_noop(Sets the sampling frequency (probability) used for +logging statement durations.), + gettext_noop(1.0 prints all queries.) + }, + log_duration_sample, + 1.0, 0.0, 1.0, NULL, NULL + }, + /* End-of-list marker */ { {NULL, 0, 0, NULL, NULL}, NULL, 0.0, 0.0, 0.0, NULL, NULL diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h index 49b88f5..fde6a26 100644 --- a/src/include/utils/guc.h +++ b/src/include/utils/guc.h @@ -145,6 +145,8 @@ extern int tcp_keepalives_idle; extern int tcp_keepalives_interval; extern int tcp_keepalives_count; +extern double log_duration_sample; + extern void SetConfigOption(const char *name, const char *value, GucContext context, GucSource source); -- 1.5.6.5 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Simon Riggs wrote: The process works like this: software gets developed, then it gets certified. If its not certified, then Undercover Elephant will not be used by the secret people. We can't answer the will it be certified? question objectively yet. If we have someone willing to write the software and put it forward for certification then we should trust that it probably will pass certification and if it doesn't we will see further patches to allow that to happen. For what it's worth, we can see that there are indeed Postgres forks on the Common Criteria certified list. http://www.commoncriteriaportal.org/products_DB.html PostgreSQL Certified Version V8.1.5 for Linux ManufacturerAssurance level Certification date NTT DATA CORPORATIONEAL122-MAR-07 Certification report c0089_ecvr.pdf http://www.commoncriteriaportal.org/files/epfiles/c0089_ecvr.pdf though at EAL1 they're quite far from the EAL4+ that DB2, Oracle, etc get. That someone went through the effort suggests that there's at least some interest in getting security certifications for postgres. It'd be interesting to hear from whomever at NTT was involved with that certification, if SEPostgreSQL would have either made that process easier or help postgres achieve a higher level. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
Dave Page wrote: On Mon, Jan 26, 2009 at 11:35 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I'm sure it depends on the user. Users that are more interested in the features we already have in the bag like window functions and WITH-clause, will obviously prefer to release earlier without hot standby. And users that want hot standby (or SE-postgresql) will prefer to delay the release and have those features included. At LinuxLive (UK) the overwhelming majority of people I spoke to over three days wanted hot standby and replication (preferably multi-master, but thats another story). Window functions, recursive queries, SE PostgreSQL, updatable views and other new features were barely mentioned. I'd say the FSM rework is the largest feature in 8.4 that most of my customers would have immediate use for. With visibility map not far behind. It's not a sexy feature, it's removing a piece of annoyance. So it's not something people would think of when you say what feature are you looking for in the next version. But it will help pretty much all users, and that's certainly more than hot standby for example. And the longer we push that back for some other feature, the more these users suffer. That said, I've certainly got a fair number of places where hot standby would be very popular. More than most others on your list above. And I'm not making a comment as to how ready hot standby is - I haven't kept up enough to comment on that. As I've pointed out before, we're not a commercial company working for our shareholders, we're a FOSS project working for our end users. If we can include an important and popular feature like this at the expense of a few weeks extra wait for the release, it seems to me that we'll be serving our users far better overall than making a fair percentage of them wait another 12 months for work that is more or less complete. Just playing the devils advocate here, but you can turn that argument around easily. We're not a commercial company who need to release a feature just because marketing said it'd be there... //Magnus -- 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_upgrade project status
Zdenek Kotala wrote: The current project is not in good shape. Feature freeze is coming and nothing is committed. Currently there are three patches in the game: [...] 2) pg_upgrade.sh http://archives.postgresql.org/pgsql-hackers/2008-12/msg00248.php Pg_upgrade.sh is shell script for catalog conversion. It works for 8.3-8.4 upgrade. It will be useful while we will not have better solution. Disadvantage is that it is korn shell script. The idea is to rewrite it in PERL which is more portable, but I'm not PERL expert and currently there is no workable solution. And what is deadline for it? We can delivery it later with 8.4.1, but best time for testing is during betas and RC period. I have had a very brief look at this. Translation to perl doesn't look difficult. I'll see what I can do during the next week or so. 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: Commitfest infrastructure (was Re: [HACKERS] 8.4 release planning)
I have started some very trivial work around this a while ago with the intent to get something simple up and working before too much bike shedding is done. I'll contact Robert off-list to discuss that. If somebody else - who actively works with what we have now!! - is interested in that discussion, let me know. Will obviously take it on-list before any decisions are made. So far I'm just talking about discussing a prototype. Sounds good. I think we will have the best chance of success if we keep it real simple. I don't want this to turn into a propaganda war about using everyone's favorite tool. I just want to write down a database schema that mimics the organization of the existing wiki page, put a thin web interface around it, and call it a day. It will take longer to analyze whether some other tool is sufficiently close to that than it will to write a tool that is exactly that. ...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] 8.4 release planning
Peter Eisentraut wrote: On Tuesday 27 January 2009 00:42:32 Ron Mayer wrote: If it were just as easy for us to pull from a all 'pending-patches' for-commit-fest-nov that pass regression tests branch, I'd happily pull from that instead. Considering that most patches don't come with regression tests, this would accomplish very little. And even those patches that did come with regression tests (e.g., updatable views) need a design analysis much more than running an automated test suite. Ultimately, it does come down to human work. So long as the patch passes the pre-existing regression tests, it's likely to be stable enough to run on some of our development instances. I certainly don't suggest that this is a substitute for reviews. Just that more testing of patches might happen incidentally (by people who currently test their own software against CVS head) if all the pending patches for a commit fest were as easy to pull as CVS head. -- 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_upgrade project status
Andrew Dunstan wrote: Zdenek Kotala wrote: 2) pg_upgrade.sh http://archives.postgresql.org/pgsql-hackers/2008-12/msg00248.php Pg_upgrade.sh is shell script for catalog conversion. It works for 8.3-8.4 upgrade. It will be useful while we will not have better solution. Disadvantage is that it is korn shell script. The idea is to rewrite it in PERL which is more portable, but I'm not PERL expert and currently there is no workable solution. I have had a very brief look at this. Translation to perl doesn't look difficult. I'll see what I can do during the next week or so. We don't require perl for any other feature, do we? Seems like a pretty onerous requireemnt for Windows in particular. We do use perl in the build scripts, but that's only required if you want to compile from source. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Peter, * Peter Eisentraut (pete...@gmx.net) wrote: As one of the earlier reviewers, I think the design is OK, but the way the implementation is presented was not acceptable, and very little has been accomplished in terms of reacting to our comments. For example, where is the SQL row security feature, which should have been designed, implemented, and committed separately, in the opinion of most commentaries. Eh? Are you thinking of column-level privileges, which was committed last week? The SQL spec doesn't define row-level security, and coming up with something willy-nilly on our own doesn't really strike me as the best approach. Oracle, SQL Server, etc, also use the security labels concept that the SE-PostgreSQL patch implements. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pg_upgrade project status
Heikki Linnakangas wrote: Andrew Dunstan wrote: Zdenek Kotala wrote: 2) pg_upgrade.sh http://archives.postgresql.org/pgsql-hackers/2008-12/msg00248.php Pg_upgrade.sh is shell script for catalog conversion. It works for 8.3-8.4 upgrade. It will be useful while we will not have better solution. Disadvantage is that it is korn shell script. The idea is to rewrite it in PERL which is more portable, but I'm not PERL expert and currently there is no workable solution. I have had a very brief look at this. Translation to perl doesn't look difficult. I'll see what I can do during the next week or so. We don't require perl for any other feature, do we? Seems like a pretty onerous requireemnt for Windows in particular. We do use perl in the build scripts, but that's only required if you want to compile from source. I think it's fairly easy to install Perl on Windows actually. It doesn't sound too onerous a requirement if you want in-place upgrade; actually it looks a very reasonable one. Much more reasonable than Korn shell in any case (or any shell for that matter; I think anything is going to be more of a potentially painful platform dependency than Perl). -- 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] pg_upgrade project status
On Tue, Jan 27, 2009 at 2:39 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: I think it's fairly easy to install Perl on Windows actually. It doesn't sound too onerous a requirement if you want in-place upgrade; actually it looks a very reasonable one. There are installers for it, but given that we made a point of porting everything to C to avoid using any scripting languages on end-user machines when we ported to Windows, it seems strange to relax that 'policy' now for convenience. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
On Tue, Jan 27, 2009 at 11:39:50AM -0300, Alvaro Herrera wrote: Heikki Linnakangas wrote: Andrew Dunstan wrote: Zdenek Kotala wrote: 2) pg_upgrade.sh http://archives.postgresql.org/pgsql-hackers/2008-12/msg00248.php Pg_upgrade.sh is shell script for catalog conversion. It works for 8.3-8.4 upgrade. It will be useful while we will not have better solution. Disadvantage is that it is korn shell script. The idea is to rewrite it in PERL which is more portable, but I'm not PERL expert and currently there is no workable solution. I have had a very brief look at this. Translation to perl doesn't look difficult. I'll see what I can do during the next week or so. We don't require perl for any other feature, do we? Seems like a pretty onerous requireemnt for Windows in particular. We do use perl in the build scripts, but that's only required if you want to compile from source. I think it's fairly easy to install Perl on Windows actually. It doesn't sound too onerous a requirement if you want in-place upgrade; actually it looks a very reasonable one. Much more reasonable than Korn shell in any case (or any shell for that matter; I think anything is going to be more of a potentially painful platform dependency than Perl). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support +1 I agree with Alvaro. Perl is a breeze to install on Windows with Activestate and that using shell code to perform this task adds a huge platform dependency to the code. Perl is a known and well defined quantity for scripting. Cheers, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
On Tue, Jan 27, 2009 at 8:44 AM, Zdenek Kotala zdenek.kot...@sun.com wrote: The current project is not in good shape. Feature freeze is coming and nothing is committed. Currently there are three patches in the game: Correction: feature freeze is long past. 1) Space reservation http://archives.postgresql.org/pgsql-hackers/2008-12/msg00886.php http://archives.postgresql.org/pgsql-hackers/2009-01/msg02031.php This patch is mandatory for page online conversion and MUST TO be part of postgreSQL 8.4. if not ... then we will be at the beginning next year. I sent updated version today. I thought we pretty much had agreement that space reservation was not a good solution to anything, although I admit I'm not quite clear on what alternative was being proposed. 2) pg_upgrade.sh http://archives.postgresql.org/pgsql-hackers/2008-12/msg00248.php Pg_upgrade.sh is shell script for catalog conversion. It works for 8.3-8.4 upgrade. It will be useful while we will not have better solution. Disadvantage is that it is korn shell script. The idea is to rewrite it in PERL which is more portable, but I'm not PERL expert and currently there is no workable solution. And what is deadline for it? We can delivery it later with 8.4.1, but best time for testing is during betas and RC period. Why is the deadline different than anything else? Surely the deadline is 11/1/2008, and you missed it. Admittedly, there is some window for reworking existing patches after the start of the commitfest, but this patch wasn't even added to the CommitFest wiki until December 5th, after being sent to the list the previous day. That's not close, and there's been little discussion of it on the mailing list since then, and given that it's written in ksh, it's clearly going to require a complete rewrite to be committable. Three months after the CommitFest started is not the right time to start a complete rewrite of a feature that wasn't even on time in the first place. There is nothing whatever to prevent you from releasing this on pgfoundry, but the idea that we should spend time on this rather than the half a dozen (or more) patches that have had far more work and are probably far closer to being committable strikes me as 100% wrong. 3) preupgrade script http://archives.postgresql.org/pgsql-hackers/2008-12/msg01273.php I sent WIP version of this script, which shows how reservation space feature will be used. This part is not important now. It will be important for 8.4-8.5 upgrade and cannot be finished until 8.5beta. ...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: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
Dave Page wrote: On Tue, Jan 27, 2009 at 2:01 PM, Peter Eisentraut pete...@gmx.net wrote: Updatable views is reverted. I agree that we should reject the rest and prepare a release. That will send a fine message to those companies that have sponsored development work - that we will arbitrarily reject large patches that have been worked on following the procedures that we require. To some extent that seems more an issue of linguistics and tone. If Peter had written we should defer the rest and try to help resolve specific issues identified in the reviews during commitfest 2009-First, sponsors might be happy rather than upset. I think one can make a strong argument that the features should be moved to the next commit-fest, just so the other patches in that commit fest ( http://wiki.postgresql.org/wiki/CommitFest_2009-First ) don't bit-rot too badly. Whether the community wants to release an 8.4 between commitfest 2008-11 and 2009-First seems to me a largely orthogonal question that would be based more on what demand there is for an 8.4 release and how distracting it would be to do a release between commitfests. -- 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_upgrade project status
Zdenek Kotala wrote: 2) pg_upgrade.sh http://archives.postgresql.org/pgsql-hackers/2008-12/msg00248.php Pg_upgrade.sh is shell script for catalog conversion. It works for 8.3-8.4 upgrade. What's the reason this script uses a postmaster? It seems it would be easier to control if you used a standalone backend (--single) for each time you are piping stuff to psql. That would reduce the need to configure authentication, hostnames, etc etc. -- 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] pg_upgrade project status
On 1/27/09, Zdenek Kotala zdenek.kot...@sun.com wrote: This patch is mandatory for page online conversion and MUST TO be part of postgreSQL 8.4. if not ... then we will be at the beginning next year. Just to clarify, does that mean that your patch has to be in for there to be any chance of in-place upgrade 8.4-8.5? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
Heikki Linnakangas wrote: Andrew Dunstan wrote: Zdenek Kotala wrote: 2) pg_upgrade.sh http://archives.postgresql.org/pgsql-hackers/2008-12/msg00248.php Pg_upgrade.sh is shell script for catalog conversion. It works for 8.3-8.4 upgrade. It will be useful while we will not have better solution. Disadvantage is that it is korn shell script. The idea is to rewrite it in PERL which is more portable, but I'm not PERL expert and currently there is no workable solution. I have had a very brief look at this. Translation to perl doesn't look difficult. I'll see what I can do during the next week or so. We don't require perl for any other feature, do we? Seems like a pretty onerous requireemnt for Windows in particular. We do use perl in the build scripts, but that's only required if you want to compile from source. Well, from that POV the only portable thing is to translate it into C. That's just a whole lot more work (remember initdb?). The perl port for Windows is easily installable, widely used and well regarded. It doesn't strike me as too high a price to pay for the ability to do upgrades, but I'll defer to more Windows-centric commenters. 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] 8.4 release planning
On Tue, Jan 27, 2009 at 06:20:41AM -0800, Ron Mayer wrote: For what it's worth, we can see that there are indeed Postgres forks on the Common Criteria certified list. http://www.commoncriteriaportal.org/products_DB.html PostgreSQL Certified Version V8.1.5 for Linux Manufacturer Assurance level Certification date NTT DATA CORPORATION EAL122-MAR-07 Certification report c0089_ecvr.pdf http://www.commoncriteriaportal.org/files/epfiles/c0089_ecvr.pdf though at EAL1 they're quite far from the EAL4+ that DB2, Oracle, etc get. As far as I understand, the different levels are about assuring a set of code/features to some assurance level. The Wikipedia page[1] gives a reasonable overview of the levels, but basically EAL1 says that a limited amount of effort (in practical terms, several person months/years of time for something like PG) was put in, EAL4 is the highest level before things start getting formal (i.e. you actually have to start doing some mathematical proofs about the design) and EAL7 has barely started, but says that the design is formally verified but the code isn't (as far as I understand). Research groups are suggesting that there should also be levels above EAL7 as we are *starting* to know how to verify code well enough that the code, as well as the design, can now be formally verified (e.g. [2]). Equally important as the assurance level are the actual feature set (there are technical names for this that I know very little about) that was actually tested for. For example, it would be comparatively easy to get PG certified saying that it loads and could be killed, but much harder to get it certified as complying with the complete SQL spec. -- Sam http://samason.me.uk/ [1] http://en.wikipedia.org/wiki/Evaluation_Assurance_Level [2] http://ertos.nicta.com.au/research/l4.verified/ -- 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_upgrade project status
Dave Page wrote: On Tue, Jan 27, 2009 at 2:39 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: I think it's fairly easy to install Perl on Windows actually. It doesn't sound too onerous a requirement if you want in-place upgrade; actually it looks a very reasonable one. There are installers for it, but given that we made a point of porting everything to C to avoid using any scripting languages on end-user machines when we ported to Windows, it seems strange to relax that 'policy' now for convenience. If you prefer to not have pg_upgrade at all for 8.4, feel free to request it to be written in C ... But I'm sure that's not what you meant. -- 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] pg_upgrade project status
Dave Page dp...@pgadmin.org writes: On Tue, Jan 27, 2009 at 2:39 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: I think it's fairly easy to install Perl on Windows actually. It doesn't sound too onerous a requirement if you want in-place upgrade; actually it looks a very reasonable one. There are installers for it, but given that we made a point of porting everything to C to avoid using any scripting languages on end-user machines when we ported to Windows, it seems strange to relax that 'policy' now for convenience. Indeed. We might put up with a perl script for awhile for the sake of development expediency, but the long-term expectation would have to be that someone would rewrite it in C. Given that, I wonder whether there's much point in a rewrite into Perl if we already have a working shell script. I suppose someone will say but you'll get no testing from Windows users then... 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] Index Scan cost expression
Amit Gupta amit.pc.gu...@gmail.com writes: Upon taking a cursory look at the cost functions of other operators, I realized that available memory (effective_cache_size) is not considered for estimating the costs of hash/sort/NLjoin/etc. Why is that the case? The relevant number for those is work_mem not effective_cache_size. 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_upgrade project status
Andrew Dunstan wrote: Heikki Linnakangas wrote: Andrew Dunstan wrote: Zdenek Kotala wrote: 2) pg_upgrade.sh http://archives.postgresql.org/pgsql-hackers/2008-12/msg00248.php Pg_upgrade.sh is shell script for catalog conversion. It works for 8.3-8.4 upgrade. It will be useful while we will not have better solution. Disadvantage is that it is korn shell script. The idea is to rewrite it in PERL which is more portable, but I'm not PERL expert and currently there is no workable solution. I have had a very brief look at this. Translation to perl doesn't look difficult. I'll see what I can do during the next week or so. We don't require perl for any other feature, do we? Seems like a pretty onerous requireemnt for Windows in particular. We do use perl in the build scripts, but that's only required if you want to compile from source. Well, from that POV the only portable thing is to translate it into C. That's just a whole lot more work (remember initdb?). The perl port for Windows is easily installable, widely used and well regarded. It doesn't strike me as too high a price to pay for the ability to do upgrades, but I'll defer to more Windows-centric commenters. Either way, there's no point to discuss that in detail until there actually is a working implementation out there... perl will do fine until then. Once we have that, we can discuss if doing it in C will be worthwhile, or if we're just going to require perl for that one feature. I have a hard time thinking that we'll have wasted a lot of time on first doing a perl implementation if we have to rewrite it in C later. The other way around would be a waste though. The amount of time spent on the perl implementation I expect to be a *lot* less than the combination of thinking up the *way* to do it in general and the C implementation time. //Magnus -- 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_upgrade project status
On Tue, Jan 27, 2009 at 2:49 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Dave Page wrote: On Tue, Jan 27, 2009 at 2:39 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: I think it's fairly easy to install Perl on Windows actually. It doesn't sound too onerous a requirement if you want in-place upgrade; actually it looks a very reasonable one. There are installers for it, but given that we made a point of porting everything to C to avoid using any scripting languages on end-user machines when we ported to Windows, it seems strange to relax that 'policy' now for convenience. If you prefer to not have pg_upgrade at all for 8.4, feel free to request it to be written in C ... But I'm sure that's not what you meant. I'd rather it was written in an appropriate language before feature freeze, not in a language that makes it easier for the author but a shade harder for thousands of users three months into feature freeze. -- Dave Page EnterpriseDB UK: 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] Hot standby, recovery infrastructure
On Tue, 2009-01-27 at 15:59 +0200, Heikki Linnakangas wrote: Regarding this comment: + /* +* Prior to 8.4 we wrote a Shutdown Checkpoint at the end of recovery. +* This could add minutes to the startup time, so we want bgwriter +* to perform it. This then frees the Startup process to complete so we can +* allow transactions and WAL inserts. We still write a checkpoint, but +* it will be an online checkpoint. Online checkpoints have a redo +* location that can be prior to the actual checkpoint record. So we want +* to derive that redo location *before* we let anybody else write WAL, +* otherwise we might miss some WAL records if we crash. +*/ Does this describe a failure case or something that would cause corruption? The tone of the message implies so, but I don't see anything wrong with deriving the redo location for the first checkpoint the usual way. I belive the case of missing some WAL records refers to the possibility that someone connects to the database and does a WAL logged change before the first checkpoint starts. But if we then crash before the checkpoint finishes, we'll start crash recovery from the previous restartpoint/checkpoint as usual, and replay that WAL record as well. And if the first checkpoint finishes, the redo ptr of that checkpoint is after that WAL record, Sorry, this is another one of those yes I thought that at first moments. and those changes are safely on disk. They may not be. They might have happened after BufferSync marks all dirty buffers BM_CHECKPOINT_NEEDED and yet before we write the physical checkpoint record. The idea of the checkpoint is to confirm the recovery is complete and make sure the starting point for crash recovery isn't somewhere in the archive. We must record the logical start before we allow any changes to be written, otherwise we might miss the intermediate changes. Just think standard-online-checkpoint and it all fits. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
* Magnus Hagander (mag...@hagander.net) wrote: Either way, there's no point to discuss that in detail until there actually is a working implementation out there... perl will do fine until then. Once we have that, we can discuss if doing it in C will be worthwhile, or if we're just going to require perl for that one feature. +1 Stephen signature.asc Description: Digital signature
Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
On Tue, Jan 27, 2009 at 8:49 AM, Bernd Helmle maili...@oopsware.de wrote: --On Dienstag, Januar 27, 2009 14:04:05 +0200 Peter Eisentraut pete...@gmx.net wrote: a view should be updatable by default if the query expression is updatable... what we need is something to make a view READ ONLY even if it should be updatable by spec... A view is read-only if you don't grant any write permissions on it. What i'm seeing here is a very divergent understanding what a read-only view is: old-school PostgreSQL-Users would expect a read-only view to have no write action installed. If we want to follow the standard closely, they need to be installed automatically, changing this behavior, hence the wish to have a syntax to restore the old behavior (e.g. for pg_dump). I'm unsure what the correct approach looks like, but it seems we need a compromise. Do we REALLY think there are people out there who are writing INSERT or UPDATE actions on views on which they haven't installed rules and counting on the fact that those operations fail for correctness? Personally, I usually write my code so it inserts into something that is, uh... insertable. ...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_upgrade project status
* Robert Haas (robertmh...@gmail.com) wrote: [pg_upgrade...] Why is the deadline different than anything else? err, isn't it because it'd be kind of difficult to do an upgrade script with large catalog-changing patches outstanding..? I thought some leeway was given for pg_upgrade specifically due to that, tho perhaps I'm wrong. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] 8.4 release planning
Tom Lane wrote: Joshua Brindle met...@manicmethod.com writes: http://marc.info/?l=selinuxm=115762285013528w=2 Is the original discussion thread for the security model used in the sepostgresql work. Hopefully you'll see some of the evidence you speak of there. Thanks for the link. I took a look through that thread and saw a lot of discussion about issues like how to relate the database-side and client-side permissions, which is all good stuff but mostly outside my purview as a database geek. I didn't find anything about the stuff that is really bothering me, which I think can be broken down into two main categories: 1. Silently filtering out rows according to an arbitrary security policy can break a bunch of fundamental SQL semantics, the most obvious being foreign key constraints --- an application might be able to see a This is correct. Strange error conditions can happen when you are using mandatory access controls. The same thing happened in linux when selinux was introduced. There was plenty of code out there that assumed if it was running as root it could do anything. Lots of it didn't even check for error conditions. The existence of poorly written applications should never be an argument against adding security. dependent row that apparently has no referenced row, or might get an update or delete failure for a row that is unreferenced as far as it can see. Things get worse if an application can insert, update or delete rows that it can't select. The only answer I've been able to get about Because type enforcement (the primary mechanism behind selinux) is very flexible it is true that policy writers have plenty of rope to hang themselves with. We can only attempt to educate and document these issues, blocking out security is not a satisfactory answer. what SEPostgres will do about that is we really don't care that we're breaking SQL semantics. I don't find that to be a satisfactory answer. Plenty of people feel the same way about SELinux (or any mandatory access controls). That is why there are options, if you want this security and don't care if your applications puke then enable it, else disable it. Noone is going to force you to use this, right? The security-geek reason why not is that it represents a huge information leak. The database-geek reason why not is that this will The great thing about security is that, by itself, it actually doesn't mean anything. Security is where you are willing to balance between stopping people from getting something done and letting them get something done. In this case, removing all covert channels would not only be impossible but it would make an unusable database system. In SELinux we didn't worry about covert flows, (actually we ignored/documented plenty of overt flows as well). With something as complex as the Linux kernel or an enterprise rdbms it is nearly impossible to eliminate such things. People who need absolute separation of information already have options, multiple server processes, polyinstanciated views, etc. For people that don't care if someone can see that you've inserted a couple rows since the primary key got larger, or can tell that an associated row that isn't visible exists in another table a more flexible, yet still mandatory system like sepostgresql is the answer. The great thing about this work is that, in many cases, a well designed system (that is, well designed for the security policy it is going to be constrained under) should not be impacted greatly by these issues. If a client needs information where they can't see all of the associated rows you can have trusted stored procedures (which run in a different selinux context, as defined by a type transition from the client context) that do the work and return the appropriate results. I know you can't use stored procedures for everything but they'd go a long way in binding queries we trust to the data they expose (just like in SELinux we bind binary code on the filesystem to domains that code can be used to enter) permanently destroy our ability to do a lot of important optimizations, eg join removal on the basis of foreign key constraints. (There are probably other good reasons, but that one will do for starters.) Perhaps this is fixable by constraining what a security policy is allowed to do, or in some other way that I don't know about, but I've seen no serious discussion about that. 2. I don't understand where to draw the dividing line between database system accesses (which can't be security constrained, at least not without breaking things entirely --- eg it will do you little good to imagine that you can hide rows in pg_security from the security-enforcement code ;-)) and user accesses that should be security-constrained. I am certain that the line is muddied beyond usability in the current system; there are a lot of user-exposed functions that are making use of the same infrastructure that core system
Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
Robert Haas wrote: Do we REALLY think there are people out there who are writing INSERT or UPDATE actions on views on which they haven't installed rules and counting on the fact that those operations fail for correctness? Personally, I usually write my code so it inserts into something that is, uh... insertable. Not everybody has control over what clients might try to do. This is a very legitimate concern, ISTM. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
On Tue, Jan 27, 2009 at 10:08 AM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: [pg_upgrade...] Why is the deadline different than anything else? err, isn't it because it'd be kind of difficult to do an upgrade script with large catalog-changing patches outstanding..? I thought some leeway was given for pg_upgrade specifically due to that, tho perhaps I'm wrong. Sure... if this script had been 100% commitable on 11/1 and now needed to be adjusted, I can't imagine anyone objecting. But the patch wasn't submitted until 12/4 and still needs a complete rewrite in a different programming language as of 1/27. Do you think we would be arguing about whether to accept Hot Standby now if it were written in ksh? And that was at least submitted on time. ...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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
On Tue, Jan 27, 2009 at 10:14 AM, Andrew Dunstan and...@dunslane.net wrote: Do we REALLY think there are people out there who are writing INSERT or UPDATE actions on views on which they haven't installed rules and counting on the fact that those operations fail for correctness? Personally, I usually write my code so it inserts into something that is, uh... insertable. Not everybody has control over what clients might try to do. This is a very legitimate concern, ISTM. Can you flesh out the scenario you're concerned about a bit more? ...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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
Peter Eisentraut pete...@gmx.net writes: On Saturday 24 January 2009 02:17:13 Tom Lane wrote: 2. You don't want those rules, so you delete them, leaving you with the traditional behavior where attempted inserts etc on the view fail. This was never meant to be supported. If you don't want updates on the rules to succeed, don't grant privileges. If we'd had the SQL-spec behavior from day one, it wouldn't be a problem, but you can't just blow off the old behavior like that. It's a potential security hole, since GRANT ALL on a view used to be de facto the same as GRANT SELECT, if you hadn't bothered to create any rules. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: SEPostgres seems qualitatively different to me, though. I think PG people have avoided reviewing it because (a) they weren't interested in it and (b) they knew they were unqualified to review it. Meanwhile it's emerging that the selinux people don't feel qualified to review it either. I'm not quite sure what to do about that. But throw it in there on faith doesn't sound like an appealing answer, and I've got no idea how long it will take to work out a non-faith-based answer. Erm, I have to say here that this strikes me as rather unfair. Perhaps I'm wrong, but I suspect KaiGai feels pretty good about the patch and his qualifications in both the PG realm and the SELinux realm. He's Not only that but he's had many discussions with us about sepostgres, from the security model to his reimplementation of the access vector cache. Just because we haven't been on this list doesn't mean we haven't been watching the work. asking the PG folks to review it because that's the process that the PG community (through the CommitFest, etc) has laid out for getting a patch included upstream. I'm confident KaiGai isn't going to just disappear into the ether if the patch is committed. He hasn't disappeared yet, that is probably a good sign :) Sure, it'd be nice if 4 or 5 other SELinux developers got in and understood the PG code well enough to implement such a patch, but I We aren't a huge community and because of the nature of SELinux we have people spread out over many different projects (X, dbus, NFS, distributions, ipsec/networking, solaris fmac), etc. I'm probably more familiar with databases than the others so I'm here to help (though my time is also spread over many other things). think the combination of KaiGai (overall), a seperate SELinux hacker (for the security design and SELinux side of it), and a PG committer (for where the hooks are placed and how), reviewing the patch and being comfortable with it is quite sufficient for a high quality result. That is all I asked for. No matter how familiar I become with the pgsql code I'll never be as qualified as you guys for identifying security hook call sites that are missing/misplaced. Assuming I think the security backend is correct then it shouldn't be hard for you guys to look at the docs, see that permissions x, y and z are required for operation foo, and know where the possible codepaths for operation foo are and check that the hooks for x, y and z are called. -- 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] Index Scan cost expression
Amit Gupta amit.pc.gu...@gmail.com writes: While trying to figure out an appropriate cost expression function for Thick indexes, i learned that we are using Mackert and Lohman formula (described in their paper Index Scans Using a Finite LRU Buffer: A Validated I/O Model, ACM Transactions on Database Systems). ... Please note that the above result only computes _heap_ page reads. Moreover it only models a single index scan. It assumes nothing is cached prior to the index scan which is very much not true if we're repeatedly scanning similar ranges of keys. Omitting the index pages is a very coarse attempt to model the caching across multiple plan invocations since upper level index pages will nearly always be cached and even lower index pages have a good chance of being cached. The problem is that modeling cross-plan-invocation caching is a hard problem we have few ideas for. Upon taking a cursory look at the cost functions of other operators, I realized that available memory (effective_cache_size) is not considered for estimating the costs of hash/sort/NLjoin/etc. Why is that the case? Well they're all different but I suspect the root of what you're observing are all the same thing. Cache doesn't affect any of these nodes unless we start with something in the cache from previous queries and we don't model that. We assume each query and even each plan node is run on a cold cache. The problem is that how much to discount the cost of the inner node depends, not only on the type of node it is, but also on the types of parameters it will be called with. So it needs very different results for something like a nested loop between an outer table with few closely spaced values and another with an outer table with values sparsely spread throughout the inner table. This is complicated by the fact that the repetitions don't necessarily come from the parent of the plan in question. You could have, for example, a subquery several nodes down from the scan that causes repetitions. I think the only way to tackle it is to come up with some parametric formula for how much to discount repetitions and carry the parameters of that formula on every plan node. So when a node needs to add the cost of n repetitions of a lower node it applies that formula using the parameters advertised by the sub-node. The tricky part of that is coming up with a formula and figuring parameters to model plan nodes using it. Consider that how much to discount repetitions will depend heavily on what distribution of parameters the plan is executed with. I'm also not clear what kinds of formulas work for this. It has to be something that can be composed effectively. That is, even if a plan node doesn't want to discount itself at all for repetitions it has to include the discount that any subplans have asked for. For example a large sequential scan which expects to overflow effective_cache_size might not want to be discounted at all, but if it has a subquery which scans a small table it will want to discount that 100% for any repetitions since it'll be cached after the first scan. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- 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] binary array and record recv
Peter Eisentraut pete...@gmx.net writes: On Tuesday 18 December 2007 18:30:22 Tom Lane wrote: Arguably, pg_dump from an older version should make sure that the auto rules should NOT get created, else it is failing to preserve an older view's behavior. We extend properties of objects all the time. That is why we make new releases. No one is required to use the new properties. Should pg_dump also make sure that tables imported from an older version are not usable for recursive unions or window functions, thus preserving the older table's behavior? That argument seems fairly bogus. The addition of those features won't change the behavior of existing applications. 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_upgrade project status
I think it's fairly easy to install Perl on Windows actually. It doesn't sound too onerous a requirement if you want in-place upgrade; actually it looks a very reasonable one. Much more reasonable than Korn shell in any case (or any shell for that matter; I think anything is going to be more of a potentially painful platform dependency than Perl). May I humbly recommend to rewrite in Python? That should be as difficult / easy as PERL, AND there is a very robust py2exe implementation, which allows to create a single .exe file which contains everything. Python is present on all Linux, Windows users are totally comfortable with .exe files. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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 to add Windows 7 support
Dave Page dp...@pgadmin.org writes: The attached patch adds support for the Windows 7 beta which we've had a few reports of incompatibility with. When we startup using pg_ctl on Windows, we create a job object (a logical grouping of processes on Windows) to which we apply various security options. One of these (JOB_OBJECT_UILIMIT_HANDLES) is used to prevent our processes seeing handles belonging to processes outside of our job, however, when we run under the service control manager, this causes the postmaster to exit immeditately for no apparent reason. I'm not entirely sure what has change in the SCM to cause this yet (Windows 7 documentation is somewhat thin on the ground at the moment), but the patch avoids theporblem by only setting JOB_OBJECT_UILIMIT_HANDLES on earlier OSs. It would be good to understand what the problem actually is and what are the risks of running without this flag. I assume we put it in there for a reason. 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 to add Windows 7 support
Tom Lane wrote: Dave Page dp...@pgadmin.org writes: The attached patch adds support for the Windows 7 beta which we've had a few reports of incompatibility with. When we startup using pg_ctl on Windows, we create a job object (a logical grouping of processes on Windows) to which we apply various security options. One of these (JOB_OBJECT_UILIMIT_HANDLES) is used to prevent our processes seeing handles belonging to processes outside of our job, however, when we run under the service control manager, this causes the postmaster to exit immeditately for no apparent reason. I'm not entirely sure what has change in the SCM to cause this yet (Windows 7 documentation is somewhat thin on the ground at the moment), but the patch avoids theporblem by only setting JOB_OBJECT_UILIMIT_HANDLES on earlier OSs. It would be good to understand what the problem actually is and what are the risks of running without this flag. I assume we put it in there for a reason. Honestly, I think the reason was simply enable all flags that we don't know will break things for us. There was no closer analysis than that. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Commitfest infrastructure (was Re: [HACKERS] 8.4 release planning)
On Wed, Jan 28, 2009 at 1:35 AM, Robert Haas robertmh...@gmail.com wrote: I have started some very trivial work around this a while ago with the intent to get something simple up and working before too much bike shedding is done. I'll contact Robert off-list to discuss that. If somebody else - who actively works with what we have now!! - is interested in that discussion, let me know. I'm very interested in that discussion. I don't know whether I am actively working with what we have now, but that's because since I wrote the original template structure, it hasn't changed a whole lot. Most of the tweaking has had to do with presentation, and massaging mediawiki to do what we wanted. As Alvaro points out, the wiki approach was intended to provide a stop-gap solution to patch tracking, and also to help us identify what we actually needed from a patch tracker, so that we could make a sensible decision about which tool to use when we did eventually move forward. Will obviously take it on-list before any decisions are made. So far I'm just talking about discussing a prototype. Sounds good. I think we will have the best chance of success if we keep it real simple. I don't want this to turn into a propaganda war about using everyone's favorite tool. I just want to write down a database schema that mimics the organization of the existing wiki page, put a thin web interface around it, and call it a day. It will take longer to analyze whether some other tool is sufficiently close to that than it will to write a tool that is exactly that. I can understand the desire to avoid a propaganda war. These discussions have borne little fruit previously, in part because we haven't had a clear idea of what was actually required from the tool. I think the picture has started to become more clear during the 8.4 dev cycle. Most importantly, there was much ado made about the need for powerful email integration features in previous discussions. This severely restricted our choices (possibly to zero?). I feel that the commitfest wiki has demonstrated that no such integration is required. Everyone wants to keep on using the mailing list for discussion, but we need somewhere else to keep track of patches and their status. To my knowledge, authors have been happy to add patches to the wiki and reviewers have been happy to update their status with no email integration whatsoever. We've continued to discuss things on the lists, while updating the wiki as required. If we forget about trying to integrate with email, the field opens right up and we can use pretty much any just-install-the-package tracking software out there and it will get the job done. For the sake of not advocating my favourite tool, I won't name any particular software, but I can think of several off the top of my head that could mirror the structure we currently have on the wiki without stretching. I think it's possible to skip the roll our own step in all of this and just move on to using a ready-made solution. In reality our requirements are very simple. Writing a low-fi version of the wiki would be pretty easy, but just dropping the patch data we already have into a patch tracker would be even easier. Cheers, BJ -- 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] mingw check hung
Magnus Hagander wrote: Have you managed to get gdb running on that box, and if so, can you try to grab a stacktrace? If not, try a stacktrace from process explorer. It doesn't actually work with mingw, but it gives you a hint based on DLL exports... I'll see what I can do. By the time I get to see the problem Dr Watson already has the process - in fact the run is hanging waiting on a Dr Watson dialog box ;-( I've installed drmingw to handle exceptions instead, so we'll see if that gives us useful info. If not, I'll see what I can do with gdb. 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] binary array and record recv
Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On Tuesday 18 December 2007 18:30:22 Tom Lane wrote: Arguably, pg_dump from an older version should make sure that the auto rules should NOT get created, else it is failing to preserve an older view's behavior. We extend properties of objects all the time. That is why we make new releases. No one is required to use the new properties. Should pg_dump also make sure that tables imported from an older version are not usable for recursive unions or window functions, thus preserving the older table's behavior? That argument seems fairly bogus. The addition of those features won't change the behavior of existing applications. How will adding updatable views change them? The only change is that when you try to insert/update/delete on a view, it used to give an error, but the new version will accept it. How can this be a problem? Surely no application is depending on the fact that this will raise an error. -- 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: Commitfest infrastructure (was Re: [HACKERS] 8.4 release planning)
I think it's possible to skip the roll our own step in all of this and just move on to using a ready-made solution. In reality our requirements are very simple. Writing a low-fi version of the wiki would be pretty easy, but just dropping the patch data we already have into a patch tracker would be even easier. Well, if you're volunteering to set something up... great. We'll take a look at it when you have it working. That's not what I'm volunteering to do, though. ...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_upgrade project status
Hi I have had a very brief look at this. Translation to perl doesn't look difficult. I'll see what I can do during the next week or so. Perhaps I can lend you a hand if you need help with this. -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: ka...@jasonic.dk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
On Tue, 2009-01-27 at 06:40 +0100, Pavel Stehule wrote: 8.4-stable 8.4-experimental stable is everything that stable is. PostgreSQL at its best. I dislike this idea - it's same like short processed 8.5 - Actually it isn't because we wouldn't accept features into 8.4-experimental. The only thing we would accept into 8.4-experimental would be bug fixes that would automatically be ported up to 8.5 (or perhaps the other way around). We would still continue to build 8.5 as normal. that is more simple. We have tried the short release cycle before, it was called 8.2. It fails, remarkably. regards Pavel Stehule Well like I said, its just an idea :) 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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
Robert Haas wrote: On Tue, Jan 27, 2009 at 10:14 AM, Andrew Dunstan and...@dunslane.net wrote: Do we REALLY think there are people out there who are writing INSERT or UPDATE actions on views on which they haven't installed rules and counting on the fact that those operations fail for correctness? Personally, I usually write my code so it inserts into something that is, uh... insertable. Not everybody has control over what clients might try to do. This is a very legitimate concern, ISTM. Can you flesh out the scenario you're concerned about a bit more? See Tom's response to Peter nearby. 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] 8.4 release planning
On Tue, 2009-01-27 at 00:58 -0500, Jaime Casanova wrote: On Tue, Jan 27, 2009 at 12:40 AM, Pavel Stehule pavel.steh...@gmail.com wrote: so it could be released. 8.5 should be implemented in shorted cycle - only one commitfest, that is enough (+3 month) for well completing SE and replication patches. we tried this before (8.2 to 8.3 i think), the idea was that the next release should be in 6 months... we release at least 6 months later... 8.2 was a short cycle release that lasted just as long as a normal release cycle :) 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] mingw check hung
Andrew Dunstan wrote: Magnus Hagander wrote: Have you managed to get gdb running on that box, and if so, can you try to grab a stacktrace? If not, try a stacktrace from process explorer. It doesn't actually work with mingw, but it gives you a hint based on DLL exports... I'll see what I can do. By the time I get to see the problem Dr Watson already has the process - in fact the run is hanging waiting on a Dr Watson dialog box ;-( There's a commandline parameter to drwatson, iirc, that will make it stop grabbing them automatically. I've installed drmingw to handle exceptions instead, so we'll see if that gives us useful info. If not, I'll see what I can do with gdb. Hadn't heard of drwmingw, I see how that can be useful :-) //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCHES] [HACKERS] Infrastructure changes for recovery
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: (replying to a very old message, since I just bumped into this in review) Alvaro Herrera wrote: The new message is not translatable, the original was. Doesn't really matter since it's an elog(), not ereport(). ... which is wrong in itself, since it's certainly meant as a user-facing (or at least DBA-facing) message. elog should generally only be used for debugging or can't happen messages, not for stuff that users are expected to see on a routine basis. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
On Mon, Jan 26, 2009 at 03:12:02PM -0500, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: So, some feedback to make this decision more difficult: Users: care about HS more than anything else in the world. I don't think this is correct. I do. People literally grab my shoulder and ask when we'll have it. I've never seen anything like the interest in this for any database feature, including the fairies-and-unicorns multi-master replication people imagine will scale linearly in every dimension by plugging in nodes. 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 to add Windows 7 support
On Tue, Jan 27, 2009 at 3:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: It would be good to understand what the problem actually is and what are the risks of running without this flag. I assume we put it in there for a reason. The risks are pretty low imho. Not having the flag means that the server has access to the handles of objects in other jobs in the same session. When running as a service, that's basically nothing as the service runs in it's own session and is isolated through other means. When run from the command line, a hacked binary could send messages to the users UI (buttons and other controls for example). It would be a difficult attack to pull off, and very hard to gain from it. It's easy enough to leave the flag in console mode however - that does work on Windows 7. As for what the problem actually is - without more info from Microsoft, I suspect we won't find out (and even then, I wouldn't hold my breath for something like this). -- Dave Page EnterpriseDB UK: 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: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
Dave Page dp...@pgadmin.org writes: On Tue, Jan 27, 2009 at 2:01 PM, Peter Eisentraut pete...@gmx.net wrote: Updatable views is reverted. I agree that we should reject the rest and prepare a release. That will send a fine message to those companies that have sponsored development work - that we will arbitrarily reject large patches that have been worked on following the procedures that we require. defer to 8.5 would have been better phraseology than reject, no doubt. We must at least have the solid belief (of a committer that that has done a proper review) that a patch cannot be polished in an appropriate timeframe, I already pointed out some pretty serious problems with the updatable views patch. Are you claiming they are trivial to fix? 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_upgrade project status
Harald Armin Massa wrote: I think it's fairly easy to install Perl on Windows actually. It doesn't sound too onerous a requirement if you want in-place upgrade; actually it looks a very reasonable one. Much more reasonable than Korn shell in any case (or any shell for that matter; I think anything is going to be more of a potentially painful platform dependency than Perl). May I humbly recommend to rewrite in Python? That should be as difficult / easy as PERL, AND there is a very robust py2exe implementation, which allows to create a single .exe file which contains everything. Python is present on all Linux, Windows users are totally comfortable with .exe files. No, I don't think so ;-) Without getting into language wars, Perl is already our de facto cross-platform scripting tool. We don't need to be adding extra knowledge requirements to the project, nor extra build requirements (right now, perl is already required for building from source, or when building with MSVC, or when running a buildfarm animal) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
Alvaro Herrera píše v út 27. 01. 2009 v 11:47 -0300: Zdenek Kotala wrote: 2) pg_upgrade.sh http://archives.postgresql.org/pgsql-hackers/2008-12/msg00248.php Pg_upgrade.sh is shell script for catalog conversion. It works for 8.3-8.4 upgrade. What's the reason this script uses a postmaster? It seems it would be easier to control if you used a standalone backend (--single) for each time you are piping stuff to psql. That would reduce the need to configure authentication, hostnames, etc etc. It is 8.1-8.2 upgrade relict. IIRC 8.1 does not have --single. I think single mode can be used. thanks Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
On Tue, Jan 27, 2009 at 3:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: We must at least have the solid belief (of a committer that that has done a proper review) that a patch cannot be polished in an appropriate timeframe, I already pointed out some pretty serious problems with the updatable views patch. Are you claiming they are trivial to fix? Not at all. I think the deferral of that particular patch is the correct thing to do because there are confirmed, real problems with it that are not realistic to fix in an appropriate timeframe for the release. The primary case that I'm objecting to is HS which you've been saying will take 10 - 12 months to complete having by your own admission not looked at the code or followed the discussion particularly closely. -- Dave Page EnterpriseDB UK: 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: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
On Tue, 2009-01-27 at 14:10 +, Dave Page wrote: On Tue, Jan 27, 2009 at 2:01 PM, Peter Eisentraut pete...@gmx.net wrote: Updatable views is reverted. I agree that we should reject the rest and prepare a release. That will send a fine message to those companies that have sponsored development work - that we will arbitrarily reject large patches that have been worked on following the procedures that we require. We are not subject to the whims of company sponsorship. We are not a company with shareholders... Where have I heard that before? We must at least have the solid belief (of a committer that that has done a proper review) that a patch cannot be polished in an appropriate timeframe, or another justifiable reason for rejecting rather than vague handwaving, guesswork and estimates based on email traffic. If this is actually what happen then I agree. I am not sure that it is though. If we do not, we will rapidly find that no company wants to sponsor features for PostgreSQL in the future for fear that their money will be wasted even if they jump through all the right hoops. I sincerely doubt this. The sponsoring company if properly educated about the process understands this is a possibility. It is a risk. If they don't have a contract in place that allows for things like this including responsibility on the developer end, then that is there problem. Not to mention the developer could offer to support their patch for a time until it is mature enough to be committed. Sincerely, 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: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
On Tue, Jan 27, 2009 at 3:48 PM, Joshua D. Drake j...@commandprompt.com wrote: On Tue, 2009-01-27 at 14:10 +, Dave Page wrote: On Tue, Jan 27, 2009 at 2:01 PM, Peter Eisentraut pete...@gmx.net wrote: Updatable views is reverted. I agree that we should reject the rest and prepare a release. That will send a fine message to those companies that have sponsored development work - that we will arbitrarily reject large patches that have been worked on following the procedures that we require. We are not subject to the whims of company sponsorship. We are not a company with shareholders... Where have I heard that before? Not basing our release schedule on our commitments to shareholders is an entirely different thing to treating sponsors of major features like crap by arbitrarily bouncing the patches they've paid to have properly developed within the community process with no good reason. -- Dave Page EnterpriseDB UK: 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] 8.4 release planning
Dave Page dp...@pgadmin.org writes: On Mon, Jan 26, 2009 at 8:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: Users: care about HS more than anything else in the world. I don't think this is correct. There are certainly a lot of users who would like an in-core replication solution, but HS by itself is not that --- you also need (near) real-time log shipping, which we have already decided to punt to 8.5. That being the case, I think the argument that HS is a must-have feature for 8.4 is actually rather weak. I don't buy that. Sure, sync-rep would be the icing on the cake, but HS with a small archive_timeout (even of the order of 10 or 15 minutes) would have been extremely useful on a number of systems I used to run. Sure, I don't deny that HS by itself would have significant use cases. But what those zillions of users want is easy-to-set-up replication (think mysql). Without an integrated and fairly high-performance log shipping capability, they are not going to find HS very compelling. Claiming otherwise is just wishful thinking. My own feeling about it is that once we have both HS and log shipping integrated and reasonably well polished, we'd have something that deserved the fabled 9.0 version number. But that's probably a year away, and we are not doing anyone a favor by not putting out 8.4 in the meantime. 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_upgrade project status
On Tue, 2009-01-27 at 09:48 -0500, Andrew Dunstan wrote: We don't require perl for any other feature, do we? Seems like a pretty onerous requireemnt for Windows in particular. We do use perl in the build scripts, but that's only required if you want to compile from source. Well, from that POV the only portable thing is to translate it into C. That's just a whole lot more work (remember initdb?). The perl port for Windows is easily installable, widely used and well regarded. It doesn't strike me as too high a price to pay for the ability to do upgrades, but I'll defer to more Windows-centric commenters. Actually as much as perl is ubiquitous it isn't. What version of perl shall we require? Will we require other modules? Does that version work on all our supported platforms (HPUX, NETBSD?) 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] Hot standby, recovery infrastructure
Simon Riggs wrote: On Tue, 2009-01-27 at 15:59 +0200, Heikki Linnakangas wrote: Regarding this comment: + /* +* Prior to 8.4 we wrote a Shutdown Checkpoint at the end of recovery. +* This could add minutes to the startup time, so we want bgwriter +* to perform it. This then frees the Startup process to complete so we can +* allow transactions and WAL inserts. We still write a checkpoint, but +* it will be an online checkpoint. Online checkpoints have a redo +* location that can be prior to the actual checkpoint record. So we want +* to derive that redo location *before* we let anybody else write WAL, +* otherwise we might miss some WAL records if we crash. +*/ Does this describe a failure case or something that would cause corruption? The tone of the message implies so, but I don't see anything wrong with deriving the redo location for the first checkpoint the usual way. I belive the case of missing some WAL records refers to the possibility that someone connects to the database and does a WAL logged change before the first checkpoint starts. But if we then crash before the checkpoint finishes, we'll start crash recovery from the previous restartpoint/checkpoint as usual, and replay that WAL record as well. And if the first checkpoint finishes, the redo ptr of that checkpoint is after that WAL record, Sorry, this is another one of those yes I thought that at first moments. and those changes are safely on disk. They may not be. They might have happened after BufferSync marks all dirty buffers BM_CHECKPOINT_NEEDED and yet before we write the physical checkpoint record. If the WAL record is written after BufferSync has started, the redo pointer is that WAL record. If we crash, recovery will start at the redo pointer, and will replay that WAL record. The idea of the checkpoint is to confirm the recovery is complete and make sure the starting point for crash recovery isn't somewhere in the archive. Ah, that's what you're worrying about. But I don't see that being an issue here. If we remove the special handling of the first checkpoint, the redo pointer will always be = what the patch does now. So it can't be in the archive any more than it is now. Hmm, I think we have small issue if the last WAL segment restored from the archive is an incomplete one: 0. Archive recovery runs as usual 1. Recovery hits the end of valid WAL, in the last incomplete WAL segment. 2. Checkpoint is started (let's assume that the redo pointer is the end of valid WAL, as the patch does now.) 3. startup process ends, the system is opened for connections 4. A new WAL record is written (and flushed) to the last incomplete WAL. 5. Crash. In crash recovery after that, recovery.conf is still in place (thanks to the changes in the patch, it's not renamed until the first checkpoint isthe finished). We always prefer the files from the archive, even if the same file already exists in pg_xlog, so we'll overwrite it with the version from the archive that doesn't contain the new WAL record written in step 4. I think we need to keep the renaming of recovery.conf unchanged. Just think standard-online-checkpoint and it all fits. Exactly that made me wonder why the first checkpoint needs to be any different. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
On Mon, 2009-01-26 at 22:55 -0500, Tom Lane wrote: Silently filtering out rows according to an arbitrary security policy can break a bunch of fundamental SQL semantics, the most obvious being foreign key constraints That was exactly my reaction when I read the way it worked and I was ready to reject the patch as a result. Bruce and KaiGai provided documents that discuss the problem and it's a clearly a known issue in the security community. Specifically, it hasn't prevented Oracle from gaining security Certification and it shouldn't prevent us either. In the end it's the certification that matters here, rather than a general review of what database security is, or could be. I've seen enough to be happy that KaiGai has done a thorough job on *attempting* to address the needs of the security people. Passing security audit is the real test and I won't be beating him up if we do miss slightly. We have to try, otherwise we'll never know. My concerns are all about what it does to our code and the impacts of that. These are things we know how to check. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
Joshua D. Drake wrote: On Tue, 2009-01-27 at 09:48 -0500, Andrew Dunstan wrote: We don't require perl for any other feature, do we? Seems like a pretty onerous requireemnt for Windows in particular. We do use perl in the build scripts, but that's only required if you want to compile from source. Well, from that POV the only portable thing is to translate it into C. That's just a whole lot more work (remember initdb?). The perl port for Windows is easily installable, widely used and well regarded. It doesn't strike me as too high a price to pay for the ability to do upgrades, but I'll defer to more Windows-centric commenters. Actually as much as perl is ubiquitous it isn't. What version of perl shall we require? Will we require other modules? Does that version work on all our supported platforms (HPUX, NETBSD?) That's what my brief examination of the script was about - looking to see if it could be translated portably. I think it very probably can. I suspect it won't need any modules at all. I suspect any 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] More FOR UPDATE/FOR SHARE problems
Jeff Davis pg...@j-davis.com wrote: On Mon, 2009-01-26 at 15:46 -0600, Kevin Grittner wrote: After the COMMIT succeeds, the locks from Session1 are released. Session2 acquires its update lock and reads row 2, finds that it doesn't match its update criteria, downgrades the lock to shared, acquires an update lock on row 3, finds that it does match the selection criteria, upgrades the lock to exclusive, updates it, acquires and update lock on row 4 finds that it doesn't match the update criteria, downgrades the lock to shared, hits the end of table, releases the shared locks. This is the part I'm having a problem with. This depends on row 3 being read after row 2. If that weren't the case (say, with a more complex update and a more complex search criteria), then the index scan would have already passed by the value and would never know that it was updated to a value that does match the search criteria. I think you're missing a fundamental point -- in Sybase, before a row or range is read it is blocked against update by other transactions until after the reading statement completes; before a row or range is updated it is blocked against another transaction even reading it. (For serializable transactions the locks are all held until commit or rollback.) So, if an index scan for S2 had already passed a certain point and blocked on an update by S1, and then S1 tried to update any part of what S2 had read, there would be a deadlock and one of these transactions would be rolled back with a serialization error. Data: i j 1 20 2 40 3 50 4 80 S1: BEGIN; UPDATE a SET j = (j - 10) WHERE i = 2 OR i = 3; S1 holds locks that prevent any other transaction reading anything about these two rows, including index entries. S2: BEGIN; UPDATE a SET j = j + 100 WHERE j = 10 or j = 40; -- Here, the index scan is already past j=10 by the time -- it blocks on a concurrently-updated tuple Assuming an index on j, S2 will block when it tries to read a modified range, so yeah, it might read j=10 and find nothing, then attempt to read j=40 and block. (Of course, in reality if the table had four rows and the database had current statistics, these would all be done with table scans and the locks would effectively be the same as table locks. That being fairly obvious behavior, I'll stick to the supposition that it's operating with row locks.) S1: COMMIT; The index entries and data rows are updated in place. Locks are then released. S2 now reads rows j=40 and finds i=3 as the only match. (No snapshot exists. No old version of the tuple. It just sees whatever is there when the locks are released.) S2: COMMIT; In PostgreSQL this sequence results in: i | j ---+ 1 | 20 4 | 80 2 | 30 3 | 40 The second update matched no tuples at all. In Sybase (and similar databases), the result would be: i | j ---+- 1 | 20 2 | 30 3 | 140 4 | 80 Let me restate -- I don't propose that PostgreSQL implement this locking scheme. I think it can and should do better in approaching compliance with the standard, and with ACID properties, without compromising concurrency and performance to the degree required by this sort of locking and blocking. I think Greg has it right: without predicate locking we can't really achieve the behavior you're expecting. So how would we better approach the semantics you want without it? Well, this thread was talking about dealing with situations where queries using FOR UPDATE/FOR SHARE return something other than what is requested, or results based on viewing only part of what was committed by another transaction. My feeling is that we should be looking harder at recognizing these cases and rolling back a transaction with a serialization failure before returning bad data. When you are using these clauses you are already vulnerable to deadlocks. This doesn't seem to me to be that different from other situations where people have said It's easy to return results quickly if you don't care whether they're accurate. Regarding the broader issues -- during discussion of documentation for the anomalies in snapshot isolation I was made aware of recent work, published by the ACM last year, which provides techniques for a more general and comprehensive solution. This has already been implemented in at least two other MVCC databases, although these changes haven't made it to a production release of anything yet. I've been trying to wait until 8.4 hits beta testing to open a discussion of this. Basically, though, this work outlines a way to provide real serializable behavior in an MVCC database without any more blocking than PostgreSQL already has. Stay tuned for a discussion of this once 8.4 is in beta. -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: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
On Tue, 2009-01-27 at 15:51 +, Dave Page wrote: On Tue, Jan 27, 2009 at 3:48 PM, Joshua D. Drake j...@commandprompt.com wrote: On Tue, 2009-01-27 at 14:10 +, Dave Page wrote: On Tue, Jan 27, 2009 at 2:01 PM, Peter Eisentraut pete...@gmx.net wrote: Updatable views is reverted. I agree that we should reject the rest and prepare a release. That will send a fine message to those companies that have sponsored development work - that we will arbitrarily reject large patches that have been worked on following the procedures that we require. We are not subject to the whims of company sponsorship. We are not a company with shareholders... Where have I heard that before? Not basing our release schedule on our commitments to shareholders is an entirely different thing to treating sponsors of major features like crap by arbitrarily bouncing the patches they've paid to have properly developed within the community process with no good reason. Certainly but I haven't seen a suggestion to that. Updateable views has as I have seen in threads, issues that can not be fixed in the appropriately time line. If they can be fixed for 8.5 great. Sincerely, 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