bug: timeseries insert error in CNPG #835

New issue

Closed

opened 2026-03-28 04:29:02 +00:00 by mfreeman451 · 1 comment

mfreeman451 commented

2026-03-28 04:29:02 +00:00

Owner

Imported from GitHub.

Original GitHub issue: #2456
Original author: @mfreeman451
Original URL: https://github.com/carverauto/serviceradar/issues/2456
Original created: 2026-01-23T10:29:11Z

Describe the bug

I just noticed things were loading very slowing in the web UI, not seeing any errors in the logs, so I checked CNPG, high CPU utilization and at least one error in one of the pod logs:

{"level":"info","ts":"2026-01-23T10:16:19.271940215Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 10:16:19.271 UTC","user_name":"ser
viceradar","database_name":"serviceradar","process_id":"301956","connection_from":"10.42.68.76:42832","session_id":"69734513.49b84","session_line_num":"1","command_tag":"INSERT","session_
start_time":"2026-01-23 09:53:23 UTC","virtual_transaction_id":"11/23721","transaction_id":"1007917","error_severity":"ERROR","sql_state_code":"23505","message":"duplicate key value viola
tes unique constraint \"timeseries_metrics_pkey\"","detail":"Key (\"timestamp\", gateway_id, metric_name)=(2026-01-23 10:16:18.266864+00, serviceradar_agent_gateway@10.42.68.105, ifOutOct
ets) already exists.","query":"INSERT INTO \"timeseries_metrics\" (\"timestamp\",\"unit\",\"value\",\"metadata\",\"partition\",\"scale\",\"tags\",\"created_at\",\"agent_id\",\"gateway_id\
",\"device_id\",\"if_index\",\"metric_name\",\"metric_type\",\"is_delta\",\"target_device_ip\") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16),($17,$18,$19,$20,$21,$22,$2
3,$24,$25,$26,$27,$28,$29,$30,$31,$32),($33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48),($49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64),($65,$66,$67,$68
,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80),($81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96),($97,$98,$99,$100,$101,$102,$103,$104,$105,$106,$107,$108,$109,$110,$11
1,$112),($113,$114,$115,$116,$117,$118,$119,$120,$121,$122,$123,$124,$125,$126,$127,$128)","backend_type":"client backend","query_id":"0"}}
{"level":"info","ts":"2026-01-23T10:17:36.702934891Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 10:17:36.702 UTC","process_id":"39
","session_id":"696f387b.27","session_line_num":"1781","session_start_time":"2026-01-20 08:10:35 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"check
point starting: time","backend_type":"checkpointer","query_id":"0"}}

{"level":"info","ts":"2026-01-23T09:48:07.118063316Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:48:07.117 UTC","process_id":"39
","session_id":"696f387b.27","session_line_num":"1770","session_start_time":"2026-01-20 08:10:35 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"check
point complete: wrote 304 buffers (1.9%); 0 WAL file(s) added, 0 removed, 1 recycled; write=30.365 s, sync=0.008 s, total=30.386 s; sync files=121, longest=0.002 s, average=0.001 s; dista
nce=16802 kB, estimate=16802 kB; lsn=3/7A036798, redo lsn=3/7924D528","backend_type":"checkpointer","query_id":"0"}}
{"level":"info","ts":"2026-01-23T09:48:37.763297405Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:48:37.762 UTC","user_name":"pos
tgres","database_name":"serviceradar","process_id":"301504","connection_from":"[local]","session_id":"697343f5.499c0","session_line_num":"1","command_tag":"SELECT","session_start_time":"2
026-01-23 09:48:37 UTC","virtual_transaction_id":"52/97903","transaction_id":"0","error_severity":"ERROR","sql_state_code":"42P01","message":"relation \"platform.agents\" does not exist",
"query":"SELECT id, uid, device_uid, gateway_id, partition FROM platform.agents WHERE uid = 'k8s-agent';","query_pos":"56","application_name":"psql","backend_type":"client backend","query
_id":"0"}}
{"level":"info","ts":"2026-01-23T09:48:49.132957382Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:48:49.132 UTC","user_name":"pos
tgres","database_name":"serviceradar","process_id":"301534","connection_from":"[local]","session_id":"69734401.499de","session_line_num":"1","command_tag":"SELECT","session_start_time":"2
026-01-23 09:48:49 UTC","virtual_transaction_id":"52/97931","transaction_id":"0","error_severity":"ERROR","sql_state_code":"42703","message":"column \"gateway_uid\" does not exist","hint"
:"Perhaps you meant to reference the column \"ocsf_agents.gateway_id\".","query":"SELECT uid, device_uid, gateway_uid, partition FROM platform.ocsf_agents WHERE uid = 'k8s-agent';","query
_pos":"25","application_name":"psql","backend_type":"client backend","query_id":"0"}}
{"level":"info","ts":"2026-01-23T09:49:04.100544557Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:49:04.100 UTC","user_name":"pos
tgres","database_name":"serviceradar","process_id":"301579","connection_from":"[local]","session_id":"69734410.49a0b","session_line_num":"1","command_tag":"SELECT","session_start_time":"2
026-01-23 09:49:04 UTC","virtual_transaction_id":"52/97992","transaction_id":"0","error_severity":"ERROR","sql_state_code":"42703","message":"column \"partition\" does not exist","query":
"SELECT uid, device_uid, gateway_id, partition, config_source FROM platform.ocsf_agents WHERE uid = 'k8s-agent';","query_pos":"37","application_name":"psql","backend_type":"client backend
","query_id":"0"}}
{"level":"info","ts":"2026-01-23T09:52:36.127949638Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:52:36.127 UTC","process_id":"39
","session_id":"696f387b.27","session_line_num":"1771","session_start_time":"2026-01-20 08:10:35 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"check
point starting: time","backend_type":"checkpointer","query_id":"0"}}
{"level":"info","ts":"2026-01-23T09:53:07.22117742Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:53:07.221 UTC","process_id":"39"
,"session_id":"696f387b.27","session_line_num":"1772","session_start_time":"2026-01-20 08:10:35 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"checkp
oint complete: wrote 312 buffers (1.9%); 0 WAL file(s) added, 0 removed, 1 recycled; write=31.066 s, sync=0.007 s, total=31.094 s; sync files=109, longest=0.002 s, average=0.001 s; distan
ce=16313 kB, estimate=16753 kB; lsn=3/7B004C00, redo lsn=3/7A23B990","backend_type":"checkpointer","query_id":"0"}}

Should go through more of these logs to see if there are more unique errors to address, CNPG running in demo-staging namespace in k8s

To Reproduce
Steps to reproduce the behavior:

Go to '...'
Click on '....'
Scroll down to '....'
See error

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

OS: [e.g. iOS]
Browser [e.g. chrome, safari]
Version [e.g. 22]

Smartphone (please complete the following information):

Device: [e.g. iPhone6]
OS: [e.g. iOS8.1]
Browser [e.g. stock browser, safari]
Version [e.g. 22]

Additional context
Add any other context about the problem here.

Imported from GitHub. Original GitHub issue: #2456 Original author: @mfreeman451 Original URL: https://github.com/carverauto/serviceradar/issues/2456 Original created: 2026-01-23T10:29:11Z --- **Describe the bug** I just noticed things were loading very slowing in the web UI, not seeing any errors in the logs, so I checked CNPG, high CPU utilization and at least one error in one of the pod logs: ``` {"level":"info","ts":"2026-01-23T10:16:19.271940215Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 10:16:19.271 UTC","user_name":"ser viceradar","database_name":"serviceradar","process_id":"301956","connection_from":"10.42.68.76:42832","session_id":"69734513.49b84","session_line_num":"1","command_tag":"INSERT","session_ start_time":"2026-01-23 09:53:23 UTC","virtual_transaction_id":"11/23721","transaction_id":"1007917","error_severity":"ERROR","sql_state_code":"23505","message":"duplicate key value viola tes unique constraint \"timeseries_metrics_pkey\"","detail":"Key (\"timestamp\", gateway_id, metric_name)=(2026-01-23 10:16:18.266864+00, serviceradar_agent_gateway@10.42.68.105, ifOutOct ets) already exists.","query":"INSERT INTO \"timeseries_metrics\" (\"timestamp\",\"unit\",\"value\",\"metadata\",\"partition\",\"scale\",\"tags\",\"created_at\",\"agent_id\",\"gateway_id\ ",\"device_id\",\"if_index\",\"metric_name\",\"metric_type\",\"is_delta\",\"target_device_ip\") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16),($17,$18,$19,$20,$21,$22,$2 3,$24,$25,$26,$27,$28,$29,$30,$31,$32),($33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48),($49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64),($65,$66,$67,$68 ,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80),($81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96),($97,$98,$99,$100,$101,$102,$103,$104,$105,$106,$107,$108,$109,$110,$11 1,$112),($113,$114,$115,$116,$117,$118,$119,$120,$121,$122,$123,$124,$125,$126,$127,$128)","backend_type":"client backend","query_id":"0"}} {"level":"info","ts":"2026-01-23T10:17:36.702934891Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 10:17:36.702 UTC","process_id":"39 ","session_id":"696f387b.27","session_line_num":"1781","session_start_time":"2026-01-20 08:10:35 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"check point starting: time","backend_type":"checkpointer","query_id":"0"}} ``` ``` {"level":"info","ts":"2026-01-23T09:48:07.118063316Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:48:07.117 UTC","process_id":"39 ","session_id":"696f387b.27","session_line_num":"1770","session_start_time":"2026-01-20 08:10:35 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"check point complete: wrote 304 buffers (1.9%); 0 WAL file(s) added, 0 removed, 1 recycled; write=30.365 s, sync=0.008 s, total=30.386 s; sync files=121, longest=0.002 s, average=0.001 s; dista nce=16802 kB, estimate=16802 kB; lsn=3/7A036798, redo lsn=3/7924D528","backend_type":"checkpointer","query_id":"0"}} {"level":"info","ts":"2026-01-23T09:48:37.763297405Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:48:37.762 UTC","user_name":"pos tgres","database_name":"serviceradar","process_id":"301504","connection_from":"[local]","session_id":"697343f5.499c0","session_line_num":"1","command_tag":"SELECT","session_start_time":"2 026-01-23 09:48:37 UTC","virtual_transaction_id":"52/97903","transaction_id":"0","error_severity":"ERROR","sql_state_code":"42P01","message":"relation \"platform.agents\" does not exist", "query":"SELECT id, uid, device_uid, gateway_id, partition FROM platform.agents WHERE uid = 'k8s-agent';","query_pos":"56","application_name":"psql","backend_type":"client backend","query _id":"0"}} {"level":"info","ts":"2026-01-23T09:48:49.132957382Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:48:49.132 UTC","user_name":"pos tgres","database_name":"serviceradar","process_id":"301534","connection_from":"[local]","session_id":"69734401.499de","session_line_num":"1","command_tag":"SELECT","session_start_time":"2 026-01-23 09:48:49 UTC","virtual_transaction_id":"52/97931","transaction_id":"0","error_severity":"ERROR","sql_state_code":"42703","message":"column \"gateway_uid\" does not exist","hint" :"Perhaps you meant to reference the column \"ocsf_agents.gateway_id\".","query":"SELECT uid, device_uid, gateway_uid, partition FROM platform.ocsf_agents WHERE uid = 'k8s-agent';","query _pos":"25","application_name":"psql","backend_type":"client backend","query_id":"0"}} {"level":"info","ts":"2026-01-23T09:49:04.100544557Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:49:04.100 UTC","user_name":"pos tgres","database_name":"serviceradar","process_id":"301579","connection_from":"[local]","session_id":"69734410.49a0b","session_line_num":"1","command_tag":"SELECT","session_start_time":"2 026-01-23 09:49:04 UTC","virtual_transaction_id":"52/97992","transaction_id":"0","error_severity":"ERROR","sql_state_code":"42703","message":"column \"partition\" does not exist","query": "SELECT uid, device_uid, gateway_id, partition, config_source FROM platform.ocsf_agents WHERE uid = 'k8s-agent';","query_pos":"37","application_name":"psql","backend_type":"client backend ","query_id":"0"}} {"level":"info","ts":"2026-01-23T09:52:36.127949638Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:52:36.127 UTC","process_id":"39 ","session_id":"696f387b.27","session_line_num":"1771","session_start_time":"2026-01-20 08:10:35 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"check point starting: time","backend_type":"checkpointer","query_id":"0"}} {"level":"info","ts":"2026-01-23T09:53:07.22117742Z","logger":"postgres","msg":"record","logging_pod":"cnpg-staging-1","record":{"log_time":"2026-01-23 09:53:07.221 UTC","process_id":"39" ,"session_id":"696f387b.27","session_line_num":"1772","session_start_time":"2026-01-20 08:10:35 UTC","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"checkp oint complete: wrote 312 buffers (1.9%); 0 WAL file(s) added, 0 removed, 1 recycled; write=31.066 s, sync=0.007 s, total=31.094 s; sync files=109, longest=0.002 s, average=0.001 s; distan ce=16313 kB, estimate=16753 kB; lsn=3/7B004C00, redo lsn=3/7A23B990","backend_type":"checkpointer","query_id":"0"}} ``` Should go through more of these logs to see if there are more unique errors to address, CNPG running in `demo-staging` namespace in k8s **To Reproduce** Steps to reproduce the behavior: 1. Go to '...' 2. Click on '....' 3. Scroll down to '....' 4. See error **Expected behavior** A clear and concise description of what you expected to happen. **Screenshots** If applicable, add screenshots to help explain your problem. **Desktop (please complete the following information):** - OS: [e.g. iOS] - Browser [e.g. chrome, safari] - Version [e.g. 22] **Smartphone (please complete the following information):** - Device: [e.g. iPhone6] - OS: [e.g. iOS8.1] - Browser [e.g. stock browser, safari] - Version [e.g. 22] **Additional context** Add any other context about the problem here.

mfreeman451 added this to the 1.1.0 milestone

2026-03-28 04:29:02 +00:00

mfreeman451 commented

2026-03-28 04:29:03 +00:00

Author

Owner

Imported GitHub comment.

Original author: @mfreeman451
Original URL: https://github.com/carverauto/serviceradar/issues/2456#issuecomment-3789864209
Original created: 2026-01-23T11:42:27Z

fixed, added indexes

Imported GitHub comment. Original author: @mfreeman451 Original URL: https://github.com/carverauto/serviceradar/issues/2456#issuecomment-3789864209 Original created: 2026-01-23T11:42:27Z --- fixed, added indexes

mfreeman451 closed this issue

2026-03-28 04:29:03 +00:00