[GENERAL] Transaction isolation levels
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I'm having some trouble with transaction isolation levels, and would appreciate some input on this. According to the documentation of PostgreSQL 8.0.3, section 12.2: "Read Committed is the default isolation level in PostgreSQL. When a transaction runs on this isolation level, a SELECT query sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions." Therefore, in this isolation level, I should not see data committed by another concurrent transaction. Now I open two terminals with a 'pgsql' command, and do the following: terminal 1: geertj=> \set AUTOCOMMIT off geertj=> set transaction isolation level read committed; SET geertj=> create table products (id integer not null, code char(20) not null); CREATE TABLE geertj=> commit; begin; COMMIT BEGIN terminal 2: geertj=> \set AUTOCOMMIT off geertj=> set transaction isolation level read committed; SET geertj=> select * from products; id | code +-- (0 rows) terminal 1: geertj=> insert into products values (10, 'pr10'); INSERT 34071 1 geertj=> commit; COMMIT terminal 2: geertj=> select * from products; id | code +-- 10 | pr10 As you see, the row inserted in the transaction from terminal 1 appears into terminal 2, but terminal 2 had a transaction open that did not commit. The top of section 12.2 of the manual tells that nonrepeatable reads may happen in the 'read committed' isolation level. I can understand the above behaviour in terms of this. However, the start of section 12.2.1 tells that in read committed mode, you get a snapshot of the database when the transaction starts, and that snapshot will not inlude committed changes from other transactions. Who is right here? Regards, Geert -BEGIN PGP SIGNATURE- Version: PGP Desktop 9.0.1 (Build 2185) iQEVAwUBQs+ycZrPkVOV4lDgAQK0NggAonVk+Qmwui4a49UJr10P7mxcmrFblw+x 6HM6yXVCBk4qRczRT8BelQp3fBQR/8kostAbmnQ27Pes0wFPOsUEjiyWyskKdCtU Mt5OXQsV7jAPAwPgFDwnYbd0geDVK76WwTJAKuXBunL5/Mz92nv6XHxWvFjLNNEs laPpirH1xGFy9Po0kpYLx7Orgg6I/m/BM4V4BItZqVcFaiFLnCTt+Lolimk6j65Z NzHIkTLq2C+ju1EqvbF6M1euAR0ni6D4DLIUd11XJnw0A8mx/+7ZV5ZGcHv+X6YT dsfutW17jZkzlB9yUyKa91wII0leo/fFlRCnoSYqG3ONGJLoiNJdXw== =/78D -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Transaction isolation levels
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Fuhr wrote: >What words in section 12.2.1 are you referring to? I see the >following (emphasis mine): > > In effect, a SELECT query sees a snapshot of the database as of > the instant that that *query* begins to run. Notice that two > successive SELECT commands can see different data, even though > they are within a single transaction, if other transactions commit > changes during execution of the first SELECT. > > Apologies for the confusion. Indeed the documentation is right. I confused "start of query" with "start of transaction". For my curiosity, what is the reason that in read comitted mode MVCC can guarantee a consistent snapshot durign a query, while in serializable mode MVCC can guarantee it for a longer interval (the entire transaction). Are these different MVCC implementations, or is some kind of locking performed when executing queries in read committed mode? Thanks, Geert -BEGIN PGP SIGNATURE- Version: PGP Desktop 9.0.1 (Build 2185) iQEVAwUBQs/izprPkVOV4lDgAQLpFggAqbxaFnMfajg94aaUcWF/IK0ZlEuEVVGl 2hmb295ukCGOV7IjhMFocCQMWxLHcAuwP4vBJM/Dkcy9HW7PGCM6XWJyWbQMYHcn iWZAw/p2bllxByEe6xli+J2VdeZAohHPixRibLa+uqCE594o2NPK8g06HmPz28rb noZefXv0+4rGI4Nlw03bLbt80WV2+bYkLFfDveEHt8tx93KS8p+cfv+KL7sFwtzK 1ZbSm1TEdCmPfLAo8UI5YZKJtHA58XoW89WTgACATz04TNHMzni+U5ToMqX4tKOI 6AniwTocjko9Nt+ul8CFquOOoyk7WlOVrtUXpUAg4QEsBMA503Z3uA== =JBQU -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Transaction isolation levels
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I'm having some trouble with transaction isolation levels, and would appreciate some input on this. According to the documentation of PostgreSQL 8.0.3, section 12.2: "Read Committed is the default isolation level in PostgreSQL. When a transaction runs on this isolation level, a SELECT query sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions." Therefore, in this isolation level, I should not see data committed by another concurrent transaction. Now I open two terminals with a 'pgsql' command, and do the following: terminal 1: geertj=> \set AUTOCOMMIT off geertj=> set transaction isolation level read committed; SET geertj=> create table products (id integer not null, code char(20) not null); CREATE TABLE geertj=> commit; begin; COMMIT BEGIN terminal 2: geertj=> \set AUTOCOMMIT off geertj=> set transaction isolation level read committed; SET geertj=> select * from products; id | code +-- (0 rows) terminal 1: geertj=> insert into products values (10, 'pr10'); INSERT 34071 1 geertj=> commit; COMMIT terminal 2: geertj=> select * from products; id | code +-- 10 | pr10 As you see, the row inserted in the transaction from terminal 1 appears into terminal 2, but terminal 2 had a transaction open that did not commit. The top of section 12.2 of the manual tells that nonrepeatable reads may happen in the 'read committed' isolation level. I can understand the above behaviour in terms of this. However, the start of section 12.2.1 tells that in read committed mode, you get a snapshot of the database when the transaction starts, and that snapshot will not inlude committed changes from other transactions. Who is right here? Regards, Geert -BEGIN PGP SIGNATURE- Version: PGP Desktop 9.0.1 (Build 2185) iQEVAwUBQs+xUJrPkVOV4lDgAQJsFggApVahUUKqbNAyf7jm3e9WYIvj3bGf2nhL 0jbBtVDp+ewemMHYaCjSyx+Bj+IbnsHJEQywDVX5GQSuL9/7AEyB/RFs0lpGss26 CZDYdH08rBYSTonpEHy2x5cM77A5O/7MZSfcPliR/ON7iQRmuVwWltWjCt0or7VD +3y45bWldzzSJ42WCBSS5eJQp5xjCA91CNR3dH09H4i+8Y5PgpHyvvZgB/cmdzCV HmRWDDsohgaKDZRilh/A+q8BhUuxks3xzqY3JUrTZ7Js3x/9kcJyoB41EXuuT/AW gwYcZJJ56XN2UrmPI8lXZWpkdKnBOAU5g2YIa+alAzw9U06CuFEatw== =1Gpf -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Weird results when using schemas
Hi, I'm experiencing some weird results with SELECT queries when I recently switched to using schemas. Basically, the same query works OK when I set my search_path to include the schema, but it doesn't when I qualify the tables in my query. One query that demonstrates the behaviour is (apologies for the long query). shs=> SELECT shs.city.id,shs.city.code,shs.city.name,shs.city.description,shs.city.mod_date,shs.city.mod_user FROM (shs.city AS city INNER JOIN shs.object_city_relationship ON shs.object_city_relationship.city_id = city.id INNER JOIN shs.object AS object ON shs.object_city_relationship.object_id = object.id) WHERE object.id = 1; NOTICE: adding missing FROM-clause entry for table "city" id | code | name | description | mod_date | mod_user +++++-- 1 | alanya | Alanya | Alanya, the nice city. | 2005-11-21 00:00:40.502701 |0 2 | bodrum | Bodrum | bodrum | 2005-11-21 00:03:53.786452 |0 (2 rows) Note the WARNING that "city" is missing in the FROM clause. I don't understand this as it seems to be there. The result is that postgres added the "city" table without a join condition, which makes the query return second row which is not the one I was after. When I set my search path and repeat the exact same query but without schemas, it works OK: shs=> set search_path to shs,public; SET shs=> SELECT city.id,city.code,city.name,city.description,city.mod_date,city.mod_user FROM (city AS city INNER JOIN object_city_relationship ON object_city_relationship.city_id = city.id INNER JOIN object AS object ON object_city_relationship.object_id = object.id) WHERE object.id = 1; id | code | name | description | mod_date | mod_user +++-++-- 2 | bodrum | Bodrum | bodrum | 2005-11-21 00:03:53.786452 |0 (1 row) I'm using PostgreSQL 8.0.4 For reference, I've included the table definitions and contents below. Does anyone have an idea what is causing this behaviour? Regards, Geert CREATE TABLE shs.city (id INTEGER DEFAULT NULL,code CHARACTER VARYING(64) DEFAULT NULL,name CHARACTER VARYING(64) DEFAULT NULL,description TEXT DEFAULT NULL,mod_date TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,mod_user INTEGER DEFAULT NULL,_refcount INTEGER NOT NULL DEFAULT 0,PRIMARY KEY (id)); CREATE TABLE shs.object (id INTEGER DEFAULT NULL,code CHARACTER VARYING(64) DEFAULT NULL,object_type CHARACTER VARYING(16) DEFAULT NULL,title CHARACTER VARYING(128) DEFAULT NULL,description TEXT DEFAULT NULL,price DECIMAL(10,2) DEFAULT NULL,acceptance_date DATE DEFAULT NULL,object_size INTEGER DEFAULT NULL,lot_size INTEGER DEFAULT NULL,construction_year INTEGER DEFAULT NULL,number_of_rooms INTEGER DEFAULT NULL,number_of_bathrooms INTEGER DEFAULT NULL,distance_to_sea INTEGER DEFAULT NULL,distance_to_center INTEGER DEFAULT NULL,lift_present BOOLEAN DEFAULT NULL,swimming_pool_present BOOLEAN DEFAULT NULL,balcony_present BOOLEAN DEFAULT NULL,airco_present CHARACTER VARYING(16) DEFAULT NULL,mod_date TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,mod_user INTEGER DEFAULT NULL,_refcount INTEGER NOT NULL DEFAULT 0,PRIMARY KEY (id)); CREATE TABLE shs.object_city_relationship (object_id INTEGER DEFAULT NULL,city_id INTEGER DEFAULT NULL,FOREIGN KEY (object_id) REFERENCES shs.object (id) ON DELETE RESTRICT,FOREIGN KEY (city_id) REFERENCES shs.city (id) ON DELETE RESTRICT,PRIMARY KEY (object_id,city_id)); And the data contents: shs=> select * from shs.city; id | code | name | description | mod_date | mod_user | _refcount +++++--+--- 1 | alanya | Alanya | Alanya, the nice city. | 2005-11-21 00:00:40.502701 |0 | 2 2 | bodrum | Bodrum | bodrum | 2005-11-21 00:03:53.786452 |0 | 2 (2 rows) shs=> select * from shs.object; id | code | object_type | title | description | price | acceptance_date | object_size | lot_size | construction_year | number_of_rooms | number_of_bathrooms | distance_to_sea | distance_to_center | lift_present | swimming_pool_present | balcony_present | airco_present | mod_date | mod_user | _refcount ++-+---+-+--+-+-+--+---+-+-+-++--+---+-+---++--+--- 1 | UT-001 | appartment | test2 | test| 1.00 | 2005-12-31 | 100 | | 2005 | 5 | | || |
Re: [GENERAL] Weird results when using schemas
Michael Fuhr wrote: You refer to shs.city. in the select list, but in the from clause you've aliased shs.city to city. As the SELECT documentation says, When an alias is provided, it completely hides the actual name of the table or function; for example given FROM foo AS f, the remainder of the SELECT must refer to this FROM item as f not foo. Michael, Thanks a lot for your very helpful answer (thanks to Stephan Szabo as well). I've now changed my query as per your indications and indeed it works ok. Best regards, Geert ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
