If you are writing generic functions using psuedo types as parameters or return values it is handy sometimes to know exactly what type was passed in or returned. The following function, written by David Fetter, takes a Datum, also known as a data value, and returns the object id (OID) of the type of data it is. The partner function written in SQL returns the type name given the OID.

It does this using one of the accessor function for the function argument list, get function expression argument type or get_fn_expr_argtype() on the first parameter of the function call.

	#include "postgres.h"
	#include 
	#include "fmgr.h"
	
	PG_FUNCTION_INFO_V1(type_of);
	Datum
	type_of(PG_FUNCTION_ARGS)
	{
	   Oid element_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
	   if (!OidIsValid(element_type))
	      elog(ERROR, "Could not determine data type of input");
	   PG_RETURN_OID(element_type);
	}

This is done is C because you are examining a data value rather than a name of a column or table or other object. You have got the object, not the name of it.

The second function is simply an SQL function which looks up the type name given the type OID.

	CREATE OR REPLACE FUNCTION type_name(oid)
    	RETURNS NAME LANGUAGE SQL AS
	$$SELECT typname FROM pg_catalog.pg_type WHERE oid = $1;$$;

The tar file for this C function should be extracted into the contrib directory. There it leverages pgxs and you should be able to:

	make
	make install

	psql dbname < type_of.sql

In testing the functionality we found one flaw. It does not work well with text types without an explicit cast. This is because text is the universal input format for any type and PostgreSQL does not presume to guess what you really meant. This is one test script and output:

	$ cat testme.sql
	select (select datname from pg_database limit 1) as value,
	   type_name( type_of( (select datname from pg_database limit 1) ) ) as type;
	select 10 as value, type_name( type_of( 10 ) ) as type;
	select 10101010101010.1212121212121 as value,
	   type_name( type_of( 10101010101010.1212121212121 ) ) as type;
	select now() as value, type_name( type_of( now() ) ) as type;
	select 12.3 as value, type_name( type_of( 12.3 ) ) as type;
	select 40000.3222 as value, type_name( type_of( 40000.3222 ) ) as type;
	select 'text' as value, type_name( type_of( 'text' ) ) as type;
	select '12/31/59' as value, type_name( type_of( '12/31/59' ) ) as type;
	
	$ psql < testme.sql
	  value  | type
	---------+------
	 webstat | name
	(1 row)
	
	 value | type
	-------+------
	    10 | int4
	(1 row)
	
	            value             |  type
	------------------------------+---------
	 10101010101010.1212121212121 | numeric
	(1 row)
	
	            value            |    type
	-----------------------------+-------------
	 2005-08-16 20:47:27.0441-07 | timestamptz
	(1 row)
	
	 value |  type
	-------+---------
	  12.3 | numeric
	(1 row)
	
	   value    |  type
	------------+---------
	 40000.3222 | numeric
	(1 row)
	
	ERROR:  could not determine anyarray/anyelement type because input has type "unknown"
	ERROR:  could not determine anyarray/anyelement type because input has type "unknown"

Contributors: david at fetter.org, elein at varlena.com

Looking for Lock Trouble Applications not closing locks 21-Aug-2005

In the course of human programming there are sometimes cases where programmers make errors and forget to close open block transactions. I know none of us commit this terrible error, however, sometimes it happens. This lack of a final COMMIT or ROLLBACK on a connection can leave it holding locks. If the connection is in a connection pool, one never knows when it will be reinitialized.

These held locks do not prevent ordinary operations, however, they do prevent operations which require exclusive table locks. For example, if you needed to drop and re-add an index on a table, it needs an exclusive lock on a table for a brief bit of time. Connections left with open transactions prevent this. They also prevent vacuum full operations.

Using ps or pg_stat_activity, you can see these transactions as " in transaction". You don't see a query in current_query in the pg_stat_activity view because there isn't one. It is waiting for another query or a COMMIT or ROLLBACK.

	oopsdb=# select datname, procpid, usename, current_query, query_start 
	oopsdb=# from pg_stat_activity where current_query = ' in transaction';
  	datname | procpid | usename | current_query         | query_start
----------+---------+---------+-----------------------+----------------------------
  oopsdb  | 22345   | appuser |  in transaction | 2005-08-21 12:52.457896-07

This can be a legitimate state for a connection if you catch it just at the end of a transaction. It is only a problem if the connection remains in that state. Run the select a few times. Give it a few minutes, even, depending on the lengths of your queries.

Here is another query that will show you the exact tables which are locked. The query_start field can be qualified to taste. Note that the exclusive lock does not show a relation. This is the lock on the transaction's own transaction id.

	=# SELECT procpid, usename , (now() - query_start) as age,
   	c.relname , l.mode, l.granted
	FROM pg_stat_activity a LEFT OUTER JOIN pg_locks l ON (a.procpid = l.pid)
	LEFT OUTER JOIN pg_class c ON (l.relation = c.oid)
	WHERE current_query = ' in transaction'
	-- and query_start < now() - '5 minutes'::interval
	ORDER BY pid;

	 procpid | usename |    age           |  relname      |      mode       | granted 
	---------+---------+------------------+---------------+-----------------+---------
	    2180 | appuser | 00:01:00.243461  | accounting    | AccessShareLock | t
	    2180 | appuser | 00:01:00.243461  | people        | AccessShareLock | t
	    2180 | appuser | 00:01:00.243461  | departments   | AccessShareLock | t
	    2180 | appuser | 00:01:00.243461  | divisions     | AccessShareLock | t
	    2180 | appuser | 00:01:00.243461  | payroll       | AccessShareLock | t
	    2180 | appuser | 00:01:00.243461  |               | ExclusiveLock   | t

Now that we can see there is a problem, what are we going to do about it? The problems are that we are using a connection pool and cannot trace the process id back to a particular user and since there is no active query, we cannot see what query or queries preceded it. If we could see the queries that preceded it then we could trace it back in the application code and look for the missing COMMIT.

To resolve this lack of information, in postgresql.conf, we set logging to include the process id in the log header. (This is an 8.0 feature.) And we turned statement logging on briefly. We grepped out the statements from the postgres log that belong to the misbehaving connection process. This was 2180 in the example above. Then we were were able to see the BEGIN transaction and the queries that followed and the lack of the COMMIT or ROLLBACK. Knowing what queries were executed, the developers of the application were able to trace back into their code and rectify the lack of a COMMIT in a certain code path.

Contributors: elein at varlena.com