Release Notes Postgres Plus® Advanced Server v9 ... - EnterpriseDB

footmouthΔιαχείριση Δεδομένων

16 Δεκ 2012 (πριν από 4 χρόνια και 6 μήνες)

1.839 εμφανίσεις

Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
1
 
Release  Notes
 
Postgres  Plus
®
 Advanced  Server
 
 
 
 
v9    (9.0.5.18
)
 
 
 
Table  of  Contents
 
I.
 
Release  Summary
................................
................................
................................
................................
.............
1
 
II.
 
PostgreSQL  v9.x  Integrated  Features
................................
................................
................................
..............
2
 
III.
 
Additional  Performan
ce  and  High  Availability  Enhancements
................................
................................
.......
4
 
IV.
 
Oracle  Compatibility  /  Capability  Features
................................
................................
................................
.....
6
 
V.
 
Enterprise  Module  Updates
................................
................................
................................
............................
9
 
VI.
 
Security
................................
................................
................................
................................
.........................
11
 
VII.
 
Installers
................................
................................
................................
................................
.......................
12
 
VIII.
 
StackBuilder  Plus
................................
................................
................................
................................
..........
13
 
IX.
 
Internationalization  /  Localization
................................
................................
................................
................
14
 
X.
 
Service  Pack  Maintenance
................................
................................
................................
............................
14
 
XI.
 
Documentatio
n  Updates
................................
................................
................................
..............................
19
 
XII.
 
Upgrade  Paths
................................
................................
................................
................................
..............
20
 
XIII.
 
Platform  Support
................................
................................
................................
................................
..........
23
 
XIV.
 
System  Requirements
................................
................................
................................
................................
...
24
 
XV.
 
Known  Issues
................................
................................
................................
................................
................
24
 
XVI.
 
How  to  Report  Problems
................................
................................
................................
..............................
25
 
I.

Release  Summary
 
EnterpriseDB  continues  to  advance  its  position  as  the  leader  in  delivering  the  innovation  and  low  cost  of  
open  source  based  databases  with  commercial  quality,  compatibility,  s
calability,  and  performance  in  this  
latest  release  of  Postgres  Plus  Advanced  Server  v9.    The  major  highlights  of  this  release  are:  1)  the  
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
2
 
integration  of  all  PostgreSQL  v9  features  including  native  support  for  the  64  bit  Windows  environment,  2)  
our  7
th
 gene
ration  of  Oracle  features,  3)  performance  and  data  loading  improvements,  and  4)  Microsoft  
SQL  Server  replication  support  using  xDB  Replication  Server,  and  5)  foundational  work  for  the  future  
release  of  PostgreSQL  Enterprise  Manager.    
 
II.

PostgreSQL  v9.x  Integ
rated  Features
 
A.

PostgreSQL  Merge  Version
 
This  version  of  Postgres  Plus  Advanced  Server  contains  the  merge  of  community  PostgreSQL  v9.0.5  
released  
September  25,  2011
.    For  more  details  visit:  
 
http://www.postgresql.org/docs/9.0/static/release
-
9
-
0
-
5.html


B.

Streaming  Replication
 
Standard  Server  now  has  b
uilt
-­‐
in
 native
 replication
.    
Standby  servers  can  now  connect  to  
a  master
 
server  
and  
be  sent  the  binary  logs  to  stay  updated  for  improv
ed  High  Availability  setups.  
Streaming  
Replication  is  an  asynchronous  mechanism  
where  
the  standby  server  lag
s
 behind  the  master.  But  
unlike  other  replication  methods,  this  lag  is  
extremely
 short,  and  can  be  as  little  as  a  single  transaction,  
depending  on  n
etwork  speed,  database  activity,  and  Hot  Standby  settings.  Also,  the  load  on  the  master  
for  
any  standby  server
 is  minimal,  allowing  a  single  master  to  support  
many
 
standby  servers
.
 
C.

Hot  Standby
 
Utilizing  Streaming  Replication  (or  log  shipping),  Hot  Standby  
allows  continuous  archive  standby  
servers  
to  execute  read
-­‐
only  queries.  
   This  feature  is  similar  to  Data  Guard  from  Oracle®.    A  Hot  
Standby  server  can  deliver  a  variety  of  benefits  including:  a)  faster  OLTP  and  reporting  performance  by  
offloading  reports  
to  a  standby  server,  b)  more  efficient  warm  standby  servers  for  improved  High  
Availability,  c)  testing  systems  in  parallel  with  the  same  data,  and  d)  migrating  seed  data  to  a  new  
system.    
The  net  effect  is  to  support  a  single  master  with  multiple  read
-­‐
only
 slave  servers.
 
D.

Easier  
Object  Permissions  Management
 
GRANT/REVOKE  IN  SCHEMA  supports  mass  permissions  changes  on  existing  objects,  while  ALTER  
DEFAULT  PRIVILEGES  allows  control  of  privileges  for  objects  created  in  the  future.  Large  objects  
(BLOBs)  now  supp
ort  privilege  management  as  well.  
 
E.

Faster
 VACUUM  FULL.  
 
The  implementation  of  this
 command  now  rewrites  the  entire  table  and  indexes,  rather  than  moving  
individual  rows  to  compact  space.  It  is  substantially  faster  in  most  cases,  and  no  longer  results  in  in
dex  
bloat.  
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
3
 
F.

New  Trigger  F
eatures
 
Advanced  Server  now  has  
SQL
-­‐
standard
-­‐
compliant  per
-­‐
column  triggers,  which
 only  f
ire  when  a  specific  
column  is  explicitly  UPDATED.  They  allow  you  to  avoid  adding  lots  of  conditional  logic  and  value  
comparisons  in  your  trigge
r  code.    Another  new  trigger  feature  is  
WHEN
 trigger
s
 
for  more  control  over  
when  triggers  fire
.  
This  can  dramatically  decrease  the  number  of  trigger  executions  and  reduce  CPU  
load  on  the  database  server.
 
G.

Exclusion  Constraints
 
These  provide  a  generalized  ve
rsion  of  unique  constraints,  allowing  enf
orcement  of  complex  conditions  
with  a  simple  option  to  an  ALTER  TABLE  command.    For  example,  an  exclusion  constraint  can  be  used  
to  automatically  reject  a  room  reservation  record  INSERT  whose  time  slot  overlaps  or  c
onflicts  with  an  
existing  reservation  record,  all  without  any  complex  coding  or  setup.
 
H.

Deferrable  Unique  Constraints
 
Mass  updates  to  unique  keys  are  now  possible  without  
difficult  work
-­‐
arounds
.  
Checking  of  constraints  
that  are  deferrable  
are  
postponed  unti
l  the  end  of  the
 statement  or
 transaction
.
   Currently,  UNIQUE,  
PRIMARY  KEY,  EXCLUDE,  and  REFERENCES  (foreign  key)  constraints  
support
 this  
feature
.  
 
I.

New  and  Enhanced  S
ecurity  features
 
These  
includ
e
 RADIUS  authentication,  LDAP  authentication  improvements,  a
nd  a  new  contrib  module  
passwordcheck  for  testing  password  strength.  
 
J.

Enhanced  Stored  Procedure  Support
 
The  DO  statement  supports  ad
-­‐
hoc  or  "anonymous"  code  blocks.  Functions  can  now  be  called  using  
named  parameters.  PL/pgSQL  is  now  installed  by  default,  a
nd  
PL/Perl
 and  
PL/Python
 have  been  
enhanced  in  several  ways,  including  support  for  Python3.  
 
K.

hstore  I
mpr
ovements
 
Improvements  include:  a)  the  addition  of
 new  functions
 and  operators,  b)  the  removal  of  size  limits  for  
Keys  and  values,  and  c)  GROUP  BY  and  DISTINCT  can  now  be  used.    These  feature  additions  
mak
e
 
hstore  a  full
y  
function
al
 key
-­‐
value  store  embedded
 in  PostgreSQL.
 
L.

Enhanced  Reporting  Q
ueries
 
New  features  
includ
e
 additional  windowing  options  (PRECEDING  and  FOLLOWING)  and  the  ability  to  
control  the  order  in  which  values  are  fed  to  aggregate  functions.  
 
M.

High
-­‐
P
erformance  LISTEN/
NOTIFY
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
4
 
This  new  implementation  of  LISTEN/NOTIFY
 
has  Pending  events
 now  stored  in  a  memory
-­‐
based  queue  
rather  than  a  table.  Also,  a  "payload"  string  can  be  sent  with  each  event,  rather  than  transmitting  ju
st  
an  event  name  as  before.  
 
N.

Multiple  Performance  E
nhancements
 
F
or  specific  types  of  queries,  including  elimination  of  unnecessary  joins
,  performance  has  been  
significantly  increased
.  This  helps  optimize  some  automatically  generated  queries,  such  as  those  
produced  by  object
-­‐
relational  mappers  (ORMs).  
 
O.

EXPLAIN  Enhancements
 
The  output  is  now  available  in  JSON,  XML,  or  YAML  format,  and  includes  buffer  utilization  and  other  
data  not  previously  available.  
 
P.

New  pg_upgrade  contrib  M
odule  
 
Removes  the  need  to  dump/
restore  a  database  to  upgrade  to  a  new  software  version.    S
upport
s
 
incredibly  fast  
in
-­‐
place  upgrades  from  8.3  or  8.4  to  9.0.  
   See  the  Upgrade  Paths  section  below.
 
 
For  a  complete  list  of  all  new  features  and  details  on  individual  features  please  visit:  
http://www.postgresql.org/docs/9.0/static/release
-
9
-
0.html#AEN99033
 
 
III.

Additional  Performance  and  High  Availability  Enhancements
 
A.

PL/pgSQL  Code  Profiler  (7656)
 
The  
PL/pgSQL  and
 SPL  Code
 
P
rofiler  is  a  plugin  that  instruments  PL/pgSQL  (and  SPL)  functions,  triggers,  
and  procedures.
   The  profiler  provides  in
-­‐
depth  characteristics  of  commands  run  against  the  database  
to  help  identify  and  remedy  performance  bottlenecks  as  well  as  info
rm  you  for  writing  new  optimized  
code  against  the  database.    When  the  profiler  is  loaded  into  a  backend  (and  enabled),  it  intercepts  
every  PL/pgSQL  statement  executed  and  writes  performance  information  about  each  statement  into  a  
table  of  your  choosing.
 
Fo
r  each  statement,  the  profiler  records:
 


execution  count  (number  of  times  each  statement  is  executed)
 


total  execution  time  (how  long  did  we  spend  executing  each  statement?)
 


longest  execution  time  (how  long  did  the  slowest  iteration  take?)
 


number  of  scans  (t
otal  number  of  sequential  and  indexed  scans)
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
5
 


blocks  fetched
 


blocks  hit  (blocks  found  in  buffer  pool)
 


tuples  returned
 


tuples  fetched  
 


tuples  inserted
 


tuples  updated
 


tuples  deleted
 
This  feature  is  documented  in  both  the  Oracle  Compatibility  Guide  and  the  Sca
lability  and  Performance  
Guide  for  Advanced  Server.  
 
B.

Index  Advisor  (16707)
 
Index  Advisor  is  a  module  that  helps  you  determine  the  application  tables  and  their  columns  on  which  
you  should  create  common,  B
-­‐
tree  type  indexes  to  reduce  the  execution  cost  of  qu
eries  you  expect  to  
use  on  your  tables.  Index  Advisor  works  with  Advanced  Server's  query  planner  by  creating  
"hypothetical  indexes"  for  the  query  planner  to  use  to  calculate  execution  costs  if  such  indexes  were  
available.    
 
Index  Advisor  comes  pre
-­‐
installe
d  with  Postgres  Plus(R)  Advanced  Server.    Documentation  for  the  
usage  of  Index  Advisor  can  be  found  in  its  README  file  (README
-­‐
index_advisor.txt)  located  in  the  
installation  directory.
 
C.

EDB*Loader  support  
for  "
DIRECT
"  and  "E
RROR"  in  c
ontrol  file
 (16505)
 
EDB
*Loader  now  has  syntax  compatibility  with  control  files  product  by  Informatica.    This  
allow
s
 users  
to  specify  DIRECT  and  ERRORS  parameters  in  the  OPTIONS  clause  in  the  control  file
.
 
D.

EDB*Loader  
support  for  parallel  data  l
oad
 (16507)
 
The  Direct  Path  load  fea
ture  in  
EDB*Loader  
has  been  enhanced  to  support  parallel  data  loads.    In  
Direct  Path  loads,  data  is  written  
directly
 to  the  database  pages,  which  are
 then  synchronized  to  disk.  
The  insert  processing  associated  with  a  conventional  path  load  is  bypassed,  the
reby  resulting  in  a  
performance  improvement.
   
 
With  parallel  load  support  t
he  performance  of  a  direct  path  load  can  be  further  improved  by  
distributing  the  loading  process  over  two
,  three,
 or  more  sessions  running  concurrently.  Each  session  
runs  a  direct  p
ath  load  into  the  same  table
 providing  a  significant  boost  in  overall  data  load  
performance
.
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
6
 
E.

Migration  Toolkit  support  for  parallel  data  load  (17288)
 
The  Postgres  Plus  Advanced  Server  Migration  Toolkit  can  now  
load  multiple  tables  in  parallel  (
using  
thread
s)  to  
dramatically  
improve  data  migration  load  time
s
.
 
F.

Migration  Studio  support  for  parallel  data  load  (17646)
 
The  Migration  Toolkit  Parallel  Data  Load  feature  has  been  integrated  into  the  Migration  Studio.    This  
allows  the  user  to  specify  the  number  of  thr
eads  to  use  
to  perform  data  load
s
 for  individual  tables  
using
 parallel  jobs.
 
G.

Dynamic  InfiniteCache  Node  Expansion  (17173)
 
Users  can  now  dynamically  add  new  or  remove  old  cache  blades/nodes  without  requiring  a  restart  of  
the  database  server,  improving  data  
availability  and  providing  seamless  administration  to  increasing  
performance  for  large  in
-­‐
memory  data  stores.
 
H.

Partitioning  Scripts  (
12522
)
 
Postgres  Plus  Advanced  Server  now  provides  a  number  of  partitioning  functions  designed  to  make  
partition  creation  muc
h  easier  to  manage.    The  parameterized  functions  create  customized  SQL  
command  strings  that  are  then  run  against  the  target  database  table  to  create  and  prepare  the  
partitions.    For  documentation  and  usage  examples,  refer  to  the  on  disk  file:  
partitioning_
functions_README
.txt  in  the  /docs  directory  of  your  installation.
 
IV.

Oracle  
Compatibility
 /  Capability  Features
 
A.

SPL  Code  Profiler  (7656)
 
The  code  profiler  referenced  in  the  previous  section  also  works  on  the  Oracle  compatible  SPL  language  
in  Advanced  Server.  
 In  fact  the  code  profiler  is  100%  compatible  with  
Oracle's  DBMS_PROFILER
 and  
implements  additional  features  as  well.
 
B.

EDB*Loader  support  for  remote  file  loading  (13570)
 
Users  can  now  
run  
EDB*Loader  on  a  remote  
machine
 where  the  input  data  source  resides.  U
sers  can  
use  the  standard  input  feature  to  pipe  the  data  from  the  data  source  such  as  another  program  or  
script,  directly  to  EDB*Loader
.    EDB*Loader  can  then  load
 the  
data  into  a  
table  
that  resides  on
 
a
 
remote  database
 host
.  This  bypasses  the  process  of  ha
ving  to  create  a  data  file  on  disk  for  EDB*Loader.  
To  use  remote  load  feature  of  edbldr,  one  must  specify  "stdin"  as  the  name  of  the  infile  and  then  feed  
data  to  edbldr  through  stdin.  One  should  also  use  
-­‐
h  option  to  specify  the  remote  server’s  host  name  
o
r  IP  address.  A  sample  control  file  may  look  like  this:
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
7
 
 
load data infile 'stdin' append into table emp

fields terminated by ' ' optionally enclosed by '"'

(empno, sal, name)


And  edbldr  can  be  invoked  as:
 

./edbldr
-
h <server
-
ip>
-
d edb userid=edb/edb con
trol=sample.ctl < sample.data OR

cat sample.data | ./edbldr
-
h <server
-
ip>
-
d edb userid=edb/edb control=sample.ctl

 
When  using  remote  load  feature,  all  other  files  such  as  control/log/bad/discard  files  are  also  treated  as  
files  local  to  the  client  machine
.  So  the  path  names  should  be  valid  on  the  client  machine.
 
C.

EDB*Loader  
support  for  parallel  data  l
oad
 (16507)
 
EDB*Loader  now  has  a
 parallel  option  
to  support  the
 large  data  load
s
 comprised  of  multiple  files  that  
all  need  to  be  loaded  into  a  single  table.  
ED
B*Loader
 enable
s
 user
s
 to  invoke  multiple  load  tasks  
in  
parallel  
where  each  
one  
takes  one  of  the  load  files  and  loads  the  target  table
 simultaneously
.
 The  
following  example  demonstrates  2  such  load  sessions:
 
$ /opt/PostgresPlus/9.0AS/bin/edbldr
-
d edb USER
ID=enterprisedb/password
CONTROL=emp_parallel_1.ctl DIRECT=TRUE PARALLEL=TRUE

WARNING: index maintenance will be skipped with PARALLEL load

EDB*Loader: Copyright (c) 2007
-
2010, EnterpriseDB Corporation.


$ /opt/PostgresPlus/9.0AS/bin/edbldr
-
d edb USERID=
enterprisedb/password
CONTROL=emp_parallel_2.ctl DIRECT=TRUE PARALLEL=TRUE

WARNING: index maintenance will be skipped with PARALLEL load

EDB*Loader: Copyright (c) 2007
-
2010, EnterpriseDB Corporation.

D.

EDB*Loader  support  for  Date  masking  (16506)
 
Date  maskin
g  
is  now  supported  
that  allows  a  user  to  specify  the  data  format  expected  in  the  input  data  
using  combinations  of  DD,  MM,  YY,  HH  etc.
   
 
E.

VARRY  collection  support
 (12580)
 
Postgres  Plus  Advanced  Server  now  has  VARRAY  support.  
A  varray  or  variable
-­‐
size  array  i
s  a  type  of  
collection  that  associates  a  positive  integer  with  a  value.  In  many  respects,  it  is  similar  to  a  nested  
table.
 
F.

xmltype  data  type  support
 (17251)
 
Oracle  native  xmltypes  are  now  mapped  to  Advanced  Server’s  xml  type.
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
8
 
G.

Constructor  functions  for  obje
ct  types  and  nested  table  types  (10727)
 
In  Oracle,  a  constructor  function  is  needed  in  certain  cases  to  initialize  a  variable  declared  using  a  
nested  t
able  type  or  an  object  type.  
Postgres  Plus  Advanced  Server,  
now  supports  
the  constructor  
function  
for
 Ora
cle  code  initializing  nested  tables  and  object  types  
that  previously  
result
ed
 in  a  syntax  
error.
 
H.

SUBSCRIPT_BEYOND_COUNT  and  SUBSCRIPT_OUTSIDE_LIMIT  Exception  Support  for  Nested  
Tables  (17669)  
 

Advanced  Server  
now  
raises  the  "subscript_beyond_count"  except
ion  if  a  program  attempts  to  access  
a  non
-­‐
existent  element  in  a  nested  table.    The  server  raises  the  "subscript_outside_limit"  exception  if  a  
program  attempts  to  access  an  element  of  a  nested  table  using  a  negative  or  zero  subscript.
 
 
I.

LIMIT  Method  Support  
for  Nested  Tables  and  Associative  Arrays  (17964)
 

The  LIMIT  method  returns  the    maximum  number  of  elements  that  a  collection  may  contains.  For  
nested  tables  and  associative  arrays,  which  have  no  maximum  size,  LIMIT  returns  NULL.
 
 
J.

Large  object  support  over  
database  links  (15877)
 
Postgres  Plus  Advanced  Server  now  supports  statements  over  Oracle  database  links  such  as:
 
Insert into ppastable select * from oracletablewithlob

K.

ECPG  enhancements  
for  Pro*C  Compatibility  features
 (16995)
 
ecpgPlus  enhances  Oracle  comp
atibility  by  adding  support  for  the  following  Pro*C  features:
 


Oracle  Dynamic  SQL:  Method  4  (DESCRIBE  BIND  VARIABLES/DESCRIBE  SELECT  LIST)
 


Host  variables  declared  outside  of  BEGIN/END  DECLARE  SECTION
 


DECLARE  DATABASE
 


DECLARE  STATEMENT
 


Parameterized  anonymou
s  blocks  (EXECUTE...END
-­‐
EXEC)
 


CALL  statement  (with  support  for  IN,  INOUT,  and  OUT  parameters)
 


Pro*C
-­‐
compatible  C  pre
-­‐
processor  (#if,  #else,  #ifdef,  #define,  macros)
 


Minor  enhancements  (COMMIT  RELEASE,  ROLLBACK  RELEASE,  PREPARE...SELECT,  WHENVER...  
DO  CONTI
NUE)
 
L.

Enhanced  support  for  Nested  Types  (
17199
)
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
9
 
A  use  case  that  used  the  declaration  ‘
TYPE  typ_rec_source  IS  RECORD  (a  type_t);
’  used  to  throw  an  
error.    This  has  been  fixed  so  that  the  following  works  now:
 
declare


CURSOR tr_cur IS select empno from emp;


TYPE type_t IS TABLE OF number INDEX BY BINARY_INTEGER;



TYPE typ_rec_source IS RECORD (a type_t);


rec_source typ_rec_source;

begin


OPEN tr_cur;


FETCH tr_cur BULK COLLECT INTO rec_source.a;


dbms_output.put_line(rec_source.a.count);


close tr_c
ur;

end;


M.

Collection_Is_Null  exception  s
upport  (14663)
 

The  Collection_Is_Null  exception  is  now  supported.  When  a  collection  method  other  than  EXISTS  is  
applied  to  an  initialized  nested  table,  the  database  server  returns  a  Collection_Is_Null  exception.  
 
N.

SY
S_EXTRACT_UTC()  Function  Support  (17848)
 

The  SYS_EXTRACT_UTC()  function  returns  Coordinated  Universal  Time  (UTC,  formerly  Greenwich  Mean  
Time)  from  a  timestamp.
 
 
O.

DBMS_SQL.DESCRIBE_COLUMNS  procedure  (11646)
 
The  DBMS_SQL.DESCRIBE_COLUMNS  procedure  describes
 the  column  for  a  cursor  opened  and  parsed  
through  DBMS_SQL.
 
V.

Enterprise  Module  Updates
 
A.

Client  Connectors
 
1.

JDBC
 
The  version  of  the  Po
stgres  Plus  Advanced  Server  v9.0
 JDBC  connector  is  based  on  pgJDBC  9.0
-­‐
801  released  on  September  20,  2010.    Two  versions  are  
available,  one  built  against  JDK  1.4  
(edb
-­‐
jdbc14.jar)  and  one  built  against  JDK  1.6  (edb
-­‐
jdbc16.jar).
 
2.

ODBC
 
The  version  of  the  Postgres  Plus  Advanced  Server  v9.0  ODBC  connector  is  b
ased  on  psqlODBC  
9.00.0200,  release  on  August  10,  2010.
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
10
 
3.

.NET
 
The  version  of  
the  Postgres  Plus  Advanced  Server  v9.0  .NET  connector  is  b
ased  on  Npgsql  
2.0.11,  released  on  November  5,  2010.
 
B.

pgpool  II  (New)
 
The  Postgres  Plus  Advanced  Server  installer  now  includes  and  installs  pgpool  II  v3.0.3.    pgpool
-­‐
II  is  
middleware  that  works  betwe
en  PostgreSQL  database  servers  and  PostgreSQL  database  clients.    It  
provides  the  following  enterprise  scaling  features:  connection  pooling,  replication,  load  balancing,  
connection  limitations  and  parallel  query.  NOTE:  a  64  bit  version  of  pgpool
-­‐
II  is  not  a
vailable  on  the  
Windows  64  platform.
 
C.

Slony
 
The  version  of  Slony  shipped  as  part  of  Postgres  Plus  Advanced  Server  v9  is:  
2.0.6 and contains the
following changes:



Fix  for  bug  #158,  Removing  extranous  '//'  from  the  perl  script
 


Fix  for  bug  #155,  slon  can  segf
ault  while  shutting  down
 


Fix  for  bug  #162,  typenameTypeId()  changes  signatures  in  9.1
 


Fix  for  bug  #160,  if  slon  gets  confused  waiting  for  its  child  it  will  exit
 


Fix  for  bug  #159,  distclean  deletes  the  slony_logshipper  flex/bison  generated  files
 


Fix  for  bug
 #154,  previous  fix  did  not  work
 


Fixed  script  syntax  error,  bug  #108
 


Memory  corruption  with  large  rows
 


Memory  leak  fixes
 


Various  documentation  updates
 


Support  for  newer  versions  of  flex
 


Fixes  to  stored  procedures  called  by  CLONE  NODE  FINISH  (bug  #119)
 
   
 
F
or  more  details,  please  visit:  
http://www.slony.info/
 
 
D.

PostGIS
 
The  version  of  PostGIS  shipped  in  StackBuilder  Plus  is:  
1.5.2.
 
E.

PL/Java
 
The  version  of  PL/Java  shipped  as  part  of  Postgres  Plus  Advanced  Server  v9  is:  
1.4
.2.
 
F.

PL/Python
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
11
 
Python  has  been  added  as  an  available  packaged  procedural  language  in  Advanced  Server  and  was  
built  against  ActivePython  2.7.1.
 
G.

pgSNMPd
 
The  version  of  pgSNMPd  shipped  as  part  of  Postgres  Plus  Advanced  Server  v9  is:  
1.0
-
released 2007
-
08
-
07.
NOTE:  a  64  bit  version  of  pgSNMPd  is  not  available  on  the  Windows  64  platform.
 
H.

iCache  (Infinite  Cache)
 
The  version  of  iCache  shipped  as  part  of  Postgres  Plus  Advanced  Server  v9  is  b
ased  on  memcached  
1.4.5
 
-­‐
 released  2010
-­‐
04
-­‐
03.
 NOTE:  a  64  bit  version  of  iC
ache  is  not  available  on  the  Windows  64  
platform.
 
VI.

Security
 
Postgres  Plus  Advanced  Server
 is  the  most  secure  open  source
 based
 database  out
-­‐
of
-­‐
the
-­‐
box
.    This  latest  
version  of  Advanced  Server  continues  to  build  upon  that  solid  foundation.  
 
 
A.

SQL/Protect  

 Pr
otection  against  SQL  Injection  Attacks
 
SQL/Protect  is  an  add
-­‐
on  security  module  from  EnterpriseDB  installed  with  Postgres  Plus  Advanced  
Server.      
 
 
While  Advanced  Server  is  well  known  for  being  the  most  secure  open  source  based  database,  
there  is  
one  sourc
e  of  attack  
against  any  database  
that  is  not  easily  protected  against  in  a  centralized  and  
standardized  fashion
;  
the  wide  variety  of  
SQL  
code  in  client  applications  that  accesses  a  database.    
These  attacks  hitch  a  ride  on  the  back  of  SQL  commands  to  compro
mise  or  damage  data.    
Preventing  
SQL/Injection  is  normally  left  up  to  
the  skills  of  the  
individual  applicatio
n  developer
,  which  can  vary  
greatly  between  people
 and  the  projects  they  work  on,  and  be  compromised  by  tight  schedules  a
nd  
commercial  
delivery  
pre
ssures.
 
 
SQL/Protect  adds  a  
standardized,  quick  to  setup,  and  easy  to  use
 security  layer  
on  the  database  server  
that  guards  against  SQL  Injection  attacks  and  is  managed  by  DBAs.    
It  provides  a  consistent  approach  
against  a  standardized  set  of  attack  vector
s  and  works  for  any  application.
 
 
SQL/Protect  is  an  optionally  installed  module  that  adds  an  additional  layer  of  security  
underneath
 
the  
normal  database  security  policies  by  examining  
inbound
 queries
 
and  stopping  common  SQL  Injection  
profiles.  
 
SQL
/
Protect
 examines  queries
 
for  the  following  types  of  
attack  vectors
:
 
 


Unauthorized  Relations
 


Utility  Commands
 


SQL  Tautology
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
12
 


Unbounded  DML
 
See  the  
SQL/Protect  documentation  in  the  Postgres  Plus  Advanced  Server  Guide  for  more  information  
at:  
http://www.enterprisedb.com/documentation/english
 
VII.

Installers
 
A.

Installer  Technology
 
Advanced  Server  utilizes  BitRock  installer  technology  and  provides  the  following  features:
 


common  installer  technology  for  all  plat
forms
 


silent  install  option
 typically  used  by  ISVs  
 


install  options  for  users  with  limited  
privileges (e.g.
non
-­‐
root  
Linux  users  and  non
-­‐
administrator  
Windows  users)  
 
B.

StackBuilder  Plus
 
Advanced  Server  is  distributed  with  StackBuilder  Plus  which  provides  a  
wide  array  of  complimentary  
components  to  the  core  database  server.    StackBuilder  Plus  also  provides  Update  Monitor  to  notify  you  
when  installed  packages  have  updates  and  helps  you  download  and  install  the  updates.    See  the  next  
section  for  more  details  on
 StackBuilder  Plus.
 
C.

Product  Keys  for  Localized  Language  Installations
 
If  you  wish  to  install  Advanced  Server  in  localized  Japanese,  Korean,  Traditional  Chinese,  Simplified  
Chinese,    or  a  Central  American  or  South  American  language  you  will  need  to  enter  a  
Product  Key
 when  
the  installer  executes.    Product  keys  can  be  obtained  from  distributors  in  each  country  as  follows:
 

Locale
Code

Country
Locale


Distributor


Contact


Contact
Email

ja_jp

Japanese

EDB
-
Japan

Yuji Fujita

yuji.fujita@enterpri
sedb.com

ja_jp

Japanese

COMTEC Inc.

Jun Satoyoshi

jsatoyoshi@ct
-
net.co.jp

ja_jp

Japanese

SIOS
Technology, Inc

Noriko Daitoku

tdaitoku@sios.com

zh_tw

Chinese
Taiwan (ROC)

EDB
-
Japan

Yuji Fujita

yuji.fujita@enterprisedb.com

zh_
cn

Chinese (PRC)


EDB
-
Japan

Yuji Fujita

yuji.fujita@enterprisedb.com

zh_hk

Chinese (Hong
Kong S.A.R)

EDB
-
Japan

Yuji Fujita

yuji.fujita@enterprisedb.com

ko_kr

Korean

Daou Tech, Inc.

K.I Lee

kilee@daou.co.kr

es_ar

Argentina
-

Sp
anish

Genup
-
IT

Max Garcia

mgarcia@genup
-
it.com

pt_br

Brazil
-

Tecnisys

Rogerio Carvalho

rogerio.carvalho@tecnisys.com.br

Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
13
 
Portuguese

es_bo

Bolivia
-

Spanish

CommIT

Oxiel Contreras

oxiel.contreras@commit.com.bo

Es_bo

Bolivia
-

Spanish

iTEAM

Marco Orellan
a

marco.orellana@iteam.com.bo

es_cl

Chile
-

Spanish

SEIS, SA

Hector Barrios

hector.barrios@seissoft.cl


es_co

Colombia
-

Spanish

Tayronaweb

Rafael Cortez

rafael.cortes@tayronaweb.com


es_ec

Ecuador
-

Spanish

Fugu Ecuador

Fernando Calderon

ac@fugu.ec


es_gt

Guatemala
-

Spanish

Systemshause
Westfalia

Esteban Calderon

esteban.calderon@westfalia
-
it.com

es_hn

Honduras
-

Spanish

Systemshause
Westfalia

Esteban Calderon

esteban.calderon@westfalia
-
it.com

es_mx

Mexico
-

Spanish

TEAM

Claudia Garcia

cgar
cia@teamnet.com.mx

es_ni

Nicaragua
-

Spanish

Systemshause
Westfalia

Esteban Calderon

esteban.calderon@westfalia
-
it.com

es_pe

Peru
-
Spanish

Software Libre
Andino

Diego Francia

dfrancia@softwarelibreandino.com

es_py

Paraguay
-

Spanish

Smar
techpy

Charles Charotti

ccharotti@gmail.com

es_sv

El Salvador
-

Spanish

Systemshause
Westfalia

Esteban Calderon

esteban.calderon@westfalia
-
it.com

es_uy

Uruguay
-

Spanish

Ideasoft

Enrique Tucci

etucci@ideasoft.biz

es_ve

Venezuela
-

Spanish

HIA Tech
nology
de Venezuela

Ernesto Lozano

elozano@hiatechnology.com.ve


 
This  list  of  distributors  possessing  product  keys  is  also  available  at:  
 
http://www.enterprisedb.com/product
-
keys

VIII.

StackBuilder  Plus
 
P
ostgres  Plus  Advanced  Server  now  contains  StackBuilder  Plus.    StackBuilder  Plus  makes  it  easy  to  
install  and  maintain  high  value  components  that  extend  or  compliment  your  Advanced  Server  
installation.    Some  of  the  key  feature  highlights  of  StackBuilder  Plu
s  include:
 
A.

Update  Monitor
 
Update  Monitor  will  notify  you  when  new  updates  are  available  for  any  component  you  have  installed  
including  the  database  server,  pgAdmin  (a.k.a.  Postgres  Studio),  and  any  StackBuilder  Plus  modules.    
You  can  then  start  StackBuilde
r  Plus  to  download  the  component,  read  its  release  notes  and  install  it.
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
14
 
Advanced  Server  v9  will  receive  automatic  updates  from  EnterpriseDB  using  the  Update  Manager  
installed  with  v9  (you  will  see  the  blue  elephant  icon  in  the  your  desktop  system  tray).    
You  will  not  be  
able  to  update  v9  using  the  older  update  mechanism  found  in  the  DBA  Management  Console.
 
B.

Value  Added  enterprise  modules
 
1.

Postgres  Plus  HQ
 is  not  supported  with  Postgres  Plus  Advanced  Server  v9.    Please  download  
and  try  the  EnterpriseDB  develo
ped  management/monitoring/tuning  tool  Postgres  
Enterprise  Manager  at:
 
                           
           
http://www.enterprisedb.com/download
-
postgres
-
enterprise
-
manager
 
 
IX.

Internationalization
 /  Localization
 
Features  new  to  Postgres  Plus  Advanced  Server  v9  have  not  yet  been  localized  into  the  following  
languages:
 


Japanese
 


Korean
 


Simplified  Chinese
 


Traditional  Chinese
 
X.

Service  Pack  Maintenance
 
Over  500  maintenance  items  (bug  fixes  and  enhancement
s)  have  been  addressed.    Some  of  the  more  
interesting  ones  are  noted  below:
 
A.

General
 
I
tems
 
1.

README  files.    
For  consistency  in  finding  all  README  documents  easily,  the  files  have  now  
all  been  moved  to  the  $PPAS_HOME/doc  directory.  (18479)
 
B.

Database  Server
 
1.

PPAS
 8.4  returned  "XX000"  SQLSTATE  error  for  all  OCI  library  errors.  The  OCI  error  messages  
have  been  improved  to  use  the  codes  specified  by  the  SQL/MED  specification.  (17215)
 
2.

Increased  memory  usage  was  observed  with  the  dblink_ora  data  copy  function  for  large
 
volume  data  migration  from  Oracle  to  Advanced  Server  with  a  big  row  size.  The  issue  has  
been  addressed  by  specifying  the  amount  of  pre
-­‐
fetching  in  OCI  in  bytes,  instead  of  rows.  
The  pre
-­‐
fetch  size  was  set  to  a  fixed  number  (1000  rows),  now  it  can  be  custo
mized  by  
adjusting  WORK_MEM  setting  in  kilobytes.  (17331)
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
15
 
3.

Changed  the  way  Redwood
-­‐
style  OUT  parameters  were  implemented.  Out  parameters  in  
SPL  functions  are  now  treated  the  same  as  in  other  PL  languages,  and  are  no  longer  
included  in  the  function  signature
.  Users  can  now  issue  ALTER,  DROP,  GRANT,  and  REVOKE  
FUNCTION  statements  using  PostgreSQL  compatible  syntax  as  well  as  the  syntax  supported  
in  earlier  Advanced  Server  versions.  (17344)
 
4.

EDB*Loader  was  modified  to  return  a  warning  when  a  user  tries  to  use  pa
rallel  load  option  
for  conventional  path  load.  (17425)
 
5.

EDB*Loader  returned  an  error  when  there  are  extra  fields  specified  in  the  data  file.  This  
issue  has  been  addressed.  EDB*Loader  now  ignores  extra  data  fields.  EDB*Loader  was  also  
modified  to  return  an  e
rror  when  there  are  less  fields  specified  in  the  data  file  unless  
TRAILING  NULLCOLS  are  specified.  (17581)
 
6.

InfiniteCache  was  modified  to  address  the  following  issues  (17603)
 
a)

The  ICACHE  FAIL  flag  on  a  dirty  buffer  was  cleared  before  it  was  successfully  writ
ten  
to  the  cache  node.  This  might  leave  a  stale  version  of  the  page  in  cache;
 
b)

The  ICACHE  READ  flag  was  not  set  when  the  icache  get  operation  fails  in  some  
scenarios;
 
c)

The  failure  counter  was  not  updated  correctly  for  manual  offline  operations  in  some  
error  
scenarios;
 
7.

Defining  a  package  cursor  with  UNION/INTERSECT/EXCEPT  queries  returned  a  syntax  error.  
This  issue  has  been  fixed.  Package  Cursor  with  SET  operation  queries  can  now  be  
successfully  defined.  (17650)  
 
8.

Initdb  operation  failed  on  Windows  for  Chinese  
Hong  Kong  locale.  The  issue  was  caused  by  
the  fact  that  the  Windows  setlocale()  function  does  not  work  with  locale  names  that  have  
dots  in  the  country  name.  There  are  three  such  countries  in  Windows  locales:  "Hong  Kong  
S.A.R.",  "Macau  S.A.R.",  and  "U.A.E."
The  issue  has  been  fixed  by  mapping  those  local
es  to  
alternative  names  (17207):
 
a)

"*_Hong  Kong  S.A.R.*"  is  now  mapped  to  "*_HKG.*"
 
b)

"*_Macau  S.A.R.*"  is  now  mapped  to  "ZHM"
 
c)

"*_U.A.E.*"  is  now  mapped  to  "*_ARE.*"  
 
9.

Client  disconnects  were  erroneously  logged  in  
the  main  database  server  log  instead  of  the  
edb  audit  log  when  edb_audit_disconnect  was  set.  The  issue  has  been  addressed;  
disconnects  are  now  logged  in  the  edb  audit  log.  (17821)
 
 
10.

The  database  server  returned  "invalid  byte  sequence  for  encoding  UTF8"  erro
r  when  users  
tried  to  retrieve  remote  data  through  a  libpq  database  link,  where  encoding  of  the  local  and  
remote  servers  was  set  to  UTF8  and  client_encoding  was  set  to  SJIS.  The  issue  has  been  
addressed  by  using  correct  client_enc
oding  in  database  links.  (
18022)  
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
16
 
11.

When  used  in  a  transaction,    CURRENT_TIMESTAMP  returned  the  timestamp  at  the  
beginning  of  the  transaction  instead  of  the  actual  timestamp  when  the  function  executed.  
This  issue  has  been  addressed  by  mapping  "current_timestamp"  to  "clock_timestamp",
 
which  returns  the  actual  timestamp  when  t
he  function  is  executed.  (17859)  
 
12.

With  previous  releases,  users  have  reported  increased  memory  usage  when  creating  a  local  
table  by  selecting  data  from  a  large  table  in  a  remote  database  over  a  database  link.  In  th
is  
release,  the  database  link  getnext()  function  has  been  modified  to  run  in  a  short
-­‐
lived  
memory  context  to  avoid  increased  memory  usage.  (18275)
 
13.

Previously,  DBMS_ALERT  reported  'ERROR:  lock  request  error'  message  if  you  attempted  to  
register  more  than  25
6  concurrent  alerts.  In  this  release,  the  implementation  of  
DBMS_ALERT  has  been  modified  to  accept  a  maximum  of  500  concurrent  alerts.  You  can  
also  specify  the  maximum  number  of  alerts  with  the  "DBMS_ALERT.MAX_ALERTS"  GUC  in  
the  postgresql.conf  file;  speci
fy  a  minimum  value  of  0,  and  a  maximum  value  of  500.  The  
default  value  is  100.(18509)
 
14.

Previously,  the  server  reported  "ERROR:  ORA
-­‐
01406:  fetched  column  value  was  truncated"  
if  half
-­‐
width  kana  characters  in  SJIS    encoding  were  selected  over  an  OCI
-­‐
type  data
base  link.  
The  problem  was  caused  by  insufficient  buffer  allocation  for  half
-­‐
width  kana  characters  
(kana  characters  can  expand  from  one  to  three  bytes  when  converted  from  SJIS  to  UTF
-­‐
8).    
In  this  release,  the  database  server  has  been  modified  to  reserve  su
fficient  buffer  space  
when  fetching  data  via  an  OCI  database  link.  (18333)
 
15.

With  past  releases,  users  have  reported  slow  performance  when  executing  a  SELECT  
statement  that  includes  multiple  condition  clauses  over  a  database  link.  In  this  release,  
database  l
inks  have  been  optimized  for  such  queries.  If  a  query  contains  a  condition  (that  
involves  a  remote  relation)  that  can  not  be  evaluated  on  the  remote  server,  other  eligible  
conditions  are  now  executed  on  the  remote  server.  (18098)
 
16.

With  past  releases,  users  
have  reported  slow  performance  when  executing  a  SELECT  
statement  that  includes  an  'IN"  clause  over  a  database  link.  In  this  release,  database  links  
have  been  optimized  to  evaluate  the  'IN"  operator  on  the  remote  server.  (18099)
 
17.

Previously,  the  to_timestamp
()  function  produced  incorrect  results  if  the  number  of  
embedded  spaces  did  not  match  the  format  string.  For  example,  to_timestamp('2011
-­‐
03
-­‐
18_23:38:15',  'YYYY
-­‐
MM
-­‐
DD__HH24:MI:SS')  (where  each  underscore  represents  a  space  
character);  returned  the  erroneous
 result:  18
-­‐
MAR
-­‐
11  03:38:15  +00:00.    The  problem  has  
been  corrected  in  this  release.  (18082)
 
18.

Previously,  if  a  package  definition  contained  a  non
-­‐
qualified  object  that  was  created  under  a  
different  schema,  the  schema  information  of  the  object  was  lost  durin
g  the  dump  process,  
causing  the  subsequent  restore  to  fail.    This  release  includes  a  new  function  that  constructs  
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
17
 
the  package  definition  with  the  schema  information  preserved;  pg_dump  has  been  
modified  to  use  the  new  function.  (16757,16743)
 
19.

In  previous  rel
eases,  pg_dump  generated  incorrect  ACL  statements  for  procedures,  causing  
the  subsequent  restore  to  fail.  In  this  release,  pg_dump  generates  accurate  ACL  
statements.(18232)
 
20.

The  EDBldr  control  file  has  support  to  provide  a  date  mask  for  date/timestamp  types
.  But  
the  date  mask  works  only  if  given  in  single  quotes,  it  didn't  work  when  the  date  mask  is  
given  in  double  quotes.    Both  single  and  double  quotes  are  now  supported  as  they  are  in  
Oracle.  (19516)
 
21.

Out  parameter  handling  with  stored  procedures  was  causing
 a  memory  leak,  the  leak  is  only  
noticeable  when  an  SPL  procedure/function  with  OUT  parameter  is  invoked  inside  a  big  
loop.  (19605)
 
22.

The  optimizer  hints  wasn't  handled  for  some  nodes,  it  was  throwing  "unrecognized  path  
type".  The  hints  feature  has  been  upda
ted  to  handle  more  node  types  that  can  appear  in  
the  plan  (19348)
 
23.

dblink_ora_copy  function  was  taking  long  time  to  fetch  rows  from  the  remote  oracle  
instance,  it  was  pre
-­‐
fetching  one  row  at  a  time  which  was  causing  multiple  round  trips  to  
the  remote  oracle
 server.  The  dblink_ora  module  is  updated  to  prefetch  rows  based  on  
memory  which  reduces  the  number  of  round  trips  and  hence  improve  the  performance  of  
the  function  (19340)
 
24.

Dependency  marking  wasn't  being  done  in
-­‐
case  of  package  variable  being  used  as  a  de
fault  
value  by  another  object.  This  was  yielding  a  cache  lookup  fail  error  the  referenced  object  
was  accessed  after  the  package  has  been  dropped.  This  issues  has  been  fixed.  (19121)
 
25.

Invoking  edb_get_server_processes()  function  would  kill  the  database  servi
ce  on  windows.  
This  was  caused  by  a  missing  "return"  in  windows  specific  function.  (19224)
 
26.

DBMS_Profiler  dbms_profiler.pause_profiler  and  dbms_profiler.resume_profiler  sometimes  
throw:  ERROR:    A  subprogram  was  called  with  an  incorrect  parameter.    (19106)
 
27.

D
BMS_Profiler  dbms_profiler.stop_profiler()  sometimes  caused:  ERROR:    query  returned  no  
rows.    (19129)  
 
28.

DBMS_PROFILER  start_profiler()  does  not  return  run  number.    (19139)  
 
29.

Not  allowed  to  DROP  USER  if  the  user  has  been  granted  privileges  on  a  package.  There
 was  
an  issue  with  dependency  marking  when  the  user  has  grants  on  a  package.    (18969)
 
30.

Syntax  error  gets  thrown  by  server  when  USING  is  used  with  BULK  COLLECT  (19039)
 
31.

Create  or  Replace  Package  command  was  throwing  a  mismatch  error  in  a  specific  scenario  
whe
n  a  package  procedure  contained  an  out  parameters  and  a  parameter  with  default  
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
18
 
value.  Creating  the  package  header  the  second  time  without  making  any  changes  was  
yielding  a  mismatch  error.  (19038)
 
32.

After  invoking  DELETE  method  on  a  nested  collection,  the  sub
sequent  insertion  or  EXTEND  
method  invocation  was  causing  a  subscript  beyond  count  error.  This  has  been  addressed  by  
setting  the  collection  logical  size  to  0  when  DELETE  is  called  on  the  collection  for  the  first  
time.  (19672)
 
C.

Client  Connectors
 
1.

TBD
 
D.

Migratio
n  
 
Studio
 
1.

The  Migration  Studio  GUI  is  now  available  at:  
http://www.enterprisedb.com/downloads/add
-
on
-
components
-
bundles
 
E.

Migration
 
Toolkit
 
1.

In  case  of  migration  from  Postgres  Pl
us  Advanced  Server  to  Oracle,  'NOT  NULL'  constraint  
was  not  being  migrated.  This  has  been  fixed.  (16887)
 
2.

If  any  of  the  column  values  contained  tab  character,  it  conflicted  with  the  tab  delimiter  used  
during  COPY  operation.  Now  the  tab  delimiter  is  escaped  
by  default.  (17950)
 
F.

xDB  Replication  Server
 
1.

Replication  i
s  available  on  a  wide  range  of  platforms  that  previously  were  not  supported  
(
e.g.  Mac  OS,  Solaris
)
.      
 
2.

Replication  from  Microsoft  SQL  Server  (v2005  and  v2008)  to  PostgreSQL  (Standard  Server)  
and  Postg
res  Plus  Advanced  Server  is  now  supported  (
17592/17629
).
 
3.

The  replication  source
-­‐
target  product  combinations  are  represented  in  the  table  below:
 
 
Source  
\
 Target
 
Oracle  
 
Microsoft  SQL  
Server
 
 
Postgres  Plus  
Standard  Server
 
Postgres  Plus  
Advanced  Server  
(Orac
le  
mode
)  
 
Postgres  Plus  
Advanced  Server  
(PostgreSQL  
mode
)  
 
Oracle
 
No
 
No
 
Yes
 
Yes
 
Yes
 
Microsoft  SQL  Server
 
No
 
No
 
Yes
 
Yes
 
Yes
 
Postgres  Plus  
Standard  Server
 
No
 
No
 
Yes
 
Yes
 
Yes
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
19
 
Source  
\
 Target
 
Oracle  
 
Microsoft  SQL  
Server
 
 
Postgres  Plus  
Standard  Server
 
Postgres  Plus  
Advanced  Server  
(Orac
le  
mode
)  
 
Postgres  Plus  
Advanced  Server  
(PostgreSQL  
mode
)  
 
Postgres  Plus  
Advanced  Server  
(Oracle  
mode
)
 
Yes
 
Yes
 
No
 
Yes
 
No
 
Postgres  Plus  
Adva
nced  Server  
(PostgreSQL  
mode
)
 
No
 
No
 
Yes
 
Yes
 
Yes
 
 
 
 
 
 
 
4.

The  Oracle  JDBC  driver  does  not  correctly  transforms  TIMESTAMP  WITH  TIME  ZONE  on  a  
system  with  DST  applicable  and  running  in  EST/EDT  time  zone.  xDB  has  been  enhanced  to  
bypass  Oracle's  JDBC  driver  for
 a  TIMESTAMP  WITH  TIME  ZONE  value  and  make  use  of  
Oracle  TO_TIMESTAMP_TZ  function.  (18345)
 
5.

Enhanced  xDB  to  notify  user  in  case  an  invalid  option  is  specified  in  xdb_pubserver.conf  or  
xdb_subserver.conf  files.  (16880)
 
6.

Fixed  an  issue  with  replication  of  doma
ins  when  publication  database  is  Postgres  Plus  
Advanced  Server  (PG  mode)  and  subscription  database  is  Postgres  Plus  Standard  Server.  
(18470)
 
7.

xDB  has  been  enhanced  to  be  aware  of  PostgreSQL  replication  roles  so  that  it  can  be  used  in  
a  cascading  replication
 model.  (17931)
 
XI.

Documentation  Updates
 
A.

Postgres  Plus  Advanced  Server  Installation  Guide
 
To  view  the  documentation,  please  visit:  
 
http://www.enterprisedb.com/produ
cts
-
services
-
training/products/documentation

B.

Updated  Documentation
 
For  the  latest  versions  of  the  following  Postgres  Plus  Advanced  Server  Guides,  please  visit:  
h
ttp://www.enterprisedb.com/products
-
services
-
training/products/documentation
 


Postgres  Plus  Advanced  Server  Guide
 


Postgres  Plus  Advanced  Server  Performance  and  Scalability  Guide
 


Oracle  Compatibility  Developers  Guide
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
20
 


Postgres  Plus  Advanced  Server  ODBC  Conne
ctor  Guide
 


Postgres  Plus  Advanced  Server  JDBC  Connector  Guide
 


Postgres  Plus  Advanced  Server  Migration  Guide
 


Postgres_Plus_v9_xDB_Replication_Server_Users_Guide
 
C.

PDF  Documentation
 
Please  note  that  PDF  versions  of  all  documentation  are  available  to  Postgres  P
lus  Advanced  Server  
subscription  holders  by  logging  into  the  EnterpriseDB  website  and  accessing  the  customer  portal  at:  
http://www.enterprisedb.com/support
 
XII.

Upgrade  Paths
 
A.

How  To  Upgrade  from  v8.4  to  v9
 
Yo
u  can  use  pg_upgrade  to  upgrade  from  PPAS  8.4  to  9.0.  pg_upgrade  allows  you  to  update  your  
installation  in  a  matter  of  minutes  for  most  users  without  the  downtime  and  additional  planning  that  
used  to  be  required  when  using  the  traditional  dump  and  restore  
method.    
 
Usage  details  for  pg_upgrade  can  be  found  in  the  Postgres  Plus  Advanced  Server  Installation  Guide  
available  on  the  EnterpriseDB  web  site  at:  
http://www.enterprisedb.com/documentati
on/english
 
Alternatively,  you  can  use  the  normal  dump  and  restore  routines  as  described  in  Section  B  below.
 
B.

How  To  Upgrde  Versions  previous  to  v8.4  to  v9    
 
Follow  the  dump  and  restore  routines  outlined  for  upgrades  between  major  releases  of  PostgreSQL  
(wh
ere  the  number  after  the  first  dot  changes)  
posted  in  the  documentation  pages  
at:  
 
http://www.enterprisedb.com/products
-
services
-
training/products/documentation

C.

Known  Incompatibilities  for  v9.x
 
Version  9.0  contains  a  number  of  changes  that  selectively  break  backwards  compatibility  in  order  to  
support  new  features  and  code  quality  improvements.  In  particular,  users  who  make  extensive  use  of  
PL/pgSQL,  Point
-­‐
In
-­‐
Time  
Recovery  (PITR),  or  Warm  Standby  should  test  their  applications  because  of  
slight  user
-­‐
visible  changes  in  those  areas.  The  know  incompatibilities  are:
 
1.

Server  Settings
 
Remove  server  parameter  add_missing_from,  which  was  defaulted  to  off  for  many  years.
 
Remo
ve  server  parameter  regex_flavor,  which  was  defaulted  to  advanced  for  many  years.
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
21
 
archive_mode  now  only  affects  archive_command;  a  new  setting,  wal_level,  affects  the  
contents  of  the  write
-­‐
ahead  log.
 
log_temp_files  now  uses  default  file  size  units  of  kilob
ytes.
 
2.

Queries
 
When  querying  a  parent  table,  do  not  do  any  separate  permission  checks  on  child  tables  
scanned  as  part  of  the  query.  The  SQL  standard  specifies  this  behavior,  and  it  is  also  much  
more  convenient  in  practice  than  the  former  behavior  of  checkin
g  permissions  on  each  child  
as  well  as  the  parent.
 
3.

Data  Types
 
bytea  output  now  appears  in  hex  format  by  default.  The  server  parameter  bytea_output  can  
be  used  to  select  the  traditional  output  format  if  needed  for  compatibility.
 
Array  input  now  considers  on
ly  plain  ASCII  whitespace  characters  to  be  potentially  
ignorable;  it  will  never  ignore  non
-­‐
ASCII  characters,  even  if  they  are  whitespace  according  to  
some  locales.  This  avoids  some  corner  cases  where  array  values  could  be  interpreted  
differently  depending  
on  the  server's  locale  settings.
 
Improve  standards  compliance  of  SIMILAR  TO  patterns  and  SQL
-­‐
style  substring()  patterns.    
This  includes  treating  ?  and  {...}  as  pattern  metacharacters,  while  they  were  simple  literal  
characters  before;  that  corresponds  to  ne
w  features  added  in  SQL:2008.  Also,  ^  and  $  are  
now  treated  as  simple  literal  characters;  formerly  they  were  treated  as  metacharacters,  as  if  
the  pattern  were  following  POSIX  rather  than  SQL  rules.  Also,  in  SQL
-­‐
standard  substring(),  
use  of  parentheses  for  
nesting  no  longer  interferes  with  capturing  of  a  substring.  Also,  
processing  of  bracket  expressions  (character  classes)  is  now  more  standards
-­‐
compliant.
 
Reject  negative  length  values  in  3
-­‐
parameter  substring()  for  bit  strings,  per  the  SQL  
standard.
 
Make  da
te_trunc  truncate  rather  than  round  when  reducing  precision  of  fractional  seconds.  
The  code  always  acted  this  way  for  integer
-­‐
based  dates/times.  Now  float
-­‐
based  dates/times  
behave  similarly.
 
4.

Object  Renaming
 
Tighten  enforcement  of  column  name  consistency  du
ring  RENAME  when  a  child  table  
inherits  the  same  column  from  multiple  unrelated  parents.
 
No  longer  automatically  rename  indexes  and  index  columns  when  the  underlying  table  
columns  are  renamed.  Administrators  can  still  rename  such  indexes  and  columns  manual
ly.  
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
22
 
This  change  will  require  an  update  of  the  JDBC  driver,  and  possibly  other  drivers,  so  that  
unique  indexes  are  correctly  recognized  after  a  rename.
 
CREATE  OR  REPLACE  FUNCTION  can  no  longer  change  the  declared  names  of  function  
parameters.  In  order  to  av
oid  creating  ambiguity  in  named
-­‐
parameter  calls,  it  is  no  longer  
allowed  to  change  the  aliases  for  input  parameters  in  the  declaration  of  an  existing  function  
(although  names  can  still  be  assigned  to  previously  unnamed  parameters).  You  now  have  to  
DROP  and
 recreate  the  function  to  do  that.
 
5.

PL/pgSQL
 
PL/pgSQL  now  throws  an  error  if  a  variable  name  conflicts  with  a  column  name  used  in  a  
query.    The  former  behavior  was  to  bind  ambiguous  names  to  PL/pgSQL  variables  in  
preference  to  query  columns,  which  often  res
ulted  in  surprising  misbehavior.  Throwing  an  
error  allows  easy  detection  of  ambiguous  situations.  Although  it's  recommended  that  
functions  encountering  this  type  of  error  be  modified  to  remove  the  conflict,  the  old  
behavior  can  be  restored  if  necessary  via
 the  configuration  parameter  
plpgsql.variable_conflict,  or  via  the  per
-­‐
function  option  #variable_conflict.
 
PL/pgSQL  no  longer  allows  variable  names  that  match  certain  SQL  reserved  words.  This  is  a  
consequence  of  aligning  the  PL/pgSQL  parser  to  match  the  co
re  SQL  parser  more  closely.  If  
necessary,  variable  names  can  be  double
-­‐
quoted  to  avoid  this  restriction.
 
PL/pgSQL  now  requires  columns  of  composite  results  to  match  the  expected  type  modifier  
as  well  as  base  type.    For  example,  if  a  column  of  the  result  ty
pe  is  declared  as  
NUMERIC(30,2),  it  is  no  longer  acceptable  to  return  a  NUMERIC  of  some  other  precision  in  
that  column.  Previous  versions  neglected  to  check  the  type  modifier  and  would  thus  allow  
result  rows  that  didn't  actually  conform  to  the  declared  res
trictions.
 
PL/pgSQL  now  treats  selection  into  composite  fields  more  consistently.    Formerly,  a  
statement  like  SELECT  ...  INTO  rec.fld  FROM  ...  was  treated  as  a  scalar  assignment  even  if  
the  record  field  fld  was  of  composite  type.  Now  it  is  treated  as  a  rec
ord  assignment,  the  
same  as  when  the  INTO  target  is  a  regular  variable  of  composite  type.  So  the  values  to  be  
assigned  to  the  field's  subfields  should  be  written  as  separate  columns  of  the  SELECT  list,  
not  as  a  ROW(...)  construct  as  in  previous  versions.  I
f  you  need  to  do  this  in  a  way  that  will  
work  in  both  9.0  and  previous  releases,  you  can  write  something  like  rec.fld  :=  ROW(...)  
FROM  ....
 
Remove  PL/pgSQL's  RENAME  declaration.    Instead  of  RENAME,  use  ALIAS,  which  can  now  
create  an  alias  for  any  variable,
 not  only  dollar  sign  parameter  names  (such  as  $1)  as  before.
 
6.

Reserved  Words
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
23
 
The  reserved  PostgreSQL  keywords  BETWEEN  and  CONCURRENTLY  are  either  new  or  have  
new  behavior.  If  either  is  being  used  as  an  unquoted  SQL  identifier  anywhere  it  may  now  
require  th
e  use  of  double  quotes  or  you  can  opt  to  use  a  different  name  for  the  SQL  object.
 
7.

Other  Incompatibilities
 
Deprecate  use  of  =>  as  an  operator  name.    Future  versions  of  PostgreSQL  will  probably  
reject  this  operator  name  entirely,  in  order  to  support  the  SQL
-­‐
standard  notation  for  named  
function  parameters.  For  the  moment,  it  is  still  allowed,  but  a  warning  is  emitted  when  such  
an  operator  is  defined.
 
Remove  support  for  platforms  that  don't  have  a  working  64
-­‐
bit  integer  data  type.    It  is  
believed  all  still
-­‐
supp
orted  platforms  have  working  64
-­‐
bit  integer  data  types.    
 
XIII.

Platform  Support
 
P
ostgres  Plus  Advanced  Server  v9.0
 is  supported  on  the  following  platforms.  T:
 
1.

CentOS  5.x    for  32/64  bit
 
2.

RHEL  6.x  and  5.x    for  32/64  bit
 
3.

openSuSE  11.x    for  32/64  bit
 
4.

SLES  11.x    for  
32/64  bit
 
5.

Ubuntu  10.4  LTS    for  32/64  bit
 
6.

Windows  Server  2008  R1  for  32/64  bit  
 
7.

Windows  Server  2008  R2  64  bit
 
8.

Sun  Solaris  
SPARC  64  v10
 
The  Solaris  specific  Release  Notes  can  be  found  in  the  Solaris  download  archive.    
 
9.

HP
-­‐
UX  v11
 
The  HP
-­‐
UX  specific  Release  No
tes  can  be  downloaded  alongside  the  binary  installer  for  HP
-­‐
UX  on  
the  EnterpriseDB  website.
 
The  following  platforms  are  commonly  used  in  our  labs  by  our  development  and  testing  groups.
   
These  platforms  are  
not  
supported  in
 a  production  environment.
 
10.

Fedora  
14.x,  13.x  and  12.x    for  32/64  bit
 
11.

Ubuntu  10.10,  9.10  for  32/64  bit
 
12.

Windows  7  32/64*
 
13.

Windows  Vista  32/64*
 
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
24
 
 
Postgres  Plus  Advanced  Server  supports  many  Linux  distributions  in  addition  to  those  listed.    To  
inquire  if  your  operating  system  is  supported  conta
ct  us  by:
 


Email:    
sales
-­‐
us@enterprisedb.com
   or
   
sales
-­‐
intl@enterprisedb.com
 
 


Phone:    
+1
-­‐
781
-­‐
357
-­‐
3390
     or    
1
-­‐
877
-­‐
377
-­‐
4352
 


Web:  
http://www.ent
erprisedb.com/general
-
inquiry
-
form
 
XIV.

System  Requirements
 
Minimum  hardware  requirements  for  running  Postgres  Plus  Advanced  Server  are:
 


600  MHz  processor
 


512  MB  of  RAM
 


512  MB  of  HDD  plus  space  for  your  data
 
XV.

K
nown  Issues
 
A.

Pg_upgrade
 
1.

On  Windows,  
if  
the  
-­‐
l  logfil
e  
option  
is  given  as  an  option
,  the  upgrade  will  fail  with  a  
message  that  includes  the  text:  
The  process  cannot  access  the  file  because  it  is  being  used  by  
another  process
.
   A  work  around  is  to  perform  pg_upgrade  without  using  the  logfile  option.    
Note  tha
t  
an  on
-­‐
screen  log  is  still  displayed  for  the  entire  upgrade  process  if  you  skip  the  log  
flag
.
 
(
18995)
 
B.

DB  Server:
 
1.

DBMS_PIPE  sends  messages  all  at  once  regardless  of  the  maxpipesize  setting
.  (
17443
)
 
C.

Migration  Toolkit:
 
1.

"Extra  data  after  last  expected  column"
 error  is  observed  in  some  MySQL  to  Advanced  
Server  migrations.  (17390)
 
D.

Postgre
s
 Studio:
 
1.

The  default  parameters  are  not  initialized  when  using  the  debugger.  (16434)
 
E.

Slony
 
Postgres  Plus  Advanced  Server  v9
 
(9.0.5.18)  
Rele
ase  Notes
 
 
 
Copyright  2011  EnterpriseDB  Corporation.  All  Rights  Reserved.
 
Page:  
25
 
The  following  Slony  v2.0.6  components  for  have  not  yet  been  published  for  Windows  ope
rating  
environments:  Slony  Tools  and  Logshipper.    The  core  replication  capabilities  are  functional.    (18568)
 
Slony  "Tools"  facilitates  using  Perl  based  scripts  for  the  configuration  of  a  Slony  cluster.  Their  absence  
does  not  prevent  the  use  of  Slony,  as  on
e  can  utilize  shell  scripts  to  invoke  Slonik  commands  to  
configure  a  Slony  cluster  .  However,  in  case  one  prefers  using  Perl  scripts  it'll  not  be  possible  on  
Windows.  As  a  work
-­‐
around  one  can  use  *nix  system  to  utilize  Perl  scripts  generation.
 
The  "Log  Shi
pper"  is  an  optional  component  provided  to  facilitate  the  application  of  Slony  offline  "log"  
archive  files  on  a  target  (offline/isolated)  Slony  subscriber  node.  However,  one  can  directly  (without  
using  Log  Shipper  utility)  apply  the  Slony  offline  "log"  fil
es  as  a  work
-­‐
around.    The  advantage  Log  
Shipper  provides  is  the  automation  of  the  process  such  that  one  can  skip  transactional  events  for  one  
or  more  tables  from  the  given  log  files  using  Log  Shipper  options.  Otherwise  one  has  to  write  a  custom  
script  to  p
erform  such  tasks.
 
Also  the  internal  functionality  of  slevent  is  absent  and  results  in  formatting  problems  for  message  
events  related  with  the  Slon  Windows  service  and  should  be  treated  as  a  known  bug.
 
XVI.

How  to  Report  Problems
 
To  report  any  issues  you  are  ha
ving  please  contact  EnterpriseDB’s  technical  support  staff:
 


Email:    
support@enterprisedb.com
 
 


Phone:    +1
-­‐
732
-­‐
331
-­‐
1320    or    1
-­‐
800
-­‐
235
-­‐
5891  (US  Only)