PostgreSQL 常����FAQ� �����2007 年 2 � 8 � ��� 22:43:13 EST ��������2007 年 2 � 12 � ��� 12:00:04 CST ����人��Bruce Momjian (pgman@candle.pha.pa.us) �������人�����(ChaoYi, Kuo)�kuo.chaoyi@gmail.com� ���������以� http://www.postgresql.org/files/documentation/faqs/FAQ.html ��� ���系統平�������� http://www.postgresql.org/docs/faq/ 裡����� _________________________________________________________________ 常��� 1.1)PostgreSQL ��麼�該�麼��� 1.2)誰���管�PostgreSQL � 1.3)PostgreSQL�����麼� 1.4)PostgreSQL�以�������系統平��� 1.5)���裡���PostgreSQL� 1.6)���� PostgreSQL ��麼� 1.7)���裡���� PostgreSQL ���� 1.8)����交�� BUG � ��� 1.9)����解已�� BUG ��缺���� 1.10)�� ����������� 1.11)��該�樣�� SQL � 1.12)���交����� ������ 1.13)PostgreSQL �����庫系統�起���� 1.14)PostgreSQL �以�������家�������� ��客�端�� 2.1)���以��麼��� PostgreSQL ����� 2.2)��麼工��以� PostgreSQL �� Web � �� 2.3)PostgreSQL ���形����(GUI)�� 系統管��� 3.1)��樣��� PostgreSQL �� /usr/local/pgsql 以����� 3.2)��������������� 3.3)��樣調���庫伺��以���好���� 3.4)PostgreSQL 裡�以���麼樣�調試��� 3.5)��麼�試���������Sorry, too many clients� ��� 3.6)PostgreSQL ���������容� 3.7)(使� PostgreSQL )���使��麼��硬�� ���� 4.1) ��������詢���� 幾���������� 4.2) ����表�索����庫以�����義����� psql 裡����詢�令並顯示��� 4.3) �������������� 4.4) �������表�����庫��大������ 4.5) ����������件裡���������空�� 4.6) ��麼���詢�����麼���詢����索�� 4.7) ��������詢�����樣�估�����詢�� 4.8) ��樣���表���索�大�寫���� ��表������樣��索���大�寫����� 4.9) ����詢裡���樣檢測������� NULL�������確�����������NULL�� 4.10) �種�符������麼��� 4.11.1) ��樣�建���������������� 4.11.2) ���������������� 4.11.3) ��使� currval() ��������������� 4.11.4) ��麼�����常������������麼���������中 ������ 4.12) �麼� OID��麼� CTID � 4.13) ��麼����誤���ERROR: Memory exhausted in AllocSetAlloc()�� 4.14) ����������� PostgreSQL ���� 4.15) ����建��� ����������� 4.16) ��������outer join��詢� 4.17) ����������庫��詢� 4.18) ��������������� 4.19) ��麼��使� PL/PgSQL ������表�����誤���relation with OID ##### does not exist�� 4.20) ��������寫(replication)����� 4.21) ���詢��顯示�表��������詢��中 ������大寫������� _________________________________________________________________ 常��� 1.1) PostgreSQL ��麼�該�麼��� PostgreSQL �� Post-Gres-Q-L�����簡稱�Postgres ���������人����裡�����件� MP3 � �� � PostgreSQL ����������庫系統�����統�業 ��庫系統���������������代 DBMS 系統�使���強���PostgreSQL ����費��並����代碼��以��� PostgreSQL �����主��������������並����網���繫�����社���� ����被任������ ��� �����������人�常����FAQ� http://www.postgresql.org/files/documentation/faqs/FAQ_DEV.html 1.2) 誰�� PostgreSQL � ��� ���PostgreSQL���人����麼中 央�������麼�屬���� �����---� ������ ������確��� � ������CVS管������工���設 �主������管�工�����PostgreSQL��������PostgreSQL� ���任�人� ��� ���人�社����������� �����就����件�表������� �����PostgreSQL���詳� ��人�常�� (Developer's FAQ) ������ 1.3) PostgreSQL �����麼? PostgreSQL�������� BSD �����許�������使� PostgreSQL���� �以�� PostgreSQL ����代碼��以������就�� ��� ���身������追訴�� 責任���就������������ ��以������ ��就����使��BSD�����容� PostgreSQL ��庫管�系統 �����c�1996-2005�PostgreSQL ������������c�1994-1996 � �大��� �Portions copyright (c) 1996-2005,PostgreSQL Global Development Group Portions Copyright (c) 1994-6 Regents of the University of California� �許�任���使�����修�����������������任�費�� 並��� 簽署� ��������������������段以����段�� ��������� �Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.� �任������ �大������ 使�� ������������任���人����� ���������� ����伴�����������失�責任��使� �大� �已�建�����失���������� �IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.� � �大��確��任����������������������������� �裡����份���������������� �� �大���責任��維 ���������強��修����� �THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.� 1.4) PostgreSQL �以�������系統平��� ��說��任���� UNIX �容���系統������ PostgreSQL ������裡���������確測試�平�� PostgreSQL ��以�������微� Windows-NT ���系統�� Win2000 SP4�WinXP � Win2003�已製��������� http://pgfoundry.org/projects/pginstaller�����MSDOS�Windows�業 系統 �Win95�Win98�WinMe�����Cygwin模�����PostgreSQL� ������� Novell Netware 6 ������� http://forge.novell.com ����OS/2(eComStation)������� http://hobbes.nmsu.edu/cgi-bin/h-search?sh=1&button=Search&key=postgre SQL&stype=all&sort=type&dir=%2F ��� 1.5) ���裡��� PostgreSQL� ���覽��� http://www.postgresql.org/ftp/ �������FTP�� ftp://ftp.PostgreSQL.org/pub/ ����� 1.6) ���� PostgreSQL ��麼� PostgreSQL �������� 8.2.3 � �����年����主�������幾���������� 1.7) ���裡���� PostgreSQL ���� PostgreSQL社����件�表��大������幫�����件�表�主�� �� http://www.postgresql.org/community/lists/��������� �General � Bug�件�表����好���� 主��IRC� ����FreeNode(irc.freenode.net)�#postgresql������ �� ����以使� UNIX �� irc���令� ��� irc -c '#postgresql' "$USER" irc.freenode.net ���使���IRC客�端�����網絡��� ��� PostgreSQL �西��� ��(#postgersql-es)���� �� (#postgresql-fr)��樣��� EFNET ����� PostgreSQL �交�� ��� �����������表�� http://techdocs.postgresql.org/companies.php �覽� 1.8) ����交�� BUG � ��� �訪� http://www.postgresql.org/support/submitbug�填寫 Bug �� �表� �����樣��訪� ftp �� ftp://ftp.PostgreSQL.org/pub/ 檢������PostgreSQL ������ ��使� Bug �交表� ����� PostgreSQL �件�表� Bug �常��以������ * ��交�容���� Bug ���� Bug ��� � * ��交�容���已�� Bug 並�已�� � TODO ���任��表� * ��交� Bug 已������被修�� * ��交� Bug 已修�������� ���������� * ���交����詳細���� + ��系統 + PostgreSQL �� + ��� Bug �測試�� + 調試�� + 調試�蹤輸� * ��交�容���� Bug����以�工�� + �建�����並��� ����主������������� + � Bug ����修���被� � TODO ���任��表� 1.9) ����解已�� BUG ��缺���� PostgreSQL ������� SQL:2003 ��������� TODO �表��解已� Bug �表��缺������������ ���� �������常���以������� * 該��已� � TODO ���任��表� * 該������ ��� �� + ������符� SQL ���������� + 該����大大�� 代碼�������帶��好��微�足��� + 該����������� �� * 該���被� � TODO ���任��表� PostgreSQL �使� Bug �蹤系統�� �������件�表中 ����以��� TODO 任��表總����������工����������實��Bug��������中 ������� �影������Bug�總��快�被修� ���������������修����� CVS �������使��������������������������� 1.10) �� ����������� PostgreSQL ��大�����主��詳細��������� ����測試����� /doc ����註��� $PGHOME/doc�� � ��以���覽 PostgreSQL �����網���http://www.PostgreSQL.org/docs� �����PostgreSQL�������� http://www.postgresql.org/docs/books/awbook.html � http://www.commandprompt.com/ppbook/ � ��大�� PostgreSQL ����購買������������ Korry Douglas 編寫��� http://techdocs.PostgreSQL.org/techdocs/bookreviews.php� ��大��� PostgreSQL ���簡�� � http://techdocs.PostgreSQL.org/ ������ PostgreSQL �大����� � 客�端��令���psql���以 \d �� � ��令��顯示�������符����������使� \? �以顯示������令� ��� web ���������� 1.11) ��該�樣�� SQL � �����述����PostgreSQL����������� Teach Yourself SQL in 21 Days, Second Edition��詳細�紹�網�� http://members.tripod.com/er4ebus/sql/index.htm� ���許�����The Practical SQL Handbook� Bowman, Judith S. 編寫�Addison-Wesley����������� The Complete Reference SQL, Groff 編寫�McGraw-Hill ����� ���網�����好������� * http://www.intermedia.net/support/sql/sqltut.shtm * http://sqlcourse.com. * http://www.w3schools.com/sql/default.asp * http://mysite.verizon.net/Graeme_Birchall/id1.html 1.12)���交����� ������ 詳� ��人�常��� (Developer's FAQ) � 1.13) PostgreSQL �����庫系統�起���� �����好幾種����������� ������� �� �� PostgreSQL ��大�����庫������������� �詢�觸��������������������� ������������������義���繼��������並���� ���������� �� PostgreSQL�����������庫���似������������快�� ����������� �����庫�����������常� +/- 10%��� �� � �������庫�� ��� �����就������������������ �測試��缺����穩�代碼������������ beta 測試���並������� ��顯示���以��穩������������使��������信�� ����������庫������� �� ����件�表�����常大���人������以幫�解決�碰�� 任���������� 總��解決������������庫���並��總�� ��� �決��� �����人����群��������觸使PostgreSQL������� �庫��好������業 ����������以給��給�����人����1.7 ��� �� � ���任�����費������������ � �以�� ����� ���裡� ����代碼������������� ��裡��� BSD������容� 1.14) PostgreSQL �以�������家�������? PostgreSQL 8.0������使���系統������庫����������� 8.0 ��以���� PostgreSQL ��身���������� _________________________________________________________________ ��客�端�� 2.1) ���以��麼��� PostgreSQL ����� PostgreSQL (� ���)���� C ���� C ��������������� ���� ���������� ����好� ����以������������������ ��編���� PHP ��訪� PostgreSQL ����Perl�TCL�Python 以����������� http://gborg.postgresql.org 網��� Drivers/Interfaces ������ 並��� Internet �容��索�� 2.2) ��麼工��以� PostgreSQL �� Web � �� ���紹以��庫������������http://www.webreview.com� �� Web ���PHP ���極好������ http://www.php.net/� �����任����人�� Perl ��� 使� CGI.pm � DBD::Pg � mod_perl � 2.3)PostgreSQL ���形������ ����������人��������� PostgreSQL�GUI �形工����� PostgreSQL社��� ���詳細��表� _________________________________________________________________ 系統管��� 3.1)��樣�� PostgreSQL �� /usr/local/pgsql 以����� ��� configure �� � --prefix �� � 3.2) ��������������� � �����PostgreSQL ��許������� unix ����� TCP/IP ������ � ���修���置�件 postgresql.conf �� listen_addresses�����置�件 $PGDATA/pg_hba.conf ���� ��� ���� host-based ��身份���並���� PostgreSQL������������ � PostgreSQL 伺�������� 3.3) ��樣調���庫��以���好���� ���主����以�� PostgreSQL ���� �詢����� �主���修��詢��以���好���: + �建索����表�����索�� + 使� COPY ��代��� Insert ��� + ���SQL��������以���交������ + ���索���������使� CLUSTER� + ����詢����������使� LIMIT� + 使�� 編���詢�Prepared Query)� + 使� ANALYZE 以��精確���統�� + ��使� VACUUM � pg_autovacuum + ��大���������索�����建索�� 伺����置 �置�件 postgres.conf ����設 置��影����������表��� 管����/��庫伺������/��庫伺�����置� �����解����http://www.varlena.com/varlena/GeneralBits/Tidb its/annotated_conf_e.html � http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html� 硬���� ��硬�����影���覽 http://candle.pha.pa.us/main/writings/pgsql/hw_performance/inde x.html � http://www.powerpostgresql.com/PerfList/� 3.4)PostgreSQL 裡�以���麼樣�調試��� PostgreSQL ����似 log_* �伺���置������詢������統�����工��調試���測試� ��幫�� 3.5) ��麼�試�������Sorry, too many clients�已�太���������� �表示� 已��� � 100 �並�(��)���������� ����修� postgresql.conf �件�� max_connections �� �� postmaster ���併�����修������� postmaster� 3.6)PostgreSQL ���������容 � PostgreSQL �����次������主������ Bug 修�工��� �� 7.4.8 ��� 7.4.9 ��� dump � restore������ ���庫伺�����������������伺����� ��PostgreSQL����該������ �使��主������������快�� ��管�次��������風��PostgreSQL��� ���������修� ��� Bug ����碼������以風�������PostgreSQL社������������ 風��������� 主��������� 7.3 � 7.4��常�修�系統表���表���� ��� ������������ ���������件����容��� �����中 �������dump�/������中 �������reload��主�������� �� 3.7)(使� PostgreSQL )���使��麼��硬� � ����硬�大����容��人�總�����信����硬�質���� ����實���� ECC RAM�帶��� ��������SCSI �硬����質�主�����便�貨��� �� ����好���� PostgreSQL 幾��以���任�硬��� ����� ������ �系統����� 就������ 究��� �硬��� �������件�表����� 硬��置�������� _________________________________________________________________ ���� 4.1) ��������詢���� 幾���������� ��� �����幾����並�� ����詢中 ��確������ �以使� LIMIT ��� �����索�� ORDER BY 中 ��件���PostgreSQL ��就������� 幾���� ��������詢��������������������詢������確����� �� �使�游�(cursor)�FETCH��� �使�以������������� SELECT cols FROM tab ORDER BY random() LIMIT 1 ; 4.2) ����表�索����庫以�����義�����psql裡����詢�令並顯示� ��� �psql�使� \dt �令�顯示��表��義���解 psql 中 ����令�表�使� \? ����� ��以�� psql ��代碼 �件 pgsql/src/bin/psql/describe.c������� psql ����令�輸���� SQL �令�� ��以帶 -E �� �� psql� �樣������ � psql 中 �給���令������實�使�� SQL �詢���PostgreSQL�������容 SQL� INFORMATION SCHEMA ��� � �以��裡������庫���� �系統�����以 pg_ �� �系統表��述�表��義� 使� psql -l �令�以�������庫� ��以�覽�� pgsql/src/tutorial/syscat.source �件�����������庫系統表������SELECT��� 4.3) �������������� �8.0��裡������������容���使� ALTER TABLE ALTER COLUMN TYPE � �以�������以�樣�� BEGIN; ALTER TABLE tab ADD COLUMN new_col new_data_type; UPDATE tab SET new_col = CAST(old_col AS new_data_type); ALTER TABLE tab DROP COLUMN old_col; COMMIT; � ���以使� VACUUM FULL tab �令�使系統����������空�� 4.4) �������表�����庫��大������ �������� ����庫�大尺寸� ����已��� 32TB ���庫� ��表��大尺寸� 32 TB ������大尺寸� 1.6 TB ������大尺寸? 1 GB ��表裡�大��� ��� ��表裡�大���� 250-1600 �������� ��表裡��大索���� ��� ���實�������������������空�������/交������ �實����述�������常�大��系統������大影�� �表��大大� 32 TB ����業 系統����件���麼大����大表��� 1 GB ��件���� � ��件系統大���������� ��� ���大���� 32K ��大��表大���大����以�� ���� �����就����大���2000� ����建索��幸����樣�索����������� ����容��MD5����������索������������� 並���檢索�許���������索� 4.5) ����������件裡���������空�� �� Postgres ��庫�� ������件�����空���������������件�身大�5����空� � �������� 100,000 ���件��������������述� �設 ��串�平��度�20���(Byte)����件�� 2.8 MB�� ������PostgreSQL��庫�件大�� 5.2 MB: 24 ���: ���� �大��� 24 ����: ��������������� + 4 ����: � ��������� ---------------------------------------- 52 ����� PostgreSQL ��� �大�� 8192 ��� (8 KB)��� 8192 ����� ------------------- = 158 �/��� ������ 52 ����� 100000 ��� -------------------- = 633 ��� ������ 146 �/��� 633 ��� * 8192 ��/� = 5,185,536 ���5.2 MB� 索�����麼���������確實��被索������ � �������大� 空� NULL �������� ������空�� 4.6) ��麼���詢�����麼���詢����索�� 並����詢����使�索�����表�大���������並��詢��� ��表�������������索�� ��� �索����起������ ���������表�� ������� ������使�索��PostgreSQL �� ����表�統�����統���以使� VACUUM ANALYZE�� ANALYZE ��� 使�統��������表中 �����就�� �好�������索�� 統���確������� ������������表��容����������� �統�������� 索��常��� ORDER BY ���������大表��次� ������次���常�索����快������ � LIMIT � ORDER BY ����起使��話��常��使�索��� ������表��������� ��� 確信PostgreSQL����使�� ������確��� �以使�SET enable_seqscan TO 'off'�令���� ���� ���次���詢�� 就�以��使���索�����確實�快��� �使���符����� LIKE � ~ ��索����������使�� * �符串������� ����符串��就�說� + LIKE 模���以 % �� � + ~ ���表���模��� 以 ^ �� � * �符串��以�����符�模���� ��� [a-e]� * 大�寫������� ILIKE � ~* ��使�索����以� 4.8 ��述�表��索�� * �� initdb ��� ��� �����置 C locale�� �系統������� C locale �������大�符��麼� ��種����� �以�建����� text_pattern_ops 索���� LIKE �索�� � 8.0 �����中 �����詢������索������������索��常��被������ �� int2, int8 �����索�� 4.7) ��������詢�����樣�估�����詢� �� EXPLAIN ��� � 4.8) ��樣���表���索�大�寫���� ��表������樣��索���大�寫����� ��符 ~ ����表������ ~* ��大�寫�����表�����大�寫��� LIKE �種�� ILIKE� 大�寫��������常寫�� SELECT * FROM tab WHERE lower(col) = 'abc'; �樣���使����索�����以�建����種���使��表��索� : CREATE INDEX tabindex ON tab (lower(col)); ���述索���建�� � UNIQUE �����索����身�容�以� �大�寫����容����� UNIQUE �������容�����大�寫������� ��������� ����種����以使� CHECK ���件��觸����������� 4.9) ����詢裡���樣檢測������� NULL �������確����������� NULL �� � IS NULL � IS NOT NULL 測試������������ SELECT * FROM tab WHERE col IS NULL; ����� NULL ������� ORDER BY �件�使� IS NULL � IS NOT NULL 修飾符��件�� true ���件�� false ���������� 就��� NULL ������������� SELECT * FROM tab ORDER BY (col IS NOT NULL) 4.10) �種�符������麼��� �� ���稱 說� VARCHAR(n) varchar ����大�度���� 符串��足�義�度������ CHAR(n) bpchar ���符串�實����足�義�度��以空� ��� TEXT text ���������������大�度��� BYTEA bytea �������使�NULL�符���許�� "char" char ���符 �系統表�����誤��裡� ������稱� �������種��� "varlena"��������就�說��� ���� ���度��������� ��實����空�����大������ ��������義�����以被�縮���� � ���空������ ����� VARCHAR(n) �������大�度����符串��好�� TEXT ���� ��大�� 1G 左����義���度��符串� CHAR(n) �������度����符串� CHAR(n)�� ���給�����度以空� ��足��足����容�� � VARCHAR(n) ����給�����容� BYTEA ����������尤���� NULL �����������差������ 4.11.1) ��樣�建��������������� PostgreSQL �� SERIAL ���������義�SERIAL������建����������� CREATE TABLE person ( id SERIAL, name TEXT ); ������以�SQL��� CREATE SEQUENCE person_id_seq; CREATE TABLE person ( id INT4 NOT NULL DEFAULT nextval('person_id_seq'), name TEXT ); �� create_sequence ��� ��������������� 4.11.2) ���������������� �種������������ nextval() ����象裡檢索���� SERIAL ��������精確����使� 4.11.1 裡��表����碼�樣�述� new_id = execute("SELECT nextval('person_id_seq')"); execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')"); �樣������詢�使���� new_id 裡����������� person 表����� 注����建� SEQUENCE �象��稱���