Next week there will be no new issue of General Bits. I will be at the O'Reilly Open Source Conference in Portland, Oregon.

I hope to meet some of you there!

Editor: elein at varlena.com

Portuguese Translations Welcome Pedro B. 17-Jul-2004

General Bits has issues recent issues translated into both Castellano and Portuguese.

Pedro B. has graciously joined our team of esteemed translators to begin translating General Bits into Portuguese, as distinct our previous translations into Brasilian-Portuguese.

Alvaro Herrera continues to be our Castellano translator even as he is coding features for the upcoming 7.5 release and helping translate the documentation.

We are still looking for another Brasilian-Portuguese translator as Juliano Da Silva has been unable to continue due to time constraints.

The translators' work requires a good deal of time and commitment as well as good knowledge of languages and, of course, PostgreSQL. This unpaid work is our way of contributing back into the PostgreSQL community.

Editor: elein at varlena.com

Arithmetic on Aggregate Expressions [GENERAL] Column as result of subtraction of two other columns? 16-Jul-2004

The following selection will not work because the column named aliases are not visible throughout the statement.

	select 1 as a, 2 as b, (b-a) as c;

The simplicity of this query is suspicious. The problem is that 1 and 2 are just sitting in for complex expressions or aggregates. The following statement using aggregates does not work either.

	select sum(a) as x, sum(b) as y, (x-y) from ...;

The two techniques to resolve this problem are to either use a subselect or to repeat the expressions.

	select x, y, x-y from (select sum(a) as x, sum(b) as y from ...);
or
	select sum(a) as x, sum(b) as y, sum(a)-sum(b) as z from ...;

If your expressions are aggregates, the system will recognize that they are repeated in the statement and not be rerun. In the two examples above, sum(a) and sum(b) are each calculated once. If you expressions are not just aggregates, the first technique above is best since the more complex expression may not be recognized as already having been run in the context of the statement.

Contributors: Mark Cave-Ayland m.cave-ayland at webbased.co.uk, Bruno Wolff III bruno at wolff.to, John Sidney-Woollett johnsw at wardbrook.com, Paul Thomas paul at tmsl.demon.co.uk, Jean-Luc Lachance jllachan at sympatico.ca, Tom Lane tgl at sss.pgh.pa.us

Pass by Value in PlPgSQL [GENERAL] Using arguments with functions 10-July-2004

This is just a little reminder that you cannot modify parameters passed into plpgsql functions. You will get an ERROR indicating that the argument is a CONSTANT.

CREATE OR REPLACE FUNCTION xget_next (varchar) RETURNS varchar AS'
DECLARE
  var1 alias for $1;
BEGIN
  var1 = chr( (ascii(var1) + 1) );
  return var1;
END;
'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

ohc=> select get_next('a');
ERROR:  "$1" is declared CONSTANT
CONTEXT:  compile of PL/pgSQL function "get_next" near line 5

CREATE OR REPLACE FUNCTION xget_next (varchar) RETURNS varchar AS'
DECLARE
  var1 alias for $1;
BEGIN
  return chr( (ascii(var1) + 1) );
END;
'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

Contributors: Robert Fitzpatrick robert at webtent.com, Oliver Elphick olly at lfix.co.uk, Tom Lane tgl at sss.pgh.pa.us

Simple Views [GENERAL] optimization with limit and order by in a view 13-July-2004

It is standard practice to define views without ORDER BY clauses and without explicit qualifications other than what is necessary for JOINs. This practice enables the users of the view to qualify and order the resulting data independently of the data set.

In one particular instance, a view was defined for a java bean which for some reason needed an order by clause within the view definition.

Subsequent usage of the view with a LIMIT on it showed that the query was very, very slow. The view without the order by in its definition and then was called adding the ORDER BY and LIMIT clauses. Without the ORDER BY in the view definition the query went very fast. The queries looked like this:

CREATE VIEW nt_ordered AS
	SELECT * FROM bigtable bt JOIN littletable lt USING (id)
	ORDER BY id;
CREATE VIEW nt_notordered AS
	SELECT * FROM bigtable bt JOIN littletable lt USING (id);

select * from nt_ordered limit 20; --- slow
select * from nt_notordered order by id limit 20; -- fast

On first glance, it would seem that the queries are exactly equivalent. But this is not actually the case. Reviewing the EXPLAIN output showed that the plan chosen by the optimizer first sorted the data set and then applied the LIMIT when the ORDER BY was part of the view definition. When the ORDER BY was not part of the view definition, it chose a different plan to optimize for the LIMIT. That plan did not try to sort the entire data set first.

Contributors: elein at varlena.com, tgl@sss.pgh.pa.us

Reindex to Recover Index Space [GENERAL] DELETE TABLE xx do not erase index file after a vacuum 01-July-2004

Deleting rows quickly from a large table can usually be done using TRUNCATE. However, if there are FOREIGN KEYS on the table, you will have to DELETE the rows.

Using DELETE followed by VACUUM FULL will not necessarily recover the space previously used by the indexes on the table. In order to ensure that the index space is recovered, it is recommended that you REINDEX the table.

Contributors: Hervé Piedvache herve at elma.fr, Tom Lane tgl at sss.pgh.pa.us