Hide template database in pgAdmin

Question

In order to change the collation of PostgreSQL's database template, I dropped template1 and recreated it with the 'correct' collation. I therefore got my inspiration from this question.

All fine now, but now that newly database template template1 is listed in the available databases in the tree view. I compared database pg_database for two servers (one listing the database template1, one not), but the values of the parameters for database template1 are the same.

I would like to hide this database from in the tree view. Anyone who can figure this out?

EDIT: this one did not bring me any further

(PostgreSQL 9.6, pgAdmin 1.22)

Answer 1

PgAdmin uses the following condition to show the database in the tree or not:

/* Condition used to show database */
if (settings->GetShowSystemObjects() || !database->GetSystemObject())
    (...)

/* Function called above */
bool pgDatabase::GetSystemObject() const
{
    if (server)
    {
        if (this->GetName() == wxT("template0")) return true;
        return (this->GetOid() <= server->GetLastSystemOID());
    }
    else
    {
        return false;
    }
}

Unless you've marked "Show System Objects in the treeview" option, I guess that your template1's oid is greater than LastSystemOID (pg_database.datlastsysoid). In this case you have three options:

1. Rebuild your cluster with right collation;
2. Accept that;
3. or, assuming you weren't in production, play with pg_database.datlastsysoid and wait for side effects.

Answer 2

PgAdmin had an issue where template1 was shown even if the option was turned off to show system objects. This happened when you migrated your database before via pg_upgrade.

The image below shows the template database in the current pgAdmin version with the Show system object option enabled:

The issue was in the condition shown by Michel Milezzi. It is not correct to determine that a database is a template by oid <= lastSystemOid. Because when you use pg_upgrade, the oid of the template1 database is changed and the new value is higher than the lastSystemOid (i don't know if this is also a bug).

oid    datname    datlastsysoid  datacl
13394  template0  13394          {=c/postgres,postgres=CTc/postgres}
1      template1  13394          {=c/postgres,postgres=CTc/postgres}

after using pg_upgrade:
13756  template0  13756          {=c/postgres,postgres=CTc/postgres}
16446  template1  13756          {postgres=CTc/postgres,=c/postgres}
 ^