invalid input syntax for type json when registering an entity

[fluca1978]

Using PostgreSQL 16, version HEAD.
I've created the current table as follows:

testdb=> \d foo_current
                               Table "public.foo_current"
 Column  |          Type          | Collation | Nullable |           Default            
---------+------------------------+-----------+----------+------------------------------
 p       | integer                |           | not null | generated always as identity
 t       | text                   |           |          | 
 bt_info | vrsn.bitemporal_record |           |          | 

but then when I try to register it and create the view:

testdb=> SELECT vrsn.admin__bitemporal_entity_register(
    p_conf := '{
"current_table": {"schema_name": "public", "table_name": "foo_current"},
 "historice_entity": "always",
        "enable_history_attributes": true,
        "main_fields_list": "p,t",
        "cached_fields_list": "cached_attr_column",
        "mitigate_conflicts": true,
        "ignore_unchanged_values": true,
        "enable_attribute_to_fields_replacement": false
    }'::jsonb,
    p_execute := TRUE 
);
ERROR:  invalid input syntax for type json
LINE 1: v_conf['operation_type']='create'
                                 ^
DETAIL:  Token "create" is invalid.
QUERY:  v_conf['operation_type']='create'
CONTEXT:  JSON data, line 1: create
PL/pgSQL function vrsn.admin__bitemporal_entity_register(jsonb,boolean) line 30 at assignment

Am I doing something wrong here?

[DomenicoL]

sorry for delay, something went wrong about notificaiton

I fix the code:

if you want to easly fix substitute line 30 with
v_conf['operation_type']=to_jsonb('create'::text);

[fluca1978]

I tried again with HEAD at b005bd7ac6bff4b35b217c2450795498610d7fdf but I got the following:

testdb=> SELECT vrsn.admin__bitemporal_entity_register(
    p_conf := '{
"current_table": {"schema_name": "public", "table_name": "bar"},
 "historice_entity": "always",
        "enable_history_attributes": true,
        "main_fields_list": "p,t",
        "cached_fields_list": "cached_attr_column",
        "mitigate_conflicts": true,
        "ignore_unchanged_values": true,
        "enable_attribute_to_fields_replacement": false
    }'::jsonb,
    p_execute := TRUE 
);
ERROR:  Missing information: You must provide at least "current_view.table_name" or "current_table.table_name".
CONTEXT:  PL/pgSQL function vrsn.__bitemporal_entity__complete_conf_param(jsonb) line 86 at RAISE
PL/pgSQL function vrsn.__bitemporal_entity__build_ddl(jsonb) line 45 at assignment
PL/pgSQL function vrsn.admin__bitemporal_entity_register(jsonb,boolean) line 32 at assignment

I'm using PostgreSQL 16, the extensions in the database are:

testdb=> \dx
                                   List of installed extensions
    Name     | Version |   Schema   |                         Description                          
-------------+---------+------------+--------------------------------------------------------------
 bool_plperl | 1.0     | public     | transform between bool and plperl
 btree_gin   | 1.3     | extensions | support for indexing common datatypes in GIN
 btree_gist  | 1.7     | extensions | support for indexing common datatypes in GiST
 hstore      | 1.8     | extensions | data type for storing sets of (key, value) pairs
 ltree       | 1.2     | extensions | data type for hierarchical tree-like structures
 pljava      | 1.6.8   | sqlj       | PL/Java procedural language (https://tada.github.io/pljava/)
 plperl      | 1.0     | pg_catalog | PL/Perl procedural language
 plperlu     | 1.0     | pg_catalog | PL/PerlU untrusted procedural language
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 tablefunc   | 1.0     | extensions | functions that manipulate whole tables, including crosstab

What am I missing here?

[DomenicoL]

it should be "table_name": "bar**_current**"

[fluca1978]

it should be "table_name": "bar**_current**"

Sorry, wrong copy and paste, it was:

testdb=> SELECT vrsn.admin__bitemporal_entity_register(
    p_conf := '{
"current_table": {"schema_name": "public", "table_name": "foo_current"},
 "historice_entity": "always",
        "enable_history_attributes": true,
        "main_fields_list": "p,t",
        "cached_fields_list": "cached_attr_column",
        "mitigate_conflicts": true,
        "ignore_unchanged_values": true,
        "enable_attribute_to_fields_replacement": false
    }'::jsonb,
    p_execute := TRUE 
);
ERROR:  Missing information: You must provide at least "current_view.table_name" or "current_table.table_name".
CONTEXT:  PL/pgSQL function vrsn.__bitemporal_entity__complete_conf_param(jsonb) line 86 at RAISE
PL/pgSQL function vrsn.__bitemporal_entity__build_ddl(jsonb) line 45 at assignment
PL/pgSQL function vrsn.admin__bitemporal_entity_register(jsonb,boolean) line 32 at assignment

testdb=> \d foo
                           Table "public.foo"
 Column |  Type   | Collation | Nullable |           Default            
--------+---------+-----------+----------+------------------------------
 p      | integer |           | not null | generated always as identity
 t      | text    |           |          | 

``

[DomenicoL]

mmm sound strange it seems to miss bt_info, but anyway in this case the error should be differnt.

I create a table like your:
-- Table: public.foo_current

-- DROP TABLE IF EXISTS public.foo_current;

CREATE TABLE IF NOT EXISTS public.foo_current
(
bt_info vrsn.bitemporal_record NOT NULL,
p integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
t text COLLATE pg_catalog."default",
CONSTRAINT foo_current_pkey PRIMARY KEY (p)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.foo_current
OWNER to btmp;

And with this query I obtain the expected resut:
sELECT vrsn.admin__bitemporal_entity_register(
p_conf := '{
"current_table": {"schema_name": "public", "table_name": "foo_current"},
"historice_entity": "always",
"enable_history_attributes": true,
"main_fields_list": "p,t",
"cached_fields_list": "cached_attr_column",
"mitigate_conflicts": true,
"ignore_unchanged_values": true,
"enable_attribute_to_fields_replacement": false
}'::jsonb,
p_execute := TRUE
);

[fluca1978]

This is what I did, following your own example:

testdb=> DROP TABLE IF EXISTS public.foo_current;

CREATE TABLE IF NOT EXISTS public.foo_current
(
bt_info vrsn.bitemporal_record NOT NULL,
p integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
t text COLLATE pg_catalog."default",
CONSTRAINT foo_current_pkey PRIMARY KEY (p)
)

TABLESPACE pg_default;
DROP TABLE
CREATE TABLE
testdb=> sELECT vrsn.admin__bitemporal_entity_register(
p_conf := '{
"current_table": {"schema_name": "public", "table_name": "foo_current"},
"historice_entity": "always",
"enable_history_attributes": true,
"main_fields_list": "p,t",
"cached_fields_list": "cached_attr_column",
"mitigate_conflicts": true,
"ignore_unchanged_values": true,
"enable_attribute_to_fields_replacement": false
}'::jsonb,
p_execute := TRUE
);
ERROR:  Missing information: You must provide at least "current_view.table_name" or "current_table.table_name".
CONTEXT:  PL/pgSQL function vrsn.__bitemporal_entity__complete_conf_param(jsonb) line 86 at RAISE
PL/pgSQL function vrsn.__bitemporal_entity__build_ddl(jsonb) line 45 at assignment
PL/pgSQL function vrsn.admin__bitemporal_entity_register(jsonb,boolean) line 32 at assignment

The only thing that differs is this part:

ALTER TABLE IF EXISTS public.foo_current
OWNER to btmp;

since I don't have a btmp user and I'm the owner of the table. Could it be a permission problem?
Or is something wrong with my installation?

[DomenicoL]

Mmm yes I suppose something went wrong in installazione Btmp is the di where i installed for scratch the version published... But i can retry But the error is applicative... could you stare the tale insider vrsn schema?

[fluca1978]

But the error is applicative... could you stare the tale insider vrsn
schema?

Not sure what you are asking me to provide.

testdb=> \dT vrsn.bitemporal_record 
                                    List of data types
 Schema |          Name          |                      Description                       
--------+------------------------+--------------------------------------------------------
 vrsn   | vrsn.bitemporal_record | type to manage bitemporal information and audit record
(1 row)

[DomenicoL]

Inside vrsn schema there are some tables. Maybe the init phase went wrong and tables are misconfigured

[fluca1978]

I reinstalled right now with the following:

% psql -c 'drop schema common cascade;' testdb
%  psql -c 'drop schema vrsn cascade;' testdb
%  psql -c 'drop schema srvc cascade;' testdb


% psql -d testdb -f LOCAL_INSTALL_common.sql  
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
CREATE SCHEMA
CREATE TYPE
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE VIEW
SET

% psql -d testdb -f LOCAL_INSTALL_vrsn.sql    
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
CREATE SCHEMA
CREATE TYPE
COMMENT
CREATE DOMAIN
CREATE DOMAIN
CREATE DOMAIN
CREATE DOMAIN
CREATE DOMAIN
CREATE TYPE
CREATE FUNCTION
CREATE DOMAIN
CREATE TYPE
COMMENT
CREATE TYPE
CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
SET
CREATE TABLE
CREATE FUNCTION
CREATE FUNCTION
CREATE TABLE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE TABLE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE AGGREGATE
CREATE AGGREGATE
CREATE AGGREGATE
CREATE TABLE
CREATE TABLE
CREATE VIEW
COMMENT
CREATE VIEW
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE VIEW
CREATE TABLE
ALTER TABLE
CREATE TABLE
CREATE TABLE
COMMENT
CREATE TABLE
COMMENT
COMMENT
COMMENT
CREATE VIEW
CREATE TABLE
CREATE TABLE
CREATE VIEW
CREATE TABLE
CREATE TABLE
CREATE VIEW
CREATE TABLE
CREATE VIEW
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
ALTER TABLE
SET

% psql -d testdb -f srvc.sql              
SET
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
CREATE SCHEMA
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
SET
CREATE TABLE
CREATE VIEW
ALTER TABLE
SET

and then

% psql testdb 
psql (16.10)
Type "help" for help.

testdb=> drop table foo_current;
DROP TABLE
testdb=> create table foo_current( bt_info vrsn.bitemporal_record NOT NULL,
p integer NOT NULL GENERATED ALWAYS AS IDENTITY , t text, primary key(p) );
CREATE TABLE
testdb=> \d 
               List of relations
 Schema |       Name        |   Type   | Owner 
--------+-------------------+----------+-------
 public | bar               | table    | luca
 public | bar_p_seq         | sequence | luca
 public | c                 | table    | luca
 public | foo               | table    | luca
 public | foo_current       | table    | luca
 public | foo_current_p_seq | sequence | luca
 public | foo_p_seq         | sequence | luca
(7 rows)

testdb=> sELECT vrsn.admin__bitemporal_entity_register(
p_conf := '{
"current_table": {"schema_name": "public", "table_name": "foo_current"},
"historice_entity": "always",
"enable_history_attributes": true,
"main_fields_list": "p,t",
"cached_fields_list": "cached_attr_column",
"mitigate_conflicts": true,
"ignore_unchanged_values": true,
"enable_attribute_to_fields_replacement": false
}'::jsonb,
p_execute := TRUE
);
ERROR:  Missing information: You must provide at least "current_view.table_name" or "current_table.table_name".
CONTEXT:  PL/pgSQL function vrsn.__bitemporal_entity__complete_conf_param(jsonb) line 86 at RAISE
PL/pgSQL function vrsn.__bitemporal_entity__build_ddl(jsonb) line 45 at assignment
PL/pgSQL function vrsn.admin__bitemporal_entity_register(jsonb,boolean) line 32 at assignment

git log is at commit b005bd7ac6bff4b35b217c2450795498610d7fdf.
I attach the backup of the schemas in case you spot what is going wrong.

backup.sql

[DomenicoL]

https://github.com/DomenicoL/table-versioning/blob/main/docs/install.md#5-initialize-package-only-the-first-time

it seems to miss
SELECT vrsn.admin__init(
<only_get_query boolean>
);

This function import the premiliminary data for support tables

[fluca1978]

https://github.com/DomenicoL/table-versioning/blob/main/docs/install.md#5-initialize-package-only-the-first-time

it seems to miss SELECT vrsn.admin__init( <only_get_query boolean> );

I thought this has to be executed only once, even after the update.
However, I did:

testdb=> SELECT vrsn.admin__init(
        false
);

and then

testdb=> sELECT vrsn.admin__bitemporal_entity_register(
p_conf := '{
"current_table": {"schema_name": "public", "table_name": "foo_current"},
"historice_entity": "always",
"enable_history_attributes": true,
"main_fields_list": "p,t",
"cached_fields_list": "cached_attr_column",
"mitigate_conflicts": true,
"ignore_unchanged_values": true,
"enable_attribute_to_fields_replacement": false
}'::jsonb,
p_execute := TRUE
);
ERROR:  Json not valid:
{"path $.operation_type: additional property not allowed"}
CONTEXT:  PL/pgSQL function vrsn.__bitemporal_entity__build_ddl(jsonb) line 29 at RAISE
PL/pgSQL function vrsn.admin__bitemporal_entity_register(jsonb,boolean) line 32 at assignment

at least the error has changed!

[DomenicoL]

Ok I can reproduce the error.

I've to investigate about first line of vrsn.admin__init output (hstore missing)

[DomenicoL]

submitted the fix for function admin__init (there was a old version in the repository).

Redeploy the vrns.sql or simply replace admin__init function and execute it.

Pay attention to the instruction used to register

"historice_entity": "always",
"enable_history_attributes": true,
"main_fields_list": "p,t",
"cached_fields_list": "cached_attr_column",

Means enable history process for each single attribute will be written in "cached_attr_column" (should be of appropriate type) , but "cached_attr_column" is not part of table definition.
moreover with "historice_entity": "always" "main_fields_list" has no effect