No rows returned by fetch() when for DELETE rewritten to UPDATE using rule

[DvdGiessen]

• asyncpg version: 0.29.0
• PostgreSQL version: 16.2 (postgres:latest Docker image)
• Python version: 3.12.3
• Platform: Tested on macOS and Linux
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• Can the issue be reproduced under both asyncio and uvloop?: Yes

Unexpected behaviour: It seems asyncpg doesn't return the rows returned by a DELETE query rewritten to an UPDATE query by a rule. Perhaps because it's optimizing (the query status is DELETE 0, so perhaps it thinks it doesn't need to return any rows) or something like that? I didn't dive in any further to check if that is indeed what is happening.

Reproduction

import asyncio
import asyncpg

async def main():
    connection = await asyncpg.connect('postgresql://postgres:password@localhost/test')

    async def fetch_print(query, *params):
        result = await connection.fetch(query, *params)
        i = 0
        for row in result:
            print(f'  - {row}')
            i += 1
        if i == 0:
            print('  (no rows returned)')
        print('')

    # Create table with rule for deletion
    await connection.execute('''
        CREATE TABLE items (
            id      serial  PRIMARY KEY,
            name    text    UNIQUE,
            deleted boolean DEFAULT false
        );
        CREATE RULE softdelete AS
            ON DELETE
            TO items
            DO INSTEAD
                UPDATE items SET deleted = true WHERE id = OLD.id RETURNING OLD.*
        ;
        INSERT INTO items (name) VALUES
            ('foo'),
            ('bar')
        ;
    ''')

    print('Our table has a rule that updates the "deleted" column instead of deleting the row.\n')

    # Show contents
    print('We start with 2 rows which are not soft-deleted:')
    await fetch_print('SELECT * FROM items')

    # Try delete (the unexpected case)
    print('Deleting with RETURNING should give us a row (it does in psql), but we do not get any in asyncpg:')
    await fetch_print('''
        DELETE FROM items WHERE name = $1 RETURNING id
    ''', 'foo')

    # Confirm above query worked
    print('But the row is now soft-deleted:')
    await fetch_print('SELECT * FROM items')

    # Workaround
    print('If wrapped in a CTE it does work:')
    await fetch_print('''
        WITH x AS (
            DELETE FROM items WHERE name = $1 RETURNING id
        ) SELECT * FROM x
    ''', 'bar')

    # Confirm above query worked
    print('And it is again properly softdeleted:')
    await fetch_print('SELECT * FROM items')

    print('We now delete the rule.\n')
    await connection.execute('DROP RULE softdelete ON items')

    # Confirm normal delete without rule returns rows
    print('Normal deletion (without the rule) does return rows correctly:')
    await fetch_print('''
        DELETE FROM items RETURNING id
    ''')

    # Confirm above query worked
    print('And now both rows are indeed gone:')
    await fetch_print('SELECT * FROM items')

    # Clean up table after we are done
    await connection.execute('DROP TABLE items')

    await connection.close()

asyncio.run(main())

Output of reproduction

Our table has a rule that updates the "deleted" column instead of deleting the row.

We start with 2 rows which are not soft-deleted:
  - <Record id=1 name='foo' deleted=False>
  - <Record id=2 name='bar' deleted=False>

Deleting with RETURNING should give us a row (it does in psql), but we do not get any in asyncpg:
  (no rows returned)

But the row is now soft-deleted:
  - <Record id=2 name='bar' deleted=False>
  - <Record id=1 name='foo' deleted=True>

If wrapped in a CTE it does work:
  - <Record id=2>

And it is again properly softdeleted:
  - <Record id=1 name='foo' deleted=True>
  - <Record id=2 name='bar' deleted=True>

We now delete the rule.

Normal deletion (without the rule) does return rows correctly:
  - <Record id=1>
  - <Record id=2>

And now both rows are indeed gone:
  (no rows returned)

In contrast the psql command line tool does show me the resulting rows when the result code is DELETED 0.

Output of psql

test=# DELETE FROM items WHERE name = 'foo' RETURNING id;
 id 
----
  1
(1 row)

DELETE 0

So it is a bit unexpected that asyncpg doesn't return any rows.