How to insert mysql_fdw tables?

[sangli00]

select * from test; id | name ----+------ 1 | ss (1 row)

how to insert into test select 2,'tt'; ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation

[sangli00]

why need mysql table first column is unique ? if first column is not unique ,so I can't inset to mysql ?

[ibrarahmad]

The first column is a key column, the delete and update operation need to have at-least one column unique.

On Wed, Mar 30, 2016 at 12:29 PM, LSang notifications@github.com wrote:

why need mysql table first column is unique ? if first column is not unique ,so I can't inset to mysql ?

— You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/96#issuecomment-203290642

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

[sangli00]

if i modify code mysql_fdw.c mysql_is_column_unique function,away return TRUE , is OK?

[ahsanhadi]

Not sure i understand your question?

[sangli00]

I don't know ,why the first column is unique? what mean in this operation .

[ahsanhadi]

I think what Ibrar is saying that we don't have something like OID or tuple-ID in mysql that we can use for performing update/delete operation. So we need to enforce the first column of the table to be unique in order to support these operations..

Ibrar can confirm...

[sangli00]

@ibrarahmad how to fast delete mysql table ? if i can truncate table in mysql .table is so much ,how to do it ? delete from tab is so slowly.

[rmuller]

Why must the first column of the target table of an INSERT operation be unique?

This is a serious issue, because table copy operations are far more expensive because of this constraint.

[ahsanhadi]

On Tue, Sep 19, 2017 at 12:23 PM, Ronald Muller notifications@github.com wrote:

Why must the first column of the target table of an INSERT operation be unique?

As Ibrar has explained earlier "The first column is a key column, the delete and update operation need to be unique". I believe this is needed because mysql doesn't have unique column identifier like PG has oid. I guess the difference is that oid is auto-generated whereas it is not the same with mysql_fdw. Is that part of your concern?

This is a serious issue, because table copy operations are far more expensive because of this constraint.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/96#issuecomment-330453052, or mute the thread https://github.com/notifications/unsubscribe-auth/AHmqpH35hj3T7LN3PArPo-KEoFPV3yWrks5sj2wNgaJpZM4H7ZJ3 .

-- Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

[ahsanhadi]

On Tue, Sep 19, 2017 at 12:59 PM, Ahsan Hadi ahsan.hadi@enterprisedb.com wrote:

On Tue, Sep 19, 2017 at 12:23 PM, Ronald Muller notifications@github.com wrote:

Why must the first column of the target table of an INSERT operation be unique?

As Ibrar has explained earlier "The first column is a key column, the delete and update operation need to be unique". I believe this is needed because mysql doesn't have unique column identifier like PG has oid. I guess the difference is that oid is auto-generated whereas it is not the same with mysql_fdw. Is that part of your concern?

Sorry i meant TID not OID since oid is not mandatory for every table...

This is a serious issue, because table copy operations are far more expensive because of this constraint.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/96#issuecomment-330453052, or mute the thread https://github.com/notifications/unsubscribe-auth/AHmqpH35hj3T7LN3PArPo-KEoFPV3yWrks5sj2wNgaJpZM4H7ZJ3 .

-- Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 <(051)%208358874> Mobile: +92-333-5162114 <0333%205162114>

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

-- Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

[rmuller]

Thanks for your reply.

The tid, or tuple identifier (row identifier) is a pair (block number, tuple index within block) that identifies the physical location of the row within its table.

The tid is the page/offset of the tuple. You can't really remove it because it is a characteristic of the tuple, not really in each tuple. Each page has a number, and each page has slots, so the page number/slotnumber of the tuple is its tid.

I do not understand how this is related to my question.

[ibrarahmad]

For update and delete PostgreSQL require a unique column to do that operations. The postgresql_fdw uses ctid to uniquely identify row to perform delete / update. For detail read postgresql_fdw.c file's function postgresAddForeignUpdateTargets. This column is hidden and we don't need any other column for that. But in mysql_fdw we don't have hidden column like that so we make a requirement for first column to be unique. We are using first column to uniquely identify the rows for UPDATE/DELETE operation.

On Wed, Sep 20, 2017 at 10:46 AM, Ronald Muller notifications@github.com wrote:

Thanks for your reply.

The tid, or tuple identifier (row identifier) is a pair (block number, tuple index within block) that identifies the physical location of the row within its table.

The tid is the page/offset of the tuple. You can't really remove it because it is a characteristic of the tuple, not really in each tuple. Each page has a number, and each page has slots, so the page number/slotnumber of the tuple is its tid.

I do not understand how this is related to my question.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/96#issuecomment-330751397, or mute the thread https://github.com/notifications/unsubscribe-auth/ADbS8t0C1h9h2tolvXWv2GAJsTY63YeKks5skKavgaJpZM4H7ZJ3 .

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

[rmuller]

Thanks