Index

pgestraier - PostgreSQL full-text search using Hyper Estraier

This package is essentially composed of two different parts:

search function

PostgreSQL function to search Hyper Estraier full-text index, using full-text queries and attribute filtering to return user-specified table of results.

This function can mimic SQL LIMIT, OFFSET and ORDER BY functionality much faster than using those SQL constructs on search results.

trigger function

PostgreSQL trigger function to keep Hyper Estraier in sync with PostgreSQL. It triggers after insert, update or delete and update full-text index accordingly.

Both functions are written in C, while test framework and supporting utilities are written in perl.

You can use just one of those functions. If you want just to search existing Hyper Estraier index or generate it off-line (after nightly batch jobs, for example), just use search function.

On the other hand, if you want just to keep your Hyper Estraier index in sync with PostgreSQL data, you can use just trigger function to achieve that.

Why is it written?

Aside from providing single query language (SQL) to RDBMS and full text index (using any language that has PostgreSQL client libraries), real power is hidden in ability to join results from full text index and structured data in RDBMS.

For simple real-life example which address problem WHERE name LIKE '%foo%' OR surname LIKE '%foo%' is slow see Tutorial and pgest-index documentation.

How to install

Installation should be simple. However, you will have to have following software already installed before you try this functions:

If you want to use helper script to create consistency triggers to keep Hyper Estraier in sync with PostgreSQL database, you will also need:

To run tests you will also need:

If you have all that, you should be able to type

  make

and see sample results. You will be asked your password once (via sudo) to install pgest.so shared library in system-wide location so that PostgreSQL could access it.

Create sample index using Hyper Estraier perl bindings

Perl bindings for Hyper Estraier are available at CPAN:

http://search.cpan.org/~dpavlin/Search-Estraier/

After installing Search::Estraier you can create index using following commands:

  cd data
  make index
  cd ..

To run tests (which require that you have estcmd in your $PATH) issue

  make test

See also included file test.sql for more examples of usage.

Usage of search function pgest from SQL

pgest PostgreSQL function tries to mimic usage of normal database tables (with support for attribute filtering, limit and offset) in following way:

	SELECT
		-- columns to return (defined later)
		id,title,size
	FROM pgest(
		-- node URI, login, password and depth of search
		'http://localhost:1978/node/trivia', 'admin', 'admin', 42,
		-- query
		'blade runner',
		-- additional attributes, use NULL or '' to disable
		-- multiple attributes conditions can be separated by {{!}}
		'@title ISTRINC blade',
		-- order results by
		'@title STRA',
		-- limit, use NULL or 0 to disable
		null,
		-- offset, use NULL or 0 to disable
		null,
		-- attributes to return as columns
		ARRAY['@id','@title','@size']
	) AS (
		-- specify names and types of returned attributes
		id text, title text, size text
	);

You should note that Hyper Estraier uses UTF-8 encoding, while your PostgreSQL installation might use different encoding. To fix that, use convert function in PostgreSQL to convert encodings.

Using index via estmaster server process

This is default and recommended way to use pgest functionality. In this case, pgest will use node API and access index through estmaster process which should be running on (local or remote) machine.

This will remove database opening overhead, at a cost of (small) additional network traffic. However, you can have Hyper Estraier estmaster process running on different machine or update index while doing searches, so benefits of this approach are obvious.

Accessing database directly

Please note that direct access to database is depreciated. As such, it's not stated in example, and it's kept just for backward compatibility, but it will probably be removed in future versions of pgest.

If you want to access database directly (without running estmaster process), you have to replace node URI, login, password and depth with full path to database file.

Have in mind that postgres user under which PostgreSQL is running must have read permission on Hyper Estraier database files.

This will work a bit faster on really small indexes. However, when your index grows bigger, you might consider using node API to remove overhead of database opening on each query.

Usage of trigger function pgest_trigger from SQL

Let's first say that I really suggest that you use dbi-index.pl helper script to create triggers because it already supports following steps automatically:

begin transaction

Transaction is needed to catch updates which might happen while creation of full-text index is in progress (and on huge collections this can take a while, just like normal index creation in PostgreSQL).

insert all existing data in full-text index

This will be done directly from PostgreSQL database to Hyper Estraier index. This is somewhat faster than waiting for trigger to fire for each existing row.

create insert, update and delete triggers

Which will keep data in sync later

commit transaction

If you still want to do that manually, you will need to know format of pgest_trigger function:

	CREATE TRIGGER pgest_trigger_insert AFTER INSERT
		ON table FOR EACH ROW
		EXECUTE PROCEDURE pgest_trigger(
			-- node URI, login and password
			'http://localhost:1978/node/trivia', 'admin', 'admin',
			-- name of primary key column
			'id',
			-- names of all other columns to index (one or more)
			'column', 'another_one', 'and_another'
		)

You have to create triggers for UPDATE and DELETE in similar way.

Who wrote this?

Hyper Estraier is written by Mikio Hirabayashi.

PostgreSQL is written by hackers calling themselves PostgreSQL Global Development Group.

This small C functions are written by Dobrica Pavlinusic, dpavlin@rot13.org.

See also