[GENERAL] CTE in a Recursive Union

[Joe Wildish]

I have a recursive view that uses a CTE in both the recursive and non-recursive 
operands to the union operator. This CTE is quite complex --- it calls various 
others CTEs and does some aggregations, etc. Looking at the explain plan for 
the view I can see that a "CTE Scan" with a Filter is being performed for the 
recursive call. As one would expect, due to the recursion, it is scanned many 
many times and is killing the performance of the view.

My question is this: are there any ways to make a CTE aware of the underlying 
indexes of its contributing tables? Given the complexity of the CTE, and that 
it is referenced on both sides of the union, I am reluctant to simply substitue 
the definition of the CTE into the FROM clause.

It seems my only option is to create the CTE as a materialized view and create 
appropriate indexes to allow the recursive union to perform acceptably.

Does anyone have other suggestions for how best to achieve a performant query?

Thanks,
-Joe

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

[GENERAL] CTE in a Recursive Union

[Joe Wildish]

I have a recursive view that uses a CTE in both the recursive and non-recursive 
operands to the union operator. This CTE is quite complex --- it calls various 
others CTEs and does some aggregations, etc. Looking at the explain plan for 
the view I can see that a "CTE Scan" with a Filter is being performed for the 
recursive call. As one would expect, due to the recursion, it is scanned many 
many times and is killing the performance of the view.

My question is this: are there any ways to make a CTE aware of the underlying 
indexes of its contributing tables? Given the complexity of the CTE, and that 
it is referenced on both sides of the union, I am reluctant to simply substitue 
the definition of the CTE into the FROM clause.

It seems my only option is to create the CTE as a materialized view and create 
appropriate indexes to allow the recursive union to perform acceptably.

Does anyone have other suggestions for how best to achieve a performant query?

Thanks,
-Joe

-- 
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] Serializable Isolation and read/write conflict with index and different keys

[Thomas Munro]

On Wed, Aug 30, 2017 at 4:28 AM, Luca Looz  wrote:
> I'm trying to use the serializable isolation but i'm getting read/write
> dependencies error even if i have an unique index on the column used and the
> transactions are using different keys.
> For an example see this gist:
> https://gist.github.com/nathanl/f98450014f62dcaf0405394a0955e18e
> Is this an expected behavior?

Take a look at the EXPLAIN plan for the SELECT.  You'll get fine
grained SIREAD locks if it's doing a btree index scan, but table-level
SIREAD locks for other kinds of plans.  Sometimes simple SSI tests can
show a lot of false positives just because of empty tables or missing
statistics (ANALYZE).

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
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] Create Action for psql when NOTIFY Recieved

[Jerry Regan]

Stuart,

Thank you!

I will investigate.

/s/jr
Consultant
Concerto GR
Mobile: 612.208.6601

Concerto - a composition for orchestra and a soloist



> On 29Aug, 2017, at 7:52 AM, Stuart Bishop  wrote:
> 
> On 29 August 2017 at 08:42, Jerry Regan
>  wrote:
>> Tom,
>> 
>> After a few minutes thought…..
>> 
>> /s/jr
>> Consultant
>> Concerto GR
>> Mobile: 612.208.6601
>> 
>> Concerto - a composition for orchestra and a soloist
>> 
>> 
>> 
>> On 28Aug, 2017, at 6:08 PM, Tom Lane  wrote:
>> 
>> "David G. Johnston"  writes:
>> 
>> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
>> jerry.re...@concertoglobalresources.com> wrote:
>> 
>> My concern is how, after LISTENing in psql, I can tell it what to do when
>> the NOTItFY is received.
>> 
>> 
>> As far as I am aware you cannot.
>> 
>> 
>> Yes, and psql is not designed to do anything of its own accord,
>> so I think the answer is really "use another program”.
>> 
>> 
>> psql would be running on *nix.
>> 
>> Let’s suppose for a moment that I piped the output of a psql instance to awk
>> or some similar program, configured to detect the NOTIFY. That program would
>> then spawn a process to actually perform the work, parameters being whatever
>> is part of the NOTIFY. Both this psql instance and the awk script would be
>> dedicated to this task.
>> 
>> Given this is not intended in any way to be production quality code - in
>> fact, it’s intended to deliver XML to the client server for validation
>> (xmllint) in a development/test environment - do you see anything that
>> clearly won’t work?  Also, this would be a very low volume connection.
>> Perhaps one NOTIFY in five minutes - or longer.
>> 
>> Yes, it’s a hack.
> 
> Or crib some code from
> http://initd.org/psycopg/docs/advanced.html#async-notify or
> https://godoc.org/github.com/lib/pq/listen_example , which is probably
> less effort than assembling this collection of hacks and trying to
> make it reliable. Most PostgreSQL APIs have support for notifications.
> 
> 
> -- 
> Stuart Bishop 
> http://www.stuartbishop.net/

[GENERAL] Serializable Isolation and read/write conflict with index and different keys

[Luca Looz]

I'm trying to use the serializable isolation but i'm getting read/write
dependencies error even if i have an unique index on the column used and
the transactions are using different keys.
For an example see this gist:
https://gist.github.com/nathanl/f98450014f62dcaf0405394a0955e18e
Is this an expected behavior?

Re: [GENERAL] Unlogged Crash Detection

[Michael Paquier]

On Tue, Aug 29, 2017 at 11:09 PM, Andres Freund  wrote:
> Huh, but that's not particularly meaningful, is it? That'll just as well
> be the case for a freshly created relation, no?

I have assumed that the OP has some control on the timing of the
relations, using an event trigger for example. There is no perfect
method I am afraid.
-- 
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] Create Action for psql when NOTIFY Recieved

[John McKown]

On Tue, Aug 29, 2017 at 10:21 AM, Daniel Verite 
wrote:

> Jerry Regan wrote:
>
> > I think I could justify the effort to ‘script’ psql. I’m not so sure I
> can
> > justify the effort to write a standalone program.
>
> As a hack around psql, you could have a script that feeds psql
> with "SELECT 1" from time to time and capture only the
> notifications output:
>
>  (echo "listen foo;"; while true; do echo "select 1;"; sleep 1; done) | \
> psql | grep notification
>
> When another session issues NOTIFY foo,  'bar'
>  that output filtered by the above command is, for example:
>
>   Asynchronous notification "foo" with payload "bar" received from
>   server process with PID 20033.
>
> which just needs to be piped into another step that runs your custom
> action.
>

​Sounds like a job for "expect".
https://www.tcl.tk/man/expect5.31/expect.1.html​



>
>
> Best regards,
> --
> Daniel Vérité
>


-- 
Caution! The OP is an hyperpolysyllabicsesquipedalianist and this email may
cause stress to those with hippopotomonstrosesquipedaliophobia.

Maranatha! <><
John McKown

Re: [GENERAL] Create Action for psql when NOTIFY Recieved

[Daniel Verite]

Jerry Regan wrote:

> I think I could justify the effort to ‘script’ psql. I’m not so sure I can
> justify the effort to write a standalone program.

As a hack around psql, you could have a script that feeds psql
with "SELECT 1" from time to time and capture only the
notifications output:

 (echo "listen foo;"; while true; do echo "select 1;"; sleep 1; done) | \
psql | grep notification

When another session issues NOTIFY foo,  'bar'
 that output filtered by the above command is, for example:

  Asynchronous notification "foo" with payload "bar" received from
  server process with PID 20033.

which just needs to be piped into another step that runs your custom
action.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
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] Unlogged Crash Detection

[Andres Freund]

On 2017-08-29 20:19:52 +0900, Michael Paquier wrote:
> On Tue, Aug 29, 2017 at 6:06 PM, Gersner  wrote:
> > I see, interesting.
> 
> Please do not top-post. This is not the recommended way of dealing
> with threads on this mailing list.
> 
> > We have lots of unlogged tables, upon a crash we want to create a
> > feedback/alert that data disappeared.
> >
> > Not very familiar with the internal structure, but is it possible to
> > identify if the current table is the INIT_FORKNUM?
> 
> Using pg_relation_filepath, you can know the path to a relation file
> on disk. So a simple idea would be to use pg_read_binary_file with the
> path of the file and the path of the init fork, which is suffixed with
> "_init", and then a comparison between both. If the data read is the
> same, the relation has been untouched.

Huh, but that's not particularly meaningful, is it? That'll just as well
be the case for a freshly created relation, no?

- Andres


-- 
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] Create Action for psql when NOTIFY Recieved

[Stuart Bishop]

On 29 August 2017 at 08:42, Jerry Regan
 wrote:
> Tom,
>
> After a few minutes thought…..
>
> /s/jr
> Consultant
> Concerto GR
> Mobile: 612.208.6601
>
> Concerto - a composition for orchestra and a soloist
>
>
>
> On 28Aug, 2017, at 6:08 PM, Tom Lane  wrote:
>
> "David G. Johnston"  writes:
>
> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
> jerry.re...@concertoglobalresources.com> wrote:
>
> My concern is how, after LISTENing in psql, I can tell it what to do when
> the NOTItFY is received.
>
>
> As far as I am aware you cannot.
>
>
> Yes, and psql is not designed to do anything of its own accord,
> so I think the answer is really "use another program”.
>
>
> psql would be running on *nix.
>
> Let’s suppose for a moment that I piped the output of a psql instance to awk
> or some similar program, configured to detect the NOTIFY. That program would
> then spawn a process to actually perform the work, parameters being whatever
> is part of the NOTIFY. Both this psql instance and the awk script would be
> dedicated to this task.
>
> Given this is not intended in any way to be production quality code - in
> fact, it’s intended to deliver XML to the client server for validation
> (xmllint) in a development/test environment - do you see anything that
> clearly won’t work?  Also, this would be a very low volume connection.
> Perhaps one NOTIFY in five minutes - or longer.
>
> Yes, it’s a hack.

Or crib some code from
http://initd.org/psycopg/docs/advanced.html#async-notify or
https://godoc.org/github.com/lib/pq/listen_example , which is probably
less effort than assembling this collection of hacks and trying to
make it reliable. Most PostgreSQL APIs have support for notifications.


-- 
Stuart Bishop 
http://www.stuartbishop.net/


-- 
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] Out of memory/corrupted shared memory problem on server

[Tom Lane]

Johann Spies  writes:
> On 25 August 2017 at 13:48, Tom Lane  wrote:
>> Remember that "work_mem" is "work memory per plan node", so a complex
>> query could easily chew up a multiple of that number --- and that's
>> with everything going according to plan.  If, say, the planner
>> underestimates the number of table entries involved in a hash
>> aggregation, the actual consumption might be much larger.

> The main source of this query (doing a lot of calculations) is another
> Materialized View
> with more than 700 million records. I then analyzed that MV and this
> morning the good news was:

> # REFRESH MATERIALIZED VIEW wos_2017_1.citation_window_mv ;
> REFRESH MATERIALIZED VIEW
> Time: 27128469.899 ms

OK, so almost certainly the crash was caused by a hash aggregate
using so much memory that it triggered the OOM killer.  Whether
a hash aggregate's hashtable will stay within work_mem is dependent
on whether the planner correctly predicts the number of entries needed.
Analyzing the input MV must have improved that estimate and led the
planner to choose some other plan.

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

Re: [GENERAL] Unlogged Crash Detection

[Michael Paquier]

On Tue, Aug 29, 2017 at 6:06 PM, Gersner  wrote:
> I see, interesting.

Please do not top-post. This is not the recommended way of dealing
with threads on this mailing list.

> We have lots of unlogged tables, upon a crash we want to create a
> feedback/alert that data disappeared.
>
> Not very familiar with the internal structure, but is it possible to
> identify if the current table is the INIT_FORKNUM?

Using pg_relation_filepath, you can know the path to a relation file
on disk. So a simple idea would be to use pg_read_binary_file with the
path of the file and the path of the init fork, which is suffixed with
"_init", and then a comparison between both. If the data read is the
same, the relation has been untouched. Note that you can only do such
a thing as a superuser, and that the data is read from disk, not from
shared buffers. So that's not perfect, but it give an indication.
-- 
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] Create Action for psql when NOTIFY Recieved

[Jerry Regan]

Tom,

After a few minutes thought…..

/s/jr
Consultant
Concerto GR
Mobile: 612.208.6601

Concerto - a composition for orchestra and a soloist



> On 28Aug, 2017, at 6:08 PM, Tom Lane  wrote:
> 
> "David G. Johnston"  writes:
>> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
>> jerry.re...@concertoglobalresources.com> wrote:
>>> My concern is how, after LISTENing in psql, I can tell it what to do when
>>> the NOTItFY is received.
> 
>> ​As far as I am aware you cannot.
> 
> Yes, and psql is not designed to do anything of its own accord,
> so I think the answer is really "use another program”.

psql would be running on *nix.

Let’s suppose for a moment that I piped the output of a psql instance to awk or 
some similar program, configured to detect the NOTIFY. That program would then 
spawn a process to actually perform the work, parameters being whatever is part 
of the NOTIFY. Both this psql instance and the awk script would be dedicated to 
this task.

Given this is not intended in any way to be production quality code - in fact, 
it’s intended to deliver XML to the client server for validation (xmllint) in a 
development/test environment - do you see anything that clearly won’t work?  
Also, this would be a very low volume connection. Perhaps one NOTIFY in five 
minutes - or longer.

Yes, it’s a hack.

> 
>> ​"​Whenever a command is executed, psql also polls for asynchronous
>> notification events generated by LISTEN and NOTIFY."
> 
> Exactly.  If you don't feed it a command, it just sits there.
> 
>> I suspect the feature request would be something like:
>> \set NOTIFY_PROGRAM './process-notify-request.bash'  (or an equivalent
>> meta-command)
>> And psql would invoke said program and pass the content of the notification
>> payload to it via stdin.
> 
> Such a program could only execute after the next time you give a command
> to psql.  You could maybe imagine feeding it a continuous stream of dummy
> commands, but that's pretty silly (and rather defeats the point of LISTEN,
> which is to *not* eat cycles while waiting).
> 
> This isn't something that could be easily fixed, AFAICS.  Even if we
> wanted to make psql pay attention to asynchronous data arrival, how
> would we get control back from libreadline?  And what would happen
> if the user had typed a partial line of input?
> 
> You really are much better off creating a program that opens its own
> connection to the DB and sits there listening.  psql cannot help you
> meaningfully with this request, and I can't see a way to make it do
> so that wouldn't be a monstrous kluge.
> 
>   regards, tom lane

Re: [GENERAL] Create Action for psql when NOTIFY Recieved

[Jerry Regan]

David,

Thanks for your response!

/s/jr
Consultant
Concerto GR
Mobile: 612.208.6601

Concerto - a composition for orchestra and a soloist



> On 28Aug, 2017, at 5:36 PM, David G. Johnston  
> wrote:
> 
> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan 
>  > wrote:
> My concern is how, after LISTENing in psql, I can tell it what to do when the 
> NOTItFY is received.
> 
> ​As far as I am aware you cannot.  The docs for psql, and its feature set, 
> with respect to LISTEN, are minimal and basically say psql will print out 
> notifications to stdout (this I'm forced to assume or read the code) and will 
> poll for notifications whenever it sends a query to the server.

I’ve tested using LISTEN and NOTIFY in psql. First I issued the LISTEN, then 
when my prompt came back, issued the NOTIFY from psql. This is an example from 
the psql docs, I believe. In any case, that the NOTIFY had occurred was 
reported in the psql window.
> 
> https://www.postgresql.org/docs/current/static/app-psql.html 
> 
> 
> ​"​Whenever a command is executed, psql also polls for asynchronous 
> notification events generated by LISTEN and NOTIFY."
> 
> I suspect the feature request would be something like:
> 
> \set NOTIFY_PROGRAM './process-notify-request.bash'  (or an equivalent 
> meta-command)
Since the database would issue the NOTIFY, not sure \set would be used, but 
otherwise, yes. Or, I may just send information about the inserted row. It 
depends on what psql would need.
> 
> And psql would invoke said program and pass the content of the notification 
> payload to it via stdin.

I’d rather have psql know the program name to invoke, but since I don’t really 
know how all this works, I may have no choice.
> 
> Given what we allow for "\copy" I don't see any technical or conceptual 
> problems with such a feature.  Just needs someone to be its primary author.
> 
> David J.

Re: [GENERAL] Create Action for psql when NOTIFY Recieved

[Jerry Regan]

Tom,

I understand all you’ve said. I was hoping for a different answer. C’est la vie.

I think I could justify the effort to ‘script’ psql. I’m not so sure I can 
justify the effort to write a standalone program.

At least I have an answer.

Thanks!

/s/jr
Consultant
Concerto GR
Mobile: 612.208.6601

Concerto - a composition for orchestra and a soloist



> On 28Aug, 2017, at 6:08 PM, Tom Lane  wrote:
> 
> "David G. Johnston"  writes:
>> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
>> jerry.re...@concertoglobalresources.com> wrote:
>>> My concern is how, after LISTENing in psql, I can tell it what to do when
>>> the NOTItFY is received.
> 
>> ​As far as I am aware you cannot.
> 
> Yes, and psql is not designed to do anything of its own accord,
> so I think the answer is really "use another program".
> 
>> ​"​Whenever a command is executed, psql also polls for asynchronous
>> notification events generated by LISTEN and NOTIFY."
> 
> Exactly.  If you don't feed it a command, it just sits there.
> 
>> I suspect the feature request would be something like:
>> \set NOTIFY_PROGRAM './process-notify-request.bash'  (or an equivalent
>> meta-command)
>> And psql would invoke said program and pass the content of the notification
>> payload to it via stdin.
> 
> Such a program could only execute after the next time you give a command
> to psql.  You could maybe imagine feeding it a continuous stream of dummy
> commands, but that's pretty silly (and rather defeats the point of LISTEN,
> which is to *not* eat cycles while waiting).
> 
> This isn't something that could be easily fixed, AFAICS.  Even if we
> wanted to make psql pay attention to asynchronous data arrival, how
> would we get control back from libreadline?  And what would happen
> if the user had typed a partial line of input?
> 
> You really are much better off creating a program that opens its own
> connection to the DB and sits there listening.  psql cannot help you
> meaningfully with this request, and I can't see a way to make it do
> so that wouldn't be a monstrous kluge.
> 
>   regards, tom lane

Re: [GENERAL] Unlogged Crash Detection

[Gersner]

I see, interesting.

We have lots of unlogged tables, upon a crash we want to create a
feedback/alert that data disappeared.

Not very familiar with the internal structure, but is it possible to
identify if the current table is the INIT_FORKNUM?

Gersner

On Tue, Aug 29, 2017 at 11:27 AM, Michael Paquier  wrote:

> On Tue, Aug 29, 2017 at 5:17 PM, Gersner  wrote:
> > Is there a reliable way to distinguish between an empty unlogged table
> to an
> > unlogged table which has been truncated due to a crash?
>
> Why do you want to make such a difference? At the beginning of a crash
> recovery all the, the existing relation files of unlogged tables are
> all removed, and replaced by the init forknum which represents their
> initial state. You can see by yourself ResetUnloggedRelations &
> friends in reinit.c.
> --
> Michael
>

Re: [GENERAL] Unlogged Crash Detection

[Michael Paquier]

On Tue, Aug 29, 2017 at 5:17 PM, Gersner  wrote:
> Is there a reliable way to distinguish between an empty unlogged table to an
> unlogged table which has been truncated due to a crash?

Why do you want to make such a difference? At the beginning of a crash
recovery all the, the existing relation files of unlogged tables are
all removed, and replaced by the init forknum which represents their
initial state. You can see by yourself ResetUnloggedRelations &
friends in reinit.c.
-- 
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] Performance with high correlation in group by on PK

[Alban Hertroys]

On 28 August 2017 at 21:32, Jeff Janes  wrote:
> On Mon, Aug 28, 2017 at 5:22 AM, Alban Hertroys  wrote:
>>
>> Hi all,
>>
>> It's been a while since I actually got to use PG for anything serious,
>> but we're finally doing some experimentation @work now to see if it is
>> suitable for our datawarehouse. So far it's been doing well, but there
>> is a particular type of query I run into that I expect we will
>> frequently use and that's choosing a sequential scan - and I can't
>> fathom why.
>>
>> This is on:
>>
>>
>> The query in question is:
>> select "VBAK_MANDT", max("VBAK_VBELN")
>>   from staging.etl1_vbak
>>  group by "VBAK_MANDT";
>>
>> This is the header-table for another detail table, and in this case
>> we're already seeing a seqscan. The thing is, there are 15M rows in
>> the table (disk usage is 15GB), while the PK is on ("VBAK_MANDT",
>> "VBAK_VBELN") with very few distinct values for "VBAK_MANDT" (in fact,
>> we only have 1 at the moment!).
>
>
> You need an "index skip-scan" or "loose index scan".  PostgreSQL doesn't
> currently detect and implement them automatically, but you can use a
> recursive CTE to get it to work.  There are some examples at
> https://wiki.postgresql.org/wiki/Loose_indexscan

Thanks Jeff, that's an interesting approach. It looks very similar to
correlated subqueries.

Unfortunately, it doesn't seem to help with my issue. The CTE is
indeed fast, but when querying the results from the 2nd level ov the
PK with the CTE results, I'm back at a seqscan on pdw2_vbak again.

Just the CTE plan is in skipScan-top.sql.
The complete plan is in skipScan-full.sql

Note: I cloned the original etl1_vbak table into a new
pdw2_vbak table that has varchars instead of chars, which reduced
the table size to just over half the original's size. Hence the
different table names, but the behaviour for this particular issue is
the same between them.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

warehouse=# explain (analyze, buffers)
with recursive t as (
select min("VBAK_MANDT") as "VBAK_MANDT" from staging.pdw2_vbak
union all
select (select min("VBAK_MANDT") as "VBAK_MANDT" from staging.pdw2_vbak 
where "VBAK_MANDT" > t."VBAK_MANDT") from t where t."VBAK_MANDT" is not null
) select "VBAK_MANDT" from t;

   QUERY PLAN  
-
 CTE Scan on t  (cost=98.31..100.33 rows=101 width=32) (actual 
time=0.031..0.054 rows=2 loops=1)
   Buffers: shared hit=9
   CTE t
 ->  Recursive Union  (cost=0.73..98.31 rows=101 width=32) (actual 
time=0.029..0.052 rows=2 loops=1)
   Buffers: shared hit=9
   ->  Result  (cost=0.73..0.74 rows=1 width=32) (actual 
time=0.029..0.029 rows=1 loops=1)
 Buffers: shared hit=5
 InitPlan 3 (returns $1)
   ->  Limit  (cost=0.56..0.73 rows=1 width=32) (actual 
time=0.026..0.027 rows=1 loops=1)
 Buffers: shared hit=5
 ->  Index Only Scan using pdw2_vbak_pkey on 
pdw2_vbak pdw2_vbak_1  (cost=0.56..2375293.75 rows=14214332 width=32) 
(actual time=0.024..0.024 rows=1 loops=1)
   Index Cond: ("VBAK_MANDT" IS NOT NULL)
   Heap Fetches: 1
   Buffers: shared hit=5
   ->  WorkTable Scan on t t_1  (cost=0.00..9.56 rows=10 width=32) 
(actual time=0.009..0.010 rows=0 loops=2)
 Filter: ("VBAK_MANDT" IS NOT NULL)
 Rows Removed by Filter: 0
 Buffers: shared hit=4
 SubPlan 2
   ->  Result  (cost=0.93..0.94 rows=1 width=32) (actual 
time=0.015..0.015 rows=1 loops=1)
 Buffers: shared hit=4
 InitPlan 1 (returns $3)
   ->  Limit  (cost=0.56..0.93 rows=1 width=32) (actual 
time=0.013..0.013 rows=0 loops=1)
 Buffers: shared hit=4
 ->  Index Only Scan using pdw2_vbak_pkey 
on pdw2_vbak  (cost=0.56..1732075.91 rows=4738111 width=32) (actual 
time=0.012..0.012 rows=0 loops=1)
   Index Cond: (("VBAK_MANDT" IS NOT NULL) 
AND ("VBAK_MANDT" > t_1."VBAK_MANDT"))
   Heap Fetches: 0
   Buffers: shared hit=4
 Planning time: 0.346 ms
 Execution time: 0.100 ms
(30 rows)
warehouse=# explain (analyze, buffers)
with recursive t as (
select min("VBAK_MANDT") as "VBAK_MANDT" from staging.pdw2_vbak
union all
select (select min("VBAK_MANDT") as 

[GENERAL] Unlogged Crash Detection

[Gersner]

Is there a reliable way to distinguish between an empty unlogged table to
an unlogged table which has been truncated due to a crash?

Gersner.

Re: [GENERAL] Out of memory/corrupted shared memory problem on server

[Johann Spies]

On 25 August 2017 at 13:48, Tom Lane  wrote:

> How complex is "complex"?  I can think of two likely scenarios:
> 1. You've stumbled across some kind of memory-leak bug in Postgres.
> 2. The query's just using too much memory.  In this connection, it's
> not good that you've got
>> work_mem = 2GB
> Remember that "work_mem" is "work memory per plan node", so a complex
> query could easily chew up a multiple of that number --- and that's
> with everything going according to plan.  If, say, the planner
> underestimates the number of table entries involved in a hash
> aggregation, the actual consumption might be much larger.
>
> My first move would be to reduce work_mem by an order of magnitude
> or two.  If that doesn't help, check the plan for the view's query
> and see if it contains any hash aggregation steps --- if so, does
> "set enable_hashagg = off" help?  (Also, make sure the view's input
> tables have been ANALYZEd recently.)
>
> If none of that helps, we should investigate the memory-leak-bug
> theory.  One thing you could do in that direction is to run
> the postmaster with a "ulimit -v" size less than what will trigger
> the ire of the OOM killer, so that the query encounters a normal
> ENOMEM error rather than SIGKILL when it's eaten too much memory.
> That should result in it dumping a memory consumption map to stderr,
> which would give some clue where the problem is.  We'd need to see
> that map as well as details about your query to make progress.


Thanks Tom and Christoph Moench-Tegeder.

I first tried to refresh it after bringing down the work_mem to 1 GB.
It failed again.
The main source of this query (doing a lot of calculations) is another
Materialized View
with more than 700 million records. I then analyzed that MV and this
morning the good news was:

# REFRESH MATERIALIZED VIEW wos_2017_1.citation_window_mv ;
REFRESH MATERIALIZED VIEW
Time: 27128469.899 ms

Regards
Johann
-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)


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