5. Special Notes for PostgreSQL

Creating the plpgsql language

The GRIA OGSA-DAI service makes use of functions written in the plpgsql language. Before using the GRIA OGSA-DAI service, you need to enable this language in the template database.

1. Login to the database server using the "template1" database.
   eg. psql -U postgres -W template1
2. Execute the following command:
   CREATE LANGUAGE plpgsql;

Creating tables

Due to a limitation of PostgreSQL, it is not possible to automatically set the correct permissions on new tables on databases created by the OGSA-DAI service. After executing a CREATE TABLE command, you are required to execute the following function:

SELECT grant_access_to_table(table_name);

For example, to create a table named testtable, you should perform the following SQL queries:

CREATE TABLE testtable (testcolumn TEXT);
SELECT grant_access_to_table('testtable');

Note that this is only necessary on databases created by users of the OGSA-DAI service. It is not needed for existing databases that have been connected to the service.

Destroying resources

When destroying a data resource that was created by the OGSA-DAI service, it is necessary to drop first all the tables that were created in that database using the automatically created role resources. This can be done using a subscription to execute DROP TABLE SQL commands or DROP OWNED (for PostgreSQL >= 8.2).

Next: Developers' Guide Previous: Destroying Resources