Update Redis cache value from PostgreSQL

I am working on a Python project and I have a requirement that whenever a row is inserted, updated, or deleted in a PostgreSQL table, the corresponding value should be updated in Redis. I am working on a Windows system and Redis is installed in a Docker container on my Windows system. The PostgreSQL version is 15.

I have tried different scenarios, but none of them have worked so far. I tried writing a trigger that calls a stored procedure function when any changes occur in my table, but I can't connect to the Redis server from my stored procedure.

I also tried using plpython3u, but I couldn't create the stored procedure due to version issues. Additionally, I tried using pg_redis, but it didn't work either.

I have included the script I used to create the stored procedure below.

1.

CREATE SCHEMA redis;
CREATE OR REPLACE FUNCTION update_redis() RETURNS TRIGGER AS $$
DECLARE
    redis_host TEXT := 'localhost';
    redis_port INTEGER := 32768;
    redis_password TEXT := 'redispw';
    redis_db INTEGER := 0;
    redis_client redis.StrictRedis;
BEGIN
    redis_client = redis.StrictRedis(host=redis_host, port=redis_port, password=redis_password,   db=redis_db);
    IF (TG_OP = 'DELETE') THEN
        redis_client.hdel(TG_TABLE_NAME, OLD.id);
    ELSE
        redis_client.hset(TG_TABLE_NAME, NEW.id, row_to_json(NEW)::text);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

2.

CREATE EXTENSION dblink;
CREATE OR REPLACE FUNCTION update_redis() RETURNS TRIGGER AS $$
DECLARE
  redis_host text := 'localhost';
  redis_port text := '32768';
  redis_db text := '0';
  redis_password text := 'redispw';
  redis_conn text := 'redis://:' || redis_password || '@' || redis_host || ':' || redis_port || '/' ||     redis_db;
BEGIN
  PERFORM pg_notify('redis_update', row_to_json(NEW)::text);

  -- Connect to Redis
  PERFORM pg_sleep(0.1); -- Wait a short time to allow the NOTIFY event to be processed
  PERFORM dblink_connect('dbname=postgres', 'host.docker.internal', 'redis', '', 'redis_conn');

  -- Set the value in Redis
  PERFORM dblink_exec('redis_conn', 'SET key value');

  -- Disconnect from Redis
  PERFORM dblink_disconnect('redis_conn');

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

**3. **

CREATE OR REPLACE FUNCTION update_redisnew() RETURNS trigger AS $$
BEGIN
  -- Retrieve the updated data from the table
  DECLARE
    data json;
  BEGIN

    data := row_to_json(NEW);

    SELECT redis.add_server('redis://default:redispw@localhost:32768');
    PERFORM redis.command('SET', 'key666', 'valll');


    -- Configure the Redis server connection
    SELECT redis.add_server('redis://localhost:6379/0');

    -- Send the updated data to Redis
    PERFORM redis.command('SET', NEW.id::text, data::text);

    RETURN NEW;
  END;
END;
$$ LANGUAGE plpgsql;

please someone help me to figure out the issue.

I have copied the errors from your comments into the question itself.

When i execute first query in postgressql, i got below error, ERROR: function dblink_connect(unknown, unknown, unknown, unknown, unknown) does not exist LINE 1: SELECT dblink_connect('dbname=postgres', 'host.docker.intern... so i cant create this sp. –

I can create last two sps by executing corresponding queries but when i trigger that sp i got below errors corresponding to each sps, HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT dblink_connect('dbname=postgres', 'host.docker.internal', 'redis', '', 'redis_conn') CONTEXT: PL/pgSQL function update_redisnew() line 13 at PERFORM SQL state: 42883 –

ERROR: function redis.add_server(unknown) does not exist LINE 1: SELECT redis.add_server('redis://default:redispw@localhost:3... HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT redis.add_server('redis://default:redispw@localhost:32768') CONTEXT: PL/pgSQL function update_redisnew() line 10 at SQL statement SQL state: 42883 –

I am using windows 10, PostgreSQL -15 and python 3.7 and 3.11. –