The title is an error, a minority error, which may be related to postgresql or citus. However, this error can only exist in one place on the network.

Story background

The wrong background is used benchmarksql The tool tests the TPCC performance of citus. Because citus is a distributed plug-in of PG, which can turn multiple stand-alone PG nodes into a distributed database, the use of this plug-in of sub database and sub table requires an additional operation: establishing partition rules.

In citus, it is to build distributed tables, for example, partition the user table based on id.

SELECT create_distributed_table('user', 'id');

The TPCC specification involves nine tables, which are divided into five scenarios. Multiple join s and transaction updates are involved between tables, so this fragmentation rule is not so easy to build.

I started with the tpcc test of citus and found the same issue: https://github.com/citusdata/citus/issues/4126.
This guy gave a scheme to build distributed tables, but the measured performance was too poor. But they got through.

So I built the following distributed tables based on the suggestion of my brother: for reference tables, please refer to the citus documentation.

SELECT create_distributed_table('bmsql_config', 'cfg_name');
SELECT create_distributed_table('bmsql_new_order', 'no_w_id');
SELECT create_distributed_table('bmsql_oorder', 'o_w_id');
SELECT create_distributed_table('bmsql_order_line', 'ol_w_id');
SELECT create_distributed_table('bmsql_history','hist_id');
SELECT create_distributed_table('bmsql_customer','c_w_id');

SELECT create_reference_table('bmsql_item');
SELECT create_reference_table('bmsql_district');
SELECT create_reference_table('bmsql_warehouse');
SELECT create_reference_table('bmsql_stock');

Then, when benchmark SQL is used for testing, the following error messages will appear:

22:05:39,296 [Thread-1] ERROR  jTPCCTData : Unexpected SQLException in PAYMENT
22:05:39,296 [Thread-1] ERROR  jTPCCTData : ERROR: cannot perform an INSERT without a partition column value
org.postgresql.util.PSQLException: ERROR: cannot perform an INSERT without a partition column value
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:365)
	at jTPCCTData.executePayment(jTPCCTData.java:930)
	at jTPCCTData.execute(jTPCCTData.java:99)
	at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:160)
	at jTPCCTerminal.run(jTPCCTerminal.java:88)
	at java.lang.Thread.run(Thread.java:748)

The light of dawn

After a night of thinking, I decided to let go of my past grievances and start on the road again.

First, the error information is searched in google, and only one exact match is found:

At this time, my heart is broken. The difficulty is not the problem, but loneliness.

So from This is a message Starting, I found that this man also encountered the same problem, but no one answered it.

This is really sad. This road has come to an end, so there is only another way.

So I continued to start with the guy's ssue: https://github.com/citusdata/citus/issues/4126.

Following the developers' answers, I found the official test benchmark scheme: https://github.com/citusdata/ch-benchmark.

This warehouse is based on HammarDB There are not many files in the citus test developed by the test framework. I think I can certainly find the table creation statement. So I looked for them one by one. I didn't expect it was in the patch file: https://github.com/citusdata/ch-benchmark/blob/master/HammerDB-patch/4.0.patch

After reading, key information is extracted:

SELECT create_distributed_table('bmsql_customer', 'c_w_id');
SELECT create_distributed_table('bmsql_district', 'd_w_id');
SELECT create_distributed_table('bmsql_history', 'h_w_id');
SELECT create_distributed_table('bmsql_warehouse', 'w_id');
SELECT create_distributed_table('bmsql_stock', 's_w_id');
SELECT create_distributed_table('bmsql_new_order', 'no_w_id');
SELECT create_distributed_table('bmsql_oorder', 'o_w_id');
SELECT create_distributed_table('bmsql_order_line', 'ol_w_id');
SELECT create_reference_table('bmsql_item');

This is the final TPCC distributed table creation statement. Using this method to build distributed tables can correctly test the results.

Happy ending

In fact, this problem is very small, but I suddenly realized something deeper.
As a programmer, understanding things and solving problems are not equivalent. I don't know the details of how citus implements distributed tables. I only know the concepts of distributed tables and reference tables. Coupled with powerful search engine capabilities, I can completely solve the problems that others have encountered.
Therefore, I can only be a programmer who solves existing problems, not a scientist who creates things.

Suddenly I feel ridiculous and helpless. Programmers think they are creating products and the world. In fact, most people just repeatedly create wheels and solve problems solved by others.
Aware of this, I found that this is the problem of the whole mankind. Mankind has the Internet and information interconnection, which is within reach. But everyone needs to learn constantly from birth to acquire these knowledge. In the long river of time, human life is short-lived, how short it is. But it can also bloom some charming.
I suddenly thought of Master Liu Cixin's science fiction "rural teacher". If human memory can be inherited, I don't need the teacher's painstaking efforts.
Memory is not equal to knowledge, knowledge is not equal to ability, and solving problems is not equal to creating the future.
I smiled and closed the computer.