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

WITH SYSID dropped

From: "Dr(dot) Ernst Molitor" <molitor(at)uni-bonn(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: WITH SYSID dropped
Date: 2006-11-05 12:39:05
Message-ID: 200611051339.05471.molitor@uni-bonn.de (view raw or flat)
Thread:
Lists: pgsql-hackers
Dear PostgreSQL gurus, 

having read through the thread on the topic cited above, I still think the change - however well-founded it may be - carries a problem: That of an upgrade on installations that relied on the statement. 

Currently, I'm preparing to switch a PostgreSQL-8.0.3-installation to the current PostgreSQL version. The installation makes use of timetravel and of a one-liner to access the pg_users.usesysid field to keep a record about who has changed what and when (which, here in Germany, is a legal requirement if you deal with medical data). With a one-liner, which is but a wrapper around PostgreSQL's internal GetUserId function, it has been nothing but creating a function "current_userid" and adding a trigger to each table that should be able to record the user who commited or changed the row. Obviously, the whole job could be performed at database level. 

The procedure still works, but due to the dropping of "WITH SYSID", moving the data to a new PostgreSQL installation causes headaches. After all, the access to the information about who has changed what is expected to stay. 

Any hint (short of doing "update <table_x> set usersno=<new value> where usersno=<old value>" for somewhat above 500 users and a chore of tables) on how to proceed??

Best regards, 

Ernst

Responses

pgsql-hackers by date

Next:From: Stefan KaltenbrunnerDate: 2006-11-05 13:52:59
Subject: Re: WITH SYSID dropped
Previous:From: Heikki LinnakangasDate: 2006-11-05 09:52:36
Subject: Re: Proposal: vacuum and autovacuum parameters to control freezing

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