PostgreSQL Schema length and Search_Path

Question

I create a new schema on Postgres with the length greater than 63 bytes characters.

CREATE SCHEMA "abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz";

The above statement created a schema abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz_abcdefghi

Postgres automatically removed extra bytes and created the schema with 63 bytes only (I was expecting an error).

Then I ran the following command:

SET search_path TO 'abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz';
SHOW search_path;

CREATE TABLE deepak(item varchar);
INSERT INTO deepak SELECT 'a';

TABLE "abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz".deepak;

My question is

• Show search_path returns the full name but makes new 63-byte name schema active. How does this happen?
• select statement is able to select the table deepak even after giving the wrong schema name. How is it possible?

I have checked information_schema.schemata and pg_tables also. 63-byte name exists in these tables.

Answer 1

The reason behind this is that all object names are of the data type name. Compare the definition of pg_namespace, which is the system catalog containing schemas:

\d pg_namespace
            Table "pg_catalog.pg_namespace"
  Column  |   Type    | Collation | Nullable | Default 
----------+-----------+-----------+----------+---------
 nspname  | name      |           | not null | 
 nspowner | oid       |           | not null | 
 nspacl   | aclitem[] |           |          | 
Indexes:
    "pg_namespace_nspname_index" UNIQUE, btree (nspname)
    "pg_namespace_oid_index" UNIQUE, btree (oid)

name is defined in src/include/c.h (NAMEDATALEN is 64, but the last byte is 0, so the effective length is 63):

/*
 * Representation of a Name: effectively just a C string, but null-padded to
 * exactly NAMEDATALEN bytes.  The use of a struct is historical.
 */
typedef struct nameData
{
    char        data[NAMEDATALEN];
} NameData;
typedef NameData *Name;

#define NameStr(name)   ((name).data)

When the parser processes an identifier, it truncates it to NAMEDATALEN-1.

This truncation has raised a NOTICE since the beginning (commit 0672a3c081 from June 2000), so I'd be surprised if you didn't see that notice (unless you set client_min_messages to warning or error).

search_path is a regular C string without a length limit, so it can contain schema names exceeding 63 bytes, but since entries is cast to name, the extra characters are effectively ignored.

This is not pretty, and I'd argue that the message should be a WARNING at least. You may want to bring it up with the hackers mailing list (or write a patch for it). Raising the level to ERROR would be the cleanest solution, but it would be bad for backward compatibility.