Re: [GENERAL] Naming conventions for column names

[Michael Paquier]

On Wed, Nov 8, 2017 at 12:41 AM, Alvaro Herrera  wrote:
> Sachin Kotwal wrote:
>> 3. Notify or highlight these changes in release notes because this can
>> break some existing tools and user code.
>
> Notifying people when their tools no longer work with a new server is
> not the problem; they figure that out pretty quickly once they try the
> new version.  The problem is that if you change any names, the
> application developers need to provide version-specific queries that
> work across all the PG versions they want to support.  That leads to
> some pretty horrible code, annoyed developers, bad publicity for Pg
> ("you guys don't love your app developers!"), etc.

This reminds me of pg_stat_activity whose pid column has been renamed,
and this column is widely used for monitoring... My experience on that
is that renaming induces technical debts, but applications would see
queries failing immediately, which is not complicated to grep and fix
properly as frontend code usually has already logic to track the
backend version number, and even if any deeper modification is needed
things like libpq allow to fetch the backend version easily. Column
name of function name changes happen (see xlog -> wal switch in v10),
and community is usually driven by consensus. So if there is a
proposal adopted by a majority of hackers thinking that changing a
column is worth long-term, then it could be considered for
integration. I personally tend to take with a pinch of salt such
proposals though if there are no good reasons behind a switch other
than because it-is-beautiful, so I agree with Álvaro that it is good
to be careful here.
-- 
Michael


-- 
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] Fwd: standby stop replicating, then picked back up

[Michael Paquier]

On Wed, Nov 8, 2017 at 5:17 AM, Laurenz Albe  wrote:
> chris kim wrote:
>> I had a standby hang for a while, not replicating, but then it fixed
>> itself but I'm not sure why it happened in the first place. What would I
>> look into to see why this happened, or any insight into why is greatly
>> appreciated.
>
> You give us precious little information.
>
> If there is nothing suspicious in the log, and hot standby is enabled,
> and the standby is configured appropriately, it could be that a conflicting
> query on the standby block WAL application for a while.

I am understanding here the following: if a standby is stopped for a
long time, would it be able to catch up automatically? This is mainly
a matter of WAL segments recycled on the primary (or a standby for
cascading streaming). In short, when the primary completes two
checkpoints, it recycles or renames past WAL segments in pg_xlog that
it does not need for recovery because it is able to recover to a
consistent state.

If the standby uses a replication slot for recovery, then you could
allow a standby to plug in back as long as the primary's pg_xlog does
not get bloated too much, at which point the partition where pg_xlog
is located would cause the primary to go down because of space
exhaustion. Using a WAL archive can be worthy if standbys are taken
down for a long time though, with a proper recovery command, or a WAL
segment range copy, you could allow a standby to recover from an
earlier point. Strategies to adopt mainly depend on if taking a full
backup is more costly than a range of WAL segments, so the data folder
size of the primary instance matters as a decision-making parameter.
-- 
Michael


-- 
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] idle in transaction, why

[Rob Sargent]

> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer  wrote:
> 
> Rob Sargent schrieb am 06.11.2017 um 23:09:
>> Gosh I wish I could learn to proof-read my posts.
>> My support crew graciously set
>> 
>> idle_transaction_timeout = 1
>> 
>> Now to ponder if I need zero or some large number.
> 
> The unit of that setting is milliseconds (if no unit is specified). 
> zero disables that feature.
> 
> One millisecond seems like an awfully short period to allow a transaction to 
> be idle. 
> 
> I would figure values in "minutes" to be more realistic depending on the 
> workload and characteristics of the application. 
> 
> A transaction that has several seconds of "think time" between individual 
> statements doesn't seem that unrealistic. 
> 
> 
> Thomas
> 
I see I didn’t clarify that the timeout was set in the pgbouncer configuration. 
(I had shown upstream that it was NOT set for the  postgres server.) In 
pgbouncer, the unit is seconds.

-- 
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] Fwd: standby stop replicating, then picked back up

[Laurenz Albe]

chris kim wrote:
> I had a standby hang for a while, not replicating, but then it fixed 
> itself but I'm not sure why it happened in the first place. What would I 
> look into to see why this happened, or any insight into why is greatly 
> appreciated.

You give us precious little information.

If there is nothing suspicious in the log, and hot standby is enabled,
and the standby is configured appropriately, it could be that a conflicting
query on the standby block WAL application for a while.

Yours,
Laurenz Albe



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Fwd: standby stop replicating, then picked back up

[chris kim]

 Forwarded Message 
Subject:standby stop replicating, then picked back up
Date:   Tue, 7 Nov 2017 13:04:18 -0700
From:   chris kim 
To: pgsql-in-gene...@postgresql.org 



Hello,

I had a standby hang for a while, not replicating, but then it fixed
itself but I'm not sure why it happened in the first place. What would I
look into to see why this happened, or any insight into why is greatly
appreciated.

Thanks,

Chris

Re: [GENERAL] Incremental refresh - Materialized view

[John R Pierce]

On 11/6/2017 11:34 PM, Krithika Venkatesh wrote:
Materialized view log is one of the feature in oracle. It creates a 
log in which the changes made to the table are recorded. This log is 
required for an asynchronous materialized view that is refreshed 
incrementally.


I read in the below link about incrementally refreshing the 
materialized view in postgresql:


https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-incremental-refresh-44d1ca742599

Can someone let me how to do incremental refresh using Write Ahead Log



I note that bloggers sample code on github no longer exists.m   I 
suspect it was half baked, and ran into intractable problems.


to do what you want, you would need to implement logical decoding [1] of 
the WAL stream,  you would need to 'understand' the views completely so 
you can tell if a given tuple update affects one of your views or not 
(relatively simple for a view which is just `select fields from table 
where simplecondition`, not so easy for a view which is a N way join 
with complex filtering and/or aggregation, or whatever), then accumulate 
these updates somewhere so your incremental refresh could replay them 
and update the table underlying a given materialized view.


I'm sure i'm not thinking of major aspects complicating this.


[1] 
https://www.postgresql.org/docs/current/static/logicaldecoding-explanation.html




--
john r pierce, recycling bits in santa cruz



--
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] Block duplications in a shared buffers

[pinker]

Thank you Tom, you were right. I needed to group by all the columns:
reldatabase, reltablespace, relforknumber too. Now all of them are unique.
Thank you for clearing this out :)



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
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] Block duplications in a shared buffers

[pinker]

Tom Lane-2 wrote
> Postgres would be completely broken if that were true, because
> modifications made to one copy would fail to propagate to other copies.
> I don't know where your data came from, but it can't be an accurate
> representation of the instantaneous state of the buffer cache.
> 
> ... actually, after looking at your query, I wonder whether the issue
> is that you're failing to include database and tablespace in the
> grouping key.  relfilenode isn't guaranteed unique across directories.
> The fork number can matter, too.

thank you I'll add those columns to my query




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
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] idle in transaction, why

[Rob Sargent]

On 11/07/2017 09:09 AM, Scott Marlowe wrote:

On Tue, Nov 7, 2017 at 7:44 AM, Rob Sargent  wrote:



On Nov 7, 2017, at 12:16 AM, Thomas Kellerer  wrote:

I would figure values in "minutes" to be more realistic depending on the 
workload and characteristics of the application.

A transaction that has several seconds of "think time" between individual 
statements doesn't seem that unrealistic.


I started with the default zero and the save went through perfectly. It takes 
ten minutes so I’ll have a concurrency issue I imagine.

10 minutes is long-ish but if it's not run all the time etc it may be
what you're stuck with. Idle in transaction doesn't necessarily mean
concurrency issues, as long as you're not operating on a whole table
other people need to update as well. I guess now's a good time to
profile what your code is doing, what's take the longest, and see if
maybe you can split that big transaction up into bite sized pieces.
Short story: The save operation in question is insert only. Done in a 
single transaction (currently).


The last run generated 1.7M new 'segments', each of those grouped into 
one of 131K segmentsets (so another 1.7M members), those 131K sets 
recorded as 'outputs'. In production we'll start 22 jobs, one per 
chromosome (no XY) for each pedigree.  We spread those across machines, 
starting roughly at the same time.  Analysis time is measured in days 
and is pretty much linear with chromosome size (and inversely to 
processor count) but pretty sure at some point two of those send their 
payload within minutes of each other.  You know they will.


Do two sets of writes to same table interfere with each other horribly?


--
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] idle in transaction, why

[Scott Marlowe]

On Tue, Nov 7, 2017 at 7:44 AM, Rob Sargent  wrote:
>
>
>> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer  wrote:
>>
>> I would figure values in "minutes" to be more realistic depending on the 
>> workload and characteristics of the application.
>>
>> A transaction that has several seconds of "think time" between individual 
>> statements doesn't seem that unrealistic.
>>
> I started with the default zero and the save went through perfectly. It takes 
> ten minutes so I’ll have a concurrency issue I imagine.

10 minutes is long-ish but if it's not run all the time etc it may be
what you're stuck with. Idle in transaction doesn't necessarily mean
concurrency issues, as long as you're not operating on a whole table
other people need to update as well. I guess now's a good time to
profile what your code is doing, what's take the longest, and see if
maybe you can split that big transaction up into bite sized pieces.


-- 
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] Naming conventions for column names

[Alvaro Herrera]

Sachin Kotwal wrote:

> 3. Notify or highlight these changes in release notes because this can
> break some existing tools and user code.

Notifying people when their tools no longer work with a new server is
not the problem; they figure that out pretty quickly once they try the
new version.  The problem is that if you change any names, the
application developers need to provide version-specific queries that
work across all the PG versions they want to support.  That leads to
some pretty horrible code, annoyed developers, bad publicity for Pg
("you guys don't love your app developers!"), etc.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Block duplications in a shared buffers

[Tom Lane]

pinker  writes:
> I was analysing shared buffers content and noticed that exactly the same
> disk block appears there many times with different or the same usagecount.

Postgres would be completely broken if that were true, because
modifications made to one copy would fail to propagate to other copies.
I don't know where your data came from, but it can't be an accurate
representation of the instantaneous state of the buffer cache.

... actually, after looking at your query, I wonder whether the issue
is that you're failing to include database and tablespace in the
grouping key.  relfilenode isn't guaranteed unique across directories.
The fork number can matter, too.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Block duplications in a shared buffers

[pinker]

Hi All,
I was analysing shared buffers content and noticed that exactly the same
disk block appears there many times with different or the same usagecount.
What's the cause of that? It's because of transactions? 

SELECT
  count(*),
  relfilenode,
  relblocknumber,
  array_agg(usagecount) usagecount,
  array_agg(isdirty)isdirty,
  array_agg(bufferid)   bufferid
FROM fo_main.buffercache
WHERE added = 'some_date'
GROUP BY relfilenode, relblocknumber
HAVING count(*) > 1
ORDER BY 1 DESC;

Sample result:

 




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
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] Naming conventions for column names

[David G. Johnston]

On Mon, Nov 6, 2017 at 10:30 PM, Sachin Kotwal  wrote:

>
> Please committers give their final view on this.
>
>
​They, and others, have - its a "don't want".​

IOW, don't expend any effort since that effort will have been wasted - not
that it would take zero effort to accomplish.

If there is an addition to the system catalogs overview page of the docs
that describes the naming convention I think that would be swell.

David J.

Re: [GENERAL] Incremental refresh - Materialized view

[Melvin Davidson]

On Tue, Nov 7, 2017 at 7:08 AM, Rakesh Kumar 
wrote:

> You have already been informed. PG, as yet, does not allow incremental
> refresh of a MV.  It allows online refresh of a MV, but that it does by
> doing a full table scan of the base table and rebuilding the MV.
>
>
> --
> *From:* Krithika Venkatesh 
> *To:* John R Pierce 
> *Cc:* pgsql-general@postgresql.org
> *Sent:* Tuesday, November 7, 2017 2:35 AM
> *Subject:* Re: [GENERAL] Incremental refresh - Materialized view
>
> Materialized view log is one of the feature in oracle. It creates a log in
> which the changes made to the table are recorded. This log is required for
> an asynchronous materialized view that is refreshed incrementally.
>
> I read in the below link about incrementally refreshing the materialized
> view in postgresql:
>
> https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-
> incremental-refresh-44d1ca742599
>
> Can someone let me how to do incremental refresh using Write Ahead Log
>
> Thanks,
> Krithika
>
>
>
>
>
> On 07-Nov-2017 12:37 PM, "John R Pierce"  wrote:
>
> On 11/6/2017 10:38 PM, Krithika Venkatesh wrote:
>
> I need to implement incremental refresh of materialized view.
>
> Please let me know how to do the incremental refresh of materialized view
> in postgresql 9.5.9 version.
>
> Is there anything similar to materialized view log in postgresql.
>
>
>
> you refresh a materialized view with REFRESH MATERIALIZED VIEW name;
> There's no 'incremental' methods, as views can be quite complex.
>
> I do not know what you mean by 'materialized view log', is this a feature
> of some other database server ?
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mail pref/pgsql-general
> 
>
>
>
>
>
>Please let me know how to do the incremental refresh of materialized view
in postgresql 9.5.9 version.

As previously stated, there is currently no such thing as an incremental
refresh of a materialized view.
I believe what you are looking for is:

*REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_name;*

REFRESH MATERIALIZED VIEW


Specifying CONCURRENTLY with prevent locking of the underlying table(s),
but will extend the
time it takes to complete the refresh.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] idle in transaction, why

[Rob Sargent]

> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer  wrote:
> 
> Rob Sargent schrieb am 06.11.2017 um 23:09:
>> Gosh I wish I could learn to proof-read my posts.
>> My support crew graciously set
>> 
>> idle_transaction_timeout = 1
>> 
>> Now to ponder if I need zero or some large number.
> 
> The unit of that setting is milliseconds (if no unit is specified). 
> zero disables that feature.
> 
> One millisecond seems like an awfully short period to allow a transaction to 
> be idle. 
> 
> I would figure values in "minutes" to be more realistic depending on the 
> workload and characteristics of the application. 
> 
> A transaction that has several seconds of "think time" between individual 
> statements doesn't seem that unrealistic. 
> 
> 
> Thomas
> 
> 
> 
I started with the default zero and the save went through perfectly. It takes 
ten minutes so I’ll have a concurrency issue I imagine. 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] Incremental refresh - Materialized view

[Rakesh Kumar]

You have already been informed. PG, as yet, does not allow incremental refresh 
of a MV.  It allows online refresh of a MV, but that it does by doing a full 
table scan of the base table and rebuilding the MV.



From: Krithika Venkatesh 
To: John R Pierce 
Cc: pgsql-general@postgresql.org
Sent: Tuesday, November 7, 2017 2:35 AM
Subject: Re: [GENERAL] Incremental refresh - Materialized view

Materialized view log is one of the feature in oracle. It creates a log in 
which the changes made to the table are recorded. This log is required for an 
asynchronous materialized view that is refreshed incrementally.

I read in the below link about incrementally refreshing the materialized view 
in postgresql:

https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-incremental-refresh-44d1ca742599

Can someone let me how to do incremental refresh using Write Ahead Log

Thanks,
Krithika





On 07-Nov-2017 12:37 PM, "John R Pierce" 
> wrote:
On 11/6/2017 10:38 PM, Krithika Venkatesh wrote:
I need to implement incremental refresh of materialized view.

Please let me know how to do the incremental refresh of materialized view in 
postgresql 9.5.9 version.

Is there anything similar to materialized view log in postgresql.


you refresh a materialized view with REFRESH MATERIALIZED VIEW name;There's 
no 'incremental' methods, as views can be quite complex.

I do not know what you mean by 'materialized view log', is this a feature of 
some other database server ?



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list 
(pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mail 
pref/pgsql-general