digoal PostgreSQL 9.3 New Features Extract

《digoal PostgreSQL 9.3 New Features Extract》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。

1、PostgreSQL9.3NewFeaturesExtractDigoal.zhouSunday,October20,20132013PostgreSQLChinaConferenceSKYMOBI,Hangzhou,ZhejiangNewFeatureChangemulticolumnONUPDATESETNULL/SETDEFAULTforeignkeyactionstoaffectallcolumnsoftheconstraint,notjustthosechangedintheUPDATE(TomLane)Prev

2、iously,wewouldsetonlythosereferencingcolumnsthatcorrespondtoreferencedcolumnsthatwerechangedbytheUPDATE.ThiswaswhatwasrequiredbySQL-92,butmorerecenteditionsoftheSQLstandardspecifythenewbehavior.http://blog.163.com/digoal@126/blog/static/16387704020134625336626/psq

3、l(9.2.4)digoal=#createtableb(c1int,c2int,c3text,primarykey(c1,c2));digoal=#createtablea(c1int,c2int,c3text,foreignkey(c1,c2)referencesb(c1,c2)onupdatesetnull);digoal=#insertintobvalues(1,2,3);digoal=#insertintoavalues(1,2,3);digoal=#updatebsetc1=2;--foreign表只对

4、c1列也就是updated的列setnulldigoal=#select*froma;c1

5、c2

6、c3

7、2

8、3digoal=#select*fromb;c1

9、c2

10、c32

11、2

12、3NewFeatureChangemulticolumnONUPDATESETNULL/SETDEFAULTforeignkeyactionstoaffectallcolumnsoftheconstraint,notjustthosechangedintheUPDATE(TomLane)Previously,wewouldsetonlyt

13、hosereferencingcolumnsthatcorrespondtoreferencedcolumnsthatwerechangedbytheUPDATE.ThiswaswhatwasrequiredbySQL-92,butmorerecenteditionsoftheSQLstandardspecifythenewbehavior.http://blog.163.com/digoal@126/blog/static/16387704020134625336626/psql(9.3devel)digoal=#cr

14、eatetableb(c1int,c2int,c3text,primarykey(c1,c2));digoal=#createtablea(c1int,c2int,c3text,foreignkey(c1,c2)referencesb(c1,c2)onupdatesetnull);digoal=#insertintobvalues(1,2,3);digoal=#insertintoavalues(1,2,3);digoal=#updatebsetc1=2;--foreign表对整个foreignkeysc1,c2列都

15、setnulldigoal=#select*froma;c1

16、c2

17、c3

18、

19、3digoal=#select*fromb;c1

20、c2

21、c32

22、2

23、3NewFeatureForcecachedplanstobereplannedifthesearch_pathchanges(TomLane)Previously,cachedplansalreadygeneratedinthecurrentsessionwerenotredoneifthequerywasre-executedwithanewsearch_paths

24、etting,resultinginsurprisingbehavior.http://blog.163.com/digoal@126/blog/static/16387704020139228113596/PostgreSQL9.2postgres=#createsc