Did you run any other operations after the add column? Perhaps creating an index upon it (which would take a write lock on the table by default)?ġ Some replication/clustering/mirroring arrangements would allow you to update a mirror (pausing updates to it during the change and replaying them after), switch over to using that copy as the live one, and so on until each copy is updated, so the downtime is limited to the time is takes to replay the changes made during the DDL operation. This blog entry has a summary which suggests adding a column can be an online operation if the column is nullable and does not have a default value or unique constraint, though that implies that the statement you state should have been run without locks (as IIRC postgres defaults columns to being NULLable unless you explicitly state otherwise). The documentation seems pretty good at listing what locks are likely to be held by DDL operations. Some operations only keep a write lock, so your application can keep serving requests that only read the affected objects.
#REDSHIFT ALTER TABLE HANGS OFFLINE#
Version: PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bitĭDL operations usually lock the object they are acting upon, so should not be performed outside planned maintenance windows (when your users are expecting disruption or the system to be completely offline for up to a planned amount of time) - there is nothing you can do about this easily 1.
What else should I provide for further analysis? Should I disable CONSTRAINTS, TRIGGERS, or something else? (rut COLLATE pg_catalog."default", activo) ĮXECUTE PROCEDURE crea_default_clientegrupo() GRANT SELECT ON TABLE cliente TO readonly GRANT SELECT ON TABLE cliente TO waypointreports GRANT SELECT ON TABLE cliente TO waypointmachines GRANT SELECT ON TABLE cliente TO waypointagenda GRANT SELECT ON TABLE cliente TO waypointadminuser GRANT SELECT, INSERT ON TABLE cliente TO waypointsalesforce GRANT ALL ON TABLE cliente TO waypointsoporte GRANT SELECT ON TABLE cliente TO waypointpphppublic GRANT SELECT ON TABLE cliente TO waypointphp GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE cliente TO waypointtomcat GRANT ALL ON TABLE cliente TO waypointtx REFERENCES cliente_familia (id) MATCH SIMPLEĬONSTRAINT pk_pais FOREIGN KEY (codigo_pais) Mqtt_enable boolean NOT NULL DEFAULT false,ĬONSTRAINT fk_cliente_familiaid FOREIGN KEY (familia) Plantilla character varying(15) DEFAULT 'WAYPOINT'::character varying,Ĭreacion timestamp without time zone DEFAULT now(), Razon_social character varying(150) NOT NULL,ĭireccion character varying(200) NOT NULL,Ĭodigo_pais character varying(3) NOT NULL, We actually had to kill the process to allow the system resume normal operations. * Login into our system requires a select from that very same table, so no one could login during the alter table. Offending SQL: ALTER TABLE cliente ADD COLUMN topicos character varying(20) Our production environment just froze* this morning for a while when altering a table, adding a column actually.
0 kommentar(er)
