resource_title breaks the 40 character limit

[Bill Sirinek]

I have a module that manages various bios settings on HP proliant servers (basically hacked up the conrep module from the forge) Anyhow, the resource names (resource_title) can get fairly long and break the 40 character column width in the database, as shown with the error below:

2017-04-22 13:30:25.912 EDT [db:pe-puppetdb,sess:58fb8caf.8cbb,pid:36027,vtid:29/23277,tid:396758] STATEMENT:  INSERT INTO resource_events ( new_value, corrective_change, property, file, report_id, old_value, containing_class, certname_id, line, resource_type, status, resource_title, timestamp, containment_path, message ) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15 ) RETURNING *

2017-04-22 13:30:25.978 EDT [db:pe-puppetdb,sess:58fb8caf.8cbb,pid:36027,vtid:29/23281,tid:396760] ERROR:  value too long for type character varying(40)

Is this something I can change without having to recreate the entire database or having puppet somehow change it back? I am not a DBA, so SQL isn't my strongest skill. 

I am running PE 2017.1.1, but this error has been going on a long time (since PE3.7 when I introduced the bios module)

Thanks

Bill

[Wyatt Alt]

On 04/22/2017 10:35 AM, Bill Sirinek wrote:
>
> 2017-04-22 13:30:25.912 EDT
> [db:pe-puppetdb,sess:58fb8caf.8cbb,pid:36027,vtid:29/23277,tid:396758]
> STATEMENT: INSERT INTO resource_events ( new_value,
> corrective_change, property, file, report_id, old_value,
> containing_class, certname_id, line, resource_type, status,
> resource_title, timestamp, containment_path, message ) VALUES ( $1,
> $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15 ) RETURNING *
> 2017-04-22 13:30:25.978 EDT
> [db:pe-puppetdb,sess:58fb8caf.8cbb,pid:36027,vtid:29/23281,tid:396760]
> ERROR: value too long for type character varying(40)

This is probably being caused by the property names rather than resource
titles. The only varchar(40) columns in that table are the property name
and the event status, and I'm assuming you're not doing anything custom
with the statuses (which typically come from Puppet).

There is no supported workaround for this, but I put up a PR here
https://github.com/puppetlabs/puppetdb/pull/2268 to resolve it.

An unsupported workaround would be to shut down PuppetDB, connect to
postgres via psql and do this:

\c pe-puppetdb
alter table resource_events alter column property type text;

This could take anywhere from seconds to 30+ minutes depending on how
much data you have, so if that's a concern you can get in touch with
support and coordinate with them. Doing this kind of thing is usually a
really bad idea, but in this case it won't hurt because a future
migration to change the old varchar column to text will simply be a noop.

Wyatt

[Bill Sirinek]

Thanks Wyatt, that worked! The alter table command returned immediately. I only have about 170k reports in my DB since I migrated to 2017.1.1 friday night. I run puppet hourly on about 8000 nodes.

A side effect of this change is that my PuppetDB command queue depth went from just over 100 to 0, and has stayed there for a couple hours now, maybe occasionally poking up to 2-3 briefly. At least I think it was related to this change. :)

Thanks

Bill

[Wyatt Alt]

On 04/22/2017 11:54 PM, Bill Sirinek wrote:
>
> A side effect of this change is that my PuppetDB command queue depth
> went from just over 100 to 0, and has stayed there for a couple hours
> now, maybe occasionally poking up to 2-3 briefly. At least I think it
> was related to this change. :)

You're probably right about that. Since you run Puppet on a regular
interval, I expect you'd have had a relatively constant number of
reports in retries at any given time, which would keep your queue
nonempty. Anyway, glad to help and that it's resolved.

Wyatt