Re: questions about wraparound

[Luca Ferrari]

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 a table locked. This produced a
wraparound in such database, but I was expecting emergency autovacuum
to keep other databases frozen. PostgreSQL 12.5.

Luca

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

[Jagmohan Kaintura]

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 *text *which are being referenced in the *where* clause.

tms_vessel_visit_aggregate_test
tms_owner_cz1qa@zdcq12c2:5434=> \d tms_vessel_visit_aggregate_test
  Table "tms_owner_cz1qa.tms_vessel_visit_aggregate_test"
 Column  |  Type  | Collation |
Nullable | Default
-++---+--+-
 vessel_visit_c  | text   |   | not
null |


tms_vessel_visit_aggregate_bak_test
tms_owner_cz1qa@zdcq12c2:5434=> \d tms_vessel_visit_aggregate_bak_test
Table "tms_owner_cz1qa.tms_vessel_visit_aggregate_bak_test"
 Column  |  Type  | Collation |
Nullable | Default
-++---+--+-
 vessel_visit_c  | text   |   | not
null |
...

tms_vessel_visit_test
tms_owner_cz1qa@zdcq12c2:5434=> \d tms_vessel_visit_test
Table "tms_owner_cz1qa.tms_vessel_visit_test"
Column |  Type  | Collation
| Nullable |   Defa
ult
---++---+--+---
---
 vessel_visit_c| text   |
| not null |


tms_vessel_visit_bak_test
...
 vessel_visit_c| text   |
| not null |


After above modification created a new View TMV_VESSEL_VISIT_TEXT_VERIFY.
Now as the base columns are *text and *typecast is also* text. *We are
getting a good optimum plan as we expected.

The application call which was earlier taking 7-8 seconds for execution is
now taking 900-1100 msec. It got improved by many factors.
>From the application single call view is getting executed 4k times.

 Aggregate  (cost=47.91..47.92 rows=1 width=32) (actual time=0.021..0.024
rows=1 loops=1)
   Buffers: shared hit=5
   ->  Nested Loop  (cost=4.49..47.91 rows=1 width=0) (actual
time=0.019..0.021 rows=0 loops=1)
 Buffers: shared hit=5
 ->  Nested Loop  (cost=0.57..41.83 rows=1 width=38) (actual
time=0.019..0.020 rows=0 loops=1)
   Join Filter: ((t.trgt_vvd_n)::text = "*SELECT*
1".vessel_visit_c)
   Buffers: shared hit=5

*   ->  Index Scan using tms_tdr_pk on tms_tdr t
 (cost=0.29..2.50 rows=1 width=7) (actual time=0.011..0.012 rows=1 loops=1)*
   Index Cond: (tdr_id = '13581258'::numeric)
 Buffers: shared hit=3
   ->  Append  (cost=0.28..39.30 rows=2 width=70) (actual
time=0.005..0.006 rows=0 loops=1)
 Buffers: shared hit=2
 ->  Subquery Scan on "*SELECT* 1"  (cost=0.28..19.65
rows=1 width=70) (actual time=0.002..0.003 rows=0 loops=1)
   Buffers: shared hit=1
   ->  Nested Loop  (cost=0.28..19.64 rows=1
width=6418) (actual time=0.002..0.002 rows=0 loops=1)
 Buffers: shared hit=1

*->  Index Scan using
tms_vessel_visit_test_vessel_visit_c_idx on tms_vessel_visit_test v
 (cost=0.14..9.09 rows=10 width=80) (actual time=0.002..0.002 rows=0
loops=1) *  Buffers: shared hit=1


*->  Index Scan using
tms_vessel_visit_aggregate_test_vessel_visit_c_idx on
tms_vessel_visit_aggregate_test a  (cost=0.14..1.05 rows=1 width=42) (never
executed) *  Index Cond:
(vessel_visit_c = v.vessel_visit_c)
   Filter: (v.vessel_call_nr =
vessel_call_nr)
 ->  Subquery Scan on "*SELECT* 2"  (cost=0.28..19.65
rows=1 width=70) (actual time=0.002..0.002 rows=0 loops=1)
   Buffers: shared hit=1
   ->  Nested Loop  (cost=0.28..19.64 rows=1
width=6404) (actual time=0.002..0.002 rows=0 loops=1)
 Buffers: shared hit=1
 ->  Index Scan using
tms_vessel_visit_bak_test_pkey on tms_vessel_visit_bak_test v_1
 (cost=0.14..9.09 rows=10 width=80)
(actual time=0.002..0.002 rows=0 loops=1)
   Buffers: shared hit=1


* ->  Index Scan using
tms_vessel_visit_aggregate_bak_test_pkey on
tms_vessel_visit_aggregate_bak_test a_1  (cost=0.14..1.05 rows=1 width=42)
(never executed)   Index Cond:
(vessel_visit_c = v_1.vessel_visit_c)*
 Filter: (v_1.vessel_call_nr = vessel_call_nr)

Re: Row not immediately visible after commit

[Steve Baldwin]

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
> postgres-flavoured Aurora product).
>
> b2bc_owner@b2bcreditonline=> select version();
>  version
>
> -
>  PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-11), 64-bit
>
> Steve
>
> On Tue, Mar 30, 2021 at 10:52 AM Adrian Klaver 
> wrote:
>
>> 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 times were taken from
>> > the postgres log (converted to my local tz).
>> >
>> > 2021-03-30 05:47:40.989+11 Session A begins a new transaction
>> > 2021-03-30 05:47:41.006+11 Session A inserts a single row into table A
>> > 2021-03-30 05:47:41.031+11 Session A inserts two rows into table B
>> > 2021-03-30 05:47:41.039+11 Session A commits (duration = 3.022 ms)
>> >
>> > 2021-03-30 05:47:41.082+11 Session B begins a new transaction
>> > 2021-03-30 05:47:41.083+11 Session B fetches one of the inserted rows
>> > from table B
>> > 2021-03-30 05:47:41.085+11 Session B attempts to fetch the inserted row
>> > from table A using the primary key. Fetch returns zero rows.
>> > 2021-03-30 05:47:41.087+11 Session B aborts the transaction with
>> rollback
>> >
>> > 2021-03-30 05:47:42.143+11 Session C begins a new transaction
>> > 2021-03-30 05:47:42.146+11 Session C fetches the same row as session B
>> above
>> > 2021-03-30 05:47:42.228+11 Session C attempts the same query on table A
>> > as session B above. The fetch returns 1 row, and session C continues
>> > processing.
>> >
>> > I can't imagine how Session B could fail to fetch the row from table A
>> > given that the commit has completed prior to Session B starting its
>> > transaction.
>> >
>> > Any suggestions?
>>
>> Ask AWS support.
>>
>> >
>> > Thanks,
>> >
>> > Steve
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>

Re: Row not immediately visible after commit

[Ron]

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();
                                                 version
-
 PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
20150623 (Red Hat 4.8.5-11), 64-bit


Steve

On Tue, Mar 30, 2021 at 10:52 AM Adrian Klaver > wrote:


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 times were taken from
> the postgres log (converted to my local tz).
>
> 2021-03-30 05:47:40.989+11 Session A begins a new transaction
> 2021-03-30 05:47:41.006+11 Session A inserts a single row into table A
> 2021-03-30 05:47:41.031+11 Session A inserts two rows into table B
> 2021-03-30 05:47:41.039+11 Session A commits (duration = 3.022 ms)
>
> 2021-03-30 05:47:41.082+11 Session B begins a new transaction
> 2021-03-30 05:47:41.083+11 Session B fetches one of the inserted rows
> from table B
> 2021-03-30 05:47:41.085+11 Session B attempts to fetch the inserted row
> from table A using the primary key. Fetch returns zero rows.
> 2021-03-30 05:47:41.087+11 Session B aborts the transaction with
rollback
>
> 2021-03-30 05:47:42.143+11 Session C begins a new transaction
> 2021-03-30 05:47:42.146+11 Session C fetches the same row as session
B above
> 2021-03-30 05:47:42.228+11 Session C attempts the same query on table A
> as session B above. The fetch returns 1 row, and session C continues
> processing.
>
> I can't imagine how Session B could fail to fetch the row from table A
> given that the commit has completed prior to Session B starting its
> transaction.
>
> Any suggestions?

Ask AWS support.

>
> Thanks,
>
> Steve


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Angular momentum makes the world go 'round.

Re: Row not immediately visible after commit

[Steve Baldwin]

Thanks Adrian. This is 'vanilla' postgres as far as I know (unlike their
postgres-flavoured Aurora product).

b2bc_owner@b2bcreditonline=> select version();
 version
-
 PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-11), 64-bit

Steve

On Tue, Mar 30, 2021 at 10:52 AM Adrian Klaver 
wrote:

> 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 times were taken from
> > the postgres log (converted to my local tz).
> >
> > 2021-03-30 05:47:40.989+11 Session A begins a new transaction
> > 2021-03-30 05:47:41.006+11 Session A inserts a single row into table A
> > 2021-03-30 05:47:41.031+11 Session A inserts two rows into table B
> > 2021-03-30 05:47:41.039+11 Session A commits (duration = 3.022 ms)
> >
> > 2021-03-30 05:47:41.082+11 Session B begins a new transaction
> > 2021-03-30 05:47:41.083+11 Session B fetches one of the inserted rows
> > from table B
> > 2021-03-30 05:47:41.085+11 Session B attempts to fetch the inserted row
> > from table A using the primary key. Fetch returns zero rows.
> > 2021-03-30 05:47:41.087+11 Session B aborts the transaction with rollback
> >
> > 2021-03-30 05:47:42.143+11 Session C begins a new transaction
> > 2021-03-30 05:47:42.146+11 Session C fetches the same row as session B
> above
> > 2021-03-30 05:47:42.228+11 Session C attempts the same query on table A
> > as session B above. The fetch returns 1 row, and session C continues
> > processing.
> >
> > I can't imagine how Session B could fail to fetch the row from table A
> > given that the commit has completed prior to Session B starting its
> > transaction.
> >
> > Any suggestions?
>
> Ask AWS support.
>
> >
> > Thanks,
> >
> > Steve
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[Adrian Klaver]

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’s some murkiness here. All of my tests 
confirm that, for example, the declaration “interval minute” to second” has the 
same effect as “interval day to second”. But you can’t even write “interval 
month to second” or “interval “year to second”. This suggests that there’s a 
hard boundary (using those words rather loosely) between “months” and
“days”.  This is consistent with this statement from the SQL Standard (1992 
edition):

« There are two classes of intervals. One class, called year-month intervals, 
has an express or implied datetime precision that includes no fields other than 
YEAR and MONTH, though not both are required. The other class, called day-time 
intervals, has an express or implied interval precision that can include any 
fields other than YEAR or MONTH. »

Oracle Database honors this by allowing only two spellings of interval 
declaration “year to month” and “day to second”. But the fact that PostgreSQL 
allows a bare “interval” declaration that allows values of all six fields 
(years, months, days, hours, minutes, and seconds) is at odds with this. (In 
fact, it allows no fewer than _fourteen_ distinct ways to declare an 
interval—but these condense into only seven semantically distinct declarations.


https://www.postgresql.org/docs/12/datatype-datetime.html#DATATYPE-INTERVAL-INPUT

"Also, a combination of years and months can be specified with a dash; 
for example '200-10' is read the same as '200 years 10 months'. (These 
shorter forms are in fact the only ones allowed by the SQL standard, and 
are used for output when IntervalStyle is set to sql_standard.)"


My stock suggestion to anyway going over datatype-datetime.html and 
associated pages is to read them several times, take a break, then read 
them again several times. Then assume you still have not nailed down the 
if, and/or's, and buts and be prepared to go over them again.




Red Adrian’s comment:


Looking for logic in dates/times/calendars is a recipe for a continuous 
pounding headache. Not the least because horological = cultural.


I’m looking for a sufficient, and clear, way to describe the rules of what the 
PostgreSQL implementation actually does. And I want to believe that at least 
some logical thinking informed the design. I rehearsed my argument that a 
genuine, and useful, distinction can be made between the two terms of art in my 
reply to Francisco Olarte. I wrote:


Again if you are looking for logic you are in the wrong place. If you 
really want to know what is going on then I suggest taking a look at the 
source, in particular:


src/backend/utils/adt/datetime.c

Bring aspirin and/or a stiff glass of something medicinal.



« My claim is that there’s a genuine distinction to be drawn in the conceptual 
domain—and that this can be made independently of any particular computer 
system. I might say that “1 day” is simply the same thing as  “24 hours”, in 
the same way that “1 foot” is the same as “12 inches”. But my discussion 
partner might argue with this saying that the length of one day is sometimes 23 
hours and sometimes 25 hours due to the much-rehearsed arguments about DST. 
Here, I’d be thinking horologically and my discussion partner would be thinking 
culturally. Those two terms of art (or other equivalent ones) are useful to 
stop a fist fight breaking out by allowing each discussion partner to 
understand, and label, the other’s mental model—both of which have merit.

Notice that the same argument could be had about the equivalence of “1 minute” 
and “60 seconds” in the light of the “leap second” phenomenon. It just happens 
that when we get to PostgreSQL, its Proleptic Gregorian Calendar implementation 
knows nothing of leap seconds. At least, this is how I interpret “because leap 
seconds are not handled” on the 
https://www.postgresql.org/docs/11/functions-datetime.html page.  »

Here’s an example where (as I believe) I can use these terms to advantage:

When you subtract two timeztamptz values which are greater apart than 24 hours, 
the “days”, “hours”, “minutes”, and “seconds” fields of the resulting interval 
are populated using horological semantics. But when you add an interval value 
to a timeztamptz value, the value of the “days” field is added using cultural 
semantics but the value of the “hours” field is added using horological 
semantics.

This is possibly what lies behind the design choice that the “days” and “hours” 
values are explicitly separately represented.







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Row not immediately visible after commit

[Adrian Klaver]

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 times were taken from 
the postgres log (converted to my local tz).


2021-03-30 05:47:40.989+11 Session A begins a new transaction
2021-03-30 05:47:41.006+11 Session A inserts a single row into table A
2021-03-30 05:47:41.031+11 Session A inserts two rows into table B
2021-03-30 05:47:41.039+11 Session A commits (duration = 3.022 ms)

2021-03-30 05:47:41.082+11 Session B begins a new transaction
2021-03-30 05:47:41.083+11 Session B fetches one of the inserted rows 
from table B
2021-03-30 05:47:41.085+11 Session B attempts to fetch the inserted row 
from table A using the primary key. Fetch returns zero rows.

2021-03-30 05:47:41.087+11 Session B aborts the transaction with rollback

2021-03-30 05:47:42.143+11 Session C begins a new transaction
2021-03-30 05:47:42.146+11 Session C fetches the same row as session B above
2021-03-30 05:47:42.228+11 Session C attempts the same query on table A 
as session B above. The fetch returns 1 row, and session C continues 
processing.


I can't imagine how Session B could fail to fetch the row from table A 
given that the commit has completed prior to Session B starting its 
transaction.


Any suggestions?


Ask AWS support.



Thanks,

Steve



--
Adrian Klaver
adrian.kla...@aklaver.com

Row not immediately visible after commit

[Steve Baldwin]

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 (converted to my local tz).

2021-03-30 05:47:40.989+11 Session A begins a new transaction
2021-03-30 05:47:41.006+11 Session A inserts a single row into table A
2021-03-30 05:47:41.031+11 Session A inserts two rows into table B
2021-03-30 05:47:41.039+11 Session A commits (duration = 3.022 ms)

2021-03-30 05:47:41.082+11 Session B begins a new transaction
2021-03-30 05:47:41.083+11 Session B fetches one of the inserted rows from
table B
2021-03-30 05:47:41.085+11 Session B attempts to fetch the inserted row
from table A using the primary key. Fetch returns zero rows.
2021-03-30 05:47:41.087+11 Session B aborts the transaction with rollback

2021-03-30 05:47:42.143+11 Session C begins a new transaction
2021-03-30 05:47:42.146+11 Session C fetches the same row as session B above
2021-03-30 05:47:42.228+11 Session C attempts the same query on table A as
session B above. The fetch returns 1 row, and session C continues
processing.

I can't imagine how Session B could fail to fetch the row from table A
given that the commit has completed prior to Session B starting its
transaction.

Any suggestions?

Thanks,

Steve

Re: Dangerous Naming Confusion

[David G. Johnston]

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 would
> > have expected an error that the column doesn't exist. Seems very
> > counter-intuitive to think PG would use a column from a different table.
>
> See:
>
>
> https://www.postgresql.org/message-id/pine.lnx.4.56.0308011345320@krusty.credativ.de
>
>
There is also an FAQ entry:

https://wiki.postgresql.org/wiki/FAQ#Why_doesn.27t_PostgreSQL_report_a_column_not_found_error_when_using_the_wrong_name_in_a_subquery.3F

David J.

Re: Dangerous Naming Confusion

[Adrian Klaver]

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 on both.

We were expecting the queries that use dts_temp to only return 3 rows. 
However the subquery starting at line 36 returns ALL 250,000 rows from 
dts_orders. Note that the "order_id" field doesn't exist in the dts_temp 
table, so I'm assuming PG is using the "order_id" field from the 
dts_orders table. If I use explicit table references like in the query 
at line 48, then I get the error I would expect that the "order_id" 
column doesn't exist in dts_temp.


When I use the actual column name "a" for dts_temp, then I get the 3 
rows back as expected.


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 would 
have expected an error that the column doesn't exist. Seems very 
counter-intuitive to think PG would use a column from a different table.


See:

https://www.postgresql.org/message-id/pine.lnx.4.56.0308011345320@krusty.credativ.de



This issue was discovered today when this logic was used in an UPDATE 
and ended up locking all rows in a 5M row table and brought many apps to 
a grinding halt. Thankfully it was caught and killed before it actually 
updated anything.


Thanks,
Don.
--
Don Seiler
www.seiler.us 



--
Adrian Klaver
adrian.kla...@aklaver.com

Dangerous Naming Confusion

[Don Seiler]

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 line 36 returns ALL 250,000 rows from
dts_orders. Note that the "order_id" field doesn't exist in the dts_temp
table, so I'm assuming PG is using the "order_id" field from the dts_orders
table. If I use explicit table references like in the query at line 48,
then I get the error I would expect that the "order_id" column doesn't
exist in dts_temp.

When I use the actual column name "a" for dts_temp, then I get the 3 rows
back as expected.

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 would have expected
an error that the column doesn't exist. Seems very counter-intuitive to
think PG would use a column from a different table.

This issue was discovered today when this logic was used in an UPDATE and
ended up locking all rows in a 5M row table and brought many apps to a
grinding halt. Thankfully it was caught and killed before it actually
updated anything.

Thanks,
Don.
-- 
Don Seiler
www.seiler.us

Re: questions about wraparound

[Laurenz Albe]

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 occasionally.  But that should not have
> > to do anything except advance "datfrozenxid".
> 
> Thanks, but this is exactly my point: since inactive databases are
> getting older, why it appears to me that autovacuum is not freezing
> them? I mean, in my experiment age( datfrozenzid) reports the same age
> for every database, even the inactive ones. And since there were
> inactive databases, I was expecting emergency autovacuum to be able to
> run and freeze them. Or am I wrong?

I didn't follow the rest of the thread, but autovacuum should handle
those databases and advance their "datfrozenxid".

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: search_path in pg_dump output.

[Adrian Klaver]

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/582edc369cdbd348d68441fc50fa26a84afd0c1a 
 
pg_dump should reset the schema path to empty and generate fully 
qualified table names which is not happening in intermittent case.  Same 
can be seen on multiple different servers and many users are impacted 
due to this. Please help me to understand this behavior difference. 
Also, If possible please let me know if it is bug or some configuration 
issue.


First, the current version of 11 is 11.11. In a quick search of the 
release notes from 11.6 --> 11.11 I don't see anything obviously on 
point, still it would worth it to try the latest version.


Second, where is the Postgres code/package coming from?




we have default search_path value in postgresql.conf file.
issue appear 1/5
*Command used to dump schema:*
pg_dump.exe --file=D:\ schmdmp_tmp --no-owner --no-tablespaces 
--schema=hpdpidb_app --schema-only --password --username=xyz --port= 
--host=localhost db_xyz


*In normal cases:*

-- Dumped from database version 11.5

-- Dumped by pg_dump version 11.5

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SELECT pg_catalog.set_config('search_path', '', false);


CREATE VIEW hpdpidb_app.dp_cartridges AS

SELECT crt.uuid

    FROM hpdpidb_app.dp_medmng_cartridge crt;

*Intermittently:*

-- Dumped from database version 11.5

-- Dumped by pg_dump version 11.5

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SELECT pg_catalog.set_config('search_path', 'hpdpidb_app', false);


CREATE VIEW hpdpidb_app.dp_cartridges AS

SELECT crt.uuid

    FROM dp_medmng_cartridge crt;

Thanks,

Neeraj




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: questions about wraparound

[Luca Ferrari]

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 occasionally.  But that should not have
> to do anything except advance "datfrozenxid".

Thanks, but this is exactly my point: since inactive databases are
getting older, why it appears to me that autovacuum is not freezing
them? I mean, in my experiment age( datfrozenzid) reports the same age
for every database, even the inactive ones. And since there were
inactive databases, I was expecting emergency autovacuum to be able to
run and freeze them. Or am I wrong?

Luca

Re: questions about wraparound

[Laurenz Albe]

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 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 occasionally.  But that should not have
to do anything except advance "datfrozenxid".

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: questions about wraparound

[Luca Ferrari]

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)
>  3: current_setting = "5000"(typeid = 25, len =
> -1, typmod = -1, byval = f)
> 
>  1: datname = "testdb"  (typeid = 19, len = 64, typmod = -1, byval = 
> f)
>  2: age = "2138438218"  (typeid = 23, len = 4, typmod = -1, byval = t)
>  3: current_setting = "5000"(typeid = 25, len =
> -1, typmod = -1, byval = f)


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. Why an emergency autovacuum is not freezing such databases
before the wraparound happens?

Luca

search_path in pg_dump output.

[NEERAJ BANSAL]

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 path to empty and generate fully qualified 
table names which is not happening in intermittent case.  Same can be seen on 
multiple different servers and many users are impacted due to this. Please help 
me to understand this behavior difference. Also, If possible please let me know 
if it is bug or some configuration issue.


we have default search_path value in postgresql.conf file.
issue appear 1/5
Command used to dump schema:
pg_dump.exe --file=D:\ schmdmp_tmp --no-owner --no-tablespaces 
--schema=hpdpidb_app --schema-only --password --username=xyz --port= 
--host=localhost db_xyz

In normal cases:
-- Dumped from database version 11.5
-- Dumped by pg_dump version 11.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);

CREATE VIEW hpdpidb_app.dp_cartridges AS
SELECT crt.uuid
   FROM hpdpidb_app.dp_medmng_cartridge crt;

Intermittently:
-- Dumped from database version 11.5
-- Dumped by pg_dump version 11.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', 'hpdpidb_app', false);

CREATE VIEW hpdpidb_app.dp_cartridges AS
SELECT crt.uuid
   FROM dp_medmng_cartridge crt;

Thanks,
Neeraj