Search Postgresql Archives

Re: BDR Alter table failing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx> 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 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@xxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux