[SQL] Making dirty reads possible?
Hi co-fanatics. I am working on a small prove of concept, and am running into a small obstacle. (the prove of concept showing, amongs other things, that doing calculations on a database works well, and that it is possible to let it run 'jobs') Considder the following stored procedure: For reasons of size the complete code is located on my site: http://www.vankoperen.nl/concepts/postgresql/primer/index.html It generates prime numbers for (parameter) odd numbers starting from the biggest known prime in the primes table. The "controller" table makes it possible to abort execution, something wich can be handy if you did a 'select primer(1000);' I am just getting to grips with the read cashing and the way to circumvent it (using the EXECUTE function) so as to read data each time and thus react to the newest data, especialy the data in the "controller" table in this case. Now what does not seem to work is the opposite thing: i can not, from the console etc, read the new data as the function is generating it. If i 'SELECT count(*);' at the start, or near the end of the running function, it always returns the same. Only when the function is finished it commits and the external select returns the new and correct value. To monitor the function's progress (and for other reasons too, wich are not important in this concept yet) i realy want to read either the UNCOMMITTED data. Or some way to COMMIT it during the functions execution, but currently only the whole function can be regarded as a transaction, and nested transactions is not (yet) supported. Some digging in the mailinglist archives pointed to isolation levels. Apparently 'dirty reads' in theory: [quote http://archives.postgresql.org/pgsql-hackers/2004-08/msg01417.php ] It's only allowed when the transaction is in READ UNCOMMITTED isolation level. Something Postgres doesn't currently support. In fact I'm not aware of any SQL database that supports it, though I'm sure there's one somewhere. You wouldn't normally want to use such a thing, but it could be useful for, for example, seeing what progress a transaction has made for a UI progress meter. [/quote] But not possible for real at the moment? So, summarising: - Nested transactions is not (yet) supported - READ UNCOMMITTED isolation level is not (yet) supported - the EXECUTE plpgsql construct does not circumvent the transaction Is there a way around this? Regards, Ellert. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Set Returning Function problem
Hi,
I noticed what i think is a weird behaviour with SRFs. When I use them
in a SELECT clause, and the SRF returns no rows, the query returns no
rows too, when I think it should return an a null column. I'm posting an
example below:
test=> CREATE TABLE foo (cod SERIAL, bar TEXT);
NOTICE: CREATE TABLE will create implicit sequence "foo_cod_seq" for
"serial" column "foo.cod"
CREATE TABLE
test=>
test=> INSERT INTO foo (bar) VALUES ('asdasd');
INSERT 60702758 1
test=> CREATE OR REPLACE FUNCTION srf_foo(INTEGER)
test-> RETURNS setof foo
test-> AS '
test'> SELECT * FROM foo WHERE cod = $1;
test'> ' LANGUAGE 'SQL';
CREATE FUNCTION
test=>
test=> SELECT cod, (srf_foo(cod)).bar
test-> FROM foo;
cod | bar
-+
1 | asdasd
(1 record)
test=>
test=> SELECT cod, (srf_foo(50)).bar
test-> FROM foo;
cod | bar
-+-
(0 records)
Thanks in advance,
--
+---+
| Alvaro Nunes MeloAtua Sistemas de Informacao |
| [EMAIL PROTECTED]www.atua.com.br |
|UIN - 42722678(54) 327-1044|
+---+
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Set Returning Function problem
Alvaro Nunes Melo <[EMAIL PROTECTED]> writes: > I noticed what i think is a weird behaviour with SRFs. When I use them > in a SELECT clause, and the SRF returns no rows, the query returns no > rows too, when I think it should return an a null column. Why would you think that? The behavior is exactly right as-is. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Making dirty reads possible?
[EMAIL PROTECTED] writes: > But not possible for real at the moment? > > So, summarising: > - Nested transactions is not (yet) supported > - READ UNCOMMITTED isolation level is not (yet) supported > - the EXECUTE plpgsql construct does not circumvent the transaction Well nested transactions are in 8.0 but I don't think they help you much. I find I've been stymied using server-side functions for large batch jobs for pretty much the same reason. I find it works better and it's more flexible to write client-side programs in the language of my choice that connect to the database and do the batch jobs. They can output progress logs or keep information about their progress in some shared space. They can also control the transaction more freely committing in the middle of the job if it's safe. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Making dirty reads possible?
Guys, > I find I've been stymied using server-side functions for large batch jobs > for pretty much the same reason. I find it works better and it's more > flexible to write client-side programs in the language of my choice that > connect to the database and do the batch jobs. FWIW, Gavin Sherry is working on CREATE PROCEDURE for 8.1, which may include some ability to have multiple-transaction procedures. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Making dirty reads possible?
Hi co-fanatics. I am working on a small prove of concept, and am running into a small obstacle. (the prove of concept showing, amongs other things, that doing calculations on a database works well, and that it is possible to let it run 'jobs') Considder the following stored procedure: For reasons of size the complete code is located on my site: http://www.vankoperen.nl/concepts/postgresql/primer/index.html It generates prime numbers for (parameter) odd numbers starting from the biggest known prime in the primes table. The "controller" table makes it possible to abort execution, something wich can be handy if you did a 'select primer(1000);' I am just getting to grips with the read cashing and the way to circumvent it (using the EXECUTE function) so as to read data each time and thus react to the newest data, especialy the data in the "controller" table in this case. Now what does not seem to work is the opposite thing: i can not, from the console etc, read the new data as the function is generating it. If i 'SELECT count(*);' at the start, or near the end of the running function, it always returns the same. Only when the function is finished it commits and the external select returns the new and correct value. To monitor the function's progress (and for other reasons too, wich are not important in this concept yet) i realy want to read either the UNCOMMITTED data. Or some way to COMMIT it during the functions execution, but currently only the whole function can be regarded as a transaction, and nested transactions is not (yet) supported). Some digging in the mailinglist archives pointed to isolation levels. Apparently 'dirty reads' in theory: [quote http://archives.postgresql.org/pgsql-hackers/2004-08/msg01417.php ] It's only allowed when the transaction is in READ UNCOMMITTED isolation level. Something Postgres doesn't currently support. In fact I'm not aware of any SQL database that supports it, though I'm sure there's one somewhere. You wouldn't normally want to use such a thing, but it could be useful for, for example, seeing what progress a transaction has made for a UI progress meter. [/quote] But not possible for real at the moment? So, summarising: - Nested transactions is not (yet) supported - READ UNCOMMITTED isolation level is not (yet) supported - EXECUTE does not circumvent the transaction Is there a way around this? Regards, Ellert. ---(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] MO: SQL Query Performance tips
Hi All, I have a partial table structure as below tbl_user_main user_id pk user_level references tbl_level user_class references tbl_class user_level references tbl_level tbl_level level_id pk level_name level_points tbl_weapon weapon_id pk weapon_name weapon_level references tbl_level weapon_class references tbl_class weapon_alignment references tbl_alignment weapon_cost tbl_class class_id pk class_name tbl_alignment alignment_id pk alignment_name tbl_user_weapon user_id references tbl_user weapon_id references tbl_weapon I want to know how many weapons a given user has of a particular class or alignment where the level of the weapons are equal or less than the players own level. For this I have developed the following SQL: 1 | select alignment.alignment as alignment, count(distinct(weapon.weapon_name)) as count from 2 | ( select * from tbl_alignment where alignment_id != 1 ) alignment, 3 | ( select * from tbl_weapon) weapon, 4 | ( select * from tbl_user_main where user_id = $user_id ) person 5 | where weapon.weapon_id IN ( 6 | select u.weapon_id 7 | from tbl_weapon u 8 | where u.weapon_level <= person.user_level 9 | and u.cost = 0 10| or u.weapon_id IN ( 11| select uu.weapon_id 12| from tbl_user_weapon uu 13| where uu.user_id = person.user_id 14| ) 15| ) 16| and alignment.alignment_id = weapon.weapon_alignment 17| group by alignment.alignment 18| order by alignment.alignment ASC; To clarify lines 5 through 15 - a weapon can be free or cost some amount. To track users that have bought a weapon there is the tbl_user_weapon table. Every purchase gets listed in there. The count must take into account all free weapons and weapons which the user has purchased. At the moment there are some 300 weapons. In the case above the "alignment" with id 1 is a catchall so I disregard it. $user_id can be any user_id from tbl_user_main. The result of a query such as this is along the lines of; alignment | count --+--- Shadow| 4 Heavenly | 6 This takes a long time to complete - circa 3 seconds. Which is fine when run one off - but it appears in a section of a website that will potentially be accessed a lot and I can see it causing a few issues in terms of table/row locking as the game app uses these tables a lot. ideally I need it to run a lot quicker. Can anyone see any ways to speed this up? I have considered views but these seem to just be a way of aliasing a query as opposed to the materialised views present in other RDBMS's. Short of creating another 3rd form table that has user_id | alignment_id | count Which would get updated upon each weapon purchase I cannot see a low overhead way of getting the data. Creating a table such as this would need to be a last resort as its maintenance will quickly become a headache due to the number of purchase routes in the project. Any help is much appreciated. Thanks, -- Michael Ossareh (M²) Technical Manager 12snap UK Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Postgesql 8 beta 5: ecpg and date type - Access violations
Hi,
There is an "access violation" caused by ecpgtypes
when using date fields and ecpg.
Everything was runnig ok with dates rangin from
2000 - 2004, but this acces violation
occurs when the date with th year 1968
appears.
Looking at my program, I can see that this happens
inside the SELECT into :data_admissao;
where "data_admissao" is a date type.
When I remove this field from the select, then it
works.
Here is the steps to reproduce the
error:
PostgreSQL 8.0.0 beta5
Windows XP Professional
MingW
Table:
CREATE TABLE funcionarios( id int4
NOT NULL, nome varchar(40) NOT NULL, data_admissao date NOT
NULL, funcao int4 NOT NULL, CONSTRAINT funcionarios_pkey
PRIMARY KEY (id)) WITHOUT OIDS;
Data:
INSERT INTO fpcadfun VALUES
(111,'SILVA','1968-01-19',10);
Program:
class funcionario { ... };
typedef std::vector
funcionario_type;
bool db_ler_funcionarios( funcionario_type
&funcs ){ funcs.clear();
EXEC SQL BEGIN DECLARE SECTION; int
id; VARCHAR nome[40]; date data_admissao; int
funcao; EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE func_cur CURSOR
FOR SELECT id, nome, data_admissao, funcao FROM funcionarios
ORDER BY id;
EXEC SQL OPEN func_cur; EXEC SQL
WHENEVER NOT FOUND DO break;
char *txt_adm;
while(true) { EXEC SQL FETCH NEXT
FROM func_cur INTO :id, :nome, :data_admissao,
:funcao;
txt_adm =
PGTYPESdate_to_asc(data_admissao); funcionario f( id, nome.arr,
txt_adm, funcao
); funcs.push_back(f); free(txt_adm); } EXEC
SQL CLOSE func_cur; return true;}
The access violation occurs at the bold line. I'm
using mingw to compile this
program.
Please fell free to ask for any other question or
comment about this error.
I hope that I could give my 2 cents for this
incredible RDMS.
Best Regards,
Paulo Assis
