Storing JSONB in Postgres

hi Graham

while I understand what is going on … what a dreadful hack that is.

basically, if you pick a size of a char field — then anything that is that size is converted to another variable type during transfer to the database.

so, as I understand, you could tell omnis that a variable of length
— 12345 is json, and
— 12346 is a network data dtype (holds an ip address)
— 12347 is a textsearch tsVector
— 12348 is a postgres array
— 12348 is a UUID
— etc.

and somehow. magically .. the dam does transformations for those to/from character fields.

is that the case of what I’m hearing???

it wold be far better if these were first class data types (in other words, there was an explicit data type in a schema for json) … and even if omnis did nothing special with it, then the assistance provided by the debugger, etc .. would state what it is.

I suppose, I’m on a soapbox here… but I appreciate that we can define data types and I might look into those once we are at studio 8. but I’d rather be straightforward.

we define all our text data types to be 10000000 these days (since it doesn’t hurt postgres at all) …. and the implication is that one would not want to use 1000000 for the definition of the length.

so, you are left with 65534 for a json data type as the maximum size — and that is just asking for truncation to occur, especially if data fields for json could hold a lot of data (We have one case where it holds the base64 representation of the library binary for our update process, and if we go multi library, then I can see this json field holding some 100 megabytes of data — which blows the custom object out the window due to size.).

what do tech support have to say about unlimited char sizes co-existing with unlimited json sizes?

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug@artsman.com
www.artsman.com
Phone (403) 650-1978

see you at the third annual users conference
tickets.proctors.org/TheatreManager/95/online?performance=29086 <tickets.proctors.org/TheatreManager/95/online?performance=29086>

> On Feb 9, 2018, at 2:32 AM, Graham Stevens <graham.stevens@gmail.com> wrote:
>
> Hi Scotte,
>
> We successfully use quite a few JSONB columns in our app. You don’t say which version of Studio you are on but in 8.1 you can use:
>
> Do PGSession.$new() Returns oSession
> Do oSession.$clearcustomtypes()
> Do oSession.$addcustomtype(50000,’JSONB’) ;; the 50000 can be any arbitrary number <= 65534 or exactly 100000000* >
> The DAM will then do the conversion for you whenever it sees a Character column with length 50000. Of course, you have to make sure none of your text columns are of length 50000.
>
> * I have registered a Fault ST/SC/079 with support, Character columns of >= 65535 get mapped to 10000000. They have told me it’s fixed for 8.2
>
> Because of the above fault and the fact we have some huge JSON arrays, we use 10000000 as our custom type and have reduced all our schema text columns to 65500.
>
> HTH,
> Graham

_____________________________________________________________
Manage your list subscriptions at lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en@lists.omnis-dev.com