PostgreSQL FAQ

 ģ ¥ : 1998 5 7

ѱ  :  ( honest@hitel.net )

ֽŹ postgreSQL Ʈ, http://postgreSQL.org    ֽϴ.

 õ    Ǿ ֽϴ.
http://postgreSQL.org/docs/faq-linux.shtml

Irix  õ    Ǿ ֽϴ.
http://postgreSQL.org/docs/faq-irix.shtml

------------------------------------------------------------------------

 :

  1. Ϲ 

       1. PostgreSQL  Դϱ??
       2. PostgreSQL  𿡼   ֽϱ?
       3. PostgreSQL  𿡼   ?
       4. PostgreSQL  ۱  ˴ϱ?
       5. PostgreSQL   
       6. ֽŹ PostgreSQL
       7. PostgreSQL   ֽϱ?
       8. PostgreSQL     ֽϱ?
       9. PostgreSQL  SQL    մϱ?
      10. PostgreSQL   ϴ Ÿ̽ PostgreSQL
             ֳ?
      11. PostgreSQL   ODBC ̹ ֽϱ?
      12.  PostgreSQL  Ű δ  
          ֽϱ?
      13. PostgreSQL  GUI (graphical user interface)  ֳ? Ʈ
          ʷʹ¿?  Ǿ ̽ ֽϱ?
      14.  ؾ SQL    ֳ?
      15. PostgreSQL    ִ α׷   
          ֽϱ?

  2. ġ/  

       1. initdb   ʽϴ.
       2. postmaster  Ű ϸ   ޽ ɴϴ.
          "FindBackend: could not find a backend to execute..."
          "postmaster: could not find backend to execute..."
       3. ý ޸(,)  Ҽ(.), ׸ ¥   ȥϴ 
          ϴ.
       4. /usr/local/pgsql  ٸ  PostgreSQL  ġϷ 
          մϱ?
       5. postmaster  Ű Bad System Call core dumped 
          ޽ ɴϴ.
       6. postmaster  Ϸ ϸ IpcMemoryCreate  
          ɴϴ.
       7. ҽ ߽ϴ.     ˾
          ϴ  ϴ.
       8.  ؾ ٸ ȣƮ  PostgreSQL  ϴ  
           ֽϱ?
       9. 'root' ڰ Ÿ̽   ϴ.
      10. ̺ ÿ ＼Ϸ ϸ   ׽ϴ. ?
      11.  ̱ ؼ  Ÿ̽  Ʃؾ մϱ?
      12. PostgreSQL       ֽϱ?
      13. 32 ̻ 鿣带 ÿ Ϸ  մϱ?
      14. н ƴ ٸ ÷   ֽϱ?

  3. ۿ  

       1. PostgreSQL  ø  մϱ?
       2.  ϴµ  ϴ.
       3. ü ߰ ִ ۾ ϰ (reliably)   
           ϴ.
       4. PostgreSQL  Ŭ̾Ʈ ø̼   մϱ?
       5. pg_group   ?
       6. ̳ʸ Ŀ Ϲ Ŀ  Ȯ ϱ?
       7. R-tree ε ̸,  ̴ ?
       8. Ʃ ִ ũⰡ  ˴ϱ?
       9. ε    ʴ  ϴ. 
          ׷ϱ?
      10. Խ ˻  ϸ ˴ϱ? ҹڸ  ʰ
          ԰˻ Ϸ?
      11. vacuum  ϴ ߿  ũð Ͼϴ. lock 
           ־ մϱ?
      12.  ĳ   Դϱ?
      13.  ȿ ʵ尡 NULL  ƴ  ˾Ƴ  ֳ?
      14.  Ƽ    óϴ   ֽϱ?
      15. ø ʵ带  ?
      16. Ÿ̽ 丮  pg_psort.XXX ϵ ϱ?
      17. ٸ ǻͿ  Ÿ̽    ?
      18. Ÿ̽  ε ۷̼ ǵǾ ִ 
            ֽϱ?
      19. Ÿӿ  ̸, vacuum   谡 ?
      20. oid  Դϱ? tid  Դϱ?
      21. PostgreSQL  Ǵ    ˰ ͽϴ.
      22. ˰ ϴ  ȭ(Genetic Query Optimization)
          ??
      23. ̺ ÷ Ϸ  մϱ?
      24.  ó  ο츸 SELECT Ϸ  մϱ?
      25.  "time" ̶ ̸ ÷   ?
      26. Ϲ  ڷ Ÿ̽ Ϸ 
          ũ  ʿѰ?

  4. PostgreSQL Ȯ忡  

       1.  Լ  psql  ״ ھ
          ˴ϴ.
       2. NOTICE:PortalHeapMemoryFree: 0x402251d0 not in alloc set! ̶
          ޽ ɴϴ.
       3. PostgreSQL  ο ڷ(types)  Լ  
          ҽϴ.
       4. Ʃ ϴ C Լ   մϱ?

  5. 

       1. ׸Ʈ  ϸ ˴ϱ?

  ------------------------------------------------------------------------

1 : Ϲ 

1.1) PostgreSQL  Դϱ??

PostgreSQL   DBMS (database management system)  Ÿ
POSTGRES DBMS   Դϴ. PostgreSQL   Ÿ 𵨰
ǳ Ÿ ״ ϸ鼭 PostQuel Ǿ Ȯ SQL 
 ü߽ϴ. PostgreSQL  ¥̸ ü ҽ Ǿ
ֽϴ.

PostgreSQL  PostgreSQL  ϸƮ ϴ ͳ
ڵ ӿ  ̷ ֽϴ.  ڵʹ ũ G.
ǪϿ(Marc G. Fournier, scrappy@postgreSQL.org ) Դϴ. ϰ 
   Ͻʽÿ. ν   PostgreSQL   
ϰ ֽϴ.

PostgreSQL 1.01  ڴ ص (Andrew Yu)  þ(Jolly
Chen)̾ϴ. ٸ  , ׽, , ׸ ڵ带
Űµ ߽ϴ. PostgreSQL  Postgres κ ĻǾµ,
Postgres   ڵ ĶϾ  Ŭ Ŭ 극Ŀ 
 Ͽ  п, кλ, ׸  α׷ӵ 
̾ϴ.

Ŭ ߵ   Ʈ  ̸ Postgres ϴ.
1995⿡ SQL  ߰Ǹ鼭 Postgres95  ٲϴ. 1996  ٽ
̸ PostgreSQL  ٲϴ.

1.2) PostgreSQL  𿡼   ֽϱ?

ڵ PostgreSQL   ÷ ϰ ׽Ʈغҽϴ. (
쿡 gcc 2.7.0  ʿ մϴ)

   * aix - IBM on AIX 3.2.5 or 4.x
   * alpha - DEC Alpha AXP on Digital Unix 2.0, 3.2, 4.0
   * BSD44_derived - OSs derived from 4.4-lite BSD (NetBSD, FreeBSD)
   * bsdi - BSD/OS 2.0, 2.01, 2.1, 3.0
   * dgux - DG/UX 5.4R4.11
   * hpux - HP PA-RISC on HP-UX 9.0, 10
   * i386_solaris - i386 Solaris
   * irix5 - SGI MIPS on IRIX 5.3
   * linux - Intel x86 on Linux 2.0 and Linux ELF SPARC on Linux ELF PPC on
     Linux Elf (For non-ELF Linux, see LINUX_ELF below).
   * sco - SCO 3.2v5
   * sparc_solaris - SUN SPARC on Solaris 2.4, 2.5, 2.5.1
   * sunos4 - SUN SPARC on SunOS 4.1.3
   * svr4 - Intel x86 on Intel SVR4 and MIPS
   * ultrix4 - DEC MIPS on Ultrix 4.4

 ÷  ׵ ߰ߵǾϴ.

   * nextstep - Motorola MC68K or Intel x86 on NeXTSTEP 3.2

1.3) PostgreSQL  𿡼   ?

PostgreSQL  ֵ anonymous FTP Ʈ  ϴ :

   * ftp://ftp.postgreSQL.org/pub

  ̷Ʈ ֽϴ :

   * ftp://postgres95.vnet.net/pub/postgres95
   * ftp://ftp.luga.or.at/pub/postgres95
   * ftp://cal011111.student.utwente.nl/pub/postgres95
   * ftp://ftp.uni-trier.de/pub/database/rdbms/postgres/postgres95
   * ftp://rocker.sch.bme.hu

1.4) PostgreSQL  ۱  ˴ϱ?

PostgreSQL  ۱  ϴ.

PostgreSQL Data Base Management System

Copyright (c) 1994-6 Regents of the University of California

 Ʈ   , , ,   ̵ 
, 鵿    Ͽ ˴ϴ.  ۱ װ 
, ׸     纻 Ǿ߸ մϴ.

ĶϾ  ̵ ̵ Ưϵ   쿡 
Ʈ   ν  Ͼ  ظ
Ͽ  迡 ؼ  ĶϾƴ ׷  ɼ
  ޾Ҵٰ  å ʴ´.

ĶϾ  Ư (Postgres) Ư  Ͽ Ǹ  
̶  Ͽ      ʴ´. ⿡ Ǵ
Ʈ "ִ ״" Ǹ ĶϾ  , ,
Ʈ, ,     ǹ .

1.5) PostgreSQL   

UC Berkeley, Ȥ  ڵκ   ϴ.
ڿڵ ¿ ؼ  ̷ϴ.

 ϸƮ questions@postgreSQL.org Դϴ. ׺ 
Ͽ PostgreSQL   ͵ ϱ  Դϴ.  Ѵٸ
 ƴ,     ԷϿ Ʒ e-mail 巹
ʽÿ.

     subscribe
     end

questions-request@postgreSQL.org
 Ʈ ֽϴ.  Ʈ Ϸ
questions-digest-request@postgreSQL.org     ԷϿ
 ֽʽÿ.

     subscribe
     end

  Ʈ ޽  30k     Ʈ
鿡 ϴ.

 ϸƮ ֽϴ.  Ʈ Ϸ   
bugs-request@postgreSQL.org    :

ڵ   ϸƮ ֽϴ.  Ʈ Ϸ
   hackers-request@postgreSQL.org    :

     subscribe
     end

PostgreSQL   ߰ PostgreSQL  WWW Ȩ ã 
ֽϴ.

http://postgreSQL.org

1.6) ֽŹ PostgreSQL

 ֽŹ PostgreSQL 6.3.2 Դϴ.

  ޸    ȹԴϴ.

1.7) PostgreSQL   ֽϱ?

ϷƮ (Illustra Information Technology, ͽ ȸ) 
ϷƮ Ҹ ü- DBMS  Ǹϸ ̰ Postgres 
 Դϴ. ϷƮ PostgreSQL     
 ְ,  ̸ ɵ ϴ. ׸   
ְ ֽϴ. ݸ鿡  . ڼ  Ͻø
sales@illustra.com  ˾ƺ.

1.8) PostgreSQL     ֽϱ?

 Ŵ, Ŵ , ׸ ణ ׽Ʈ  ǿ 
ֽϴ. pgintro, sql, ׸ pgbuiltin Ŵ  Ư ߿մϴ.
pgintro   Ŵ  ֽϴ.

psql  \d ɾ ڷ , Լ, aggregates    
 ֽϴ.

ø̼ ̵ Բ Ʈ׷   ɿ  ټ
   ãƺ  ֽϴ.

1.9) PostgreSQL  SQL    մϱ?

PostgreSQL  SQL-92   մϴ.

1.10) PostgreSQL   ϴ Ÿ̽ PostgreSQL 
  ֳ?

PostgreSQL v1.09  v1.01   ͺ̽ ȣȯ˴ϴ.

  6.3  ׷̵Ϸ    ľ
մϴ.

6.2   6.2.1  ׷̵Ϸ    ľ
մϴ.

6.2  6.2.1  ׷̵   ʾƵ ˴ϴ. 
/migration 丮 ִ   ʽÿ.

1.09   ׷̵Ϸ  dump/reload    ä 1.09
 ׷̵ , 1.09  ڷḦ ϰ װ 6.2.1 ̳ 6.3
÷ մϴ.

1.11) PostgreSQL   ODBC ̹ ֽϱ?

PostODBC  OpenLink ODBC, ̷   ODBS ̹ ֽϴ.

PostODBC  ǿ ԵǾֽϴ. PostODBC  ִ  
PostODBC  ٷ ϸƮ  ֽϴ.

   * postodbc-users@listserv.direct.net
   * postodbc-developers@listserv.direct.net

 Ʈ Ϲ majordomo ϸƮԴϴ.  ּҷ 
   ֽϴ.

   * majordomo@listserv.direct.net

OpenLink ODBC  ſ θ ǰ ֽϴ. http://www.openlinksw.com
 ã  ֽϴ. ̰ PostgreSQL  ǥ ODBC Ŭ̾Ʈ
Ʈ  ǹǷ ڴ PostgreSQL  ϴ 
Ŭ̾Ʈ ÷ PostgreSQL ODBC    ֽϴ.

 Ʈ   ϴ 鿡 ̰ Ǹϰ  
ֽϴ.        Դϴ.
postgres95@openlink.co.uk  ϼ.

1.12)  PostgreSQL  Ű δ   ֽϱ?

 Ÿ̽       ƮԴϴ.

http://www.webtools.com

  ؼ PHP   Ǹ ̽Դϴ. URL 
ϴ.

http://www.php.net

PHP  ܼ ۾ ؼ ſ پϴ.    ۾
ؼ  CGI.pm   ̽ ϱ⵵ մϴ.

WDB  Ͽ  ϴ WWW Ʈ̸   ٿε 
 ֽϴ.

   * http://www.eol.ists.ca/~dunlop/wdb -p95

1.13) PostgreSQL  GUI (graphical user interface)  ֳ? Ʈ
¿?  Ǿ ̽ ֽϱ?

ǿ ԵǾ ִ  pgaccess  Ǹ GUI  ֽϴ. Ʈ
⵵ Բ ֽϴ.

ecpg  ͵ ִµ, C   (embedded) SQL Ǿ
̽Դϴ.  ǿ ԵǾ ֽϴ.

1.14)  ؾ SQL    ֳ?

http://w3.one.net/~jhoffman/sqltut.htm  Ǹ Ʃ丮 ֽϴ. 
ڵ The Practical SQL Handbook, Bowman et al, Addison Wesley 
ȣմϴ.

1.15) PostgreSQL    ִ α׷    ֽϱ?

Ʒ ϴ:

   * C(interfaces/libpq)
   * C++(interfaces/libpq++)
   * Embedded C(interfaces/ecpg)
   * Java(interfaces/jdbc)
   * Perl(interfaces/perl5)
   * ODBC(interfaces/odbc)
   * Python(interfaces/python)
   * TCL(interfaces/libpgtcl)
   * A crude C/4GL(contrib/pginterface)

  ------------------------------------------------------------------------

2 : ġ  

2.1) initdb   ʽϴ.

   * (path)  ϵ    
   * ΰ  Ǿִ Ȯ 
   * ʿ ϵ ڰ postgre  Ǿִ Ȯ 
   * $PGDATA/files  ϵ  ִ, ׸  ϵ ƴ
     Ȯ . ϵ ų ִٸ "gmake install" ɾ
      ε  Դϴ.

2.2) postmaster  Ű ϸ   ޽ ɴϴ.
"FindBackend: could not find a backend to execute..." "postmaster: could
not find backend to execute..."

Ƹ μ ߸Ǿ ̴ϴ. 'postgres'   ȿ
־ մϴ.

2.3) ý ޸(,)  Ҽ(.), ׸ ¥   ȥϴ 
ϴ.

   . PostgreSQL  postmaster μ Ų
   մϴ. ش  ȯ濡 ߼.

2.4) /usr/local/pgsql  ٸ  PostgreSQL  ġϷ 
մϱ?

Makefile.global  Ͽ POSTGRESDIR  ׿ ° ؾ մϴ.
ƴϸ Makefile.custom   POSTGRESDIR װ ϼ.

2.5) postmaster  Ű Bad System Call core dumped  ޽
ɴϴ.

    ֽϴ.  ý V Ȯ Ŀο ν
Ǿ Ȯϼ. PostgreSQL  ޸𸮸 ϱ  Ŀ
ʿ մϴ.

2.6) postmaster  Ϸ ϸ IpcMemoryCreate   ɴϴ.

Ŀο ޸  ߸Ǿ  ֽϴ. ƴϸ Ŀο 밡
޸ ũ⸦ ÷־ մϴ. ʿ ޸  ý
, postmaster     ٸϴ. κ ýۿ 
⺻  ּ 760K  ޸𸮰 ʿմϴ.

2.7) ҽ ߽ϴ.     ˾ ϴ
 ϴ.

ԵǴ ϵ  谡 Makefile  ԵǾ  ʽϴ.
'make clean'  Ű make  ٽ Ű.

2.8)  ؾ ٸ ȣƮ  PostgreSQL  ϴ   
ֽϱ?

⺻ PostgreSQL  ش迡  Ӹ ϰ Ǿ
ֽϴ. postmaster  -i ÷׸ ߰ϰ $PGDATA/pg_hba  ˸°
Ͽ ȣƮ̽  밡ϰ ؾ մϴ.

2.9) 'root' ڰ Ÿ̽   ϴ.

 id  0   (root)  Ÿ̽ ڸ  ȵ˴ϴ.
׷ ϸ ش ڵ Ÿ̽    ˴ϴ. This is a
security precaution because of the ability of any user to dynamically link
object modules into the database engine.

2.10) ̺ ÿ ＼Ϸ ϸ   ׽ϴ. ?

  ʵ Ǿ ִ Ŀζ Ͼ Դϴ.

2.11)  ̱ ؼ  Ÿ̽  Ʃؾ մϱ?

  ִ    ֽϴ. postmaster  ⵿  '-o -F' 
Ͽ fsync()   ϵ   ֽϴ. ̷ ϸ fsync()
 ۵ Ƿ  Ʈ   ũûҸ  ʰ
˴ϴ.

postmaster -B  ϸ 鿣 μ ϴ ޸ 
 þϴ.  Ķ͸ ʹ    μ ۵
ʰų ڱ ׾ϴ.  ϳ ũ 8K ̰,  ʱġ 64
Դϴ.

postgres -S  Ͽ  鿣 μ  ؼ ϴ
޸ ִũ⸦ ø  ֽϴ.  ۴ 1K ṵ̃ ʱġ 512
 Դϴ.

CLUSTER  Ἥ ̺̽ ִ ڷ ε µ ׷
 ֽϴ. ڼ  cluster(1) Ŵ  ϼ.

2.12) PostgreSQL       ֽϱ?

PostgreSQL  뿡   ִ   
ֽϴ.

 configure  -enagle-cassert ɼ Ű assert()  鿣
¸ ϸ  ̻ Ͼ  α׷ ŵϴ.

postmaster  postgres     ɼ   ֽϴ. ù
° postmaster  ⵿ų , Ʒ  ǥ° ǥؿ
αϷ  ȮϽʽÿ.

     cd /usr/local/pgsql
     ./bin/postmaster >server.log 2>&1 &

̷ ϸ PostgreSQL  ֻ 丮 server.log  
Դϴ.    ´ڶ߸ ̳ 鿡  
 ϰ ˴ϴ. postmaster  -d ɼ ϸ  ڼ 
ϵ˴ϴ. -d ɼ   1-3  ڸ ޽ϴ. 
 3 س ſ  α  ״ Ͻʽÿ.

Ʈ׷ 鿣带 Ŀǵο Ų  SQL  ٷ Է
 ֽϴ.   Ҷ ϴ   Դϴ.
ݷ ƴ϶ ๮ڰ   Ÿٴ Ϳ Ͻʽÿ. 
 ɺ Խ ߴٸ Ÿ Ͽ   Ͼ
ִ   ֽϴ. 鿣尡 postmaster κ ۵ ʾұ 
  ȯ ƴϸ locking/鿣 ȣۿ  Ȱ
Ͼ   ֽϴ.  ü ǰ ִ 鿣带
 Ͽ  м  ֽϴ.

Ʈ׷ α׷ -s, -A, -t ɼ  ̰  
 ſ մϴ.

 Լ ð Һϴ ֵ   ֽϴ. 
鿣  ϵ pgsql/data/base/dbname 丮 ̰ ˴ϴ.
Ŭ̾Ʈ    丮 Դϴ.

EXPLAIN  (FAQ  ãƺ)  ϸ PostgreSQL   
 ϴ   ֽϴ.

2.13) 32 ̻ 鿣带 ÿ Ϸ  մϱ?

include/storage/sinvaladt.h  MaxBackendId   ٲټ. δ
MaxBackendId  ڰ   ִ   ȹԴϴ.

2.14) н ƴ ٸ ÷   ֽϱ?

libpq C ̺귯, psql, ׸ ٸ ̽ ̳ʸ MS 
÷ ǵ  ϴ  մϴ. ׷ , Ŭ̾Ʈ
MS  Ǹ ٸ Unix ÷ ư ִ  TCP/IP
    ֽϴ.

ñ׳ʽ Unix/NT  ̺귯  PostgreSQL Ÿ̽ 
NT  Ϸ õ  ־   
ϴ.

  ------------------------------------------------------------------------

3 : PostgreSQL  

3.1) PostgreSQL  ø  մϱ?

մϴ.

3.2)  ϴµ  ϴ.

 PostgreSQL  ý   ֽϴ. 並  
 Insert, Update, Delete  óϱ⿡   ֽϴ.

3.3) ü ߰ ִ ۾ ϰ (reliably)    
ϴ.

ι üý  Ϻϰ ۵մϴ. ̻ ü
  ŵ ˴ϴ.

3.4) PostgreSQL  Ŭ̾Ʈ ø̼   մϱ?

PostgreSQL  C  ȣⰡ ̺귯 ̽   پ
̽ մϴ. /src/interfaces 丮 ʽÿ.

ٸ   ̽ PostgreSQL   WWW Ʈ̵ 
ҽϴ. ڼ  PostgreSQL Ȩ ϼ.

3.5) pg_group   ?

 ڱ׷    ִ  ϴ. pg_group ̺
 insert/update ؾ մϴ. ڸ :

     jolly=> insert into pg_group (groname, grosysid, grolist)
     jolly=> values ('posthackers', '1234', '{5443, 8261}');
     INSERT 548224
     jolly=> grant insert on foo to group posthackers;
     CHANGE
     jolly=>

pg_group  ʵ  ϴ.

   * groname: ׷ Դϴ. ĺ ڷθ Ǿ ־ մϴ. ̳
         ϴ.
   * grosysid: ׷ ̵ int4 Դϴ.  ׷츶 ũؾ մϴ.
   * grolist: ׷쿡  pg_user ̵  int4[] Դϴ.

3.6) ̳ʸ Ŀ Ϲ Ŀ  Ȯ ϱ?

declare  Ŵ  ִ  ϼ.

3.7) R-tree ε ̸,  ̴ ?

r-tree ε  Ÿ εϴµ Դϴ. ؽ ε 
˻ ó  ϴ. B-tree ε 1 ˻ ó 
ֽϴ. 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 ε  ڽ ٷ  ֽϴ. ̷ R-tree
    ڷᵵ ٷ  ֽϴ. δ R-tree  Ȯϴ 
  ̰   ؾϴ    
ϴ.

3.8) Ʃ ִ ũⰡ  ˴ϱ?

Ʃ ũ 8K Ʈ ѵ˴ϴ. ý Ӽ ٸ ϸ Ѵٸ
ϰ ڷḦ ϱ ؼ 8000 Ʈ  ۰ ڷḦ ϴ
 ϴ. 8K   ū ڷḦ  ü ̽ 
.

Ʃ 8K 踦 Ѿ Ƿ 5K ¥ Ʃ 8K   ϰ
˴ϴ.

3.9) ε    ʴ  ϴ.  ׷ϱ?

PostgreSQL  ڵ 踦  ʽϴ. 踦 ϱ ؼ
 'vacuum'  ȣؾ մϴ. 谡 ŵǸ Ƽ
ε   ϰ  Դϴ. Ƽ  ƴϸ 
쿡 (  OR  ִ ) ִ ε   
ִٴ  Ͻʽÿ. Ư÷  ȭ 踦  'vacuum
analyze'  ϼ.

׷ ε  ʴ´ٸ Ƹ  *_ops  ϴ ʵ忡
ε  Դϴ.   CHAR(4) ʵ带   char_ops
ε type_class    ֽϴ.

create_index  Ŵ    Ÿ Ŭ 밡  
ֽϴ. ʵ Ÿ԰ ġؾ .

PostgreSQL    ε  ص ڿ  
ʽϴ.

ORDER BY 꿡 ε  ʽϴ.

3.10) Խ ˻  ϸ ˴ϱ? ҹڸ  ʰ ԰˻
Ϸ?

psql  \do  .

3.11) vacuum  ϴ ߿  ũð Ͼϴ. lock  
־ մϱ?

vacuum Ŵ  .

3.12)  ĳ   Դϱ?

 Type      Internal Name Notes
 CHAR      char          1 character
 CHAR(#)   bpchar        blank padded to the specified fixed length
 VARCHAR(#)varchar       size specifies maximum length, no padding
 TEXT      text          length limited only by maximum tuple length
 BYTEA     bytea         variable-length array of bytes

ο(internal operations)   internal name  ؾ մϴ.

    "varlena" Դϴ( ù  Ʈ ̸, 
Ʈ鿡 ڷḦ ֽϴ). CHAR(#)  ʵ忡 󸶸ŭ ڷᰡ 
  ִġ Ҵմϴ. TEXT, VARCHAR(#), ׸ BYTEA 
̷ ڷḦ ϰ ׶ ణ ϸ . Ȯ
ڸ ϴ  ڷ  ù° ÷  ÷  
Ͼϴ.

3.13)  ȿ ʵ尡 NULL  ƴ  ˾Ƴ  ֳ?

IS NULL  IS NOT NULL  ÷  ˻ϼ.

3.14)  Ƽ    óϴ   ֽϱ?

explain  Ŵ  .

3.15) ø ʵ带  ?

PostgreSQL  ڵ ÷ SERIAL  ϴ  
ʽϴ. ,  ο oid ʵ带 ũ    ֽϴ.
·, Ÿ̽ ϰ ٽ ε ʿ䰡 ִٸ pgdump  -o
ɼ ϰų COPY  WITH OIDS ɼ Ͽ oid   
ֽϴ.

SERIAL   SEQUENCE Լ   ֽϴ. create_sequence
Ŵ  ϼ.

   ۾ ϴ Լ   ֽϴ.

     create table my_oids (f1 int4);
     insert into my_oids values (1);
     create function new_oid () returns int4 as
     'update my_oids set f1 = f1 + 1; select f1 from my_oids; '
     language 'sql';

׸

     create table my_stuff (my_key int4, value text);
     insert into my_stuff values (new_oid(), 'hello');

 Լ     Ͽ ϳ Ʈ ϴ  ٸ
ϳ Ʈ ؼ ᱹ    id  ϰ   ִٴ
 ο νñ ٶϴ.  ռ  Ʈ Ͽ Ǿ
մϴ.

contrib/spi/autoinc.c  ִ autoinc() Ϲ Ʈ Լ ϴ
 ֽϴ.

3.16) Ÿ̽ 丮  pg_psort.XXX ϵ ϱ?

 ϵ  α׷  Ǵ ӽϵԴϴ. 
 ORDER BY  ϱ  ĵǾ ϴ 찡 ִٸ  
  ӽ.

 ǰ ִ ̳ Ʈ ٸ pg_psort.XXX ϵ
ϴ  մϴ.

3.17) ٸ ǻͿ  Ÿ̽    ?

ʱ⼳δ  Į ӽſ  н  ϿḸ
մϴ. TCP/IP  ϰ Ϸ postmaster -i ɼ ϼ.
 ȣƮ pgsql/data/pg_hba Ͽ ߰ؾ մϴ. pg_hba.conf
Ŵ  ϼ.

3.18) Ÿ̽  ε  ǵǾ ִ   
ֽϱ?

psql  ׷  ֱ Ͽ ÷ ϴ  
غ  ֽϴ. \?  Ͽ ɾ ãƺ.

pgsql/src/tutorial/syscat.source ϵ ãƺ. Ÿ̽ ý
̺  ̾Ƴ  select  ʿ  ϰ
ֽϴ.

3.19) Ÿӿ  ̸, vacuum   谡 ?

PostgreSQL  ̻    ʽϴ. ϴ ڵ ΰ
Ǿϴ. ɰ ũ  ϱ ؼϴ.

3.20) oid  Դϱ? tid  Դϱ?

oid  ũ ο id, Ȥ ø ÷ شϴ Դϴ. PostgreSQL
 Ǵ  ο ũ oid   ˴ϴ. initdb  
Ǵ  oid  16384  ۽ϴ(backend/access/transam.h ).
initdb ,  ڿ  Ǵ oid  ׺ ų ũ ˴ϴ.
ʱ⼳ζ  oid  ̺, Ȥ Ÿ̽ ȿ Ӹ ƴ϶
ġ PostgreSQL ü    ˴ϴ.

PostgreSQL   ý ̺ oid    ̺ ο츦
Űµ մϴ. oid  溰  ο츦 ϴµ ̰,
  Դϴ. oid  ϱ ؼ oid ÷Ÿ ϴ
 ٶ մϴ. sql(1) Ŵ  ٸ ÷鿡 
ֽϴ. oid ʵ忡 ε    ＼ մϴ.

oid   Ÿ̽  Ǵ ߾ӿ   ο쿡
Ҵ˴ϴ. oid   ٸ  ٲٰ Ͱų ̺ oid  
ä 纻  ʹٸ      ֽϴ.

     CREATE TABLE new_table (mycol int);
     INSERT INTO new_table SELECT oid, mycol FROM old_table;

tid  Ư  ο츦 ϴµ ̸ ϰ   ϴ.
tid  ο  ٲų ٽ εǸ ǰ, ε Ʈ 
 ο츦 Űµ Դϴ. SQL  ؼ tid   
ϴ.

3.21) PostgreSQL  Ǵ    ˰ ͽϴ.

 ҽڵ    Ϲ   ֽϴ. 
ڸ  ϴ.

   * row, record, tuple
   * attribute, field, column
   * table, class
   * retrieve, select
   * replace, update
   * append, insert
   * oid, serial value
   * portal, cursor
   * range variable, table name, table alias

 ϰ ִ  ִٸ ˷ ֽʽÿ.

3.22) ˰ ϴ  ȭ(Genetic Query Optimization)
??

PosgreSQL  GEQO  ˰(GA) ̿Ͽ  ̺ ϴ
 ȭ  Ǯ  Դϴ. ̰ non-exhaustive search  
ū ̺ ϴ  ϰ ݴϴ.

  ʿϴٸ README.GEQO  ʽÿ. utesch@aut.tu-freiberg.de

3.23) ̺ ÷ Ϸ  մϱ?

ALTER TABLE DROP COLUMN      ϸ ˴ϴ.

     SELECT ... -- Ϸ ÷ ϰ  ÷ 
     ϼ
     INTO TABLE new_table
     FROM old_table;
     DROP TABLE old_table;
     ALTER TABLE new_table RENAME TO old_table;

3.24)  ó  ο츸 SELECT Ϸ  մϱ?

fetch  Ŵ  .

̷ ϴ    ο Ŭ̾Ʈ ۵Ǵ   ̰,
ó  ο츸 Ѵٰ    ü  ó˴ϴ.
ORDER BY   ñ ٶϴ. ü     ĵǱ 
Ϻ ο츸   ִ  ϴ.

3.25)  "time" ̶ ̸ ÷   ?

6.2.1  ANSI-92 ǥ      
߰Ǿϴ.      Դϴ.
ftp.postgresql.org  ִ ġ ϸ  ÷ ̸ "time"   
ֽϴ.

3.26) Ϲ  ڷ Ÿ̽ Ϸ  ũ
 ʿѰ?

300,000  Ǿְ,  ึ     ִ 
 ô.  ũ 2.4 MB Դϴ.  ڷḦ  PostgreSQL
Ÿ̽  ũ     ֽϴ.

40 bytes +  ο  (뷫 ũ)
 8 bytes + 4Ʈ ũ  ʵ  
 4 bytes + Ʃ Ű 
-------- =
52 bytes :  ο  Ʈ

PostgreSQL  Ÿ  ũ 8192 Ʈ̹Ƿ :

8192 bytes per page
-------------------  =  157 rows : Ÿ̽  ο  (ݿø)
 52 bytes per row

300000 data rows
-----------------  =   1911 : ü ڷ 忡 ʿ Ÿ̽  
157 rows per page

1911 database pages * 8192 bytes per page  =  15,654,912 or 15.5MB

ε ̷  带   εǴ ڷḦ
ϹǷ  Ŀ  ֽϴ.

  ------------------------------------------------------------------------

4 : PostgreSQL Ȯϱ

4.1)  Լ  psql  ״ ھ ˴ϴ.

    ֽϴ.  Լ  ׽Ʈ
α׷   ñ ٶϴ. , Ʈ尡 ڷḦ
ٸ , ׷ϱ type_in() ̳ type_out  Լ Ǵ ߿
elog NOTICES   ʴ Ȯ ʽÿ.

4.2) NOTICE:PortalHeapMemoryFree: 0x402251d0 not in alloc set! ̶
޽ ɴϴ.

palloc    pfree Ϸ    Դϴ. 
Լ   "libpq-fe.h"  Ŭ . ̰ Ŭ
Ű palloc  malloc  ϰ ǰ 鿣尡 丮 pfree Ϸ
  Ͱ ޽ Ÿ ˴ϴ.

4.3) PostgreSQL  ο ڷ(types)  Լ  
ҽϴ.

ٸ PostgreSQL ڵ װ͵  ֽñ ٶϴ.  
ϸƮ   contrib/ 丮   ̴ϴ.

4.4) Ʃ ϴ C Լ   մϱ?

ſ  Դϴ.  õ   . δ
մϴٸ,  ؼ... ȵ ̴ϴ.   ϰ 
ſ.
  ------------------------------------------------------------------------

5  : 

5.1) ׸Ʈ  ϸ ˴ϱ?

http://postgreSQL.org  FAQ  ϼ.

FTP Ʈ ftp://ftp.postgreSQL.org/pub   ֱ PostgreSQL 
ִ 캸.

" ø" Ͽ ؼ  ּҷ ʽÿ.

   * bugs@postgreSQL.org

 ϸƮ ּԴϴ.
