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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services