Listing Databases in Postgres

January 22nd, 2013

When working with an SQL database, especially in a development environment, it is easy to lose track of which databases are in which instance. MySQL has a really convenient way of listing its databases with the command

SHOW DATABASES;

in a client session or even programmatically. But then, structurally MySQL is a good deal simpler - or at least it was - than Postgres.

 

ZPsycopgDA and no commits

January 17th, 2013

I discovered the solution to this problem a couple of months ago. Goodness knows how I happened upon it but today the same thing occurred. Of course, I had forgotten about the solution, fretting for several hours wondering what was wrong with my code. Best if I write it down this time, eh.

The Problem

Are your updates and inserts in Postgres not being committed?

Your are using a modern Postgres database (8.4 in my case) using ZSQL Methods from Zope or Plone to target the database through ZPsycopgDA and psycopg2. Everything seems to be fine; all your complex reads and queries work great. But when you try to update the database using either update or insert commands, nothing seems to happen. Perhaps you have even been able to verify that the update or insert commands are actually getting through OK.

It just seems as if nothing is getting committed. What is going on?

The Solution

And yes indeed, this is the problem - your transactions are not being committed.

The reason for this is to do with isolation levels provided by psycopg2. In the adaptor (ZPsycopgDA) not all levels were supported and those that were defined were mapped incorrectly. A double whammy.

The problem should have been resolved in psycopg by now (according to this thread from August 2011) but if you still have a broken version of psycopg 2.4.2, you can make the correction by editing the dtml files add.dtml and edit.dtml in the database adaptor. The selector for the Transaction Isolation Level needs modified.

In old, broken versions, it looked like this:

<select name="tilevel:int">
 <option value="1">Read committed</option>
 <option value="2" selected="YES">Serializable</option>
</select>

Instead, the selector should read something like this

<select name="tilevel:int">
 <option value="0" <dtml-if expr="tilevel==0">selected="YES"</dtml-if>>Autocommit</option>
 <option value="1" <dtml-if expr="tilevel==1">selected="YES"</dtml-if>>Read Uncommitted</option>
 <option value="2" <dtml-if expr="tilevel==2">selected="YES"</dtml-if>>Read Committed</option>
 <option value="3" <dtml-if expr="tilevel==3">selected="YES"</dtml-if>>Repeatable Read</option>
 <option value="4" <dtml-if expr="tilevel==4">selected="YES"</dtml-if>>Serializable</option>
</select>

Once you have restarted your Zope instance you can then access the ZMI and edit the database connector. Choosing “Read Committed” now will set the correct value and your transactions will be committed.

Update

Good news! As of psycopg2 v2.4.6, the distributed ZPsycopgDA has been amended and will now work out-of-the-box and this fix is no longer required. And there is more: the Zope product, ZPsycopgDA, is now available in its own package from github and as an egg on pypi; the availability on pypi should mean it can be included as a requirement in a zope buildout.conf.

 

plpgsql: INSERT data using RECORD

August 6th, 2012

When inserting records into an SQL database using a existing table as a source usually means we need to know and specify the column structure of the target table. If we are trying to do this using a stored procedure then our script must be changed every-time the table's schema changes.

Here is a way to simplify this job of duplicating records in a Postgres database.

The Use Case

Suppose you have a database with 2 tables.
  • A library table containing details of different book libraries
  • A book table containing references to actual books
So we know all the books in a library and which library has each book.

Now suppose we decide to add a new library and populate it with the same books in an existing library. We could iterate through a select on the books

SELECT * FROM books WHERE id=oldid;

where oldid is the id of the existing library.

And for each record, we want to add a new record using newid as the id of the new library. This is quite awkward; all we want to do is replace the old id with the new but we have to unpack all the columns of the books table and repacked them in a new insert; something like

field1 = book.field1
field2 = book.field2
field3 = book.field3
  ...
INSERT INTO books VALUES (newid, field1, field2, field3,.....);

This is quite tedious and if we want to achieve this programmatically means we have to specify the table structure of the books table is such a way that if the schema changes, we have to remember to go back and change this script as well.

But there is a better way:

The Solution

Instead of stating the fields explicitly, we can use the

INSERT INTO table SELECT *

form provided we can figure a way of replacing the library id. The following plpgsql script illustrates how to do this:

CREATE FUNCTION duplicatebooks (oldid INTEGER, newid INTEGER)
RETURNS void
AS $$
DECLARE
   book books;
BEGIN
   FOR book IN SELECT * FROM books WHERE id=oldid LOOP
     book.id = newid;
     INSERT INTO BOOKS SELECT book.*;
   END LOOP;
 END;
$$ LANGUAGE plpgsql;

In this script:

  1. we pass the ids of the libraries, oldid and newid, as parameters to the function
  2. declare a book RECORD variable to contain data on each book
  3. iterate over the books we want to duplicate
  4. change the id of the book record to match the new library
  5. and finally add the new book record
In this way, we do not even need to know the other fields in the book table

Problems

In principle, we could have used a generic RECORD variable when iterating over the books table but unfortunately trying to pass a generic RECORD to the INSERT statement is explicitly disallowed and results in the error:

ERROR: record type has not been registered

The solution is to just declare a type for the record at the outset. So instead of having this fragment (which fails):

DECLARE
  book RECORD;
BEGIN
  FOR book IN SELECT * FROM books WHERE id=oldid LOOP
    book.id = newid;
    INSERT INTO books SELECT book.*;

we do this instead

DECLARE
  book books;
BEGIN
  FOR book IN SELECT * FROM books WHERE id=oldid LOOP
    book.id = newid;
    INSERT INTO books SELECT book.*;
 
 
© 2013 Andy Ferguson