Return to Question

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. –

Iam working in a python project and I have a requirement that if any insertion/updation/delation done in a postgressql table then that value should be updated in redis. That means if I add a new row it should be updated in Redis. iam working on a windows system and redis is installed in docker in my windows system.

PostgresSQL version is 15.

I have tried with different scenarios and none of them are worked.

1. I have write a trigger and it calls a stored procedure function if any changes occur in my table. But I Can't connect to the Redis server from my stored procedure.

I have tried with plpython3u but due to version issue I can't create the stored procedure. Also i have tried with Pg_redis it also didn't work.

I have tries with below scripts to create the store procedure.

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.

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;

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;