new stuff from postgres learned

watch

if you have open psql shell and want to have refreshed by n-seconds certain command, after executing this command once just type, e.g 10 stands for refresh each 10 seconds

\watch 10

SECURITY DEFINER - running procedure/function as OWNER

default behavior: fun is run by executing user, you can easily change it with SECURITY DEFINER you add just after defining your procedure e.g:

CREATE OR REPLACE FUNCTION public.foo() 
RETURNS VOID AS $$

-- do whatever you need here and remember

$$ LANGUAGE plpgsql SECURITY DEFINER;

• the user inside the scope is the owner of the function - the one who defined it (effective user)
• and to get the user actually calling it you may need to have wrapping function called without SECURITY DEFINER grab there user and pass it to the function run as SECURITY DEFINER.

just for reference the default modifier is called SECURITY INVOKER.

https://www.postgresql.org/docs/current/sql-createfunction.html

pg_stat_activity

One row per server process, showing information related to the current activity of that process, such as state and current query. Find it useful as lookup for more context info missing in event trigger with ddl_command_start.

https://www.postgresql.org/docs/9.2/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

debug level

SET client_min_messages TO DEBUG5;

all available from DEBUG5 to PANIC described here https://www.postgresql.org/docs/9.1/runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS

using it in PL/PgSQL:

RAISE NOTICE `running as user: %', session_user;

using it in plpythonu:

plpy.debug('query: {}'.format(query))