Re: Duplicated entries are not ignored even if a "do instead nothing" rule is added.

You accidentally clicked "Reply" instead of "Reply-all" ;)

On 4 Jan 2012, at 3:03, 邓尧 wrote:

>> On Tue, Jan 3, 2012 at 3:42 PM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
>> On 3 Jan 2012, at 5:20, 邓尧 wrote:
>>
>> > Hi,
>> >
>> > I'm new to pgsql, I need the do something like the "INSERT IGNORE" in mysql. After some searching I got a solution, which is adding a "do instead nothing" rule to the corresponding table, but it fails sometimes.
>>
>> Yeah, if a concurrent transaction tries to create the same record, one of the transactions is going to find that it already exists on transaction commit. An INSERT-rule is not going to protect you against that.
>>
> I modified the code, just commit after every insertion. Haven't seen this kind of error for a few hours. It might hurt the performance, but for my application, database isn't the bottleneck.
>
>
>> > The table and the rule is created with the following sql statements:
>> >
>> > create sequence ACCOUNT_ID_SEQ;
>> > create table ACCOUNT (
>> > ID bigint primary key default nextval('ACCOUNT_ID_SEQ'),
>> > HOME char(255) not null,
>> > NAME char(255)
>> > );
>> > create unique index on ACCOUNT(HOME);
>> > create index on ACCOUNT(NAME);
>>
>> It seems to me that account(home) is actually the PK - do you really need the artificial id column? That is a matter of personal preference; wars are waged on artificial vs natural keys. People in here will usually tell you to use what fits the problem best, both sides have benefits and drawbacks ;)
>>
>> Another problem you'll have is that char columns are padded up to their full size with spaces - you'll end up trimming every value in your client applications. You probably want varchar(255) or perhaps better, text. The latter also rids you of that 255 length limit from mysql.
>>
> Thanks for the advice, I actually ran into a bug because of the padded spaces ;p
>
>
>> > There are about 20 clients do the following insertion (no UPDATE, some of them might DELETE):
>> > begin transaction:
>> > insert into ACCOUNT(HOME) values (v1);
>> > insert into ACCOUNT(HOME) values (v2);
>> > ...
>> > commit;
>> >
>> > Sometimes I got the error says the unique constraint "account_home_idx" is violated.
>> >
>> > Any suggestions?
>>
>> I assume you're talking about parallel inserts from a multi-process tool for importing this data?
>> Yes, I'm running a specific crawler. Crawl a twitter like web site. Python 3 + psycopg2 is my programming platform.
>>
>>
>> First of all, especially if you're inserting a lot of data like this, see if you can use COPY FROM STDIN instead. That loads the whole transaction contents in one go, which is a lot more efficient then thousands of sequential inserts. As it's a single statement that way, you don't even need to wrap it in a transaction anymore - you'll get an implicit transaction per single statement, which is in this case exactly what you want for this single COPY statement.
> True, I don't need transactions, neither do I want them, but psycopg2 create transactions for me automatically :-(

Well, if psycopg didn't, Postgres would wrap each statement in a transaction anyway. But that doesn't matter.

The problem was that you were verifying whether you were running into a conflict in one transaction (say 'A') before a potentially conflicting commit in a parallel transaction (say 'B').

So while you were verifying in 'A', transaction 'B' wasn't committed yet and therefore the conflicting row from 'B' wasn't visible to 'A'. When you then tried to commit 'A', the database found there was a conflicting record: namely the one from transaction 'B' that was just committed.

Alban Hertroys

P.S: Normally I would have trimmed down this mail to the bottom part of the conversation, but as the original mail didn't make it to the list I decided to keep all of it.

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.