Multiple databases usage

[gospodima]

In django-pgtrigger there is an option to work with multiple databases. Unfortunately, I couldn't find any information in docs about such possibility in django-pgpubsub. Could you please elaborate whether it is possible and if yes - how?

[PaulGilmartin]

@gospodima Unfortunately it is not currently possible. I don't have any hands on experience with working across multiple databases on a Django app, so I'm also unsure how much work would be involved. I don't imagine we'd need too much more on top of the existing hooks Django has for this, but I can't be sure.

I would definitely like to support it in the future, but have no plans to right now (unless someone volunteers to help with mor experience in that domain).

[mixtah]

@gospodima I was just browsing the issues and noticed this question. I ended up implementing this in my project by overriding a couple parts. Intended on making a PR, but was busy at the time and forgot. It might be late, but may be useful for any others with this question in mind.

I have a multi-db setup, in which the default db isn't the main db I use. To clarify I don't exactly use Pgpubsub for both databases, but you can extend what I did to do just that. Effectively I added an additional option to select which database you listen and notify. You'll just need to run a 2nd listener for a 2nd database.

Below is my custom 'notify' which will accept a database id (as per settings.py), and will default to a new setting or just the default database if nothing else.

from django.utils.connection import ConnectionProxy
from django.db import connections, DEFAULT_DB_ALIAS
from django.db.transaction import atomic
from django.conf import settings

from pgpubsub.channel import locate_channel, Channel, registry

DEFAULT_DB_ALIAS = getattr(settings, "PGPUBSUB_DEFAULT_DATABASE", DEFAULT_DB_ALIAS)

# Taken from pgpubsub's notify but adde the option to specify the db connection
@atomic
def notify(
    channel: Union[Type[Channel], str], database_alias: str = DEFAULT_DB_ALIAS, **kwargs
):
    channel_cls = locate_channel(channel)
    channel = channel_cls(**kwargs)
    serialized = channel.serialize()
    connection = ConnectionProxy(connections, database_alias)
    with connection.cursor() as cursor:
        name = channel_cls.name()
        logger.info(f"Notifying channel {name} with payload {serialized}")
        cursor.execute(
            f"select pg_notify('{channel_cls.listen_safe_name()}', '{serialized}');"
        )
        if channel_cls.lock_notifications:
            from pgpubsub.models import Notification

            Notification.objects.create(
                channel=name,
                payload=serialized,
            )
    return serialized

This is the listener to match, the last line overrides the correct function such that manage.py listen will listen to the DB configured in settings.py

from django.utils.connection import ConnectionProxy
from django.db import connections, DEFAULT_DB_ALIAS

from django.conf import settings

import pgpubsub.listen
from pgpubsub.channel import (
    BaseChannel,
    ChannelNotFound,
    locate_channel,
    registry,
)


DEFAULT_DB_ALIAS = getattr(settings, "PGPUBSUB_DEFAULT_DATABASE", DEFAULT_DB_ALIAS)


def listen_to_channels(
    channels: list[BaseChannel] | list[str] = None,
    database_alias: str = DEFAULT_DB_ALIAS,
):

    if channels is None:
        channels = registry
    else:
        channels = [locate_channel(channel) for channel in channels]
        channels = {
            channel: callbacks
            for channel, callbacks in registry.items()
            if issubclass(channel, tuple(channels))
        }
    if not channels:
        raise ChannelNotFound()
    connection = ConnectionProxy(connections, database_alias)
    cursor = connection.cursor()
    for channel in channels:
        pgpubsub.listen.logger.info(f"Listening on {channel.name()}\n")
        cursor.execute(f"LISTEN {channel.listen_safe_name()};")
    return connection.connection


pgpubsub.listen.listen_to_channels = listen_to_channels

I use it in a little bit of a different way, so you may need to play with this.

[mixtah]

@PaulGilmartin Let me know if you're open to a PR over this. Probably better for my project's maintainability if I didn't patch the library. If you're active enough to review and approve one, I'll put in the effort to make one some afternoon.

Edit: Would be good to get some guidance on how you usually test this end-to-end, and if you think there will be a way to test and mock the multi-database setup.

[PaulGilmartin]

@mixtah Thanks for the contribution! Your change looks fairly straightforward which is now.
Would your proposed PR support being able to specify the database per listener function? e.g.

@pgpubsub.post_insert_listener(AuthorTriggerChannel, database='blah')
def create_first_post_for_author(old: Author, new: Author):
    print(f'Creating first post for {new.name}')
    Post.objects.create(
        author_id=new.pk,
        content='Welcome! This is your first post',
        date=datetime.date.today(),
    )

or is it limited to being able to specify a specific db for pgpubsub at a more global level?

[mixtah]

I only tried it (and needed it) at a global level, so I'm not entirely sure, but I doubt it's an issue to do it as you described.

I wrote that code ~6 months ago, but need to do some redesigns anyway and plan on using this library a bit more so it will soon be a good time to revisit and improve my implementation. I won't promise the PR within a few weeks but I plan on doing it when I start to implement design my changes.

Edit: Thinking more about a per-database listener. I think I can get the setting in via the decorator, however the listener itself will only monitor 1 database. I may be able to upgrade the listener to support N databases, but it may be simpler to simply run 2 listeners and use an argument to specify which it's for, and it will only listen to listeners for that particular database.
I'll have a go at N databases, but it leaves the realm of a simple change, and one that risks me adding bugs that break things for others. I might make 2 PRs, a simple one that is low risk, then I'll work on the multi-database one which will probably require more time testing.