On 11/15/05, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Csaba Nagy wrote:
>
> > session_1=> create table test (col smallint primary key);
> > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> > "test_pkey" for table "test"
> > CREATE TABLE
> > session_1=> begin;
> > BEGIN
> > cnagy=> insert into test values (1);
> > INSERT 165068987 1
> >
> > session_2=> begin;
> > BEGIN
> > session_2=> insert into test values (1);
> >
> > [session_2 is now waiting]
>
> This only happens because of the unique index. There's no predicate
> locking involved. The btree code goes some lengths to make this work;
> it would be probably simple to modify this to support MERGE or REPLACE
> on the limited cases where there's a UNIQUE index. Tom has already said
> this twice (on this thread only; he has already said it before IIRC.)
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
And the only type of predicate locking we need for MySQL REPLACE
because it needs a pk or unique index to know it has to replace
otherwise it inserts the row...
that's the way it works as mysql spec said...
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)