External_avatar_url field is too short

(Sam Stickland) #1

TLDR: Is it safe for me to change external_avatar_url to a text datatype in the DB?

I’m getting this error message from time to time:

ActiveRecord::StatementInvalid (PG::StringDataRightTruncation: ERROR: value too long for type character varying(255) : UPDATE "single_sign_on_records" SET "external_avatar_url" = $1

I’m using S3 with paperclip on my site to store avatars:

  has_attached_file :profile_picture,
    storage: :s3,
    path: "#{Rails.env}/users/profile_pictures/:id_:style_:filename_:hash",
    hash_secret: 'xxxxxxxxx',
    styles: {
      thumb: "100x100#",
      small: "150x150>",
      medium: "200x200"
    default_url: "missing_profile_picture.png"

(I’ve replaced the hash secret obviously).

It turns out this is generating surprisingly long URLs.

User.all.collect { |u| u.profile_picture.url.length if u.profile_picture.exists? }.uniq
=> [nil, 196, 197, 195, 147, 260, 259]

I’d rather not have to regenerate all my S3 URLs.

(Régis Hanol) #2

I would happily merge a pull request that increases the size of the VARCHAR to something like 1024 or 2048 instead of using the TEXT type :wink:

(Sam Stickland) #3

Oh that’s easy. I didn’t realise postgresql supported varchars bigger than
255 (MySQL doesn’t IIRC).

I’ll get right on it :slight_smile:


(Rafael dos Santos Silva) #4

Why use varchar (with 1024 or more length) over text?

Since they’re stored to be the same, but varchar does waste an extra cycle to verify length using text is way more idiomatic for PostgreSQL.

Also, updating the length is slow :turtle: , and locks the table :lock: .

(Andrés Jacobo) #5

I’m a year late, but I just ran into the exact same problem for our discourse hosted deployment. I’m guessing the 255 char limit was never upped?

(Jeff Atwood) #6

We haven’t seen any other requests for this to date.