transactions.out

PostgreSQL7.4.6 for Linux

--
-- TRANSACTIONS
--
BEGIN;
SELECT * 
   INTO TABLE xacttest
   FROM aggtest;
INSERT INTO xacttest (a, b) VALUES (777, 777.777);
END;
-- should retrieve one value--
SELECT a FROM xacttest WHERE a > 100;
  a  
-----
 777
(1 row)

BEGIN;
CREATE TABLE disappear (a int4);
DELETE FROM aggtest;
-- should be empty
SELECT * FROM aggtest;
 a | b 
---+---
(0 rows)

ABORT;
-- should not exist 
SELECT oid FROM pg_class WHERE relname = 'disappear';
 oid 
-----
(0 rows)

-- should have members again 
SELECT * FROM aggtest;
  a  |    b    
-----+---------
  56 |     7.8
 100 |  99.097
   0 | 0.09561
  42 |  324.78
(4 rows)

-- Read-only tests
CREATE TABLE writetest (a int);
CREATE TEMPORARY TABLE temptest (a int);
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
DROP TABLE writetest; -- fail
ERROR:  transaction is read-only
INSERT INTO writetest VALUES (1); -- fail
ERROR:  transaction is read-only
SELECT * FROM writetest; -- ok
 a 
---
(0 rows)

DELETE FROM temptest; -- ok
UPDATE temptest SET a = 0 WHERE a = 1 AND writetest.a = temptest.a; -- ok
PREPARE test AS UPDATE writetest SET a = 0; -- ok
EXECUTE test; -- fail
ERROR:  transaction is read-only
SELECT * FROM writetest, temptest; -- ok
 a | a 
---+---
(0 rows)

CREATE TABLE test AS SELECT * FROM writetest; -- fail
ERROR:  transaction is read-only
START TRANSACTION READ WRITE;
DROP TABLE writetest; -- ok
COMMIT;