What’s New

The following features have been changed in EDB Postgres Advanced Server 12 to create Advanced Server 13:

  • Advanced Server now supports the PARALLEL | NOPARALLEL clause in the CREATE TABLE, ALTER TABLE, CREATE INDEX, and ALTER INDEX commands to enable or disable parallelism on an index or a table. For information, see the Database Compatibility for Oracle Developer’s SQL Guide.

  • Advanced Server now supports USING INDEX ... create_index_statement clause in the CREATE TABLE, and ALTER TABLE commands to explicitly create an index on a table. For information, see the Database Compatibility for Oracle Developer’s SQL Guide.

  • Advanced Server introduces AUTOMATIC LIST PARTITIONING, which allows a database to automatically create a new partition if the partitioning key value does not correspond to an existing partition. For information, see the Database Compatibility Table Partitioning Guide.

  • Advanced Server has added the STATS_MODE function that takes an argument a set of values to return the most frequent input value. For information, see the Database Compatibility for Oracle Developer’s Reference Guide.

  • Advanced Server has added the SYSDATE fuction that returns the timestamp without time zone. For information, see the Database Compatibility for Oracle Developer’s Reference Guide.

  • Advanced Server has added smallint, init, bigint and numeric variants to the MEDIAN function. For information, see the Database Compatibility for Oracle Developer’s Reference Guide.

  • Advanced Server has added support for the FM format in the TO_NUMBER function. For information, see the Database Compatibility for Oracle Developer’s Reference Guide.

  • Advanced Server now supports statement logging and records the number of rows processed during bulk execution. For information, see edb_log_every_bulk_value under Reporting and Logging / What to Log.

  • Advanced Server has added a new GUC to support Oracle-style display output by setting dbms_output.serveroutput to FALSE in the DBMS_OUTPUT package. For information, see the Database Compatibility for Oracle Developer’s Built-in Package Guide.

  • EDB*Loader now supports HANDLE_CONFLICTS parameter to move duplicate records to the BAD file. For information, see the Database Compatibility for Oracle Developer’s Tools and Utilities Guide.

  • Advanced Server has added ALTER DIRECTORY ...OWNER TO command to change the owner of a directory. For information, see the Database Compatibility for Oracle Developer’s SQL Guide.

  • Advanced Server has added ALTER TRIGGER ...ON AUTHORIZATION command to change an owner of the trigger’s implicit objects. For information, see the Database Compatibility for Oracle Developer’s SQL Guide.

  • EDB*Loader now supports -c CONNECTION STRING or connstr=CONNECTION_STRING parameter, which allows you to specify the SSL parameters and the connection parameters supported by libpq. For information, see the Database Compatibility for Oracle Developer’s Tools and Utilities Guide.

  • Advanced Server has added the TO_TIMESTAMP_TZ function that converts a timestamp formatted string to a TIMESTAMPTZ datatype. For information, see the Database Compatibility for Oracle Developer’s Reference Guide.

  • Advanced Server has added additional fields to make logs consistent across both CSV and XML audit logs. For information, see EDB Audit Logging.

  • Advanced Server has added a new GUC edb_dblink_oci.rescan to control the scan type for the remote statement. For information, see the Database Compatibility for Oracle Developer’s SQL Guide.

  • Advanced Server has added support for utl_http.end_of_body exception in the UTL_HTTP package to handle error from read_line, read_text, and read_raw package procedures when no data is present in the response body. For information, see the Database Compatibility for Oracle Developer’s Built-in Package Guide.

  • Advanced Server now supports the forward declaration of function or procedure inside a package body. For information, see the Database Compatibility for Oracle Developer’s SQL Guide.

  • Advanced Server introduces PARTITIONS, SUBPARTITIONS number to create automatic hash partitions at subpartition level and STORE IN clause to specify the tablespaces to store the autogenerated partitions or subpartitions. For information, see the Database Compatibility Table Partitioning Guide.

  • Advanced Server has added support for AES192 and AES256 in the DBMS_CRYPTO package for encrypting and decrypting data types. For information, see the Database Compatibility for Oracle Developer’s Built-in Package Guide.

  • Advanced Server now supports pg_hba.conf entry upon successful authentication with the server. For information, see Modifying the pg_hba.conf File.

  • Advanced Server has implemented WHEN condition inside COMPOUND TRIGGER to execute a trigger when specified conditions occur. For information, see the Database Compatibility for Oracle Developer’s SQL Guide or Database Compatibility Stored Procedural Language Guide.

  • Advanced Server has added support for column_value_long, define_column_long, and last_error_position in the DBMS_SQL package to define and return a part of the LONG column. For information, see the Database Compatibility for Oracle Developer’s Built-in Package Guide.

Limitations

The following table describes various hard limits of PostgreSQL. However, practical limits such as performance limitations or available disk space may apply before absolute hard limits are reached.

Item

Upper Limit

Comment

database size

unlimited

number of databases

4,294,950,911

relations per database

1,431,650,303

relation size

32 TB

with the default BLCKSZ of 8192 bytes

rows per table

limited by the number of tuples that can fit onto 4,294,967,295 pages

columns per table

1600

further limited by tuple size fitting on a single page; see note below

field size

1 GB

identifier length

63 bytes

can be increased by recompiling PostgreSQL

indexes per table

unlimited

constrained by maximum relations per database

columns per index

32

can be increased by recompiling PostgreSQL

partition keys

32

can be increased by recompiling PostgreSQL

Note

The maximum number of columns for a table is further reduced as the tuple being stored must fit in a single 8192-byte heap page. For example, excluding the tuple header, a tuple made up of 1600 int columns would consume 6400 bytes and could be stored in a heap page, but a tuple of 1600 bigint columns would consume 12800 bytes and would therefore not fit inside a heap page. Variable-length fields of types such as text, varchar, and char can have their values stored out of line in the table’s TOAST table when the values are large enough to require it. Only an 18-byte pointer must remain inside the tuple in the table’s heap. For shorter length variable-length fields, either a 4-byte or 1-byte field header is used and the value is stored inside the heap tuple.

Note

Columns that have been dropped from the table also contribute to the maximum column limit. Moreover, although the dropped column values for newly created tuples are internally marked as null in the tuple’s null bitmap, the null bitmap also occupies space.