dpevunov commented 1 year ago

Hello!

I have postgresql 12.12 and oracle_fdw 2.5.0. Installed everything using readme and created extention in postgresql, then successfully created schema and foreign tables in it, Everything went ok, But I'm getting Segmentation fault errors when trying to select those oracle tables using oracle_fdw, An i/o error occurred while sending to the backend. Postgresql log shows error "signal 11: Segmentation fault". Also, I found oracle client diag traces and they are showing the error "oci-24550 [11] [[si_signo=11] [si_errno=0] [si_code=1] [si_int=0] [si_ptr=(nil)] [si_addr=0x8]] [] [] [] [] [] [] [] [] [] []"

========= Dump for incident 129 (oci 24550 [11]) ======== Tracing is in restricted mode! at 0x7ffecbe2fe18 placed dbge.c@1343 [TOC00003] ----- Short Call Stack Trace ----- dbgexPhaseII()+1895<-dbgexProcessError()+937<-dbgePostErrorDirectVaList_int()+2162<-dbgePostErrorDirect()+806<-kpeDbgSignalHandler()+303<-skgesig_sigactionHandler()+272<-__sighandler()<-oracleIterateForeignScan()+688<-0x0000000000000002<-0x313A39322E36322E

Then I've got stack trace using gdb. And it showed an error:

Program received signal SIGSEGV, Segmentation fault. oracleIterateForeignScan (node=) at oracle_fdw.c:1348 1348 ExecClearTuple(slot);

0 oracleIterateForeignScan (node=) at oracle_fdw.c:1348

1 0x0000000000643c7b in ForeignNext ()

2 0x000000000061f5d1 in ExecScan ()

3 0x0000000000636d69 in ExecLimit ()

4 0x0000000000616f1b in standard_ExecutorRun ()

5 0x000000000076b6db in PortalRunSelect ()

6 0x000000000076ccce in PortalRun ()

7 0x0000000000768e3f in exec_simple_query ()

8 0x000000000076a727 in PostgresMain ()

9 0x00000000006f5e46 in ServerLoop ()

10 0x00000000006f6ca9 in PostmasterMain ()

11 0x0000000000481eae in main ()

Could you please help me to resolve the issue?

wbr, Denis

laurenz commented 1 year ago

That should not happen...

I see where the problem happens, but I'd need to be able to reproduce the problem to find out what is wrong.

dpevunov commented 1 year ago

Hi @laurenz ! Thanks for the reply. How can I help you to reproduce it? Maybe something to provide?

wbr, Denis

laurenz commented 1 year ago

What would help is a sequence of commands I can run here to reproduce the crash.

dpevunov commented 1 year ago

Postgresql and Oracle are on the different servers. I checked connectivity, Everything is ok, I can select Oracle tables via sqlplus. Then in postgres:

1) CREATE EXTENSION oracle_fdw; CREATE EXTENSION

2) create server pkub_oracle foreign data wrapper oracle_fdw options (dbserver '//172.16.26.29:1521/pkub' ); CREATE SERVER

3) create user mapping for idm server pkub_oracle options (user 'CUBE_HR_IMP', password 'manager'); CREATE USER MAPPING

4) grant usage on foreign server pkub_oracle to idm; GRANT

5) CREATE SCHEMA hr_imp_fdw; CREATE SCHEMA

6) IMPORT FOREIGN SCHEMA "CUBE_HR_IMP" from SERVER pkub_oracle into hr_imp_fdw; IMPORT FOREIGN SCHEMA

7) select * from hr_imp_fdw.sup_department;

An i/o error occurred while sending to the backend. signal 11: Segmentation fault

wbr, Denis

laurenz commented 1 year ago

Thanks. IMPORT FOREIGN SCHEMA means that the foreign table definition depends on the Oracle table definition. Can you give me the CREATE TABLE statement that was used in Oracle?

dpevunov commented 1 year ago

For all tables or just for one? There are about 15 tables there,

dpevunov commented 1 year ago

CREATE TABLE "CUBE_HR_IMP"."SAP_DEPARTMENT" ( "ID" NUMBER NOT NULL ENABLE, "SOURCEID" NVARCHAR2(10) NOT NULL ENABLE, "PARENT_ID" NUMBER, "NAME" NVARCHAR2(2000), "SHORT_NAME" NVARCHAR2(40), "MANAGER_ID" NUMBER, "MAILINGADDRESS" NVARCHAR2(241), "BEGDATE" DATE NOT NULL ENABLE, "ENDDATE" DATE, "IS_OUTDATED" NUMBER(*,0) NOT NULL ENABLE, "POST_COUNTS" NUMBER, CONSTRAINT "DEPARTMENT_PK" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "CUBE_HR_IMP_DATA" ENABLE, CONSTRAINT "SAP_DEPARTMENT_U02" UNIQUE ("SOURCEID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "CUBE_HR_IMP_DATA" ENABLE, CONSTRAINT "SAP_DEPARTMENT_C01" CHECK (name is not null or short_name is not null) ENABLE, CONSTRAINT "SAP_DEPARTMENT_R01" FOREIGN KEY ("PARENT_ID") REFERENCES "CUBE_HR_IMP"."SAP_DEPARTMENT" ("ID") DISABLE, CONSTRAINT "SAP_DEPARTMENT_R02" FOREIGN KEY ("MANAGER_ID") REFERENCES "CUBE_HR_IMP"."SAP_STUFF" ("ID") ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "CUBE_HR_IMP_DATA" ;

laurenz commented 1 year ago

Thanks. I created that Oracle table, imported the foreign table in PostgreSQL and could run the query with no problem.

Perhaps the problem is related to certain data values? Can you try different queries like

SELECT * FROM sap_department WHERE id = 42;  -- existing key
SELECT * FROM sap_department WHERE id = -1;  -- key that doesn't exist
SELECT * FROM sap_department WHERE id BETWEEN 10 AND 1000;  -- different ranges

If only some of these fail, it might be related to the data.

dpevunov commented 1 year ago

Hi @laurenz !

None of these queries worked. All 3 crashed with segmentation faults.

wbr, Denis

laurenz commented 1 year ago

Does the crash only happen with that single table?

So far, I have no clue as to what causes the problem. Let me ask some general questions:

Are you using normal, open source PostgreSQL or some modified version?
Did you build it yourself or install it from packages? Which packages?
What version of Oracle client and server are you using (SELECT oracle_diag('pkub_oracle');)?
What is the operating system?
Did you build oracle_fdw from source?
Did you build oracle_fdw on the machine where you are running it?

I can only hope that the answers give me an idea, because as of yet, I have no idea what is going wrong.

dpevunov commented 1 year ago

HI @laurenz !

This is initially custmomer's env. But I managed to reproduce the issue on my virtalbox test env. Here are the steps to reproduce:

Install RedOS 7.3.2 (This is the Russian fork of RHEL 7). You can download it from https://redos.red-soft.ru/product/downloads/ it's free.
During installation choose "Server with GUI" and the option "Postgresql server". This will install Postgresql 12.12.
Install these packages on the OS:

source-highlight-3.1.9-2.el7.x86_64 gdb-headless-8.3-3.el7.x86_64 gdb-8.3-3.el7.x86_64 boost-regex-1.73.0-5.el7.x86_64 clang-devel-12.0.1-1.el7.x86_64 clang-tools-extra-12.0.1-1.el7.x86_64 clang-12.0.1-1.el7.x86_64 clang-libs-12.0.1-1.el7.x86_64 clang-resource-filesystem-12.0.1-1.el7.x86_64 llvm-devel-12.0.1-1.el7.x86_64 llvm-static-12.0.1-1.el7.x86_64 ncurses-devel-6.2-3.20200222.el7.x86_64 libedit-devel-3.1-24.20191231cvs.el7.x86_64 ncurses-c++-libs-6.2-3.20200222.el7.x86_64 llvm-test-12.0.1-1.el7.x86_64 llvm-12.0.1-1.el7.x86_64 postgresql-upgrade-devel-12.12-1.el7.x86_64 postgresql-upgrade-12.12-1.el7.x86_64 libnsl-2.28-6.el7.x86_64 glibc-headers-2.28-6.el7.x86_64 glibc-devel-2.28-6.el7.x86_64 glibc-common-2.28-6.el7.x86_64 glibc-2.28-6.el7.x86_64 glibc-all-langpacks-2.28-6.el7.x86_64 lz4-1.9.1-3.el7.x86_64 oracle-instantclient18.5-sqlplus-18.5.0.0.0-3.x86_64 oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64 oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64 uuid-1.6.2-1.el7.x86_64 postgresql-contrib-12.12-1.el7.x86_64 postgresql-1c-devel-12.6-2.el7.x86_64

Set this in postgres user .bash_profile

PGDATA=/var/lib/pgsql/data export PGDATA

[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile PATH=$PATH:/usr/pgsql-12/bin export PATH export LD_LIBRARY_PATH=/usr/lib/oracle/18.5/client64/lib export ORACLE_HOME=/usr/lib/oracle/18.5/client64 export PATH=$PATH:$ORACLE_HOME/bin export TNS_ADMIN=$ORACLE_HOME/network/admin

chown -R postgres:postgres /usr/pgsql-12
Set this:

vi /etc/ld.so.conf

include ld.so.conf.d/*.conf /usr/lib/oracle/18.5/client64/lib /usr/lib/oracle/18.5/client64

ldconfig

Download https://github.com/laurenz/oracle_fdw/archive/refs/tags/ORACLE_FDW_2_5_0.zip
Make and make install oracle_fdw
Create postgres database and enable it to start as a service

postgresql-setup --initdb systemctl start postgresql systemctl enable postgresql
In psql:

CREATE USER idm WITH password 'P@$$w0rd'; CREATE DATABASE idm WITH OWNER idm; GRANT ALL PRIVILEGES ON DATABASE idm TO idm; CREATE EXTENSION IF NOT EXISTS "pg_trgm"; CREATE EXTENSION IF NOT EXISTS "btree_gin"; CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION IF NOT EXISTS "dblink"; ALTER DATABASE IDM SET sap_export.export_id = ''; GRANT ALL PRIVILEGES ON SCHEMA idm TO idm; create schema idm; GRANT ALL PRIVILEGES ON SCHEMA idm TO idm; create schema hr_imp; GRANT ALL PRIVILEGES ON SCHEMA HR_IMP TO idm; create extension oracle_fdw; create server pkub_oracle foreign data wrapper oracle_fdw options (dbserver '//192.168.56.105:1521/pkub' ); create user mapping for idm server pkub_oracle options (user 'CUBE_HR_IMP', password 'password'); grant usage on foreign server pkub_oracle to idm; grant usage on foreign server pkub_oracle to postgres; create user mapping for postgres server pkub_oracle options (user 'CUBE_HR_IMP', password 'Lae4olookeed'); CREATE SCHEMA hr_imp_fdw; grant usage on schema hr_imp_fdw to idm; IMPORT FOREIGN SCHEMA "CUBE_HR_IMP" from SERVER pkub_oracle into hr_imp_fdw; GRANT ALL ON ALL TABLES IN SCHEMA hr_imp_fdw TO idm; SELECT x.* FROM hr_imp_fdw.sap_department x;

You'll get segmentation fault here.

wbr, Denis

dpevunov commented 1 year ago

Here are the answers to your questions. But I think that everything is clear from m yprevious post :)

Are you using normal, open source PostgreSQL or some modified version?

This is pre-definded version from the OS disributive.

Did you build it yourself or install it from packages? Which packages?

No, installed whith the OS.

What version of Oracle client and server are you using (SELECT oracle_diag('pkub_oracle');)?

oracle-instantclient18.5

What is the operating system?

RedOS 7.3.2

Did you build oracle_fdw from source?

Yes.

Did you build oracle_fdw on the machine where you are running it?

Yes.

laurenz commented 1 year ago

Thanks for the exact steps; that is valuable. I cannot predict when I will find time to set up a VM to reproduce this. I'll leave the bug open; let's hope I can find time sometime soon.

Did you try with Git HEAD from oracle_fdw? Perhaps the changes I have made since the last release accidentally fixed whatever the problem is.

laurenz commented 1 year ago

I installed the VM as you said, and I see that you messed up the installation at one point. After the installation, I have these packages:

postgresql-contrib-12.12-1.el7.x86_64
postgresql-server-12.12-1.el7.x86_64
postgresql-docs-12.12-1.el7.x86_64
postgresql-1c-12.6-4.el7.x86_64
postgresql-12.12-1.el7.x86_64
postgresql-1c-devel-12.6-4.el7.x86_64
postgresql-1c-libs-12.6-4.el7.x86_64

The C headers are installed from different packages than the server. postgresql-1c-devel installs in /usr/pgsql-12, while postgresql-server installs in /usr/bin, /usr/lib64 etc.

You should install postgresql-devel instead and remove the 1c packages. After that, can you still reproduce the problem?

dpevunov commented 1 year ago

Hi @laurenz !

This is not such simple :) If I install postgresql-devel then it will install libpq-devel with version 14.3-1.el7. And this will install pg_config for postresql 14, but we need pg_config from postgresql 12. That is why I had to install postgresql-1c-devel-12.6-4.el7.x86_64 - to have pg_config from postgresql 12.

wbr, Denis

chrullrich commented 1 year ago

Are you using normal, open source PostgreSQL or some modified version?

From the RedOS website, it appears that their PostgreSQL 12 packages are dedicated to support running something called the "1C:Enterprise" system. I would not be surprised if there were changes from the upstream PostgreSQL.

Can you compare the contents of the postgresql-1c-devel package to the upstream headers from the same PostgreSQL version (https://www.postgresql.org/ftp/source/v12.6/), or if not, post the RPM file somewhere we can look at it?

dpevunov commented 1 year ago

Hi @chrullrich !

I cannot compare them as I'm not such a guru. I uploaded the package to https://drive.google.com/file/d/1C77rVmlixFjFOsRwyc32LiUQrL4rn8EY/view?usp=sharing

dpevunov commented 1 year ago

Thanks for the exact steps; that is valuable. I cannot predict when I will find time to set up a VM to reproduce this. I'll leave the bug open; let's hope I can find time sometime soon.

Did you try with Git HEAD from oracle_fdw? Perhaps the changes I have made since the last release accidentally fixed whatever the problem is.

I'm not sure what is the "Git HEAD". Could you please point me to it?

wbr, Denis

chrullrich commented 1 year ago

There are significant differences between data structures in PostgreSQL's REL_12_STABLE and the files from the RPM. There may be more differing files than listed below elsewhere in the upstream distribution archive for 12.6.

What I do not know is whether any of these changes would affect oracle_fdw, particularly as it was presumably built against these headers.

$ diff -ur --exclude probes.h --exclude pg_config\*.h --exclude gram.h postgresql-12.6/src/include/ usr/pgsql-12/include/server | diffstat | fgrep -v only
 usr/pgsql-12/include/server/catalog/pg_type_d.h          |    2 +
 usr/pgsql-12/include/server/executor/execExpr.h          |    6 +++
 usr/pgsql-12/include/server/nodes/execnodes.h            |    5 ++
 usr/pgsql-12/include/server/nodes/nodeFuncs.h            |    3 +
 usr/pgsql-12/include/server/nodes/pathnodes.h            |   12 ++++++
 usr/pgsql-12/include/server/nodes/plannodes.h            |    3 +
 usr/pgsql-12/include/server/nodes/supportnodes.h         |   14 +++++++
 usr/pgsql-12/include/server/optimizer/cost.h             |    5 ++
 usr/pgsql-12/include/server/optimizer/pathnode.h         |    8 +++-
 usr/pgsql-12/include/server/optimizer/paths.h            |   26 +++++++++++++-
 usr/pgsql-12/include/server/optimizer/planmain.h         |   13 ++++++-
 usr/pgsql-12/include/server/utils/builtins.h             |    1
 usr/pgsql-12/include/server/utils/lsyscache.h            |    4 ++
 usr/pgsql-12/include/server/utils/selfuncs.h             |   27 ++++++++++++---

Example:

--- postgresql-12.6/src/include/nodes/pathnodes.h       2021-02-08 22:56:03.000000000 +0100
+++ usr/pgsql-12/include/server/nodes/pathnodes.h       2023-04-25 12:50:06.612163000 +0200
@@ -15,11 +15,13 @@
 #define PATHNODES_H

 #include "access/sdir.h"
+#include "catalog/pg_statistic.h"
 #include "fmgr.h"
 #include "lib/stringinfo.h"
 #include "nodes/params.h"
 #include "nodes/parsenodes.h"
 #include "storage/block.h"
+#include "utils/lsyscache.h"

 /*
@@ -833,6 +835,10 @@
        bool            amcanmarkpos;   /* does AM support mark/restore? */
        /* Rather than include amapi.h here, we declare amcostestimate like this */
        void            (*amcostestimate) ();   /* AM's cost estimator */
+
+       /* cache for per-tuple index statistic. That stats could be large and it
+        * will be expensive to uncompress it every time */
+       AttStatsSlot    *sslots;
 };

 /*
@@ -1372,6 +1378,12 @@
        /* Index of first partial path in subpaths; list_length(subpaths) if none */
        int                     first_partial_path;
        double          limit_tuples;   /* hard limit on output tuples, or -1 */
+       bool            pull_tlist;             /* if = true, create_append_plan()
+                                                                * should get targetlist from any
+                                                                * subpath - they are the same,
+                                                                * because the only place - append
+                                                                * index scan for range OR */
+
 } AppendPath;

 #define IS_DUMMY_APPEND(p) \

There are only two Google results for

postgresql "should get targetlist from any"

(see the long comment at the end), and both refer to "1C". I also did some spot checks against PostgreSQL 13 in case the changes were made after the 12.6 release, but did not find anything there either.

chrullrich commented 1 year ago

dpevunov wrote (at various times):

I'm not sure what is the "Git HEAD". Could you please point me to it?

Install git if you do not have it, then git clone https://github.com/laurenz/oracle_fdw.git or download https://github.com/laurenz/oracle_fdw/archive/refs/heads/master.tar.gz .

This is not such simple :) If I install postgresql-devel then it will install libpq-devel with version 14.3-1.el7. And this will install pg_config for postresql 14, but we need pg_config from postgresql 12. That is why I had to install postgresql-1c-devel-12.6-4.el7.x86_64 - to have pg_config from postgresql 12.

I'm confused, I think. In Laurenz's package list 6 comments above there is a server package in version 12.12; do you have the same? If you do, where does the pg_config in version 14 come from? If there is a postgresql-server-12.12 package there really should also be a postgresql-devel-12.12 package.

dpevunov commented 1 year ago

Install git if you do not have it, then git clone https://github.com/laurenz/oracle_fdw.git or download https://github.com/laurenz/oracle_fdw/archive/refs/heads/master.tar.gz .

Thanks for pointing me.

I'm confused, I think. In Laurenz's package list 6 comments above there is a server package in version 12.12; do you have the same? If you do, where does the pg_config in version 14 come from? If there is a postgresql-server-12.12 package there really should also be a postgresql-devel-12.12 package.

No, I wrote him a step-by-step insruction on how to create the test VM amd reproduce the issue. Please look at my long post. In our case Postgresql 12.12 is installed as an OS installation component, from the installation options. And I'm also confused why if we have Postgresql 12.12 installed execution of "yum install postgresql-devel" provides libpq-devel from the 14'th version. Also I noticed that nothing provides postgresql-devel-12.12. There is no such package. But we need to have pg_config from the 12'th version.

wbr, Denis

chrullrich commented 1 year ago

Do you have postgresql-server-devel-12.12-1.el7.x86_64.rpm? It's in the 7.3 ISO, and it looks like it has all headers that oracle_fdw needs.

chrullrich commented 1 year ago

What I do not know is whether any of these changes would affect oracle_fdw, particularly as it was presumably built against these headers.

I managed to confuse myself again. I tend to do that too often. If oracle_fdw is built using these modified headers, but loaded into an "original" server, the crashes are inevitable.

dpevunov commented 1 year ago

Do you have postgresql-server-devel-12.12-1.el7.x86_64.rpm? It's in the 7.3 ISO, and it looks like it has all headers that oracle_fdw needs.

I do not agree. 7.3 ISO contains postgresql-devel-9.2.18-1.el7.x86_64.rpm, not postgresql-server-devel-12.12-1.el7.x86_64. And the dowloaded ISO from RedOS 7.3 contains just postgresql-devel-11.8-4.el7.x86_64

wbr, Denis

laurenz commented 1 year ago

The problem is very likely that you used the headers from one PostgreSQL installation (the "c1" packages) to build PostgreSQL and copied the executable to be used with the other installation.

If one of these code bases was modified, that is a good explanation for the error. But even of both packages use the same code base, but were configured differently, that could be a reason for a crash like that.

No matter how you do it, you have to build oracle_fdw against the "devel" package from the same installation. Otherwise no guarantees.

dpevunov commented 1 year ago

I didn't build PostgreSQL. It was installed with OS. Then I needed pg_config. And as there are no postgresql-server-devel-12.12-1.el7.x86_64 I had to use postgresql-1c-devel-12.6-4.el7.x86_64 because it installs pg_config in /usr/pgsql-12/bin. I just used it to compile oracle_fdw and there were no mistakes during make or make install. Also there were no mistakes during extention creation later. You can find out yourself what packages provide pg_config for PostgreSQL 12.12:

yum provides pg_config

If oracle_fdw cannot work with such combination of compiled code then why there were no errors during the compilation?

chrullrich commented 1 year ago

7.3 ISO contains postgresql-devel-9.2.18-1.el7.x86_64.rpm, not postgresql-server-devel-12.12-1.el7.x86_64. And the dowloaded ISO from RedOS 7.3 contains just postgresql-devel-11.8-4.el7.x86_64

OK, when I wrote 7.3, I meant what you told Laurenz to use to reproduce, which was 7.3.2.

sha1(redos-MUROM-7.3.2-20221027.0-Everything-x86_64-DVD1.iso) = 01a21b926c4bf76cde0989cf94e4105dbf70f84a
sha256(redos-MUROM-7.3.2-20221027.0-Everything-x86_64-DVD1.iso) = 1e75ce4d82a33ca983e176fe287547bdc03ad7e6d17ddb84b292b2867817d2de

dpevunov commented 1 year ago

You are correct, sorry. I miised this rpm somehow, I installed it now but it still doesn't provide pg_config needed by oracle_fdw. It installs:

keyutils-libs-devel-1.6-1.el7.x86_64.rpm libverto-devel-0.3.0-5.el7.x86_64.rpm libsepol-devel-2.9-1.el7.x86_64.rpm libcom_err-devel-1.44.6-4.el7.3.x86_64.rpm krb5-devel-1.20.1-1.el7.x86_64.rpm libselinux-devel-2.9-2.4.el7.3.x86_64.rpm pcre2-devel-10.36-4.el7.3.x86_64.rpm libicu-devel-65.1-4.el7.x86_64.rpm postgresql-server-devel-12.12-1.el7.x86_64.rpm

If we check what provides pg_config then we see:

libpq-devel-12.5-1.el7.i686 : Development files for building PostgreSQL client tools Репозиторий : base Совпадения с: Имя файла : /usr/bin/pg_config

libpq-devel-12.5-1.el7.x86_64 : Development files for building PostgreSQL client tools Репозиторий : base Совпадения с: Имя файла : /usr/bin/pg_config

libpq-devel-12.7-1.el7.i686 : Development files for building PostgreSQL client tools Репозиторий : updates Совпадения с: Имя файла : /usr/bin/pg_config

libpq-devel-12.7-1.el7.x86_64 : Development files for building PostgreSQL client tools Репозиторий : updates Совпадения с: Имя файла : /usr/bin/pg_config

libpq-devel-13.4-1.el7.i686 : Development files for building PostgreSQL client tools Репозиторий : updates Совпадения с: Имя файла : /usr/bin/pg_config

libpq-devel-13.4-1.el7.x86_64 : Development files for building PostgreSQL client tools Репозиторий : updates Совпадения с: Имя файла : /usr/bin/pg_config

libpq-devel-13.4-2.el7.i686 : Development files for building PostgreSQL client tools Репозиторий : updates Совпадения с: Имя файла : /usr/bin/pg_config

libpq-devel-13.4-2.el7.x86_64 : Development files for building PostgreSQL client tools Репозиторий : updates Совпадения с: Имя файла : /usr/bin/pg_config

libpq-devel-14.2-1.el7.i686 : Development files for building PostgreSQL client tools Репозиторий : updates Совпадения с: Имя файла : /usr/bin/pg_config

libpq-devel-14.2-1.el7.x86_64 : Development files for building PostgreSQL client tools Репозиторий : updates Совпадения с: Имя файла : /usr/bin/pg_config

libpq-devel-14.3-1.el7.i686 : Development files for building PostgreSQL client tools Репозиторий : updates Совпадения с: Имя файла : /usr/bin/pg_config

libpq-devel-14.3-1.el7.x86_64 : Development files for building PostgreSQL client tools Репозиторий : updates Совпадения с: Имя файла : /usr/bin/pg_config

postgresql-devel-11.8-4.el7.i686 : PostgreSQL development header files and libraries Репозиторий : base Совпадения с: Имя файла : /usr/bin/pg_config

postgresql-devel-11.8-4.el7.x86_64 : PostgreSQL development header files and libraries Репозиторий : base Совпадения с: Имя файла : /usr/bin/pg_config

For version 12 of PostgreSQL it is provided by libpq-devel-12.7-1.el7.x86_64 : Development files for building PostgreSQL client tools. And I don't see such rpm for 12.12.

Also I managed to create another test. I used OEL 8.5 as OS and installed PostgresPro 15.2. On this VM orac le_fdw is working without issues. Now I'm asknig my customer to reinstall their test env.

wbr, Denis

chrullrich commented 1 year ago

If oracle_fdw cannot work with such combination of compiled code then why there were no errors during the compilation?

oracle_fdw.so and the PostgreSQL server itself exchange data structures at run time. The crashes happen because the two have different ideas about the size and contents of some of these data structures. When you compiled oracle_fdw, the headers from the 1c devel package told the compiler what the structures look like. As long as all the things that oracle_fdw actually uses are present, the compiler is happy – all it needs to know is where to find the data the program wants to read or write.

At run time, there are no names or data types anymore, everything is a bag of bits. When the two programs exchange data, each relies on the other putting the same bits in the same place. Because the definitions differ, the side that receives data misinterprets it and, for example, treats some data as a pointer that turns out to point to nowhere.

chrullrich commented 1 year ago

For version 12 of PostgreSQL it is provided by libpq-devel-12.7-1.el7.x86_64 : Development files for building PostgreSQL client tools. And I don't see such rpm for 12.12.

Perhaps there was literally no change to libpq between 12.7 and 12.12, and someone decided not to repackage the same code with a different version? Anyway, if you can install this package without dependency issues, it should work.

Also I managed to create another test. I used OEL 8.5 as OS and installed PostgresPro 15.2. On this VM orac le_fdw is working without issues. Now I'm asknig my customer to reinstall their test env.

If this means that your customer is not absolutely tied to version 12 after all, there may be another option. There is what looks like a full set of PostgreSQL packages for version 14.7 at http://repo.red-soft.ru/redos/7.3/x86_64/extras/postgresql14-73/. Can you perhaps use this?

laurenz commented 1 year ago

I just installed postgresql-server-devel-12.12-1.el7.x86_64 from CDROM on my virtual machine (and removed the "c1" packages). This weird PostgreSQL distribution installs pg_config as /usr/bin/pg_server_config. So you'd have to make a hack like

ln -s pg_server_config /bin/pg_config

After that, you should be able to build oracle_fdw.

Frankly, I would recommend against using a questionable PostgreSQL distribution like that. Use the packages from the PostgreSQL site.

dpevunov commented 1 year ago

Hi Laurenz! Thanks for the solution. Now everything is woking.

wbr, Denis

dpevunov commented 1 year ago

If this means that your customer is not absolutely tied to version 12 after all, there may be another option. There is what looks like a full set of PostgreSQL packages for version 14.7 at http://repo.red-soft.ru/redos/7.3/x86_64/extras/postgresql14-73/. Can you perhaps use this?

No, they are not tied to version 12. I think it was used as it is already present in the OS distribution.

wbr, Denis

dpevunov commented 1 year ago

oracle_fdw.so and the PostgreSQL server itself exchange data structures at run time. The crashes happen because the two have different ideas about the size and contents of some of these data structures. When you compiled oracle_fdw, the headers from the 1c devel package told the compiler what the structures look like. As long as all the things that oracle_fdw actually uses are present, the compiler is happy – all it needs to know is where to find the data the program wants to read or write.

At run time, there are no names or data types anymore, everything is a bag of bits. When the two programs exchange data, each relies on the other putting the same bits in the same place. Because the definitions differ, the side that receives data misinterprets it and, for example, treats some data as a pointer that turns out to point to nowhere.

Thanks for the explanation. Now it is clear to me.

wbr, Denis

laurenz commented 1 year ago

Great this could be fixed. Thanks all for your cooperation!

laurenz / oracle_fdw

oracleIterateForeignScan (node=<optimized out>) at oracle_fdw.c:1348 #583

0 oracleIterateForeignScan (node=) at oracle_fdw.c:1348

1 0x0000000000643c7b in ForeignNext ()

2 0x000000000061f5d1 in ExecScan ()

3 0x0000000000636d69 in ExecLimit ()

4 0x0000000000616f1b in standard_ExecutorRun ()

5 0x000000000076b6db in PortalRunSelect ()

6 0x000000000076ccce in PortalRun ()

7 0x0000000000768e3f in exec_simple_query ()

8 0x000000000076a727 in PostgresMain ()

9 0x00000000006f5e46 in ServerLoop ()

10 0x00000000006f6ca9 in PostmasterMain ()

11 0x0000000000481eae in main ()