Problem description

Recently I've partitioned a big table with the help of pg_pathman. After excluding parent table from the query plan I sometimes get an error . The error text on the server side reads as follows:

2018-09-03 12:14:47.791 UTC [13158] ERROR:  attribute 6 of type root_dict_1 has wrong type2018-09-03 12:14:47.791 UTC [13158] DETAIL:  Table has type text, but query expects bigint.

Stack traces on the client look like that:

org.postgresql.util.PSQLException: ERROR: attribute 6 of type root_dict_1 has wrong type      Detail: Table has type text, but query expects bigint.       at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)       at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)       at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)       at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)       at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)       at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:150)       at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:113)       at com.github.pg_pathman_error.App.testBrokenQuery(App.java:56)       at com.github.pg_pathman_error.App.main(App.java:43)

Such errors occur when my app written in Java queries the partitioned table via jdbc driver. If the table is not partitioned, it works well. If parent table is not excluded from the plan, it also works correctly. Also, I tried to reproduce the problem by manually running the same query many times in PgAdmin, but didn't succeed.

I managed to reproduce the problem and createda small repo on github where you can finda vagrant file which sets up the environment. In case vagrant is not convenient for you, you can manually set everything up just by typing in commands inconfig.vm.provision section .

It seems to be important that before creating partitions I changed the structure of the table to normalize it. A new table which keeps unique code+value pairs was created. Then a foreign key was added to the table to be partitioned. After filling foreign key values unnecessary columns were dropped. Only after that create_hash_partitions was called. For complete steps seedb_migrations.sql.

The code that actually executes queries and receives errors is located inApp.java. This is a simple jdbc program that connects to database and tries to run the same query in a loop. The weirdest part is that it is always the 10th iteration in the reproduction scenario that fails. First 9 iterations finish successfully.

When I return parent table to the query plan the error disappears completely.

Environment

• CentOS 7
• Postgresql Server 10.5
• pg_pathman
• latest jdbc driver

SELECT * FROM pg_extension;

extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
------------+----------+--------------+----------------+------------+---------------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
pg_pathman | 10 | 2200 | f | 1.4 | {16386,16397} | {"",""}
(2 rows)

SELECT version();

PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit

SELECT get_pathman_lib_version()

1.4.13