Skip to content

use exact count when estimates fail#4393

Merged
jgao54 merged 4 commits into
mainfrom
use-exact-count-if-estimated-count-fails
Jun 10, 2026
Merged

use exact count when estimates fail#4393
jgao54 merged 4 commits into
mainfrom
use-exact-count-if-estimated-count-fails

Conversation

@jgao54

@jgao54 jgao54 commented Jun 6, 2026

Copy link
Copy Markdown
Contributor

Encountered a scenario where the query count estimate led to ERROR: bigint out of range (SQLSTATE 22003); and the while the exact row count for the table is very low.

The query is:

SELECT CASE
WHEN c.relhassubclass THEN 0
WHEN c.reltuples >= 0 AND c.relpages > 0 THEN
	(c.reltuples / c.relpages * (pg_relation_size(c.oid) / current_setting('block_size')::integer))::bigint
ELSE c.reltuples::bigint
END
FROM pg_class c WHERE c.oid = to_regclass($1)

When stats are not populated due to a relatively new table, reltuples could be 0 or -1, and in both cases the result would not overflow. Given the actual row count is small, the most likely scenario seem to be table bloat with dead rows that led to both reltuples and pg_relation_size to be very high.

When we hit this error, we can conservatively fall back to actual table count.

Fixes: DBI-766

@jgao54
jgao54 requested a review from a team as a code owner June 6, 2026 23:40
@claude

claude Bot commented Jun 6, 2026 •
edited
Loading

Copy link
Copy Markdown

Code Review

1 issue found. Checked for bugs and CLAUDE.md compliance.

Bug: Fallback to precise count is unreachable after transaction abort

File: flow/connectors/postgres/qrep_partition.go, lines 472-476

pp.tx is a pgx.Tx created via BeginTx with RepeatableRead isolation. When the ::bigint cast overflows in the estimated count SQL query, PostgreSQL raises a server-side error that puts the transaction into an aborted state. All subsequent commands on that transaction will fail with current transaction is aborted, commands ignored until end of transaction block. This means the precise count fallback query at line 491 will also fail -- the fallback is unreachable in the overflow scenario this PR is designed to fix.

Possible fixes:

  1. Savepoint: wrap the estimated count query in a SAVEPOINT / ROLLBACK TO SAVEPOINT so the transaction can recover from the error.
  2. Fix the SQL: prevent the overflow at the SQL level, e.g. cast intermediates to numeric before the final ::bigint cast.
  3. Separate connection: run the estimated count query outside the transaction.

@jgao54
jgao54 force-pushed the use-exact-count-if-estimated-count-fails branch 3 times, most recently from 19afc56 to 4f70a58 Compare June 8, 2026 02:34
@jgao54
jgao54 force-pushed the use-exact-count-if-estimated-count-fails branch from 4f70a58 to 5da3830 Compare June 8, 2026 02:36
@codecov

codecov Bot commented Jun 8, 2026

Copy link
Copy Markdown

❌ 2 Tests Failed:

Tests completed Failed Passed Skipped
2301 2 2299 209
View the top 2 failed test(s) by shortest run time 
github.com/PeerDB-io/peerdb/flow/e2e::TestPeerFlowE2ETestSuitePG_CH
Stack Traces | 0.01s run time 
=== RUN   TestPeerFlowE2ETestSuitePG_CH
=== PAUSE TestPeerFlowE2ETestSuitePG_CH
=== CONT  TestPeerFlowE2ETestSuitePG_CH
--- FAIL: TestPeerFlowE2ETestSuitePG_CH (0.01s)

github.com/PeerDB-io/peerdb/flow/e2e::TestPeerFlowE2ETestSuitePG_CH/Test_Composite_PKey
Stack Traces | 61.2s run time 
=== RUN   TestPeerFlowE2ETestSuitePG_CH/Test_Composite_PKey
=== PAUSE TestPeerFlowE2ETestSuitePG_CH/Test_Composite_PKey
=== CONT  TestPeerFlowE2ETestSuitePG_CH/Test_Composite_PKey
2026/06/08 02:41:14 INFO Received AWS credentials from peer for connector: ci x-peerdb-additional-metadata={Operation:FLOW_OPERATION_UNKNOWN}
    clickhouse_test.go:3539: UNEXPECTED STATUS TIMEOUT STATUS_SNAPSHOT
    clickhouse.go:107: begin tearing down postgres schema pgch_5sl19fuo
--- FAIL: TestPeerFlowE2ETestSuitePG_CH/Test_Composite_PKey (61.16s)

To view more test analytics, go to the Test Analytics Dashboard
📋 Got 3 mins? Take this short survey to help us improve Test Analytics.

@github-actions

github-actions Bot commented Jun 8, 2026

Copy link
Copy Markdown
Contributor

🔄 Flaky Test Detected

Analysis: The single isolated subtest TestPeerFlowE2ETestSuitePG_CH/Test_Composite_PKey hit a 60s snapshot-status timeout (STATUS_SNAPSHOT never reached RUNNING) on a trivial 1-row table under heavy parallel CI load, with the unrelated PG-TLS HEAD commit — a classic resource-contention flake, not a real bug.
Confidence: 0.82

✅ Automatically retrying the workflow

View workflow run

ilidemi
ilidemi reviewed Jun 9, 2026
View reviewed changes
Comment thread flow/connectors/postgres/qrep_partition.go
if err := pp.tx.QueryRow(ctx, estimatedCountTemplate, pp.watermarkTable).Scan(&n); err != nil {
return 0, fmt.Errorf("failed to query for estimated row count: %w", err)
} else if v, err := n.Int64Value(); err != nil {
pp.logger.Warn("estimated row count outside int64 range, falling back to precise count",

@ilidemi ilidemi Jun 9, 2026

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Just a thought, could also query the inputs into the formula and log them for debugging purposes

@jgao54
jgao54 enabled auto-merge (squash) June 10, 2026 01:35
@jgao54
jgao54 merged commit a506468 into main Jun 10, 2026
15 checks passed
@jgao54
jgao54 deleted the use-exact-count-if-estimated-count-fails branch June 10, 2026 01:55
@jgao54 jgao54 mentioned this pull request Jul 23, 2026
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@ilidemi ilidemi ilidemi approved these changes
@masterashu masterashu masterashu approved these changes

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@jgao54 @ilidemi @masterashu