Postgis Locking

Long term locking for Postgis is available on the database side of things. I implemented locking for the postgis Data Source, but it got clobbered at somepoint during the move to DataStore.

This work needs to be done on the geotools side of the fence in the Postgis datastore. I can find the list of SQL functions that need to be called for you:

How to use a lock

Here is the second description of locks, the names have changed to be more feature oriented (since locks are by feature id this is not such a bad thing).

Note this lockcheck function is now part of postgis ...

 CREATE FUNCTION lockcheck(TEXT,TEXT) RETURNS TRIGGER AS
 '/data1/Refractions/Projects/PostGIS/work_dave/postgis/libpostgis.so.0.8' LANGUAGE C;

 CREATE FUNCTION getTransactionID() RETURNS XID AS
 '/data1/Refractions/Projects/PostGIS/work_dave/postgis/libpostgis.so.0.8','getTransactionID' LANGUAGE C;


 CREATE OR REPLACE FUNCTION have_lock_for(INT) RETURNS BOOLEAN AS
 '
 DECLARE
     lockid alias for $1;
     okay boolean;
     myrec record;
 BEGIN
 -- check to see if table exists
 --  if not, CREATE TEMP TABLE mylock (transid xid, lockcode int)

     okay := ''f'';
     FOR myrec IN SELECT * FROM pg_class WHERE relname = ''temp_lock_have_table'' LOOP
         okay := ''t'';
     END LOOP;
     IF (okay <> ''t'') THEN
         EXECUTE ''CREATE TEMP TABLE temp_lock_have_table (transid xid, lockcode int)'';
     END IF;

--  delete everything in TEMP table with xid != current xid
     EXECUTE ''DELETE FROM temp_lock_have_table WHERE not(xideq(transid , getTransactionID()))'';
--  INSERT INTO mylock VALUES ( getTransactionID(), $1)
     EXECUTE ''INSERT INTO temp_lock_have_table VALUES ( getTransactionID(), ''||lockid ||'')'';
     RETURN true::boolean;
 END;
 '
  LANGUAGE PLPGSQL with (isstrict);

create the lock table

create table geo_table_loc (id int, lock_key int, expires date);

create your database table

CREATE TABLE geo_table (id int primary key,the_geom geometry, name text);

attach the row-authorization lock on it

 CREATE TRIGGER locktrig_geo_table BEFORE UPDATE OR DELETE
    ON geo_table FOR EACH ROW
    EXECUTE PROCEDURE lockcheck('id','geo_table_loc');

put data in it

 insert into geo_table values (1, 'POINT(0 0)', 'dave');
 insert into geo_table values (2, 'POINT(1 0)', 'jody');
 insert into geo_table values (3, 'POINT(2 0)', 'brent');
 insert into geo_table values (4, 'POINT(3 0)', 'justin');

grab a lock on the geo_table

 insert into geo_table_loc values (2, 666, '2100-1-1');  --loc row 2 with lock id 666

sample transaction

 BEGIN;

     select have_lock_for(667);
     select have_lock_for(668);


     update geo_table set name = 'nexus' where id =1;  -- no lock here, so its good
     update geo_table set name = 'tenderflake' where id =2; -- error or ignore since we do not have lock 666

 COMMIT;

Historical Attempt at Locks

For reference here is the first attempt at locks:

SQL:

CREATE FUNCTION lockcheck(TEXT,TEXT) RETURNS TRIGGER AS
'/data1/Refractions/Projects/PostGIS/work_dave/postgis/libpostgis.so.0.8' LANGUAGE C;
CREATE TABLE geo_table (id int primary key,the_geom geometry, name text);

CREATE TRIGGER locktrig_geo_table BEFORE UPDATE OR DELETE
  ON geo_table FOR EACH ROW
  EXECUTE PROCEDURE lockcheck('id','geo_table_loc');

insert into geo_table values (1, 'POINT(0 0)', 'dave');
insert into geo_table values (2, 'POINT(1 0)', 'jody');
insert into geo_table values (3, 'POINT(2 0)', 'brent');
insert into geo_table values (4, 'POINT(3 0)', 'justin');

create table geo_table_loc (id int, lock_key int, expires date);

insert into geo_table_loc values (2, 666, '2100-1-1');  --loc row 2 with lock id 666

BEGIN;

   select have_lock_for(666);
   select have_lock_for(667);
   select have_lock_for(668);


   update geo_table set name = 'nexus' where id =1;  -- no lock here, so its good
   update geo_table set name = 'tenderflake' where id =2; -- error or ignore

COMMIT;