BUG #15149: Invalid cache id = 42

The following bug has been logged on the website:

Bug reference: 15149
Logged by: Dmitry Chirkin
Email address: dmitry(dot)chirkin(at)gmail(dot)com
PostgreSQL version: 10.3
Operating system: Debian
Description:

After upgrading to 10.3 I have found strange issue with
pg_try_advisory_lock
Here is my simplified workflow which is executed every minute:

1. TX start
2. select pg_try_advisory_lock(42) as lock; // we have several workers so we
don't want to start more than one simultaneously.
if lock is true -> continue, else -> rollback and finish.
3. Make a query via oracle_fdw, usually return 70-80 rows.
4. insert on conflict update in table1.
5. run some analyze query with several CTE's -> insert some artifacts into
table2.
6. run another one analyze query with several CTE's -> inserts some
artifacts into table 2.
7. TX commit.

So, few days ago i've added step 6 and on step 7 i'm getting error: Invalid
cache id: 42
On 10.2 everything working fine but on 10.3 - i'm getting error.

I'm not sure it's possible to provide good steps to reproduce with plain SQL
queries because of fdw but maybe this would be enough to understand what is
the reason for issue.

Thank you!

=?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)org> writes:
> So, few days ago i've added step 6 and on step 7 i'm getting error: Invalid
> cache id: 42
> On 10.2 everything working fine but on 10.3 - i'm getting error.

Odd. Could you set a breakpoint at errfinish() and get a backtrace
from the point of the error?

regards, tom lane

Oh, I’m just a nodejs developer, is there any instructions which I can follow to achieve this?

More details to come: cleaning table2 solves the issue. But this is not the case for production envs beacause this data is really important. Will try with vacuum full.

> 10 апр. 2018 г., в 19:11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> написал(а):
>
> =?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)org> writes:
>> So, few days ago i've added step 6 and on step 7 i'm getting error: Invalid
>> cache id: 42
>> On 10.2 everything working fine but on 10.3 - i'm getting error.
>
> Odd. Could you set a breakpoint at errfinish() and get a backtrace
> from the point of the error?
>
> regards, tom lane

Chirkin Dmitry <dmitry(dot)chirkin(at)gmail(dot)com> writes:
> Oh, I’m just a nodejs developer, is there any instructions which I can follow to achieve this?

There's some info on our wiki:

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

regards, tom lane