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).
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.
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)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 descThis change what pronounced "amazing".
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 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:
\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; **************************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.
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.
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.
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