Too short field

[Karl Martin Skoldebrand]

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.

Re: Too short field

[Thomas Kellerer]

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.

RE: Too short field

[Karl Martin Skoldebrand]

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 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.

Re: Too short field

[Karsten Hilbert]

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

Re: Too short field

[Karsten Hilbert]

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

RE: Too short field

[Karl Martin Skoldebrand]

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 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.

Re: Too short field

[Gavin Flower]

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