|
||||||||||||||||
|
||||||||||||||||
|
||||||||||||||||
|
||||||||||||||||
|
||||||||||||||||
This kind of message is the kind we like to see :-) Postgresql Hackers, Just a quick but heartfelt "Thank You!" to all of you who make postgresql as sturdy as it is. Noon today, our datacenter provider suffered an, um, interruption in their uninterruptable redundant power supply systems. Power was restored seconds later, and this is what the postmaster had to say about it: LOG: database system was interrupted at 2004-10-08 11:55:47 EDT FATAL: the database system is starting up LOG: checkpoint record is at 1C/2A4CF040 LOG: redo record is at 1C/2A4CF040; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 16776013; next OID: 3686077 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 1C/2A4CF080 LOG: record with zero length at 1C/2A521034 LOG: redo done at 1C/2A521010 FATAL: the database system is starting up FATAL: the database system is starting up LOG: database system is readyWe were back online within minutes of the interruption w/o any data loss. So, I raise my glass to you! Thank you!
Vacuum reclaims deleted rows and free space for PostgreSQL. It is required because PostgreSQL has a lazy delete system where tuples are marked for deletion but not actually deleted until VACUUM is run. VACUUM without FULL does not use exclusive locks and so it can be used on active databases. VACUUM with FULL uses locks and does more rearranging of data to compact the table. VACUUM with FULL is also (understandably) a bit slower because it does more work. In the postgresql.conf file the variable DEFAULT_STATISTICS_TARGET sets the maximum number of entries in the most-common-value list and the maximum number of bins for the histograms used in calculating the statistics for all tables. The default value is 10, but may be changed to a value from 0 to 1000. A value of 0 effectively (and unwisely) disables statistics. A higher value will take more space and time during an ANALYZE or VACUUM ANALYZE command. The higher value will also create more accurate statistics and therefore more accurately optimized queries. It is possible to tune the statistics for particular columns in tables by using the ALTER TABLE command. ALTER TABLE ALTER COLUMN SET STATISTICS 10;This setting overrides the postgresql.conf DEFAULT_STATISTICS_TARGET setting for the column specified. For columns never used in a WHERE, GROUP BY or ORDER BY clause, this value can be set lower. For columns often used, it could be higher with the caveat that ANALYZE will be a tiny bit more costly. To unset the statistics setting on a particular column and have it use the setting in DEFAULT_STATISTICS_TARGET, use ALTER TABLE to set the value to -1. VACUUM VERBOSE can help you set your free space mapping variables in the postgresql.conf. This technique is described in the first article in Issue #79. It is important to notice when plain VACUUM does not reclaim space and yet VACUUM FULL does reclaim space; this may indicate that your free space map settings are too low. Setting Free Space Mappings is discussed in the sixth article in Issue #13.
If you need to sort your output results by the same column(s) often it may be worthwhile to index the order by columns. Also keep in mind, that if you are ordering by a function or an expression, for example, lower(email_name), use a functional index. CREATE INDEX email_name_idx on email_addrs ( lower(email_name) );
In 7.4 it is possible to create a row type and return it from a function. Other than the creation of the row type's values the only thing you must remember is to call it from the right side of the FROM. create type abc_ty as ( acol text, bcol integer, ccol float ); create or replace function abcout() returns abc_ty AS ' DECLARE myrow abc_ty%ROWTYPE; out text; BEGIN select into myrow ''this is acol'', 15, 87.2; return myrow; END; ' language 'plpgsql'; elein=# select * from abcout(); acol | bcol | ccol --------------+------+------ this is acol | 15 | 87.2 (1 row) But suppose you wanted to pass that row type to another function? In 7.4 you cannot do that but in 8.0 (Beta3) you can. In this version of the function we are calling a the function abcshow(abc_ty) with the row we just created. In 7.4 this will cause an error about the variable myrow but in 8.0 it does run clean. The function abcshow(abc_ty) simply dissects the row into a text string and returns that. Then the first function raises a notice to see what that string looks like. create or replace function abcout() returns abc_ty AS ' DECLARE myrow abc_ty%ROWTYPE; out text; BEGIN select into myrow ''this is acol'', 15, 87.2; select into out abcshow( myrow ); RAISE NOTICE ''%'', out; return myrow; END; ' language 'plpgsql'; create or replace function abcshow( abc_ty ) returns text as ' DECLARE inrow abc_ty%ROWTYPE; out text; BEGIN inrow := $1; out := ''abcshow: acol=['' || inrow.acol || ''], bcol=['' || \ inrow.bcol::text || ''], ccol=[''|| to_char( inrow.ccol, ''99.9'') || '']''; return out; END; ' language 'plpgsql'; elein=# select * from abcout(); NOTICE: abcshow: acol=[this is acol], bcol=[15], ccol=[ 87.2] acol | bcol | ccol --------------+------+------ this is acol | 15 | 87.2 (1 row) We can see that that works. But what if you changed the row in the second function? Add a line to increment bcol to the abcshow(abc_ty) function and run it again. We then get the following: elein=# select * from abcout(); NOTICE: abcshow: acol=[this is acol], bcol=[16], ccol=[ 87.2] acol | bcol | ccol --------------+------+------ this is acol | 15 | 87.2 (1 row)The value of bcol was changed only within the scope of the second function. From that we can assume that the row is passed By Value. As an experiment, we took the increment of bcolo out of abcshow() and create a new function called abcinc(acb_ty) which did the addition and returned the resulting row instead of the string that abcshow(abc_ty) returned. And then we changed the initial abcout(abc_ty) function to first call abcshow() and raise notice with the text output and then call the incrementing function, catching the result. Since we have it, then we called abcoout(abc_ty) with the incremented row. Then, instead of returning the original row from the first function, we returned the incremented row so that we can see it was actually incremented in the correct context. create or replace function abcout() returns abc_ty AS ' DECLARE myrow abc_ty%ROWTYPE; otherrow abc_ty%ROWTYPE; out text; BEGIN select into myrow ''this is acol'', 15, 87.2; select into out abcshow( myrow ); RAISE NOTICE ''%'', out; select into otherrow * from abcinc( myrow ); select into out abcshow( otherrow ); RAISE NOTICE ''%'', out; return otherrow; END; ' language 'plpgsql'; create or replace function abcinc( abc_ty) returns abc_ty as ' DECLARE inrow abc_ty%ROWTYPE; out text; BEGIN inrow := $1; inrow.bcol := inrow.bcol + 1; return inrow; END; ' language 'plpgsql'; elein=# select * from abcout(); NOTICE: abcshow: acol=[this is acol], bcol=[15], ccol=[ 87.2] NOTICE: abcshow: acol=[this is acol], bcol=[16], ccol=[ 87.2] acol | bcol | ccol --------------+------+------ this is acol | 16 | 87.2 (1 row) This is what we've shown in these experiments with 8.0.
|
||||||||||||||||
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in! Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009 |