Postgress BDR insert failed for "complete" table
Hello, I am using postgres with BDR bidirectional replication which requires primary keys for the freeswitch tables. So far so good , except freeswitch is complaining about one table here is the error 2021-10-26 23:40:47.772796 95.47% [DEBUG] mod_pgsql.c:784 Query (insert into complete values (0,'hash_remote', 'list', '', '', '', '', '', '', '', '', 'dagobah-api-gw-1-2')) returned PGRES_FATAL_ERROR 2021-10-26 23:40:47.772796 95.47% [ERR] mod_pgsql.c:551 Error executing query: ERROR: column "complete_uuid" is of type uuid but expression is of type integer LINE 1: insert into complete values (0,'hash_remote', 'list', '', ''... What I noticed is that freeswitch is doing an INSERT INTO command with no column names specified. I checked the source code and found this to be the only case for the "complete" table whereas the other tables when INSERT INTO is used column names are specified. Here is the structure of my complete table with the new column complete_uuid as primary key. CREATE TABLE :myschema.complete ( complete_uuid uuid DEFAULT public.gen_random_uuid() PRIMARY KEY, sticky INTEGER, a1 VARCHAR(128), a2 VARCHAR(128), a3 VARCHAR(128), a4 VARCHAR(128), a5 VARCHAR(128), a6 VARCHAR(128), a7 VARCHAR(128), a8 VARCHAR(128), a9 VARCHAR(128), a10 VARCHAR(128), hostname VARCHAR(256) ); Any suggestions? Thanks Abdirahman A. Osman
On Wed, Oct 27, 2021 at 5:44 PM Abdirahman Osman <abdirahman.osm@gmail.com> wrote:
Hello,
I am using postgres with BDR bidirectional replication which requires primary keys for the freeswitch tables.
So far so good , except freeswitch is complaining about one table here is the error
2021-10-26 23:40:47.772796 95.47% [DEBUG] mod_pgsql.c:784 Query (insert into complete values (0,'hash_remote', 'list', '', '', '', '', '', '', '', '', 'dagobah-api-gw-1-2')) returned PGRES_FATAL_ERROR 2021-10-26 23:40:47.772796 95.47% [ERR] mod_pgsql.c:551 Error executing query: ERROR: column "complete_uuid" is of type uuid but expression is of type integer LINE 1: insert into complete values (0,'hash_remote', 'list', '', ''...
What I noticed is that freeswitch is doing an INSERT INTO command with no column names specified. I checked the source code and found this to be the only case for the "complete" table whereas the other tables when INSERT INTO is used column names are specified.
Here is the structure of my complete table with the new column complete_uuid as primary key.
CREATE TABLE :myschema.complete ( complete_uuid uuid DEFAULT public.gen_random_uuid() PRIMARY KEY, sticky INTEGER, a1 VARCHAR(128), a2 VARCHAR(128), a3 VARCHAR(128), a4 VARCHAR(128), a5 VARCHAR(128), a6 VARCHAR(128), a7 VARCHAR(128), a8 VARCHAR(128), a9 VARCHAR(128), a10 VARCHAR(128), hostname VARCHAR(256) );
Any suggestions?
What is the table "complete" used for? by which module? You can't do anything, only modify source in a way to explicit use fields, and give a default to generate pk, or fill the pk explicitly. Anyway, which module uses this table? -giovanni -- Sincerely, Giovanni Maruzzelli OpenTelecom.IT cell: +39 347 266 56 18
Hi Giovanni, Thanks for your comment. I think the table is used by fs_cli to list available commands. Another possible solution I came across is to use Replica Identity " ALTER TABLE mytable REPLICA IDENTITY FULL", will give it a try. Abdirahman A. Osman On Wed, 27 Oct 2021 at 13:53, Giovanni Maruzzelli <gmaruzz@gmail.com> wrote:
On Wed, Oct 27, 2021 at 5:44 PM Abdirahman Osman <abdirahman.osm@gmail.com> wrote:
Hello,
I am using postgres with BDR bidirectional replication which requires primary keys for the freeswitch tables.
So far so good , except freeswitch is complaining about one table here is the error
2021-10-26 23:40:47.772796 95.47% [DEBUG] mod_pgsql.c:784 Query (insert into complete values (0,'hash_remote', 'list', '', '', '', '', '', '', '', '', 'dagobah-api-gw-1-2')) returned PGRES_FATAL_ERROR 2021-10-26 23:40:47.772796 95.47% [ERR] mod_pgsql.c:551 Error executing query: ERROR: column "complete_uuid" is of type uuid but expression is of type integer LINE 1: insert into complete values (0,'hash_remote', 'list', '', ''...
What I noticed is that freeswitch is doing an INSERT INTO command with no column names specified. I checked the source code and found this to be the only case for the "complete" table whereas the other tables when INSERT INTO is used column names are specified.
Here is the structure of my complete table with the new column complete_uuid as primary key.
CREATE TABLE :myschema.complete ( complete_uuid uuid DEFAULT public.gen_random_uuid() PRIMARY KEY, sticky INTEGER, a1 VARCHAR(128), a2 VARCHAR(128), a3 VARCHAR(128), a4 VARCHAR(128), a5 VARCHAR(128), a6 VARCHAR(128), a7 VARCHAR(128), a8 VARCHAR(128), a9 VARCHAR(128), a10 VARCHAR(128), hostname VARCHAR(256) );
Any suggestions?
What is the table "complete" used for? by which module?
You can't do anything, only modify source in a way to explicit use fields, and give a default to generate pk, or fill the pk explicitly.
Anyway, which module uses this table?
-giovanni
-- Sincerely,
Giovanni Maruzzelli OpenTelecom.IT cell: +39 347 266 56 18
_________________________________________________________________________
The FreeSWITCH project is sponsored by SignalWire https://signalwire.com Enhance your FreeSWITCH install with disruptive priced SMS and PSTN services. Build your next product on our scalable cloud platform.
Join our online community to chat in real time https://signalwire.community
Professional FreeSWITCH Services sales@freeswitch.com https://freeswitch.com
Official FreeSWITCH Sites https://freeswitch.com/oss https://freeswitch.org/confluence https://cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users https://freeswitch.com
On Thu, Oct 28, 2021 at 5:23 PM Abdirahman Osman <abdirahman.osm@gmail.com> wrote:
I think the table is used by fs_cli to list available commands. Another possible solution I came across is to use Replica Identity " ALTER TABLE mytable REPLICA IDENTITY FULL", will give it a try.
You can add an autogenerated uuid (eg: defaults to some uuid generation function) pk to that table, and it will work with BDR -- Sincerely, Giovanni Maruzzelli OpenTelecom.IT cell: +39 347 266 56 18
participants (2)
-
Abdirahman Osman
-
Giovanni Maruzzelli