custom session variables?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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-

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Apr 24, 2013 at 9:47 PM, Darren Duncan <darren@darrenduncan.net>wrote:

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?

Hi,

Maybe you must see this extension [1]http://pgxn.org/dist/session_variables/ ;-)

[1]: http://pgxn.org/dist/session_variables/

<http://pgxn.org/dist/session_variables/&gt;--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Apr 25, 2013 at 1:17 AM, Darren Duncan <darren@darrenduncan.net>wrote:

On 2013.04.24 7:16 PM, � wrote:

Maybe you must see this extension [1] ;-)

[1] http://pgxn.org/dist/session_**variables/&lt;http://pgxn.org/dist/session_variables/&gt;

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<http://frefo.blogspot.ca/2009/04/session-variables-in-postgresql.html&gt;which I initially pointed to.

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

I use this feature to store global session variables for a long time... In
my first implementation of this feature I used temp tables, but this caused
catalog bloat. So I had to change this strategy using
set_config/current_setting functions and it has worked fine since then.

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.

To solve this you can extend this extension... ;-)

CREATE FUNCTION get_value_as_integer(TEXT) RETURNS INTEGER AS $$
SELECT CAST(get_value($1) AS INTEGER);
$$ LANGUAGE sql;

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL