pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Donald Fraser <postgres@kiwi-fraser.net>
Cc: [ADMIN] <pgsql-admin@postgresql.org>
Subject: Re: WITH SYSID feature dropped
Date: Wed, 21 Dec 2005 11:35:55 -0500
Message-ID: <13891.1135182955@sss.pgh.pa.us> (raw)
In-Reply-To: <016201c60649$c6254b70$0264a8c0@demolish1>
References: <011501c60578$47642e40$0264a8c0@demolish1>
	<29845.1135094224@sss.pgh.pa.us>
	<012201c60583$4a832080$0264a8c0@demolish1>
	<525.1135097989@sss.pgh.pa.us>
	<016201c60649$c6254b70$0264a8c0@demolish1>

"Donald Fraser" <postgres@kiwi-fraser.net> writes:
> From: "Tom Lane"
>> keeping the currently assigned OID in the aux table, setting the
>> field to null or zero if the user doesn't currently exist in pg_authid.

> Our "user" table stores information about the user before, during and
> after the user is a postgresql user so that we can effectively hold an
> audit of who has created, edited, deleted what and when. Therefore
> the primary key (SYSID) in this table, is the foreign key in many
> other tables that users can access for record creation and
> modification. Therefore the primary key cannot be null when the
> user is no longer a postgresql user and idealy we don't want it
> changing as this could potentially mean 100's if not 1000's of
> foreign key cascading updates.

I didn't say it should be the primary key.  You can still use a notional
sysid as your pkey, you just need an additional column that records the
current OID (if any) of the user as a PG user.  This is essentially the
reverse of your proposal to add an unused sysid column to pg_authid ...

			regards, tom lane



view thread (13+ messages)  latest in thread

Message-ID: <13891.1135182955@sss.pgh.pa.us>
Permalink:  ../13891.1135182955@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/13891.1135182955@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, postgres@kiwi-fraser.net
  Subject: Re: WITH SYSID feature dropped
  In-Reply-To: <13891.1135182955@sss.pgh.pa.us>
  References: <011501c60578$47642e40$0264a8c0@demolish1> <29845.1135094224@sss.pgh.pa.us> <012201c60583$4a832080$0264a8c0@demolish1> <525.1135097989@sss.pgh.pa.us> <016201c60649$c6254b70$0264a8c0@demolish1> <13891.1135182955@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox