Confluence has been updated to version 6.15.9

Page tree
Skip to end of metadata
Go to start of metadata

Problem

You need to import data into a textarea column, and the database is a postgresql (version 9.4 or higher)

You are working with the table grid editor to collect data around how IP routing should be modified.  The table grid is a very simple one detailing out

  • type of operation
  • sourceip
  • destip



Solution

The grid configuration is as follows

############################################
# General grid properties section
#----------------------------------------
gd.ds = jira
gd.tablename = Testtablegrid
gd.allowAddRows = true
gd.delete.allowed = true
gd.cleanupTable = true
gd.tracking = true
gd.initFromClone=true

gd.columns = ChangeType, SourceIP, DestIP

############################################
# Column section for the ChangeType column
#----------------------------------------

col.ChangeType = Type of Change
col.ChangeType.type = list
col.ChangeType.list.size = 3
col.ChangeType.name1 = Add
col.ChangeType.name2 = Delete
col.ChangeType.name3 = Modification
col.ChangeType.required=true
col.ChangeType.tooltip = Indicate if this is a new addition or deletion of an old request or modification to an existing rule


############################################
# Column section for the SourceIP column
#----------------------------------------

col.SourceIP = Source Host FQDN/IP(s)
col.SourceIP.type=textarea
col.SourceIP.rows=100
col.SourceIP.cols=100
col.SourceIP.tooltip = Include the correct source IP address and hostname


############################################
# Column section for the DestIP column
#----------------------------------------

col.DestIP = Destination HOST FQDN/IP(s)
col.DestIP.type=textarea
col.DestIP.rows=100
col.DestIP.cols=100
col.DestIP.tooltip = Include the correct Destination IP address and hostname


When accessing the data in psql (or any other sql client), you find out that the data is not what you expect

Background information

Textarea columns are created as large objects allowing to store huge amount of data (the exact size depends a bit on the database - in case of postgresql, you can store up to 1 GB).
In postgresql, large objects are stored in a separate table, pg_largeobject, and the number is the id of that large object.  More information on this subject can be found in the postgresql documentation: 
https://www.postgresql.org/docs/current/static/largeobjects.html


Storing data

Assume that you plan to migrate the table grid tables from mysql to postgresql, you need to make sure that the content of the textarea attributes are stored in the pg_largeobject table (and not in the grid table itself).  If you would store the text information directly in the table you bump into mystical error messages such as


The proper way of storing this data is by using the lo_  functions as documented on 
https://www.postgresql.org/docs/current/static/lo-funcs.html


To store data into the example table grid, you would do compose following insert query

insert into testtablegrid_c10109 ( id, issueid,modified, sourceip, destip) 
values (6, 10000, 1,  lo_from_bytea(0,'1.1.1.1'),  lo_from_bytea(0,'2.2.2.2'));

 

The lo_from_bytea function will do all the hard work

  • Create a large object
  • write the content of the large object in the pg_largeobject table
  • Return the id of the large object

Reading data

Of course, when you write data, you would also like to read data (else it is not very useful, is it).  Here you need to use the lo_get function to retrieve the content of the large object

select lo_get(cast(sourceip as bigint)) from testtablegrid_c10109;


This results in a hexadecimal notation (tongue)

So, you need to do some extra conversion using the convert_from function


select convert_from(lo_get(cast(sourceip as bigint)),'utf8') from testtablegrid_c10109;


Qué?

In case all this stuff looks a bit complicated, you are right, it is.  
Just let us know and we'll try to help. Check our support options here.