Thread: FTS don't work

FTS don't work

From
"Martins Zarins"
Date:
Hi, all!

There is one full text search solution contributed with postgres
source (I use 7.1.3). And there is sed that

select id from fti where string ~ '^substring'

will use indexes.
But it don't.

As I find out index works only if I search like
SELECT id from fti where string = 'string';

How can I search for strings like 'strin%' and use index?


Mark

Race condition in 7.1.2?

From
Steve Frampton
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello:

I've been writing a billing system with C++, ODBC, and PostgreSQL 7.1.2.
After I optimized my queries, I was surprised at how fast PostgreSQL can
swoop through 200,000 billing records.  But I noticed, sometimes, the
final stage of my processing never completes, and PostgreSQL is spiralling
the CPU at almost 100%.  I have to kill the process, which is thankfully
detected by my application which does a rollback.

Anyway, I'm quite sure it isn't my software that is causing this, because
if I do a simple vacuum after the rollback, and then run the same sequence
again, the final stage completes as quickly as I've come to expect.

I'm just wondering if there is a known race condition or something else
which would cause this phenomenon.

Thanks in advance.

- ---------------< LINUX: The choice of a GNU generation. >-------------
Steve Frampton   <frampton@LinuxNinja.com>   http://www.LinuxNinja.com
GNU Privacy Guard ID: D055EBC5  (see http://www.gnupg.org for details)
GNU-PG Fingerprint: EEFB F03D 29B6 07E8 AF73  EF6A 9A72 F1F5 D055 EBC5
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.0 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE7uvUJmnLx9dBV68URAoz5AJ4jIyNXAM1ly4iehXKczXEEtd1FNwCeLVEY
SKJ4PPoMK7w2WlfmoWj+rnk=
=fVJo
-----END PGP SIGNATURE-----


Re: Race condition in 7.1.2?

From
Steve Frampton
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, 3 Oct 2001, Steve Frampton wrote:

> But I noticed, sometimes, the final stage of my processing never
> completes, and PostgreSQL is spiralling the CPU at almost 100%.  I
> have to kill the process, which is thankfully detected by my
> application which does a rollback.
>
> Anyway, I'm quite sure it isn't my software that is causing this, because
> if I do a simple vacuum after the rollback, and then run the same sequence
> again, the final stage completes as quickly as I've come to expect.

Just thought I'd add that, when my process runs normally, PostgreSQL takes
between 30% and 50% of CPU.  Only when this mysterious condition occurs
does it hit near 100%.  Also, I happened to notice upon vacuuming the DB
the last time this happened, slightly larger than a multiple of my tuples
were deleted from the index -- which seems to indicate that my application
did the processing it was supposed to have done, but somehow PostgreSQL
got "stuck" on the final update.

Strange.

- ---------------< LINUX: The choice of a GNU generation. >-------------
Steve Frampton   <frampton@LinuxNinja.com>   http://www.LinuxNinja.com
GNU Privacy Guard ID: D055EBC5  (see http://www.gnupg.org for details)
GNU-PG Fingerprint: EEFB F03D 29B6 07E8 AF73  EF6A 9A72 F1F5 D055 EBC5
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.0 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE7uvlhmnLx9dBV68URAvBQAJ9oeMWMpKmw0br/ISXrlKgZLe80xQCggg5Y
R8RPG9jyVuzo2TV3qoLquLY=
=3st0
-----END PGP SIGNATURE-----


Re: Race condition in 7.1.2?

From
Tom Lane
Date:
There's not enough information here to give any useful answer.

A replicatable test case would be nice, or at least show us the queries
that are taking a long time and the table schemas they are working on.

            regards, tom lane