PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

08-Dec-2003 Issue: 53

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

Publication Delay
Issues 53 and 54 12-Dec-2003

Due to a technical glitch (doh!) Issue 53 was not published until later in the week instead of its usual Monday date.

Due to this late publication and that I will be out of town early next week, Issue #54 is postponed by a week. I am sorry for the inconvenience.

Editor: elein at
Why use Cursors?
[GENERAL] are cursors necessary? 5-Dec-2003

In the program testlibpq.c, these five SQL statements are executed to illustrate the basics of how cursors work.

   res = PQexec(conn, "BEGIN");
   res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
   res = PQexec(conn, "FETCH ALL in myportal");
   res = PQexec(conn, "CLOSE myportal");
   res = PQexec(conn, "END");
This is the equivalent of
	res = PQexec(conn, "select * from pg_database");
So why would we want to execute five statements instead of one?

If the data returned by the query is relatively small or if you want all of the data at once, then, the single query is the best option. Your entire result set will be selected and sent to the client at once.

If you want to scroll through the data set without downloading the whole set, or if you want to download the data piecemeal, then a cursor is what you want to use.

Cursors can save client and network bandwidth. If you want to show a page full of data at a time, and enable your application to scroll up or down in the data, then the cursor is the right thing to use. However, to get the whole data set fastest, use the plain select.

While this example is with libpq, the same applies for all client interfaces.

Jan Weick: You really think people would ever want to store more than 640 rows?
Mike Mascari: Ha ha! With each one being 1K?

Contributors: Mark Harrison mh at Richard Huxton dev at Jan Wieck JanWieck at Mike Mascari mascarm at Alex Satrapa alex at
Brief review of 7.4 gotchas
Changes in 7.4 06-Dec-2003

In previous issues, various 7.4 feature changes were discussed. In Issue #48 the changes to postgresql.conf were discussed for example. This list is from 7.4 Documentation Appendix E.1.2. Migration to version 7.4 and highlights changes which might be puzzling or problematic. The Release Notes are required reading.

  • The server-side autocommit setting was removed and reimplemented in client applications and languages. autocommit is no longer available in postgresql.conf.
    To turn autocommit off, use \SET AUTOCOMMIT OFF in psql.
  • Error message wording has changed substantially in this release, and error codes have been added.
    If any of your applications parse the error messages, then it is imperative that you consult the new error code documentation in the Appendix of the PostgreSQL 7.4 documentation. The error codes now match the SQL standards for error codes. This means that the error code and not the error message text should be checked. The error codes are unlikely to change whereas the error message text may change and may also be different with different languages.
  • ANSI joins may behave differently because they are now better optimized
    You may see different EXPLAIN results with ANSI JOINs. If your vacuuming and statistics are up to date, these queries should be better optimized. If you see a behavior change which results in a much slower query then you must re-examine the "usual suspects." Check the data types in the qualifications and the indexes to be sure that they match, etc.
  • MOVE/FETCH 0 now does nothing; return value of MOVE/FETCH 1/0 varies based on the current offset in the cursor
    Remember that the difference between MOVE and FETCH only is that FETCH returns data and MOVE does not. Both move the CURSOR to an enumerated row in a result set. Previously FETCH 0 was PostgreSQL specific was equivalent to FETCH ALL. You could not use FETCH 0 or FETCH RELATIVE 0 to retrieve the current row. The new behavior is that these commands will now fetch the row the cursor is currently pointing to.
  • COPY now can process carriage-return/line-feed and carriage-return terminated files. Literal carriage-returns and line-feeds are no longer accepted as data values; use \r and \n instead.
    If you are counting on using old files created by COPY and have data which contains literal carriage-returns and line-fees it is time to break out the perl scripts to convert those characters to \r and \n.
  • Trailing spaces are now trimmed when converting from CHAR(n) to VARCHAR(n) / TEXT
    If you use CHAR(n) and are counting on the trailing spaces be sure to not use the explicit or implicit casts to TEXT or VARCHAR(n). TEXT and VARCHAR(n) data types do not keep the trailing blanks.
  • FLOAT(p) now measures 'p' in bits, not digits
    This change now brings PostgreSQL back in line with the SQL standards. Check your float fields if they are defined with precision to ensure you have the right storage declared.
  • 'now' and 'today' will no longer work as a column default; now() should be used instead
    Review any timestamp fields with now or today as a default. Change them to use the correct function call now() or special variables, current_timestamp, current_date, etc.

    Contributors: Tom Lane tgl at, Bruce Momjian pgman at, elein at
    Concurrent Updates
    [GENERAL] postgresql locks the whole table! 03-Dec-2003

    Given a table like the one below, two parallel inserts on separate connections using BLOCK transactions are performed. The second insert will be locked out until the first transaction is completed.

    CREATE TABLE take2
      id serial not null,
      timestamp timestamp NOT NULL DEFAULT now(),
      description text,
      FOREIGN KEY (asset_id) REFERENCES public.asset (id),  
      PRIMARY KEY(id)
    Connection 1 Connection 2
    insert into table take2
      values(default, default, 't1');
    insert into table take2
      values(default, default, 't2');
    ...other commands...   Locked out
    ...COMMIT;   Unlocks
    idle   ...other commands...
    idle   ...COMMIT;

    When PostgreSQL references public.asset, it creates an exclusive lock on the table--the equivalent of SELECT FOR UPDATE. This exclusive lock can be seen by selecting from pg_locks.

     relation | database | transaction |  pid  | mode             | granted
        39356 |    34862 |        NULL | 18671 | AccessShareLock  | t
        39356 |    34862 |        NULL | 18671 | RowExclusiveLock | t
         NULL |     NULL |        9914 | 18671 | ExclusiveLock    | t
        39354 |    34862 |        NULL | 18671 | AccessShareLock  | t
        34886 |    34862 |        NULL | 18671 | AccessShareLock  | t
        34886 |    34862 |        NULL | 18671 | RowShareLock     | t
        16759 |    34862 |        NULL | 18671 | AccessShareLock  | t
    (7 rows)
    PostgreSQL does not implement shared read locks on the row level. (Ed: yet?)

    To avoid this type of conflict, be absolutely sure that your transactions are a small as possible. Never have user interaction when a transaction is begun. (Humans are so unreliable :-) This advice holds for all kinds of transactions.

    In this particular case, where the transaction is already as small as possible, you can defer the foreign key check until commit time. There are two ways to do this, depending on the actions within your transaction. You can define the FOREIGN KEY to be deferrable or you can SET CONSTRAINTS DEFERRED for the transaction.

       CREATE TABLE take2
         id serial not null,
         timestamp timestamp NOT NULL DEFAULT now(),
         description text,
         FOREIGN KEY (asset_id) REFERENCES public.asset (id) DEFERRED,
         PRIMARY KEY(id)
    Setting the transaction constraints:
       insert into table take2
    values(default, default, 't1'); ... COMMIT;

    Contributors: Dr NoName spamacct11 at, Mike Mascari mascarm at, Alistair Hopkins alistair at, Jan Wieck JanWieck at, Scott Ribe scott_ribe at, Greg Stark gsstark at, Bruce Momjian pgman at
    Reverse Function
    [SQL] Equivalent of Reverse() functions 27-Nov-2003

    Here are two nice implementations of a function which reverses the character order of a text field. The first, by Joe Conway, wins in simplicity, but requires plperl. The second, by Greg Sabino Mullane, is a nice example of using loops in plpgsql.

    	   RETURN reverse($_[0]);
    	' LANGUAGE 'plperl';
    	      original ALIAS FOR $1;
    	      reversed TEXT := \'\';
    	      onechar  VARCHAR;
    	      mypos    INTEGER;
    	      SELECT LENGTH(original) INTO mypos;
    	         EXIT WHEN mypos < 1;
    	         SELECT substring(original FROM mypos FOR 1) INTO onechar;
    	         reversed := reversed || onechar;
    	         mypos := mypos -1;
    	      END LOOP;
    	      RETURN reversed;

    Contributors: Kumar sgnerd at, Joe Conway mail at, greg at
    Number of Rows Affected
    [GENERAL] plpgsql question 25-Nov-2003

    To determine the number of rows affected by a statement in a plpgsql function, you can use GET DIAGNOSTICS. This is very handy if you want to check the number of rows from a statement which affects many rows, either directly or indirectly via a trigger or a rule.

         RowsAffected INTEGER;
         -- DO your statement
         GET DIAGNOSTICS RowsAffected = ROW_COUNT;

    Contributors: Brian Hirt bhirt, Michael A Nachbaur

  • Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
    Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

    Search General Bits & Search WWW