[GENERAL] Good candidate query for window syntax?
Hello, I have a table defined as: CREATE TABLE demo AS ( id serial PRIMARY KEY, start_time timestamp without timezone, duration integer ) A sample data set I am working with is: start_time | duration | end_time -+--+- 2006-08-28 16:55:11 | 94 | 2006-08-28 16:56:45 2006-08-28 16:56:00 | 63 | 2006-08-28 16:57:03 2006-08-28 16:56:02 | 25 | 2006-08-28 16:56:27 2006-08-28 16:56:20 | 11 | 2006-08-28 16:56:31 2006-08-28 16:56:20 | 76 | 2006-08-28 16:57:36 2006-08-28 16:56:29 | 67 | 2006-08-28 16:57:36 2006-08-28 16:56:45 | 21 | 2006-08-28 16:57:06 2006-08-28 16:56:50 | 44 | 2006-08-28 16:57:34 2006-08-28 16:56:50 | 36 | 2006-08-28 16:57:26 2006-08-28 16:56:53 | 26 | 2006-08-28 16:57:19 2006-08-28 16:56:57 | 55 | 2006-08-28 16:57:52 2006-08-28 16:57:28 |1 | 2006-08-28 16:57:29 2006-08-28 16:57:42 | 17 | 2006-08-28 16:57:59 2006-08-28 16:57:46 | 28 | 2006-08-28 16:58:14 2006-08-28 16:58:25 | 51 | 2006-08-28 16:59:16 2006-08-28 16:58:31 | 20 | 2006-08-28 16:58:51 2006-08-28 16:58:35 | 27 | 2006-08-28 16:59:02 generated by the query: SELECT start_time, duration, to_timestamp((extract(epoch from start_time) + duration))::timestamp as end_time FROM demo ORDER BY start_time, duration, 3; My goal is: To find the maximum number of concurrent rows over an arbitrary interval. Concurrent is defined as overlapping in their duration. Example from the set above: Assume the desired interval is one day. Rows 1 and 2 are concurrent because row 2's start_time is within the duration of row 1. If you go through the set the max concurrency is 5 (this is a guess cause I did it visually and may have miscounted). I took a scan of how I tried to solve it manually and attached the image. I tried using timelines to visualize the start, duration, and end of each row then looked for where they overlapped. inline: intervals.jpeg My desired output set would be: max_concurrency | interval (in this case grouped by day) +- 5 | 2006-08-28 if the interval for this set were different, say 30 minutes, then I would expect to see something like: max_concurrency | interval + 0 | 2006-08-28 00:00:00 - 2006-08-28 00:29:59 0 | 2006-08-28 00:30:00 - 2006-08-28 00:59:59 0 | 2006-08-28 01:00:00 - 2006-08-28 01:29:59 ...continues. 0 | 2006-08-28 16:00:00 - 2006-08-28 16:29:59 5 | 2006-08-28 16:30:00 - 2006-08-28 16:59:59 I think that a query that involves a window could be used to solve this question as the documentation says: A window function call represents the application of an aggregate-like function over some portion of the rows selected by a query...the window function is able to scan all the rows that would be part of the current row's group according to the grouping specification I am hoping that someone with more experience could help devise a way to do this with a query. Thanks in advance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Good candidate query for window syntax?
Ok I have been flailing at trying to understand both syntax and concepts...I think I am moving forward as I have a query that returns a result...its just the wrong result SELECT count(*) OVER w as max_concurrency, start_time::date as interval FROM demo GROUP BY start_time::date, case when ( (start_time, to_timestamp((extract(epoch from start_time) + duration))::timestamp) OVERLAPS (start_time, to_timestamp((extract(epoch from start_time) + duration))::timestamp) ) = TRUE then 1 end WINDOW w AS ( PARTITION BY start_time::date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ); The results are : max_concurrency | interval -+-- 1 | 2006-08-28 1 | 2010-09-09 1 | 2010-09-10 (3 rows) The count is returning the count of the date, not if a rows interval overlaps another rows. Also I recognize that I really want the max count over the given interval. Any thoughts would be appreciated On Fri, Sep 10, 2010 at 9:40 AM, Ketema ket...@gmail.com wrote: On Sep 10, 9:08 am, jgo...@gmail.com (Jorge Godoy) wrote: Have you checked the OVERLAPS operator in the documentation? http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html -- Jorge Godoy jgo...@gmail.com On Fri, Sep 10, 2010 at 10:03, Ketema Harris ket...@gmail.com wrote: Hello, I have a table defined as: CREATE TABLE demo AS ( id serial PRIMARY KEY, start_time timestamp without timezone, duration integer ) A sample data set I am working with is: start_time | duration | end_time -+--+- 2006-08-28 16:55:11 | 94 | 2006-08-28 16:56:45 2006-08-28 16:56:00 | 63 | 2006-08-28 16:57:03 2006-08-28 16:56:02 | 25 | 2006-08-28 16:56:27 2006-08-28 16:56:20 | 11 | 2006-08-28 16:56:31 2006-08-28 16:56:20 | 76 | 2006-08-28 16:57:36 2006-08-28 16:56:29 | 67 | 2006-08-28 16:57:36 2006-08-28 16:56:45 | 21 | 2006-08-28 16:57:06 2006-08-28 16:56:50 | 44 | 2006-08-28 16:57:34 2006-08-28 16:56:50 | 36 | 2006-08-28 16:57:26 2006-08-28 16:56:53 | 26 | 2006-08-28 16:57:19 2006-08-28 16:56:57 | 55 | 2006-08-28 16:57:52 2006-08-28 16:57:28 |1 | 2006-08-28 16:57:29 2006-08-28 16:57:42 | 17 | 2006-08-28 16:57:59 2006-08-28 16:57:46 | 28 | 2006-08-28 16:58:14 2006-08-28 16:58:25 | 51 | 2006-08-28 16:59:16 2006-08-28 16:58:31 | 20 | 2006-08-28 16:58:51 2006-08-28 16:58:35 | 27 | 2006-08-28 16:59:02 generated by the query: SELECT start_time, duration, to_timestamp((extract(epoch from start_time) + duration))::timestamp as end_time FROM demo ORDER BY start_time, duration, 3; My goal is: To find the maximum number of concurrent rows over an arbitrary interval. Concurrent is defined as overlapping in their duration. Example from the set above: Assume the desired interval is one day. Rows 1 and 2 are concurrent because row 2's start_time is within the duration of row 1. If you go through the set the max concurrency is 5 (this is a guess cause I did it visually and may have miscounted). I took a scan of how I tried to solve it manually and attached the image. I tried using timelines to visualize the start, duration, and end of each row then looked for where they overlapped. My desired output set would be: max_concurrency | interval (in this case grouped by day) +- 5 | 2006-08-28 if the interval for this set were different, say 30 minutes, then I would expect to see something like: max_concurrency | interval + 0 | 2006-08-28 00:00:00 - 2006-08-28 00:29:59 0 | 2006-08-28 00:30:00 - 2006-08-28 00:59:59 0 | 2006-08-28 01:00:00 - 2006-08-28 01:29:59 ...continues. 0 | 2006-08-28 16:00:00 - 2006-08-28 16:29:59 5 | 2006-08-28 16:30:00 - 2006-08-28 16:59:59 I think that a query that involves a window could be used to solve this question as the documentation says: A window function call represents the application of an aggregate-like function over some portion of the rows selected by a query...the window function is able to scan all the rows that would be part of the current row's group according to the grouping specification I am hoping that someone with more experience could help devise a way
Re: [GENERAL] could you tell me this..?
Never kill -9. Use kill -INT, whatever signal num that is, 11? Sent from my iPhone On Aug 5, 2010, at 3:14 AM, 백승엽 syb...@seriends.com wrote: Hi. I am korean database engineer. my english skill is very poor. but i desire that you teach this situation. um... I am testing postgresql performance in these days; today, I found this situation. Session 1. - begin; delete from ; Session 2 - delete from ; thus, it occured row level locking. so. i killed Session 1's PID with kill -9 commands but. both session are crashed. is this normal ? or bug? could you tell me this situation whether normal or bug? please teach me about this. have a nice day. thank you for your help. From Seung yup. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PHP] Some undefined function errors
you might want to also check your architecture flags for your compiler. i ran into this problem on a mac when I was compiling php and by default it tries to make a 32 bit and 64 bit binary, but pg was compiled as 64 bit only. I found that the php compile did not fail, but i was missing some functions just like you. when I recompiled php only 64 bit everything was picked up. I found that watching the configure output gave indication as to whether or not certain functions will be picked up: PQescapeString PQunescapeBytea PQsetnonblocking PQcmdTuples PQoidValue PQclientEncoding PQparameterStatus PQprotocolVersion PQtransactionStatus PQexecParams PQprepare PQexecPrepared PQresultErrorField PQsendQueryParams PQsendPrepare PQsendQueryPrepared PQputCopyData PQputCopyEnd PQgetCopyData PQfreemem PQsetErrorVerbosity PQftable PQescapeStringConn PQescapeByteaConn pg_encoding_to_char If you see these thigns set to NO even after PostgreSQL support says yes, then you know you wont be getting all the functions. Ketema J. Harris ket...@ketema.net ket...@jabber.ketema.net (Jabber) http://www.ketema.net ket...@voip.ketema.net - VoIP 407-459-4809- main 603-413-2882- fax On May 20, 2010, at 12:26 PM, Ashley Sheridan wrote: On Thu, 2010-05-20 at 09:21 -0700, Giancarlo Boaron wrote: Hi Jim. I think it depends on each server configuration but I checked in my server and I don't have the 'pgsql.so' and 'psql.ini' files and neither the 'www' directory. My Apache version is 2.2.15 and my root directory is /usr/local/apache2/htdocs. Thank you. --- Em qui, 20/5/10, Jim Lucas li...@cmsws.com escreveu: De: Jim Lucas li...@cmsws.com Assunto: Re: [PHP] Some undefined function errors Para: Giancarlo Boaron gboa...@yahoo.com.br Cc: pgsql-general@postgresql.org, php-gene...@lists.php.net, pgsql-nov...@postgresql.org Data: Quinta-feira, 20 de Maio de 2010, 13:04 Giancarlo Boaron wrote: Hi all. Recently, I wrote an email about the problem I was having with some Postgres functions that when those functions were called, I received the following error: Call to undefined function function_name. After some answers, I decided to rebuild a brand new linux virtual machine with Apache + PHP + Postgres, but I still get this annoying error messege with some functions like pg_prepare() and pg_escape_string(). I compiled Postgres with --without-readline option. I compiled PHP with --with-apxs2=/usr/local/apache2/bin/apxs and --with-pgsql=/usr/local/pgsql/ And the compilation process has no errors. What am I doing wrong? Do I have to change something in php_config.h file? If so, what do I have to change? Thank you. Depending on what version of apache you are running, you need to make sure that you are loading the pgsql.so file or have the psql.ini file in your /var/www/conf/php5/ directory. This way it knows to load pg. -- Jim Lucas Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them. Twelfth Night, Act II, Scene V by William Shakespeare I've generally found these sorts of things to be in /etc/php, /etc/php5 or something similar. Within /etc you should see directories for both Apache and PHP. If you have shell access to the machine, try calling a whereis command, which might give you a better idea of where things are kept in your system. Thanks, Ash http://www.ashleysheridan.co.uk
[GENERAL] Grouping Question
I have a table defined as CREATE TABLE mytable ( carrier varchar, start_time timestamp with time zone, call_date date, cost numeric, call_length numeric ) I want to create a query that will generate a the following columns: carrier, week, sum(call_length) as totallength, sum(cost) as total_cost from mytable group by carrier, (WHAT HERE?) order by week, carrier week is defined as a date range so something like 7/6/2009 - 7/13/2009 I would need the timestamps to be grouped into 7 day intervals starting from the first one and moving through the table. is this possible in a single query or would I have to write a function ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Grouping Question
OK that worked, but now how do I get the integer returned by extract into a human friendly string like 7/1 - 7/8 or something similar ? On Jul 31, 2009, at 12:37 PM, Rob Wickert wrote: You would use some of postgresql built in date functions to extract the week from the date and group by that i.e. select carrier, extract(week from start_time) as week, sum(call_length) as totallength, sum(cost) as total_cost from mytable group by carrier, extract(week from start_time) order by week, carrier you'll probably want to extract the year as well so dates weeks from alternate years don't get merged together Ketema Harris wrote: I have a table defined as CREATE TABLE mytable ( carrier varchar, start_time timestamp with time zone, call_date date, cost numeric, call_length numeric ) I want to create a query that will generate a the following columns: carrier, week, sum(call_length) as totallength, sum(cost) as total_cost from mytable group by carrier, (WHAT HERE?) order by week, carrier week is defined as a date range so something like 7/6/2009 - 7/13/2009 I would need the timestamps to be grouped into 7 day intervals starting from the first one and moving through the table. is this possible in a single query or would I have to write a function ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why I cannot call a function from within an SQL function?
Use dollar quoting around your fiction body I'd double up on the single quotes around the dash Sent from my iPhone On Feb 20, 2009, at 8:14 AM, Eus e...@member.fsf.org wrote: Hi Ho! Sorry, let me revise the query a bit. I copied and pasted the original one from another big query. --- On Fri, 2/20/09, Eus e...@member.fsf.org wrote: The following query works well: select count (*) from item_audit as ia where audit_ts = '2008-05-30 00:00:00' and audit_ts = '2008-10-30 00:00:00' and 'wst' != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = ia.status ) But, when I transform it into the following SQL function, the function cannot be created barking: ERROR: syntax error at or near - LINE 6:and $1 != (select split_part(category, '-', 2) create or replace function get_I(text, timestamp, timestamp) returns bigint as 'select count (*) from item_audit as ia where audit_ts = $2 and audit_ts = $3 and $1 != (select split_part(category, '-', 2) from description where split_part(category, '-', 1) = 'item' and shorthand = ia.status ) ' language sql; What's wrong? Thank you. Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] unable to assign value to composite column in trigger
The following is a snippet from a trigger i am using: _mycompositeType.user_id = (OLD).mycompositeType.user_id; ...do some stuff... --notice that assigning from the trigger record works but later on... (new).mycompositeType.transaction_id := _transaction_id; Fails with syntax error at or near ( I have tried removing the () and it fails with syntax error at or near new non composite column can be assigned to in the trigger just fine: new.other_columm := 5; --gives no issues How can a composite column be assigned to inside a trigger? Thanks Ketema J. Harris www.ketema.net ket...@ketema.net ketemaj on iChat -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unable to assign value to composite column in trigger
On Dec 18, 2008, at 4:19 PM, Ketema Harris wrote: The following is a snippet from a trigger i am using: _mycompositeType.user_id = (OLD).mycompositeType.user_id; ...do some stuff... --notice that assigning from the trigger record works but later on... (new).mycompositeType.transaction_id := _transaction_id; Fails with syntax error at or near ( I have tried removing the () and it fails with syntax error at or near new non composite column can be assigned to in the trigger just fine: new.other_columm := 5; --gives no issues How can a composite column be assigned to inside a trigger? Thanks Ketema J. Harris www.ketema.net ket...@ketema.net ketemaj on iChat -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general I have answered my own question. It appears that any composite column elements in the NEW and OLD variables within a trigger can be read from using the () accessor syntax, but not assigned to. What I had to do was create a variable of the composite type and then assign the whole column. EX: _var mycompositeType; _var.element := 1 _var.element2 := 2 new.compositeCol := _var It seems a little strange that the same accessor syntax can't be used on both sides of the assignment operator, but this method solved my problem. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Composite type evaluates to Null if any element is null
I have just re-read chapter 8.15 of the 8.3 manual and I am understanding why a composite type would evaluate to NUll if any of it elements are null. Can anyone explain this behavior? To me if I have a composite type column and there are some values in it, its incomplete, yes, but not null. Thanks for the feedback. Ketema J. Harris www.ketema.net ket...@ketema.net ketemaj on iChat -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] View vs Constantly Updated Table
How often are you using the bank balance value? I have no data on this as of yet, but it obviously needs to be correct for when the user looks at it. The opposite argument, is how long does the computation take? The computation is simple, however the amount of data that goes into it grows as there are more and more transactions added. This is why I was thinking of storing the balance in a table sort of as a cache to avoid spinning through all pas transaction to get the current balance. and how quickly do you need it? as fast as possible of course :) Also, if you would be taking any action with the value returned by the balance calculation, remember to lock any tables necessary to ensure the balance doesn't change between the time you compute it and the time you act on it. I'll keep this in mind. never done a lot of manual locking before, but I can see where this is going to be needed. Its possible to take row locks and not complete table locks correct? (manual chapter 13.3) I'm thinking if I use a single table to hold all users cached balances then I would not want to lock the entire table just to retrieve and act on one users balance. thanks for the input On Dec 15, 2008, at 11:27 AM, Adam Rich wrote: Truncated... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] View vs Constantly Updated Table
if i have a column that is a calculation, say a bank balance - sum of all the debits and credits...is it more efficient to make a view that executes the underlying calc query doing the math, or to create a table that has a column called balance that is updated for each transaction? so in the end select balance from view or select balance from table ? What are the pros cons ? Thanks Ketema J. Harris www.ketema.net ket...@ketema.net ketemaj on iChat inline: kmail.png -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trigger/Rules Order of operations
I am interested in finding out the pros, cons, pitfalls of using the following design: Manual insert into Table A. Table A has a BEFORE INSERT trigger that causes an insert to table B. Table B has an AFTER INSERT trigger that causes an insert back to table A (With different criteria not an endless loop) Table A will have its Before Trig fire again and this time the criteria causes it to finish with a return new. Will the second insert into table A commit before the first insert into table A? What order does the insert into table B finish up? Ketema J. Harris www.ketema.net ket...@ketema.net ketemaj on iChat -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Inherited FK Indexing
I have the following table set up:
CREATE TABLE states
(
state_id integer NOT NULL DEFAULT nextval
('state_province_id_seq'::regclass),
state character(2),
full_name character varying,
timezone character varying,
CONSTRAINT PK_state_id PRIMARY KEY (state_id)
)
CREATE TABLE canadian_provinces
(
-- Inherited: state_id integer NOT NULL DEFAULT nextval
('state_province_id_seq'::regclass),
-- Inherited: state character(2),
-- Inherited: full_name character varying,
-- Inherited: timezone character varying,
CONSTRAINT PK_province_id PRIMARY KEY (state_id)
)
as expected I can do select * from states and get everything out of
the child table as well. What I can't do is create a FK to the
states table and have it look in the child table as well. Is this on
purpose? Is it possible to have FK that spans into child tables?
Thanks
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] Best Data type for Binary Data?
Hi, I would like to store binary data from a tcpdump (libpcap) file in a table. What is the best type to use? i have read posts saying lo, oid, and bytea. Which one would be best for this scenario?Thanks, ketema
