functions/WRITE_MESSAGE.pg_sql

Functions

Source

0001: \echo Start functions/WRITE_MESSAGE.pg_sql
0002: 
0003: /** Write given parameters to different targets.
0004:  *  The targets get defined in PROPERTY table.
0005:  *
0006:  *  @function WRITE_MESSAGE
0007:  *  @return void
0008:  *  @param in text I_MESSAGE Value for LOG.MESSAGE.
0009:  *  @param in text I_LEVEL Value for LOG.LEVEL.
0010:  *  @private
0011:  *  @info This function is meant to be private. Its use outside pglogger is
0012:  *        very limited at most. That's why it is marked as private eventhough
0013:  *        it is visible to the outside. All of this is due to the fact that
0014:  *        PostgreSQL 10 and earlier does not support packages.
0015:  *  @version $Id: WRITE_MESSAGE.pg_sql 18 2018-06-15 20:24:13Z thiemo $
0016:  *  @todo Once PostgreSQL supports packages, integrate this function into one.
0017:  *        For the time being, it should get installed within a schema on its
0018:  *        own and no execution grants should be given on it.
0019:  *  @todo Once HyperSQL supports a license tag, convert the corresponding
0020:  *        info tag into one.
0021:  *  @copyright Thiemo Kellner, 2018 -
0022:  *  @info License LGPLv3
0023:  *  @webpage https://www.sourceforge.net/projects/pglogger
0024:  */
0025: create or replace function WRITE_MESSAGE(
0026:     I_MESSAGE text,
0027:     I_LEVEL text
0028: )
0029:   returns void
0030:   language plpgsql
0031:   stable
0032:   -- Include the hosting schema into search_path so that dblink
0033:   -- can find the pglogger objects. There is no need to access
0034:   -- objects in other schematas not covered with public.
0035:   set search_path = :SCHEMA_NAME, public
0036:   as
0037: $body$
0038:     declare
0039:         -- constants
0040:         C_CALLER_FUNCTION constant name :=
0041:           GET_CALLER_FUNCTION();
0042:         C_PRESENT_USER constant name := current_user;
0043:         C_SESSION_USER constant name := session_user;
0044:         C_TRANSACTION_TIMESTAMP constant timestamp with time zone :=
0045:           transaction_timestamp();
0046:         C_TRANSACTION_ID constant bigint :=
0047:           txid_current_if_assigned();
0048:         C_SERVER_PID constant bigint := pg_backend_pid();
0049:         C_REMOTE_ADDRESS constant inet := inet_client_addr();
0050:         C_REMOTE_PORT constant int := inet_client_port();
0051:         C_TIMESTAMPFORMAT constant text :=
0052:           GET_PROPERTY_VALUE_STRING(
0053:               I_PROPERTY_NAME => 'TIMESTAMP_DISPLAY_FORMAT'
0054:           );
0055:         C_INTEGERFORMAT constant text :=
0056:           GET_PROPERTY_VALUE_STRING(
0057:               I_PROPERTY_NAME => 'INTEGER_DISPLAY_FORMAT'
0058:           );
0059:         C_LB constant text :=
0060:           GET_PROPERTY_VALUE_STRING(
0061:               I_PROPERTY_NAME => 'LINEBREAK'
0062:           );
0063: 
0064:         -- variables
0065:         V_STACK text;
0066:         V_MESSAGE text;
0067:         V_ENTRY text;
0068:         -- concatenation with null results in null
0069:         V_CALLER_FUNCTION text := coalesce(C_CALLER_FUNCTION, '');
0070:         V_TRANSACTION_ID text :=
0071:           trim(
0072:               coalesce(
0073:                   to_char(C_TRANSACTION_ID, C_INTEGERFORMAT),
0074:                   ''
0075:               )
0076:           );
0077:         V_SERVER_PID text := trim(to_char(C_SERVER_PID, C_INTEGERFORMAT));
0078:         V_REMOTE_ADDRESS text := coalesce(host(C_REMOTE_ADDRESS), '');
0079:         V_REMOTE_PORT text :=
0080:           trim(
0081:               coalesce(
0082:                   to_char(C_REMOTE_PORT, C_INTEGERFORMAT),
0083:                   ''
0084:               )
0085:           );
0086:         V_DBLINK_CONNECT_STRING text;
0087:         V_DBLINK_CONNECTION_NAME text;
0088:         V_QUERY text;
0089:     begin
0090:         if IS_LOGGING_TO_TABLE() then
0091:             V_DBLINK_CONNECTION_NAME :=
0092:               GET_PROPERTY_VALUE_STRING(
0093:                   I_PROPERTY_NAME => 'DBLINK_CONNECTION_NAME'
0094:               );
0095:             V_DBLINK_CONNECT_STRING :=
0096:               GET_PROPERTY_VALUE_STRING(
0097:                   I_PROPERTY_NAME => 'DBLINK_CONNECT_STRING'
0098:               );
0099:             -- Use literal (%L) as it returns the value null as the unquoted
0100:             -- string NULL.
0101:             V_QUERY := format(
0102:                            $s$select true from %I( $s$ || C_LB ||
0103:                              $s$           I_FUNCTION => %L::text, $s$ ||
0104:                              C_LB ||
0105:                              $s$           I_MESSAGE => %L::text, $s$ || C_LB ||
0106:                              $s$           I_LEVEL => %L::text, $s$ || C_LB ||
0107:                              $s$           I_PRESENT_USER => %L::name, $s$ ||
0108:                              C_LB ||
0109:                              $s$           I_SESSION_USER => %L::name, $s$ ||
0110:                              C_LB ||
0111:                              $s$           I_TRANSACTION_TIMESTAMP => $s$ ||
0112:                              C_LB ||
0113:                              $s$             %L::timestamptz, $s$ || C_LB ||
0114:                              $s$           I_TRANSACTION_ID => %L::bigint, $s$
0115:                              || C_LB   ||
0116:                              $s$           I_SERVER_PID => %L::bigint, $s$ ||
0117:                              C_LB ||
0118:                              $s$           I_REMOTE_ADDRESS => %L::inet, $s$ ||
0119:                              C_LB   ||
0120:                              $s$           I_REMOTE_PORT => %L::int $s$ ||
0121:                              C_LB ||
0122:                              $s$       ) $s$,
0123:                            'write_message_to_table',  -- lower case is needed
0124:                                                       -- because   %L
0125:                                                       -- double quotes
0126:                             C_CALLER_FUNCTION,
0127:                             I_MESSAGE,
0128:                             I_LEVEL,
0129:                             C_PRESENT_USER,
0130:                             C_SESSION_USER,
0131:                             C_TRANSACTION_TIMESTAMP,
0132:                             C_TRANSACTION_ID,
0133:                             C_SERVER_PID,
0134:                             C_REMOTE_ADDRESS,
0135:                             C_REMOTE_PORT
0136:                       );
0137:             if IS_ASYNCH_TAB_LOGGING() then
0138:                 -- Background workers for autonomous transactions are not (yet)
0139:                 -- even modules. One had to write/compile code like the one of
0140:                 -- Robert Haas (available at
0141:                 -- https://github.com/vibhorkum/pg_background)
0142:                 -- As pglogger is meant to be usable out-of-the-box,
0143:                 -- pg_background_launch et al. is not a way to go.
0144: 
0145:                 -- better to use dblink_connect_u with password file?
0146:                 perform dblink_connect(
0147:                             V_DBLINK_CONNECTION_NAME,
0148:                             V_DBLINK_CONNECT_STRING
0149:                         );
0150:                 perform dblink_send_query(
0151:                             V_DBLINK_CONNECTION_NAME,
0152:                             V_QUERY
0153:                         );
0154:                 perform dblink_disconnect(V_DBLINK_CONNECTION_NAME);
0155:             else
0156:                 perform * from dblink(
0157:                                    V_DBLINK_CONNECT_STRING,
0158:                                    V_QUERY
0159:                                ) as r(res boolean);
0160:             end if;
0161:             if (
0162:                 not IS_LOGGING_TO_STANDARD() and
0163:                   I_LEVEL = 'EXCEPTION'
0164:             ) then
0165:                 raise;
0166:             end if;
0167:         end if;
0168:         -- As raising an exception to standard will terminate execution,
0169:         -- logging to standard has to be last.
0170:         if IS_LOGGING_TO_STANDARD() then
0171:             V_ENTRY :=
0172:               to_char(
0173:                   clock_timestamp(),
0174:                   C_TIMESTAMPFORMAT) ||
0175:               C_LB ||
0176:               '   - Calling function: ' || V_CALLER_FUNCTION || C_LB ||
0177:               '   - Current user: ' || C_PRESENT_USER || C_LB ||
0178:               '   - Session user: ' || C_SESSION_USER || C_LB ||
0179:               '   - Transaction timestamp: ' ||
0180:               to_char(C_TRANSACTION_TIMESTAMP, C_TIMESTAMPFORMAT) || C_LB ||
0181:               '   - Transaction ID: ' || V_TRANSACTION_ID || C_LB ||
0182:               '   - Server process ID: ' || C_SERVER_PID || C_LB ||
0183:               '   - Address of the remote connection: ' ||
0184:               V_REMOTE_ADDRESS || C_LB ||
0185:               '   - Port of the remote connection: ' ||
0186:               V_REMOTE_PORT || C_LB ||
0187:               '   - Message: ' || coalesce(I_MESSAGE, '');
0188:             case I_LEVEL
0189:                 when 'DEBUG' then
0190:                     raise debug '%', V_ENTRY;
0191:                 when 'LOG' then
0192:                     raise log '%', V_ENTRY;
0193:                 when 'INFO' then
0194:                     raise info '%', V_ENTRY;
0195:                 when 'NOTICE' then
0196:                     raise notice '%', V_ENTRY;
0197:                 when 'WARNING' then
0198:                     raise warning '%', V_ENTRY;
0199:                 when 'EXCEPTION' then
0200:                     raise exception '%', V_ENTRY;
0201:                 else
0202:                     raise exception '%', 'Unsupported level ' || I_LEVEL;
0203:             end case;
0204:         end if;
0205:     exception when others then
0206:         if GET_PROPERTY_VALUE_BOOLEAN('OUTPUT_EXCEPTION_STACK') then
0207:             -- 'get stacked diagnostics' can be used only within axception
0208:             -- clause.
0209:             get stacked diagnostics V_STACK = PG_EXCEPTION_CONTEXT;
0210:             perform WRITE_MESSAGE(
0211:                         I_MESSAGE => E'Error call stack\n' || V_STACK,
0212:                         I_LEVEL => 'NOTICE'
0213:                     );
0214:         end if;
0215:         raise;
0216:     end;
0217: $body$;
0218: 
0219: comment on function WRITE_MESSAGE(
0220:     I_MESSAGE text,
0221:     I_LEVEL text
0222: ) is
0223:   'Write given parameters to different targets.
0224: The targets get defined in PROPERTY table.
0225: $Header: svn+ssh://thiemo@svn.code.sf.net/p/pglogger/code/functions/WRITE_MESSAGE.pg_sql 18 2018-06-15 20:24:13Z thiemo $';
0226: 
0227: commit; -- unlike Oracle not all ddl commit implicitly
0228: 
0229: \echo End functions/WRITE_MESSAGE.pg_sql