X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5A4AFD1D363 for ; Fri, 30 Jan 2004 07:41:46 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 99922-03 for ; Fri, 30 Jan 2004 03:41:47 -0400 (AST) Received: from rediffmail.com (unknown [203.199.83.248]) by svr1.postgresql.org (Postfix) with SMTP id 08EDFD1D321 for ; Fri, 30 Jan 2004 03:41:44 -0400 (AST) Received: (qmail 26890 invoked by uid 510); 30 Jan 2004 07:41:42 -0000 Date: 30 Jan 2004 07:41:42 -0000 Message-ID: <20040130074142.26889.qmail@webmail36.rediffmail.com> Received: from unknown (202.140.41.196) by rediffmail.com via HTTP; 30 jan 2004 07:41:42 -0000 MIME-Version: 1.0 From: "cheater cheater" Reply-To: "cheater cheater" To: pgsql-sql@postgresql.org Subject: locking problem Content-type: multipart/alternative; boundary="Next_1075448502---0-203.199.83.248-26880" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/280 X-Sequence-Number: 16673 This is a multipart mime message --Next_1075448502---0-203.199.83.248-26880 Content-type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

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...

regards
cheetor
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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 transacti= on was
commited, the insert of step 4 was not visible in query of step 5.

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 vis= ible.
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 =3D DBName2
AND marks =3D 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 =3D DBName3 AND marks =3D
DBMarks3)
THEN
raise notice ''exists'';
ELSE
raise notice ''not exists'';
END IF;

return ''done'';

END;
' language 'plpgsql';

________________________________________________________________________
Oracle procedure
_______________________________________________________________________

CREATE OR REPLACE PROCEDURE myproc
(
DBMarks INT,
DBName VARCHAR,
DBMarks2 INT,
DBName2 VARCHAR,
DBMarks3 INT,
DBName3 VARCHAR
)
AS
DBMarks4 INT;
DBName4 VARCHAR (100);

BEGIN
SELECT name, marks
INTO DBName4, DBMarks4
FROM mytab
WHERE name =3D DBName2
AND marks =3D DBMarks2 FOR UPDATE;

dbms_output.put_line(' Name :' || DBName4 || ' : Marks : ' ||
DBMarks4 ||':');

INSERT INTO mytab (name, marks) VALUES (DBName, DBMarks);

dbms_output.put_line('Insert Done');

BEGIN
SELECT name, marks into DBName4, DBMarks4 FROM mytab WHERE na= me =3D
DBName3 AND marks =3D DBMarks3;
dbms_output.put_line('exists');
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('not exists');
END;
dbms_output.put_line('done');
END;
________________________________________________________________________

--Next_1075448502---0-203.199.83.248-26880 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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... regards cheetor =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D 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. 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 =3D DBName2 AND marks =3D 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 =3D DBName3 AND marks =3D DBMarks3) THEN raise notice ''exists''; ELSE raise notice ''not exists''; END IF; return ''done''; END; ' language 'plpgsql'; ________________________________________________________________________ Oracle procedure _______________________________________________________________________ CREATE OR REPLACE PROCEDURE myproc ( DBMarks INT, DBName VARCHAR, DBMarks2 INT, DBName2 VARCHAR, DBMarks3 INT, DBName3 VARCHAR ) AS DBMarks4 INT; DBName4 VARCHAR (100); BEGIN SELECT name, marks INTO DBName4, DBMarks4 FROM mytab WHERE name =3D DBName2 AND marks =3D DBMarks2 FOR UPDATE; dbms_output.put_line(' Name :' || DBName4 || ' : Marks : ' || DBMarks4 ||':'); INSERT INTO mytab (name, marks) VALUES (DBName, DBMarks); dbms_output.put_line('Insert Done'); BEGIN SELECT name, marks into DBName4, DBMarks4 FROM mytab WHERE name =3D DBName3 AND marks =3D DBMarks3; dbms_output.put_line('exists'); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('not exists'); END; dbms_output.put_line('done'); END; ________________________________________________________________________ --Next_1075448502---0-203.199.83.248-26880--