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
```py
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:
-
-
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:
-
-
If wrapped in a CTE it does work:
-
And it is again properly softdeleted:
-
-
We now delete the rule.
Normal deletion (without the rule) does return rows correctly:
-
-
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.
postgres:latestDocker image)Unexpected behaviour: It seems
asyncpgdoesn'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 isDELETE 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
```py 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: -In contrast the
psqlcommand line tool does show me the resulting rows when the result code isDELETED 0.Output of
``` test=# DELETE FROM items WHERE name = 'foo' RETURNING id; id ---- 1 (1 row) DELETE 0 ```psqlSo it is a bit unexpected that
asyncpgdoesn't return any rows.