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 ready

So, I raise my glass to you! Thank you!

Contributors: James Robinson jlrobins at socialserve.com

Vacuum Options
Vacuum Full, Analyze, Lazy, Auto	06-Oct-2004

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;

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.

Contributors: elein at varlena.com

Faster ORDER BY
[GENERAL] Indexing Order By columns	06-Sep-2004

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)

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.

• how to declare and run a function which returns a rowtype
• how to pass a rowtype to another function
• how to change a rowtype in a subfunction and return it.

Contributors: Henry Combrinck henry at metroweb.co.za, Tom Lane tgl at sss.pgh.pa.us, Rory Campbell-Lange rory at campbell-lange.net, elein at varlena.com Alvaro Herrera alvherre at dcc.uchile.cl