Current CVS tip has most of the needed infrastructure for SQL-spec
schema support: you can create schemas, and you can create objects
within schemas, and search-path-based lookup for named objects works.
There's still a number of things to be done in the backend, but it's
time to start working on schema support in the various frontends that
have been broken by these changes. I believe that pretty much every
frontend library and client application that looks at system catalogs
will need revisions. So, this is a call for help --- I don't have the
time to fix all the frontends, nor sufficient familiarity with many
of them.

JDBC and ODBC metadata code is certainly broken; so are the catalog
lookups in pgaccess, pgadmin, and so on. psql and pg_dump are broken
as well (though I will take responsibility for fixing pg_dump, and will
then look at psql if no one else has done it by then). I'm not even
sure what else might need to change.

Here's an example of what's broken:

test=# create schema foo;
CREATE
test=# create table foo.mytab (f1 int, f2 text);
CREATE
test=# create schema bar;
CREATE
test=# create table bar.mytab (f1 text, f3 int);
CREATE
test=# \d mytab
Table "mytab"
Column | Type | Modifiers
--------+---------+-----------
f1 | text |
f1 | integer |
f2 | text |
f3 | integer |

psql's \d command hasn't the foggiest idea that there might now be more
than one pg_class entry with the same relname. It needs to be taught
about that --- but even before that, we need to work out schema-aware
definitions of the wildcard expansion rules for psql's backslash
commands that accept wildcarded names. In the above example, probably
"\d mytab" should have said "no such table" --- because neither foo nor
bar were in my search path, so I should not see them unless I give a
qualified name (eg, "\d foo.mytab" or "\d bar.mytab"). For commands
that accept wildcard patterns, what should happen --- should "\z my*"
find these tables, if they're not in my search path? Is "\z f*.my*"
sensible to support? I dunno yet.

If you've got time to work on fixing frontend code, or even helping
to work out definitional questions like these, please check out current
CVS tip or a nightly snapshot tarball and give it a try. (But do NOT
put any valuable data into current sources --- until pg_dump is fixed,
you won't be able to produce a useful backup of a database that uses
multiple schemas.)

Some documentation can be found at
http://developer.postgresql.org/docs/postgres/sql-naming.html
http://developer.postgresql.org/docs/postgres/sql-createschema.html
http://developer.postgresql.org/docs/postgres/sql-grant.html
http://developer.postgresql.org/docs/postgres/runtime-config.html#RUNTIME-CONFIG-GENERAL (see SEARCH_PATH)
but more needs to be written. (In particular, I think the Tutorial
could stand to have a short section added about schemas; and the Admin
Guide ought to be revised to discuss running one database with per-user
schemas as a good alternative to per-user databases. Any volunteers to
write that stuff?)

Some things that don't work yet in the backend:

1. There's no DROP SCHEMA. (If you need to, you can drop the contained
objects and then manually delete the pg_namespace row for the schema.)
No ALTER SCHEMA RENAME either (though you can just UPDATE the
pg_namespace row if you need that).

2. CREATE SCHEMA with sub-statements isn't up to SQL spec requirements
yet. Best bet is to create the schema and then create contained objects
separately, as in the above example.

3. I'm not sure that the newly-defined GRANT privileges are all checked
everywhere they should be. Also, the default privilege settings
probably need fine-tuning still.

4. We probably need more helper functions and/or predefined system views
to make it possible to fix the frontends in a reasonable way --- for
example, it's still quite difficult for something looking at pg_class to
determine which tables are visible in the current search path. Thoughts
about what should be provided are welcome.

regards, tom lane

Search Discussions

  • Bill Cunningham at Apr 30, 2002 at 5:46 pm
    <snip>
    Here's an example of what's broken:

    test=# create schema foo;
    CREATE
    test=# create table foo.mytab (f1 int, f2 text);
    CREATE
    test=# create schema bar;
    CREATE
    test=# create table bar.mytab (f1 text, f3 int);
    CREATE
    test=# \d mytab
    Table "mytab"
    Column | Type | Modifiers
    --------+---------+-----------
    f1 | text |
    f1 | integer |
    f2 | text |
    f3 | integer |
    I would think this should produce the following:

    Table "bar.mytab"
    Column | Type | Modifiers
    --------+---------+-----------
    f1 | text |
    f1 | integer |

    Table "foo.mytab"
    Column | Type | Modifiers
    --------+---------+-----------
    f2 | text |
    f3 | integer |

    What do you think?


    - Bill Cunningham
  • Tom Lane at Apr 30, 2002 at 6:28 pm

    Bill Cunningham writes:
    I would think this should produce the following:
    test=# \d mytab
    Table "bar.mytab"
    Column | Type | Modifiers
    --------+---------+-----------
    f1 | text |
    f1 | integer |
    Table "foo.mytab"
    Column | Type | Modifiers
    --------+---------+-----------
    f2 | text |
    f3 | integer |
    Even when schemas bar and foo are not in your search path? (And,
    perhaps, not even accessible to you?)

    My gut feeling is that "\d mytab" should tell you about the same
    table that "select * from mytab" would find. Anything else is
    probably noise to you --- if you wanted to know about foo.mytab,
    you could say "\d foo.mytab".

    However, \d is not a wildcardable operation AFAIR. For the commands
    that do take wildcard patterns (like \z), I'm not as sure what should
    happen.

    regards, tom lane
  • Bill Cunningham at Apr 30, 2002 at 8:47 pm

    Tom Lane wrote:
    Bill Cunningham <billc@ballydev.com> writes:
    I would think this should produce the following:
    test=# \d mytab
    Table "bar.mytab"
    Column | Type | Modifiers
    --------+---------+-----------
    f1 | text |
    f1 | integer |
    Table "foo.mytab"
    Column | Type | Modifiers
    --------+---------+-----------
    f2 | text |
    f3 | integer |
    Even when schemas bar and foo are not in your search path? (And,
    perhaps, not even accessible to you?)

    My gut feeling is that "\d mytab" should tell you about the same
    table that "select * from mytab" would find. Anything else is
    probably noise to you --- if you wanted to know about foo.mytab,
    you could say "\d foo.mytab".

    However, \d is not a wildcardable operation AFAIR. For the commands
    that do take wildcard patterns (like \z), I'm not as sure what should
    happen.

    regards, tom lane
    So we now have a default schema name of the current user? For example:

    foobar@somewhere> psql testme
    testme=# select * from mytab

    Table "foobar.mytab"
    Column | Type | Modifiers
    --------+---------+-----------
    f2 | text |
    f3 | integer |


    like that? This is exactly how DB2 operates, implict schemas for each user.

    - Bill Cunningham
  • Tom Lane at Apr 30, 2002 at 9:16 pm

    Bill Cunningham writes:
    So we now have a default schema name of the current user?
    ... This is exactly how DB2 operates, implict schemas for each user.
    You can operate that way. It's not the default though; the DBA will
    have to explicitly do a CREATE SCHEMA for each user. For instance:

    test=# CREATE USER tgl;
    CREATE USER
    test=# CREATE SCHEMA tgl AUTHORIZATION tgl;
    CREATE
    test=# \c - tgl
    You are now connected as new user tgl.
    test=> select current_schemas();
    current_schemas
    -----------------
    {tgl,public} -- my search path is now tgl, public
    (1 row)

    -- this creates tgl.foo:
    test=> create table foo(f1 int);
    CREATE
    test=> select * from foo;
    f1
    ----
    (0 rows)

    test=> select * from tgl.foo;
    f1
    ----
    (0 rows)


    If you don't create schemas then you get backwards-compatible behavior
    (all the users end up sharing the "public" schema as their current
    schema).

    See the development-docs pages I mentioned before for details.

    regards, tom lane
  • Christopher Kings-Lynne at May 1, 2002 at 2:11 am

    test=# CREATE USER tgl;
    CREATE USER
    test=# CREATE SCHEMA tgl AUTHORIZATION tgl;
    CREATE
    What about "CREATE USER tgl WITH SCHEMA;" ?

    Which will implicitly do a "CREATE SCHEMA tgl AUTHORIZATION tgl;"

    Chris
  • Tom Lane at May 1, 2002 at 3:20 am

    "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
    What about "CREATE USER tgl WITH SCHEMA;" ?
    Uh, what about it? It's not a standard syntax AFAIK.

    If I were running an installation where I wanted "one schema per user"
    as default, I'd rather have an "auto_create_schema" SET parameter that
    told CREATE USER to do the dirty work for me automatically.

    But the sneaky part of this is that users are installation-wide,
    whereas schemas are only database-wide. To make this really work
    painlessly, you'd want some kind of mechanism that'd auto-create
    a schema for the user in every database he's allowed access to.
    How can we define that cleanly?

    regards, tom lane
  • Bruce Momjian at Jun 8, 2002 at 4:42 am

    Tom Lane wrote:
    If you don't create schemas then you get backwards-compatible behavior
    (all the users end up sharing the "public" schema as their current
    schema).
    I am a little uncomfortable about this. It means that CREATE TABLE will
    create a table in 'public' if the user doesn't have a schema of their
    own, and in their private schema if it exists. I seems strange to have
    such a distinction based on whether a private schema exists. Is this OK?

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 853-3000
    + If your life is a hard drive, | 830 Blythe Avenue
    + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
  • Tom Lane at Jun 8, 2002 at 4:53 am

    Bruce Momjian writes:
    I am a little uncomfortable about this. It means that CREATE TABLE will
    create a table in 'public' if the user doesn't have a schema of their
    own, and in their private schema if it exists. I seems strange to have
    such a distinction based on whether a private schema exists. Is this OK?
    You have a better idea?

    Given that we want to support both backwards-compatible and SQL-spec-
    compatible behavior, I think some such ugliness is inevitable.

    regards, tom lane
  • Bruce Momjian at Jun 8, 2002 at 5:42 am

    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    I am a little uncomfortable about this. It means that CREATE TABLE will
    create a table in 'public' if the user doesn't have a schema of their
    own, and in their private schema if it exists. I seems strange to have
    such a distinction based on whether a private schema exists. Is this OK?
    You have a better idea?

    Given that we want to support both backwards-compatible and SQL-spec-
    compatible behavior, I think some such ugliness is inevitable.
    I don't have a better idea, but I am wondering how this will work. If I
    create a schema with my name, does it get added to the front of my
    schema schema search path automatically, or do I set it with SET,
    perhaps in my per-user startup SET column?

    If I want to prevent some users from creating tables in my database, do
    I remove CREATE on the schema using REVOKE SCHEMA, then create a schema
    for every user using the database?

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 853-3000
    + If your life is a hard drive, | 830 Blythe Avenue
    + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
  • Tom Lane at Jun 8, 2002 at 2:45 pm

    Bruce Momjian writes:
    I don't have a better idea, but I am wondering how this will work. If I
    create a schema with my name, does it get added to the front of my
    schema schema search path automatically,
    Yes (unless you've futzed with the standard value of search_path).
    If I want to prevent some users from creating tables in my database, do
    I remove CREATE on the schema using REVOKE SCHEMA, then create a schema
    for every user using the database?
    Well, you revoke world create access on the public schema (or maybe even
    delete the public schema, if you don't need it). I don't see why you'd
    give people their own schemas if the intent is to keep them from
    creating tables.

    regards, tom lane
  • Bruce Momjian at Jun 8, 2002 at 3:12 pm

    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    I don't have a better idea, but I am wondering how this will work. If I
    create a schema with my name, does it get added to the front of my
    schema schema search path automatically,
    Yes (unless you've futzed with the standard value of search_path).
    If I want to prevent some users from creating tables in my database, do
    I remove CREATE on the schema using REVOKE SCHEMA, then create a schema
    for every user using the database?
    Well, you revoke world create access on the public schema (or maybe even
    delete the public schema, if you don't need it). I don't see why you'd
    give people their own schemas if the intent is to keep them from
    creating tables.
    No, I was saying you would have to create schemas for the people who you
    _want_ to be able to create tables.

    With the old NOCREATE patch, you could just remove create permission
    from a user. With schemas, you have to remove all permission for table
    creation, then grant it to those you want by creating schemas for them.

    This is similar to handling of Unix permissions. If you want to
    restrict access to a file or directory, you remove public permission,
    and add group permission, then add the people who you want access to
    that group.

    There are no _negative_ permissions, as there are no negative
    permissions in the unix file system. I just wanted to be clear that
    restricting access will be multi-step process.

    If I remove public create access to public, can the super user or db
    owner still create tables?

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 853-3000
    + If your life is a hard drive, | 830 Blythe Avenue
    + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
  • Tom Lane at Jun 8, 2002 at 4:03 pm

    Bruce Momjian writes:
    If I remove public create access to public, can the super user or db
    owner still create tables?
    Superusers can always do whatever they want.

    The DB owner (assume he's not a superuser) has no special privileges
    w.r.t. the public schema at the moment. We could perhaps put in a
    kluge to change this, but it would definitely be a kluge --- I don't
    see any clean way to make the behavior different.

    One possible approach would be for a superuser to change the ownership
    of public to be the DB owner.

    regards, tom lane
  • Bruce Momjian at Jun 11, 2002 at 10:24 pm
    There was discussion of how template1's "public" schema should behave.
    I think the only solution is to make template1's public schema writable
    only by the super-user. This way, we can allow utility commands to
    connect to template1, but they can't change anything or add their own
    tables.

    As part of createdb, the new database will have to have it's public
    schema changed to world-writable.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 853-3000
    + If your life is a hard drive, | 830 Blythe Avenue
    + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
  • Tom Lane at Jun 12, 2002 at 2:59 am

    Bruce Momjian writes:
    As part of createdb, the new database will have to have it's public
    schema changed to world-writable.
    That ain't gonna happen, unfortunately. CREATE DATABASE runs in some
    database other than the target one, so it's essentially impossible for
    the newly-created DB to contain any internal state that's different
    from the template DB. Next idea please?

    regards, tom lane
  • Bruce Momjian at Jun 12, 2002 at 3:14 am

    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    As part of createdb, the new database will have to have it's public
    schema changed to world-writable.
    That ain't gonna happen, unfortunately. CREATE DATABASE runs in some
    database other than the target one, so it's essentially impossible for
    the newly-created DB to contain any internal state that's different
    from the template DB. Next idea please?
    Yes, there was an even bigger problem with my argument. If someone
    wanted to make public no-write, and have all created databases inherit
    from that, it wouldn't work because it would clear that on creation.

    How about if we hard-wire template1 as being no-write to public
    somewhere in the code, rather than in the db tables?

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 853-3000
    + If your life is a hard drive, | 830 Blythe Avenue
    + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
  • Tom Lane at Jun 12, 2002 at 3:42 am

    Bruce Momjian writes:
    How about if we hard-wire template1 as being no-write to public
    somewhere in the code, rather than in the db tables?
    Seems pretty icky :-(

    It occurs to me that maybe we don't need to worry. The main reason why
    we've offered the advice "don't fill template1 with junk" in the past
    is that it was so hard to clear out the junk without zapping built-in
    entries. But now, you really have to work hard at it to shoot yourself
    in the foot that way. If you created junk in template1.public, no
    sweat:
    \c template1 postgres
    DROP SCHEMA public;
    CREATE SCHEMA public;
    -- don't forget to set its permissions appropriately
    (This assumes we get DROP SCHEMA implemented in time for 7.3, but
    I think we can build that based on Rod's pg_depend stuff.) (Which
    I really really gotta review and apply soon.)

    I'm of the opinion that template1 and public are not very special
    at the moment; the C-level code doesn't think either of them are
    special, which is why you can drop and recreate them if you have to.
    We should try not to re-introduce any low-level specialness.

    regards, tom lane
  • Bruce Momjian at Jun 12, 2002 at 3:44 am

    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    How about if we hard-wire template1 as being no-write to public
    somewhere in the code, rather than in the db tables?
    Seems pretty icky :-(

    It occurs to me that maybe we don't need to worry. The main reason why
    we've offered the advice "don't fill template1 with junk" in the past
    is that it was so hard to clear out the junk without zapping built-in
    entries. But now, you really have to work hard at it to shoot yourself
    in the foot that way. If you created junk in template1.public, no
    sweat:
    \c template1 postgres
    DROP SCHEMA public;
    CREATE SCHEMA public;
    -- don't forget to set its permissions appropriately
    (This assumes we get DROP SCHEMA implemented in time for 7.3, but
    I think we can build that based on Rod's pg_depend stuff.) (Which
    I really really gotta review and apply soon.)

    I'm of the opinion that template1 and public are not very special
    at the moment; the C-level code doesn't think either of them are
    special, which is why you can drop and recreate them if you have to.
    We should try not to re-introduce any low-level specialness.
    It is strange we have to allow template1 open just for client stuff. I
    would really like to lock it down read-only. I guess we can tell admins
    to lock down public in template1, and all newly created databases will
    be the same.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 853-3000
    + If your life is a hard drive, | 830 Blythe Avenue
    + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
  • Oleg Bartunov at Apr 30, 2002 at 6:54 pm
    I think DBD::Pg driver very much depends on system tables.
    Hope, Jeffrey (current maintainer) is online.

    regards,

    Oleg
    On Tue, 30 Apr 2002, Tom Lane wrote:

    Current CVS tip has most of the needed infrastructure for SQL-spec
    schema support: you can create schemas, and you can create objects
    within schemas, and search-path-based lookup for named objects works.
    There's still a number of things to be done in the backend, but it's
    time to start working on schema support in the various frontends that
    have been broken by these changes. I believe that pretty much every
    frontend library and client application that looks at system catalogs
    will need revisions. So, this is a call for help --- I don't have the
    time to fix all the frontends, nor sufficient familiarity with many
    of them.

    JDBC and ODBC metadata code is certainly broken; so are the catalog
    lookups in pgaccess, pgadmin, and so on. psql and pg_dump are broken
    as well (though I will take responsibility for fixing pg_dump, and will
    then look at psql if no one else has done it by then). I'm not even
    sure what else might need to change.

    Here's an example of what's broken:

    test=# create schema foo;
    CREATE
    test=# create table foo.mytab (f1 int, f2 text);
    CREATE
    test=# create schema bar;
    CREATE
    test=# create table bar.mytab (f1 text, f3 int);
    CREATE
    test=# \d mytab
    Table "mytab"
    Column | Type | Modifiers
    --------+---------+-----------
    f1 | text |
    f1 | integer |
    f2 | text |
    f3 | integer |

    psql's \d command hasn't the foggiest idea that there might now be more
    than one pg_class entry with the same relname. It needs to be taught
    about that --- but even before that, we need to work out schema-aware
    definitions of the wildcard expansion rules for psql's backslash
    commands that accept wildcarded names. In the above example, probably
    "\d mytab" should have said "no such table" --- because neither foo nor
    bar were in my search path, so I should not see them unless I give a
    qualified name (eg, "\d foo.mytab" or "\d bar.mytab"). For commands
    that accept wildcard patterns, what should happen --- should "\z my*"
    find these tables, if they're not in my search path? Is "\z f*.my*"
    sensible to support? I dunno yet.

    If you've got time to work on fixing frontend code, or even helping
    to work out definitional questions like these, please check out current
    CVS tip or a nightly snapshot tarball and give it a try. (But do NOT
    put any valuable data into current sources --- until pg_dump is fixed,
    you won't be able to produce a useful backup of a database that uses
    multiple schemas.)

    Some documentation can be found at
    http://developer.postgresql.org/docs/postgres/sql-naming.html
    http://developer.postgresql.org/docs/postgres/sql-createschema.html
    http://developer.postgresql.org/docs/postgres/sql-grant.html
    http://developer.postgresql.org/docs/postgres/runtime-config.html#RUNTIME-CONFIG-GENERAL (see SEARCH_PATH)
    but more needs to be written. (In particular, I think the Tutorial
    could stand to have a short section added about schemas; and the Admin
    Guide ought to be revised to discuss running one database with per-user
    schemas as a good alternative to per-user databases. Any volunteers to
    write that stuff?)

    Some things that don't work yet in the backend:

    1. There's no DROP SCHEMA. (If you need to, you can drop the contained
    objects and then manually delete the pg_namespace row for the schema.)
    No ALTER SCHEMA RENAME either (though you can just UPDATE the
    pg_namespace row if you need that).

    2. CREATE SCHEMA with sub-statements isn't up to SQL spec requirements
    yet. Best bet is to create the schema and then create contained objects
    separately, as in the above example.

    3. I'm not sure that the newly-defined GRANT privileges are all checked
    everywhere they should be. Also, the default privilege settings
    probably need fine-tuning still.

    4. We probably need more helper functions and/or predefined system views
    to make it possible to fix the frontends in a reasonable way --- for
    example, it's still quite difficult for something looking at pg_class to
    determine which tables are visible in the current search path. Thoughts
    about what should be provided are welcome.

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster
    Regards,
    Oleg
    _____________________________________________________________
    Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
    Sternberg Astronomical Institute, Moscow University (Russia)
    Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
    phone: +007(095)939-16-83, +007(095)939-23-83
  • Jeffrey W. Baker at May 1, 2002 at 11:07 pm

    On Tue, Apr 30, 2002 at 09:41:47PM +0300, Oleg Bartunov wrote:
    I think DBD::Pg driver very much depends on system tables.
    Hope, Jeffrey (current maintainer) is online.
    These changes may break DBD::Pg. What is the expected
    time of this release? I will review my code for impact.

    Thanks for the warning,
    Jeffrey
  • Oleg Bartunov at May 2, 2002 at 3:31 pm

    On Wed, 1 May 2002, Jeffrey W. Baker wrote:
    On Tue, Apr 30, 2002 at 09:41:47PM +0300, Oleg Bartunov wrote:
    I think DBD::Pg driver very much depends on system tables.
    Hope, Jeffrey (current maintainer) is online.
    These changes may break DBD::Pg. What is the expected
    time of this release? I will review my code for impact.
    Jeffrey,

    btw, DBD-Pg 1.13 doesn't passed all tests
    (Linux 2.4.17, pgsql 7.2.1, DBI-1.21)

    t/02prepare.........ok
    t/03bind............ok
    t/04execute.........FAILED tests 5-7
    Failed 3/10 tests, 70.00% okay
    t/05fetch...........ok
    t/06disconnect......ok
    Thanks for the warning,
    Jeffrey

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    http://archives.postgresql.org
    Regards,
    Oleg
    _____________________________________________________________
    Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
    Sternberg Astronomical Institute, Moscow University (Russia)
    Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
    phone: +007(095)939-16-83, +007(095)939-23-83
  • Tom Lane at May 2, 2002 at 3:39 pm

    On Wed, 1 May 2002, Jeffrey W. Baker wrote:
    These changes may break DBD::Pg. What is the expected
    time of this release? I will review my code for impact.
    I think the current plan is to go beta in late summer. So there's
    no tremendous hurry. I was just sending out a wake-up call ...

    regards, tom lane
  • Jeffrey W. Baker at May 2, 2002 at 4:22 pm

    On Thu, May 02, 2002 at 05:28:36PM +0300, Oleg Bartunov wrote:
    On Wed, 1 May 2002, Jeffrey W. Baker wrote:
    On Tue, Apr 30, 2002 at 09:41:47PM +0300, Oleg Bartunov wrote:
    I think DBD::Pg driver very much depends on system tables.
    Hope, Jeffrey (current maintainer) is online.
    These changes may break DBD::Pg. What is the expected
    time of this release? I will review my code for impact.
    Jeffrey,

    btw, DBD-Pg 1.13 doesn't passed all tests
    (Linux 2.4.17, pgsql 7.2.1, DBI-1.21)

    t/02prepare.........ok
    t/03bind............ok
    t/04execute.........FAILED tests 5-7
    Failed 3/10 tests, 70.00% okay
    t/05fetch...........ok
    t/06disconnect......ok
    These tests were failing when I inherited the code. I'll fix them
    when I rewrite the parser.

    -jwb
  • Dave Page at Apr 30, 2002 at 6:55 pm

    -----Original Message-----
    From: Tom Lane
    Sent: 30 April 2002 18:32
    To: pgsql-hackers@postgresql.org; pgsql-interfaces@postgresql.org
    Subject: [INTERFACES] Schemas: status report, call for developers


    Current CVS tip has most of the needed infrastructure for
    SQL-spec schema support: you can create schemas, and you can
    create objects within schemas, and search-path-based lookup
    for named objects works. There's still a number of things to
    be done in the backend, but it's time to start working on
    schema support in the various frontends that have been broken
    by these changes. I believe that pretty much every frontend
    library and client application that looks at system catalogs
    will need revisions. So, this is a call for help --- I don't
    have the time to fix all the frontends, nor sufficient
    familiarity with many of them.

    JDBC and ODBC metadata code is certainly broken; so are the
    catalog lookups in pgaccess, pgadmin, and so on. psql and
    pg_dump are broken as well (though I will take responsibility
    for fixing pg_dump, and will then look at psql if no one else
    has done it by then). I'm not even sure what else might need
    to change.
    Thanks Tom, this is just the post I've been waiting for!

    To anyone thinking of hacking pgAdmin at the moment -> now would
    probably not be the best time as I will be *seriously* restructuring
    pgSchema.

    Regards, Dave.
  • Christopher Kings-Lynne at May 1, 2002 at 2:06 am

    JDBC and ODBC metadata code is certainly broken; so are the
    catalog lookups in pgaccess, pgadmin, and so on. psql and
    pg_dump are broken as well (though I will take responsibility
    for fixing pg_dump, and will then look at psql if no one else
    has done it by then). I'm not even sure what else might need
    to change.
    phpPgAdmin (WebDB) will be broken as well. I think myself and at least a
    few other committers lurk here tho.

    Other things that will break:

    TOra
    Various KDE interfaces

    Chris
  • Ross J. Reedstrom at May 1, 2002 at 3:32 am

    On Wed, May 01, 2002 at 10:05:23AM +0800, Christopher Kings-Lynne wrote:

    phpPgAdmin (WebDB) will be broken as well. I think myself and at least a
    few other committers lurk here tho.

    Other things that will break:

    TOra
    Various KDE interfaces
    GNUe will break, as well.

    Ross
  • Rod Taylor at May 1, 2002 at 3:46 am
    I think it would be much faster simply to list of the programs that
    use Postgresql internals that won't break.
    --
    Rod
    ----- Original Message -----
    From: "Ross J. Reedstrom" <reedstrm@rice.edu>
    To: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
    Cc: "Dave Page" <dpage@vale-housing.co.uk>; "Tom Lane"
    <tgl@sss.pgh.pa.us>; <pgsql-hackers@postgresql.org>;
    <pgsql-interfaces@postgresql.org>; <pgadmin-hackers@postgresql.org>
    Sent: Tuesday, April 30, 2002 11:28 PM
    Subject: Re: [HACKERS] [INTERFACES] Schemas: status report, call for
    developers

    On Wed, May 01, 2002 at 10:05:23AM +0800, Christopher Kings-Lynne
    wrote:
    phpPgAdmin (WebDB) will be broken as well. I think myself and at
    least a
    few other committers lurk here tho.

    Other things that will break:

    TOra
    Various KDE interfaces
    GNUe will break, as well.

    Ross

    ---------------------------(end of
    broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to majordomo@postgresql.org so that your
    message can get through to the mailing list cleanly
  • Tom Lane at May 1, 2002 at 4:12 am

    "Rod Taylor" <rbt@zort.ca> writes:
    I think it would be much faster simply to list of the programs that
    use Postgresql internals that won't break.
    Approximately none, I'm sure :-(. This thread isn't about that, it's
    about stirring up the troops to fix everything that must be fixed.

    regards, tom lane
  • Tom Lane at May 1, 2002 at 4:03 am

    "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
    GNUe will break, as well.
    Do I hear a volunteer to fix it?

    regards, tom lane
  • Ross J. Reedstrom at May 1, 2002 at 4:49 am

    On Wed, May 01, 2002 at 12:03:00AM -0400, Tom Lane wrote:
    "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
    GNUe will break, as well.
    Do I hear a volunteer to fix it?
    I'm willing to implement whatever clever solution we all come up with.
    I'll have to coordinate w/ the GNUe IRC folks to get it checked in.

    Ross
  • Tom Lane at May 1, 2002 at 4:55 am

    "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
    GNUe will break, as well.
    I'm willing to implement whatever clever solution we all come up with.
    If you need help in inventing a solution, it'd be a good idea to explain
    the problem. Personally I'm not familiar with GNUe ...

    regards, tom lane
  • Ross J. Reedstrom at May 1, 2002 at 5:21 am

    On Wed, May 01, 2002 at 12:56:00AM -0400, Tom Lane wrote:
    "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
    GNUe will break, as well.
    I'm willing to implement whatever clever solution we all come up with.
    If you need help in inventing a solution, it'd be a good idea to explain
    the problem. Personally I'm not familiar with GNUe ...
    I think all the interfaces are having the same fundemental problem: how to
    limit the tables 'seen' to a particular list of schema (those in the path).

    GNUe is GNU Enterprise System - a somewhat grandiose name for a business
    middleware solutions project. It's highly modular, with a common core to
    deal with things like DB access. There's a reasonably nice forms designer
    to handle quickie 2-tier DB apps (client-server, skip the middleware).

    Right now, it's mostly coded in python. I'm taking off on a
    business trip for the remainder of the week, starting tomorrow (err
    today?!) morning. I'll take the GNUe code along and see what it's db
    schema discovery code is actually doing, and think about what sort of
    clever things to do. I think for GNUe, we might get away with requiring
    the end-user (designer) to select a particular schema to work in, and then
    just qualify everything.

    Later,
    Ross
  • Ian Barwick at May 1, 2002 at 1:45 am

    For commands
    that accept wildcard patterns, what should happen --- should "\z my*"
    find these tables, if they're not in my search path? Is "\z f*.my*"
    sensible to support? I dunno yet.
    Technical question - this query:

    SELECT nspname AS schema,
    relname AS object
    FROM pg_class c
    INNER JOIN pg_namespace n
    ON c.relnamespace=n.oid
    WHERE relkind in ('r', 'v', 'S') AND
    relname NOT LIKE 'pg$_%%' ESCAPE '$'

    produces a result like this:

    schema | object
    --------+--------
    public | abc
    foo | abc
    foo | xyz
    bar | xyz
    (4 rows)

    How can I restrict the query to the schemas in the
    current search path, i.e. the schema names returned
    by SELECT current_schemas() ?


    Ian Barwick
  • Christopher Kings-Lynne at May 1, 2002 at 2:17 am

    produces a result like this:

    schema | object
    --------+--------
    public | abc
    foo | abc
    foo | xyz
    bar | xyz
    (4 rows)

    How can I restrict the query to the schemas in the
    current search path, i.e. the schema names returned
    by SELECT current_schemas() ?
    Now, if we had functions-returning-sets, this would all be easy as all you'd
    need to do would be to join it with the function returning the set of
    schemas in your search path :)

    Chris
  • Tom Lane at May 1, 2002 at 4:39 am

    Ian Barwick writes:
    How can I restrict the query to the schemas in the
    current search path, i.e. the schema names returned
    by SELECT current_schemas() ?
    Well, this is the issue open for public discussion.

    We could define some function along the lines of
    "is_visible_table(oid) returns bool", and then you could use
    that as a WHERE clause in your query. But I'm worried about
    the performance implications --- is_visible_table() would have
    to do several retail probes of the system tables, and I don't
    see any way to optimize that across hundreds of table OIDs.

    I have a nagging feeling that this could be answered by defining
    a view on pg_class that only shows visible tables ... but I don't
    quite see how to define that efficiently, either. Ideas anyone?

    regards, tom lane
  • Ian Barwick at May 26, 2002 at 5:56 pm

    On Wednesday 01 May 2002 06:38, Tom Lane wrote:
    Ian Barwick <barwick@gmx.net> writes:
    How can I restrict the query to the schemas in the
    current search path, i.e. the schema names returned
    by SELECT current_schemas() ?
    Well, this is the issue open for public discussion.

    We could define some function along the lines of
    "is_visible_table(oid) returns bool", and then you could use
    that as a WHERE clause in your query. But I'm worried about
    the performance implications --- is_visible_table() would have
    to do several retail probes of the system tables, and I don't
    see any way to optimize that across hundreds of table OIDs.

    I have a nagging feeling that this could be answered by defining
    a view on pg_class that only shows visible tables ... but I don't
    quite see how to define that efficiently, either. Ideas anyone?
    (time passes...)

    How about a function such as the one attached: "select_schemas_setof()"
    which returns the OIDs of the schemas in the current search path as
    a set. (Note: "select_schemas_setof()" as shown is a userspace C function.)

    It works like this:

    template1=# CREATE DATABASE schema_test;
    CREATE DATABASE
    template1=# \c schema_test
    You are now connected to database schema_test.
    schema_test=# CREATE OR REPLACE FUNCTION current_schemas_setof()
    schema_test-# RETURNS setof OID
    schema_test-# as '/path/to/current_schemas_setof.so'
    schema_test-# LANGUAGE 'C';
    CREATE FUNCTION


    I can then do this:

    schema_test=# CREATE SCHEMA foo;
    CREATE SCHEMA
    schema_test=# CREATE TABLE foo.mytab(col1 int, col2 text);
    CREATE TABLE
    schema_test=# CREATE SCHEMA bar;
    CREATE SCHEMA
    schema_test=# CREATE TABLE bar.mytab(col1 int, col2 text);
    CREATE TABLE
    schema_test=# SET search_path = public, foo, bar;
    SET
    schema_test=# SELECT current_schemas();
    current_schemas
    ------------------
    {public,foo,bar}
    (1 row)

    schema_test=# SELECT current_schemas_setof, n.nspname
    schema_test-# FROM public.current_schemas_setof() cs, pg_namespace n
    schema_test-# WHERE cs.current_schemas_setof = n.oid;
    current_schemas_setof | nspname
    ----------------------+------------
    16563 | pg_temp_1
    11 | pg_catalog
    2200 | public
    24828 | foo
    24835 | bar
    (3 rows)


    With the function in place I can then create an SQL function like this:

    CREATE OR REPLACE FUNCTION public.first_visible_namespace(name)
    RETURNS oid
    AS
    'SELECT n.oid
    FROM pg_namespace n, pg_class c, public.current_schemas_setof() cs
    WHERE c.relname= $1
    AND c.relnamespace=n.oid
    AND n.oid= cs.current_schemas_setof
    LIMIT 1'
    LANGUAGE 'sql';

    which can be used like this:

    schema_test=# select public.first_visible_namespace('mytab');
    first_visible_namespace
    -------------------------
    24828
    (1 row)

    i.e. finds the first visible schema containing an unqualified relation name.
    24828 corresponds to the OID of schema "foo".

    The following VIEW:

    CREATE VIEW public.desc_table_view AS
    SELECT n.nspname AS "Schema",
    c.relname AS "Table",
    a.attname AS "Column",
    format_type (a.atttypid, a.atttypmod) AS "Type"
    FROM pg_class c, pg_attribute a, pg_namespace n
    WHERE a.attnum > 0
    AND c.relkind IN ('r', 'v', 'S')
    AND a.attrelid = c.oid
    AND c.relnamespace=n.oid
    AND n.oid IN (SELECT first_visible_namespace(c.relname))
    ORDER BY a.attnum;

    then provides a simplified simulation of psql's slash command \d [NAME] for
    unqualified relation names, e.g.:

    schema_test=# SELECT * FROM public.desc_table_view WHERE "Table" = 'mytab';
    Schema | Table | Column | Type
    --------+-------+--------+---------
    foo | mytab | col1 | integer
    foo | mytab | col2 | text
    (2 rows)
    schema_test=# SET search_path= bar, foo, public;
    SET
    schema_test=# SELECT * FROM public.desc_table_view WHERE "Table" = 'mytab';
    Schema | Table | Column | Type
    --------+-------+--------+---------
    bar | mytab | col1 | integer
    bar | mytab | col2 | text
    (2 rows)

    schema_test=# SET search_path= public;
    SET
    schema_test=# SELECT * FROM public.desc_table_view WHERE "Table" = 'mytab';
    Schema | Table | Column | Type
    --------+-------+--------+------
    (0 rows)


    which I think is the desired behaviour. Currently \d [NAME] produces this:

    schema_test=# SET search_path= bar, foo, public;
    SET
    schema_test=# \d mytab
    Table "mytab"
    Column | Type | Modifiers
    --------+---------+-----------
    col1 | integer |
    col1 | integer |
    col2 | text |
    col2 | text |

    i.e. finds and describes "foo.mytab" and "bar.mytab".

    (Note: "SELECT * FROM public.desc_table_view" will just dump an unordered
    list of all columns for the first visible instance of each table name).

    Assuming "current_schemas_setof()" can be implemented as an internal function,
    (I haven't managed it myself yet :-( ), I suspect it is a more efficient
    alternative to a putative "is_visible_table(oid)" and could be used in psql
    (and elsewhere) to resolve the schemas of unqualified relation names.
    Thoughts? (Or am I barking up the wrong tree?)

    BTW is anyone working on schema support in psql? If the various definition
    issues raised by Tom Lane at the start of this thread are resolved (discussion
    seems to have trailed off without a consensus), I have some free time in June
    and would be willing to take it on.


    Ian Barwick
  • Tom Lane at May 26, 2002 at 8:12 pm

    Ian Barwick writes:
    CREATE OR REPLACE FUNCTION public.first_visible_namespace(name)
    RETURNS oid
    AS
    'SELECT n.oid
    FROM pg_namespace n, pg_class c, public.current_schemas_setof() cs
    WHERE c.relname=3D $1
    AND c.relnamespace=3Dn.oid
    AND n.oid=3D cs.current_schemas_setof
    LIMIT 1'
    LANGUAGE 'sql';
    I don't believe this is correct. The LIMIT clause will ensure you
    get at most one answer, but it'd be pure luck whether it is the right
    answer, when there are multiple tables of the same name in the
    namespaces of the search path.
    The following VIEW:
    CREATE VIEW public.desc_table_view AS
    SELECT n.nspname AS "Schema",
    c.relname AS "Table",
    a.attname AS "Column",
    format_type=09(a.atttypid, a.atttypmod) AS "Type"
    FROM pg_class c, pg_attribute a, pg_namespace n
    WHERE a.attnum > 0
    AND c.relkind IN ('r', 'v', 'S')
    AND a.attrelid =3D c.oid
    AND c.relnamespace=3Dn.oid
    AND n.oid IN (SELECT first_visible_namespace(c.relname))
    ORDER BY a.attnum;
    I was hoping to find something more efficient than that --- quite aside
    from the speed or correctness of first_visible_namespace(), a query
    depending on an IN is not going to be fast.

    regards, tom lane
  • Ian Barwick at May 1, 2002 at 10:53 pm

    Tom Lane wrote:
    psql's \d command hasn't the foggiest idea that there might now be more
    than one pg_class entry with the same relname. It needs to be taught
    about that --- but even before that, we need to work out schema-aware
    definitions of the wildcard expansion rules for psql's backslash
    commands that accept wildcarded names. In the above example, probably
    "\d mytab" should have said "no such table" --- because neither foo nor
    bar were in my search path, so I should not see them unless I give a
    qualified name (eg, "\d foo.mytab" or "\d bar.mytab").
    (and also in mail to Bill Cunningham)
    My gut feeling is that "\d mytab" should tell you about the same
    table that "select * from mytab" would find. Anything else is
    probably noise to you --
    General consistency with SELECT behaviour sounds right to me.
    For commands
    that accept wildcard patterns, what should happen --- should "\z my*"
    find these tables, if they're not in my search path? Is "\z f*.my*"
    sensible to support? I dunno yet.
    My digestive organs tell me: an unqualified wildcard pattern should
    stick to the search path; the search path should only be overridden
    when the user explicitly provides a wildcard pattern for schema names.
    This would be consistent with the behaviour of \d etc., i.e.
    "\d mytab" should look for 'mytab' in the current search path;
    "\dt my*" should look for tables beginning with "my" in the current
    search path; "\dt f*.my*" would look for same in all schemas beginning
    with "f"; and "\dt *.my*" would look in all schemas.

    Problem: "wildcard pattern" is a bit of a misnomer, the relevant
    commands take regular expressions, which means the dot in "\z f*.my*"
    won't necessarily be the dot in "\z foo.mytab" - it would have to
    be written "\z f*\\.my*". Though technically correct this
    strikes me as counterintuitive, especially with the double escaping
    (once for psql, once for the regex literal).

    An alternative solution would be to allow the pattern matching
    commands to accept either one ("\z my*") or two ("\z f* my*") regular
    expressions; in the latter case the first regex is for the schema name,
    the second for the object name. However, doing away with the dot altogether
    is also counterintuitive and breaks with the usual schema denotation.

    Proposal: in "wildcard" slash commands drop regular expressions and
    use LIKE for pattern matching. This would enable commands such as
    "\z f%.my%". (Would this mean major breakage? Is there an installed
    base of scripts which rely on psql slash commands and regular expressions?)
    I can't personally recall ever having needed to use a regular expression
    any more complex than the wildcard pattern matching which could be implemented
    just as well with LIKE. (Anyone requiring regular expression matching could
    still create appropriate views).

    Question - which output format is preferable?:

    schema_test=# \z
    Access privileges for database "schema_test"
    Schema | Object | Access privileges
    --------+--------+-------------------
    public | bar |
    foo | bar |
    (2 rows)

    or

    schema_test=# \z
    Access privileges for database "schema_test"
    Object | Access privileges
    ------------+-------------------
    public.bar |
    foo.bar |
    (2 rows)
    If you've got time to work on fixing frontend code, or even helping
    to work out definitional questions like these (...)
    Hmm, time for "ask not what your database can do for you but what
    you can do for your database". I'm willing to put my keyboard where
    my mouth is and take on psql once any outstanding questions are
    cleared up, if noone better qualified than me comes
    forward and provided someone takes a critical look at anything I do.


    Yours

    Ian Barwick
  • Nigel J. Andrews at May 2, 2002 at 2:05 am

    On Thu, 2 May 2002, Ian Barwick wrote:

    Tom Lane wrote:
    [snipped]
    My gut feeling is that "\d mytab" should tell you about the same
    table that "select * from mytab" would find. Anything else is
    probably noise to you --
    General consistency with SELECT behaviour sounds right to me.

    I take it temporary tables are going to be included in such a list, since that
    would seem sensible from the SELECT behaviour point of view, and may be even
    also from the user's point of view.

    So, how does one determine the current schema for temporary tables, i.e. what
    name would be in search_path if it wasn't implicitly included? (Just throwing
    ideas around in my head)


    --
    Nigel J. Andrews
    Director

    ---
    Logictree Systems Limited
    Computer Consultants
  • Tom Lane at May 2, 2002 at 3:33 am

    "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
    So, how does one determine the current schema for temporary tables,
    i.e. what name would be in search_path if it wasn't implicitly included?
    The temp schema is pg_temp_nnn where nnn is your BackendId (PROC array
    slot number). AFAIK there isn't any exported way to determine your
    BackendId from an SQL query. Another problem is that the pg_temp
    schema is "lazily evaluated" --- it's not actually attached to and
    cleaned out until you first try to create a temp table in a particular
    session. This seems a clear win from a performance point of view,
    but it makes life even more difficult for queries that are trying to
    determine which pg_class entries are visible in one's search path.

    I have already had occasion to write subroutines that answer the
    question "is this relation (resp. type, function, operator) visible
    in the current search path?" --- where visible means not just that
    its namespace is in the path, but that this object is the frontmost
    entry of its particular name. Perhaps it'd make sense to export these
    routines as SQL functions, along the lines of "relation_is_visible(oid)
    returns bool". Then one could use queries similar to

    select * from pg_class p
    where p.relname like 'match_pattern'
    and relation_is_visible(p.oid);

    to implement a psql command that requires finding tables matching
    an (unqualified) relation-name pattern. The tables found would be
    only those that you could reference with unqualified table names.

    This doesn't yield much insight about cases where the match pattern
    includes a (partial?) schema-name specification, though. If I'm
    allowed to write something like "\z s*.t*" to find tables beginning
    with t in schemas beginning with s, should that include all schemas
    beginning with s? Only those in my search path (probably wrong)?
    Only those that I have USAGE privilege on? Not sure.

    regards, tom lane
  • Hannu Krosing at May 2, 2002 at 6:24 am

    On Thu, 2002-05-02 at 05:33, Tom Lane wrote:
    "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
    So, how does one determine the current schema for temporary tables,
    i.e. what name would be in search_path if it wasn't implicitly included?
    The temp schema is pg_temp_nnn where nnn is your BackendId (PROC array
    slot number). AFAIK there isn't any exported way to determine your
    BackendId from an SQL query.
    The non-portable way on Linux RH 7.2 :
    create function getpid() returns int as '/lib/libc.so.6','getpid' language 'C'; CREATE
    select getpid()
    getpid1
    ---------
    31743
    (1 row)

    I think that useful libc stuff things like this should be put in some
    special schema, initially available to superusers only.

    perhaps LIBC.GETPID()

    ----------
    Hannu
  • Tom Lane at May 2, 2002 at 2:14 pm

    Hannu Krosing writes:
    On Thu, 2002-05-02 at 05:33, Tom Lane wrote:
    The temp schema is pg_temp_nnn where nnn is your BackendId (PROC array
    slot number). AFAIK there isn't any exported way to determine your
    BackendId from an SQL query.
    The non-portable way on Linux RH 7.2 :
    create function getpid() returns int as '/lib/libc.so.6','getpid' language 'C';
    But PID is not BackendId.

    regards, tom lane
  • Hannu Krosing at May 2, 2002 at 2:15 pm

    On Thu, 2002-05-02 at 15:48, Tom Lane wrote:
    Hannu Krosing <hannu@tm.ee> writes:
    On Thu, 2002-05-02 at 05:33, Tom Lane wrote:
    The temp schema is pg_temp_nnn where nnn is your BackendId (PROC array
    slot number). AFAIK there isn't any exported way to determine your
    BackendId from an SQL query.
    The non-portable way on Linux RH 7.2 :
    create function getpid() returns int as '/lib/libc.so.6','getpid' language 'C';
    But PID is not BackendId.
    Are you sure ?

    I was assuming that BackendId was the process id of current backend
    and that's what getpid() returns.


    What is the Backend ID then ?

    Is "PROC array slot number" something internal to postgres ?

    -------------
    Hannu
  • Tom Lane at May 2, 2002 at 3:39 pm

    Hannu Krosing writes:
    Is "PROC array slot number" something internal to postgres ?
    Yes.

    If we used PID then we'd eventually have 64K (or whatever the range of
    PIDs is on your platform) different pg_temp_nnn entries cluttering
    pg_namespace. But we only need MaxBackends different entries at any one
    time. So the correct nnn value is 1..MaxBackends. BackendId meets the
    need perfectly.

    regards, tom lane
  • Hannu Krosing at May 3, 2002 at 2:48 pm

    On Thu, 2002-05-02 at 16:52, Tom Lane wrote:
    Hannu Krosing <hannu@tm.ee> writes:
    Is "PROC array slot number" something internal to postgres ?
    Yes.

    If we used PID then we'd eventually have 64K (or whatever the range of
    PIDs is on your platform) different pg_temp_nnn entries cluttering
    pg_namespace.
    Should they not be cleaned up at backend exit even when they are in
    range 1..MaxBackends ?
    But we only need MaxBackends different entries at any one
    time. So the correct nnn value is 1..MaxBackends. BackendId meets the
    need perfectly.
    ----------
    Hannu
  • Tom Lane at May 3, 2002 at 2:49 pm

    Hannu Krosing writes:
    On Thu, 2002-05-02 at 16:52, Tom Lane wrote:
    If we used PID then we'd eventually have 64K (or whatever the range of
    PIDs is on your platform) different pg_temp_nnn entries cluttering
    pg_namespace.
    Should they not be cleaned up at backend exit even when they are in
    range 1..MaxBackends ?
    Hm. We currently remove the schema contents (ie the temp tables) but
    not the pg_namespace entry itself. Seems like deleting that only to
    have to recreate it would be a waste of cycles.

    regards, tom lane
  • Nigel J. Andrews at May 6, 2002 at 4:28 pm
    Re: BackendID and the schema search path

    Coming back to this subject if I may but only briefly, I hope. How about making
    a slight change to current_schemas() and including an optional argument such
    that something like:

    current_schemas(1)

    returns the complete list of schemas in the search path including the implicit
    temporary space and the pg_catalog (if not already listed obviously), while
    current_schemas() and current_schemas(0) behave as now.

    An alternative is to provide a get_backend_id() call but I don't think there's
    really appropiate and then means the client has to know how to construct the
    name of the temporary schema, which isn't a good idea.

    Having something like this would enable client's like PgAccess to determine the
    complete list of visible objects. Without it it's difficult to see how it is
    possible to include temporary objects in a list of tables and such. In such
    a circumstance I'm inclined to say temporary objects are intermediate items and
    so of no interest to the PgAccess user.


    --
    Nigel J. Andrews
    Director

    ---
    Logictree Systems Limited
    Computer Consultants
  • Tom Lane at May 6, 2002 at 4:38 pm

    "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
    Coming back to this subject if I may but only briefly, I hope. How
    about making a slight change to current_schemas() and including an
    optional argument such that something like:
    current_schemas(1)
    returns the complete list of schemas in the search path including the
    implicit temporary space and the pg_catalog (if not already listed
    obviously), while current_schemas() and current_schemas(0) behave as
    now.
    I don't really care for that syntax, but certainly we could talk about
    providing a version of current_schemas that tells the Whole Truth.
    Having something like this would enable client's like PgAccess to
    determine the complete list of visible objects.
    Well, no, it wouldn't. Say there are multiple tables named foo in
    different namespaces in your search path (eg, a temp table hiding a
    permanent table of the same name). A test like "where current_schemas
    *= relnamespace" won't reflect this correctly.

    I'm suspecting that what we really need is some kind of
    "is_visible_table()" test function, and then you'd do
    select * from pg_class where is_visible_table(oid);
    At least I've not been able to think of a better idea than that.

    regards, tom lane
  • Nigel J. Andrews at May 6, 2002 at 7:34 pm

    On Mon, 6 May 2002, Tom Lane wrote:

    "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
    Coming back to this subject if I may but only briefly, I hope. How
    about making a slight change to current_schemas() and including an
    optional argument such that something like:
    current_schemas(1)
    returns the complete list of schemas in the search path including the
    implicit temporary space and the pg_catalog (if not already listed
    obviously), while current_schemas() and current_schemas(0) behave as
    now.
    I don't really care for that syntax, but certainly we could talk about
    providing a version of current_schemas that tells the Whole Truth.
    Having something like this would enable client's like PgAccess to
    determine the complete list of visible objects.
    Well, no, it wouldn't. Say there are multiple tables named foo in
    different namespaces in your search path (eg, a temp table hiding a
    permanent table of the same name). A test like "where current_schemas
    *= relnamespace" won't reflect this correctly.

    I'm suspecting that what we really need is some kind of
    "is_visible_table()" test function, and then you'd do
    select * from pg_class where is_visible_table(oid);
    At least I've not been able to think of a better idea than that.
    Ok, where I was coming from was the idea of the client, I'm most interested in
    PgAccess at the moment, retrieving the search path and cross referencing that
    against the results of the queries for tables etc.

    I seemed to remember mention of an is_visible() function earlier in the thread
    but that for some reason this would mean a performance hit across the board, or
    at least in many places. However, reviewing my emails I see not such comment
    about performance. Tom originally suggested relation_is_visible(oid) as the
    function.

    I also got it wrong about when the temporary space is emptied. I had been
    thinking it was when the connection terminated. However, I see from the same
    old message that this happens when the first temporary item is created in a
    session. Therefore, my way would be invalid anyway; or would it?


    --
    Nigel J. Andrews
    Director

    ---
    Logictree Systems Limited
    Computer Consultants
  • Tom Lane at May 6, 2002 at 8:09 pm

    "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
    I also got it wrong about when the temporary space is emptied. I had been
    thinking it was when the connection terminated. However, I see from the same
    old message that this happens when the first temporary item is created in a
    session. Therefore, my way would be invalid anyway; or would it?
    It would work as long as the variant form of current_schemas() truly
    reflects the effective search path --- because until you create a
    temporary item, there is no temp schema in the effective path.

    Still, the issue of hiding seems to be a good reason not to code
    clients that way.

    regards, tom lane
  • Nigel J. Andrews at May 6, 2002 at 9:16 pm

    On Mon, 6 May 2002, Nigel J. Andrews wrote:
    On Mon, 6 May 2002, Tom Lane wrote:

    "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
    Coming back to this subject if I may but only briefly, I hope. How
    about making a slight change to current_schemas() and including an
    optional argument such that something like:
    current_schemas(1)
    returns the complete list of schemas in the search path including the
    implicit temporary space and the pg_catalog (if not already listed
    obviously), while current_schemas() and current_schemas(0) behave as
    now.
    I don't really care for that syntax, but certainly we could talk about
    providing a version of current_schemas that tells the Whole Truth.
    Wouldn't such a function just be based on
    backend/catalog/namespace.c:RelnameGetRelid(const char *relname) ?

    I'm suspecting that what we really need is some kind of
    "is_visible_table()" test function, and then you'd do
    select * from pg_class where is_visible_table(oid);
    At least I've not been able to think of a better idea than that.
    [snip]
    For this if we look once again at RelnameGetRelid(relname) in
    backend/catalog/namespace.c wouldn't this is_visible() function simply be a
    wrapper around it? Obviously the parameter [probably] wouldn't be an OID but
    rather a name.

    If I knew which file would be most appropiate for this (utils/adt/name.c?) I'd
    have had a go at making a patch.


    --
    Nigel J. Andrews
    Director

    ---
    Logictree Systems Limited
    Computer Consultants

Related Discussions

People

Translate

site design / logo © 2022 Grokbase