[SQL] locking problem
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 postgres...
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.
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';
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 = DBName2
AND marks = 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 =
DBName3 AND marks = 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;
Re: [SQL] limit 1 and functional indexes: SOLVED
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: viernes, 30 de enero de 2004 7:08
>
> Yeah, the problem with functional indexes is that the optimizer doesn't have
> any clue how the records are distributed since it only has statistics for
> columns, not your expression. Notice it's estimating 2956 rows where in fact
> there are 0.
Thanks for the explication.
> Given the type of data you're storing, which looks like hex strings, are you
> sure you need to do a case-insensitive search here? Can't you just uppercase
> it when you store it?
That would be great but we store a variety of case insensitive proof of purchase
codes here. Some we give to customers in upper case and some in lower case.
Hopefully someday we can redesign it all to just be in uppercase...
> The offset 0 prevents the optimizer from pulling the subquery into the outer
> query. I think this will prevent it from even considering the order_date index
> scan, but you'll have to try to be sure.
It works perfectly - thanks a million!
Strangely the offset 0 does not seem to make any difference.
Gotta read up more about subqueries :)
explain analyze
select code,order_date
from (
select code, order_date
from transactions
where UPPER(pop) = UPPER('c892eb2f877e3a28ddc8e196cd5a8aad')
limit 1
) as foo
order by order_date DESC;
--
Sort (cost=3.95..3.96 rows=1 width=33) (actual time=0.14..0.14 rows=1 loops=1)
Sort Key: order_date
-> Subquery Scan foo (cost=0.00..3.94 rows=1 width=33) (actual time=0.06..0.07
rows=1 loops=1)
-> Limit (cost=0.00..3.94 rows=1 width=33) (actual time=0.06..0.06 rows=1
loops=1)
-> Index Scan using transactions_pop_i on transactions
(cost=0.00..11653.84 rows=2956 width=33) (actual
time=0.05..0.06 rows=2 loops=1)
Index Cond: (upper((pop)::text) =
'C892EB2F877E3A28DDC8E196CD5A8AAD'::text)
Total runtime: 0.20 msec
(7 rows)
explain analyze
select code,order_date
from (
select code, order_date
from transactions
where UPPER(pop) = UPPER('c892eb2f877e3a28ddc8e196cd5a8aad')
limit 1
offset 0
) as foo
order by order_date DESC;
--
Sort (cost=3.95..3.96 rows=1 width=33) (actual time=0.14..0.14 rows=1 loops=1)
Sort Key: order_date
-> Subquery Scan foo (cost=0.00..3.94 rows=1 width=33) (actual time=0.06..0.07
rows=1 loops=1)
-> Limit (cost=0.00..3.94 rows=1 width=33) (actual time=0.06..0.06 rows=1
loops=1)
-> Index Scan using transactions_pop_i on transactions
(cost=0.00..11653.84 rows=2956 width=33) (actual
time=0.06..0.06 rows=2 loops=1)
Index Cond: (upper((pop)::text) =
'C892EB2F877E3A28DDC8E196CD5A8AAD'::text)
Total runtime: 0.20 msec
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] limit 1 and functional indexes: SOLVED
"Alexandra Birch" <[EMAIL PROTECTED]> writes:
> It works perfectly - thanks a million!
> Strangely the offset 0 does not seem to make any difference.
> Gotta read up more about subqueries :)
>
> explain analyze
> select code,order_date
>from (
> select code, order_date
>from transactions
> where UPPER(pop) = UPPER('c892eb2f877e3a28ddc8e196cd5a8aad')
> limit 1
> ) as foo
> order by order_date DESC;
I think what you're trying to do here is get the last order? Then you'll want
the limit to be on the outer query where it's ordered by order_date:
select code,order_date
from (
select code, order_date
from transactions
where UPPER(pop) = UPPER('c892eb2f877e3a28ddc8e196cd5a8aad')
offset 0
) as foo
order by order_date DESC;
limit 1
Note that in theory postgres should be able to find the same plan for this
query as yours since it's equivalent. It really ought to use the order_date
index since it thinks it would be more efficient.
However it's unable to because postgres doesn't try every possible index, only
the ones that look like they'll be useful for a where clause or an order by.
And the order by on the outer query isn't considered when it's looking at the
subquery.
It normally handles this case by merging the subquery into the outer query,
but it can't do that if there's a limit or offset. So an "offset 0" is
convenient for fooling it into thinking the subquery can't be pulled up
without actually changing the output.
You could do "order by upper(pop)" instead which might be clearer for someone
reading the query in that it makes it look like you're trying to encourage it
to use the index on upper(pop). In theory "order by"s on subqueries are
useless and postgres could ignore them, but it doesn't.
--
greg
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Empty String Comparison Standard compliant?
Just a short question (PostgreSQL 7.3.4 on hppa-hp-hpux10.20, compiled by GCC 2.8.1)
SELECT ('' > 'GDMF') ; SELECT ('GDMF' > '');
?column?
--
f
(1 row)
?column?
--
t
(1 row)
Are these results standard compliant?
Looks at least pretty reasonable to me.
It's just because I have a DBMS here, where the results are vice versa.
TIA.
Regards, Christoph
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Empty String Comparison Standard compliant?
Christoph Haller <[EMAIL PROTECTED]> writes: > [ '' is less than 'GDMF' ] > Are these results standard compliant? I doubt that the SQL standard really says anything on the subject, but ISTM any rational string-sorting algorithm would put '' before anything else. > It's just because I have a DBMS here, where the results are vice versa. Let me guess ... it's Oracle? Last I heard Oracle had a problem distinguishing empty strings from NULLs. If it sorts NULLs at the end then it wouldn't be surprising for empty strings to come out at the end in an ORDER BY sort. However, I'm not sure that explains the exact example you cite of boolean comparison results. If it were taking the '' as a NULL then both comparisons ought to return NULL. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Empty String Comparison Standard compliant?
On Fri, 30 Jan 2004, Christoph Haller wrote:
> Just a short question (PostgreSQL 7.3.4 on hppa-hp-hpux10.20, compiled by GCC 2.8.1)
>
> SELECT ('' > 'GDMF') ; SELECT ('GDMF' > '');
> ?column?
> --
> f
> (1 row)
>
> ?column?
> --
> t
> (1 row)
>
> Are these results standard compliant?
I think so in general. If the two lengths are not equal, then
"effectively" the shorter string is replaced by an extended version. If
it's NO PAD (varchar/text for us) the character used is a character that
sorts less than any string. Otherwise (char) it's a space. Then you use
the collating sequence to determine the value of >. Most will put space
before any of GDMF, so the above seems reasonable.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] query not using index for descending records?
Hi, | You probably don't want to do that. The DESC only applies to the | one expression it follows. What you want is probably: | explain select datetime,id from trafficlogs order by | datetime desc,id desc limit 20; This is exactly what I was after - worked a treat! Thanks. Darren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] java.lang.StringIndexOutOfBoundsException: String index
O kyrios [EMAIL PROTECTED] egrapse stis Jan 29, 2004 : > Now i a get another wierd error > > DEBUG] Transaction - -called safeRollback with null argument > java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer supported > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) > at > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:482) > at > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:461) > at > org.postgresql.jdbc1.AbstractJdbc1Connection.setAutoCommit(AbstractJdbc1Connection.java:957) > > They say its a driver problem i think i am having the right driver. I am > having RedHat Linux and postgres7.4 and pg73jdbc3.jar and jdbc-2.0.jar in > my class path. I donot understand what is going wrong What java version (java -version), os (uname -a) are you running? What is this jdbc-2.0.jar? In any case postgres7.4 doesnt match (again!) pg73jdbc3.jar. You better use : java 1.4.1 (blackdown in linux, native jdk14 in FreeBSD) postgresql server 7.4.1 postgresql jdbc 7.4.1 Also as i said check your classpath. > > but in my server logs i also see this error for torque > > [DEBUG] SharedPoolDataSourceFactory - -Starting initJdbc2Pool > [DEBUG] TorqueInstance - -getConfiguration() = > [EMAIL PROTECTED] > [DEBUG] AbstractDataSourceFactory - -applyConfiguration(null, > [EMAIL PROTECTED]) > [DEBUG] AbstractDataSourceFactory - > -applyConfiguration([EMAIL PROTECTED], > [EMAIL PROTECTED]) > [DEBUG] ConvertUtils - -Convert string 'SELECT 1' to class 'java.lang.String' > [ERROR] AbstractDataSourceFactory - -Property: validationQuery value: > SELECT 1 is not supported by DataSource: > org.apache.commons.dbcp.datasources.SharedPoolDataSource > [ERROR] AbstractDataSourceFactory - -Property: defaultMaxActive value: 16 > is not supported by DataSource: > org.apache.commons.dbcp.datasources.SharedPoolDataSource > > please let me know if any has an idea > > Thanks in Advance > > --Mohan > > > > > > O kyrios [EMAIL PROTECTED] egrapse stis Jan 29, 2004 : > > > >> Hi All > >> > >> I am having the torque3.1.jar and postgresql-7.4. I have compiled the > >> new jdbc driver called as postgresql.jar and have placed it in the lib > >> directory from where the ant scripts catch the jars. Whenever i try to > >> access through torque > >> > >> gestList = BaseGestlistPeer.doSelect(new Criteria()); > >> > >> this error arises > >> > >> java.lang.StringIndexOutOfBoundsException: String index out of range: > >> 23 > >> at java.lang.String.charAt(String.java:460) > >> at > >> org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653) > >> at > >> org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398) > > > > Just from the above, some one could conclude you are running > > a 7.2.x postgresql driver. > > > > Make sure you havent messed up with the CLASSPATH. > > Also after ensuring your 7.4.1 postgresql.jar is in the right location, > > make sure you are running a 7.4.1 backend. > > > >> > >> I have searched all the lists and the answer i got is a jdbc driver > >> issue. Please let me know what i need to for getting around with this > >> problem. This happens only with this method in the whole application. > >> But the same method works when i did not use the new torque 3.1 and > >> everything happens smoothly.Please let me know if there is a way to > >> configure jdbc with torque or any other configuration issue for > >> postgres you know might have been creating this problem. I am having > >> one heck of a night mare wid this issue. > >> > >> > >> thanks > >> > >> --Mohan > >> > >> > >> > >> ---(end of > >> broadcast)--- TIP 3: if posting/reading > >> through Usenet, please send an appropriate > >> subscribe-nomail command to [EMAIL PROTECTED] so that > >> your message can get through to the mailing list cleanly > >> > > > > -- > > -Achilleus > > > > > > ---(end of broadcast)--- > > TIP 8: explain analyze is your friend > > > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
PROBLEM SOLVEDRe: [SQL] java.lang.StringIndexOutOfBoundsException: String index
Thank you all guys. Thank you very much. Yeah it was the driver issue. I was runnin postgres 7.4 but using the pg73jdbc3.jar driver. I did not know that. Thank you all once again for your help. --mohan > O kyrios [EMAIL PROTECTED] egrapse stis Jan 29, 2004 : > >> Now i a get another wierd error >> >> DEBUG] Transaction - -called safeRollback with null argument >> java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer >> supported >> at >> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) >> at >> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:482) >> at >> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:461) >> at >> org.postgresql.jdbc1.AbstractJdbc1Connection.setAutoCommit(AbstractJdbc1Connection.java:957) >> >> They say its a driver problem i think i am having the right driver. I >> am having RedHat Linux and postgres7.4 and pg73jdbc3.jar and >> jdbc-2.0.jar in my class path. I donot understand what is going wrong > > What java version (java -version), os (uname -a) are you running? > > What is this jdbc-2.0.jar? > > In any case postgres7.4 doesnt match (again!) pg73jdbc3.jar. > > You better use : > java 1.4.1 (blackdown in linux, native jdk14 in FreeBSD) > postgresql server 7.4.1 > postgresql jdbc 7.4.1 > > Also as i said check your classpath. > >> >> but in my server logs i also see this error for torque >> >> [DEBUG] SharedPoolDataSourceFactory - -Starting initJdbc2Pool >> [DEBUG] TorqueInstance - -getConfiguration() = >> [EMAIL PROTECTED] >> [DEBUG] AbstractDataSourceFactory - -applyConfiguration(null, >> [EMAIL PROTECTED]) >> [DEBUG] AbstractDataSourceFactory - >> -applyConfiguration([EMAIL PROTECTED], >> [EMAIL PROTECTED]) >> [DEBUG] ConvertUtils - -Convert string 'SELECT 1' to class >> 'java.lang.String' [ERROR] AbstractDataSourceFactory - -Property: >> validationQuery value: SELECT 1 is not supported by DataSource: >> org.apache.commons.dbcp.datasources.SharedPoolDataSource >> [ERROR] AbstractDataSourceFactory - -Property: defaultMaxActive value: >> 16 is not supported by DataSource: >> org.apache.commons.dbcp.datasources.SharedPoolDataSource >> >> please let me know if any has an idea >> >> Thanks in Advance >> >> --Mohan >> >> >> >> >> > O kyrios [EMAIL PROTECTED] egrapse stis Jan 29, 2004 : >> > >> >> Hi All >> >> >> >> I am having the torque3.1.jar and postgresql-7.4. I have compiled >> the new jdbc driver called as postgresql.jar and have placed it in >> the lib directory from where the ant scripts catch the jars. >> Whenever i try to access through torque >> >> >> >> gestList = BaseGestlistPeer.doSelect(new Criteria()); >> >> >> >> this error arises >> >> >> >> java.lang.StringIndexOutOfBoundsException: String index out of >> range: 23 >> >> at java.lang.String.charAt(String.java:460) >> >> at >> >> org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653) at >> >> org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398) >> > >> > Just from the above, some one could conclude you are running >> > a 7.2.x postgresql driver. >> > >> > Make sure you havent messed up with the CLASSPATH. >> > Also after ensuring your 7.4.1 postgresql.jar is in the right >> location, make sure you are running a 7.4.1 backend. >> > >> >> >> >> I have searched all the lists and the answer i got is a jdbc driver >> issue. Please let me know what i need to for getting around with >> this problem. This happens only with this method in the whole >> application. But the same method works when i did not use the new >> torque 3.1 and everything happens smoothly.Please let me know if >> there is a way to configure jdbc with torque or any other >> configuration issue for postgres you know might have been creating >> this problem. I am having one heck of a night mare wid this issue. >> >> >> >> >> >> thanks >> >> >> >> --Mohan >> >> >> >> >> >> >> >> ---(end of >> >> broadcast)--- TIP 3: if posting/reading >> through Usenet, please send an appropriate >> >> subscribe-nomail command to [EMAIL PROTECTED] so that >> >> your message can get through to the mailing list cleanly >> >> >> > >> > -- >> > -Achilleus >> > >> > >> > ---(end of >> broadcast)--- TIP 8: explain analyze is your >> friend >> >> >> > > -- > -Achilleus > > > - To > unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
