On Thu, Sep 20, 2012 at 11:50:58AM +0100, Will Crawford wrote: > On 20 September 2012 11:19, Peter Rabbitson <rabbit+d...@rabbit.us> wrote: > > > I experimented with some stuff that is still on my disk, trying to set the > > nextpos to (SELECT pos .... ) + 1. It worked for most engines except for Pg > > for which this is not an atomic operation. Then I laughed at Pg and forgot > > about the whole thing. I need to get back to it at some point.
This is a describing a different problem froma thread a year ago. It is what happens when new rows are inserted *concurrently* with pos unspecified (i.e. pushed to the end of the stack) > Ipricing=# create table test1 ( id serial primary key, value text ); > NOTICE: CREATE TABLE will create implicit sequence "test1_id_seq" for > serial column "test1.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "test1_pkey" for table "test1" > CREATE TABLE > pricing=# create table test2 ( id serial primary key, test1_id integer > not null references test1, position integer not null, unique > (test1_id, position) ); > NOTICE: CREATE TABLE will create implicit sequence "test2_id_seq" for > serial column "test2.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "test2_pkey" for table "test2" > NOTICE: CREATE TABLE / UNIQUE will create implicit index > "test2_test1_id_position_key" for table "test2" > CREATE TABLE > pricing=# insert into test1 (value) values ('first'); > INSERT 0 1 > pricing=# insert into test2 (test1_id, position) values (1, 0); > INSERT 0 1 > pricing=# insert into test2 (test1_id, position) values (1, 1); > INSERT 0 1 > pricing=# insert into test2 (test1_id, position) values (1, 2); > INSERT 0 1 > pricing=# insert into test2 (test1_id, position) values (1, 3); > INSERT 0 1 > pricing=# insert into test2 (test1_id, position) values (1, 5); > INSERT 0 1 > pricing=# insert into test2 (test1_id, position) values (1, 4); > INSERT 0 1 > pricing=# delete from test2 where test1_id = 1 and position = 2 ; > DELETE 1 > pricing=# update test2 set position = position - 1 where test1_id = 1 > and position between 3 and 5 ; > UPDATE 3 The reason Bill started this thread is that the above apparently did not work for him. I am now confused... _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk