Table Partitioning & Long Names

While working on a way to reapply indexes to existing partitions in my partition management extension, it dawned on me that PostgreSQL’s default object name length limit of 63 characters could cause some interesting issues for child table names when setting up partitioning. Since most partitioning relies on a suffix on the end of the child table names for uniqueness and to easily identify their contents, the automatic truncation of a long child name could really cause some big problems. So I set about seeing if I could handle this with pg_partman.

After several weeks of rewriting a lot of the core functionality of object naming, I think I’ve handled this as best as I can. This applied not only to the child table names, but also to the trigger function and trigger name. Below is a simplified version of the function that’s included in the extension for general use in any situation.

CREATE FUNCTION check_name_length (p_object_name text, p_object_schema text DEFAULT NULL, p_suffix text DEFAULT NULL) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_new_length    int;
    v_new_name      text;
BEGIN

IF char_length(p_object_name) + char_length(COALESCE(p_suffix, '')) >= 63 THEN
v_new_length := 63 - char_length(COALESCE(p_suffix, ''));
v_new_name := COALESCE(p_object_schema ||'.', '') || substring(p_object_name from 1 for v_new_length) || COALESCE(p_suffix, ''); 
ELSE
v_new_name := COALESCE(p_object_schema ||'.', '') || p_object_name||COALESCE(p_suffix, '');
END IF;

RETURN v_new_name;

END
$$;

This takes whatever object name is given, checks the length with or without the provided suffix, and returns an object name with a valid length. If the object name would be too long with the suffix, the original name is truncated and the suffix is added. You can also just provide an object name and no suffix and it will check that as well. If no truncation is necessary, you get your original name back with the schema and/or suffix applied if given. The schema name of an object does not count against this length limit, but a parameter is provided so you can get a schema qualified name returned.

Here is an example when no truncation is needed:

keith=# SELECT check_name_length('tablename', 'schema', '_suffix');
    check_name_length    
-------------------------
 schema.tablename_suffix

Here’s an example where truncation would be needed

keith=# select check_name_length('tablename_thisobjectsnameistoolongforpostgresandwillbetruncated', 'schema', '_suffix');
                           check_name_length                            
------------------------------------------------------------------------
 schema.tablename_thisobjectsnameistoolongforpostgresandwillbetr_suffix

The only issue I’ve seen with this method is that if you have a lot of really long, similarly named tables, you can still run into naming conflicts. Especially with serial based partitioning where the original table name is slowly getting truncated more and more over time. But I think handling it like this is preferred to having the suffix unexpectedly truncated, which could cause conflicts within the same partition set.

It’s edge cases like this that drove me to try and make something to handle partitioning easier in PostgreSQL until it’s built in. I’ll be giving a talk at PG Open next week about the development I’ve been doing and how the new extensions system has made it much easier to contribute to PostgreSQL without knowing a single line of C!