update/delete only one row for SQL SERVER foreign

[homelink]

exeuete sql: update if_bord_group_foreign set Name='abc' where name='test' or delete from if_bord_group_foreign where name='test' in PG, It deleted only one row. But I have many rows with name='test' in the DB.

[rdunklau]

If you execute the corresponding select query, how many rows are returned ?

[homelink]

Sure, 3 rows are returned. and if I want to delete those 3 recoreds, I need to execute 3 times delete/update command.

[rdunklau]

How have you declared the rowid column ? Is it mapped against a primary key ?

[homelink]

YES, my foreign table defined below:

create foreign table ifBordGroup_foreign (
groupid integer,
name varchar,
sortid integer
) server alchemy_srv options (
  tablename 'ifBordGroup',
  db_url 'mssql+pymssql://xxxx:xxxxx@host/db',
  primary_key 'groupid'
);
insert into ifBordGroup_foreign(name,sortid) values ('test',100);
insert into ifBordGroup_foreign(name,sortid) values ('test',100);
insert into ifBordGroup_foreign(name,sortid) values ('test',100);
select * from ifBordGroup_foreign where name = 'test'

delete from ifBordGroup_foreign where name='test'
--The first record was deleted, groupid=66

delete from ifbordgroup_foreign where groupid in (66,67,68,69)
--The second record was deleted, groupid=67

select * from ifBordGroup_foreign where name = 'test' result set as below.

I found below code in multicorn.c

static TupleTableSlot *
multicornExecForeignDelete(EState *estate, ResultRelInfo *resultRelInfo,
                           TupleTableSlot *slot, TupleTableSlot *planSlot)
{
    MulticornModifyState *modstate = resultRelInfo->ri_FdwState;
    ConversionInfo *cinfos = *(modstate->resultCinfos);
    PyObject   *fdw_instance = modstate->fdw_instance,
               *p_row_id,
               *p_new_value;
    bool        is_null;
    ConversionInfo *cinfo = modstate->rowidCinfo;
    Datum       value = ExecGetJunkAttribute(planSlot, modstate->rowidAttno, &is_null);

    p_row_id = datumToPython(value, cinfo->atttypoid, cinfo);
    p_new_value = PyObject_CallMethod(fdw_instance, "delete", "(O)", p_row_id);
...

and sqlalchemyfdw.py:

   def delete(self, rowid):
        self.connection.execute(
            self.table.delete()
            .where(self.table.c[self._row_id_column] == rowid))

seems it delete only one record each time.

[rdunklau]

I can reproduce your test case, however I do not have a fix for it right now.

It happens only with SQL Server (at least with pymssql): the problem is, the result set returned by sqlalchemy is somewhat consumed between the select and the individuals DELETE issued after it.

More investigation is needed to understand why its not behaving the same way as with the oracle, postgresql or sqlite drivers.

[rdunklau]

Thank you for this report.