PostgreSQL 19 Beta 3: Testing JSON and Foreign Data Wrapper Regressions

Introduction

PostgreSQL is widely used for applications where database logic goes beyond simple tables and CRUD operations. Modern applications often work with JSON documents, foreign tables, reporting databases, and data that is spread across multiple PostgreSQL servers.

That flexibility also means that a PostgreSQL upgrade should not be tested only with basic SELECT, INSERT, UPDATE, and DELETE statements.

PostgreSQL 19 Beta 3 is a good example of why targeted regression testing matters. The beta release includes fixes for a postgres_fdw issue involving array comparisons with implicit type coercion and a separate fix for JSON deparsing involving JSON_ARRAY(query).

These changes are small in description but important from a testing perspective. A query can execute successfully while still returning an unexpected result, or generated SQL can be structurally different from what the application expects.

This article looks at how developers can build practical regression tests around JSON and postgres_fdw when evaluating PostgreSQL 19 Beta 3.

What Is `postgres_fdw`?

postgres_fdw is PostgreSQL's foreign-data wrapper for accessing data stored on another PostgreSQL server. It allows a local PostgreSQL database to work with tables that physically reside in a remote PostgreSQL database.

A simplified architecture looks like this:

Application
     |
     v
Local PostgreSQL
     |
     | postgres_fdw
     v
Remote PostgreSQL
     |
     v
Remote Table

From the application's perspective, a foreign table can look similar to an ordinary table.

For example:

SELECT *
FROM remote_customers;

The actual data, however, may be stored on another PostgreSQL server.

Why Foreign Data Wrappers Need Regression Testing

A local query can involve several layers:

Local SQL
   |
   v
Query Planner
   |
   v
FDW Pushdown
   |
   v
Remote SQL
   |
   v
Remote PostgreSQL
   |
   v
Result

A problem in any part of this process can produce unexpected behavior.

This is particularly important when the query contains:

Arrays
Type conversions
Functions
Joins
Filters
Aggregations
JSON expressions

PostgreSQL's documentation explains that postgres_fdw attempts to push suitable query conditions to the remote server to reduce the amount of data transferred.

That optimization is useful, but it also makes query-planning and type-handling behavior worth testing.

PostgreSQL 19 Beta 3 Changes

The PostgreSQL 19 Beta 3 release notes identify several changes relevant to this article.

Among them are:

postgres_fdw:
Fix wrong query results when pushing down
an array comparison such as field = ANY($1)
involving implicit type coercion.

JSON:
Fix missing FORMAT clause when deparsing
JSON_ARRAY(query).

These fixes are explicitly listed in the PostgreSQL 19 Beta 3 announcement.

This gives developers two concrete regression-testing areas:

1. postgres_fdw array comparisons
2. JSON_ARRAY(query) deparsing

The goal should not be to reproduce an issue artificially just because it appears in release notes.

Instead, test whether the application's real workload exercises the affected areas.

Important Note About Beta Releases

PostgreSQL Beta releases are intended for testing, not production use. The PostgreSQL project specifically encourages developers to run their normal workloads against beta releases to identify compatibility problems before the final release.

That makes PostgreSQL 19 Beta 3 appropriate for a controlled regression environment.

A simple test environment could be:

Developer Machine
       |
       +---- PostgreSQL 19 Beta 3
       |
       +---- Test Application
       |
       +---- Remote PostgreSQL

Do not use a beta database as a production database merely because a particular test passes.

Setting Up a JSON Test Table

Start with a simple table containing JSON data.

CREATE TABLE orders
(
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    details JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Insert some representative data:

INSERT INTO orders
(
    customer_id,
    details
)
VALUES
(
    101,
    '{
        "status": "completed",
        "items": 3,
        "total": 250.50
    }'
),
(
    102,
    '{
        "status": "pending",
        "items": 1,
        "total": 75.00
    }'
);

Now the test database contains both relational and JSON data.

Testing JSON Extraction

Start with a simple query:

SELECT
    id,
    details->>'status' AS status
FROM orders;

Then test JSON values in filtering:

SELECT *
FROM orders
WHERE details->>'status' = 'completed';

These tests establish a baseline before moving into more complex JSON expressions.

Testing JSON Aggregation

Applications often need to construct JSON results from relational data.

For example:

SELECT JSON_AGG(
    JSON_BUILD_OBJECT(
        'id', id,
        'customerId', customer_id,
        'status', details->>'status'
    )
)
FROM orders;

The important thing is to validate both:

JSON Structure
+
JSON Values

A query that returns valid JSON is not necessarily returning the correct JSON.

Testing `JSON_ARRAY(query)`

PostgreSQL 19 includes a fix related to deparsing JSON_ARRAY(query).

This makes it useful to include query-based JSON construction in a regression suite.

For example:

SELECT JSON_ARRAY(
    SELECT details
    FROM orders
);

The exact SQL shape should be adapted to the syntax supported by the PostgreSQL version and the application's requirements.

The test should verify:

Valid SQL
Valid JSON
Correct number of elements
Correct element values
Expected formatting semantics

What Does Deparsing Mean?

Deparsing is essentially the process of reconstructing SQL from an internal representation.

This matters in PostgreSQL because several database components work with parsed or planned representations of queries.

A simplified flow is:

SQL
 |
 v
Parser
 |
 v
Internal Representation
 |
 v
Planner / Other Component
 |
 v
Deparsed SQL

If the deparser produces incorrect SQL, the resulting query can behave differently from the original intent.

That is why a seemingly small deparser fix can deserve a regression test.

Building a JSON Regression Test

A useful test should define expected output explicitly.

For example:

Input:
2 orders

Expected:
JSON array containing exactly 2 elements

Then execute the query and validate:

Element count = 2
First ID = expected ID
Second ID = expected ID

Avoid relying only on:

Query executed successfully

The test should verify the actual result.

Testing JSON NULL Behavior

JSON and SQL NULL values can behave differently.

Consider:

SELECT
    details->'discount'
FROM orders;

If the property does not exist, the result may not behave like an ordinary SQL column containing NULL.

Therefore, include test data such as:

{
    "status": "completed"
}

and:

{
    "status": "completed",
    "discount": null
}

Then test both cases.

This can reveal application assumptions that would otherwise remain hidden.

Testing Nested JSON

Real applications rarely use only flat JSON objects.

For example:

{
    "customer": {
        "name": "Alice"
    },
    "payment": {
        "method": "card",
        "status": "paid"
    }
}

Test nested access:

SELECT
    details #>> '{customer,name}' AS customer_name,
    details #>> '{payment,status}' AS payment_status
FROM orders;

Then verify the returned values.

Testing JSON With .NET

A .NET application may map JSON data through EF Core or access it through raw SQL.

For example:

var orders = await db.Orders
    .Where(x => x.Details.RootElement
        .GetProperty("status")
        .GetString() == "completed")
    .ToListAsync();

The exact expression depends on the PostgreSQL provider and application model.

When evaluating PostgreSQL 19 Beta 3, test the complete path:

.NET Application
       |
       v
EF Core / Provider
       |
       v
Generated SQL
       |
       v
PostgreSQL 19
       |
       v
JSON Result

This is more valuable than testing the database in isolation.

What Is an FDW Array Comparison?

One of the PostgreSQL 19 Beta 3 fixes concerns postgres_fdw when pushing down an array comparison such as:

field = ANY($1)

when implicit type coercion is involved. PostgreSQL's release announcement identifies this as a case that could produce wrong query results and was fixed in Beta 3.

This is an excellent example of a bug that may be difficult to detect through ordinary syntax validation.

Understanding `ANY`

Consider a query like:

SELECT *
FROM remote_customers
WHERE customer_id = ANY($1);

Conceptually, this asks PostgreSQL to find rows where:

customer_id

matches one of the values supplied in the array parameter.

For example:

[101, 205, 310]

means:

customer_id = 101
OR
customer_id = 205
OR
customer_id = 310

The important detail is the data type of both sides.

Why Type Coercion Matters

Suppose:

customer_id
BIGINT

but the supplied array is:

INTEGER[]

PostgreSQL may need to perform an implicit conversion.

The query now involves:

BIGINT
   |
   v
Comparison
   ^
   |
INTEGER[]

The PostgreSQL 19 Beta 3 fix specifically addresses a postgres_fdw case where array comparison pushdown combined with implicit type coercion could produce wrong results.

This is exactly the kind of regression that should be tested using real parameter types.

Building an FDW Test Environment

Create a remote table:

CREATE TABLE remote_customers
(
    id BIGINT PRIMARY KEY,
    name TEXT NOT NULL
);

Add test data:

INSERT INTO remote_customers (id, name)
VALUES
    (101, 'Alice'),
    (205, 'Bob'),
    (310, 'Charlie'),
    (450, 'David');

Then expose the table through postgres_fdw.

The local database can define a foreign table corresponding to the remote table.

The exact connection configuration depends on your test environment.

Test Array-Based Filtering

Once the foreign table is configured, test:

SELECT *
FROM remote_customers
WHERE id = ANY(ARRAY[101, 205]);

The expected result is:

101 | Alice
205 | Bob

Now introduce different integer types.

For example:

SELECT *
FROM remote_customers
WHERE id = ANY(ARRAY[101, 205]::INTEGER[]);

Then compare the result with an explicitly compatible type:

SELECT *
FROM remote_customers
WHERE id = ANY(ARRAY[101, 205]::BIGINT[]);

The purpose is to verify that implicit and explicit conversions produce the expected logical result.

Compare Local and FDW Queries

One of the strongest regression techniques is to compare a local query with an equivalent foreign query.

For example:

Local PostgreSQL
      |
      v
Local Table
      |
      v
Expected Result

and:

Local PostgreSQL
      |
      v
postgres_fdw
      |
      v
Remote Table
      |
      v
Actual Result

The result sets should match.

This creates a practical oracle:

Local Query Result
        =
FDW Query Result

assuming the underlying datasets are equivalent.

Testing With Different Array Sizes

Do not test only two IDs.

Use:

Empty array
One value
Two values
Many values
Duplicate values
NULL values where applicable

For example:

ARRAY[101]

then:

ARRAY[101, 205, 310]

and finally a larger set.

This can expose issues that only occur with specific parameter shapes.

Test Empty Arrays

An empty array is a useful edge case:

SELECT *
FROM remote_customers
WHERE id = ANY(ARRAY[]::BIGINT[]);

The expected result should be clearly defined by the application.

Usually, no rows should match.

The important part is that the application should know what it expects rather than relying on assumptions.

Test Duplicate Values

Consider:

ARRAY[101, 101, 205]

The result should not contain duplicate database rows merely because the input array contains duplicate values.

Expected:

101 | Alice
205 | Bob

not:

101 | Alice
101 | Alice
205 | Bob

This is a useful semantic test.

Test the Query Plan

Because postgres_fdw can push conditions to the remote server, inspect the execution plan when performance matters.

For example:

EXPLAIN
SELECT *
FROM remote_customers
WHERE id = ANY(ARRAY[101, 205]::BIGINT[]);

The exact plan depends on the environment.

The purpose is to determine whether the intended condition is being pushed down appropriately.

Do not assume that every query must be fully pushed down.

The goal is to understand what PostgreSQL is actually doing.

Testing Implicit vs Explicit Types

Create a test matrix:

Local Column	Array Type	Expected
BIGINT	BIGINT[]	Correct
BIGINT	INTEGER[]	Correct
INTEGER	INTEGER[]	Correct
TEXT	TEXT[]	Correct
Different compatible types	Implicit conversion	Validate carefully

This helps identify cases where type coercion changes query behavior.

Testing Application Parameters

The real-world case is often not a hard-coded array.

A .NET application may provide parameters dynamically:

var customerIds = new long[]
{
    101,
    205,
    310
};

The application then passes those values to a database query.

The test should verify:

C# Type
   |
   v
Provider Parameter Type
   |
   v
PostgreSQL Parameter Type
   |
   v
FDW Comparison
   |
   v
Result

This is important because application-level types and PostgreSQL types do not always map in exactly the same way.

Testing JSON and FDW Together

Complex applications may use both features.

For example:

Local Database
      |
      +---- JSON filtering
      |
      +---- postgres_fdw
              |
              v
        Remote PostgreSQL

A realistic query might:

Filter remote records.
Push a condition to the remote server.
Return JSON data.
Construct a JSON result locally.

This is a more advanced integration test.

However, do not start with this scenario.

First verify:

JSON alone
FDW alone

Then combine them.

This makes troubleshooting much easier.

Regression Test Design

A good regression test should contain four parts:

Input
  |
  v
Query
  |
  v
Expected Result
  |
  v
Assertion

For example:

Input:
Customer IDs = [101, 205]

Query:
FDW query using ANY()

Expected:
Alice, Bob

Assertion:
Actual result == Expected result

For JSON:

Input:
Two order rows

Query:
JSON_ARRAY(query)

Expected:
Two JSON elements

Assertion:
JSON structure and values match

Testing Against Previous PostgreSQL Versions

If you are evaluating an upgrade, run the same tests against:

Current PostgreSQL
        |
        v
PostgreSQL 19 Beta 3

Compare:

Result
Execution Plan
Execution Time
Errors
Warnings

The goal is not necessarily to find differences and call them bugs.

Some behavior changes may be intentional.

The benchmark should identify unexpected differences that require investigation.

Automating the Tests

For .NET applications, database regression tests can be incorporated into an integration-test project.

A simplified test could look like:

[Fact]
public async Task RemoteCustomerQuery_Should_ReturnExpectedCustomers()
{
    var ids = new long[] { 101, 205 };

    var results = await repository
        .GetCustomersAsync(ids);

    Assert.Equal(2, results.Count);
    Assert.Contains(results, x => x.Id == 101);
    Assert.Contains(results, x => x.Id == 205);
}

For JSON:

[Fact]
public async Task OrderJson_ShouldContainExpectedStatus()
{
    var order = await repository.GetOrderAsync(101);

    Assert.Equal(
        "completed",
        order.Status);
}

The actual implementation will depend on the data-access architecture.

Testing Production-Like Data

Synthetic data is useful, but production-like data is better for compatibility testing.

Include:

Small JSON documents
Large JSON documents
Missing properties
NULL values
Nested objects
Arrays
Large ID lists
Different numeric types

For FDW testing, include:

Small remote tables
Large remote tables
Indexed columns
Non-indexed columns
Different data types
Different parameter sizes

This makes the regression suite more representative.

Common Mistakes

Mistake 1: Testing Only Successful Execution

A query can execute and still return incorrect data.

Mistake 2: Ignoring Data Types

Implicit type coercion is specifically relevant to the PostgreSQL 19 Beta 3 postgres_fdw fix.

Mistake 3: Testing Only Local Tables

FDW behavior must be tested through the foreign-data wrapper.

Mistake 4: Ignoring JSON Structure

Valid JSON is not necessarily correct JSON.

Mistake 5: Using Only Small Test Data

Small datasets may hide query-planning and FDW performance problems.

Mistake 6: Combining Too Many Variables

Test JSON and FDW independently before testing complex combinations.

Mistake 7: Ignoring the Application Layer

A database query that works manually may behave differently when executed through the application's database provider.

Mistake 8: Running Beta Software in Production

PostgreSQL explicitly advises against using beta releases in production environments.

Troubleshooting

Problem	What to Check
FDW returns unexpected rows	Compare local and remote query results
Array comparison behaves differently	Check parameter and column data types
Remote filter is not pushed down	Inspect the execution plan
JSON result has wrong structure	Validate each JSON element independently
JSON property is missing	Test missing vs explicit null
.NET result differs from psql	Inspect generated SQL and parameter types
Query works locally but not through FDW	Check remote execution and type coercion
Tests are intermittent	Check remote database state and replication/network conditions
PostgreSQL upgrade changes results	Compare execution plans and exact query behavior

Best Practices

Test the Exact PostgreSQL Version

Do not assume that behavior from another release is identical.

Validate Results, Not Just Errors

The strongest regression test verifies actual data.

Test Type Coercion Explicitly

Especially when arrays and FDW queries are involved.

Keep Local and Remote Test Data Controlled

Equivalent datasets make comparison much easier.

Inspect Execution Plans

Use them to understand FDW pushdown and performance behavior.

Include Edge Cases

Test:

Empty
NULL
Duplicate
Large
Nested
Mixed Type

where applicable.

Test Through the Real Application

For .NET applications, include the database provider and EF Core workflow.

Keep Beta Testing Isolated

Use containers, dedicated test databases, or other disposable environments.

Advantages

Finds Subtle Compatibility Problems

Regression tests can catch incorrect results that ordinary build tests cannot.

Improves Upgrade Confidence

Teams can validate existing workloads against PostgreSQL 19 Beta 3 before a future production upgrade.

Protects Data Correctness

Comparing expected and actual results helps detect semantic problems.

Useful for Distributed Database Architectures

postgres_fdw testing is particularly relevant when data is spread across PostgreSQL servers.

Works Well With Automated CI

Once the test environment is reproducible, the same regression suite can be executed repeatedly.

Disadvantages and Limitations

FDW Testing Requires Multiple Database Environments

A realistic test needs both local and remote database components.

Performance Results Are Environment-Specific

Network latency, indexes, hardware, and data size can change results.

JSON Tests Can Become Complex

Deeply nested JSON structures require more detailed assertions.

Beta Behavior Can Change

PostgreSQL beta releases are pre-release software, and the project notes that behavior can still change before general availability.

Not Every Difference Is a Regression

A difference between releases needs investigation before it is classified as a defect.

A Practical Regression Workflow

For a .NET team evaluating PostgreSQL 19 Beta 3, the following workflow is practical:

Create Test Databases
        |
        v
Load Representative Data
        |
        +----------------+
        |                |
        v                v
JSON Tests          FDW Tests
        |                |
        +--------+-------+
                 |
                 v
       Application Tests
                 |
                 v
       Compare Results
                 |
                 v
       Inspect Query Plans
                 |
                 v
        Record Differences
                 |
                 v
        Investigate Failures

Start with focused tests and gradually increase complexity.

Example Regression Checklist

Before declaring the database upgrade test successful, verify:

[ ] JSON extraction works

[ ] JSON filtering works

[ ] JSON aggregation works

[ ] JSON_ARRAY(query) produces expected results

[ ] Missing JSON properties are handled correctly

[ ] JSON null values are handled correctly

[ ] postgres_fdw connection works

[ ] Foreign tables return expected rows

[ ] Array comparisons return correct rows

[ ] Implicit type coercion is tested

[ ] Explicit type coercion is tested

[ ] Empty arrays are tested

[ ] Duplicate array values are tested

[ ] Large parameter arrays are tested

[ ] FDW query plans are reviewed

[ ] .NET integration tests pass

[ ] Results are compared with the existing PostgreSQL version

Conclusion

PostgreSQL 19 Beta 3 provides a good opportunity to test database workloads that depend on JSON processing and foreign data access before a future upgrade. The release specifically includes a fix for incorrect postgres_fdw query results involving pushed-down array comparisons with implicit type coercion, along with a fix related to deparsing JSON_ARRAY(query). These are good examples of why database regression testing should verify actual results instead of checking only whether SQL executes successfully. For .NET developers, the strongest approach is to test the complete path from application code and database-provider parameters through PostgreSQL and, where applicable, across a remote PostgreSQL server. Start with focused JSON and FDW tests, add edge cases such as NULL, empty arrays, duplicate values, and different data types, and then compare the results with the current PostgreSQL version. PostgreSQL Beta 3 is intended for testing rather than production use, so the best use of this release is to find compatibility issues now and make the eventual upgrade smoother.

Introduction

What Is postgres_fdw?

Why Foreign Data Wrappers Need Regression Testing

PostgreSQL 19 Beta 3 Changes

Important Note About Beta Releases

Setting Up a JSON Test Table

Testing JSON Extraction

Testing JSON Aggregation

Testing JSON_ARRAY(query)

What Does Deparsing Mean?

Building a JSON Regression Test

Testing JSON NULL Behavior

Testing Nested JSON

Testing JSON With .NET

What Is an FDW Array Comparison?

Understanding ANY

Why Type Coercion Matters

Building an FDW Test Environment

Test Array-Based Filtering

Compare Local and FDW Queries

Testing With Different Array Sizes

Test Empty Arrays

Test Duplicate Values

Test the Query Plan

Testing Implicit vs Explicit Types

Testing Application Parameters

Testing JSON and FDW Together

Regression Test Design

Testing Against Previous PostgreSQL Versions

Automating the Tests

Testing Production-Like Data

Common Mistakes

Mistake 1: Testing Only Successful Execution

Mistake 2: Ignoring Data Types

Mistake 3: Testing Only Local Tables

Mistake 4: Ignoring JSON Structure

Mistake 5: Using Only Small Test Data

Mistake 6: Combining Too Many Variables

Mistake 7: Ignoring the Application Layer

Mistake 8: Running Beta Software in Production

Troubleshooting

Best Practices

Test the Exact PostgreSQL Version

Validate Results, Not Just Errors

Test Type Coercion Explicitly

Keep Local and Remote Test Data Controlled

Inspect Execution Plans

Include Edge Cases

Test Through the Real Application

Keep Beta Testing Isolated

Advantages

Finds Subtle Compatibility Problems

Improves Upgrade Confidence

Protects Data Correctness

Useful for Distributed Database Architectures

Works Well With Automated CI

Disadvantages and Limitations

FDW Testing Requires Multiple Database Environments

Performance Results Are Environment-Specific

JSON Tests Can Become Complex

Beta Behavior Can Change

Not Every Difference Is a Regression

A Practical Regression Workflow

Example Regression Checklist

Conclusion

What Is `postgres_fdw`?

Testing `JSON_ARRAY(query)`

Understanding `ANY`