How to update text representations of Java maps in PostgreSQL database?

Question

We are running Atlassian Bitbucket Server for our developers and we have a backup/restore mechanism that copies the productive configuration onto a test environment. To make everything work well on the test environment, SQL scripts are used to adjust certain settings in the Bitbucket PostgreSQL database (e.g., the base URL, LDAP connection settings, etc.).

Some values in the Bitbucket database seem to be stored as text representations of java.util.Map objects, like the key_value field in this example of the Bitbucket look-and-feel settings (e.g., UI header color):

bitbucket=# select * from plugin_setting where key_name like 'look%';

         namespace         |          key_name           |              key_value              |  id  
---------------------------+-----------------------------+-------------------------------------+------
 bitbucket.global.settings | look-and-feel:COLOR_MAP     | #java.util.Map                     +| 7988
                           |                             | customThemePrimaryColour\x0C#abcdef+| 
                           |                             | customThemeHeaderColour\x0C#9933ff  |

In order to better distinguish our test and productive environments, I would like to update the key_value with a whole new map of values, but whatever I tried so far, either had no effect (i.e., the default settings got applied) or even ended up corrupting the database and Bitbucket failed to restart. For example, I tried this, but it simply has no effect:

UPDATE plugin_setting SET key_value='#java.util.Map\ncustomThemeHeaderColour\x0C#abcdef'
                      WHERE key_name='look-and-feel:COLOR_MAP';

In other words, the UPDATE successfully writes the key_value, but somehow without any line breaks (and the Bitbucket header color remains unchanged after a restart). I also tried to explicitly add a + before the \n line break, but that also had no effect.

It seems that I'm not correctly escaping certain characters or something...

Thanks. I updated my question. The UPDATE works fine, but the value seems to be wrong, or wrongly encoded, so Bitbucket seems to just ignore it... — dokaspar, Oct 16 '20 at 12:09
`\n` has no meaning in SQL. Maybe you need `'#java.util.Map'||chr(10)||'...'||chr(10)||'...'` — , Oct 16 '20 at 12:12

score 0 · Answer 1 · answered Oct 04 '21 at 08:04

In the meantime, I found the solution. In essence, I just forgot to add the escape String marker E:

-- set the Bitbucket header color to light purple via properties of the look-and-feel plugin
UPDATE plugin_setting  SET key_value = E'#java.util.Map\ncustomThemeHeaderColour\x0C#b941ff'
                       WHERE key_name = 'look-and-feel:COLOR_MAP';

See also: What's the "E" before a Postgres string?

How to update text representations of Java maps in PostgreSQL database?

1 Answers1