AsyncAppend Bug In PostgreSQL ForeignScan Qual

Sep 19, 2025 by Square 47 views

The Case When AsyncAppend Exists Also in the Qual of Async ForeignScan

Hey guys, let's dive into a fascinating PostgreSQL bug report! This issue, reported by Andrey Lepikhov from Postgres Professional, revolves around the interaction between AsyncAppend and Async ForeignScan within partitioned tables. Specifically, the problem arises when an AsyncAppend node exists within the qualification (QUAL) of an Async ForeignScan. This can lead to unexpected errors, including a rather nasty segmentation fault. Let's break down the details and see how Andrey's quick fix attempts to address this.

Unpacking the Bug: AsyncAppend and ForeignScan

To really understand the bug, let’s clarify some key concepts:

AsyncAppend: This is a plan node in PostgreSQL that efficiently combines results from multiple subqueries or partitions. Think of it as a way to gather data from different sources concurrently, making queries faster.
ForeignScan: This is how PostgreSQL interacts with external data sources, like other databases or even files. It allows you to query data that isn't directly stored within your PostgreSQL instance.
Partitioning: Table partitioning is a technique that divides a large table into smaller, more manageable pieces (partitions). This can significantly improve query performance, especially for large datasets.
QUAL (Qualification): In SQL terms, the qualification is your WHERE clause. It's the part of the query that filters the data based on specific conditions.

So, putting it all together, the bug happens when we have a query that needs to pull data from foreign tables (using ForeignScan) which are part of a partitioned table and the filtering criteria (WHERE clause) involves an AsyncAppend. This is where things get tricky, and PostgreSQL can stumble.

The Scenario: A Recipe for Disaster

Andrey provided a clear example to demonstrate the issue. Here's a breakdown of the steps to reproduce the bug:

Create a Partitioned Table: First, a table named test is created and partitioned using hash partitioning on the x column. This means the data will be divided into partitions based on the hash value of x.

CREATE TABLE test (x int) PARTITION BY HASH (x);

Create Local Tables: Two local tables, test_1 and test_2, are created. These will hold the actual data for the partitions.

CREATE TABLE test_1 (x int);
CREATE TABLE test_2 (x int);

Create Foreign Tables: This is where the ForeignScan comes in. Two foreign tables, ftest_1 and ftest_2, are created as partitions of the test table. These foreign tables point to the local tables test_1 and test_2, respectively, using the loopback and loopback2 servers. Basically, they are referencing local tables as if they were external.

CREATE FOREIGN TABLE ftest_1 PARTITION OF test
FOR VALUES WITH (modulus 2, remainder 0)
SERVER loopback OPTIONS (table_name 'test_1');
CREATE FOREIGN TABLE ftest_2 PARTITION OF test
FOR VALUES WITH (modulus 2, remainder 1)
SERVER loopback2 OPTIONS (table_name 'test_2');

Insert Data: Some sample data is inserted into the test table.

INSERT INTO test (SELECT * FROM generate_series(1,10));

Analyze Tables: The ANALYZE command is run on all the tables to update statistics, which helps the PostgreSQL query planner make better decisions.

ANALYZE test,test_1,test_2,ftest_1,ftest_2;

The Trigger Query: This is the query that triggers the bug. It selects data from the partitioned test table where the x value is NOT IN the average of x values greater than 10. The subquery involving the average and the NOT IN condition creates the scenario where AsyncAppend is used within the QUAL of the ForeignScan.

EXPLAIN (ANALYZE)
SELECT * FROM test WHERE x NOT IN (
SELECT avg(x) FROM test WHERE x > 10
);

Running this query results in the dreaded error: ERROR: InstrEndLoop called on running node. In some cases, adding ', COSTS OFF, SUMMARY OFF, TIMING OFF' to the EXPLAIN command causes a segmentation fault, which is even worse!

Decoding the Error: What's Going Wrong?

The error message InstrEndLoop called on running node hints at an issue within the instrumentation code used for performance analysis. Instrumentation code is like a little detective that tracks what's happening inside the query execution engine. It seems like the instrumentation is getting confused when dealing with the combination of AsyncAppend and ForeignScan in the WHERE clause.

Essentially, the query planner is trying to optimize the query by using AsyncAppend to gather results from the foreign table partitions concurrently. However, the subquery in the WHERE clause also involves aggregation (avg(x)), which might be interfering with the proper execution flow of the AsyncAppend node. The InstrEndLoop error suggests that the instrumentation is trying to stop a loop that's not currently running, or is in an inconsistent state.

The Quick Fix: A Patch in the Dark

Andrey recognized the severity of the issue and provided a patch as a potential solution. Patches are like bandages for software bugs – they contain the code changes needed to fix the problem. While he acknowledges that it might not be the perfect solution, it serves as a valuable starting point for debugging.

Unfortunately, without diving deep into the patch code itself (which is beyond the scope of this discussion), we can't say exactly what the patch does. However, based on the description and the problem context, it likely addresses the incorrect handling of tuples or the loop management within the AsyncAppend node when it's part of a ForeignScan's qualification.

Why This Matters: The Impact of the Bug

This bug, while seemingly specific, highlights the complexities of query optimization in modern database systems. The interaction between different plan nodes, like AsyncAppend and ForeignScan, can create unexpected corner cases. Bugs like this can lead to:

Query Failures: The InstrEndLoop error, and especially the segmentation fault, can prevent queries from completing, disrupting applications that rely on those queries.
Performance Issues: Even if the query doesn't fail outright, the query planner might choose a suboptimal execution plan due to the bug, leading to slower query performance.
Data Inconsistency: In severe cases, bugs like this could potentially lead to data corruption, although that's less likely in this particular scenario.

The Takeaway: A Community Effort

This bug report is a great example of the PostgreSQL community working together to improve the system. Andrey's detailed report, including the reproducible test case and the quick fix patch, is incredibly helpful for other developers to understand and address the issue.

This also illustrates the importance of thorough testing and debugging, especially when dealing with complex features like partitioning and foreign tables. Database systems are intricate pieces of software, and even small interactions between different components can have significant consequences.

It's crucial to remember that bug fixes are often iterative. Andrey's patch is likely just the first step in resolving this issue. Other developers will review the patch, test it further, and potentially suggest alternative solutions or refinements. This collaborative process is what makes open-source projects like PostgreSQL so robust and reliable. So, next time you run into a weird PostgreSQL error, remember that there's a whole community of people working to make things better!