Thread: contrib catalogs

This might seem like a silly question, but what are the implications of
PostgreSQL allowing developers to create custom catalogs?

For example, PostgreSQL currently uses the pg_catalog schema to store
system catalogs / relations / functions / etc.  Has thought gone into
extending the scope to allow contrib modules to create their own
catalogs (ie. a PostGIS catalog, cube, tsearch2, etc.)?

The largest advantage I see is significantly easier upgrades.  Having
contrib tables/functions flagged as a catalog, pg_dump won't include
them in a dump - restoring into an upgraded database becomes painless.

Cheers,
Kevin

Kevin Neufeld <[email protected]> writes:
> This might seem like a silly question, but what are the implications of
> PostgreSQL allowing developers to create custom catalogs?

> For example, PostgreSQL currently uses the pg_catalog schema to store
> system catalogs / relations / functions / etc.  Has thought gone into
> extending the scope to allow contrib modules to create their own
> catalogs (ie. a PostGIS catalog, cube, tsearch2, etc.)?

A superuser can create whatever he wants in pg_catalog.  Whether this
is a good idea or will behave smoothly is a topic that has not been
thought about, to my knowledge.

            regards, tom lane

Really?  I didn't know that ... guess I never tried.  I'll have to do
some experimenting!  Thanx Tom.
-- Kevin

Tom Lane wrote:
> Kevin Neufeld <[email protected]> writes:
>> This might seem like a silly question, but what are the implications of
>> PostgreSQL allowing developers to create custom catalogs?
>
>> For example, PostgreSQL currently uses the pg_catalog schema to store
>> system catalogs / relations / functions / etc.  Has thought gone into
>> extending the scope to allow contrib modules to create their own
>> catalogs (ie. a PostGIS catalog, cube, tsearch2, etc.)?
>
> A superuser can create whatever he wants in pg_catalog.  Whether this
> is a good idea or will behave smoothly is a topic that has not been
> thought about, to my knowledge.
>
>             regards, tom lane
>

> Tom Lane wrote:

>> A superuser can create whatever he wants in pg_catalog.  Whether this
>> is a good idea or will behave smoothly is a topic that has not been
>> thought about, to my knowledge.
>>
>>             regards, tom lane
>>
>

Sorry, Tom. I think you are mistaken.  In my 8.3 instance, system
catalog modifications are not allowed.

/opt/pgsql83/bin/psql -p 5432 -U postgres postgres

Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help with psql commands
        \g or terminate with semicolon to execute query
        \q to quit

postgres=# \du
                                List of roles
  Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
  postgres  | yes       | yes         | yes       | no limit    | {}


postgres=# CREATE TABLE pg_catalog.test (id integer);
ERROR:  permission denied to create "pg_catalog.test"
DETAIL:  System catalog modifications are currently disallowed.


It would be interesting to test a contrib module install in pg_catalog
though.
Cheers,
-- Kevin

Kevin Neufeld <[email protected]> writes:
>> Tom Lane wrote:
>>> A superuser can create whatever he wants in pg_catalog.  Whether this
>>> is a good idea or will behave smoothly is a topic that has not been
>>> thought about, to my knowledge.

> Sorry, Tom. I think you are mistaken.  In my 8.3 instance, system
> catalog modifications are not allowed.

Hm, I had forgotten that the allow_system_table_mods filter extends to
anything at all in pg_catalog.  But you could easily enable that setting
in a test database and then see what happens.

            regards, tom lane

Thanx for tip, Tom.  I'll definitely give that a try in my spare time.
Cheers,
Kevin

Tom Lane wrote:
> Kevin Neufeld <[email protected]> writes:
>>> Tom Lane wrote:
>>>> A superuser can create whatever he wants in pg_catalog.  Whether this
>>>> is a good idea or will behave smoothly is a topic that has not been
>>>> thought about, to my knowledge.
>
>> Sorry, Tom. I think you are mistaken.  In my 8.3 instance, system
>> catalog modifications are not allowed.
>
> Hm, I had forgotten that the allow_system_table_mods filter extends to
> anything at all in pg_catalog.  But you could easily enable that setting
> in a test database and then see what happens.
>
>             regards, tom lane