-- validate lock tuple trigger
-- set defaults for the testing environment
\set ON_ERROR_STOP 1
SET client_min_messages='notice';
-- load extension for PostgreSQL 9.1 and later
CREATE EXTENSION tuplock;
-- test in a distinct schema...
CREATE SCHEMA test_tuplock;
SET search_path TO test_tuplock, public;
CREATE TABLE test(
  tid SERIAL PRIMARY KEY,
  data TEXT NOT NULL,
  -- locking attribute
  lock BOOLEAN NOT NULL DEFAULT FALSE
);
-- create the locking trigger
CREATE TRIGGER test_lock
  BEFORE UPDATE OR DELETE ON test
  FOR EACH ROW EXECUTE PROCEDURE tuplock(lock);
-- must also forbid truncate
CREATE TRIGGER test_lock2
BEFORE TRUNCATE ON test
EXECUTE PROCEDURE tuplock();
-- table for bad examples
CREATE TABLE test2(
  tid SERIAL PRIMARY KEY,
  data TEXT NOT NULL,
  lock BOOLEAN NOT NULL DEFAULT FALSE);
-- current status: 0
SELECT COUNT(*) FROM test;
 count 
-------
     0
(1 row)

-- first insert new values
INSERT INTO test(data) VALUES('hello');
INSERT INTO test(data) VALUES('world');
INSERT INTO test(data) VALUES('!');
INSERT INTO test(data) VALUES('foo');
INSERT INTO test(data) VALUES('bla');
-- show all
SELECT * FROM test ORDER BY tid;
 tid | data  | lock 
-----+-------+------
   1 | hello | f
   2 | world | f
   3 | !     | f
   4 | foo   | f
   5 | bla   | f
(5 rows)

-- update is fine
UPDATE test
  SET data='HELLO'
  WHERE data='hello';
-- delete is fine
DELETE FROM test
 WHERE data = '!';
-- then lock all but foo & bla entries
UPDATE test
  SET lock = TRUE
  WHERE lock = FALSE AND length(data) <> 3;
-- errors are fine, now:
\set ON_ERROR_STOP 0
-- now updates are not ok
UPDATE test
  SET data='WORLD'
  WHERE data='world'; -- fails
ERROR:  trigger "test_lock" on "test": item locked by attribute "lock"
UPDATE test
  SET lock = FALSE
  WHERE lock; -- fails
ERROR:  trigger "test_lock" on "test": item locked by attribute "lock"
-- now deletes are not ok
DELETE FROM test
  WHERE data = 'HELLO'; -- fails
ERROR:  trigger "test_lock" on "test": item locked by attribute "lock"
-- as well as truncates
TRUNCATE test;
ERROR:  trigger "test_lock2" on "test": items may be locked, cannot TRUNCATE
-- test2
INSERT INTO test2(data)
  VALUES ('hello world');
-- ERROR: insert is not allowed
CREATE TRIGGER test2_lock_insert
  BEFORE INSERT ON test2
  FOR EACH ROW EXECUTE PROCEDURE tuplock(lock);
INSERT INTO test2(data)
  VALUES ('hello world 2');
ERROR:  trigger "test2_lock_insert" on "test2": must be called for UPDATE or DELETE
DROP TRIGGER test2_lock_insert ON test2;
-- ERROR: for each statement
CREATE TRIGGER test2_lock_stmt
  BEFORE UPDATE OR DELETE ON test2
  FOR EACH STATEMENT EXECUTE PROCEDURE tuplock();
UPDATE test2
  SET lock = TRUE
  WHERE data = 'hello world';
ERROR:  trigger "test2_lock_stmt" on "test2": must be called FOR EACH ROW
DROP TRIGGER test2_lock_stmt ON test2;
-- ERROR: too many arguments
CREATE TRIGGER test2_lock_nargs
  BEFORE UPDATE OR DELETE ON test2
  FOR EACH ROW EXECUTE PROCEDURE tuplock(lock, data);
UPDATE test2
  SET lock = TRUE
  WHERE data = 'hello world';
ERROR:  trigger "test2_lock_nargs" on "test2": expecting attribute name as only argument
DROP TRIGGER test2_lock_nargs ON test2;
-- ERROR: no arg
CREATE TRIGGER test2_lock_zero
  BEFORE UPDATE OR DELETE ON test2
  FOR EACH ROW EXECUTE PROCEDURE tuplock();
UPDATE test2
  SET lock = TRUE
  WHERE data = 'hello world';
ERROR:  trigger "test2_lock_zero" on "test2": expecting attribute name as only argument
DROP TRIGGER test2_lock_zero ON test2;
-- ERROR: wrong type
CREATE TRIGGER test2_lock_text
  BEFORE UPDATE OR DELETE ON test2
  FOR EACH ROW EXECUTE PROCEDURE tuplock(data);
UPDATE test2
  SET lock = TRUE
  WHERE data = 'hello world';
ERROR:  trigger "test2_lock_text" on "test2": attribute "data" is not BOOL
DROP TRIGGER test2_lock_text ON test2;
-- ERROR: no such attribute
CREATE TRIGGER test2_lock_foo
  BEFORE UPDATE OR DELETE ON test2
  FOR EACH ROW EXECUTE PROCEDURE tuplock(foo);
UPDATE test2
  SET lock = TRUE
  WHERE data = 'hello world';
ERROR:  trigger "test2_lock_foo" on "test2": attribute "foo" not found
DROP TRIGGER test2_lock_foo ON test2;
-- back to normal
\set ON_ERROR_STOP 1
-- this update is ok
UPDATE test
  SET data = 'FOO'
  WHERE data = 'foo';
-- this delete is ok
DELETE FROM test
  WHERE data='bla';
-- show all
SELECT * FROM test ORDER BY tid;
 tid | data  | lock 
-----+-------+------
   1 | HELLO | t
   2 | world | t
   4 | FOO   | f
(3 rows)

-- cleanup
DROP SCHEMA test_tuplock CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table test
drop cascades to table test2
DROP EXTENSION tuplock;