pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrei Bintintan <klodoma@ar-sd.net>
To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
Cc: pgsql-admin@postgresql.org
Subject: Re: duplicates
Date: Mon, 6 Sep 2004 13:04:07 +0300
Message-ID: <00e801c493f8$da7f12f0$0b00a8c0@forge> (raw)
References: <Pine.LNX.4.58.0409060922390.20253@p-roman.jct.ac.il>
	<008601c493e5$e0965940$0b00a8c0@forge>
	<Pine.LNX.4.58.0409061050190.20694@p-roman.jct.ac.il>
	<00c701c493eb$def5b9e0$0b00a8c0@forge>
	<Pine.LNX.4.58.0409061211580.20694@p-roman.jct.ac.il>


----- Original Message -----
From: "Tsirkin Evgeny" <tsurkin@mail.jct.ac.il>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: <pgsql-admin@postgresql.org>
Sent: Monday, September 06, 2004 12:19 PM
Subject: Re: [ADMIN] duplicates


> yes i understand that i can create a primary key/unique etc...
> however my question is if i have to understand if and why
> the i got duplicate rows inserted.
> so here is the picture:
> an application is deleting rows and inserting right after that
> new ones some of which are the same as the old one,and i am getting

Maybe you're inserting not only one time that dupplicate pair. In your table
definition nothing stopes this.

> duplicates !Don't transaction should prevent this by not letting
> insert ot do something in case the delete did not succeed?

Only if you check your query's result and you say "rollback" to the
transaction in case of an error.

> Another option is that i have to clients deleting and then inserting the
> same thing into table ,but again should not transaction prevent duplicates
> in that case ?

No, perhaps you're missunderstanding transactions. Transactions don't
prevent anything, only if you say so.

For ex:
Begin
Delete query -- if here is error you should run a Rollback and don't run any
insert.
If the delete query succedes then you should run the inserts... etc etc.
At the end of the transaction you should  Commit ONLY if everything worked
as you wanted!!!!

The uniqe indexes helps you to prevent double inserts into the database.
For example:
CREATE UNIQUE INDEX table_column_uniqueidx ON table(studentid, groupid);
will assure you that you have only one ROW with this pair of data. By assure
I mean, that if you have the pair (1111, 22) in the table, and you want to
insert another (1111, 22) pair into the table, the insert query will fail.

In the transactions you will have to check the result of every
delete/insert/update query so that you know there was a failuire.

You can also look in the database log file, and see exaclty what happened
there.


Hope this helps.

>
> > Use something like this:
> >
> >  CREATE TABLE schedule (
> >     id serial PRIMARY KEY,
> >     studentid    decimal(9),
> >     groupid      decimal(10),
> >     maslulsignid decimal(7),
> >     tfusot        varchar(29)
> >  );
> >
>
>
> --
> Evgeny.




view thread (14+ messages)  latest in thread

Message-ID: <00e801c493f8$da7f12f0$0b00a8c0@forge>
Permalink:  ../00e801c493f8$da7f12f0$0b00a8c0@forge/
Also on:    postgresql.org/message-id/00e801c493f8$da7f12f0$0b00a8c0@forge

 · 

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: klodoma@ar-sd.net, tsurkin@mail.jct.ac.il
  Subject: Re: duplicates
  In-Reply-To: <00e801c493f8$da7f12f0$0b00a8c0@forge>
  References: <Pine.LNX.4.58.0409060922390.20253@p-roman.jct.ac.il> <008601c493e5$e0965940$0b00a8c0@forge> <Pine.LNX.4.58.0409061050190.20694@p-roman.jct.ac.il> <00c701c493eb$def5b9e0$0b00a8c0@forge> <Pine.LNX.4.58.0409061211580.20694@p-roman.jct.ac.il> <00e801c493f8$da7f12f0$0b00a8c0@forge>

* 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