Re: [GENERAL] plpgsql functions
2008/8/15 c k [EMAIL PROTECTED]: Hi, I am getting an error for a function written in plpgsql, as - CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer) RETURNS SETOF uf_closingbal AS $BODY$begin select accgroups.accgroupid, COALESCE(sum(osc),0) as obc, COALESCE(sum(osd),0) as obd, COALESCE(sum(csc),0) as sc, COALESCE(sum(csd),0) as sd, (COALESCE(sum(osc),0)+COALESCE(sum(csc),0)) as cc, (COALESCE(sum(osd),0)+COALESCE(sum(csd),0)) as cd, accgroups.localglobal, accgroups.companyid, accgroups.isledgeraccount, accgroups.accgrouplevel, accgroups.mastergroup, accgroups.accobjectid, accgroups.accobjecttype, accgroups.againstid from (accgroups left join (select * from uf_accgroupob($1)) as accob on accgroups.accgroupid=accob.accgroupid) left join (select * from uf_accgroupcurrentbal($1, $2)) as accgcb2 on accgroups.accgroupid = accgcb2.accgroupid group by accgroups.accgroupid, accgroups.localglobal, accgroups.companyid, accgroups.isledgeraccount, accgroups.accgrouplevel, accgroups.mastergroup, accgroups.accobjectid, accgroups.accobjecttype, accgroups.againstid; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ROWS 1000; giving me an error when called from another function as 'query has no destination for result data'. Why? Please give the details about creating functions having only SELECT statements using plpgsql?, What points to be remembered? pl/pgsql isn't SQL/PSM - so there are not allowed one statement functions. Use SQL language instead. Regards Pavel Stehule Thanks CPK -- 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] What's size of your PostgreSQL Database?
On Fri, Aug 15, 2008 at 9:42 PM, Amber [EMAIL PROTECTED] wrote: Dear all: We are currently considering using PostgreSQL to host a read only warehouse, we would like to get some experiences, best practices and performance metrics from the user community, following is the question list: 1. What's size of your database? Varies. I've had reporting dbs in the low 100s of gigabytes. 2. What Operating System are you using? I've generally worked with Linux. RHEL, Centos, or Ubuntu. 3. What level is your RAID array? For transactional, ALWAYS RAID 10. For reporting sometimes RAID-5, mostly RAID-10 The reporting server I built at my last company was a collection of spare parts and ran a software RAID-10 over 4 150G sata drives. It routinely outran the Oracle RAC cluster with 14 drives in RAID 6 sitting next to it doing reports on the same data. 4. How many cores and memory does your server have? The reporting server from my last company had a single hyperthreaded P4 and 4 Gig of ram. Current transactional server runs on 8 opterons, with 32 Gigs of ram. 5. What about your performance of join operations? Always been pretty good. Kind of a wide open question really. I'd say PostgreSQL's query planner is usually very smart planning complex queries. note that joins were never an issue, but I had to pay attention to how I designed correlated subqueries and aggregate queries. 6. What about your performance of load operations? Pretty much dependent on the hardware you're on. I can replicate the current ~15 Gig transactional db in about 15 or 20 minutes from one 8 core 16 drive machine to another. 7. How many concurrent readers of your database, and what's the average transfer rate, suppose all readers are doing one table scaning. Concurrent but idle connections in production are around 600. Active connections at a time are in the dozens. I can read at about 60 to 70 Megs a second for random access and around 350 to 400 Megs a second for sequential reads. 8. Single instance or a cluster, what cluster software are you using if you have a cluster? Two machines with one as slony master and the other as slony slave, with the application doing weighted load balancing on reads between the two. The important thing about pgsql is how well it scales to work on larger hardware. If you throw enough drives on a quality RAID controller at it you can get very good throughput. If you're looking at read only / read mostly, then RAID5 or 6 might be a better choice than RAID-10. But RAID 10 is my default choice unless testing shows RAID-5/6 can beat it. -- 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] How to tell if a trigger is disabled
On Fri, Aug 15, 2008 at 5:59 PM, Tom Lane [EMAIL PROTECTED] wrote: Ian Harding [EMAIL PROTECTED] writes: Is there any way to tell if a trigger or triggers are disabled on a table? I was updating some data a week or two ago and must have forgotten to re-enable the triggers. Took me a little while to figure out. \d tablename didn't tell me, nor did \d+ tablename. This is on 8.2.3. FWIW, 8.3's psql knows about showing this in \dt. Yet another reason to upgrade.. Thanks! - Ian -- 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] Confronting the maximum column limitation
On Aug 12, 2008, at 3:15 PM, Jeff Gentry wrote: So I've seen the header file where the 1600 column limit is defined IIRC, that limit is directly related to block size in the header, so one possible fix is to increase block size. AFAIK anything up to 64K blocks should be safe. BTW, keep in mind that if you're storing anything that's a varlena (anything that's variable length, including NUMBER) where you have that many columns, every single varlena is going to end up toasted. That's bound to have a *serious* performance impact. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Changing between ORDER BY DESC and ORDER BY ASC
On Aug 15, 2008, at 12:35 PM, William Garrison wrote: Is there an easy way to write one single query that can alternate between ASC and DESC orders? Ex: CREATE OR REPLACE FUNCTION GetSomeStuff(_start integer, _count integer, _sortDesc boolean) RETURNS SETOF text AS $BODY$ SELECT something FROM whatever WHERE whatever ORDER BY another_column OFFSET $1 LIMIT $2 ($4 = true ? 'DESC' : 'ASC'); $BODY$ LANGUAGE 'sql' VOLATILE; I can think of a few ways, but I am hoping for something more elegant. 1) In my case another_column is numeric, so I could multiple by negative one if I want it in the other order. Not sure what this does to the optimizer if the column is indexed or not. In my experience, it's pretty rare for an index to be used to satisfy an ORDER BY. 2) I could write the statement twice, once with ASC and once with DESC, and then use IF/ELSE structure to pick one. 3) I could generate the statement dynamically. I am hoping there is some super secret extension that can handle this. This seems like one of those foolish things in SQL, where it is too declarative. ASC and DESC should be parameters to order by, not a part of the syntax. But I digress... any other suggestions? None that I can think of, unfortunately. It might not be horribly hard to allow plpgsql to use a variable for ASC vs DESC; that might be your best bet. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
[GENERAL] selecting data from subquery in same order
hi if i execute this statement: select * from users where id in (2341548, 2325251, 2333130, 2015421, 2073536, 2252374, 2273219, 2350850, 2367318, 2032977, 2032849, ) the order of rows obtained is random. is there anyway i can get the rows in the same order as the ids in subquery? or is there a different statement i can use? thanks! -- 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] Confronting the maximum column limitation
On Sat, Aug 16, 2008 at 1:28 PM, Decibel! [EMAIL PROTECTED] wrote: On Aug 12, 2008, at 3:15 PM, Jeff Gentry wrote: So I've seen the header file where the 1600 column limit is defined IIRC, that limit is directly related to block size in the header, so one possible fix is to increase block size. AFAIK anything up to 64K blocks should be safe. Unless something's changed, I'm pretty sure things start breaking after 32k blocks. BTW, keep in mind that if you're storing anything that's a varlena (anything that's variable length, including NUMBER) where you have that many columns, every single varlena is going to end up toasted. That's bound to have a *serious* performance impact. Yeah, usually you're better off using arrayed types than 1600+ columns. -- 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] selecting data from subquery in same order
On Sat, Aug 16, 2008 at 6:11 PM, mark [EMAIL PROTECTED] wrote: hi if i execute this statement: select * from users where id in (2341548, 2325251, 2333130, 2015421, 2073536, 2252374, 2273219, 2350850, 2367318, 2032977, 2032849, ) the order of rows obtained is random. is there anyway i can get the rows in the same order as the ids in subquery? or is there a different statement i can use? thanks! Technically, that's just a list, not a subquery, but that's not important right now. You can use a case statement. select field1, field2, idfield from users where id in (1,4,3) order by case when idfield=1 then 1 when idfield=3 then 2 when idfield=4 then 3 end -- 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] selecting data from subquery in same order
On Sat, Aug 16, 2008 at 8:07 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Sat, Aug 16, 2008 at 6:11 PM, mark [EMAIL PROTECTED] wrote: hi if i execute this statement: select * from users where id in (2341548, 2325251, 2333130, 2015421, 2073536, 2252374, 2273219, 2350850, 2367318, 2032977, 2032849, ) the order of rows obtained is random. is there anyway i can get the rows in the same order as the ids in subquery? or is there a different statement i can use? thanks! Technically, that's just a list, not a subquery, but that's not important right now. You can use a case statement. select field1, field2, idfield from users where id in (1,4,3) order by case when idfield=1 then 1 when idfield=3 then 2 when idfield=4 then 3 end oops, that should be when idfield=1 then 1 when idfield=4 then 2 when idfield=3 then 3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
