Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
Guillaume Smet ha scritto: On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut pete...@gmx.net wrote: The question is how you want to implement this in a data type independent fashion. You can't assume that increasing the typmod is a noop for all data types. Sure. See my previous answer on -hackers (I don't think this discussion belong to -bugs) and especially the discussion in the archives about Jonas' patch. I recently had a similar problem when I added some domains to the application. ALTER TABLE ... TYPE varchar_dom was leading to a full table rewrite even though the underlying type definition were exactly the same (i.e. varchar(64)). I can live with it, but I suppose this fix might be related to the varlen one. Cheers -- Matteo Beccati OpenX - http://www.openx.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
On Thu, Mar 5, 2009 at 10:47 AM, Matteo Beccati p...@beccati.com wrote: Guillaume Smet ha scritto: On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut pete...@gmx.net wrote: The question is how you want to implement this in a data type independent fashion. You can't assume that increasing the typmod is a noop for all data types. Sure. See my previous answer on -hackers (I don't think this discussion belong to -bugs) and especially the discussion in the archives about Jonas' patch. I recently had a similar problem when I added some domains to the application. ALTER TABLE ... TYPE varchar_dom was leading to a full table rewrite even though the underlying type definition were exactly the same (i.e. varchar(64)). I can live with it, but I suppose this fix might be related to the varlen one. ALTER TABLE ... TYPE does cause a table rewrite even if new_type = old_type, and that is actually useful... for example when you add a fillfactor to an existing table that fillfactor will not affect the existing data until you rewrite the table and a convenient way is exactly using ALTER TABLE ... TYPE. now, back to the problem... is not easier to define a column as TEXT and to put a check to constraint the length? if you wanna change the constraint that will be almost free -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
Jaime Casanova jcasa...@systemguards.com.ec wrote: ALTER TABLE ... TYPE does cause a table rewrite even if new_type = old_type, and that is actually useful... for example when you add a fillfactor to an existing table that fillfactor will not affect the existing data until you rewrite the table and a convenient way is exactly using ALTER TABLE ... TYPE. I find that to be exactly as useful as it would be to have a table rewrite if I added a new null-capable column, and somewhat less useful than it would be have a table rewrite on dropping a column. Maintaining the function of this clever trick should not be the basis of imposing a burden on relatively common maintenance operations. now, back to the problem... is not easier to define a column as TEXT and to put a check to constraint the length? if you wanna change the constraint that will be almost free Thanks for the interesting suggestion. I'm not sure I'd want to go there for various reasons; but even if I wanted to go that route, how would I modify that constraint without causing the whole table to be scanned for compliance? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
Jaime Casanova jcasa...@systemguards.com.ec wrote: the table will be scanned but not rewritten That can still be a very long time on some tables. And there would still be the issue of dodging all the brickbats thrown at me by developers whose tools use the system tables to limit the number of characters a user is allowed to type into an application. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
On Thu, Mar 5, 2009 at 2:46 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: now, back to the problem... is not easier to define a column as TEXT and to put a check to constraint the length? if you wanna change the constraint that will be almost free Thanks for the interesting suggestion. I'm not sure I'd want to go there for various reasons; but even if I wanted to go that route, how would I modify that constraint without causing the whole table to be scanned for compliance? the table will be scanned but not rewritten -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
On Thu, Mar 5, 2009 at 3:27 PM, Xuân Baldauf xuan--2009.03--submitbug--support--postgresql@baldauf.org wrote: Well, while this behaviour is well-known for PostgreSQL, this is actually an abuse of syntax. If there are legitimate requirements for rewriting a table, then there should be explicit syntax for such a feature, like ALTER TABLE ... REWRITE. Rewriting a table in case of ALTER TABLE ... TYPE is, by the semantics of that statement, just a side-effect, which may or may not happen, depending on how optimized the DBMS is. It is bad design to avoid optimization just because an unnecessary side-effect would be optimized away. note that this is my opinion and not represent the PGDG (Postgresql Global Development Group) opinion now, back to the problem... is not easier to define a column as TEXT and to put a check to constraint the length? if you wanna change the constraint that will be almost free No. Is it possible to change the column type from VARCHAR(5) to TEXT without a table-rewrite penalty? the idea is to make that change once (and to create new tables just with TEXT) and then you can make ALTER TABLE ... ADD CHECK (length(column) = a_value) as many times as you want without the need for a table rewrite -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
Jaime Casanova wrote: On Thu, Mar 5, 2009 at 10:47 AM, Matteo Beccati p...@beccati.com wrote: Guillaume Smet ha scritto: On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut pete...@gmx.net wrote: The question is how you want to implement this in a data type independent fashion. You can't assume that increasing the typmod is a noop for all data types. Sure. See my previous answer on -hackers (I don't think this discussion belong to -bugs) and especially the discussion in the archives about Jonas' patch. I recently had a similar problem when I added some domains to the application. ALTER TABLE ... TYPE varchar_dom was leading to a full table rewrite even though the underlying type definition were exactly the same (i.e. varchar(64)). I can live with it, but I suppose this fix might be related to the varlen one. ALTER TABLE ... TYPE does cause a table rewrite even if new_type = old_type, and that is actually useful... for example when you add a fillfactor to an existing table that fillfactor will not affect the existing data until you rewrite the table and a convenient way is exactly using ALTER TABLE ... TYPE. Well, while this behaviour is well-known for PostgreSQL, this is actually an abuse of syntax. If there are legitimate requirements for rewriting a table, then there should be explicit syntax for such a feature, like ALTER TABLE ... REWRITE. Rewriting a table in case of ALTER TABLE ... TYPE is, by the semantics of that statement, just a side-effect, which may or may not happen, depending on how optimized the DBMS is. It is bad design to avoid optimization just because an unnecessary side-effect would be optimized away. now, back to the problem... is not easier to define a column as TEXT and to put a check to constraint the length? if you wanna change the constraint that will be almost free No. Is it possible to change the column type from VARCHAR(5) to TEXT without a table-rewrite penalty? ciao, Xuân.
