locking problem - Mailing list pgsql-sql

<p> hi,<br /><br /> can anyone help me out on the following scenario:<br /> why this is happening, if i'm doing any
thingwrong or its the feature of postgres...<br /><br /> regards<br /> cheetor<br />
========================================================================<br/> PostgreSQL<br /> Steps:<br /><br /> 1.
Createa table<br /> create table mytab (name varchar(100), marks NUMERIC(9));<br /><br /> 2. insert a row into the
table:<br/> INSERT INTO mytab (name, marks) VALUES ('abc', 3);<br /><br /> 3. compile the function myproc (at end of
mail)<br/><br /> 4. Open sql prompt and type:<br />     begin;<br />     select myproc(1, 'xyz', 3, 'abc', 10,
'pqr');<br/><br /> This would insert into the table the values 'xyz' and 1.<br /><br /> 5. Open another sql prompt and
type:<br/>     begin;<br />     select myproc(10, 'pqr', 3, 'abc', 1, 'xyz');<br /><br /> This would try and insert
intothe table values 'pqr' and 10.<br /><br /> But as the query in step4 has locked the table records, the query of<br
/>step 5 would wait..<br /><br /> 6. On the first  sql prompt type commit;<br /> This would let the transaction of step
5complete, but it outputs the<br /> statement "not exists". This means that even after the transaction was<br />
commited,the insert of step 4 was not visible in query of step 5.<br /><br /> 7. on sql prompt of step 5, again type<br
/>select myproc(10, 'pqr', 3, 'abc', 1, 'xyz');<br /><br /> and this outputs "exists" which means that now the insert
isvisible.<br /> Therefore it implies that if the second transaction is blocking on a<br /> locked resource, after it
resumes,it does not see any inserts, but if<br /> has not blocked, these inserts are visible.<br /><br /> The same
stepswere tried on oracle 8.1.7.<br /> Steps:<br /><br /> 1. Create a table<br /> create table mytab (name
varchar(100),marks int);<br /><br /> 2. insert a row into the table:<br /> INSERT INTO mytab (name, marks) VALUES
('abc',3);<br /> commit;<br /><br /> 3. compile the procedure myproc (at end of mail)<br /><br /> 4. Open sql prompt
(setserver output on) and type:<br />     exec myproc(1, 'xyz', 2, 'abc', 10, 'pqr');<br /><br /> 5. Open another sql
promptand type (set server output on):<br /> exec myproc(10, 'pqr', 2, 'abc', 1, 'xyz');<br /> But as the query is
step4has locked the table records, the query of<br /> step 5 would wait..<br /><br /> 6. On the first sql type
commit;<br/> This would let the transaction of step 5 complete, and it outputs the<br /> statement "exists". This means
thatafter the transaction was<br /> commited, the insert of step 4 is visible in query of step 5.<br /><br />
______________________________________________________________________<br/> Postgres function<br />
____________________________________________________________________<br/><br /> CREATE FUNCTION myproc (INT8, VARCHAR,
INT8,VARCHAR, INT8, VARCHAR)<br /> RETURNS TEXT AS '<br /> DECLARE<br /><br /> DBMarks  ALIAS FOR $1;<br /> DBName 
ALIASFOR $2;<br /><br /> DBMarks2 ALIAS FOR $3;<br /> DBName2  ALIAS FOR $4;<br /><br /> DBMarks3 ALIAS FOR $5;<br />
DBName3 ALIAS FOR $6;<br /><br /> DBMarks4 INT8;<br /> DBName4  VARCHAR (100);<br /><br /> BEGIN<br /><br /> SELECT
name,marks<br /> INTO  DBName4, DBMarks4<br /> FROM mytab<br /> WHERE name = DBName2<br /> AND marks = DBMarks2 FOR
UPDATE;<br/><br /> raise notice '' name : % : marks : % :'', DBName4, DBMarks4;<br /><br /> INSERT INTO mytab (name,
marks)VALUES (DBName, DBMarks);<br /><br /> raise notice ''insert done'';<br /><br /> IF EXISTS(SELECT * FROM mytab
WHEREname = DBName3 AND marks =<br /> DBMarks3)<br /> THEN<br />   raise notice ''exists'';<br /> ELSE<br />   raise
notice''not exists'';<br /> END IF;<br /><br /><br /> return ''done'';<br /><br /><br /> END;<br /> ' language
'plpgsql';<br/><br /><br /> ________________________________________________________________________<br /> Oracle
procedure<br/> _______________________________________________________________________<br /><br /> CREATE OR REPLACE
PROCEDUREmyproc<br /> (<br />   DBMarks  INT,<br />   DBName  VARCHAR,<br />   DBMarks2 INT,<br />   DBName2 
VARCHAR,<br/>   DBMarks3 INT,<br />   DBName3  VARCHAR<br /> )<br /> AS<br />   DBMarks4 INT;<br />   DBName4  VARCHAR
(100);<br/><br />   BEGIN<br />   SELECT name, marks<br />   INTO  DBName4, DBMarks4<br />   FROM mytab<br />   WHERE
name= DBName2<br />   AND marks = DBMarks2 FOR UPDATE;<br /><br />   dbms_output.put_line(' Name :' || DBName4 || ' :
Marks: ' ||<br /> DBMarks4 ||':');<br /><br />   INSERT INTO mytab (name, marks) VALUES (DBName, DBMarks);<br /><br />
 dbms_output.put_line('Insert Done');<br /><br />   BEGIN<br />     SELECT name, marks into DBName4, DBMarks4 FROM
mytabWHERE name =<br /> DBName3 AND marks = DBMarks3;<br />     dbms_output.put_line('exists');<br />   EXCEPTION<br />
   WHEN NO_DATA_FOUND THEN<br />     dbms_output.put_line('not exists');<br />   END;<br />  
dbms_output.put_line('done');<br/>   END;<br />
________________________________________________________________________<br /><br /><a
href="http://clients.rediff.com/signature/track_sig.asp"target="_blank"><img border="0" height="74" hspace="0"
src="http://ads.rediff.com/RealMedia/ads/adstream_nx.cgi/www.rediffmail.com/inbox.htm@Bottom"vspace="0" width="496"
/></a>