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.
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:
You must have
downloaded and installed R in order to use PL/R.
PL/R can be
downloaded and reviewed
Documentation for PL/R is available at
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 daysOoops. Y2K hits again. Don't forget to use four digit years.
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
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.sqlIf you need encryption in all of your databases, install pgcrypto into template1 and subsequently created databases will have the functions predefined.
See the documentation in contrib/pgcrpto for more information on the full capability of pgcrypto.
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... ) );This rule will not work because when the original table is updated, the OLD values have changed and are visible. Therefore the insert into the log table logs the new values rather than the old ones. If you switch the order of the UPDATE and INSERT statements, this RULE should work for logging.
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.
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