Re: questions about wraparound

On Mon, Mar 29, 2021 at 7:12 PM Laurenz Albe wrote: > I didn't follow the rest of the thread, but autovacuum should handle > those databases and advance their "datfrozenxid". That did not happen. In short: I turned off autovacuum globally, then in a database I consumed a lot of xid while keeping

Re: PostgreSQL Views Where Clause Character Columns getting Typecasted to Text and Indexes not getting Used

Hi Tom, Thanks for your analysis!! As we were discussing on the typecasted column not able to use the indexes as base table columns are with datatype varchar and they are type casted to text internally . We made some more analysis and tried to create some test tables with columns datatype as *tex

Re: Row not immediately visible after commit

Ok, I believe I have found an explanation, and it is due to a logic error, not due to anything funky happening with the database. Please excuse the noise. Steve On Tue, Mar 30, 2021 at 11:06 AM Steve Baldwin wrote: > Thanks Adrian. This is 'vanilla' postgres as far as I know (unlike their > pos

Re: Row not immediately visible after commit

RDS is also a modified version of Postgresql, just not as modified as Aurora. On 3/29/21 7:06 PM, Steve Baldwin wrote: Thanks Adrian. This is 'vanilla' postgres as far as I know (unlike their postgres-flavoured Aurora product). b2bc_owner@b2bcreditonline=> select version();                    

Re: Row not immediately visible after commit

Thanks Adrian. This is 'vanilla' postgres as far as I know (unlike their postgres-flavoured Aurora product). b2bc_owner@b2bcreditonline=> select version(); version -

Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

On 3/29/21 3:32 PM, Bryn Llewellyn wrote: On 27-Mar-2021, at 09:16, Adrian Klaver wrote: … Re Adrian’s quote of the doc thus: Also, field values “to the right” of the least significant field allowed by the fields specification are silently discarded. Yes, I do take this point. But there’

Re: Row not immediately visible after commit

On 3/29/21 4:39 PM, Steve Baldwin wrote: Hi all, I know this is going to sound weird/unbelievable, but I'm trying to come up with an explanation for what I've observed. First, a couple of data points. The instance is running on AWS RDS and is on version 13.1. All my timestamps and elapsed ti

Row not immediately visible after commit

Hi all, I know this is going to sound weird/unbelievable, but I'm trying to come up with an explanation for what I've observed. First, a couple of data points. The instance is running on AWS RDS and is on version 13.1. All my timestamps and elapsed times were taken from the postgres log (converte

Re: Dangerous Naming Confusion

On Mon, Mar 29, 2021 at 3:20 PM Adrian Klaver wrote: > On 3/29/21 3:00 PM, Don Seiler wrote: > > > > I'm wondering if this is expected behavior that PG uses the > > dts_orders.order_id value in the subquery "select order_id from > > dts_temp" when dts_temp doesn't have its own order_id column. I

Re: Dangerous Naming Confusion

On 3/29/21 3:00 PM, Don Seiler wrote: Good evening, Please see my gist at https://gist.github.com/dtseiler/9ef0a5e2b1e0efc6a13d5661436d4056 for a complete test case. I tested this on PG 12.6 and 13.2 and observed the same o

Dangerous Naming Confusion

Good evening, Please see my gist at https://gist.github.com/dtseiler/9ef0a5e2b1e0efc6a13d5661436d4056 for a complete test case. I tested this on PG 12.6 and 13.2 and observed the same on both. We were expecting the queries that use dts_temp to only return 3 rows. However the subquery starting at

Re: questions about wraparound

On Mon, 2021-03-29 at 16:58 +0200, Luca Ferrari wrote: > > If there is no activity on a database, its "datfrozenxid" stays > > the same. So, as transaction IDs are consumed, it is getting older > > automatically. That means that even inactive databases will receive > > an anti-wraparound vacuum o

Re: search_path in pg_dump output.

On 3/28/21 11:00 PM, NEERAJ BANSAL wrote: Hi, we are using pg_dump output to compare the schema consistency. But after upgrade to postgres 11.5 we are seeing different behavior of pg_dump intermittently.  As per the https://github.com/postgres/postgres/commit/582edc369cdbd348d68441fc50fa26a84

Re: questions about wraparound

On Mon, Mar 29, 2021 at 11:14 AM Laurenz Albe wrote: > > If there is no activity on a database, its "datfrozenxid" stays > the same. So, as transaction IDs are consumed, it is getting older > automatically. That means that even inactive databases will receive > an anti-wraparound vacuum occasion

Re: questions about wraparound

On Mon, 2021-03-29 at 10:33 +0200, Luca Ferrari wrote: > Another thing that comes into my mind as a doubt is: why are all > databases becoming old? I mean, I'm provoking activity _only_ on > testdb, therefore other database such as template1 are not doing > anything. That one I can answer. If the

Re: questions about wraparound

On Mon, Mar 22, 2021 at 8:56 AM Luca Ferrari wrote: > backend> select datname, age( datfrozenxid), current_setting( > 1: datname = "template0" (typeid = 19, len = 64, typmod > = -1, byval = f) > 2: age = "2138438218" (typeid = 23, len = 4, typmod = -1, byval = t) >

search_path in pg_dump output.

Hi, we are using pg_dump output to compare the schema consistency. But after upgrade to postgres 11.5 we are seeing different behavior of pg_dump intermittently. As per the https://github.com/postgres/postgres/commit/582edc369cdbd348d68441fc50fa26a84afd0c1a pg_dump should reset the schema pat