[GENERAL] Text,Citext column and Btree index
Hi, Why is a citext column not using Btree index for like operation even without any wildcard character? It can use Btree index if there is no wildcard character and if it is at the end right? Also a text column is using index when there is no wildcard character,but it is also not using if it is present at the end. -- View this message in context: http://postgresql.nabble.com/Text-Citext-column-and-Btree-index-tp5918823.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update or Delete causes canceling of long running slave queries
Hi, What if we have a chain of slaves (master->slave->sec slave)? And if queries would run on each of them? or a config like master ->slave1 ->slave2 Is it ok to enable feedback on both slaves ? -- View this message in context: http://postgresql.nabble.com/Update-or-Delete-causes-canceling-of-long-running-slave-queries-tp5903250p5903914.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update or Delete causes canceling of long running slave queries
Hi, Thank you for the explanation. The slave queries are mostly reporting queries,which sometimes would take 30+ running time due to complex joins and criteria. We haven't tried running these queries on master before splitting, and it is possible that there will be heavy updates and deletes on master on some tables some times. Now we have to decide between these two parameters as which one to use. Can you give any suggestion? -- View this message in context: http://postgresql.nabble.com/Update-or-Delete-causes-canceling-of-long-running-slave-queries-tp5903250p5903415.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update or Delete causes canceling of long running slave queries
Hi Jeff, Yes I am turning off autovacuum for experimental purpose only. I was curious to know what is causing the queries to be killed when the autovacuum is not running. I guess it had to be the pruning of HOT update chains like you have mentioned. Also I have already tried changing the parameters hot_standby_feedback and vacuum_defer_cleanup_age, it works fine, but if HOT update is the problem then changing these setting will hinder it right? If so then is it safe to use these parameters? Or will there be any notable problems or performance issues? This pruning of HOT update could be the case for update only or delete queries too? -- View this message in context: http://postgresql.nabble.com/Update-or-Delete-causes-canceling-of-long-running-slave-queries-tp5903250p5903344.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update or Delete causes canceling of long running slave queries
Hi, I am using postgres 9.5 Yes,I restarted the server after changing the autovacuum to off. Also verified that autovacuum process is not running. standby db configurations: (mostly default conf only) hot_standby = on max_standby_archive_delay = 30s max_standby_streaming_delay = 30s wal_receiver_status_interval = 10s hot_standby_feedback = off wal_receiver_timeout = 60s wal_retrieve_retry_interval = 5s -- View this message in context: http://postgresql.nabble.com/Update-or-Delete-causes-canceling-of-long-running-slave-queries-tp5903250p5903334.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Update or Delete causes canceling of long running slave queries
Hi, I have the following configurations. On master: autovacuum=off vacuum_defer_cleanup_age=0 On slave: hot_standby_feedback = off max_standby_streaming_delay = 30s Now when I try to run a long query on slave and do some update or delete on the table on master I am getting the following error. ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. The error code was : 40001 I am not running vacuum manually and autovacuum is 'off' What could possibly causing the conflict? -- View this message in context: http://postgresql.nabble.com/Update-or-Delete-causes-canceling-of-long-running-slave-queries-tp5903250.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
