Re: [SQL] sessions and prepared statements
On Jun 15, 2006, at 11:49 AM, chester c young wrote: in PHP for example, where there are multiple sessions and which you get is random: how do you know if the session you're in has prepared a particular statement? and/or how do you get a list of prepared statements? last, is there any after login trigger that one could use to prepare statements the session would need? or is this a dumb idea? If you are using pooled connections, I don't think there is a reasonable way you could managed prepared statements across requests. You'll probably want to just prepare the ones you need for the current request and discard them when the request ends. I have a short article where you might find some useful information for managing prepared statements: http://pgedit.com/resource/php/pgfuncall You might also post your question to PostgreSQL PHP list -- probably more PHP expertise there. Best, John John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] sessions and prepared statements
On Fri, 2006-06-16 at 08:27 -0400, John DeSoi wrote: > On Jun 15, 2006, at 11:49 AM, chester c young wrote: > > > in PHP for example, where there are multiple sessions and which you > > get is random: > > > > how do you know if the session you're in has prepared a particular > > statement? > > > > and/or how do you get a list of prepared statements? > > > > last, is there any after login trigger that one could use to > > prepare statements the session would need? or is this a dumb idea? > > If you are using pooled connections, I don't think there is a > reasonable way you could managed prepared statements across requests. > You'll probably want to just prepare the ones you need for the > current request and discard them when the request ends. Temporary tables. BEGIN; SAVEPOINT; SELECT * FROM temporary_prepared_statement; ROLLBACK TO SAVEPOINT < on failure>; CREATE TEMPORARY TABLE temporary_prepared_statement ...; COMMIT; Now you have a place to store and retrieve prepared connection state for the lifetime of the database backend provided PHP doesn't remove temporary tables on the connection. > I have a short article where you might find some useful information > for managing prepared statements: > > http://pgedit.com/resource/php/pgfuncall > > You might also post your question to PostgreSQL PHP list -- probably > more PHP expertise there. > > Best, > > John > > > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- ---(end of broadcast)--- TIP 1: 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] sessions and prepared statements
On Fri, Jun 16, 2006 at 08:55:16AM -0400, Rod Taylor wrote:
> BEGIN;
> SAVEPOINT;
> SELECT * FROM temporary_prepared_statement;
> ROLLBACK TO SAVEPOINT < on failure>;
> CREATE TEMPORARY TABLE temporary_prepared_statement ...;
> COMMIT;
>
> Now you have a place to store and retrieve prepared connection state for
> the lifetime of the database backend provided PHP doesn't remove
> temporary tables on the connection.
This doesn't help today, but 8.2 will have a pg_prepared_statements
view.
http://archives.postgresql.org/pgsql-committers/2006-01/msg00143.php
http://developer.postgresql.org/docs/postgres/view-pg-prepared-statements.html
test=> PREPARE stmt (integer) AS SELECT * FROM foo WHERE x = $1;
test=> \x
Expanded display is on.
test=> SELECT * FROM pg_prepared_statements;
-[ RECORD 1 ]---+--
name| stmt
statement | PREPARE stmt (integer) AS SELECT * FROM foo WHERE x = $1;
prepare_time| 2006-06-16 07:07:41.682999-06
parameter_types | {integer}
from_sql| t
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Repetitive code
Aaron Bono wrote: Each of your queries has the filter xxx >= $dt where the xxx is the first column in each select. You could simplify the query by turning the unioned selects into a sub-query and then putting the $dt filter in the outer query. It would probably have to be two subqueries unless I can find a way to merge the differences between new and changed rows. I don't know if this will cause performance problems though. If PostgreSQL completes the inner query before filtering by your $dt you may be better off leaving the $dt filters where they are. The query is only run a few times a week so performance is largely not a concern. I'm trying to simplify it to make adding tables less cumbersome (as a separate effort, the schema may be modified to normalize it, e.g., topic joins to entry via subject_id and actor_id and a subject and actor can also appear in topic_entry's topic_id). I know Oracle has materialized views. Does PostgreSQL also have materialized views? If so, you could get great performance from your views AND simplify your SQL. AFAIK PostgreSQL does not support materialized views but it's interesting that you mention that because in essence the query is used to materialize a view, i.e., it's part of an INSERT / SELECT into a table which is then joined back to the other tables to construct a web page as well as an RSS feed. Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] concurrency problem
Dear all, I tried the lock table option today. yes it works fine when saving simultaneously. but after two or three times even when all of us close our application, the lock exists there in the database. when we run the select query from other db tool it hangs up.
our code looks like Statement stmt = con.createStatement(); while(true){ try{ stmt.execute("begin"); stmt.execute("lock table rcp_patient_visit_monitor");
break; }catch(SQLException e){ stmt.execute("commit"); } } psSave.executeUpdate(); //psSave is a prepared statement
stmt.execute("commit"); if saved then con.commit(); is called if not saved then con.rollback(); is called First we run in two system, we did not faced the problem, then we run in three system the record is saved, after that we simulate the run once again, we face the problem,
i could not predict the problem precisely - what might be the problem. thanks in advance- Show quoted text -
On 6/16/06, Richard Huxton wrote: sathish kumar shanmugavelu wrote:> INSERT INTO rcp_patient_visit_monitor (> entry_no, patient_id, visit_date, is_newpatient,
> visit_type, is_medical,> is_review, is_labtest, is_scan, is_scopy, is_xray,> weight, height)> VALUES ((SELECT coalesce(max(entry_no)+1, 1) FROM> rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
>> The point to note here is the select statement which gets the max> entry_no and adds one to it and save the new value. entry_no is the primary> key of the above table.>> Now i run the same program (different instance) from two systems, save
> the form simultaneously, only one entry is saved, in the other system the> error says - duplicate key violates.
BEGIN;LOCK TABLE ...INSERT ...COMMIT;
You'll need to handle possible errors where one client fails to get a lock and times out. It won't happen often, but you do need to considerthe option.
-- Richard Huxton Archonet Ltd
-- Sathish Kumar.SSpireTEK
Re: [SQL] concurrency problem
I would use a BIGSERIAL for the ID. It simplifies your inserts, you don't have to mess with any locking and the sequence is maintained for you outside your transaction so two transactions can do inserts without stepping on each other.
This is how I handle auto generated numbers.The only downside is if an insert fails for some reason - then a number will be skipped. You would have to have some really restrictive requirements for this to matter though.
-Aaron BonoOn 6/15/06, sathish kumar shanmugavelu <[EMAIL PROTECTED]
> wrote:Dear group, Let me explain my issue. We use Database -
postgresql-8.1 JDBC Driver - postgresql-8.1-407.jdbc3.jar Java - jdk1.5 The default transaction isolation level is - Read Committed
Auto Commit is false In our application we used a single connection object. We open the connection in the MDI form and close it only when the MDI closes , simply when the application closes.
I give a insert statment like
INSERT INTO rcp_patient_visit_monitor ( entry_no, patient_id, visit_date, is_newpatient, visit_type, is_medical, is_review, is_labtest, is_scan, is_scopy, is_xray, weight, height)
VALUES ((SELECT coalesce(max(entry_no)+1, 1) FROM rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?) The point to note here is the select statement which gets the max entry_no and adds one to it and save the new value. entry_no is the primary key of the above table.
Now i run the same program (different instance) from two systems, save the form simultaneously, only one entry is saved, in the other system the error says - duplicate key violates. If i use the transaction level - Serializable - again one entry is saved. Only on closing this application (closing the connection) the application running in other system is getting saved.
If i lock the table and create a transaction - by sending the commands con.createStatement().executeUpdate("begin"); con.createStatement().executeUpdate("lock table rcp_patient_visit_monitor");
int rows = psSave.executeUpdate(); con.createStatement().executeUpdate("commit"); The form in one system is saved, in another system an error says - ' Deadlock detected .'
When i test the above said commands in dbvisualizer from two different systems , it works, but here it does not. why. how to solve this concurrency problem.Thanks in advance,
--
Sathish Kumar.SSpireTEK
Re: [SQL] Repetitive code
> AFAIK PostgreSQL does not support materialized views but it's > interesting that you mention that because in essence the query is used > to materialize a view, i.e., it's part of an INSERT / SELECT into a > table which is then joined back to the other tables to construct a web > page as well as an RSS feed. This thread: http://archives.postgresql.org/pgsql-performance/2006-06/msg00324.php mentions the use of materialized views in postgresql. See: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html However, I am not sure if this link shows how postgresql supports materialized views or if it just shows how to simulate a materialized view with procedural code. Either way, I thought it might be of interest to you. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] concurrency problem
>INSERT INTO rcp_patient_visit_monitor (
>entry_no, patient_id, visit_date,
> is_newpatient,
> visit_type, is_medical,
>is_review, is_labtest, is_scan,
> is_scopy, is_xray,
> weight, height)
>VALUES ((SELECT
> coalesce(max(entry_no)+1, 1) FROM
>
rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
You are only working on one table so you sholdn't have
to manage a transaction or deal with explicit locking.
Just let the database handle this for you with a
sequence. Your concurrency issues will disappear.
1) create a sequence:
create sequence entry_no_sequence
2) set the new sequence's value to your table's
current entry_no value (n):
select setval('entry_no_sequence',n)
3) recreate your table so that the entry_no will get
it's value from calling nextval() on your new
sequence:
entry_no integer not null default
nextval('entry_no_sequence')
Thereafter, when an insert is made on your table, the
enry_no field will get its value from the sequence and
the sequence will be incremented. You would then drop
entro_no from your insert statement and it would
become something like:
INSERT INTO rcp_patient_visit_monitor (
patient_id, visit_date, is_newpatient, visit_type,
is_medical, is_review,
is_labtest, is_scan, is_scopy, is_xray, weight,
height)
VALUES (?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Repetitive code
Richard Broersma Jr <[EMAIL PROTECTED]> writes: > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html > However, I am not sure if this link shows how postgresql supports > materialized views or if it just shows how to simulate a materialized > view with procedural code. The latter. PG doesn't (currently) have direct support for materialized views, but it has some tools you can get the effect with. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] sessions and prepared statements
If you are using pooled connections, doesn't PostgreSQL manage the prepared statements for you? I would expect that, once I prepare a statement, if I attempt to do it again, PostgreSQL would say, "righty then, already done it, here you go". Then again, I don't know what PostgreSQL does under the covers.
This seems like a lot of work to squeek out a small amount of efficiency. Would it really give you much value?-Aaron BonoOn 6/16/06, Michael Fuhr
<[EMAIL PROTECTED]> wrote:On Fri, Jun 16, 2006 at 08:55:16AM -0400, Rod Taylor wrote:
> BEGIN;> SAVEPOINT;> SELECT * FROM temporary_prepared_statement;> ROLLBACK TO SAVEPOINT < on failure>;> CREATE TEMPORARY TABLE temporary_prepared_statement ...;> COMMIT;
>> Now you have a place to store and retrieve prepared connection state for> the lifetime of the database backend provided PHP doesn't remove> temporary tables on the connection.This doesn't help today, but
8.2 will have a pg_prepared_statementsview.http://archives.postgresql.org/pgsql-committers/2006-01/msg00143.php
http://developer.postgresql.org/docs/postgres/view-pg-prepared-statements.htmltest=> PREPARE stmt (integer) AS SELECT * FROM foo WHERE x = $1;test=> \xExpanded display is on.test=> SELECT * FROM pg_prepared_statements;
-[ RECORD 1 ]---+--name| stmtstatement | PREPARE stmt (integer) AS SELECT * FROM foo WHERE x = $1;prepare_time| 2006-06-16 07:07:
41.682999-06parameter_types | {integer}from_sql| t--Michael Fuhr
Re: [SQL] concurrency problem
I know this is a Java issue but I would recommend something more like:
Statement stmt = con.createStatement(); try { stmt.execute("begin"); stmt.execute("lock table rcp_patient_visit_monitor"); psSave.executeUpdate(); //psSave is a prepared statement
stmt.execute("commit"); con.commit(); is called } catch (Exception e) { con.rollback(); is called } finally { stmt.close(); }
On 6/16/06, sathish kumar shanmugavelu <[EMAIL PROTECTED]> wrote:
Dear all, I tried the lock table option today. yes it works fine when saving simultaneously. but after two or three times even when all of us close our application, the lock exists there in the database. when we run the select query from other db tool it hangs up.
our code looks like Statement stmt = con.createStatement(); while(true){ try{ stmt.execute("begin"); stmt.execute("lock table rcp_patient_visit_monitor");
break; }catch(SQLException e){ stmt.execute("commit"); } } psSave.executeUpdate(); //psSave is a prepared statement
stmt.execute("commit"); if saved then con.commit(); is called if not saved then con.rollback(); is called
Re: [SQL] listen_addresses = '*' ok, specific address(es) no
Thank you Tom Lane and Phillip Smith, you've answered my questions. --Geoff On Jun 15, 2006, at 22:46, Tom Lane wrote: Geoffrey Knauth <[EMAIL PROTECTED]> writes: Andrew Sullivan wrote: Well, do you actually have an interface with that address? I think I do, in that the machine's wireless interface is set up with a 192.168.1.x/24 address and 1.33 is on the same subnet. Or maybe I'm misunderstanding. I thought the purpose of listen_addresses was to allowing incoming connections only from listed addresses. You're misunderstanding then. What listen_addresses can bind to is IP addresses of *your own machine*. For example, if you bind to only 127.0.0.1 then only local loopback connections will work. Binding to just one external IP address is only interesting if your machine has more than one such address; then it prevents connections that're coming in through one of the other addresses. The right way to limit incoming connections to only come *from* particular IP addresses is to use pg_hba.conf. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Repetitive code
I haven't stared at your query as long as you have so I may have missed something but it looks like in all the selects you are combining the first column in the select is the column you filter on. So the the outer query doesn't have to know wiether it is a new or changed row:
SELECT * FROM ( SELECT created as my_date, topic_id, 0, 0, 0, 0 FROM topic WHERE page_type IN (1, 2) UNION SELECT updated as my_date, topic_id, 1, 0, 0, 0 FROM topic WHERE date_trunc('day', updated) != created
AND page_type IN (1, 2) UNION SELECT e.created as my_date, subject_id, 0, 1, entry_id, subject_type FROM entry e, topic WHERE subject_id = topic_id AND page_type IN (1, 2) UNION
SELECT e.created as my_date, actor_id, 0, 1, entry_id, actor_type FROM entry e, topic WHERE actor_id = topic_id AND page_type IN (1, 2) UNION SELECT e.updated as my_date, subject_id, 1, 1, entry_id, subject_type
FROM entry e, topic WHERE date_trunc('day', e.updated) != e.created AND subject_id = topic_id AND page_type IN (1, 2) UNION SELECT e.updated as my_date, actor_id, 1, 1, entry_id, actor_type
FROM entry e, topic WHERE date_trunc('day', e.updated) != e.created AND actor_id = topic_id AND page_type IN (1, 2) UNION SELECT e.created as my_date, e.topic_id, 0, 1, entry_id, rel_type
FROM topic_entry e, topic t WHERE e.topic_id = t.topic_id AND page_type IN (1, 2) UNION SELECT e.updated as my_date, e.topic_id, 1, 1, entry_id, rel_type FROM topic_entry e, topic t
WHERE e.topic_id = t.topic_id AND date_trunc('day', e.updated) != e.created AND page_type IN (1, 2)) my_unionwhere my_union.my_date >= $dtI would almost be tempted to create a view for each small query and name them something meaningful and then another view that does the union. It would make the queries easier to understand at least (self documented).
-AaronOn 6/16/06, Joe <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:> Each of your queries has the filter xxx >= $dt where the xxx is the> first column in each select. You could simplify the query by turning> the unioned selects into a sub-query and then putting the $dt filter in
> the outer query.It would probably have to be two subqueries unless I can find a way tomerge the differences between new and changed rows.
Re: [SQL] Repetitive code
Aaron Bono wrote:
I haven't stared at your query as long as you have so I may have missed
something but it looks like in all the selects you are combining the
first column in the select is the column you filter on. So the the
outer query doesn't have to know wiether it is a new or changed row:
SELECT * FROM (
SELECT created as my_date, topic_id, 0, 0, 0, 0 FROM topic
WHERE page_type IN (1, 2)
UNION
[snip]
SELECT e.updated as my_date, e.topic_id, 1, 1, entry_id, rel_type
FROM topic_entry e, topic t
WHERE e.topic_id = t.topic_id
AND date_trunc('day', e.updated) != e.created
AND page_type IN (1, 2)
) my_union
where my_union.my_date >= $dt
Thanks Aaron. That does look like a great solution, overlooked since
I'm not that familiar with SELECTs in the FROM clause. It may even make
it possible to discard the interim table and do the web page/RSS feed
directly from the view.
I would almost be tempted to create a view for each small query and name
them something meaningful and then another view that does the union. It
would make the queries easier to understand at least (self documented).
That sounds like a good idea too because schema changes would be
somewhat insulated by the layered views.
Best regards,
Joe
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Repetitive code
"Aaron Bono" <[EMAIL PROTECTED]> writes: > I haven't stared at your query as long as you have so I may have missed > something Likewise I'm perhaps speaking too quickly, but at the risk of making a fool of myself: you should perhaps realize that UNION has to do a fair amount of work to eliminate duplicates. If you know the rows are going to be distinct or if you don't care about duplicates you can save a lot of time by using UNION ALL instead. -- greg ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] sessions and prepared statements
in PHP for example, where there are multiple sessions and which you get is random: how do you know if the session you're in has prepared a particular statement? and/or how do you get a list of prepared statements? last, is there any after login trigger that one could use to prepare statements the session would need? or is this a dumb idea? Ahem, if you're concerned about performance, you probably... - don't really use PHP ? Or at least use eaccelerator or some form of compiled code caching ? - use a lighttpd with php running as a fastcgi process ? The second option ensures that the PHP code execution is less likely to be interrupted by a client disconnection ; however it can still crash or exceed the time or memory limit. Thus, when using the persistent connections of PHP : - use pg_pconnect to connect, this will give you a connection from a pool - Make sure the connection does not contain a pending transaction, by chosing one of these options : - Trust the PHP designers (ahem) - Issue a ROLLBACK as your first query - register_shutdown_function( 'pg_query', 'ROLLBACK' ); This issues a ROLLBACK as the last query even if your script is interrupted, but not if the PHP interpreter crashes (happens...). As for preparing the statements only once, I would do the following : Have a SQL script which prepares all statements, creates temp tables etc, and whose last command is : PREPARE connection_status_test AS SELECT 1; Then, you can start your script by EXECUTE connection_status_test; if it fails complaining that the prepared statement is not found, execute the SQL script ; else resume normal operations. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] keeping last 30 entries of a log table
I need to write a function which inserts a log entry in a log table and only keeps the last 30 records. I was thinking of using a subquery similar to the following: insert into log (account_id, message) values (1, 'this is a test); delete from log where account_id = 1 and id not in ( select id from log where account_id = 1 order by timestamp desc limit 30); I'm wondering if there is a more performance oriented method of doing the delete that I'm not thinking of. -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 1: 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] concurrency problem
Dear group
Its my mistake that i did not reveal the whole scenario. Actually within that begin and commit, i insert in 10 tables. The above said table is the key table. I fetch the consultatioin_no and add one to it, i should know this consultation_no to save the other 10 tables. because i use this number as foreign key in other tables. Also in my program, the data for that 10 tables are collected in different java classes and save coding is also there. I initiate this save coding for all the 10 forms in the one form (some main form).
so if any error occurs i have to roll back the whole transaction. Is there any method to release the lock explicitly, where postgres store this locking information. Is both stmt.execute
("commit"); con.commit(); are both same. should i have to call con.commit() method after stmt.execute("commit") Now Iam also thinking to use sequence. but please clear the above doubts.
-- Sathish Kumar.SSpireTEKOn 6/16/06, Ash Grove <[EMAIL PROTECTED]> wrote:
>INSERT INTO rcp_patient_visit_monitor (>entry_no, patient_id, visit_date,> is_newpatient,> visit_type, is_medical,>is_review, is_labtest, is_scan,
> is_scopy, is_xray,> weight, height)>VALUES ((SELECT> coalesce(max(entry_no)+1, 1) FROM>rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
You are only working on one table so you sholdn't haveto manage a transaction or deal with explicit locking.Just let the database handle this for you with asequence. Your concurrency issues will disappear.
1) create a sequence:create sequence entry_no_sequence2) set the new sequence's value to your table'scurrent entry_no value (n):select setval('entry_no_sequence',n)3) recreate your table so that the entry_no will get
it's value from calling nextval() on your newsequence:entry_no integer not null defaultnextval('entry_no_sequence')Thereafter, when an insert is made on your table, theenry_no field will get its value from the sequence and
the sequence will be incremented. You would then dropentro_no from your insert statement and it wouldbecome something like:INSERT INTO rcp_patient_visit_monitor (patient_id, visit_date, is_newpatient, visit_type,
is_medical, is_review,is_labtest, is_scan, is_scopy, is_xray, weight,height)VALUES (?,current_timestamp,?,?,?,?,?,?,?,?,?,?)__Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection aroundhttp://mail.yahoo.com---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
-- Sathish Kumar.SSpireTEK
