Обсуждение: 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

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