Re: DELETE, INSERT vs SELECT, UPDATE || INSERT

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Anton Maksimenkov <engineer(at)hlebprom(dot)ru>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: DELETE, INSERT vs SELECT, UPDATE || INSERT
Date: 2005-12-23 15:30:00
Message-ID: 19921.1135351800@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Anton Maksimenkov <engineer(at)hlebprom(dot)ru> writes:
> Second idea was to try to SELECT (user, cookie) pair and then UPDATE
> it if it exists or INSERT if not. I has thought that if UPDATE will
> rewrite same place in file with new count it may lead to more compact
> table (file not grow and information about actual rows in file will
> not changed).

You're wasting your time, because Postgres doesn't work that way.
UPDATE is really indistinguishable from DELETE+INSERT, and there will
always be a dead row afterwards, because under MVCC rules both versions
of the row have to be left in the table for some time after your
transaction commits. See
http://www.postgresql.org/docs/8.1/static/mvcc.html

regards, tom lane

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2005-12-23 15:54:48 Re: Order by behaviour
Previous Message Guido Neitzer 2005-12-23 15:03:36 Re: Order by behaviour