Thread: oid dependency question...
Hi all... I just jumped on the postgres boat for a new project and I have a sort of 'design / theory' question. I am building a web application and I want to store pieces of the output in the database as large objects so that I don't have to recalculate them (I am predicting a sizeable CPU overhead for generating the pages...). So, lets say I render page X which contains a list of 10 items that were queried out of a bunch of different tables. Now I take that rendering and store it in my cache table... this is fine, but if any of those 10 items are changed I want the cache entry to automatically expire so that the page is recalculated next time it is requested. In another case a page will be dependent on the table itself (such as the insertion of a new record...). So far, I have learned that every row, table, etc in postgres can be associated to a globally unique object id (oid), correct? I tried to explore the system tables, pg_, etc but I could not find a table which actually just contained the oids, so I assume that this is hidden somewhere on a layer below the database... I want to know what the best method to implement this system will be... I am thinking triggers / store procedures? but where do I store them... on each table that may have a dependency? any advice would be appreciated... later, Andy
On Fri, Apr 07, 2000 at 01:18:47PM -0700, Andrew Schmeder wrote:
...
> So far, I have learned that every row, table, etc in postgres can be associated
> to a globally unique object id (oid), correct? I tried to explore the system
> tables, pg_, etc but I could not find a table which actually just contained the
> oids, so I assume that this is hidden somewhere on a layer below the
> database...
Maybe this is useful for your quest for oids:
regression=# \d test
Table "test"
Attribute | Type | Modifier
-----------+-----------+----------
d | timestamp |
regression=# select * from test;
d
-----------
-infinity
infinity
(2 rows)
regression=# select oid,* from test;
oid | d
--------+-----------
148169 | -infinity
148170 | infinity
(2 rows)
So, if you use "oid" specifically, it appears.
> I want to know what the best method to implement this system will
> be... I am thinking triggers / store procedures? but where do I store them...
> on each table that may have a dependency?
>
> any advice would be appreciated...
I don't know much about these things - my guess would be triggers in the 10
tables which on {update,insert,delete} deleted your cached page, but hopefully
someone more knowledgeable will answer this bit.
Cheers,
Patrick
I'd recommend creating a table that holds the info on all your cached pages (the link to the large object, and all of the product #'s of the items used to generate the page) and then put an update/delete trigger on your main products table that does a DELETE FROM cache_toc WHERE products.product_id = cached_item
While OID's will work, you may run into problems when dumping/restoring your database (pgdump doesn't dump OID's by default) so if you can avoid them by using another field, you are probably better off. An OID is just an unsigned int4 anyway.
At 05:18 PM 4/7/00, Andrew Schmeder wrote:
While OID's will work, you may run into problems when dumping/restoring your database (pgdump doesn't dump OID's by default) so if you can avoid them by using another field, you are probably better off. An OID is just an unsigned int4 anyway.
At 05:18 PM 4/7/00, Andrew Schmeder wrote:
Hi all... I just jumped on the postgres boat for a new project and I have a
sort of 'design / theory' question.
I am building a web application and I want to store pieces of the output in the
database as large objects so that I don't have to recalculate them (I am
predicting a sizeable CPU overhead for generating the pages...). So, lets say
I render page X which contains a list of 10 items that were queried out of a
bunch of different tables. Now I take that rendering and store it in my cache
table... this is fine, but if any of those 10 items are changed I want the
cache entry to automatically expire so that the page is recalculated next time
it is requested. In another case a page will be dependent on the table itself
(such as the insertion of a new record...).
So far, I have learned that every row, table, etc in postgres can be associated
to a globally unique object id (oid), correct? I tried to explore the system
tables, pg_, etc but I could not find a table which actually just contained the
oids, so I assume that this is hidden somewhere on a layer below the
database... I want to know what the best method to implement this system will
be... I am thinking triggers / store procedures? but where do I store them...
on each table that may have a dependency?
any advice would be appreciated...
later,
Andy
Andrew Schmeder writes: > So far, I have learned that every row, table, etc in postgres can be > associated to a globally unique object id (oid), correct? *is* associated > I tried to explore the system tables, pg_, etc but I could not find a > table which actually just contained the oids, so I assume that this is > hidden somewhere on a layer below the database... Every row in the database gets a unique oid upon creation, but there is no "oid catalog" as such, the oids are just there. On the user's side they do not have much practical value unless you choose to use them as foreign keys (but there is rarely a reason to really do that). About the rest of you project, the way I see it you probably want to detect changes to your data (whereever that comes from) externally and update the database from there. Triggers and such can help keeping everything in sync but your milage will most certainly vary. I am not sure to what extend large objects are supported by all parts of the game. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden