Re: [GENERAL] optimizing a query

On Wed, Jun 22, 2016 at 2:23 PM, Jonathan Vanasco wrote: > > Note 2: > > This is odd, but this index is used by the planner: > CREATE INDEX idx_partial_fkey_id ON table_a(fkey_1, id) > WHERE col_partial IS NOT FALSE; > > but this index is never

Re: [GENERAL] optimizing a query

On Jun 22, 2016, at 2:38 PM, David G. Johnston wrote: > What query? ​A self-contained email would be nice.​ This was the same query as in the previous email in the thread. I didn't think to repeat it. I did include it below. >

Re: [GENERAL] Help on recovering my standby

I had the same issue... A slave server had missing wal_files... and it wasn't synced. I had to re-sync all the DB, by running the pg_basebackup command So.. basically, what I did is: 1 - Ensure that the wal_files are being inserted into the slave 2 - Backup the recovery.conf,

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

On Wed, 22 Jun 2016 10:20:38 + Sameer Kumar wrote: > On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov wrote: > > > I am running PostgreSQL 9.5. > > > > CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT); > > > > The constraint that the

Re: [GENERAL] Postgres 9.5.2 upgrade to 9.6

On Wed, Jun 22, 2016 at 2:36 PM, Michelle Schwan wrote: > I have a question about the upgrade from 9.5.2 to 9.6 > > > > I know 9.6 is still in beta – but I’m trying to be ahead of the game! > > > > I found instructions on how to move from 9.4 to 9.5. I will try testing >

Re: [GENERAL] optimizing a query

On Wed, Jun 22, 2016 at 2:23 PM, Jonathan Vanasco wrote: > > On Jun 22, 2016, at 4:25 AM, Erik Gustafson wrote: > > > don't you want an index on t_a2b.col_a, maybe partial where col_a=1 ? > > that table has indexes on all columns. they're never referenced because > the rows

[GENERAL] Postgres 9.5.2 upgrade to 9.6

I have a question about the upgrade from 9.5.2 to 9.6 I know 9.6 is still in beta - but I'm trying to be ahead of the game! I found instructions on how to move from 9.4 to 9.5. I will try testing these steps with 9.5 to 9.6 However, I was wondering if there is a "nicer" way to upgrade -

Re: [GENERAL] optimizing a query

On Jun 22, 2016, at 4:25 AM, Erik Gustafson wrote: > don't you want an index on t_a2b.col_a, maybe partial where col_a=1 ? that table has indexes on all columns. they're never referenced because the rows are so short. this was just an example query too, col_a has 200k variations After a

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

On 6/22/2016 3:07 AM, Vlad Arkhipov wrote: CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT); The constraint that the data must satisfy is `there is no more than 3 records with the same name`. I am not in control of queries that modify the table, so advisory locks can hardly be of

Re: [GENERAL] Help on recovering my standby

On Wed, Jun 22, 2016 at 12:22 PM, Alan Hodgson wrote: > On Tuesday 21 June 2016 19:34:18 Ramalingam, Sankarakumar wrote: > > Hi I have my standby (streaming replication) down due to missing wal > files. > > You would see the same error in the logs stating "cannot find

Re: [GENERAL] Help on recovering my standby

On Tuesday 21 June 2016 19:34:18 Ramalingam, Sankarakumar wrote: > Hi I have my standby (streaming replication) down due to missing wal files. > You would see the same error in the logs stating "cannot find the wal file > ..." What is the best way to get it going so that when we switch between >

Re: ***SPAM*** Re: [GENERAL] random huge delay when recreate a VIEW or FUNCTION

well, the for your reply, we have PG on linux ubuntu 14.04 distribution, but pls remember this issue started when our DB size increase to 3-5GB, now it is 16GB. this happend only when we use "CREATE OR REPLACE" since I can run a query and get reply

[GENERAL] Help on recovering my standby

Hi I have my standby (streaming replication) down due to missing wal files. You would see the same error in the logs stating "cannot find the wal file ..." What is the best way to get it going so that when we switch between standby and primary once in a while they are in sync? Currently I am

Re: [GENERAL] Regression in query optimizer when upgrading from 9.4.5 to 9.5.2?

On Jun 20, 2016, at 1:36 PM, David G. Johnston > wrote: ​Please don't top-post. Yes, using "[VACUUM] ANALYZE" is what was meant (the VACUUM doesn't hurt - and possibly helps - but wasn't required). ANALYZE recomputes the

Re: [GENERAL] pg_restore error-s after pg_dump

On 06/22/2016 04:00 AM, SDAG wrote: Hi Postgres version : *PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit* We transfer from one server to another (with better cpu an ram) our system using vm converter and when I try to backup

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

Le Wed, 22 Jun 2016 18:07:46 +0800, Vlad Arkhipov a écrit : > I am running PostgreSQL 9.5. > > CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT); I guess this is not your definitive definition of the table and you might have some other fields isn't it ? I can

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

Le Wed, 22 Jun 2016 10:49:13 +, Albe Laurenz a écrit : > Sameer Kumar wrote: > > On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov wrote: > >> I am running PostgreSQL 9.5. > >> > >> CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT); > >>

[GENERAL] pg_restore error-s after pg_dump

Hi Postgres version : *PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit* We transfer from one server to another (with better cpu an ram) our system using vm converter and when I try to backup database have an error : *pg_dump: reading

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

That is why I need to lock the table before. The transactions are running at the READ COMMITTED isolation level. On 06/22/2016 06:49 PM, Albe Laurenz wrote: But be warned that this will only work if all transactions involved use the isolation level SERIALIZABLE. -- Sent via pgsql-general

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

Sameer Kumar wrote: > On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov wrote: >> I am running PostgreSQL 9.5. >> >> CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT); >> >> The constraint that the data must satisfy is `there is no more than 3 >> records with the

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov wrote: > I am running PostgreSQL 9.5. > > CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT); > > The constraint that the data must satisfy is `there is no more than 3 > records with the same name`. > > I am not in

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

I am running PostgreSQL 9.5. CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT); The constraint that the data must satisfy is `there is no more than 3 records with the same name`. I am not in control of queries that modify the table, so advisory locks can hardly be of help to me.

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

Vlad Arkhipov wrote: > I have a constraint that requires a table to be locked before checking > it (i.e. no more than 2 records with the same value in the same column). > If I lock the table in the SHARE ROW EXCLUSIVE mode, any vacuuming (or > autovacuuming) process prevents me from checking the

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

On Wed, Jun 22, 2016 at 5:10 PM Vlad Arkhipov wrote: > Hello, > > I have a constraint that requires a table to be locked before checking > it (i.e. no more than 2 records with the same value in the same column). > If I lock the table in the SHARE ROW EXCLUSIVE mode, any

[GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

Hello, I have a constraint that requires a table to be locked before checking it (i.e. no more than 2 records with the same value in the same column). If I lock the table in the SHARE ROW EXCLUSIVE mode, any vacuuming (or autovacuuming) process prevents me from checking the constraint. What

Re: [GENERAL] optimizing a query

Hi, don't you want an index on t_a2b.col_a, maybe partial where col_a=1 ? On Wed, Jun 22, 2016 at 2:07 AM, Jonathan Vanasco wrote: > > On Jun 21, 2016, at 6:55 PM, David G. Johnston wrote: > > ​Aside from the name these indexes are identical...​ > > > sorry. tired eyes

R: [GENERAL] Vacuum full: alternatives?

Excellent Scott! Thank you! Francesco Da: Scott Marlowe [scott.marl...@gmail.com] Inviato: martedì 21 giugno 2016 2.06 A: Job Cc: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] Vacuum full: alternatives? On Mon, Jun 20, 2016 at 3:18 AM, Job