Re: [GENERAL] Default fill factor for tables?
On Fri, Jul 11, 2008 at 5:53 PM, Simon Riggs <[EMAIL PROTECTED]> wrote: > > On Fri, 2008-07-11 at 15:25 -0600, Scott Marlowe wrote: >> On Fri, Jul 11, 2008 at 3:24 PM, Simon Riggs <[EMAIL PROTECTED]> wrote: >> > >> > On Fri, 2008-07-11 at 14:51 -0600, Scott Marlowe wrote: >> > >> >> I would kindly disagree. I'm looking at a project where HOT updates >> >> are going to be a real performance enhancement, but I'll have to >> >> create a hundred or so tables ALL with fillfactor tacked on the end. >> > >> > You clearly think that adjusting fillfactor helps in all cases with HOT. >> > I disagree with that, else would have pushed earlier for exactly what >> > you suggest. In fact, I've has this exact discussion previously. >> >> How odd, because that's clearly NOT what I said. In fact I used the >> single "a" to describe the project I was looking at where having a >> default table fill factor of < 100 would be very useful. OTOH, I have >> stats databases that have only insert and drop child tables that would >> not benefit from < 100 fill factor. For a heavily updated database, >> where most of the updates will NOT be on indexed columns, as the ONE >> project I'm looking at, a default fill factor would be quite a time >> saver. > > I apologise if my phrasing sounded confrontational. > > For specific workloads, tuning of particular tables can be effective, > > I have not heard of evidence that setting fillfactor < 100 helps as an > across-the-board tuning measure on longer-term tests of performance. > Theoretically, it makes little sense, but current theory is not always > right. Until we have even hear-say evidence of benefit, introducing a > parameter would be inadvisable, IMHO. I will change that view in an > instant, with reasonable evidence. Ok, here's my scenario. We have a content management / calendaring / social networking website. The tables in this db fall into two categories, and that's either small lookup tables like a list of states which are seldom updated, or fairly large tables with lots of data that are updated constantly. There are literally several hundred medium to large tables that are updated constantly. There are a dozen or so lookup tables, which are small. Now, if I had a default fill factor of 90%, I doubt you could detect a performance slow down on the smaller tables. But I'm quite sure we'll see a difference on the large heavily updated tables. Now, I've got about 20Gigs of data to migrate from 8.1 to 8.3 and I'll be dumping the schema and data separately for this one. So I can go in and edit every single create table and create index portion of the schema to set a fill factor. It would be so much easier to group my tables by type and set a default fill factor. It's not like there isn't already a default fill factor, there IS. It's just hard coded into pgsql. I can't see where having a knob exposed to change that would be a particularly bad thing. Can't be more of a foot gun than work_mem. -- 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] Top N within groups?
On Fri, Jul 11, 2008 at 01:24:28PM +1000, Klint Gore wrote: > Can someone familiar with the source for DISTINCT ON comment on how hard > it would be to add another parameter to return more than one row? you can make top-n per group quite easily using the technique described here: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/ best regards, depesz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Wiki: FreeOpenSourceSoftware.org
Hi PostgreSQL folks, I'm the author of the Internet site http://www.livinginternet.com/ with input from many of the original creators of the Internet. I was lucky enough to get the FreeOpenSourceSoftware and FreeLibreOpenSourceSoftware domains, and have donated them to the FOSS community. I have set up a wiki at the domains to provide a home for FOSS, specifically as separate from Commercial Open Source Software (COSS) with some published code but closed elements for advanced functionality that potentially leads right back to proprietary lock-in. The link to "FOSS Philosophy" starts with a quote from Adam Smith (!), and describes the clear FOSS / COSS separation I believe important to help the FOSS brand continue to succeed. I've seeded the Wiki with basic starting information. Feel free to add. Please spread the word to let others know of the launch! http://freeopensourcesoftware.org/ Cheers, Bill -- Bill Stewart Founder, Eseri.net http://Eseri.net/ p: 613-796-8529 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Relation not gfound in view Error
I created two tables populated both with valid data including forgien key values. I create a view that uses both of these tables in a view that. When I select from the view, I get this error ERROR: relation "vwsinglelevellist" does not existSQL state: 42P01 But when I run the defination as a simple select statement, I do not get an error. Any ideas? TIA Michael
[GENERAL] Users functions library
Hi, I need to write a function that gives me a password string, no just a numbers-characters string; something like people wrote in php, its based on determined syllables and numbers. I think it be useful to other people, is there a site where one can post it and share with other postgres users? Alejandro -- 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] Users functions library
2008/7/12 Alejandro D. Burne <[EMAIL PROTECTED]>: > Hi, I need to write a function that gives me a password string, no > just a numbers-characters string; something like people wrote in php, > its based on determined syllables and numbers. > I think it be useful to other people, is there a site where one can > post it and share with other postgres users? > > Alejandro > Sorry, the function: CREATE OR REPLACE FUNCTION gen_password(plenght smallint) RETURNS bpchar AS $BODY$ DECLARE lValid_Consonant bpchar DEFAULT 'BCDFGHJKMNPRSTV'; lValid_Vowel bpchar DEFAULT 'AEIOUY'; lValid_Numbers bpchar DEFAULT '23456789'; lConsonant_Length smallint DEFAULT char_length(lValid_Consonant); lVowel_Length smallint DEFAULT char_length(lValid_Vowel); lNumbers_Length smallint DEFAULT char_length(lValid_Numbers); lPassword bpchar DEFAULT ''; BEGIN LOOP IF ROUND(RANDOM()*3)<>1 THEN lPassword:=lPassword||SUBSTRING(lValid_Consonant FROM (ROUND(RANDOM()*(lConsonant_Length-1))+1)::integer FOR 1)|| SUBSTRING(lValid_Vowel FROM (ROUND(RANDOM()*(lVowel_Length-1))+1)::integer FOR 1); IF ROUND(RANDOM()*2)<>1 THEN lPassword:=lPassword||SUBSTRING(lValid_Consonant FROM (ROUND(RANDOM()*(lConsonant_Length-1))+1)::integer FOR 1); END IF; ELSE lPassword:=lPassword||SUBSTRING(lValid_Numbers FROM (ROUND(RANDOM()*(lNumbers_Length-1))+1)::integer FOR 1); END IF; IF char_length(lPassword) >= plenght THEN EXIT; END IF; END LOOP; RETURN SUBSTRING(lPassword FROM 1 FOR plenght); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; -- 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] Users functions library
Hello safe it to pgfoundry http://pgfoundry.org/ regards Pavel Stehule 2008/7/13 Alejandro D. Burne <[EMAIL PROTECTED]>: > 2008/7/12 Alejandro D. Burne <[EMAIL PROTECTED]>: >> Hi, I need to write a function that gives me a password string, no >> just a numbers-characters string; something like people wrote in php, >> its based on determined syllables and numbers. >> I think it be useful to other people, is there a site where one can >> post it and share with other postgres users? >> >> Alejandro >> > > Sorry, the function: > > CREATE OR REPLACE FUNCTION gen_password(plenght smallint) > RETURNS bpchar AS > $BODY$ > > DECLARE > lValid_Consonant bpchar DEFAULT 'BCDFGHJKMNPRSTV'; > lValid_Vowel bpchar DEFAULT 'AEIOUY'; > lValid_Numbers bpchar DEFAULT '23456789'; > > lConsonant_Length smallint DEFAULT char_length(lValid_Consonant); > lVowel_Length smallint DEFAULT char_length(lValid_Vowel); > lNumbers_Length smallint DEFAULT char_length(lValid_Numbers); > > lPassword bpchar DEFAULT ''; > > BEGIN > LOOP > IF ROUND(RANDOM()*3)<>1 THEN > lPassword:=lPassword||SUBSTRING(lValid_Consonant FROM > (ROUND(RANDOM()*(lConsonant_Length-1))+1)::integer FOR 1)|| > SUBSTRING(lValid_Vowel FROM > (ROUND(RANDOM()*(lVowel_Length-1))+1)::integer FOR 1); > IF ROUND(RANDOM()*2)<>1 THEN > lPassword:=lPassword||SUBSTRING(lValid_Consonant FROM > (ROUND(RANDOM()*(lConsonant_Length-1))+1)::integer FOR 1); > END IF; > ELSE > lPassword:=lPassword||SUBSTRING(lValid_Numbers FROM > (ROUND(RANDOM()*(lNumbers_Length-1))+1)::integer FOR 1); > END IF; > IF char_length(lPassword) >= plenght THEN > EXIT; > END IF; > END LOOP; > > RETURN SUBSTRING(lPassword FROM 1 FOR plenght); > END; > > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > -- > 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
