forked from postgres/postgres
-
Notifications
You must be signed in to change notification settings - Fork 6
/
Copy pathFAQ_chinese_trad
793 lines (599 loc) · 28.9 KB
/
FAQ_chinese_trad
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
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 å°è±¡çå稱å°ææ¯
<table>_<serialcolumn>_seqï¼ é裡 table å serialcolumn
åå¥æ¯ä½ ç表çå稱åä½ ç SERIAL æ¬ä½çå稱ã
é¡ä¼¼çï¼å¨ SERIAL å°è±¡é è¨æå¥å¾ä½ å¯ä»¥ç¨å½æ¸ currval() 檢索å賦å¼ç
SERIAL å¼ï¼ä¾å¦ï¼
execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
new_id = execute("SELECT currval('person_id_seq')");
4.11.3) åæä½¿ç¨ currval() æå°è´åå¶ä»ç¨æ¶çè¡çªææ³åï¼
ä¸æãcurrval() è¿åçæ¯ä½ æ¬æ¬¡æ話é²ç¨æ賦çå¼èä¸æ¯ææç¨æ¶çç¶åå¼ã
4.11.4) çºä»éº¼ä¸å¨äºåç°å¸¸ä¸æ¢å¾éç¨åºåèå¢ï¼çºä»éº¼å¨åºåèæ¬ä½çåå¼ä¸åå¨éæ·å¢ï¼
çºäºæé«ä½µç¼æ§ï¼åºåèå¨éè¦çæå賦äºæ
£å¨éè¡çäºåï¼ä¸¦ä¸å¨äºåçµæä¹åä¸é²è¡éå®ï¼ éå°±æå°è´ç°å¸¸ä¸
æ¢çäºåå¾ï¼åºåèæåºç¾ééã
4.12) ä»éº¼æ¯ OID ï¼ä»éº¼æ¯ CTID ï¼
PostgreSQL 裡åµå»ºçæ¯ä¸è¡è¨éé½æç²å¾ä¸åå¯ä¸ç OIDï¼é¤éå¨åµå»ºè¡¨æ使ç¨
WITHOUT OIDS é¸é ã OID åµå»ºææèªåçæä¸å 4ä½åçµçæ´æ¸ï¼ææ OID å¨ç¸æ
PostgreSQL 伺æå¨ä¸åæ¯å¯ä¸çã ç¶èï¼å®å¨è¶é 40åæå°æº¢åºï¼ OID æ
¤å¾æåºç¾éè¤ãPostgreSQL å¨å®çå§é¨ç³»çµ±è¡¨è£¡ä½¿ç¨ OID
å¨è¡¨ä¹é建ç«è¯ç¹«ã
å¨ç¨æ¶çè³æ表ä¸ï¼æ好æ¯ä½¿ç¨ SERIAl ä¾ä»£æ¿ OID å çº SERIAL åªè¦ä¿è
å¨å®å表ä¸çæ¸å¼æ¯å¯ä¸çå°±å¯ä»¥äºï¼é樣å®æº¢åºçå¯è½æ§å°±é常å°äºï¼
SERIAL8 å¯ç¨ä¾ä¿å8ååçµçåºåæ¸å¼ã
CTID ç¨æ¼æ¨è帶èè³æå¡ï¼å°åï¼åï¼å¡å§ï¼å移çç¹å®çç©çè¡ã CTID
å¨è¨é被æ´æ¹æéè¼å¾ç¼çæ¹è®ãç´¢å¼è³æ使ç¨å®åæåç©çè¡ã
4.13) çºä»éº¼ææ¶å°é¯èª¤è³è¨ãERROR: Memory exhausted in AllocSetAlloc()ãï¼
éå¾å¯è½æ¯ç³»çµ±çèæ¬å§åç¨åäºï¼æèå§æ ¸å°æäºè³æºæè¼ä½çéå¶å¼ãå¨åå
postmaster ä¹å試試ä¸é¢çå½ä»¤ï¼
ulimit -d 262144
limit datasize 256m
å決æ¼ä½ ç¨ç
shellï¼ä¸é¢å½ä»¤åªæä¸æ¢è½æåï¼ä½æ¯å®å°æä½ çé²ç¨è³æ段éå¶è¨å¾æ¯è¼é«ï¼
å èä¹è¨±è½è®æ¥è©¢å®æãéæ¢å½ä»¤æç¨æ¼ç¶åé²ç¨ï¼ä»¥åææå¨éæ¢å½ä»¤éè¡å¾åµå»ºç
åé²ç¨ã
å¦æä½ æ¯å¨éè¡SQL客æ¶ç«¯æå çºå¾å°è¿åäºå¤ªå¤çè³æèåºç¾åé¡ï¼è«å¨éè¡å®¢æ¶ç
«¯ä¹åå·è¡ä¸è¿°å½ä»¤ã
4.14) æå¦ä½æè½ç¥éæéè¡çPostgreSQLççæ¬ï¼
å¾ psql 裡ï¼è¼¸å¥ SELECT version();æ令ã
4.15) æå¦ä½åµå»ºä¸åé è¨å¼æ¯ç¶åæéçæ¬ä½ï¼
ä½¿ç¨ CURRENT_TIMESTAMPï¼
CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
4.16) ææ樣é²è¡ outer join ï¼å¤é£æ¥ï¼ï¼
PostgreSQL æ¡ç¨æ¨æºç SQL èªæ³æ¯æå¤é£æ¥ãé裡æ¯å©åä¾åï¼
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 OUTER
JOINï¼å°è¿å t2 ä¸æªé£æ¥çè¡ã å®å¨å¤é£æ¥ï¼FULL OUTER JOINï¼å°è¿å t1 å t2
ä¸æªé£æ¥çè¡ã ééµå OUTER å¨å·¦[å¤]é£æ¥ãå³[å¤]é£æ¥åå®å¨[å¤]é£æ¥ä¸
æ¯å¯é¸çï¼æ®éé£æ¥è¢«ç¨±çºå§é£æ¥ï¼INNER JOINï¼ã
4.17) å¦ä½ä½¿ç¨æ¶åå¤åè³æ庫çæ¥è©¢ï¼
æ²æ辦æ³æ¥è©¢ç¶åè³æ庫ä¹å¤çè³æ庫ã
å çºPostgreSQLè¦å è¼èè³æ庫ç¸éç系統ç®éï¼ç³»çµ±è¡¨ï¼ï¼å æ
¤è·¨è³æ庫çæ¥è©¢å¦ä½å·è¡æ¯ä¸å®çã
éå å¢å¼æ¨¡å¡ contrib/dblink
å許æ¡ç¨å½æ¸èª¿ç¨å¯¦ç¾è·¨åº«æ¥è©¢ãç¶ç¶ç¨æ¶ä¹å¯ä»¥åæé£æ¥å°ä¸åçè³æ庫å·è
¡æ¥è©¢ç¶å¾å¨å®¢æ¶ç«¯åä½µçµæã
4.18) å¦ä½è®å½æ¸è¿åå¤è¡æå¤åè³æï¼
å¨å½æ¸ä¸è¿åè³æè¨ééçåè½æ¯å¾å®¹æ使ç¨çï¼è©³æåè¦ï¼
http://techdocs.postgresql.org/guides/SetReturningFunctions
4.19) çºä»éº¼æå¨ä½¿ç¨ PL/PgSQL å½æ¸ååè¨æ表æææ¶å°é¯èª¤è³è¨ãrelation with OID
##### does not existãï¼
PL/PgSQL æç·©åå½æ¸çè³æ¬å§å®¹ï¼ç±æ¤å¸¶ä¾çä¸åä¸å¥½çå¯ä½ç¨æ¯è¥ä¸å
PL/PgSQL
å½æ¸è¨ªåäºä¸åè¨æ表ï¼ç¶å¾è©²è¡¨è¢«åªé¤ä¸¦é建äºï¼åå次調ç¨è©²å½æ¸å°å¤
±æï¼ å çºç·©åçå½æ¸å§å®¹ä»ç¶æåèçè¨æ表ã解決çæ¹æ³æ¯å¨ PL/PgSQL ä¸
ç¨EXECUTE å°è¨æ表é²è¡è¨ªåãé樣æä¿èæ¥è©¢å¨å·è¡å總æ被éæ°è§£æã
4.20) ç®åæåªäºè³æè¤å¯«(replication)æ¹æ¡å¯ç¨ï¼
ãè¤å¯«ãåªæ¯ä¸åè¡èªï¼æ好幾種è¤å¯«æè¡å¯ç¨ï¼æ¯ç¨®é½æåªé»å缺é»ï¼
主/å¾å¼è¤å¯«æ¹å¼æ¯å許ä¸å主伺æå¨æ¥åè®/寫çç³è«ï¼èå¤åå¾ä¼ºæå¨åªè½æ¥å
è®/SELECTæ¥è©¢çç³è«ï¼ ç®åææµè¡ä¸åè²»ç主/å¾PostgreSQLè¤å¯«æ¹æ¡æ¯
Slony-I ã
å¤å主伺æå¨çè¤å¯«æ¹å¼å許å°è®/寫çç³è«ç¼é給å¤å°ç主æ©ï¼é種æ¹å¼ç±æ¼é
è¦å¨å¤å°ä¼ºæå¨ä¹éåæ¥è³æè®å å¯è½æ帶ä¾è¼å´éçæ§è½æ失ï¼Pgcluster
æ¯ç®åé種æ¹æ¡ä¸æ好çï¼ä¸¦ä¸éå¯ä»¥åè²»ä¸è¼ã
ä¹æä¸äºåæ¥éä»è²»ååºæ¼ç¡¬é«çè³æè¤å¯«æ¹æ¡ï¼æ¯æä¸è¿°å種è¤å¯«æ¨¡åã
4.21) çºä½æ¥è©¢çµæ顯示ç表åææ¬åèæçæ¥è©¢èªå¥ä¸çä¸åï¼çºä½å¤§å¯«çæä¸è½ä¿çï¼
æ常è¦çåå æ¯å¨åµå»ºè¡¨æå°è¡¨åææ¯æ¬å使ç¨äºéå¼è( ' '
)ï¼ç¶ä½¿ç¨äºéå¼èå¾ï¼è¡¨åææ¬åï¼ç¨±çºæ¨è符ï¼åå²ææ¯åå 大å°å¯«çï¼
éæè¬èä½ å¨æ¥è©¢æ表åææ¬åä¹æ使ç¨éå¼èï¼ä¸äºå·¥å·è»é«ï¼å pgAdmin
æå¨ç¼åºåµå»ºè¡¨çæ令æèªåå°å¨æ¯åæ¨è符ä¸å éå¼èã å æ¤ï¼çºäºæ¨è
符ççµ±ä¸ï¼ä½ æ該ï¼
* å¨åµå»ºè¡¨æé¿åå°æ¨è符使ç¨éå¼èå¼èµ·ä¾ã
* å¨æ¨è符ä¸åªä½¿ç¨å°å¯«åæ¯ã
* ï¼çºäºèå·²åå¨çæ¨è符ç¸åï¼å¨æ¥è©¢ä¸ä½¿ç¨éå¼èå°æ¨è符å¼èµ·ä¾ã