Edit - Stack Overflow

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

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.

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;

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

Answer*

OK, so what you need to do is stop zig-zagging around trying random things without stopping to understand the details on any of them.

Let's take your first error message (which is for your second example block of code). It is telling you that it can't find a function `dblink_connect()` with the provided parameter-types ("unknown" is any quoted value before PG can identify its real type). It is pretty clear on that point, so you should stop and figure out why you and your database system disagree.

There can only be three options:

1. You are connected to a different database than you expect and dblink is installed somewhere else. If your code shows what you are actually running, this seems unlikely since you create the extension right before creating the function.
2. There is no `dblink_connect` function at all (which would suggest your `CREATE EXTENSION` didn't work).
3. There is one, but it requires different parameters.

So - what you need to do now (and this is just standard debugging procedure that applies to any system - database, network router, web stack) is narrow down the two options.

Connect to your database with `psql` and run `\dx` that will print all your installed extensions. If you don't see dblink, then it didn't install. Try the `CREATE EXTENSION` now by hand and read and look up the error message.

If it is there, check the schema listed for it - is it in your `search_path`? If it says "public" then it almost certainly is - if not, you need to know about search-paths. Look that up too.

If the extension is installed and it is in your search-path then you can check if the function exists with `\df dblink_connect`. What does it say the parameters you need are?

I can guess what it is going to say, even though I don't have dblink installed on my database because I do have access to the official manuals.

https://www.postgresql.org/docs/15/contrib-dblink-connect.html

And it says it is expecting two strings with the connection-name as the first parameter. Not several with the connection-name as the last parameter. No idea where you got that from, but it isn't from the docs.

Now, before you go jumping in and fixing this expecting it to all work - are you sure that dblink can connect to redis at all? I've only ever heard of it being used to connect to other PostgreSQL servers. The answer will be in the documentation, so sit down and read that first.

I'm guessing you either need one of the resdis/fdw wrappers (which may require you to compile them) or to use pl/python3u and import a redis client into python (which will require you to understand what version of python is compatible with your installed postgresql).

Draft saved

Draft discarded

Edit Summary*

Cancel

Hi, thank you for the reply. I have tried with second option(dblink_connect). Created function to connect to Redis and set a trigger as well. But when doing the connection check in pgadmin by updating a table. it hangs. No output is showing, 'waiting for the query to complete : ' with a running time is showing in bottom of Pgadmin. I have tried with the connection test Test-NetConnection localhost -Port 32768 in PowerShell ISE. it is showing connection successful. My Redis is running inside docker. 'redis://default:redispw@localhost:32768' this is Redis connection string.

jps
– jps

2023年05月02日 04:51:46 +00:00
Commented May 2, 2023 at 4:51
Can you post a link to the docs that show dblink works with redis, because like I said, I only found references to connecting to othe PostgreSQL instances.

Richard Huxton
– Richard Huxton

2023年05月02日 06:17:00 +00:00
Commented May 2, 2023 at 6:17

Add a comment |

How to Edit

Correct minor typos or mistakes
Clarify meaning without changing it
Add related resources or links
Always respect the author’s intent
Don’t use edits to reply to the author

How to Format

create code fences with backticks ` or tildes ~
```
like so
```
add language identifier to highlight code
```python
def function(foo):
print(foo)
```
put returns between paragraphs
for linebreak add 2 spaces at end
_italic_ or **bold**
indent code by 4 spaces
backtick escapes `like _so_`
quote by placing > at start of line
to make links (use https whenever possible)

<https://example.com>

[example](https://example.com)

<a href="https://example.com">example</a>

formatting help »
answering help »

How to Tag

A tag is a keyword or label that categorizes your question with other, similar questions. Choose one or more (up to 5) tags that will help answerers to find and interpret your question.

complete the sentence: my question is about...
use tags that describe things or concepts that are essential, not incidental to your question
favor using existing popular tags
read the descriptions that appear below the tag

If your question is primarily about a topic for which you can't find a tag:

combine multiple words into single-words with hyphens (e.g. python-3.x), up to a maximum of 35 characters
creating new tags is a privilege; if you can't yet create a tag you need, then post this question without it, then ask the community to create it for you

popular tags »

default

CollectivesTM on Stack Overflow

Update Redis cache value from PostgreSQL

Answer*