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;

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 turtle-entertainment.de, Teodor Sigaev teodor at stack.net, Oleg Bartunov oleg at sai.msu.su, Tom Lane tgl at sss.pgh.pa.us

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 pgsql.com, and hub.com host the www.postgresql.org 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.

	store.pgsql.com
	www.pgsql.com/contributors

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.

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)

	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 

Contributors: Eric Cholet cholet at logilune.com, Stephan Szabo sszabo at megazone23.bigpanda.com, Tom Lane tgl at sss.pgh.pa.us

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 '
		tbl=args[0]
		qry="grant all on "+tbl+" to public;"
		ret=plpy.execute( qry );
	' language 'plpython';

	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';

 	\d
	********* 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;
	**************************

Contributors: CSN cool_screen_name90001 at yahoo.com, codeWarrior GPatnude at adelphia.net, Bruce Momjian pgman at candle.pha.pa.us, elein at varlena.com

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 searhc.org, scott.marlowe scott.marlowe at ihs.com, Medi Montaseri medi.montaseri at intransa.com

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'; 
	or
	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 natserv.com, Bruce Momjian pgman at candle.pha.pa.us, Christoph Dalitz christoph.dalitz at hs-niederrhein.de, Tom Lane tgl at sss.pgh.pa.us, Mariusz Czu?ada manieq at idea.net.pl, Jim Buttafuoco jim at contactbda.com, Simon Mitchell pgsql at jseb.com, Sean Chittenden sean at chittenden.org, Curt Sampson cjs at cynic.net