Re: [PATCHES] HOT latest patch - version 8
Heikki Linnakangas wrote: Stefan Kaltenbrunner wrote: tried to test a bit on my Solaris 10 install(sun studio , 64bit build) but I'm hitting the following while trying to initdb a new cluster: I can't reproduce this error, but I found a bug that's likely causing it. The patch uses InvalidOffsetNumber in lp_off to mark so called redirect dead line pointers, but that special case is not checked in PageGetRedirectingOffset-function, writing to a caller-supplied array with -1 index instead, globbering over whatever is there. Actually storing InvalidOffsetNumber in lp_off is a bit bogus in the first place since lp_off is unsigned, and InvalidOffsetNumber is -1, so I fixed that as well. this seems to fix the problem for me - a least I can now successfully initdb a new cluster with the HOT patch applied. Stefan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] pg_dump --no-tablespaces patch
This is the patch I proposed on hackers to make pg_dump optionally ignore tablespaces. The patch is against 8.2.4. If I should be applying it to CVS head or what not, please let me know (along with any thoughts, concerns or issues). Gavin pg_dump-8.2.4-no-tablespace-option.patch Description: Binary data ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] pg_dump --no-tablespaces patch
Gavin M. Roy wrote: This is the patch I proposed on hackers to make pg_dump optionally ignore tablespaces. The patch is against 8.2.4. If I should be applying it to CVS head or what not, please let me know (along with any thoughts, concerns or issues). Hi Gavin, It should be against -head - there are changes to pg_dump in 8.3 that may well prevent a patch against 8.2.4 applying cleanly. As it's you, I assume you realise that you're too late for 8.3? Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Deferred RI trigger for non-key UPDATEs and subxacts
On Sat, 2007-07-14 at 00:12 +0100, Affan Salman wrote: With some time to spare, I thought I'd submit a quick-fix patch to the issue I reported here: http://archives.postgresql.org/pgsql-hackers/2007-07/msg00339.php This should preclude optimizing away a deferred RI trigger if the UPDATEd row (in the FK table) was inserted by current transaction (i.e. defined by TransactionIdIsCurrentTransactionId()) and not necessarily by our own transaction as the code currently says. Good bug fix, looks correct to me. I've re-checked all the call points of GetCurrentTransactionId() to see if there was any further abuse of it, but cannot find any. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] HOT latest patch - version 8
On Fri, 2007-07-13 at 16:22 +0100, Heikki Linnakangas wrote: Heikki Linnakangas wrote: I have some suggestions which I'll post separately, I'm looking for ways to make the patch simpler and less invasive. We may want to put back some of this stuff, or come up with a more clever solution, in future releases, but right let's keep it simple. I believe we're all trying to do that, but I would like to see some analysis of which techniques are truly effective and which are not. Simpler code may not have desirable behaviour and then the whole lot of code is pointless. Let's make it effective by making it complex enough. I'm not clear where the optimum lies. (c.f. Flying Buttresses). A significant chunk of the complexity and new code in the patch comes from pruning hot chains and reusing the space for new updates. Because we can't reclaim dead space in the page like a VACUUM does, without holding the vacuum lock, we have to deal with pages that contain deleted tuples, and be able to reuse them, and keep track of the changes in tuple length etc. A much simpler approach would be to try to acquire the vacuum lock, and compact the page the usual way, and fall back to a cold update if we can't get the lock immediately. The obvious downside of that is that if a page is continuously pinned, we can't HOT update tuples on it. Keeping in mind that the primary use case for HOT is largeish tables, small tables are handled pretty well by autovacuum, chances are pretty good that you can get a vacuum lock when you need it. The main problem HOT seeks to avoid is wasted inserts into indexes, and the subsequent VACUUMing that requires. Small tables have smaller indexes, so that the VACUUMing is less of a problem. If we have hot spots in larger tables, DSM would allow us to avoid the I/O on the main table, but we would still need to scan the indexes. So HOT *can* be better than DSM. I'm worried that requiring the vacuum lock in all cases will mean that HOT will be ineffective where it is needed most - in the hot spots - i.e. the blocks that contain frequently updated rows. [As an aside, in OLTP it is frequently the index blocks that become hot spots, so reducing index inserts because of UPDATEs will also reduce block contention] Our main test case for OLTP is DBT-2 which follows TPC-C in being perfectly scalable with no hot spots in the heap and limited hot spots in the indexes. As such it's a poor test of real world applications, where Benfold's Law holds true. Requiring the vacuum lock in all cases would allow good benchmark performance but would probably fail in the real world at providing good long term performance. I'm interested in some numbers that show which we need. I'm thinking of some pg_stats output that shows how many vac locks were taken and how many prunes were made. Something general that allows some beta testers to provide feedback on the efficacy of the patch. That leads to the suggestion that we should make the HOT pruning logic into an add-on patch, commit it, but evaluate its performance during beta. If we have no clear evidence of additional benefit, we remove it again. I'm not in favour of background retail vacuuming by the bgwriter. The timeliness of that is (similarly) in question and I think bgwriter has enough work to do already. [Just as a note to all performance testers: HOT is designed to show long-term steady performance. Short performance tests frequently show no benefit if sufficient RAM is available to avoid the table bloat and we avoid hitting the point where autovacuums kick in. I know Heikki knows this, just not sure we actually said it.] -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Deferred RI trigger for non-key UPDATEs and subxacts
Affan Salman [EMAIL PROTECTED] writes: With some time to spare, I thought I'd submit a quick-fix patch to the issue I reported here: http://archives.postgresql.org/pgsql-hackers/2007-07/msg00339.php I don't think this is right. If the original tuple was inserted by a subtransaction of our transaction, it will have been checked at subtransaction subcommit, no? ISTM what we need is to schedule the on-UPDATE trigger if the original tuple was inserted by either our current (sub)transaction or one of its parents, and those are not the semantics of TransactionIdIsCurrentTransactionId, unfortunately. Stephan, have you looked at this bug report? What do you think? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
