Project: PGX was born from my frustration with the numerous PostgreSQL administration tools. Each one offers the same tree view for the schema which quickly outgrows its scrolling area. Most database developers become very familiar with their schemas, so displaying this information is secondary to putting together queries for various reports and applications. Even worse, some administration tools don't allow saving or storage of SQL statements or query results.
Project: PGX makes SQL statements the primary object of the application. Instead of schema navigation, the primary use of Project: PGX is to organize and execute SQL statements into projects
. In addition, query results can be saved along with the project or exported to other formats.
When a new document is created, the connection sheet is presented which prompts the user to add postgresql connections. A default connection is created with localhost connectivity. Connections can be added or deleted using the +
and -
buttons. Project: PGX supports any number of connections per project. To scan a server for database, click "Scan Server" which will populate the popup menu with database names. It is often useful to have multiple connections where there are separate development, testing, and production databases.
Once the connections have been set, Project: PGX will automatically connect to them and present their statuses (connected or disconnected) in the connection drawer. The connections will remain open for as long as the project is open.
The left-most outline view presents the statement and statement groups. Initially, one static statement called Scratch
is created. This statement cannot be deleted- its purpose is to allow quick one-off execution of statements which would otherwise not be part of a project. As an example, one could run a quick vacuum. The Scratch
statement is also pasted into by the schema inspector when one wishes to see the SQL for that database object. It is important not to store any important project statements in the volatile Scratch
space.
Statement groups are useful for associating various queries together, such as for an application module. Groups can be nested arbitrarily. Groups also provide batch statement processing. To create a new group, click the +Group
button.
The SQL View displays the last-selected statement. It features simple syntax-highlighting.
To encourage statement reuse, Project: PGX allows the storage and organization of arbitrary statement parameters. Each parameter set can be named by choosing "Edit Parameter Set List..." in the pull-down menu. Once a parameter set is named, parameters can be added or deleted using the +
and -
buttons respectively. Each parameter can be given an arbitrary name (which is never used by the database) and a value to bind to. The statement must include the same number of parameters that are in the parameter list. Specifying parameter lists is also useful when the statements should be exportable to application code.
Example: Retrieve all customers whose names start with J
and became customers after June 30,2006
.
SELECT * FROM customers WHERE name LIKE $1 AND added>$2;
Then the parameters to bind would be $1:'J%' and $2:'June 30,2006'. Changing a parameter would then change the results. This is especially useful in reports where modifying the statement directly could be error-prone (e.g. if a date field appears in multiple places, only a single external parameter would need to be modified).
Transaction management is handled with these buttons as expected. Note that all operations affect currently-selected connections in the connection status drawer. This allows for connection statement and transaction management multiplexing.
Tip: Keeping a transaction open for a prolonged period of time prevents maximum effectiveness of database vacuuming (cleanup).
Two-phase commit is a database-side feature which stores a transaction for later commit or rollback. Using this feature across multiple connections makes cross-connection transactions more reliable.
For example, if you wish to update the same datasets in the production and development databases, before actually committing the transactions, a two-phase commit can take place which stores the entire transaction on disk on both servers and ensures that they will (likely) be committed properly on demand. This nearly eliminates the case where an update on one database succeeds while another fails and then trying to clean up the discrepancy.
Project: PGX supports one-click two-phase commit (hopefully not patented). Once all selected connections have transactions open (and those connections are connected to postgresql server 8.1 or later, when 2PC was introduced), the Use 2PC
checkbox will be enabled. Now execute the cross-connection statements normally. If one checks the Use 2PC
box, then clicking commit will engage a two-phase commit to ensure that either all transactions commit successfully or all are rolled back. Only if the two-phase commit fails, will the user be notified.