Add tests for LOCK TABLE
Started by Robins Tharakanover 12 years ago3 messages
Hi,
Please find attached a patch to take code-coverage of LOCK TABLE (
src/backend/commands/lockcmds.c) from 57% to 84%.
Any and all feedback is welcome.
--
Robins Tharakan
Attachments:
regress_lock.patchapplication/octet-stream; name=regress_lock.patchDownload
diff --git a/src/test/regress/expected/lock.out b/src/test/regress/expected/lock.out
new file mode 100644
index 0000000..d84918f
--- /dev/null
+++ b/src/test/regress/expected/lock.out
@@ -0,0 +1,108 @@
+--
+-- CASE
+-- Test the case statement
+--
+-- Setup
+CREATE SCHEMA lock_schema1;
+SET search_path = lock_schema1;
+CREATE TABLE lock_tbl1 (a BIGINT);
+-- Should work. Using Valid options.
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE;
+LOCK TABLE lock_tbl1 IN ROW SHARE MODE;
+LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN SHARE MODE;
+LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE;
+ROLLBACK;
+-- Should work. Using NOWAIT along with valid options.
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ROW SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE NOWAIT;
+ROLLBACK;
+-- Should fail. Can only LOCK a TABLE
+CREATE VIEW lock_view1 AS SELECT 1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_view1 IN EXCLUSIVE MODE;
+ERROR: "lock_view1" is not a table
+ROLLBACK;
+DROP VIEW lock_view1;
+-- Should fail. Can LOCK only if USER has permission
+CREATE ROLE lock_usr1;
+CREATE TABLE lock_tbl2(a BIGINT);
+REVOKE ALL ON TABLE lock_tbl2 FROM lock_usr1;
+SET ROLE lock_usr1;
+SET search_path = lock_schema1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl2 IN EXCLUSIVE MODE;
+ERROR: relation "lock_tbl2" does not exist
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl2;
+DROP ROLE lock_usr1;
+-- Should work. Ensure that LOCK works for inherited tables;
+CREATE ROLE lock_rol3;
+CREATE TABLE lock_tbl3 (a BIGINT);
+CREATE TABLE lock_tbl4 (b BIGINT) INHERITS (lock_tbl3);
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl4 * IN access EXCLUSIVE MODE;
+SET ROLE lock_rol3;
+SET search_path = lock_schema1;
+LOCK TABLE lock_tbl3 NOWAIT;
+ERROR: relation "lock_tbl3" does not exist
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl4;
+DROP TABLE lock_tbl3;
+DROP ROLE lock_rol3;
+-- Should fail. Ensure that LOCK is allowed when ROLE has appropriate rights
+-- either directly, or via INHERITANCE
+CREATE ROLE lock_rol5;
+CREATE TABLE lock_tbl5 (a BIGINT);
+CREATE TABLE lock_tbl6 (b BIGINT) INHERITS (lock_tbl5);
+GRANT USAGE ON SCHEMA lock_schema1 TO lock_rol5;
+GRANT ALL ON TABLE lock_tbl5 TO lock_rol5;
+REVOKE ALL ON TABLE lock_tbl6 FROM lock_rol5;
+SET ROLE lock_rol5;
+SET search_path=lock_schema1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl5 * IN access EXCLUSIVE MODE;
+ERROR: permission denied for relation lock_tbl6
+ROLLBACK;
+BEGIN TRANSACTION;
+LOCK TABLE ONLY lock_tbl5 IN access EXCLUSIVE MODE;
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl6;
+DROP TABLE lock_tbl5;
+REVOKE ALL ON SCHEMA lock_schema1 FROM lock_rol5;
+DROP ROLE lock_rol5;
+-- Should fail. Ensure that LOCK is allowed when ROLE has appropriate rights
+-- either directly, or via INHERITANCE
+CREATE TABLE lock_tbl7 (a BIGINT);
+CREATE TABLE lock_tbl8 (b BIGINT) INHERITS (lock_tbl7);
+CREATE TABLE lock_tbl9 (c BIGINT) INHERITS (lock_tbl8);
+CREATE TABLE lock_tbl10(d BIGINT) INHERITS (lock_tbl9);
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl7 * IN access EXCLUSIVE MODE;
+ROLLBACK;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl7 * IN access EXCLUSIVE MODE NOWAIT;
+ROLLBACK;
+DROP TABLE lock_tbl10;
+DROP TABLE lock_tbl9;
+DROP TABLE lock_tbl8;
+DROP TABLE lock_tbl7;
+--
+-- Clean up
+--
+DROP SCHEMA lock_schema1 CASCADE;
+NOTICE: drop cascades to table lock_tbl1
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 2af28b1..1610cbe 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -88,7 +88,7 @@ test: privileges security_label collate matview
# ----------
# Another group of parallel tests
# ----------
-test: alter_generic misc psql
+test: alter_generic misc psql lock
# rules cannot run concurrently with any test that creates a view
test: rules
diff --git a/src/test/regress/sql/lock.sql b/src/test/regress/sql/lock.sql
new file mode 100644
index 0000000..d39dbdd
--- /dev/null
+++ b/src/test/regress/sql/lock.sql
@@ -0,0 +1,118 @@
+--
+-- CASE
+-- Test the case statement
+--
+
+-- Setup
+CREATE SCHEMA lock_schema1;
+SET search_path = lock_schema1;
+CREATE TABLE lock_tbl1 (a BIGINT);
+
+-- Should work. Using Valid options.
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE;
+LOCK TABLE lock_tbl1 IN ROW SHARE MODE;
+LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN SHARE MODE;
+LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE;
+ROLLBACK;
+
+-- Should work. Using NOWAIT along with valid options.
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ROW SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE NOWAIT;
+ROLLBACK;
+
+
+
+-- Should fail. Can only LOCK a TABLE
+CREATE VIEW lock_view1 AS SELECT 1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_view1 IN EXCLUSIVE MODE;
+ROLLBACK;
+DROP VIEW lock_view1;
+
+-- Should fail. Can LOCK only if USER has permission
+CREATE ROLE lock_usr1;
+CREATE TABLE lock_tbl2(a BIGINT);
+REVOKE ALL ON TABLE lock_tbl2 FROM lock_usr1;
+SET ROLE lock_usr1;
+SET search_path = lock_schema1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl2 IN EXCLUSIVE MODE;
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl2;
+DROP ROLE lock_usr1;
+
+-- Should work. Ensure that LOCK works for inherited tables;
+CREATE ROLE lock_rol3;
+CREATE TABLE lock_tbl3 (a BIGINT);
+CREATE TABLE lock_tbl4 (b BIGINT) INHERITS (lock_tbl3);
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl4 * IN access EXCLUSIVE MODE;
+SET ROLE lock_rol3;
+SET search_path = lock_schema1;
+LOCK TABLE lock_tbl3 NOWAIT;
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl4;
+DROP TABLE lock_tbl3;
+DROP ROLE lock_rol3;
+
+
+-- Should fail. Ensure that LOCK is allowed when ROLE has appropriate rights
+-- either directly, or via INHERITANCE
+CREATE ROLE lock_rol5;
+CREATE TABLE lock_tbl5 (a BIGINT);
+CREATE TABLE lock_tbl6 (b BIGINT) INHERITS (lock_tbl5);
+GRANT USAGE ON SCHEMA lock_schema1 TO lock_rol5;
+GRANT ALL ON TABLE lock_tbl5 TO lock_rol5;
+REVOKE ALL ON TABLE lock_tbl6 FROM lock_rol5;
+SET ROLE lock_rol5;
+SET search_path=lock_schema1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl5 * IN access EXCLUSIVE MODE;
+ROLLBACK;
+BEGIN TRANSACTION;
+LOCK TABLE ONLY lock_tbl5 IN access EXCLUSIVE MODE;
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl6;
+DROP TABLE lock_tbl5;
+REVOKE ALL ON SCHEMA lock_schema1 FROM lock_rol5;
+DROP ROLE lock_rol5;
+
+
+
+-- Should fail. Ensure that LOCK is allowed when ROLE has appropriate rights
+-- either directly, or via INHERITANCE
+CREATE TABLE lock_tbl7 (a BIGINT);
+CREATE TABLE lock_tbl8 (b BIGINT) INHERITS (lock_tbl7);
+CREATE TABLE lock_tbl9 (c BIGINT) INHERITS (lock_tbl8);
+CREATE TABLE lock_tbl10(d BIGINT) INHERITS (lock_tbl9);
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl7 * IN access EXCLUSIVE MODE;
+ROLLBACK;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl7 * IN access EXCLUSIVE MODE NOWAIT;
+ROLLBACK;
+DROP TABLE lock_tbl10;
+DROP TABLE lock_tbl9;
+DROP TABLE lock_tbl8;
+DROP TABLE lock_tbl7;
+
+--
+-- Clean up
+--
+
+DROP SCHEMA lock_schema1 CASCADE;
Re: Add tests for LOCK TABLE
On 23 May 2013 18:19, Robins Tharakan <tharakan@gmail.com> wrote:
Please find attached a patch to take code-coverage of LOCK TABLE (
src/backend/commands/lockcmds.c) from 57% to 84%.
Updated the patch:
- Updated ROLEs as per Robert's feedback
- Added test to serial_schedule.
--
Robins Tharakan
Attachments:
regress_lock_v2.patchapplication/octet-stream; name=regress_lock_v2.patchDownload
diff --git a/src/test/regress/expected/lock.out b/src/test/regress/expected/lock.out
new file mode 100644
index 0000000..b5aa979
--- /dev/null
+++ b/src/test/regress/expected/lock.out
@@ -0,0 +1,108 @@
+--
+-- CASE
+-- Test the case statement
+--
+-- Setup
+CREATE SCHEMA lock_schema1;
+SET search_path = lock_schema1;
+CREATE TABLE lock_tbl1 (a BIGINT);
+-- Should work. Using Valid options.
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE;
+LOCK TABLE lock_tbl1 IN ROW SHARE MODE;
+LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN SHARE MODE;
+LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE;
+ROLLBACK;
+-- Should work. Using NOWAIT along with valid options.
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ROW SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE NOWAIT;
+ROLLBACK;
+-- Should fail. Can only LOCK a TABLE
+CREATE VIEW lock_view1 AS SELECT 1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_view1 IN EXCLUSIVE MODE;
+ERROR: "lock_view1" is not a table
+ROLLBACK;
+DROP VIEW lock_view1;
+-- Should fail. Can LOCK only if USER has permission
+CREATE ROLE regress_lock_rol1;
+CREATE TABLE lock_tbl2(a BIGINT);
+REVOKE ALL ON TABLE lock_tbl2 FROM regress_lock_rol1;
+SET ROLE regress_lock_rol1;
+SET search_path = lock_schema1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl2 IN EXCLUSIVE MODE;
+ERROR: relation "lock_tbl2" does not exist
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl2;
+DROP ROLE regress_lock_rol1;
+-- Should work. Ensure that LOCK works for inherited tables;
+CREATE ROLE regress_lock_rol3;
+CREATE TABLE lock_tbl3 (a BIGINT);
+CREATE TABLE lock_tbl4 (b BIGINT) INHERITS (lock_tbl3);
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl4 * IN access EXCLUSIVE MODE;
+SET ROLE regress_lock_rol3;
+SET search_path = lock_schema1;
+LOCK TABLE lock_tbl3 NOWAIT;
+ERROR: relation "lock_tbl3" does not exist
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl4;
+DROP TABLE lock_tbl3;
+DROP ROLE regress_lock_rol3;
+-- Should fail. Ensure that LOCK is allowed when ROLE has appropriate rights
+-- either directly, or via INHERITANCE
+CREATE ROLE regress_lock_rol5;
+CREATE TABLE lock_tbl5 (a BIGINT);
+CREATE TABLE lock_tbl6 (b BIGINT) INHERITS (lock_tbl5);
+GRANT USAGE ON SCHEMA lock_schema1 TO regress_lock_rol5;
+GRANT ALL ON TABLE lock_tbl5 TO regress_lock_rol5;
+REVOKE ALL ON TABLE lock_tbl6 FROM regress_lock_rol5;
+SET ROLE regress_lock_rol5;
+SET search_path=lock_schema1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl5 * IN access EXCLUSIVE MODE;
+ERROR: permission denied for relation lock_tbl6
+ROLLBACK;
+BEGIN TRANSACTION;
+LOCK TABLE ONLY lock_tbl5 IN access EXCLUSIVE MODE;
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl6;
+DROP TABLE lock_tbl5;
+REVOKE ALL ON SCHEMA lock_schema1 FROM regress_lock_rol5;
+DROP ROLE regress_lock_rol5;
+-- Should fail. Ensure that LOCK is allowed when ROLE has appropriate rights
+-- either directly, or via INHERITANCE
+CREATE TABLE lock_tbl7 (a BIGINT);
+CREATE TABLE lock_tbl8 (b BIGINT) INHERITS (lock_tbl7);
+CREATE TABLE lock_tbl9 (c BIGINT) INHERITS (lock_tbl8);
+CREATE TABLE lock_tbl10(d BIGINT) INHERITS (lock_tbl9);
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl7 * IN access EXCLUSIVE MODE;
+ROLLBACK;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl7 * IN access EXCLUSIVE MODE NOWAIT;
+ROLLBACK;
+DROP TABLE lock_tbl10;
+DROP TABLE lock_tbl9;
+DROP TABLE lock_tbl8;
+DROP TABLE lock_tbl7;
+--
+-- Clean up
+--
+DROP SCHEMA lock_schema1 CASCADE;
+NOTICE: drop cascades to table lock_tbl1
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 3e6b306..fd08e8d 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
# ----------
# Another group of parallel tests
# ----------
-test: privileges security_label collate matview
+test: privileges security_label collate matview lock
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 3ad289f..57b7acc 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -139,3 +139,4 @@ test: largeobject
test: with
test: xml
test: stats
+test: lock
diff --git a/src/test/regress/sql/lock.sql b/src/test/regress/sql/lock.sql
new file mode 100644
index 0000000..83cf93b
--- /dev/null
+++ b/src/test/regress/sql/lock.sql
@@ -0,0 +1,118 @@
+--
+-- CASE
+-- Test the case statement
+--
+
+-- Setup
+CREATE SCHEMA lock_schema1;
+SET search_path = lock_schema1;
+CREATE TABLE lock_tbl1 (a BIGINT);
+
+-- Should work. Using Valid options.
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE;
+LOCK TABLE lock_tbl1 IN ROW SHARE MODE;
+LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN SHARE MODE;
+LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE;
+LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE;
+ROLLBACK;
+
+-- Should work. Using NOWAIT along with valid options.
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl1 IN ACCESS SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ROW SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ROW EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE UPDATE EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN SHARE ROW EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN EXCLUSIVE MODE NOWAIT;
+LOCK TABLE lock_tbl1 IN ACCESS EXCLUSIVE MODE NOWAIT;
+ROLLBACK;
+
+
+
+-- Should fail. Can only LOCK a TABLE
+CREATE VIEW lock_view1 AS SELECT 1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_view1 IN EXCLUSIVE MODE;
+ROLLBACK;
+DROP VIEW lock_view1;
+
+-- Should fail. Can LOCK only if USER has permission
+CREATE ROLE regress_lock_rol1;
+CREATE TABLE lock_tbl2(a BIGINT);
+REVOKE ALL ON TABLE lock_tbl2 FROM regress_lock_rol1;
+SET ROLE regress_lock_rol1;
+SET search_path = lock_schema1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl2 IN EXCLUSIVE MODE;
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl2;
+DROP ROLE regress_lock_rol1;
+
+-- Should work. Ensure that LOCK works for inherited tables;
+CREATE ROLE regress_lock_rol3;
+CREATE TABLE lock_tbl3 (a BIGINT);
+CREATE TABLE lock_tbl4 (b BIGINT) INHERITS (lock_tbl3);
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl4 * IN access EXCLUSIVE MODE;
+SET ROLE regress_lock_rol3;
+SET search_path = lock_schema1;
+LOCK TABLE lock_tbl3 NOWAIT;
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl4;
+DROP TABLE lock_tbl3;
+DROP ROLE regress_lock_rol3;
+
+
+-- Should fail. Ensure that LOCK is allowed when ROLE has appropriate rights
+-- either directly, or via INHERITANCE
+CREATE ROLE regress_lock_rol5;
+CREATE TABLE lock_tbl5 (a BIGINT);
+CREATE TABLE lock_tbl6 (b BIGINT) INHERITS (lock_tbl5);
+GRANT USAGE ON SCHEMA lock_schema1 TO regress_lock_rol5;
+GRANT ALL ON TABLE lock_tbl5 TO regress_lock_rol5;
+REVOKE ALL ON TABLE lock_tbl6 FROM regress_lock_rol5;
+SET ROLE regress_lock_rol5;
+SET search_path=lock_schema1;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl5 * IN access EXCLUSIVE MODE;
+ROLLBACK;
+BEGIN TRANSACTION;
+LOCK TABLE ONLY lock_tbl5 IN access EXCLUSIVE MODE;
+ROLLBACK;
+RESET ROLE;
+DROP TABLE lock_tbl6;
+DROP TABLE lock_tbl5;
+REVOKE ALL ON SCHEMA lock_schema1 FROM regress_lock_rol5;
+DROP ROLE regress_lock_rol5;
+
+
+
+-- Should fail. Ensure that LOCK is allowed when ROLE has appropriate rights
+-- either directly, or via INHERITANCE
+CREATE TABLE lock_tbl7 (a BIGINT);
+CREATE TABLE lock_tbl8 (b BIGINT) INHERITS (lock_tbl7);
+CREATE TABLE lock_tbl9 (c BIGINT) INHERITS (lock_tbl8);
+CREATE TABLE lock_tbl10(d BIGINT) INHERITS (lock_tbl9);
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl7 * IN access EXCLUSIVE MODE;
+ROLLBACK;
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl7 * IN access EXCLUSIVE MODE NOWAIT;
+ROLLBACK;
+DROP TABLE lock_tbl10;
+DROP TABLE lock_tbl9;
+DROP TABLE lock_tbl8;
+DROP TABLE lock_tbl7;
+
+--
+-- Clean up
+--
+
+DROP SCHEMA lock_schema1 CASCADE;
Re: Add tests for LOCK TABLE
On Sun, Jul 7, 2013 at 12:17 PM, Robins Tharakan <tharakan@gmail.com> wrote:
Updated the patch:
- Updated ROLEs as per Robert's feedback
You managed to use a naming convention different from the one that you
used before. Before, you had regress_rol_op1; here, you've got
regress_lock_rol1. Consistency may be the hobgoblin of little minds,
but grep's mind is very little.
- Added test to serial_schedule.
When you add the test to serial_schedule, you're supposed to add it to
the same place that it occupies in the parallel schedule, more or
less, not just add it to the bottom of the file. The idea is that the
two files should roughly correspond. We should probably automate
that, but for now, this is how it is.
I have committed this patch with substantial simplifications and a few
other tweaks that I thought would improve test coverage. I feel that
this patch wasn't prepared as carefully as it could have been. For
example, consider this expected output:
+-- Should work. Ensure that LOCK works for inherited tables;
+CREATE ROLE regress_lock_rol3;
+CREATE TABLE lock_tbl3 (a BIGINT);
+CREATE TABLE lock_tbl4 (b BIGINT) INHERITS (lock_tbl3);
+BEGIN TRANSACTION;
+LOCK TABLE lock_tbl4 * IN access EXCLUSIVE MODE;
+SET ROLE regress_lock_rol3;
+SET search_path = lock_schema1;
+LOCK TABLE lock_tbl3 NOWAIT;
+ERROR: relation "lock_tbl3" does not exist
+ROLLBACK;
+RESET ROLE;The comment asserts that this "should work", but only part of it
works. Also, the failure is evidently intending to test whether
regress_lock_rol3 can lock lock_tbl3 despite lack of privileges on
that object, but the error message is complaining about something
different - namely, that regress_lock_rol3 doesn't even have
permissions on the schema. So the thing you meant to test isn't
really being tested.
The comment block at the top of the file was obviously cut and pasted
from somewhere else without being modified to reflect reality.
I altogether removed the last block - with lock_tbl{7,8,9,10} -
because the comment asserts that it "should fail" yet no statement in
that chunk actually fails, and locking on tables with inheritance is
test higher up. I did however modify the inheritance test to use a
multi-level inheritance hierarchy, since that does seem worth
verifying.
I also removed the test that simply checked whether a user without
permissions could lock the table; there's already a similar check in
privileges.sql.
All in all, I'm starting to get a bit skeptical of your test coverage
numbers. How are you deriving those, exactly? I agree that the tests
in that patch as committed are worthwhile, but they don't seem
sufficient to raise the coverage from 57% to 84% of... whatever those
numbers are percentages of. Now maybe in simplifying this down I
simplified away something essential, but I can't see what.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers