Обсуждение: How to discover what table is
I have a publication/subscription replication. Then this week started to see this message on Log of replica server. Message is "duplicate key value violates unique constraint "pksyslookup"" Detail is "Key (lookup_id)=(56) already exists." and on production server message is "logical decoding found consistent point at 5D5/5CD64ED0" I have 200 schemas on that database and every schema has that table, so ho can I discover what table is having that violation ? There is a way to know what WAL is being processed and get the command should run to discover what table should be updated ? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 3/8/20 12:26 PM, PegoraroF10 wrote:
Are you asking which schema it's in?
I have a publication/subscription replication. Then this week started to see this message on Log of replica server. Message is "duplicate key value violates unique constraint "pksyslookup"" Detail is "Key (lookup_id)=(56) already exists." and on production server message is "logical decoding found consistent point at 5D5/5CD64ED0" I have 200 schemas on that database and every schema has that table, so ho can I discover what table is having that violation ?
Are you asking which schema it's in?
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
correct, what schema that table belongs to. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Now I have the same problem with a different message. I´ve added a table on all schemas and did a refresh publication. when postgres sees a new table on publisher he goes to replicamand trucates that table to start copying. ok but I have 300 schemas, how can I know what schema that table belongs to ? On log of replica server ... Message cannot truncate a table referenced in a foreign key constraint Detail Table "rel_indicacao" references "cad_digitacao". Hint Truncate table "rel_indicacao" at the same time, or use TRUNCATE ... CASCADE. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 3/10/20 5:16 AM, PegoraroF10 wrote: > Now I have the same problem with a different message. > I´ve added a table on all schemas and did a refresh publication. when > postgres sees a new table on publisher he goes to replicamand trucates that > table to start copying. ok but I have 300 schemas, how can I know what > schema that table belongs to ? Postgres version(s)? Are you using the built in logical replication or the pglogical plugin? > > On log of replica server ... > > Message > cannot truncate a table referenced in a foreign key constraint > Detail > Table "rel_indicacao" references "cad_digitacao". > Hint > Truncate table "rel_indicacao" at the same time, or use TRUNCATE ... > CASCADE. > > > > > -- > Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com
built in logical replication -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 3/10/20 8:42 AM, PegoraroF10 wrote: > built in logical replication Well it does not do DDL replication so I am not sure how the new table is getting to the replica? It might help if you provide an start to end example of what you are doing. > > > > -- > Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com
Well, for now it´s solved but I´ll explain what happens to solve it better on future. Suppose on Master you have a database with hundreds of schemas with same structure, so table Customer happens 500 times on that DB. That database being replicated with publication/subscription for all tables model, just that. Now to have new schemas added to this database you go to replica, create them with structure only, go to master and create them with data, go back to replica and refresh publication. But then imagine that one of those schemas you´ve created on replica with data. This is where problem occurs and message is just PK of Table Customer is duplicated but is not easy to find which table because I´ll have that table and that key on 90% of my schemas. If, on error message we just have which schema belongs that table would be great. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 3/11/20 1:22 AM, PegoraroF10 wrote: > Well, for now it´s solved but I´ll explain what happens to solve it better on > future. > Suppose on Master you have a database with hundreds of schemas with same > structure, so table Customer happens 500 times on that DB. That database > being replicated with publication/subscription for all tables model, just > that. Now to have new schemas added to this database you go to replica, > create them with structure only, go to master and create them with data, go > back to replica and refresh publication. But then imagine that one of those > schemas you´ve created on replica with data. This is where problem occurs > and message is just PK of Table Customer is duplicated but is not easy to > find which table because I´ll have that table and that key on 90% of my > schemas. If, on error message we just have which schema belongs that table > would be great. Some digging found that the ERROR uses get_rel_name for the relation name and that led to: ~/src/backend/utils/cache/lsyscache.c /* * get_rel_name * Returns the name of a given relation. * * Returns a palloc'd copy of the string, or NULL if no such relation. * * NOTE: since relation name is not unique, be wary of code that uses this * for anything except preparing error messages. */ Going any further is going to need someone with more knowledge of the above to chime in. > > > > -- > Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com