2007-02-28

PostgreSQL 限制約束::參照外部鍵(Foreign Key)

更新:2007-02-28
對映章節:II,5

內容:
資料類型是約束我們可以在表裡存儲何種類型資料的一種方法。 不過,對於許多應用,它們提供的約束實在是太粗糙。
外部鍵約束聲明一個欄位(或者一組欄位)的數值必須匹配另外一個表中某些行出現的數值。 我們把這個行為稱做兩個關聯表之間的參照完整性(referential integrity)

在 PostgreSQL 裡可以透過觀查 information_schema 的視圖(views) "referential_constraints", 來了解目前使用中的參照限制的詳細資訊.
也可以使用GUI介面, 畫面如下...

phpPgAdmin-4.x:



pgAdmin-III 1.6.2:


語法:

ADD COLUMN product_no integer REFERENCES products (product_no),

ADD COLUMN product_no integer REFERENCES products,

上面二句均等同系統執行下列完整SQL:
ADD CONSTRAINT [TableName]_product_no_fkey FOREIGN KEY (product_no)
REFERENCES
products (product_no) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION;

並自動產生以"fki_[TableName]_product_no_fkey"命名的外部鍵索引.


===================================================
FOREIGN KEY (b, c) REFERENCES other_table (c1, c2),


PostgreSQL TODO List 重點整理

修正日期:2007-02-28
(這篇文我將持續保持修正內容)

PostgreSQL TODO List
上百名的開發團隊, 會在每週公告上週對 PostgreSQL 下個版本與各次子版本所做的一切包括"新功能", "程式碼再優化", "效能的改進", 與安全性修補...等等, 一條條的列詳細來告訴您, "我們"上週做了什麼....平均一週能進行進百項的大大小小的事項.

更會在 TODO List 網頁上公告說明 PostgreSQL 對下個版本功能的增加, 原功能的調校與改進, 每一次的版本提升, 至少都累積了幾百項大小不等的原始碼的演進, 身為使用者一員的我, 也不得不建議您將您的現有版本提升, 因為它來自上百名志願開發團隊者的心血與調校, 品質已非一般商業型DBMS可擬比!

我們在做的是....下個目標版本: 8.3.0

管理

  • 改進複寫叢集解決方案: 主從式負載平衡 pgcluster.
  • 增加與改進組態檔設定內容: postgresql.conf, pg_hba.conf
  • Point-In-Time Recovery (PITR) 追加功能.
資料類型
  • 增加 64 位元版本"MONEY"資料類型.
  • 增加 Globally/Universally Unique Identifier (GUID/UUID)
  • 支持一個特別的資料類型 enumerated values (ENUM)
  • 增進對 XML 的支持
多國語言的支持
  • 增加 NCHAR( )
  • CREATE COLLATE
SQL 命令
  • 增加 SQL:2003 WITH RECURSIVE (hierarchical) queries to SELECT
  • GRANT/REVOKE 增加 欄位層級特權.
VACCUM
  • 改進速度與索引.
  • 縮小 lock 時間在 VACCUM FULL 期搬遷 tuples 及"讀取鎖住"時, 其次"寫入鎖住"和截斷表.
  • 改進 AUTO-VACCUM 控制

中文分詞

完整來源
http://www.postgresql.org/docs/faqs.TODO.html

PostgreSQL 擁有對 ISO/IEC SQL:2003 最多項的標準相容順從

更新:2007-02-28
對映章節:VIII,D

內容:
SQL 標準的正式名稱是 ISO/IEC 9705 "Database Language SQL"
最近的一個版本是在 2003 年稱作 ISO/IEC 9075:2003,或者簡稱為 SQL:2003。這個版本之前的是 SQL:1999SQL-92
每個標準都代替了前面那個,所以聲稱相容早期版本沒有什麼官方的好處。 PostgreSQL 的開發力圖瞄準相容標準最新的正式版本,只要這樣的相容不會和傳統的特性或者常識衝突。 許多 SQL:2003 要求的特性都已經得到了支援,只是有些時候函數的語法略有不同。 更多有關標準相容的特性將在未來的版本裡看到。

註:在 8.3 版將加入完整對 ISO/IEC 9075-14 XML-related specifications/XML 相關的規範 (SQL/XML) 的支持能力。

PostgreSQL 支援大多數 SQL:2003 的主要特性。
在總共 164 個強制性要求完全相容的核心特性裡, PostgreSQL 遵循至少 150 個。
另外,PostgreSQL 還支援一長串可選的特性。
值得一提的是,在我們寫這些的時候,沒有任何目前版本的資料庫管理系統聲稱支援全部核心 SQL:2003。

在下面的連結, PostgreSQL 完整的透明化公開說明了當前版本 PostgreSQL 對 SQL 的相容與不順從的整理表:
Features-sql-standard
Unsupported-features-sql-standard

PostgreSQL 存取訪問配置安全性(一)概論

更新:2007-02-28
對映章節:

內容:
PostgreSQL 存取訪問配置組態(一)概論

與 PostgreSQL 有關的主要設定配置文件:

  1. postgresql.conf : 系統啟用時主配置文件
  2. pg_hba.conf : 存取訪問.認證控制安全性文件
  3. pg_ident.conf : 非 sameuser 的身份映射(maping)定義在身份映射文件
配置文件只有在重啟系統時才重新裝載, 而不是每次連線就重新裝載.

postgresql.conf / pg_settings
在系統開始運作時載入, 可以籍由 System View (pg_settings) 這張表來觀看系統執行中的配置, 且可經由 UPDATE 來改更其值, 效果等同 SET.
listen_addresses = '(string)' 這是個很重要的選項, 如果您的 Server 要能透過TCP/IP進行的話, 請設定它. 預設只監聽 localhost.

2007-02-27

PostgreSQL 實用的數學函數與運算子

更新:2007-02-27
對映章節:II,9.3

今天來介紹幾個實用的
數學函數(Mathematical Functions)與運算子(Mathematical Operators), 詳細的還是請看官方文檔.

準確度和數值範圍方面的行為是根據宿主系統而變化的。

運算子詳述
例子結果
% 模除 (求余)5 % 41
^ 冪(指數運算)2.0 ^ 3.08
! 階乘5 !120
@ 絕對值@ -5.05
& 二進制 AND91 & 1511

函數返回類型描述例子結果
abs(x)(和x類型相同)絕對值abs(-17.4)17.4
ceil(dp 或者 numeric)與輸入相同不小於參數的最小的整數ceil(-42.8)-42
ceiling(dp or numeric)(與輸入相同)不小於參數的最小整數(ceil 的別名)ceiling(-95.3)-95
floor(dpnumeric)與輸入相同不大於參數的最大整數floor(-42.8)-43
mod(y, x)(和參數類型相同)除法 y/x 的餘數(模)mod(9,4)1
random()dp0.0 到 1.0 之間的隨機數值random()
round(dp 或者 numeric)(與輸入相同)圓整為最接近的整數round(42.4)42
round(v numeric, s integer)numeric圓整為s位小數數字round(42.4382, 2)42.44
setseed(dp)integer為隨後的 random() 調用設置種子setseed(0.54823)1177314959
trunc(dp 或者 numeric)(和輸入相同)截斷(向零靠近)(truncate)trunc(42.8)42
trunc(v numeric, s integer)numeric截斷為 s 小數位置的數字trunc(42.4382, 2)42.43

PostgreSQL 預設的進程(Process)說明

PostgreSQL 用進程來 listen 一般情況下(Debian)
會有5個以上 Daemon 保持著

Linux 下:


Win32:


  1. -D 宣告資料庫的路徑, 並守護著.
  2. writer: 寫入器進程
  3. stats buffer: 狀態緩衝器進程
  4. stats collector: 狀態收集器進程
  5. idle ~ N:Client 使用中的進程, 每多一個Client+1.

2007-02-26

PostgreSQL 8.1 on GNU/Debian - 預設說明整理

修訂:2007-02-27

在 Debian 上直接使用 dpkg 安裝的話~
以 8.1 為例

initdb 初始化DB的位置
/var/lib/postgresql/8.1/main

Runing...
/usr/lib/postgresql/8.1/bin/postmaster -D \
/var/lib/postgresql/8.1/main -c \ config_file=/etc/postgresql/8.1/main/postgresql.conf


conf:
/etc/postgresql/8.1/main/*
environment (for postgres starting)
pg_hab.conf
pg_ident.conf
postgresql.conf
start.conf (for pgcluster relation)

log:
log -> /var/log/postgresql/postgresql-8.1-main.log

PostgreSQL Home目錄
/var/lib/postgresql

PostgreSQL 預設 bin, lib 安裝的位置在
(預設權限全給了 root)
/usr/lib/postgresql/8.1/*

指令包括:
postmaster -> postgres (link)
postgres
initdb :: 初始化DB用
psql :: Client 命令終端器
pg_ctl
pg_dump/pg_dumpall/pg_restore

createuser/dropuser
createdb/dropdb
createlang/droplang

reindexdb
vacuumdb

*ipcclean
*clusterdb
*pg_controldata
*vacuumlo

PostgreSQL on Windows :入門

更新:2007-03-03

這是一篇很棒的 MS 上安裝的圖文安裝解說的 PDF

http://port25.technet.com/videos/research/postgresql on windows_final.pdf

讓初步想了解 PostgreSQL 的朋友有個概括的安裝手冊

在 Win32下僅適合開發與練習測試用
若是要實際作業仍建議使用在 Unix-like 的環境下效能才會好.
理由是 WIN32 環境很多屬於 MS 公司非 FREE 授權,
導致很多 GNU 開發過程必須做必要的"繞徑"手段, 卻也導致在 WIN32 效能低落的主因.

該手冊從下載到安裝和基本的組態檔設定, 也包括了 pgAdmin 的基本使用教學.
手冊是"英文"版的哦!!!

(不過您安裝好後的 pgAdmin 可是有完全 100% 的正體/簡體中文語系可使用的~)


PostgreSQL 報表設計工具 - OpenRPT

OpenRPT
是支持各種平台 PostgreSQL 最佳化的
圖形化GUI SQL 報表撰寫者, 設計者與解析引擎.

(有Win32版哦!! 更支援條碼bar code製作哦!!!)

http://pgfoundry.org/projects/openrpt/



OpenRPT 特色

  • Report Definitions are saved in the industry standard XML format
  • Stand-alone or Embeddable WYSIWYG Report Designer
  • Embeddable Report Renderer renders to local printers, including PDF and Postscript distillers
  • Support for All/Even/Odd/First/Last Page Headers and Footers
  • Support for Multiple Column Detail Sections
  • Support for static and database sourced images
  • Support for static and/or database sourced watermarks and page identifiers
  • Support for multiple detail sections and optional, multiple group heads and footers for each detail section
Formats Supported
  • Bar Code Formats
    • Code 3of9
    • Code 3of9 Extended
    • Code 128
    • UPC-A
    • UPC-E
    • EAN-8
    • EAN-13
  • Support for Avery Standard Labels
  • Support for portrait or landscape, Letter, Legal and A4 documents

Platforms Supported

  • Operating Systems:
    • Windows 98/ME/NT/2K/XP
    • Linux
    • Mac OS X
    • xBSD
    • Solaris
    • AIX
    • HPUX
  • Databases (right now):
    • PostgreSQL
  • With a little work:
    • DB2
    • Oracle
    • SQL Server
    • MySQL
    • Generic ODBC connections



PostgreSQL 社群專屬搜尋引擎與 Firefox Search Plugin

完整的開放, 也豪不保留的回饋~
這就是社群軟體因參與開發過程人數不斷上升
加速超越商業軟體品質的主因.

您現在能在 Firefox 的搜尋列加上結合
search.postgresql.org 的資源搜尋功能
http://www.gunduz.org/postgresql/searchpostgresqlorg.html



另外由
http://www.pgsql.ru/db/pgsearch/
製作的完整搜尋器能針對 PostgreSQL 的各式文件做有效的搜尋
另外該社群也製作了針對 Mail List 的搜尋列
http://www.pgsql.ru/db/mw/

PostgreSQL - pgpool 子專案的簡介

更新:2007-02-28

pgpool 專案

"pgpool-II 的" 下一代來臨! 它准許平行的詢問、超過 2 臺伺服器, GUI admin 工具和更多。



PostgreSQL 專用的所謂「連接池(pool)」伺服器, PostgreSQL 客戶端 和 PostgreSQL 伺服器之間做兩者的媒介.根據利用 pgpool 做的事務, 能降低對 PostgreSQL 伺服器的連接 overhead,能使之提高系統全體的負載量.

PostgreSQL 的客戶端連接到 pgpool 的話, 當第一回 PostgreSQL 的連接被 pgpool 確立.
對有客戶端結束也對 PostreSQL 的連接不切斷留下, 下次同樣的用戶, 資料庫對 pgpool 的連接的時候這個 PostgreSQL 的連接會被再利用.

再者, 因為pgpool有「Hot-Backup」的機能, 能按住到最小限度系統的 down time.

另外 pgpool「replication」也可行的.根據使用replication的事, 物理地對 2 台軟體能實時地取得硬體的接應, 當軟體障礙發生也繼續運用為一邊.
(如果第一伺服器下來, pgpool 自動地將交換對次要伺服器。)

pgpool 的 replication 因為根據 2 台伺服器發送同樣的查詢的事進行, 所謂同期 replication 的就是一種.

再者,現在,replication以只2台,3台以上的replication不能.

pgpool 有著分配檢索諮詢(SELECT)為2台伺服器的負荷分散機能.
根據這個能大幅度提高檢索性能.



是首先的基準結果.一次近本源的PostgreSQL的 2 倍的性能超過的查詢.測量條件是以下.

  • P4 2.4GHz x2/1GB RAM/80GB IDE 7200rpm
  • Red Hat 9/PostgreSQL 7.4.3(shared_buffers=2048)
  • pgbench -S -c 1...128 -t 10000

PostgreSQL - 當前主要特點(簡報用)

修訂:2007-03-05

PostgreSQL features

主體:

  • Transactions
  • Subselects
  • Views
  • Foreign key referential integrity
  • Sophisticated locking
  • User-defined types
  • Multi Server-side Languages support (PL/pgSQL, Java, Perl, Ruby, C/C++, ....)
  • GIS data types <===特有的地理性資料類型庫
  • Inheritance <== 被稱為"物件"關聯資料庫系統(O-RDBMS)的主因
  • Rules
  • MVCC(Multiple-version concurrency control)
  • WAL(Write-Ahead Log)
  • Native Microsoft Windows version
  • Table spaces
  • Ability to alter column types
  • PITR(Point-in-time recovery)
  • XML (8.3 Dev)
  • Native GUI Admin Tool (support Slony-1)
8.2 Add
  • Improved Warm standby databases
  • Online index builds
  • SQL2003 aggregates
  • Improved TSearch2 with Generalized Inverted Indexes(GIN)
  • Support for DTrace probes
  • Advisory Locks
  • New ISN/ISBN and pgCrypto modules
  • Selective pg_dump options
HA(High-Availability)
  • Higher performance (+20% on OLTP tests)
  • Slony-1 複寫叢集系統
  • pgmemcache 記憶體快取機制
  • pgCluster 同步多主點叢集系統
  • pgpool-II 連結池實作, Fail-Over(熱備援), 複寫機制叢集

2007-02-25

PostgreSQL 子專案與相關站點(彙報篇)

(持續整理中...)

核心

技術性文件

應用

Tweaker.net - PostgreSQL 與 MySQL 效能報告

這是 Tweaker.net 對二個開源碼的資料庫系統做的
效能測試報告:
PostgreSQL 8.2
MySQL 5.0

(點圖放大)






詳細的測試報告:
http://tweakers.net/reviews/657/6

Drupal 群組團隊 - PostgreSQL

Drupal
是個非常好的內容管理系統
現在也加入了新的群組 - PostgreSQL



http://groups.drupal.org/postgresql

正體中文網站: http://drupaltaiwan.org/
簡體中文網站: http://www.drupalchina.org/

2007-02-24

FOSDEM 2007 大會 - PostgreSQL (現場照片)

更新:2007-03-05

FOSDEM 2007

FOSDEM 是歐洲的一個非商業的社群組織, 主要以開放源碼為主的活動組織.
2007-02-24~25
在比利時(歐洲國家)的Brussels(布魯塞爾)城, 舉行二日的開放源碼的開發者聚會,
一些重要的組織都出現在這 Google, Debian, Firefox....

以下是些現場的照片集:(點圖可放大)

PostgreSQL 的攤位






Google Inc.




GNU Debian Linux


Firefox~


OpenOffice.org


大象馬克杯@@""




http://ads.wars-nicht.de/pictures/v/events/fosdem_2007/

PostgreSQL 同步多重主點複寫(Multimaster replication)叢集技術



2007-02-23 由奧地利的成立於2000年的
"Cybertec Geschwinde & Schoenig GmbH" 這家公司發表了成果 -

PostgreSQL 同步多重主點複寫(Multi-Master Replication)叢集技術

同時亦能在 Linux 和 Windows 上運行.
運行可高達 128 個叢集節點和各種平台上.

技術含蓋"負載平衡", "叢集化資料庫", "複寫"..



這家公司更提供 24*7 的不間斷服務與除錯服務.
該公司是自 2000年至今第一個在歐洲最主要銷售 PostgreSQL 的公司.

High-Performance OLAP cubes


2007-02-23

EnterpriseDB - 從 MySQL 遷移到 EnterpriseDB


EnterpriseDB 是多數將 PostgreSQL 商業化並提供技術服務的國際公司之一.
這篇是在這家公司網站上強調對 MySQL 客戶遷移到以 PostgreSQL 商品化的產品上.

原文在這:
http://www.enterprisedb.com/solutions/gotmysql.do



運行可靠, 擴展與升級, 重要(mission-critical)的應用- 在價格上可與 MySQL 比較

MySQL 用戶直接知道, MySQL 是最有效的為 read-only 環境和 web/edge 層級 - 但不是為應用在要求 OLTP 環境, 要求企業類別 -可靠性、有效性, 和規劃可伸縮性。

實際上, 一些 EnterpriseDB 客戶繼續跑 MySQL 資料庫為較少 demanding 應用並且轉向我們他們有 "hit a wall" 與 MySQL 。

用被證明的企業特點和可比較的執照費用, EnterpriseDB 先進的伺服器 支持這些組織的mission-critical 應用和, 在某些情況下, 替換MySQL 在企業過程中。

結束商業終止在成本和效能之間

EnterpriseDB 先進的伺服器 根據 PostgreSQL, 世界的最先進的開放來源資料庫。PostgreSQL 被開發了和連續被改進了過去 20 年, 和由最大和受尊敬的開放來源社區的當中一個維護在世界上。

EnterpriseDB 提高PostgreSQL 以 24x7 用戶支持 - 以及表現改進貢獻回到社區。生產力 和表現工具隨員 更加進一步保證資料完整性和高效率的表現為 OLTP 應用 demanding 需要。

不 同於MySQL, EnterpriseDB 先進的伺服器被設計從開始是企業類級的 OLTP RDBMS 。能力譬如觸發器, 被存放的規程, 支持次級選擇, 並且當地 PL/SQL 支持是在 EnterpriseDB 從一開始。認為許多其它企業類特點可利用在我們的資料庫。

企業級優秀特點

豐富的功能性

  • 真實的線上備份及時間點復原(PITR)
  • 健全的 Oracle 遷移與資料轉換工具
  • 能力使用資料表, 表空間和複雜分成, 包括資料和索引

儲存的安全保障

  • PITR
  • 認證通過 SSL 或 Kerberos
  • 更多顆粒狀的認證
  • 固定密碼學以 256 位元編成密碼
  • 唯一的資料庫的當中一個充分地是 PCI 服從在資料庫之內

被證實的可靠性

  • EnterpriseDB 、PostgreSQL 和 Oracle 真實的複寫
  • 日誌運行為警告的可靠者
  • PITR 以線上備份與復原
  • MVCC

證明

  • Vonage 的總統宣稱他喜歡 EnterpriseDB 因為它提供許多 Oracle 的力量, 包括兼容性以Oracle 應用, 但 以開放源碼 MySQL 的價格 。"Oracle 是大錘。MySQL 是一把小錘子。我們之間需要某事, "他說。
    -- Computerworld
  • EnterpriseDB 的瑕疵密度 比 MySQL 好由數量級 和更好比平均商業代碼由二個數量級, 根據 Coverity
  • "EnterpriseDB 的產品 品質就像 Oracle 的, 造成幾乎沒有停工期為再培訓資料庫管理員。"
    -- BusinessWeek

EnterpriseDB - 從 Oracle 遷移到 EnterpriseDB


EnterpriseDB 是多數將 PostgreSQL 商業化並提供技術服務的國際公司之一.
這篇是在這家公司網站上強調對 Oracle 客戶遷移到以 PostgreSQL 商品化的產品上.

原文在這:
http://www.enterprisedb.com/solutions/gotoracle.do

粗略翻譯如下文:



在 Oracle 的應用程序不需重構的在 EnterpriseDB 上運行 - 大約 80%


高額 Oracle 資料庫花費虹吸著您的資訊預算, 富挑戰性董事維護贏利仍然仍然資助創新為成長。

EnterpriseDB 為 Oracle 提供企業類選擇在費用的分數。我們的方法提供永久邊際改善, 授權您追求, 在一個高價資料庫環境裡, 簡單地不是可行的主動性。
儲款達到沒有衝擊對資料庫功能、可靠性、安全, 或表現。另外, 許多先進的特點, 包括資料加密, 是標準的與EnterpriseDB, 但是只可利用的在其它資料庫通過昂貴的添加模塊。

做開關由 Oracle 要求對現有的應用的一點點或沒有修改。使用我們的遷移工具箱, 過程經常被完成在分鐘。實際上, 我們的客戶報告, 超過 75% 他們的應用不是 100% 兼容, 要求對奔跑的變動在EnterpriseDB 。

一些EnterpriseDB 客戶選擇整個地替換 Oracle; 其他人使用我們的資料庫作為一個更加高效率的選擇為具體應用和報告。

開放來源儲款。企業類結果。

EnterpriseDB 先進的伺服器 根據 PostgreSQL, 世界的最先進的開放來源資料庫。PostgreSQL 被開發了和連續被改進了過去 20 年, 和由最大和受尊敬的開放來源社區的當中一個維護在世界上。

EnterpriseDB 以及表現改進 提高 PostgreSQL 以 24x7 用戶支持 貢獻回到社群。並且被提供生產力 和表現工具隨員, 包括我們的 遷移Toolset 容易地移動您的應用和資料。

直覺的工具箱容易地出口所有資料庫圖解資訊和程式邏輯- 並且資料- 從 Oracle 對 EnterpriseDB, 消滅對手工資料遷移的需要。

證明

  • 從 Oracle 在轉換只一些以後他們的應用, SONY Online 上娛樂年年 項目他們開發 所有新應用在EnterpriseDB 和繼續行動Oracle 的$1M 儲款- 一個原因。
  • "EnterpriseDB 的能力跑應用被寫為Oracle 是一個關鍵系數... EnterpriseDB 是非常有吸引力的被給我們的可靠性和可測量性要求。"
    -- Tim 史密斯, 網路總統, Vonage
  • "EnterpriseDB 的產品 品質就像 Oracle 的, 造成幾乎沒有停工期為再培訓資料庫管理員。"
    -- BusinessWeek

PostgreSQL 索引(Indexs)概要

對映章節: II,C11

索引是提高資料庫性能的常用方法。
索引可以令資料庫伺服器以比沒有索引更快的速度查找和檢索特定的行。
不過索引也在總體上增加了資料庫系統的負載, 因此我們應該權衡地使用它們。

一旦您建立了索引,那麼就不在需要更多的干涉了︰ 當系統認為用索引資料表順序的資料表掃瞄快的時候它就會使用索引。 不過您可能必須經常性地執行 ANALYZE 命令以更新統計訊息,好讓查詢規劃器能夠做出有訓練的判斷。

PostgreSQL 提供了好幾種索引類型︰ B-tree,R-tree,Hash 和 GiST。每種索引類型都比較適合某些特定的查詢類型, 因為它們用了不同的算法。 預設時,CREATE INDEX 命令將建立一個 B-tree 索引, 它適合大多數情況。

CREATE INDEX test1_id_index ON test1 (id);

DROP INDEX

CREATE INDEX name ON table USING hash (column);

http://www.postgresql.org/docs/8.2/interactive/indexes.html

2007-02-22

PostgreSQL 關聯式模型初探

我們把 PostgreSQL 稱為 ORDBMS, 相對其它DBMS多了"物件",
有了物件就的關念, 就產生了很棒的主要特性"繼承",
就有必要來用物件的關念了解 PostgreSQL 對物件的關念.

表 Table 是一個物件模型 model,
各各欄位 Column 就是物件的各各"屬性"
欄位值就相當於屬性"值"

tuple = row

從資料庫系統的選擇重點來談 PostgreSQL

(末寫完~本日放假@@)
我來大概加一加內容, 根據最下方轉述的文章提到的重點,
來對 PostgreSQL 加內容:

1、高可靠/穩定性(High-Availability)
PostgreSQL 在全球各地的政府/學術/商業環境中被持續的使用著.
且有多數公司將其 PostgreSQL 商業化成公司, 來提供支援例如 EnterpriseDB ..
透過 Slon-I 複寫叢集, WAL 預寫式日誌, pgCluster 叢集化系統...,
再利用異地備份的實作, 跟其它商業型產品比並不輸哦!!!

站內延伸閱讀: PostgreSQL 高可靠性與 WAL

2、可擴展(High-Scalability)

3、安全性(Security)

4、豐富的開發工具

5、服務質量



以下轉述"數據庫的選型原則"一篇
http://blog.csdn.net/lingli99/archive/2004/11/17/184469.aspx
對DBMS提出五個選型重點:

一、數據庫的選型原則
1、穩定可靠(High-Availability)
數據庫保存的是企業最重要的數據,是企業應用的核心, 穩定可靠的數據庫可以保證企業的應用常年運行,而不會因為數據庫的宕機而遭受損失。企業的信息化可以促進生產力,但如果選擇了不穩定產品,經常影響業務生 產的正常運營,則實際效果很可能是拖了企業的後退。無論是計劃中(數據庫維護等正常工作)還是意外的宕機都將給企業帶來巨大的損失,這意味著企業要減低收 入、要降低生產力、要丟失客戶、要在激烈的企業競爭中丟失信心。信息系統的穩定可靠是由多方面的因素構成的,包括網絡、主機、操作系統、數據庫以及應用軟 件等幾方面,這些因素互相之間又有一定的依賴關係,因此,在企業信息化的選型中要通盤考慮這些問題。在數據庫方面主要看數據庫要具備災難恢復、系統錯誤恢 復、人為操作錯誤恢復等功能,同時要儘量降低數據庫的計劃內維護宕機時間。
2、可擴展(High-Scalability)
企業的 應用是不斷深入和擴展的,數據量和單位時間的事務處理量都會逐漸增加。如果要求企業購置一套信息系統足以滿足未來若干年發展的需要顯然是不恰當的,因為這 實際意味著企業要多花很多錢而不能發揮信息設備的最大效能,造成資源的浪費。比較好的解決辦法就是企業先購置一套配置較低,功能適用的系統,當未來業務有 需要時可以方便的對系統進行擴展,使系統的處理能力逐步增加滿足業務處理的需求。落實到數據庫就是要選擇具有良好的伸縮性及靈活的配置功能的產品,無論是 主機系統的內存或硬盤方面的擴展還是集群系統的擴展,都能夠被數據庫利用,從而提高系統的處理能力。
3、安全性(Security)
數 據庫的安全性是指保護數據庫以防止不合法的使用造成的數據洩露、更改或破壞。安全性問題不是數據庫系統獨有的,所有計算機系統都有這個問題。只是在數據庫 系統中保存著大量重要的數據,而且為許多最終用戶共享使用,從而安全問題更為突出。系統安全保護措施是否有效是數據庫系統的重要指標之一。 數據庫的安全控制主要通過用戶標識與鑑別、存取控制、視圖機制、審計、數據加密等機制完成。
4、豐富的開發工具
無論是優秀的硬件平台還 是功能強大的數據庫管理系統,都不能直接解決最終用戶的應用問題,企業信息化的工作也要落實到開發或購買適合企業自身管理的應用軟件。目前流行的數據庫管 理系統大都遵循統一的接口標準,所以大部分的開發工具都可以面向多種數據庫的應用開發。當然,數據庫廠商通常都有自己的開發工具,例如SYBASE公司的 PowerBuilder,Oracle公司的Developer2000,以及Ms的VisualStudio。這些開發工具各有利弊,但無疑選擇和數 據庫同一個廠商的產品會更有利於應用軟件的開發以及將來得到統一的技術支持。
5、服務質量
在現今信息高度發達的競爭中,數據庫廠商 完全靠產品質量打動用戶的年代已不復存在,各數據庫產品在質量方面的差距逐漸縮小,而用戶選擇產品的一個重要因素就是定位在廠家的技術服務方面。因為在你 購買了數據庫系統之後,你面臨著複雜的軟件開發,數據庫的維護,數據庫產品的升級等等,你需要得到數據庫廠商的培訓,各種方式的技術支持(電話、用戶現 場)和諮詢。數據庫廠家的服務質量的好壞將直接影響到企業信息化建設的工作。

PostgreSQL - 在維基百科的說明

維基百科,自由的百科全書

維基百科將會是人類史上最大的內容管理
幾乎從古到今, 從個人到全宇宙, 從中文到全球語言...
大大小小的事全都被記錄在這
http://zh.wikipedia.org/

對 PostgreSQL 的介紹
http://zh.wikipedia.org/wiki/PostgreSQL

2007-02-21

PostgreSQL - LISTEN/NOTIFY 宣告

用途:
NOTIFY:產生一個通告
LISTEN:產生通告的監聽
UNLISTEN:取消通告的監聽

系統表:
pg_listener :記錄目前監聽中的程序;


範例:

#LISTEN virtual;
#NOTIFY virtual;
------------------------
Asynchronous notification "virtual" received from server process with PID 8448.
**2007-02-22
(目前必須加 " ", 否則可能會造成 error )
http://people.planetpostgresql.org/greg/index.php?/archives/98-guid.html

http://www.postgresql.org/docs/8.2/static/sql-notify.html
http://www.postgresql.org/docs/8.2/static/sql-listen.html

2007-02-20

PostgreSQL 類型轉換(CAST)概要

CAST
中譯:類型轉換, 型別轉換, 型態轉換

目的:
將資料類型做轉換成另一種資料類型.
轉換過程可能造成原有資料變形, 被削去資料長度, 產生錯誤等風險.

pgAdmin III 擷圖(按圖放大)


語法:

  1. CAST ([data] AS [target_datatype])
  2. '[data]'::[target_datatype] -- 這是 PostgreSQL 具歷史的替代語法.
PostgreSQL 預設的 cast 定義目前有 256個
使用者可以透過自行定義來增加 cast

語法:
CREATE CAST [source_type] AS [target_type]
WITH FUNCTION [target_type]([source_type])
AS IMPLICIT; -- 表示這個轉換可以在任何環境裡隱含調用。

DROP CAST

PostgreSQL 編碼轉換(Convert)與應用技巧

這個功能特別適合用在對特定欄位先進行"來源字元編碼"轉換(Convert)"目的字元編碼"後, 再進行必要的資料操作事務, 例如用來做從UTF-8資料庫中抽出欄位 Convert 成 BIG-5 編碼後再進行排序.

PostgreSQL 對 "Convert" 預先定義了近 120 種供您選擇與使用(棒吧!)
詳細請查看 pg_catalog 網要模式中的 convert.

pgAdmin-III 擷圖(點圖放大)


排序可能受到的環境變數影響正確性:(請觀察您自己)
server_encoding: UTF8
client_encoding:UTF8
lc_collate:zh_TW.UTF-8

要把數據庫初始化成支持中文的 locale,比如我用 zh_TW.utf8:

initdb --locale=zh_TW.utf8 --encoding=utf8 ...

{轉述:laser}在一般用途的 postgresql 的使用時,一般會建議使用 C 做為初始化 locale,這樣PG將會使用自身內部的比較函數對各種字符(尤其是中文字符)進行排序,這麼做是合適的,因為大量OS的locale實現存在一些問 題。對於tsearch2,因為它使用的是locale來進行基礎的字串分析的工作,因此,如果錯誤使用locale,那麼很有可能得到的是空字串結果, 因為多字節的字符會被當做非法字符過濾掉。{轉述:laser}


locale 要指定為"C"

語法:
CONVERT([colname] USING [Aencodeing_to_Bencodeing])


範例:
select * from countries order by convert(state using
utf8_to_big5);

===============================================
順便提一下 I18N
Debian:
/usr/share/i18n
/usr/share/locale


請善用 Google.....

國際化(Internationalization,I18N)
程式本身所具的能力, 使其能依使用者的語言環境不同, 而適應不同的語言文字、字碼集的處理等等。國際化是指軟件能用於多國語言環境的能力。在Linux中通過locale來設置程序運行的不同語言環境,locale 由 ANSI C提供支持。locale的命名規則為<語言>_<地區>.<字符集編碼>,如zh_CN.UTF-8,zh代表中

"國際化"可能是目前我們找得到的最好解答, 國際化的英文名稱是 InternationalizatioN,這個英文單字的第一個字母 I 與最後一個字母 N 之間有 18 個字母,所以也被簡稱為 I18N。 I18N 是一種觀念跟目標,這個想法是要提供一個架構, 讓同樣的程式碼可以適用在各種語文習慣跟編碼系統上面, 程式設計人員只要利用這個架構的機制跟準則撰寫應用程式, 就可以在不需重新編譯程式的情況下,自然的支援各式各樣的語言, 不過為了要達成這樣的目標,作業系統必須提供一定程度的支援, 特別是在各種的程式庫裡面都得有支援 I18N 的 設計才可以, 這邊特別重要的就屬 C 程式庫以及 X 視窗系統的國際化設計了。

phppgadmin 4.2 dev 新中文語系檔



這二天剛翻譯完的
預計在4.2版內才提供

給目前有用 phppgadmin 4.1 版的朋友測試用
複製下載包內的結構全部文件入您安裝的目錄替代即可用

包括二個新語系選項
簡體中文(UTF-8)
正體中文(UTF-8)

請各位朋友協助找出翻譯不好的詞彙來改進
讓 PostgreSQL 更融入中文社群 害羞

附件: phppgadmin4.dev.rar
(大小: 60.95KB)

2007-02-19

PostgreSQL 序列數與 Serial 資料類型

語法:
CREATE SEQUENCE [seq_name];
DROP SEQUENCE [seq_name];
ALTER SEQUENCE [seq_name];

引數:
INCRIMENT:遞增量
MINVALUE:最小值
MAXVALUE:最大值
START:初始值
CACHE:快取
CYCLE:循環

函數"
nextval()
currval()
setval('a', n):重設

範例:
CREATE SEQUENCE mytest_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

使用序列數:
ALTER TABLE mytest ALTER COLUMN id SET DEFAULT nextval('mytest_id_seq');
系統會補上成為
nextval('mytest_id_seq'::regclass)

pgAdmin-III GUI 擷圖:(按圖放大)



phppgadmin 4.1+ 擷圖:(按圖放大)



Serial 資料類型
自動利用 sequence 創建一個從1開始, 遞增1的連續數欄位.

官方文件說明

PostgreSQL 例行程序維護工作 - VACUUM 概要

對映章節:III.C22.1

版本差異:
8.0+ 增加可詷整的組態參數選項
8.1+ 增加 Auto-Vacuum Daemon

目的:
清理與最佳化資料庫的索引定義, 另可清理因頻繁更新資料殘存在內部的"垃圾記錄"

  1. 恢復由已更新的或已刪除的行佔據的磁盤空間。
  2. 更新 PostgreSQL 查詢規劃器使用的資料統計資訊。
  3. 避免因為事務 ID 重疊造成的老舊資料的遺失。
phpPgadmin 擷圖:

不是很想對這名詞做中文化, 原因是翻成"清理"...似乎不太能代表這重大功能原文的含義.

pgAdmin III 擷圖:


由來:
RDBMS 利用"定義索引"技術來加快搜尋.
透過 Vacuum 技術來"清理和整理"索引.

選項:
  1. 完整的:會對所有資料表進行 Vacuum, 缺點就是費時.
  2. analyze: 進行更詳細的索引分析, 更費時, 但能建更有效率的執行計劃.
限制條件:
Vacuum 這維修工作期間會locks住資料表, 導致無法存取.
建議在深夜或是使用少時排定行程.

統計分析對 postgresql.org 瀏覽者們

今天看到一篇來自 Planet PostgreSQL
Magnus Hagander's - Google analytics makes pretty graphs
利用 Google Analytics 來解析全球對 postgresql.org 的概要分析,
我粗略翻譯整理轉載如下:

兩三天前, 我們使 Google Analytics 對 postgresql.org 網站
收集一些統計主要關於我們的訪客使用何種語言的瀏覽器。

這張圖統計出瀏覽者使用的何種語言的OS來參觀postgresql.org 網站
(英文語系使用者, 竟然佔了近50%, 中文加油@@""")

當您改為看 URLs, 有一些進一步有趣的題材:

下載人數大於上來找參考資料的人。 0.0


統計最後的片斷瀏覽器和平臺被使用。首先, 瀏覽器:

領先的是 Firefox, 大於 50% 。Internet Explorer 有我們的訪客三分之一。多數其它瀏覽器非常小。
(果然會想了解 postgresql.org 的朋友大多都是OOS的Firefox 的愛好者)

現在, 看來源者的平臺:

即使PostgreSQL 是非常 Unix 中心, 我們仍然有我們的訪客的幾乎四分之三使用 MS。

我們並且得到 NTT DoCoMo 出現作為頂面瀏覽器, 並且感興趣。
我想是在日本行動上網人數和對 PostgreSQL 感興趣的人數比其它國家更高更先進。

原文:
http://people.planetpostgresql.org/mha/index.php?/archives/136-Google-analytics-makes-pretty-graphs.html

2007-02-16

PostgreSQL 結合 memcached 進行快取資料與多主機同步

更新:2007-03-04
對應章節:

內容:
Memcached 是一個分散式的 Memory Object 架構,最早由 Life Journal 所採用。
它可以啟動一支 Daemon 來將所有其它 Client 的 Object 都集合起來,並且做到多主機同步化的工作。

運用的重心在:
減少直接對實體資料庫進行"讀"操作,將絕大多數"讀"的資料放在快取中。
再或者可以直接將產生的頁面 html 代碼放在緩存中。

來加速資料對Client的反應速度.


PostgreSQL 也進行著和這 Daemon 的搭載開發:
http://pgfoundry.org/projects/pgmemcache/



===================(待整理)
http://www.danga.com/memcached/download.bml
http://www.linuxjournal.com/article/7451
http://www.example.net.cn/archives/2006/01/eoamemcachedoea.html
http://bbs.pgsqldb.com/index.php?t=msg&th=8574&start=20&rid=&
http://lightyror.thegiive.net/2006/12/rails-memcached_21.html
http://blog.roodo.com/jaceju/archives/2429636.html
http://blog.twpug.org/post/30/239

2007-02-15

PostgreSQL 8.1 版本的新特色

新增的高級資料庫特性

角色: PostgreSQL現在支持資料庫角色功能,該功能簡化了資料庫中有大量用戶且用戶權限有複雜交叉情況下的管理功能。

IN/OUT 輸入/輸出參數: PostgreSQL函數現在支持IN,OUT和INOUT參數,該功能進一步提高了對JAVA和.NET應用程序中複雜商業邏輯的支持。

2段式提交(2PC): 該特性是廣域網應用和不同種類的資料中心使用PostgreSQL時一直提出的要求,它也允許ACID兼容的事務跨服務器執行。

性能的改進和增強

提升多處理器(SMP)架構的性能: 8.1版本的緩衝管理已強化為可隨處理器的數量而線性變化,由此對8路、16路、雙核及多核處理器的服務器極大地提高的性能。

位圖掃瞄:索引將會在適當的時候動態地轉化為內存中的位圖,由此對非常大的資料表在進行複雜查詢時最多可提高近二十倍的索引性能。這也有助於通過大幅減少多列的索引而簡化資料庫的管理功能。

表分區: 查詢優化器現在可以通過對資料表使用名為排除約束的技術而避免掃瞄一個大表的全部內容。類似於其他資料庫系統的表分區技術,這個特性提高了性能同時也優化了也對多達數G字節的資料表的管理。

共享行級鎖: PostgreSQL的"更佳的行級鎖"現在通過附加對外鍵的共享鎖而支持更高級別的併發處理,共享鎖也將提高很多大容量的OLTP應用程序的資料錄入和更新的性能。

"PostgreSQL 8.1版本極大地優化了的性能,在我們的雙Opteron處理的服務器上",Reliable Computer Solutions公司的資料庫管理員Merlin Moncure說道,"更明確地來說,我發現簡單查詢的運行時間大約減少20%,CPU負載降低20%,服務器的負載提升了讓人驚奇的20-40%"。

另外還有超過120多處其他方面的改進,部分詳細情況可參見8.1版本的presskit文檔。

新版本中的其他特性
除了在新聞發佈稿中所列的主要特性外,還有另外由200多位開發人員為8.1版貢獻的超過120處的新特性和改進,其中包括:

* GiST: PostgreSQL的GiST功能加上索引機制提升了對高速併發能力、可復原性的支持,同時也改進了以前只在使用B-Tree索引才有較好性能的更新功能, GiST?也是PostgreSQL全文索引(TSearch2)、空間地理資料(GIS) 和樹狀結構索引的後台核心。通過這個改進,複雜資料類型在大型高可靠性要求的應用程序中也會表現出色。

* COPY功能重構: COPY經重新開發優化後,對批量加載資料可提高處理速度30%。另外通過 對加載CSV格式資料的改進, 這也使得遷移其他大型資料庫至PostgreSQL比以前更加快捷。

* 64位共享內存: 在64位系統平台上,PostgreSQL的緩衝管理器最大可使用2TB內存,這也使PostgreSQL在將來的大容量內存的服務器上使用打下基礎。

* 內部集成自動清理功能(Autovacuum): PostgreSQL的資料庫維護進程進行了改進並集成到主服務程序中,這使得PostgreSQL服務器的安裝及維護更加簡單。

* 更快的聚合功能: 聚合功能進行了改進,使得報表類的查詢更快。PostgreSQL的開發人員重寫了聚合功能的內存管理部分,增加了對MIN()和MAX()功能的索引優化。

* 管理功能: 為了方便地獲得PostgreSQL服務器自身的信息而增加了一些管理功能,另外從PSQL命令行程序中也可以基本的DBA(資料庫管理員)管理功能。

* 兼容功能: 新增了lastval(),greatest() 和 least() 等函數,使得應用程序從MySQL和Oracle向PostgreSQL遷移更簡單。

你也可以閱讀到8.1版本一系列的改進,詳情可瀏覽:發佈說明。

中文化名詞定義的翻譯說明

目前我正在進行
1. pgAdmin III 1.6.3+ ~1.8+
2.phpPgAdmin 4.2 +
3.PostgreSQL 常見問答 FAQ

版本的正/簡體中文版本的第二階段修詞
小弟到處找相關書籍和資料
本著將本地化做到最適切的語意,
在這篇要整理一下對翻譯詞的說明:

role. member. user(角色.成員.使用者)

tuples::變數值集合
變數值組; 元組元素; 組合 (在關係資料庫中, 標識一個實體以及它的屬性的某種關係的一部分

heap::堆疊
是用來動態使用記憶體的方式,使用的自由度最高,但需要自行善後清理。通常是用malloc/free或是new/delete來處理。

2007-02-14

phpPgAdmin 4.x for Debian 快速安裝使用

1.安裝套件:
#apt-get install phppgadmin
(packages 的相依性由 Debian 解決)

2.相關目錄說明:
/etc/phppgadmin/ :主要組態檔位置.

  • config.inc.php : PostgreSQL 連結設定.
  • apache.conf : Apache 用的 url 及 IP 限制設定.
/usr/share/phppgadmin/ : 套件相關程式位置

3.將 Apache2 的配置檔軟連結給 Apache2 的 site:
#ln -s /etc/phppgadmin/apache.conf /etc/apache2/sites-enabled/001-phppgadmin

4.重新載入Apache2
#/etc/init.d/apache2 restart

5.完成
http://[localhost]/phppgadmin

注意事項
----------------------------------
1.自行升級到4.1+, 需留意
config.inc.php
$conf['servers'][0]['host'] = ' '; (預設是空的, 要填IP)

/usr/share/phppgadmin/conf/config.inc.php 要能連結到

SQL:同時 INSERT 多行記錄

版本限制: 8.2 版以上才具本功能.

EX.
INSERT INTO test1 (my_url) VALUES ('299c7e5d2b785d84'),('3e7c95377f69550c');
-----------
INSERT 0 2

SQL:CREATE TABLE 的深入研究

在預設的情況下新增一個表指定
1.serial:
系統會自動產生一個 Sequence 物件, 並以"[TableName]_[ColumnName]_seq"進行命名.
2.PRIMARY KEY:
系統會自動產生一個 Constraint 物件, 並以"[TableName]_pkey"進行命名.

範例
=# CREATE TABLE test1
-# (
(# id serial PRIMARY KEY,
(# my_url varchar(50)
(# );
------------------------------------------------------------------------------------
NOTICE: CREATE TABLE will create implicit sequence "test1_id_seq" for serial column "test1.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test1_pkey" for table "test1"
CREATE TABLE

=#

如上的範例, 但系統在背後轉成正式語法如下:
CREATE TABLE test1
(
id serial NOT NULL,
my_url character varying(50),
CONSTRAINT test1_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE test1 OWNER TO postgres;

2007-02-13

型別/類型轉換(Type Conversion)

對映章節:C.10

1.
2.用戶自定義:CREATE CAST
內建類型類型轉換集已經經過仔細的雕琢了,不要去變更它們。

---------------------------------------------------
取時間到小數第6位
# select cast(current_timestamp as timestamp);
or
# SELECT current_timestamp::timestamp;

now
----------------------------
2007-02-13 08:55:14.269532
(1 row)


轉成字串只取小數六位
'US':微秒
#select to_char(current_timestamp, 'US');
to_char
---------
512888
(1 row)


http://www.postgresql.org/docs/8.2/interactive/functions-formatting.html

2007-02-12

[轉載]DB2 與 postgresql 開發的異同

原作者:Laser(PostgreSQL 中國)
原文連結:http://bbs.pgsqldb.com
--------------------------------------------------------------------------
最近用了用DB2,寫了點區別。不一定完全正確。不過可以看看兩者區別。

1,數據定義語言(DDL)以及外圍工具:
db2與postgresql的區別幾乎為0,
具體區別是:(desc)<>

a、db2 varchar 在沒有設置特殊表空間的話,上限為32K,postgresql的varchar
沒有這個限制;

b、db2的二進制大對象類型blob,在postgresql裡用bytea類型代替;

c、db2的文本大對象類型clob、long varchar,在postgresql裡用text類型代替;

d、db2中generated by default 或者 generated always 類型鍵字在
postgresql中用serial或者bigserial代替;(這個區別是最大的)

e、db2中沒有序列(sequence對象),postgresql中有,因而更加靈活;

f、db2中的自增主鍵的值重置需要alter table,而postgresql裡可以用setval()
系列函數設置序列,更簡單;

g、db2中不支持空間數據類型(點、線、面),postgresql支持;

h、db2中事務隔離級別定義與SQL標準不夠兼容,postgresql完全兼容SQL標準;

i、db2中JDBC type4驅動不支持GBK的數據庫編碼,postgresql沒有這個問題;

j,db2的數據庫編碼轉換非常搞笑且複雜,postgresql很簡單,只需要 set
client_encoding to xxx;

k,目前db2 8.1還不支持UNICODE後台編碼,postgresql支持,因此可以支持多內碼;

l,db2 8.1的觸發器無法讀取被觸發的數據表,postgresql沒有這個限制;

m,db2 8.1 的觸發器不能執行存儲過程,postgresql沒有這個限制;

n,postgresql 中有bit(位)、bitstring(位串)類型,db2沒有;

o,db2的客戶端db2/db2cmd執行sql腳本需要指定分隔符,比如@;postgresql的客
戶端缺省就是分號;

p,db2的客戶端db2/db2cmd沒有命令歷史功能,postgresql的客戶端有,可以編輯
歷史命令;

q,db2無法在同一機器上安裝不同數據庫版本,postgresql可以;

r,db2有聯邦數據庫的概念,postgresql沒有;

s,db2 II可以進行異構數據庫複製,postgresql需要自己開發軟件;

t,db2 8.1沒有很好的辦法將整個數據庫備份成文本腳本,postgresql很容易;

u,db2 8.1的java存儲過程重載需要重啟數據庫,postgresql不需要;

v,db2 的客戶端db2/db2cmd中的SQL命令缺省下無法使用折行,postgresql的可以;

w,db2沒有中文文檔,postgresql有,並且很完整;

x,db2有比較完整的錯誤碼,postgresql目前不夠完整;

y,db2中調用函數需要:select func() from sysibm.sysdummy1,
postgresql 中只需要:select func(); 即可

z,db2中返回結果集的存儲過程或者函數書寫非常複雜,需要使用額外的tablet存
儲結果集,
postgresql中只是增加了RETURN NEXT語句;

A,db2 中存儲過程沒有ROWTYPE/RECORD類型的概念,postgresql中有;


2,數據操作語言(DML):

在基本數據操作上幾乎沒有區別。SQL標準的函數定義也完全相同。
但是postgresql的類型轉換函數 CAST 是 SQL 標準,db2中略有區別,
考慮到db2中只能進行有限的類型轉換,所以,postgresql實際上
是解放了db2開發人員。大部分postgrsql的函數類似oracle,與db2
提供的函數有些區別。

其它基本相同。

3,客戶端接口

C接口肯定差距巨大,這個不用說了。

ODBC是標準,postgresql提供足夠的ODBC支持。

JDBC是標準,不過postgresql只提供類型4的,db2似乎更喜歡用類型2的。

JDBC連接建立的區別是:

db2的類路徑是:COM.ibm.db2.jdbc.app.DB2Driver (type2),
com.ibm.db2.jcc.DB2Driver(type4),

postgresql的類路徑是:org.postgresql.Driver。

....

(我自己用db2的感覺是,MD真垃圾,和IBM的牌子反差太大了,
估計又是外購的產品,說實話,IBM的好軟件不多。。。。)

--------------------------
flying:
補充一下下:
對於1(DDL)中的「o:db2的客戶端db2/db2cmd執行sql腳本需要指定分隔符,比如@;postgresql的客
戶端缺省就是分號;」
db2的客戶端db2/db2cmd,在執行sql腳本時,缺省也認為分號是分隔符。但是也可以根據需要指定其他符號作為分隔符。如:
1) db2 -t filename
不指出特殊分隔符,則db2執行時候,將filename中的分號解釋為分隔符

2)db2 -td@ filename
用-d 指出 使用@作為分隔符


另外,我自己曾經理解錯誤過,以為只要有分隔符,sql文件內的語句彼此間不用特意的書寫換行。但實際上db2的客戶端db2/db2cmd的情景是,分隔符配合換行,sql文件才能被正確執行。

添加:
1 DDL,
B 、DB2不可以用Alter Table 語句刪除列;PostgreSQL可以。

DB2中對視圖的更新,最通用的解決方法是,寫INSTEAD OF 觸發器。參見http://www-128.ibm.com/developerworks/cn/db2/library/techarticles/0210rielau/0210rielau.html

而 PostgreSQL推薦用 CREATE RULE 來實現對視圖的更新,參見http://www.pgsqldb.org/pgsqldoc-8.1c/sql-createrule.html

[OS優化]針對PostgreSQL進行OS調校

(陸續撰寫中...)

為了能夠應付眾多的使用者,必要對OS層級來對PostgreSQL
在根基上進行調校.

OS的系統資源:
1.共享記憶體(Shared Memory:SM)
2.Semaphore的數量
3.檔案系統表(File Table)

1:
#more /proc/sys/kernel/shmmax
Linux上該參數的預設值通常為 32MB。
#ipcs -l -m
可動態進行擴展
#echo 41697280 > /proc/sys/kernel/shmmax

2:
#ipcs -l -s
------ Semaphore Limits --------
max number of arrays = 128 (最大使用者數量)
max semaphores per array = 250
max semaphores system wide = 32000 ()
max ops per semop call = 32
semaphore max value = 32767



3:
#cat /proc/sys/fs/file-max
Linux上該參數的預設值通常為 99900。(Debian)
可動態進行擴展
#echo xxxx > /proc/sys/fs/file-max
總計99900個/每人估計使用40個=2497.5人
足夠應付同時Session人數.

延伸閱讀:
http://www.eygle.com/archives/2005/11/whats_mean_linux_shmmax.html

伺服器程式撰寫(V)

對映主題:V

通過增加用戶定義的類型,操作符,聚集, 以及查詢語言和編程語言函數來擴展服務器功能。
包含以下的方式與表題:

1.擴展 SQL :透過改變與用戶自定義"系統表schme"各式物件,方式進行高度自由性變化.
2.觸發器(Trigger) : "CREATE TRIGGER", 時間點分二種-before與after.
3.規則系統:重寫
4.程序語言(Procedural Language:PL):PL/pgSQL...
5.伺服器程式元件(SPI)

SQL:UPDATE [Table]...BETWEEN...AND

更新資料表資料內容與限定條件範圍:

範例01:

UPDATE multimedias
SET noid01 = 39
WHERE id BETWEEN 124 AND 127;
--------------------------------------
UPDATE 4

範例02:

update table set "col_name"='1221' where id='1111';

2007-02-11

max_stack_depth



http://www.pgsqldb.org/pgsqldoc-8.1c/regress-evaluation.html
27.2.6. 堆棧深度不夠
如果 errors 測試導致在 select infinite_recurse() 命令的時候服務器崩潰,這就意味著平台對進程堆棧的限制小於 max_stack_depth 參數指出的值。我們可以通過在更高的堆棧限制的數值上運行 postmaster 繞開這個問題 (缺省的 max_stack_depth 下,建議數值是 4M)。 如果你無法這麼做,那麼另外一個方法是減少 max_stack_depth 的值。

http://www.pgsqldb.org/pgsqldoc-cvs/runtime-config-resource.html
17.4.1. 內存
max_stack_depth (integer)
聲明服務器的執行堆棧的最大安全深度。為此設置一個參數的原因是內核強制的實際堆棧尺寸(就是 ulimit -s 或者局部等效物的設置),小於一個安全的一兆字節左右的範圍。 需要這麼一個安全的界限是因為在服務器裡,並非所有過程都檢查了堆棧深度, 兒只是在可能遞規的過程,比如表達式計算這樣的過程裡面進行檢查。 把這個參數設置得大於實際的內核限制講意味著一個正在跑的遞歸函數可能會導致一個獨立服務器進程的崩潰。 缺省設置是 2048 KB (兩兆),這個值相對比較小,不容易導致崩潰。 但是,這個值可能太小了,以至於無法執行複雜的函數。

PostgreSQL 8.2.3 Documentation 補充說明
Setting max_stack_depth higher than the actual kernel limit will mean that a runaway recursive function can crash an individual backend process. On platforms where PostgreSQL can determine the kernel limit, it will not let you set this variable to an unsafe value. However, not all platforms provide the information, so caution is recommended in selecting a value.

8.2版 發行公告
http://www.postgresql.org/docs/8.2/interactive/release-8-2.html
Prevent max_stack_depth from being set to unsafe values
On platforms where we can determine the actual kernel stack depth limit (which is most), make sure that the initial default value of max_stack_depth is safe, and reject attempts to set it to unsafely large values.

2007-02-09

伺服器組態-記憶體資源消秏量(17.4.1)

根據:17.4.1

1.shared_buffers (integer) :
共享緩衝器-預設:32MB
2.temp_buffers (integer):
每個資料庫Session最大臨時緩衝器-預設:8MB
3.max_prepared_transactions (integer)
最大已準備事務數量-預設5個 >= "max_connections"
4.work_mem (integer)
內部工作起始數量-預設:1MB
5.maintenance_work_mem (integer)
維護性工作運作時數量-預設:16MB >= "work_mem"
6.max_stack_depth (integer)
最大堆疊深度

2007-02-08

同作控制(Concurrency Control) - 簡介

今天來談談
同作控制(Concurrency Control)
對映參照範圍:II.SQL語言.C12.同作控制

目標:保護資料完整性的機制
Concurrency可以譯作為"同作"."並發"."併發", 意思是同時發生的狀況"點"。
所以同作控制在 PostgreSQL 裡要談的就是,系統在兩個或者更多Sessions試圖同時訪問同樣的Data的時候表現出來的情況。 在這種同作Sessions的情況下的目標是為所有Sessions提供效率的存取,同時還要維護嚴格的資料完整性

PostgreSQL採用的方法-MVCC
多版本同作控制(Multiversion Concurrency Control, MVCC), 這是先進於其它DBMS所特有的控制方式。當然~也提供了同於其它DBMS均有的LOCKS相關方式, 善用傳統的Locks並配合MVCC使用,會使CC更佳有效。再配合事務隔離(Tansaction isolation), 就能達到資料一致性。

MVCC 與 Locks
MVCC採用的是多個版本時間點資料模型來接受要求的Sessions, 避開Locks與鎖死的問題。 使用MVCC多版本併發控制比鎖定模型的主要優點是在MVCC裡, 對檢索(讀)Data的鎖要求與寫Data的鎖要求不衝突, 所以讀不會阻塞寫,而寫也從不阻塞讀。

PostgreSQL採行的CC種類:
1.事務隔離:讀-已提交, 可序列化層級二種。
2.明確鎖定:表級鎖, 行級鎖, 死鎖排除, Advisory Locks(8.2版新增) An advisory lock is a cooperative lock.。
3.應用層的數據完整性檢查
4.鎖和索引


http://www.postgresql.org/docs/8.2/interactive/mvcc.html

2007-02-06

[轉載]簡評MySQL和PostgreSQL優劣

這是一篇轉載的好文..
但因為有文章比較基期有時效的關係
阿益會對文做標註刪除~

http://www.51cto.com 2006-12-26 15:03 出處:chinaitlab

【導讀】在這篇文章中,我們選用MySQL 4.0.2-alpha與PostgreSQL 7.2進行比較,因為MySQL 4.0.2-alpha開始支持事務的概念,因此這樣的比較對於MySQL應該較為有利。

一、前言

前一段時間,曾經翻譯過一篇將《你的網站從MySQL改為PostgreSQL》,其實當初我更感興趣的是一個應用程序的後台數據庫從 MySQL轉為PostgreSQL的具體操作,並沒有關心MySQL和PostgreSQL的優劣,沒想到反應出乎意料的大,因此我也就覺得有寫這篇文 章的必要了。

在這篇文章中,我們選用MySQL 4.0.2-alpha與PostgreSQL 7.2進行比較,因為MySQL 4.0.2-alpha開始支持事務的概念,因此這樣的比較對於MySQL應該較為有利。

我們這樣的比較不想僅僅成為一份性能測試報告,因為至少從我個人來看,對於一個數據庫,穩定性和速度並不能代表一切。對於一個成熟的數據庫,穩定性肯定會日益提供。而隨著硬件性能的飛速提高,速度也不再是什麼太大的問題。

二、兩者的共同優勢

這兩個產品都屬於開放源碼的一員,性能和功能都在高速地提高和增強。MySQL AB的人們和PostgreSQL的開發者們都在儘可能地把各自的數據庫改得越來越好,所以對於任何商業數據庫使用其中的任何一個都不能算是錯誤的選擇。

三、兩者不同的背景

MySQL的背後是一個成熟的商業公司,而PostgreSQL的背後是一個龐大的志願開發組。這使得MySQL的開發過程更為慎重,而PostgreSQL的反應更為迅速。這樣的兩種背景直接導致了各自固有的優點和缺點。

四、MySQL的主要優點

1、首先是速度,MySQL通常要比PostgreSQL快得多。MySQL自已也宣稱速度是他們追求的主要目標之一,基於這個原因, MySQL在以前的文檔中也曾經說過並不準備支持事務和觸發器。但是在最新的文檔中,我們看到MySQL 4.0.2-alpha已經開始支持事務,而且在MySQL的TODO中,對觸發器、約束這樣的註定會降低速度的功能也列入了日程。但是,我們仍然有理由 相信,MySQL將有可能一直保持速度的優勢。

2、MySQL比PostgreSQL更流行,流行對於一個商業軟件來說,也是一個很重要的指標,流行意味著更多的用戶,意味著經受了更多的考驗,意味著更好的商業支持、意味著更多、更完善的文檔資料。

3、與PostgreSQL相比,MySQL更適宜在Windows環境下運行。MySQL作為一個本地的Windows應用程序運行(在 NT/Win2000/WinXP下,是一個服務),而PostgreSQL是運行在Cygwin模擬環境下。PostgreSQL在Windows下運 行沒有MySQL穩定,應該是可以想像的。

4、MySQL使用了線程,而PostgreSQL使用的是進程。在不同線程之間的環境轉換和訪問公用的存儲區域顯然要比在不同的進程之間要快得多。

5、MySQL可以適應24/7運行。在絕大多數情況下,你不需要為MySQL運行任何清除程序。PostgreSQL目前仍不完全適應24/7運行,這是因為你必須每隔一段時間運行一次VACUUM。

6、MySQL在權限系統上比PostgreSQL某些方面更為完善。PostgreSQL只支持對於每一個用戶在一個數據庫上或一個數據 表上的INSERT、SELECT和UPDATE/DELETE的授權,而MySQL允許你定義一整套的不同的數據級、表級和列級的權限。對於列級的權 限,PostgreSQL可以通過建立視圖,並確定視圖的權限來彌補。MySQL還允許你指定基於主機的權限,這對於目前的PostgreSQL是無法實 現的,但是在很多時候,這是有用的。

7、由於MySQL 4.0.2-alpha開始支持事務的概念,因此事務對於MySQL不再僅僅成為劣勢。相反,因為MySQL保留無事務的表類型。這就為用戶提供了更多的選擇。

8、MySQL的MERGE表提供了一個獨特管理多個表的方法。

9、MySQL的myisampack可以對只讀表進行壓縮,此後仍然可以直接訪問該表中的行。

五、PostgreSQL的主要優點:

1、對事務的支持與MySQL相比,經歷了更為徹底的測試。對於一個嚴肅的商業應用來說,事務的支持是不可或缺的。

2、MySQL對於無事務的MyISAM表。採用表鎖定,一個長時間運行的查詢很可能會長時間地阻礙對表的更新。而PostgreSQL不存在這樣的問題。

3、PostgreSQL支持存儲過程,而目前MySQL不支持,對於一個嚴肅的商業應用來說,作為數據庫本身,有眾多的商業邏輯的存在, 此時使用存儲過程可以在較少地增加數據庫服務器的負擔的前提下,對這樣的商業邏輯進行封裝,並可以利用數據庫服務器本身的內在機制對存儲過程的執行進行優 化。此外存儲過程的存在也避免了在網絡上大量的原始的SQL語句的傳輸,這樣的優勢是顯而易見的。

4、對視圖的支持,視圖的存在同樣可以最大限度地利用數據庫服務器內在的優化機制。而且對於視圖權限的合理使用,事實上可以提供行級別的權限,這是MySQL的權限系統所無法實現的。

5、對觸發器的支持,觸發器的存在不可避免的會影響數據庫運行的效率,但是與此同時,觸發器的存在也有利於對商業邏輯的封裝,可以減少應用程序中對同一商業邏輯的重複控制。合理地使用觸發器也有利於保證數據的完整性。

6、對約束的支持。約束的作用更多地表現在對數據完整性的保證上,合理地使用約束,也可以減少編程的工作量。

7、對子查詢的支持。雖然在很多情況下在SQL語句中使用子查詢效率低下,而且絕大多數情況下可以使用帶條件的多表連接來替代子查詢,但是子查詢的存在在很多時候仍然不可避免。而且使用子查詢的SQL語句與使用帶條件的多表連接相比具有更高的程序可讀性。

8、支持R-trees這樣可擴展的索引類型,可以更方便地處理一些特殊數據。

9、PostgreSQL可以更方便地使用UDF(用戶定義函數)進行擴展。

六、那麼我究竟應該使用MySQL還是PostgreSQL

這個問題很難說得清,而且事實上除了MySQL和PostgreSQL外,使用Oracle、Sybase、Informix等也是明智的選擇。如何你確定只在MySQL和PostgreSQL中進行選擇,以下規則總是有效的。

1、如果你的操作系統是Windows,你應該使用MySQL。

2、如果你對數據庫並不了十分瞭解,甚至不知道事務、存儲過程等究竟是什麼,你應該使用MySQL。

3、如果你的應用對數據的完整性和嚴肅性要求不高,但是追求處理的高速度。例如是一個論壇和社區,你應該使用MySQL。

4、你的應用是一個嚴肅的商業應用,對數據完整性要求很高。而且你希望對一些商業數據邏輯進行很好的封裝,例如是一個網上銀行,你應該使用PostgreSQL。

5、你的應用處理的是地理數據,由於R-TREES的存在,你應該使用PostgreSQL。

6、你是一個數據庫內核的狂熱愛好者,你甚至希望擁有你自己版本的數據庫,毫無疑問,你必須使用PostgreSQL,沒準下一個PostgreSQL版本中某一個模塊的作者就是你。

七、結語

以上只是希望從我的理解客觀公正地評價MySQL和PostgreSQL的優劣。其中的帶有傾向性的意見只代表個人觀點,由於本人水平的限制,有不當之處還請批評指正。

2007-02-03

XML 與 PostgreSQL

2007-02-20
Peter Eisentraut 已完成函數 sql_to_xml(TEXT)table_to_xmlschema(TEXT) 它們使用XML數據類型。

在 8.3 dev 的計劃中預定加入對 xml 的 DataType 支援
章節:8.14
xml
http://developer.postgresql.org/pgdocs/postgres/datatype.html
http://developer.postgresql.org/pgdocs/postgres/datatype-xml.html
http://developer.postgresql.org/pgdocs/postgres/functions-xml.html

目前的作法 8.2 版含以下:
章節:8.14
/contrib/xml2
http://www.postgresql.org/docs/8.2/interactive/datatype-xml.html

新安裝的情況下會有如下的畫面進行

不過應該會安裝失敗....
下面這篇應該可以協助需要的朋友
http://archives.postgresql.org/pgsql-novice/2005-11/msg00216.php
如果你想在 DB 上對 xml 進行處理,你可以用 xml2 (需自加Plugin功能) , 如果是存文件的話用text存入即可.

1.xml 與 資料庫
2.

2007-02-01

備用還原方法-pg_dump and pg_restore

Debian:
/usr/bin/pg_dump, pg_dumpall, pg_restore 都被 link 到
/usr/share/postgresql-common/pg_wrapper

pg_dump [db_name] -D > [bak_db_name.sql]

#留意對寫入位置的"postgres"用戶權限問題

EX.
pg_dump dbname -D > db.sql

pg_dump
優點:
1.用於製作異質平台的DBMS的轉儲功能.
缺點:
1.備份回存的速度非常緩慢
.

備份指令:

pg_dump -Fc -i -EUTF8 -O -x -hlocahost -Upostgres -W -fc:\test_databse.backup test_databse

恢復指令:

pg_restore -dtest_databse -Fc -i -l -O -x -hlocahost -Upostgres -W -f c:\test_databse.backup

在這個例子中,編碼為UTF8,主機為localhost,用戶名為postgres,數據庫為 test_databae, 備份恢覆文件為test_databse.backup

網誌存檔

PostgreSQL & Google-Analytics Running...

::Planet PostgreSQL::

PostgreSQL Information Page

PostgreSQL日記(日本 石井達夫先生Blog)

PostgreSQL News

黑喵的家 - 資料庫相關

Google 網上論壇
PostgreSQL 8 DBA 專業指南中文版
書籍內容討論與更多下載區(造訪此群組)
目錄下載: PostgreSQL_8 _DBA_Index_zh_TW.pdf (更新:2007-05-18)

全球訪客分佈圖(Google)

全球訪客分佈圖(Google)