Thread: Prefix search, word completion, full text search

Hi !

I am  playing with the new full  text search in PostgreSQL  8.3. I would
like to search for prefixes to  allow to complete words. For example, if
I have  the lexemes 'foo', 'foobar' and  'bar', I would like  to get all
the words whose prefix is 'foo', i.e 'foo' and 'foobar'.

GIST or  GIN (I don't remember)  should allow to search  for prefix. How
can I query them?

Thanks.
--
BOFH excuse #348:
We're on Token Ring, and it looks like the token got loose.

On Sat, 29 Dec 2007, Vincent Bernat wrote:

> Hi !
>
> I am  playing with the new full  text search in PostgreSQL  8.3. I would
> like to search for prefixes to  allow to complete words. For example, if
> I have  the lexemes 'foo', 'foobar' and  'bar', I would like  to get all
> the words whose prefix is 'foo', i.e 'foo' and 'foobar'.
>
> GIST or  GIN (I don't remember)  should allow to search  for prefix. How
> can I query them?

there is no built-in. For your purpose I'd create separate table with
words you want to be completed (anywa, you dont complete garbage words)
and write simple dictionary which generates all prefixes beginning from
minlen (about 3) for input word and use it to create GIN index.
Should works. IIRC, someone already asked about such dictionary and even
posted such dictionary.

>
> Thanks.
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

OoO En ce  début de soirée du samedi 29 décembre  2007, vers 21:50, Oleg
Bartunov <oleg@sai.msu.su> disait:

>> GIST or  GIN (I don't remember)  should allow to search  for prefix. How
>> can I query them?

> there is no built-in. For your purpose I'd create separate table with
> words you want to be completed (anywa, you dont complete garbage words)
> and write simple dictionary which generates all prefixes beginning
> from minlen (about 3) for input word and use it to create GIN index.

If I have a table with all  the words that I want to complete, why would
I use something more complex than this?

 SELECT word FROM words WHERE word LIKE 'foo%'

Does such a search can't use normal index?

> Should works. IIRC, someone already asked about such dictionary and even
> posted such dictionary.

Do you  have some keywords to  search for? "dictionary  prefix" does not
match anything useful.

Thanks.
--
Write clearly - don't be too clever.
            - The Elements of Programming Style (Kernighan & Plauger)

Hi,
We are running PostgreSQL 8.2.5 on amd64-portbld-freebsd6.2. We have 2 databases, one is a small one (and the pg_dump works ok) and the other one with 14,553,106 rows (the larget table has 3,700,000 rows).
Every day we run a Windows Programmed pg_dump, it used to work fine with PostgreSQL 8.0, but since we installed the new version we are not able to make a backup. We tried to make a whole database backup and it never ends. We tried to backup table by table and it reachs a table of 50,000 rows and it keeps for hours in that table, we started it again and pg_dump backups that same table in seconds, but it stops in another different table.
If we watch the server message.# we don't have any error, it's like some tables were locked and pg_dump would keep waiting for the table to be unlocked.

Do we need to configure the server to be able to make a backup as we used to do it with 8.0 version?

Thanks.
     Sebastián

---

Yahoo! Encuentros
Ahora encontrar pareja es mucho más fácil, probá el nuevo Yahoo! Encuentros.
Visitá http://yahoo.cupidovirtual.com/servlet/NewRegistration

Sebastián Baioni wrote:
> Every day we run a Windows Programmed pg_dump, it used to
> work fine with PostgreSQL 8.0, but since we installed the new
> version we are not able to make a backup. We tried to make a
> whole database backup and it never ends. We tried to backup
> table by table and it reachs a table of 50,000 rows and it
> keeps for hours in that table, we started it again and
> pg_dump backups that same table in seconds, but it stops in
> another different table.
> If we watch the server message.# we don't have any error,
> it's like some tables were locked and pg_dump would keep
> waiting for the table to be unlocked.

If pg_dump is locked out, there should be entries in
pg_catalog.pg_locks. Check when the dump hangs!

Does the dump file keep growing or not?

Yours,
Laurenz Albe

I don't have any table called pg_locks in pg_catalog.
We let running pg_dump 8.2.5 at 1:30 AM after an automatic reboot of the PostgreSQL service and it didn't finish. When I came to the office at 9:00 there were a lot of locks, but all of them were from the pg_dump conection.

Thanks
     Sebastián

Albe Laurenz <laurenz.albe@wien.gv.at> escribió:

Sebastián Baioni wrote:
> Every day we run a Windows Programmed pg_dump, it used to
> work fine with PostgreSQL 8.0, but since we installed the new
> version we are not able to make a backup. We tried to make a
> whole database backup and it never ends. We tried to backup
> table by table and it reachs a table of 50,000 rows and it
> keeps for hours in that table, we started it again and
> pg_dump backups that same table in seconds, but it stops in
> another different table.
> If we watch the server message.# we don't have any error,
> it's like some tables were locked and pg_dump would keep
> waiting for the table to be unlocked.

If pg_dump is locked out, there should be entries in
pg_catalog.pg_locks. Check when the dump hangs!

Does the dump file keep growing or not?

Yours,
Laurenz Albe

---

Los referentes más importantes en compra/venta de autos se juntaron:
Demotores y Yahoo!. Ahora comprar o vender tu auto es más fácil.
Visitá http://ar.autos.yahoo.com/

On Jan 3, 2008, at 5:59 AM, Sebastián Baioni wrote:

> I don't have any table called pg_locks in pg_catalog.
> We let running pg_dump 8.2.5 at 1:30 AM after an automatic reboot
> of the PostgreSQL service and it didn't finish. When I came to the
> office at 9:00 there were a lot of locks, but all of them were from
> the pg_dump conection.

The pg_locks view most certainly exists.  Here's the list of all of
the system catalog tables and view:  http://www.postgresql.org/docs/
8.2/interactive/catalogs.html.  If you haven't looked at pg_locks how
do you know "there were a lot of locks"?

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Please, don't top post!

Sebastián Baioni wrote:
>>> Every day we run a Windows Programmed pg_dump, it used to
>>> work fine with PostgreSQL 8.0, but since we installed the new
>>> version we are not able to make a backup. We tried to make a
>>> whole database backup and it never ends. We tried to backup
>>> table by table and it reachs a table of 50,000 rows and it
>>> keeps for hours in that table, we started it again and
>>> pg_dump backups that same table in seconds, but it stops in
>>> another different table.
>>> If we watch the server message.# we don't have any error,
>>> it's like some tables were locked and pg_dump would keep
>>> waiting for the table to be unlocked.
>>
>> If pg_dump is locked out, there should be entries in
>> pg_catalog.pg_locks. Check when the dump hangs!
>
> I don't have any table called pg_locks in pg_catalog.

It is a view.

> We let running pg_dump 8.2.5 at 1:30 AM after an automatic
> reboot of the PostgreSQL service and it didn't finish. When I
> came to the office at 9:00 there were a lot of locks, but all
> of them were from the pg_dump conection.

Is there any row in pg_locks that has a "false" in "granted"?
That would be a wait for a lock to be released.

Does pg_dump consume any processor cycles?

You say "Windows Programmed pg_dump" - what exactly is that?
Can you use a debugger to see where your program hangs?

Yours,
Laurenz Albe

>Sebastián Baioni wrote:
>>>> Every day we run a Windows Programmed pg_dump, it used to 
>>>> work fine with PostgreSQL 8.0, but since we installed the new 
>>>> version we are not able to make a backup. We tried to make a 
>>>> whole database backup and it never ends. We tried to backup 
>>>> table by table and it reachs a table of 50,000 rows and it 
>>>> keeps for hours in that table, we started it again and 
>>>> pg_dump backups that same table in seconds, but it stops in 
>>>> another different table.
>>>> If we watch the server message.# we don't have any error, 
>>>> it's like some tables were locked and pg_dump would keep 
>>>> waiting for the table to be unlocked.
>>>
>>> If pg_dump is locked out, there should be entries in
>>> pg_catalog.pg_locks.Check when the dump hangs!
>>
>> I don't have any table called pg_locks in pg_catalog.


It is a view.

> We let running pg_dump 8.2.5 at 1:30 AM after an automatic 
> reboot of the PostgreSQL service and it didn't finish. When I 
> came to the office at 9:00 there were a lot of locks, but all 
> of them were from the pg_dump conection.
>
>Is there any row in pg_locks that has a "false" in "granted"?
>That would be a wait for a lock to be released.
>
>Does pg_dump consume any processor cycles?
>
>You say "Windows Programmed pg_dump" - what exactly is that?
>Can you use a debugger to see where your program hangs?
>
>Yours,
>Laurenz Albe

--

I don't know how it is "Tareas programadas" in english, I have Windows in spanish, but it's the tool where yuo set a work to execute in a determinated time. We call a .bat that executepg_dump:
"C:\Archivos de programa\PostgreSQL\8.2\bin\pg_dump.exe" -i -h 192.168.0.1 -p 5432 -U MyUser -F c -b -v -f "C:\back\pd_dump\si\si.backup" MyDB

--

>Erik Jones wrote:
>The pg_locks view most certainly exists. Here's the list of all of
>the system catalog tables and view: http://www.postgresql.org/docs/
>8.2/interactive/catalogs.html. If you haven't looked at pg_locks how
>do you know "there were a lot of locks"?

With the Server Status in the pg Admin III.

Thanks
     Sebastián

---

Los referentes más importantes en compra/venta de autos se juntaron:
Demotores y Yahoo!. Ahora comprar o vender tu auto es más fácil.
Visitá http://ar.autos.yahoo.com/

Sebastián Baioni wrote:
>>>>> Every day we run a Windows Programmed pg_dump, it used to
>>>>> work fine with PostgreSQL 8.0, but since we installed the new
>>>>> version we are not able to make a backup. We tried to make a
>>>>> whole database backup and it never ends. We tried to backup
>>>>> table by table and it reachs a table of 50,000 rows and it
>>>>> keeps for hours in that table, we started it again and
>>>>> pg_dump backups that same table in seconds, but it stops in
>>>>> another different table.
>>>>> If we watch the server message.# we don't have any error,
>>>>> it's like some tables were locked and pg_dump would keep
>>>>> waiting for the table to be unlocked.
>>>
>>> We let running pg_dump 8.2.5 at 1:30 AM after an automatic
>>> reboot of the PostgreSQL service and it didn't finish. When I
>>> came to the office at 9:00 there were a lot of locks, but all
>>> of them were from the pg_dump conection.
>>>
>>> Is there any row in pg_locks that has a "false" in "granted"?
>>> That would be a wait for a lock to be released.

Is there, now?

Try
SELECT locktype, relation, pid, mode, granted FROM pg_catalog.pg_locks;

>> Does pg_dump consume any processor cycles?

Does it, now, while the dump hangs?

>> You say "Windows Programmed pg_dump" - what exactly is that?
>> Can you use a debugger to see where your program hangs?
>
> I don't know how it is "Tareas programadas" in english, I
> have Windows in spanish, but it's the tool where yuo set a
> work to execute in a determinated time. We call a .bat that execute
>  pg_dump:
> "C:\Archivos de programa\PostgreSQL\8.2\bin\pg_dump.exe" -i -h 192.168.0.1 -p 5432 -U MyUser -F c -b -v -f
"C:\back\pd_dump\si\si.backup"MyDB 

That seems fine. Except that the "-i" is bad (I told you to use the same version of pg_dump as the server version).
And "-b" is without effect, and "-v" won't do anything for you. But no problem.

Maybe you could try "-F p" temporarily so that you can see where the output is stalled.

Also, when the dump hangs, what is the result of:

SELECT procpid, current_timestamp, query_start, current_query
FROM pg_catalog.pg_stat_activity;

Yours,
Laurenz Albe

Albe Laurenz <laurenz.albe@wien.gv.at> escribió:

Sebastián Baioni wrote:
>>>>> Every day we run a Windows Programmed pg_dump, it used to
>>>>> work fine with PostgreSQL 8.0, but since we installed the new
>>>>> version we are not able to make a backup. We tried to make a
>>>>> whole database backup and it never ends. We tried to backup
>>>>> table by table and it reachs a table of 50,000 rows and it
>>>>> keeps for hours in that table, we started it again and
>>>>> pg_dump backups that same table in seconds, but it stops in
>>>>> another different table.
>>>>> If we watch the server message.# we don't have any error,
>>>>> it's like some tables were locked and pg_dump would keep
>>>>> waiting for the table to be unlocked.
>>>
>>> We let running pg_dump 8.2.5 at 1:30 AM after an automatic
>>> reboot of the PostgreSQL service and it didn't finish. When I
>>> came to the office at 9:00 there were a lot of locks, but all
>>> of them were from the pg_dump conection.
>>>
>>> Is there any row in pg_locks that has a "false" in "granted"?
>>> That would be a wait for a lock to be released.

Is there, now?

Try
SELECT locktype, relation, pid, mode, granted FROM pg_catalog.pg_locks;

>> Does pg_dump consume any processor cycles?

Does it, now, while the dump hangs?

>> You say "Windows Programmed pg_dump" - what exactly is that?
>> Can you use a debugger to see where your program hangs?
>
> I don't know how it is "Tareas programadas" in english, I
> have Windows in spanish, but it's the tool where yuo set a
> work to execute in a determinated time. We call a .bat that execute
> pg_dump:
> "C:\Archivos de programa\PostgreSQL\8.2\bin\pg_dump.exe" -i -h 192.168.0.1 -p 5432 -U MyUser -F c -b -v -f "C:\back\pd_dump\si\si.backup" MyDB

That seems fine. Except that the "-i" is bad (I told you to use the same version of pg_dump as the server version).
And "-b" is without effect, and "-v" won't do anything for you. But no problem.

Maybe you could try "-F p" temporarily so that you can see where the output is stalled.

Also, when the dump hangs, what is the result of:

SELECT procpid, current_timestamp, query_start, current_query
FROM pg_catalog.pg_stat_activity;

Yours,
Laurenz Albe

Thank you everyone for your help, now backups works ok, I'm using the same pg_dump version than server and I took out the -i parameter.

Thank you.

---

Yahoo! Encuentros
Ahora encontrar pareja es mucho más fácil, probá el nuevo Yahoo! Encuentros.
Visitá http://yahoo.cupidovirtual.com/servlet/NewRegistration