Please add Postgres BDR support for migrations #770

Open
opened 2017-11-03 22:50:24 +00:00 by viktike · 0 comments
viktike commented 2017-11-03 22:50:24 +00:00 (Migrated from github.com)

BDR is an open-source multi-master solution for Postgres backed by 2ndquadrant. XORM already works great with it in case of DML (I use it with Gitea). But during migrations (DML) I often encounter the following issues:

Adding column to an existing table with a default value:

ERROR: ALTER TABLE ... ADD COLUMN ... DEFAULT may only affect UNLOGGED or TEMPORARY tables when BDR is active;

Solution for is easy:

  • Add the column without a default value
  • Set all rows' new column to the seired default value
  • Alter the column to have that default value in a separate query

Sequences are not global
This causes problems only on the long-run. It should just try to execute:
ALTER SEQUENCE table_id_seq USING bdr;
It will fail if it's s not a BDR database, but still better. It's not that important by the way. I could just set default_sequenceam = 'bdr' in the server's configuration.

Actually there could be another issue:
ERROR: Cannot run UPDATE or DELETE on table table_name because it does not have a PRIMARY KEY.
But I think there is nothing to do with this, the schema should make sure there is a primary key on all the tables.

So to summerize, if only the first problem can be solved I would be very happy. Actually this would benefit any use who uses logical level replication.

BDR is an open-source multi-master solution for Postgres backed by 2ndquadrant. XORM already works great with it in case of DML (I use it with Gitea). But during migrations (DML) I often encounter the following issues: **Adding column to an existing table with a default value:** ``` ERROR: ALTER TABLE ... ADD COLUMN ... DEFAULT may only affect UNLOGGED or TEMPORARY tables when BDR is active; ``` Solution for is easy: - Add the column without a default value - Set all rows' new column to the seired default value - Alter the column to have that default value in a separate query **Sequences are not global** This causes problems only on the long-run. It should just try to execute: `ALTER SEQUENCE table_id_seq USING bdr;` It will fail if it's s not a BDR database, but still better. It's not that important by the way. I could just set _default_sequenceam = 'bdr'_ in the server's configuration. Actually there could be another issue: `ERROR: Cannot run UPDATE or DELETE on table table_name because it does not have a PRIMARY KEY.` But I think there is nothing to do with this, the schema should make sure there is a primary key on all the tables. So to summerize, if only the first problem can be solved I would be very happy. Actually this would benefit any use who uses **logical level replication**.
lunny modified the milestone from (deleted) to (deleted) 2020-03-03 12:55:51 +00:00
lunny added
kind
proposal
and removed
kind
enhancement
labels 2020-03-03 12:56:23 +00:00
lunny removed this from the (deleted) milestone 2023-07-26 01:23:28 +00:00
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#770
No description provided.