BDR Alter table failing
Why does this not work? From what I read only default values should cause
issue. I'm on release 9.4.4:
bms=# ALTER TABLE trap ALTER COLUMN trap_timestamp TYPE TIMESTAMP WITH TIME
ZONE;
ERROR: ALTER TABLE ... ALTER COLUMN TYPE may only affect UNLOGGED or
TEMPORARY
tables when BDR is active; trap is a regular table
On 04/27/2016 07:13 AM, Will McCormick wrote:
Why does this not work? From what I read only default values should
cause issue. I'm on release 9.4.4:bms=# ALTER TABLE trap ALTER COLUMN trap_timestamp TYPE TIMESTAMP WITH
TIME ZONE;
ERROR: ALTER TABLE ... ALTER COLUMN TYPE may only affect UNLOGGED or
TEMPORARY
tables when BDR is active; trap is a regular table
http://bdr-project.org/docs/stable/ddl-replication-statements.html
8.2.3. DDL statements with restrictions
ALTER TABLE
Generally ALTER TABLE commands are allowed. There are a however
several sub-commands that are not supported, mainly those that perform a
full-table re-write.
...
ALTER COLUMN ... TYPE - changing a column's type is not supported.
Chaning a column in a way that doesn't require table rewrites may be
suppported at some point.
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
But this is the exact column definition that exists on the table when I
execute the statement ....
It's like it does not check the pre-existing state of the column. Our code
is expecting a column already exists error but this error predicates that.
On Wed, Apr 27, 2016 at 10:21 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
Show quoted text
On 04/27/2016 07:13 AM, Will McCormick wrote:
Why does this not work? From what I read only default values should
cause issue. I'm on release 9.4.4:bms=# ALTER TABLE trap ALTER COLUMN trap_timestamp TYPE TIMESTAMP WITH
TIME ZONE;
ERROR: ALTER TABLE ... ALTER COLUMN TYPE may only affect UNLOGGED or
TEMPORARY
tables when BDR is active; trap is a regular tablehttp://bdr-project.org/docs/stable/ddl-replication-statements.html
8.2.3. DDL statements with restrictions
ALTER TABLE
Generally ALTER TABLE commands are allowed. There are a however
several sub-commands that are not supported, mainly those that perform a
full-table re-write....
ALTER COLUMN ... TYPE - changing a column's type is not supported. Chaning
a column in a way that doesn't require table rewrites may be suppported at
some point.--
Adrian Klaver
adrian.klaver@aklaver.com
I guess the only viable option would be to the check explicitly ourselves.
On Wed, Apr 27, 2016 at 11:25 AM, Will McCormick <wmccormick@gmail.com>
wrote:
Show quoted text
But this is the exact column definition that exists on the table when I
execute the statement ....It's like it does not check the pre-existing state of the column. Our code
is expecting a column already exists error but this error predicates that.On Wed, Apr 27, 2016 at 10:21 AM, Adrian Klaver <adrian.klaver@aklaver.com
wrote:
On 04/27/2016 07:13 AM, Will McCormick wrote:
Why does this not work? From what I read only default values should
cause issue. I'm on release 9.4.4:bms=# ALTER TABLE trap ALTER COLUMN trap_timestamp TYPE TIMESTAMP WITH
TIME ZONE;
ERROR: ALTER TABLE ... ALTER COLUMN TYPE may only affect UNLOGGED or
TEMPORARY
tables when BDR is active; trap is a regular tablehttp://bdr-project.org/docs/stable/ddl-replication-statements.html
8.2.3. DDL statements with restrictions
ALTER TABLE
Generally ALTER TABLE commands are allowed. There are a however
several sub-commands that are not supported, mainly those that perform a
full-table re-write....
ALTER COLUMN ... TYPE - changing a column's type is not supported.
Chaning a column in a way that doesn't require table rewrites may be
suppported at some point.--
Adrian Klaver
adrian.klaver@aklaver.com
Based on my experience, I can say BDR does not performs pre-DDL checks. For example, if you try to CREATE TABLE with the name of an existing table, BDR will acquire lock anyway, and then will fail when executing the DDL statement on the first node, because the table already exists.
In your case, it's the same: BDR does not checks(nor needs to) if the DDL statement is or not required, as that's a dba dutty. Then, BDR executes the statement(ane acquires locks), and fails because it would require a full table rewrite, which, at the time, is not supported by BDR.
A workaround for this would be:
- ALTER TABLE .... ADD COLUMN (with another name)
- UPDATE (to convert values from the old column to the new one)
- ALTER TABLE .... DROP COLUMN (on the old column)
- ALTER TABLE .... RENAME COLUMN (so new column alhas the same name)
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
Sent from my Sony Xperia™ smartphone
---- Will McCormick wrote ----
But this is the exact column definition that exists on the table when I
execute the statement ....
It's like it does not check the pre-existing state of the column. Our code
is expecting a column already exists error but this error predicates that.
On Wed, Apr 27, 2016 at 10:21 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
Show quoted text
On 04/27/2016 07:13 AM, Will McCormick wrote:
Why does this not work? From what I read only default values should
cause issue. I'm on release 9.4.4:bms=# ALTER TABLE trap ALTER COLUMN trap_timestamp TYPE TIMESTAMP WITH
TIME ZONE;
ERROR: ALTER TABLE ... ALTER COLUMN TYPE may only affect UNLOGGED or
TEMPORARY
tables when BDR is active; trap is a regular tablehttp://bdr-project.org/docs/stable/ddl-replication-statements.html
8.2.3. DDL statements with restrictions
ALTER TABLE
Generally ALTER TABLE commands are allowed. There are a however
several sub-commands that are not supported, mainly those that perform a
full-table re-write....
ALTER COLUMN ... TYPE - changing a column's type is not supported. Chaning
a column in a way that doesn't require table rewrites may be suppported at
some point.--
Adrian Klaver
adrian.klaver@aklaver.com
On 27 April 2016 at 23:43, Alvaro Aguayo Garcia-Rada <
aaguayo@opensysperu.com> wrote:
Based on my experience, I can say BDR does not performs pre-DDL checks.
For example, if you try to CREATE TABLE with the name of an existing table,
BDR will acquire lock anyway, and then will fail when executing the DDL
statement on the first node, because the table already exists.
Correct, and it has to because otherwise it'd face a race condition where
the table might be created between when it checked and when it tries to
create it.
In your case, it's the same: BDR does not checks(nor needs to) if the DDL
statement is or not required, as that's a dba dutty. Then, BDR executes the
statement(ane acquires locks), and fails because it would require a full
table rewrite, which, at the time, is not supported by BDR.
Yeah. This is more of a "we never thought anyone would want to do that and
didn't much care" problem. In this case we could lock the table and then
inspect it. In fact we really should be locking it to prevent races, but we
rely on the global DDL lock mechanism for that right now. (That's not what
it's for, just a side-effect that's kind of handy).
Applications *must* be adapted to run on BDR. You can't just point it at a
BDR-enabled DB and go "we'll be right". DDL is one thing, but multimaster
async replication conflicts are rather more significant concerns. Also
handling of the currently somewhat quirky global sequence support's habit
of ERRORing if you go too fast, trying to keep your transaction sizes down,
and not trusting row locking for mutual exclusion between nodes. You can't
use LISTEN/NOTIFY between nodes either, or advisory locking, or
pg_largeobject ... yeah. Apps require audit and usually require changes.
Changing an expected error code will be the least of your worries.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
So if I wanted to extend a column from 100 characters to 255 characters is
this permitted? The fact that I'm not making a change and the BDR kicked me
out makes me skeptical.
On Wed, Apr 27, 2016 at 11:56 AM, Craig Ringer <craig@2ndquadrant.com>
wrote:
Show quoted text
On 27 April 2016 at 23:43, Alvaro Aguayo Garcia-Rada <
aaguayo@opensysperu.com> wrote:Based on my experience, I can say BDR does not performs pre-DDL checks.
For example, if you try to CREATE TABLE with the name of an existing table,
BDR will acquire lock anyway, and then will fail when executing the DDL
statement on the first node, because the table already exists.Correct, and it has to because otherwise it'd face a race condition where
the table might be created between when it checked and when it tries to
create it.In your case, it's the same: BDR does not checks(nor needs to) if the DDL
statement is or not required, as that's a dba dutty. Then, BDR executes the
statement(ane acquires locks), and fails because it would require a full
table rewrite, which, at the time, is not supported by BDR.Yeah. This is more of a "we never thought anyone would want to do that and
didn't much care" problem. In this case we could lock the table and then
inspect it. In fact we really should be locking it to prevent races, but we
rely on the global DDL lock mechanism for that right now. (That's not what
it's for, just a side-effect that's kind of handy).Applications *must* be adapted to run on BDR. You can't just point it at a
BDR-enabled DB and go "we'll be right". DDL is one thing, but multimaster
async replication conflicts are rather more significant concerns. Also
handling of the currently somewhat quirky global sequence support's habit
of ERRORing if you go too fast, trying to keep your transaction sizes down,
and not trusting row locking for mutual exclusion between nodes. You can't
use LISTEN/NOTIFY between nodes either, or advisory locking, or
pg_largeobject ... yeah. Apps require audit and usually require changes.
Changing an expected error code will be the least of your worries.--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
If you change the length of a character varying, it should work. I'm almost sure I have done that before on my BDR cluster.
It may work as long as it does not require a full table rewrite. I think, the length change for a character varying won't need a full table rewrite, as the length is only a limit, but the actual content is length-independent.
Also, even if it works for character varying, it may not work for other types, specially numeric types.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
Sent from my Sony Xperia™ smartphone
---- Will McCormick wrote ----
So if I wanted to extend a column from 100 characters to 255 characters is
this permitted? The fact that I'm not making a change and the BDR kicked me
out makes me skeptical.
On Wed, Apr 27, 2016 at 11:56 AM, Craig Ringer <craig@2ndquadrant.com>
wrote:
Show quoted text
On 27 April 2016 at 23:43, Alvaro Aguayo Garcia-Rada <
aaguayo@opensysperu.com> wrote:Based on my experience, I can say BDR does not performs pre-DDL checks.
For example, if you try to CREATE TABLE with the name of an existing table,
BDR will acquire lock anyway, and then will fail when executing the DDL
statement on the first node, because the table already exists.Correct, and it has to because otherwise it'd face a race condition where
the table might be created between when it checked and when it tries to
create it.In your case, it's the same: BDR does not checks(nor needs to) if the DDL
statement is or not required, as that's a dba dutty. Then, BDR executes the
statement(ane acquires locks), and fails because it would require a full
table rewrite, which, at the time, is not supported by BDR.Yeah. This is more of a "we never thought anyone would want to do that and
didn't much care" problem. In this case we could lock the table and then
inspect it. In fact we really should be locking it to prevent races, but we
rely on the global DDL lock mechanism for that right now. (That's not what
it's for, just a side-effect that's kind of handy).Applications *must* be adapted to run on BDR. You can't just point it at a
BDR-enabled DB and go "we'll be right". DDL is one thing, but multimaster
async replication conflicts are rather more significant concerns. Also
handling of the currently somewhat quirky global sequence support's habit
of ERRORing if you go too fast, trying to keep your transaction sizes down,
and not trusting row locking for mutual exclusion between nodes. You can't
use LISTEN/NOTIFY between nodes either, or advisory locking, or
pg_largeobject ... yeah. Apps require audit and usually require changes.
Changing an expected error code will be the least of your worries.--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On 28 April 2016 at 02:47, Will McCormick <wmccormick@gmail.com> wrote:
So if I wanted to extend a column from 100 characters to 255 characters is
this permitted? The fact that I'm not making a change and the BDR kicked me
out makes me skeptical.
Off the top of my head I'm not sure and would need to test. There's no
specific logic in there for detecting such changes and permitting them, so
I suspect not.
If you're changing types in BDR you're expected to do it the long way. Add
a new col, update to copy the data, drop the old col and rename the new
col. Yes, that's ugly. We'd like to change it at some point. If you find
this particular problem annoying enough it'd be helpful if you could send a
patch for bdr_commandfilter.c to detect and permit changes that only affect
a column's typmod.
In the specific case of varchar columns, personally I recommend just using
'text' and adding a CHECK constraint on length. That's what I do most
places, not just BDR.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services