Re: ALTER TEXT field to VARCHAR(1024)

Merlin Moncure-2 wrote
> On Mon, Sep 22, 2014 at 10:40 AM, John McKown
> <

> john.archie.mckown@

> > wrote:
>> On Mon, Sep 22, 2014 at 10:31 AM, Merlin Moncure <

> mmoncure@

> > wrote:
>>> I'll pile on here: in almost 20 years of professional database
>>> development I've never had an actual problem that was solved by
>>> introducing or shortening a length constraint to text columns except
>>> in cases where overlong strings violate the data model (like a two
>>> character state code for example). It's a database equivalent of "C
>>> programmer's disease". Input checks from untrusted actors should
>>> happen in the application.
>>>
>>> merlin
>>>
>>
>> I do not have your experience level with data bases, but if I may, I
>> will make an addition. Input checks should also happen in the RDBMS
>> server. I have learned you cannot trust end users _or_ programmers.
>> Most are good and conscientious. But there are a few who just aren't.
>> And those few seem to be very prolific in making _subtle_ errors. Had
>> one person who was really good at replacing every p with a [ and P
>> with {
>
> Sure. The point is distinguishing things which are *demonstrably*
> false (like a US VIN must be exactly 17 chars) from those that are
> based assumption (such as a cityname must be <= 50 characters). The
> former should be validated in the schema and the latter should not be.
> If you're paranoid about the user submitting 100mb strings for
> "username" and don't trust the application to deal with that, I'd
> maybe consider making a domain 'safetext' which checks length on the
> order of a few thousand bytes and using that instead of 'text' and use
> it everywhere. This will prevent the dba from outsmarting the
> datamodel which is a *much* bigger problem in practice than the one
> length checks attempt to solve.
>
> Domains have certain disadvantages (like no array type) -- be advised.
>
> merlin

These responses all seem beside the point. The OP isn't concerned that
too-long data is making it into the database but rather that an unadorned
text type is functionally a CLOB which the application he is using is
treating like a document instead of a smallish text field that would be
treated like any other value. It's like the difference between choosing
input/text or textarea in HTML. Now, some tools distinguish between "text"
and "varchar" only and the length piece is irrelevant; but whether that
applies here I have no idea.

It might be easier to simply create a view over the table, using the desired
type (truncating the actual value if needed), and feed that view to the
reporting engine.

In the end the two questions are:
1) does adding a length restriction cause a table rewrite?
2) what level of locking occurs while the length check is resolving?

I don't confidently know the answers to those two questions.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/ALTER-TEXT-field-to-VARCHAR-1024-tp5819608p5819939.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.