Hello,
I'm currently in the midst of working on a serializable transaction which drops indexes on several tables, does a bulk
copy,and rebuilds the indexes. Based on what I've read it seemed as though I'd be able to concurrently execute read on
queriesagainst these tables, returning results based on the table snapshot from before the serialized transaction
began.However, this doesn't work. A simple read-only select query waits until the serialized transaction is finished
beforereturning results.
Based on the user documentation, specifically
(http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=explicit-locking.html#LOCKING-TABLES),it would seem
thatthe only issue in PostgreSQL that could block a simple select call would be an ACCESS EXCLUSIVE lock, which is
acquiredonly by DROP TABLE, ALTER TABLE, and VACUUM FULL, none of which I'm using. In fact, I've noticed this exact
behaviorwith DROP INDEX.
Please excuse my futile attempt to outline two concurrent transactions here:
testdb=# \d trans_test
Table "public.trans_test"
Column | Type | Modifiers
- -------+--------+-----------
val integer
Indexes: idx_trans_test btree(val)
testdb=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
[TRANSACTION 1] BEGIN;
BEGIN
[TRANSACTION 1] SELECT * FROM trans_test;
val
- -----
1
2
[TRANSACTION 2] SELECT * FROM trans_test;
val
- -----
1
2
[TRANSACTION 1] DROP INDEX idx_trans_test;
DROP INDEX
[TRANSACTION 2] SELECT * FROM trans_test;
... (Waiting)
[TRANSACTION 1] COMMIT;
COMMIT
(TRANSACTION 2 returns after COMMIT)
val
- -----
1
2
So is this a glitch or am I missing some nuance of a serializable transaction? In either case I'm eager to figure out
whatsactually going on.
Thanks,
- -justin
_____________________________________________________________
Sign up for FREE email from bboy.com at http://www.bboy.com
_____________________________________________________________
Select your own custom email address for FREE! Get you@yourchoice.com w/No Ads, 6MB, POP & more!
http://www.everyone.net/selectmail?campaign=tag
