Thanks for the reply Tom.
As far as I know, the TID of a row can change over time (e.g. when the
row is updated). Isn't it so? If so, it wont solve my problem.
It says here (http://www.postgresql.org/docs/9.0/static/ddl-system-columns.html)
that
"The OID, or even better a user-defined serial number, should be used
to identify logical rows.", so I thought I can use OID. You are right
as OIDs are 32-bit ints, so they will wrap-around at some point.
The reason why I don't want to use a SERIAL user-column is that I want
the process to be transparent to the user.
What I want accomplished is the following. I want to trace all
accesses (read/update/insert) to rows. If a row was accessed twice, it
should appear twice in the trace. Later (offline), I want to analyze
the trace to find some patterns, and after deducing some results from
the pattern, I should be able to access the original rows in the
database. For example, if my pattern-analysis says that row x is
"best", I should be able to read x from the DB. Hence, a tuple should
uniquely be identifiable (have same ID) in the DB and in the trace.
What would you suggest for such a scenario?
Thanks for the help.
On Thu, Sep 1, 2011 at 10:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Fazool <fazoolmein@gmail.com> writes:
>> I am implementing some functionality into Postgresql, where I want to
>> track which row was accessed by a user query. I am implementing the
>> functionality inside Postgres, so that there are no changes required
>> on client side (e.g. re-writing queries).
>> Rows are identified by OIDs, and I have set default_with_oids=true, so
>> that all tables are created with OIDs.
>
> It's a serious, serious error to suppose that OIDs are adequate
> identifiers for rows in user tables.
>
> We use OIDs to identify rows in system catalogs. We can get away with
> that, more or less, because (a) system catalogs are not likely to
> contain billions of rows, and (b) we place a unique index on OID on
> every system catalog that has OIDs. Neither of these statements is
> very tenable for user tables.
>
> You haven't said anything about what it is you actually need to
> accomplish here, but can you use TIDs as row identifiers? What's
> the required lifetime of the identifiers?
>
> regards, tom lane
>