PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

03-Feb-2003 Issue: 11

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

Functional indexes, cachable and immutable
[GENERAL] tsearch comments 22-Jan-2003

A proposed functional index using the contrib/tsearch function txt2txtidx() brought up a number of interesting issues with regards to functional indexes. Initially, the function txt2txtidx() was rejected as a functional index because the function could not be defined WITH (iscachable).

When a function is IMMUTABLE, it means that given the same set of parameters it always returns the same result and that it neither reads nor writes to other tables. A function may also be defined to be STABLE, expecting consistent results within the context of given SQL statement or the function can be defined as VOLATILE which is the default. A function is VOLATILE if it reads or writes from another table or if it relies on another VOLATILE function such as random() or current_timestamp.

Index functions must be defined as IMMUTABLE in order to work correctly. In the case of txt2txtidx() it was determined that the function became VOLATILE only when there was change of parser and or dictionaries in the tsearch module. Otherwise it matched the definition of IMMUTABLE. The parser and dictionary changes would only happen after a new gmake install of contrib/tsearch. If the indexes were dropped and recreated after each gmake install of contrib/tsearch then the txt2txtidx() function could be created as IMMUTABLE.

The syntax to create function as immutable is

	create function foo() as '....' language 'sql' IMMUTABLE;
Prior to 7.3 the syntax used WITH (iscachable) and WITH (iscachable) still works. Sometime during the 7.3 development, the option WITH (isImmutable) worked, but was changed to simply IMMUTABLE to align more closely with SQL specifications.

It was also observed that the searches using the functional index speeded up. This is because the optimizer can cache results of IMMUTABLE functions and reuse them. (Hence the original name, iscachable.)

For the fans of contrib/tsearch, a new version of tsearch and alpha will be available next week. It will include Snowball, ISpell, SQL-level for configure parsers and dictionaries, and SQL-level for configure map (which dictionary for each type of lexem).

Contributors: Björn Metzdorf bm at, Teodor Sigaev teodor at, Oleg Bartunov oleg at, Tom Lane tgl at
Contributions to the PostgreSQL Project (obsolete)
[GENERAL] donations? 30-Jan-2003

The PostgreSQL Global Development project can use your contributions. The companies PostgresSQL, Inc., aka, and host the machines, websites and mailing lists. By purchasing CDs, trinkets, shirts, mugs or just making a plain contribution you can help defray the costs of these invaluable resources.

To make a purchase or contribute directly use the following URLs. Marc Fournier mentioned that there had been a problem with the contributor link that is now corrected.
The pgsql site reads:
Profits from your purchase of promotional items and your donations are used to fund and maintain the operation of the PostgreSQL Global Development Project's website as well as ongoing work on increasing PostgreSQL's features and functions.
Contributors: Holger Klawitter lists at, Justin Clift justin at, Marc G. Fournier scrappy at
Index performance
[GENERAL] index on timestamp performance 29-Jan-2003

There is a table with three columns. There are two indexes on the table, the first is a two part index and the second is a single index.

	 motid  | integer                     | not null
	 objid  | integer                     | not null
	 ts     | timestamp without time zone | not null
	Indexes: dico_frs_motid_date btree (motid, ts)
	         dico_frs_objid btree (objid)
The performance on the index containing the timestamp column seemed to be much slower than the index using only the objid index. The timestamp indexed query and its EXPLAIN ANALYZE follow:
	select objid 
	from dico_frs 
	where motid=1247
	and date <= '2003-01-29 17:55:17' and date >= '2002-10-29 17:55:17'
	order by date desc limit 11;

   Limit  (cost=4752.14..4752.17 rows=11 width=12)
          (actual time=63.20..63.37 rows=11 loops=1)
      ->  Sort  (cost=4752.14..4755.11 rows=1187 width=12)
                (actual time=63.17..63.23 rows=12 loops=1)
         Sort Key: date 
         ->  Index Scan using dico_frs_motid_date on dico_frs 
                   (cost=0.00..4691.50 rows=1187 width=12) 
                   (actual time=0.08..41.88 rows=2924 loops=1)
         Index Cond: ((motid = 1247) AND
                      (ts <= '2003-01-29 17:55:17'::timestamp without time zone) AND 
                      (ts >= '2002-10-29 17:55:17'::timestamp without time zone))
   Total runtime: 63.93 msec
   (6 rows)

This EXPLAIN shows that the data is first being sorted by motid and then by ts, in the order of the index definition. It first grabs all of the indexed rows and sorts them rather doing the ORDER BY sort first. The LIMIT does not help in that case. It was suggested that the ORDER BY be changed to:

	...ORDER BY motid desc, ts desc 
This change what pronounced "amazing".

Contributors: Eric Cholet cholet at, Stephan Szabo sszabo at, Tom Lane tgl at
Grant all
[GENERAL] grant to all tables 20-Jan-2003

There is no statement in PostgreSQL which enables you to grant permissions on all tables at once. You have to do one table at a time. The syntax is:

	grant all on tablename to public;

However, PostgreSQL has many nice hooks for doing a little more. Consider this function:

	create or replace function granted(text)
	returns void as '
		qry="grant all on "+tbl+" to public;"
		ret=plpy.execute( qry );
	' language 'plpython';
This function when invoked on a set of table names will grant permission on the table name to public. As postgres, invoke this function in a query of relation (table) names and you have what you want. Be sure to select only tables and only the ones you want. I suggest limiting the query by owner. relkind = 'r' denotes a table.
	select granted(relname)
	from pg_catalog.pg_class c
	left join pg_catalog.pg_user u ON u.usesysid = c.relowner
	where c.relkind = 'r' and u.usename = 'owner name';
That query was derived from the query for psql's \d (describe table function). The query can be seen by invoking psql with the -E option and then issuing a \d. The \d query is:
	********* QUERY **********
	SELECT n.nspname as "Schema",
  	c.relname as "Name",
  	CASE c.relkind WHEN 'r' THEN 'table' 
		WHEN 'v' THEN 'view' 
		WHEN 'i' THEN 'index' 
		WHEN 'S' THEN 'sequence' 
		WHEN 's' THEN 'special' END as "Type",
  	u.usename as "Owner"
	FROM pg_catalog.pg_class c
     	LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     	LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
	WHERE c.relkind IN ('r','v','S','')
      	AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      	AND pg_catalog.pg_table_is_visible(c.oid)
	ORDER BY 1,2;
As usual, use proper discretion when granting permissions. Don't grant all to all unless you are capable and willing to handle the possibly detrimental consequences.

Contributors: CSN cool_screen_name90001 at, codeWarrior GPatnude at, Bruce Momjian pgman at, elein at
Case insensitive selection
[GENERAL] SELECT question 30-Jan-2003

Case insensitive searches are quite simple. Your database should be initialized to use the C locale and then you can use the following:

	select * from mytable where name ilike = 'ham';

You can also use the Perl-like regular expressions in PostgreSQL.

	select * from mytable where name ~* 'ham';

See the documentation on Functions and Operators for more information about different ways to use regular expressions and conversions.

Contributors: Brian Avis brian.avis at, scott.marlowe scott.marlowe at, Medi Montaseri medi.montaseri at
Table Spaces (Obsolete)
[GENERAL] Status of tablespaces 27-Jan-2003

This thread brought up several interesting issues with regards to table spaces. Table spaces are locations or disk space specifically allocated to a particular table. PostgreSQL does not (yet) support table spaces. Oracle (and other big brand dbs) support table spaces by pre-allocating the space on disk, whether it is used or not. That implementation is seen (by PostgreSQL developers :) as annoying.

In general, PostgreSQL uses the operating system as much as possible. This means that backends are processes and data spaces are files. If PostgreSQL were to support tablespaces, it would not be by pre-allocating space, but rather by designating a target directory. Those designated directories would probably be on different disks to allow for best allocation of the space. Within directories, the operating system tools would also be able to handle quotas in a more efficient manner than the server which would have to synchronize updates.

There was a patch submitted by Jim Buttafuoco to implement table spaces back in March 2000. It was never adopted because, as Tom Lane recalled, it did was not a general enough implementation of table spaces.

There currently is a way to locate the data directory for an entire database. Tom Lane, however, suggests that this option would be removed when we have an adequate tablespace substitute. Also, I was unable to get corresponding createdb shell command to work correctly with a location option. Although no error was raised on using --location, the database was created in $PGDATA in the usual way. The SQL command did work as expected. It uses a parameter which is the name of an exported environment variable. This tie to the environment variable is somewhat awkward. The database location options are not recommended.

	shell> createdb mydb --location 'mydb_directory'; 
	psql%# create database with location 'environment_variable';

Bruce Momjian wrote that the tablespace option would be moved from the "exotic" to do list to the administration to do list.

Contributors: Francisco Reyes lists at, Bruce Momjian pgman at, Christoph Dalitz christoph.dalitz at, Tom Lane tgl at, Mariusz Czu?ada manieq at, Jim Buttafuoco jim at, Simon Mitchell pgsql at, Sean Chittenden sean at, Curt Sampson cjs at

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

Search General Bits & Search WWW