Thread: Create GLOBAL TABLE

Marius Cornea wrote:
> How can I make a global table (like pg_users, pg_shaddow) ?

You can't afaik. There are three things you can do though.

1. Anything you put into pg_template1 gets created in each new database
(unless you choose a different template when creating).
2. The dblink() module in contrib/ lets you connect two databases (on
the same or different clusters)
3. Use separate schemas rather than separate databases to split your data.

That any help?

--
   Richard Huxton
   Archonet Ltd

1.The sintax for create table is :
    CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
  { column_name data_type [ DEFAULT default_expr ] [ column_constraint [
... ] ] ...

What mean the parameter GLOBAL|LOCAL ??

2. in pg_class it is a field "relisshared" how can i use it ?
3.1 how can I use BKI script ?,
3.2 it is posible to modify postgres.bki to create another table with initdb
script ?
 like: "create bootstrap shared_relation .." ?

----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Marius Cornea" <marius@tns-agb.ro>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, October 19, 2005 3:58 PM
Subject: Re: [GENERAL] Create GLOBAL TABLE


> Marius Cornea wrote:
>> How can I make a global table (like pg_users, pg_shaddow) ?
>
> You can't afaik. There are three things you can do though.
>
> 1. Anything you put into pg_template1 gets created in each new database
> (unless you choose a different template when creating).
> 2. The dblink() module in contrib/ lets you connect two databases (on the
> same or different clusters)
> 3. Use separate schemas rather than separate databases to split your data.
>
> That any help?
>
> --
>   Richard Huxton
>   Archonet Ltd
>

Marius Cornea wrote:
> 1.The sintax for create table is :
>    CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
>  { column_name data_type [ DEFAULT default_expr ] [ column_constraint [
> ... ] ] ...
>
> What mean the parameter GLOBAL|LOCAL ??

http://www.postgresql.org/docs/8.0/static/sql-createtable.html

"Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP.
This makes no difference in PostgreSQL, but see Compatibility."

> 2. in pg_class it is a field "relisshared" how can i use it ?
> 3.1 how can I use BKI script ?,
> 3.2 it is posible to modify postgres.bki to create another table with
> initdb script ?
> like: "create bootstrap shared_relation .." ?

It almost certainly *is* possible to add your own system table. However,
that's probably not something to do without taking time to make sure you
know what you're doing. It will also mean your PostgreSQL installation
will be different from everyone else's.

First thing to do is search the mailing list archives and see if anyone
else has done the same.
   http://archives.postgresql.org/

Then, I'd see how an existing table was added (say pg_authid which I
think is new in 8.1 to handle roles). There is a new cvs browser from
command-prompt - details here.
   http://archives.postgresql.org/pgsql-general/2005-10/msg00852.php

Not sure if you might want to look at whether you need to allocate
another fixed OID for your new shared table too.

Check it looks possible and then post details of your plan on the
hackers list and see what they say.

--
   Richard Huxton
   Archonet Ltd

I found a solution to make a global table:
  Create a normal table, set the rellisshared to true (from pg_class),
and move the file <relfilenode> from current database folder to folder
global.
I don't know if cascase updates is running, if I link this table to
multi databases.

Thx,
Marius

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: 20 October 2005 10:17
To: Marius Cornea
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Create GLOBAL TABLE

Marius Cornea wrote:
> 1.The sintax for create table is :
>    CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
(
>  { column_name data_type [ DEFAULT default_expr ] [ column_constraint
[
> ... ] ] ...
>
> What mean the parameter GLOBAL|LOCAL ??

http://www.postgresql.org/docs/8.0/static/sql-createtable.html

"Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP.
This makes no difference in PostgreSQL, but see Compatibility."

> 2. in pg_class it is a field "relisshared" how can i use it ?
> 3.1 how can I use BKI script ?,
> 3.2 it is posible to modify postgres.bki to create another table with
> initdb script ?
> like: "create bootstrap shared_relation .." ?

It almost certainly *is* possible to add your own system table. However,

that's probably not something to do without taking time to make sure you

know what you're doing. It will also mean your PostgreSQL installation
will be different from everyone else's.

First thing to do is search the mailing list archives and see if anyone
else has done the same.
   http://archives.postgresql.org/

Then, I'd see how an existing table was added (say pg_authid which I
think is new in 8.1 to handle roles). There is a new cvs browser from
command-prompt - details here.
   http://archives.postgresql.org/pgsql-general/2005-10/msg00852.php

Not sure if you might want to look at whether you need to allocate
another fixed OID for your new shared table too.

Check it looks possible and then post details of your plan on the
hackers list and see what they say.

--
   Richard Huxton
   Archonet Ltd