-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathHISTORY
8587 lines (7642 loc) · 404 KB
/
HISTORY
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
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
Release Notes
Release 8.1.4
Release date: 2006-05-23
This release contains a variety of fixes from 8.1.3, including patches
for extremely serious security issues.
__________________________________________________________________
Migration to version 8.1.4
A dump/restore is not required for those running 8.1.X. However, if you
are upgrading from a version earlier than 8.1.2, see the release notes
for 8.1.2.
Full security against the SQL-injection attacks described in
CVE-2006-2313 and CVE-2006-2314 may require changes in application
code. If you have applications that embed untrustworthy strings into
SQL commands, you should examine them as soon as possible to ensure
that they are using recommended escaping techniques. In most cases,
applications should be using subroutines provided by libraries or
drivers (such as libpq's PQescapeStringConn()) to perform string
escaping, rather than relying on ad hoc code to do it.
__________________________________________________________________
Changes
* Change the server to reject invalidly-encoded multibyte characters
in all cases (Tatsuo, Tom)
While PostgreSQL has been moving in this direction for some time,
the checks are now applied uniformly to all encodings and all
textual input, and are now always errors not merely warnings. This
change defends against SQL-injection attacks of the type described
in CVE-2006-2313.
* Reject unsafe uses of \' in string literals
As a server-side defense against SQL-injection attacks of the type
described in CVE-2006-2314, the server now only accepts '' and not
\' as a representation of ASCII single quote in SQL string
literals. By default, \' is rejected only when client_encoding is
set to a client-only encoding (SJIS, BIG5, GBK, GB18030, or UHC),
which is the scenario in which SQL injection is possible. A new
configuration parameter backslash_quote is available to adjust this
behavior when needed. Note that full security against CVE-2006-2314
may require client-side changes; the purpose of backslash_quote is
in part to make it obvious that insecure clients are insecure.
* Modify libpq's string-escaping routines to be aware of encoding
considerations and standard_conforming_strings
This fixes libpq-using applications for the security issues
described in CVE-2006-2313 and CVE-2006-2314, and also
future-proofs them against the planned changeover to SQL-standard
string literal syntax. Applications that use multiple PostgreSQL
connections concurrently should migrate to PQescapeStringConn() and
PQescapeByteaConn() to ensure that escaping is done correctly for
the settings in use in each database connection. Applications that
do string escaping "by hand" should be modified to rely on library
routines instead.
* Fix weak key selection in pgcrypto (Marko Kreen)
Errors in fortuna PRNG reseeding logic could cause a predictable
session key to be selected by pgp_sym_encrypt() in some cases. This
only affects non-OpenSSL-using builds.
* Fix some incorrect encoding conversion functions
win1251_to_iso, win866_to_iso, euc_tw_to_big5, euc_tw_to_mic,
mic_to_euc_tw were all broken to varying extents.
* Clean up stray remaining uses of \' in strings (Bruce, Jan)
* Make autovacuum visible in pg_stat_activity (Alvaro)
* Disable full_page_writes (Tom)
In certain cases, having full_page_writes off would cause crash
recovery to fail. A proper fix will appear in 8.2; for now it's
just disabled.
* Various planner fixes, particularly for bitmap index scans and
MIN/MAX optimization (Tom)
* Fix incorrect optimization in merge join (Tom)
Outer joins could sometimes emit multiple copies of unmatched rows.
* Fix crash from using and modifying a plpgsql function in the same
transaction
* Fix WAL replay for case where a btree index has been truncated
* Fix SIMILAR TO for patterns involving | (Tom)
* Fix "SELECT INTO" and "CREATE TABLE AS" to create tables in the
default tablespace, not the base directory (Kris Jurka)
* Fix server to use custom DH SSL parameters correctly (Michael Fuhr)
* Improve qsort performance (Dann Corbit)
Currently this code is only used on Solaris.
* Fix for Bonjour on Intel Macs (Ashley Clark)
* Fix various minor memory leaks
* Fix problem with password prompting on some Win32 systems (Robert
Kinberg)
* Improve pg_dump's handling of default values for domains
* Fix pg_dumpall to handle identically-named users and groups
reasonably (only possible when dumping from a pre-8.1 server) (Tom)
The user and group will be merged into a single role with LOGIN
permission. Formerly the merged role wouldn't have LOGIN
permission, making it unusable as a user.
* Fix pg_restore -n to work as documented (Tom)
__________________________________________________________________
Release 8.1.3
Release date: 2006-02-14
This release contains a variety of fixes from 8.1.2, including one very
serious security issue.
__________________________________________________________________
Migration to version 8.1.3
A dump/restore is not required for those running 8.1.X. However, if you
are upgrading from a version earlier than 8.1.2, see the release notes
for 8.1.2.
__________________________________________________________________
Changes
* Fix bug that allowed any logged-in user to "SET ROLE" to any other
database user id (CVE-2006-0553)
Due to inadequate validity checking, a user could exploit the
special case that "SET ROLE" normally uses to restore the previous
role setting after an error. This allowed ordinary users to acquire
superuser status, for example. The escalation-of-privilege risk
exists only in 8.1.0-8.1.2. However, in all releases back to 7.3
there is a related bug in "SET SESSION AUTHORIZATION" that allows
unprivileged users to crash the server, if it has been compiled
with Asserts enabled (which is not the default). Thanks to Akio
Ishida for reporting this problem.
* Fix bug with row visibility logic in self-inserted rows (Tom)
Under rare circumstances a row inserted by the current command
could be seen as already valid, when it should not be. Repairs bug
created in 8.0.4, 7.4.9, and 7.3.11 releases.
* Fix race condition that could lead to "file already exists" errors
during pg_clog and pg_subtrans file creation (Tom)
* Fix cases that could lead to crashes if a cache-invalidation
message arrives at just the wrong time (Tom)
* Properly check DOMAIN constraints for UNKNOWN parameters in
prepared statements (Neil)
* Ensure "ALTER COLUMN TYPE" will process FOREIGN KEY, UNIQUE, and
PRIMARY KEY constraints in the proper order (Nakano Yoshihisa)
* Fixes to allow restoring dumps that have cross-schema references to
custom operators or operator classes (Tom)
* Allow pg_restore to continue properly after a "COPY" failure;
formerly it tried to treat the remaining "COPY" data as SQL
commands (Stephen Frost)
* Fix pg_ctl unregister crash when the data directory is not
specified (Magnus)
* Fix libpq PQprint HTML tags (Christoph Zwerschke)
* Fix ecpg crash on AMD64 and PPC (Neil)
* Allow SETOF and %TYPE to be used together in function result type
declarations
* Recover properly if error occurs during argument passing in
PL/python (Neil)
* Fix memory leak in plperl_return_next (Neil)
* Fix PL/perl's handling of locales on Win32 to match the backend
(Andrew)
* Various optimizer fixes (Tom)
* Fix crash when log_min_messages is set to DEBUG3 or above in
"postgresql.conf" on Win32 (Bruce)
* Fix pgxs -L library path specification for Win32, Cygwin, OS X, AIX
(Bruce)
* Check that SID is enabled while checking for Win32 admin privileges
(Magnus)
* Properly reject out-of-range date inputs (Kris Jurka)
* Portability fix for testing presence of finite and isinf during
configure (Tom)
* Improve speed of "COPY IN" via libpq, by avoiding a kernel call per
data line (Alon Goldshuv)
* Improve speed of "/contrib/tsearch2" index creation (Tom)
__________________________________________________________________
Release 8.1.2
Release date: 2006-01-09
This release contains a variety of fixes from 8.1.1.
__________________________________________________________________
Migration to version 8.1.2
A dump/restore is not required for those running 8.1.X. However, you
might need to "REINDEX" indexes on textual columns after updating, if
you are affected by the locale or plperl issues described below.
__________________________________________________________________
Changes
* Fix Windows code so that postmaster will continue rather than exit
if there is no more room in ShmemBackendArray (Magnus)
The previous behavior could lead to a denial-of-service situation
if too many connection requests arrive close together. This applies
*only* to the Windows port.
* Fix bug introduced in 8.0 that could allow ReadBuffer to return an
already-used page as new, potentially causing loss of
recently-committed data (Tom)
* Fix for protocol-level Describe messages issued outside a
transaction or in a failed transaction (Tom)
* Fix character string comparison for locales that consider different
character combinations as equal, such as Hungarian (Tom)
This might require "REINDEX" to fix existing indexes on textual
columns.
* Set locale environment variables during postmaster startup to
ensure that plperl won't change the locale later
This fixes a problem that occurred if the postmaster was started
with environment variables specifying a different locale than what
initdb had been told. Under these conditions, any use of plperl was
likely to lead to corrupt indexes. You may need "REINDEX" to fix
existing indexes on textual columns if this has happened to you.
* Allow more flexible relocation of installation directories (Tom)
Previous releases supported relocation only if all installation
directory paths were the same except for the last component.
* Prevent crashes caused by the use of ISO-8859-5 and ISO-8859-9
encodings (Tatsuo)
* Fix longstanding bug in strpos() and regular expression handling in
certain rarely used Asian multi-byte character sets (Tatsuo)
* Fix bug where COPY CSV mode considered any \. to terminate the copy
data
The new code requires \. to appear alone on a line, as per
documentation.
* Make COPY CSV mode quote a literal data value of \. to ensure it
cannot be interpreted as the end-of-data marker (Bruce)
* Various fixes for functions returning RECORDs (Tom)
* Fix processing of "postgresql.conf" so a final line with no newline
is processed properly (Tom)
* Fix bug in "/contrib/pgcrypto" gen_salt, which caused it not to use
all available salt space for MD5 and XDES algorithms (Marko Kreen,
Solar Designer)
Salts for Blowfish and standard DES are unaffected.
* Fix autovacuum crash when processing expression indexes
* Fix "/contrib/dblink" to throw an error, rather than crashing, when
the number of columns specified is different from what's actually
returned by the query (Joe)
__________________________________________________________________
Release 8.1.1
Release date: 2005-12-12
This release contains a variety of fixes from 8.1.0.
__________________________________________________________________
Migration to version 8.1.1
A dump/restore is not required for those running 8.1.X.
__________________________________________________________________
Changes
* Fix incorrect optimizations of outer-join conditions (Tom)
* Fix problems with wrong reported column names in cases involving
sub-selects flattened by the optimizer (Tom)
* Fix update failures in scenarios involving CHECK constraints,
toasted columns, *and* indexes (Tom)
* Fix bgwriter problems after recovering from errors (Tom)
The background writer was found to leak buffer pins after write
errors. While not fatal in itself, this might lead to mysterious
blockages of later VACUUM commands.
* Prevent failure if client sends Bind protocol message when current
transaction is already aborted
* "/contrib/tsearch2" and "/contrib/ltree" fixes (Teodor)
* Fix problems with translated error messages in languages that
require word reordering, such as Turkish; also problems with
unexpected truncation of output strings and wrong display of the
smallest possible bigint value (Andrew, Tom)
These problems only appeared on platforms that were using our
"port/snprintf.c" code, which includes BSD variants if --enable-nls
was given, and perhaps others. In addition, a different form of the
translated-error-message problem could appear on Windows depending
on which version of "libintl" was used.
* Re-allow AM/PM, HH, HH12, and D format specifiers for to_char(time)
and to_char(interval). (to_char(interval) should probably use
HH24.) (Bruce)
* AIX, HPUX, and MSVC compile fixes (Tom, Hiroshi Saito)
* Optimizer improvements (Tom)
* Retry file reads and writes after Windows NO_SYSTEM_RESOURCES error
(Qingqing Zhou)
* Prevent autovacuum from crashing during ANALYZE of expression index
(Alvaro)
* Fix problems with ON COMMIT DELETE ROWS temp tables
* Fix problems when a trigger alters the output of a SELECT DISTINCT
query
* Add 8.1.0 release note item on how to migrate invalid UTF-8 byte
sequences (Paul Lindner)
__________________________________________________________________
Release 8.1
Release date: 2005-11-08
__________________________________________________________________
Overview
Major changes in this release:
Improve concurrent access to the shared buffer cache (Tom)
Access to the shared buffer cache was identified as a
significant scalability problem, particularly on multi-CPU
systems. In this release, the way that locking is done in the
buffer manager has been overhauled to reduce lock contention and
improve scalability. The buffer manager has also been changed to
use a "clock sweep" replacement policy.
Allow index scans to use an intermediate in-memory bitmap (Tom)
In previous releases, only a single index could be used to do
lookups on a table. With this feature, if a query has "WHERE
tab.col1 = 4 and tab.col2 = 9", and there is no multicolumn
index on col1 and col2, but there is an index on col1 and
another on col2, it is possible to search both indexes and
combine the results in memory, then do heap fetches for only the
rows matching both the col1 and col2 restrictions. This is very
useful in environments that have a lot of unstructured queries
where it is impossible to create indexes that match all possible
access conditions. Bitmap scans are useful even with a single
index, as they reduce the amount of random access needed; a
bitmap index scan is efficient for retrieving fairly large
fractions of the complete table, whereas plain index scans are
not.
Add two-phase commit (Heikki Linnakangas, Alvaro, Tom)
Two-phase commit allows transactions to be "prepared" on several
computers, and once all computers have successfully prepared
their transactions (none failed), all transactions can be
committed. Even if a machine crashes after a prepare, the
prepared transaction can be committed after the machine is
restarted. New syntax includes "PREPARE TRANSACTION" and
"COMMIT/ROLLBACK PREPARED". A new system view pg_prepared_xacts
has also been added.
Create a new role system that replaces users and groups (Stephen Frost)
Roles are a combination of users and groups. Like users, they
can have login capability, and like groups, a role can have
other roles as members. Roles basically remove the distinction
between users and groups. For example, a role can:
+ Have login capability (optionally)
+ Own objects
+ Hold access permissions for database objects
+ Inherit permissions from other roles it is a member of
Once a user logs into a role, she obtains capabilities of the
login role plus any inherited roles, and can use "SET ROLE" to
switch to other roles she is a member of. This feature is a
generalization of the SQL standard's concept of roles. This
change also replaces pg_shadow and pg_group by new role-capable
catalogs pg_authid and pg_auth_members. The old tables are
redefined as read-only views on the new role tables.
Automatically use indexes for MIN() and MAX() (Tom)
In previous releases, the only way to use an index for MIN() or
MAX() was to rewrite the query as "SELECT col FROM tab ORDER BY
col LIMIT 1". Index usage now happens automatically.
Move /contrib/pg_autovacuum into the main server (Alvaro)
Integrating autovacuum into the server allows it to be
automatically started and stopped in sync with the database
server, and allows autovacuum to be configured from
"postgresql.conf".
Add shared row level locks using SELECT ... FOR SHARE (Alvaro)
While PostgreSQL's MVCC locking allows "SELECT" to never be
blocked by writers and therefore does not need shared row locks
for typical operations, shared locks are useful for applications
that require shared row locking. In particular this reduces the
locking requirements imposed by referential integrity checks.
Add dependencies on shared objects, specifically roles (Alvaro)
This extension of the dependency mechanism prevents roles from
being dropped while there are still database objects they own.
Formerly it was possible to accidentally "orphan" objects by
deleting their owner. While this could be recovered from, it was
messy and unpleasant.
Improve performance for partitioned tables (Simon)
The new constraint_exclusion configuration parameter avoids
lookups on child tables where constraints indicate that no
matching rows exist in the child table.
This allows for a basic type of table partitioning. If child
tables store separate key ranges and this is enforced using
appropriate "CHECK" constraints, the optimizer will skip child
table accesses when the constraint guarantees no matching rows
exist in the child table.
__________________________________________________________________
Migration to version 8.1
A dump/restore using pg_dump is required for those wishing to migrate
data from any previous release.
The 8.0 release announced that the to_char() function for intervals
would be removed in 8.1. However, since no better API has been
suggested, to_char(interval) has been enhanced in 8.1 and will remain
in the server.
Observe the following incompatibilities:
* add_missing_from is now false by default (Neil)
By default, we now generate an error if a table is used in a query
without a FROM reference. The old behavior is still available, but
the parameter must be set to 'true' to obtain it.
It may be necessary to set add_missing_from to true in order to
load an existing dump file, if the dump contains any views or rules
created using the implicit-FROM syntax. This should be a one-time
annoyance, because PostgreSQL 8.1 will convert such views and rules
to standard explicit-FROM syntax. Subsequent dumps will therefore
not have the problem.
* Cause input of a zero-length string ('') for float4/float8/oid to
throw an error, rather than treating it as a zero (Neil)
This change is consistent with the current handling of zero-length
strings for integers. The schedule for this change was announced in
8.0.
* default_with_oids is now false by default (Neil)
With this option set to false, user-created tables no longer have
an OID column unless "WITH OIDS" is specified in "CREATE TABLE".
Though OIDs have existed in all releases of PostgreSQL, their use
is limited because they are only four bytes long and the counter is
shared across all installed databases. The preferred way of
uniquely identifying rows is via sequences and the SERIAL type,
which have been supported since PostgreSQL 6.4.
* Add E'' syntax so eventually ordinary strings can treat backslashes
literally (Bruce)
Currently PostgreSQL processes a backslash in a string literal as
introducing a special escape sequence, e.g. \n or \010. While this
allows easy entry of special values, it is nonstandard and makes
porting of applications from other databases more difficult. For
this reason, the PostgreSQL project is planning to remove the
special meaning of backslashes in strings. For backward
compatibility and for users who want special backslash processing,
a new string syntax has been created. This new string syntax is
formed by writing an E immediately preceding the single quote that
starts the string, e.g. E'hi\n'. While this release does not change
the handling of backslashes in strings, it does add new
configuration parameters to help users migrate applications for
future releases:
+ standard_conforming_strings -- does this release treat
backslashes literally in ordinary strings?
+ escape_string_warning -- warn about backslashes in ordinary
(non-E) strings
The standard_conforming_strings value is read-only. Applications
can retrieve the value to know how backslashes are processed.
(Presence of the parameter can also be taken as an indication that
E'' string syntax is supported.) In a future release,
standard_conforming_strings will be true, meaning backslashes will
be treated literally in non-E strings. To prepare for this change,
use E'' strings in places that need special backslash processing,
and turn on escape_string_warning to find additional strings that
need to be converted to use E''. Also, use two single-quotes ('')
to embed a literal single-quote in a string, rather than the
PostgreSQL-supported syntax of backslash single-quote (\'). The
former is standards-conforming and does not require the use of the
E'' string syntax. You can also use the $$ string syntax, which
does not treat backslashes specially.
* Make "REINDEX DATABASE" reindex all indexes in the database (Tom)
Formerly, "REINDEX DATABASE" reindexed only system tables. This new
behavior seems more intuitive. A new command "REINDEX SYSTEM"
provides the old functionality of reindexing just the system
tables.
* Read-only large object descriptors now obey MVCC snapshot semantics
When a large object is opened with INV_READ (and not INV_WRITE),
the data read from the descriptor will now reflect a "snapshot" of
the large object's state at the time of the transaction snapshot in
use by the query that called lo_open(). To obtain the old behavior
of always returning the latest committed data, include INV_WRITE in
the mode flags for lo_open().
* Add proper dependencies for arguments of sequence functions (Tom)
In previous releases, sequence names passed to nextval(),
currval(), and setval() were stored as simple text strings, meaning
that renaming or dropping a sequence used in a DEFAULT clause made
the clause invalid. This release stores all newly-created sequence
function arguments as internal OIDs, allowing them to track
sequence renaming, and adding dependency information that prevents
improper sequence removal. It also makes such DEFAULT clauses
immune to schema renaming and search path changes.
Some applications might rely on the old behavior of run-time lookup
for sequence names. This can still be done by explicitly casting
the argument to text, for example nextval('myseq'::text).
Pre-8.1 database dumps loaded into 8.1 will use the old text-based
representation and therefore will not have the features of
OID-stored arguments. However, it is possible to update a database
containing text-based DEFAULT clauses. First, save this query into
a file, such as "fixseq.sql":
SELECT 'ALTER TABLE ' ||
pg_catalog.quote_ident(n.nspname) || '.' ||
pg_catalog.quote_ident(c.relname) ||
' ALTER COLUMN ' || pg_catalog.quote_ident(a.attname) ||
' SET DEFAULT ' ||
regexp_replace(d.adsrc,
$$val\(\(('[^']*')::text\)::regclass$$,
$$val(\1$$,
'g') ||
';'
FROM pg_namespace n, pg_class c, pg_attribute a, pg_attrdef d
WHERE n.oid = c.relnamespace AND
c.oid = a.attrelid AND
a.attrelid = d.adrelid AND
a.attnum = d.adnum AND
d.adsrc ~ $$val\(\('[^']*'::text\)::regclass$$;
Next, run the query against a database to find what adjustments are
required, like this for database db1:
psql -t -f fixseq.sql db1
This will show the "ALTER TABLE" commands needed to convert the
database to the newer OID-based representation. If the commands
look reasonable, run this to update the database:
psql -t -f fixseq.sql db1 | psql -e db1
This process must be repeated in each database to be updated.
* In psql, treat unquoted \{digit}+ sequences as octal (Bruce)
In previous releases, \{digit}+ sequences were treated as decimal,
and only \0{digit}+ were treated as octal. This change was made for
consistency.
* Remove grammar productions for prefix and postfix % and ^ operators
(Tom)
These have never been documented and complicated the use of the
modulus operator (%) with negative numbers.
* Make &< and &> for polygons consistent with the box "over"
operators (Tom)
* "CREATE LANGUAGE" may ignore the provided arguments in favor of
information from pg_pltemplate (Tom)
A new system catalog pg_pltemplate has been defined to carry
information about the preferred definitions of procedural languages
(such as whether they have validator functions). When an entry
exists in this catalog for the language being created, "CREATE
LANGUAGE" will ignore all its parameters except the language name
and instead use the catalog information. This measure was taken
because of increasing problems with obsolete language definitions
being loaded by old dump files. As of 8.1, pg_dump will dump
procedural language definitions as just "CREATE LANGUAGE name",
relying on a template entry to exist at load time. We expect this
will be a more future-proof representation.
* Make pg_cancel_backend(int) return a boolean rather than an integer
(Neil)
* Some users are having problems loading UTF-8 data into 8.1.X. This
is because previous versions allowed invalid UTF-8 byte sequences
to be entered into the database, and this release properly accepts
only valid UTF-8 sequences. One way to correct a dumpfile is to run
the command "iconv -c -f UTF-8 -t UTF-8 -o cleanfile.sql
dumpfile.sql". The -c option removes invalid character sequences. A
diff of the two files will show the sequences that are invalid.
"iconv" reads the entire input file into memory so it might be
necessary to use split to break up the dump into multiple smaller
files for processing.
__________________________________________________________________
Additional Changes
Below you will find a detailed account of the additional changes
between PostgreSQL 8.1 and the previous major release.
__________________________________________________________________
Performance Improvements
* Improve GiST and R-tree index performance (Neil)
* Improve the optimizer, including auto-resizing of hash joins (Tom)
* Overhaul internal API in several areas
* Change WAL record CRCs from 64-bit to 32-bit (Tom)
We determined that the extra cost of computing 64-bit CRCs was
significant, and the gain in reliability too marginal to justify
it.
* Prevent writing large empty gaps in WAL pages (Tom)
* Improve spinlock behavior on SMP machines, particularly Opterons
(Tom)
* Allow nonconsecutive index columns to be used in a multicolumn
index (Tom)
For example, this allows an index on columns a,b,c to be used in a
query with "WHERE a = 4 and c = 10".
* Skip WAL logging for "CREATE TABLE AS" / "SELECT INTO" (Simon)
Since a crash during "CREATE TABLE AS" would cause the table to be
dropped during recovery, there is no reason to WAL log as the table
is loaded. (Logging still happens if WAL archiving is enabled,
however.)
* Allow concurrent GIST index access (Teodor, Oleg)
* Add configuration parameter full_page_writes to control writing
full pages to WAL (Bruce)
To prevent partial disk writes from corrupting the database,
PostgreSQL writes a complete copy of each database disk page to WAL
the first time it is modified after a checkpoint. This option turns
off that functionality for more speed. This is safe to use with
battery-backed disk caches where partial page writes cannot happen.
* Use O_DIRECT if available when using O_SYNC for wal_sync_method
(Itagaki Takahiro)
O_DIRECT causes disk writes to bypass the kernel cache, and for WAL
writes, this improves performance.
* Improve "COPY FROM" performance (Alon Goldshuv)
This was accomplished by reading "COPY" input in larger chunks,
rather than character by character.
* Improve the performance of COUNT(), SUM, AVG(), STDDEV(), and
VARIANCE() (Neil, Tom)
__________________________________________________________________
Server Changes
* Prevent problems due to transaction ID (XID) wraparound (Tom)
The server will now warn when the transaction counter approaches
the wraparound point. If the counter becomes too close to
wraparound, the server will stop accepting queries. This ensures
that data is not lost before needed vacuuming is performed.
* Fix problems with object IDs (OIDs) conflicting with existing
system objects after the OID counter has wrapped around (Tom)
* Add warning about the need to increase max_fsm_relations and
max_fsm_pages during "VACUUM" (Ron Mayer)
* Add temp_buffers configuration parameter to allow users to
determine the size of the local buffer area for temporary table
access (Tom)
* Add session start time and client IP address to pg_stat_activity
(Magnus)
* Adjust pg_stat views for bitmap scans (Tom)
The meanings of some of the fields have changed slightly.
* Enhance pg_locks view (Tom)
* Log queries for client-side "PREPARE" and "EXECUTE" (Simon)
* Allow Kerberos name and user name case sensitivity to be specified
in "postgresql.conf" (Magnus)
* Add configuration parameter krb_server_hostname so that the server
host name can be specified as part of service principal (Todd
Kover)
If not set, any service principal matching an entry in the keytab
can be used. This is new Kerberos matching behavior in this
release.
* Add log_line_prefix options for millisecond timestamps (%m) and
remote host (%h) (Ed L.)
* Add WAL logging for GIST indexes (Teodor, Oleg)
GIST indexes are now safe for crash and point-in-time recovery.
* Remove old "*.backup" files when we do pg_stop_backup() (Bruce)
This prevents a large number of "*.backup" files from existing in
"/pg_xlog".
* Add configuration parameters to control TCP/IP keep-alive times for
idle, interval, and count (Oliver Jowett)
These values can be changed to allow more rapid detection of lost
client connections.
* Add per-user and per-database connection limits (Petr Jelinek)
Using "ALTER USER" and "ALTER DATABASE", limits can now be enforced
on the maximum number of sessions that can concurrently connect as
a specific user or to a specific database. Setting the limit to
zero disables user or database connections.
* Allow more than two gigabytes of shared memory and per-backend work
memory on 64-bit machines (Koichi Suzuki)
* New system catalog pg_pltemplate allows overriding obsolete
procedural-language definitions in dump files (Tom)
__________________________________________________________________
Query Changes
* Add temporary views (Koju Iijima, Neil)
* Fix "HAVING" without any aggregate functions or "GROUP BY" so that
the query returns a single group (Tom)
Previously, such a case would treat the "HAVING" clause the same as
a "WHERE" clause. This was not per spec.
* Add "USING" clause to allow additional tables to be specified to
"DELETE" (Euler Taveira de Oliveira, Neil)
In prior releases, there was no clear method for specifying
additional tables to be used for joins in a "DELETE" statement.
"UPDATE" already has a FROM clause for this purpose.
* Add support for \x hex escapes in backend and ecpg strings (Bruce)
This is just like the standard C \x escape syntax. Octal escapes
were already supported.
* Add "BETWEEN SYMMETRIC" query syntax (Pavel Stehule)
This feature allows "BETWEEN" comparisons without requiring the
first value to be less than the second. For example, "2 BETWEEN
[ASYMMETRIC] 3 AND 1" returns false, while "2 BETWEEN SYMMETRIC 3
AND 1" returns true. "BETWEEN ASYMMETRIC" was already supported.
* Add "NOWAIT" option to "SELECT ... FOR UPDATE/SHARE" (Hans-Juergen
Schoenig)
While the statement_timeout configuration parameter allows a query
taking more than a certain amount of time to be cancelled, the
"NOWAIT" option allows a query to be canceled as soon as a "SELECT
... FOR UPDATE/SHARE" command cannot immediately acquire a row
lock.
__________________________________________________________________
Object Manipulation Changes
* Track dependencies of shared objects (Alvaro)
PostgreSQL allows global tables (users, databases, tablespaces) to
reference information in multiple databases. This addition adds
dependency information for global tables, so, for example, user
ownership can be tracked across databases, so a user who owns
something in any database can no longer be removed. Dependency
tracking already existed for database-local objects.
* Allow limited "ALTER OWNER" commands to be performed by the object
owner (Stephen Frost)
Prior releases allowed only superusers to change object owners.
Now, ownership can be transferred if the user executing the command
owns the object and would be able to create it as the new owner
(that is, the user is a member of the new owning role and that role
has the CREATE permission that would be needed to create the object
afresh).
* Add "ALTER" object "SET SCHEMA" capability for some object types
(tables, functions, types) (Bernd Helmle)
This allows objects to be moved to different schemas.
* Add "ALTER TABLE ENABLE/DISABLE TRIGGER" to disable triggers
(Satoshi Nagayasu)
__________________________________________________________________
Utility Command Changes
* Allow "TRUNCATE" to truncate multiple tables in a single command
(Alvaro)
Because of referential integrity checks, it is not allowed to
truncate a table that is part of a referential integrity
constraint. Using this new functionality, "TRUNCATE" can be used to
truncate such tables, if both tables involved in a referential
integrity constraint are truncated in a single "TRUNCATE" command.
* Properly process carriage returns and line feeds in "COPY CSV" mode
(Andrew)
In release 8.0, carriage returns and line feeds in "CSV COPY TO"
were processed in an inconsistent manner. (This was documented on
the TODO list.)
* Add "COPY WITH CSV HEADER" to allow a header line as the first line
in "COPY" (Andrew)
This allows handling of the common "CSV" usage of placing the
column names on the first line of the data file. For "COPY TO", the
first line contains the column names, and for "COPY FROM", the
first line is ignored.
* On Windows, display better sub-second precision in "EXPLAIN
ANALYZE" (Magnus)
* Add trigger duration display to "EXPLAIN ANALYZE" (Tom)
Prior releases included trigger execution time as part of the total
execution time, but did not show it separately. It is now possible
to see how much time is spent in each trigger.
* Add support for \x hex escapes in "COPY" (Sergey Ten)
Previous releases only supported octal escapes.
* Make "SHOW ALL" include variable descriptions (Matthias Schmidt)
"SHOW" varname still only displays the variable's value and does
not include the description.
* Make initdb create a new standard database called postgres, and
convert utilities to use postgres rather than template1 for
standard lookups (Dave)
In prior releases, template1 was used both as a default connection
for utilities like createuser, and as a template for new databases.
This caused "CREATE DATABASE" to sometimes fail, because a new
database cannot be created if anyone else is in the template
database. With this change, the default connection database is now
postgres, meaning it is much less likely someone will be using
template1 during "CREATE DATABASE".
* Create new reindexdb command-line utility by moving
"/contrib/reindexdb" into the server (Euler Taveira de Oliveira)
__________________________________________________________________
Data Type and Function Changes
* Add MAX() and MIN() aggregates for array types (Koju Iijima)
* Fix to_date() and to_timestamp() to behave reasonably when CC and
YY fields are both used (Karel Zak)
If the format specification contains CC and a year specification is
YYY or longer, ignore the CC. If the year specification is YY or
shorter, interpret CC as the previous century.
* Add md5(bytea) (Abhijit Menon-Sen)
md5(text) already existed.
* Add support for "numeric ^ numeric" based on power(numeric,
numeric)
The function already existed, but there was no operator assigned to
it.
* Fix NUMERIC modulus by properly truncating the quotient during
computation (Bruce)
In previous releases, modulus for large values sometimes returned
negative results due to rounding of the quotient.
* Add a function lastval() (Dennis Björklund)
lastval() is a simplified version of currval(). It automatically
determines the proper sequence name based on the most recent
nextval() or setval() call performed by the current session.
* Add to_timestamp(DOUBLE PRECISION) (Michael Glaesemann)
Converts Unix seconds since 1970 to a TIMESTAMP WITH TIMEZONE.
* Add pg_postmaster_start_time() function (Euler Taveira de Oliveira,
Matthias Schmidt)
* Allow the full use of time zone names in "AT TIME ZONE", not just
the short list previously available (Magnus)
Previously, only a predefined list of time zone names were
supported by "AT TIME ZONE". Now any supported time zone name can
be used, e.g.:
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Europe/London';
In the above query, the time zone used is adjusted based on the
daylight saving time rules that were in effect on the supplied
date.
* Add GREATEST() and LEAST() variadic functions (Pavel Stehule)
These functions take a variable number of arguments and return the
greatest or least value among the arguments.
* Add pg_column_size() (Mark Kirkwood)
This returns storage size of a column, which might be compressed.
* Add regexp_replace() (Atsushi Ogawa)
This allows regular expression replacement, like sed. An optional
flag argument allows selection of global (replace all) and
case-insensitive modes.
* Fix interval division and multiplication (Bruce)
Previous versions sometimes returned unjustified results, like "'4
months'::interval / 5" returning "'1 mon -6 days'".
* Fix roundoff behavior in timestamp, time, and interval output (Tom)
This fixes some cases in which the seconds field would be shown as
60 instead of incrementing the higher-order fields.
* Add a separate day field to type interval so a one day interval can
be distinguished from a 24 hour interval (Michael Glaesemann)
Days that contain a daylight saving time adjustment are not 24
hours long, but typically 23 or 25 hours. This change creates a
conceptual distinction between intervals of "so many days" and
intervals of "so many hours". Adding 1 day to a timestamp now gives
the same local time on the next day even if a daylight saving time
adjustment occurs between, whereas adding 24 hours will give a
different local time when this happens. For example, under US DST
rules:
'2005-04-03 00:00:00-05' + '1 day' = '2005-04-04 00:00:00-04'
'2005-04-03 00:00:00-05' + '24 hours' = '2005-04-04 01:00:00-04'
* Add justify_days() and justify_hours() (Michael Glaesemann)
These functions, respectively, adjust days to an appropriate number
of full months and days, and adjust hours to an appropriate number
of full days and hours.
* Move "/contrib/dbsize" into the backend, and rename some of the
functions (Dave Page, Andreas Pflug)
+ pg_tablespace_size()
+ pg_database_size()
+ pg_relation_size()
+ pg_total_relation_size()
+ pg_size_pretty()
pg_total_relation_size() includes indexes and TOAST tables.
* Add functions for read-only file access to the cluster directory
(Dave Page, Andreas Pflug)
+ pg_stat_file()
+ pg_read_file()
+ pg_ls_dir()
* Add pg_reload_conf() to force reloading of the configuration files
(Dave Page, Andreas Pflug)
* Add pg_rotate_logfile() to force rotation of the server log file
(Dave Page, Andreas Pflug)
* Change pg_stat_* views to include TOAST tables (Tom)
__________________________________________________________________
Encoding and Locale Changes
* Rename some encodings to be more consistent and to follow
international standards (Bruce)
+ UNICODE is now UTF8
+ ALT is now WIN866
+ WIN is now WIN1251
+ TCVN is now WIN1258
The original names still work.
* Add support for WIN1252 encoding (Roland Volkmann)
* Add support for four-byte UTF8 characters (John Hansen)
Previously only one, two, and three-byte UTF8 characters were
supported. This is particularly important for support for some
Chinese character sets.
* Allow direct conversion between EUC_JP and SJIS to improve
performance (Atsushi Ogawa)
* Allow the UTF8 encoding to work on Windows (Magnus)
This is done by mapping UTF8 to the Windows-native UTF16
implementation.
__________________________________________________________________
General Server-Side Language Changes
* Fix "ALTER LANGUAGE RENAME" (Sergey Yatskevich)
* Allow function characteristics, like strictness and volatility, to
be modified via "ALTER FUNCTION" (Neil)
* Increase the maximum number of function arguments to 100 (Tom)
* Allow SQL and PL/PgSQL functions to use "OUT" and "INOUT"
parameters (Tom)
"OUT" is an alternate way for a function to return values. Instead
of using "RETURN", values can be returned by assigning to
parameters declared as "OUT" or "INOUT". This is notationally
simpler in some cases, particularly so when multiple values need to
be returned. While returning multiple values from a function was
possible in previous releases, this greatly simplifies the process.
(The feature will be extended to other server-side languages in
future releases.)
* Move language handler functions into the pg_catalog schema
This makes it easier to drop the public schema if desired.
* Add SPI_getnspname() to SPI (Neil)
__________________________________________________________________
PL/PgSQL Server-Side Language Changes
* Overhaul the memory management of PL/PgSQL functions (Neil)
The parsetree of each function is now stored in a separate memory
context. This allows this memory to be easily reclaimed when it is
no longer needed.
* Check function syntax at "CREATE FUNCTION" time, rather than at
runtime (Neil)
Previously, most syntax errors were reported only when the function
was executed.
* Allow "OPEN" to open non-"SELECT" queries like "EXPLAIN" and "SHOW"
(Tom)
* No longer require functions to issue a "RETURN" statement (Tom)
This is a byproduct of the newly added "OUT" and "INOUT"
functionality. "RETURN" can be omitted when it is not needed to
provide the function's return value.
* Add support for an optional "INTO" clause to PL/PgSQL's "EXECUTE"
statement (Pavel Stehule, Neil)
* Make "CREATE TABLE AS" set "ROW_COUNT" (Tom)
* Define SQLSTATE and SQLERRM to return the SQLSTATE and error
message of the current exception (Pavel Stehule, Neil)
These variables are only defined inside exception blocks.
* Allow the parameters to the "RAISE" statement to be expressions
(Pavel Stehule, Neil)
* Add a loop "CONTINUE" statement (Pavel Stehule, Neil)
* Allow block and loop labels (Pavel Stehule)
__________________________________________________________________
PL/Perl Server-Side Language Changes
* Allow large result sets to be returned efficiently (Abhijit
Menon-Sen)
This allows functions to use return_next() to avoid building the
entire result set in memory.
* Allow one-row-at-a-time retrieval of query results (Abhijit
Menon-Sen)
This allows functions to use spi_query() and spi_fetchrow() to
avoid accumulating the entire result set in memory.
* Force PL/Perl to handle strings as UTF8 if the server encoding is
UTF8 (David Kamholz)
* Add a validator function for PL/Perl (Andrew)
This allows syntax errors to be reported at definition time, rather
than execution time.
* Allow PL/Perl to return a Perl array when the function returns an
array type (Andrew)
This basically maps PostgreSQL arrays to Perl arrays.
* Allow Perl nonfatal warnings to generate "NOTICE" messages (Andrew)
* Allow Perl's strict mode to be enabled (Andrew)
__________________________________________________________________
psql Changes
* Add "\set ON_ERROR_ROLLBACK" to allow statements in a transaction
to error without affecting the rest of the transaction (Greg Sabino
Mullane)
This is basically implemented by wrapping every statement in a
sub-transaction.
* Add support for \x hex strings in psql variables (Bruce)
Octal escapes were already supported.
* Add support for "troff -ms" output format (Roger Leigh)
* Allow the history file location to be controlled by HISTFILE
(Andreas Seltenreich)
This allows configuration of per-database history storage.
* Prevent "\x" (expanded mode) from affecting the output of "\d
tablename" (Neil)
* Add "-L" option to psql to log sessions (Lorne Sunley)
This option was added because some operating systems do not have
simple command-line activity logging functionality.
* Make "\d" show the tablespaces of indexes (Qingqing Zhou)
* Allow psql help ("\h") to make a best guess on the proper help
information (Greg Sabino Mullane)
This allows the user to just add "\h" to the front of the syntax
error query and get help on the supported syntax. Previously any
additional query text beyond the command name had to be removed to
use "\h".
* Add "\pset numericlocale" to allow numbers to be output in a
locale-aware format (Eugen Nedelcu)
For example, using C locale 100000 would be output as 100,000.0
while a European locale might output this value as 100.000,0.
* Make startup banner show both server version number and psql's
version number, when they are different (Bruce)
Also, a warning will be shown if the server and psql are from
different major releases.
__________________________________________________________________
pg_dump Changes
* Add "-n" / "--schema" switch to pg_restore (Richard van den Berg)
This allows just the objects in a specified schema to be restored.
* Allow pg_dump to dump large objects even in text mode (Tom)
With this change, large objects are now always dumped; the former
"-b" switch is a no-op.
* Allow pg_dump to dump a consistent snapshot of large objects (Tom)
* Dump comments for large objects (Tom)
* Add "--encoding" to pg_dump (Magnus Hagander)
This allows a database to be dumped in an encoding that is
different from the server's encoding. This is valuable when
transferring the dump to a machine with a different encoding.
* Rely on pg_pltemplate for procedural languages (Tom)
If the call handler for a procedural language is in the pg_catalog
schema, pg_dump does not dump the handler. Instead, it dumps the
language using just "CREATE LANGUAGE name", relying on the
pg_pltemplate catalog to provide the language's creation parameters
at load time.
__________________________________________________________________
libpq Changes
* Add a PGPASSFILE environment variable to specify the password
file's filename (Andrew)
* Add lo_create(), that is similar to lo_creat() but allows the OID
of the large object to be specified (Tom)
* Make libpq consistently return an error to the client application
on malloc() failure (Neil)
__________________________________________________________________
Source Code Changes
* Fix pgxs to support building against a relocated installation
* Add spinlock support for the Itanium processor using Intel compiler
(Vikram Kalsi)
* Add Kerberos 5 support for Windows (Magnus)
* Add Chinese FAQ ([email protected])
* Rename Rendezvous to Bonjour to match OS/X feature renaming (Bruce)
* Add support for fsync_writethrough on Darwin (Chris Campbell)
* Streamline the passing of information within the server, the
optimizer, and the lock system (Tom)
* Allow pg_config to be compiled using MSVC (Andrew)
This is required to build DBD::Pg using MSVC.
* Remove support for Kerberos V4 (Magnus)
Kerberos 4 had security vulnerabilities and is no longer
maintained.
* Code cleanups (Coverity static analysis performed by EnterpriseDB)
* Modify "postgresql.conf" to use documentation defaults on/off
rather than true/false (Bruce)
* Enhance pg_config to be able to report more build-time values (Tom)
* Allow libpq to be built thread-safe on Windows (Dave Page)