Thread: Referencing columns from system tables possible?

Hello pgsql-general,

When trying to create a table

CREATE TABLE sessions (
 id serial PRIMARY KEY,
 procpid int4 REFERENCES pg_listener(listenerpid) ON DELETE CASCADE
 );

I get a warning saying 'relation "pg_listener" is a system catalog'

Is it unreasonable and/or impossible to do this?

--
-Boris

On Fri, Nov 07, 2003 at 06:20:32PM -0800, Boris Popov wrote:

> CREATE TABLE sessions (
>  id serial PRIMARY KEY,
>  procpid int4 REFERENCES pg_listener(listenerpid) ON DELETE CASCADE
>  );
>
> I get a warning saying 'relation "pg_listener" is a system catalog'
>
> Is it unreasonable and/or impossible to do this?

IMHO it's both.  It's impossible because system catalogs don't have
trigger checking and other stuff, so you can't really have foreign
keys.  To do so would make the whole system much slower.

It's also unreasonable because you shouldn't be relying on such a
system-specific way of representing user data.  I remember what you were
trying to achieve; I don't have any ideas to give to you, but I can tell
you this is not what you are looking for.

(I don't remember why you rejected the idea of having a cron job to
delete entries belonging to expired sessions ...)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Los dioses no protegen a los insensatos.  Éstos reciben protección de
otros insensatos mejor dotados" (Luis Wu, Mundo Anillo)

Hello Alvaro,

Friday, November 7, 2003, 7:25:33 PM, you wrote:

AH> On Fri, Nov 07, 2003 at 06:20:32PM -0800, Boris Popov wrote:

>> CREATE TABLE sessions (
>>  id serial PRIMARY KEY,
>>  procpid int4 REFERENCES pg_listener(listenerpid) ON DELETE CASCADE
>>  );
>>
>> I get a warning saying 'relation "pg_listener" is a system catalog'
>>
>> Is it unreasonable and/or impossible to do this?

AH> IMHO it's both.  It's impossible because system catalogs don't have
AH> trigger checking and other stuff, so you can't really have foreign
AH> keys.  To do so would make the whole system much slower.

AH> It's also unreasonable because you shouldn't be relying on such a
AH> system-specific way of representing user data.  I remember what you were
AH> trying to achieve; I don't have any ideas to give to you, but I can tell
AH> you this is not what you are looking for.

AH> (I don't remember why you rejected the idea of having a cron job to
AH> delete entries belonging to expired sessions ...)

Reason I'm trying to find a different solution is to avoid
implementing application heartbeat that updates the timestamp. On one
hand interval value has to be low to keep the system as close as
possible to real-time, yet it has to be rare enough to avoid
unnessecary load. If there was some way I could beat the backend into
maintaining the list automatically it'd be so much greater. It does it
already in pg_listener, this can't be hard to make available to
general public.

--
-Boris

On Fri, Nov 07, 2003 at 07:33:47PM -0800, Boris Popov wrote:

Boris,

> AH> (I don't remember why you rejected the idea of having a cron job to
> AH> delete entries belonging to expired sessions ...)
>
> Reason I'm trying to find a different solution is to avoid
> implementing application heartbeat that updates the timestamp.

I don't think there's another way because you'd need the "trigger on
disconnect" or some such that doesn't exist (yet).

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo" (Jaime Salinas)

Why not place the pid of the process into your session and set up a cronjob to look at pg_listner and delete any pid's from the session file that have gone away?  Only down side is if you recycle pid's really quickly.

Alvaro Herrera wrote:

On Fri, Nov 07, 2003 at 07:33:47PM -0800, Boris Popov wrote:

Boris,
 

AH> (I don't remember why you rejected the idea of having a cron job to
AH> delete entries belonging to expired sessions ...)

Reason I'm trying to find a different solution is to avoid
implementing application heartbeat that updates the timestamp.   

I don't think there's another way because you'd need the "trigger on
disconnect" or some such that doesn't exist (yet).