Re: Duplicate key issue in a transaction block

Hi All,

I decided to go with the following fix. Instead of strait delete and insert statements I am gonna use stored procedures
fordelete and insert: 

CREATE OR REPLACE FUNCTION delete_group(integer)
RETURNS void AS
$BODY$
     BEGIN
         delete from infotest where infotest.groupid = $1;
         RETURN;
     END;
 $BODY$
LANGUAGE 'plpgsql' VOLATILE;


CREATE OR REPLACE FUNCTION insert_group(integer, integer, timestamp with time zone)
  RETURNS void AS
$BODY$
     BEGIN
         insert into infotest (groupid, subgroupid, datemodified)
        select $1,$2,$3 where 1 not in (select 1 from infotest where groupid = $1 and subgroupid = $2);
         RETURN;
     END;
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE

This solved my problem.
Sequoia is not gonna drop a backend if the delete returns different number of updated rows (because the procedure
returnsvoid) and the record is not gonna be inserted anymore if already exists in the table... 

The info in this table is not critical and does not affect the functionality of the application. Also is gonna be
refreshedat each login. So I am sure I am safe with this solution... 

Thanks for the help,
Ioana


--- On Mon, 6/8/09, Ioana Danes <ioanasoftware@yahoo.ca> wrote:

> From: Ioana Danes <ioanasoftware@yahoo.ca>
> Subject: Re: [GENERAL] Duplicate key issue in a transaction block
> To: "Scott Marlowe" <scott.marlowe@gmail.com>
> Cc: "Bill Moran" <wmoran@potentialtech.com>, "Vyacheslav Kalinin" <vka@mgcp.com>, "PostgreSQL General"
<pgsql-general@postgresql.org>
> Received: Monday, June 8, 2009, 2:58 PM
>
> I am actually using Sequoia and the big problem is not the
> primary key error. I could leave with that!
> Sometimes the problem is generated not by insert but by the
> delete because the two transactions don't happen on the
> exactly same order and time on both backends and then the
> delete returns different updated records on the the db
> servers and sequoia drops one backend...
> That is my big pain...............
>
> I have some ideas I will test now and I will let you know
> which one I choose to fix my problem...
>
> Thanks a lot for your help,
> Ioana
>
>
> --- On Mon, 6/8/09, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>
> > From: Scott Marlowe <scott.marlowe@gmail..com>
> > Subject: Re: [GENERAL] Duplicate key issue in a
> transaction block
> > To: "Ioana Danes" <ioanasoftware@yahoo.ca>
> > Cc: "Bill Moran" <wmoran@potentialtech.com>,
> "Vyacheslav Kalinin" <vka@mgcp.com>,
> "PostgreSQL General" <pgsql-general@postgresql.org>
> > Received: Monday, June 8, 2009, 2:37 PM
> > On Mon, Jun 8, 2009 at 12:25 PM,
> > Ioana Danes<ioanasoftware@yahoo.ca>
> > wrote:
> > >
> > > Well, I guess I have my answer...
> > >
> > > I tried to narrow down an issue I get on one of
> the
> > production sites, where using a similar transaction I
> get
> > the same error.
> > >
> > > In my production environment the group id is
> actually
> > a unique number for the terminal (terminalid) and the
> same
> > transaction CANNOT be called for the same terminalid.
> So
> > this should never happen because each terminal has its
> own
> > ID and this procedure is called on login operation for
> each
> > terminal... At least that's what I thought so...
> > >
> > > But it does happen during the nightly online
> backup
> > (pg_dump).
> > > It looks like when the client logs in, the
> request is
> > sent from the client to the db, the backup (pg_dump)
> slows
> > down the server (or holds the lock on that table?) and
> the
> > user does not have patience and restarts the client
> and logs
> > in again.
> > > In this case I can get two parallel transactions
> for
> > the same terminal...
> >
> > You mentioned earlier you're using slony for
> replication,
> > so the
> > answer is obvious, run the backup against a read
> slave, and
> > set the
> > users, during backup, to only have access to the
> written to
> > master.
> >
>
>
>      
> __________________________________________________________________
> Looking for the perfect gift? Give the gift of Flickr!
>
> http://www.flickr.com/gift/
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


      __________________________________________________________________
Yahoo! Canada Toolbar: Search from anywhere on the web, and bookmark your favourite sites. Download it now
http://ca.toolbar.yahoo.com.