Thread: subtable <-> bigtable

I've a very big table that is read very frequently and I want to split
this table in more subtables with different permissions for
insert/update/delete.  I need to keep the big table too, cause the
reading application can only deal with one table.

So I am thinking about generating a set of update,insert,delete-rules
for each of the 'subtables' that will update,insert,delete the
'maintable'.
 I'm not worried about the needed time, cause write access is very
seldom, but I'm needed about the effort to implement this solution.
First I've to add a new oid-column to establish the relation between
the subtables and the maintable, then I've to create three long rules
for each subtable with long insert,update,delete-commands and in fact
I dont only have 1 table to split but a load.

Is there any construct I can use to form a 'virtual table' that
contains all the subtables or any other method to make my work easier.

I guess my problem is not so uncommon ..

thnx,
peter

ps: and I'd like to win the price for the worth subject in day, but I
really didnt know how to deal with it ...

--
mag. peter pilsl

phone: +43 676 3574035
fax  : +43 676 3546512
email: pilsl@goldfisch.at
sms  : pilsl@max.mail.at

pgp-key available

On Sun, Dec 16, 2001 at 02:54:43AM +0100, Peter Pilsl wrote:
> I've a very big table that is read very frequently and I want to split
> this table in more subtables with different permissions for
> insert/update/delete.  I need to keep the big table too, cause the
> reading application can only deal with one table.

Sounds like you might be interested in inheritence. Only it works the other
way round. Each select on the main table is equivalent to a union of selects
on all the subtables.

Like so:

create table maintable ( yadda );

create subtable1 () inherits (maintable);
create subtable2 () inherits (maintable);

insert into subtable1 values ( blah );
insert into subtable2 values ( blah );

Now, selecting either subtable1 one or suntable2 will produce one row each,
but selecting from maintable will produce both rows.

> So I am thinking about generating a set of update,insert,delete-rules
> for each of the 'subtables' that will update,insert,delete the
> 'maintable'.
>  I'm not worried about the needed time, cause write access is very
> seldom, but I'm needed about the effort to implement this solution.
> First I've to add a new oid-column to establish the relation between
> the subtables and the maintable, then I've to create three long rules
> for each subtable with long insert,update,delete-commands and in fact
> I dont only have 1 table to split but a load.

Although this is probably doable in the end, the solution given above is
certainly much easier to code, though I give guarentees about performance.
Oh, and foreign keys and unique indicies won't work on the table either.

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.

Just wanted to let you guys know of a web page problem. If I goto one of the
mailing list archive pages, for example,
http://archives.postgresql.org/pgsql-announce/ and use the form at the top
to do a search, I get an error message. However, if I click on the link
"Search The Archives", which goes to http://fts.postgresql.org/db/mw/, for
the 4th US mirror, then the search form on that page works. I think it's
this way for all the US mirrors.

On Sat, 2001-12-15 at 20:54, Peter Pilsl wrote:
> I've a very big table that is read very frequently and I want to split
> this table in more subtables with different permissions for
> insert/update/delete.  I need to keep the big table too, cause the
> reading application can only deal with one table.

So you want to have different permissions depending on what sort of
data, right?

> Is there any construct I can use to form a 'virtual table' that
> contains all the subtables or any other method to make my work easier.

Yep, and they're called VIEWs.  Personally, if the data models well to a
single table, then I'd stick with a single table design.  Use VIEWs to
break up access and write RULEs to handle INSERT, UPDATE and DELETES.

--
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F
613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me

This seems to be the one I was looking for !

I only have some concerns about security, cause I must give full
access to the users to the maintable to allow all the rules be
performed: insert, update, delete anyway and also select cause the
update-command would fail otherwise (see below).
 So noone prevents my user to open the maintable via odcb and
ms-access (which is the way they connect) and mess it up - especially
mess up parts that are dedicated to other users.

thnx,
peter

ps: t is the table and t_v1 is the view.

\dp
 t                     | {"=","peter=arwR","test2=aw"}
 t_v1                  | {"=","peter=arwR","test2=arwR"}

\d t
           Table "t"
 Attribute |  Type   | Modifier
-----------+---------+----------
 num       | integer |
 rank      | integer |
 x         | integer |

CREATE RULE update_t_v1 as on update to t_v1 do instead update t set num = new.num, rank=new.rank,x=new.x where
num=old.num; 

UPDATE t_v1 set num=10 where num=11;
ERROR:  t: Permission denied.



On Sat, Dec 15, 2001 at 11:42:56PM -0500, Andrew G. Hammond wrote:
>
> Yep, and they're called VIEWs.  Personally, if the data models well to a
> single table, then I'd stick with a single table design.  Use VIEWs to
> break up access and write RULEs to handle INSERT, UPDATE and DELETES.
>



--
mag. peter pilsl

phone: +43 676 3574035
fax  : +43 676 3546512
email: pilsl@goldfisch.at
sms  : pilsl@max.mail.at

pgp-key available