PostgreSQL patch : 外部表支持 generated columns

作者

digoal

日期

2021-08-06

标签

PostgreSQL , generated columns , 外部表

以前的版本, 如果远端表包含generated column, 使用ft进行写入或更新时会报错, 因为有覆盖generated column操作.

ERROR: cannot insert a non-DEFAULT  
value into column "foo"" when inserting into the foreign tables  
复制

这个patch解决了这个问题, 在操作generated column时, 传送default, 不会产生generated覆盖.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=aa769f80ed80b7adfbdea9a6bc267ba4aeb80fd7

postgres_fdw: Fix issues with generated columns in foreign tables.  
author  Etsuro Fujita <efujita@postgresql.org>    
Thu, 5 Aug 2021 11:00:00 +0000 (20:00 +0900)  
committer   Etsuro Fujita <efujita@postgresql.org>    
Thu, 5 Aug 2021 11:00:00 +0000 (20:00 +0900)  
commit  aa769f80ed80b7adfbdea9a6bc267ba4aeb80fd7  
tree    dbf6a90f94426c203f47ab46ed07abd18885e871    tree  
parent  93d573d86571d148e2d14415166ec6981d34ea9d    commit | diff  
postgres_fdw: Fix issues with generated columns in foreign tables.  
postgres_fdw imported generated columns from the remote tables as plain  
columns, and caused failures like "ERROR: cannot insert a non-DEFAULT  
value into column "foo"" when inserting into the foreign tables, as it  
tried to insert values into the generated columns.  To fix, we do the  
following under the assumption that generated columns in a postgres_fdw  
foreign table are defined so that they represent generated columns in  
the underlying remote table:  
* Send DEFAULT for the generated columns to the foreign server on insert  
  or update, not generated column values computed on the local server.  
* Add to postgresImportForeignSchema() an option "import_generated" to  
  include column generated expressions in the definitions of foreign  
  tables imported from a foreign server.  The option is true by default.  
The assumption seems reasonable, because that would make a query of the  
postgres_fdw foreign table return values for the generated columns that  
are consistent with the generated expression.  
While here, fix another issue in postgresImportForeignSchema(): it tried  
to include column generated expressions as column default expressions in  
the foreign table definitions when the import_default option was enabled.  
Per bug #16631 from Daniel Cherniy.  Back-patch to v12 where generated  
columns were added.  
Discussion: https://postgr.es/m/16631-e929fe9db0ffc7cf%40postgresql.org  
复制

+    <varlistentry>  
+     <term><literal>import_generated</literal> (<type>boolean</type>)</term>  
+     <listitem>  
+      <para>  
+       This option controls whether column <literal>GENERATED</literal> expressions  
+       are included in the definitions of foreign tables imported  
+       from a foreign server. The default is <literal>true</literal>.  
+       The <command>IMPORT</command> will fail altogether if an imported generated  
+       expression uses a function or operator that does not exist locally.  
+      </para>  
+     </listitem>  
+    </varlistentry>  
复制

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.