Re: On-line backup

Hey Tom,

Here is the query:

DELETE FROM recent_projects WHERE project_id = 3 AND user_id = 139;

And here is the query plan:

Index Scan using pk_recent_projects on recent_projects (cost=0.00..5.81
rows=1 width=6)
Index Cond: ((user_id = 139) AND (project_id = 3))

The table definition is :

CREATE TABLE recent_projects
(
user_id int4 NOT NULL,
project_id int4 NOT NULL,
last_viewed timestamp,
CONSTRAINT pk_recent_projects PRIMARY KEY (user_id, project_id),
CONSTRAINT fk_recent_project_id FOREIGN KEY (project_id)
REFERENCES project (project_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE CASCADE,
CONSTRAINT fk_recent_user_id FOREIGN KEY (user_id)
REFERENCES users (user_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;

This is a table with a lot of transactions.

The behavior we noticed is that we do the delete as specified above and then
do a reinsert with a new timestamp and everything else the same (lazy I
know, but not my code). What happens some of the time is that the reinsert
fails and give a duplicate key failure. What has fixed this in the past is
reindexing the table - but we don't want to rely on that forever.

We are also have an issue with processes locking up. We can't kill -9 pid
because postgres ends up restarting the whole cluster. What can we do?
kill -s INT TERM or SIGQUIT don't seem to work either. Should we be root or
logged in as postgres when we try to kill these?

Thanks!
~DjK

>From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>To: "Mr. Dan" <bitsandbytes88(at)hotmail(dot)com>
>CC: pgsql-admin(at)postgresql(dot)org
>Subject: Re: [ADMIN] On-line backup Date: Mon, 17 Jul 2006 14:43:30 -0400
>
>"Mr. Dan" <bitsandbytes88(at)hotmail(dot)com> writes:
> > ... What happens is that we have a 'hot' table (one with many many
> > transactions) that gets inserted and deleted often. About once a month
>now
> > when we do a select from that table the results of the select do not
>match
> > the where clause, ex.
>
> > select * from recent_projects
> > where user_id = 139
>
> > sometimes produces these results:
>
> > user_id project_id
> > 139 3
> > 139 1
> > 139 17
> > 754 11
>
>Hmmm .... that looks sorta familiar. What is the query plan that's used
>for this SELECT?
>
> regards, tom lane