installcheck not passed with pg12 pg13 pg14

[jerome-peng]

i use pg_bulkload-VERSION3_1_19.tar.gz execute "make installcheck" ,get " 1 of 10 tests failed. " for test load_function

[postgres@pg pg_bulkload-VERSION3_1_19]$ make installcheck
Makefile:33: warning: overriding recipe for target check' /opt/pg13/lib/postgresql/pgxs/src/makefiles/pgxs.mk:433: warning: ignoring old recipe for target check'
make[1]: Entering directory `/opt/pg_extension/pg_bulkload-VERSION3_1_19/bin'
cp sql/init-extension-v2.sql sql/init-13.sql
cp sql/init-13.sql sql/init.sql
cp sql/load_bin-v2.sql sql/load_bin-13.sql
cp sql/load_bin-13.sql sql/load_bin.sql
cp sql/load_csv-v2.sql sql/load_csv-13.sql
cp sql/load_csv-13.sql sql/load_csv.sql
cp sql/load_function-v3.sql sql/load_function-13.sql
cp sql/load_function-13.sql sql/load_function.sql
cp sql/load_filter-v3.sql sql/load_filter-13.sql
cp sql/load_filter-13.sql sql/load_filter.sql
cp sql/write_bin-v2.sql sql/write_bin-13.sql
cp sql/write_bin-13.sql sql/write_bin.sql
/opt/pg13/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/opt/pg13/bin' --dbname=contrib_regression --encoding=UTF8 init load_bin load_csv load_remote load_function load_encoding load_check load_filter load_parallel write_bin
(using postmaster on Unix socket, port 1306)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries ==============
test init ... ok 3706 ms
test load_bin ... ok 1031 ms
test load_csv ... ok 638 ms
test load_remote ... ok 199 ms
test load_function ... FAILED 2560 ms
test load_encoding ... ok 447 ms
test load_check ... ok 307 ms
test load_filter ... ok 1018 ms
test load_parallel ... ok 4831 ms
test write_bin ... ok 698 ms

=======================
1 of 10 tests failed.

The differences that caused some tests to fail can be viewed in the
file "/opt/pg_extension/pg_bulkload-VERSION3_1_19/bin/regression.diffs". A copy of the test summary that you see
above is saved in the file "/opt/pg_extension/pg_bulkload-VERSION3_1_19/bin/regression.out".

make[1]: *** [installcheck] Error 1
make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/bin' make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/lib'
make[1]: Nothing to be done for installcheck'. make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/lib'
make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/util' make[1]: Nothing to be done for installcheck'.
make[1]: Leaving directory `/opt/pg_extension/pg_bulkload-VERSION3_1_19/util'
make: *** [installcheck] Error 2

[jerome-peng]

regression.zip

[jerome-peng]

i test pg12 pg13 pg14,almost the same error message: "tests failed for test load_function"

[mikecaat]

Hi, thanks for reporting.

I tested in my environments and I found that all tests passed

• Ubuntu 20.04
• PostgreSQL 13.6
• pg_bulkload source code: pg_bulkload-3.1.19.tar.gz

The test procedure is following.

# start the PostgreSQL server
$ initdb -D data
$ pg_ctl -D data start 

# build and test pg_bulkload
$ wget https://github.com/ossc-db/pg_bulkload/releases/download/VERSION3_1_19/pg_bulkload-3.1.19.tar.gz
$ tar xzvf pg_bulkload-3.1.19.tar.gz
$ cd pg_bulkload-3.1.19
$ make clean
$ make install
$ make installcheck
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test init                         ... ok         2696 ms
test load_bin                     ... ok         3073 ms
test load_csv                     ... ok         1926 ms
test load_remote                  ... ok          664 ms
test load_function                ... ok         4285 ms
test load_encoding                ... ok         1385 ms
test load_check                   ... ok         1697 ms
test load_filter                  ... ok         4300 ms
test load_parallel                ... ok         6702 ms
test write_bin                    ... ok         1269 ms

======================
 All 10 tests passed.
======================

Please let me know your environment and your reproducible procedure.

Regards,

[jerome-peng]

centos7.6 x86_64 pg12.10 pg13.6 pg14.2

[jerome-peng]

eg: pg13.6

postgres@pg pg_bulkload-VERSION3_1_19]$ cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core)

[postgres@pg pg_bulkload-VERSION3_1_19]$ psql
psql (13.6)
Type "help" for help.

postgres=# \q

[postgres@pg pg_bulkload-VERSION3_1_19]$ make uninstall
[postgres@pg pg_bulkload-VERSION3_1_19]$ make clean
[postgres@pg pg_bulkload-VERSION3_1_19]$ make

[postgres@pg pg_bulkload-VERSION3_1_19]$ make install
Makefile:33: warning: overriding recipe for target check' /opt/pg13/lib/postgresql/pgxs/src/makefiles/pgxs.mk:433: warning: ignoring old recipe for target check'
make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/bin' make[1]: Nothing to be done for all'.
make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/bin' make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/lib'
make[1]: Nothing to be done for all'. make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/lib'
make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/util' make[1]: Nothing to be done for all'.
make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/util' make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/bin'
/usr/bin/mkdir -p '/opt/pg13/bin'
make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/bin' make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/lib'
/usr/bin/mkdir -p '/opt/pg13/lib/postgresql'
/usr/bin/mkdir -p '/opt/pg13/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/pg13/share/postgresql/extension'
make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/lib' make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/util'
/usr/bin/mkdir -p '/opt/pg13/lib/postgresql'
/usr/bin/mkdir -p '/opt/pg13/share/postgresql/contrib'
make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/util' make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/bin'
/usr/bin/mkdir -p '/opt/pg13/bin'
/usr/bin/install -c pg_bulkload '/opt/pg13/bin'
/usr/bin/install -c -m 755 .//postgresql '/opt/pg13/bin/'
make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/bin' make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/lib'
/usr/bin/mkdir -p '/opt/pg13/lib/postgresql'
/usr/bin/mkdir -p '/opt/pg13/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/pg13/share/postgresql/extension'
/usr/bin/install -c -m 755 pg_bulkload.so '/opt/pg13/lib/postgresql/pg_bulkload.so'
/usr/bin/install -c -m 644 .//pg_bulkload.control '/opt/pg13/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//pg_bulkload--1.0.sql .//pg_bulkload--unpackaged--1.0.sql .//uninstall_pg_bulkload.sql pg_bulkload.sql '/opt/pg13/share/postgresql/extension/'
make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/lib' make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/util'
/usr/bin/mkdir -p '/opt/pg13/lib/postgresql'
/usr/bin/mkdir -p '/opt/pg13/share/postgresql/contrib'
/usr/bin/install -c -m 755 pg_timestamp.so '/opt/pg13/lib/postgresql/pg_timestamp.so'
/usr/bin/install -c -m 644 .//uninstall_pg_timestamp.sql pg_timestamp.sql '/opt/pg13/share/postgresql/contrib/'
make[1]: Leaving directory `/opt/pg_extension/pg_bulkload-VERSION3_1_19/util'

[postgres@pg pg_bulkload-VERSION3_1_19]$ make installcheck
Makefile:33: warning: overriding recipe for target check' /opt/pg13/lib/postgresql/pgxs/src/makefiles/pgxs.mk:433: warning: ignoring old recipe for target check'
make[1]: Entering directory `/opt/pg_extension/pg_bulkload-VERSION3_1_19/bin'
cp sql/init-extension-v2.sql sql/init-13.sql
cp sql/init-13.sql sql/init.sql
cp sql/load_bin-v2.sql sql/load_bin-13.sql
cp sql/load_bin-13.sql sql/load_bin.sql
cp sql/load_csv-v2.sql sql/load_csv-13.sql
cp sql/load_csv-13.sql sql/load_csv.sql
cp sql/load_function-v3.sql sql/load_function-13.sql
cp sql/load_function-13.sql sql/load_function.sql
cp sql/load_filter-v3.sql sql/load_filter-13.sql
cp sql/load_filter-13.sql sql/load_filter.sql
cp sql/write_bin-v2.sql sql/write_bin-13.sql
cp sql/write_bin-13.sql sql/write_bin.sql
/opt/pg13/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/opt/pg13/bin' --dbname=contrib_regression --encoding=UTF8 init load_bin load_csv load_remote load_function load_encoding load_check load_filter load_parallel write_bin
(using postmaster on Unix socket, port 1306)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries ==============
test init ... ok 3457 ms
test load_bin ... ok 1102 ms
test load_csv ... ok 631 ms
test load_remote ... ok 205 ms
test load_function ... FAILED 1683 ms
test load_encoding ... ok 430 ms
test load_check ... ok 297 ms
test load_filter ... ok 1121 ms
test load_parallel ... ok 4356 ms
test write_bin ... ok 827 ms

=======================
1 of 10 tests failed.

The differences that caused some tests to fail can be viewed in the
file "/opt/pg_extension/pg_bulkload-VERSION3_1_19/bin/regression.diffs". A copy of the test summary that you see
above is saved in the file "/opt/pg_extension/pg_bulkload-VERSION3_1_19/bin/regression.out".

make[1]: *** [installcheck] Error 1
make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/bin' make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/lib'
make[1]: Nothing to be done for installcheck'. make[1]: Leaving directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/lib'
make[1]: Entering directory /opt/pg_extension/pg_bulkload-VERSION3_1_19/util' make[1]: Nothing to be done for installcheck'.
make[1]: Leaving directory `/opt/pg_extension/pg_bulkload-VERSION3_1_19/util'
make: *** [installcheck] Error 2

[mikecaat]

Thanks,

I also tested with CentOS 7.9 and it works.
How do you create the data directory?

Please to make new data cluster and test the pg_bulkload.

# start the PostgreSQL server
$ initdb -D data
$ pg_ctl -D data start 

The regression.diff you shared said the following.

+ERROR: query failed: ERROR:  cache lookup failed for type 1024
+CONTEXT:  SQL function "load_function2" statement 1
+DETAIL: query was: SELECT * FROM pgbulkload.pg_bulkload($1)

So, I would like to examine whether the root cause is pg_bulkload or not.
Please check that you can execute the following query.

CREATE TABLE customer (
    c_id            int4 NOT NULL,
    c_d_id          int2 NOT NULL,
    c_w_id          int4 NOT NULL,
    c_first         varchar(16) NOT NULL,
    c_middle        char(2) NOT NULL,
    c_last          varchar(16) NOT NULL,
    c_street_1      varchar(20) NOT NULL,
    c_street_2      varchar(20) NOT NULL,
    c_city          varchar(20) NOT NULL,
    c_state         char(2) NOT NULL,
    c_zip           char(9) NOT NULL,
    c_phone         char(16) NOT NULL,
    c_since         timestamp NOT NULL,
    c_credit        char(2) NOT NULL,
    c_credit_lim    numeric(16,4) NOT NULL,
    c_discount      numeric(16,4) NOT NULL,
    c_balance       numeric(16,4) NOT NULL,
    c_ytd_payment   numeric(16,4) NOT NULL,
    c_payment_cnt   float4 NOT NULL,
    c_delivery_cnt  float8 NOT NULL,
    c_data          varchar(500) NOT NULL
) WITH (fillfactor=20);

ALTER TABLE customer ADD PRIMARY KEY (c_id, c_w_id, c_d_id);
CREATE INDEX idx_btree ON customer USING btree (c_d_id, c_last);
CREATE INDEX idx_btree_fn ON customer USING btree ((abs(c_w_id) + c_d_id));
CREATE INDEX idx_hash ON customer USING hash (c_d_id);
CREATE INDEX idx_hash_fn ON customer USING hash ((abs(c_w_id) + c_d_id));

CREATE FUNCTION public.load_function2(int2, int4, varchar(500), VARIADIC int4[]) RETURNS SETOF customer AS
$$
	SELECT $4[i], $1, $2, i::varchar(16), 'AA'::char(2), 'AAAAAAAAAAAAAAAA'::varchar(16), 'c_street_1          '::varchar(20), 'c_street_2          '::varchar(20), 'AAAAAAAAAAAAAAAAAAAA'::varchar(20), 'AA'::char(2), 'AAAAAAAAA'::char(9), 'AAAAAAAAAAAAAAAA'::char(16), '2006-01-01 12:34:56'::timestamp, 'AA'::char(2), '12345.6789'::numeric(16,4), '12345.6789'::numeric(16,4), '12345.6789'::numeric(16,4), '12345.6789'::numeric(16,4), '12345.6789'::float4, '12345.6789'::float8, $3 FROM generate_subscripts($4, 1) g(i);
$$ LANGUAGE SQL;

SELECT public.load_function2('216', 0, 'function2', -2, 2, 4, 6, 8, 10, 12);

[jerome-peng]

i use a cleaning PGDATA it's ok.

so i check the postgresql.conf, found the difference: when i add shared_preload_libraries ='pgaudit', it occured.

[jerome-peng]

i test pg12(with pgaudit-1.4.3) 、pg13 (with pgaudit-1.5.2) 、pg14 (with pgaudit-1.6.2)，when drop the "pgaudit" in shared_preload_libraries, all the 10 tests passed.

[mikecaat]

Thanks, I could reproduce the issue.

[zwyan0]

VERSION3_1_20 fixed this issue. So I close this issue.