Thread: WITH SYSID dropped
<p>Dear PostgreSQL gurus, <p><p>having read through the thread on the topic cited above, I still think the change - howeverwell-founded it may be - carries a problem: That of an upgrade on installations that relied on the statement. <p><p>Currently,I'm preparing to switch a PostgreSQL-8.0.3-installation to the current PostgreSQL version. The installationmakes use of timetravel and of a one-liner to access the pg_users.usesysid field to keep a record about who haschanged 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 changedthe row. Obviously, the whole job could be performed at database level. <p><p>The procedure still works, but due tothe dropping of "WITH SYSID", moving the data to a new PostgreSQL installation causes headaches. After all, the accessto the information about who has changed what is expected to stay. <p><p>Any hint (short of doing "update <table_x>set usersno=<new value> where usersno=<old value>" for somewhat above 500 users and a chore oftables) on how to proceed??<p><p>Best regards, <p><p>Ernst
Dr. Ernst Molitor wrote: > 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. maybe I'm missing something simple - but why are you not using current_user/session_user (http://www.postgresql.org/docs/current/interactive/functions-info.html) for that task - that seems much less error-prone then using an arbitrary number to reference a given database role ... Stefan
Dear Stefan Kaltenbrunner, dear PostgreSQL gurus, current_user returns a "name" (currently, 64 bytes of data); I felt it would be contrary to good database practice to repetitiously include (up to) 64 bytes of data in each and every row of each and every table instead of making use of what relational databases are about and storing just enough data (e.g., the usesysid) to make the relation clear. My assumption was that the PostgreSQL extension to the standard language (the "WITH SYSID" option of the CREATE USER command, which in itself is still included in the grammar to allow reading dumps created with pre-8.2.x versions of PostgreSQL) was here to stay. I stand up to my wrong decision - it's just my fault - but still hope for a hint on how to minimize its consequences on my applications (screenshots of one of them are at http://mibi03.meb.uni-bonn.de/demo/en/ ). Maybe I face no better option than to store the old database's usesysid's and the corresponding user names in a table before migrating to 8.2.x and live with a break (or, for future updates, with some breaks ...) in the usename-usesysid-relation. Best wishes and regards, Ernst On Sunday, November 5th, 2006, 14:52 Stefan Kaltenbrunner wrote > Dr. Ernst Molitor wrote: > > Dear PostgreSQL gurus, > > > > > > 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. > > maybe I'm missing something simple - but why are you not using > current_user/session_user > (http://www.postgresql.org/docs/current/interactive/functions-info.html) > for that task - that seems much less error-prone then using an arbitrary > number to reference a given database role ...