Postgresql backend

Postgresql Backend

The primary motivation for doing this is for the inet and cidr column types, which provide support for directly storing and manipulating both IPv4 and IPv6 addresses, including netmasks.

Required Code Changes

Real Transactions	Rather than the semi-manual transactions hacked in for ancient versions of MySQL, the Postgresql upgrade will including using SQL level transactions. This will be done by enhancing and using the xaction_begin, xaction_rollback, and xaction_commit primitives. These will supplement, but not replace, table locking.
Table Locking	Table locking should be done using the lock_tables primitive, rather than calling the SQL statement directly. This function will ensure that tables are always locked in the same order, helping prevent deadlocks. Similarly, table unlocking should be done using the xaction_rollback and xaction_commit primitives, as attempting to unlock already unlocked tables (as would happen if the raw UNLOCK TABLES were called twice in a row) will generate a database error.
Implied vs Explicit Cross Join	The implied join in MySQL, written as FROM (credentials AS C, protections as P) must now be written out as an explicit cross join in Postgresql: FROM credentials AS C CROSS JOIN protections as P.
Quoting	MySQL is fairly loose, and allows single or double quotes to quoting strings in SQL queries. Postgresql is much stricter - double quotes must only be used for quoting columns, and single quotes must only be used for quoting strings.
Insert ID	The MySQL specific mysql_insertid attribute should no longer be used. Instead, use the last_insert_id method of the database handle: my $rowid = $dbh->last_insert_id(undef, undef, "table_name", undef);
Integer Not Automatically Cast to Boolean	In MySQL, WHERE 1 can be used as a synonym for no WHERE clause, making it a little easier to generate SQL strings. In Postgresql, however, integer values are not automatically cast into boolean values, so you have to explicitly use a boolean TRUE instead: WHERE TRUE.
Validation Functions	Validations routines will have to be added or updated to handle strictly v4 addresses, either v4 or v6 addresses, or strictly v6 addresses, depending on context.
Use inet/cidr Column Types	Although these are not strictly required, the primary goal of converting to Posgresql is to enable IPv6 support. As a very simple first step to this, columns should be converted from storing IP addresses and masks in pairs of 32 int columns to using inet or cidr types as appropriate. Likewise, the various direct bit masking logic should be replaced by the native IP address handling functions, which are both IPv4 and IPv6 capable.
Sub-second Timestamp Resolution	The Netdb layer uses a timestamp column as a version value for each row for use in op locking. While MySQL uses a date and time string with second resolution, Postgresql uses one with sub-second resolution. Validation has to be updated to allow the fraction of a second portion.
Inequality Operator	MySQL allows the non-standard FOO != BAR inequality operator. Postgresql requires the SQL standard FOO <> BAR syntax instead.
Non-zero Date Columns	Postgresql does not allow zero values in date and timestamp columns. Instead, the column must be NULL-able, with the corresponding check changes as well (ie, expires IS NOT NULL vs expires > 0)
String Concatenation	MySQL uses the non-standard concat operator for concatenating strings: concat('net', 'reg'). Postgresql instead must use the || operator: 'net' || 'reg'.