How to handle logical replication 12->14, when our max_replication_slots gets overrun by inactive sync workers
Hi, I reported a bug aobut it earlier, and from what I know it has been fixed, but new release will come later. For now I have this situation: 1. max_replication_slots is 50 2. database to replicate has 67 schemas, and ~ 26k tables. 3. schemas are split into 5 slots 4. pg14 side has max_sync_workers_per_subscription = 2 we start replication. within an hour all 50 replication slots on pg12 are used. 5 by our pg14 upgrade slots, and the other *45* by sync workers, which are generally active = false. at this moment all sync traffic dies (seen in network traffic data). we tried to kill inactive sync workers - didn't help. I tried to set max_sync_workers_per_subscription = 0, to avoid using these extra workers, but it just seems to make slots sit there. 1 table in each slot changed status to 'r', but all other are at 'i'. Currently I'm running a test where all tables are in single slot, with 2 max_sync_workers_per_subscription but it will take "a while" to get it to working state. Is there anything we can do now? I seem to have a case where the problem is 100% repeatable, so if anyone has ideas I can test them fully. Best regards, depesz
Re: i added Arabic Dictionary but how I know i'm using it
Hello Dear sorry for the late reply your response helped me to finish the feature successfully thank you for your efforts my best regards On Wed, 17 Aug 2022, 6:59 pm Laurenz Albe, wrote: > On Wed, 2022-08-17 at 14:33 +0300, Mohammed falih wrote: > > I created the dictionary by this query > > CREATE TEXT SEARCH DICTIONARY arabic_dict ( > > TEMPLATE = ispell, > >DictFile = th_ar, > > AffFile = th_ar, > > Stopwords = arabic); > > and as you know the purpose in dictionary is like when you're searching > for an > > example a "house" the results will also retrieve a records with "home" > because > > it's a synonymous but when I run queries nothing happens > > That would be a "synonym dictionary": > > https://www.postgresql.org/docs/current/textsearch-dictionaries.html#TEXTSEARCH-SYNONYM-DICTIONARY > An Ispell dictionary normalizes words, for example by removing suffixes for > plural and case. > > > You'd have to create a synonym file yourself. > > Yours, > Laurenz Albe >
problem with on conflict / do update using psql 14.4
I have not been able to get the "ON CONFLICT" clause to work I am using psql 14.4 on ubuntu Given the following table definition CREATE TABLE my_companies ( company_id SERIAL NOT NULL PRIMARY KEY, second_id INTEGER NOT NULL REFERENCES big_list(second_id), string_company_id TEXT NOT NULL, company_name TEXT NOT NULL, person_name TEXT NOT NULL ) INHERITS ( _audit_table, _delete_table ); and the following constraints CREATE UNIQUE INDEX my_companies_id_unique ON my_companies(string_company_id) WHERE delete_timestamp IS NULL; CREATE UNIQUE INDEX my_companies_company_name_unique ON my_companies(company_name) WHERE delete_timestamp IS NULL; I issued the following sql insert in an attempt to use "on conflict" to update the duplicate column name insert into my_companies (second_id,string_company_id,company_name,person_name) values (1,'66','widgets','seller-toto') on conflict (company_name) do update set company_name = concat(company_name,'++',string_company_id); In this example a record with a company name of 'widgets' already exists and will result in an constraint violation when I ran my sql statement I received the following error message bkimelman=# \i insert-companies-1c.sql psql:insert-companies-1c.sql:3: ERROR: column reference "company_name" is ambiguous LINE 3: ...company_name) do update set company_name = concat(company_na... I tried fully qualifying the column names in the concat() function call, but all that did was get me a different error message What would be the proper format for the "on conflict" clause ?
Re: problem with on conflict / do update using psql 14.4
You need to prefix the rhs column(s) with 'excluded.'. For example: on conflict (company_name) do update set company_name = concat(excluded.company_name,'++',excluded.string_company_id); On Sat, Sep 24, 2022 at 7:28 AM Barry Kimelman wrote: > > I have not been able to get the "ON CONFLICT" clause to work > I am using psql 14.4 on ubuntu > > Given the following table definition > > CREATE TABLE my_companies ( > company_id SERIAL NOT NULL PRIMARY KEY, > second_id INTEGER NOT NULL REFERENCES big_list(second_id), > string_company_id TEXT NOT NULL, > company_name TEXT NOT NULL, > person_name TEXT NOT NULL > ) > INHERITS ( _audit_table, _delete_table ); > > and the following constraints > > CREATE UNIQUE INDEX my_companies_id_unique ON > my_companies(string_company_id) WHERE delete_timestamp IS NULL; > CREATE UNIQUE INDEX my_companies_company_name_unique ON > my_companies(company_name) WHERE delete_timestamp IS NULL; > > I issued the following sql insert in an attempt to use "on conflict" to > update the > duplicate column name > > insert into my_companies > (second_id,string_company_id,company_name,person_name) > values (1,'66','widgets','seller-toto') > on conflict (company_name) do update set company_name = > concat(company_name,'++',string_company_id); > > In this example a record with a company name of 'widgets' already exists > and will > result in an constraint violation > > when I ran my sql statement I received the following error message > > bkimelman=# \i insert-companies-1c.sql > psql:insert-companies-1c.sql:3: ERROR: column reference "company_name" is > ambiguous > LINE 3: ...company_name) do update set company_name = concat(company_na... > > I tried fully qualifying the column names in the concat() function call, > but all that did was get me a different error message > > What would be the proper format for the "on conflict" clause ? >
Re: problem with on conflict / do update using psql 14.4
On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman wrote: > > CREATE UNIQUE INDEX my_companies_id_unique ON > my_companies(string_company_id) WHERE delete_timestamp IS NULL; > CREATE UNIQUE INDEX my_companies_company_name_unique ON > my_companies(company_name) WHERE delete_timestamp IS NULL; > > I issued the following sql insert in an attempt to use "on conflict" to > update the > duplicate column name > > insert into my_companies > (second_id,string_company_id,company_name,person_name) > values (1,'66','widgets','seller-toto') > on conflict (company_name) do update set company_name = > concat(company_name,'++',string_company_id); > > > when I ran my sql statement I received the following error message > > bkimelman=# \i insert-companies-1c.sql > psql:insert-companies-1c.sql:3: ERROR: column reference "company_name" is > ambiguous > LINE 3: ...company_name) do update set company_name = concat(company_na... > > I tried fully qualifying the column names in the concat() function call, > but all that did was get me a different error message > Which is the more interesting one since the ambiguous column name error you did show has been resolved... > What would be the proper format for the "on conflict" clause ? > I think that since your index is partial you need to modify your insert command's on conflict clause to include a matching where clause. (WHERE index_predicate) https://www.postgresql.org/docs/current/sql-insert.html David J.
Re: problem with on conflict / do update using psql 14.4
On Fri, Sep 23, 2022 at 4:43 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman > wrote: > >> >> CREATE UNIQUE INDEX my_companies_id_unique ON >> my_companies(string_company_id) WHERE delete_timestamp IS NULL; >> CREATE UNIQUE INDEX my_companies_company_name_unique ON >> my_companies(company_name) WHERE delete_timestamp IS NULL; >> >> I issued the following sql insert in an attempt to use "on conflict" to >> update the >> duplicate column name >> >> insert into my_companies >> (second_id,string_company_id,company_name,person_name) >> values (1,'66','widgets','seller-toto') >> on conflict (company_name) do update set company_name = >> concat(company_name,'++',string_company_id); >> >> >> when I ran my sql statement I received the following error message >> >> bkimelman=# \i insert-companies-1c.sql >> psql:insert-companies-1c.sql:3: ERROR: column reference "company_name" >> is ambiguous >> LINE 3: ...company_name) do update set company_name = concat(company_na... >> >> I tried fully qualifying the column names in the concat() function call, >> but all that did was get me a different error message >> > > Which is the more interesting one since the ambiguous column name error > you did show has been resolved... > > >> What would be the proper format for the "on conflict" clause ? >> > > I think that since your index is partial you need to modify your insert > command's on conflict clause to include a matching where clause. (WHERE > index_predicate) > > https://www.postgresql.org/docs/current/sql-insert.html > > David J. > > Thanks for the reply. but could you be more specific about the where clause ? I am new to postgresql and ON CONFLICT -- Barry Kimelman Winnipeg, MB, Canada
