Thread: Logical Decoding - Execute join query
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.
cheers
- Harry
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". Greetings, Andres Freund
On 1 April 2016 at 17:45, Andres Freund <andres@anarazel.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.