Re: [SQL] Install two different versions of postgres which should run in parallel
I didn't succeed installing the two versions of postgres. For installing I am running the "configure" script as follows: ./configure --prefix=/usr/local/pgsql-7.4.5 --with-java --with-pgport=6947 Although I specify a different port than the default one, the postgres it is installed with the default port. The port must be specified also in another configuration files? Regards, Loredana
Re: [SQL] Install two different versions of postgres which should run in parallel
Check the postgresql.conf in the $prefix/data dir. Also post the exact error log (which in this case should be some like "address already in use", because port 5432 is in use by the 8.2.4 version), and also the exact command you are executing. Cheers. Gerardo I didn't succeed installing the two versions of postgres. For installing I am running the "configure" script as follows: ./configure --prefix=/usr/local/pgsql-7.4.5 --with-java --with-pgport=6947 Although I specify a different port than the default one, the postgres it is installed with the default port. The port must be specified also in another configuration files? Regards, Loredana ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [NOVICE] Install two different versions of postgres which should run in parallel
"Loredana Curugiu" <[EMAIL PROTECTED]> writes: > For installing I am running the "configure" script as follows: > ./configure --prefix=/usr/local/pgsql-7.4.5 --with-java --with-pgport=6947 > Although I specify a different port than the default one, the postgres it is > installed with the default port. On what grounds do you conclude that? I use --with-pgport every day. It works fine. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Comparing two slices within one table efficiently
I have a table with the following simplified form: create table t ( run_id integer, domain_id integer, mta_id integer, attribute1 integer, attribute2 integer, unique(run_id, domain_id, mta_id) ); The table has about 1 million rows with run_id=1, another 1 million rows with run_id=2, and so on. I need to efficiently query the differences between "runs" - i.e. For each (domain_id, mta_id) tuple in run 1, is there a coresponding tuple in run 2 where either attribute1 or attribute2 have changed? The only way I have been able to think of doing this so far is an o(n^2) search, which even with indexes takes a long time. e.g. select * from t t1 where exists (select 1 from t t2 where t2.mta_id=t1.mta_id and t2.domain_id=t1.domain_id and (t2.attribute1 != t1.attribute1 or t2.attribute2 != t1.attribute2) This query takes millenia... Any help would be greatly appreciated. I hope I am naively missing some obvious alternative strategy, since this sort of operation must be common in databases. Thanks, Ken -- Ken Simpson, CEO MailChannels Corporation Reliable Email Delivery (tm) http://www.mailchannels.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Comparing two slices within one table efficiently
> I have a table with the following simplified form: > > create table t ( > run_id integer, > domain_id integer, > mta_id integer, > attribute1 integer, > attribute2 integer, > unique(run_id, domain_id, mta_id) > ); > > The table has about 1 million rows with run_id=1, another 1 million > rows with run_id=2, and so on. > > I need to efficiently query the differences between "runs" - i.e. For > each (domain_id, mta_id) tuple in run 1, is there a coresponding > tuple in run 2 where either attribute1 or attribute2 have changed? > > The only way I have been able to think of doing this so far is an > o(n^2) search, which even with indexes takes a long time. e.g. > > select * from t t1 where exists (select 1 from t t2 where > t2.mta_id=t1.mta_id and t2.domain_id=t1.domain_id and (t2.attribute1 > != t1.attribute1 or t2.attribute2 != t1.attribute2) > > This query takes millenia... > first, add a change flag change_tf that is set through a trigger whether this record different from record in the previous run. second, create an index on domain and mta where change_tf, so you're only indexing changed records. this would allow you to find your changes very efficiently at the relatively small cost of adding one lookup and one extra index per insert. Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Comparing two slices within one table efficiently
Ken Simpson wrote: > I have a table with the following simplified form: > > create table t ( > run_id integer, > domain_id integer, > mta_id integer, > attribute1 integer, > attribute2 integer, > unique(run_id, domain_id, mta_id) > ); > > The table has about 1 million rows with run_id=1, another 1 million rows with > run_id=2, and so on. > > I need to efficiently query the differences between "runs" - i.e. For each > (domain_id, mta_id) tuple in run 1, is there a coresponding tuple in run 2 > where either attribute1 or attribute2 have changed? > > The only way I have been able to think of doing this so far is an o(n^2) > search, which even with indexes takes a long time. e.g. > > select * from t t1 where exists (select 1 from t t2 where > t2.mta_id=t1.mta_id and t2.domain_id=t1.domain_id and (t2.attribute1 != > t1.attribute1 or t2.attribute2 != t1.attribute2) > > This query takes millenia... > > Any help would be greatly appreciated. I hope I am naively missing some > obvious alternative strategy, since this sort of operation must be common in > databases. A self-join will probably perform better in your case. Consider the following query: select t1.domain_id as domain_id, t1.mta_id as mta_id, t1.run_id as run_id_1, t1.attribute1 as attribute1_1, t1.attribute2 as attribute2_1, t2.run_id as run_id_2, t2.attribute1 as attribute1_2, t2.attribute2 as attribute2_2 from t t1 join t t2 on t1.mta_id = t2.mta_id and t1.domain_id = t2.domain_id and (t1.attribute1 != t2.attribute1 or t1.attribute2 != t2.attribute2) You are basically joining together 2 copies of the same table (one aliased to "t1" and the other aliased to "t2"). The logic in the join conditions are the same you had in the subselect. You will probably want to constrain the query some more in the join conditions. As my example above is currently written, you will get 2 records for each true difference (e.g. for a given mta_id/domain_id, you'll get 1 record when run_id X comes from t1 and run_id Y comes from t2 & you get another record describing the same difference when run_id X comes from t2 and run_id Y comes from t1). Some example constraints that might help are: t1.run_id < t2.run_id Assuming run_id increases chronologically, this will return 1 record for each case there is a difference in attributes from any prior run. t1.run_id + 1 = t2.run_id Again assuming run_id increases chronologically, this will only return a record when there are attribute differences between 2 successive runs. Indexes should also be considered for the performance of this query. At a minimum, you will probably want a compound index on (domain_id, mta_id). [... thinks for a bit ...] Actually, you could re-order your unique constraint to take care of this, as a unique constraint is basically an index with a unique constraint on it. Your unique constraint is currently on (run_id, domain_id, mta_id). If you re-order that unique constraint to (domain_id, mta_id, run_id), you will probably see a couple of benefits: - Due to how PG can use the first parts of a compound index, it will be able to use the (domain_id, mta_id) portion as an index to help your joins. - I assume domain_id is much more selective than run_id, so the unique checks should speed up as well. The way you have the unique constraint written, the index is first scanned based on run_id which isn't very selective - once it finds the correct run_id in the index, there will still be a lot of index entries to scan to match on domain_id and mta_id. Re-ordering the way I propose should narrow down the number of index entries quite quickly, as it will first be narrowed on domain_id, then mta_id, and finally run_id. Hope this helps. Andrew ---(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] how to moce back in refcursor
Hi
I have never worked with cursors but for now I have to step back on special
events to calculate a difference. So I have this sample code tested but I can
not "move" in my cursor. I have lokke at docs but this didn't help me. Can you
say how to declare my cursor to step back?
Thank You
Chris
PS code sample
create table fimi.eodbar(fi_id int8, market_id int8);
insert into fimi.eodbar values(322,200);
create or replace function fimi.test("fiid" int8) returns setof int as
$BODY$
DECLARE
cur1 REFCURSOR;
foo int;
BEGIN
OPEN cur1 FOR execute('select market_id from fimi.eodbar where fi_id=' ||
"fiid");
loop
fetch cur1 into foo;
if not found then
exit ;
else
-- move -1 in cur1;
fetch cur1 into foo;
return next foo;
end if;
end loop;
close cur1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION fimi.getfoo(bpchar) OWNER TO fimi;
select * from fimi.test(322);
--
cu
Chris
GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Comparing two slices within one table efficiently
Yes and you could make it even more speedy with the use table partitioning. http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html > select > t1.domain_id as domain_id, > t1.mta_id as mta_id, > t1.run_id as run_id_1, > t1.attribute1 as attribute1_1, > t1.attribute2 as attribute2_1, > t2.run_id as run_id_2, > t2.attribute1 as attribute1_2, > t2.attribute2 as attribute2_2 > from > t t1 > join t t2 on > t1.mta_id = t2.mta_id and > t1.domain_id = t2.domain_id and > (t1.attribute1 != t2.attribute1 or t1.attribute2 != t2.attribute2) -- cu Chris Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Comparing two slices within one table efficiently
"=?utf-8?B?S2VuIFNpbXBzb24=?=" <[EMAIL PROTECTED]> writes: > select * from t t1 where exists (select 1 from t t2 where > t2.mta_id=t1.mta_id and t2.domain_id=t1.domain_id and (t2.attribute1 > != t1.attribute1 or t2.attribute2 != t1.attribute2) > This query takes millenia... Yeah, because you're effectively forcing the least efficient style of join --- a nestloop is generally going to suck for a full-table join, even if you've got indexes. Try something like this: select * from t t1 join t t2 on (t2.mta_id=t1.mta_id and t2.domain_id=t1.domain_id) where (t2.attribute1 != t1.attribute1 or t2.attribute2 != t1.attribute2) Make sure you've got work_mem cranked up to something appropriate. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] how to moce back in refcursor
"Christian Kindler" <[EMAIL PROTECTED]> writes: >-- move -1 in cur1; plpgsql supports that in CVS HEAD (8.3-to-be) but not in any existing release. You'll need to rethink your logic or do this from your application rather than within plpgsql. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Getting pk of the most recent row, in a group by
I've got a table of "coupons" which have an expiration date. For each type of coupon, I'd like to get the primary key of the coupon which will expire first. # create table coupon ( coupon_id serial primary key, type varchar(255), expires date ); insert into coupon values(DEFAULT,'free','2007-01-01'); insert into coupon values(DEFAULT,'free','2007-01-01'); insert into coupon values(DEFAULT,'free','2007-06-01'); insert into coupon values(DEFAULT,'free','2007-06-01'); insert into coupon values(DEFAULT,'50%','2008-06-01'); insert into coupon values(DEFAULT,'50%','2008-06-02'); insert into coupon values(DEFAULT,'50%','2008-06-03'); The desired query would look like: # select coupon_id,type,expires from coupon where type='free' order by expires limit 1; coupon_id | type | expires ---+--+ 1 | free | 2007-01-01 But be grouped by type: # select type,min(expires),count(*) from coupon group by type; type |min | count --++--- free | 2007-01-01 | 4; pk=1 50% | 2008-06-01 | 3; pk=5 In the second example, is it possible to get the primary key of the row with the minimum expires time? -- Visit http://www.obviously.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Comparing two slices within one table efficiently
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Christian Kindler [13/08/07 21:34 +0200]: > Yes and you could make it even more speedy with the use table partitioning. > http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html Thanks for all your speedy help, everyone. I tried doing a "self join" and that sped things up enormously (query took on the order of 30 seconds to compare two million-row table slices, resulting in a 20K row result). I will also try re-ordering the unique constraint to get speedier indexing out of it and will look at table partitioning. Regards, Ken - -- Ken Simpson CEO, MailChannels Fax: +1 604 677 6320 Web: http://mailchannels.com MailChannels - Reliable Email Delivery (tm) -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGwLQq2YHPr/ypq5QRApP8AKDfRGqDFkcONh0YaojX7362nXP12gCg3WZ6 k5ZBwcMplXyVkEguQtbgdFU= =bsyu -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Getting pk of the most recent row, in a group by
On 8/13/07, Bryce Nesbitt <[EMAIL PROTECTED]> wrote: > In the second example, is it possible to get the primary key of the row > with the minimum expires time? SELECT TYPE, MIN(expires), COUNT(*) , (SELECT MIN(coupon_id) FROM coupon WHERE expires = MIN(c.expires)) AS coupon_id FROM coupon c GROUP BY TYPE; ---(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] Getting pk of the most recent row, in a group by
On Aug 13, 2007, at 15:05 , Bryce Nesbitt wrote: # select type,min(expires),count(*) from coupon group by type; type |min | count --++--- free | 2007-01-01 | 4; pk=1 50% | 2008-06-01 | 3; pk=5 In the second example, is it possible to get the primary key of the row with the minimum expires time? I believe DISTINCT ON will do what you want, if you don't mind using non-SQL-spec functionality: SELECT DISTINCT ON (type) type, expires, coupon_id FROM coupon ORDER BY type, expires; I believe you'd need to add the COUNT using a join: SELECT type, expires, coupon_id, type_count FROM ( SELECT DISTINCT ON (type) type, expires, coupon_id FROM coupon ORDER BY type, expires ) earliest_to_expire JOIN ( SELECT type, count(coupon_id) as type_count FROM coupons GROUP BY type ) type_counts USING (type); Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [PERFORM] Performance on writable views
On Aug 11, 2007, at 8:58 AM, Joshua D. Drake wrote: Heikki Linnakangas wrote: Enrico Weigelt wrote: I'm often using writable views as interfaces to clients, so they only see "virtual" objects and never have to cope with the actual storage, ie. to give some client an totally denormalized view of certain things, containing only those information required for certain kind of operations. Now I've got the strange feeling that this makes updates slow, since it always has to run the whole view query to fetch an record to be updated (ie. to get OLD.*). There is some overhead in rewriting the query, but it shouldn't be significantly slower than issuing the statements behind the view directly. I wouldn't worry about it, unless you have concrete evidence that it's causing problems. I don't know about that, at least when using rules for partitioning the impact can be significant in comparison to triggers. That's because you have to re-evaluate the input query for each rule that's defined, so even if you only have rules for 2 partitions in a table (which is really about the minimum you can have, at least for some period of overlap surrounding the time when you switch to a new partition), you're looking at evaluating every input query twice. In this case, the rules presumably are just simply re-directing DML, so there'd only be one rule in play at a time. That means the only real overhead is in the rewrite engine. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Getting pk of the most recent row, in a group by
Do you have a table of coupon types? Terry Terry Fielder [EMAIL PROTECTED] Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085 Bryce Nesbitt wrote: I've got a table of "coupons" which have an expiration date. For each type of coupon, I'd like to get the primary key of the coupon which will expire first. # create table coupon ( coupon_id serial primary key, type varchar(255), expires date ); insert into coupon values(DEFAULT,'free','2007-01-01'); insert into coupon values(DEFAULT,'free','2007-01-01'); insert into coupon values(DEFAULT,'free','2007-06-01'); insert into coupon values(DEFAULT,'free','2007-06-01'); insert into coupon values(DEFAULT,'50%','2008-06-01'); insert into coupon values(DEFAULT,'50%','2008-06-02'); insert into coupon values(DEFAULT,'50%','2008-06-03'); The desired query would look like: # select coupon_id,type,expires from coupon where type='free' order by expires limit 1; coupon_id | type | expires ---+--+ 1 | free | 2007-01-01 But be grouped by type: # select type,min(expires),count(*) from coupon group by type; type |min | count --++--- free | 2007-01-01 | 4; pk=1 50% | 2008-06-01 | 3; pk=5 In the second example, is it possible to get the primary key of the row with the minimum expires time? ---(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] how to move back in refcursor
Oh I see But damn I need it in plpgsql because all my Application logic is delevoped in the backend. Ok maybe I can work around this. Thank you very much! Chris Original-Nachricht Datum: Mon, 13 Aug 2007 15:55:40 -0400 Von: Tom Lane <[EMAIL PROTECTED]> An: "Christian Kindler" <[EMAIL PROTECTED]> CC: pgsql-sql@postgresql.org Betreff: Re: [SQL] how to moce back in refcursor > "Christian Kindler" <[EMAIL PROTECTED]> writes: > >-- move -1 in cur1; > > plpgsql supports that in CVS HEAD (8.3-to-be) but not in any existing > release. You'll need to rethink your logic or do this from your > application rather than within plpgsql. > > regards, tom lane -- cu Chris Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser ---(end of broadcast)--- TIP 6: explain analyze is your friend
