Обсуждение: levenshtein_less_equal does not work
Hello to all, I always use PostgreSQL from enterpriseDB. I have both 9.2 and 9.1 latest verions installed on two different Ubuntu 64bit machines. For some reason levenshtein_less_equal does not work, in contrast to levenshtein which works as expected. I even downloaded the src code for PostgreSQL code for fuzzystrmatch and compiled myself, added the so file to the correct place and still it does not work. The process was correct, because I have increased the levenshtein limit from 255 to 2048. Can you tell me how to make levenshtein_less_equal. Are those mysterious IFDEF levenshtein_less_equal that are messing compilation? Thanks, Alexandros -- View this message in context: http://postgresql.1045698.n5.nabble.com/levenshtein-less-equal-does-not-work-tp5788710.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 01/24/2014 12:50 PM, alexandros_e wrote: > For some reason levenshtein_less_equal does not work, Define "does not work", i.e. cut and paste exact SQL and output so we can see and reproduce your issue. Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.14 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJS4tQrAAoJEDfy90M199hl3lkP/2CCMGk+KB7mCvD2Bdc1sOee EuXmd3gu/ZJcdhn79UKcER47VU/cCCdLe0rA5MDt4EYCCw2ZI5thsMtBjVN9duYG VpcpvsJcwo9Pa7+f+A8iBmFD1omWVh0nW+TYbH56ytlS4ZRwTZ2/Unth05XqvTz8 zycv/D6v1SmvcLEcfWrsvfYFtbcZ6PiHaiKeabEls6azSOxgqgFIWiRWdN2SbWfe DVKJQtxgJVACpCMYqr5yvWM/lW6eJMoWmxddwPoj3QHwxbtZ18KnlGvx2ahI7e55 2ZzfboNylQHAK/6+6n4OueWDbuT1XBif4KvxGNa9qO6I+UWzTnpm03lJ/xDAEC0s hJtJuSiYFmbwOyMrp1PPGa0Wkjq+aNqd/IHymjXTwIkj/Co6NhgMYWmWKJZ/Hsnl 7xe34ESPHCHyKaB/6Mzu+2soL+/OThYcpLHdA+evxqeZzXzX1MCb/DniWRK4SHSy q4NBwFmxKmcc5qMVW9jYrmpbSqQvtggcoqbOibQgFI/EsXh7OGEPmNzcTHgVZ1q4 yb/TBhW3LJDBUcfkxrmaFM7qXQdONmNi8MmOkP/uEamcIe10rI81ORig+VCef2az MI2qae4fDeA1qxgDmsg2qro7pk8UGvUhW6I3+c0l6J3Tdg/4jJ0dcb6zrgGezwmt cjbhCXuHj5gQIiSu9bP6 =rPy6 -----END PGP SIGNATURE-----
Query:
SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
ERROR: function levenshtein_less_equal(unknown, unknown, integer) does not
exist
LINE 1: SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
Even this:
SELECT levenshtein_less_equal('extensive'::TEXT,
'exhaustive'::TEXT,2::INTEGER);
function levenshtein_less_equal(text, text, integer) does not exist
But this works perfectly:
SELECT levenshtein('extensive', 'exhaustive');
4
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/levenshtein-less-equal-does-not-work-tp5788710p5788713.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 01/24/2014 01:07 PM, alexandros_e wrote:
> SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
Did you CREATE the extension?
8<-----------------------------
psql (9.3.2)
Type "help" for help.
iftest=# SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
ERROR: function levenshtein_less_equal(unknown, unknown, integer)
does not exist
LINE 1: SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
iftest=# create extension fuzzystrmatch;
CREATE EXTENSION
iftest=# SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
levenshtein_less_equal
- ------------------------
3
(1 row)
8<-----------------------------
psql (9.1.11)
Type "help" for help.
test=# create extension fuzzystrmatch;
CREATE EXTENSION
test=# SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
levenshtein_less_equal
- ------------------------
3
(1 row)
8<-----------------------------
We need more specific info about how you obtained and installed the
extension.
Joe
- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBAgAGBQJS4thcAAoJEDfy90M199hlXisP/1wnp9j4311QENBWAM23xosl
JO68J4dt6LaexrG2cXYKdVZ1steTg4LG2yEPnW1ED132pqa/yt/d+xmA7TAUNHnS
es3PAw45XTFbVp5/tUhBhKnx3sEkLwE5ONDH1AjHDq/gUJMPf83xYhxYuP8rcXuY
0/cGof4pINMkfA94tKnr34x+N0xoY3jfTXPzE6x8Zci+78Kpx/SiCRlylAURexza
sKHmcDUqzvnaH+LzTsS1F06tybjJh/pHVNlA/TMhyV5AEfLmVDR7LpA4yVROX7Nh
qw3dBciCy7Q0GVqBNVK8UZzynlGhd3m7ldUN0tDZxovP1KEo5L+5D/oXU2Piejtb
Y7bJqS9nqx8xLNkQZ+Zqsj7nXhzLF9SdCumF/6q0gCCkP97+13RESMGIr/4Un4KJ
Wb4L28iilRZifbvJ9LV2grLUxoZCZeJEhQqfcjiwrBNGJMRPFZ0sveEIIeHa2H8m
/3UfkSyklMGkYupemLFlXTz4qD8m6giizUpbOVbEjBicIvpMwTP6ebuZjzpwI9ui
DbYRcTvWeLdVUKTq8ZCUmKUuaiqgRMJWGMYA7UVKHKf3aJ+v7+F9ddXD8YLTVO6Z
cVhigwR22rGmP+si4tWMu9JOVYjhuUsrlUeGHfZALmTOUbaKRQScnR+jwXSf+a9w
kqNSFvwOHVxD6fIgmjgK
=+GSh
-----END PGP SIGNATURE-----
On 01/24/2014 01:07 PM, alexandros_e wrote:
> Query:
>
> SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
>
> ERROR: function levenshtein_less_equal(unknown, unknown, integer) does not
> exist
> LINE 1: SELECT levenshtein_less_equal('extensive', 'exhaustive',2);
> ^
> HINT: No function matches the given name and argument types. You might need
> to add explicit type casts.
>
> Even this:
>
> SELECT levenshtein_less_equal('extensive'::TEXT,
> 'exhaustive'::TEXT,2::INTEGER);
> function levenshtein_less_equal(text, text, integer) does not exist
>
> But this works perfectly:
> SELECT levenshtein('extensive', 'exhaustive');
> 4
Are you sure you are connecting to a 9.1 server? levenshtein_less_equal
does not exist in 9.0- which would result in that error.
When you are in psql what does:
SELECT version();
and
\df levenshtein_less_equal
show?
>
>
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/levenshtein-less-equal-does-not-work-tp5788710p5788713.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
Just this: create extension fuzzystrmatch; If the extension was not installed, then the simple levenstein distance would not work either. -- View this message in context: http://postgresql.1045698.n5.nabble.com/levenshtein-less-equal-does-not-work-tp5788710p5788715.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
SELECT version();
and
\df levenshtein_less_equal
"PostgreSQL 9.1.11 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-52), 64-bit"
routing_nw2=# \df levenshtein_less_equal
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
routing_nw2=#
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/levenshtein-less-equal-does-not-work-tp5788710p5788717.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 01/24/2014 01:19 PM, alexandros_e wrote: > Just this: > > create extension fuzzystrmatch; > > If the extension was not installed, then the simple levenstein distance > would not work either. Which is what makes me think you are not connecting to the server you think you are. > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/levenshtein-less-equal-does-not-work-tp5788710p5788715.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@gmail.com
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 01/24/2014 01:28 PM, alexandros_e wrote: > SELECT version(); > > and > > \df levenshtein_less_equal > > "PostgreSQL 9.1.11 on x86_64-unknown-linux-gnu, compiled by gcc > (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit" > > routing_nw2=# \df levenshtein_less_equal List of functions Schema > | Name | Result data type | Argument data types | Type > --------+------+------------------+---------------------+------ (0 > rows) > > routing_nw2=# Hmm, can we see: \dx and \df levenshtein* Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.14 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJS4tu3AAoJEDfy90M199hlvUAP/RAH98dvcZ5/X5XxT90+mmh7 zZbvBhSbSSuE6lr9DZGKrMNOOYSFrgjwB+TA4GIJpNqx+4MUaAYuB4WyMf7zUD9a 2a22AFciA+lJie+DzcM3bCihaSvLwNA+CbavsmI4yYZu6Y0RED/1cFTe/Eg7aZhI hdv9ZiojY86GkEnii75L8WI+uwEQR3QNB7TPK34FyQUaBVxJxAl9iBOCU+IVkLph T1Kdv+M/gll2qlOO51nxNMfCTV1Ejw5cqbhhhSULkn9C4Vdnv8CGr7s2XEesmNkr f4x3sKNCzKSPTvrnTm5hgTiOtJq2snIIdkUas2pG+7zogJNclIeOB8QyQT5kmECD IaDAFIJUZpc6Pgrc08Rw06Cg9f3p4q0E9iQJhpx4b3TaupSwm3LKxVdXl2+D9giU 6+lP/iPPI0A5Ha6lohiU0CdhaYKlvEpeIVC3C4qJJ29+4i6vDhbNOF8eUWIBKfru nWgrpG40SsuqsA45TbtSXUPivEvxxq1xTBuEo1+zBhcUBmWjBnYmvRDmuT9rfeF0 31OpmeWWltbqrZIlnC2m2vs55gGSEiumnoYgrb+8GlWmPzsvBPPef+3k5CHPBlMd qwo1IAcdYpLtrZV2F6C3sWvRXTzTeWfNKOKJY9I3m3qTNzjBwXhcXYw3uuX/LKWO PYd9LRGyNJrUnzOl6nNF =AOTN -----END PGP SIGNATURE-----
=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
-# \df levenshtein*
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-------------+------------------+---------------------+--------
public | levenshtein | integer | text, text | normal
(1 row)
The server is in localhost. The binaries are from enterpriseDB. I also
downloaded src code for Postgres 9.1.11. Recompiled fuzzystrmatch, which
includes levenstein less_equal. Replaced .so file in Postgres installation
dir. Change in levenshtein limit of character successful.
It is not a compilation error, because I do not get levenstein less_equal
in the Postgres 9.2 server which is exactly as downloaded from
EnterpriseDB.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/levenshtein-less-equal-does-not-work-tp5788710p5788723.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 01/24/2014 01:38 PM, alexandros_e wrote:
> =# \dx List of installed extensions Name | Version | Schema |
> Description
> ---------+---------+------------+------------------------------
> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (1
> row)
According to this you have not CREATED fuzzystrmatch extension.
> -# \df levenshtein* List of functions Schema | Name |
> Result data type | Argument data types | Type
> --------+-------------+------------------+---------------------+--------
>
>
public | levenshtein | integer | text, text | normal
> (1 row)
> The server is in localhost. The binaries are from enterpriseDB. I
> also downloaded src code for Postgres 9.1.11. Recompiled
> fuzzystrmatch, which includes levenstein less_equal. Replaced .so
> file in Postgres installation dir. Change in levenshtein limit of
> character successful.
>
> It is not a compilation error, because I do not get levenstein
> less_equal in the Postgres 9.2 server which is exactly as
> downloaded from EnterpriseDB.
Simply replacing an .so file is not going to make any new functions
available from SQL. Are you sure the "CREATE EXTENSION fuzzystrmatch"
you ran was successful? (it sure looks like it was not from the above)
The output of those commands should look like:
test=# \df levenshtein*
List of functions
Schema | Name | Result data type |
Argument data types | Type
-
--------+------------------------+------------------+------------------------------------------------+--------
public | levenshtein | integer | text, text
| normal
public | levenshtein | integer | text, text,
integer, integer, integer | normal
public | levenshtein_less_equal | integer | text, text,
integer | normal
public | levenshtein_less_equal | integer | text, text,
integer, integer, integer, integer | normal
(4 rows)
test=# \dx
List of installed extensions
Name | Version | Schema |
Description
-
---------------+---------+------------+---------------------------------------------------------------------
fuzzystrmatch | 1.0 | public | determine similarities and
distance between strings
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
Joe
- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBAgAGBQJS4t8DAAoJEDfy90M199hlBWYP/1oT20qVXDrSjvfRhZNaZ1f+
iW7P3KuyRMiXpW3oXLzF/CI58ePewCFvZ0sIGMHWBGFpyS1bOeYKETpp2AXiActY
qwMDn3qkHaWiexEph158fdsqtIeaqNbJWOEWPkO6sm99Oib9hqWb18PUvKsDl9Xn
lmh+J5z8fL5MUBC30VlQW9TgpPSgl5CXbi2wi3k6bRIgF6DHnje2FOtxr+840u8d
PhIOcuHpPCdOzDZf9jFh9ZmYkeTTYxsi78EG0Vq+8YxpgqCzUgJPFGJDioc9+0Sy
gkvFO6CfmQnnbHZ4SPluQOc7TssREYvxIv9gcu0wlQxha0u2dVqyO12vrISgp4TA
WWNUl+ewF7UvQGkihU+bCHWnTSHKUuQhFt1HNZf6wnok8DItTnJioxLWUzWEERjD
42o6I+DctOI1frRMtXj9mkmxm+SmVfG1ozelQ2dwQyDiTXiuoKSedqLlcuY2POF8
tr1QMmOlhHz+3AzHAHwdvgSyQgCdyJNZz5deInxplG6jJ6JjlPyM91EXh9N8K+WR
wBp1XfIRb2DTHf1gbbl/iNX+GHZ7H5cUXhnre2y8TP1a19+DTOcvf3zvDAo51mCT
bD3Hs0CB5SdbYsQKfxn72eFC5o+wFYLZS0XHws0a1qJOEN0Tz1o57ozAJN7JIeW0
EJ4/zXgo2/41VxyVTJSk
=edCG
-----END PGP SIGNATURE-----
On 01/24/2014 01:38 PM, alexandros_e wrote: > =# \dx > List of installed extensions > Name | Version | Schema | Description > ---------+---------+------------+------------------------------ > plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language > (1 row) > > -# \df levenshtein* > List of functions > Schema | Name | Result data type | Argument data types | Type > --------+-------------+------------------+---------------------+-------- > public | levenshtein | integer | text, text | normal > (1 row) > > > The server is in localhost. The binaries are from enterpriseDB. I also > downloaded src code for Postgres 9.1.11. Recompiled fuzzystrmatch, which > includes levenstein less_equal. Replaced .so file in Postgres installation > dir. Change in levenshtein limit of character successful. > > It is not a compilation error, because I do not get levenstein less_equal > in the Postgres 9.2 server which is exactly as downloaded from > EnterpriseDB. You realize fuzzystrmatch is a contrib package, not part of the core code for Postgres? I do not use the EnterpriseDB installer enough to be certain, but I am fairly sure you can use the StackBuilder app to get and install the package for you. > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/levenshtein-less-equal-does-not-work-tp5788710p5788723.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@gmail.com
With your help I found the solution. My DB was initially based on a 9.0 server and I had restored it on the 9.1 server. For some reason 9.0 fuzzystrmatch functions (levenstein...) were already present in this DB. So, CREATE EXTENSION fuzzystrmatch did not work. I had to manually remove function 9.0 fuzzystrmatch functions and then CREATE EXTENSION .... worked perfectly and levenstein_less_equal as well. So, it was error from the DB restored from an older version of postgresql. -- View this message in context: http://postgresql.1045698.n5.nabble.com/levenshtein-less-equal-does-not-work-tp5788710p5788729.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 01/24/2014 02:07 PM, alexandros_e wrote: > With your help I found the solution. My DB was initially based on a > 9.0 server and I had restored it on the 9.1 server. For some reason > 9.0 fuzzystrmatch functions (levenstein...) were already present in > this DB. So, CREATE EXTENSION fuzzystrmatch did not work. I had to > manually remove function 9.0 fuzzystrmatch functions and then > CREATE EXTENSION .... worked perfectly and levenstein_less_equal as > well. So, it was error from the DB restored from an older version > of postgresql. Normally in this situation you are supposed to do something like this after the dump/restore: CREATE EXTENSION fuzzystrmatch FROM unpackaged; The file fuzzystrmatch--unpackaged--1.0.sql which is shipped with fuzzystrmatch will then "do the right thing" (at least in theory, can't say I have tried it extensively). But your method works too ;-) Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.14 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJS4uV8AAoJEDfy90M199hlaRYP/2ODsaC4SuVmpv1+XiyMjfoV MB5Mvwerlngw/WgGZMf/E7bDnOzGlReXRG9t8pCaT+DuwXgnvK7JlaOU8yCZaf1N wUBFf9N4XU21swNpgZU7UIOEfVM1r8dWQaQtP3bVOapTDiWQTnJIymAHouz3Mgn7 3dUwcurVSv2p3WCVsTXaQMwy2wK4BmUftkT2ooPpG00HodZc6zsiu/PO660RAw8W hyxYPv8tqHPBq1GTM2wcXNU9AYO7gzrvSGwxNZP2JKthSIUBdl7bUXGSe422MC9O 9bfNVFMzOtOBdNIdkmsAre2yROFs1rSa9nvwqdJJ9bmkQOLfX6+2LPsfSZWda5HE 3ifpfjfwFnEGfKKJZqnb4r1giHWFXQ2rcKAtUIBumUj5l3y/3Yw1S1cOx0lzTeF4 BvauvvQPn9+ynEh8/eSFUNDlTxpSp+QQWfc06KO+nU3f2UUMKXRNiswiDxlwwuyj YBYdnpwg3jc9ePLqDMDYScACDzxVoEWL0AEYyFZkkTV/wCP37bK/y+HoKC2onyIm Uk/LCMsisDcv0aFgkDDZ7zqJ8KlWcP0+ZTvAKMH3TVIP+rPJ4X/XhQKV8L8Dzhc5 fsZ383QIFtODxxRHFyLNVj1dYmq3+Jg5P6YmVFdw1e0HGP5KA2pCgKPcrEOoF5BF x4g2W68HwWgxtw21NlLC =mkQE -----END PGP SIGNATURE-----
On 01/24/2014 02:07 PM, alexandros_e wrote: > With your help I found the solution. My DB was initially based on a 9.0 > server and I had restored it on the 9.1 server. For some reason 9.0 > fuzzystrmatch functions (levenstein...) were already present in this DB. So, > CREATE EXTENSION fuzzystrmatch did not work. I had to manually remove > function 9.0 fuzzystrmatch functions and then CREATE EXTENSION .... worked > perfectly and levenstein_less_equal as well. So, it was error from the DB > restored from an older version of postgresql. Just as well my advice turns out to be bogus. The contrib stuff is already loaded into the share/ directory. > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/levenshtein-less-equal-does-not-work-tp5788710p5788729.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@gmail.com