Обсуждение: PostgreSQL: SQLSetPos fails with SetPos update return error.

Hi Guys,

I am hitting a peculiar error when trying to update a row in PostgreSQL database using odbc SQLSetPos call.

I read in PostgreSQL forums that it doesn’t support POSITIONED UPDATE.

The error being returned is:

STATE=HY000, CODE=7, MSG=SetPos update return error\n" ...} }

I am using 09.03.04 version of postgres ODBC driver and 9.4.5 version of postgres server.

IS there any known issue when doing update using odbc SQLSetPos API?

Any workaround/solution that can be applied to get rid of this problem?

This needs to be resolved as soon as possible. So any feedback would be really helpful.

Thanks,
Sekhar

Hi Hiroshi,

Thanks for the response.

Below is the snippet of the MyLog output. Let me know what you find.

FYI: After I enable DB partitioning feature , I see this problem.

++++++++++++++++++++

[42328-902.543]libpq_bind_and_exec: execute stmt=000000000338A150

[42328-902.543]SC_scanQueryAndCountParams: entering...

[42328-902.543]SC_scanQueryAndCountParams: entering...

[42328-902.543]process_statements:parsed for the first command length=-1(-1) num_p=72

[42328-902.543]in QR_Constructor

[42328-902.543]exit QR_Constructor

[42328-902.543]num_fields = 1

[42328-902.543]in QR_set_num_fields

[42328-902.543]exit QR_set_num_fields

[42328-902.543]QR_from_PGResult: fieldname='ctid', adtid=27, adtsize=6, atttypmod=-1 (rel,att)=(710649,-1)

[42328-902.543]REALLOC: old_count = 0, size = 0

[42328-902.543]qresult: len=6, buffer='(0,18)'

[42328-902.543]qresult: len=5, buffer='(0,6)'

[42328-902.543]extend_column_bindings: entering ... self=000000000338A230, bindings_allocated=0, num_columns=1

[42328-902.543]exit extend_column_bindings=00000000017C1EE0

[42328-902.543]SC_set_Result(338a150, 3d49950)[42328-902.543]QResult: enter DESTRUCTOR

[42328-902.543]retval=0

[42328-902.543]PGAPI_ExecDirect: returned 0 from PGAPI_Execute

[42328-902.543]pos_update_callback in

[42328-902.543]STATEMENT ERROR: func=irow_update, desc='', errnum=7, errmsg='SetPos update return error'

[42328-902.543]CONN ERROR: func=irow_update, desc='', errnum=0, errmsg='(NULL)'

[42328-902.543]PGAPI_FreeStmt: entering...hstmt=000000000338A150, fOption=1

[42328-902.543]QResult: enter DESTRUCTOR

[42328-902.543]QResult: in QR_close_result

[42328-902.543]QResult: free memory in, fcount=2

[42328-902.543]QResult: free memory out

[42328-902.543]QResult: exit close_result

[42328-902.543]QResult: exit DESTRUCTOR

[42328-902.543]SC_init_Result(338a150)[42328-902.543]SC_Destructor: self=000000000338A150, self->result=0000000000000000, self->hdbc=00000000003D9020

[42328-902.543]reset_a_column_binding: entering ... self=000000000338A230, bindings_allocated=1, icol=1

[42328-902.543]APD_free_params:  ENTER, self=000000000338A310

[42328-902.543]APD_free_params:  EXIT

[42328-902.559]IPD_free_params:  ENTER, self=000000000338A380

[42328-902.559]IPD_free_params:  EXIT

[42328-902.559]PDATA_free_params:  ENTER, self=000000000338A470

[42328-902.559]PDATA_free_params:  EXIT

[42328-902.559]SC_Destructor: EXIT

[42328-902.559]PGAPI_SetPos returning -1

[42328-902.559][SQLGetDiagRecW][42328-902.559]PGAPI_GetDiagRec entering type=3 rec=1

[42328-902.559]ER_ReturnError: status = 7, msg = #SetPos update return error#

[42328-902.559]                     szSqlState = 'HY000',len=26, szError='SetPos update return error'

[42328-902.559]PGAPI_GetDiagRec exiting 0

[42328-902.559][[SQLFreeHandle]][42328-902.559]PGAPI_FreeStmt: entering...hstmt=0000000003388950, fOption=1

[42328-902.559]QResult: enter DESTRUCTOR

[42328-902.559]QResult: in QR_close_result

[42328-902.559]QResult: free memory in, fcount=1

[42328-902.559]CC_send_query: conn=00000000003D9020, query='DEALLOCATE "_KEYSET_0000000003238830"'

[42328-902.559]in QR_Constructor

[42328-902.559]exit QR_Constructor

[42328-902.559]send_query: ok - 'C' - SAVEPOINT

[42328-902.559]send_query: setting cmdbuffer = 'SAVEPOINT'

[42328-902.559]send_query: ok - 'C' - DEALLOCATE

[42328-902.559]send_query: setting cmdbuffer = 'DEALLOCATE'

[42328-902.559]send_query: returning res = 0000000003D496B0

[42328-902.559]send_query: ok - 'C' - RELEASE

[42328-902.559]in QR_Constructor

[42328-902.559]exit QR_Constructor

[42328-902.559]send_query: setting cmdbuffer = 'RELEASE'

[42328-902.559]send_query: returning res = 0000000003D48ED0

[42328-902.559]QResult: enter DESTRUCTOR

[42328-902.559]QResult: in QR_close_result

[42328-902.559]QResult: free memory in, fcount=0

[42328-902.559]QResult: free memory out

++++++++++++++++

Hi Sekhar,

Hi Guys,

 

I am hitting a peculiar error when trying to update a row in PostgreSQL database using odbc SQLSetPos call.

I read in PostgreSQL forums that it doesn’t support POSITIONED UPDATE.

 

The error being returned is:

 

STATE=HY000, CODE=7, MSG=SetPos update return error\n" ...} }

Are there any other error messages?
If there's no other message, could you take Mylog output and send it to me?

regards,
Hiroshi Inoue

Hi,

I didn’t send the complete traces since it is huge (upto 200MB). If you still need them, let me know how to share it?

Please provide some feedback as this needs to be resolved to complete our product release.

Thanks,

Sekhar

Hi Hiroshi,

Thanks for the response.

Below is the snippet of the MyLog output. Let me know what you find.

FYI: After I enable DB partitioning feature , I see this problem.

++++++++++++++++++++

[42328-902.543]libpq_bind_and_exec: execute stmt=000000000338A150

[42328-902.543]SC_scanQueryAndCountParams: entering...

[42328-902.543]SC_scanQueryAndCountParams: entering...

[42328-902.543]process_statements:parsed for the first command length=-1(-1) num_p=72

[42328-902.543]in QR_Constructor

[42328-902.543]exit QR_Constructor

[42328-902.543]num_fields = 1

[42328-902.543]in QR_set_num_fields

[42328-902.543]exit QR_set_num_fields

[42328-902.543]QR_from_PGResult: fieldname='ctid', adtid=27, adtsize=6, atttypmod=-1 (rel,att)=(710649,-1)

[42328-902.543]REALLOC: old_count = 0, size = 0

[42328-902.543]qresult: len=6, buffer='(0,18)'

[42328-902.543]qresult: len=5, buffer='(0,6)'

[42328-902.543]extend_column_bindings: entering ... self=000000000338A230, bindings_allocated=0, num_columns=1

[42328-902.543]exit extend_column_bindings=00000000017C1EE0

[42328-902.543]SC_set_Result(338a150, 3d49950)[42328-902.543]QResult: enter DESTRUCTOR

[42328-902.543]retval=0

[42328-902.543]PGAPI_ExecDirect: returned 0 from PGAPI_Execute

[42328-902.543]pos_update_callback in

[42328-902.543]STATEMENT ERROR: func=irow_update, desc='', errnum=7, errmsg='SetPos update return error'

[42328-902.543]CONN ERROR: func=irow_update, desc='', errnum=0, errmsg='(NULL)'

[42328-902.543]PGAPI_FreeStmt: entering...hstmt=000000000338A150, fOption=1

[42328-902.543]QResult: enter DESTRUCTOR

[42328-902.543]QResult: in QR_close_result

[42328-902.543]QResult: free memory in, fcount=2

[42328-902.543]QResult: free memory out

[42328-902.543]QResult: exit close_result

[42328-902.543]QResult: exit DESTRUCTOR

[42328-902.543]SC_init_Result(338a150)[42328-902.543]SC_Destructor: self=000000000338A150, self->result=0000000000000000, self->hdbc=00000000003D9020

[42328-902.543]reset_a_column_binding: entering ... self=000000000338A230, bindings_allocated=1, icol=1

[42328-902.543]APD_free_params:  ENTER, self=000000000338A310

[42328-902.543]APD_free_params:  EXIT

[42328-902.559]IPD_free_params:  ENTER, self=000000000338A380

[42328-902.559]IPD_free_params:  EXIT

[42328-902.559]PDATA_free_params:  ENTER, self=000000000338A470

[42328-902.559]PDATA_free_params:  EXIT

[42328-902.559]SC_Destructor: EXIT

[42328-902.559]PGAPI_SetPos returning -1

[42328-902.559][SQLGetDiagRecW][42328-902.559]PGAPI_GetDiagRec entering type=3 rec=1

[42328-902.559]ER_ReturnError: status = 7, msg = #SetPos update return error#

[42328-902.559]                     szSqlState = 'HY000',len=26, szError='SetPos update return error'

[42328-902.559]PGAPI_GetDiagRec exiting 0

[42328-902.559][[SQLFreeHandle]][42328-902.559]PGAPI_FreeStmt: entering...hstmt=0000000003388950, fOption=1

[42328-902.559]QResult: enter DESTRUCTOR

[42328-902.559]QResult: in QR_close_result

[42328-902.559]QResult: free memory in, fcount=1

[42328-902.559]CC_send_query: conn=00000000003D9020, query='DEALLOCATE "_KEYSET_0000000003238830"'

[42328-902.559]in QR_Constructor

[42328-902.559]exit QR_Constructor

[42328-902.559]send_query: ok - 'C' - SAVEPOINT

[42328-902.559]send_query: setting cmdbuffer = 'SAVEPOINT'

[42328-902.559]send_query: ok - 'C' - DEALLOCATE

[42328-902.559]send_query: setting cmdbuffer = 'DEALLOCATE'

[42328-902.559]send_query: returning res = 0000000003D496B0

[42328-902.559]send_query: ok - 'C' - RELEASE

[42328-902.559]in QR_Constructor

[42328-902.559]exit QR_Constructor

[42328-902.559]send_query: setting cmdbuffer = 'RELEASE'

[42328-902.559]send_query: returning res = 0000000003D48ED0

[42328-902.559]QResult: enter DESTRUCTOR

[42328-902.559]QResult: in QR_close_result

[42328-902.559]QResult: free memory in, fcount=0

[42328-902.559]QResult: free memory out

++++++++++++++++

Hi Sekhar,

Hi Guys,

 

I am hitting a peculiar error when trying to update a row in PostgreSQL database using odbc SQLSetPos call.

I read in PostgreSQL forums that it doesn’t support POSITIONED UPDATE.

 

The error being returned is:

 

STATE=HY000, CODE=7, MSG=SetPos update return error\n" ...} }

Are there any other error messages?
If there's no other message, could you take Mylog output and send it to me?

regards,
Hiroshi Inoue

From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.com]

FYI: After I enable DB partitioning feature , I see this problem.

I guess your app performed an UPDATE statement which changes the value of the partitioning key columns.

Unfortunately, as Inoue-san said, the ODBC driver cannot handle that case.  Changing the partitioning key value causes the database server to move the row to a different partition (= child table).  On the other hand, the ODBC driver uses the table name and ctid to position the row to update.  But the driver uses the original table name, not the target child table name, failing to find the row to update.  The driver cannot know the appropriate child table name.  It it due to the lack of real partitioning as in other database products that the client side has to be aware of the child table.

Your case is described in the manual below.  As mentioned, the workaround is to use UPDATE trigger.

http://www.postgresql.org/docs/current/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS

[Excerpt]

The schemes shown here assume that the partition key column(s) of a row never change, or at least do not change enough to require it to move to another partition. An UPDATE that attempts to do that will fail because of the CHECK constraints. If you need to handle such cases, you can put suitable update triggers on the partition tables, but it makes management of the structure much more complicated.

Regards

Takayuki Tsunakawa

Thanks Takayuki and Inoue for the update.
We want to support data partitioning in our application for PostgreSQL.

In this case, I have partitioned all the tables, the application uses in the database and have written triggers to move the data from the original tables to partitioned tables so that triggers manage the data migration.

I need to understand how to write update triggers which you mentioned as the workaround.

Any examples/samples which I can refer would really help. Since all the tables are partitioned, I assume I have to make sure the odbc driver knows the partitioned table name’s row to update. This has to be implemented as part of the trigger. Isn’t it?

Can you please point  me to any samples if any ?

Thanks for all the help,

Sekhar

From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.com]

FYI: After I enable DB partitioning feature , I see this problem.

I guess your app performed an UPDATE statement which changes the value of the partitioning key columns.

Unfortunately, as Inoue-san said, the ODBC driver cannot handle that case.  Changing the partitioning key value causes the database server to move the row to a different partition (= child table).  On the other hand, the ODBC driver uses the table name and ctid to position the row to update.  But the driver uses the original table name, not the target child table name, failing to find the row to update.  The driver cannot know the appropriate child table name.  It it due to the lack of real partitioning as in other database products that the client side has to be aware of the child table.

Your case is described in the manual below.  As mentioned, the workaround is to use UPDATE trigger.

http://www.postgresql.org/docs/current/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS

[Excerpt]

The schemes shown here assume that the partition key column(s) of a row never change, or at least do not change enough to require it to move to another partition. An UPDATE that attempts to do that will fail because of the CHECK constraints. If you need to handle such cases, you can put suitable update triggers on the partition tables, but it makes management of the structure much more complicated.

Regards

Takayuki Tsunakawa

Also I would like to understand which partition key is being referred here. IS there any information in MyLog output that indicates that?

This is just understand the use-case. Let me know.

Thanks Takayuki and Inoue for the update.
We want to support data partitioning in our application for PostgreSQL.

In this case, I have partitioned all the tables, the application uses in the database and have written triggers to move the data from the original tables to partitioned tables so that triggers manage the data migration.

I need to understand how to write update triggers which you mentioned as the workaround.

Any examples/samples which I can refer would really help. Since all the tables are partitioned, I assume I have to make sure the odbc driver knows the partitioned table name’s row to update. This has to be implemented as part of the trigger. Isn’t it?

Can you please point  me to any samples if any ?

Thanks for all the help,

Sekhar

From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.com]

FYI: After I enable DB partitioning feature , I see this problem.

I guess your app performed an UPDATE statement which changes the value of the partitioning key columns.

Unfortunately, as Inoue-san said, the ODBC driver cannot handle that case.  Changing the partitioning key value causes the database server to move the row to a different partition (= child table).  On the other hand, the ODBC driver uses the table name and ctid to position the row to update.  But the driver uses the original table name, not the target child table name, failing to find the row to update.  The driver cannot know the appropriate child table name.  It it due to the lack of real partitioning as in other database products that the client side has to be aware of the child table.

Your case is described in the manual below.  As mentioned, the workaround is to use UPDATE trigger.

http://www.postgresql.org/docs/current/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS

[Excerpt]

The schemes shown here assume that the partition key column(s) of a row never change, or at least do not change enough to require it to move to another partition. An UPDATE that attempts to do that will fail because of the CHECK constraints. If you need to handle such cases, you can put suitable update triggers on the partition tables, but it makes management of the structure much more complicated.

Regards

Takayuki Tsunakawa

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

Also I would like to understand which partition key is being referred here. IS there any information in MyLog output that indicates that?

This is just understand the use-case. Let me know.

No, it's an inspiration from your use of partitioning.

From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.com]

I need to understand how to write update triggers which you mentioned as the workaround.

Any examples/samples which I can refer would really help. Since all the tables are partitioned, I assume I have to make sure the odbc driver knows the partitioned table name’s row to update. This has to be implemented as part of the trigger. Isn’t it?

Sorry, I misunderstood.  I'm afraid there's no workaround.

The ODBC driver probably has to handle the migration of a row between partitions.  Currently it tracks the row position by fetching the new ctid value with "INSERT/UPDATE ... RETURNING ctid".  The table name is fixed.

Instead, the driver has to fetch tableoid as well as ctid, and store it in memory with each row.  In addition, the driver has to fetch the table name which corresponds to the tableoid, and store the mapping of tableoid to table name.  It uses the map to build UPDATE statements.  I'm not sure yet if it's feasible and how difficult, as I'm new to psqlODBC.

Until when does this problem need to be solved?  What's the effect if it is not solved?  Does your product fail to certify against PostgreSQL, or some function is unavailable?

Regards

Takayuki Tsunakawa

Hi Takayuki,

Thanks for your patience and in depth response to my email.

WE are trying to certify our product with PostgreSQL DB. One of the features which our product supports is data partitioning on all RDBMS like SQL Server, Oracle, etc.

We want to support it for PostgreSQL DB too.

We cannot certify our product using partitioning with postgeSQL if the current issue is not solved.

I would like to understand that if I implement “Enabling Row Movement in a Partitioned Table”, will odbc driver handle this case?

Or odbc driver needs to be modified (as you mentioned in your previous response) to handle updates on partitioning keys.

Any samples to achieve automatic row movement would help in this case if it can be done.

Thanks,

Sekhar

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

Also I would like to understand which partition key is being referred here. IS there any information in MyLog output that indicates that?

This is just understand the use-case. Let me know.

No, it's an inspiration from your use of partitioning.

From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.com]

I need to understand how to write update triggers which you mentioned as the workaround.

Any examples/samples which I can refer would really help. Since all the tables are partitioned, I assume I have to make sure the odbc driver knows the partitioned table name’s row to update. This has to be implemented as part of the trigger. Isn’t it?

Sorry, I misunderstood.  I'm afraid there's no workaround.

The ODBC driver probably has to handle the migration of a row between partitions.  Currently it tracks the row position by fetching the new ctid value with "INSERT/UPDATE ... RETURNING ctid".  The table name is fixed.

Instead, the driver has to fetch tableoid as well as ctid, and store it in memory with each row.  In addition, the driver has to fetch the table name which corresponds to the tableoid, and store the mapping of tableoid to table name.  It uses the map to build UPDATE statements.  I'm not sure yet if it's feasible and how difficult, as I'm new to psqlODBC.

Until when does this problem need to be solved?  What's the effect if it is not solved?  Does your product fail to certify against PostgreSQL, or some function is unavailable?

Regards

Takayuki Tsunakawa

Hi Takayuki,

Is there any plans to implement Automatic row movement for partition in PostgreSQL?

We need to certify PostgreSQL data partitioning feature with our product. This is a must-do feature.

We feel if the odbc driver itself supports this functionality it’s less cumbersome for the application.

Let me know what is your plan/suggestion.

Thanks,
Sekhar

Hi Takayuki,

Thanks for your patience and in depth response to my email.

WE are trying to certify our product with PostgreSQL DB. One of the features which our product supports is data partitioning on all RDBMS like SQL Server, Oracle, etc.

We want to support it for PostgreSQL DB too.

We cannot certify our product using partitioning with postgeSQL if the current issue is not solved.

I would like to understand that if I implement “Enabling Row Movement in a Partitioned Table”, will odbc driver handle this case?

Or odbc driver needs to be modified (as you mentioned in your previous response) to handle updates on partitioning keys.

Any samples to achieve automatic row movement would help in this case if it can be done.

Thanks,

Sekhar

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

Also I would like to understand which partition key is being referred here. IS there any information in MyLog output that indicates that?

This is just understand the use-case. Let me know.

No, it's an inspiration from your use of partitioning.

From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.com]

I need to understand how to write update triggers which you mentioned as the workaround.

Any examples/samples which I can refer would really help. Since all the tables are partitioned, I assume I have to make sure the odbc driver knows the partitioned table name’s row to update. This has to be implemented as part of the trigger. Isn’t it?

Sorry, I misunderstood.  I'm afraid there's no workaround.

The ODBC driver probably has to handle the migration of a row between partitions.  Currently it tracks the row position by fetching the new ctid value with "INSERT/UPDATE ... RETURNING ctid".  The table name is fixed.

Instead, the driver has to fetch tableoid as well as ctid, and store it in memory with each row.  In addition, the driver has to fetch the table name which corresponds to the tableoid, and store the mapping of tableoid to table name.  It uses the map to build UPDATE statements.  I'm not sure yet if it's feasible and how difficult, as I'm new to psqlODBC.

Until when does this problem need to be solved?  What's the effect if it is not solved?  Does your product fail to certify against PostgreSQL, or some function is unavailable?

Regards

Takayuki Tsunakawa

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

Is there any plans to implement Automatic row movement for partition in PostgreSQL?

We need to certify PostgreSQL data partitioning feature with our product. This is a must-do feature.

We feel if the odbc driver itself supports this functionality it’s less cumbersome for the application.

Let me know what is your plan/suggestion.

I'll try to figure out if it's feasible, because I'd be glat if EMC certifies PostgreSQL with their products.  Give me a few days.

Regards

Takayuki Tsunakawa

Thanks Takayuki. Let me know your plans/progress as and when you have more information on this.

Thanks,

Sekhar

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

Is there any plans to implement Automatic row movement for partition in PostgreSQL?

We need to certify PostgreSQL data partitioning feature with our product. This is a must-do feature.

We feel if the odbc driver itself supports this functionality it’s less cumbersome for the application.

Let me know what is your plan/suggestion.

I'll try to figure out if it's feasible, because I'd be glat if EMC certifies PostgreSQL with their products.  Give me a few days.

Regards

Takayuki Tsunakawa

Does this new odbc driver addresses  automatic row movement during partition key changes?

Hi,

Could you please try the 09.05.0211 at
  http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/index.html
?

regards,
Hiroshi Inoue

Thanks Takayuki. Let me know your plans/progress as and when you have more information on this.

 

Thanks,

Sekhar

 

From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Wednesday, May 11, 2016 1:26 PM
To: Venkatesan, Sekhar; Inoue, Hiroshi
Cc: Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

Is there any plans to implement Automatic row movement for partition in PostgreSQL?

We need to certify PostgreSQL data partitioning feature with our product. This is a must-do feature.

We feel if the odbc driver itself supports this functionality it’s less cumbersome for the application.

Let me know what is your plan/suggestion.

 

 

I'll try to figure out if it's feasible, because I'd be glat if EMC certifies PostgreSQL with their products.  Give me a few days.

 

Regards

Takayuki Tsunakawa

このメッセージにウイルス は検出されませんでした。
AVG によってチェックされました - www.avg.com
バージョン： 2016.0.7597 / ウイルスデータベース：4568/12245 - リリース日：2016/05/16

Hello, Inoue-san, Sekhar,

Sorry for my late reply, I had to (and have still been) deal with my customer's trouble in the field, was away from work to go to hospital, etc.  And I still don't understand psqlODBC well enough to keep up with Inoue-san.

From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]

I don't think the cause of the SQLSetPos failure reported is partition row movement.

Yes, the cause is simply that the current SQLSetPos() implementation does not handle table inheritance.  For example, if a table called "parent" has some child tables like "child1" and "child2", and only the child tables have actual rows, SQLSetPos() against the result set from "parent" issues UPDATE statements on "parent".  It should execute UPDATE on the child tables instead.

Regards

Takayuki Tsunakawa

Hello, Sekhar,

From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]

Sent: Tuesday, May 17, 2016 6:09 PM

Could you please try the 09.05.0211 at

  http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/index.html

?

Could you try this Inoue-san's driver?

If you need the driver for other OSes, try building the driver from Git.

Regards

Takayuki Tsunakawa

Hi Takayuki/Inoue San,

The latest driver provided resolves the “SetPos update” error that you guys mentioned. Thanks for the fix.

Is the fix provided an official and certified one? Can you also provide the fix for Linux?

We are trying to certify our product with PostgreSQL (windows and linux). So we need confirmation from you regarding the right odbc driver version (with this fix in there) so that we can go ahead with our functional testing with the new fix in place.

There is one another issue regarding partition’s automatic row movement with regards to partitioning key change. This is not fixed.

Let me know if it is feasible to fix that as well.

Thanks for all the effort and help provided so far. Appreciate it !!!

Thanks,

Sekhar

Hello, Sekhar,

From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]

Sent: Tuesday, May 17, 2016 6:09 PM

Could you please try the 09.05.0211 at

  http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/index.html

?

Could you try this Inoue-san's driver?

If you need the driver for other OSes, try building the driver from Git.

Regards

Takayuki Tsunakawa

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

The latest driver provided resolves the “SetPos update” error that you guys mentioned. Thanks for the fix.

That's good news.

Is the fix provided an official and certified one? Can you also provide the fix for Linux?

Inoue-san is the only committer now and he will decide that.

There is one another issue regarding partition’s automatic row movement with regards to partitioning key change. This is not fixed.

Let me know if it is feasible to fix that as well.

As Inoue-san and I mentioned before, automatic row movement seems to have nothing to do with your problem.  Could you elaborate your test case which fails?

Regards

Takayuki Tsunakawa

Hi Takayuki/ Inoue,

When partition is enabled in my application and I change the partitioning key in the table to save the new data into another partition, I get error like this:

ERROR: new row for relation "testpart_s" violates check constraint " p1_testpart12_s"

I enabled constraint_exclusion in postgresql.conf.

I followed the document you shared to enable row movement across partitions by writing triggers on partition tables. It works up to a point but not confident it will work for all use-cases.

If this can be fixed in the odbc driver itself, it would be better. Let me know your thoughts on this.

Thanks,
Sekhar

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

The latest driver provided resolves the “SetPos update” error that you guys mentioned. Thanks for the fix.

That's good news.

Is the fix provided an official and certified one? Can you also provide the fix for Linux?

Inoue-san is the only committer now and he will decide that.

There is one another issue regarding partition’s automatic row movement with regards to partitioning key change. This is not fixed.

Let me know if it is feasible to fix that as well.

As Inoue-san and I mentioned before, automatic row movement seems to have nothing to do with your problem.  Could you elaborate your test case which fails?

Regards

Takayuki Tsunakawa

Inoue,

Can you please let me know if a fix can be provided for the “SetPos Update” issue. I need it for Linux and Windows?

Thanks,
Sekhar

Hi Takayuki/ Inoue,

When partition is enabled in my application and I change the partitioning key in the table to save the new data into another partition, I get error like this:

ERROR: new row for relation "testpart_s" violates check constraint " p1_testpart12_s"

I enabled constraint_exclusion in postgresql.conf.

I followed the document you shared to enable row movement across partitions by writing triggers on partition tables. It works up to a point but not confident it will work for all use-cases.

If this can be fixed in the odbc driver itself, it would be better. Let me know your thoughts on this.

Thanks,
Sekhar

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

The latest driver provided resolves the “SetPos update” error that you guys mentioned. Thanks for the fix.

That's good news.

Is the fix provided an official and certified one? Can you also provide the fix for Linux?

Inoue-san is the only committer now and he will decide that.

There is one another issue regarding partition’s automatic row movement with regards to partitioning key change. This is not fixed.

Let me know if it is feasible to fix that as well.

As Inoue-san and I mentioned before, automatic row movement seems to have nothing to do with your problem.  Could you elaborate your test case which fails?

Regards

Takayuki Tsunakawa

From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.com]
 When partition is enabled in my application and I change the partitioning key in the table to save the new data into another partition, I get error like this:

ERROR: new row for relation "testpart_s" violates check constraint " p1_testpart12_s"

I enabled constraint_exclusion in postgresql.conf.

I followed the document you shared to enable row movement across partitions by writing triggers on partition tables. It works up to a point but not confident it will work for all use-cases.

Did this error begin to happen with the unreleased driver, or already happen with the officially released driver?

SQLSetPos(SQL_UPDATE) issues an UPDATE statement.  Could you identify the statement causing the error by logging the statements, i.e. adding log_statement = ‘all’ in postgresql.conf?  Does the statement seem to be wroing when you consider the check constraint name, i.e. is the target table correct?

Are you sure your trigger specifies the appropriate table for the new key value?

Regards

Takayuki Tsunakawa

Hi Inoue,

I am looking for a new postgres ODBC driver installer for windows and RHEL OS with fix for “SetPos Update” error.

I looked at https://www.postgresql.org/ftp/odbc/versions/msi/ but the latest drivers are not available.

Can you let me know when this will be available so that I can plan the testing activity?

Thanks,
Sekhar

I just pushed the change about inheritance to git.

regards,
Hiroshi Inoue

Inoue,

 

Can you please let me know if a fix can be provided for the “SetPos Update” issue. I need it for Linux and Windows?

 

Thanks,
Sekhar

 

From: Venkatesan, Sekhar
Sent: Wednesday, May 25, 2016 11:04 AM
To: 'Tsunakawa, Takayuki'
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

Hi Takayuki/ Inoue,

 

When partition is enabled in my application and I change the partitioning key in the table to save the new data into another partition, I get error like this:

 

ERROR: new row for relation "testpart_s" violates check constraint " p1_testpart12_s"

 

I enabled constraint_exclusion in postgresql.conf.

I followed the document you shared to enable row movement across partitions by writing triggers on partition tables. It works up to a point but not confident it will work for all use-cases.

If this can be fixed in the odbc driver itself, it would be better. Let me know your thoughts on this.

 

Thanks,
Sekhar

 

From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Monday, May 23, 2016 1:18 PM
To: Venkatesan, Sekhar
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

The latest driver provided resolves the “SetPos update” error that you guys mentioned. Thanks for the fix.

 

 

That's good news.

 

 

Is the fix provided an official and certified one? Can you also provide the fix for Linux?

 

 

Inoue-san is the only committer now and he will decide that.

 

 

There is one another issue regarding partition’s automatic row movement with regards to partitioning key change. This is not fixed.

Let me know if it is feasible to fix that as well.

 

 

As Inoue-san and I mentioned before, automatic row movement seems to have nothing to do with your problem.  Could you elaborate your test case which fails?

 

Regards

Takayuki Tsunakawa

Hi Takayuki,

I have the following constraints for the table in question w.r.t data partitioning.

dm_repo51_docbase=> \d+ testpart25_s

                           Table "repo51.testpart25_s"

    Column    |     Type      | Modifiers | Storage  | Stats target | Descriptio

n

--------------+---------------+-----------+----------+--------------+-----------

--

r_object_id  | character(16) |           | extended |              |

c1           | integer       |           | plain    |              |

i_is_replica | smallint      |           | plain    |              |

i_vstamp     | integer       |           | plain    |              |

i_partition  | integer       |           | plain    |              |

Indexes:

    "d_1f0013ea80000502" UNIQUE, btree (r_object_id, i_partition)

Triggers:

    tr_fn_trigg_testpart25_s BEFORE INSERT ON testpart25_s FOR EACH ROW EXECUTE

PROCEDURE trigg_testpart25_s()

Child tables: p1_testpart25_s,

              p2_testpart25_s,

              p3_testpart25_s,

              p4_testpart25_s

dm_repo51_docbase=> \d+ p1_testpart25_s

                          Table "repo51.p1_testpart25_s"

    Column    |     Type      | Modifiers | Storage  | Stats target | Descriptio

n

--------------+---------------+-----------+----------+--------------+-----------

--

r_object_id  | character(16) |           | extended |              |

c1           | integer       |           | plain    |              |

i_is_replica | smallint      |           | plain    |              |

i_vstamp     | integer       |           | plain    |              |

i_partition  | integer       |           | plain    |              |

Check constraints:

    "chk_2" CHECK (i_partition < 2)

Inherits: testpart25_s

dm_repo51_docbase=> \d+ p4_testpart25_s

                          Table "repo51.p4_testpart25_s"

    Column    |     Type      | Modifiers | Storage  | Stats target | Descriptio

n

--------------+---------------+-----------+----------+--------------+-----------

--

r_object_id  | character(16) |           | extended |              |

c1           | integer       |           | plain    |              |

i_is_replica | smallint      |           | plain    |              |

i_vstamp     | integer       |           | plain    |              |

i_partition  | integer       |           | plain    |              |

Check constraints:

    "chk_10" CHECK (i_partition >= 8 AND i_partition < 10)

Inherits: testpart25_s

I enabled logs for the specific use-case for UPDATE statement and below is the snippet of the error (in postgresql.conf):

++++++++++++

2016-06-06 11:57:58 IST LOG:  statement: RELEASE _EXEC_SVP_0000000001569810

2016-06-06 11:57:58 IST LOG:  statement: SAVEPOINT _EXEC_SVP_0000000001569810

2016-06-06 11:57:58 IST LOG:  execute <unnamed>:  UPDATE testpart25_s  SET i_partition=   9,i_vstamp=   1 WHERE (r_object_id=$1           AND i_vstamp=$2           )

2016-06-06 11:57:58 IST DETAIL:  parameters: $1 = '000013ea80000500', $2 = '0'

2016-06-06 11:57:58 IST ERROR:  new row for relation "p1_testpart25_s" violates check constraint "chk_2"

2016-06-06 11:57:58 IST DETAIL:  Failing row contains (000013ea80000500, 56, 0, 1, 9).

2016-06-06 11:57:58 IST STATEMENT:   UPDATE testpart25_s  SET i_partition=   9,i_vstamp=   1 WHERE (r_object_id=$1           AND i_vstamp=$2           )

2016-06-06 11:57:58 IST LOG:  statement: ROLLBACK to _EXEC_SVP_0000000001569810

2016-06-06 11:57:58 IST LOG:  statement: RELEASE _EXEC_SVP_0000000001569810

2016-06-06 11:57:58 IST LOG:  statement: ROLLBACK

++++++++++++

The partitioning key (check constraint) defined is on “i_partition” column.

As you can see, update statement is fired on first partition  (p1_testpart25_s) rather than p4_testpart25_s table (as i_partition value of 9 should move the data to p4_testpart25_s table).

I expected automatic row movement to happen to p4_testpart25_s table from PostgreSQL itself which isn’t the case.

To work around the problem, I created UPDATE database triggers on partitioned tables that redirects the UPDATEs as INSERT statement on main table (testpart25_s) and the INSERT trigger on main table would push the data to the partitioned table.

This works as expected. But I’m not sure if all use-cases would work with this approach.

It would be great if PostgreSQL/ODBC driver itself does this automatic row movement rather than the application handling it. (Other databases like Oracle and SQL Server handle this by itself).

Let me know what you think.

Thanks,

Sekhar

From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.com]
 When partition is enabled in my application and I change the partitioning key in the table to save the new data into another partition, I get error like this:

ERROR: new row for relation "testpart_s" violates check constraint " p1_testpart12_s"

I enabled constraint_exclusion in postgresql.conf.

I followed the document you shared to enable row movement across partitions by writing triggers on partition tables. It works up to a point but not confident it will work for all use-cases.

Did this error begin to happen with the unreleased driver, or already happen with the officially released driver?

SQLSetPos(SQL_UPDATE) issues an UPDATE statement.  Could you identify the statement causing the error by logging the statements, i.e. adding log_statement = ‘all’ in postgresql.conf?  Does the statement seem to be wroing when you consider the check constraint name, i.e. is the target table correct?

Are you sure your trigger specifies the appropriate table for the new key value?

Regards

Takayuki Tsunakawa

Hi Inoue,

When can I expect a build for windows and linux? This is required to do complete functional testing on our side.

Will it be updated in https://www.postgresql.org/ftp/odbc/versions/msi/ ?

Thanks,

Sekhar

I just pushed the change about inheritance to git.

regards,
Hiroshi Inoue

Inoue,

 

Can you please let me know if a fix can be provided for the “SetPos Update” issue. I need it for Linux and Windows?

 

Thanks,
Sekhar

 

From: Venkatesan, Sekhar
Sent: Wednesday, May 25, 2016 11:04 AM
To: 'Tsunakawa, Takayuki'
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

Hi Takayuki/ Inoue,

 

When partition is enabled in my application and I change the partitioning key in the table to save the new data into another partition, I get error like this:

 

ERROR: new row for relation "testpart_s" violates check constraint " p1_testpart12_s"

 

I enabled constraint_exclusion in postgresql.conf.

I followed the document you shared to enable row movement across partitions by writing triggers on partition tables. It works up to a point but not confident it will work for all use-cases.

If this can be fixed in the odbc driver itself, it would be better. Let me know your thoughts on this.

 

Thanks,
Sekhar

 

From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Monday, May 23, 2016 1:18 PM
To: Venkatesan, Sekhar
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

The latest driver provided resolves the “SetPos update” error that you guys mentioned. Thanks for the fix.

 

 

That's good news.

 

 

Is the fix provided an official and certified one? Can you also provide the fix for Linux?

 

 

Inoue-san is the only committer now and he will decide that.

 

 

There is one another issue regarding partition’s automatic row movement with regards to partitioning key change. This is not fixed.

Let me know if it is feasible to fix that as well.

 

 

As Inoue-san and I mentioned before, automatic row movement seems to have nothing to do with your problem.  Could you elaborate your test case which fails?

 

Regards

Takayuki Tsunakawa

Hi Inoue/Takayuki,

Just need information on the new fix for child table inheritance. How can I get a new build with this change?

Please help me with an answer.

Thanks,

Sekhar

Hi Inoue,

When can I expect a build for windows and linux? This is required to do complete functional testing on our side.

Will it be updated in https://www.postgresql.org/ftp/odbc/versions/msi/ ?

Thanks,

Sekhar

I just pushed the change about inheritance to git.

regards,
Hiroshi Inoue

Inoue,

 

Can you please let me know if a fix can be provided for the “SetPos Update” issue. I need it for Linux and Windows?

 

Thanks,
Sekhar

 

From: Venkatesan, Sekhar
Sent: Wednesday, May 25, 2016 11:04 AM
To: 'Tsunakawa, Takayuki'
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

Hi Takayuki/ Inoue,

 

When partition is enabled in my application and I change the partitioning key in the table to save the new data into another partition, I get error like this:

 

ERROR: new row for relation "testpart_s" violates check constraint " p1_testpart12_s"

 

I enabled constraint_exclusion in postgresql.conf.

I followed the document you shared to enable row movement across partitions by writing triggers on partition tables. It works up to a point but not confident it will work for all use-cases.

If this can be fixed in the odbc driver itself, it would be better. Let me know your thoughts on this.

 

Thanks,
Sekhar

 

From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Monday, May 23, 2016 1:18 PM
To: Venkatesan, Sekhar
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

The latest driver provided resolves the “SetPos update” error that you guys mentioned. Thanks for the fix.

 

 

That's good news.

 

 

Is the fix provided an official and certified one? Can you also provide the fix for Linux?

 

 

Inoue-san is the only committer now and he will decide that.

 

 

There is one another issue regarding partition’s automatic row movement with regards to partitioning key change. This is not fixed.

Let me know if it is feasible to fix that as well.

 

 

As Inoue-san and I mentioned before, automatic row movement seems to have nothing to do with your problem.  Could you elaborate your test case which fails?

 

Regards

Takayuki Tsunakawa

Hi, Sekhar,

Sorry for my late response.

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar
To work around the problem, I created UPDATE database triggers on partitioned tables that redirects the UPDATEs as INSERT statement on main table (testpart25_s) and the INSERT trigger on main table would push the data to the partitioned table.

This works as expected. But I’m not sure if all use-cases would work with this approach.

Your approach of creating an UPDATE trigger and making it move the row between partitions is correct.  As I mentioned before, the current PostgreSQL doesn't automatically move updated rows to their appropriate partions, which is described here:

https://www.postgresql.org/docs/devel/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS

[Excerpt]

The schemes shown here assume that the partition key column(s) of a row never change, or at least do not change enough to require it to move to another partition. An UPDATE that attempts to do that will fail because of the CHECK constraints. If you need to handle such cases, you can put suitable update triggers on the partition tables, but it makes management of the structure much more complicated.

A slightly different alternative would be to make your UPDATE trigger INSERT directly into the appropriate child table (p4_testpart_s) and DELETE from the old child table (p1_testpart_s), only when the partition changes.  That would be more efficient because INSERT trigger does not have to be executed.  That would complicate the trigger logic, and it's a trade-off.

It would be great if PostgreSQL/ODBC driver itself does this automatic row movement rather than the application handling it. (Other databases like Oracle and SQL Server handle this by itself).

That’s a pain point in the current PostgreSQL, and I really wish it will be solved.  Fortunately, the community is addressing the real partitioning feature, which does not use table inheritance, in the next remajor version (9.7 or 10.0) which will be released in September 2017.

Regards

Takayuki Tsunakawa

Thanks Takayuki for your reply. I got it. Hopefully future versions support this use-case as you mentioned. Keep me posted as and when things happen in this area.

To my other question about fix to resolve child inheritance done by Inoue. I need the fix for windows and linux as part of oDBC driver? Where can I find the new drivers? I understand that Inoue checked-in the fix into GIT but I cannot see any source changes in https://www.postgresql.org/ftp/odbc location?

This fix is urgently needed for our functional testing. Can you please assist expediting this request?

Thanks in advance for all the help in this regard.

-Sekhar

Hi, Sekhar,

Sorry for my late response.

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar
To work around the problem, I created UPDATE database triggers on partitioned tables that redirects the UPDATEs as INSERT statement on main table (testpart25_s) and the INSERT trigger on main table would push the data to the partitioned table.

This works as expected. But I’m not sure if all use-cases would work with this approach.

Your approach of creating an UPDATE trigger and making it move the row between partitions is correct.  As I mentioned before, the current PostgreSQL doesn't automatically move updated rows to their appropriate partions, which is described here:

https://www.postgresql.org/docs/devel/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS

[Excerpt]

The schemes shown here assume that the partition key column(s) of a row never change, or at least do not change enough to require it to move to another partition. An UPDATE that attempts to do that will fail because of the CHECK constraints. If you need to handle such cases, you can put suitable update triggers on the partition tables, but it makes management of the structure much more complicated.

A slightly different alternative would be to make your UPDATE trigger INSERT directly into the appropriate child table (p4_testpart_s) and DELETE from the old child table (p1_testpart_s), only when the partition changes.  That would be more efficient because INSERT trigger does not have to be executed.  That would complicate the trigger logic, and it's a trade-off.

It would be great if PostgreSQL/ODBC driver itself does this automatic row movement rather than the application handling it. (Other databases like Oracle and SQL Server handle this by itself).

That’s a pain point in the current PostgreSQL, and I really wish it will be solved.  Fortunately, the community is addressing the real partitioning feature, which does not use table inheritance, in the next remajor version (9.7 or 10.0) which will be released in September 2017.

Regards

Takayuki Tsunakawa

Inoue, thanks for getting back to me. Much appreciate it.

Any timeline you have in delivering the build?

Thanks,
Sekhar

Hi,

OK we would make a new release.
I have to apply several patches before the release.

regards,
Hiroshi Inoue

Hi Inoue/Takayuki,

 

Just need information on the new fix for child table inheritance. How can I get a new build with this change?

Please help me with an answer.

 

Thanks,

Sekhar

 

From: Venkatesan, Sekhar
Sent: Tuesday, June 07, 2016 9:34 AM
To: 'Inoue, Hiroshi'
Cc: Tsunakawa, Takayuki; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

Hi Inoue,

 

When can I expect a build for windows and linux? This is required to do complete functional testing on our side.

Will it be updated in https://www.postgresql.org/ftp/odbc/versions/msi/ ?

 

Thanks,

Sekhar

 

From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
Sent: Friday, May 27, 2016 4:56 PM
To: Venkatesan, Sekhar
Cc: Tsunakawa, Takayuki; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

I just pushed the change about inheritance to git.

regards,
Hiroshi Inoue

On 2016/05/25 15:02, Venkatesan, Sekhar wrote:

Inoue,

 

Can you please let me know if a fix can be provided for the “SetPos Update” issue. I need it for Linux and Windows?

 

Thanks,
Sekhar

 

From: Venkatesan, Sekhar
Sent: Wednesday, May 25, 2016 11:04 AM
To: 'Tsunakawa, Takayuki'
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

Hi Takayuki/ Inoue,

 

When partition is enabled in my application and I change the partitioning key in the table to save the new data into another partition, I get error like this:

 

ERROR: new row for relation "testpart_s" violates check constraint " p1_testpart12_s"

 

I enabled constraint_exclusion in postgresql.conf.

I followed the document you shared to enable row movement across partitions by writing triggers on partition tables. It works up to a point but not confident it will work for all use-cases.

If this can be fixed in the odbc driver itself, it would be better. Let me know your thoughts on this.

 

Thanks,
Sekhar

 

From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Monday, May 23, 2016 1:18 PM
To: Venkatesan, Sekhar
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

The latest driver provided resolves the “SetPos update” error that you guys mentioned. Thanks for the fix.

 

 

That's good news.

 

 

Is the fix provided an official and certified one? Can you also provide the fix for Linux?

 

 

Inoue-san is the only committer now and he will decide that.

 

 

There is one another issue regarding partition’s automatic row movement with regards to partitioning key change. This is not fixed.

Let me know if it is feasible to fix that as well.

 

 

As Inoue-san and I mentioned before, automatic row movement seems to have nothing to do with your problem.  Could you elaborate your test case which fails?

 

Regards

Takayuki Tsunakawa

Hi Inoue,

Thanks so much for all the help !!!

Let me know the link once it is ready.

Thanks,
Sekhar

Hi Sekhar,

We'd like to package a new release this weekend.

Inoue, thanks for getting back to me. Much appreciate it.

Any timeline you have in delivering the build?

 

Thanks,
Sekhar

 

From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
Sent: Thursday, June 16, 2016 3:37 PM
To: Venkatesan, Sekhar
Cc: Tsunakawa, Takayuki; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

Hi,

OK we would make a new release.
I have to apply several patches before the release.

regards,
Hiroshi Inoue

On 2016/06/14 17:33, Venkatesan, Sekhar wrote:

Hi Inoue/Takayuki,

 

Just need information on the new fix for child table inheritance. How can I get a new build with this change?

Please help me with an answer.

 

Thanks,

Sekhar

 

From: Venkatesan, Sekhar
Sent: Tuesday, June 07, 2016 9:34 AM
To: 'Inoue, Hiroshi'
Cc: Tsunakawa, Takayuki; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

Hi Inoue,

 

When can I expect a build for windows and linux? This is required to do complete functional testing on our side.

Will it be updated in https://www.postgresql.org/ftp/odbc/versions/msi/ ?

 

Thanks,

Sekhar

 

From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
Sent: Friday, May 27, 2016 4:56 PM
To: Venkatesan, Sekhar
Cc: Tsunakawa, Takayuki; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

I just pushed the change about inheritance to git.

regards,
Hiroshi Inoue

On 2016/05/25 15:02, Venkatesan, Sekhar wrote:

Inoue,

 

Can you please let me know if a fix can be provided for the “SetPos Update” issue. I need it for Linux and Windows?

 

Thanks,
Sekhar

 

From: Venkatesan, Sekhar
Sent: Wednesday, May 25, 2016 11:04 AM
To: 'Tsunakawa, Takayuki'
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

Hi Takayuki/ Inoue,

 

When partition is enabled in my application and I change the partitioning key in the table to save the new data into another partition, I get error like this:

 

ERROR: new row for relation "testpart_s" violates check constraint " p1_testpart12_s"

 

I enabled constraint_exclusion in postgresql.conf.

I followed the document you shared to enable row movement across partitions by writing triggers on partition tables. It works up to a point but not confident it will work for all use-cases.

If this can be fixed in the odbc driver itself, it would be better. Let me know your thoughts on this.

 

Thanks,
Sekhar

 

From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Monday, May 23, 2016 1:18 PM
To: Venkatesan, Sekhar
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

The latest driver provided resolves the “SetPos update” error that you guys mentioned. Thanks for the fix.

 

 

That's good news.

 

 

Is the fix provided an official and certified one? Can you also provide the fix for Linux?

 

 

Inoue-san is the only committer now and he will decide that.

 

 

There is one another issue regarding partition’s automatic row movement with regards to partitioning key change. This is not fixed.

Let me know if it is feasible to fix that as well.

 

 

As Inoue-san and I mentioned before, automatic row movement seems to have nothing to do with your problem.  Could you elaborate your test case which fails?

 

Regards

Takayuki Tsunakawa

Hi Inoue,

Let me know if the new release is available with the inheritance fix for both windows and linux?

If yes, what’s the link to download them?

Thanks,
Sekhar

Hi Inoue,

Thanks so much for all the help !!!

Let me know the link once it is ready.

Thanks,
Sekhar

Hi Sekhar,

We'd like to package a new release this weekend.

Inoue, thanks for getting back to me. Much appreciate it.

Any timeline you have in delivering the build?

 

Thanks,
Sekhar

 

From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
Sent: Thursday, June 16, 2016 3:37 PM
To: Venkatesan, Sekhar
Cc: Tsunakawa, Takayuki; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

Hi,

OK we would make a new release.
I have to apply several patches before the release.

regards,
Hiroshi Inoue

On 2016/06/14 17:33, Venkatesan, Sekhar wrote:

Hi Inoue/Takayuki,

 

Just need information on the new fix for child table inheritance. How can I get a new build with this change?

Please help me with an answer.

 

Thanks,

Sekhar

 

From: Venkatesan, Sekhar
Sent: Tuesday, June 07, 2016 9:34 AM
To: 'Inoue, Hiroshi'
Cc: Tsunakawa, Takayuki; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

Hi Inoue,

 

When can I expect a build for windows and linux? This is required to do complete functional testing on our side.

Will it be updated in https://www.postgresql.org/ftp/odbc/versions/msi/ ?

 

Thanks,

Sekhar

 

From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
Sent: Friday, May 27, 2016 4:56 PM
To: Venkatesan, Sekhar
Cc: Tsunakawa, Takayuki; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

I just pushed the change about inheritance to git.

regards,
Hiroshi Inoue

On 2016/05/25 15:02, Venkatesan, Sekhar wrote:

Inoue,

 

Can you please let me know if a fix can be provided for the “SetPos Update” issue. I need it for Linux and Windows?

 

Thanks,
Sekhar

 

From: Venkatesan, Sekhar
Sent: Wednesday, May 25, 2016 11:04 AM
To: 'Tsunakawa, Takayuki'
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

Hi Takayuki/ Inoue,

 

When partition is enabled in my application and I change the partitioning key in the table to save the new data into another partition, I get error like this:

 

ERROR: new row for relation "testpart_s" violates check constraint " p1_testpart12_s"

 

I enabled constraint_exclusion in postgresql.conf.

I followed the document you shared to enable row movement across partitions by writing triggers on partition tables. It works up to a point but not confident it will work for all use-cases.

If this can be fixed in the odbc driver itself, it would be better. Let me know your thoughts on this.

 

Thanks,
Sekhar

 

From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Monday, May 23, 2016 1:18 PM
To: Venkatesan, Sekhar
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

 

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar

The latest driver provided resolves the “SetPos update” error that you guys mentioned. Thanks for the fix.

 

 

That's good news.

 

 

Is the fix provided an official and certified one? Can you also provide the fix for Linux?

 

 

Inoue-san is the only committer now and he will decide that.

 

 

There is one another issue regarding partition’s automatic row movement with regards to partitioning key change. This is not fixed.

Let me know if it is feasible to fix that as well.

 

 

As Inoue-san and I mentioned before, automatic row movement seems to have nothing to do with your problem.  Could you elaborate your test case which fails?

 

Regards

Takayuki Tsunakawa

Hi, Sekhar,

You can get 09.05.0300, which includes the fix, in various forms here:

http://ftp.postgresql.org/pub/odbc/versions/

Just out of curiosity, could you tell me what product you are trying to certify if you don’t mind?  I’d like to add it in the PostgreSQL ecosystem wiki page.

Regards

Takayuki Tsunakawa

Thanks Takayuki. Is this fix also part of the linux package ? I need this fix for linux too.

FYI, We have a product called EMC Documentum Content Server and we are trying to certify PostgreSQL with this product.

Thanks,

Sekhar

Hi, Sekhar,

You can get 09.05.0300, which includes the fix, in various forms here:

http://ftp.postgresql.org/pub/odbc/versions/

Just out of curiosity, could you tell me what product you are trying to certify if you don’t mind?  I’d like to add it in the PostgreSQL ecosystem wiki page.

Regards

Takayuki Tsunakawa

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar
Thanks Takayuki. Is this fix also part of the linux package ? I need this fix for linux too.

Sorry, I’m not perfectly sure, but I don’t think the psqlODBC community itself creates packages for Unix/Linux.  I guess the Ubuntu and Fedora communities find the new release of psqlODBC and packages it.  So you may want to ask them.

FYI, We have a product called EMC Documentum Content Server and we are trying to certify PostgreSQL with this product.

Hi Takayuki,

Can you please assist in finding the right people in the community who can provide the Linux packages.

This issue is critical for us to certify PostgreSQL for Cent-OS flavor of linux.

Thanks,
Sekhar

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar
Thanks Takayuki. Is this fix also part of the linux package ? I need this fix for linux too.

Sorry, I’m not perfectly sure, but I don’t think the psqlODBC community itself creates packages for Unix/Linux.  I guess the Ubuntu and Fedora communities find the new release of psqlODBC and packages it.  So you may want to ask them.

FYI, We have a product called EMC Documentum Content Server and we are trying to certify PostgreSQL with this product.

Hi, Sekhar,

Pavel (praiskup@redhat.com) seems to be the person who creates the psqlodbc package for Fedora and RHEL.  Just yesterday, I exchanged emails with him on this ML about the regression test failure.

As I'm relatively new in this psqlODBC community, I don't know who takes care of other Linux distros.  But you can probably find them by searching "Ubuntu" or "Debian" in the mail archive.  You can search the mail archive here:

https://www.postgresql.org/search/?m=1&q=

Regards

Takayuki Tsunakawa

Hi Takayuki,

As a last note, can you assist me to know if there are folks from PostgreSQL consulting whom we can approach for help with regards to general PostgreSQL performance when compared to other RDBMS like SQL Server/Oracle.

Appreciate for all the help and assistance you guys have provided so far.

Thanks,
Sekhar

Hi, Sekhar,

Pavel (praiskup@redhat.com) seems to be the person who creates the psqlodbc package for Fedora and RHEL.  Just yesterday, I exchanged emails with him on this ML about the regression test failure.

As I'm relatively new in this psqlODBC community, I don't know who takes care of other Linux distros.  But you can probably find them by searching "Ubuntu" or "Debian" in the mail archive.  You can search the mail archive here:

https://www.postgresql.org/search/?m=1&q=

Regards

Takayuki Tsunakawa

Hi, Sekhar,

For commercial support, see:

https://www.postgresql.org/support/professional_support/

For community support, report the problem to pgsql-performance@postgresql.org.  Note that you should describe the problem as concretely as possible, according to the guideline linked from "pgsql-performance" item in the page:

https://www.postgresql.org/list/

Regards

Takayuki Tsunakawa

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar
Sent: Wednesday, June 29, 2016 1:45 PM
To: Tsunakawa, Takayuki/綱川 貴之; Inoue, Hiroshi
Cc: Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

Hi Takayuki,

As a last note, can you assist me to know if there are folks from PostgreSQL consulting whom we can approach for help with regards to general PostgreSQL performance when compared to other RDBMS like SQL Server/Oracle.

Appreciate for all the help and assistance you guys have provided so far.

Thanks,
Sekhar

Hi Takayuki,

Does the odbc driver release below certified with 9.4 postgres server version? I assume the driver has backward compatibility with lower server versions.

Or I have to use 9.5 server?

Thanks,

Sekhar

Hi, Sekhar,

You can get 09.05.0300, which includes the fix, in various forms here:

http://ftp.postgresql.org/pub/odbc/versions/

Just out of curiosity, could you tell me what product you are trying to certify if you don’t mind?  I’d like to add it in the PostgreSQL ecosystem wiki page.

Regards

Takayuki Tsunakawa

Hi, Sekhar,

I don’t kow what testing is done, but It should work.

Regards

Takayuki Tsunakawa