The first build of 7.4 for beta is now available. Go to the PostgreSQL Mirrors to choose a mirror and then select beta. Alternatively, you can go to the developers beta site The CVS tag is REL7_4_BETA1. Both .gz and .bz2 file formats are available.

Alternatively, you can pick up CVS HEAD and stay up to the minute with the bug fixes being applied to this beta release. You can watch or review the changes with the CVS Web interface. You can also download individual files from the CVS WEB.

Beta releases, as you know, are not production quality releases. (Well, they might be, but don't make that assumption.)

It is extremely critical for as many of us as possible to download and test 7.4. This is our primary opportunity to have wide community feedback about the release. Test and Speak now! The bugs, if any, that you find will be given high priority. And all of this will lead to an even more solid productuction release for 7.4.

Bugs found in the beta release should be reported in pgsql-bugs or pgsql-hackers. And they are coming in as we speak, ahem, write, read, whatever... The early bugs are seem to be mostly platform specific. The following warning messages can be ignored for now. It is flex complaining.

	 In file included from preproc.y:6278:
	 pgc.c: In function `yylex':
	 pgc.c:1386: warning: label `find_rule' defined but not used
	 /usr/include/ctype.h: At top level:
	 pgc.c:3367: warning: `yy_flex_realloc' defined but not used

Note: Now is also the time to be thinking about your upgrade path. Plan for it and schedule time to upgrade to 7.4 when it is released for production.

Contributors: Marc Fournier at postgresql.org elein at varlena.com

Feature Overview of PL/R [ANNOUNCE] GBORG NEWS: PL/R Version 0.5.0-alpha 5-Aug-2003

PL/R is the R Procedural Language for PostgreSQL. It enables user-defined SQL functions to be written in the R language.

R is `GNU S' - A language and environment for statistical computing and graphics. R is similar to the award-winning S system, which was developed at Bell Laboratories by John Chambers, et al. It provides a wide variety of statistical and graphical techniques (linear and nonlinear modelling, statistical tests, time series analysis, classification, clustering, etc.). PL/R allows access to R's analytical power efficiently and consistently, while enabling leverage of scarce specialty skills and processing hardware.

PL/R is one of the most advanced Procedural Languages available for PostgreSQL. It supports most, if not all, of the relevant new features implemented in PostgreSQL 7.3 and 7.4. Specifically:

• Table Function support (can return rows and columns).
• Converts Postgres array arguments to R vectors, matrixes, or 3D arrays as appropriate, and vice-versa on return.
• Allows polymorphic function definition (arguments and return type may be anyarray or anyelement at function creation time allowing the same function to disparate or similar types. accept, e.g. INT8 on one call, and TEXT on the next).
• Compiled function cache specifically supports polymorphic functions.
• Preinitialization support (preload_libraries)
• "firstpass" variable support (allow global variables to be reset on the first call to the function within a SQL statement).
• Nested error contexts.

You must have downloaded and installed R in order to use PL/R. PL/R can be downloaded and reviewed from GBORG Documentation for PL/R is available at http://www.joeconway.com/plr/doc/index.html Contributors: Joe Conway mail at joeconway.com

Fun with Aging [GENERAL] birthday calculation 22-Jul-2003

Given a timestamp the age() function will return an interval which is the difference between now and the timestamp. So how old are you really?

	elein=# SELECT age('12/31/59'::timestamp);
	            age             
	----------------------------
	 -56 years -4 mons -23 days

Let's try again. By adding the extract function, you can select out parts of the interval, years, months, days, etc. This does not round the interval value or convert the value into days, or hours. It simply pulls out the piece requested.

	elein=# SELECT age('12/31/1959'::timestamp);
	          age           
	------------------------
	 43 years 7 mons 8 days
	
	elein=# SELECT extract(year FROM age('12/31/1959'::timestamp));
	 date_part 
	-----------
	        43
	
	elein=# SELECT extract(days FROM age('12/31/1959'::timestamp));
	 date_part 
	-----------
	         8

A variation that is sometimes useful is to use date_trunc. Instead of selecting out the named pieces, it truncates the date to the level requested.

	=# SELECT date_trunc('days' , age('12/31/1959'::timestamp));
	       date_trunc       
	------------------------
	 43 years 7 mons 8 days
	
	=# SELECT date_trunc('months' ,age('12/31/1959'::timestamp));
	   date_trunc    
	-----------------
	 43 years 7 mons
	
	=# SELECT date_trunc('years' , age('12/31/1959'::timestamp));
	 date_trunc 
	------------
	 43 years

Contributors: Ben bench at silentmedia.com, nolan at celery.tssi.com, Dennis Gearon gearond at cvc.net, Network Administrator netadmin at vcsn.com, Greg Sabino Mullane greg at turnstep.com

Storing an encrypted password [GENERAL] How to encrypt data in Postgresql 24-Jul-2003

contrib/pgcrypto enables storing of encrypted data in fields. With the functions it provides you can encrypt and store the data as well as verify passwords.

	create table users (
	   name  text,
	   passwd   text
	);
	
	insert into users (name, passwd) values ('harold', crypt('1234', gen_salt('md5')));
	select * from users;
	
	=# select * from users;
	  name  |               passwd
	--------+------------------------------------
	 harold | $1$Ahuzwzzz$gdWC5OZLMk8Hhqa1V7Lbz/
	(1 row)
	
	=# select '$1$Ahuzwzzz$gdWC5OZLMk8Hhqa1V7Lbz/' = crypt('1234', passwd) 
		from users where name='harold';
	 ?column?
	----------
	 t
	(1 row)
	
	=# select name from users where passwd = crypt('1234', passwd);
	  name
	--------
	 harold
	(1 row)

Note that when using encryption, the convention is to store and compare the encrypted value. Being able to decrypt stored passwords is generally considered to be a "bad thing".

To install contrib/pgcrypto from source, in that directory, perform make, make install. To install the functions into a database, use

	psql mydb < pgcrypto.sql 

See the documentation in contrib/pgcrpto for more information on the full capability of pgcrypto.

Contributors: Terence Chang TChang at nqueue.com, Richard Welty rwelty at averillpark.net, Franco Bruno Borghesi franco at akyasociados.com.ar, Douglas Trainor trainor at uic.edu, Reuben D. Budiardja techlist at voyager.phys.utk.edu, Benjamin Jury benjamin.jury at mpuk.com

Use Triggers, not Rules for History Logging [GENERAL] INSERT RULE QUERY ORDER 5-Aug-2003

One technique for tracking updates of a table is to store the old values into a another table as a log of changes. All or most of the columns of the original table are duplicated with in the log table and a timestamp or other key is added.

This technique is best done with a trigger instead of an UPDATE RULE, particularly if the UPDATE RULE was to apply to a view. A possible update rule might be this:

	CREATE RULE update_org AS ON UPDATE TO orig_table_view DO INSTEAD
	(
		UPDATE orig_table set col=NEW.col...
		INSERT INTO log_table ...( old.col... )
	);

However, with TRIGGERS you do not need to wrestle with the visibility rules. You will still need the rule for updating the view. The trigger will be on the base table and insert the log record only.

To see both techniques in action check out Logging Updates.

Contributors: Justin Tocci jtocci at tlcusa.com, Tom Lane tgl at sss.pgh.pa.us, elein at varlena.com