Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Gregory Stark
Pavan Deolasee [EMAIL PROTECTED] writes: On 9/6/07, Gregory Stark [EMAIL PROTECTED] wrote: Ah, as I understand it you can't actually do the pruning then because the executor holds references to source tuple on the page. In other words you can never get the vacuum lock there because you

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Pavan Deolasee wrote: On 9/6/07, Gregory Stark [EMAIL PROTECTED] wrote: Tom Lane [EMAIL PROTECTED] writes: ISTM the only time we should be doing HOT cleanup work is when we are trying to insert a new tuple on that page --- and maybe even only when we try and it doesn't fit straight off.

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] writes: When I suggested that we get rid of the LP_DELETE flag for heap tuples, the tuple-level fragmentation and all that, and just take the vacuum lock and call PageRepairFragmentation, I was thinking that we'd do it in heap_update and only when we run

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: When I suggested that we get rid of the LP_DELETE flag for heap tuples, the tuple-level fragmentation and all that, and just take the vacuum lock and call PageRepairFragmentation, I was thinking that we'd do it in heap_update and

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Florian Pflug
Heikki Linnakangas wrote: That's a pretty sensitive tradeoff, we want to prune often to cut the long HOT chains, but not too often because it's pretty expensive to acquire the vacuum lock and move tuples around. I don't think we've found the optimal solution yet. Separating the pruning and

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: Another real problem with doing pruning only in UPDATE path is that we may end up with long HOT chains if the page does not receive a UPDATE, after many consecutive HOT updates. How is that, if the same number of

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: Another real problem with doing pruning only in UPDATE path is that we may end up with long HOT chains if the page does not receive a UPDATE, after many consecutive HOT updates. How is that, if the same number of prune attempts

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Alvaro Herrera wrote: Pruning is going to take place on next vacuum anyway, isn't it? Yes. If HOT is working well, you're not going to run vacuum very often, though. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Alvaro Herrera wrote: Pruning is going to take place on next vacuum anyway, isn't it? AFAIUI the point is that you can't do cleanup selectively only on UPDATE because of pointers that your process may already have to the page, which is why you wanted to do it on every heap_fetch. I suggest

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Florian Pflug
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: Another real problem with doing pruning only in UPDATE path is that we may end up with long HOT chains if the page does not receive a UPDATE, after many consecutive HOT updates. How is that, if the same number of

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Me Greg just had a little chat, and came up with this scheme: 1. on heap_update, if the page is full, you prune the page (but don't defragment it, because you can't get the vacuum lock). That hopefully leaves behind a large enough gap to put the new tuple in. Insert the new tuple in the gap, and

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Imagine a page with just one tuple on it: 1 After a bunch of updates, it looks like this 1 - 2 - 3 - 4 - 5 1 is the tuple the indexes point to, others are heap only. But if we were attempting prune at every update, at

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Florian Pflug wrote: Tom Lane wrote: A rather wild idea: Could we maybe pin individual tuples, instead of the whole page? Then we'd just have to be careful not to move those when pruning during the update. Uh, that's a huge change. We might be able to keep track of tuples that we have

Re: [PATCHES] tsearch refactorings

2007-09-06 Thread Heikki Linnakangas
Teodor Sigaev wrote: Heikki Linnakangas wrote: In any case, I think we need to calculate the CRC/hash in tsqueryrecv, instead of trusting the client. Agreed. I started to write a patch for that, as I realized that we're transferring the strings in a tsvector/tsquery in server encoding. That's

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Simon Riggs
On Wed, 2007-09-05 at 18:15 -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Uh, why would any of this code at all execute during a pure lookup? No idea. It seems an index lookup tries to prune a heap chain, and he was asking if it should look at other

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Alvaro Herrera
Heikki Linnakangas escribió: Hmm. I wonder if we could prune/defragment in bgwriter? That would be best, if at all possible. You can prune without accessing anything outside the page itself, right? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Alvaro Herrera wrote: Heikki Linnakangas escribió: Hmm. I wonder if we could prune/defragment in bgwriter? That would be best, if at all possible. You can prune without accessing anything outside the page itself, right? Yes, though you do need to have an oldest xmin to determine which

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Florian Pflug wrote: Heikki Linnakangas wrote: That's a pretty sensitive tradeoff, we want to prune often to cut the long HOT chains, but not too often because it's pretty expensive to acquire the vacuum lock and move tuples around. I don't think we've found the optimal solution yet.

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Alvaro Herrera
Heikki Linnakangas escribió: Every time you do an index lookup, you have to follow that chain from 1 to 5. Which gets expensive. That's why we want to prune the chain to make it shorter, even if there's still plenty of room on the page for updates, and even if we're never going to update it

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Simon Riggs
On Thu, 2007-09-06 at 16:15 +0100, Heikki Linnakangas wrote: Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Imagine a page with just one tuple on it: 1 After a bunch of updates, it looks like this 1 - 2 - 3 - 4 - 5 1 is the tuple the indexes point to, others

Re: [PATCHES] [HACKERS] pg_regress config

2007-09-06 Thread Magnus Hagander
Andrew Dunstan wrote: Magnus Hagander wrote: Andrew Dunstan wrote: I would like to add an argument to pg_regress that allows us to set some config options for the temp install. Specifically right now I am interested in setting the following: log_line_prefix = '[%c] ' log_statement

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Simon Riggs wrote: Yes, the main point is that an UPDATE doesn't always allow you to prune. You can always remove dead HOT tuples in heap_update. But you can never defragment the page at that point. If it did, that would be the right place. Since it doesn't the best place to prune is surely

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: I don't follow. HOT chains can only get longer by updates. Yes. We don't seem to be on the same wavelength... Imagine a page with just one tuple on it: 1 After a bunch of updates, it looks like this 1 - 2 - 3 - 4 - 5 1 is

Re: [PATCHES] tsearch refactorings

2007-09-06 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] writes: I started to write a patch for that, as I realized that we're transferring the strings in a tsvector/tsquery in server encoding. That's not good, right? A dump created with COPY ... BINARY wouldn't be portable across clusters with different

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Alvaro Herrera
Heikki Linnakangas escribió: Alvaro Herrera wrote: Heikki Linnakangas escribió: Hmm. I wonder if we could prune/defragment in bgwriter? That would be best, if at all possible. You can prune without accessing anything outside the page itself, right? Yes, though you do need to

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] writes: Alvaro Herrera wrote: That would be best, if at all possible. You can prune without accessing anything outside the page itself, right? Yes, though you do need to have an oldest xmin to determine which tuples are dead, and the removed tuples need

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes: Heikki Linnakangas escribió: and the removed tuples need to be WAL-logged. Is this a problem for bgwriter? bgwriter does checkpoints, which emit WAL records; and they also call GetOldestXmin. So we know a-priori that those two things work. There may

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Simon Riggs
On Thu, 2007-09-06 at 19:05 +0100, Heikki Linnakangas wrote: Pruning is expensive, you need to scan all tuples on the page and write WAL record. And defragment the page if you consider that part of pruning. You don't want to do it too aggressively. OK, so my understanding or the meaning of

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Tom Lane
I wrote: Hmm ... also check commit status (pg_clog access). I've always thought that those things couldn't be done in bgwriter, because it wasn't running real transactions, but right at the moment I can't see that there is any obstacle. Perhaps that meme dates from a time when GetOldestXmin

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Heikki Linnakangas
Tom Lane wrote: I wrote: Hmm ... also check commit status (pg_clog access). I've always thought that those things couldn't be done in bgwriter, because it wasn't running real transactions, but right at the moment I can't see that there is any obstacle. Perhaps that meme dates from a time

[PATCHES] Re: [BUGS] create index concurrently blocks on transactions in other databases

2007-09-06 Thread Simon Riggs
On Thu, 2007-09-06 at 21:47 +0100, Simon Riggs wrote: On Thu, 2007-09-06 at 15:55 -0400, Tom Lane wrote: This would have been painful to fix in 8.2 but it seems relatively trivial in HEAD --- we just need another filter option in GetCurrentVirtualXIDs. Patch enclosed. Tested manually on

Re: [PATCHES] HOT patch - version 15

2007-09-06 Thread Greg Smith
On Thu, 6 Sep 2007, Heikki Linnakangas wrote: I wonder if pruning in bgwriter only is enough to make HOT work efficiently. On a very frequently updated page, bgwriter will have to work hard to keep up. One of the goals for how I rebuilt the just-in-time BGW was to try and make smaller values

Re: [PATCHES] Re: [BUGS] create index concurrently blocks on transactions in other databases

2007-09-06 Thread Tom Lane
Simon Riggs [EMAIL PROTECTED] writes: On Thu, 2007-09-06 at 15:55 -0400, Tom Lane wrote: This would have been painful to fix in 8.2 but it seems relatively trivial in HEAD --- we just need another filter option in GetCurrentVirtualXIDs. Patch enclosed. Tested manually on same/different