1

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;
5
  • 1
    What client are you using to insert the data into the database? To me it looks like that the client is not doing correct adaptation of the initial values. Also show the client code you are using to do the insert. Add information as text update to question text. Commented Jul 30, 2024 at 21:56
  • 1
    What you have shown is not json, so your question doesn't make sense. Also, if something throws errors, you need to show the something, and show the errors. Commented Jul 31, 2024 at 0:07
  • The name of the language is Perl, not PERL. It's not a acronym. Commented Jul 31, 2024 at 6:43
  • JSONB is for JSON data, the data you've provided is not JSON. Commented Jul 31, 2024 at 21:52
  • @RawleyFowler See, I have updated the postgresql Commented Aug 4, 2024 at 16:38

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.