DEV Community

andi
andi

Posted on

1 2

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))
👋 While you are here

Reinvent your career. Join DEV.

It takes one minute and is worth it for your career.

Get started

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay