From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200402031408.PAA02272@rodos>
Subject: Re: locking problem
To: cheetor@rediffmail.com ("cheater  cheater")
Date: Tue, 03 Feb 2004 15:08:05 MET
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20040130074142.26889.qmail@webmail36.rediffmail.com>;
	from "cheater  cheater" at Jan 30, 104 8:46 am

> 
> hi,
> 
> can anyone help me out on the following scenario:
> why this is happening, if i'm doing any thing wrong or its the feature of p=
> ostgres...
I'd say it's a feature - see below. 
> 
> regards
> cheetor
> ==========================
> ==========================
> ======================
>  PostgreSQL
>  Steps:
> 
>  1. Create a table
>  create table mytab (name varchar(100), marks NUMERIC(9));
> 
>  2. insert a row into the table:
>  INSERT INTO mytab (name, marks) VALUES ('abc', 3);
> 
>  3. compile the function myproc (at end of mail)
> 
>  4. Open sql prompt and type:
>      begin;
>      select myproc(1, 'xyz', 3, 'abc', 10, 'pqr');
> 
>  This would insert into the table the values 'xyz' and 1.
> 
>  5. Open another sql prompt and type:
>      begin;
>      select myproc(10, 'pqr', 3, 'abc', 1, 'xyz');
> 
>  This would try and insert into the table values 'pqr' and 10.
> 
>  But as the query in step4 has locked the table records, the query of
>  step 5 would wait..
> 
>  6. On the first  sql prompt type commit;
>  This would let the transaction of step 5 complete, but it outputs the
>  statement "not exists". This means that even after the transaction was
>  commited, the insert of step 4 was not visible in query of step 5.

My understanding of PG transactions is within a transaction started 
with BEGIN; you'll see only what was committed before the BEGIN; 
So even after commit of step 4, step 5 is still in the pre-commit state. 

> 
>  7. on sql prompt of step 5, again type
>  select myproc(10, 'pqr', 3, 'abc', 1, 'xyz');
> 
>  and this outputs "exists" which means that now the insert is visible.
>  Therefore it implies that if the second transaction is blocking on a
>  locked resource, after it resumes, it does not see any inserts, but if
>  has not blocked, these inserts are visible.
> 
>  The same steps were tried on oracle 8.1.7.
>  Steps:
> 
>  1. Create a table
>  create table mytab (name varchar(100), marks int);
> 
>  2. insert a row into the table:
>  INSERT INTO mytab (name, marks) VALUES ('abc', 3);
>  commit;
> 
>  3. compile the procedure myproc (at end of mail)
> 
>  4. Open sql prompt (set server output on) and type:
>      exec myproc(1, 'xyz', 2, 'abc', 10, 'pqr');
> 
>  5. Open another sql prompt and type (set server output on):
>  exec myproc(10, 'pqr', 2, 'abc', 1, 'xyz');
>  But as the query is step4 has locked the table records, the query of
>  step 5 would wait..
> 
>  6. On the first sql type commit;
>  This would let the transaction of step 5 complete, and it outputs the
>  statement "exists". This means that after the transaction was
>  commited, the insert of step 4 is visible in query of step 5.
> 
> ______________________________________________________________________
>  Postgres function
> ____________________________________________________________________
> 
>  CREATE FUNCTION myproc (INT8, VARCHAR, INT8, VARCHAR, INT8, VARCHAR)
>  RETURNS TEXT AS '
>  DECLARE
> 
>  DBMarks  ALIAS FOR $1;
>  DBName  ALIAS FOR $2;
> 
>  DBMarks2 ALIAS FOR $3;
>  DBName2  ALIAS FOR $4;
> 
>  DBMarks3 ALIAS FOR $5;
>  DBName3  ALIAS FOR $6;
> 
>  DBMarks4 INT8;
>  DBName4  VARCHAR (100);
> 
>  BEGIN
> 
>  SELECT name, marks
>  INTO   DBName4, DBMarks4
>  FROM mytab
>  WHERE name = DBName2
>  AND marks = DBMarks2 FOR UPDATE;
> 
>  raise notice '' name : % : marks : % :'', DBName4, DBMarks4;
> 
>  INSERT INTO mytab (name, marks) VALUES (DBName, DBMarks);
> 
>  raise notice ''insert done'';
> 
>  IF EXISTS(SELECT * FROM mytab WHERE name = DBName3 AND marks =
>  DBMarks3)
>  THEN
>   raise notice ''exists'';
>  ELSE
>   raise notice ''not exists'';
>  END IF;
> 
> 
>  return ''done'';
> 
> 
>  END;
>  ' language 'plpgsql';
> 
> 

Regards, Christoph