Error when sync View with Elastic

[Themanwhosmellslikesugar]

PGSync version: 2.3.1 (also trying with latest version on github)

Postgres version: 12.4

Elasticsearch version: 2.0.0 (OpenSearch)

Redis version: 6.2

Python version: 3.9.7

Problem Description:

I trying to sync PostgreSQL View (not Materialized), but pgsync handle with view like usual table. I'm using this example.

What am I doing wrong? Its working, if i use Materialized View. I also rolled back to a commit with added support View, stupidly added to my view column xmin from table, did not help.

schema.json

[
	{
		"database": "test",
		"index": "products",
		"nodes": {
			"table": "products_terms",
			"schema": "public",
			"base_tables": ["products"],
			"primary_key": ["id"],
			"columns": [
				"id",
				"title",
				"preview",
				"terms"
			]
		}
	}
]

Error Message (if any):

2022-07-22 17:19:41.429:DEBUG:pgsync.sync: pull txmin: None - txmax: 538128                                                                                                                                        
2022-07-22 17:19:41.467:ERROR:pgsync.elastichelper: Exception (psycopg2.errors.UndefinedColumn) column products_terms_1.xmin does not exist                                                                        
LINE 3: WHERE CAST(CAST(products_terms_1.xmin AS TEXT) AS BIGINT) < ...                                                                                                                                            
                        ^                                                                                                                                                                                          
                                                                                                                                                                                                                   
[SQL: SELECT count(*) AS count_1                                                                                                                                                                                   
FROM public.products_terms AS products_terms_1                                                                                                                                                                     
WHERE CAST(CAST(products_terms_1.xmin AS TEXT) AS BIGINT) < %(param_1)s]                                                                                                                                           
[parameters: {'param_1': 538128}]                                                                                                                                                                                  
(Background on this error at: https://sqlalche.me/e/14/f405)                                                                                                                                                       
Traceback (most recent call last):                                                                                                                                                                                 
  File "/usr/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context                                                                                                                
    self.dialect.do_execute(                                                                                                                                                                                       
  File "/usr/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute                                                                                                                    
    cursor.execute(statement, parameters)                                                                                                                                                                          
psycopg2.errors.UndefinedColumn: column products_terms_1.xmin does not exist                                                                                                                                       
LINE 3: WHERE CAST(CAST(products_terms_1.xmin AS TEXT) AS BIGINT) < ...                                                                                                                                            
                        ^                                                                                                                                                                                          
                                    

[toluaina]

Unfortunately, this will only work with materialized views.
I will update the docs to reflect this.
Due to the implementation we need to have xmin columns which is only present on materialized views.

[Themanwhosmellslikesugar]

@toluaina, ok, thanks, I thought it was about View (not Materialized).