pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mr. Dan <bitsandbytes88@hotmail.com>
To: tgl@sss.pgh.pa.us
Cc: pgsql-admin@postgresql.org
Subject: Re: On-line backup
Date: Wed, 19 Jul 2006 11:51:31 -0400
Message-ID: <BAY116-F335616657FEF6D0113DC0AD1600@phx.gbl> (raw)
In-Reply-To: <4954.1153161810@sss.pgh.pa.us>

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





view thread (5+ messages)

Message-ID: <BAY116-F335616657FEF6D0113DC0AD1600@phx.gbl>
Permalink:  ../BAY116-F335616657FEF6D0113DC0AD1600@phx.gbl/
Also on:    postgresql.org/message-id/BAY116-F335616657FEF6D0113DC0AD1600@phx.gbl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: bitsandbytes88@hotmail.com, tgl@sss.pgh.pa.us
  Subject: Re: On-line backup
  In-Reply-To: <BAY116-F335616657FEF6D0113DC0AD1600@phx.gbl>
  References: <4954.1153161810@sss.pgh.pa.us> <BAY116-F335616657FEF6D0113DC0AD1600@phx.gbl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox