postgres error: mergejoin input data is out of order

Question

I have two tables in my postgres database, relations and versioninfo:

postgres=# \d+ relations
                                                                 Table "public.relations"
                Column                |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------------------------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 system_name                          | character varying           |           |          |         | extended |             |              | 
 from_package_name                    | character varying           |           |          |         | extended |             |              | 
 from_version                         | semver                      |           |          |         | plain    |             |              | 
 to_package_name                      | character varying           |           |          |         | extended |             |              | 
 actual_requirement                   | character varying           |           |          |         | extended |             |              | 
 to_version                           | semver                      |           |          |         | plain    |             |              | 
 to_package_highest_available_release | character varying           |           |          |         | extended |             |              | 
 interval_start                       | timestamp without time zone |           |          |         | plain    |             |              | 
 interval_end                         | timestamp without time zone |           |          |         | plain    |             |              | 
 is_out_of_date                       | boolean                     |           |          |         | plain    |             |              | 
 is_regular                           | boolean                     |           |          |         | plain    |             |              | 
 warnings                             | character varying           |           |          |         | extended |             |              | 
Access method: heap

postgres=# \d+ versioninfo
                                                    Table "public.versioninfo"
    Column    |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 system_name  | character varying           |           |          |         | extended |             |              | 
 package_name | character varying           |           |          |         | extended |             |              | 
 version_name | semver                      |           |          |         | plain    |             |              | 
 release_date | timestamp without time zone |           |          |         | plain    |             |              | 
Access method: heap

And I am joining them on some criteria:

CREATE TABLE exclude_records AS
SELECT R2.system_name, R2.from_package_name, R2.from_version, R2.to_package_name, COUNT(*) AS count_no
FROM relations R2
INNER JOIN versioninfo V3
ON R2.from_package_name = V3.package_name AND R2.from_version = V3.version_name AND R2.system_name = V3.system_name
INNER JOIN versioninfo V4
ON R2.to_package_name = V4.package_name AND R2.to_version = V4.version_name AND R2.system_name = V4.system_name
GROUP BY R2.system_name, R2.from_package_name, R2.from_version, R2.to_package_name
HAVING COUNT(*) > 1;

But this query returns error:

ERROR:  mergejoin input data is out of order 

SQL state: XX000

Cannot figure out what the problem is. My understanding is that for joining the tables using inner join, postgres is using mergejoin (because the ON condition contains equal operators and those two table are very large). And that may be the reason for this error?

Just curious: Do you get the error if you cast the from_version, to_version, and version_name to text in your query? e.g. . . . AND R2.from_version::text = v3.version_name::text. . . and again at R2.to_version::text = V4.version_name::text? I ask because there may be a bug or missing implementation in the semver extension that causes this. — Mike Organek
– Mike Organek, Commented Aug 27, 2023 at 16:48
Weirdly enough, after converting them to VARCHAR it is working properly. Thanks for the suggestion. But still not sure why using SEMVER was causing the error. — Imranur Rahman
– Imranur Rahman, Commented Aug 27, 2023 at 19:28
I suggest that you please take this up with the developers of the semver extension. Your problem report here is very detailed, so if the project is under active development, then the developer(s) should be able to pinpoint the root cause fairly quickly. — Mike Organek
– Mike Organek, Commented Aug 27, 2023 at 20:02
It could be index corruption. Does a REINDEX of the involved tables fix the problem? — Laurenz Albe
– Laurenz Albe, Commented Aug 28, 2023 at 6:03
I had the same problem, I'm having trouble fixing it. But a reindex of the indexes of all the offending tables solved the problem. Thanks a lot @LaurenzAlbe — Jeff
– Jeff, Commented Sep 18, 2024 at 15:38

Imranur Rahman · Accepted Answer · 2024-09-21 23:38:00Z

0

According to the discussion, the problem is solved by converting to VARCHAR or using a REINDEX. Thanks for the comments!

answered Sep 21, 2024 at 23:38

Imranur Rahman

3271 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres error: mergejoin input data is out of order

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related