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

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/curre...ions-info.html)
for that task - that seems much less error-prone then using an arbitrary
number to reference a given database role ...


Stefan

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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/curre...ions-info.html)
> for that task - that seems much less error-prone then using an arbitrary
> number to reference a given database role ...

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings