[GENERAL] When are SQL functions inlined how can I tell?

[J. Greg Davidson]

I've been having some serious ( 100x) slowness in some of my code
which uses nice maintainable little SQL functions versus when I do
the same code as a big messy expression.  I got rid of STRICT, I've
tried STABLE and even IMMUTABLE and I can't seem to get the speed
difference to go away, so here are some questions:

(1) What are the current criteria for when an SQL function called
within another SQL function will be inlined?

(2) Is there some easy way to tell without massive logging whether
a particular function has been inlined?  I've tried doing various

EXPLAIN ... SELECT foo(...);

kinds of things where foo() calls bar() and I don't see that I can
tell anything about the inline status of either function.

Thanks,

_Greg

J. Greg Davidson

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Danger of idiomatic plpgsql loop for merging data

[J. Greg Davidson]

On Wed, 2010-08-04 at 11:12 -0400, Merlin Moncure wrote:

 The infinite loop check is good but you missed the most important
 part: you need to be checking sqlerrm to see where the unique
 violation is coming from.  Your original issue was that some dependent
 trigger was causing the error which is getting caught here.  Your
 check should ONLY be handling unique violations on the table 'foos'.
 
 The error message (sqlerrm) will look something like this:
 ERROR:  duplicate key value violates unique constraint a_constraint_pkey
 
 I would do something like this:
   WHEN unique_violation THEN -- another TABLE?
 this_table := false;
 
 IF SQLERRM ~ 'unique constraint a_constraint_pkey' THEN
   this_table := true;
 END IF;
 
 IF SQLERRM ~ 'unique constraint another_unique_constraint' THEN
   this_table := true;
 END IF;
 
IF NOT this_table
  RAISE '%', SQLERRM USING ERRCODE = 'unique_violation';
END IF;
 
 yes, this is awful.  hopefully your constraints have useful names that
 are unique.  IMNSHO the fully schema qualified table name should be in
 the error message.
 
 merlin

My infinite loop check is probably paranoia if I put in the check
you suggest.  The check you suggest is absolutely correct, yet it
cannot be coded portably.  The unique constraints have whatever name
PostgreSQL generates in response to the PRIMARY KEY or UNIQUE keywords.
I have to deal with 48 different tables in the current codebase, so
both maintenance and boilerplate reduction are important.  This leads
me to suggest the following new idiom for this kind of function,
starting with two necessary utility functions:

-- definitions needed here moved to bottom of message

CREATE OR REPLACE
FUNCTION errm_is_from_table(text, regclass) RETURNS boolean AS $$
-- Warning: Non-portable implementation!
-- Based on current PostgreSQL SQLERRM strings like:
-- duplicate key value violates unique constraint ...
-- ... foos_pkey
  SELECT $1 LIKE '%' || $2 || '_%%'
$$ LANGUAGE sql;

CREATE OR REPLACE
FUNCTION errm_not_from_table(
  text, regclass, regprocedure, VARIADIC text[] = '{}'::TEXT[]
) RETURNS boolean AS $$
BEGIN
  IF NOT errm_is_from_table($1, $2) THEN
RETURN true;
  END IF;
  RAISE NOTICE '% raised: %',
$3::regproc || '(' || array_to_string($4, ',') || '): ', $1;
  RETURN false;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE
FUNCTION get_foo(text) RETURNS foo_reftype AS $$
DECLARE
 _ref foo_reftype;
 _table regclass := 'foos';
 this regprocedure := 'get_foo(text)';
BEGIN
 LOOP
   SELECT ref_ INTO _ref FROM foos WHERE name_ = $1;
   IF FOUND THEN RETURN _foo; END IF;
   BEGIN
 INSERT INTO foos(name_) VALUES($1);
   EXCEPTION
 WHEN unique_violation THEN
   IF errm_not_from_table(ERRM, _table, _this, $1) THEN
RAISE;-- re-raises original exception
  END IF;
   END;
 END LOOP;
END;
$$ LANGUAGE plpgsql;

If I could move the re-raising into errm_not_from_table()
then I could make things even cleaner, but I don't know
if that's possible.

Here are the omitted definitions needed to make this
simplified example code work:

CREATE DOMAIN foo_reftype AS INTEGER;

CREATE TABLE foos (
   ref_ foo_reftype PRIMARY KEY,
   name_ text UNIQUE NOT NULL
);

CREATE SEQUENCE foos__seq OWNED BY foos.ref_;

CREATE FUNCTION next_foo_ref() RETURNS foo_reftype AS $$
  SELECT nextval('foos__seq')::foo_reftype
$$ LANGUAGE sql;

ALTER  TABLE foos ALTER COLUMN ref_ SET DEFAULT next_foo_ref();

_Greg

J. Greg Davidson

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Danger of idiomatic plpgsql loop for merging data

[J. Greg Davidson]

Hi fellow PostgreSQL hackers,

First, a thank you to Merlin for commenting on my earlier post!
I've run into another dangerous problem since the earlier post.

I began converting from the plpgsql loop idiom for merging data
into a COALESCE(find(), create(), find()) idiom and ran into a
problem with the latter.  I'll hold the full code to the end of
this post and summarize the situation first.

I'm using this idiom in 48 get_ functions associated with
48 different tables!  In all cases, the functions should be
idempotent with monotonic side effects, i.e. when the data
desired is not present (the first time they are called),
they create the data; in all cases, they return a reference
(primary key value) to the data.

Here's the new problem: Depending on the idiom I use, when I
nest a call to one of these get_ functions as an argument to
another function, the outer function does not see the new data!

To be more specific, in the call:
   SELECT access_foo( get_foo(foo_data) )
where access_foo expects a reference to a row of TABLE foos
and get_foo returns such a reference, possibly creating the
desired row,

when I write get_foo() with the plpgsql loop idiom, it seems
to always return a reference which access_foo can use immediately.
On the other hand, when I use the COALESCE(find(), create(), find())
idiom and the get_foo() function created a new row, access_foo
fails to find it!

In all cases saying:
   SELECT get_foo(foo_data);
   SELECT access_foo( get_foo(foo_data) );
works fine since if the data needed to be added, it was done
in the separate earlier transaction.

Because of this problem, I'm abandoning my original preference
for the COALESCE(find(), create(), find()) idiom and I'm adding
a check to the plpgsql LOOP idiom to prevent it going infinite.

For those who'd like to see the gory details, here is the
code, simplified as much as possible (and with a suffix on
the type name to please Merlin:):

-- (0) The table in question:

-- The trailing underscores can be read as field or slot
-- which is sometimes useful to avoid clashes with reserved
-- words, local variables, etc.

CREATE TABLE foos (
  ref_ foo_reftype PRIMARY KEY DEFAULT next_foo_ref();
  name_ text UNIQUE NOT NULL;
);

-- (1) The idiom from the PostgreSQL reference manual, which
-- unfortunately can go into an infinite loop if a trigger
-- should raise a unique_violation exception.

-- The underscore prefix can be read as local
-- and is sometimes useful to avoid name clashes, etc.

CREATE OR REPLACE
FUNCTION get_foo(text) RETURNS foo_reftype AS $$
DECLARE
  _ref foo_reftype;
BEGIN
  LOOP
SELECT ref_ INTO _ref
  FROM foos WHERE name_ = $1;
IF FOUND THEN RETURN _ref; END IF;
BEGIN
  INSERT INTO foos(name_) VALUES($1);
EXCEPTION
  WHEN unique_violation THEN
  -- maybe another thread?
END;
  END LOOP;
END;
$$ LANGUAGE plpgsql STRICT;

-- (2) Where I was originally going
-- to avoid the infinite loop problem,
-- and also hoping to get better performance
-- on the most common case where the
-- first call to old_foo() finds the row
-- (since SQL functions are inlined into
-- the execution plan):

CREATE OR REPLACE
FUNCTION old_foo(text) RETURNS foo_reftype AS $$
  SELECT ref_ FROM foos WHERE name_ = $1
$$ LANGUAGE SQL STRICT;

CREATE OR REPLACE
FUNCTION new_foo(text) RETURNS foo_reftype AS $$
DECLARE
  this regprocedure := 'new_foo(text)';
  _ref foo_reftype;
BEGIN
  INSERT INTO foos(name_) VALUES ($1)
RETURNING ref_ INTO _ref;
  RETURN _ref;
EXCEPTION
  WHEN unique_violation THEN
-- maybe another thread?
RAISE NOTICE '% % unique_violation', this, $1;
RETURN NULL;
END;
$$ LANGUAGE plpgsql STRICT;

CREATE OR REPLACE
FUNCTION get_foo(text) RETURNS foo_reftype AS $$
  SELECT COALESCE(
old_foo($1), new_foo($1), old_foo($1)
  )
$$ LANGUAGE sql STRICT;

-- (3) Where I'm going now, although it feels
-- like a patch (I hate :-( patches!):

CREATE OR REPLACE
FUNCTION get_foo(text) RETURNS foo_reftype AS $$
DECLARE
  _ref foo_reftype;
  killroy_was_here boolean := false;
  this regprocedure := 'get_foo(text)';
BEGIN
  LOOP
SELECT ref_ INTO _ref
  FROM foos WHERE name_ = $1;
IF FOUND THEN RETURN _foo; END IF;
IF killroy_was_here THEN
  RAISE EXCEPTION '% % loops!', this, $1;
END IF;
killroy_was_here := true;
BEGIN
  INSERT INTO foos(name_) VALUES($1);
EXCEPTION
  WHEN unique_violation THEN -- another thread?
  RAISE NOTICE '% % unique_violation', this, $1;
END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- end code

_Greg

P.S. Sorry about the excessive underscores!
I've been burned by having local variables
confused with fields, so when I need to clearly
differentiate such things, I use this convention.

J. Greg Davidson

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Danger of idiomatic plpgsql loop for merging data

[J. Greg Davidson]

Hi fellow PostgreSQL hackers,

I just got burned by the idiomatic loop
documented in the PostgreSQL manual as

Example 39-2. Exceptions with UPDATE/INSERT

I have now replaced this standard idiom
with a safer one described below.

What went wrong:

It seems that the table I was either
inserting into or selecting from had
a trigger inserting some associated
data which was sometimes raising a
unique_violation exception, turning the
standard idiom into an infinite loop!

My (simplified) old code looked like this:

CREATE TABLE foos (
  foo_ foo PRIMARY KEY DEFAULT next_foo();
  name_ text UNIQUE NOT NULL;
);

CREATE OR REPLACE
FUNCTION get_foo(text) RETURNS foo AS $$
DECLARE
  _foo foo;
BEGIN
  LOOP
SELECT foo_ INTO _foo
  FROM foos WHERE name_ = $1;
IF FOUND THEN RETURN _foo; END IF;
BEGIN
  INSERT INTO foos(name_) VALUES($1);
EXCEPTION
  WHEN unique_violation THEN
  -- maybe another thread?
END;
  END LOOP;
END;
$$ LANGUAGE plpgsql STRICT;

My (simplified) new code is longer but
more flexible, safer and adds logging:

CREATE OR REPLACE
FUNCTION old_foo(text) RETURNS foo AS $$
  SELECT foo_ FROM foos WHERE name_ = $1
$$ LANGUAGE SQL STRICT;

CREATE OR REPLACE
FUNCTION new_foo(text) RETURNS foo AS $$
DECLARE
  this regprocedure := 'new_foo(text)';
  _foo foo;
BEGIN
  INSERT INTO foos(name_) VALUES ($1)
RETURNING foo_ INTO _foo;
  RETURN _ref;
EXCEPTION
  WHEN unique_violation THEN
-- maybe another thread?
RAISE NOTICE '% % unique_violation', this, $1;
RETURN NULL;
END;
$$ LANGUAGE plpgsql STRICT;

CREATE OR REPLACE
FUNCTION get_foo(text) RETURNS foo AS $$
  SELECT COALESCE(
old_foo($1), new_foo($1), old_foo($1)
  )
$$ LANGUAGE sql STRICT;

_Greg

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Need xml element constructor given tag, attribute array children

[J. Greg Davidson]

Hi Everybody,

I'm having trouble finding any way to build up
XML values from their logical components, e.g.
given an XML element tag, an array of attributes
in some form, and an xml[] array of children I
can't find any SQL function or expression which
would construct the XML element.  Surely the
syntactic sugary XMLPARSE must be calling such
a function?  Am I missing something or is
PostgreSQL missing something?

Thanks,

_Greg

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Can ARRAY( ... ) generate text[][]?

[J. Greg Davidson]

Hi fellow PostgreSQL hackers!

I tried to write an SQL glue function to turn an array
of alternating key/value pairs into an array of arrays
and got the message

ERROR:  42704: could not find array type for data type text[]

-- BEGIN CODE

-- Here's a simplified example:

CREATE OR REPLACE
FUNCTION text__(variadic text[]) RETURNS text[][] AS $$
SELECT ARRAY(
  SELECT ARRAY[ $1[i], $1[i+1] ] 
  FROM generate_series(1, array_upper($1,1), 2) i
)
$$ LANGUAGE sql;

-- Here's what I really wanted:

CREATE OR REPLACE
FUNCTION xpath_(text, xml, variadic text[]) RETURNS xml AS $$
SELECT ( xpath($1,$2,ARRAY(
  SELECT ARRAY[ $3[i], $3[i+1] ] 
  FROM generate_series(1, array_upper($3,1), 2) i
)   )   )[1]::xml
$$ LANGUAGE sql;

-- END CODE

Is there any decent way to do this in SQL, or do I need
to write it in a procedural language?

Thanks,

_Greg

J. Greg Davidson


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] surprised by non-strict array_append

[J. Greg Davidson]

I was caught out today by the non-strict behavior of array_append
causing me to get an undesired result for a COALESCE.  My subsequent
attempt to create a STRICT VARIADIC generalization of array_append
led to another surprise.  The problem was easily solved, but might
be of interest to others.  Perhaps someone will enlighten me as to
why the standard array functions are non-strict and why the STRICT
VARIADIC function fails to be strict.  I've boiled it down to make
it clear:

psql (8.4.2)

SELECT COALESCE( ARRAY[1] || NULL::integer, ARRAY[42] );
  coalesce 
--
 {1,NULL}
(1 row)

SELECT COALESCE( array_append( ARRAY[1], NULL), ARRAY[42] );
  coalesce 
--
 {1,NULL}
(1 row)

CREATE OR REPLACE
FUNCTION array_add(ANYARRAY, VARIADIC ANYARRAY) RETURNS ANYARRAY AS $$
  SELECT $1 || $2
$$ LANGUAGE sql STRICT;
COMMENT ON FUNCTION array_add(ANYARRAY, ANYARRAY)
IS 'a strict generalization version of array_append';

SELECT COALESCE( array_add( ARRAY[1], NULL, NULL), ARRAY[42] );
coalesce
---
 {1,NULL,NULL}
(1 row)

-- hmm - not very strict!

CREATE OR REPLACE
FUNCTION array_add1(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY AS $$
  SELECT $1 || $2
$$ LANGUAGE sql STRICT;
COMMENT ON FUNCTION array_add1(ANYARRAY, ANYELEMENT)
IS 'a strict version of array_append';

SELECT COALESCE( array_add1( ARRAY[1], NULL), ARRAY[42] );

coalesce 
--
 {42}
(1 row)

-- ah, finally!

_Greg

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Any need to copy args before SPI C function callls SQL function?

[J. Greg Davidson]

I have some SPI C functions which dispatch to a selected PostgreSQL
function based on the the first argument value.  I have 3 questions
and am very open to any other advise you might have:

1. If any of the arguments are bigger than a word, e.g. text or arrays,
do I need to copy them into upper memory or can I just reuse the Datum
value passed to the C function in calling the SQL function?

2. When the returned value is bigger than a word I need to copy it
before returning it - is there a way for the SPI C function to do this
without having to know the detailed type of the value returned?  I'm
hoping there might be some handy formula with macros here.

3. I'm doing the calling using saved query plans - this seems to be the
only way to call an arbitrary function using the documented SPI API.
However, I see other code using undocumented functions to directly
call PostgreSQL functions from C, which looks easier.  I want to use the
fastest method possible that will not break with future releases - what
do you advise?

Thanks for your answers to these questions and thanks for the excellent
responses to my earlier questions!

Happy New Year,

_Greg

J. Greg Davidson

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Seeking expected return type info for SPI function

[J. Greg Davidson]

When PostgreSQL calls a C function I get all kinds of interesting
information in the
struct FunctionCallInfoData
and
struct FmgrInfo
(details at bottom).  I was hoping to get the oid of the expected
return type somewhere, but I don't see it.  Am I missing something?
I'm trying to avoid the overhead of looking up the function in the
pgproc table while handling it.

(In case you were wondering, I'm trying to handle a large family
of similar types with a single set of C functions for their input
and output methods.)

Thanks,

_Greg

Excerpt from server/fmgr.h:

typedef struct FmgrInfo {
 PGFunction fn_addr; /* pointer to function or handler to be called */
 Oid fn_oid;/* OID of function (NOT of handler, if any) */
 short fn_nargs; /* 0..FUNC_MAX_ARGS, or -1 if variable arg count */
 bool fn_strict;/* function is strict (NULL in = NULL out) */
 bool fn_retset;/* function returns a set */
 unsigned char fn_stats; /* collect stats if track_functions  this */
 void *fn_extra;/* extra space for use by handler */
 MemoryContext fn_mcxt; /* memory context to store fn_extra in */
 fmNodePtr fn_expr; /* expression parse tree for call, or NULL */
} FmgrInfo;

/*
 * This struct is the data actually passed to an fmgr-called function.
 */
typedef struct FunctionCallInfoData {
 FmgrInfo   *flinfo;/* ptr to lookup info used for this call */
 fmNodePtr context; /* pass info about context of call */
 fmNodePtr resultinfo;  /* pass or return extra info about result */
 bool isnull;   /* function must set true if result is NULL */
 short nargs;   /* # arguments actually passed */
 Datum arg[FUNC_MAX_ARGS];  /* Arguments passed to function */
 bool argnull[FUNC_MAX_ARGS]; /* T if arg[i] is actually NULL */
} FunctionCallInfoData

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How can I interpolate psql variables in function bodies? - workaround

[J. Greg Davidson]

I've found a workaround using the new pg 8.3 feature of default values
for function arguments.  It is not a completely general workaround (it
won't, e.g. allow type or table names to be interpolated) but it does
what I need and perhaps others may find this trick useful.

To briefly recap the problem:

On Mon, 2009-06-15 at 15:28 -0700, J. Greg Davidson wrote:
 Hi dear colleagues,
 
 When I need such a constant in a function
 it is not substituted:
 $ psql -v foo=10

 # create function foo() returns integer as 'select '(:foo) language sql;
 ERROR:  syntax error at or near ( at character 51

The workaround:

CREATE FUNCTION foo(integer DEFAULT :foo) RETURNS integer AS $$
  SELECT $1
$$ LANGUAGE sql;

In other words, I add an extra DEFAULT argument for each psql variable I
need to use in the function body.

I'm wondering if there is any advantage in psql's declining to do
substitutions inside of quotes, especially $$ oxford quotes $$.  Perhaps
a future version of psql can make things easier.

_Greg

J. Greg Davidson

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How can I interpolate psql variables in function bodies?

[J. Greg Davidson]

On Tue, 2009-06-16 at 09:20 +0200, Albe Laurenz wrote:

 I came up with the following:
 
 psql -v foo=10
 test= \set funcbody `echo 'SELECT\ `:foo`echo '`
 test= create function foo() returns integer as :funcbody language sql;
 
 But that is ugly as hell and depends on the underlying operating
 system to have a Bourne-like shell.
 
 Yours,
 Laurenz Albe

Here is a similar trick:

psql -v f=10
\set g '''select ' :f '::integer;'''
create function g() returns integer as :g language sql immutable;

g() can be used in the bodies of other functions where it should
be inline substituted since it's immutable.

I'm concerned as to whether this might break if psql slightly
changes how it it does substitution.  The documentation does not
fully specify how substitution behaves.

_Greg

J. Greg Davidson

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How can I interpolate psql variables in function bodies?

[J. Greg Davidson]

Hi dear colleagues,

I'm trying to pull some platform-specific constants out of my
code by using psql variables, e.g.:

$ psql -v TypeLength=4

# CREATE TYPE tref (
  INTERNALLENGTH = :TRefTypeLength,
  INPUT = tref_in,
  OUTPUT = tref_out,
  PASSEDBYVALUE
);

which works fine, but when I need such a constant in a function
it is not substituted.  A simplified example:

$ psql -v foo=10

# select :foo;
 ?column? 
--
   10
(1 row)

# create function foo() returns integer as 'select '(:foo) language sql;
ERROR:  syntax error at or near ( at character 51

I'm sure that I could do something horrible by using EXECUTE inside of
a plpgsql function, and I'm hoping that someone will have a simpler
alternative.  For example, is there some kind of quoting mechanism I can
use which will not impede psql from doing substitutions?

Thanks,

_Greg


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] User-defined-type in C crashing PostgreSQL server: What am I doing wrong?

[J. Greg Davidson]

Hello, 

My user-defined types are crashing the PostgreSQL server and I don't
understand why.  I've been trying to figure it out on my own for overr
a week.  I've cooked what I'm doing down to the essentials and I'm
asking for help.  Help: What am I doing wrong?

* Sections of this message:
** Overview with psql session
** SQL code
** C Code
** gdb backtraces
***  gdb backtrace after SELECT ARRAY[ pair_tag_id(3, 4) ];
***  gdb backtrace after SELECT x from pair_tag_id(3, 4) x;
***  gdb backtrace after SELECT ROW( pair_tag_id(3, 4) );
** Thanks for your help!

** Overview wiith psql session

Tested with: PostgreSQL-8.1.4 and  PostgreSQL-8.2beta3,
installed from source with
./configure ... --enable-debug --enable-cassert
Platform: SuSE Linux 10.0 on i686 Dell Notebook Computer

I have a simple one-word datatype called a pair, which consists of a
1-byte tag and a 3-byte id.  

-- At first, it seems to work:

pairs=# select pair_tag_id(3, 4);
   pair_tag_id   
-
 pair tag=3 id=4 /
(1 row)

pairs=# select tag_pair(pair_tag_id(3, 4));
 tag_pair 
--
3
(1 row)

pairs=# select id_pair(pair_tag_id(3, 4));
 id_pair 
-
   4
(1 row)

-- But all three of these crash the server with Signal 11:

SELECT ARRAY[ pair_tag_id(3, 4) ];

SELECT x from pair_tag_id(3, 4) x;

SELECT ROW( pair_tag_id(3, 4) );

-- Note:Nothing special about 3 and 4, all values I've tried behave
--  the same!

** SQL code

-- CREATE TYPE pair; -- PostgreSQL 8.2

CREATE OR REPLACE
FUNCTION pair_in(cstring) RETURNS pair
AS 'pair.so' LANGUAGE 'c' STRICT;

CREATE OR REPLACE
FUNCTION pair_out(pair) RETURNS cstring
AS 'pair.so' LANGUAGE 'c' STRICT;

CREATE TYPE pair (
  INTERNALLENGTH = 4,   -- 32-bits
  INPUT = pair_in,
  OUTPUT = pair_out
);

CREATE OR REPLACE
FUNCTION tag_pair(pair) RETURNS integer
AS 'pair.so' LANGUAGE 'c' STRICT;

CREATE OR REPLACE
FUNCTION id_pair(pair) RETURNS integer
AS 'pair.so' LANGUAGE 'c' STRICT;

CREATE OR REPLACE
FUNCTION pair_tag_id(integer, integer) RETURNS pair
AS 'pair.so' LANGUAGE 'c' STRICT;

** C Code

$ cc -fpic -I/Ubuntu/usr/local/pgsql-8.1.4/include/server -Wall   -c -o
pair.o pair.c
$ cc -shared -o pair.so pair.o
$ cat pair.c

#include stdlib.h
#include stdarg.h
#include string.h
#include postgres.h   /* general Postgres declarations */
#include fmgr.h   /* for argument/result macros */

#define PAIR_TAG_WIDTH 8
#define PAIR_MAX_TAG ( (1U  PAIR_TAG_WIDTH) - 1)
#define PAIR_MAX_ID ( (int) (INT_MAX  PAIR_TAG_WIDTH) )
#define PAIR_MIN_ID ( -PAIR_MAX_ID )

typedef long pairs; // a tag plus an ID
typedef unsigned tags;
#define F_TAG tag: %u // Format string for tags
typedef long ids;
#define F_ID id: %ld  // Format string for ids

typedef char *StrPtr;   // '\0' terminated modifiable string
typedef const char *Str;// '\0' terminated constant string

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

inline pairs TagPairId (tags tag, ids id) {
  return (id  PAIR_TAG_WIDTH) | tag;
}
inline tags TagPair(pairs v) {
  return v  ~(~0U   PAIR_TAG_WIDTH);
}
inline ids IdPair(pairs v) {
  return v  PAIR_TAG_WIDTH;
}

#define FUNCTION_DEFINE(f)  \
PG_FUNCTION_INFO_V1(f); \
Datum (f)(PG_FUNCTION_ARGS)

FUNCTION_DEFINE(pair_tag_id) {  // (tags, ids) - pair
  tags tag = PG_GETARG_INT32(0);
  ids  id =  PG_GETARG_INT32(1);
  PG_RETURN_INT32( TagPairId( tag,  id) );
}

FUNCTION_DEFINE(tag_pair) { // (pair) -  tags
  PG_RETURN_INT32( TagPair( PG_GETARG_INT32(0) ) );
}

FUNCTION_DEFINE(id_pair) {  // (pair) - ids
  PG_RETURN_INT32( IdPair( PG_GETARG_INT32(0) ) );
}

inline StrPtr NewStr( Str old ) {
  return strcpy( palloc(strlen(old)+1), old );
}

StrPtr PairToXMLStr(Str message, pairs p) {
  tags tag = TagPair(p);
  ids id = IdPair(p);
  Str msg = message ? message : ;
  Str format = message
? pair tag=\%u\ id=\%d\%s/pair
: pair tag=\%u\ id=\%d\ %s/;
// I tried with both of the next two lines, just in case: same behavior
//  char buffer[strlen(format) + strlen(msg) + 2 * 20]; // gcc extension
  char * buffer = palloc(strlen(format) + strlen(msg) + 2 * 20);
  sprintf(buffer, format, tag, id, msg);
  return NewStr(buffer);
}

FUNCTION_DEFINE(pair_in) {  // cstring - pair
  PG_RETURN_INT32(0);
}

FUNCTION_DEFINE(pair_out) { // pair - cstring
  PG_RETURN_CSTRING( PairToXMLStr( NULL, PG_GETARG_INT32(0) ) );
}


** gdb backtraces

# gdb bin/postgres data/core
GNU gdb 6.3
...
Core was generated by `postgres: greg pairs [local] SELECT
'.

Program terminated with signal 11, Segmentation fault.
...


***  gdb backtrace after SELECT ARRAY[ pair_tag_id(3, 4) ];
(gdb) backtrace
#0  0xb7d2a07d in memmove () from /lib/tls/libc.so.6
#1  0x0403 in ?? ()
#2  0x081ed694 in ArrayCastAndSet (src=138452140, typlen=1027,
typbyval=value optimized out, typalign=105 'i',
dest=0x8409cac \177\177\177\177~, '\177' repeats