Обсуждение: custom session variables?

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

Hello

You could just use temporary tables like:

 BEGIN;
 CREATE TEMPORARY TABLE csid (i int);
    -- somehow write the ID you want into that table
     -- and then...
 INSERT INTO other_table (changeset_ids, msg) VALUES
   ((SELECT i FROM csid), 'Some log message');
 COMMIT;

When inserting a new changeset, you fill the temporary table
afterwards with curval('changeset_id_seq'). If you just want
to add more referring lines, you seem to know the ID already
can can just insert it. In any case, the following INSERTs into
the "other tables" will look exactly the same, which seem to
be your goal.

bye,

-christian-


Am Wed, 24 Apr 2013 16:17:58 -0700
schrieb Darren Duncan <darren@darrenduncan.net>:

> 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
>
>

Thank you for that.

I had actually thought of this, but it seemed to me that using a temporary table
was a heavy-handed approach and that a temporary scalar variable would be more
efficient or less verbose to use.

It is *a* solution certainly, and potentially a better one than the url I
pointed to, but I was hoping for something less bulky.

Granted, I realize that sequence generators are basically small tables like this
behind the scenes, but is there any appreciable overhead of creating and
dropping a temporary table for every session?

-- Darren Duncan

On 2013.04.24 5:07 PM, Christian Hammers wrote:
> Hello
>
> You could just use temporary tables like:
>
>   BEGIN;
>   CREATE TEMPORARY TABLE csid (i int);
>     -- somehow write the ID you want into that table
>       -- and then...
>   INSERT INTO other_table (changeset_ids, msg) VALUES
>     ((SELECT i FROM csid), 'Some log message');
>   COMMIT;
>
> When inserting a new changeset, you fill the temporary table
> afterwards with curval('changeset_id_seq'). If you just want
> to add more referring lines, you seem to know the ID already
> can can just insert it. In any case, the following INSERTs into
> the "other tables" will look exactly the same, which seem to
> be your goal.
>
> bye,
>
> -christian-

On 2013.04.24 7:16 PM, � wrote:
> Maybe you must see this extension [1] ;-)
>
> [1] http://pgxn.org/dist/session_variables/
>
> Fabrízio de Royes Mello

Thanks for your response.

====

/*
  * Author: Fabrízio de Royes Mello
  * Created at: Thu Oct 27 14:37:36 -0200 2011
  *
  */

CREATE FUNCTION set_value(TEXT, TEXT) RETURNS void AS $$
BEGIN
   PERFORM set_config('session_variables.'||$1, $2, false);
   RETURN;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION set_value(TEXT, TEXT) IS
'Create/Assign value to a new/existing session variable';

SET check_function_bodies TO OFF;
CREATE FUNCTION get_value(TEXT) RETURNS TEXT AS $$
   SELECT current_setting('session_variables.'||$1);
$$ LANGUAGE sql;
COMMENT ON FUNCTION get_value(TEXT) IS
'Returns the value of session variable passed as a parameter';

====

So, ok, basically the same as
http://frefo.blogspot.ca/2009/04/session-variables-in-postgresql.html which I
initially pointed to.

I'll take that as several people endorsing set_config()/current_setting() as a
preferred way to do this.

The main limitation seems to be that those builtins just store and return text
values, but a little casting on store/fetch should take care of that.

The temporary table approach wouldn't need casting in contrast.

Well, I can choose either then as the circumstances warrant.

Thanks for the feedback, I now consider my question to be answered.

-- Darren Duncan