We have jsonb objects (which have backslashes in the name)in the database table. Whenever we try to update or delete the jsonb object, postgresql throws the error. Names with backslashes in it are getting added in the database but once we try to delete/update via name field, the postgresql starts throwing error. How to correct this problem, that upon delete/update of backup plan names from UI, the postgresql doesn't throw the errors.
database=> select * from mytable2 where name like '%\\%';
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------
id | 383
name | New\protection\plan - ff0fda66-1a8a-46ed-a603-0225f56a4fbb
type | acronis
port | 0
extra_tags | {"__param_target": "ff0fda66-1a8a-46ed-a603-0225f56a4fbb", "acronis_policy_uuid": "ff0fda66-1a8a-46ed-a603-0225f56a4fbb"}
capability | serviceMonitoringAcronis
subaccnt | QN9LWY
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------------
id | 384
name | Australia\\protection\\plan - 54751ad1-5cdb-48d2-925c-173fbbff71f8
type | acronis
port | 0
extra_tags | {"__param_target": "54751ad1-5cdb-48d2-925c-173fbbff71f8", "acronis_policy_uuid": "54751ad1-5cdb-48d2-925c-173fbbff71f8"}
capability | serviceMonitoringAcronis
subaccnt | QN9LWY
-[ RECORD 3 ]-------------------------------------------------------------------------------------------------------------------------
id | 385
name | Shubhra\\\\Backup\\\Plan - b35c5e31-ea54-4cbc-81b7-abf55f6f9a80
type | acronis
port | 0
extra_tags | {"__param_target": "b35c5e31-ea54-4cbc-81b7-abf55f6f9a80", "acronis_policy_uuid": "b35c5e31-ea54-4cbc-81b7-abf55f6f9a80"}
capability | serviceMonitoringAcronis
subaccnt | QN9LWY
=================================================
database>>mytable2
Table "public.mytable2"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------------------------------------------------------
id | integer | | not null | nextval('mytable2_id_seq'::regclass)
name | text | | not null |
type | text | | not null |
port | integer | | not null |
extra_tags | jsonb | | not null | '{}'::jsonb
capability | text | | not null |
subaccnt | text | | |
There is another table where these names (with backslashes propagates)
database=>
database=> select * from mytable1 where subaccnt='QN9LWY';
id | subaccnt | enabled | server_pair | services | serial
-------+----------+---------+---------------------------------------------+----------------------------------------------------------------------+--------
54814 | QN9LWY | t | abc.com | {"New\\protection\\plan - ff0fda66-1a8a-46ed-a603-0225f56a4fbb": {}} | 0
(1 row)
Time: 0.622 ms
database=>
=========================
database=> \d mytable1
Table "public.mytable1"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------------------------------------------------------
id | integer | | not null | nextval('subaccnt_monitoring_setting_id_seq'::regclass)
subaccnt | text | | not null |
enabled | boolean | | not null | true
server_pair | text | | not null |
services | jsonb | | not null | '{}'::jsonb
serial | integer | | not null | 0
=========================== The error:
The following status messages were emitted, and interrupted by an error: Succ: Network assignments removed., Succ: Change-tracking changes deleted., Succ: Subaccnt features (pricing) deactivated. :error: Query Failed: DELETE FROM subaccnt WHERE ( ( id = ? AND paccnt = ? ) ) - DBI Exception: DBD::Pg::st execute failed: ERROR: invalid input syntax for type json DETAIL: Escape sequence "\p" is invalid. CONTEXT: JSON data, line 1: {"New\p... SQL statement "SELECT EXISTS (SELECT 1 FROM subaccnt_monitoring_setting WHERE services @> servicesJson::jsonb)" PL/pgSQL function enforce_monitoring_service_unused() line 6 at IF SQL statement "DELETE FROM ONLY "public"."subaccnt_monitoring_service" WHERE $1::pg_catalog.text OPERATOR(pg_catalog.=) "subaccnt"" [for Statement "DELETE FROM subaccnt WHERE ( ( id = ? AND paccnt = ? ) )" with ParamValues: 1='13726741', 2='7077073']
Original postgresql procedure: Here we need to add
BEGIN;
ALTER TABLE mytable1 ADD COLUMN subaccnt TEXT REFERENCES subaccnt(uniq_id) ON UPDATE CASCADE ON DELETE CASCADE;
CREATE OR REPLACE FUNCTION enforce_mytable1_unused() RETURNS trigger AS $$
DECLARE
servicesJson text;
BEGIN
servicesJson := concat('{"', OLD.name, '":{"enabled":"1"}}');
IF EXISTS (SELECT 1 FROM mytable2 WHERE services @> servicesJson::jsonb) THEN
RAISE EXCEPTION 'Removing referenced monitoring services is not allowed: %', OLD.name;
END IF;
RETURN OLD;
END
$$ LANGUAGE plpgsql;