Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On Fri, Jun 5, 2015 at 1:47 PM, Thomas Munro wrote: > On Fri, Jun 5, 2015 at 11:47 AM, Thomas Munro > wrote: >> On Fri, Jun 5, 2015 at 9:29 AM, Robert Haas wrote: >>> Here's a new version with some more fixes and improvements: >>> [...] >> >> With this patch, when I run the script >> "checkpoint

Re: [GENERAL] Planner cost adjustments

Daniel Begin wrote: > I can tweak values and restart Postgres without any hardship! Many of the important performance-related settings (especially cost factors) can be adjusted with the SET command to affect just the one connection. This can make experimenting a lot easier. > About seq_page_co

Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch wrote: > On Thu, Jun 04, 2015 at 05:29:51PM -0400, Robert Haas wrote: >> Here's a new version with some more fixes and improvements: > > I read through this version and found nothing to change. I encourage other > hackers to study the patch, though. The

[GENERAL] BDR - Failure of Primary Server - How to recover?

If my Primary Server in a BDR environment fails what is my recourse for recovery? My servers are in the cloud so I don't have control over IP address assignment either. This hasn't happen but I need to present a plan if our Production system has this occur. Thanks in advance! Carter Objectbrain

Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

Robert Haas writes: > On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch wrote: >> I read through this version and found nothing to change. I encourage other >> hackers to study the patch, though. The surrounding code is challenging. > Andres tested this and discovered that my changes to > find_multix

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On 2015-06-05 11:43:45 -0400, Tom Lane wrote: > Robert Haas writes: > > On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch wrote: > >> I read through this version and found nothing to change. I encourage other > >> hackers to study the patch, though. The surrounding code is challenging. > > > Andres t

[GENERAL] alter column type

I have a database in which one table references the primary key of another. The type of the primary key was initially int, but I changed it to bigint. However, I forgot to update the type of a column that references it. So, I've initiated "ALTER TABLE foo ALTER COLUMN bar TYPE bigint", where foo

Re: [GENERAL] postgres_fdw - push down conditionals for ENUMs

> > > 1. Is there any plans to add "non-strict mode" (configurable via options >> on >> > server/table/column level) to allow pushing down conditions for all data >> > types? >> >> No. You might as well call it a "return random answers" mode. >> > > Its bad. I think most users would be happy to ha

Re: [GENERAL] replicating many to one

We had similar database architecture like yours before. Our 4 databases are for sharding purpose. We used Bucardo to replicate from 4 different databases, aggregate them into 1 collector database. For the 4 databases A,B,C,D, for their auto increment sequence, we set them with different "start wit

[GENERAL] Query running slow for only one specific id. (Postgres 9.3) version

Postgresql 9.3 Version Guys Here is the issue that I'm facing for couple of weeks now. I have table (size 7GB) If I run this query with this specific registration id it is using the wrong execution plan and takes more than a minute to complete. Total number of rows for this registr

[GENERAL] Re: Query running slow for only one specific id. (Postgres 9.3) version

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Sheena, Prabhjot Sent: Friday, June 05, 2015 1:55 PM To: pgsql-general@postgresql.org; pgsql-performa...@postgresql.org Subject: [PERFORM] Query running slow for only one specific id. (Postg

Re: [GENERAL] alter column type

CAUTION: This is very dangerous and may cause corruption. *** DO THIS IN A TEST DATABASE FIRST *** --1. Get the oid for int8 (bigint) SELECT t.oid FROM pg_type t WHERE typname = 'int8'; --2. Get the oid for your table SELECT c.oid, c.relname as table, a.attname , a

[GENERAL] Re: [PERFORM] Query running slow for only one specific id. (Postgres 9.3) version

On 06/05/2015 10:54 AM, Sheena, Prabhjot wrote: Postgresql 9.3 Version Guys Here is the issue that I’m facing for couple of weeks now. I have table (size 7GB) *If I run this query with this specific registration id it is using the wrong execution plan and takes more than a minu

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On Fri, Jun 5, 2015 at 12:00 PM, Andres Freund wrote: > On 2015-06-05 11:43:45 -0400, Tom Lane wrote: >> Robert Haas writes: >> > On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch wrote: >> >> I read through this version and found nothing to change. I encourage >> >> other >> >> hackers to study the

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

Robert Haas writes: > On Fri, Jun 5, 2015 at 12:00 PM, Andres Freund wrote: >> On 2015-06-05 11:43:45 -0400, Tom Lane wrote: >>> So where are we on this? Are we ready to schedule a new set of >>> back-branch releases? If not, what issues remain to be looked at? >> We're currently still doing b

Re: [GENERAL] alter column type

Why is PG even re-writing all rows when the data type is being changed from smaller (int) to larger (bigint) type, which automatically means existing data is safe. Like, changing from varchar(30) to varchar(50) should involve no rewrite of existing rows. -- Sent via pgsql-general mailing list (p

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

Tom Lane wrote: > Robert Haas writes: > > There are at least two other known issues that seem like they should > > be fixed before we release: > > > 1. The problem that we might truncate an SLRU members page away when > > it's in the buffers, but not drop it from the buffers, leading to a > > fa

Re: [GENERAL] alter column type

On Fri, Jun 5, 2015 at 12:23 PM, Casey Deccio wrote: > I have a database in which one table references the primary key of > another. The type of the primary key was initially int, but I changed it > to bigint. However, I forgot to update the type of a column that > references it. So, I've init

[GENERAL] Re: Query running slow for only one specific id. (Postgres 9.3) version

When I run vacuum analyze it fixes the problem but after 1 or 2 days the problem comes back Here is the table structure Column |Type | Modifiers | Storage | Stats target | Description -

[GENERAL] Re: Query running slow for only one specific id. (Postgres 9.3) version

From: Sheena, Prabhjot [mailto:prabhjot.si...@classmates.com] Sent: Friday, June 05, 2015 2:38 PM To: Igor Neyman; pgsql-general@postgresql.org; pgsql-performa...@postgresql.org Subject: RE: Query running slow for only one specific id. (Postgres 9.3) version When I run vacuum analyze it fixes th

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On 2015-06-05 14:33:12 -0400, Tom Lane wrote: > Robert Haas writes: > > 1. The problem that we might truncate an SLRU members page away when > > it's in the buffers, but not drop it from the buffers, leading to a > > failure when we try to write it later. I've got a fix for this, and about three

Re: [GENERAL] alter column type

On Fri, Jun 5, 2015 at 2:36 PM, Michael Nolan wrote: > > Probably too late for this time, but in the past when I've needed to > redefine the type for a column, I've made a dump, edited the dump file to > change the type and then renamed the table and reloaded it. That's usually > several orders

Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

Robert Haas wrote: > On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch wrote: > > On Thu, Jun 04, 2015 at 05:29:51PM -0400, Robert Haas wrote: > >> Here's a new version with some more fixes and improvements: > > > > I read through this version and found nothing to change. I encourage other > > hackers t

Re: [GENERAL] alter column type

On 6/5/2015 11:37 AM, Ravi Krishna wrote: Why is PG even re-writing all rows when the data type is being changed from smaller (int) to larger (bigint) type, which automatically means existing data is safe. Like, changing from varchar(30) to varchar(50) should involve no rewrite of existing rows.

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On Fri, Jun 5, 2015 at 11:47 AM Andres Freund wrote: > But I'd definitely like some > independent testing for it, and I'm not sure if that's doable in time > for the wrap. > I'd be happy to test on my database that was broken, for however much that helps. It's a VM so I can easily revert back as

Re: [GENERAL] alter column type

On Fri, Jun 5, 2015 at 2:23 PM, Melvin Davidson wrote: > CAUTION: This is very dangerous and may cause corruption. > *** DO THIS IN A TEST DATABASE FIRST *** > > --1. Get the oid for int8 (bigint) > SELECT t.oid > FROM pg_type t > WHERE typname = 'int8'; > > --2. Get the oid for your t

Re: [GENERAL] alter column type

> On 6/5/2015 11:37 AM, Ravi Krishna wrote: >> >> Why is PG even re-writing all rows when the data type is being changed >> from smaller (int) to larger (bigint) type, which automatically means >> existing data is safe. Like, changing from varchar(30) to varchar(50) >> should involve no rewrite of

Re: [GENERAL] alter column type

> In the above case PG will simply do a dictionary update of meta > tables. So all new rows will reflect col-T and as and when the old I will clarify it bit further: All new rows will have space allocated for col-T and no space allocated for col-S, while existing dormant rows are left unmodified

Re: [GENERAL] Re: Query running slow for only one specific id. (Postgres 9.3) version

On 06/05/2015 11:38 AM, Sheena, Prabhjot wrote: When I run vacuum analyze it fixes the problem but after 1 or 2 days the problem comes back Is autovacuum running and using what settings? (select name, setting from pg_settings where name ~ 'autovacuum' Konsole output or name ~ 'statistics'

Re: [PERFORM] Re: [GENERAL] Re: Query running slow for only one specific id. (Postgres 9.3) version

On 06/05/2015 12:28 PM, Steve Crawford wrote: On 06/05/2015 11:38 AM, Sheena, Prabhjot wrote: When I run vacuum analyze it fixes the problem but after 1 or 2 days the problem comes back Is autovacuum running and using what settings? (select name, setting from pg_settings where name ~ 'aut

Re: [GENERAL] alter column type

Casey Deccio writes: > Being unfamiliar with the internals, what's the risk here? If postgres > thinks something is a bigint, but previously stored it as an int, does that > mean it will try to extract data beyond the boundary of some of the (old) > 32-bit values and potentially throw off offsets

Re: [GENERAL] alter column type

On 6/5/2015 11:46 AM, Casey Deccio wrote: On Fri, Jun 5, 2015 at 2:36 PM, Michael Nolan > wrote: Probably too late for this time, but in the past when I've needed to redefine the type for a column, I've made a dump, edited the dump file to change the type an

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On Fri, Jun 5, 2015 at 2:47 PM, Andres Freund wrote: > On 2015-06-05 14:33:12 -0400, Tom Lane wrote: >> Robert Haas writes: >> > 1. The problem that we might truncate an SLRU members page away when >> > it's in the buffers, but not drop it from the buffers, leading to a >> > failure when we try t

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On Fri, Jun 5, 2015 at 2:36 PM, Alvaro Herrera wrote: > Tom Lane wrote: >> Robert Haas writes: > >> > There are at least two other known issues that seem like they should >> > be fixed before we release: >> >> > 1. The problem that we might truncate an SLRU members page away when >> > it's in the

Re: [GENERAL] alter column type

On Fri, Jun 5, 2015 at 4:00 PM, John R Pierce wrote: > Actually, not too late. My first ALTER is still running, and I still have > four more to go. Sigh. I had thought of this but wasn't sure how it might > compare. Thanks for the data point :) > > > > if all 5 alters' were to the same table,

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On June 5, 2015 10:02:37 PM GMT+02:00, Robert Haas wrote: >On Fri, Jun 5, 2015 at 2:47 PM, Andres Freund >wrote: >> On 2015-06-05 14:33:12 -0400, Tom Lane wrote: >>> Robert Haas writes: >>> > 1. The problem that we might truncate an SLRU members page away >when >>> > it's in the buffers, but no

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

Andres Freund writes: > On June 5, 2015 10:02:37 PM GMT+02:00, Robert Haas > wrote: >> I think we would be foolish to rush that part into the tree. We >> probably got here in the first place by rushing the last round of >> fixes too much; let's try not to double down on that mistake. > My prob

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On 06/05/2015 01:56 PM, Tom Lane wrote: If we have confidence that we can ship something on Monday that is materially more trustworthy than the current releases, then let's aim to do that; but let's ship only patches we are confident in. We can do another set of releases later that incorporate

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

Joshua D. Drake wrote: > I believe there are likely quite a few parties willing to help test, if we > knew how? The code involved is related to checkpoints, pg_basebackups that take a long time to run, and multixact freezing and truncation. If you can set up test servers that eat lots of multixa

Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1

On Fri, Jun 5, 2015 at 4:40 PM, Andres Freund wrote: >>I think we would be foolish to rush that part into the tree. We >>probably got here in the first place by rushing the last round of >>fixes too much; let's try not to double down on that mistake. > > My problem with that approach is that I th

[GENERAL] Re: [PERFORM] Query running slow for only one specific id. (Postgres 9.3) version

On Fri, Jun 5, 2015 at 2:54 PM, Sheena, Prabhjot < prabhjot.si...@classmates.com> wrote: > explain analyze SELECT max(last_update_date) AS last_update_date FROM > btdt_responses WHERE registration_id = 8718704208 AND response != 4; > > > QUERY PLAN > > > ---

Re: [GENERAL] Thousands of schemas and ANALYZE goes out of memory

On Wed, May 13, 2015 at 3:10 PM, Tom Lane wrote: > Bruce Momjian writes: > > Is there a reason the following patch wasn't applied? > > I don't think anybody ever did the legwork to verify it was a good idea. > In particular, it'd be good to check if sending a tabstat message for each > table add