Wrong UPDATE if values in the first column are not unique (data corrupted)

[enterprisedb]

Wrong UPDATE if values in the first column are not unique (data corrupted)

[ibrarahmad]

The first column must be unique, I think that is missing in the
documentation. I will add that in the documentation.

Actually postgres_fdw is using ctid to identify the unique column for
DELETE and UPDATE which does not exists in mysql, so I am using the first
column for that purpose. Some other fdw are using the same technique.

I am thinking to add an option to specify a key column (which must be
unique) while creating a foreign table, instead of using the first column.

On Tue, Mar 3, 2015 at 8:49 PM, Radim Blazek [email protected]
wrote:

PostgreSQL 9.3, MySQL 5.5, mysql_fdw ddd5344
ddd5344

MySQL:

CREATE TABLE foo (
a int,
b int,
c varchar(3)
);
INSERT INTO foo VALUES ( 1, 1, 'aaa');
INSERT INTO foo VALUES ( 1, 2, 'bbb');

PostgreSQL:

CREATE FOREIGN TABLE foo ( a int, b int, c varchar(44) )
SERVER mysql_server OPTIONS (dbname 'test', table_name 'foo');
UPDATE foo set c = 'zzz' WHERE b = 2;
SELECT * FROM foo;
a | b | c
---+---+-----
1 | 1 | zzz
1 | 2 | zzz

I.e. all rows were updated (very dangerous IMO). Works OK if values in 'a'
(the first column) are unique or if the first column is not included in the
foreign table.

BTW, probably not related directly to this issue, but there are also some
strange skips over the first column here
https://github.com/EnterpriseDB/mysql_fdw/blob/e03bc73/mysql_fdw.c#L943
https://github.com/EnterpriseDB/mysql_fdw/blob/e03bc73/mysql_fdw.c#L1244

—
Reply to this email directly or view it on GitHub
#45.

Ibrar Ahmed
EnterpriseDB http://www.enterprisedb.com

[blazek]

If the first column must be unique, it should be checked when the foreign table is created and it should raise an error if it is not unique.

It should be probably also checked again when an UPDATE is executed because the table in mysql could change since foreign table was created.

[ibrarahmad]

Agreed, I will add these checks, thanks for reporting.

On Tue, Mar 3, 2015 at 9:55 PM, Radim Blazek [email protected]
wrote:

If the first column must be unique, it should be checked when the foreign
table is created and it should raise an error if it is not unique.

It should be probably also checked again when an UPDATE is executed
because the table in mysql could change since foreign table was created.

—
Reply to this email directly or view it on GitHub
#45 (comment)
.

Ibrar Ahmed
EnterpriseDB http://www.enterprisedb.com