pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedduplicates
14+ messages / 5 participants
[nested] [flat]
* duplicates
@ 2004-09-06 06:33 Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
0 siblings, 1 reply; 14+ messages in thread
From: Tsirkin Evgeny @ 2004-09-06 06:33 UTC (permalink / raw)
To: pgsql-admin
Hello dear list!
Here is the problem i have:
i am using 7.3.4 postgres .i have an aplication that updating 2 tables.
while it needs to update something - it does not select the rows that
are already in the table search what it needs to update and execute
an 'update' query .in place it deletes all the old rows and inserts the
new one.However while we have a havy load we got a duplicate rows in the
table ,althought we use transaction an both delete and the new insert
are in the same transaction.We are pretty sure there is no bug in
the applicatioin that inserts the data more then once .
Is that a known problem ?What could be the problem?
--
Evgeny.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
@ 2004-09-06 07:48 ` Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
0 siblings, 1 reply; 14+ messages in thread
From: Andrei Bintintan @ 2004-09-06 07:48 UTC (permalink / raw)
To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; pgsql-admin
I am not sure that I understand clearly your problem. Are you sure that your
query's are written correctly?
For duplicates you can make a uniqe indexing so this will avoid any
dupplicates in your table.
CREATE UNIQUE INDEX table_column_uniqueidx ON table(column);
If the rows are dissapearing, please check your delete query, because that
is the only way to erase all rows from the table.
If still got problems, please post some queries, be more specific.
Best regards,
Andy.
----- Original Message -----
From: "Tsirkin Evgeny" <tsurkin@mail.jct.ac.il>
To: <pgsql-admin@postgresql.org>
Sent: Monday, September 06, 2004 9:33 AM
Subject: [ADMIN] duplicates
> Hello dear list!
> Here is the problem i have:
> i am using 7.3.4 postgres .i have an aplication that updating 2 tables.
> while it needs to update something - it does not select the rows that
> are already in the table search what it needs to update and execute
> an 'update' query .in place it deletes all the old rows and inserts the
> new one.However while we have a havy load we got a duplicate rows in the
> table ,althought we use transaction an both delete and the new insert
> are in the same transaction.We are pretty sure there is no bug in
> the applicatioin that inserts the data more then once .
> Is that a known problem ?What could be the problem?
>
> --
> Evgeny.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
@ 2004-09-06 07:57 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
0 siblings, 1 reply; 14+ messages in thread
From: Tsirkin Evgeny @ 2004-09-06 07:57 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-admin
On Mon, 6 Sep 2004, Andrei Bintintan wrote:
> If still got problems, please post some queries, be more specific.
>
> Best regards,
> Andy.
>
Ok i will try:
CREATE TABLE schedule (
studentid decimal(9),
groupid decimal(10),
maslulsignid decimal(7),
tfusot varchar(29)
);
that is the table for writing down the courses/groups
a students takes.note there is NO unique constrain here
(maybe it should be but that the way it is and probably
can't can't be changed).while changing groups for a student
the applic. deletes all the groups the student had :
delete from schedule where studentid=11111;
and then inserts the new groups (regular inserts).
Now sometimes we got duplicates of the same groupid and
studentid in the table(everything is same).i thought that \
maybe the delete do not delete but the insert succeed?
Note :I know that there could be created the a unique key and
a constrain but befor we have to understand why the duplicates
were created.
--
Evgeny.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
@ 2004-09-06 08:31 ` Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 09:19 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
0 siblings, 1 reply; 14+ messages in thread
From: Andrei Bintintan @ 2004-09-06 08:31 UTC (permalink / raw)
To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; +Cc: pgsql-admin
----- 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 10:57 AM
Subject: Re: [ADMIN] duplicates
> On Mon, 6 Sep 2004, Andrei Bintintan wrote:
>
> > If still got problems, please post some queries, be more specific.
> >
> > Best regards,
> > Andy.
> >
> Ok i will try:
> CREATE TABLE schedule (
> studentid decimal(9),
> groupid decimal(10),
> maslulsignid decimal(7),
> tfusot varchar(29)
> );
Use something like this:
CREATE TABLE schedule (
id serial PRIMARY KEY,
studentid decimal(9),
groupid decimal(10),
maslulsignid decimal(7),
tfusot varchar(29)
);
Now, ALWAYS use a ID for a table. This id will be always uniqe(because it's
primary key). You don't have to insert this field when you're making an
insert, the server does it automatically. And when you delete you refere
this key and not the student ID.
For ex: you have student id = 1111 in 3 groups
id studentid groupid maslulsignid
1 1111 22 some val
2 1111 33 some val
3 1111 44 some val
If you delete: "Delete from table where studentid=1111" it deletes all the
fields from table. If you want to delete only field 2 then you delete:
delete from table where id=2.
Normally in this kind of tables you should never have dupplicates I mean, in
tables you should not have dupplicates(all fields the samein your case
(studentid groupid maslulsignid tfusot) the same val - this means
structure conception error).
Hope this helps.
Best regards.
>
> that is the table for writing down the courses/groups
> a students takes.note there is NO unique constrain here
> (maybe it should be but that the way it is and probably
> can't can't be changed).while changing groups for a student
> the applic. deletes all the groups the student had :
> delete from schedule where studentid=11111;
> and then inserts the new groups (regular inserts).
> Now sometimes we got duplicates of the same groupid and
> studentid in the table(everything is same).i thought that \
> maybe the delete do not delete but the insert succeed?
>
> Note :I know that there could be created the a unique key and
> a constrain but befor we have to understand why the duplicates
> were created.
>
> --
> Evgeny.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
@ 2004-09-06 09:19 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 09:40 ` Re: duplicates Kostis Mentzelos <mentzelos@gmx.net>
2004-09-06 10:04 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 10:40 ` Re: duplicates Michael Paesold <mpaesold@gmx.at>
0 siblings, 3 replies; 14+ messages in thread
From: Tsirkin Evgeny @ 2004-09-06 09:19 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-admin
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
duplicates !Don't transaction should prevent this by not letting
insert ot do something in case the delete did not succeed?
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 ?
> Use something like this:
>
> CREATE TABLE schedule (
> id serial PRIMARY KEY,
> studentid decimal(9),
> groupid decimal(10),
> maslulsignid decimal(7),
> tfusot varchar(29)
> );
>
--
Evgeny.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 09:19 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
@ 2004-09-06 09:40 ` Kostis Mentzelos <mentzelos@gmx.net>
2004-09-06 10:08 ` Re: duplicates Kostis Mentzelos <mentzelos@gmx.net>
2 siblings, 1 reply; 14+ messages in thread
From: Kostis Mentzelos @ 2004-09-06 09:40 UTC (permalink / raw)
To: pgsql-admin
Tsirkin Evgeny wrote:
> 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
> duplicates !Don't transaction should prevent this by not letting
> insert ot do something in case the delete did not succeed?
> 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 ?
>
>
>>Use something like this:
>>
>> CREATE TABLE schedule (
>> id serial PRIMARY KEY,
>> studentid decimal(9),
>> groupid decimal(10),
>> maslulsignid decimal(7),
>> tfusot varchar(29)
>> );
>>
>
>
>
No, you should lock the table.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 09:19 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 09:40 ` Re: duplicates Kostis Mentzelos <mentzelos@gmx.net>
@ 2004-09-06 10:08 ` Kostis Mentzelos <mentzelos@gmx.net>
0 siblings, 0 replies; 14+ messages in thread
From: Kostis Mentzelos @ 2004-09-06 10:08 UTC (permalink / raw)
To: pgsql-admin
> No, you should lock the table.
I mean that you can select or insert into that table even if a
transaction is running.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 09:19 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
@ 2004-09-06 10:04 ` Andrei Bintintan <klodoma@ar-sd.net>
2 siblings, 0 replies; 14+ messages in thread
From: Andrei Bintintan @ 2004-09-06 10:04 UTC (permalink / raw)
To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; +Cc: pgsql-admin
----- 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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 09:19 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
@ 2004-09-06 10:40 ` Michael Paesold <mpaesold@gmx.at>
2004-09-06 10:51 ` Re: duplicates Michael Paesold <mpaesold@gmx.at>
2004-09-06 12:10 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2 siblings, 2 replies; 14+ messages in thread
From: Michael Paesold @ 2004-09-06 10:40 UTC (permalink / raw)
To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; +Cc: pgsql-admin
Tsirkin Evgeny wrote:
> On Mon, 6 Sep 2004, Michael Paesold wrote:
> Does not the Serializable Isolation Level do insure that?
> what i thought is that while using this level then i am getting
> the BEGIN and COMMIT to behave the same as the code you wrote!
> since the second concarent transaction (the one that started a little
> later) should wait for the first one and only then DELETE .
> Is not that true?
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DELETE FROM schedule WHERE studentid = ... ;
INSERT INTO schedule (studentid, ...) VALUES (... );
INSERT INTO schedule (studentid, ...) VALUES (... );
COMMIT;
If you do it like in the above sql code, there is still a problem. The
serializable checking here only works, if DELETE FROM schedule... really
finds at least one row. If it does not, it will not recognize the
serialization problem. So it's still possible that your programm creates
duplicates.
I have tested this here. I don't really know if this is just the case with
PostgreSQL serializable transactions (MVCC limitation) or a general problem.
> PS Does not the perl::DBI use the serializable level?
I don't really know about DBI, you should really check that.
* check that autocommit is off
* check that transaction isolation level is serializable
Still serializable transaction level is not enough. An aproach that allows
more concurrency is possible, but you have to make sure, that all relevant
code does the locking in the same way:
(assuming you have a student table with studentid as primary/unique key)
BEGIN;
SELECT * FROM student WHERE studentid = ... FOR UPDATE;
-- lock the student record
DELETE FROM schedule WHERE studentid = ... ;
INSERT INTO schedule (studentid, ...) VALUES (... );
INSERT INTO schedule (studentid, ...) VALUES (... );
COMMIT;
This will not lock the whole schedule table, but only one row of the student
table.
I hope that helps.
Best Regards,
Michael Paesold
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 09:19 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 10:40 ` Re: duplicates Michael Paesold <mpaesold@gmx.at>
@ 2004-09-06 10:51 ` Michael Paesold <mpaesold@gmx.at>
2004-09-06 10:56 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
1 sibling, 1 reply; 14+ messages in thread
From: Michael Paesold @ 2004-09-06 10:51 UTC (permalink / raw)
To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; +Cc: pgsql-admin
I wrote:
> BEGIN;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>
> DELETE FROM schedule WHERE studentid = ... ;
> INSERT INTO schedule (studentid, ...) VALUES (... );
> INSERT INTO schedule (studentid, ...) VALUES (... );
>
> COMMIT;
>
> If you do it like in the above sql code, there is still a problem. The
> serializable checking here only works, if DELETE FROM schedule... really
> finds at least one row. If it does not, it will not recognize the
> serialization problem. So it's still possible that your programm creates
> duplicates.
>
> I have tested this here. I don't really know if this is just the case with
> PostgreSQL serializable transactions (MVCC limitation) or a general
problem.
There is a detailed description about this problem in Section 12.2.2.1. of
the PostgreSQL 8 docs here:
http://developer.postgresql.org/docs/postgres/transaction-iso.html
(Serializable Isolation versus True Serializability)
Best Regards,
Michael Paesold
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 09:19 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 10:40 ` Re: duplicates Michael Paesold <mpaesold@gmx.at>
2004-09-06 10:51 ` Re: duplicates Michael Paesold <mpaesold@gmx.at>
@ 2004-09-06 10:56 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
0 siblings, 0 replies; 14+ messages in thread
From: Tsirkin Evgeny @ 2004-09-06 10:56 UTC (permalink / raw)
To: Michael Paesold <mpaesold@gmx.at>; +Cc: pgsql-admin; eitanr@savion.cc.huji.ac.il
You are greate Michael!
Thanks.
On Mon, 6 Sep 2004, Michael Paesold wrote:
> I wrote:
>
> > BEGIN;
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> >
> > DELETE FROM schedule WHERE studentid = ... ;
> > INSERT INTO schedule (studentid, ...) VALUES (... );
> > INSERT INTO schedule (studentid, ...) VALUES (... );
> >
> > COMMIT;
> >
> > If you do it like in the above sql code, there is still a problem. The
> > serializable checking here only works, if DELETE FROM schedule... really
> > finds at least one row. If it does not, it will not recognize the
> > serialization problem. So it's still possible that your programm creates
> > duplicates.
> >
> > I have tested this here. I don't really know if this is just the case with
> > PostgreSQL serializable transactions (MVCC limitation) or a general
> problem.
>
> There is a detailed description about this problem in Section 12.2.2.1. of
> the PostgreSQL 8 docs here:
> http://developer.postgresql.org/docs/postgres/transaction-iso.html
>
> (Serializable Isolation versus True Serializability)
>
> Best Regards,
> Michael Paesold
>
--
Evgeny.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 09:19 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 10:40 ` Re: duplicates Michael Paesold <mpaesold@gmx.at>
@ 2004-09-06 12:10 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 12:30 ` Re: duplicates Michael Paesold <mpaesold@gmx.at>
1 sibling, 1 reply; 14+ messages in thread
From: Tsirkin Evgeny @ 2004-09-06 12:10 UTC (permalink / raw)
To: Michael Paesold <mpaesold@gmx.at>; +Cc: pgsql-admin
one moer question ,how did you tested it?
> I have tested this here. I don't really know if this is just the case with
> Best Regards,
> Michael Paesold
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
--
Evgeny.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 09:19 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 10:40 ` Re: duplicates Michael Paesold <mpaesold@gmx.at>
2004-09-06 12:10 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
@ 2004-09-06 12:30 ` Michael Paesold <mpaesold@gmx.at>
2004-09-06 14:36 ` Re: duplicates Jean-Luc Lachance <jllachan@sympatico.ca>
0 siblings, 1 reply; 14+ messages in thread
From: Michael Paesold @ 2004-09-06 12:30 UTC (permalink / raw)
To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; +Cc: pgsql-admin
Tsirkin Evgeny wrote:
> one moer question ,how did you tested it?
>
> > I have tested this here. I don't really know if this is just the case
with
> > Best Regards,
> > Michael Paesold
First I created the your schedule table. Then I opened two psql sessions...
Session A Session B
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DELETE FROM schedule WHERE studentid = 1;
INSERT INTO schedule VALUES (1, 1, 0, 0);
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DELETE FROM schedule WHERE studentid = 1;
-- if there existed records for this delete,
-- session B will now wait for session A to
-- commit/rollback and error out on commit;
-- otherwise no error
COMMIT;
INSERT INTO schedule VALUES (1, 1, 0, 0);
COMMIT;
You can also try and rollback the first transaction etc.
Again, what really helps is:
ALTER TABLE schedule ADD CONSTRAINT uniq_schedule UNIQUE (studentid,
groupid);
Best Regards,
Michael Paesold
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: duplicates
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Re: duplicates Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 09:19 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 10:40 ` Re: duplicates Michael Paesold <mpaesold@gmx.at>
2004-09-06 12:10 ` Re: duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 12:30 ` Re: duplicates Michael Paesold <mpaesold@gmx.at>
@ 2004-09-06 14:36 ` Jean-Luc Lachance <jllachan@sympatico.ca>
0 siblings, 0 replies; 14+ messages in thread
From: Jean-Luc Lachance @ 2004-09-06 14:36 UTC (permalink / raw)
To: Michael Paesold <mpaesold@gmx.at>; +Cc: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; pgsql-admin
If the reccord you are inserting do not depend on data in the record you
want to delete, why not simply use a trigger?
Before insert delete the record with the same key!
Michael Paesold wrote:
> Tsirkin Evgeny wrote:
>
>
>
>>one moer question ,how did you tested it?
>>
>>
>>>I have tested this here. I don't really know if this is just the case
>
> with
>
>>>Best Regards,
>>>Michael Paesold
>
>
> First I created the your schedule table. Then I opened two psql sessions...
>
> Session A Session B
>
> BEGIN;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> DELETE FROM schedule WHERE studentid = 1;
> INSERT INTO schedule VALUES (1, 1, 0, 0);
>
> BEGIN;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> DELETE FROM schedule WHERE studentid = 1;
> -- if there existed records for this delete,
> -- session B will now wait for session A to
> -- commit/rollback and error out on commit;
> -- otherwise no error
>
>
> COMMIT;
> INSERT INTO schedule VALUES (1, 1, 0, 0);
> COMMIT;
>
>
> You can also try and rollback the first transaction etc.
>
> Again, what really helps is:
> ALTER TABLE schedule ADD CONSTRAINT uniq_schedule UNIQUE (studentid,
> groupid);
>
> Best Regards,
> Michael Paesold
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2004-09-06 14:36 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-06 06:33 duplicates Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 07:48 ` Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 07:57 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 08:31 ` Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 09:19 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 09:40 ` Kostis Mentzelos <mentzelos@gmx.net>
2004-09-06 10:08 ` Kostis Mentzelos <mentzelos@gmx.net>
2004-09-06 10:04 ` Andrei Bintintan <klodoma@ar-sd.net>
2004-09-06 10:40 ` Michael Paesold <mpaesold@gmx.at>
2004-09-06 10:51 ` Michael Paesold <mpaesold@gmx.at>
2004-09-06 10:56 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 12:10 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-09-06 12:30 ` Michael Paesold <mpaesold@gmx.at>
2004-09-06 14:36 ` Jean-Luc Lachance <jllachan@sympatico.ca>
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