Skip site navigation (1) Skip section navigation (2)

Re: Getting the OID inside the executor

From: Fazool <fazoolmein(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Getting the OID inside the executor
Date: 2011-09-02 05:58:23
Message-ID: CAO+OJGAS2HyThb2=hxMj2yN989qbzCCGegpA5NUfgExLrB-zdQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Fazool <fazoolmein(at)gmail(dot)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
>

In response to

pgsql-hackers by date

Next:From: Jeremy DrakeDate: 2011-09-02 06:48:31
Subject: Re: pgsql: Remove "fmgr.h" include in cube contrib --- caused crash on a Ge
Previous:From: Tom LaneDate: 2011-09-02 05:03:37
Subject: Re: Getting the OID inside the executor

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group