Re: custom session variables? - Mailing list pgsql-general

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