From c9ab560b5b44c6878d804060704abc5238736feb Mon Sep 17 00:00:00 2001 From: Sandro Santilli Date: Sun, 25 Jun 2006 23:59:33 +0000 Subject: [PATCH] Added Long Transaction Support routines, dox and regress test. git-svn-id: http://svn.osgeo.org/postgis/trunk@2398 b70326c6-7e19-0410-871a-916f4a2858ee --- CHANGES | 15 +- doc/Makefile | 2 +- doc/long_xact.xml | 88 +++++++++ doc/postgis.xml | 6 +- lwgeom/Makefile | 6 +- lwgeom/long_xact.c | 174 +++++++++++++++++ lwgeom/long_xact.sql | 376 +++++++++++++++++++++++++++++++++++++ lwgeom/lwpostgis.sql.in | 4 + regress/Makefile | 7 +- regress/long_xact.sql | 61 ++++++ regress/long_xact_expected | 22 +++ 11 files changed, 745 insertions(+), 16 deletions(-) create mode 100644 doc/long_xact.xml create mode 100644 lwgeom/long_xact.c create mode 100644 lwgeom/long_xact.sql create mode 100644 regress/long_xact.sql create mode 100644 regress/long_xact_expected diff --git a/CHANGES b/CHANGES index a797cb0e2..5cbd1e24d 100644 --- a/CHANGES +++ b/CHANGES @@ -1,26 +1,27 @@ PostGIS 1.1.3CVS + - NEW Long Transactions support. - Can run the regress tests with 'make check' now. - New regress test support for loader/dumper. - BUGFIX in pgsql2shp successful return code. - BUGFIX in shp2pgsql handling of MultiLine WKT. - - BUGFIX in affine() failing to update bounding box + - BUGFIX in affine() failing to update bounding box. - WKT parser: forbidden construction of multigeometries with EMPTY elements (still supported for GEOMETRYCOLLECTION). - Added --with-proj-libdir and --with-geos-libdir configure - switches + switches. - JDBC: - Improved regression tests: MultiPoint and scientific ordinates - Fixed some minor bugs in jdbc code - Added proper accessor functions for all fields in preparation of making those fields private later - - Support for build Tru64 build - - Use Jade for generating documentation - - NEW DumpRings() function - - BUGFIX in distance(poly,poly) giving wrong results + - Support for build Tru64 build. + - Use Jade for generating documentation. + - NEW DumpRings() function. + - BUGFIX in distance(poly,poly) giving wrong results. - NEW AsHEXEWKB(geom, XDR|NDR) function. - Don't link pgsql2shp to more libs then required. - - Initial support for PostgreSQL 8.2 + - Initial support for PostgreSQL 8.2. PostGIS 1.1.2 2006/03/30 diff --git a/doc/Makefile b/doc/Makefile index 33d112671..bfd2f4edb 100644 --- a/doc/Makefile +++ b/doc/Makefile @@ -28,7 +28,7 @@ requirements_not_met: @echo " http://postgis.refractions.net/docs" @echo -postgis-out.xml: postgis.xml ../Version.config +postgis-out.xml: postgis.xml long_xact.xml ../Version.config cat $< | sed "s/@@LAST_RELEASE_VERSION@@/$(LAST_RELEASE_VERSION)/g" > $@ chunked-html: postgis-out.xml diff --git a/doc/long_xact.xml b/doc/long_xact.xml new file mode 100644 index 000000000..9ecc19464 --- /dev/null +++ b/doc/long_xact.xml @@ -0,0 +1,88 @@ + + Long Transactions support + + +This module and associated pl/pgsql functions have been implemented +to provide long locking support required by +Web Feature Service specification. + + + + + Users must use serializable transaction level otherwise locking mechanism would break. + + + + + + + EnableLongTransactions() + + + Enable long transaction support. This function creates the + required metadata tables, needs to be called once before + using the other functions in this section. Calling it twice + is harmless. + + + + + + DisableLongTransactions() + + + Disable long transaction support. This function removes the + long transaction support metadata tables, and drops all + triggers attached to lock-checked tables. + + + + + + CheckAuth([<schema>], <table>, <rowid_col>) + + + Check updates and deletes of rows in + given table for being authorized. + Identify rows using <rowid_col> column. + + + + + + LockRow([<schema>], <table>, <rowid>, <authid>, [<expires>]) + + + Set lock/authorization for specific row in table + <authid> is a text value, <expires> is a timestamp + defaulting to now()+1hour. + Returns 1 if lock has been assigned, 0 otherwise + (already locked by other auth) + + + + + + UnlockRows(<authid>) + + + Remove all locks held by specified authorization id. + Returns the number of locks released. + + + + + + AddAuth(<authid>) + + + Add an authorization token to be used in current + transaction. + + + + + + + + diff --git a/doc/postgis.xml b/doc/postgis.xml index ebf8d1f23..b93b53fd0 100644 --- a/doc/postgis.xml +++ b/doc/postgis.xml @@ -1,6 +1,8 @@ +"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd" [ + +]> PostGIS Manual @@ -5261,7 +5263,7 @@ FROM geometry_table; - + &long_xact; diff --git a/lwgeom/Makefile b/lwgeom/Makefile index d0a7a97dc..8e6b1240e 100644 --- a/lwgeom/Makefile +++ b/lwgeom/Makefile @@ -84,7 +84,7 @@ override CXXFLAGS += $(CSTAR_FLAGS) SA_OBJS=measures.o box2d.o ptarray.o lwgeom_api.o lwgeom.o lwpoint.o lwline.o lwpoly.o lwmpoint.o lwmline.o lwmpoly.o lwcollection.o $(GEOS_WRAPPER) $(JTS_WRAPPER) wktunparse.o lwgparse.o wktparse.tab.o lex.yy.o vsprintf.o -OBJS=$(SA_OBJS) liblwgeom.o lwgeom_pg.o lwgeom_debug.o lwgeom_spheroid.o lwgeom_ogc.o lwgeom_functions_analytic.o $(JTS_OBJ) lwgeom_inout.o lwgeom_estimate.o lwgeom_functions_basic.o lwgeom_gist.o lwgeom_btree.o lwgeom_transform.o stringBuffer.o lwgeom_box.o lwgeom_box3d.o lwgeom_box2dfloat4.o lwgeom_chip.o lwgeom_svg.o lwgeom_gml.o lwgeom_triggers.o lwgeom_dump.o lwgeom_functions_lrs.o +OBJS=$(SA_OBJS) liblwgeom.o lwgeom_pg.o lwgeom_debug.o lwgeom_spheroid.o lwgeom_ogc.o lwgeom_functions_analytic.o $(JTS_OBJ) lwgeom_inout.o lwgeom_estimate.o lwgeom_functions_basic.o lwgeom_gist.o lwgeom_btree.o lwgeom_transform.o stringBuffer.o lwgeom_box.o lwgeom_box3d.o lwgeom_box2dfloat4.o lwgeom_chip.o lwgeom_svg.o lwgeom_gml.o lwgeom_triggers.o lwgeom_dump.o lwgeom_functions_lrs.o long_xact.o #OTHERS=y.output lex.yy.c wktparse.tab.c wktparse.tab.h lwpostgis.sql OTHERS=y.output postgis_geos_version.h @@ -128,10 +128,10 @@ lwgeom_functions_basic.o: lwgeom_functions_basic.c profile.h ../lwpostgis_upgrade.sql: ../lwpostgis.sql ../utils/postgis_proc_upgrade.pl $(PERL) ../utils/postgis_proc_upgrade.pl ../lwpostgis.sql > ../lwpostgis_upgrade.sql -../lwpostgis.sql: lwpostgis.sql.in +../lwpostgis.sql: lwpostgis.sql.in long_xact.sql cpp -P -traditional-cpp -DUSE_VERSION=$(USE_VERSION) $< | sed -e 's:@MODULE_FILENAME@:$(MODULE_FILENAME):g;s:@POSTGIS_VERSION@:$(POSTGIS_VERSION):g;s:@POSTGIS_SCRIPTS_VERSION@:$(SCRIPTS_VERSION):g;s/@POSTGIS_BUILD_DATE@/$(POSTGIS_BUILD_DATE)/g' | grep -v '^#' > $@ -../regress/lwpostgis.sql: lwpostgis.sql.in +../regress/lwpostgis.sql: lwpostgis.sql.in long_xact.sql cpp -P -traditional-cpp -DUSE_VERSION=$(USE_VERSION) $< | sed -e 's#@MODULE_FILENAME@#$(REGRESS_MODULE_FILENAME)#g;s#@POSTGIS_VERSION@#$(POSTGIS_VERSION)#g;s#@POSTGIS_SCRIPTS_VERSION@#$(SCRIPTS_VERSION)#g;s/@POSTGIS_BUILD_DATE@/$(POSTGIS_BUILD_DATE)/g' | grep -v '^#' > $@ install: all install-lib-shared install-lwgeom-scripts diff --git a/lwgeom/long_xact.c b/lwgeom/long_xact.c new file mode 100644 index 000000000..4570154a8 --- /dev/null +++ b/lwgeom/long_xact.c @@ -0,0 +1,174 @@ +#include "postgres.h" +#include "executor/spi.h" /* this is what you need to work with SPI */ +#include "commands/trigger.h" /* ... and triggers */ +#include "utils/lsyscache.h" /* for get_namespace_name() */ + +/*#define PGIS_DEBUG 1*/ + +#define ABORT_ON_AUTH_FAILURE 1 + +Datum check_authorization(PG_FUNCTION_ARGS); + +/* + * This trigger will check for authorization before + * allowing UPDATE or DELETE of specific rows. + * Rows are identified by the provided column. + * Authorization info is extracted by the + * "authorization_table" + * + */ +PG_FUNCTION_INFO_V1(check_authorization); +Datum check_authorization(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + char *colname; + HeapTuple rettuple_ok; + HeapTuple rettuple_fail; + TupleDesc tupdesc; + int SPIcode; + char query[1024]; + const char *pk_id = NULL; + SPITupleTable *tuptable; + HeapTuple tuple; + char *lockcode; + char *authtable = "authorization_table"; + const char *op; +#define ERRMSGLEN 256 + char errmsg[ERRMSGLEN]; + + + /* Make sure trigdata is pointing at what I expect */ + if ( ! CALLED_AS_TRIGGER(fcinfo) ) + { + elog(ERROR,"check_authorization: not fired by trigger manager"); + } + + if ( ! TRIGGER_FIRED_BEFORE(trigdata->tg_event) ) + { + elog(ERROR,"check_authorization: not fired *before* event"); + } + + if ( TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event) ) + { + rettuple_ok = trigdata->tg_newtuple; + rettuple_fail = NULL; + op = "UPDATE"; + } + else if ( TRIGGER_FIRED_BY_DELETE(trigdata->tg_event) ) + { + rettuple_ok = trigdata->tg_trigtuple; + rettuple_fail = NULL; + op = "DELETE"; + } + else + { + elog(ERROR,"check_authorization: not fired by update or delete"); + PG_RETURN_NULL(); + } + + + tupdesc = trigdata->tg_relation->rd_att; + + /* Connect to SPI manager */ + SPIcode = SPI_connect(); + + if (SPIcode != SPI_OK_CONNECT) + { + elog(ERROR,"check_authorization: could not connect to SPI"); + PG_RETURN_NULL() ; + } + + colname = trigdata->tg_trigger->tgargs[0]; + pk_id = SPI_getvalue(trigdata->tg_trigtuple, tupdesc, + SPI_fnumber(tupdesc, colname)); + +#if PGIS_DEBUG + elog(NOTICE,"check_authorization called"); +#endif + + sprintf(query,"SELECT authid FROM \"%s\" WHERE expires >= now() AND toid = '%d' AND rid = '%s'", authtable, trigdata->tg_relation->rd_id, pk_id); + +#if PGIS_DEBUG > 1 + elog(NOTICE,"about to execute :%s", query); +#endif + + SPIcode = SPI_exec(query,0); + if (SPIcode !=SPI_OK_SELECT ) + elog(ERROR,"couldnt execute to test for lock :%s",query); + + if (!SPI_processed ) + { +#if PGIS_DEBUG + elog(NOTICE,"there is NO lock on row '%s'", pk_id); +#endif + SPI_finish(); + return PointerGetDatum(rettuple_ok); + } + + /* there is a lock - check to see if I have rights to it! */ + + tuptable = SPI_tuptable; + tupdesc = tuptable->tupdesc; + tuple = tuptable->vals[0]; + lockcode = SPI_getvalue(tuple, tupdesc, 1); + +#if PGIS_DEBUG + elog(NOTICE, "there is a lock on row '%s' (auth: '%s').", pk_id, lockcode); +#endif + + /* + * check to see if temp_lock_have_table table exists + * (it might not exist if they own no locks) + */ + sprintf(query,"SELECT * FROM pg_class WHERE relname = 'temp_lock_have_table'"); + SPIcode = SPI_exec(query,0); + if (SPIcode != SPI_OK_SELECT ) + elog(ERROR,"couldnt execute to test for lockkey temp table :%s",query); + if (SPI_processed==0) + { + goto fail; + } + + sprintf(query, "SELECT * FROM temp_lock_have_table WHERE xideq( transid, getTransactionID() ) AND lockcode ='%s'", lockcode); + +#if PGIS_DEBUG + elog(NOTICE,"about to execute :%s", query); +#endif + + SPIcode = SPI_exec(query,0); + if (SPIcode != SPI_OK_SELECT ) + elog(ERROR, "couldnt execute to test for lock aquire: %s", query); + + if (SPI_processed >0) + { +#if PGIS_DEBUG + elog(NOTICE,"I own the lock - I can modify the row"); +#endif + SPI_finish(); + return PointerGetDatum(rettuple_ok); + } + + fail: + + snprintf(errmsg, ERRMSGLEN, "%s where \"%s\" = '%s' requires authorization '%s'", + op, colname, pk_id, lockcode); + errmsg[ERRMSGLEN-1] = '\0'; + +#ifdef ABORT_ON_AUTH_FAILURE + elog(ERROR, "%s", errmsg); +#else + elog(NOTICE, "%s", errmsg); +#endif + + SPI_finish(); + return PointerGetDatum(rettuple_fail); + + +} + +PG_FUNCTION_INFO_V1(getTransactionID); +Datum getTransactionID(PG_FUNCTION_ARGS) +{ + TransactionId xid = GetCurrentTransactionId(); + PG_RETURN_DATUM( TransactionIdGetDatum(xid) ); +} diff --git a/lwgeom/long_xact.sql b/lwgeom/long_xact.sql new file mode 100644 index 000000000..ee840e6fd --- /dev/null +++ b/lwgeom/long_xact.sql @@ -0,0 +1,376 @@ +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +-- +-- $Id$ +-- +-- PostGIS - Spatial Types for PostgreSQL +-- http://postgis.refractions.net +-- Copyright 2001-2003 Refractions Research Inc. +-- +-- This is free software; you can redistribute and/or modify it under +-- the terms of the GNU General Public Licence. See the COPYING file. +-- +-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +#define CREATEFUNCTION CREATE OR REPLACE FUNCTION + +#if USE_VERSION > 72 +# define _IMMUTABLE_STRICT IMMUTABLE STRICT +# define _IMMUTABLE IMMUTABLE +# define _STABLE_STRICT STABLE STRICT +# define _STABLE STABLE +# define _VOLATILE_STRICT VOLATILE STRICT +# define _VOLATILE VOLATILE +# define _STRICT STRICT +#else +# define _IMMUTABLE_STRICT with(iscachable,isstrict) +# define _IMMUTABLE with(iscachable) +# define _STABLE_STRICT with(isstrict) +# define _STABLE +# define _VOLATILE_STRICT with(isstrict) +# define _VOLATILE +# define _STRICT with(isstrict) +#endif + +#if USE_VERSION >= 73 +# define HAS_SCHEMAS 1 +#endif + + +----------------------------------------------------------------------- +-- LONG TERM LOCKING +----------------------------------------------------------------------- + +-- UnlockRows(authid) +-- removes all locks held by the given auth +-- returns the number of locks released +CREATEFUNCTION UnlockRows(text) + RETURNS int + AS ' +DECLARE + ret int; +BEGIN + + IF NOT LongTransactionsEnabled() THEN + RAISE EXCEPTION ''Long transaction support disabled, use EnableLongTransaction() to enable.''; + END IF; + + EXECUTE ''DELETE FROM authorization_table where authid = '' || + quote_literal($1); + + GET DIAGNOSTICS ret = ROW_COUNT; + + RETURN ret; +END; +' +LANGUAGE 'plpgsql' _VOLATILE_STRICT; + +-- LockRow([schema], table, rowid, auth, [expires]) +-- Returns 1 if successfully obtained the lock, 0 otherwise +CREATEFUNCTION LockRow(text, text, text, text, timestamp) + RETURNS int + AS ' +DECLARE + myschema alias for $1; + mytable alias for $2; + myrid alias for $3; + authid alias for $4; + expires alias for $5; + ret int; + mytoid oid; + myrec RECORD; + +BEGIN + + IF NOT LongTransactionsEnabled() THEN + RAISE EXCEPTION ''Long transaction support disabled, use EnableLongTransaction() to enable.''; + END IF; + + EXECUTE ''DELETE FROM authorization_table WHERE expires < now()''; + +#ifdef HAS_SCHEMAS + SELECT c.oid INTO mytoid FROM pg_class c, pg_namespace n + WHERE c.relname = mytable + AND c.relnamespace = n.oid + AND n.nspname = myschema; +#else + SELECT c.oid INTO mytoid FROM pg_class c + WHERE c.relname = mytable; +#endif + + -- RAISE NOTICE ''toid: %'', mytoid; + + FOR myrec IN SELECT * FROM authorization_table WHERE + toid = mytoid AND rid = myrid + LOOP + IF myrec.authid != authid THEN + RETURN 0; + ELSE + RETURN 1; + END IF; + END LOOP; + + EXECUTE ''INSERT INTO authorization_table VALUES (''|| + quote_literal(mytoid)||'',''||quote_literal(myrid)|| + '',''||quote_literal(expires)|| + '',''||quote_literal(authid) ||'')''; + + GET DIAGNOSTICS ret = ROW_COUNT; + + RETURN ret; +END;' +LANGUAGE 'plpgsql' _VOLATILE_STRICT; + +-- LockRow(schema, table, rid, authid); +CREATEFUNCTION LockRow(text, text, text, text) + RETURNS int + AS +'SELECT LockRow($1, $2, $3, $4, now()::timestamp+''1:00'');' + LANGUAGE 'sql' _VOLATILE_STRICT; + +-- LockRow(table, rid, authid); +CREATEFUNCTION LockRow(text, text, text) + RETURNS int + AS +#ifdef HAS_SCHEMAS +'SELECT LockRow(current_schema(), $1, $2, $3, now()::timestamp+''1:00'');' +#else +'SELECT LockRow('''', $1, $2, $3, now()::timestamp+''1:00'');' +#endif + LANGUAGE 'sql' _VOLATILE_STRICT; + +-- LockRow(schema, table, rid, expires); +CREATEFUNCTION LockRow(text, text, text, timestamp) + RETURNS int + AS +#ifdef HAS_SCHEMAS +'SELECT LockRow(current_schema(), $1, $2, $3, $4);' +#else +'SELECT LockRow('''', $1, $2, $3, $4);' +#endif + LANGUAGE 'sql' _VOLATILE_STRICT; + + +CREATEFUNCTION AddAuth(text) + 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 text) + okay := ''f''; + FOR myrec IN SELECT * FROM pg_class WHERE relname = ''temp_lock_have_table'' LOOP + okay := ''t''; + END LOOP; + IF (okay <> ''t'') THEN + CREATE TEMP TABLE temp_lock_have_table (transid xid, lockcode text); + -- this will only work from pgsql7.4 up + -- ON COMMIT DELETE ROWS; + END IF; + + -- INSERT INTO mylock VALUES ( $1) +-- EXECUTE ''INSERT INTO temp_lock_have_table VALUES ( ''|| +-- quote_literal(getTransactionID()) || '','' || +-- quote_literal(lockid) ||'')''; + + INSERT INTO temp_lock_have_table VALUES (getTransactionID(), lockid); + + RETURN true::boolean; +END; +' +LANGUAGE PLPGSQL; + + +-- CheckAuth( , , ) +-- +-- Returns 0 +-- +CREATEFUNCTION CheckAuth(text, text, text) + RETURNS INT + AS ' +DECLARE +#ifdef HAS_SCHEMAS + schema text; +#endif +BEGIN + IF NOT LongTransactionsEnabled() THEN + RAISE EXCEPTION ''Long transaction support disabled, use EnableLongTransaction() to enable.''; + END IF; + +#ifdef HAS_SCHEMAS + if ( $1 != '''' ) THEN + schema = $1; + ELSE + SELECT current_schema() into schema; + END IF; +#endif + + -- TODO: check for an already existing trigger ? + + EXECUTE ''CREATE TRIGGER check_auth BEFORE UPDATE OR DELETE ON '' +#ifdef HAS_SCHEMAS + || quote_ident(schema) || ''.'' || quote_ident($2) +#else + || quote_ident($2) +#endif + ||'' FOR EACH ROW EXECUTE PROCEDURE CheckAuthTrigger('' + || quote_literal($3) || '')''; + + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + +-- CheckAuth(
, ) +CREATEFUNCTION CheckAuth(text, text) + RETURNS INT + AS + 'SELECT CheckAuth('''', $1, $2)' + LANGUAGE 'SQL'; + +CREATEFUNCTION CheckAuthTrigger() + RETURNS trigger AS + '@MODULE_FILENAME@', 'check_authorization' + LANGUAGE C; + +CREATEFUNCTION GetTransactionID() + RETURNS xid AS + '@MODULE_FILENAME@', 'getTransactionID' + LANGUAGE C; + + +-- +-- Enable Long transactions support +-- +-- Creates the authorization_table if not already existing +-- +CREATEFUNCTION EnableLongTransactions() + RETURNS TEXT + AS ' +DECLARE + query text; + exists bool; + rec RECORD; + +BEGIN + + exists = ''f''; + FOR rec IN SELECT * FROM pg_class WHERE relname = ''authorization_table'' + LOOP + exists = ''t''; + END LOOP; + + IF NOT exists + THEN + query = ''CREATE TABLE authorization_table ( + toid oid, -- table oid + rid text, -- row id + expires timestamp, + authid text + )''; + EXECUTE query; + END IF; + + exists = ''f''; + FOR rec IN SELECT * FROM pg_class WHERE relname = ''authorized_tables'' + LOOP + exists = ''t''; + END LOOP; + + IF NOT exists THEN + query = ''CREATE VIEW authorized_tables AS '' || + ''SELECT '' || +#ifdef HAS_SCHEMAS + ''n.nspname as schema, '' || +#endif + ''c.relname as table, trim('' || + quote_literal(''\\\\000'') || + '' from t.tgargs) as id_column '' || + ''FROM pg_trigger t, pg_class c, pg_proc p '' || +#ifdef HAS_SCHEMAS + '', pg_namespace n '' || +#endif + ''WHERE p.proname = '' || quote_literal(''checkauthtrigger'') || +#ifdef HAS_SCHEMAS + '' AND c.relnamespace = n.oid'' || +#endif + '' AND t.tgfoid = p.oid and t.tgrelid = c.oid''; + EXECUTE query; + END IF; + + RETURN ''Long transactions support enabled''; +END; +' +LANGUAGE 'plpgsql'; + +-- +-- Check if Long transactions support is enabled +-- +CREATEFUNCTION LongTransactionsEnabled() + RETURNS bool +AS ' +DECLARE + rec RECORD; +BEGIN + FOR rec IN SELECT oid FROM pg_class WHERE relname = ''authorized_tables'' + LOOP + return ''t''; + END LOOP; + return ''f''; +END; +' +LANGUAGE 'plpgsql'; + +-- +-- Disable Long transactions support +-- +-- (1) Drop any long_xact trigger +-- (2) Drop the authorization_table +-- (3) KEEP the authorized_tables view +-- +CREATEFUNCTION DisableLongTransactions() + RETURNS TEXT + AS ' +DECLARE + query text; + exists bool; + rec RECORD; + +BEGIN + + -- + -- Drop all triggers applied by CheckAuth() + -- + FOR rec IN + SELECT c.relname, t.tgname, t.tgargs FROM pg_trigger t, pg_class c, pg_proc p + WHERE p.proname = ''checkauthtrigger'' and t.tgfoid = p.oid and t.tgrelid = c.oid + LOOP + EXECUTE ''DROP TRIGGER '' || quote_ident(rec.tgname) || + '' ON '' || quote_ident(rec.relname); + END LOOP; + + -- + -- Drop the authorization_table table + -- + FOR rec IN SELECT * FROM pg_class WHERE relname = ''authorization_table'' LOOP + DROP TABLE authorization_table; + END LOOP; + + -- + -- Drop the authorized_tables view + -- + FOR rec IN SELECT * FROM pg_class WHERE relname = ''authorized_tables'' LOOP + DROP VIEW authorized_tables; + END LOOP; + + RETURN ''Long transactions support disabled''; +END; +' +LANGUAGE 'plpgsql'; + +--------------------------------------------------------------- +-- END +--------------------------------------------------------------- + diff --git a/lwgeom/lwpostgis.sql.in b/lwgeom/lwpostgis.sql.in index 8a5faa278..270857c68 100644 --- a/lwgeom/lwpostgis.sql.in +++ b/lwgeom/lwpostgis.sql.in @@ -42,6 +42,9 @@ # define _SECURITY_DEFINER #endif +#if USE_VERSION >= 73 +# define HAS_SCHEMAS 1 +#endif BEGIN; @@ -3686,6 +3689,7 @@ END; ' LANGUAGE 'plpgsql' _IMMUTABLE_STRICT; +#include "long_xact.sql" --------------------------------------------------------------- -- END diff --git a/regress/Makefile b/regress/Makefile index c62777358..62d15f360 100644 --- a/regress/Makefile +++ b/regress/Makefile @@ -25,7 +25,8 @@ TESTS = \ snaptogrid \ affine \ wkt \ - measures + measures \ + long_xact ifeq ($(USE_GEOS),1) TESTS += regress_ogc regress_bdpoly @@ -37,8 +38,8 @@ endif all: test -test: lwpostgis.sql ../lwgeom/liblwgeom.so - @USE_VERSION="$(USE_VERSION)" ./run_test long_xact # $(TESTS) +test check: lwpostgis.sql ../lwgeom/liblwgeom.so + @USE_VERSION="$(USE_VERSION)" ./run_test $(TESTS) lwpostgis.sql: ../lwgeom/lwpostgis.sql.in $(MAKE) -C ../lwgeom ../regress/lwpostgis.sql diff --git a/regress/long_xact.sql b/regress/long_xact.sql new file mode 100644 index 000000000..17edd4e18 --- /dev/null +++ b/regress/long_xact.sql @@ -0,0 +1,61 @@ + +SELECT EnableLongTransactions(); + +CREATE TABLE test_locks (id numeric, state varchar); +INSERT INTO test_locks(id) VALUES (1); +INSERT INTO test_locks(id) VALUES (2); +INSERT INTO test_locks(id) VALUES (3); + +-- Enable locks checking on the table +SELECT CheckAuth('test_locks', 'id'); + +-- this has no lock +UPDATE test_locks SET state = 'nolocks'; + +-- place the lock +SELECT LockRow('test_locks', '1', 'auth1', now()::timestamp+'00:01'); +SELECT LockRow('test_locks', '2', 'auth2', now()::timestamp+'00:01'); + +-- this should fail due to missing auth +UPDATE test_locks SET state = 'unauthorized' where id = 1; + +BEGIN; + + -- Add authorization for row 1 + SELECT AddAuth('auth1'); + + -- we're authorized for row 1 + UPDATE test_locks SET state = 'authorized' where id = 1; +END; + +-- Out of transaction we're no more authorized for row 1 +UPDATE test_locks SET state = 'unauthorized' where id = 1; + +BEGIN; + + -- Add authorization for row 2 + SELECT AddAuth('auth2'); + + -- we're authorized for row 2 + UPDATE test_locks SET state = 'authorized' where id = 2; +END; + + +BEGIN; + + -- Add authorization for row 2 + SELECT AddAuth('auth2'); + + -- we're *not* authorized for row 1 + UPDATE test_locks SET state = 'unauthorized' where id = 1; +END; + +UPDATE test_locks SET state = 'unauthorized' where id = 2; +UPDATE test_locks SET state = 'unauthorized' where id = 1; + +SELECT * from test_locks; + +DROP TABLE test_locks; + +SELECT DisableLongTransactions(); + diff --git a/regress/long_xact_expected b/regress/long_xact_expected new file mode 100644 index 000000000..fa6952cc9 --- /dev/null +++ b/regress/long_xact_expected @@ -0,0 +1,22 @@ +Long transactions support enabled +0 +1 +1 +ERROR: UPDATE where "id" = '1' requires authorization 'auth1' +BEGIN +t +COMMIT +ERROR: UPDATE where "id" = '1' requires authorization 'auth1' +BEGIN +t +COMMIT +BEGIN +t +ERROR: UPDATE where "id" = '1' requires authorization 'auth1' +COMMIT +ERROR: UPDATE where "id" = '2' requires authorization 'auth2' +ERROR: UPDATE where "id" = '1' requires authorization 'auth1' +3|nolocks +1|authorized +2|authorized +Long transactions support disabled -- 2.50.0