Re: [GENERAL] Does this perf output seem 'normal'?

[Jeff Janes]

On Apr 26, 2016 4:29 PM, "Peter Devoy"  wrote:
>
> Hi all,
>
> I am trying to work out why a piece of software, Mapnik, is executing
> slowly.  All it is doing is loading a config file which causes about
> 12 preparation queries (i.e. with LIMIT 0) to be executed.  I can see
> from pg_stat_statements these only take ~1ms in their totality.
>
> So next I ran "perf record -a -g sleep 0.4" and quickly loaded my PHP
> page to start the process (I am interfacing with mapnik via a PHP
> Extension), this is a sample of the result:
>
>
https://cloud.githubusercontent.com/assets/4375326/14836015/08b0a8b4-0c05-11e6-96e5-f1836497cdfe.png

I'm no expert in perf, but to me that looks like the profile of a system
which is bored.  Which matches your description, of spending 0.4 seconds
watching something which only takes 0.06 seconds to happen.  If you really
want to profile this, you should fire it off in a tight loop, using wget or
ab2 or curl.

Cheers,

Jeff

Re: [GENERAL] Slow join over three tables

[David Rowley]

On 27 April 2016 at 11:27, Tim van der Linden  wrote:
> Hi all
>
> I have asked this question in a somewhat different form on the DBA 
> Stackexchange site, but without much luck 
> (https://dba.stackexchange.com/questions/136423/postgresql-slow-join-on-three-large-tables).
>  So I apologize for possible double posting, but I hope this might get a 
> better response on the mailing list.
>
> I'm joining three fairly large tables together, and it is slow. The tables 
> are:
>
> - "reports": 6 million rows
> - "report_drugs": 20 million rows
> - "report_adverses": 20 million rows
>
> The table "reports" holds main report data and has a primary key column "id". 
> The other two tables have a foreign key to that table with "rid". It are 
> those columns that I use to join them together.
>
> All tables have indexes on the "id"/"rid" columns and on the "drug"/"adverse" 
> columns.
>
> The query:
>
> SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug
> FROM reports r
> JOIN report_drugs d ON d.rid = r.id
> JOIN report_adverses a ON a.rid = r.id
> WHERE a.adverse = ANY (ARRAY['back pain - nonspecific', 'nonspecific back 
> pain', 'back pain'])
> AND d.drug = ANY (ARRAY[359, 360, 361, 362, 363]) ORDER BY r.created;
>
> The plan:
>
> Sort  (cost=105773.63..105774.46 rows=333 width=76) (actual 
> time=5143.162..5143.185 rows=448 loops=1)
>Sort Key: r.created
>Sort Method: quicksort  Memory: 60kB
>->  Nested Loop  (cost=1.31..105759.68 rows=333 width=76) (actual 
> time=54.784..5142.872 rows=448 loops=1)
>  Join Filter: (d.rid = a.rid)
>  ->  Nested Loop  (cost=0.87..94657.59 rows=14005 width=72) (actual 
> time=0.822..2038.952 rows=14199 loops=1)
>  ->  Index Scan using report_drugs_drug_idx on report_drugs d  
> (cost=0.44..500.28 rows=14005 width=31) (actual time=0.669..3.900 rows=14199 
> loops=1)
>Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[]))
>  ->  Index Scan using reports_id_key on reports r  (cost=0.43..6.71 
> rows=1 width=41) (actual time=0.143..0.143 rows=1 loops=14199)
>Index Cond: (id = d.rid)
>  ->  Index Scan using report_adverses_rid_idx on report_adverses a  
> (cost=0.44..0.78 rows=1 width=12) (actual time=0.218..0.218 rows=0 
> loops=14199)
>Index Cond: (rid = r.id)
>Filter: (adverse = ANY ('{"back pain - nonspecific","nonspecific 
> back pain","back pain"}'::text[]))
>Rows Removed by Filter: 5
> Planning time: 13.994 ms
> Execution time: 5143.235 ms
>
> This takes well over 5 seconds, which to me, feels much too slow.
> If I query each table directly with the same conditions, thus:
>
> SELECT reason
> FROM report_drugs
> WHERE drug = ANY (ARRAY[359, 360, 361, 362, 363]);
>
> I get:
>
> Index Scan using report_drugs_drug_idx on report_drugs  (cost=0.44..500.28 
> rows=14005 width=27) (actual time=0.621..4.510 rows=14199 loops=1)
>   Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[]))
> Planning time: 6.939 ms
> Execution time: 4.759 ms
>
> Under 5 ms. The same goes for querying the "adverse" column in the 
> "report_adverses" table: under 20 ms.

I'm not sure why you're comparing this to the join plan above. They're
very different, I can only imagine that it' because you've not quite
understood what the EXPLAIN output is saying.

>  ->  Nested Loop  (cost=0.87..94657.59 rows=14005 width=72) (actual 
> time=0.822..2038.952 rows=14199 loops=1)
>  ->  Index Scan using report_drugs_drug_idx on report_drugs d  
> (cost=0.44..500.28 rows=14005 width=31) (actual time=0.669..3.900 rows=14199 
> loops=1)
>Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[]))
>  ->  Index Scan using reports_id_key on reports r  (cost=0.43..6.71 
> rows=1 width=41) (actual time=0.143..0.143 rows=1 loops=14199)
>Index Cond: (id = d.rid)

This is a parameterised nested loop. This means that the inner side of
the loop (reports), is parameterised by the outerside of the loop, you
can see the d.rid in the Index Cond. This means that the
reports_id_key index is looked up 14199 times. You can see from the
plan that the nested loop here took 2038.952 milliseconds to complete,
so about 0.144 ms per lookup, not too bad, right?

The alternative to a parameterised nested loop plan is that a Hash
Join plan is generated. If you imagine what would happen here, likely
the matching report_drugs records would be hashed, then the outer side
of the join would then perform a SeqScan over the entire 6 million
reports records... probably not better. You can test this yourself
with; SET enable_nestloop = 0; you might need to SET enable_mergejoin
= 0; too. I imagine it'll be slower.

Likely the only way to speed this up would be to create indexes;

create index on reports (id, age, gender, created);

the above might allow an index only scan, which should speed up the
nested loop a bit. This will only be useful if you're never going to

Re: [GENERAL] Slow join over three tables

[Sameer Kumar]

On Wed, 27 Apr 2016 07:28 Tim van der Linden,  wrote:

> Hi all
>
> I have asked this question in a somewhat different form on the DBA
> Stackexchange site, but without much luck (
> https://dba.stackexchange.com/questions/136423/postgresql-slow-join-on-three-large-tables).
> So I apologize for possible double posting, but I hope this might get a
> better response on the mailing list.
>
> I'm joining three fairly large tables together, and it is slow. The tables
> are:
>
> - "reports": 6 million rows
> - "report_drugs": 20 million rows
> - "report_adverses": 20 million rows
>
> The table "reports" holds main report data and has a primary key column
> "id". The other two tables have a foreign key to that table with "rid". It
> are those columns that I use to join them together.
>
> All tables have indexes on the "id"/"rid" columns and on the
> "drug"/"adverse" columns.
>
> The query:
>
> SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug
> FROM reports r
> JOIN report_drugs d ON d.rid = r.id
> JOIN report_adverses a ON a.rid = r.id
> WHERE a.adverse = ANY (ARRAY['back pain - nonspecific', 'nonspecific back
> pain', 'back pain'])
> AND d.drug = ANY (ARRAY[359, 360, 361, 362, 363]) ORDER BY r.created;
>
> The plan:
>
> Sort  (cost=105773.63..105774.46 rows=333 width=76) (actual
> time=5143.162..5143.185 rows=448 loops=1)
>Sort Key: r.created
>Sort Method: quicksort  Memory: 60kB
>->  Nested Loop  (cost=1.31..105759.68 rows=333 width=76) (actual
> time=54.784..5142.872 rows=448 loops=1)
>  Join Filter: (d.rid = a.rid)
>  ->  Nested Loop  (cost=0.87..94657.59 rows=14005 width=72) (actual
> time=0.822..2038.952 rows=14199 loops=1)
>

Quite clearly the nested loop joins are the most costly operations here.


 ->  Index Scan using report_drugs_drug_idx on report_drugs d
> (cost=0.44..500.28 rows=14005 width=31) (actual time=0.669..3.900
> rows=14199 loops=1)
>Index Cond: (drug = ANY
> ('{359,360,361,362,363}'::integer[]))
>  ->  Index Scan using reports_id_key on reports r
> (cost=0.43..6.71 rows=1 width=41) (actual time=0.143..0.143 rows=1
> loops=14199)
>Index Cond: (id = d.rid)
>  ->  Index Scan using report_adverses_rid_idx on report_adverses a
> (cost=0.44..0.78 rows=1 width=12) (actual time=0.218..0.218 rows=0
> loops=14199)
>Index Cond: (rid = r.id)
>Filter: (adverse = ANY ('{"back pain -
> nonspecific","nonspecific back pain","back pain"}'::text[]))
>Rows Removed by Filter: 5
>

I suppose. It might help if the filters are performed before the join. I am
not an expert on optimizer but I guess it might help if you change the join
order and add duplicate conditions for reports-

SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug
FROM report_drugs d
JOIN report_adverses a ON a.rid = d.rid
JOIN reports r ON d.rid = r.id
WHERE a.adverse = ANY (ARRAY['back pain - nonspecific', 'nonspecific back
pain', 'back pain'])
AND d.drug = ANY (ARRAY[359, 360, 361, 362, 363]) ORDER BY r.created;


OR since you are using INNER JOIN, (As far as I understand the concept of
joins) it won't hurt the result set if the where clause is pushed into the
INNER JOIN criteria-

SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug
FROM report_drugs d
JOIN report_adverses a ON a.rid = d.rid AND
a.adverse = ANY (ARRAY['back pain - nonspecific', 'nonspecific back pain',
'back pain'])
AND d.drug = ANY (ARRAY[359, 360, 361, 362, 363]) ORDER BY r.created;
JOIN reports r ON d.rid = r.id;


Planning time: 13.994 ms
> Execution time: 5143.235 ms
>
> This takes well over 5 seconds, which to me, feels much too slow.
>

Well given your hardware (which seems to be pretty decent), I am not sure
if this is an unfair expectation.


> If I query each table directly with the same conditions, thus:
>
> SELECT reason
> FROM report_drugs
> WHERE drug = ANY (ARRAY[359, 360, 361, 362, 363]);
>
> I get:
>
> Index Scan using report_drugs_drug_idx on report_drugs  (cost=0.44..500.28
> rows=14005 width=27) (actual time=0.621..4.510 rows=14199 loops=1)
>   Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[]))
> Planning time: 6.939 ms
> Execution time: 4.759 ms
>
> Under 5 ms. The same goes for querying the "adverse" column in the
> "report_adverses" table: under 20 ms.
>

> This indicates to me that indeed the join itself causes a major
> performance bottleneck.
>
> I'm running the cluster from an SSD drive, as a traditional HDD could not
> even manage the query in under 5 minutes. The system has a total memory of
> 24 GB, runs on Debian and uses an 4Ghz 8 core i7-4790 processor.
>
> Some important postgresql.conf readouts:
>
> - shared_buffers = 4GB
> - work_mem = 64MB
> - maintenance_work_mem = 1GB
> - checkpoint_segments = 50
> - checkpoint_completion_target = 0.9
> - autovacuum = on
>
> Is there something I am missing here? Any help on getting this join faster
> is much appreciated.
>
> Cheers,
> Tim
>
>
> --

[GENERAL] Slow join over three tables

[Tim van der Linden]

Hi all

I have asked this question in a somewhat different form on the DBA 
Stackexchange site, but without much luck 
(https://dba.stackexchange.com/questions/136423/postgresql-slow-join-on-three-large-tables).
 So I apologize for possible double posting, but I hope this might get a better 
response on the mailing list.

I'm joining three fairly large tables together, and it is slow. The tables are:

- "reports": 6 million rows
- "report_drugs": 20 million rows
- "report_adverses": 20 million rows

The table "reports" holds main report data and has a primary key column "id". 
The other two tables have a foreign key to that table with "rid". It are those 
columns that I use to join them together.

All tables have indexes on the "id"/"rid" columns and on the "drug"/"adverse" 
columns.

The query:

SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug
FROM reports r
JOIN report_drugs d ON d.rid = r.id
JOIN report_adverses a ON a.rid = r.id 
WHERE a.adverse = ANY (ARRAY['back pain - nonspecific', 'nonspecific back 
pain', 'back pain']) 
AND d.drug = ANY (ARRAY[359, 360, 361, 362, 363]) ORDER BY r.created;

The plan:

Sort  (cost=105773.63..105774.46 rows=333 width=76) (actual 
time=5143.162..5143.185 rows=448 loops=1)
   Sort Key: r.created
   Sort Method: quicksort  Memory: 60kB
   ->  Nested Loop  (cost=1.31..105759.68 rows=333 width=76) (actual 
time=54.784..5142.872 rows=448 loops=1)
 Join Filter: (d.rid = a.rid)
 ->  Nested Loop  (cost=0.87..94657.59 rows=14005 width=72) (actual 
time=0.822..2038.952 rows=14199 loops=1)
 ->  Index Scan using report_drugs_drug_idx on report_drugs d  
(cost=0.44..500.28 rows=14005 width=31) (actual time=0.669..3.900 rows=14199 
loops=1)
   Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[]))
 ->  Index Scan using reports_id_key on reports r  (cost=0.43..6.71 
rows=1 width=41) (actual time=0.143..0.143 rows=1 loops=14199)
   Index Cond: (id = d.rid)
 ->  Index Scan using report_adverses_rid_idx on report_adverses a  
(cost=0.44..0.78 rows=1 width=12) (actual time=0.218..0.218 rows=0 loops=14199)
   Index Cond: (rid = r.id)
   Filter: (adverse = ANY ('{"back pain - nonspecific","nonspecific 
back pain","back pain"}'::text[]))
   Rows Removed by Filter: 5
Planning time: 13.994 ms
Execution time: 5143.235 ms

This takes well over 5 seconds, which to me, feels much too slow.
If I query each table directly with the same conditions, thus:

SELECT reason
FROM report_drugs 
WHERE drug = ANY (ARRAY[359, 360, 361, 362, 363]);

I get:

Index Scan using report_drugs_drug_idx on report_drugs  (cost=0.44..500.28 
rows=14005 width=27) (actual time=0.621..4.510 rows=14199 loops=1)
  Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[]))
Planning time: 6.939 ms
Execution time: 4.759 ms

Under 5 ms. The same goes for querying the "adverse" column in the 
"report_adverses" table: under 20 ms.

This indicates to me that indeed the join itself causes a major performance 
bottleneck.

I'm running the cluster from an SSD drive, as a traditional HDD could not even 
manage the query in under 5 minutes. The system has a total memory of 24 GB, 
runs on Debian and uses an 4Ghz 8 core i7-4790 processor.

Some important postgresql.conf readouts:

- shared_buffers = 4GB
- work_mem = 64MB
- maintenance_work_mem = 1GB
- checkpoint_segments = 50
- checkpoint_completion_target = 0.9
- autovacuum = on

Is there something I am missing here? Any help on getting this join faster is 
much appreciated.

Cheers,
Tim


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

[GENERAL] Does this perf output seem 'normal'?

[Peter Devoy]

Hi all,

I am trying to work out why a piece of software, Mapnik, is executing
slowly.  All it is doing is loading a config file which causes about
12 preparation queries (i.e. with LIMIT 0) to be executed.  I can see
from pg_stat_statements these only take ~1ms in their totality.

So next I ran "perf record -a -g sleep 0.4" and quickly loaded my PHP
page to start the process (I am interfacing with mapnik via a PHP
Extension), this is a sample of the result:

https://cloud.githubusercontent.com/assets/4375326/14836015/08b0a8b4-0c05-11e6-96e5-f1836497cdfe.png

The PHP page returns in ~60ms (well within the 400ms used for the perf
recording).  If possible, please can anyone with more familiarity with
perf and Postgres tell me whether the result of the recording looks
healthy?

I am wondering if there is some problem with the way in which Mapnik
is implementing libpq -- I already moved from Mapnik 2.2 because it
was effectively DOSing Postgres by leaving client connections open.

Any help would be greatly appreciated.


Kind regards

Peter


-- 
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] psql color hostname prompt

[Cal Heldenbrand]

Would it be reasonable to just take the simple approach with same algorithm
I used in the shell script?  Basically:  If the psql client uses a local
UNIX domain socket, or localhost TCP connection, use the string output by
"hostname" system command.  From the C perspective, this is just calling
the uname(2) and/or gethostname(2) system call.

All other remote connections would then fall back to the current %M
functionality.


---
Cal Heldenbrand
   Web Operations at FBS
   Creators of flexmls ® and Spark Platform

   c...@fbsdata.com

On Tue, Apr 26, 2016 at 11:02 AM, Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> 2)  %M vs shell call
>>
>> %M on when connected to the local machine displays the string "[local]"
>> which I didn't like.  I wanted a real hostname to show no matter which
>> client/server pair I was using.  Zero chance for mistaken commands on the
>> wrong host.  Many times we ssh to a remote server, then run psql locally.
>>
>> Perhaps the more elegant route here, is to change psql's behavior with %M
>> when connected to the local machine?  (This would also solve point #3)
>>
>>
> There is a basic problem - what is the hostname?
>
> 1.2.3.4? db23.example.com? server42? 127.0.0.1? 192.168.54.23? Those
> could all be the same PostgreSQL instance, all different or some mix. And
> we haven't even considered local Unix connections, servers running on
> different ports and the fact that localhost is machine specific and
> non-fully-qualified names that depend on resolver search orders.
>
> One possible solution would be to essentially do the reverse of
> application_name. I.e. do something along the lines of adding a server-side
> parameter to specify the name of the server instance that would be
> exchanged in the startup handshake and made available to client processes.
>
> I could see some value in that but can't speak to the work and possible
> wire-protocol breakage that might be involved relative to the benefits.
>
> Cheers,
> Steve
>
>

Re: [GENERAL] truncate table getting blocked

[Mike Sofen]

From: Jayadevan M  Sent: Tuesday, April 26, 2016 6:32 AM



Hello,

 

I have a python script. It opens a cursor…

 

Thanks,

Jayadevan

 

 

 

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[George Neuner]

On Mon, 25 Apr 2016 21:48:44 -0400, Adam Brusselback
 wrote:

>>It is not difficult to simulate column store in a row store system if
>>you're willing to decompose your tables into (what is essentially)
>>BCNF fragments.  It simply is laborious for designers and programmers.
>
>I could see a true column store having much better performance than
>tricking a row based system into it.  Just think of the per-row overhead we
>currently have at 28 bytes per row.  Breaking up data manually like that
>may help a little, but if you don't have a very wide table to begin with,
>it could turn out you save next to nothing by doing so.  A column store
>wouldn't have this issue, and could potentially have much better
>performance.

A column store must be able to distinguish entries in the column
[which may be non-unique] as well as join the columns of the
fragmented virtual table to reconstruct its rows.  

These requirements dictate that a "column" be at least a triple:

{ id, table_row, data }

so there is no space saving WRT row store - the opposite in fact:
column store usually requires more space.

Column store enhances performance mainly by not fetching and not
caching unused data.  And standard practices like controlling the
physical locations of tables help both row and column store systems.

George



-- 
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] psql color hostname prompt

[Steve Crawford]

>
> 2)  %M vs shell call
>
> %M on when connected to the local machine displays the string "[local]"
> which I didn't like.  I wanted a real hostname to show no matter which
> client/server pair I was using.  Zero chance for mistaken commands on the
> wrong host.  Many times we ssh to a remote server, then run psql locally.
>
> Perhaps the more elegant route here, is to change psql's behavior with %M
> when connected to the local machine?  (This would also solve point #3)
>
>
There is a basic problem - what is the hostname?

1.2.3.4? db23.example.com? server42? 127.0.0.1? 192.168.54.23? Those could
all be the same PostgreSQL instance, all different or some mix. And we
haven't even considered local Unix connections, servers running on
different ports and the fact that localhost is machine specific and
non-fully-qualified names that depend on resolver search orders.

One possible solution would be to essentially do the reverse of
application_name. I.e. do something along the lines of adding a server-side
parameter to specify the name of the server instance that would be
exchanged in the startup handshake and made available to client processes.

I could see some value in that but can't speak to the work and possible
wire-protocol breakage that might be involved relative to the benefits.

Cheers,
Steve

Re: [GENERAL] psql color hostname prompt

[Cal Heldenbrand]

Thanks for the input everyone.  I'll try to comment on each discussion
point:

1) garbled output in large queries

I messed around with a few things, and have not been able to recreate any
issues.  Can you provide a test case for this?  Also, any other interesting
things about your terminal, like your $TERM setting, locale, etc.  (I'm
using xterm with UTF-8, and KDE's Konsole app)


2)  %M vs shell call

%M on when connected to the local machine displays the string "[local]"
which I didn't like.  I wanted a real hostname to show no matter which
client/server pair I was using.  Zero chance for mistaken commands on the
wrong host.  Many times we ssh to a remote server, then run psql locally.

Perhaps the more elegant route here, is to change psql's behavior with %M
when connected to the local machine?  (This would also solve point #3)

3)  a forked process for every prompt

While this also isn't very elegant, it seems to work fine.  It's not
something to worry about these days when the server has 40 cores.  ;-)
But, I've noticed that different distros store lsof in different locations,
so per-environment tweaking might be necessary.

It would be nice if there was a way to do some kind of templating script
with the psqlrc file.  Something that would dynamically generate the "\set
PROMPT" commands on psql startup, rather than calling out to a shell every
command.  (I'm thinking along the lines of ERB for Ruby, Django for Python,
etc.)

But again, I think the more elegant approach is to alter the %M logic.

Any thoughts?

Thanks!

--Cal


On Mon, Apr 25, 2016 at 9:39 AM, Francisco Olarte 
wrote:

> Hi:
>
> On Mon, Apr 25, 2016 at 4:04 PM, Achilleas Mantzios
>  wrote:
> > Hello, have done that, looked really nice, but unfortunately this
> resulted
> > in a lot of garbled output, in case of editing functions, huge queries,
> up
> > arrows, etc...
>
> Did you  use %[ %] to delimit non-printing sequences as Cal did? I've
> tested his prompt ( substituting 'echo tarari' for his sh script ) and
> it works fine ( at least in xfce4-terminal / ubuntu / 6 terminal lines
> long input line ). I've seen a lot of color prompts worked by
> forgetting them ( even when the doc for them specially speaks of color
> changes ).
> Frnacisco Olarte.
>
>
> --
> 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] Problems running the WorkerSpi sample

[Pavel Stehule]

2016-04-26 15:48 GMT+02:00 :

> Solved. The sample can indeed be loaded at startup (although it emits some
> strange LOG messages).
>
>
>
> But to load it dynamically requires this SQL:
>
>
>
> CREATE OR REPLACE FUNCTION worker_spi_launch(i INT) RETURNS INT
>
> AS '' LANGUAGE C;
>
> SELECT * FROM worker_spi_launch();
>
>
>
> It would be helpful to add this to the documentation. From this point on,
> it looks pretty straightforward, but getting here was not easy.
>
>
>
> I’m still wondering how to do a clean restart of the server without
> pg_ctl. Restarting the service is a bit brutal.
>

server, service are equivalent for Postgres. There are not any difference.

Regards

Pavel


>
>
> Regards
>
> David M Bennett FACS
> *--*
>
> *Andl - A New Database Language - andl.org *
>
>
>
>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] *On Behalf Of *da...@andl.org
> *Sent:* Tuesday, 26 April 2016 4:04 PM
> *To:* pgsql-general@postgresql.org
> *Subject:* [GENERAL] Problems running the WorkerSpi sample
>
>
>
> I’ve been trying to get the WorkerSpi sample working on a Windows
> installation, VS 2015, x64. So far, the following problems.
>
>
>
> 1.   Does not compile OOB. Added the following lines.
>
> PGDLLEXPORT void _PG_init(void);
>
> PGDLLEXPORT Datum worker_spi_launch(PG_FUNCTION_ARGS);
>
> PGDLLEXPORT void worker_spi_main(Datum);
>
> 2.  Two warnings about int usage. Ignored.
>
> 3.   Documentation is a bit light on, but copying the control file
> into the right folder makes the extension visible.
>
> 4.   Extension installs but does nothing. No log messages. No schema.
> No table.
>
> The variable worker_spi.naptime appears in pg_settings but
> worker_spi.total_workers does not.
>
> 5.   Extension uninstalls without error, but does not release the DLL.
>
> 6.   Tried to restart server with pg_ctl but it does nothing. No
> error, no log message, nada. [Is this a known problem with Windows?]
>
> 7.   Restarted server using Task Manager. And so back to the
> beginning and try again.
>
>
>
> The worker_spi.c code contains this sequence:
>
>
>
>  DefineCustomIntVariable("worker_spi.naptime",
>
>  "Duration between each check (in
> seconds).",
>
> 
>
>  if (!process_shared_preload_libraries_in_progress)
>
>return;
>
>  DefineCustomIntVariable("worker_spi.total_workers",
>
>  "Number of workers.",
>
>
>
> From this I deduce that process_shared_preload_libraries_in_progress was
> false. So this appears to be an extension that must be preloaded. That does
> not seem to be documented anywhere.
>
>
>
> It would be helpful to get some suggestions about what changes would be
> needed to allow it to be loaded on demand.
>
>
>
> Regards
>
> David M Bennett FACS
> *--*
>
> *Andl - A New Database Language - andl.org *
>
>
>

Re: [GENERAL] Does frequent update of a row affects performance

[Adrian Klaver]

On 04/26/2016 05:55 AM, Rakesh Kumar wrote:

Pardon me if this has been discussed before.

I believe that PG back-end does not version index rows the way it does
the data rows. Assume that the app updates a row frequently (several
times in a second). For each update, PG will create a new version.
 However I believe the primary key index pointing to that row will point
to only the first row and the back end has to work its way traversing
the list until it finds the appropriate row matching the xmin/xmax.
 Does frequent vaccum take care of this by removing the dead rows and
hence reducing the commute.


Might want to take a look at:

http://www.postgresql.org/docs/9.5/interactive/indexam.html







--
Adrian Klaver
adrian.kla...@aklaver.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] truncate table getting blocked

[Albe Laurenz]

Jayadevan M wrote:
> I have a python script. It opens a cursor, and sets the search_path (using 
> psycopg2). In case
> something goes wrong in the script , a record is inserted into a table. In 
> that script, I am not doing
> any thing else other than reading a file and publishing the lines to a queue 
> (no database operations).
> The database is used just to track the errors. But my set search_path is 
> locking a truncate table I am
> executing from a psql session. Is this expected?
> 
> When the truncate table hung, I used this query
>  SELECT blocked_locks.pid AS blocked_pid,
>  blocked_activity.usename  AS blocked_user,
>  blocking_locks.pid AS blocking_pid,
>  blocking_activity.usename AS blocking_user,
>  blocked_activity.queryAS blocked_statement,
>  blocking_activity.query   AS current_statement_in_blocking_process
>FROM  pg_catalog.pg_locks blocked_locks
> JOIN pg_catalog.pg_stat_activity blocked_activity  ON 
> blocked_activity.pid = blocked_locks.pid
> JOIN pg_catalog.pg_locks blocking_locks
> ON blocking_locks.locktype = blocked_locks.locktype
> AND blocking_locks.DATABASE IS NOT DISTINCT FROM 
> blocked_locks.DATABASE
> AND blocking_locks.relation IS NOT DISTINCT FROM 
> blocked_locks.relation
> AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
> AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
> AND blocking_locks.virtualxid IS NOT DISTINCT FROM 
> blocked_locks.virtualxid
> AND blocking_locks.transactionid IS NOT DISTINCT FROM 
> blocked_locks.transactionid
> AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
> AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
> AND blocking_locks.objsubid IS NOT DISTINCT FROM 
> blocked_locks.objsubid
> AND blocking_locks.pid != blocked_locks.pid
>JOIN pg_catalog.pg_stat_activity blocking_activity ON 
> blocking_activity.pid = blocking_locks.pid
> 
>WHERE NOT blocked_locks.GRANTED;
> 
> and got this (schemaname/user/tablename modified)-
> 
> blocked_pid | blocked_user | blocking_pid | blocking_user |
> blocked_statement |
> current_statement_in_blocking_process
> -+--+--+---+--+---
> 
> 9223 | myuser   |12861 | myuser  | truncate table 
> myschema.table1; | SET
> search_path TO  myschema,public
> 
> 
> PG version :
> 
> PostgreSQL 9.4.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 
> 4.8.5, 64-bit

It is not the "SET search_path" statement that is blocking the truncate,
but probably some earlier statement issued in the same transaction.

Take a look at pg_locks to find out what lock the transaction is holding on 
myschema.table1.

Use statement logging to find out which statement causes the lock.

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

Re: [GENERAL] Problems running the WorkerSpi sample

[david]

Solved. The sample can indeed be loaded at startup (although it emits some 
strange LOG messages).

 

But to load it dynamically requires this SQL:

 

CREATE OR REPLACE FUNCTION worker_spi_launch(i INT) RETURNS INT

AS '' LANGUAGE C;

SELECT * FROM worker_spi_launch();

 

It would be helpful to add this to the documentation. From this point on, it 
looks pretty straightforward, but getting here was not easy.

 

I’m still wondering how to do a clean restart of the server without pg_ctl. 
Restarting the service is a bit brutal.

 

Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org

 

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of da...@andl.org
Sent: Tuesday, 26 April 2016 4:04 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Problems running the WorkerSpi sample

 

I’ve been trying to get the WorkerSpi sample working on a Windows installation, 
VS 2015, x64. So far, the following problems.

 

1.   Does not compile OOB. Added the following lines.

PGDLLEXPORT void _PG_init(void);

PGDLLEXPORT Datum worker_spi_launch(PG_FUNCTION_ARGS);

PGDLLEXPORT void worker_spi_main(Datum);

2.  Two warnings about int usage. Ignored.

3.   Documentation is a bit light on, but copying the control file into the 
right folder makes the extension visible.

4.   Extension installs but does nothing. No log messages. No schema. No 
table.

The variable worker_spi.naptime appears in pg_settings but 
worker_spi.total_workers does not.

5.   Extension uninstalls without error, but does not release the DLL.

6.   Tried to restart server with pg_ctl but it does nothing. No error, no 
log message, nada. [Is this a known problem with Windows?]

7.   Restarted server using Task Manager. And so back to the beginning and 
try again.

 

The worker_spi.c code contains this sequence:

 

 DefineCustomIntVariable("worker_spi.naptime",

 "Duration between each check (in 
seconds).",



 if (!process_shared_preload_libraries_in_progress) 

   return;

 DefineCustomIntVariable("worker_spi.total_workers",

 "Number of workers.",

 

>From this I deduce that process_shared_preload_libraries_in_progress was 
>false. So this appears to be an extension that must be preloaded. That does 
>not seem to be documented anywhere.

 

It would be helpful to get some suggestions about what changes would be needed 
to allow it to be loaded on demand.

 

Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org

 

Re: [GENERAL] 9.5: tsvector problem

[Charles Clavadetscher]

Hello Johann

There are two to_tsvector functions:

charles@charles.[local]=# \df to_tsvector
 List of functions
   Schema   |Name | Result data type | Argument data types | 
Type

+-+--+-+
 pg_catalog | to_tsvector | tsvector | regconfig, text | normal
 pg_catalog | to_tsvector | tsvector | text| normal
(2 rows)

I think that you may try casting the result of coalesce() to TEXT.

Bye
Charles

On 04/26/2016 03:25 PM, Johann Spies wrote:

I have never seen this problem before.  It occurred while trying to
import a dump (done by 9.5 client of a 9.4 database) also.

Table definition:

-
CREATE TABLE source.annual
(
   filename text,
   gzipfile text,
   id serial NOT NULL,
   tsv tsvector,
   ut character varying(19),
   xml xml,
   processed boolean,
   CONSTRAINT annual_pkey PRIMARY KEY (id)
)
WITH (
   OIDS=FALSE
);
CREATE INDEX xml_tsv_idx
   ON source.annual
   USING gin
   (tsv);


CREATE TRIGGER tsvectorupdate_source_xml
   BEFORE INSERT OR UPDATE
   ON source.annual
   FOR EACH ROW
   EXECUTE PROCEDURE source.update_xml_tsv();


And the trigger function:

CREATE OR REPLACE FUNCTION source.update_xml_tsv()
   RETURNS trigger AS
$BODY$

begin

new.tsv :=  to_tsvector('english', coalesce(new.xml,''));

return new;

end
$BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;


When I try to insert a value into this  table, I get:
==
ERROR:  function to_tsvector(unknown, xml) does not exist
LINE 1: SELECT to_tsvector('english', coalesce(new.xml))
^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:  SELECT to_tsvector('english', coalesce(new.xml))
CONTEXT:  PL/pgSQL function source.update_xml_tsv() line 5 at assignment

** Error **

ERROR: function to_tsvector(unknown, xml) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might
need to add explicit type casts.
Context: PL/pgSQL function source.update_xml_tsv() line 5 at assignment
=

But I can do

select to_tsvector('English', 'This is a problem')

without a problem.

What is causing this?

Regards
Johann

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


--

Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org


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

[GENERAL] truncate table getting blocked

[Jayadevan M]

Hello,

I have a python script. It opens a cursor, and sets the search_path (using
psycopg2). In case something goes wrong in the script , a record is
inserted into a table. In that script, I am not doing any thing else other
than reading a file and publishing the lines to a queue (no database
operations). The database is used just to track the errors. But my set
search_path is locking a truncate table I am executing from a psql session.
Is this expected?

When the truncate table hung, I used this query
 SELECT blocked_locks.pid AS blocked_pid,
 blocked_activity.usename  AS blocked_user,
 blocking_locks.pid AS blocking_pid,
 blocking_activity.usename AS blocking_user,
 blocked_activity.queryAS blocked_statement,
 blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity  ON
blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM
blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM
blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM
blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM
blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM
blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM
blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
   JOIN pg_catalog.pg_stat_activity blocking_activity ON
blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;

and got this (schemaname/user/tablename modified)-

blocked_pid | blocked_user | blocking_pid | blocking_user |
 blocked_statement | current_statement_in_blocking_process
-+--+--+---+--+---
9223 | myuser   |12861 | myuser  | truncate table
myschema.table1; | SET search_path TO  myschema,public


PG version :
PostgreSQL 9.4.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.8.5, 64-bit


Thanks,
Jayadevan

Re: [GENERAL] Intemittendly get "server process (PID 5884) exited with exit code 3"

[Wetzel, Juergen (Juergen)]

Hi Tom,

many thanks for your answer. This is a good hint. Will check this. 

Regards
Jürgen

-Ursprüngliche Nachricht-
Von: Tom Lane [mailto:t...@sss.pgh.pa.us] 
Gesendet: Montag, 25. April 2016 17:09
An: Wetzel, Juergen (Juergen)
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Intemittendly get "server process (PID 5884) exited with 
exit code 3"

"Wetzel, Juergen (Juergen)"  writes:
> I'm working with postgres version 9.3.10 on Windows. From time to time a 
> postgres process terminates with following messages:

> LOG:  server process (PID 5884) exited with exit code 3

As far as I can recall at the moment, no Postgres child process would ever 
choose to exit with exit code 3; we use 0, 1, and 2, but not 3.  I am thinking 
that maybe you've got some custom code in plperl or plpython or some such that 
does an exit(3).

> This ends up in an endless loop of these messages. Only possibility to 
> recover is to restart postgres service.

Or maybe the exit(3) is in an extension that is preloaded into all processes 
via shared_preload_libraries or similar?

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] Background worker with Listen

[Jinhua Luo]

Why not use libpq in worker? i.e. your worker works just like a pure PG client.

In my project, I uses worker in this way and it works well. I do not
use any backend API to access the database.

2016-04-21 15:51 GMT+08:00 Ihnat Peter | TSS Group a.s. :
> I am trying to create background worker which listens to notifications and
> do some work after receiving one.
>
> I got 2 problems:
>
> -  Worker is receiving notifications from every channel not only the
> registered channel (in my case “foo”)
>
> -  Notifications are not logged in the server log – I cannot store
> the payloads for further work
>
> Any help is welcomed.
>
>
>
> Here is the code:
>
> PG_MODULE_MAGIC;
>
>
>
> void _PG_init(void);
>
> void _PG_fini(void);
>
>
>
> static volatile sig_atomic_t got_sigterm = false;
>
> static volatile sig_atomic_t got_sigusr1 = false;
>
> static char *notify_database = NULL;
>
> static emit_log_hook_type prev_log_hook = NULL;
>
>
>
> static void
>
> bgw_sigterm(SIGNAL_ARGS)
>
> {
>
> int save_errno = errno;
>
> got_sigterm = true;
>
> if (MyProc)
>
> SetLatch(>procLatch);
>
> errno = save_errno;
>
> }
>
>
>
> static void
>
> bgw_sigusr1(SIGNAL_ARGS)
>
> {
>
> int save_errno = errno;
>
> got_sigusr1 = true;
>
> if (MyProc)
>
> SetLatch(>procLatch);
>
>errno = save_errno;
>
> }
>
>
>
> static void
>
> notify_main(Datum main_arg)
>
> {
>
> pqsignal(SIGTERM, bgw_sigterm);
>
> pqsignal(SIGUSR1, bgw_sigusr1);
>
>
>
> BackgroundWorkerUnblockSignals();
>
> BackgroundWorkerInitializeConnection(notify_database, NULL);
>
>  EnableNotifyInterrupt();
>
>
>
> pgstat_report_activity(STATE_RUNNING, "background_worker");
>
> StartTransactionCommand();
>
> Async_Listen("foo");
>
> CommitTransactionCommand();
>
> pgstat_report_activity(STATE_IDLE, NULL);
>
>
>
> while (!got_sigterm)
>
> {
>
> int   rc;
>
>
>
> rc = WaitLatch(>procLatch,
> WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH, 1L);
>
> ResetLatch(>procLatch);
>
>
>
> if (rc & WL_POSTMASTER_DEATH)
>
> proc_exit(1);
>
>
>
> if (got_sigusr1)
>
> {
>
> got_sigusr1 = false;
>
> elog(INFO, "
> background_worker: notification received");
>
> // DO SOME WORK WITH STORED
> NOTIFICATIONS
>
> }
>
>
>
> }
>
>
>
> elog(LOG, "background_worker: finished");
>
> proc_exit(0);
>
> }
>
>
>
> static void
>
> store_notification(ErrorData *edata)
>
> {
>
> // HERE STORE THE NOTIFICATION FROM SERVER LOG
>
>
>
> if (prev_log_hook)
>
> (*prev_log_hook) (edata);
>
> }
>
>
>
> void
>
> _PG_init(void)
>
> {
>
> BackgroundWorker worker;
>
> DefineCustomStringVariable("postgres", NULL, NULL,
> _database,
>
>"postgres",
>
>PGC_POSTMASTER, 0, NULL, NULL,
> NULL);
>
>
>
> MemSet(, 0, sizeof(BackgroundWorker));
>
> snprintf(worker.bgw_name, BGW_MAXLEN, "background_worker");
>
> worker.bgw_flags = BGWORKER_SHMEM_ACCESS |
> BGWORKER_BACKEND_DATABASE_CONNECTION;
>
> worker.bgw_start_time = BgWorkerStart_RecoveryFinished;
>
> worker.bgw_main = notify_main;
>
> worker.bgw_restart_time = 10;
>
> worker.bgw_main_arg = (Datum) 0;
>
> worker.bgw_notify_pid = 0;
>
> RegisterBackgroundWorker();
>
>
>
> prev_log_hook = emit_log_hook;
>
> emit_log_hook = store_notification;
>
> }
>
>
>
> void
>
> _PG_fini(void)
>
> {
>
> emit_log_hook = prev_log_hook;
>
> }


-- 
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] Background worker with Listen

[Peter Kardoš]

Hi Peter!

The solution to this problem would be also interesting for me. We have
application which use sending data to background worker and it's look like
the asynchronous notification can be ideal solution. But we do not found
solution how to work with this notifications. Worker calculating data and
update some tables. Amount of data is too big and therefore we can’t
calculate and update tables only in fixed period.
If the notification can’t be used for this, then it is possible to use
other method how to send data to worker or wake up worker?

Best regards,
Peter K.

From: Ihnat Peter | TSS Group a(dot)s(dot) >
>> I got 2 problems:
>>
>> -  Worker is receiving notifications from every channel not only
the registered channel (in my case "foo")
>>
>> -  Notifications are not logged in the server log - I cannot
store the payloads for further work
>> Any help is welcomed.
>>
>> Here is the code:
>>
>> PG_MODULE_MAGIC;
>>
>> void _PG_init(void);
>> void _PG_fini(void);
>>
>> static volatile sig_atomic_t got_sigterm = false;
>> static volatile sig_atomic_t got_sigusr1 = false;
>> static char *notify_database = NULL;
>> static emit_log_hook_type prev_log_hook = NULL;
>>
>> static void
>> bgw_sigterm(SIGNAL_ARGS)
>> {
>> int save_errno = errno;
>> got_sigterm = true;
>> if (MyProc)
>> SetLatch(>procLatch);
>> errno = save_errno;
>> }
>>
>> static void
>> bgw_sigusr1(SIGNAL_ARGS)
>> {
>> int save_errno = errno;
>> got_sigusr1 = true;
>> if (MyProc)
>> SetLatch(>procLatch);
>>errno = save_errno;
>> }
>>
>> static void
>> notify_main(Datum main_arg)
>> {
>> pqsignal(SIGTERM, bgw_sigterm);
>> pqsignal(SIGUSR1, bgw_sigusr1);
>>
>> BackgroundWorkerUnblockSignals();
>> BackgroundWorkerInitializeConnection(notify_database,
NULL);
>>  EnableNotifyInterrupt();
>>
>> pgstat_report_activity(STATE_RUNNING,
"background_worker");
>> StartTransactionCommand();
>> Async_Listen("foo");
>> CommitTransactionCommand();
>> pgstat_report_activity(STATE_IDLE, NULL);
>>
>> while (!got_sigterm)
>> {
>> int   rc;
>>
>> rc = WaitLatch(>procLatch,
WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH, 1L);
>> ResetLatch(>procLatch);
>>
>> if (rc & WL_POSTMASTER_DEATH)
>> proc_exit(1);
>>
>> if (got_sigusr1)
>> {
>> got_sigusr1 = false;
>> elog(INFO, "
background_worker: notification received");
>> // DO SOME WORK WITH
STORED NOTIFICATIONS
>> }
>>
>> }
>>
>> elog(LOG, "background_worker: finished");
>> proc_exit(0);
>> }
>>
>> static void
>> store_notification(ErrorData *edata)
>> {
>> // HERE STORE THE NOTIFICATION FROM SERVER LOG
>>
>> if (prev_log_hook)
>> (*prev_log_hook) (edata);
>> }
>>
>> void
>> _PG_init(void)
>> {
>> BackgroundWorker worker;
>> DefineCustomStringVariable("postgres", NULL, NULL,
_database,
>>"postgres",
>>PGC_POSTMASTER, 0, NULL,
NULL, NULL);
>>
>> MemSet(, 0, sizeof(BackgroundWorker));
>> snprintf(worker.bgw_name, BGW_MAXLEN,
"background_worker");
>> worker.bgw_flags = BGWORKER_SHMEM_ACCESS |
BGWORKER_BACKEND_DATABASE_CONNECTION;
>> worker.bgw_start_time = BgWorkerStart_RecoveryFinished;
>> worker.bgw_main = notify_main;
>> worker.bgw_restart_time = 10;
>> worker.bgw_main_arg = (Datum) 0;
>> worker.bgw_notify_pid = 0;
>> RegisterBackgroundWorker();
>>
>> prev_log_hook = emit_log_hook;
>> emit_log_hook = store_notification;
>> }
>>
>> void
>> _PG_fini(void)
>> {
>> emit_log_hook = prev_log_hook;
>> }

Re: [GENERAL] Invalid data read from synchronously replicated hot standby

[martin . kamp . jensen]

Adrian Klaver  wrote on 04/21/2016 16:03:55:

> From: Adrian Klaver 
> To: Martin Kamp Jensen/DK/Schneider@Europe, pgsql-general@postgresql.org
> Date: 04/21/2016 16:09
> Subject: Re: [GENERAL] Invalid data read from synchronously 
> replicated hot standby
> 
> On 04/21/2016 01:05 AM, martin.kamp.jen...@schneider-electric.com wrote:
> > Hi,
> >
> > We are getting invalid data when reading from a synchronously 
replicated
> > hot standby node in a 2-node setup. To better understand the 
situation,
> > we have created a document that provides an overview. We are hoping 
that
> > someone might be able to confirm whether or not the setup makes sense,
> > i.e., whether we are using PostgreSQL correctly and experiencing a 
bug,
> > or if we are using PostgreSQL incorrectly.
> >
> > Link to document that contains a step-by-step description of the
> > situation:
> > https://docs.google.com/document/d/1MuX8rq1gKw_WZ-
> HVflqxFslvXNTRGKa77A4NHto4ue0/edit?usp=sharing
> >
> >
> > If the setup is sane (and expected to work), we will work on setting 
up
> > a minimal reproduce that avoids our complete system. We are thinking
> > that a scripted Ansible/Vagrant setup makes sense.
> 
> Questions:
> 
> What is wal_level set to?

wal_level = hot_standby

> 
> Why on Node A do you have in recovery.conf?:
> primary_conninfo = 'host='
> 
> What exactly are you trying to do?
> 
> Looks to me you are trying to have multi-master, is that the case?

Eh, that's a mistake in the document. Probably because of a leftover 
recovery.done file. We only have Node A as master. I have updated the 
document.

I have been trying to reproduce the issue with a simple setup but so far 
without any luck.

> 
> >
> > Best regards,
> > Martin
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com
> 
> __
> This email has been scanned by the Symantec Email Security.cloud 
service.
> __