技术控

    今日:0| 主题:49390
收藏本版 (1)
最新软件应用技术尽在掌握

[其他] What’s new in PostgreSQL 9.5

[复制链接]
空心印末夕情 发表于 2016-10-4 14:25:35
32 0

立即注册CoLaBug.com会员,免费获得投稿人的专业资料,享用更多功能,玩转个人品牌!

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
Fedora 24 ships with    PostgreSQL 9.5, a major upgrade from version 9.4 that is included in Fedora 23. The new version 9.5 provides several enhancements and new features, but also brings some compatibility changes, as it has been very common between PostgreSQL major versions. Note that in the PostgreSQL versioning scheme, 9.4 and 9.5 are two major versions, while the first number is mostly marketing and increments when major features are introduced in the release.  
  New features and    enhancementsin 9.5  

  GROUPING SETS

  PostgreSQL has been traditionally OLTP, rather than OLAP, but this may change in the future; small steps like    GROUPING SETShelp on this path, since the GROUPING SETS allow to use more complex aggregation operations (grouping). CUBE and ROLEUP are then just specific variants of GROUPING SETS.  
  ON CONFLICT

  Users may also be very happy about    ON CONFLICTenhancement, that allows to do something sane in case the current statement would generate a conflict. That is quite general approach with two possible solutions — we can either turn the INSERT statement to UPDATE or ignore the statement at all. This feature is often called UPSERT, and in other DBMS we may know something very similar as MERGE command. However, it is not 100% MERGE implementation of the SQL standard in case of PostgreSQL, so it is not called like that. UPSERT implementation in PostgreSQL should also be more safe because CTE (common table expressions) might lead to race condition if not used properly. An example of inserting new tags into database and ignoring duplicate records may look like this:  
  1. INSERT INTO tags (tag) VALUES ('PostgreSQL'),('Database') ON CONFLICT DO NOTHING;
复制代码
Row-level security control

  Another feature that may substantially simplify SQL queries, is    row-level security control, that allowing check access on particular rows. For example, if every row includes information about who is owner of the record, we would need to check in the application that the currently logged user equals the owner column. With row-level security feature we may leave this to the DBMS and thus we can not only keep application logic clear, but we can also be a bit more sure that potential attacker would not get around, because it’s checked on one layer further. An example how to use the row-level security control may look like this:  
  1. CREATE POLICY policy_article_user ON articles
  2. FOR ALL TO PUBLIC
  3. USING (user = current_user);
  4. ALTER TABLE articles ENABLE ROW LEVEL SECURITY;
  5. SELECT * FROM articles;
  6. id | user | title
  7. ---+------+-------------------
  8.   1 | joe  | How I went to Rome
  9.   4 | joe  | My Favorite Recipe
  10. (2 rows)
复制代码
With this, currently logged user can only see items that were created by the user that is logged in.
  Other improvements

  Have you ever tried connecting two database servers into one instance, so application does not need to care about connections to more servers separately? This was already possible using CREATE FOREIGN TABLE, but one needed to re-define every table with every single column. And of course change it again, once structure of the foreign table changed.
  From version 9.5 we can import whole schema as easy as this, so not only we have simpler and less error-prone way to connect two remote databases, but it can be very handy also for data migration:
  1. IMPORT FOREIGN SCHEMA invoices
  2. LIMIT TO (customers, customers_invoices)
  3. FROM SERVER invoice.example.com INTO remote_invoices;
复制代码
What else we find in 9.5? Of course there are several performance enhancements, but that is almost a must for every release, right? What might not be that common for every release though is a brand new index type —    BRIN (Block Range Index). According to the documentation, it is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table. In such cases the bitmap index scans may be used and performance of especially analytical queries might be substantially better.  
      Upgrading from PostgreSQL 9.4      

  Note that upgrading from PostgreSQL 9.4 is not automatic. If you have already used PostgreSQL in some previous version, you need to proceed with upgrade. Upgrade procedure, as it is common in case of PostgreSQL, is not an automatic procedure and admins are required to proceed with the steps manually. Fedora helps here a lot by providing    postgresql-setupbinary that accepts either    –initdb(for initializing the datadir) or    –upgradearguments and helps proceeding with the whole procedure almost automatically.  
  Warning: Do not forget to back-up all your data before proceeding with the upgrade.

  After system upgrade (F23 to F24 in this case), you will probably see something like this after trying to run PostgreSQL server:
  1. $ sudo systemctl start postgresql.service
  2. Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.
复制代码
That’s because server knows about version of the datadir and refuses to start to not break anything. So let’s proceed with upgrade — install the    upgradesubpackage first:  
  1. $ sudo dnf install postgresql-upgrade
复制代码
Then run upgrade itself:
  1. $ sudo postgresql-setup --upgrade
  2. * Upgrading database.
  3. * Upgraded OK.
  4. WARNING: The configuration files were replaced by default configuration.
  5. WARNING: The previous configuration and data are stored in folder
  6. WARNING: /var/lib/pgsql/data-old.
  7. * See /var/lib/pgsql/upgrade_postgresql.log for details.
复制代码
At this point you should really look at the log file as suggested, but you should also be able to start the service now:
  1. $ sudo systemctl start postgresql.service
  2. $ sudo systemctl status postgresql.service
复制代码
And that’s all. Easy, right? As always, any feedback is welcome.
友荐云推荐




上一篇:Inspired by a HN comment, four half-star characters accepted by Unicode
下一篇:A Look at the Prototype Design Pattern
酷辣虫提示酷辣虫禁止发表任何与中华人民共和国法律有抵触的内容!所有内容由用户发布,并不代表酷辣虫的观点,酷辣虫无法对用户发布内容真实性提供任何的保证,请自行验证并承担风险与后果。如您有版权、违规等问题,请通过"联系我们"或"违规举报"告知我们处理。

*滑动验证:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

我要投稿

推荐阅读

扫码访问 @iTTTTT瑞翔 的微博
回页顶回复上一篇下一篇回列表手机版
手机版/CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 )|网站地图 酷辣虫

© 2001-2016 Comsenz Inc. Design: Dean. DiscuzFans.

返回顶部 返回列表