Re: [SQL] setting the where clause
On 2009-06-10, johnf wrote: > Hi, > I'm am programming in python using the Dabo modules. www.dabodev.com if your > interested. Dabo is a framework that provides an easy way to build desktop > app's. To clear a data entry form. I have been setting the where clause > to "where 1=0". This of course retrieves 0 records and my form will display > nothing. It has been suggested that it might be better to set the where > clause to a primary key value that does not exist in the table "where PK >= -999". "where PK=NULL" is better as anything=NULL is never true. PK=-999 may be true sometimes. As Tom says PK=-999 causes postgres to look for a record that matches, PK=NULL doesn't cause needless search. that said if -999 is outside of the valid range for PK then the search will finish very quickly as at worst only a sigle btree page will need to be loaded. in order of preference. no query at all where FALSE where PK=NULL where PK=-999 (note that this one may not work) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Find periods for a given... action set?
I have a table where there are actinos for some user logged. It's part of the MPI system of some sort. For every user, action type and time of the action is logged. There are many action types but the ones which are of interest to me are BEGIN and END. I need to find the durations for all the periods between BEGINs and ENDs, for each user. Here is what the table looks like: CREATE TABLE actions ( user_id integer, action_mark character varying, action_time timestamp ) There are no PK constraints because those columns are excerpt from a action_log table, there is a message_id column which is a PK; user_id and action_mark are FKs to the users and actions tables. Now that I look at it, in the above table PK would be (user_id, action_time), but there are no PKs as for now :) Some example data: INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 12:00:00'); INSERT INTO actions VALUES (1, 'ACT01', '2009-02-02 12:01:22'); INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 12:02:01'); INSERT INTO actions VALUES (1, 'ACT02', '2009-02-02 13:10:00'); INSERT INTO actions VALUES (3, 'BEGIN', '2009-02-02 13:11:02'); INSERT INTO actions VALUES (1, 'END', '2009-02-02 13:21:01'); INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 14:01:01'); INSERT INTO actions VALUES (2, 'END', '2009-02-02 16:11:21'); INSERT INTO actions VALUES (1, 'ACT-1', '2009-02-02 17:13:31'); INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 18:11:12'); INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 18:12:21'); INSERT INTO actions VALUES (2, 'END', '2009-02-02 19:00:01'); INSERT INTO actions VALUES (1, 'END', '2009-02-02 19:10:01'); INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 10:11:01'); Now, for each user, i need to find all periods 'enclosed' with BEGIN/END action_type pairs. If I have a BEGIN and no END, than there is no period. So, if I have, for some user, BEGIN-END-BEGIN-END-BEGIN, there are only two periods. Also, if there are consecutive BEGINS, only the last one counts: BEGIN-END-BEGIN-BEGIN-BEGIN-END-BEGIN - again, only two periods. The results I would like to get look like this: user_id action_duration 1 01:21:01 1 00:57:40 2 04:09:20 2 00:48:49 User_id 3 has just the BEGIN - there is no period there, because I don't have and endpoint. Similarly, user_id 1 has BEGIN as the last action - just two periods for user_id 1, because last BEGIN denotes 'period in progress'. Also, user_id 1 has following actions happened, time ordered: BEGIN, END, BEGIN, BEGIN, END - only two periods, because 'third' BEGIN canceled second BEGIN and all the actions between second and third BEGIN. Now, using some imperative Python, Perl, C, whatever, it's not that complicated to get what I want, but I would realy like to have it solved within plain SQL :) So, if anyone has any suggestions, I would greatly appreciate them. Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Find periods for a given... action set?
Dobro Vece, smth like: SELECT a.user_id,a.action_mark,a.action_time,a.action_time- (select a2.action_time from actions a2 where a2.oid= (select a3.oid from actions a3 where a3.user_id=a.user_id and a3.action_mark='BEGIN' and a3.action_time I have a table where there are actinos for some user logged. It's part > of the MPI system of some sort. For every user, action type and time of > the action is logged. There are many action types but the ones which are > of interest to me are BEGIN and END. I need to find the durations for > all the periods between BEGINs and ENDs, for each user. > > Here is what the table looks like: > > CREATE TABLE actions ( > > user_id integer, > > action_mark character varying, > > action_time timestamp > > ) > > There are no PK constraints because those columns are excerpt from a > action_log table, there is a message_id column which is a PK; user_id > and action_mark are FKs to the users and actions tables. Now that I look > at it, in the above table PK would be (user_id, action_time), but there > are no PKs as for now :) > > Some example data: > > > > INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 12:00:00'); > > INSERT INTO actions VALUES (1, 'ACT01', '2009-02-02 12:01:22'); > > INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 12:02:01'); > > INSERT INTO actions VALUES (1, 'ACT02', '2009-02-02 13:10:00'); > > INSERT INTO actions VALUES (3, 'BEGIN', '2009-02-02 13:11:02'); > > INSERT INTO actions VALUES (1, 'END', '2009-02-02 13:21:01'); > > INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 14:01:01'); > > INSERT INTO actions VALUES (2, 'END', '2009-02-02 16:11:21'); > > INSERT INTO actions VALUES (1, 'ACT-1', '2009-02-02 17:13:31'); > > INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 18:11:12'); > > INSERT INTO actions VALUES (1, 'BEGIN', '2009-02-02 18:12:21'); > > INSERT INTO actions VALUES (2, 'END', '2009-02-02 19:00:01'); > > INSERT INTO actions VALUES (1, 'END', '2009-02-02 19:10:01'); > > INSERT INTO actions VALUES (2, 'BEGIN', '2009-02-02 10:11:01'); > > > Now, for each user, i need to find all periods 'enclosed' with BEGIN/END > action_type pairs. If I have a BEGIN and no END, than there is no > period. So, if I have, for some user, BEGIN-END-BEGIN-END-BEGIN, there > are only two periods. > Also, if there are consecutive BEGINS, only the last one counts: > BEGIN-END-BEGIN-BEGIN-BEGIN-END-BEGIN - again, only two periods. > > The results I would like to get look like this: > > user_id action_duration > > 1 01:21:01 > > 1 00:57:40 > > 2 04:09:20 > > 2 00:48:49 > > User_id 3 has just the BEGIN - there is no period there, because I don't > have and endpoint. Similarly, user_id 1 has BEGIN as the last action - > just two periods for user_id 1, because last BEGIN denotes 'period in > progress'. > > Also, user_id 1 has following actions happened, time ordered: BEGIN, > END, BEGIN, BEGIN, END - only two periods, because 'third' BEGIN > canceled second BEGIN and all the actions between second and third BEGIN. > > Now, using some imperative Python, Perl, C, whatever, it's not that > complicated to get what I want, but I would realy like to have it solved > within plain SQL :) > > So, if anyone has any suggestions, I would greatly appreciate them. > Smth > Mike > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] updateable/insertable view having left joined tables
Hi, I'm taking a first foray into writing rules, and am struggling with one for a view that has a left joined table: ------ CREATE TABLE shoes ( sh_id serial PRIMARY KEY, sh_name text, sh_avail integer ); CREATE TABLE shoelaces ( sl_id serial PRIMARY KEY, sh_id integer REFERENCES shoes, sl_name text ); INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh1', 2); INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh2', 0); INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh3', 4); INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh4', 3); INSERT INTO shoelaces (sh_id, sl_name) VALUES (1, 'sl1'); INSERT INTO shoelaces (sh_id, sl_name) VALUES (3, 'sl2'); SELECT * FROM shoes; sh_id | sh_name | sh_avail ---+-+-- 1 | sh1 |2 2 | sh2 |0 3 | sh3 |4 4 | sh4 |3 SELECT * FROM shoelaces; sl_id | sh_id | sl_name ---+---+- 1 | 1 | sl1 2 | 3 | sl2 -- We create a view that could be used to easily insert data into -- shoelaces table: CREATE VIEW shoe AS SELECT sh.sh_id, sh_name, sh_avail, sl_name FROM shoes sh LEFT JOIN shoelaces sl USING (sh_id); SELECT * FROM shoe; sh_id | sh_name | sh_avail | sl_name ---+-+--+- 1 | sh1 |2 | sl1 2 | sh2 |0 | 3 | sh3 |4 | sl2 4 | sh4 |3 | --- --- Say I want to update this view like: UPDATE shoe SET sl_name = 'sl3' WHERE sh_id = 2; The right (well, to me) thing to do would be to insert a row in shoelaces like this: INSERT INTO shoelaces (sh_id, sl_name) VALUES (2, 'sl3'); Of course, if the update involves a row that is already available from shoelaces, it would be an update on at least one of the tables, rather than an insert on shoelaces. I'm not sure how a rule to do this would look like, so any pointers would be appreciated. Thanks. -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Taking the cache out of the equation?
On Jun 9, 2009, at 10:51 AM, Rob Sargent wrote: Caching helps a *lot* and I'm thankful for that but I would like to take it out of the picture as I massage my queries for better performance. Naturally the first invocation of the query cannot take advantage of the cache and these queries would normally only be called once for the same target data.What tricks are there to flush, ignore, circumvent the caching boost? (Especially in the production environment.) Why on earth would you want your queries to always go to disk? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Taking the cache out of the equation?
On Sat, Jun 13, 2009 at 12:12 AM, Erik Jones wrote: > > On Jun 9, 2009, at 10:51 AM, Rob Sargent wrote: > >> Caching helps a *lot* and I'm thankful for that but I would like to take >> it out of the picture as I massage my queries for better performance. >> Naturally the first invocation of the query cannot take advantage of the >> cache and these queries would normally only be called once for the same >> target data. What tricks are there to flush, ignore, circumvent the >> caching boost? (Especially in the production environment.) > > Why on earth would you want your queries to always go to disk? I think he answered that in the original message -- to better represent the real workload. Unfortunately there isn't really a good answer. On Linux you can echo 1 > /proc/sys/vm/drop_caches but that doesn't affect the postgres shared buffers and worse, it does affect other buffers that probably would still be cached. The best answer is usually to build a test configuration large enough that it has similar cache effects as your production environment. Then test random values and repeat the test many times to avoid any random fluctuations. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] updateable/insertable view having left joined tables
The following seems to work, but I don't feel confident this is really the correct code: CREATE RULE shoe_upd AS ON UPDATE TO shoe DO INSTEAD ( UPDATE shoes SET sh_name = NEW.sh_name, sh_avail = NEW.sh_avail WHERE shoes.sh_id = NEW.sh_id; INSERT INTO shoelaces (sh_id, sl_name) SELECT New.sh_id, New.sl_name WHERE New.sl_name IS NOT NULL;); I could find out doing a number of tests, but it would be good to understand what is going on. -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
