[SQL] List archives moved and cleaned up ...
Finally figuring that enough is enough, I've been spending the past few days working on the list archives ... I've reformatted, so far, the following lists into a cleaner format: pgsql-hackers pgsql-sql pgsql-bugs pgsql-general pgadmin-hackers pgadmin-support With more lists to be worked on over the next few days ... Major changes include the following: Replaced the wide banner in the center with two smaller, 120x120 banners in the corners ... Provide a search facility incorporated into each page that searches the mhonarc pages themselves ... Change the colors to better match the main site ... Moved the archives to its own URL/Domain so that it is no longer part of the general mirror of the site ... There is still alot of work that I'm planning on doing on this, but I want to get all of the archives moved first ... To access any of the archives that have been moved, go to: http://archives.postgresql.org/ I've been modifying the links from the main web site for those lists that I've moved, as I've moved them, so getting there through 'normal channels' should also work ... Once finished, there will also be links to the OpenFTS search facility that we have online, which uses a different way of formatting/displaying the messages, so you will have the choice of using either ... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] pgsql-performance mailing list / newsgroup created
Morning all ... Josh Berkus the other day shook my cage a bit and reminded me to create the -performance list that had been discussed awhile back ... so I did :) [EMAIL PROTECTED] or comp.databases.postgresql.performance archives wont' show it up yet, still have to reconfig all of that stuff, but the list is there and ready to go ... or should be. If there are any problems, please let me know ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Testing gateway
In theory, the news2mail gateway is back in place ... ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] removing precision from timestamp (microseconds) ..
>From the docs, if you do: traffic=# select CURRENT_TIMESTAMP(0); timestamptz 2003-10-13 11:04:09-03 (1 row) the 0 reduces the precision of the time to get rid of the microseconds ... is there a way of having this done by default on, if anything, a per connection basis? For instance, I want to be get rid of the microseconds from: traffic=# select now(); now --- 2003-10-13 11:02:20.837124-03 (1 row) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] off subject - pg web hosting
http://www.hub.org http://www.pghoster.com http://www.commandprompt.com On Thu, 6 Nov 2003, chester c young wrote: > can anybody recomend web hosting that provides postgresql? I have > found a couple, but their pricing is several times the going rate using mySql. > > __ > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard > http://antispam.yahoo.com/whatsnewfree > > ---(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 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] simple LEFT JOIN giving wrong results ...
I've got to be missing something obvious here ... I have two tables, on containing traffic stats, the other disk usage ... I want to do a JOIN in one query to give me the sum of traffic and average of storage ... seperately, the results are right .. put together, traffic values are way off, while storage is still correct ... So, I'm doing my LEFT JOIN wrong *somehow*, but its eluding me as to what/how .. :( ams=# select ct.ip_id, sum(ct.bytes) as traffic, avg(cs.bytes)::bigint as storage from company_00186.traffic ct left join company_00186.storage cs ON ( ct.ip_id = cs.ip_id AND month_trunc(cs.runtime) = '2003-12-01') where month_trunc(ct.runtime) = '2003-12-01' group by ct.ip_id; ip_id | traffic| storage ---+--+- 1194 | 76761728 | 1839676259 1226 | 5744576925 | 1134 | 17042528 |24794553 1089 | 311779796360 | 10814211187 1200 | 82535202840 | 3165073628 1088 | 1969333472 | 2119206061 1227 | 44816947957 | 4891683299 1179 | 3867502285 | (8 rows) where, individually, the results should be: ams=# select ip_id, avg(bytes)::bigint from company_00186.storage where month_trunc(runtime) = '2003-12-01' group by ip_id; ip_id | avg ---+- 1227 | 4891683299 1255 | 0 1134 |24794553 1194 | 1839676259 1089 | 10814211187 1088 | 2119206061 1200 | 3165073628 (7 rows) and ams=# select ip_id, sum(bytes) from company_00186.traffic where month_trunc(runtime) = '2003-12-01' group by ip_id; ip_id | sum ---+- 1194 | 9595216 1226 | 5744576925 1134 | 2130316 1089 | 38972474545 1200 | 10316900355 1088 | 246166684 1227 | 44816947957 1179 | 3867502285 (8 rows) the storage/avg values come out right in the JOIN, but the traffic/sum values get royally screwed ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] simple LEFT JOIN giving wrong results ...
On Thu, 11 Dec 2003, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > So, I'm doing my LEFT JOIN wrong *somehow*, but its eluding me as to > > what/how .. :( > > > ams=# select ct.ip_id, sum(ct.bytes) as traffic, > > avg(cs.bytes)::bigint as storage > > from company_00186.traffic ct > >left join company_00186.storage cs ON ( ct.ip_id = cs.ip_id AND > >month_trunc(cs.runtime) = '2003-12-01') > >where month_trunc(ct.runtime) = '2003-12-01' group by ct.ip_id; > > I suspect you do not want the month_trunc constraint to be included > in the JOIN ON condition, only in WHERE. 'k, but then would that take in all storage for all dates, since I'm only then joining on the ip_id? right now, I only have storage #s for Dec, so it wouldn't make any differences for this one, but .. results are still way off though, even with removing it: ip_id | traffic| storage ---+--+- 1088 | 1979325872 | 2119206061 1200 | 84004842024 | 3165073628 1227 | 45591571353 | 4891683299 1179 | 3893192839 | 1194 | 77360968 | 1839676259 1134 | 17357504 |24794553 1226 | 5836213601 | 1089 | 315424415080 | 10814211187 (8 rows) By changing the query to: ams=# select ip_id, sum(bytes), (select avg(bytes) from company_00186.storage cs where month_trunc(runtime) = '2003-12-01' and cs.ip_id = ct.ip_id)::bigint as storage from company_00186.traffic ct where month_trunc(runtime) = '2003-12-01' group by ip_id; ip_id | sum | storage ---+-+- 1194 | 9670121 | 1839676259 1134 | 2169688 |24794553 1226 | 5836213601 | 1089 | 39428051885 | 10814211187 1088 | 247415734 | 2119206061 1200 | 10500605253 | 3165073628 1227 | 45591571353 | 4891683299 1179 | 3893192839 | (8 rows) I can get the right results again, it jus doesn't seem as clean ;( Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Skip dups on INSERT instead of generating an error ...
I need to be able to run an INSERT INTO / SELECT FROM UNION which combines two tables into one ... *but* ... the INTO table has a primary key on the first column, so if the result of the UNION generates dups, by default, of course, it will generate errors ... what I'd like is to have it so that it just skips over those records. First thought would be to write a quite plpgsql function that would do a SELECT first, to see if the value already exists, and if not, then do the INSERT ... but am wondering if maybe there is a cleaner way that I'm not thinking of? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Ok, what am I doing wrong here?
On Tue, 17 Feb 2004, Karl Denninger wrote: > I want to insert values from one table into another, and add some "default" > values (that are not defaults on the table different reasons - that is, this > is maintenance function and in normal operation there would be "real" values > there - and null is valid) > > So, I want to do, for example, the following: > > insert into table (id, time, type) values (select id, now(), '1' from secondtable); > > Postgres's command line pukes on this, complaining that "select" is invalid > inside the values part of the definition. > > SQL's language specification says otherwise, as does "\h insert" from the > command line. > > The query stand-alone returns a table with values that are valid for the > table I wish to insert into. > > Where's my brain-fade on this? INSERT INTO table (id, time, type) SELECT id, now(), '1' FROM secondtable; Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] How do FKs work?
Got a problem here, and this is a new area for me ... analyzing FKs and improving their performance :) Have a table with two FKs on it ... 2 different fields in the table point to the same field in another table ... When I do an 'EXPLAIN ANALYZE DELETE FROM table WHERE field = #;', it never comes back ... or, at lesat, takes a *very* long time ... If I do a count(1) for that #, there are 1639 rows ... Now, 'ON DELETE' is set to 'NO ACTION', so my *guess* is that all it does is a 'SELECT FROM table WHERE field = value' on the referenced table, to make sure it exists ... Is this correct? So, its effectively having to do 3278 "SELECTS" against the REFERENCED table? (two fields have contraints on them, 1639 rows to be deleted) ... ? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How do FKs work?
On Sat, 9 Oct 2004, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Have a table with two FKs on it ... 2 different fields in the table point to the same field in another table ... When I do an 'EXPLAIN ANALYZE DELETE FROM table WHERE field = #;', it never comes back ... or, at lesat, takes a *very* long time ... Do you have indexes on the referencing columns? Are they exactly the same datatype as the referenced column? You can get really awful plans for the FK-checking queries if not. Yup, that was my first thought ... running SELECT's joining the two tables on the FK fields shows indices being used, and fast times ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How do FKs work?
On thing I failed to note here, that is probably critical ... its a 7.3
database ...
On Sat, 9 Oct 2004, Tom Lane wrote:
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
Have a table with two FKs on it ... 2 different fields in the table point
to the same field in another table ...
When I do an 'EXPLAIN ANALYZE DELETE FROM table WHERE field = #;', it
never comes back ... or, at lesat, takes a *very* long time ...
Do you have indexes on the referencing columns? Are they exactly the
same datatype as the referenced column? You can get really awful plans
for the FK-checking queries if not.
regards, tom lane
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Easier way to 'strip' on multiple matches?
I need to strip out all spaces, and all \' from a string ... is there an easier way then doing: select lower(replace(replace(name, ' ', ''), '\\\'', '')) from business; Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] PL/PgSQL - returning multiple columns ...
I have a function that I want to return 'server_name, avg(load_avg)' ... if I wanted to return matching rows in a table, I can do a 'setof ', with a for loop inside ... but what do I set the 'RETURNS' to if I want to return the results of query that returns only two fields of a table, or, in the case of the above, one column and oen 'group by' column? thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] PL/PgSQL - returning multiple columns ...
Perfect, worked like a charm ... but the RETURNS still needs to be a
SETOF, other then that, I'm 'away to the races' ... thanks :)
On Wed, 2 Feb 2005, George Weaver wrote:
Hi Marc,
One option is to create a simple data type and return the rowtype of the
datatype
eg CREATE TYPE tserverload AS ("server_name" text, "load_avg" int4);
CREATE FUNCTION getserverload()
RETURNS tserverload
AS
'DECLARE
r tserverload%rowtype;
etc.
You would then return r, comprised of r.server_name and r.load_avg.
George
----- Original Message - From: "Marc G. Fournier"
<[EMAIL PROTECTED]>
To:
Sent: Wednesday, February 02, 2005 3:10 PM
Subject: [SQL] PL/PgSQL - returning multiple columns ...
I have a function that I want to return 'server_name, avg(load_avg)' ... if
I wanted to return matching rows in a table, I can do a 'setof ',
with a for loop inside ... but what do I set the 'RETURNS' to if I want to
return the results of query that returns only two fields of a table, or, in
the case of the above, one column and oen 'group by' column?
thanks ...
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(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
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Major flood of mail to lists ...
Do to moderator error (namely, mine), several hundred messages (spread across all the lists) were just approved ... Sorry for all the incoming junk :( Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] UPDATEABLE VIEWS ... Examples?
Reading through the docs, both the CREATE VIEW and CREATE RULE pages refer to how you can use a RULE to 'simulate' an updateable VIEW ... but I can't seem to find any examples of this ... Does anyone know of an online example of doing this that I can read through? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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
[SQL] Putting an INDEX on a boolean field?
Does that make sense? Would it ever get used? I can't see it, but figured I'd ask ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Putting an INDEX on a boolean field?
On Fri, 17 Jun 2005, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Does that make sense? Would it ever get used? It could get used if one of the two values is far less frequent than the other. Personally I'd think about a partial index instead ... Hr, hadn't thought of that ... wouldn't you have to build two indexes (one for true, one for false) for this to be completely effective? unless you know all your queries are going to search for one, but not the other? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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
[SQL] using 'zoneinfo' to manipulate dates ...
Does anyone have a 'table of timezones' that could be used to "localize" as part of a query? For instance, something like: SELECT date FROM table WHERE ( time || ' ' || ( SELECT timezone FROM zones WHERE id = table.timezone )) = '2004-12-12'; Something like this, but that works: # select ( now() || ' ' || 'PST8PDT' )::timestamp; ERROR: invalid input syntax for type timestamp: "2005-06-26 00:23:29.789703-03 PST8PDT" Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] ARRAYs and INDEXes ...
Can't seem to find anything concerning this in the docs, and I don't think it is something that can be done, but figure I'll double check before I write it off completely ... If I create a table: CREATE TABLE customers ( customer_id SERIAL, monthly_balance DECIMAL(7,2)[12] ); Is it possible to create an INDEX on customers.monthly_balance such that I could do something like: SELECT * FROM customers WHERE monthly_balance[6] = 0.00; As an example ... or SELECT * FROM customers WHERE 0.00 = any (monthly_balance); Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] pl/PgSQL: Samples doing UPDATEs ...
I have an older PostgreSQL book here, that has chapter on pl/PgSQL in it, but no good samples What I'm looking for is a sample of a function that returns # of rows updated, so that I can make a decision based on that ... does anyone know where I could find such (and others, would be great) online? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] PL/SQL Function: self-contained transaction?
In PostgreSQL, as everyone knows, a QUERY == a transaction, unless wrap'd in a BEGIN/END explicitly ... how does that work with a function? is there an implicit BEGIN/END around the whole transaction, or each QUERY within the function itself? If the whole function (and all QUERYs inside of it) are considered one transaction, can you do a begin/end within the function itself to 'force' commit on a specific part of the function? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] [HACKERS] ALTER TABLE ( smallinto -> boolean ) ...
Moved off of -hackers, since its long gotten out of that realm :) On Thu, 1 Sep 2005, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: On Mon, 29 Aug 2005, Tom Lane wrote: No, because there's no built-in cast from smallint to bool. 'k, I just took a read through the "CREATE CAST" man page, and don't think I can use that for this, Sure you can. Make a SQL or PLPGSQL function that does the conversion you want and then create a cast using it. Ah, okay, I just re-read the man page and think I stumbled upon what I overlooked the first time ... all I want to do is: CREATE CAST ( 0 AS boolean ) WITH FUNCTION AS ASSIGNMENT; And then each time I try to insert a '0' into a BOOLEAN field, it will auto convert that (based on my function) to 'f' ... And I'd need to do a second one for 1 -> 't' ... Am I reading it right this time ... ? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] a "catch all" type ... such a thing?
Are there any data types that can hold pretty much any type of character? UTF-16 isn't supported (or its missing from teh docs), and UTF-8 doesn't appear to have a big enough range ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] convert timezone to string ...
I know that the server knows that ADT == -0400, and AST == -0300 ... is there any way of reversing that? Basically, I want to say: SELECT timezone_str(-0400, 'not dst'); and have it return ADT ... I've got a method of doing it right now, using a function, but just find it looks so messy, just wondering if there is a clean way of doing it ... Thanks ... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] convert timezone to string ...
On Tue, 25 Oct 2005, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: I know that the server knows that ADT == -0400, and AST == -0300 ... Other way around isn't it? Unless Canada observes a pretty strange variety of daylight saving time ;-) I knew I was going to get that backwards :( Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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
[SQL] pl/* overhead ...
Does anyone know of, or have, any comparisions of the overhead going with something like pl/perl or pl/php vs using pl/pgsql? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] padding an integer ...
Is there any way of "padding" an integer, similar to how, in perl, I would
do:
printf("%03d", 1);
to get:
001
Specifically, I'm looking to do this in a pl/pgsql function ... like to
avoid moving to pl/php or pl/perl if I can ... but, from what I've been
able to find, I suspect I'm not going to have much of a choice ...
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] pl/* overhead ...
On Wed, 26 Oct 2005, Michael Fuhr wrote:
On Wed, Oct 26, 2005 at 12:58:13AM -0300, Marc G. Fournier wrote:
Does anyone know of, or have, any comparisions of the overhead going with
something like pl/perl or pl/php vs using pl/pgsql?
Benchmark results will probably depend on the type of processing
you're doing. I'd expect PL/pgSQL to be faster at database operations
like looping through query results, and other languages to be faster
at non-database operations like text munging and number crunching,
depending on the particular language's strengths.
[Does quick test.]
Whale oil beef hooked. PL/pgSQL just outran PL/Perl when I expected
the latter to win. Hang on, let me play with it until it comes back
with the results I want
'k, let's repharase the questions :)
Overall, I'd expect pl/pgsql to have less overhead, since its "built into"
the server ... in the case of something like pl/php or pl/perl, assuming
that I don't use any external modules, is it just as 'built in', or am I
effectively calling an external interpreter each time I run that function?
For instance, if there wasn't something like to_char() (thanks for
pointing that one out), then i could write a simple pl/perl function that
'simulated it', but itself did no db queries just a simple:
RETURN sprintf("%04d", intval);
Don't know if that made much more sense ... ?
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] # of 5 minute intervals in period of time ...
Is there a simpler way of doing this then:
select (date_part('epoch', now()) -
date_part('epoch', now() - '30 days'::interval)) / ( 5 * 60 );
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] FOREIGN KEYs ... I think ...
I'm not sure if I'm looking at (for) the right thing or not, but if I am, then I'm not finding any useful examples :( I have two tables, simplified as: CREATE TABLE incident_summary ( id serial, subject text, status boolean ); CREATE TABLE incident_comments ( id serial, incident_id int4, body text, comment_date timestamp, status boolean ); Now, what I want to do is add a FOREIGN KEY (again, I think) that when incident_summary.status is changed (either closed, or reopened), the associated records in incident_comments are changed to the same state ... It *looks* like it should be simple enough, I want incident_comments.status to change to incident_summary.status whenever incident_summary.status changes ... since I'm finding nothing searching on FOREIGN KEYS, I'm guessing that I'm looking at the wrong thing ... So, what should I be searching on / reading for this one? Pointers preferred, especially one with some good examples :) Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] FOREIGN KEYs ... I think ...
On Wed, 4 Jan 2006, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Now, what I want to do is add a FOREIGN KEY (again, I think) that when incident_summary.status is changed (either closed, or reopened), the associated records in incident_comments are changed to the same state ... Why not just get rid of the status column in incident_comments, and treat incident_summary.status as the sole copy of the state? When you need to get to it from incident_comments, you do a join. I may end up getting to that point ... The foreign key you really ought to have here is from incident_comments.incident_id to incident_summary.id (assuming that I've understood your schema correctly). 'k, where I'm getting lost here is how do I get status changed in _comments on UPDATE of incident_summary.id? There doesn't seem to be anything for ON UPDATE to 'run SQL query' or some such ... or I'm reading old docs :( This is the part that I'm having a bugger of a time wrapping my head around ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] FOREIGN KEYs ... I think ...
Thanks to everyone for the responses ... ended up doing a trigger on the comments table that updates another table to maintain a "pointer" to the active record ... sped up the query that was hampering us from ~26 000ms to 47ms ... the killer part of the query was that each time it was havin gto figure out the 'active remark record' doing a 'max(create_time)' ... On Wed, 4 Jan 2006, Marc G. Fournier wrote: I'm not sure if I'm looking at (for) the right thing or not, but if I am, then I'm not finding any useful examples :( I have two tables, simplified as: CREATE TABLE incident_summary ( id serial, subject text, status boolean ); CREATE TABLE incident_comments ( id serial, incident_id int4, body text, comment_date timestamp, status boolean ); Now, what I want to do is add a FOREIGN KEY (again, I think) that when incident_summary.status is changed (either closed, or reopened), the associated records in incident_comments are changed to the same state ... It *looks* like it should be simple enough, I want incident_comments.status to change to incident_summary.status whenever incident_summary.status changes ... since I'm finding nothing searching on FOREIGN KEYS, I'm guessing that I'm looking at the wrong thing ... So, what should I be searching on / reading for this one? Pointers preferred, especially one with some good examples :) Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)------- TIP 6: explain analyze is your friend Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Update counter when row SELECT'd ... ?
I have a simple table: name, url, counter I want to be able to do: SELECT * FROM table ORDER BY counter limit 5; But, I want counter to be incremented by 1 *if* the row is included in that 5 ... so that those 5 basically move to the bottom of the list, and the next 5 come up ... I've checked CREATE TRIGGER, and that doesn't work 'ON SELECT' ... is there anything that I *can* do, other then fire back an UPDATE based on the records I've received? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Reverse Index ... how to ...
I'm still searching through Google and whatnot, but not finding anything off the bat ... is there some way of creating a 'REVERSE INDEX' on a column in a table? For instance, when I do a 'CLUSTER' to sort a table based on an INDEX, I'd like to sort it in reverse order, so would need the INDEX to go from 'GREATEST to LOWEST', vs 'LOWEST to GREATEST' ... Thx Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Reverse Index ... how to ...
On Wed, 5 Apr 2006, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: I'm still searching through Google and whatnot, but not finding anything off the bat ... is there some way of creating a 'REVERSE INDEX' on a column in a table? For instance, when I do a 'CLUSTER' to sort a table based on an INDEX, I'd like to sort it in reverse order, so would need the INDEX to go from 'GREATEST to LOWEST', vs 'LOWEST to GREATEST' ... You shouldn't need to worry about that during CLUSTER, as the system is perfectly capable of scanning an index in either forward or backward order at runtime. For example, regression=# explain select * from tenk1 order by unique1; QUERY PLAN Index Scan using tenk1_unique1 on tenk1 (cost=0.00..1572.00 rows=1 width=244) (1 row) regression=# explain select * from tenk1 order by unique1 desc; QUERY PLAN - Index Scan Backward using tenk1_unique1 on tenk1 (cost=0.00..1572.00 rows=1 width=244) (1 row) Perfect, that was what I was looking for, thx ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Ignore, test of an alias
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] timestamp query doesn't use an index ...
I'm trying to figure out some way to speed up the following query:
select ps2.page_id, ps2.template_component_id, max(ps2.start_time)
from page_schedule ps2
where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
group by ps2.page_id, ps2.template_component_id
When run through EXPLAIN ANALYZE, it shows:
QUERY PLAN
---
HashAggregate (cost=2613.28..2614.17 rows=72 width=16) (actual
time=976.629..976.938 rows=128 loops=1)
-> Seq Scan on page_schedule ps2 (cost=0.00..2364.95 rows=33110 width=16)
(actual time=0.021..623.363 rows=94798 loops=1)
Filter: (timezone('MST7MDT'::text, start_time) <= '2006-05-17
08:09:18'::timestamp without time zone)
Total runtime: 977.224 ms
(4 rows)
I've tried doing a function index, like:
create index start_time_page_schedule
on page_schedule
using btree ( timezone('MST7MDT'::text, start_time ) );
But, same result ... whereas, if I change the <= to just =, the index is
used, but that is expected ...
Is there some other way I can either write above query *or* do an index,
such that it will use the index?
thanks ...
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED]
Yahoo . yscrappy Skype: hub.orgICQ . 7615664
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] timestamp query doesn't use an index ...
On Sun, 21 May 2006, Michael Glaesemann wrote:
On May 21, 2006, at 10:42 , Marc G. Fournier wrote:
-> Seq Scan on page_schedule ps2 (cost=0.00..2364.95 rows=33110
width=16) (actual time=0.021..623.363 rows=94798 loops=1)
I don't know about rewriting the query, but it appears your statistics are a
little out of date (e.g., rows expected/actual 33110/94798). Does running
ANALYZE help?
the data is idle, just loaded it on my desktop for testing purposes ...
being paranoid, I have been doing a vacuum analyze on the table as I
change the index's *just in case*, but, doing a full analyze on the whole
database doesn't change the results any:
Actually, the above results are odd anyway, since a second run of the
exact same query, shows more normal numbers:
QUERY PLAN
---
HashAggregate (cost=3051.91..3054.19 rows=183 width=16) (actual
time=1030.970..1031.257 rows=128 loops=1)
-> Seq Scan on page_schedule ps2 (cost=0.00..2364.95 rows=91594 width=16)
(actual time=0.019..636.599 rows=94798 loops=1)
Filter: (timezone('MST7MDT'::text, start_time) <= '2006-05-17
08:09:18'::timestamp without time zone)
Total runtime: 1031.681 ms
(4 rows)
So not 100% certain where the 33110/94798 gap came from ;)
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED]
Yahoo . yscrappy Skype: hub.orgICQ . 7615664
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] timestamp query doesn't use an index ...
On Sun, 21 May 2006, Tom Lane wrote:
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
I'm trying to figure out some way to speed up the following query:
select ps2.page_id, ps2.template_component_id, max(ps2.start_time)
from page_schedule ps2
where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
group by ps2.page_id, ps2.template_component_id
Is there some other way I can either write above query *or* do an index,
such that it will use the index?
One-sided inequalities frequently *shouldn't* use an index, because
they're retrieving too much of the table. Are you sure this is fetching
only a small fraction of the table? Are you using PG 8.1 (8.1 would be
likely to try to use a bitmap indexscan for this)?
You could experiment with enable_seqscan = off to see if the planner is
actually wrong about its choice. If so, reducing random_page_cost might
be the best permanent solution.
vrnprd=# select version();
version
PostgreSQL 8.1.3 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4
[FreeBSD] 20050518
(1 row)
vrnprd=# set enable_seqscan = off;
SET
vrnprd=# explain analyze
select ps2.page_id, ps2.template_component_id, max(ps2.start_time)
from page_schedule ps2
where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
group by ps2.page_id, ps2.template_component_id
;
QUERY PLAN
-
HashAggregate (cost=3509.96..3513.50 rows=283 width=16) (actual
time=839.460..839.769 rows=128 loops=1)
-> Bitmap Heap Scan on page_schedule ps2 (cost=573.65..2822.86 rows=91614
width=16) (actual time=149.788..505.438 rows=94798 loops=1)
Recheck Cond: (timezone('MST7MDT'::text, start_time) <= '2006-05-17
08:09:18'::timestamp without time zone)
-> Bitmap Index Scan on start_time_page_schedule (cost=0.00..573.65
rows=91614 width=0) (actual time=127.761..127.761 rows=94798 loops=1)
Index Cond: (timezone('MST7MDT'::text, start_time) <=
'2006-05-17 08:09:18'::timestamp without time zone)
Total runtime: 846.604 ms
(6 rows)
vrnprd=#
And yup, it is definitely returning just 128 rows out of the 93k or so:
110 | 419 | 2005-10-26 13:15:00-03
130 | 215 | 2006-04-26 10:15:00-03
(128 rows)
And, not sure how far to reduce random_page_cost, but it was 4 ... tried 2
and 1, and both come up with the same results ... with seqscan enabled, it
does a seqscan :(
I suspected with the <= there wasn't going to be much I could do with
this, but figured I'd make sure there wasn't something that I was
overlooking :(
Thx ...
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED]
Yahoo . yscrappy Skype: hub.orgICQ . 7615664
---(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] DBD::Pg ... how would I format this prepare?
I need to do: NOW() + '2 day'::interval where 2 is a variable ... if I do: NOW() + '? day'::interval it, of course, takes the ? as a literal ... so is there some way I can do this such that I can do the placeholder? Thx ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] DBD::Pg ... how would I format this prepare?
On Wed, 26 Jul 2006, Jim Buttafuoco wrote: try now() + (? || ' day')::interval Perfect ... had thought of that, but put my ) after ::interval instead of before ;( Thx ... -- Original Message --- From: "Marc G. Fournier" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Wed, 26 Jul 2006 18:29:32 -0300 (ADT) Subject: [SQL] DBD::Pg ... how would I format this prepare? I need to do: NOW() + '2 day'::interval where 2 is a variable ... if I do: NOW() + '? day'::interval it, of course, takes the ? as a literal ... so is there some way I can do this such that I can do the placeholder? Thx Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq --- End of Original Message --- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(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
[SQL] Way to reverse ordering of an IP ... ?
for Reverse DNS purposes, I'd like to reverse an IP ... ie: 200.46.204.1 would become 1.204.46.200 Is there an easy way of doing this *short* of writing a plpgsql function? I've checked the docs, and found the substring() function that allows for using a regex, which I thought might allow this, but can't seem to figure out a proper format for it ;( If I have to write a function to do it, fine ... just wanted to make sure I wasn't missing something first ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(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
[SQL] Recursive pl/pgsql function ...
'k, this isn't quite working as expected (or, rather, hoped) ... I've created a
recursive function (that does work nicely) that takes a date in the past, and
keeps advancing in steps of 'n months' until the date is in the future:
CREATE OR REPLACE FUNCTION get_next_billing_date(timestamp with time zone,
int4) RETURNS TIMESTAMP AS '
DECLARE
max_date RECORD;
ret TIMESTAMP WITH TIME ZONE;
start_date ALIAS FOR $1;
payment_period ALIAS FOR $2;
BEGIN
SELECT INTO max_date CASE WHEN now() < start_date + ( payment_period || ''
months'')::interval THEN payment_period ELSE NULL END;
IF max_date.case IS NULL THEN
SELECT INTO ret get_next_billing_date(start_date + ( payment_period || ''
months'')::interval, payment_period);
ELSE
RETURN start_date + ( payment_period || '' months'')::interval;
END IF;
RETURN ret;
END;
' LANGUAGE plpgsql;
It works, no problem there:
# select get_next_billing_date('2005-10-15', 3);
get_next_billing_date
---
2007-01-15 00:00:00
(1 row)
But, as soon as I try and use that function as a field in a query, it gives an
error:
1 Record:
# select get_next_billing_date(activated, 12) from company_details where
company_id = 771;
get_next_billing_date
2007-05-03 15:09:19.491958
(1 row)
More then one Record:
# select get_next_billing_date(activated, 12) from company_details;
ERROR: control reached end of function without RETURN
CONTEXT: PL/pgSQL function "get_next_billing_date"
Something I've written wrong in the function, or just not something that is
doable?
Thanks ...
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED]
Yahoo . yscrappy Skype: hub.orgICQ . 7615664
pgpQYr5LP4REk.pgp
Description: PGP signature
Re: [SQL] Recursive pl/pgsql function ...
--On Sunday, October 15, 2006 23:27:34 -0400 Tom Lane <[EMAIL PROTECTED]> wrote:
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
More then one Record:
# select get_next_billing_date(activated, 12) from company_details;
ERROR: control reached end of function without RETURN
CONTEXT: PL/pgSQL function "get_next_billing_date"
Hm, what PG version is this? I couldn't duplicate that in HEAD, though
I did find out that a zero or negative payment_period makes it recurse
until "stack depth exceeded".
8.1.4 .. the odd thing is that I can throw any date at it, it seems, but I
can't use it as a 'field' in a query of a table, even when returning a single
record:
# select * from company_details where company_id = 76;
company_id | address_one | address_two | city| province_state |
postal_code | activated | disabled | recurring | referrer_info |
billing_type_id | currency_id | country_id
+-+-+---++-+-+--+---+---+-+-+
76 | 127 Main Street | | Wolfville | NS | B0P
1X0 | 2001-11-01 02:00:00 | | t | |
2 | 1 | 45
(1 row)
=# select get_next_billing_date('2001-11-01', 3);
get_next_billing_date
---
2006-11-01 00:00:00
(1 row)
=# select get_next_billing_date(activated, 3) from company_details where
company_id = 76;
ERROR: control reached end of function without RETURN
CONTEXT: PL/pgSQL function "get_next_billing_date"
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
SQL statement "SELECT get_next_billing_date( $1 , $2 )"
PL/pgSQL function "get_next_billing_date" line 12 at perform
=# \d company_details
Table "public.company_details"
Column |Type | Modifiers
-+-+---
company_id | integer |
address_one | text| not null
address_two | text|
city| text| not null
province_state | text|
postal_code | text|
activated | timestamp without time zone | default now()
disabled| timestamp without time zone |
recurring | boolean | default false
referrer_info | text|
billing_type_id | integer |
curr
Re: [SQL] get_next_billing_date() ...
--On Monday, October 16, 2006 09:53:56 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: In fact, more info to work with ... it likes the date, just not when I use it as part of a SELECT query of a table ... I suspect it's not so much the "table" part as some specific value of the date coming from the table --- can you find out what value it's failing on? That's the thing ... the one example I sent only returns one record, and if I plug in the timestamp value that it returns directly, it doesn't fail ... Got it, and the error message in no way indicates ... I created the function 'timestamp with time zone' ... the field in the table is 'timestamp without time zone' ... fix the function , and voila: company_id | get_next_billing_date + 708 | 2006-12-13 14:09:11.442111 679 | 2006-10-25 11:04:00.117714 771 | 2006-11-03 15:09:19.491958 688 | 2006-11-11 18:18:42.23105 701 | 2006-11-28 11:45:36.182217 656 | 2006-11-05 20:46:01.335434 703 | 2006-11-28 19:35:25.081577 704 | 2006-12-05 21:53:57.122358 756 | 2007-01-02 14:18:22.11068 I knew it had to be *somethign* stupid :( Thx ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 pgpx724ev1rmQ.pgp Description: PGP signature
