Index Children only with based Condition

[kundankumarcasio]

PGSync version: latest

Postgres version: 15.2

Elasticsearch/OpenSearch version: 7.17.13

Redis version: 7.2

Python version: 3.8

Problem Description: I want to index children with given condition [ { "database": "databaseName", "index": "indexName", "nodes": { "table": "parentTable", "columns": [], "children": [ { "table": "childTable", "columns": [], "relationship": { "variant": "object", "type": "one_to_many", "foreign_key": { "child": [ "parent_id" ], "parent": [ "id" ], "condition": { "child_column1": "some Value" //need help here } } } } ] } } ]

Equivalent SQL Query: select * from parentTable join childTable where parentTable.id=childTable.parent_id where child_column1="some Value"

Error Message (if any):

[jvanderen1]

I wonder if this could also be achieved within PGSync's plugin API? That way, conditions can be more expressive. Something like:

from pgsync import plugin

class FooPlugin(plugin.Plugin):
    name = 'FooPlugin'

    def transform(self, doc, **kwargs):
       ...

    def should_index(self, doc, **kwargs):
        return doc['childTable']['parent_id'] == 'someValue'

[kundankumarcasio]

thank you very much. i will try this.

[jvanderen1]

I don't think an API exists today for something like this. However, it could be an enhancement.

[kundankumarcasio]

yes. Any work around for this ?

[jvanderen1]

You can probably write a plugin like the following:

from pgsync import plugin

class FooPlugin(plugin.Plugin):
    name = 'FooPlugin'

    def transform(self, doc, **kwargs):
      return doc if doc['childTable']['parent_id'] == 'someValue' else None

But I am concerned that depending on the condition, this won't necessarily insert/delete documents as expected. It may work for your use case though.

[kundankumarcasio]

I tried this and it worked, but this creates some sync inconsistencies.

[jvanderen1]

This would be a great enhancement to add for things like soft-deleted or archived records.

[toluaina]

I think Plugins are the right/only way to do this. Can you elaborate on what you mean by sync inconsistencies? I can assist with a plugin if you need.

[jvanderen1]

@toluaina I believe if you were to have indexed a PostgreSQL record at one point, but then a plugin used were to return None based on some condition, the correlating document would not necessarily get removed from the index.