Re: [TLM] Re: How to insert on duplicate key?

On Thursday 27 December 2007 12:23, Samantha Atkins wrote:
> On Dec 24, 2007, at 11:15 PM, Greg Smith wrote:
> > This may be better because it isn't doing the query first. You may
> > discover that you need to aggressively run one of the VACUUM
> > processes (I'd guess regular and ANALYZE but not FULL) in order to
> > keep performance steady as the number of records grows. Anytime you
> > update a row, that becomes a dead row that's still taking up space,
> > and if you do a lot of those they get in the way of finding the rows
> > that are still live. Take a look at
> > http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html
> > to get an idea of the process.
>
> Whoa. I am going to have to dig into the implementation. What is
> wrong with update in place, concurrency issues? The dead row
> presumably is no longer indexed, right?

At the time your transaction commits, it cannot update in place, since someone
else may be looking at the old version of the row in the middle of thier
transaction, so you need two copies. Even after updated you still need some
pointer in the index for the old version of the row, in case it its
referenced again.

> Since it is known to be dead
> is it automatically removed when there are no live transaction that
> reference or may reference it and its data page space marked available
> for new rows? If not, why not? I'm dredging my mind for stuff from
> my RDBMS implementation grad course a very long time ago.
>

The problem is you have determined in your mind that a row is "known dead"
without explination of how that would actually be determined. A given
transaction doesn't have a way to determine if there are live transaction
looking at the row, that would require quite a bit of knowledge about what
else is occuring in the system to be able to determine that. That level of
knowledge/complexity is what vacuum takes care of.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL