postgresql function and trigger execute select current_setting into integer variable causes error-CodePudding

Postgresql 9.6.x I am getting an error with a postgresql function where I am recording a log on every table modification. This was all working great until I added this functionality where I am recording the current user id using current_setting functionality of postgresql. I set the current user on transactions in the background like so:

 select  set_config('myvars.active_user_id', '2123', true)

All this functionality works perfectly fine, except when the user is not set. This occurs when the tables are being updated by back end system queries and in that case the setting 'myvars.active_user_id' is null.
I want it to be null when it is not set. The user id field in the log is nullable. It seems to be trying to convert null to an empty string and put that in the integer variable which it doesn't like.
This appears to be some kind of weird problem specific to functions with triggers. I must be doing something wrong because as far as I know assigning a null value through a select...into"is no issue. The error I get in that case is so:

PSQLException: ERROR: invalid input syntax for integer: ""

I have added tracing statements and it is on this line:

EXECUTE 'select current_setting(''myvars.active_user_id'', true)  ' into log_user_id;

I don't understand why in this setting it gets upset about the null value. But it seems limited to this type of trigger function. Below is essentially the function I am using

CREATE OR REPLACE FUNCTION update_log() RETURNS TRIGGER AS $update_log$
DECLARE
    logid int;
   log_user_id int;
BEGIN
EXECUTE 'select current_setting(''myvars.active_user_id'', true)  ' into log_user_id;

  IF (TG_OP='DELETE') THEN
            EXECUTE 'select nextval(''seq_log'') ' into logid;
--            INSERT INTO log ....
            RETURN NULL;
  ELSIF (TG_OP='INSERT') THEN
            EXECUTE 'select nextval(''seq_log'') ' into logid;
--            INSERT INTO log ....
            RETURN NEW;

  ELSIF (TG_OP='UPDATE') THEN
--            INSERT INTO log ....
        END IF;
  END IF;
        RETURN NULL; 
    END;
$log$ LANGUAGE plpgsql;

Any thoughts?

CodePudding user response：

GUC (Global User Setting) variables like your myvars.active_user_id are not nullable internally. It holds text or empty text. These variables cannot to store NULL. So when you store NULL, then empty string is stored, and this empty string is returned from function current_setting.

In Postgres (and any database without Oracle) NULL is not empty string and empty string is not NULL.

So this error is expected:

postgres=# do $$
declare x int;
begin
  perform set_config('x.xx', null, false);
  execute $_$ select current_setting('x.xx', true) $_$ into x;
end;
$$;
ERROR:  invalid input syntax for integer: ""
CONTEXT:  PL/pgSQL function inline_code_block line 5 at EXECUTE

You need to check result first, and replace empty string by NULL:

create or replace function nullable(anyelement)
returns anyelement as $$
  select case when $1 = '' then NULL else $1 end;
$$ language sql;

do $$
declare x int;
begin
  perform set_config('x.xx', null, false);
  execute $_$ select nullable(current_setting('x.xx', true)) $_$ into x;
end;
$$;
DO

@Laurenz Albe has big true in your comment. Use dynamic SQL (execute command) only when it is necessary. It is not this case. So your code should looks like:

do $$
declare x int;
begin
  perform set_config('x.xx', null, false);
  x := nullable(current_setting('x.xx', true)); 
end;
$$;
DO