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@sss.pgh.pa.us>
>To: "Mr. Dan" <bitsandbytes88@hotmail.com>
>CC: pgsql-admin@postgresql.org
>Subject: Re: [ADMIN] On-line backup Date: Mon, 17 Jul 2006 14:43:30 -0400
>
>"Mr. Dan" <bitsandbytes88@hotmail.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