Thread: problem with custom_variable_classes
Hi,
I have a pl/pgSQL function[1] to calculate row numbers (based on [2]).
It uses a custom variable. This was working earlier, but is breaking now
saying it can't recognise the variable. The custom_variable_classes is
set in the postgresql.conf.
pgdb001=> select rownum('') from generate_series(1,10);
ERROR: unrecognized configuration parameter "olap.rownum_name"
pgdb001=> show custom_variable_classes;
custom_variable_classes
-------------------------
olap
(1 row)
I am using PostgreSQL 8.3.3 on CentOS 5.2 x64.
TIA,
Malcolm
[1]
CREATE OR REPLACE FUNCTION rownum(in_code TEXT)
RETURNS INT4
LANGUAGE plpgsql
as $BODY$
DECLARE
current_id TEXT;
current_rownum INT4;
settings_id TEXT;
BEGIN
current_id := statement_timestamp()::TEXT || in_code;
settings_id := current_setting('olap.rownum_name');
IF settings_id IS DISTINCT FROM current_id THEN
PERFORM set_config('olap.rownum_name',
current_id::TEXT, false);
current_rownum := 0;
ELSE
current_rownum :=
current_setting('olap.rownum_count')::INT4;
END IF;
current_rownum := current_rownum + 1;
PERFORM set_config('olap.rownum_count', current_rownum::TEXT,
false);
RETURN current_rownum;
END;
$BODY$;
[2]
<http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/>
On Wed, Sep 24, 2008 at 12:15:41PM -0400, Malcolm Studd wrote:
> I have a pl/pgSQL function[1] to calculate row numbers (based on [2]).
> It uses a custom variable. This was working earlier, but is breaking now
> saying it can't recognise the variable. The custom_variable_classes is
> set in the postgresql.conf.
> pgdb001=> select rownum('') from generate_series(1,10);
> ERROR: unrecognized configuration parameter "olap.rownum_name"
> pgdb001=> show custom_variable_classes;
> custom_variable_classes
> -------------------------
> olap
> (1 row)
> settings_id := current_setting('olap.rownum_name');
it looks like a bug to me.
it did work in 8.2, and it doesn't in 8.3, or in 8.4.
while manual in 8.4 still states:
"When custom_variable_classes is set, the server will accept definitions of arbitrary variables within each specified
class.".
so it should work, but it doesn't:
# show custom_variable_classes ;
custom_variable_classes
-------------------------
depesz
(1 row)
# select current_setting('depesz.xxx');
ERROR: unrecognized configuration parameter "depesz.xxx"
of course i still can set a value, and then get it:
# select set_config('depesz.xxx', 'xx', false);
set_config
------------
xx
(1 row)
# select current_setting('depesz.xxx');
current_setting
-----------------
xx
(1 row)
but it means that there is currently no way to check if there is value in this
- i.e. if it has been initialized.
i would suggest to return "NULL" when calling current_setting on unknown variable.
this is not how it worked in 8.2 (it returned empty string), but NULL is (in my opinion) better choice.
strangely - name of "current_setting" function nor "custom_variable_classes"
doesn't show in any release docs, which makes the change somewhat mysterious.
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
On Thu, Sep 25, 2008 at 12:38 AM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> but it means that there is currently no way to check if there is value in this
> - i.e. if it has been initialized.
now you have to initialize this variable once per session before usage:
SELECT set_config('olap.rownum_name', false);
Regards,
Taras Kopets
hubert depesz lubaczewski <depesz@depesz.com> writes:
> while manual in 8.4 still states:
> "When custom_variable_classes is set, the server will accept definitions of arbitrary variables within each specified
class.".
It will accept *definitions*, yes. I can't imagine why you'd think
it's a good idea to not throw error when asked for an unknown variable's
value.
The whole custom-variable thing is being abused far beyond what the
facility was intended for, anyway. Rather than allowing variables to
spring into existence like magic, what we should have is some facility
for letting session-local variables be *declared*, complete with type
(int/real/string) and other info as needed. See the archives --- this
was discussed not too long ago.
regards, tom lane
On Wed, Sep 24, 2008 at 07:33:27PM -0400, Tom Lane wrote: > It will accept *definitions*, yes. I can't imagine why you'd think > it's a good idea to not throw error when asked for an unknown variable's > value. because this is how it worked. i'm not saying it was good. it worked that way, and introducing such change made some code (rownum in this example) not working. i understand that postgresql is more about correctness than end-user niceness, which has some obvious benefits, but i think - in such cases at least a one-line information in release docs wouldn't be out of place. > The whole custom-variable thing is being abused far beyond what the > facility was intended for, anyway. i think it's great idea. somebody thought about cool thing for modules, other people found other uses for it. right now they are being effectively punished for creativeness (unfortunatelly i can't say it is me, as i got the variable trick from somebody else). best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
Taras Kopets wrote:
> now you have to initialize this variable once per session before usage:
>
> SELECT set_config('olap.rownum_name', false);
Ok, thanks. That has fixed my problem.
Malcolm