During a setup of multiple PostgreSQL instances, replicating content via the replication framework Slony-I, I had to manually create the very same SQL schema to every Postgres node – as Slony just replicates the payload data and not the actual SQL schemas.
I was creating tables like this on every node:
CREATE TABLE x (
id SERIAL PRIMARY KEY,
content VARCHAR(255) DEFAULT NULL
);
but decided after half of having those nodes I already configured to rename the table from ‘x‘ to ‘y‘ using the ‘ALTER TABLE‘ command
ALTER TABLE x RENAME TO y;
and continued creating the schemas on the remaining nodes with the following command:
CREATE TABLE y (
id SERIAL PRIMARY KEY,
content VARCHAR(255) DEFAULT NULL
);
After finally having provided the schema to all nodes I started the replication daemons and got thrown errors from half of the nodes that replication doesn’t work properly since the schema doesn’t match the one on the master replication server:
CESTERROR remoteWorkerThread_1: “select “_db”.setAddTable_int(1, 3, ‘”public”.”y”‘, ‘x_pkey’, ‘Table public.y with primary key’); ” PGRES_FATAL_ERROR ERROR: Slony-I: setAddTable_int(): table “public”.”y” has no index x_pkey
All of the non-working nodes were those, I first created the table x on and later renamed it to y, instead of just directly creating table y like I did on the others.
Looking at the global table definitions – including the automatically co-created sequence – you can see that the table did get renamed, but the sequence didn’t:
Table y directly created:
postgres=# d
List of relations
Schema | Name | Type | Owner
——–+—————-+———-+———-
public | y | table | postgres
public | y_id_seq | sequence | postgres
(4 rows)
Table x created and altered to be named y:
postgres=# d
List of relations
Schema | Name | Type | Owner
——–+—————-+———-+———-
public | y | table | postgres
public | x_id_seq | sequence | postgres
(4 rows)
That normally doesn’t cause any trouble, since the reference of table y (formerly x) to the sequence x_id_seq is still valid. However since replication requires the very exact same schema on every node this actually is causing trouble. However that’s not the error mentioned in the error message above, which is referring to the primary key.
Diff’ing the actual schemas shows up more differences:
Table “public.y”
Column | Type | Modifiers
———+————————+————————————————
– id | integer | not null default nextval(‘x_id_seq‘::regclass)
+ id | integer | not null default nextval(‘y_id_seq‘::regclass)
content | character varying(255) | default NULL::character varying
Indexes:
– “x_pkey” PRIMARY KEY, btree (id)
+ “y_pkey” PRIMARY KEY, btree (id)
The reference to the sequence and and name of the reference to the value of the primary key were NOT updated by altering the table name to match again. This separation of table-name and references might be a feature, however I find it hard to imagine a use-case where it makes sense using the sequence and/or primary key of another table. UPDATE: I just got told that it indeed might make sense sharing one sequence among several tables.
Also sequence and primary key were created inside / co-created by the ‘CREATE TABLE‘ statement, so at least I’d find it more consistent if both would be always reference by this table, by means of the table they got originally created with.
Looking for information, hints or documentation about this behaviour wasn’t fruitful as well.
So personally I’d really like to really see those reference updated – by changing the tables name – automatically. I’d like to see at least a NOTICE that primary key and sequence are still haveing the name of / are referring to it’s old values and need to be updated / re-created to match again.
Conclusion: Make sure – when altering table names in Postgres – references to primary key and sequence are getting updated as well – manually! Primary key and sequence are NOT tied together with the table they got created with!