Hi all,
I tried to execute a join query using SPI_execute() in logical decoding part and got inconsistent values (i am referring it as inconsistent since it is returning the old values which is present at the postgresql server start).
My data directory has to tables
table1(a integer PRIMARY KEY, b integer, c integer)
table2(x integer PRIMARY KEY, y integer, z integer)
I have table1 as
a | b | c
---+---+---
1 | 1 | 1
2 | 2 | 2
and table 2 as
x | y | z
---+---+---
1 | 1 | 1
2 | 2 | 2
Then through psql client inserted a new row to table1 as: insert into table1(3,3,3);
While decoding this insert query i am trying to execute the below query using SPI_execute
SELECT * FROM (table1 LEFT JOIN table2 ON ((table1.a = table2.x)));
And got only 2 rows.
I cant able to get any new rows that are inserted. Are these new values get locked somewhere?
Is there a way to get inserted changes?
PS:When i restart the pgsql server i can able to get those new values that are inserted/updated in previous instance.