Hello,
I would like to have one or more session-scoped global variables that are
useable in a similar way to sequence generators, via analogies to
setval()+currval().
Here's a (simplified) scenario ...
Say that for auditing purposes all regular database tables have a changeset_id
column, which is a foreign key into a changesets table that has extra columns
like when_occurred and who_did_it and purpose_of_changes etc. There is a
sequence generator changeset_id_gen that is typically used to generate the
changeset_id values.
During typical day to day use, when a set of data manipulation work is done to
enact some application task, a changesets record is added with a newly generated
changeset_id, and that changeset_id then used in the other records
added/updated/deleted (there are also audit/history tables) to associate
everything that was done as a logical unit for some task.
So in the changesets table we have this as its pk:
changeset_id integer not null default nextval('changeset_id_gen'::regclass)
... and in the other tables we have this as a non-pk field:
changeset_id integer not null default currval('changeset_id_gen'::regclass)
... or there may also be a trigger to similar effect of the latter, so it is
changed for a record update too.
Now I understand that within a particular database session currval will fail if
nextval or setval weren't called on that sequence generator yet. And so I
depend on this behavior to enforce a general business rule that a changesets
record has to be inserted before other changes in the current session.
However, under some circumstances, we may want alternately to associate some
regular changes with a prior changesets record, or otherwise with some
changesets record whose changeset_id didn't come from the sequence generator.
To make the database simpler or cut down on verbosity, I would like in those
cases to effectively setval() changeset_id_gen at the start of that changeset to
some explicit value, so subsequent changes in that session can just use that
value instead of some newly generated one.
Now here's the main point of this message ...
When I effectively setval() in the session, I want that change to only be
visible in the session and have no effect outside of it, such as affecting what
nextval() produces in other sessions.
It seemed to me that the proper way to do this would be to have some other
session scope variable, say changeset_id_to_use, that is an intermediary between
the sequence generator and nearly all the code using it.
So at the start of a session, changeset_id_to_use would be manually set by the
application, either from nextval(changeset_id_gen) or from an explicit value,
and then the table default defs or triggers would read changeset_id_to_use
rather than reading currval(changeset_id_gen).
The changeset_id_to_use should start off null/missing at the start of the
session so code that uses it will fail per business rules without it being set
first.
I would like to know the proper/best way to declare and access the
aforementioned changeset_id_to_use variable?
I found a 2009 blog post
http://frefo.blogspot.ca/2009/04/session-variables-in-postgresql.html which
looks relevant to what I want to do, but that looks more like abuse of the
system rather than using it as intended, though I could be wrong. Is there are
better solution than that or what should I be doing?
Thank you in advance.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
