Re: pg_trgm

#1

Marian Aldenhövel <marian (AT) mba-software (DOT) de> wrote:

Quote:

I have an installation of Postgres 8.2.3 on MacOS X.

To my shame I admit that I know next to nothing about Postgres apart from
that it exists. I have inherited this database as backend for a web
application and it has worked so flawlessly for me since that I have not
bothered to investigate in-depth.

It is not a shame not to know something.
But for your own good you should start learning, because otherwise
somebody will accuse you some day that it is your fault that the database
crashed/there is no backup/got hacked/...

For example, you should upgrade to 8.2.7.

Quote:

I have now been tasked with installing/enabling pg_trgm and tsearch2 on
the MacOS target machine. And am lost.

I have tsearch2.so in /usr/local/pgsql/lib and have run the tsearch.sql
which did not show any errors, so I seem to be fine on that.

But what about pg_trgm?

SELECT show_trgm('abc');

fails with

ERROR: function show_trgm("unknown") does not exist

OK. There is no file by name *pg_trgm* on the system.

Do I need to add something? Build something? Or is there SQL I can run as
for tsearch2?

You need to install pg_trgm.

There should be a file README.pg_trgm and a file pg_trgm.sql on your system.
The former contains documentation, and the latter should be run as
superuser to create the functions.

Similar to what you did with tsearch2.

Yours,
Laurenz Albe

#2

Marian Aldenhövel <marian (AT) mba-software (DOT) de> wrote:

Quote:

I have an installation of Postgres 8.2.3 on MacOS X.

To my shame I admit that I know next to nothing about Postgres apart from
that it exists. I have inherited this database as backend for a web
application and it has worked so flawlessly for me since that I have not
bothered to investigate in-depth.

It is not a shame not to know something.
But for your own good you should start learning, because otherwise
somebody will accuse you some day that it is your fault that the database
crashed/there is no backup/got hacked/...

For example, you should upgrade to 8.2.7.

Quote:

I have now been tasked with installing/enabling pg_trgm and tsearch2 on
the MacOS target machine. And am lost.

I have tsearch2.so in /usr/local/pgsql/lib and have run the tsearch.sql
which did not show any errors, so I seem to be fine on that.

But what about pg_trgm?

SELECT show_trgm('abc');

fails with

ERROR: function show_trgm("unknown") does not exist

OK. There is no file by name *pg_trgm* on the system.

Do I need to add something? Build something? Or is there SQL I can run as
for tsearch2?

You need to install pg_trgm.

There should be a file README.pg_trgm and a file pg_trgm.sql on your system.
The former contains documentation, and the latter should be run as
superuser to create the functions.

Similar to what you did with tsearch2.

Yours,
Laurenz Albe

#3

Marian Aldenhövel <marian (AT) mba-software (DOT) de> wrote:

Quote:

I have an installation of Postgres 8.2.3 on MacOS X.

To my shame I admit that I know next to nothing about Postgres apart from
that it exists. I have inherited this database as backend for a web
application and it has worked so flawlessly for me since that I have not
bothered to investigate in-depth.

It is not a shame not to know something.
But for your own good you should start learning, because otherwise
somebody will accuse you some day that it is your fault that the database
crashed/there is no backup/got hacked/...

For example, you should upgrade to 8.2.7.

Quote:

I have now been tasked with installing/enabling pg_trgm and tsearch2 on
the MacOS target machine. And am lost.

I have tsearch2.so in /usr/local/pgsql/lib and have run the tsearch.sql
which did not show any errors, so I seem to be fine on that.

But what about pg_trgm?

SELECT show_trgm('abc');

fails with

ERROR: function show_trgm("unknown") does not exist

OK. There is no file by name *pg_trgm* on the system.

Do I need to add something? Build something? Or is there SQL I can run as
for tsearch2?

You need to install pg_trgm.

There should be a file README.pg_trgm and a file pg_trgm.sql on your system.
The former contains documentation, and the latter should be run as
superuser to create the functions.

Similar to what you did with tsearch2.

Yours,
Laurenz Albe

#4

Marian Aldenhövel <marian (AT) mba-software (DOT) de> wrote:

Quote:

I have an installation of Postgres 8.2.3 on MacOS X.

To my shame I admit that I know next to nothing about Postgres apart from
that it exists. I have inherited this database as backend for a web
application and it has worked so flawlessly for me since that I have not
bothered to investigate in-depth.

It is not a shame not to know something.
But for your own good you should start learning, because otherwise
somebody will accuse you some day that it is your fault that the database
crashed/there is no backup/got hacked/...

For example, you should upgrade to 8.2.7.

Quote:

I have now been tasked with installing/enabling pg_trgm and tsearch2 on
the MacOS target machine. And am lost.

I have tsearch2.so in /usr/local/pgsql/lib and have run the tsearch.sql
which did not show any errors, so I seem to be fine on that.

But what about pg_trgm?

SELECT show_trgm('abc');

fails with

ERROR: function show_trgm("unknown") does not exist

OK. There is no file by name *pg_trgm* on the system.

Do I need to add something? Build something? Or is there SQL I can run as
for tsearch2?

You need to install pg_trgm.

There should be a file README.pg_trgm and a file pg_trgm.sql on your system.
The former contains documentation, and the latter should be run as
superuser to create the functions.

Similar to what you did with tsearch2.

Yours,
Laurenz Albe

#5

Marian Aldenhövel <marian (AT) mba-software (DOT) de> wrote:

Quote:

I have an installation of Postgres 8.2.3 on MacOS X.

To my shame I admit that I know next to nothing about Postgres apart from
that it exists. I have inherited this database as backend for a web
application and it has worked so flawlessly for me since that I have not
bothered to investigate in-depth.

It is not a shame not to know something.
But for your own good you should start learning, because otherwise
somebody will accuse you some day that it is your fault that the database
crashed/there is no backup/got hacked/...

For example, you should upgrade to 8.2.7.

Quote:

I have now been tasked with installing/enabling pg_trgm and tsearch2 on
the MacOS target machine. And am lost.

I have tsearch2.so in /usr/local/pgsql/lib and have run the tsearch.sql
which did not show any errors, so I seem to be fine on that.

But what about pg_trgm?

SELECT show_trgm('abc');

fails with

ERROR: function show_trgm("unknown") does not exist

OK. There is no file by name *pg_trgm* on the system.

Do I need to add something? Build something? Or is there SQL I can run as
for tsearch2?

You need to install pg_trgm.

There should be a file README.pg_trgm and a file pg_trgm.sql on your system.
The former contains documentation, and the latter should be run as
superuser to create the functions.

Similar to what you did with tsearch2.

Yours,
Laurenz Albe

#6

Marian Aldenhövel <marian (AT) mba-software (DOT) de> wrote:

Quote:

I have an installation of Postgres 8.2.3 on MacOS X.

To my shame I admit that I know next to nothing about Postgres apart from
that it exists. I have inherited this database as backend for a web
application and it has worked so flawlessly for me since that I have not
bothered to investigate in-depth.

It is not a shame not to know something.
But for your own good you should start learning, because otherwise
somebody will accuse you some day that it is your fault that the database
crashed/there is no backup/got hacked/...

For example, you should upgrade to 8.2.7.

Quote:

I have now been tasked with installing/enabling pg_trgm and tsearch2 on
the MacOS target machine. And am lost.

I have tsearch2.so in /usr/local/pgsql/lib and have run the tsearch.sql
which did not show any errors, so I seem to be fine on that.

But what about pg_trgm?

SELECT show_trgm('abc');

fails with

ERROR: function show_trgm("unknown") does not exist

OK. There is no file by name *pg_trgm* on the system.

Do I need to add something? Build something? Or is there SQL I can run as
for tsearch2?

You need to install pg_trgm.

There should be a file README.pg_trgm and a file pg_trgm.sql on your system.
The former contains documentation, and the latter should be run as
superuser to create the functions.

Similar to what you did with tsearch2.

Yours,
Laurenz Albe

#7

Marian Aldenhövel <marian (AT) mba-software (DOT) de> wrote:

Quote:

I have an installation of Postgres 8.2.3 on MacOS X.

To my shame I admit that I know next to nothing about Postgres apart from
that it exists. I have inherited this database as backend for a web
application and it has worked so flawlessly for me since that I have not
bothered to investigate in-depth.

It is not a shame not to know something.
But for your own good you should start learning, because otherwise
somebody will accuse you some day that it is your fault that the database
crashed/there is no backup/got hacked/...

For example, you should upgrade to 8.2.7.

Quote:

I have now been tasked with installing/enabling pg_trgm and tsearch2 on
the MacOS target machine. And am lost.

I have tsearch2.so in /usr/local/pgsql/lib and have run the tsearch.sql
which did not show any errors, so I seem to be fine on that.

But what about pg_trgm?

SELECT show_trgm('abc');

fails with

ERROR: function show_trgm("unknown") does not exist

OK. There is no file by name *pg_trgm* on the system.

Do I need to add something? Build something? Or is there SQL I can run as
for tsearch2?

You need to install pg_trgm.

There should be a file README.pg_trgm and a file pg_trgm.sql on your system.
The former contains documentation, and the latter should be run as
superuser to create the functions.

Similar to what you did with tsearch2.

Yours,
Laurenz Albe

#8

Marian Aldenhövel <marian (AT) mba-software (DOT) de> wrote:

Quote:

I have an installation of Postgres 8.2.3 on MacOS X.

To my shame I admit that I know next to nothing about Postgres apart from
that it exists. I have inherited this database as backend for a web
application and it has worked so flawlessly for me since that I have not
bothered to investigate in-depth.

It is not a shame not to know something.
But for your own good you should start learning, because otherwise
somebody will accuse you some day that it is your fault that the database
crashed/there is no backup/got hacked/...

For example, you should upgrade to 8.2.7.

Quote:

I have now been tasked with installing/enabling pg_trgm and tsearch2 on
the MacOS target machine. And am lost.

I have tsearch2.so in /usr/local/pgsql/lib and have run the tsearch.sql
which did not show any errors, so I seem to be fine on that.

But what about pg_trgm?

SELECT show_trgm('abc');

fails with

ERROR: function show_trgm("unknown") does not exist

OK. There is no file by name *pg_trgm* on the system.

Do I need to add something? Build something? Or is there SQL I can run as
for tsearch2?

You need to install pg_trgm.

There should be a file README.pg_trgm and a file pg_trgm.sql on your system.
The former contains documentation, and the latter should be run as
superuser to create the functions.

Similar to what you did with tsearch2.

Yours,
Laurenz Albe

#9

Marian Aldenhövel <marian (AT) mba-software (DOT) de> wrote:

Quote:

There should be a file README.pg_trgm and a file pg_trgm.sql on your
system.

I have confirmed that there are no *pg_trgm* files on the system.

I looked at the package you downloaded, and it really does only contain
one contrib module, namely tsearch2.

Quote:

Before posting I had downloaded the source to the module from

http://www.sai.msu.su/~megera/postgres/gist/pg_trgm/

An checked the contents. It includes a README, but that does not tell
me anything about installation, just how to use the functions.

There is a pg_trgm.sql.in that apparently needs to be processed through
a build process to arrive at something useable:

..
create function set_limit(float4)
returns float4
AS 'MODULE_PATHNAME'
LANGUAGE 'C' with (isstrict,iscachable);

pg_trgm.sql from the package is a set of tests demonstrating the functions.

So does the fact that I don't have a file called pg_trgm.so or such mean
that my package does not include/support the module? That I would have to
build it from source?

Yes, that's the way it looks.

The site you quoted in your original post contains "Build Instructions"
which may make the task easier.

I have no idea why the packagers decided to omit most of the contrib
modules, maybe you can ask them...

There are also good build instructions in the original PostgreSQL
documentation. It shouldn't be too hard, but you'll need a development
machine where you can install all the required software.

Be sure to use the same PostgreSQL version for everything!

Maybe you can use this to convince your bosses that
a) they should invest some money into letting you learn PostgreSQL
b) you might as well update to 8.2.7 while you're at it.

Yours,
Laurenz Albe

#10

Marian Aldenhövel <marian (AT) mba-software (DOT) de> wrote:

Quote:

There should be a file README.pg_trgm and a file pg_trgm.sql on your
system.

I have confirmed that there are no *pg_trgm* files on the system.

I looked at the package you downloaded, and it really does only contain
one contrib module, namely tsearch2.

Quote:

Before posting I had downloaded the source to the module from

http://www.sai.msu.su/~megera/postgres/gist/pg_trgm/

An checked the contents. It includes a README, but that does not tell
me anything about installation, just how to use the functions.

There is a pg_trgm.sql.in that apparently needs to be processed through
a build process to arrive at something useable:

..
create function set_limit(float4)
returns float4
AS 'MODULE_PATHNAME'
LANGUAGE 'C' with (isstrict,iscachable);

pg_trgm.sql from the package is a set of tests demonstrating the functions.

So does the fact that I don't have a file called pg_trgm.so or such mean
that my package does not include/support the module? That I would have to
build it from source?

Yes, that's the way it looks.

The site you quoted in your original post contains "Build Instructions"
which may make the task easier.

I have no idea why the packagers decided to omit most of the contrib
modules, maybe you can ask them...

There are also good build instructions in the original PostgreSQL
documentation. It shouldn't be too hard, but you'll need a development
machine where you can install all the required software.

Be sure to use the same PostgreSQL version for everything!

Maybe you can use this to convince your bosses that
a) they should invest some money into letting you learn PostgreSQL
b) you might as well update to 8.2.7 while you're at it.

Yours,
Laurenz Albe