Re: Logical Decoding - Execute join query

On 1 April 2016 at 17:45, Andres Freund <andres(at)anarazel(dot)de> wrote:

> 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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services