Change Notes for PostgreSQL Extension

1.0a11 (2002-06-12)

Fixed an infinite loop bug in the connection pooling logic. Thanks to Eric Soroos for the bug report.

1.0a10 (2002-03-19)

Moved all glue scripts and sub-tables containing glue scripts for the old libpq interface to the postgreSQL.PQ sub-table. If your code still uses the old interface, you have to replace all references to the postgreSQL table with references to the postgreSQL.PQ table.

[first public release since 1.0a5]

1.0a9 (2002-03-15)

When converting date/time types from PostgreSQL to UserTalk and vice versa, time zone offsets are now observed. UserTalk dateType values are always assumed to be in the current time zone. Due to a change in PostgreSQL 7.2, I had to drop support for converting UserTalk date types to PostgreSQL TIME (WITHOUT TIME ZONE) values. You will now have to use postgreSQL.util.dateTypeToPGTIME if you need an SQL string representation that PostgreSQL will recognize as a TIME (WITHOUT TIME ZONE). Other PostgreSQL date, time, and timestamp types, including TIME WITH TIME ZONE are still supported.

When returning PostgreSQL CHAR(n) and arrays of CHAR(n), trailing spaces are now stripped off automatically.

Improved error messages for errors occurring while converting result values from PostgreSQL to UserTalk data types. A copy of the string representation returned by PostgreSQL is now included in the error message to simplify debugging.

Fixed bug related to array of boxes: We used an incorrect character for delimitting array items. (see notes for 1.0a8)

Fixed bug releated to arrays of several string types: Backslashes are now escaped properly as long as you use PotgreSQL 7.2. (see notes for 1.0a8)

[released to selected testers only]

1.0a8 (2002-03-09)

The extension now supports PostgreSQL geometric data types. They are converted to/from UserTalk records with a specific structure.

Added support for more array types to the PostgreSQL to UserTalk data type conversion code. There are currently two known problematic cases that appear to be bugs in the PostgreSQL server code: The conversion of CHAR, VARCHAR, or TEXT arrays will fail if one of the array items ends with a backslash. When a query asks for a array of boxes, the respective column in the query result set always contains just a one-item array, no matter how many items where contained in the array assigned to that field.

When an error occurs while converting a result field from a PostgreSQL data type to a UserTalk data type, the error message now offers more details about what went wrong.

postgreSQL.cursor.executeQuery, postgreSQL.cursor.executeManyQueries, and postgreSQL.cursor.callProcedure now accept lists as a parameter. They will be converted to PostgreSQL arrays.

It should now be safe to pass binary values as parameters to postgreSQL.cursor.executeQuery, postgreSQL.cursor.executeManyQueries, and postgreSQL.cursor.callProcedure. They will be coerced to a string and problematic characters (backslashes, single quotes, and ascii nils) will be properly escaped.

Upgraded the client library code used in the extension from PostgreSQL 7.1.2 to the most recent version 7.2. Changes are mostly limited to error messages returned by the client library.

Fixed a bug in postgreSQL.cursor.executeQuery and postgreSQL.cursor.executeManyQueries: The extension stores the result of parsing the SQL string for parameter references. That way, if you submit the same query several times in a row, it doesn't have to do the parsing again. The code that determined whether we could use the cached results from the previous query produced false positive cache hits if the previous SQL string had the same length as the current SQL string.

Fixed a bug in postgreSQL.cursor.executeQuery or postgreSQL.cursor.executeManyQueries: The extension would sometimes crash while accessing items from the parameter record by name.

[released to selected testers only]

1.0a7 (2002-03-07)

postgreSQL.cursor.executeQuery now supports passing the address of a table for the params parameter. The SQL command can reference params by name and by position. Since the DLL interface is somewhat limited as far as resolving addresses and obtaining table values are concerned, I have had to resort to a bit of a hack to look up params by name in the table: The DLL constructs a string beginning with the string representation of the table address, followed by a dot, and the param name specified inside the curly braces in the SQL command. This string is evaluated via the DoScript callback of the DLL interface. In theory, this allows you to execute arbitrary UserTalk code as part of the param name. Suppose the address of the table is @paramTable.postargs and the SQL command contains the param reference {$username}, then the DLL would basically evaluate the string "paramTable.postargs.username" as UserTalk code. On the one hand, this approach allows you to also reference items in sub-tables of the param table by using a param name like {$data.filename} in the SQL command. On the other hand, a param name like {$foobar; delete(@someTable)} would actually delete someTable in Frontier.root and just substitute true for the param value.

Added an optional boolean parameter named flCoerceToUserTalkTypes to the postgreSQL.cursor.fetchOneRow, postgreSQL.cursor.fetchMultipleRows, and postgreSQL.cursor.fetchAllRows verbs. If set to false, result values will not be converted to UserTalk data types. This can be useful if you need to work around some limitations in UserTalk's type system, e.g. when dealing with dates outside of the range supported by UserTalk.

The postgresSQL.cursor.fetchOneRow, postgresSQL.cursor.fetchMultipleRows, and postgresSQL.cursor.fetchAllRows verbs now convert some PostgreSQL array types to UserTalk lists. Supported types so far are boolean, int2, int4, float4, float8, date, time, and timestamp. Support for more types is on the way. See postgreSQL.examples.arrays for a demo.

Fixed a bug where double precision floating point numbers would effectively be reduced to single precision during type conversion. Note that Frontier's string representation of a double precision floating point number breaks off after eight decimals. The precision used internally still is the usual 15 digits, though.

Changed the postgreSQL.examples.highlevel script to no longer use connection pooling. Started a separate script at postgreSQL.examples.pooling to demonstrate connection pooling.

[released to selected testers only]

1.0a6 (2002-03-05)

Implemented connection pooling and an easier-to-use high-level interface for executing queries and obtaining query results. See postreSQL.examples.highlevel for a demo.

In one of the next releases, I plan to move all verbs making up the basic PostgreSQL libpq interface out of the way into their own sub-table. To that end, I have already copied all these verbs into the new postgreSQL.PQ sub-table. In a future release, I will delete all these verbs from the top-level postgreSQL table since they no longer are the prefered way to interact with the database.

[released to selected testers only]

1.0a5 (2001-10-26)

Support for the large objects client-side API. On Carbon, file paths currently have to follwo Unix conventions, but I'm thinking about changing this in a future release. I haven't tested the API on Classic or Windows yet. See postgreSQL.examples.largeObjects for an example.

1.0a4 (2001-10-25)

Mac OS X: Support for authentication with encrypted passwords. (see "crypt" setting in /user/local/pgsql/data/pg_hba.conf)

Mac OS Classic: No longer hangs indefinitely if it can't connect to the postmaster process.

Mac OS Classic: Fixed bug introduced in 1.0a3 where a premature termination of the connection was reported whenever it received a login error from the postmaster.

[released to selected testers only]

1.0a3 (2001-10-23)

Support for Mac OS X based on the native BSD socket framework. It's possible to use Unix Domain Sockets to connect to a postmaster process running on the local machine.

[released to selected testers only]

1.0a2 (???)

[unreleased]

1.0a1 (2000-10-17)

First public release for Windows and classic Mac OS.