PostgreSQL(ݥȥ쥹塼)ˤĤƤ褯Ȥβ(FAQ)

ʸǽ: Sat Sep 22 20:07:41 EDT 2001

ߤΰݻ: Bruce Momjian (pgman@candle.pha.pa.us)

ʸκǿǤ http://www.PostgreSQL.org/docs/faq-english.html Ǹ뤳Ȥ
Ǥޤ

ץåȥۡͭμˤĤƤ:http://www.PostgreSQL.org/users-lounge/
docs/faq.html
˲ޤ

(ʲԤˤ [  ] ȤǰϤǵޤ)
[
        ܸˤĤƤΥϺǸذưޤ

        ܸǤΤʸ ܲ "User's Lounge"  "Collection of FAQs" 
        "Japanese" ȤФΤȤˤޤޤʲΥȤˤ
        ޤ
                http://www.rccm.co.jp/~juk/pgsql/
                http://www.linux.or.jp/JF/

        ˤĤƤŤ(juk@postgresql.jp)ޤǥ᡼Ǥ󤻲
]



                                 Ūʼ                                  

1.1) PostgreSQLȤϲǤ
1.2) PostgreSQLϤɤʤäƤޤ
1.3) PostgreSQLưUnixץåȥۡϡ
1.4) UnixʳΰܿǤǻȤΤϡ
1.5) PostgreSQLϤɤǤޤ
1.6) ݡȤϤɤǼޤ
1.7) ǿǤϤɤǤ
1.8) ɤΤ褦ʸ񤬤ޤ
1.9) ΤΥХ̵̤ǽϤɤäƸĤޤ
1.10) SQLϤɤгؤ٤ޤ
1.11) PostgreSQL2000ǯ(Y2K)бƤޤ
1.12) ȯˤϤɤΤ褦˻äޤ
1.13) ХݡȤϤɤΤ褦ȯޤ
1.14) ¾DBMSΤ٤PostgreSQLϤɤʤΤǤ

                         桼饤Ȥμ                          

2.1) PostgreSQL  ODBC ɥ饤СϤޤ
2.2) PostgreSQL  Web ڡϢȤˤϤɤʥġ뤬ޤ
2.3) PostgreSQL ˥ե롦桼󥿡եϤޤݡȥ
ͥ졼ϡ䤤碌쥤󥿡եϡ
2.4) PostgreSQL ̿ˤϤɤʸ줬Ȥޤ

                                 μ                                  

3.1) ɤΤ褦ˤ /usr/local/pgsql ʳξ˥󥹥ȡǤޤ
3.2) postmaster 餻ȡ Bad System Call ȤפȤΥå
ФޤʤǤ
3.3) postmaster 餻褦Ȥȡ IpcMemoryCreate 顼ФޤʤǤ

3.4) postmaster餻褦Ȥȡ IpcSemaphoreCreate 顼Фޤʤ

3.5) ¾ΥۥȤ鼫ʬPostgreSQLǡ١ؤΥɤˤϤɤޤ

3.6) ʤ¾Υޥ󤫤鼫ʬΥǡ١³ǤʤΤǤ礦
3.7) ɤǽ뤿ˤϡǡ١󥸥ɤΤ褦Ĵ
Ǥ
3.8) ɤΤ褦ʥǥХǽȤޤ
3.9) ³褦ȤȤ 'Sorry, too many clients' ФޤʤǤ
3.10) ʬΥǡ١ǥ쥯ȥˤ pg_sorttemp.XXX եϲǤ


                                 μ                                  

4.1) ʤƥϥޤ侮եեޥåȤǺ𤹤ΤǤ
4.2) Хʥꡦ̾參Ȥθ̩ʰ㤤ϲǤ
4.3) ǽοԤΤߤ select ˤϤɤޤ
4.4) ơ֥䤽¾ξΥꥹȤ psql ǸˤϤɤޤ
4.5) ơ֥뤫κϤɤΤ褦ˤޤ
4.6) ԡơ֥롢ǡ١κ祵ϡ
4.7) Ūʥƥȥե뤫ǡ¸ˤϡǡ١Υǥ
̤ϤɤΤ餤ɬפǤ
4.8) ǡ١줿ơ֥䥤ǥåɤΤ褦ˤƸĤФ
ޤ
4.9) 䤤碌٤ǥåȤäƤͻҤޤ󡣤ʤǤ

4.10) 䤤碌֥ƥޥɤΤ褦䤤碌ɾ뤫򸫤ˤϤɤ
ޤ
4.11) R-tree ǥåȤϲǤ
4.12) Ū䤤碌ŬȤϲǤ
4.13) ɽǤθʸȾʸȤ̤ʤɽϤɤΤ褦˼
ޤʸȾʸȤ̤ʤΤΥǥåϤɤΤ褦˻
ޤ
4.14) 䤤碌ǡեɤ NULL Ǥ뤳Ȥ򸡽ФˤϤɤޤ

4.15) ʸΤ줾ΰ㤤ϲǤ
4.16.1) (serial)ưʬեɤϤɤΤ褦ˤĤޤ
4.16.2) SERIALǡͤϡɤޤ
4.16.3) ¾Υ桼Ȥζ֤򤱤뤿ˤϡcurrval()  nextval() ϻȤ
ۤ褤ΤǤ礦
4.17) OID ȤϲǤ TID ȤϲǤ
4.18) PostgreSQL ǻȤ뤤ĤѸΰ̣ϲǤ
4.19) 顼å "ERROR: Memory exhausted in AllocSetAlloc()"ФΤϤ
Ǥ
4.20) ɤΥС PostgreSQL 餻ƤΤĴ٤ˤϤɤޤ
4.21) 顼֥Ȥǡinvalid large obj descriptorȽФޤʤ

4.22) ߤλ郎ǥեȤȤʤ褦ϤɤΤ褦ˤĤޤ
4.23) ʤINȤ䤤碌ȤƤ٤ΤǤ
4.24) (outer join)ϤɤΤ褦˼¸ޤ?
4.25) ʣΥǡ١Ȥ䤤碌ϤɤΤ褦ˤФǤޤ

                        PostgreSQLγĥˤĤƤμ                         

5.1) ʬǽ񤤤桼ؿ psql Ǽ¹ԤȥפƤޤ
ϤʤǤ
5.2) PostgreSQL Ѥ˽񤤤äŨʿؿ󶡤ƥץȤ
׸ΤǤ
5.3) ץ֤ CδؿϤɤΤ褦˽񤭤ޤ
5.4) եѹޤƥѥ뤷ƤѲʤΤϤʤ
Ǥ


                                 Ūʼ                                  

1.1) PostgreSQL ȤϲǤ

PostgreSQL ϼ DBMS ѤΥץȥפǤä POSTGRES ǡ١
ƥβǤǤPostgreSQL  POSTGRES ζϤʥǡǥ˭٤ʥǡ
()ݻʤ顢POSTGRES ǻȤ줿 PostQuel 䤤碌򡢳
ĥ SQL Υ֥åȤ֤ƤޤPostgreSQL ̵Ǵʥ
ѤǤޤ

PostgreSQL γȯϡPostgreSQL ȯ᡼󥰥ꥹȤ˻äƤ륤󥿡ͥ
ȾγȯԥǤ٤ƹԤʤƤޤߤκĹ Marc G. Fournier ( 
scrappy@PostgreSQL.org )Ǥ(ʲ˻äλޤ)ߡΥब
PostgreSQL ȯΤ٤ƤݤߤƤޤ

Postgres95-1.01 濴ŪʳȯԤ Andrew Yu  Jolly Chen Ǥ¾
ο͡ΥɤΰܿƥȡǥХӡɤ˻äޤ
PostgreSQL ɤǤ POSTGRES ϥե˥إС쥤ˤ
ơ Michael Stonebraker λشΤȡ¿γ´ܿΥץ
Ϥˤޤ

С쥤ˤ뤳ΥեȥΤȤ̾ Postgres ǤSQL εǽ
ɲä줿 1995 ǯˤ̾ Postgres95 ѹ졢1996 ǯνˤ̾
 PostgreSQL ѹޤ Post-Gres-Q-L.(ݥ - 쥹 - 塼 - 
) ȯޤ

1.2) PostgreSQL ϤɤʤäƤޤ

PostgreSQL ϲ˽ޤ
    [
        ʸϱѸǤͤȤơʸʻǺܤޤ
    ]


PostgreSQL Data Base Management System

Portions Copyright (c) 1996-2000, 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.

        POSTGRESQL ǡ١ƥ

        ʬŪ (c) 1996-2001, PostgreSQLݳȯ
        ʬŪ (c) 1994-6 ե˥ܹ


          ܥեȥӤʸ켰Ͼ嵭ɽȡʸ
        Ӥ³ĤƤʣźդƤ¤ˤ
        ơѡʣդεĤ򡢤ʤŪǤä⡢̵
        ǤƱս̵˹Ԥʤ뤳Ȥ򤳤ǧޤ
        
          ե˥ؤϡʤԤˤƤ⡢פβ
        ޤࡢľŪŪ̡뤤ɬŪˤ餺
        »ˤĤơȤե˥ؤ»ˤĤ
        ƤȤƤ⡢ڤǤ餤ޤ
        
          ե˥ؤϡŪˤۤݾڤȡŪ
        Ŭ˴ؤƤϤȤꡢ˸¤餺ʤݾڤ
        뤳ȤޤʲѰդ줿եȥϡ֤Τޤޡפ
        ܸȤե˥ؤϤݻٱ硢ɤ
        뤤Ͻ̳餤ޤ

    [
        ˴ؤʸϾ嵭αѸˤɽǤܸϤޤ
        ͤǤ
    ]


1.3) PostgreSQL ưĶϡ

Ԥ PostgreSQL ΥѥȥƥȤ򼡤ΥץåȥۡǹԤʤޤ
(ΤΤĤϥѥ gcc ɬפǤ)

ŪˡǶUnixߴץåȥۡʤPostgreSQLϤ餻ϤǤ
꡼λǼºݤ˥ƥȤԤʤäȤ𤬤ʤ줿ץåȥۡˤ
Ƥϥ󥹥ȡ󤷤Ƥޤ

1.4) UnixʳΰܿǤǻȤΤϡ

饤

MS Windows ץåȥۡǡlibpq C 饤֥ꡢpsqlȤ¾Υ󥿡
եϥѥǽǡХʥ꡼ޤξ硢饤Ȥ MS
Windows 餻ơTCP/IP ͳǥݡȤƤ Unix ץåȥۡ
륵Ф̿ޤ

Win32 libpq 饤֥ psql 뤿ˡwin31.mak ۤ˴ޤޤƤޤ
PostgreSQL ODBC 饤ȤȤ̿Ǥޤ



ߡCygnus Unix/NT ܿ饤֥ Cygwin ȤäơPostgreSQL ǡ١
Ф Windows NT  Win2k DzƯƤޤۤ˴ޤޤpgsql/doc/
FAQ_MSWIN뤤ϥ֥Ȥˤ MS Windows FAQ Microsoft 
Υץåȥۡ˰ܿײϤޤ


[

Win32ͥƥ(Win32 Native version)

          Windows-Native С & 饤ȥѥåƣˤ
        ݻƤޤ
            http://hp.vector.co.jp/authors/VA023283/PostgreSQL.html
        (Windows-Native Server&Client Package for PostgreSQL by Hiroshi Saito)
            http://hp.vector.co.jp/authors/VA023283/PostgreSQLe.html


]

1.5) PostgreSQL ϤɤǤޤ

PostgreSQL θ anonymous ftp ȤǤ
   
   
  ftp://ftp.PostgreSQL.org/pub/

ߥ顼ȤˤĤƤϡ桹Υᥤ Web ڡ
    [:

        ʲܤΥߥ顼ȤǤ:

       Japan: ftp://mirror.nucba.ac.jp/mirror/postgresql/pub/
       Japan: ftp://ring.ip-kyoto.ad.jp/pub/misc/db/postgresql/
       Japan: ftp://ring.crl.go.jp/pub/misc/db/postgresql/
       Japan: ftp://ring.saitama-u.ac.jp/pub/misc/db/postgresql/
       Japan: ftp://ring.astem.or.jp/pub/misc/db/postgresql/
       Japan: ftp://ring.exp.fujixerox.co.jp/pub/misc/db/postgresql/
       Japan: ftp://ring.jah.ne.jp/pub/misc/db/postgresql/
       Japan: ftp://ring.etl.go.jp.jp/pub/misc/db/postgresql/
       Japan: ftp://ring.asahi-net.or.jp/pub/misc/db/postgresql/
       Japan: ftp://ring.so-net.ne.jp/pub/misc/db/postgresql/
       Japan: ftp://ring.aist.go.jp/pub/misc/db/postgresql/
    ]


1.6) ݡȤϤɤǼޤ

פʥ᡼󥰡ꥹȤ: pgsql-general@PostgreSQL.orgǤPostgreSQL ˴ؤ
뤳ȤǤеǤޤΥꥹȤؤλäΤϡŻҥ᡼ʸ(Subject
ԤǤϤޤ)˼ΣԤ񤤤ơ
        subscribe
        end

pgsql-general-request@PostgreSQL.org äƲ

ǤΥ᡼󥰡ꥹȤ⤢ޤΥꥹȤؤλä "ʸ"ˡ
        subscribe
        end
Ƚ񤤤 pgsql-general-digest-request@PostgreSQL.org Żҥ᡼äƲ


ǤϡᥤꥹȤǼå 30k ί˥
ǥꥹȤΥСդޤ

ХݡѤΥ᡼󥰥ꥹȤ⤢ޤΥꥹȤؤλä "ʸ"Ȥä
ˡ bugs-request@PostgreSQL.org Żҥ᡼äƲ

ȯԤεΤΥ᡼󥰥ꥹȤѤǤޤΥꥹȤؤλäŻҥ
ʸˡ

        subscribe
        end

Ƚ񤤤ơpgsql-hackers-request@PostgreSQL.orgŻҥ᡼äƲ

PostgreSQL ˤĤƤäȾܤΤꤿС postgreSQL WWWۡڡ
餿ɤޤ
   
    http://www.PostgreSQL.org

EFNet  #PostgreSQL Ȥ IRC ͥ⤢ޤ unix ޥɤirc -c '#
PostgreSQL' "$USER" irc.phoenix.net/ Ȥޤ

    [:
          1999ǯ723PostgreSQL桼(ˤۤ ݤȤ줹 桼 )άJPUGΩޤ
        JPUG ȿǡPostgreSQLѤã߶ϤξǤ
        β̵ǤѶŪʹ׸αĤƤޤܤϡJPUGWeb :
                http://www.postgresql.jp/
        ϿǽȤʤäƤޤ
          1990ǯ椴ꡢݥȥ쥹ܸ᡼󥰡ꥹȤа ãפ󤬼ŤƤޤܺ٤ϡ
                http://www.sra.co.jp/people/t-ishii/PostgreSQL/ML/info.html
        ֤򡢤來ꤵpgsql-jp MLƥ
                http://datula.mio.org/~iwakiri/pgsql_jp/
        Ǹ뤳ȤǤޤ
        ]


ѥݡȲҤΥꥹȤhttp://www.postgresql.org/users-lounge/
commercial-support.htmlˤޤ
    [:
         ܤǤϡSRA Inc. ץ󥷥ƥ ˤƾѥݡȤԤʤƤޤ
        ߥ饯롦ʥå  "Miracle Linux for PostgreSQL" ȥݡȤ
        Ϥޤ
    ]


1.7) ǿǤϤɤǤ

PostgreSQL κǿǤϥС 7.2 Ǥ

桹ϡ4˥᥸㡼꡼ԤʤȤײ褷Ƥޤ

1.8) ɤΤ褦ʸ񤬤ޤ

դˡĤΥޥ˥奢ȥ饤󡦥ޥ˥奢(ޥ˥奢롦ڡ)
ӤĤξʥƥ꤬ޤޤޤ/doc ǥ쥯ȥޤ
ޥ˥奢ϡ http://www.PostgreSQL.org/users-lounge/docs/ ǥ饤Ǥ
Ǥޤ

PostgreSQL ܤ⤢ޤ http://www.PostgreSQL.org/docs/awbook.html

    [:
        ܥݥȥ쥹桼PostgreSQL Bookʬʲǡ
        Ȥʹ档
    ]


psql ⡢黻ҡؿ󡢤¾ξ򤪸롢Ĥ餷
\d ޥɤޤ

桹 Web Ȥˤϡäʸ񤬤ޤ

1.9) ΤΥХ̵̤ǽϤɤäƸĤޤ

PostgreSQLϳĥ줿SQL-92Υ֥åȤ򥵥ݡȤޤ桹Υڡ TODO
ꥹȤˡΤΥХǽ侭ײˤĤƤεҤޤ

1.10) SQL Ϥɤгؤ٤ޤ

http://www.PostgreSQL.org/docs/awbook.html ˤPostgreSQLܤ SQL 򶵤Ƥ
ޤ

餷ؽˤϡ http://w3.one.net/~jhoffman/sqltut.htm  http://
ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM.

Ȥޤ¾ˡ "Teach Yourself SQL in 21 Days, Second Edition"  
http://members.tripod.com/er4ebus/sql/index.htm ˤޤ

¿Υ桼ˡ The Practical SQL Handbook, Bowman Judith S. et al.,
Addison-Wesley ɾǤ¾ˡThe Complete Reference SQL, Groff et al.,
McGraw-Hill Τ褦ʤΤ⤢ޤ

    [:
         аã׻ˤܸλʸξҲڡ
                http://www.SRA.co.jp/people/t-ishii/PostgreSQL/doc-jp/index.html
        ޤ
         ƣľʸΡֽ鿴ԸΣģ߷硦ӣѣ绲ͽҲפΥʡ
                http://www.shonan.ne.jp/~nkon/ipsql/books_SQL.html
        ޤ
         ѱѻΡPostgreSQLܸޥ˥奢
                http://www.net-newbie.com/
        Ǥϥ饤ޥ˥奢θǤޤ
         ݻ׻UNIX ǡ١
                http://www.wakhok.ac.jp/DB/DB.html
        ϥ饤ɤळȤǤޤ
    ]

1.11) PostgreSQL2000ǯ(Y2K)бƤޤ

бƤޤ2000ǯդ⡢2000ǯդ⡢ñ˰
ޤ

1.12) ȯˤϤɤΤ褦˻äޤ

ޤǽ()ˡǿΥɤ桹 Web Ȥۤ˴ޤ
Ƥ PostgreSQL Developersʸɤߤޤܤˡpgsql-hackers 
pgsql-patches ᡼󥰡ꥹȤ(subscribe)ޤܤˡʼΥѥ
pgsql-patchesȯޤ褽ͤäȤοãPostgreSQL CVS
֤˥ߥåȤ븢¤äƤޤΤ줾οãιʼʥѥå
ȯΤǡߥߥåȤʤäƤãϤɤդΤѤǤ
桹餬ߥåȤѥåϹʼǤȳοƤޤ

1.13) ХݡȤϤɤΤ褦ȯޤ

"bug-template" եιܤơpgsql-bugs@PostgreSQL.orgäƲ


 http://postgreSQL.orgˤǿ FAQ åƲ

Ʊ ftp  ftp://ftp.postgreSQL.org/pub/ǡäȿС
 PostgreSQL 뤤ϥѥå򤵤ƤߤƲ

1.14) ¾DBMSΤ٤PostgreSQLϤɤʤΤǤ

եȥפˡˤϤĤޤǽǽȿȥݡȤȲ
Ǥ

ǽ(Features)
    PostgreSQLϡȥ󥶥䤤碌ȥꥬӥ塼䳰
    Ȥ䡢ơ줿åʤɡ絬ϾDBMSĵǽۤȤ
    äƤޤPostgreSQLϡˡ桼Ѿޥ- С
    ƥ󥷥ʤɡDBMS碌ʤǽ򤤤Ĥ碌Ƥ
    ޤλ(foreign key referential integrity)å
    ƥ󥷥򸺤餹γ (outer concurrency control)ϻ
    碌ޤ
   
   
ǽ(Performance)
    PostgreSQLĤΥ⡼ɤޤ̤fsync⡼ɤϡOSå夷
    ꡢøŸꤷȤΤˡȥ󥶥󤬴λ
    ǥ˽񤭹ߡ٤ƤΥǡǥ¸ޤΥ⡼ɤǤϡ
    ۤȤɤξѥǡ١٤ʤޤʬŪͳȤơ
    ѤΥǡ١ˤϤΤ褦ݼŪʥǥ񤭹ߤǥեȤȤ
    ƤΤʤȤȤ⤢ޤ no-fsync⡼ɤǡ̡PostgreSQL
    Ͼѥǡ١®ʤޤʤ顢OSΥåǥǡ
    ˲뤫⤷ޤ󡣲桹ϡ֥⡼ɤȯǡ줬
    椯ȡfsync ⡼ɤۤǽˤ뤳ȤʤOSå夹
    30ޤǤΥǡݤƤ褦ˤʤޤ
   
    MySQLʤɤòǡ١ƥˤ٤ơPostgreSQL
    ٤Τϡȥ󥶥ˤ륪Сإåɤ뤫Ǥ
    MySQLˤϾ嵭Features˼褦ʵǽϤޤäޤ󡣲桹ϡ
    PostgreSQL˽ȵǽȤ߹ߤʤ⡢䤨ץե顼˳ݤ
    ꥽ɤϤꤷơǽβ³ƤޤPostgreSQL 
    MySQL ȤӤƤ Web ڡ http://openacs.org/
    why-not-mysql.html ˤޤ
   
    PostgreSQLϡUnixץư뤳Ȥˤ桼³ޤʣ
    ΥХåɡץåʤǡХåեͭ
    ޤޥCPUǤϡñʣΥХåɤ򤽤줾CPU餻뤳
    Ǥޤ
   
   
(Reliability)
    桹ϡDBMSο⤯ʤƤϤβ̵ͤȤ򤷤Ƥޤʬ
    ȤơꤷɤХǾˤƤ꡼褦˶ФƤޤ
    줾Υ꡼ϾʤȤ1ʾΥ١ƥȤԤʤޤ
    Υ꡼򤬡ǤȤưꤷǤʥ꡼Ǥ뤳Ȥʪä
    ޤʬǤϡ¾Υǡ١٤Ƥ½ʤȤ˼
    Ƥޤ
ݡ(Support)
    桹Υ᡼󥰥ꥹȤϡ뤤ʤˤĤƤؤμ
    Ƥ롢ȯԤ桼礭ʽޤ󶡤Ƥޤ桹β
    ݾڤ뤳ȤϤǤޤ󤬡ѥǡ١ǤäƤ˲褵櫓
    Ϥޤ󡣳ȯԤ䡢桼ߥ˥ƥޥ˥奢ࡢˡ
    ɤʤɤľܥǤ뤳Ȥäơ PostgreSQLΥݡȤϡ¾DBMS
    ݡȤͥ줿ΤȤʤäƤޤ˾ơξѥݡ
    Ȥʤɤ⤢ޤʥݡFAQܤˡ
(Price)
    PostgreSQLѤϡѤǤѤǤ⡢٤̵Ǥ嵭˼ƤBSD
    λѵ˳ʤ¤ꡢPostgreSQLΥɤ̵ǾʤȤ
    ळȤǤޤ
   
   

                         桼饤Ȥμ                          

2.1) PostgreSQL Τ ODBC ɥ饤СϤޤ

PsqlODBC  OpenLink ODBC Ĥ ODBC ɥ饤СѲǽǤ

PsqlODBC  PostgreSQL ۤ˴ޤޤƤޤˤĤƤΤ˾ܺ٤ʾ
ftp://ftp.PostgreSQL.org/pub/odbc/ Ǥޤ

    [:
        PsqlODBC  ܸѥåҲ͵(kataoka@interwiz.koganei.tokyo.jp)ޤ:
        http://www.interwiz.koganei.tokyo.jp/software/PsqlODBC/index.html
    ]


OpenLink ODBC  http://www.openlinksw.com/ǤޤɸŪ ODBC 
ȡեȥǻȤޤΤǡٱ礷Ƥ뤹٤ƤΥץåȥۡ(Win,
Mac, Unix, VMS) PostgreSQL  ODBC ѤǤޤ

֤ϡʼΥݡȤɬפʿ͡äƤȻפޤե꡼
ǤϤĤǤǽΤ褦Ǥϡpostgres95@openlink.co.ukˤꤤ
ޤ Programmer's Guide  ODBC ξϤ⤴

2.2) PostgreSQL  Web ڡϢȤˤϤɤʥġ뤬ޤ

ǡ١΢˻ Web ڡˤĤƤ餷Ҳ𤬡
http://www.webtools.com ˤޤ

http://www.phone.net/home/mwm/hotlist/ˤ⡢⤦Ĥޤ

Web ؤγĥΤˤϡPHP ۤ󥿡եȤʤäƤޤhttp://
www.php.net/ˤޤ
    [:
          PHP˴ؤܸξϡ2000ǯ419ȯ­PHP桼Υ
                http://www.php.gr.jp/
        뤤ϡע वΥ
                http://www.cityfujisawa.ne.jp/~louis/apps/phpfi/index.html
        ˤʤޤȤƤޤ
           ˤ줿PHP/FIܸѥå͡ʿͤμФPHP3.0.7ŬѤޤ
        ߤPHPJ-DEVˤơ
                http://php.jpnnet.com/
        ƣ濴˥ޥХȳĥȤƺľ졢ǿǤPHP-3.0.18бƤޤ
           餵ϡPHP4 Ѥܸطγĥ⥸塼
                ftp://night.fminn.nagano.nagano.jp/php4/
        ѰդƲäƤޤ
          ܲȤǹݲMLΩ夬äƤޤ
    ]


ʣʾ硢¿οͤ Perl 󥿡ե CGI.pm Ȥޤ
    [:
        WDB ϡWeb  DataBase ؤ Perl  Interface Ǥ
        wdb-p95 ؤΥ󥯤ڤƤޤäƤޤ餯Perl DBI ͳ DBD::Pg ѤǽȻפޤ
        ߡWDBI Ȥ̾ˤʤäƤ
                http://www.egroups.com/list/wdb-users/
        ȡWDB̾ΤޤޤΤ
                http://www.i-con.dk/wdb/
        ȤޤηаޤϤ褯狼ޤ
    ]


2.3) PostgreSQL ˥ե롦桼󥿡եϤޤݡȡ
ͥ졼ϡ䤤碌ؤΥ󥿡եϡ

pgaccess ȸƤФ餷ե롦桼󥿡եꡢ
ۤȶ˽в٤ޤPgaccess ˤϥݡȡͥ졼⤢ޤWeb ڡ
http://www.flex.ro/pgaccessǤ

ecpg Ȥ C Τ SQL 䤤碌쥤󥿡ե⤢ޤ


2.4) PostgreSQL ̿ˤϤɤʸ줬Ȥޤ

ʲΤΤޤ:
   
   
  C (libpq)
  C++ (libpq++)
  C (ecpg)
  Java (jdbc)
  Perl (perl5)
  ODBC (odbc)
  Python (PyGreSQL)
  TCL (libpgtcl)
  C Easy API (libpgeasy)
  HTML (PHP from http://www.php.net)

    [
        rubyκԤǤޤĤ 椭Ҥ(matz@ZetaBITS.COM)ȡޤĤ (ematsu@pfu.co.jp)
        ruby  PostgreSQL 󥿡եޤߤΰݻƣ Ф󤬤Ƥޤ
                http://webclub.kcom.ne.jp/mb/noborus/ruby/
        PgBash    󤬺ä bash  PostgreSQL 󥿡եǤ
                http://www.psn.co.jp/PostgreSQL/pgbash/
        Bashޥɥ饤postgres 䤤碌Ǥޤ
        Perl Υ⥸塼ϸŤ餢 Pg  DBI ɥ饤Ф DBD::Pg Ȥꡢ
         Edmund Mergl ˤΤ CPAN Ȥˤޤ
    ]



                                 μ                                  

3.1) ɤΤ褦ˤ /usr/local/pgsql ʳξ˥󥹥ȡǤޤ

ñˡϡ configure 餻Ȥ --prefix ץꤹ뤳ȤǤ


3.2) postmaster 餻ȡBad System Call ȤפȤΥå
ФޤʤǤ

ޤޤ꤬ͤޤޤǽˤʤΥͥ System V IPC γ
ĥ󥹥ȡ뤵Ƥ뤫ǧƸƤPostgreSQL ϥͥˤ
ͭ꡼ȥޥեΥݡȤɬפȤޤ

3.3) postmaster 餻褦ȤȡIpcMemoryCreate 顼ФޤʤǤ


ͥ뤬ͭ꡼ˤʤäƤʤäǤʤСͥ
ƻȤ붦ͭ꡼礭礭ꤹɬפޤŪ礭
ȤäƤ륢ƥpostmaster 餻ȤꤹХåեοȥ
åɥץ˰¸ޤۤȤɤΥƥǤϡͤΥХåե
ΤޤޤǡʤȤ1MBɬפǤ PostgreSQL Administrator's Gide ˶ͭ
꡼ȥޥեˤĤƤξξܺ٤ޤ

3.4) postmaster餻褦ȤȡIpcSemaphoreCreate 顼Фޤʤ


⤷顼åIpcSemaphoreCreate: semget failed (No space left on
device)ǤСͥ뤬ʬʥޥեȤ褦˹Ƥޤ
PostgresŪʥХåɥץ˰ĤΥޥեɬפȤޤȤꤢ
βpostmasterưȤˡХåɥץο꾯ʤ
¤򤹤뤳ȤǤͤ32꾮ʿΥѥ᡼-NǻȤޤ깱
ŪʲϡͥSEMMNS  SEMMNI ѥ᡼䤹ȤǤ

⤷顼åʤˤ¾ΤΤǤСͥιǤޤäޥ
ΥݡȤ򤷤Ƥʤ⤷ޤ PostgreSQL Administrator's Gide ˶ͭ
꡼ȥޥեˤĤƤξξܺ٤ޤ

3.5) ¾ΥۥȤ鼫ʬ PostgreSQL ǡ١ؤΥɤˤϤɤ


ͤǤϡPostgreSQL  unix ɥᥤ󥽥åȤȤޥ󤫤³
ޤpostmaster ư -i եåä$PGDATA/pg_hba.conf ե
ŬڤľơۥȼƳǧڤȤʤ¾Υޥ󤫤³Ǥ
Ǥ礦ˤTCP/IP³ǽˤʤޤ

ǽʥޥե٤Υǡ١˥åȤ
ޤ

3.6) ¾Υޥ󤫤鼫ʬΥǡ١³ǤʤΤϤʤǤ

Ǥϥޥ󤫤 unix ɥᥤΥå³ޤ
TCP/IP ³ǽˤˤ postmaster  -i ץdzϤƤơpgsql/
data/pg_hba.conf եŬڤʥۥȤεܤɲäƤ뤳ȤǧƤ


3.7) ɤǽ뤿ˤϡǡ١󥸥ɤΤ褦Ĵ
Ǥ

Τ˥ǥå䤤碌®٤ޤEXPLAINޥɤ PostgreSQL 
ɤΤ褦ˤʤ䤤碌Ƥ뤫򸫤뤳ȤǤơɤΥ
ǥåȤƤ뤫򸫤뤳ȤǤޤ

⤷ INSERT ¿ѤƤϡCOPY ޥɤȤä礭ʥХåǤ
ԤʤȤƤƲϡINSERT ̡˹Ԥʤäȹ®Ǥ
ˡBEGIN WORK/COMMIT Υȥ󥶥󡦥֥å̵ʸϡ鼫Ȥ
줾Υȥ󥶥äƤȸʤޤĤʸĤΥȥ
󥶥󡦥֥åǹԤʤȤͤƲˤȥ󥶥
ΥСإåɤޤޤ礭ʥǡѹԤʤݤϥǥå
ٳơľȤͤƤߤƲ

塼˥󥰤Υץ󤬤Ĥޤpostmaster  -o -F ץǵ
ư뤳Ȥˤäơfsync() ̵ˤ뤳ȤǤޤˤäơƥȥ
 fsync() ǥǥ򹹿Τߤᤵޤ

postmaster -B ץȤäƥХåɡץˤȤ붦ͭ
Хåե礭뤳ȤǤޤ⤷Υѥ᡼⤯ȡ
ͥζͭ꡼֤ͤۤƤޤä postmaster ʤ
ʤǤ礦ͤǤϡ줾ΥХåե礭 8K ǡХåե 64
Ǥ

Хåɤ -S ץȤäơ줾ΥХåɡץŪ
¤ؤˤäƻȤ꡼κ祵䤹ȤǤޤ -S 
ϥХñ̤ǡͤ 512 (ʤ512K)Ǥ

ޤCLUSTER ޥɤȤäơơ֥Υǡ򥤥ǥå˹碌뤿
롼ײ뤳ȤǤޤܤϡ饤ޥ˥奢 CLUSTER 򸫤Ʋ


3.8) ɤΤ褦ʥǥХǽȤޤ

PostgreSQL ϡǥХΤ˰̣Τ롢־𤹤뤤Ĥεǽ
ޤ

ޤ--enable-cassert ץ configure 餻ޤƥѥ
뤳Ȥˤꡢ assert() ХåɤοĽƻ뤷ͽ
̤Ȥȥץߤ褦ˤʤޤ

postmaster  postgres ξǤĤΥǥХץѤǤޤ
Τ褦 postmaster ưȤϤĤǤ⡢ɸϤȥ顼Ϥ
ե褦ˤƤ뤳ȤΤƲ
        cd /usr/local/pgsql
        ./bin/postmaster >server.log 2>&1 &

ˤ PostgreSQL κǾΥǥ쥯ȥ server.log ե뤬֤ޤ
ΥեϥС䥨顼ˤĤͭѤʾޤߤޤ
Postmaster Ϲ˾ܺ٤ʾ𤹤뤿 -d ץޤ -d 
ץϡǥХ٥ꤷޤ⤤ǥХ٥Ǥϡ礭ʥե
뤳ȤդʤƤϤʤޤ

⤷postmasteräƤʤСpostgresХåɤ򥳥ޥɹԤ餻
뤳ȤǤľSQLʸ򥿥פ뤳ȤǤޤΤ꤫ϡǥХŪ
ȤᤷޤߥǤϤʤԤ䤤碌νˤʤ뤳Ȥ
դƤ⤷ǥХܥƥѥ뤷ƤСǥХå
äƲƤ뤫򸫤뤳ȤǤޤpostmaster Хåɤ򳫻Ϥ
櫓ǤϤʤΤǡΩʴĶäƤΤǤϤʤåХåɤȤ
꤬ʣ뤳ȤϤޤ

⤷postmasteräƤС륦ɥpsql򳫻Ϥȡpsql ǻȤ
 postgresץPIDĤޤǥХåȤäpostgresPID˥å
(attach)ޤǥХå椫֥졼ݥȤ򥻥åȤpsql䤤
ȯԤޤǥХΤpostgresưϡPGOPTIONS="-W n" 
Ǥ줫顢psql 򳫻Ϥޤˤꡢn óϤ٤餻ϤʤΤǡ
ХåǥåƻưɤäƸ뤳ȤǤޤ

postgreSQL ץˤϡǥХǽ¬ˤȤƤΩ -s -A -t Υ
ץ󤬤ޤ

ȤؿɤΤ餤¹Ի֤򿩤äƤ뤫򸫤뤿ˡץե󥰡
ץեդˤǥѥ뤹뤳ȤǽǤΥХåɤΥץե
롦ե pgsql/data/base/dbname ǥ쥯ȥ˳ǼǤ礦饤
ȤΥץեϥ饤Ȥθԥǥ쥯ȥ֤Ǥ礦

3.9) ³褦ȤȤ 'Sorry, too many clients' ФޤʤǤ

postmasterƱưǤХåɥץФ¿䤹ɬפ
ޤ

κץ32ץǤ-NŬڤͤˤpostmasterƵư
뤫postgresql.conf 뤳Ȥˤäơͤ䤹ȤǤޤ
ιǤ-NϺ1024ޤǤޤ⤷äɬפǤinclude/
config.hMAXBACKENDSäƹۤޤ⤷˾ʤconfigure
--with-maxbackendsؤȤäơ-NδͤǤޤ

⤷-N  32礭ΤǤС-B64礭ͤä
ƤϤʤʤ-B ϾʤȤ -N 2ܤϤʤƤϤʤ餺餯ǹǽ
˾ʤФ礭ͤɬפʤϤǤХåɥץ򤿤ˤ
ȡUnixͥ빽ѥ᡼䤹Ȥɬפˤʤ뤫⤷ޤ
󡣶ͭ꡼֥åκ(SHMMAX)ޥեκ(SEMMNSSEMMNI)
ץκ(NPROC)桼κץ(MAXUPRC)եκ
(NFILENINODE ǧ˴ޤޤޤ PostgreSQL˵ХåɤΥ
¤ƤΤϡƥΥ꥽Ȥ̤ƤޤȤ򤱤
Ǥ

6.5ΥСPostgreSQLǤϥХåɤκ64Ǥѹ
ˤϡinclude/storage/sinvaladt.hMaxBackendId˺ƹۤɬ
פǤ

3.10) ʬΥǡ١ǥ쥯ȥˤ pg_sorttemp.XXXեϲǤ


䤤碌¹ԥ⥸塼ˤä줿ŪʥեǤ㤨С⤷
ORDER BY ˥Хåɤ -S ѥ᡼ǵĤͤ礭ʥ
ڡȤκݤɬפȤȡ줿ǡݻ뤿˰Ūʥե
뤬Ĥޤ

ŪʥեϼưŪ˾äϤǤ⤷ȤǥХå
ɤå夷ƤޤȤϤʤޤ󡣤ΤȤХåɤҤȤĤ
äƤʤСpg_tempNNN.NNեäƤפǤ
    [
         SYSLOGD ͳǥϤˤϡޤconfigure  --enable-syslog 
        դ餻塢ѥȥ󥹥ȡԤʤޤ
         ˡsyslog.conf  local0.*  ꤷ(Ķѿѹǽ)
        syslogd  HUP ʥäƽƤޤơ
        $PGDATA/pg_options  syslog=2 äơ postmaster  -S 
        ץդˤƥХ⡼ɤǵưޤ(С 7.1  
        pg_options  postgresql.conf ˤʤäƤޤ)
    ]



                                 μ                                  

4.1) ʤƥϥޤ侮եեޥåȤǺ𤹤ΤǤ

ΤƲPostgreSQL  postmaster ץ餻桼
ΥȤޤpostgres psql ˤ SET ޥɤꡢǡ
񼰤Ǥޤ򤢤ʤĶ˹碌ꤷƲ

4.2) Хʥꡦ̾參Ȥθ̩ʰ㤤ϲǤ

ܽҤϡ饤ޥ˥奢 DECLARE 򸫤Ʋ

4.3) ǽοԤΤߤ SELECT ˤϤɤޤ

饤ޥ˥奢FETCH򸫤Ƥ뤤ϡSELECT ... LIMIT....Ȥ
ƤߤƲ

ȤߤΤϺǽοԤǤ⡢٤Ƥ䤤碌ɾʤƤϤʤ
⤷ޤORDER BY ä䤤碌ͤƤߤƲ⤷ORDER BY
˹äǥåȤ PostgreSQL׵ᤵ줿ǽοԤɾ
뤫⤷ޤ󤬡ǤʤСPostgreSQL ϰտޤԤޤǤ٤Ƥ
ԤɾʤФʤʤ⤷ޤ

4.4) ơ֥䤽¾ξΥꥹȤ psql ǸˤϤɤޤ

psqlΥɤȤƽ񤫤줿 pgsql/src/bin/psql/describe.c եɤळ
ȤǤˤϡpsqlΥХåå女ޥɤˤϤΤSQL
ޥɤޤޤƤޤ psql  -E ץĤƵưСͿ
ɤ¹Ԥ뤿䤤碌Ϥޤ

4.5) ơ֥뤫κϤɤΤ褦ˤޤ

ALTER TABLE DROP COLUMN ϥݡȤƤޤ󤬡ˤޤ:
        SELECT ...  -- ʳ򤹤٤򤷤ޤ
        INTO TABLE new_table
        FROM old_table;
        DROP TABLE old_table;
        ALTER TABLE new_table RENAME TO old_table;
[ɲä ALTER TABLE ADD COLUMN ǹԤޤ]

4.6) ԡơ֥롢ǡ١κ祵ϡ

¤ϰʲΤȤǤ
ǡ١κ祵?       ̵ (60GB Υǡ١¸ߤޤ)
ơ֥κ祵?           16TB
Ԥκ祵?                 7.1ʹߤ̵
եɤκ祵?         7.1ʹߤ1GB
ơ֥Ǥκ?       ̵
ơ֥Ǥκ祫?     ηˤ250-1600
ơ֥Ǥκ祤ǥ? ̵

󡢤ϼºݤ̵¤ǤϤʤǥ̤ȥ꡼䥹åץڡ
礭ˤ¤ޤǽϤͤȤΤۤ礭ʻ
ޤ

ơ֥륵16TBϥڥ졼ƥ󥰥ƥˤեΥݡ
ɬפȤޤ󡣵ʥơ֥ʣ1GBΥեʬ¸ޤΤǡ
ե륷ƥ¤ϽפǤϤޤ

ǥեȤΥ֥å32kˤȺơ֥륵Ⱥ祫Ȥ
ޤ

4.7) Ūʥƥȥե뤫ǡ¸ˤϡǡ١Υǥ
̤ϤɤΤ餤ɬפǤ

PostgreSQL Υǡ١¸ˤϡ̤Υե6.5ܤΥǥ
ɬפȤޤ

ƹԤĤ 300,000ԤΥեͤƤߤޤ礦Υե
Ǥ 2.4MB ǤΥǡޤ PostgreSQL ǡ١ե礭ϼ
褦14MBȸѤ뤳ȤǤޤ
    36 bytes: ƹԤΥإå()
   + 8 bytes: 4ХȤĤ(int)ե
   + 4 bytes: ڡΥåץؤΥݥ
   ----------------------------------------
   48 bytes per row

   PostgreSQL Υǡڡ 8192Х(8KB)ʤΤ:

   8192 bytes per page
   -------------------   =  171 rows per database page (ڤ夲)
     48 bytes per row

   300000 data rows
   --------------------  =  1755 database pages
      171 rows per page
 
1755 database pages * 8192 bytes per page  =  14,376,960 bytes (14MB)

ǥåϡۤɤΥХإåɤ׵ᤷޤ󤬡ǥåդ
ǡޤʾ塢ʤ礭ʤޤ

4.8) ǡ١줿ơ֥䥤ǥåɤΤ褦ˤƸĤФ
ޤ

psql ˤϤʥХåå塦ޥɤꡢɽޤ
Хåå塦ޥɤμ򸫤ˤ \? ȤäƲ

ޤpgsql/src/tutorial/syscat.source ե餻ƤߤƲϡ
 SELECT ʸˤɬפʾǡ١Υƥࡦơ֥뤫Ф
㼨Ƥޤ

4.9) 䤤碌٤ǥåȤäƤͻҤޤ󡣤ʤǤ


PostgreSQL ׾ưŪˤݼ餷ޤ׾򹹿뤿ˤϡ
VACUUM 餻ʤƤϤʤޤ׾󤬹줿ϡ֥ƥޥ
֥˲Ԥ뤫ΤäơǥåȤ٤ηɤޤ
֥ƥޥϥơ֥뤬Ϣ³®Ǥϥǥ
ȤʤȤˤղ

κŬפΤVACUUM ANALYZEȤޤVACUUM ANALYZEʣʣ
(multi-join)䤤碌ΤڤǤΤǡ֥ƥޥϤ줾Υơ
֥뤫֤ԤοѤ뤳ȤǤηӤޤХå
ɤϤ켫ȤǤפݻʤΤǡŪˤ򽸤뤿ˤ VACUUM
ANALYZE 餻ʤƤϤʤޤ

̡ǥå ORDER BY ΤˤϻȤޤ󡣥ʥ
ϤȤƤ٤Τǡ缡³ŪȤϡʥơ
򥤥ǥå󤹤®Ǥ

LIKE 뤤 ~ Τ褦ʥ磻ɥɱ黻(wild-card operators)ȤȤ
γϤʸλϤʬ˸ꤵƤȤˤΤߡǥåȤ
櫓ǡǥåȤˤϡLIKE Ǥ%ǻϤʤ褦
ơޤ~(ɽ)^ ǻϤ褦ˤ٤Ǥ [Ū˥
ǥåȤˤ SET enable_seqscan = off ¹Ԥޤ]

4.10) 䤤碌֥ƥޥɤΤ褦䤤碌ɾΤ򸫤ˤϤ
ޤ

饤ޥ˥奢 EXPLAIN 򸫤Ʋ

4.11) R-tree ǥåȤϲǤ

R-tree ǥå϶Ūʥǡ˥ǥåդ뤿˻Ȥޤϥ
奤ǥåǤϰϤθǤޤ󡣤ޤB-tree ǥåǤϡ
ǤϰϤθǤޤR-tree ǥåǤ¿Υǡ򰷤
ޤȤС⤷ R-tree ǥå point °դ뤳ȤǤ
ȥƥϡĹ˰Ϥޤ줿򤹤٤򤹤פȤ褦䤤碌
ˡΨɤޤ

R-Tree ߷פθŵȤʤ븢Ҥʸ:

Guttman, A. "R-Trees: A Dynamic Index Structure for Spatial Searching." Proc of
the 1984 ACM SIGMOD Int'l Conf on Mgmt of Data, 45-57.

ʸϡStonebraker  "Readings in Database Systems" Ǥ夲
Ƥޤ

    [
          ü¼R-TreeطʸҲ𤷤ĺޤ
        ܸ Postgres ML Υ֤ "Subject: [postgres95 801] spatial data structures" 
                http://www.sra.co.jp/people/t-ishii/PostgreSQL/mhonarc/pgsql-jp/1996Oct/msg00007.html
        
    ]


ȹߤ R-Tree ǥݥꥴܥåǤޤŪˤR-Tree Ϥäȹ
褦ˤĥǤޤ¼ŪˤϡR-Tree γĥˤϤäȤ
ȤɬפǤơߡ桹ϤɤΤ褦ˤ뤫ˤĤƤʸäƤ


    [
          󥿡Ҳ¿֥ȤؤγĥǤܤϡ
        http://www.interwiz.koganei.tokyo.jp/software/geometric/index.html
        
    ]


4.12) Ū䤤碌ŬȤϲǤ

GEQO ⥸塼ϡΥơ֥礹ȤˡŪ르ꥺ(GA)
碌®ޤˤꡢߤĤ֤õԤʤʤƤ⡢礭ʷ
(join queries)򰷤ȤǤ褦ˤʤޤ

4.13) ɽǤθʸȾʸȤ̤ʤɽϤɤΤ褦˼
ޤʸȾʸȤ̤ʤΤΥǥåϤɤΤ褦˻
ޤ

~黻ҤɽȹԤʤ~* ʸȾʸ̤ʤ
(case-insensitive)ɽȹԤޤ PostgreSQL 7.1 ʹߤǤϡʸȾ
ʸ̤ʤ LIKE 黻Ҥ ILIKE Ȥޤ

ʸȾʸ̤ʤӼΤ褦ɽǤ롧
    SELECT *
    FROM tab
    WHERE lower(col) = 'abc'
    
ɸ।ǥåǤϻȤ줺ʤ顢⤷ؿǥåäʤ餽
줬ȤǤ礦
   CREATE INDEX tabindex on tab (lower(col));
   
        WHERE lower(textfield) LIKE lower(pattern)

4.14) 䤤碌ǡեɤ NULL Ǥ뤳Ȥ򸡽ФˤϤɤޤ


IS NULLΥ IS NOT NULL ǻƤߤƲ

4.15) ͡ʸΤ줾ΰ㤤ϲǤ

Type            Internal Name   Notes
--------------------------------------------------
"char"          char            1 character
CHAR(#)         bpchar          ꤵ줿ĹȤʤ褦˶򤬵ͤ
VARCHAR(#)      varchar         Ĺξ¤̵ƥ
TEXT            text            ŤϺĹˤ
BYTEA           bytea           ĹΥХ

̾ˤܤˤΤϡƥࡦĴ٤Ȥ䡢顼å
ȤǤ

嵭ηΤΣĤη "varlena" Ǥ(ʤǥκǽΣХ
ȤǡĹǡθ˼ºݤΥǡ³ޤ)Τ褦˼ºݤζ֤
줿礭⾯礭ʤޤΥǡTOASTˤ갵̤
줿ʣԤϤä¸줿ꤷơǥζ֤ϻפä꾮ʤ


4.16.1) (serial)ưʬեɤϤɤΤ褦ˤĤޤ

PostgreSQL  SERIAL ǡ򥵥ݡȤޤ֤ȥǥåư
ޤȤС
        CREATE TABLE person ( 
                id   SERIAL, 
                name TEXT 
        );
ϼưŪ˼Τ褦ޤ:
        CREATE SEQUENCE person_id_seq;
        CREATE TABLE person ( 
                id   INT4 NOT NULL DEFAULT nextval('person_id_seq'),
                name TEXT 
        );
        CREATE UNIQUE INDEX person_id_key ON person ( id );
֤ˤĤƤΤäȾܤϡ饤ޥ˥奢 create_sequence 


ޤƹԤOIDեɤͤȤƻȤȤǤޤʤ顢⤷
ǡ١פƤɤɬפϡOID¸뤿
pg_dump  -oץȤޤϡCOPY WITH OIDSץȤɬפ
ޤ Bruce Momjian (http://www.PostgreSQL.org/docs/aw_pgsql_book)
Numbering RowsξϤˤꤢޤ

4.16.2) SERIALǡͤϡɤޤ

ҤȤĤˡϡnextval() ؿȤäƤͤ(before) SEQUENCE 
֥Ȥ鼡 SERIAL ͤФ줫ºݤ򤹤뤳ȤǤ 
4.16.1 ǻȤäơ֥ȤȤȡΤ褦ˤʤޤ
        $newSerialID = nextval('person_id_seq');
        INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
ơ$newSerialID ¸ͤ¾䤤碌(ȤСperson 
֥Ф볰(foreign key)Τ褦)ȤȤ褤Ǥ礦ưŪ˺
SEQUENCE֥Ȥ̾ϡ<table>_<serialcolumn>_seq Τ褦ˤʤꡢΤ
table  serialcolumn Ϥ줾ơ֥̾SERIAL̾Ǥ

뤤ϡͿ줿SERIALͤ򡢤줬ͤȤ줿(after)
currval() ؿȤäƼФȤǤޤȤС
        INSERT INTO person (name) VALUES ('Blaise Pascal');
        $newID = currval('person_id_seq');
ǸˡINSERTʸ֤OIDȤäơͤߤĤ뤳ȤǤޤ
ϺǤܿ㤤Ǥ礦PerlDBI Edmund Mergl κä DBD::Pg
⥸塼ȤС$sth->execute() θ $sth->{pg_oid_status} ͳƤ
OID ͤȤ褦ˤ뤳ȤϤǤޤ

4.16.3) ¾Υ桼Ȥζ֤򤱤뤿ˤϡcurrval()  nextval() ϻȤ
ۤ褤ΤǤ礦

Хåɤ˽Τǡ֤ˤʤ뤳Ȥͭޤ

4.17) OID ȤϲǤ TID ȤϲǤ

OID Ȥϰդι ID Ф PostgreSQL ǤPostgreSQL ǤĤ
٤ƤιԤϰդ OID ޤinitdb ȯ OID Ϥ٤ 16384
(backend/access/transam.h )꾮ͤǤinitdb Τ٤Ƥ OID (桼
)ϤʾͤˤʤޤǤϡ餹٤Ƥ OIDϰĤΥǡ֥
ǡ١αޤ餺PostgreSQL 󥹥ȥ졼ΤǰդǤ

PostgreSQL ϥơ֥֤ιԤӤĤ뤿ˡΥƥơ֥ OID 
Ȥޤ OID Υ桼ιԤ̤뤿ǻȤ뤳Ȥ
ޤOID ͤ¸뤿ˤ OID ˻ȤȤ򾩤ޤ®
뤿 OID եɤ˥ǥå뤳ȤǤޤ OID ϡ
Υǡ١ǻȤΰ褫顢ƤοԤ˳ƤޤOID 
¾βѤ뤤ϸ OID ơ֥Ȱ˥ԡΤʤ顢Ǥ
ʤϤޤ
        CREATE TABLE new (old_oid oid, mycol int);
        SELECT old_oid, mycol INTO new FROM old;
        COPY new TO '/tmp/pgtable';
        DELETE FROM new;
        COPY new WITH OIDS FROM '/tmp/pgtable';


OID ϡ4ХȤȤ¸ƤΤǡ40ۤȰƤޤǤ
ï⤳줬𤷤ƤͤϤޤǤʤˤ¤
Ȥײ褷Ƥޤ

TID ʪԤ򤽤Υ֥åȥեåͤǼ̤뤿˻ȤޤTID
ϹԤ줿ƥɤѤޤ TID ϡʪԤؤ
˥ǥåܤǻȤޤ

4.18) PostgreSQL ǻȤ뤤ĤѸΰ̣ϲǤ

ĤΥɤŤʸˤϡ줾ʬǤäȰŪ
ȤѸ줬ȤƤޤ
   
   
  ơ֥(table)ط(relation)饹(class)
  (row)쥳(record)åץ(tuple)
  (column)ե(field)°(attribute)
  (retrieve)(select)
  ִ(replace)(update)
  ɲ(append)(insert)
  OID, Ϣ(serial value)
  ݡ(portal), (cursor)
  ΰѿ(range variable)ơ֥̾(table name)ơ֥̾(table alias)

Ūʥǡ١ѸΥꥹȤϡ http://www.comptechnews.com/~reaster/
dbdesign.html ǸĤޤ

4.19) 顼å "ERROR: Memory exhausted in AllocSetAlloc()"ФΤϤ
Ǥ

⤷7.1 ŤС򤪻ȤξϡåץǡȤˤäƤ
ǤǤ礦ȡƥβۥ꡼ƻȤ̤ƤޤäƤ
ǽ뤫ͥ뤬꥽ˤĤƤͤ㤹ǽ
ޤ postmaster ưˤƤߤƲ
        ulimit -d 262144
        limit datasize 256m

ˤäơɤ餫ҤȤĤǤ礦ϥץΥǡ
¤⤯ꤷ֤䤤碌뤹褦ˤʤǤ礦
ޥɤϸԤΥץȡΥޥɤ餻˺ƤΥ֥ץ
ˤĤŬѤޤХåɤȤƤ¿Υǡ֤SQL 饤
Ȥ꤬³ƤΤǤС饤Ȥ򳫻ϤˤƤߤ


4.20) ɤΥС PostgreSQL 餻Ƥ뤫Ĵ٤ˤϤɤޤ

psql  select version(); 򥿥פޤ

4.21) 顼֥Ȥinvalid large obj descriptor ޤ
ʤǤ礦

顼֥򤹤ȤϡBEGIN WORKCOMMITդɬפ
ޤʤlo_open ... lo_closeϤ߹ߤޤ

ߤϡPostgreSQLΥȥ󥶥ΥߥåȻ˥顼֥ȡϥ
Ĥ뤳Ȥˤꡢlo_openޥɤλľ˶Ū˥롼¹Ԥޤ
Τᡢǽ˥ϥɥФƲ򤷤褦Ȥȡinvalid large obj
descriptor(顼֥ȤεһҤ)Ȥʤޤǡ⤷ȥ
ȤΤ˺ȡʾʤȤۤȤɤλ֡ƯƤɤ
åФΤǤ

⤷ODBCΤ褦ʥ饤ȥ󥿡ե򤪻Ȥʤ顢auto-commit off
ꤹɬפ뤫⤷ޤ

4.22) ߤλ郎ǥեȤȤʤ褦ϤɤΤ褦ˤĤޤ

now()Ȥޤ:
        CREATE TABLE test (x int, modtime timestamp DEFAULT now() );

4.23) ʤINȤ䤤碌ȤƤ٤ΤǤ

ߡ䤤碌γƹԤˤĤ䤤碌η̤֤˥󤹤뤳Ȥ
ꡢ䤤碌䤤碌˷礷Ƥޤ̤INEXISTS֤
뤳ȤǤ
        SELECT *
        FROM tab
        WHERE col1 IN (SELECT col2 FROM TAB2)
֤ơ
        SELECT *
        FROM tab
        WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2)
Ȥޤ¤ϾΥ꡼ľȻפäƤޤ

4.24) (outer join)ϤɤΤ褦˼¸ޤ?

PostgreSQL 7.1 ʹߤǤSQLɸ๽ʸȤ(祤)򥵥ݡȤ
ޤˡ꤬2Ĥޤ
SELECT *
 FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
뤤
SELECT *
 FROM t1 LEFT OUTER JOIN t2 USING (col);
ξħŪ䤤碌Ǥ t1.col  t2.col ȷ礷ơt1 η礵ʤ
(t2 Ȱפʤä)֤ƤޤRIGHT  t2 η礵ʤä
äǤ礦FULL ϡפԤ t1  t2 Ϸ礵ʤäԤ
֤Ǥ礦OUTER Ȥդϥץ LEFT, RIGHT, ޤ FULL ʤɤη
ꤵƤޤΥ꡼Ǥϳ(outer join)UNION  NOT IN
Ȥäƥߥ졼ȤǤޤȤСtab1  tab2 礹Ȥϡ䤤
碌ĤΥơ֥礷ޤ
        SELECT tab1.col1, tab2.col2
        FROM tab1, tab2
        WHERE tab1.col1 = tab2.col1
UNION ALL
        SELECT tab1.col1, NULL
        FROM tab1
        WHERE tab1.col1 NOT IN (SELECT tab2.col1 FROM tab2)
        ORDER BY col1

4.25) ʣΥǡ١Ȥ䤤碌ϤɤΤ褦ˤФǤޤ

(current)ơǡ١ؤ䤤碌ˡϤޤ󡣤ȤΤ
PostgreSQLǡ١ͤΥƥ५ɤ߹िǡˤϡ
Τդ򤹤ˤǡ١ۤ䤤碌򤹤뤹٤ޤ


󡢥饤ȤƱ˰ۤʤʣΥǡ١³Ƥˤ
ޡ뤳ȤϤǤޤ


                        PostgreSQLγĥˤĤƤμ                         

5.1) ʬǽ񤤤桼ؿ psql Ǽ¹ԤȥפƤޤ
ϤʤǤ

Ͽȹͤޤޤǽˡ桼ؿñȤΥƥȥ
ˤƻƤߤƲ

5.2) PostgreSQL Ѥ˽񤤤äŨʿؿ󶡤ƥץȤ
׸ΤǤ

ιԤʤäĥpgsql-hackers ᡼󥰡ꥹȤäƤ
ơ椯椯Ϥĥ contrib/ ֥ǥ쥯ȥ뤳ȤˤʤǤ
礦

5.3) ץ֤ CδؿϤɤΤ褦˽񤭤ޤ

ŪˤϲǽǤˤϵˤ̯פޤΤǡԤΤޤǤ̤ï
äȤޤ

5.4) եѹޤƥѥ뤷ƤѲʤΤϤʤ
Ǥ

Ĥ Makefile 󥯥롼ɡեФŬڤʰ¸طäƤ
make clean 򤷤Ƥ⤦ make ԤʤʤƤϤʤޤ󡣤⤷GCC
򤪻ȤǤ configure  --enable-depend ץȤäơѥ
¸طưŪĴ٤뤳ȤǤޤ

[
  ܸǤˤĤƤϰʲ̤Ǥ

  ǽ:   2001ǯ1210
  :       ¼  (Jun Kuwamura <juk@postgresql.jp>)

  FAQκˤ궨Ϥ򤷤Ƥä(ɾΤάƤޤ):

                 ̭(Minoru Tanaka <Tanaka.Minoru@keiken.co.jp>)
                а ã(Tatsuo Ishii <t-ishii@sra.co.jp>)
                ƣ ο(Tomohito Saitoh <tomos@elelab.nsc.co.jp>)
                Ͼ  ȥ(Hajime Baba <baba@kusastro.kyoto-u.ac.jp>)
                 칬(Kazuyuki Okamoto <kokamoto@itg.hitachi.co.jp>)
                 (Shoichi Kosuge <s-kosuge@str.hitachi.co.jp>)
                 Ƿ(Yoshiyuki Yamashita <dica@eurus.dti.ne.jp>)
                 Ϻ(Sintaro Sakai <s_sakai@mxn.mesh.ne.jp>)
                 (Masami Ogoshi <ogochan@zetabits.com>)
                 ӹ(Toshiyuki Ishikawa <tosiyuki@gol.com>)
                 й(Shigehiro Honda <fwif0083@mb.infoweb.ne.jp>)
                 (Jun Sese <sesejun@linet.gr.jp>)
                ë ѹ(Hidetaka Kamiya <hkamiya@catvmics.ne.jp>)

  Ϥᡢݥȥ쥹˴ؤ˭٤ܸݥȥ쥹᡼󥰥ꥹȡ
  ΤääƤ줿 JF(Linux Japanese FAQ Mailing List)ץȡ¾
  ľܤ뤤ϴŪˤäƤ뤹٤ƤΥץ󥽡ߥ˥ƥ
  ˴դޤ


  ܸǤΤʸϡʲ⤿ɤޤ
        http://www.rccm.co.jp/~juk/pgsql/(FAQ PostgreSQL ˤĤƤ褯)
        http://www.linux.or.jp/JF/(PostgreSQL-FAQ.j)
        http://www.sra.co.jp/people/t-ishii/PostgreSQL/doc-jp/

  ʤ˴ؤ뤴ո(juk@postgresql.jp)ޤǤ󤻲
]