Hi, On 2016-04-01 15:09:59 +0530, hari.prasath wrote: > 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).
You are not allowed to access non catalog tables in an output plugin. To quote the manual: > Read only access to relations is permitted as long as only relations are > accessed that either have been created by <command>initdb</command> in > the <literal>pg_catalog</literal> schema, or have been marked as user > provided catalog tables using
The reason for that is that we'd have to keep all rows in the tables, if you wanted to be look at the state "in the past".
I suspect this is going to come up more and more as people start using logical decoding.
A while back I had a quick look at ways to ensure we actually die with an assertion failure when this happens. I didn't have much luck. The places I could find where something definitely unsafe would be happening were too far from anywhere that had knowledge of the relation's catalog entry to check whether it was a user catalog. Not without doing relcache lookups just to check an assertion, anyway. Or to necessarily even know it was running under a historical snapshot without poking through layers messily. OTOH, I don't know the area well and didn't dig too deeply.
Then again, IIRC the SPI still lets you proceed in read-only mode without having a snapshot set up or an open xact... and it might work. For a while. Sometimes. Possibly even with correct results. Depending on what exactly you do. The world doesn't seem to have ended as a result of not immediately dying with an assertion failure in that situation.