Thread: Too short field
Hi,
We solved the problem of yesterday where I was looking at sequences. It eventually turned that sequence was irrelevant (at least in the PostgreSQL sense) to the problem.
Now, we have a bug in another application that prevents an automatic tool to enter certain users in the database. The organisational field is varchar(60) while the actual Organisation “abbreviation” may be as long as 70 characters (don’t ask why).
What happens to data if I simple redefine the table field as varchar(80) (or something, at least 70+). Does “everything” break database side or can I just go on running the app as is.
Do we need to restart databases or something else that requires an interrupted service?
Best regards,
Martin S
============================================================================================================================
Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html externally http://tim.techmahindra.com/tim/disclaimer.html internally within TechMahindra.
============================================================================================================================
Karl Martin Skoldebrand schrieb am 03.07.2019 um 13:30: > Now, we have a bug in another application that prevents an automatic > tool to enter certain users in the database. The organisational field > is varchar(60) while the actual Organisation “abbreviation” may be as > long as 70 characters (don’t ask why). > > What happens to data if I simple redefine the table field as > varchar(80) (or something, at least 70+). Does “everything” break > database side or can I just go on running the app as is. Nothing will break on the database size. > Do we need to restart databases or something else that requires an > interrupted service? No, you just run ALTER the_table ALTER COLUMN organisational TYPE varchar(80); It requires an exclusive lock on the table, but the actual operation will finish in a few milliseconds because the table is not physically changed (as the limit is only increased). Getting the exlusive lock might take a while if the table is actively used, so the ALTER statement itself might look as it would take a while, but once the lock could be obtained the change is very fast. There is no real need to stop anything if you can anticipate that there will be a few (milli)seconds where the ALTER statement succeeds in obtainin (and releasing) the lock.
Thanks. That is what I thought - good to have a confirmation. Best regards, Martin S ============================================================================================================================ Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindrapolicy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html>externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html>internally within TechMahindra. ============================================================================================================================
On Wed, Jul 03, 2019 at 01:56:03PM +0200, Thomas Kellerer wrote: > Karl Martin Skoldebrand schrieb am 03.07.2019 um 13:30: > > Now, we have a bug in another application that prevents an automatic > > tool to enter certain users in the database. The organisational field > > is varchar(60) while the actual Organisation “abbreviation” may be as > > long as 70 characters (don’t ask why). > > > > What happens to data if I simple redefine the table field as > > varchar(80) (or something, at least 70+). Does “everything” break > > database side or can I just go on running the app as is. > > Nothing will break on the database size. We don't know. There may be functions in triggers or for direct use which rely on the assumption that it be 60 chars max. PostgreSQL itself won't care, that much holds. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
On Wed, Jul 03, 2019 at 02:10:55PM +0200, Karsten Hilbert wrote: > > Karl Martin Skoldebrand schrieb am 03.07.2019 um 13:30: > > > Now, we have a bug in another application that prevents an automatic > > > tool to enter certain users in the database. The organisational field > > > is varchar(60) while the actual Organisation “abbreviation” may be as > > > long as 70 characters (don’t ask why). > > > > > > What happens to data if I simple redefine the table field as > > > varchar(80) (or something, at least 70+). Does “everything” break > > > database side or can I just go on running the app as is. > > > > Nothing will break on the database size. > > We don't know. Sorry, I didn't read properly: >>> What happens to >>data<< if I simple redefine the table field Nothing should happen to the data. But: > There may be functions in triggers or for direct use which > rely on the assumption that it be 60 chars max. :) Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Valid point. I've added that to the report. Best regards, Martin Skjoldebrand ============================================================================================================================ Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindrapolicy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html>externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html>internally within TechMahindra. ============================================================================================================================
On 03/07/2019 23:30, Karl Martin Skoldebrand wrote: > > Hi, > > We solved the problem of yesterday where I was looking at sequences. > It eventually turned that sequence was irrelevant (at least in the > PostgreSQL sense) to the problem. > > Now, we have a bug in another application that prevents an automatic > tool to enter certain users in the database. The organisational field > is varchar(60) while the actual Organisation “abbreviation” may be as > long as 70 characters (don’t ask why). > > What happens to data if I simple redefine the table field as > varchar(80) (or something, at least 70+). Does “everything” break > database side or can I just go on running the app as is. > > Do we need to restart databases or something else that requires an > interrupted service? > > Best regards, > > Martin S > > ============================================================================================================================ > > Disclaimer: This message and the information contained herein is > proprietary and confidential and subject to the Tech Mahindra policy > statement, you may review the policy at > http://www.techmahindra.com/Disclaimer.html externally > http://tim.techmahindra.com/tim/disclaimer.html internally within > TechMahindra. > > ============================================================================================================================ > Is there any reason to put a limit of the number of characters in the field in the database? If not, consider using, the 'text' type. Cheers, Gavin