`no partition of relation "builds" found for row` error when submitting builds.

[duzinkie]

Hello, I'm evaluating XCMetrics for our project and run into an error in a quick prototype setup I've created (I've setup the spotify/xcmetrics docker container along with redis, both running in docker compose and also a postgres server that runs outside of docker)

Whenever a build I've configured to send metrics executes, xcmetrics backend contains the following logs:

xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:18 [ProcessMetricsJob] message dequeued [job_id: 29B6B3C4-49B0-4475-8F9E-1922FF2D474D]
xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:18 [ProcessMetricsJob] fetching log from file:///tmp/69C932BD-C5E8-43FA-960F-CE58558D6838/267AD278-DF66-4807-BF23-31EBAAACEFF9.xcactivitylog [job_id: 29B6B3C4-49B0-4475-8F9E-1922FF2D474D]
xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:18 [ProcessMetricsJob] log fetched to file:///tmp/1681F40B-2399-414B-9D29-4B1912040C3D/8A431D3D-FE8D-4899-B45D-B0716775986E.xcactivitylog [job_id: 29B6B3C4-49B0-4475-8F9E-1922FF2D474D]
xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:26 [ProcessMetricsJob] log parsed file:///tmp/69C932BD-C5E8-43FA-960F-CE58558D6838/267AD278-DF66-4807-BF23-31EBAAACEFF9.xcactivitylog [job_id: 29B6B3C4-49B0-4475-8F9E-1922FF2D474D]
xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:26 [PostgressMetricsRepository] creating daily partitions
xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:26 [PostgressMetricsRepository] creating daily partitions
xcmetrics-dev-1 |[ ERROR ] [ProcessMetricsJob] error inserting log from file:///tmp/69C932BD-C5E8-43FA-960F-CE58558D6838/267AD278-DF66-4807-BF23-31EBAAACEFF9.xcactivitylog: server: no partition of relation "builds" found for row (ExecFindPartition) [job_id: 29B6B3C4-49B0-4475-8F9E-1922FF2D474D]
xcmetrics-dev-1 |[ ERROR ] Job failed, retrying... server: no partition of relation "builds" found for row (ExecFindPartition) [job_id: 29B6B3C4-49B0-4475-8F9E-1922FF2D474D, job_name: ProcessMetricsJob, queue: default]
xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:26 [ProcessMetricsJob] message dequeued [job_id: 29B6B3C4-49B0-4475-8F9E-1922FF2D474D]
xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:26 [ProcessMetricsJob] fetching log from file:///tmp/69C932BD-C5E8-43FA-960F-CE58558D6838/267AD278-DF66-4807-BF23-31EBAAACEFF9.xcactivitylog [job_id: 29B6B3C4-49B0-4475-8F9E-1922FF2D474D]
xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:26 [ProcessMetricsJob] log fetched to file:///tmp/1530E5C8-B77F-4A74-863D-A858905CFE1C/07DC4EB1-BC0D-4293-AB99-70C763F77DA0.xcactivitylog [job_id: 29B6B3C4-49B0-4475-8F9E-1922FF2D474D]
xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:34 [ProcessMetricsJob] log parsed file:///tmp/69C932BD-C5E8-43FA-960F-CE58558D6838/267AD278-DF66-4807-BF23-31EBAAACEFF9.xcactivitylog [job_id: 29B6B3C4-49B0-4475-8F9E-1922FF2D474D]
xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:34 [PostgressMetricsRepository] creating daily partitions
xcmetrics-dev-1 |[ NOTICE ] 06/10/2023 23:50:34 [PostgressMetricsRepository] creating daily partitions
xcmetrics-dev-1 |[ ERROR ] [ProcessMetricsJob] error inserting log from file:///tmp/69C932BD-C5E8-43FA-960F-CE58558D6838/267AD278-DF66-4807-BF23-31EBAAACEFF9.xcactivitylog: server: no partition of relation "builds" found for row (ExecFindPartition) [job_id: 29B6B3C4-49B0-4475-8F9E-1922FF2D474D]
...

and then my postgres server contains the following:

2023-10-06 16:50:42.305 PDT [70258] STATEMENT:  INSERT INTO "builds" ("tag", "category", "compiled_count", "machine_name", "duration", "start_timestamp_microseconds", "project_name", "error_count", "user_id", "user_id_256", "start_timestamp", "warning_count", "compilation_end_timestamp_microseconds", "was_suspended", "compilation_duration", "day", "id", "end_timestamp", "build_status", "schema", "end_timestamp_microseconds", "is_ci", "compilation_end_timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23) RETURNING "id"
2023-10-06 16:50:50.163 PDT [70258] ERROR:  no partition of relation "builds" found for row
2023-10-06 16:50:50.163 PDT [70258] DETAIL:  Partition key of the failing row contains (day) = (2023-10-05).

from a cursory look, database tables appear to have been initialized, but the only one that is populated upon a build is job_log_entries, each build creating a row that looks roughly like this:

[
  {
    "id": "29B6B3C4-49B0-4475-8F9E-1922FF2D474D",
    "log_file": "267AD278-DF66-4807-BF23-31EBAAACEFF9.xcactivitylog",
    "log_url": "file:///tmp/69C932BD-C5E8-43FA-960F-CE58558D6838/267AD278-DF66-4807-BF23-31EBAAACEFF9.xcactivitylog",
    "status": "failed",
    "error": "server: no partition of relation \"builds\" found for row (ExecFindPartition)",
    "queued_at": "2023-10-06 23:50:18.634539 +00:00",
    "dequeued_at": "2023-10-06 23:50:18.917057 +00:00",
    "finished_at": "2023-10-06 23:50:50.225589 +00:00",
    "created_at": "2023-10-06 23:50:18.635936 +00:00",
    "updated_at": "2023-10-06 23:50:50.225608 +00:00"
  }
]

I'd appreciate any guidance on how to troubleshoot and/or fix this issue so I could continue my evaluation.

[duzinkie]

I did some more research... and it appears that the timestamps of the data I'm sending to xcmetrics are dated... 1 day back

I'm assuming those partitions were created by xcmetrics backend

Partition key: LIST (day)
Partitions: public.builds_20231006 FOR VALUES IN ('2023-10-06')
            public.builds_20231010 FOR VALUES IN ('2023-10-10')

however the data that is being inserted to the database carries the timestamp of the day before (or it interpreted at such) as I can see error logs from the day of the issue report mention 10-05 and from today 10-09. Not exactly sure where the invalid timestamps are taken from but at least I got a thread to continue my research on.

[duzinkie]

closing the issue for now, this must have been an issue of my postgres configuration (replacing it with one running in docker container resolved the problem)