There is a new link on PostgreSQL General Bits for Spanish speakers. In contains articles from General Bits translated by Alvaro Herreta. Welcome to Spanish speaking readers! I'm sure you will enjoy Alvaro's work.

Hay una nueva parte de PostgreSQL General Bits para los que leen Castellano (Español). Este tiene artículos traduccidos por Álvaro Herrera. ¡Bienvenidos! Que gozarían el trabajo de Álvaro.

Se puede leer el primero edición en Castellano aquí. Al izquierda hay Ediciones en Castellano donde se puede ver quales ediciones habia traducidos.

(Errores en español aquí son mios. Claro que Álvaro escribe mejor que yo en español. --elein)

Contributors: elein at varlena.com

New French language mailing list [pgsql-advocacy] French ML and web site 10-Sept-2003

The new French mailing list (pgsql-fr-generale) announced previously has been set up and there is now also have a web site http://pgsql-fr.tuxfamily.org/ mostly dedicated to the translation of PostgreSQL to French.

The mailing list will soon be added to PostgreSQL Mailing Lists and archived.

Contributors: Francois Suter dba at paragraf.ch

Dynamic Queries in plpgsql Dynamic Queries 13-Sept-2003

It is possible to execute dynamic queries in plpgsql by first constructing the query string and then executing it in the function. Dynamic queries are particularly nice when you want to pass in a table name and or a column name. But there are a few details which can frustrate if you are not aware of them.

In plpgsql, you can run a dynamic SELECT statement and be able to handle the selected data only when you select into a FOR LOOP. This is very handy for returning SETS and SETS of RECORDS. But sometimes it requires putting a singleton select into the loop structure. It is not possible to store the selected value into a variable by just assigning it or selecting into a variable. You can, however, construct INSERT, UPDATE and DELETE statements as well a DDL (Data Definition Language: CREATE, ALTER, etc.)

The other thing to watch out for in constructing dynamic queries in plpgsql is the ever present single quote. Remember you are doubling the single quotes inside of the function definition and if you use single quotes in your constructed query, you must quadruple them. Use the concatenate operators for text '||' to link text and variable values into the query string.

This example function takes a table name and a column name. It trims and updates the specified column for every row in the table.

	create or replace function trimtablecol(text, text )
	returns void
	as '
	DECLARE
	qry text;
	tab alias for $1;
	col alias for $2;
	BEGIN
	   qry := ''update '' || tab || '' set '' || col || ''=trim('' || col || '');'';
	   EXECUTE qry;
	   RETURN;
	END;

	' language 'plpgsql';

Using a FOR LOOP in this example, the query string is built inline. Because the argument is a text argument it needs the extra-extra quotes.

	create or replace function fortythree(text)
	returns float
	as '
	DECLARE
	   v_tmp RECORD;
	   ret_avg_price float;
	   myid alias for $1;
	BEGIN
	   FOR v_tmp IN EXECUTE ''SELECT avg(price) AS avg_price
	                     FROM fortythree
	                     WHERE id = '''''' || myid || ''''''
	   LOOP
	
	   ret_avg_price := v_tmp.avg_price;
	
	   END LOOP;
	   RETURN ret_avg_price;
	END;
	' language 'plpgsql';

Note that you can also execute dynamic queries in plperl, tcl and plpythonu and in some cases the construction of the string is simpler.

Contributors: elein at varlena.com

Meandering Thread [GENERAL] State of Beta 2 9-Sept-2003

This meandering thread covered several interesting topics:

• production use of 7.4Beta,
• existing problems with int8 columns being compared to constant integers,
• the requirement and implementation of upgrading the system catalog
• and finally, whether or not raising the default page size would affect the limited size of a row.

To upgrade or not to upgrade is the question. People coming up on their own release schedules want to know if 7.4 is safe enough for production because it will be simpler to go forward with 7.4 than to go with 7.3 and then upgrade again soon thereafter. Of the beta testers only one weighed in on this issue with an actual installation and that was Marc Fournier noting that the archives for PostgreSQL.org are now running on 7.4Beta2.

Transitionally, the point was raise that in order to resolve the problem with int8 columns being compared with constant integers, a dump and restore may be required before 7.4 goes production. This led into a foray into how much people dislike the dump and restore requirements.

Dump and restore is required, in general, when system catalog changes are made. Several people raised the issue that, especially with very large databases, a dump and restore is prohibitive and that the requirement often causes people to delay upgrades that otherwise would be very helpful. Finding a place and time to dump and restore a couple of terabytes is obviously a problem.

Sometime it is possible to do database upgrades via upgrade scripts on the database, preventing the need for dump and restore. At this point Tom was the voice of reality when he pointed out that every change would have to be examined for upgrade requirements and portions of upgrade scripts must be written. This worked at Informix (when I was there), but is difficult to enforce in our environment. Also, the upgrade scripts have to be perfect and our paid developers are few and far between as are the number of people who can do core development on PostgreSQL. In reality, this is a good idea and if we had a dedicated person to pursue the issue consistently it would be great.

This discussion of update scripts continued past publication time for General Bits and has been discussed previously on pgsql-hackers. Look for ongoing discussions and possible changes in the future.

Another change that would lead to the requirement of a dump and restore is the default page size in PostgreSQL. One reason for increasing the page size is thought to be a way to handle very wide rows better. The other more important reason is that it increases speed on some machines.

Although the increase in page size change may be available after the 7.4 release in patch form it comes with several issues. First, more tests on more platforms would be required before a change like that is certain. Next, data dumped from Machine X cannot necessarily be restored on Machine Y if it has a different page size. This is will be a key warning for people considering the patched change. The final issue that was raise was the nebulous issues of row size.

The maximum number of columns that can be in a row is about 1600. But that number is a red herring, a false lead--it is not in fact the actual limit. Depending on the types of data in the row, it is far more likely that you will hit the limit with fewer columns. The real limitation for row size is whether the tuple will fit into the block. Comments in code were thrown about at this point :-) However, the maximum size of a row's representation should be a page size. The size is made of of tuple headers plus columns of various types, TOASTED or not.

This is really only calculatable on a table by table basis and within that a column by column basis. For example, it was speculated that you could have 1600 columns of int2, if you found a use for it. And Tom Lane said, "If all your columns are toastable types, and you toast every one of them, then the toast pointers are 20 bytes each, so with 8K block size the maximum usable number of columns is somewhere around 400."

Meandering threads, though not encouraged often have a variety of information in them about what people are working on. Now we know that the postgresql.org mail archives are run on 7.4, that there is a lingering problem with int8 and constant integer comparisons, that there is a push toward upgrades rather than dump and restore and we are reminded that the maximum row size is linked to the block size and is not fixed.

Contributors: Andrew Rawnsley ronz at ravensfield.com, Marc G. Fournier scrappy at postgresql.org, Vivek Khera khera at kcilink.com, scott.marlowe scott.marlowe at ihs.com, Tom Lane tgl at sss.pgh.pa.us, Manfred Koizar mkoi-pg at aon.at, Ron Johnson ron.l.johnson at cox.net, Nigel J. Andrews nandrews at investsystems.co.uk, Dennis Gearon gearond at fireserve.net, Kaare Rasmussen kar at kakidata.dk, Joshua D. Drake jd at commandprompt.com, Doug McNaught doug at mcnaught.org, Alvaro Herrera alvherre at dcc.uchile.cl, Lamar Owen lowen at pari.edu, Sean Chittenden sean at chittenden.org, Bruce Momjian pgman at candle.pha.pa.us

Time differences and Type Comparisons [GENERAL] I need a SQL... 11-Sept-2003

Given only times and not date and time, it is possible to calculate time differences accurately. This nice little function from Mattias Kregert does exactly that. But can you spot the error?

	SELECT starttime, stoptime, 
	   (CASE WHEN stoptime-starttime <= 0
	    THEN stoptime-starttime 
	    ELSE stoptime-starttime+'24 hours' END) as timediff 
	FROM mytable;

When this function is run on two database with different locales, then the WHEN comparison is different. Assuming that stoptime and starttime are time types, then you are comparing the result, an interval, with 0. It just so happens that with the C locale, this works correctly, however, for another locale, the condition fails.

To properly compare the types, the condition should read:

	stoptime - starttime <= '0 minutes'

Contributors: Bjørn T Johansen btj at havleik.no, Andrew L. Gould algould at datawok.com, Mattias Kregert mattias at kregert.se, Nigel J. Andrews nandrews at investsystems.co.uk, Tom Lane tgl at sss.pgh.pa.us, Adam Kavan akavan at cox.net

Win32 Status Information Bruce's Win32 project status 13-Sept-03

To find out the latest and greatest information on the status of the Native Win32 port of PostgreSQL see Bruce Momjian's writeup at http://momjian.postgresql.org/main/writings/pgsql/win32.html

If you can help in any of the remaining To Do issues join the development mailing list at: pgsql-hackers-win32-request@postgresql.org

Contributors: elein at varlena.com