Re: tsearch in core patch, for inclusion

We (Oleg and me) are glad to present tsearch in core of pgsql patch. In basic,
layout, functions, methods, types etc are the same as in current tsearch2 with a
lot of improvements:

- pg_ts_* tables now are in pg_catalog
- parsers, dictionaries, configurations now have owner and namespace similar to
other pgsql's objects like tables, operator classes etc
- current tsearch configuration is managed with a help of GUC variable
tsearch_conf_name.
- choosing of tsearch cfg by locale may be done for each schema separately
- managing of tsearch configuration with a help of SQL commands, not with
insert/update/delete statements. This allows to drive dependencies,
correct dumping and dropping.
- psql support with a help of \dF* commands
- add all available Snowball stemmers and corresponding configuration
- correct memory freeing by any dictionary

Work is sponsored by EnterpriseDB's PostgreSQL Development Fund.

patch: http://www.sigaev.ru/misc/tsearch_core-0.33.gz
docs: http://mira.sai.msu.su/~megera/pgsql/ftsdoc/ (not yet completed and it's
not yet a patch, just a SGML source)

Implementation details:
- directory layout
src/backend/utils/adt/tsearch - all IO function and simple operations
src/backend/utils/tsearch - complex processing functions, including
language processing and dictionaries
- most of snowball dictionaries are placed in separate .so library and
they plug in into data base by similar way as character conversation
library does.

If there aren't objections then we plan commit patch tomorrow or after tomorrow.
Before committing, I'll changes oids from 5000+ to lower values to prevent holes
in oids. And after that, I'll remove tsearch2 contrib module.

--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/

Teodor Sigaev wrote:
> If there aren't objections then we plan commit patch tomorrow or
> after tomorrow.

I still haven't heard any argument for why this would be necessary or
desirable at all, other than that it looks better for marketing
reasons, which I will counter by saying that it looks worse for
marketing reasons because our hailed plugin mechanism is apparently so
poor that it can't support some practical extension module such as
this.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Teodor Sigaev wrote:
>> If there aren't objections then we plan commit patch tomorrow or
>> after tomorrow.
>
> I still haven't heard any argument for why this would be necessary or
> desirable at all, other than that it looks better for marketing
> reasons, which I will counter by saying that it looks worse for
> marketing reasons because our hailed plugin mechanism is apparently so
> poor that it can't support some practical extension module such as
> this.

Of which I will counter that we don't have a hailed plugin mechanism. We
have a contrib which professionals generally consider untested and not
part of PostgreSQL.

I am constantly running into this:

Q. Does PostgreSQL have full text indexing?
A. Yes it is in contrib.
Q. But that isn't part of core.
A. *sigh*

Where on the website can I see what "plugins" are included with PostgreSQL?

Where on the website can I see the Official PostgreSQL Documentation for
Full Text Indexing?

With TSearch2 in core will that fix the many upgrade problems associated
with using TSearch2?

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

> Teodor Sigaev wrote:
>> If there aren't objections then we plan commit patch tomorrow or
>> after tomorrow.

This is a fairly large patch and I would like the chance to review it
before it goes in --- "we'll commit tomorrow" is not exactly a decent
review window.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> I still haven't heard any argument for why this would be necessary or
> desirable at all, other than that it looks better for marketing
> reasons,

One possible argument for this over the contrib version is a saner
approach to dumping and restoring configurations. However, as against
that:

1) what's the upgrade path for getting an existing tsearch2
configuration into this implementation?

2) once we put this in core we are going to be stuck with supporting its
SQL API forever. Are we convinced that this API is the one we want?
I don't recall even having seen any proposal or discussion. It was OK
for tsearch2's API to change every release while it was in contrib, but
the expectation of stability is a whole lot higher for core features.

regards, tom lane

Joshua D. Drake wrote:
> Peter Eisentraut wrote:
>
>> Teodor Sigaev wrote:
>>
>>> If there aren't objections then we plan commit patch tomorrow or
>>> after tomorrow.
>>>
>> I still haven't heard any argument for why this would be necessary or
>> desirable at all, other than that it looks better for marketing
>> reasons, which I will counter by saying that it looks worse for
>> marketing reasons because our hailed plugin mechanism is apparently so
>> poor that it can't support some practical extension module such as
>> this.
>>
>
> Of which I will counter that we don't have a hailed plugin mechanism. We
> have a contrib which professionals generally consider untested and not
> part of PostgreSQL.
>
> I am constantly running into this:
>
> Q. Does PostgreSQL have full text indexing?
> A. Yes it is in contrib.
> Q. But that isn't part of core.
> A. *sigh*
>
> Where on the website can I see what "plugins" are included with PostgreSQL?
>
> Where on the website can I see the Official PostgreSQL Documentation for
> Full Text Indexing?
>
> With TSearch2 in core will that fix the many upgrade problems associated
> with using TSearch2?
>
>
>

contrib is a horrible misnomer. Can we maybe bite the bullet and call it
something else?

cheers

andrew

On Wed, 2007-01-24 at 19:15 +0100, Peter Eisentraut wrote:
> Teodor Sigaev wrote:
> > If there aren't objections then we plan commit patch tomorrow or
> > after tomorrow.
>
> I still haven't heard any argument for why this would be necessary or
> desirable at all, other than that it looks better for marketing
> reasons, which I will counter by saying that it looks worse for
> marketing reasons because our hailed plugin mechanism is apparently so
> poor that it can't support some practical extension module such as
> this.
>

On that point, why do we have /contrib? It's for "plugins" that are so
version-dependent that they can't exist as a separate project, as I
understand it.

But what we want when we say we have a plugin mechanism is something
more like CPAN, where software is developed on it's own timeline and can
be added seamlessly into any version of PostgreSQL that supports the
needs of the project.

PostGIS is a good example of this. You don't have to wait for a
PostgreSQL release to upgrade PostGIS, and they don't have to discuss
the intricacies of spatial queries and data on -hackers.

If tsearch2 really does need to be in lockstep with the PostgreSQL
releases (although I don't see why it does), I don't see a problem
putting it in core. It's an important feature, and we're already giving
up a lot of the benefits of plugins anyway by distributing it with the
project.

Regards,
Jeff Davis

On Wed, Jan 24, 2007 at 01:53:54PM -0500, Andrew Dunstan wrote:
> Joshua D. Drake wrote:
> >Peter Eisentraut wrote:
> >
> >>Teodor Sigaev wrote:
> >>
> >>>If there aren't objections then we plan commit patch tomorrow or
> >>>after tomorrow.
> >>>
> >>I still haven't heard any argument for why this would be necessary or
> >>desirable at all, other than that it looks better for marketing
> >>reasons, which I will counter by saying that it looks worse for
> >>marketing reasons because our hailed plugin mechanism is apparently so
> >>poor that it can't support some practical extension module such as
> >>this.
> >
> >Of which I will counter that we don't have a hailed plugin mechanism. We
> >have a contrib which professionals generally consider untested and not
> >part of PostgreSQL.
> >
> >I am constantly running into this:
> >
> >Q. Does PostgreSQL have full text indexing?
> >A. Yes it is in contrib.
> >Q. But that isn't part of core.
> >A. *sigh*
> >
> >Where on the website can I see what "plugins" are included with
> >PostgreSQL?
> >
> >Where on the website can I see the Official PostgreSQL
> >Documentation for Full Text Indexing?
> >
> >With TSearch2 in core will that fix the many upgrade problems
> >associated with using TSearch2?
>
> contrib is a horrible misnomer. Can we maybe bite the bullet and
> call it something else?

Some version of "version-dependent plugins?"

Cheers,
D (who hasn't come up with anything shorter just yet)
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Wed, 24 Jan 2007, Peter Eisentraut wrote:

> Teodor Sigaev wrote:
> > If there aren't objections then we plan commit patch tomorrow or
> > after tomorrow.
>
> I still haven't heard any argument for why this would be necessary or
> desirable at all, other than that it looks better for marketing
> reasons, which I will counter by saying that it looks worse for
> marketing reasons because our hailed plugin mechanism is apparently so
> poor that it can't support some practical extension module such as
> this.

I for one am greatly looking forward to tsearch2 being in core. I was
very fond of the plugin mechanism, until I signed up with a hosting
provider. I do not have superuser privileges on the database cluster, and
they will not install any plugins due to unspecified "security concerns".
So ATM if I want full text indexing, my only choice would be to avail
myself of their mysql instance which has it built in. So I have been
jaded, and my opinion of optional plugins has gone from "wow, this is
neat" to "man, this is a pain". They do not install plpgsql so I cannot
write any triggers, they don't install tsearch2 so I don't get full text
indexing, so all of the great features of postgres I have come to enjoy on
my own box are suddenly taken away :(

Sorry for the rant, I am just looking forward to 8.3 so I could get full
text indexing...

--
ARCHDUKE FERDINAND FOUND ALIVE --
FIRST WORLD WAR A MISTAKE

Jeremy Drake wrote:
> On Wed, 24 Jan 2007, Peter Eisentraut wrote:
>
>> Teodor Sigaev wrote:
>>> If there aren't objections then we plan commit patch tomorrow or
>>> after tomorrow.
>> I still haven't heard any argument for why this would be necessary or
>> desirable at all, other than that it looks better for marketing
>> reasons, which I will counter by saying that it looks worse for
>> marketing reasons because our hailed plugin mechanism is apparently so
>> poor that it can't support some practical extension module such as
>> this.
>
> I for one am greatly looking forward to tsearch2 being in core. I was
> very fond of the plugin mechanism, until I signed up with a hosting
> provider. I do not have superuser privileges on the database cluster, and
> they will not install any plugins due to unspecified "security concerns".

You could move to Hub or Command Prompt ;)

Joshua D. Drake

> So ATM if I want full text indexing, my only choice would be to avail
> myself of their mysql instance which has it built in. So I have been
> jaded, and my opinion of optional plugins has gone from "wow, this is
> neat" to "man, this is a pain". They do not install plpgsql so I cannot
> write any triggers, they don't install tsearch2 so I don't get full text
> indexing, so all of the great features of postgres I have come to enjoy on
> my own box are suddenly taken away :(
>
> Sorry for the rant, I am just looking forward to 8.3 so I could get full
> text indexing...
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Wed, 2007-01-24 at 13:49 -0500, Tom Lane wrote:
> 2) once we put this in core we are going to be stuck with supporting its
> SQL API forever. Are we convinced that this API is the one we want?
> I don't recall even having seen any proposal or discussion.

There has been some prior discussion:

http://archives.postgresql.org/pgsql-hackers/2006-12/msg00919.php

But I agree that we need considerably more discussion before committing
the patch. I'm personally not sold on the need for modifications to the
SQL grammar, for example, as opposed to just using a set of SQL-callable
functions and some new system catalogs.

Another question that would be easier to resolve before the patch is
committed is naming: the patch currently uses a mix of "full text" and
"tsearch[2]" as the name of the full-text search feature. If we're going
to bless this as "the" integrated full-text search in PG, it might make
more sense to use "full text search" and "FTS" exclusively.

-Neil

Jeremy Drake wrote:
> On Wed, 24 Jan 2007, Peter Eisentraut wrote:
>
>> I still haven't heard any argument for why this would be necessary or
>> desirable at all, other than that it looks better for marketing
>> reasons, which I will counter by saying that it looks worse for
>> marketing reasons because our hailed plugin mechanism is apparently so
>> poor that it can't support some practical extension module such as
>> this.
>>
>
> I for one am greatly looking forward to tsearch2 being in core.
>
>

For goodness' sake! This is work that's been sponsored! Are we going to
turn around now and reject it? We'd be a laughing stock.

cheers

andrew

Andrew Dunstan wrote:
> contrib is a horrible misnomer. Can we maybe bite the bullet and call
> it something else?

plugins?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Jeff Davis wrote:
> On that point, why do we have /contrib? It's for "plugins" that are
> so version-dependent that they can't exist as a separate project, as
> I understand it.

No. (I don't know a better and succinct answer, but that is not it.)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Jeremy Drake wrote:
> I for one am greatly looking forward to tsearch2 being in core. I
> was very fond of the plugin mechanism, until I signed up with a
> hosting provider.

Yes, you have told us about your hosting provider before. Just make
sure your next hosting provider does not refuse to install database
objects whose OID is a multiple of 13 because of bad luck, or you might
miss out on full-text indexing again.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Jeremy Drake wrote:
>> I for one am greatly looking forward to tsearch2 being in core. I
>> was very fond of the plugin mechanism, until I signed up with a
>> hosting provider.
>
> Yes, you have told us about your hosting provider before. Just make
> sure your next hosting provider does not refuse to install database
> objects whose OID is a multiple of 13 because of bad luck, or you might
> miss out on full-text indexing again.

Well we just turn off OIDs to help prevent that possible curse.

Sincerely,

Joshua D. Drake

>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Neil Conway wrote:
> On Wed, 2007-01-24 at 13:49 -0500, Tom Lane wrote:
>> 2) once we put this in core we are going to be stuck with supporting its
>> SQL API forever. Are we convinced that this API is the one we want?
>> I don't recall even having seen any proposal or discussion.
>
> There has been some prior discussion:
>
> http://archives.postgresql.org/pgsql-hackers/2006-12/msg00919.php
>
> But I agree that we need considerably more discussion before committing
> the patch. I'm personally not sold on the need for modifications to the
> SQL grammar, for example, as opposed to just using a set of SQL-callable
> functions and some new system catalogs.

I think one can find arguments for both variants - one of the question
might even be how other databases are doing that and if the proposed
syntax is resembling one of those or not.

>
> Another question that would be easier to resolve before the patch is
> committed is naming: the patch currently uses a mix of "full text" and
> "tsearch[2]" as the name of the full-text search feature. If we're going
> to bless this as "the" integrated full-text search in PG, it might make
> more sense to use "full text search" and "FTS" exclusively.

making this consistent makes a lot of sense and I agree that it might be
a good idea to just call it FTS (or similiar).
But on the other side would have to go as far as renaming
TSVECTOR/TSQUERY to FTSVECTOR/FTSQUERY or similiar which might pose some
considerable headache for people upgrading from the contrib/ version.

Stefan

Neil Conway wrote:
> But I agree that we need considerably more discussion before
> committing the patch. I'm personally not sold on the need for
> modifications to the SQL grammar, for example, as opposed to just
> using a set of SQL-callable functions and some new system catalogs.

In particular, I would think that unless one is affiliated with The New
COBOL World Order, one would *prefer* a set of functions over new SQL
statements. And using functions to manage extensions seems to be the
established way in Oracle land, if that matters at all.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Andrew Dunstan wrote:
>
>> contrib is a horrible misnomer. Can we maybe bite the bullet and call
>> it something else?
>>
>
> plugins?
>
>

standard-plugins might be more informative. I think of them as being
like perl's standard modules, things that are part of the standard perl
distribution as opposed to all the other stuff on CPAN.

Maybe it needs to split into two - things that are genuine plugins and
other stuff (e.g. start-scripts).

cheers

andrew

Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> writes:
> Neil Conway wrote:
>> Another question that would be easier to resolve before the patch is
>> committed is naming: the patch currently uses a mix of "full text" and
>> "tsearch[2]" as the name of the full-text search feature. If we're going
>> to bless this as "the" integrated full-text search in PG, it might make
>> more sense to use "full text search" and "FTS" exclusively.

> making this consistent makes a lot of sense and I agree that it might be
> a good idea to just call it FTS (or similiar).
> But on the other side would have to go as far as renaming
> TSVECTOR/TSQUERY to FTSVECTOR/FTSQUERY or similiar which might pose some
> considerable headache for people upgrading from the contrib/ version.

If we use "text search" (abbrev TS) as the key phrase we can avoid that.

But this reiterates my point that the upgrade path for existing tsearch2
users is an important thing to consider.

regards, tom lane

Peter Eisentraut wrote:
> Jeremy Drake wrote:
>> I for one am greatly looking forward to tsearch2 being in core. I
>> was very fond of the plugin mechanism, until I signed up with a
>> hosting provider.
>
> Yes, you have told us about your hosting provider before. Just make
> sure your next hosting provider does not refuse to install database
> objects whose OID is a multiple of 13 because of bad luck, or you might
> miss out on full-text indexing again.

sure that ISP is a bit stupid(especially wrt plpgsql) - but tsearch2 in
the current version is actually imposing some additional(often
non-trivial) complexity for things like database restores and upgrades
so I can see an ISP wanting to avoid that altogether.
A fully integrated fulltext search could make that much easier(in a few
years when most distributions have picked up 8.3) and just telling
people they should switch their hosting ISP is not always an immediatly
workable solution (think contracts,migration costs,legacy apps).

Stefan

Andrew Dunstan wrote:
> Joshua D. Drake wrote:
>> Where on the website can I see what "plugins" are included with
>> PostgreSQL?

YES! That's IMHO a more fundamental problem. The specific
question about Text Search seems more like a symptom. While
I don't mind Text Search in core, it seems an even bigger deal
that it's hard to find information on extensions (whether
from contrib or from gborg or from external places like postgis).

A web page with a table easily visible on the
postgresql web site that had
Extension (i.e. tsearch2, postgis)
Project Maturity (i.e. alpha/beta/stable)
Compatability (i.e. extension 1.0 works with postgresql 8.2)
Description (i.e. "full text search")
URL
would be a partial fix.

> contrib is a horrible misnomer. Can we maybe bite the bullet and call it
> something else?

+1
How about "plugins" or "extensions" or "optional libraries".

Stefan Kaltenbrunner wrote:
> sure that ISP is a bit stupid(especially wrt plpgsql) - but tsearch2
> in the current version is actually imposing some additional(often
> non-trivial) complexity for things like database restores and
> upgrades so I can see an ISP wanting to avoid that altogether.

I have never used tsearch2 across an upgrade, so what exactly are those
problems and why would they be specific to tsearch2?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Stefan Kaltenbrunner wrote:
>> sure that ISP is a bit stupid(especially wrt plpgsql) - but tsearch2
>> in the current version is actually imposing some additional(often
>> non-trivial) complexity for things like database restores and
>> upgrades so I can see an ISP wanting to avoid that altogether.
>
> I have never used tsearch2 across an upgrade, so what exactly are those
> problems and why would they be specific to tsearch2?

Tsearch2 changes things occasionally from release to release which make
upgrades impossible with a standard pg_dump/pg_restore. I would have to
double check (because I always work around the problem now) but IIRC
there have been function call changes that are different from one
release to the next.

Sincerely,

Joshua D. Drake

>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Stefan Kaltenbrunner wrote:
> I think one can find arguments for both variants - one of the
> question might even be how other databases are doing that and if the
> proposed syntax is resembling one of those or not.

The closest I could find is Oracle Text, the full-text search for
Oracle. Browsing the documentation I see things like

exec ctx_ddl.create_preference('myjlexer','japanese_lexer');
exec ctx_ddl.add_stopword('globallist','the','French');

which look pretty similar to what a procedure-based interface to
tsearch2 could look like.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/

I wrote:
> The closest I could find is Oracle Text, the full-text search for
> Oracle.

Oh, and note that Oracle Text is an "extension" and not included in the
Oracle database product proper.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> I wrote:
>> The closest I could find is Oracle Text, the full-text search for
>> Oracle.
>
> Oh, and note that Oracle Text is an "extension" and not included in the
> Oracle database product proper.

Cool. Then we will have yet another reason to claim we are superior.

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Joshua D. Drake wrote:
> Peter Eisentraut wrote:
> > I wrote:
> >> The closest I could find is Oracle Text, the full-text search for
> >> Oracle.
> >
> > Oh, and note that Oracle Text is an "extension" and not included in the
> > Oracle database product proper.
>
> Cool. Then we will have yet another reason to claim we are superior.

It's probably separate just so they can charge extra for it ;-) In our
case it's going to be free either way.

In any case, I agree with Andrew that it would be pretty dumb to reject
a funded, already written patch. If people had a problem with
integrating tsearch2 in core they should have said so much earlier.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, 2007-01-24 at 18:38 -0300, Alvaro Herrera wrote:
> In any case, I agree with Andrew that it would be pretty dumb to reject
> a funded, already written patch.

Well, there are two separate issues: should we include tsearch2 in core,
and what syntax should it use? Changing the syntax would not require
rejecting the entire patch.

> If people had a problem with integrating tsearch2 in core they should
> have said so much earlier.

Peter, Tom and others raised essentially identical objections when this
design was initially proposed. For example:

http://archives.postgresql.org/pgsql-hackers/2006-11/msg00392.php
http://archives.postgresql.org/pgsql-hackers/2006-11/msg00405.php
http://archives.postgresql.org/pgsql-hackers/2006-11/msg00437.php
http://archives.postgresql.org/pgsql-hackers/2006-11/msg00397.php

Was a consensus reached in that thread? (I didn't see one, but perhaps
I've overlooked a mail.)

-Neil

Neil Conway wrote:
>> If people had a problem with integrating tsearch2 in core they should
>> have said so much earlier.
>>
>
> Peter, Tom and others raised essentially identical objections when this
> design was initially proposed. For example:
>
> http://archives.postgresql.org/pgsql-hackers/2006-11/msg00392.php
> http://archives.postgresql.org/pgsql-hackers/2006-11/msg00405.php
> http://archives.postgresql.org/pgsql-hackers/2006-11/msg00437.php
> http://archives.postgresql.org/pgsql-hackers/2006-11/msg00397.php
>
> Was a consensus reached in that thread? (I didn't see one, but perhaps
> I've overlooked a mail.)
>
>

IIRC Tom's main objection to the previous proposal was that it involved
large grammar changes, which I understand is not now proposed. The way I
read that thread was that there was no strenuous objection apart from
the grammar parts.

Certainly I think we can still argue about details, such as the
functional API.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> IIRC Tom's main objection to the previous proposal was that it involved
> large grammar changes, which I understand is not now proposed.

No, they're already in there --- the patch seems to have been written
according to that proposal despite the objections.

regards, tom lane

On Wed, Jan 24, 2007 at 09:38:06PM +0100, Stefan Kaltenbrunner wrote:
> sure that ISP is a bit stupid(especially wrt plpgsql) - but tsearch2 in
> the current version is actually imposing some additional(often
> non-trivial) complexity for things like database restores and upgrades
> so I can see an ISP wanting to avoid that altogether.

Something I've wondered about before is the concept of having installed
Modules in the system. Let's say for example that while compiling
postgres it compiled the modules in contrib also and installed them in
a modules directory.

Once installed there, unpriviledged users could say "INSTALL foo" and
it would install the module, even if they do not have the permissions
to create them themselves.

That way you don't clutter the catalogs with external projects, and
there is some indication from the postgres team of some trust in these
modules. After all, if the installation made it easy to use for users,
it must be safe, right?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>> IIRC Tom's main objection to the previous proposal was that it involved
>> large grammar changes, which I understand is not now proposed.
>>
>
> No, they're already in there --- the patch seems to have been written
> according to that proposal despite the objections.
>
>
>

Oh. ouch.

That seems strange given this query from Oleg back on 18 Nov:

> So, if we'll not touch grammar, are there any issues with tsearch2 in core ?

cheers

andrew

On Wed, 24 Jan 2007, Martijn van Oosterhout wrote:

> On Wed, Jan 24, 2007 at 09:38:06PM +0100, Stefan Kaltenbrunner wrote:
> > sure that ISP is a bit stupid(especially wrt plpgsql) - but tsearch2 in
> > the current version is actually imposing some additional(often
> > non-trivial) complexity for things like database restores and upgrades
> > so I can see an ISP wanting to avoid that altogether.
>
> Something I've wondered about before is the concept of having installed
> Modules in the system. Let's say for example that while compiling
> postgres it compiled the modules in contrib also and installed them in
> a modules directory.
>
> Once installed there, unpriviledged users could say "INSTALL foo" and
> it would install the module, even if they do not have the permissions
> to create them themselves.

That would be great, and also it would be great to be able to CREATE
LANGUAGE as a regular user for a trusted pl that is already
compiled/installed.

>
> That way you don't clutter the catalogs with external projects, and
> there is some indication from the postgres team of some trust in these
> modules. After all, if the installation made it easy to use for users,
> it must be safe, right?

Essentially, I think they are just pretty reluctant to run commands as a
superuser on behalf of a user...

--
It is better never to have been born. But who among us has such luck?
One in a million, perhaps.

On Wed, 24 Jan 2007, Jeremy Drake wrote:

> On Wed, 24 Jan 2007, Martijn van Oosterhout wrote:
>
> > Something I've wondered about before is the concept of having installed
> > Modules in the system. Let's say for example that while compiling
> > postgres it compiled the modules in contrib also and installed them in
> > a modules directory.
> >
> > Once installed there, unpriviledged users could say "INSTALL foo" and
> > it would install the module, even if they do not have the permissions
> > to create them themselves.
>
> That would be great, and also it would be great to be able to CREATE
> LANGUAGE as a regular user for a trusted pl that is already
> compiled/installed.

Something like the attached (simple) change to allow CREATE LANGUAGE by
unprivileged users for trusted languages already present in pg_pltemplate.
I'm not quite sure how one would go about doing the module thing, I think
that would be more complex. Something simple like allowing creation of C
language functions in libraries in $libdir would probably not be
sufficient, because an unprivileged user could create functions that have
the wrong paramters or return values and crash things pretty good that
way. Any ideas how this would work? Perhaps a sql script in
sharedir could be run by the backend as though by a superuser...

--
Ed Sullivan will be around as long as someone else has talent.
-- Fred Allen

Jeremy Drake <pgsql(at)jdrake(dot)com> writes:
> On Wed, 24 Jan 2007, Jeremy Drake wrote:
>> That would be great, and also it would be great to be able to CREATE
>> LANGUAGE as a regular user for a trusted pl that is already
>> compiled/installed.

> Something like the attached (simple) change to allow CREATE LANGUAGE by
> unprivileged users for trusted languages already present in pg_pltemplate.

If it were merely a matter of removing an error check I think we would
have done it already. However, pltemplate will have all the languages
in it whether the DBA wants to allow them to be used or not; so I'd say
that there really needs to be *some* sort of privilege check here.
What that is and how to implement it are the hard parts.

regards, tom lane

On Wed, 24 Jan 2007, Tom Lane wrote:

> Jeremy Drake <pgsql(at)jdrake(dot)com> writes:
> > On Wed, 24 Jan 2007, Jeremy Drake wrote:
> >> That would be great, and also it would be great to be able to CREATE
> >> LANGUAGE as a regular user for a trusted pl that is already
> >> compiled/installed.
>
> > Something like the attached (simple) change to allow CREATE LANGUAGE by
> > unprivileged users for trusted languages already present in pg_pltemplate.
>
> If it were merely a matter of removing an error check I think we would
> have done it already. However, pltemplate will have all the languages
> in it whether the DBA wants to allow them to be used or not; so I'd say
> that there really needs to be *some* sort of privilege check here.
> What that is and how to implement it are the hard parts.

So I guess it depends on what you mean by "DBA". Perhaps the database
owner? Or some new privilege type (GRANT CREATE ON LANGUAGE ...? Or GRANT
CREATE LANGUAGE ON DATABASE...?) that the db owner has by default?

--
7:30, Channel 5: The Bionic Dog (Action/Adventure)
The Bionic Dog drinks too much and kicks over the National
Redwood Forest.

Jeremy Drake <pgsql(at)jdrake(dot)com> writes:
> On Wed, 24 Jan 2007, Tom Lane wrote:
>> that there really needs to be *some* sort of privilege check here.
>> What that is and how to implement it are the hard parts.

> So I guess it depends on what you mean by "DBA". Perhaps the database
> owner? Or some new privilege type (GRANT CREATE ON LANGUAGE ...? Or GRANT
> CREATE LANGUAGE ON DATABASE...?) that the db owner has by default?

Not the DB owner. If you are worried about whether to allow use of PLs
it's almost certainly an installation-wide security concern, so I'd say
that the privilege has to flow from a superuser.

GRANT CREATE ON LANGUAGE feeding into a flag bit in pltemplate would
work, if restricted to superusers, but I suspect people would find this
confusing because it'd work completely differently from GRANT USAGE ON
LANGUAGE (eg, because the latter has only database-local effects).
Might be better to use a different syntax.

Note I'm not arguing against allowing it to be "on" by default, I just
want to be sure there is a way for paranoid DBAs to turn it off. Maybe
it'd be sufficient if the flag bit was there but "UPDATE pg_pltemplate"
was the only way to manipulate it --- we've gotten along with treating
datistemplate and datallowconn that way.

Or we could go the full nine yards and add ACLs to pltemplate, but
that's probably overkill.

regards, tom lane

On Wed, 24 Jan 2007, Tom Lane wrote:

> Not the DB owner. If you are worried about whether to allow use of PLs
> it's almost certainly an installation-wide security concern, so I'd say
> that the privilege has to flow from a superuser.
>
> GRANT CREATE ON LANGUAGE feeding into a flag bit in pltemplate would
> work, if restricted to superusers, but I suspect people would find this
> confusing because it'd work completely differently from GRANT USAGE ON
> LANGUAGE (eg, because the latter has only database-local effects).
> Might be better to use a different syntax.

I had thought that it would be database-local, but I understand now that
it makes more sense to be global.

>
> Note I'm not arguing against allowing it to be "on" by default, I just
> want to be sure there is a way for paranoid DBAs to turn it off. Maybe
> it'd be sufficient if the flag bit was there but "UPDATE pg_pltemplate"
> was the only way to manipulate it --- we've gotten along with treating
> datistemplate and datallowconn that way.

That sounds reasonable to me. I'll try to put together a patch like this
(adding a boolean column to pg_pltemplate) and see if this is acceptable.
I assume that only superusers can modify pg_pltemplate already ;)

> Or we could go the full nine yards and add ACLs to pltemplate, but
> that's probably overkill.

Agreed.

--
He thought he saw an albatross
That fluttered 'round the lamp.
He looked again and saw it was
A penny postage stamp.
"You'd best be getting home," he said,
"The nights are rather damp."

[ redirecting thread from -patches to -hackers for wider comment ]

Jeremy Drake <pgsql(at)jdrake(dot)com> writes:
> On Wed, 24 Jan 2007, Tom Lane wrote:
>> Note I'm not arguing against allowing it to be "on" by default, I just
>> want to be sure there is a way for paranoid DBAs to turn it off. Maybe
>> it'd be sufficient if the flag bit was there but "UPDATE pg_pltemplate"
>> was the only way to manipulate it --- we've gotten along with treating
>> datistemplate and datallowconn that way.

> That sounds reasonable to me. I'll try to put together a patch like this
> (adding a boolean column to pg_pltemplate) and see if this is acceptable.
> I assume that only superusers can modify pg_pltemplate already ;)

I had a further thought about this: if we allow random users to create
languages, then without any further tweaking the instance of the
language in their DB would be owned by them and they could grant or deny
USAGE on it to others in their DB. This is probably not good. Given
the current structure of pg_language, a language is effectively a
one-time-per-DB resource and so random users could obstruct others from
using a language.

Perhaps it'd make sense to limit this to the DB owner, who would then be
able to grant or deny language usage to the other users in his database.

In detail, it'd look something like:

* For an untrusted language: must be superuser to either create or use
the language (no change from current rules). Ownership of the
pg_language entry is really irrelevant, as is its ACL.

* For a trusted language:

* if pg_pltemplate.something is ON: either a superuser or the current
DB's owner can CREATE the language. In either case the pg_language
entry will be marked as owned by the DB owner (pg_database.datdba),
which means that subsequently he (or a superuser) can grant or deny
USAGE within his DB.

* if pg_pltemplate.something is OFF: must be superuser to CREATE the
language; subsequently it will be owned by you, so only you or another
superuser can grant or deny USAGE (same behavior as currently).

Comments? The bit about assigning the datdba as the owner might seem
a bit odd, but I'm worried about the case where someone has the DBA
privilege as a role but issues the create under his own ID. If it's
owned directly by him, you'd end up in a situation where other holders
of the DBA role couldn't manipulate the language, which seems
undesirable.

regards, tom lane

On Wed, 24 Jan 2007, Tom Lane wrote:

> [ redirecting thread from -patches to -hackers for wider comment ]
>
> Jeremy Drake <pgsql(at)jdrake(dot)com> writes:
> > On Wed, 24 Jan 2007, Tom Lane wrote:
> >> Note I'm not arguing against allowing it to be "on" by default, I just
> >> want to be sure there is a way for paranoid DBAs to turn it off. Maybe
> >> it'd be sufficient if the flag bit was there but "UPDATE pg_pltemplate"
> >> was the only way to manipulate it --- we've gotten along with treating
> >> datistemplate and datallowconn that way.
>
> > That sounds reasonable to me. I'll try to put together a patch like this
> > (adding a boolean column to pg_pltemplate) and see if this is acceptable.
> > I assume that only superusers can modify pg_pltemplate already ;)
>
> I had a further thought about this: if we allow random users to create
> languages, then without any further tweaking the instance of the
> language in their DB would be owned by them and they could grant or deny
> USAGE on it to others in their DB. This is probably not good. Given
> the current structure of pg_language, a language is effectively a
> one-time-per-DB resource and so random users could obstruct others from
> using a language.
>
> Perhaps it'd make sense to limit this to the DB owner, who would then be
> able to grant or deny language usage to the other users in his database.
>

I am digging through the code looking at this, and I have a question. As
far as I can tell, there is currently no owner for a pg_language entry.
Is this correct or is ownership information stored somewhere other than
the pg_language relation? Are you suggesting that a lanowner column would
need to be added?

As far as the column name referred to below as "pg_pltemplate.something",
for now I am calling it tmpldbaallowed. I am not particularly attached to
nor fond of that name, however, and am open to naming suggestions.

> In detail, it'd look something like:
>
> * For an untrusted language: must be superuser to either create or use
> the language (no change from current rules). Ownership of the
> pg_language entry is really irrelevant, as is its ACL.
>
> * For a trusted language:
>
> * if pg_pltemplate.something is ON: either a superuser or the current
> DB's owner can CREATE the language. In either case the pg_language
> entry will be marked as owned by the DB owner (pg_database.datdba),
> which means that subsequently he (or a superuser) can grant or deny
> USAGE within his DB.
>
> * if pg_pltemplate.something is OFF: must be superuser to CREATE the
> language; subsequently it will be owned by you, so only you or another
> superuser can grant or deny USAGE (same behavior as currently).
>
> Comments? The bit about assigning the datdba as the owner might seem
> a bit odd, but I'm worried about the case where someone has the DBA
> privilege as a role but issues the create under his own ID. If it's
> owned directly by him, you'd end up in a situation where other holders
> of the DBA role couldn't manipulate the language, which seems
> undesirable.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
>

--
Save the Whales -- Harpoon a Honda.

On Wed, 24 Jan 2007, Jeremy Drake wrote:

> I am digging through the code looking at this, and I have a question. As
> far as I can tell, there is currently no owner for a pg_language entry.
> Is this correct or is ownership information stored somewhere other than
> the pg_language relation? Are you suggesting that a lanowner column would
> need to be added?
>

Sort of answered my own question, found this comment:
* Note: for now, languages are treated as owned by the bootstrap
* user. We should add an owner column to pg_language instead.

So in the course of implementing this, an owner column would probably need
to be added to pg_language, I guess.

--
If a 6600 used paper tape instead of core memory, it would use up tape
at about 30 miles/second.
-- Grishman, Assembly Language Programming

Jeremy Drake <pgsql(at)jdrake(dot)com> writes:
>> I am digging through the code looking at this, and I have a question. As
>> far as I can tell, there is currently no owner for a pg_language entry.

Er, doh.

> Sort of answered my own question, found this comment:
> * Note: for now, languages are treated as owned by the bootstrap
> * user. We should add an owner column to pg_language instead.

> So in the course of implementing this, an owner column would probably need
> to be added to pg_language, I guess.

If you believe my idea that the DB owner ought to have special privilege
in this regard, then probably yes. Alternatively, we could hard-wire
the treatment of the DB owner.

regards, tom lane

On Wed, 24 Jan 2007, Tom Lane wrote:

> In detail, it'd look something like:
>
> * For an untrusted language: must be superuser to either create or use
> the language (no change from current rules). Ownership of the
> pg_language entry is really irrelevant, as is its ACL.
>
> * For a trusted language:
>
> * if pg_pltemplate.something is ON: either a superuser or the current
> DB's owner can CREATE the language. In either case the pg_language
> entry will be marked as owned by the DB owner (pg_database.datdba),
> which means that subsequently he (or a superuser) can grant or deny
> USAGE within his DB.
>
> * if pg_pltemplate.something is OFF: must be superuser to CREATE the
> language; subsequently it will be owned by you, so only you or another
> superuser can grant or deny USAGE (same behavior as currently).

I think I have what is described here implemented in this patch, so that
it can be better understood. Thoughts?

--
Nobody said computers were going to be polite.

Hi there,

sorry, if I will a bit verbose - just tried to answer to several postings.

On Wed, 24 Jan 2007, Tom Lane wrote:

>> Teodor Sigaev wrote:
>>> If there aren't objections then we plan commit patch tomorrow or
>>> after tomorrow.
>
> This is a fairly large patch and I would like the chance to review it
> before it goes in --- "we'll commit tomorrow" is not exactly a decent
> review window.
>

I see your argument, no problem with that. We intentionally announced
its availability several weeks ago.

> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> I still haven't heard any argument for why this would be necessary or
>> desirable at all, other than that it looks better for marketing
>> reasons,
>
> One possible argument for this over the contrib version is a saner
> approach to dumping and restoring configurations. However, as against
> that:
>
> 1) what's the upgrade path for getting an existing tsearch2
> configuration into this implementation?

this is a real question and we will prepare UPGRADE notes.

>
> 2) once we put this in core we are going to be stuck with supporting its
> SQL API forever. Are we convinced that this API is the one we want?
> I don't recall even having seen any proposal or discussion. It was OK
> for tsearch2's API to change every release while it was in contrib, but
> the expectation of stability is a whole lot higher for core features.

If you're talking about SQL and psql commands, than they are new and we tried
to be consistent with existing approach to manage system objects.
Any inconsistence we'd be happy to discuss and improve.

I don't remember we changed operators and function for a long
time, so users of tsearch2 should not be confused.

After all, our intention is to meet user's wish to have FTS in PostgreSQL and
nothing more. We several times wrote in mailing list that it's too early
to move tsearch2 to the pg core, since we consider (that time) it has some
scalability problem. GiN was specially developed to solve this problem and
it did it.

It's de facto standard to have FTS in modern database and
it has no difference how you call it - plugin, extension, contrib module or
built-in.

It's infair to compare approach of commercial DB with postgres, since
they have their own marketing police - they charge separately for every
extension ! Our usual peer - MySQL has built-in FTS, for example, and
I don't see any objections to not have an additional argument for our
PR people, since our FTS is a way better.

I agree, that requirements for core features should be stronger
that for contrib module, especially, for the stability of API. So, let us
discuss it. We are open for suggestions for about 6 years :)
I
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On Wed, 24 Jan 2007, Neil Conway wrote:

> On Wed, 2007-01-24 at 13:49 -0500, Tom Lane wrote:
>> 2) once we put this in core we are going to be stuck with supporting its
>> SQL API forever. Are we convinced that this API is the one we want?
>> I don't recall even having seen any proposal or discussion.
>
> There has been some prior discussion:
>
> http://archives.postgresql.org/pgsql-hackers/2006-12/msg00919.php
>
> But I agree that we need considerably more discussion before committing
> the patch. I'm personally not sold on the need for modifications to the
> SQL grammar, for example, as opposed to just using a set of SQL-callable
> functions and some new system catalogs.
>
> Another question that would be easier to resolve before the patch is
> committed is naming: the patch currently uses a mix of "full text" and
> "tsearch[2]" as the name of the full-text search feature. If we're going
> to bless this as "the" integrated full-text search in PG, it might make
> more sense to use "full text search" and "FTS" exclusively.

We tried to use full-text search (FTS) in the documentation
http://mira.sai.msu.su/~megera/pgsql/ftsdoc/index.html. Tsearch[2] used just
for historical notes, which may not go to the official documentation.

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

On Wed, 24 Jan 2007 22:27:10 +0100, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> I wrote:
>> The closest I could find is Oracle Text, the full-text search for
>> Oracle.
>
> Oh, and note that Oracle Text is an "extension" and not included in the
> Oracle database product proper.
>

Same with DB2 NSE, IBM's fulltext search engine for their UDB. However, they employ external
admin tools like db2text to create, configure and alter fulltext indexes (like slonik
for example). Textsearch could be done with functions (contains()) in SQL.

Bernd

On 1/24/07, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Peter Eisentraut wrote:
> >> contrib is a horrible misnomer. Can we maybe bite the bullet and call
> >> it something else?
> > plugins?

How about 'modules' or 'extras' or 'extensions'? :)

> standard-plugins might be more informative. I think of them as being
> like perl's standard modules, things that are part of the standard perl
> distribution as opposed to all the other stuff on CPAN.

Personally, I don't quite like 'plugins'. it may be that when I think of
plugins, I think of 'GIMP plugins'. ;) And I think hosting providers
would exclude plugins almost as often as they do with contrib.
"They are not 'core' so it's safe to exclude them"

Same with 'extras' or 'extensions' -- they seem to imply that you
can do without them.

This is the reason I like 'modules' best. It makes one think that it is
something maybe part of core, maybe not, but it has been isolated
into separate entity for maintenance reasons.

My EUR 0.02

Regards,
Dawid

Dawid Kuroczko wrote:
> This is the reason I like 'modules' best. It makes one think that it
> is something maybe part of core, maybe not, but it has been isolated
> into separate entity for maintenance reasons.

On etymological grounds, "modules" would also be my favorite, but the
term "module" is already used in the SQL standard for something
different.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

> This is a fairly large patch and I would like the chance to review it
> before it goes in --- "we'll commit tomorrow" is not exactly a decent
> review window.
Not a problem.

> One possible argument for this over the contrib version is a saner
> approach to dumping and restoring configurations. However, as against
> that:
>
> 1) what's the upgrade path for getting an existing tsearch2
> configuration into this implementation?

It's should clear enough for now - dump data from old db and load into new one.
But dump should be without any contrib/tsearch2 related functions.

>
> 2) once we put this in core we are going to be stuck with supporting its
> SQL API forever. Are we convinced that this API is the one we want?
> I don't recall even having seen any proposal or discussion. It was OK
> for tsearch2's API to change every release while it was in contrib, but
> the expectation of stability is a whole lot higher for core features.

Basic tsearch2 SQL API doesn't changed since its first release, just extended.
As I can see, there isn't any standard of fulltext search in SQL. DB/2, MS SQL,
Oracle and MySQL use different SQL API. I don't know which better. I remember
only one suggestion: 'CREATE FULLTEXT INDEX ...'. So, I believe, existing SQL
API satisfies users. But it possible to emulate on grammar level subset of MySQL
syntax:
SQL commands
CREATE FULLTEXT INDEX idxname ON tbl [ USING {GIN|GIST} ] ( field1[, [...]] );
SELECT .. FROM table WHERE MATCH( field1[, [...]] ) AGAINST ( txt );

will be translated to
CREATE INDEX idxname ON tbl [ USING {GIN|GIST} ] ( to_tsquery(field1)[ || [...]] );
SELECT .. FROM table WHERE ( to_tsquery(field1)[ || [...]] ) @@ plainto_tsquery(
txt );

Notes
1 that is full equivalent MySQL's MATCH() AGAINST (txt IN BOOLEAN MODE)
2 it requires to keyword MATCH & AGAINST which cannot be a function's name
without quoting.

Internal API changed sometimes (not every release), but I don't see a problem
here: all other internal API's in postgres are often changed.

--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/

> the patch. I'm personally not sold on the need for modifications to the
> SQL grammar, for example, as opposed to just using a set of SQL-callable
> functions and some new system catalogs.

SQL grammar isn't changed significantly - just add variants of CREATE/DROP/ALTER
/COMMENTS commands. Next, functions haven't autocomplete feature or built-in
quick help - if you don't remember exactly kind/type of argument(s) of function
then you should read a docs.

--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/

Teodor Sigaev wrote:
>> the patch. I'm personally not sold on the need for modifications to the
>> SQL grammar, for example, as opposed to just using a set of SQL-callable
>> functions and some new system catalogs.
>
> SQL grammar isn't changed significantly - just add variants of
> CREATE/DROP/ALTER /COMMENTS commands. Next, functions haven't
> autocomplete feature or built-in quick help - if you don't remember
> exactly kind/type of argument(s) of function then you should read a docs.

I didn't read the patch but I did skim the docs for this and if the docs
are current I see things like this:

CREATE FULLTEXT DICTIONARY en_ispell
( OPT = 'DictFile="ispell/english.dict",
AffFile="ispell/english.aff",
StopFile="english.stop"'
) LIKE ispell_template;

ALTER FULLTEXT DICTIONARY en_stem SET OPT='english.stop';

Which to me is perfectly reasonable and intuitive. It is unfortunate
though that we still have the more odd grammar of actually using Tsearch
to query. Although I don't really have a better suggestion without
adding some ungodly obscure operator.

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On 1/25/07, Teodor Sigaev <teodor(at)sigaev(dot)ru> wrote:
> It's should clear enough for now - dump data from old db and load into new one.
> But dump should be without any contrib/tsearch2 related functions.

Upgrading from 8.1.x to 8.2.x was not tivial because of very trivial
change in API (actually not really API but the content of "pg_ts_*"
tables): russian snowball stemming function was forked to 2 different
ones, for koi8 and utf8 encodings. So, as I dumped my pg_ts_* tables
data (to keep my tsearch2 settings), I saw errors during restoration
(btw, why didn't you keep old russian stemmer function name as a
synonym to koi8 variant?) -- so, I had to change my dump file
manually, because I didn't manage to follow "tsearch2 best practices"
(to use some kind of "bootstrap" script that creates tsearch2
configuration you need from default one -- using several INSERTs and
UPDATEs). And there were no upgrade notes for tsearch2.

So, I consider upgrading process for tsearch2 to be a little bit
tricky till present. I assume it will be improved with 8.3...

--
Best regards,
Nikolay

> though that we still have the more odd grammar of actually using Tsearch
> to query. Although I don't really have a better suggestion without
> adding some ungodly obscure operator.

IMHO, best possible solution is 'WHERE table.text_field @ text'.
Operator @ internally makes equivalent of 'to_tsvector(table.text_field) @@
plainto_tsquery(text)', it's also possible to add GIN/GIST opclasses to speedup
search queries. Performance of making headline in this case will be decreased
insignificant, but ranking time will be disastrous. Because of reparsing of
whole found texts. GIST performance may be decreased too - GIST indexing of
tsvector is lossy.

--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/

On Thu, 25 Jan 2007, Nikolay Samokhvalov wrote:

> On 1/25/07, Teodor Sigaev <teodor(at)sigaev(dot)ru> wrote:
>> It's should clear enough for now - dump data from old db and load into new
>> one.
>> But dump should be without any contrib/tsearch2 related functions.
>
> Upgrading from 8.1.x to 8.2.x was not tivial because of very trivial
> change in API (actually not really API but the content of "pg_ts_*"
> tables): russian snowball stemming function was forked to 2 different
> ones, for koi8 and utf8 encodings. So, as I dumped my pg_ts_* tables
> data (to keep my tsearch2 settings), I saw errors during restoration
> (btw, why didn't you keep old russian stemmer function name as a
> synonym to koi8 variant?) -- so, I had to change my dump file
> manually, because I didn't manage to follow "tsearch2 best practices"

sed and grep did the trick.

> (to use some kind of "bootstrap" script that creates tsearch2
> configuration you need from default one -- using several INSERTs and
> UPDATEs). And there were no upgrade notes for tsearch2.

This is unfair, you promised to write upgrade notes and we discussed the
problem with name change before release and I rely on you. It was my fault,
of course.

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

On Wed, 24 Jan 2007, Jeremy Drake wrote:

> On Wed, 24 Jan 2007, Tom Lane wrote:
>
> > In detail, it'd look something like:
> >
> > * For an untrusted language: must be superuser to either create or use
> > the language (no change from current rules). Ownership of the
> > pg_language entry is really irrelevant, as is its ACL.
> >
> > * For a trusted language:
> >
> > * if pg_pltemplate.something is ON: either a superuser or the current
> > DB's owner can CREATE the language. In either case the pg_language
> > entry will be marked as owned by the DB owner (pg_database.datdba),
> > which means that subsequently he (or a superuser) can grant or deny
> > USAGE within his DB.
> >
> > * if pg_pltemplate.something is OFF: must be superuser to CREATE the
> > language; subsequently it will be owned by you, so only you or another
> > superuser can grant or deny USAGE (same behavior as currently).
>
> I think I have what is described here implemented in this patch, so that
> it can be better understood. Thoughts?

This version of the patch creates a shared dependency on the language
owner.

I have thought of some other questions about the owner stuff which I will
send on -hackers...

--
Afternoon, n.:
That part of the day we spend worrying about how we wasted the
morning.

On Wed, 24 Jan 2007, Tom Lane wrote:

> * For an untrusted language: must be superuser to either create or use
> the language (no change from current rules). Ownership of the
> pg_language entry is really irrelevant, as is its ACL.
>
> * For a trusted language:
>
> * if pg_pltemplate.something is ON: either a superuser or the current
> DB's owner can CREATE the language. In either case the pg_language
> entry will be marked as owned by the DB owner (pg_database.datdba),
> which means that subsequently he (or a superuser) can grant or deny
> USAGE within his DB.

What happens on ALTER DATABASE ALTER OWNER? Does the ownership of the
language change to the new datdba or stay the old one?

If the CREATE LANGUAGE results in creating the handler and validation
funcs, who should own them? At the moment it is the user doing the CREATE
LANGUAGE, but what does that mean? Can they then do odd things to the
permissions of the procs, such as denying execute on them, to break other
user's usage of the language, or does the perms on a language pre-empt the
perms on the func?

What happens if pg_pltemplate.something changes after the language is
created? The datdba would continue to own the language, and can change
permissions and drop it, but could not recreate it. I assume if the
superuser wanted to revoke the ability for database owners to create that
language they would remove it from people's databases who already have it.

>
> * if pg_pltemplate.something is OFF: must be superuser to CREATE the
> language; subsequently it will be owned by you, so only you or another
> superuser can grant or deny USAGE (same behavior as currently).

What if pg_pltemplate.something is OFF, the language is CREATEd by a
superuser, and then pg_pltemplate is set ON? The language is now owned by
a superuser, so the db owner could not manipulate it.

The patch I put together adds an owner to pg_language. Should there be an
ALTER LANGUAGE OWNER TO command added as well. Thinking about these
conditions I have described here, it seems to me there should be.

Or there could not be an owner for a language and who the owner is depends
on the conditions listed. But then permissions checks for languages
would depend on pg_pltemplate, which seems less than clear or ideal to me.
Besides which, when the acl is initalized from NULL to a value, it depends
on who the owner is. It would need to be changed as well when the owner
changing conditions change.

I think that an ALTER LANGUAGE OWNER TO is the proper response to these
things, and unless I hear otherwise I will attempt to add this to my
patch.

--
Checkuary, n.:
The thirteenth month of the year. Begins New Year's Day and ends
when a person stops absentmindedly writing the old year on his checks.

On Thu, 25 Jan 2007, Jeremy Drake wrote:

> I think that an ALTER LANGUAGE OWNER TO is the proper response to these
> things, and unless I hear otherwise I will attempt to add this to my
> patch.

Here is the patch which adds this. It also allows ALTER LANGUAGE RENAME
TO for the owner, which I missed before. I would appreciate someone with
more knowledge of the permissions infrastructure to take a look at it
since I am fairly new to it and may not fully understand its intricacies.

--
The makers may make
And the users may use,
But the fixers must fix
With but minimal clues

Andrew Dunstan wrote:
>> I am constantly running into this:
>>
>> Q. Does PostgreSQL have full text indexing?
>> A. Yes it is in contrib.
>> Q. But that isn't part of core.
>> A. *sigh*
>>
>> Where on the website can I see what "plugins" are included with
>> PostgreSQL?
>>
>> Where on the website can I see the Official PostgreSQL Documentation for
>> Full Text Indexing?
>>
>> With TSearch2 in core will that fix the many upgrade problems associated
>> with using TSearch2?
>>
>>
>>
>
> contrib is a horrible misnomer. Can we maybe bite the bullet and call
> it something else?
After years of PG use, I am still afraid to use contrib modules because
it just *feels* like voodoo. I have spent much time reading this mailing
list and on IRC with PG users, and I know that contrib modules are on
the whole tested and safe, but the lack of web documentation and any
indication of what they do other than "check the notes that come with
the source" makes me just feel like they are "use and cross fingers"
type thing.

I don't know how hard it would be to implement, but perhaps contrib
modules could be compiled in a similar way to Apache modules. E.g.,
./configure --with-modulename with the onus for packaging them
appropriately falling onto the shoulders of the module authors. I feel
that even a basic module management system like this would greatly
increase awareness of and confidence in the contrib modules. Oh, and
+1 on renaming contrib
+1 on the need for a comprehensive list of them
+1 on the need for more doc on the website about each of them, onus
falling on module authors, perhaps require at least a basic doc patch as
a requirement for /contrib inclusion.

- Naz

On Thu, 25 Jan 2007, Jeremy Drake wrote:

> On Thu, 25 Jan 2007, Jeremy Drake wrote:
>
> > I think that an ALTER LANGUAGE OWNER TO is the proper response to these
> > things, and unless I hear otherwise I will attempt to add this to my
> > patch.
>
> Here is the patch which adds this. It also allows ALTER LANGUAGE RENAME
> TO for the owner, which I missed before. I would appreciate someone with
> more knowledge of the permissions infrastructure to take a look at it
> since I am fairly new to it and may not fully understand its intricacies.
>

I have refactored the owner checking of languages in the same manner as it
is for other owned objects. I have changed to using standard permissions
error messages (aclcheck_error) for the language permissions errors.

I consider this patch ready for review, assuming the permissions rules
outlined by Tom Lane on -hackers are valid. For reference, here are the
rules that this patch is intended to implement:

On Wed, 24 Jan 2007, Tom Lane wrote:

> In detail, it'd look something like:
>
> * For an untrusted language: must be superuser to either create or use
> the language (no change from current rules). Ownership of the
> pg_language entry is really irrelevant, as is its ACL.
>
> * For a trusted language:
>
> * if pg_pltemplate.something is ON: either a superuser or the current
> DB's owner can CREATE the language. In either case the pg_language
> entry will be marked as owned by the DB owner (pg_database.datdba),
> which means that subsequently he (or a superuser) can grant or deny
> USAGE within his DB.
>
> * if pg_pltemplate.something is OFF: must be superuser to CREATE the
> language; subsequently it will be owned by you, so only you or another
> superuser can grant or deny USAGE (same behavior as currently).

The only difference from this is, that when superuser is required, the
owner of the language is not the superuser who created it, but
BOOTSTRAP_SUPERUSERID. This is because my interpretation was that the
"same behavior as currently" took precedence. The current behavior in cvs
is that languages have no owner, and for purposes where one would be
needed it is assumed to be BOOTSTRAP_SUPERUSERID.

Is this valid, or should I instead set the owner to GetUserId() in those
cases?

--
Academic politics is the most vicious and bitter form of politics,
because the stakes are so low.
-- Wallace Sayre

Jeremy Drake <pgsql(at)jdrake(dot)com> writes:
> The only difference from this is, that when superuser is required, the
> owner of the language is not the superuser who created it, but
> BOOTSTRAP_SUPERUSERID. This is because my interpretation was that the
> "same behavior as currently" took precedence. The current behavior in cvs
> is that languages have no owner, and for purposes where one would be
> needed it is assumed to be BOOTSTRAP_SUPERUSERID.

> Is this valid, or should I instead set the owner to GetUserId() in those
> cases?

I'd go with GetUserId() in the cases where you're not explicitly
assigning ownership to the datdba role. AFAIR the assumption that
languages are owned by BOOTSTRAP_SUPERUSERID was just a kluge to use in
some bits of code that had to have a notion of a specific owner. Now
in reality every superuser has the same privileges as every other one,
and so it doesn't matter much which one you use, but we might as well
record who actually did the deed.

regards, tom lane

On Sat, 27 Jan 2007, Tom Lane wrote:

> I'd go with GetUserId() in the cases where you're not explicitly
> assigning ownership to the datdba role. AFAIR the assumption that
> languages are owned by BOOTSTRAP_SUPERUSERID was just a kluge to use in
> some bits of code that had to have a notion of a specific owner. Now
> in reality every superuser has the same privileges as every other one,
> and so it doesn't matter much which one you use, but we might as well
> record who actually did the deed.

Here is a version of the patch which does this. Very minor change from
the last version...

--
Begathon, n.:
A multi-day event on public television, used to raise money so
you won't have to watch commercials.

On Fri, 26 Jan 2007, Jeremy Drake wrote:

> On Sat, 27 Jan 2007, Tom Lane wrote:
>
> > I'd go with GetUserId() in the cases where you're not explicitly
> > assigning ownership to the datdba role. AFAIR the assumption that
> > languages are owned by BOOTSTRAP_SUPERUSERID was just a kluge to use in
> > some bits of code that had to have a notion of a specific owner. Now
> > in reality every superuser has the same privileges as every other one,
> > and so it doesn't matter much which one you use, but we might as well
> > record who actually did the deed.
>
> Here is a version of the patch which does this. Very minor change from
> the last version...

Oops, was missing an include which resulted in an implicitly defined
function warning. This version fixes it...

--
Anybody who doesn't cut his speed at the sight of a police car is
probably parked.

This version of the patch includes documentation changes. Please
review...

--
The more things change, the more they stay insane.

The most recent version of this patch has been added.

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Jeremy Drake wrote:
> On Thu, 25 Jan 2007, Jeremy Drake wrote:
>
> > On Thu, 25 Jan 2007, Jeremy Drake wrote:
> >
> > > I think that an ALTER LANGUAGE OWNER TO is the proper response to these
> > > things, and unless I hear otherwise I will attempt to add this to my
> > > patch.
> >
> > Here is the patch which adds this. It also allows ALTER LANGUAGE RENAME
> > TO for the owner, which I missed before. I would appreciate someone with
> > more knowledge of the permissions infrastructure to take a look at it
> > since I am fairly new to it and may not fully understand its intricacies.
> >
>
> I have refactored the owner checking of languages in the same manner as it
> is for other owned objects. I have changed to using standard permissions
> error messages (aclcheck_error) for the language permissions errors.
>
> I consider this patch ready for review, assuming the permissions rules
> outlined by Tom Lane on -hackers are valid. For reference, here are the
> rules that this patch is intended to implement:
>
> On Wed, 24 Jan 2007, Tom Lane wrote:
>
> > In detail, it'd look something like:
> >
> > * For an untrusted language: must be superuser to either create or use
> > the language (no change from current rules). Ownership of the
> > pg_language entry is really irrelevant, as is its ACL.
> >
> > * For a trusted language:
> >
> > * if pg_pltemplate.something is ON: either a superuser or the current
> > DB's owner can CREATE the language. In either case the pg_language
> > entry will be marked as owned by the DB owner (pg_database.datdba),
> > which means that subsequently he (or a superuser) can grant or deny
> > USAGE within his DB.
> >
> > * if pg_pltemplate.something is OFF: must be superuser to CREATE the
> > language; subsequently it will be owned by you, so only you or another
> > superuser can grant or deny USAGE (same behavior as currently).
>
> The only difference from this is, that when superuser is required, the
> owner of the language is not the superuser who created it, but
> BOOTSTRAP_SUPERUSERID. This is because my interpretation was that the
> "same behavior as currently" took precedence. The current behavior in cvs
> is that languages have no owner, and for purposes where one would be
> needed it is assumed to be BOOTSTRAP_SUPERUSERID.
>
> Is this valid, or should I instead set the owner to GetUserId() in those
> cases?
>
>
> --
> Academic politics is the most vicious and bitter form of politics,
> because the stakes are so low.
> -- Wallace Sayre
Content-Description:

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Teodor Sigaev wrote:
> We (Oleg and me) are glad to present tsearch in core of pgsql patch. In basic,
> layout, functions, methods, types etc are the same as in current tsearch2 with a
> lot of improvements:
>
> - pg_ts_* tables now are in pg_catalog
> - parsers, dictionaries, configurations now have owner and namespace similar to
> other pgsql's objects like tables, operator classes etc
> - current tsearch configuration is managed with a help of GUC variable
> tsearch_conf_name.
> - choosing of tsearch cfg by locale may be done for each schema separately
> - managing of tsearch configuration with a help of SQL commands, not with
> insert/update/delete statements. This allows to drive dependencies,
> correct dumping and dropping.
> - psql support with a help of \dF* commands
> - add all available Snowball stemmers and corresponding configuration
> - correct memory freeing by any dictionary
>
> Work is sponsored by EnterpriseDB's PostgreSQL Development Fund.
>
> patch: http://www.sigaev.ru/misc/tsearch_core-0.33.gz
> docs: http://mira.sai.msu.su/~megera/pgsql/ftsdoc/ (not yet completed and it's
> not yet a patch, just a SGML source)
>
> Implementation details:
> - directory layout
> src/backend/utils/adt/tsearch - all IO function and simple operations
> src/backend/utils/tsearch - complex processing functions, including
> language processing and dictionaries
> - most of snowball dictionaries are placed in separate .so library and
> they plug in into data base by similar way as character conversation
> library does.
>
> If there aren't objections then we plan commit patch tomorrow or after tomorrow.
> Before committing, I'll changes oids from 5000+ to lower values to prevent holes
> in oids. And after that, I'll remove tsearch2 contrib module.
>
> --
> Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
> WWW: http://www.sigaev.ru/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

FYI, I added this to the patches queue because I think we decided
full-text indexing should be in the core. If I am wrong, please let me
know.

---------------------------------------------------------------------------

Teodor Sigaev wrote:
> We (Oleg and me) are glad to present tsearch in core of pgsql patch. In basic,
> layout, functions, methods, types etc are the same as in current tsearch2 with a
> lot of improvements:
>
> - pg_ts_* tables now are in pg_catalog
> - parsers, dictionaries, configurations now have owner and namespace similar to
> other pgsql's objects like tables, operator classes etc
> - current tsearch configuration is managed with a help of GUC variable
> tsearch_conf_name.
> - choosing of tsearch cfg by locale may be done for each schema separately
> - managing of tsearch configuration with a help of SQL commands, not with
> insert/update/delete statements. This allows to drive dependencies,
> correct dumping and dropping.
> - psql support with a help of \dF* commands
> - add all available Snowball stemmers and corresponding configuration
> - correct memory freeing by any dictionary
>
> Work is sponsored by EnterpriseDB's PostgreSQL Development Fund.
>
> patch: http://www.sigaev.ru/misc/tsearch_core-0.33.gz
> docs: http://mira.sai.msu.su/~megera/pgsql/ftsdoc/ (not yet completed and it's
> not yet a patch, just a SGML source)
>
> Implementation details:
> - directory layout
> src/backend/utils/adt/tsearch - all IO function and simple operations
> src/backend/utils/tsearch - complex processing functions, including
> language processing and dictionaries
> - most of snowball dictionaries are placed in separate .so library and
> they plug in into data base by similar way as character conversation
> library does.
>
> If there aren't objections then we plan commit patch tomorrow or after tomorrow.
> Before committing, I'll changes oids from 5000+ to lower values to prevent holes
> in oids. And after that, I'll remove tsearch2 contrib module.
>
> --
> Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
> WWW: http://www.sigaev.ru/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
>
> FYI, I added this to the patches queue because I think we decided
> full-text indexing should be in the core. If I am wrong, please let me
> know.

One of the objections I remember to this particular implementation was
that configuration should be done using functions rather than new syntax
in gram.y. This seems a good idea because it avoids bloating the
grammar, while still allowing dependency tracking, pg_dump support,
syscache support etc.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, 20 Feb 2007, Alvaro Herrera wrote:

> Bruce Momjian wrote:
>>
>> FYI, I added this to the patches queue because I think we decided
>> full-text indexing should be in the core. If I am wrong, please let me
>> know.
>
> One of the objections I remember to this particular implementation was
> that configuration should be done using functions rather than new syntax
> in gram.y. This seems a good idea because it avoids bloating the
> grammar, while still allowing dependency tracking, pg_dump support,
> syscache support etc.

It's not so big addition to the gram.y, see a list of commands
http://mira.sai.msu.su/~megera/pgsql/ftsdoc/sql-commands.html.
SQL commands make FTS syntax clear and follow tradition to manage
system objects. From the user's side, I'd be very unhappy to configure
FTS, which can be very complex, using functions. All we want is to
provide users clear syntax.

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

Oleg Bartunov wrote:
> On Tue, 20 Feb 2007, Alvaro Herrera wrote:
>
> > Bruce Momjian wrote:
> >>
> >> FYI, I added this to the patches queue because I think we decided
> >> full-text indexing should be in the core. If I am wrong, please let me
> >> know.
> >
> > One of the objections I remember to this particular implementation was
> > that configuration should be done using functions rather than new syntax
> > in gram.y. This seems a good idea because it avoids bloating the
> > grammar, while still allowing dependency tracking, pg_dump support,
> > syscache support etc.
>
> It's not so big addition to the gram.y, see a list of commands
> http://mira.sai.msu.su/~megera/pgsql/ftsdoc/sql-commands.html.
> SQL commands make FTS syntax clear and follow tradition to manage
> system objects. From the user's side, I'd be very unhappy to configure
> FTS, which can be very complex, using functions. All we want is to
> provide users clear syntax.

I looked at the diff file and the major change in gram.y is the creation
of a new object type FULLTEXT, so you can CREATE, ALTER and DROP
FULLTEXT.

I don't know fulltext administration well enough, so if Oleg says a
function API would be too complex, I am OK with his new parser syntax.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Tue, 20 Feb 2007, Bruce Momjian wrote:

>
> The most recent version of this patch has been added.
>
> Your patch has been added to the PostgreSQL unapplied patches list at:
>
> http://momjian.postgresql.org/cgi-bin/pgpatches
>
> It will be applied as soon as one of the PostgreSQL committers reviews
> and approves it.

Cool, I was going to bring this up again once the regexp patch got in.
There is one thing in this patch I was not sure on, and that is in
AlterLanguageOwner what should the second parameter of heap_close be? I
have RowExclusiveLock in the patch, but I am not sure that is correct.
It would be good if someone more knowledgeable about such things checked
on this when applying it...

The latest version of the patch is currently at
http://momjian.us/mhonarc/patches/msg00014.html

> ---------------------------------------------------------------------------
>
>
> Jeremy Drake wrote:
> > On Thu, 25 Jan 2007, Jeremy Drake wrote:
> >
> > > On Thu, 25 Jan 2007, Jeremy Drake wrote:
> > >
> > > > I think that an ALTER LANGUAGE OWNER TO is the proper response to these
> > > > things, and unless I hear otherwise I will attempt to add this to my
> > > > patch.
> > >
> > > Here is the patch which adds this. It also allows ALTER LANGUAGE RENAME
> > > TO for the owner, which I missed before. I would appreciate someone with
> > > more knowledge of the permissions infrastructure to take a look at it
> > > since I am fairly new to it and may not fully understand its intricacies.
> > >
> >
> > I have refactored the owner checking of languages in the same manner as it
> > is for other owned objects. I have changed to using standard permissions
> > error messages (aclcheck_error) for the language permissions errors.
> >
> > I consider this patch ready for review, assuming the permissions rules
> > outlined by Tom Lane on -hackers are valid. For reference, here are the
> > rules that this patch is intended to implement:
> >
> > On Wed, 24 Jan 2007, Tom Lane wrote:
> >
> > > In detail, it'd look something like:
> > >
> > > * For an untrusted language: must be superuser to either create or use
> > > the language (no change from current rules). Ownership of the
> > > pg_language entry is really irrelevant, as is its ACL.
> > >
> > > * For a trusted language:
> > >
> > > * if pg_pltemplate.something is ON: either a superuser or the current
> > > DB's owner can CREATE the language. In either case the pg_language
> > > entry will be marked as owned by the DB owner (pg_database.datdba),
> > > which means that subsequently he (or a superuser) can grant or deny
> > > USAGE within his DB.
> > >
> > > * if pg_pltemplate.something is OFF: must be superuser to CREATE the
> > > language; subsequently it will be owned by you, so only you or another
> > > superuser can grant or deny USAGE (same behavior as currently).
> >
> > The only difference from this is, that when superuser is required, the
> > owner of the language is not the superuser who created it, but
> > BOOTSTRAP_SUPERUSERID. This is because my interpretation was that the
> > "same behavior as currently" took precedence. The current behavior in cvs
> > is that languages have no owner, and for purposes where one would be
> > needed it is assumed to be BOOTSTRAP_SUPERUSERID.
> >
> > Is this valid, or should I instead set the owner to GetUserId() in those
> > cases?
> >
> >
> > --
> > Academic politics is the most vicious and bitter form of politics,
> > because the stakes are so low.
> > -- Wallace Sayre
> Content-Description:
>
> [ Attachment, skipping... ]
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
>
>

--
A UNIX saleslady, Lenore,
Enjoys work, but she likes the beach more.
She found a good way
To combine work and play:
She sells C shells by the seashore.

>
> It's not so big addition to the gram.y, see a list of commands
> http://mira.sai.msu.su/~megera/pgsql/ftsdoc/sql-commands.html.
> SQL commands make FTS syntax clear and follow tradition to manage
> system objects. From the user's side, I'd be very unhappy to configure
> FTS, which can be very complex, using functions. All we want is to
> provide users clear syntax.
This is like the third time we have been around this problem. The syntax
is clear and reasonable imo.
Can we stop arguing about it and just include? If there are specific
issues beyond syntax that is one
thing, but that this point it seems we are arguing for the sake of arguing.

Joshua D. Drake

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Oleg Bartunov wrote:
>> It's not so big addition to the gram.y, see a list of commands
>> http://mira.sai.msu.su/~megera/pgsql/ftsdoc/sql-commands.html.

> I looked at the diff file and the major change in gram.y is the creation
> of a new object type FULLTEXT,

You mean four different object types. I'm not totally clear on bison's
scaling behavior relative to the number of productions, but I think
there's no question that this patch will impose a measurable distributed
penalty on every single query issued to Postgres by any application,
whether it's heard of tsearch or not. The percentage overhead would
be a lot lower if the patch were introducing a similar number of entries
into pg_proc.

regards, tom lane

Hi,

Tom Lane wrote:
> You mean four different object types. I'm not totally clear on bison's
> scaling behavior relative to the number of productions

You really want to trade parser performance (which is *very*
implementation specific) for ease of use?

Bison generates a LALR [1] parser, which depend quite a bit on the
number of productions. But AFAIK the dependency is mostly on memory
consumption for the internal symbol sets, not so much on runtime
complexity. I didn't find hard facts about runtime complexity of LALR,
though (pointers are very welcome).

Are there any ongoing efforts to rewrite the parser (i.e. using another
algorithm, like a recursive descent parser)?

Regards

Markus

[1]: Wikipedia on the LALR parsing algorithm:
http://en.wikipedia.org/wiki/LALR_parser

Markus Schiltknecht wrote:
> Hi,
>
> Tom Lane wrote:
>> You mean four different object types. I'm not totally clear on bison's
>> scaling behavior relative to the number of productions
>
> You really want to trade parser performance (which is *very*
> implementation specific) for ease of use?
>
> Bison generates a LALR [1] parser, which depend quite a bit on the
> number of productions. But AFAIK the dependency is mostly on memory
> consumption for the internal symbol sets, not so much on runtime
> complexity. I didn't find hard facts about runtime complexity of LALR,
> though (pointers are very welcome).

According to http://en.wikipedia.org/wiki/LR_parser processing one
token in any LR(1) parser in the worst case needs to
a) Do a lookup in the action table with the current (state, token) pair
b) Do a lookup in the goto table with a (state, rule) pair.
c) Push one state onto the stack, and pop n states with
n being the number of symbols (tokens or other rules) on the right
hand side of a rule.

a) and b) should be O(1). Processing one token pushes at most one state
onto the stack, so overall no more than N stats can be popped off again,
making the whole algorithm O(N) with N being the number of tokens of the
input stream.

AFAIK the only difference between SLR, LALR and LR(1) lies in the
generation of the goto and action tables.

> Are there any ongoing efforts to rewrite the parser (i.e. using another
> algorithm, like a recursive descent parser)?
Why would you want to do that?

greetings, Florian Pflug

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Oleg Bartunov wrote:
> >> It's not so big addition to the gram.y, see a list of commands
> >> http://mira.sai.msu.su/~megera/pgsql/ftsdoc/sql-commands.html.
>
> > I looked at the diff file and the major change in gram.y is the creation
> > of a new object type FULLTEXT,
>
> You mean four different object types. I'm not totally clear on bison's
> scaling behavior relative to the number of productions, but I think
> there's no question that this patch will impose a measurable distributed
> penalty on every single query issued to Postgres by any application,
> whether it's heard of tsearch or not. The percentage overhead would
> be a lot lower if the patch were introducing a similar number of entries
> into pg_proc.

My point is that the grammar splits off all the tsearch2 objects by
prefixing them with CREATE FULLTEXT object, where there are four object
types supported.

But as others have pointed out, the performance of the grammar is
probably not an issue in this case.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Hi,

Florian G. Pflug wrote:
> According to http://en.wikipedia.org/wiki/LR_parser processing one
> token in any LR(1) parser in the worst case needs to
> a) Do a lookup in the action table with the current (state, token) pair
> b) Do a lookup in the goto table with a (state, rule) pair.
> c) Push one state onto the stack, and pop n states with
> n being the number of symbols (tokens or other rules) on the right
> hand side of a rule.
>
> a) and b) should be O(1). Processing one token pushes at most one state
> onto the stack, so overall no more than N stats can be popped off again,
> making the whole algorithm O(N) with N being the number of tokens of the
> input stream.

Looks correct, thanks. What exactly is Tom worried about, then?

>> Are there any ongoing efforts to rewrite the parser (i.e. using
>> another algorithm, like a recursive descent parser)?
> Why would you want to do that?

I recall having read something about rewriting the parser. Together with
Tom being worried about parser performance and knowing GCC has switched
to a hand written parser some time ago, I suspected bison to be slow.
That's why I've asked.

Regards

Markus

Markus Schiltknecht wrote:

> Hi,
>
> I recall having read something about rewriting the parser. Together
> with Tom being worried about parser performance and knowing GCC has
> switched to a hand written parser some time ago, I suspected bison to
> be slow. That's why I've asked.
>
This has little to do with performance and everything to do with the
insanity which is C++:
http://gnu.teleglobe.net/software/gcc/gcc-3.4/changes.html

> * A hand-written recursive-descent C++ parser has replaced the
> YACC-derived C++ parser from previous GCC releases. The new
> parser contains much improved infrastructure needed for better
> parsing of C++ source codes, handling of extensions, and clean
> separation (where possible) between proper semantics analysis
> and parsing. The new parser fixes many bugs that were found in
> the old parser.
>

Short form: C++ is basically not LALR(1) parseable.

Brian

"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> Markus Schiltknecht wrote:
>> I didn't find hard facts about runtime complexity of LALR,
>> though (pointers are very welcome).

> a) and b) should be O(1). Processing one token pushes at most one state
> onto the stack, so overall no more than N stats can be popped off again,
> making the whole algorithm O(N) with N being the number of tokens of the
> input stream.

Yeah. I was concerned about the costs involved in trying to pack the
state tables, but it appears that that cost is all paid when the grammar
is compiled --- looking into gram.c, it appears the inner loop contains
just simple array lookups. Still, bloating of the state tables is
something we ought to pay attention to, because there's a distributed
cost once they no longer fit in a processor's L1 cache. On my machine
"size gram.o" is over 360K already ...

regards, tom lane

Markus Schiltknecht wrote:
>>> Are there any ongoing efforts to rewrite the parser (i.e. using
>>> another algorithm, like a recursive descent parser)?
>> Why would you want to do that?
>
> I recall having read something about rewriting the parser. Together with
> Tom being worried about parser performance and knowing GCC has switched
> to a hand written parser some time ago, I suspected bison to be slow.
> That's why I've asked.

I think the case is different for C and C++. The grammars of C and C++
appear to be much more parser-friendly then SQL, making handcrafting
a parser easier I'd think. And I believe that one of the reasons gcc
wasn't happy with bison was that I limited the quality of their error
reporting - which isn't that much of a problem for SQL, since SQL
statements are rather short compared to your typical C/C++ source file.

Last, but not least, the C and C++ syntax is basically set in stone - At
least now the g++ supports nearly all (or all? don't know) of the C++
standard. So it doesn't really matter if changes to the parse are a bit
more work, because the rarely happen. Postgres seems to add new features
that change the grammar with every release (with is a good thing!).

greetings, Florian Pflug

"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> Markus Schiltknecht wrote:
>>> Are there any ongoing efforts to rewrite the parser (i.e. using
>>> another algorithm, like a recursive descent parser)?

>> Why would you want to do that?

> Last, but not least, the C and C++ syntax is basically set in stone - At
> least now the g++ supports nearly all (or all? don't know) of the C++
> standard. So it doesn't really matter if changes to the parse are a bit
> more work, because the rarely happen. Postgres seems to add new features
> that change the grammar with every release (with is a good thing!).

Yeah. I think it would be a pretty bad idea for us to go over to a
handwritten parser: not only greater implementation effort for grammar
changes, but greater risk of introducing bugs. Bison tells you about it
when you've written something ambiguous ...

regards, tom lane

Oleg Bartunov wrote:
> It's not so big addition to the gram.y, see a list of commands
> http://mira.sai.msu.su/~megera/pgsql/ftsdoc/sql-commands.html.

In that proposed syntax, I would drop all "=", ",", "(", and ")". They
don't seem necessary and they are untypical for SQL commands. I'd
compare with CREATE FUNCTION or CREATE SEQUENCE for SQL commands that
do similar things.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Joshua D. Drake wrote:
> This is like the third time we have been around this problem. The
> syntax is clear and reasonable imo.

But others have differing opinions.

> Can we stop arguing about it and just include? If there are specific
> issues beyond syntax that is one
> thing, but that this point it seems we are arguing for the sake of
> arguing.

How is that worse than wanting to abort the discussion for the sake of
aborting the discussion?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Thu, 22 Feb 2007, Peter Eisentraut wrote:

> Oleg Bartunov wrote:
>> It's not so big addition to the gram.y, see a list of commands
>> http://mira.sai.msu.su/~megera/pgsql/ftsdoc/sql-commands.html.
>
> In that proposed syntax, I would drop all "=", ",", "(", and ")". They
> don't seem necessary and they are untypical for SQL commands. I'd
> compare with CREATE FUNCTION or CREATE SEQUENCE for SQL commands that
> do similar things.

that looks reasonable.

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

Hi,

Peter Eisentraut wrote:
> Oleg Bartunov wrote:
>> It's not so big addition to the gram.y, see a list of commands
>> http://mira.sai.msu.su/~megera/pgsql/ftsdoc/sql-commands.html.

As we still to still discuss the syntax: is there a proposal for how a
function based syntax would look like?

CREATE FULLTEXT CONFIGURATION myfts LIKE template_cfg AS DEFAULT;

just seems so much more SQL-like than:

SELECT add_fulltext_config('myfts', 'template_cfg', True);

I admit, that's a very simple and not thought through example. But as
long as those who prefer not to extend the grammar don't come up with a
better alternative syntax, one easily gets the impression that extending
the grammar in general is evil.

> In that proposed syntax, I would drop all "=", ",", "(", and ")". They
> don't seem necessary and they are untypical for SQL commands. I'd
> compare with CREATE FUNCTION or CREATE SEQUENCE for SQL commands that
> do similar things.

Yup, I'd second that.

Regards

Markus

> In that proposed syntax, I would drop all "=", ",", "(", and ")". They
> don't seem necessary and they are untypical for SQL commands. I'd
> compare with CREATE FUNCTION or CREATE SEQUENCE for SQL commands that
> do similar things.

I was looking at CREATE TYPE mostly. With removing "=", ",", "(", and ")" in
CREATE/ALTER FULLTEXT it's needed to add several items in unreserved_keyword
list. And increase gram.y by adding new rules similar to OptRoleList instead of
simple opt_deflist:
'(' def_list ')' { $$ = $2; }
| /*EMPTY*/ { $$ = NIL; }
;

Is it acceptable?
List of new keywords is: LOCALE, LEXIZE, INIT, OPT, GETTOKEN, LEXTYPES, HEADLINE

So, syntax will be
CREATE FULLTEXT DICTIONARY dictname
LEXIZE lexize_function
[ INIT init_function ]
[ OPT opt_text ];

CREATE FULLTEXT DICTIONARY dictname
[ { LEXIZE lexize_function | INIT init_function | OPT opt_text } [...] ]
LIKE template_dictname;

--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/

> CREATE FULLTEXT CONFIGURATION myfts LIKE template_cfg AS DEFAULT;
> SELECT add_fulltext_config('myfts', 'template_cfg', True);
That's simple, but what about
CREATE FULLTEXT MAPPING ON cfgname FOR lexemetypename[, ...] WITH dictname1[, ...];
?

SELECT create_fulltext_mapping(cfgname, '{lexemetypename[, ...]}'::text[],
'{dictname1[, ...]}'::text[]);

Seems rather ugly for me...

And function interface does not provide autocompletion and online help in psql.
\df says only types of arguments, not a meaning.

--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/

Teodor Sigaev wrote:
>> In that proposed syntax, I would drop all "=", ",", "(", and ")".
>> They don't seem necessary and they are untypical for SQL commands.
>> I'd compare with CREATE FUNCTION or CREATE SEQUENCE for SQL commands
>> that do similar things.
>
> I was looking at CREATE TYPE mostly. With removing "=", ",", "(", and
> ")" in CREATE/ALTER FULLTEXT it's needed to add several items in
> unreserved_keyword list. And increase gram.y by adding new rules
> similar to OptRoleList instead of
> simple opt_deflist:
> '(' def_list ')' { $$ = $2; }
> | /*EMPTY*/ { $$ = NIL; }
> ;
>
> Is it acceptable?
> List of new keywords is: LOCALE, LEXIZE, INIT, OPT, GETTOKEN,
> LEXTYPES, HEADLINE
>
> So, syntax will be
> CREATE FULLTEXT DICTIONARY dictname
> LEXIZE lexize_function
> [ INIT init_function ]
> [ OPT opt_text ];
>
> CREATE FULLTEXT DICTIONARY dictname
> [ { LEXIZE lexize_function | INIT init_function | OPT opt_text }
> [...] ]
> LIKE template_dictname;
>

If we are worried about the size of the transition table and keeping it
in cache (see remarks from Tom upthread) then adding more keywords seems
a bad idea, as it will surely expand the table. OTOH, I'd hate to make
that a design criterion. My main worry has been that the grammar would
be stable.

Just to quantify all this, I did a quick check on the grammar using
bison -v - we appear to have 473 terminal symbols, and 420 non-terminal
sybols in 1749 rules, generating 3142 states. The biggest tables
generated are yytable and yycheck, each about 90kb on my machine.

cheers

andrew

Hi,

Andrew Dunstan wrote:
> If we are worried about the size of the transition table and keeping it
> in cache (see remarks from Tom upthread) then adding more keywords seems
> a bad idea, as it will surely expand the table. OTOH, I'd hate to make
> that a design criterion.

Yeah, me too. Especially because it's an implementation issue against
ease of use. (Or can somebody convince me that functions would provide a
simple interface?)

> My main worry has been that the grammar would
> be stable.

You mean stability of the grammar for the new additions or for all the
grammar? Why are you worried about that?

> Just to quantify all this, I did a quick check on the grammar using
> bison -v - we appear to have 473 terminal symbols, and 420 non-terminal
> sybols in 1749 rules, generating 3142 states. The biggest tables
> generated are yytable and yycheck, each about 90kb on my machine.

That already sounds somewhat better that Tom's 300 kb. And considering
that these caches most probably grow faster than our grammar...

Regards

Markus

On Thursday 25 January 2007 12:51, Oleg Bartunov wrote:
> On Thu, 25 Jan 2007, Nikolay Samokhvalov wrote:
> > On 1/25/07, Teodor Sigaev <teodor(at)sigaev(dot)ru> wrote:
> >> It's should clear enough for now - dump data from old db and load into
> >> new one.
> >> But dump should be without any contrib/tsearch2 related functions.
> >
> > Upgrading from 8.1.x to 8.2.x was not tivial because of very trivial
> > change in API (actually not really API but the content of "pg_ts_*"
> > tables): russian snowball stemming function was forked to 2 different
> > ones, for koi8 and utf8 encodings. So, as I dumped my pg_ts_* tables
> > data (to keep my tsearch2 settings), I saw errors during restoration
> > (btw, why didn't you keep old russian stemmer function name as a
> > synonym to koi8 variant?) -- so, I had to change my dump file
> > manually, because I didn't manage to follow "tsearch2 best practices"
>
> sed and grep did the trick.
>
> > (to use some kind of "bootstrap" script that creates tsearch2
> > configuration you need from default one -- using several INSERTs and
> > UPDATEs). And there were no upgrade notes for tsearch2.
>
> This is unfair, you promised to write upgrade notes and we discussed the
> problem with name change before release and I rely on you. It was my fault,
> of course.
>

I got bit by this today and, afaict the best solution for the status quo would
be to change the install schema to something like tsearch2, which would then
allow for much easier dump and restore handling.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Am Donnerstag, 22. Februar 2007 14:33 schrieb Teodor Sigaev:
> \df says only types of arguments, not a meaning.

Only if you don't provide argument names.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Jeremy Drake <pgsql(at)jdrake(dot)com> writes:
> This version of the patch includes documentation changes. Please
> review...

Applied with minor revisions --- in particular, I thought the initial
owner of a language should be its creator, full stop, rather than the
rather strange (and undocumented) behavior you had. Also you missed
updating pg_dump.

regards, tom lane

On Mon, 26 Mar 2007, Tom Lane wrote:

> Jeremy Drake <pgsql(at)jdrake(dot)com> writes:
> > This version of the patch includes documentation changes. Please
> > review...
>
> Applied with minor revisions --- in particular, I thought the initial
> owner of a language should be its creator, full stop, rather than the
> rather strange (and undocumented) behavior you had.

The reason I did it like that was this email from you:
http://archives.postgresql.org/pgsql-hackers/2007-01/msg01186.php

> Also you missed updating pg_dump.

Indeed.

Now, all I need is the 'tsearch2 in core' patch to go in, and 8.3 will
solve almost all of my problems :) Then I just need to nag my hosting
provider to upgrade once it is released. I have been refraining from
nagging about them still running 8.1.3 in anticipation of this ;)

--
Five is a sufficiently close approximation to infinity.
-- Robert Firth

Jeremy Drake <pgsql(at)jdrake(dot)com> writes:
> On Mon, 26 Mar 2007, Tom Lane wrote:
>> Applied with minor revisions --- in particular, I thought the initial
>> owner of a language should be its creator, full stop, rather than the
>> rather strange (and undocumented) behavior you had.

> The reason I did it like that was this email from you:
> http://archives.postgresql.org/pgsql-hackers/2007-01/msg01186.php

Yeah, but that idea predated the addition of ALTER LANGUAGE OWNER to
the patch. Given that, a superuser can give away the language to
someone else if he wants, and so there's no need for us to try to be
fancy about guessing what he wants (which was more or less what that
rule was).

regards, tom lane