I use one normal table(postgesql table) (1.5 million records) and three foreign tables(oracle table) (1.5 million records, 5 million records, 5 million records) to query data, it works.
Then I create a new normal table, and export foreign table data(1.5 million records) into this table,
After that I use two normal tables and two foreign tables query data, it does not work.
And display these errors,
-----------------------------
ERROR: invalid cache ID: 42
CONTEXT: parallel worker
-----------------------------
Same sql why it does not work. I do not know how to resolve it.
I use postgresql 10.4, and oracle_fdw 2.0.0.
I use postgresql-10.4-1-linux-x64.run to install the database.
===============================================================
bash-4.1$ psql --version
psql (PostgreSQL) 10.4
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
===============================================================
the sql is like below:
When it works,
tableA is a normal postgresql table
tableB, tableB1, tableC are foreign tables
When it does not work,
tableA, tableC are normal postgresql tables
tableB, tableB1 are foreign tables
=====================================================================
with sql1 as(
SELECT
a.x1,
a.x2,
a.x3,
a.x4,
abs(b.y1) y1,
b1.z1,
c.s1,
(b1.z1-a.x3) as difference_value
FROM tableA as a
left join tableB as b
on a.f1 = b.f1
left join tableB1 as b1
on a.f1 = b1.f1
and b.f1 = b1.f1
left join tableC as c
on a.belong = c.belong
and a.no = c.no
where a.date >= to_date('2017-12-12', 'yyyy-mm-dd')
and a.code='XX'
)
SELECT a.x1,
PERCENTILE_DISC(0.025) WITHIN GROUP(ORDER BY a.difference_value ),
PERCENTILE_DISC(0.975) WITHIN GROUP(ORDER BY a.difference_value )
FROM sql1 a
GROUP BY a.x1
union all
SELECT a.x1,
PERCENTILE_DISC(0.025) WITHIN GROUP(ORDER BY a.x2 ),
PERCENTILE_DISC(0.975) WITHIN GROUP(ORDER BY a.x2 )
FROM sql1 a
GROUP BY a.x1
union all
SELECT a.x1,
PERCENTILE_DISC(0.025) WITHIN GROUP(ORDER BY a.x3 ),
PERCENTILE_DISC(0.975) WITHIN GROUP(ORDER BY a.x3 )
FROM sql1 a
GROUP BY a.x1
union all
SELECT a.x1,
PERCENTILE_DISC(0.025) WITHIN GROUP(ORDER BY a.x4 ),
PERCENTILE_DISC(0.975) WITHIN GROUP(ORDER BY a.x4)
FROM sql1 a
GROUP BY a.x1
union all
SELECT a.x1,
PERCENTILE_DISC(0.025) WITHIN GROUP(ORDER BY a.y1 ),
PERCENTILE_DISC(0.975) WITHIN GROUP(ORDER BY a.y1 )
FROM sql1 a
GROUP BY a.x1
union all
SELECT a.x1,
PERCENTILE_DISC(0.025) WITHIN GROUP(ORDER BY a.x5 ),
PERCENTILE_DISC(0.975) WITHIN GROUP(ORDER BY a.x5 )
FROM sql1 a
GROUP BY a.x1
union all
SELECT a.x1,
PERCENTILE_DISC(0.025) WITHIN GROUP(ORDER BY a.s1 ),
PERCENTILE_DISC(0.975) WITHIN GROUP(ORDER BY a.s1 )
FROM sql1 a
GROUP BY a.x1
union all
SELECT a.x1,
PERCENTILE_DISC(0.025) WITHIN GROUP(ORDER BY a.z1 ),
PERCENTILE_DISC(0.975) WITHIN GROUP(ORDER BY a.z1 )
FROM sql1 a
GROUP BY a.x1;
===================================================================
