Re: [HACKERS] logical replication - still unstable after all these months

[Erik Rijkers]

On 2017-05-27 01:35, Mark Kirkwood wrote:

On 26/05/17 20:09, Erik Rijkers wrote:


this whole thing 100x


Some questions that might help me get it right:
- do you think we need to stop and start the instances every time?
- do we need to init pgbench each time?
- could we just drop the subscription and publication and truncate the 
replica tables instead?


I have done all that in earler versions.

I deliberately added these 'complications' in view of the intractability 
of the problem: my fear is that an earlier failure leaves some 
half-failed state behind in an instance, which then might cause more 
failure.  This would undermine the intent of the whole exercise (which 
is to count succes/failure rate).  So it is important to be as sure as 
possible that each cycle starts out as cleanly as possible.



- what scale pgbench are you running?


I use a small script to call the main script; at the moment it does 
something like:

---
duration=60
from=1
to=100
for scale in 25 5
do
  for clients in 90 64 8
  do
date_str=$(date +"%Y%m%d_%H%M")
outfile=out_${date_str}.txt
time for x in `seq $from $to`
do
./pgbench_derail2.sh $scale $clients $duration $date_str
[...]
---


- how many clients for the 1 min pgbench run?


see above

- are you starting the pgbench run while the copy_data jobs for the 
subscription are still running?


I assume with copy_data you mean the data sync of the original table 
before pgbench starts.

And yes, I think here might be the origin of the problem.
( I think the problem I get is actually easily avoided by putting wait 
states here and there in between separate steps.  But the testing idea 
here is to force the system into error, not to avoid any errors)



- how exactly are you calculating those md5's?


Here is the bash function: cb (I forget what that stands for, I guess 
'content bench').  $outf is a log file to which the program writes 
output:


---
function cb()
{
  #  display the 4 pgbench tables' accumulated content as md5s
  #  a,b,t,h stand for:  pgbench_accounts, -branches, -tellers, -history
  num_tables=$( echo "select count(*) from pg_class where relkind = 'r' 
and relname ~ '^pgbench_'" | psql -qtAX )

  if [[ $num_tables -ne 4 ]]
  then
 echo "pgbench tables not 4 - exit" >> $outf
 exit
  fi
  for port in $port1 $port2
  do
md5_a=$(echo "select * from pgbench_accounts order by aid"|psql 
-qtAXp $port|md5sum|cut -b 1-9)
md5_b=$(echo "select * from pgbench_branches order by bid"|psql 
-qtAXp $port|md5sum|cut -b 1-9)
md5_t=$(echo "select * from pgbench_tellers  order by tid"|psql 
-qtAXp $port|md5sum|cut -b 1-9)
md5_h=$(echo "select * from pgbench_history  order by hid"|psql 
-qtAXp $port|md5sum|cut -b 1-9)
cnt_a=$(echo "select count(*) from pgbench_accounts"  |psql 
-qtAXp $port)
cnt_b=$(echo "select count(*) from pgbench_branches"  |psql 
-qtAXp $port)
cnt_t=$(echo "select count(*) from pgbench_tellers"   |psql 
-qtAXp $port)
cnt_h=$(echo "select count(*) from pgbench_history"   |psql 
-qtAXp $port)
md5_total[$port]=$( echo "${md5_a} ${md5_b} ${md5_t} ${md5_h}" | 
md5sum )

printf "$port a,b,t,h: %8d %6d %6d %6d" $cnt_a $cnt_b $cnt_t $cnt_h
echo -n "  $md5_a $md5_b $md5_t $md5_h"
if   [[ $port -eq $port1 ]]; then echo" master"
elif [[ $port -eq $port2 ]]; then echo -n " replica"
else  echo"   ERROR  "
fi
  done
  if [[ "${md5_total[$port1]}" == "${md5_total[$port2]}" ]]
  then
echo " ok"
  else
echo " NOK"
  fi
}
---

this enables:

echo "-- getting md5 (cb)"
cb_text1=$(cb)

and testing that string like:

if echo "$cb_text1" | grep -qw 'replica ok';
then
   echo "-- All is well."

[...]


Later today I'll try to clean up the whole thing and post it.














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

Re: [HACKERS] ALTER SUBSCRIPTION ..SET PUBLICATION refresh is not throwing error.

[Euler Taveira]

2017-05-26 21:29 GMT-03:00 Petr Jelinek :

>
> Actually another possibility would be to remove the REFRESH keyword
> completely and just have [ WITH (...) ] and have the refresh option
> there, ie simplified version of what you have suggested (without the
> ugliness of specifying refresh twice to disable).


It will cause confusion. It seems that WITH sets ALTER SUBSCRIPTION
properties. Indeed, they are REFRESH properties. I think we shouldn't
exclude REFRESH keyword. Syntax leaves no doubt that WITH are REFRESH
properties.


-- 
   Euler Taveira   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Euler Taveira]

2017-05-26 17:52 GMT-03:00 Peter Eisentraut <
peter.eisentr...@2ndquadrant.com>:

>
> You cannot publish a system catalog.  But a user-created table in
> information_schema is not a system catalog.


Replication of information_schema tables works. However, pg_dump doesn't
include information_schema tables into CREATE PUBLICATION command
(user-defined information_schema tables aren't included in pg_dump even
*before* logical replication). IMO allow publish/subscribe of tables into
information_schema is harmless (they aren't special tables like catalogs).
Also, how many people would create real tables into information_schema?
Almost zero. Let's leave it alone. Since pg_dump doesn't document that
information_schema isn't dumped, I think we shouldn't document this for
logical replication.


-- 
   Euler Taveira   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: [HACKERS] Extra Vietnamese unaccent rules

[Michael Paquier]

On Fri, May 26, 2017 at 5:48 PM, Thomas Munro
 wrote:
> Unicode has two ways to represent characters with accents: either with
> composed codepoints like "é" or decomposed codepoints where you say
> "e" and then "´".  The field "00E2 0301" is the decomposed form of
> that character above.  Our job here is to identify the basic letter
> that each composed character contains, by analysing the decomposed
> field that you see in that line.  I failed to realise that characters
> with TWO accents are described as a composed character with ONE accent
> plus another accent.

Doesn't that depend on the NF operation you are working on? With a
canonical decomposition it seems to me that a character with two
accents can as well be decomposed with one character and two composing
character accents (NFKC does a canonical decomposition in one of its
steps).

> You don't have to worry about decoding that line, it's all done in
> that Python script.  The problem is just in the function
> is_letter_with_marks().  Instead of just checking if combining_ids[0]
> is a plain letter, it looks like it should also check if
> combining_ids[0] itself is a letter with marks.  Also get_plain_letter
> would need to be able to recurse to extract the "a".

Actually, with the recent work that has been done with
unicode_norm_table.h which has been to transpose UnicodeData.txt into
user-friendly tables, shouldn't the python script of unaccent/ be
replaced by something that works on this table? This does a canonical
decomposition but just keeps the first characters with a class
ordering of 0. So we have basic APIs able to look at UnicodeData.txt
and let caller do decision making with the result returned.
-- 
Michael


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

Re: [HACKERS] Logical replication & corrupted pages recovery

[Craig Ringer]

On 27 May 2017 01:03, "Aleksander Alekseev" 
wrote:

Hi Konstantin,

> May be it is possible to somehow optimize it, by checking ranges of
primary
> key values

It's possible. An optimization you are looking for is called Merkle
tree [1]. Particularly it's used in Riak [2].

[1] https://en.wikipedia.org/wiki/Merkle_tree
[2] http://docs.basho.com/riak/kv/2.2.3/learn/concepts/active-anti-entropy/


Personally I'd very much value a solid, standalone resynchronization
function implemented in C using a PGconn. Even better if it could report
differences and/or apply local changes.

Re: [HACKERS] ALTER SUBSCRIPTION ..SET PUBLICATION refresh is not throwing error.

[Petr Jelinek]

On 27/05/17 02:13, Euler Taveira wrote:
> 2017-05-26 17:58 GMT-03:00 Peter Eisentraut
>  >:
> 
> On 5/24/17 15:38, Petr Jelinek wrote:
> >>> I wonder if we actually need the SKIP REFRESH syntax, there is the
> >>> "REFRESH [ WITH ... ]" when user wants to refresh, so if REFRESH is 
> not
> >>> specified, we can just behave as if SKIP REFRESH was used, it's not 
> like
> >>> there is 3rd possible behavior.
> >>
> >> Attached patch does exactly that.
> >
> > And of course I forgot to update docs...
> 
> Do we want not-refreshing to be the default behavior?
> 
> 
> It is a different behavior from the initial proposal. However, we
> fortunately have ALTER SUBSCRIPTION foo REFRESH PUBLICATION and can
> refresh later. Also, if "refresh" is more popular than "skip", it is
> just a small word in the command. That's the price we pay to avoid
> ambiguity that the previous syntax had.At least I think Petr's proposal
> is less confusing than mine (my proposal maintains current behavior but
> can cause some confusion).
> 

Actually another possibility would be to remove the REFRESH keyword
completely and just have [ WITH (...) ] and have the refresh option
there, ie simplified version of what you have suggested (without the
ugliness of specifying refresh twice to disable).

-- 
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


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

Re: [HACKERS] ALTER SUBSCRIPTION ..SET PUBLICATION refresh is not throwing error.

[Euler Taveira]

2017-05-26 17:58 GMT-03:00 Peter Eisentraut <
peter.eisentr...@2ndquadrant.com>:

> On 5/24/17 15:38, Petr Jelinek wrote:
> >>> I wonder if we actually need the SKIP REFRESH syntax, there is the
> >>> "REFRESH [ WITH ... ]" when user wants to refresh, so if REFRESH is not
> >>> specified, we can just behave as if SKIP REFRESH was used, it's not
> like
> >>> there is 3rd possible behavior.
> >>
> >> Attached patch does exactly that.
> >
> > And of course I forgot to update docs...
>
> Do we want not-refreshing to be the default behavior?


It is a different behavior from the initial proposal. However, we
fortunately have ALTER SUBSCRIPTION foo REFRESH PUBLICATION and can refresh
later. Also, if "refresh" is more popular than "skip", it is just a small
word in the command. That's the price we pay to avoid ambiguity that the
previous syntax had.At least I think Petr's proposal is less confusing than
mine (my proposal maintains current behavior but can cause some confusion).


-- 
   Euler Taveira   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: [HACKERS] logical replication - still unstable after all these months

[Mark Kirkwood]

On 26/05/17 20:09, Erik Rijkers wrote:



The idea is simple enough:

startup instance1
startup instance2 (on same machine)
primary: init pgbench tables
primary: add primary key to pgbench_history
copy empty tables to replica by dump/restore
primary: start publication
replica: start subscription
primary: run 1-minute pgbench
wait till the 4 md5's of primary pgbench tables
  are the same as the 4 md5's of replica pgbench
  tables (this will need a time-out).
log 'ok' or 'not ok'
primary: clean up publication
replica: clean up subscription
shutdown primary
shutdown replica

this whole thing 100


I might have a look at scripting this up (especially if it keeps raining 
here)...


Some questions that might help me get it right:
- do you think we need to stop and start the instances every time?
- do we need to init pgbench each time?
- could we just drop the subscription and publication and truncate the 
replica tables instead?

- what scale pgbench are you running?
- how many clients for the 1 min pgbench run?
- are you starting the pgbench run while the copy_data jobs for the 
subscription are still running?

- how exactly are you calculating those md5's?

Cheers

Mark




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

[HACKERS] logical replication busy-waiting on a lock

[Jeff Janes]

When I create a subscription in the disabled state, and then later doing
"alter subscription sub enable;", on the master I sometimes get a tight
loop of the deadlock detector:

(log_lock_waits is on, of course)

deadlock_timeout is set to 1s, so I don't know why it seems to be running
several times per millisecond.

47462 idle in transaction 2017-05-26 16:05:20.505 PDT LOG:  logical
decoding found initial starting point at 1B/7BAC9D50
47462 idle in transaction 2017-05-26 16:05:20.505 PDT DETAIL:  Waiting for
transactions (approximately 9) older than 73326615 to end.
47462 idle in transaction waiting 2017-05-26 16:05:21.505 PDT LOG:  process
47462 still waiting for ShareLock on transaction 73322726 after 1000.060 ms
47462 idle in transaction waiting 2017-05-26 16:05:21.505 PDT DETAIL:
 Process holding the lock: 47457. Wait queue: 47462.
47462 idle in transaction waiting 2017-05-26 16:05:21.506 PDT LOG:  process
47462 still waiting for ShareLock on transaction 73322726 after 1000.398 ms
47462 idle in transaction waiting 2017-05-26 16:05:21.506 PDT DETAIL:
 Process holding the lock: 47457. Wait queue: 47462.
47462 idle in transaction waiting 2017-05-26 16:05:21.506 PDT LOG:  process
47462 still waiting for ShareLock on transaction 73322726 after 1000.574 ms
47462 idle in transaction waiting 2017-05-26 16:05:21.506 PDT DETAIL:
 Process holding the lock: 47457. Wait queue: 47462.
47462 idle in transaction waiting 2017-05-26 16:05:21.506 PDT LOG:  process
47462 still waiting for ShareLock on transaction 73322726 after 1000.816 ms
47462 idle in transaction waiting 2017-05-26 16:05:21.506 PDT DETAIL:
 Process holding the lock: 47457. Wait queue: 47462.
47462 idle in transaction waiting 2017-05-26 16:05:21.506 PDT LOG:  process
47462 still waiting for ShareLock on transaction 73322726 after 1001.180 ms
47462 idle in transaction waiting 2017-05-26 16:05:21.506 PDT DETAIL:
 Process holding the lock: 47457. Wait queue: 47462.
47462 idle in transaction waiting 2017-05-26 16:05:21.507 PDT LOG:  process
47462 still waiting for ShareLock on transaction 73322726 after 1001.284 ms
47462 idle in transaction waiting 2017-05-26 16:05:21.507 PDT DETAIL:
 Process holding the lock: 47457. Wait queue: 47462.
47462 idle in transaction waiting 2017-05-26 16:05:21.507 PDT LOG:  process
47462 still waiting for ShareLock on transaction 73322726 after 1001.493 ms
.

And so on out to "after 9616.814", when it finally acquires the lock.

The other process, 47457, is doing the initial COPY of another table as
part of the same publisher/subscriber set.

Cheers,

Jeff

Re: [HACKERS] Extra Vietnamese unaccent rules

[Thomas Munro]

On Sat, May 27, 2017 at 9:09 AM, Kha Nguyen  wrote:
> Could you explain to me what this line means:
> “
> 1EA5;LATIN SMALL LETTER A WITH CIRCUMFLEX AND ACUTE;Ll;0;L;00E2
> 0301N;;;1EA4;;1EA4
> “
>
> If you could give me an example of adding a rule for “recursive” case, I can 
> do the rest. I am not familiar with this unaccent format generation yet.

So contrib/unaccent/generate_unaccent_rules.py is a Python script that
takes UnicodeData.txt, a list of information about all Unicode
codepoints available at a URL that is shown in a comment, and
generates unaccent.rules.  The idea was to avoid having to change it
manually every time someone finds characters that should be in there
(as you have just done!) by doing it systematically.

Unicode has two ways to represent characters with accents: either with
composed codepoints like "é" or decomposed codepoints where you say
"e" and then "´".  The field "00E2 0301" is the decomposed form of
that character above.  Our job here is to identify the basic letter
that each composed character contains, by analysing the decomposed
field that you see in that line.  I failed to realise that characters
with TWO accents are described as a composed character with ONE accent
plus another accent.

You don't have to worry about decoding that line, it's all done in
that Python script.  The problem is just in the function
is_letter_with_marks().  Instead of just checking if combining_ids[0]
is a plain letter, it looks like it should also check if
combining_ids[0] itself is a letter with marks.  Also get_plain_letter
would need to be able to recurse to extract the "a".

I hope that helps!

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


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

Re: [HACKERS] logical replication - still unstable after all these months

[Jeff Janes]

On Fri, May 26, 2017 at 12:27 AM, Erik Rijkers  wrote:

> On 2017-05-26 08:58, Simon Riggs wrote:
>
>> On 26 May 2017 at 07:10, Erik Rijkers  wrote:
>>
>> - Do you agree this number of failures is far too high?
>>> - Am I the only one finding so many failures?
>>>
>>
>> What type of failure are you getting?
>>
>
> The failure is that in the result state the replicated tables differ from
> the original tables.
>

But what is the actual failure?  Which tables differ?  Do they have the
same number of rows? Do they only differ in the *balance column or
something else?  Are they transactionally consistent?

I have not been able to replicate the problem.

Cheers,

Jeff

Re: [HACKERS] Regarding Postgres Dynamic Shared Memory (DSA)

[Amit Kapila]

On Wed, May 24, 2017 at 11:39 AM, Mahi Gurram  wrote:
> One solution that is striking me is
> 1. I'll create one background worker and will initialise DSA in it.
> 2. If there are any callbacks available for client open/close connections,
> i'll attach/detach to the DSA in those callbacks.
>
> But i'm not sure there are such callbacks available. If such callbacks
> exists in postgres and you guys know please help me out with that.
>

We do share DSA handle, planned statement, params etc from master
backend with workers.  See ExecInitParallelPlan that might help you in
your work.


-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


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

Re: [HACKERS] Create subscription with `create_slot=false` and incorrect slot name

[Peter Eisentraut]

On 5/25/17 19:16, Petr Jelinek wrote:
>> The reported error is just one of many errors that can happen when DROP
>> SUBSCRIPTION tries to drop the slot (doens't exist, still active, no
>> permission, etc.).  We don't want to give the hint that is effectively
>> "just forget about the slot then" for all of them.  So we would need
>> some way to distinguish "you can't do this right now" from "this would
>> never work" (400 vs 500 errors).
>>
> This specific error returns ERRCODE_UNDEFINED_OBJECT error code so we
> could check for it and offer hint only for this case.

We would have to extend the walreceiver interface a little to pass that
through, but it seems doable.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] ALTER SUBSCRIPTION ..SET PUBLICATION refresh is not throwing error.

[Peter Eisentraut]

On 5/24/17 15:38, Petr Jelinek wrote:
>>> I wonder if we actually need the SKIP REFRESH syntax, there is the
>>> "REFRESH [ WITH ... ]" when user wants to refresh, so if REFRESH is not
>>> specified, we can just behave as if SKIP REFRESH was used, it's not like
>>> there is 3rd possible behavior.
>>
>> Attached patch does exactly that.
> 
> And of course I forgot to update docs...

Do we want not-refreshing to be the default behavior?

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] pg_dump ignoring information_schema tables which used in Create Publication.

[Peter Eisentraut]

On 5/25/17 22:45, Robert Haas wrote:
> I guess I'm not convinced that it's really the same.  I think we want
> to allow users to create views over system objects; our life might be
> easier if we hadn't permitted that, but views over e.g. pg_locks are
> common, and prohibiting them doesn't seem like a reasonable choice.
> I'm less clear that we want to let them publish system objects.  Aside
> from the pg_dump issues, does it work?

The confusion in this discussion is exactly that there are multiple
definitions of what a "system object" might be.

You cannot publish a system catalog.  But a user-created table in
information_schema is not a system catalog.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] logical replication and PANIC during shutdown checkpoint in publisher

[Peter Eisentraut]

On 5/26/17 14:16, Michael Paquier wrote:
> So, now that the last round of minor releases has happened and that
> some dust has settled on this patch, shouldn't there be a backpatch?
> If yes, do you need patches for all branches? This problems goes down
> to 9.2 anyway as BASE_BACKUP can generate end-of-backup records.

Yes, this could be backpatched now.  It looks like it will need a bit of
fiddling to get it into all the backbranches.  If you want to give it a
closer look, go ahead please.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Broken hint bits (freeze)

[Vladimir Borodin]

> 26 мая 2017 г., в 23:04, Michael Paquier  
> написал(а):
> 
> On Fri, May 26, 2017 at 2:39 PM, Amit Kapila  wrote:
>> Yeah, I think this is quite suspicious.  This seems to indicate that
>> not all WAL records are replicated before the switchover.  What is the
>> value of "synchronous_commit" you are using?  I think you somehow need
>> to ensure before switchover that all the WAL is replicated to ensure
>> this is not a setup problem.
> 
> It is so easy to corrupt a server because of an incorrect base backup
> flow or an incorrect switchover that it would be good first to
> understand how you are doing your switchover. Any corruption happening
> after a promotion, a failover or a switchover may be the top of the
> iceberg of what's on the data pages, and you may just see one symptom
> among other problems. Particularly, did you kill the master in any
> violent way after promoting the standby? Has the former master been
> able to perform at least once a clean shutdown checkpoint and has it
> been rewound?

At first we cleanly stop the former master and then we extra check that all 
data has been received by the standby to be promoted. Old master is returned by 
simply generating recovery.conf and starting it, all other standbys are done in 
the same way. WAL history on all hosts and in archive remains linear, no 
pg_rewind is needed. This procedure has been well tested on 9.3 (when the 
ability to do such switchover without restoring standbys from backup appeared), 
automated and has not changed for all these years.

Actually we have already found that LSNs of all corrupted tuples are somewhere 
near the time of upgrade from 9.5 to 9.6. There is still no evidence but it 
seems that it is mostly related to upgrade procedure. We now extract backups of 
9.5 and 9.6 for a database where we now have corrupted pages to check this 
version. But it is still not obvious if it could be a pg_upgrade bug or our 
mistake in a way we did upgrade.

> -- 
> Michael


--
May the force be with you…
https://simply.name

Re: [HACKERS] Broken hint bits (freeze)

[Michael Paquier]

On Fri, May 26, 2017 at 2:39 PM, Amit Kapila  wrote:
> Yeah, I think this is quite suspicious.  This seems to indicate that
> not all WAL records are replicated before the switchover.  What is the
> value of "synchronous_commit" you are using?  I think you somehow need
> to ensure before switchover that all the WAL is replicated to ensure
> this is not a setup problem.

It is so easy to corrupt a server because of an incorrect base backup
flow or an incorrect switchover that it would be good first to
understand how you are doing your switchover. Any corruption happening
after a promotion, a failover or a switchover may be the top of the
iceberg of what's on the data pages, and you may just see one symptom
among other problems. Particularly, did you kill the master in any
violent way after promoting the standby? Has the former master been
able to perform at least once a clean shutdown checkpoint and has it
been rewound?
-- 
Michael


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

Re: [HACKERS] Renaming a table to an array's autogenerated name

[Vik Fearing]

On 05/26/2017 03:20 PM, Tom Lane wrote:
> Vik Fearing  writes:
>> On 05/25/2017 05:24 PM, Tom Lane wrote:
>>> After some experimentation, I came up with the attached, which simply
>>> skips the "recursive" step if it would apply to the same array type we
>>> already moved.
> 
>> This looks good to me.
> 
> Pushed, thanks for reviewing.

Thanks!
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


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

Re: [HACKERS] fix side-effect in get_qual_for_list()

[Jeevan Ladhe]

Hi Ashutosh,

Thanks for catching this. For now this isn't a problem since
> generate_partition_qual() is crafting PartitionBoundInfo which it
> doesn't use anywhere else. But if the function gets used where the
> PartitionBoundSpec is being used somewhere else as well.


Yes, this behavior currently does not affect adversely, but I think this
function is quite useful for future enhancements and should be fixed.

While you are
> at it, can we use castNode() in place of
> PartitionBoundSpec *spec = (PartitionBoundSpec *) bound; Or do you
> think it should be done separately?
>

I have made this change at couple of places applicable.

PFA.

Regards,
Jeevan Ladhe


fix_listdatums_get_qual_for_list_v2.patch
Description: Binary data

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

Re: [HACKERS] Renaming a table to an array's autogenerated name

[Tom Lane]

Vik Fearing  writes:
> On 05/25/2017 05:24 PM, Tom Lane wrote:
>> After some experimentation, I came up with the attached, which simply
>> skips the "recursive" step if it would apply to the same array type we
>> already moved.

> This looks good to me.

Pushed, thanks for reviewing.

regards, tom lane


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

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

[Regina Obe]

> "Regina Obe"  writes:
>> I figured out the culprit was the change in CASE WHEN behavior with 
>> set returning functions Had a criteria something of the form:
>> CASE WHEN some_condition_dependent_on_sometable_that_resolves_to_false 
>>  THEN (regexp_matches(...))[1] ELSE ...  END FROM sometable;

> You might want to consider changing such usages to use regexp_match()
instead of regexp_matches().

>   regards, tom lane

Thanks.  I ended up swapping out with substring which was a bit shorter than
regexp_match()[].

But I've got similar problems with PostGIS topology logic and the easiest
change to make was take advantage
of the fact that you guys are treating CASE constant ... THEN  SRF ...

Differently 

Than 

CASE not_constant_based_on_table_value THEN  SRF ..


So I switched those to constant checks.  This feels a little dirty and
fragile to me though.

Is this behavior going to stay or change?

It seems inconsistent from a user perspective that

CASE constant  == short-circuit skipping over SRFs that may  otherwise
fail

While 

CASE not_constant_table_dependent  doesn't short-circuit.

I can understand the motive behind it, it just feels a little inconsistent
from an end-user POV.


Thanks,
Regina



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

Re: [HACKERS] logical replication - still unstable after all these months

[Jeff Janes]

On Fri, May 26, 2017 at 5:17 AM, tushar 
wrote:

>
> run second time =
> ./pgbench -T 20   -c 90 -j 90  -f test.sql  postgres
>
> check the row count on master/standby
> Master=
> postgres=# select count(*) from pgbench_history ;
>  count
> 
>  536836
> (1 row)
>
> Standby =
>
> postgres=#  select count(*) from pgbench_history ;
>   count
> -
>  1090959
> (1 row)


Hi Tushar,

pgbench starts out by truncating pgbench_history.  That truncation does not
get replicated to the subscriber.  The later inserts do.  So your
subscriber ends up with about twice as many rows.

Cheers,

Jeff

Re: [HACKERS] Broken hint bits (freeze)

[Amit Kapila]

On Tue, May 23, 2017 at 10:50 PM, Dmitriy Sarafannikov
 wrote:
> Hi hackers,
>
> We have some problems on our production with hint bits and frozen tuples.
> More and more following errors began to appear on master after switchover:
> ERROR:  58P01: could not access status of transaction 1952523525
> DETAIL:  Could not open file "pg_clog/0746": No such file or directory.
> LOCATION:  SlruReportIOError, slru.c:896
>
> We investigated the problem with pageinspect and found the tuples that are 
> the cause:
>
> xdb311g(master)=# select * from mytable where ctid = '(4,21)';
> ERROR:  58P01: could not access status of transaction 1951521353
> DETAIL:  Could not open file "pg_clog/0745": No such file or directory.
> LOCATION:  SlruReportIOError, slru.c:896
>
> But the same query successfully executed on replica.
>
> We found some difference in hint bits between master and replica:
>
> xdb311g(master)=# SELECT t_xmin, t_infomask::bit(32) & X'0300'::int::bit(32) 
> FROM heap_page_items(get_raw_page(‘mytable',4)) where lp=21;
> -[ RECORD 1 ]--
> t_xmin   | 1951521353
> ?column? | 
>
> old master, now replica:
> xdb311e(replica)=# SELECT t_xmin, t_infomask::bit(32) & X'0300'::int::bit(32) 
> FROM heap_page_items(get_raw_page(‘mytable',4)) where lp=21;
> -[ RECORD 1 ]--
> t_xmin   | 1951521353
> ?column? | 0011
>
> X’0300’ = HEAP_XMIN_FROZEN according to
>
> #define HEAP_XMIN_COMMITTED 0x0100  /* t_xmin committed */
> #define HEAP_XMIN_INVALID   0x0200  /* t_xmin invalid/aborted */
> #define HEAP_XMIN_FROZEN(HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)
>
> xdb311g(master)=# select relfrozenxid from pg_class where relname = ‘mytable';
> relfrozenxid
> --
> 2266835605
> (1 row)
>
> This tuple must be frozen but there are no set bits HEAP_XMIN_COMMITTED and 
> HEAP_XMIN_INVALID on master
>
> Another interesting thing that LSN of this page on master and replica are not 
> the same:
> xdb311g(master)=# select lsn from page_header(get_raw_page(‘mytable',4));
>lsn
> ---
> 8092/6A26DD08
> (1 row)
>
> xdb311e(replica)=# select lsn from page_header(get_raw_page(‘mytable',4));
>lsn
> ---
> 838D/C4A0D280
> (1 row)
>
> And LSN on replica is greater that LSN on master (838D/C4A0D280 > 
> 8092/6A26DD08)
> How can this be possible?
>

Yeah, I think this is quite suspicious.  This seems to indicate that
not all WAL records are replicated before the switchover.  What is the
value of "synchronous_commit" you are using?  I think you somehow need
to ensure before switchover that all the WAL is replicated to ensure
this is not a setup problem.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


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

Re: [HACKERS] Extra Vietnamese unaccent rules

[Thomas Munro]

On Sat, May 27, 2017 at 5:13 AM, Tom Lane  wrote:
> I wrote:
>> Nguyen Le Hoang Kha  writes:
>>> Most of the time in Vietnamese language, there are up to 2 accents in a
>>> character. These unaccent rules are added to handle such cases (which are
>>> very common).
>
>> I can't see any reason not to add these --- any objections out there?
>
> Oh, wait a minute.  Patching unaccent.rules directly isn't the way
> to do this; that file is supposed to be generated by
> generate_unaccent_rules.py.  Can you see how to modify that script
> to produce these rules?

Looking at one example from this patch:

UTF8: 
Codepoint: 1EA5
Name: LATIN SMALL LETTER A WITH CIRCUMFLEX AND ACUTE

In UnicodData.txt it's this line:

1EA5;LATIN SMALL LETTER A WITH CIRCUMFLEX AND ACUTE;Ll;0;L;00E2
0301N;;;1EA4;;1EA4

The problem is that generate_unaccent_rules.py assumes that the
composing data is a plain letter followed by some number of
diacritical modifiers.  That's true for the characters with a single
accent, but in this multi-accent case it's *composed* character 00E2
(LATIN SMALL LETTER A WITH CIRCUMFLEX) and a diacritical marker 0301
(COMBINING ACCENT ACUTE).  So we need to teach it to be recursive.

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


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

Re: [HACKERS] logical replication and PANIC during shutdown checkpoint in publisher

[Michael Paquier]

On Sat, May 6, 2017 at 6:40 AM, Michael Paquier
 wrote:
> Agreed. Just adding an ERROR message in XLogInsert() is not going to
> help much as this leads also to PANIC for critical sections :(
> So a patch really needs to be a no-op for all WAL-related operations
> within the WAL sender, and that will be quite invasive I am afraid.
>
>> I will move the open item to "Older Bugs" now, because the user
>> experience regression, so to speak, in version 10 has been addressed.
>> (This could be a backpatching candidate, but I am not planning on it for
>> next week's releases in any case.)
>
> No issues with all that.

So, now that the last round of minor releases has happened and that
some dust has settled on this patch, shouldn't there be a backpatch?
If yes, do you need patches for all branches? This problems goes down
to 9.2 anyway as BASE_BACKUP can generate end-of-backup records.
-- 
Michael


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

Re: [HACKERS] Renaming a table to an array's autogenerated name

[Vik Fearing]

On 05/25/2017 05:24 PM, Tom Lane wrote:
> Vik Fearing  writes:
>> In commit 9aa3c782c93, Tom fixed a bug in which creating a table _foo
>> when an array type of that name already existed would make the array
>> type change its name to get out of the way. But it missed a trick in
>> that renaming a table would still cause a conflict.
> 
> Good catch.
> 
>> One interesting thing to note however, is that if you rename a table to
>> its own array's name (which was my case when I found this bug), the new
>> array name will be ___foo instead of just __foo.  I don't know if it's
>> worth worrying about that.
> 
> After some experimentation, I came up with the attached, which simply
> skips the "recursive" step if it would apply to the same array type we
> already moved.

This looks good to me.

> I added some queries to the regression test case to show exactly what
> happens to the array type names, and in that, you can see that the
> behavior for the "normal" case with distinct array types is that neither
> array type ends up with a name that is just the unsurprising case of
> an underscore followed by its element type's name; they *both* have an
> extra underscore compared to that.  Maybe that's okay.  We could possibly
> rejigger the order of renaming so that one of them ends with an
> unsurprising name, but I failed to make that happen without considerably
> more surgery.

I don't think this really matters to anyone in practice, so I'm fine
with it.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


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

Re: [HACKERS] Extra Vietnamese unaccent rules

[Tom Lane]

I wrote:
> Nguyen Le Hoang Kha  writes:
>> Most of the time in Vietnamese language, there are up to 2 accents in a
>> character. These unaccent rules are added to handle such cases (which are
>> very common).

> I can't see any reason not to add these --- any objections out there?

Oh, wait a minute.  Patching unaccent.rules directly isn't the way
to do this; that file is supposed to be generated by
generate_unaccent_rules.py.  Can you see how to modify that script
to produce these rules?

regards, tom lane


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

Re: [HACKERS] Logical replication & corrupted pages recovery

[Aleksander Alekseev]

Hi Konstantin,

> May be it is possible to somehow optimize it, by checking ranges of primary
> key values

It's possible. An optimization you are looking for is called Merkle
tree [1]. Particularly it's used in Riak [2].

[1] https://en.wikipedia.org/wiki/Merkle_tree
[2] http://docs.basho.com/riak/kv/2.2.3/learn/concepts/active-anti-entropy/

-- 
Best regards,
Aleksander Alekseev


signature.asc
Description: PGP signature

Re: [HACKERS] Extra Vietnamese unaccent rules

[Tom Lane]

Nguyen Le Hoang Kha  writes:
> Most of the time in Vietnamese language, there are up to 2 accents in a
> character. These unaccent rules are added to handle such cases (which are
> very common).

I can't see any reason not to add these --- any objections out there?

regards, tom lane


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

Re: [HACKERS] Error log for psql (uploading backup) in PostgreSQL 9.3.17

[Robert Haas]

On Wed, May 24, 2017 at 10:00 AM, Nick Dro  wrote:
> Why is this happens?

>From this amount of information, I can't tell you, and I suspect
nobody else can either.

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

Also, this isn't really the correct mailing list for this kind of
problem.  pgsql-general might be a better choice.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Bug when dumping "empty" operator classes

[Daniel Gustafsson]

> On 26 May 2017, at 17:08, Tom Lane  wrote:
> 
> Daniel Gustafsson  writes:
>> While hacking on pg_upgrade in downstream Greenplum I ran into an error which
>> seems like an old, and obscure, bug in pg_dump (unrelated to pg_upgrade).
>> pg_dump generates incorrect SQL for an operator class which has no operators 
>> or
>> procedures, and which has the same column and storage types.
> 
> Good catch.
> 
>> The attached patch adds a belts-and-suspenders check in dumpOpclass() which
>> appends the STORAGE clause in case nothing had been added.
> 
> Seems reasonable (the comment could use some wordsmithing maybe) ...
> 
>> ... The DROP in
>> alter_generic is also removed to exercise the code path, being able to
>> pg_upgrade what is executed in regression seem like a good idea.
> 
> ... but that's a nonstarter.  We can't have the regression tests leaving
> global objects (users) lying around.

Fair enough, 

> I'll commit and back-patch this without a test case.  Possibly Frost will
> be excited enough about it to add something to the pg_dump TAP tests,
> but those tests are too opaque for me to want to do so.

Thanks!

cheers ./daniel

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

Re: [HACKERS] Bug when dumping "empty" operator classes

[Tom Lane]

Daniel Gustafsson  writes:
> While hacking on pg_upgrade in downstream Greenplum I ran into an error which
> seems like an old, and obscure, bug in pg_dump (unrelated to pg_upgrade).
> pg_dump generates incorrect SQL for an operator class which has no operators 
> or
> procedures, and which has the same column and storage types.

Good catch.

> The attached patch adds a belts-and-suspenders check in dumpOpclass() which
> appends the STORAGE clause in case nothing had been added.

Seems reasonable (the comment could use some wordsmithing maybe) ...

> ... The DROP in
> alter_generic is also removed to exercise the code path, being able to
> pg_upgrade what is executed in regression seem like a good idea.

... but that's a nonstarter.  We can't have the regression tests leaving
global objects (users) lying around.

I'll commit and back-patch this without a test case.  Possibly Frost will
be excited enough about it to add something to the pg_dump TAP tests,
but those tests are too opaque for me to want to do so.

regards, tom lane


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

Re: [HACKERS] logical replication - still unstable after all these months

[Petr Jelinek]

On 26/05/17 16:51, Alvaro Herrera wrote:
> Erik Rijkers wrote:
> 
>> I wouldn't say that problems (re)appeared at a certain point; my impression
>> is rather that logical replication has become better and better.  But I kept
>> getting the odd failure, without a clear cause, but always (eventually)
>> repeatable on other machines.  I did the 1-minute pgbench-derail version
>> exactly because of the earlier problems with snapbuild: I wanted a test that
>> does a lot of starting and stopping of publication and subscription.
> 
> I think it is pretty unlikely that the logical replication plumbing is
> the buggy place.  You're just seeing it now becaues we didn't have any
> mechanism as convenient to consume logical decoding output.  In other
> words, I strongly suspect that the hyphothetical bugs are in the logical
> decoding side (and snapbuild sounds the most promising candidate) rather
> than logical replication per se.
> 

Well, that was true for the previous issues Erik found as well (mostly
snapshot builder was problematic). But that does not mean there are no
issues elsewhere. We could do with some more output from the tests (do
they log some intermediary states of those md5 checksums, maybe numbers
of rows etc?), description of the problems, errors from logs, etc. I for
example don't get any issues from similar test as the one described in
this thread so without more info it might be hard to reproduce and fix
whatever the underlaying issue is.

-- 
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


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

Re: [HACKERS] logical replication - still unstable after all these months

[Alvaro Herrera]

Erik Rijkers wrote:

> I wouldn't say that problems (re)appeared at a certain point; my impression
> is rather that logical replication has become better and better.  But I kept
> getting the odd failure, without a clear cause, but always (eventually)
> repeatable on other machines.  I did the 1-minute pgbench-derail version
> exactly because of the earlier problems with snapbuild: I wanted a test that
> does a lot of starting and stopping of publication and subscription.

I think it is pretty unlikely that the logical replication plumbing is
the buggy place.  You're just seeing it now becaues we didn't have any
mechanism as convenient to consume logical decoding output.  In other
words, I strongly suspect that the hyphothetical bugs are in the logical
decoding side (and snapbuild sounds the most promising candidate) rather
than logical replication per se.

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


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

Re: retry shm attach for windows (WAS: Re: [HACKERS] OK, so culicidae is *still* broken)

[Magnus Hagander]

On Fri, May 26, 2017 at 8:24 AM, Michael Paquier 
wrote:

> On Fri, May 26, 2017 at 8:20 AM, Amit Kapila 
> wrote:
> > I think the real question here is, shall we backpatch this fix or we
> > want to do this just in Head or we want to consider it as a new
> > feature for PostgreSQL-11.  I think it should be fixed in Head and the
> > change seems harmless to me, so we should even backpatch it.
>
> The thing is not invasive, so backpatching is a low-risk move. We can
> as well get that into HEAD first, wait a bit for dust to settle on it,
> and then backpatch.
>


I would definitely suggest putting it in HEAD (and thus, v10) for a while
to get some real world exposure before backpatching. But if it does work
out well in the end, then we can certainly consider backpatching it. But
given the difficulty in reliably reproducing the problem etc, I think it's
a good idea to give it some proper real world experience in 10 first.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: [HACKERS] Patch: Add --no-comments to skip COMMENTs with pg_dump

[Stephen Frost]

Greetings,

* Robins Tharakan (thara...@gmail.com) wrote:
> Attached is a patch adds a --no-comments argument to pg_dump to skip
> generation of COMMENT statements when generating a backup. This is crucial
> for non-superusers to restore a database backup in a Single Transaction.
> Currently, this requires one to remove COMMENTs via scripts, which is
> inelegant at best.

Having --no-comments seems generally useful to me, in any case.

> A similar discussion had taken place earlier [1], however that stopped
> (with a Todo entry) since it required more work at the backend.

Well, that was one possible solution (COMMENT IF NOT EXISTS).  That does
seem like it'd be nice too, though what I think we really want here is
something like:

CREATE EXTENSION IF NOT EXISTS plpgsql ... COMMENT blah;

That general capability has been asked for and discussed before, but
it's complicated because we support comments on lots of objects and
doesn't address other possible issues with this approach (comments
aren't the only things that could exist on plpgsql, and I can't see us
having a way to get every component included in a single command...).

That leads to an interesting thought which we could consider, which
would be represented in some crazy syntax such as:

CREATE EXTENSION IF NOT EXISTS plpgsql ... (
  COMMENT xyz;
  GRANT USAGE ON EXTENSION plpgsql whatever;
);

> This patch provides a stop-gap solution until then. If the feedback is to
> tackle this is by filtering comments for specific DB objects, an argument
> name (for e.g. --no-extension-comments or something) would help and I could
> submit a revised patch.

That seems like it might be a bit too specific.

> Alternatively, if there are no objections, I could submit this to the
> Commitfest.

Yes, please add it to the commitfest.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] logical replication - still unstable after all these months

[Erik Rijkers]

On 2017-05-26 15:59, Petr Jelinek wrote:

Hi,

Hmm, I was under the impression that the changes we proposed in the
snapbuild thread fixed your issues, does this mean they didn't? Or the
modified versions of those that were eventually committed didn't? Or 
did

issues reappear at some point?


I do think the snapbuild fixed solved certain problems.  I can't say 
where the present problems are caused (as I have said, I suspect logical 
replication, but also my own test-harness: perhaps it leaves some 
error-state lying around (although I do try hard to prevent that) -- so 
I just don't know.


I wouldn't say that problems (re)appeared at a certain point; my 
impression is rather that logical replication has become better and 
better.  But I kept getting the odd failure, without a clear cause, but 
always (eventually) repeatable on other machines.  I did the 1-minute 
pgbench-derail version exactly because of the earlier problems with 
snapbuild: I wanted a test that does a lot of starting and stopping of 
publication and subscription.



Erik Rijkers






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

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

[Tom Lane]

"Regina Obe"  writes:
> I figured out the culprit was the change in CASE WHEN behavior with set
> returning functions
> Had a criteria something of the form:
> CASE WHEN some_condition_dependent_on_sometable_that_resolves_to_false THEN
> (regexp_matches(...))[1] ELSE ...  END
> FROM sometable;

You might want to consider changing such usages to use regexp_match()
instead of regexp_matches().

regards, tom lane


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

Re: [HACKERS] fix side-effect in get_qual_for_list()

[Ashutosh Bapat]

On Thu, May 25, 2017 at 3:12 PM, Jeevan Ladhe
 wrote:
> Hi,
>
> While working on one of the crash reported on default partition for list
> partitioning table[1] I found some strange behavior in get_qual_for_list()
> while
> I tried to call it from the new code I wrote for default partition.
>
> After debugging, I noticed that the function get_qual_for_list() is
> implicitly
> manipulating the (PartitionBoundSpec) spec->listdatums list. AFAICU, this
> manipulation is needed just to construct a list of datums to be passed to
> ArrayExpr, and this should be done without manipulating the original list.
> The function name is get_qual_for_list(), which implies that this function
> returns something and does not modify anything.
>
> I have made this change in attached patch, as I think this is useful for
> future
> developments, as there may be a need in future to call get_qual_for_list()
> from
> other places, and the caller might not expect that PartitionBoundSpec gets
> modified.
>

Thanks for catching this. For now this isn't a problem since
generate_partition_qual() is crafting PartitionBoundInfo which it
doesn't use anywhere else. But if the function gets used where the
PartitionBoundSpec is being used somewhere else as well. While you are
at it, can we use castNode() in place of
PartitionBoundSpec *spec = (PartitionBoundSpec *) bound; Or do you
think it should be done separately?

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


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

[HACKERS] Extra Vietnamese unaccent rules

[Nguyen Le Hoang Kha]

Most of the time in Vietnamese language, there are up to 2 accents in a
character. These unaccent rules are added to handle such cases (which are
very common).

Kha Nguyen | nlhkh@github


vietnamese-unaccent-rules.patch
Description: Binary data

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

[HACKERS] Patch: Add --no-comments to skip COMMENTs with pg_dump

[Robins Tharakan]

Hi,

Attached is a patch adds a --no-comments argument to pg_dump to skip
generation of COMMENT statements when generating a backup. This is crucial
for non-superusers to restore a database backup in a Single Transaction.
Currently, this requires one to remove COMMENTs via scripts, which is
inelegant at best.

A similar discussion had taken place earlier [1], however that stopped
(with a Todo entry) since it required more work at the backend.

This patch provides a stop-gap solution until then. If the feedback is to
tackle this is by filtering comments for specific DB objects, an argument
name (for e.g. --no-extension-comments or something) would help and I could
submit a revised patch.

Alternatively, if there are no objections, I could submit this to the
Commitfest.

References:
[1] https://www.postgresql.org/message-id/1420.1397099637%40sss.pgh.pa.us

-
robins
​​


pgdump_nocomments_v1.patch
Description: Binary data

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

Re: [HACKERS] logical replication - still unstable after all these months

[Petr Jelinek]

Hi,

Hmm, I was under the impression that the changes we proposed in the
snapbuild thread fixed your issues, does this mean they didn't? Or the
modified versions of those that were eventually committed didn't? Or did
issues reappear at some point?

-- 
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


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

[HACKERS] Bug when dumping "empty" operator classes

[Daniel Gustafsson]

While hacking on pg_upgrade in downstream Greenplum I ran into an error which
seems like an old, and obscure, bug in pg_dump (unrelated to pg_upgrade).
pg_dump generates incorrect SQL for an operator class which has no operators or
procedures, and which has the same column and storage types.  While it’s
arguably a pretty uninteresting operator class, it is allowed since we don’t
validate that all required operators/procedures are present.

The alter_generic test suite contains examples of opclasses which trigger this
behavior.  The below operator class:

  CREATE OPERATOR CLASS alt_opc1 FOR TYPE uuid USING hash AS STORAGE uuid;

..is dumped like this (after an ALTER .. RENAME, thus the new name):

  CREATE OPERATOR CLASS alt_opc3
  FOR TYPE uuid USING hash FAMILY alt_opc1 AS
  ;

The reason why this hasn’t been caught by the PostgreSQL pg_upgrade test is
because the schema in which the operator classes are created is dropped at the
end of the suite, removing the DROP cause pg_upgrade to fail with:

  pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at 
or near ";"
  LINE 3: ;
  ^
  Command was: CREATE OPERATOR CLASS "alt_opc2"
  FOR TYPE "macaddr" USING "hash" FAMILY "alt_opc2" AS
  ;

The attached patch adds a belts-and-suspenders check in dumpOpclass() which
appends the STORAGE clause in case nothing had been added.  While adding
storage when it’s identical to the column type goes against the documentation,
it’s valid SQL and won’t break restore (and is handled by DefineOpClass()).
Validating the options fully would of course ensure that the dump always has
enough to render, but it also adds a lot of complexity (a quick look in the
archives doesn’t turn up many reports of it being a big problem).  The DROP in
alter_generic is also removed to exercise the code path, being able to
pg_upgrade what is executed in regression seem like a good idea.

cheers ./daniel



opclass_pgdump.patch
Description: Binary data

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

Re: retry shm attach for windows (WAS: Re: [HACKERS] OK, so culicidae is *still* broken)

[Michael Paquier]

On Fri, May 26, 2017 at 8:20 AM, Amit Kapila  wrote:
> I think the real question here is, shall we backpatch this fix or we
> want to do this just in Head or we want to consider it as a new
> feature for PostgreSQL-11.  I think it should be fixed in Head and the
> change seems harmless to me, so we should even backpatch it.

The thing is not invasive, so backpatching is a low-risk move. We can
as well get that into HEAD first, wait a bit for dust to settle on it,
and then backpatch.
-- 
Michael


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

Re: retry shm attach for windows (WAS: Re: [HACKERS] OK, so culicidae is *still* broken)

[Amit Kapila]

On Fri, May 26, 2017 at 5:30 AM, Tsunakawa, Takayuki
 wrote:
> From: pgsql-hackers-ow...@postgresql.org
>> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Amit Kapila
>> Yes, I also share this opinion, the shm attach failures are due to
>> randomization behavior, so sleep won't help much.  So, I will change the
>> patch to use 100 retries unless people have other opinions.
>
> Perhaps I'm misunderstanding, but I thought it is not known yet whether the 
> cause of the original problem is ASLR.  I remember someone referred to 
> anti-virus software and something else.
>

We are here purposefully trying to resolve the randomize shm
allocation behavior due to ASLR.  The original failure was on a linux
machine and is resolved.  We presumably sometimes get the failures [1]
due to this behavior.

>  I guessed that the reason Noah suggested 1 - 5 seconds of retry is based on 
> the expectation that the address space might be freed by the anti-virus 
> software.
>

Noah is also suggesting to have a retry count, read his mail above in
this thread and refer to his comment ("Thus, measuring time is
needless complexity; retry count is a suitable proxy.")


I think the real question here is, shall we backpatch this fix or we
want to do this just in Head or we want to consider it as a new
feature for PostgreSQL-11.  I think it should be fixed in Head and the
change seems harmless to me, so we should even backpatch it.


[1] - https://www.postgresql.org/message-id/14121.1485360296%40sss.pgh.pa.us

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


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

Re: [HACKERS] logical replication - still unstable after all these months

[tushar]

On 05/26/2017 12:57 PM, Erik Rijkers wrote:
The failure is that in the result state the replicated tables differ 
from the original tables. 

I am also getting similar behavior

Master=
run pgbench with scaling factor =1  (./pg_bench -i -s 1 postgres  )
delete rows from pgbench_history  ( delete from pgbench_history)
create publication  (create publication pub for table pgbench_history)

Slave=
run pgbench with scaling factor =1  (./pg_bench -i -s 1 postgres -p 5000  )
delete rows from pgbench_history  ( delete from pgbench_history)
create subscription (create subscription sub connection 'dbname=postgres 
host=localhost user=centos) publication pub;


create a test.sql file , having an insert statement
[centos@centos-cpula bin]$ cat test.sql
insert into pgbench_history values (1,1,1,1,now(),'anv');

now run pgbench with -T / -c / -j options
First time = ./pgbench -t 5   -c 90 -j 90  -f test.sql  postgres

count on Master/slave are SAME .

run second time =
./pgbench -T 20   -c 90 -j 90  -f test.sql  postgres

check the row count on master/standby
Master=
postgres=# select count(*) from pgbench_history ;
 count

 536836
(1 row)

Standby =

postgres=#  select count(*) from pgbench_history ;
  count
-
 1090959
(1 row)

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company



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

[HACKERS] Logical replication & corrupted pages recovery

[Konstantin Knizhnik]

Several PgPro cstomers, which are evaluating our multimaster, are 
interested in possibility to recover corrupted pages from other cluster 
nodes.
This task seems to be more general and is not multimaster specific. This 
is why I want to discuss it here.


With physical (streaming) replication content of master and replica 
database are identical, so it is quite easy do restore corrupted page 
from the replica by just copying correspondent file or part of file. 
With logical replication content of database pages on the disk may be 
different even through data is logically identical.
If some heap page is corrupted, then there is no some simple and 
efficient way to determine records which were located on this page.

Clustered indexes can help, but this is a long story...

So my question is whether there is now some efficient way to synchronize 
two tables?
If not, are there any plans to provide such functionality in logical 
replication in future?


Right now, the only approach which comes to me mind is to extract all 
primary keys at two nodes, exchanges them between nodes, find out 
missing tuples by comparing two ordered set of keys and request them 
from other node. It is based on the assumption that Postgres just skips 
records from the corrupted pages.
The drawback of this approach is that it will be very slow and cause 
large network traffic for huge tables.
May be it is possible to somehow optimize it, by checking ranges of 
primary key values
(if number of records in the range is the same at both nodes, then 
ranges can be considered as identical and not compared).


Also this approach requires suspending of cluster while table 
synchronization (or at least, locking this table).
Synchronization of table in case of presence of active updates of this 
tables seems to be much more challenged task.


If somebody has already thought about this problem, have some plan or 
may be even ready solution for it, please share your thoughts.

Thanks in advance,

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



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

Re: [HACKERS] logical replication - still unstable after all these months

[Erik Rijkers]

On 2017-05-26 10:29, Mark Kirkwood wrote:

On 26/05/17 20:09, Erik Rijkers wrote:


On 2017-05-26 09:40, Simon Riggs wrote:


If we can find out what the bug is with a repeatable test case we can 
fix it.


Could you provide more details? Thanks


I will, just need some time to clean things up a bit.


But what I would like is for someone else to repeat my 100x1-minute 
tests, taking as core that snippet I posted in my previous email.  I 
built bash-stuff around that core (to take md5's, shut-down/start-up 
the two instances between runs, write info to log-files, etc).  But it 
would be good if someone else made that separately because if that 
then does not fail, it would prove that my test-harness is at fault 
(and not logical replication).




Will do - what I had been doing was running pgbench, waiting until the


Great!

You'll have to think about whether to go with instances of either 
master, or master+those 4 patches.  I guess either choice makes sense.



row counts on the replica pgbench_history were the same as the
primary, then summing the %balance and delta fields from the primary
and replica dbs and comparing. So far - all match up ok. However I'd


I did number-summing for a while as well (because it's a lot faster than 
taking md5's over the full content).
But the problem with summing is that (I think) in the end you cannot be 
really sure that the result is correct (false positives, although I 
don't understand the odds).



been running a longer time frames (5 minutes), so not the same number
of repetitions as yet.


I've run 3600-, 30- and 15-minute runs too, but in this case (these 100x 
tests) I wanted to especially test the area around startup/initialise of 
logical replication.  Also the increasing quality of logical replication 
(once it runs with the correct


thanks,

Erik Rijkers


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

Re: [HACKERS] logical replication - still unstable after all these months

[Mark Kirkwood]

On 26/05/17 20:09, Erik Rijkers wrote:


On 2017-05-26 09:40, Simon Riggs wrote:


If we can find out what the bug is with a repeatable test case we can 
fix it.


Could you provide more details? Thanks


I will, just need some time to clean things up a bit.


But what I would like is for someone else to repeat my 100x1-minute 
tests, taking as core that snippet I posted in my previous email.  I 
built bash-stuff around that core (to take md5's, shut-down/start-up 
the two instances between runs, write info to log-files, etc).  But it 
would be good if someone else made that separately because if that 
then does not fail, it would prove that my test-harness is at fault 
(and not logical replication).




Will do - what I had been doing was running pgbench, waiting until the 
row counts on the replica pgbench_history were the same as the primary, 
then summing the %balance and delta fields from the primary and replica 
dbs and comparing. So far - all match up ok. However I'd been running a 
longer time frames (5 minutes), so not the same number of repetitions as 
yet.


regards

Mark


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

Re: [HACKERS] logical replication - still unstable after all these months

[Erik Rijkers]

On 2017-05-26 09:40, Simon Riggs wrote:


If we can find out what the bug is with a repeatable test case we can 
fix it.


Could you provide more details? Thanks


I will, just need some time to clean things up a bit.


But what I would like is for someone else to repeat my 100x1-minute 
tests, taking as core that snippet I posted in my previous email.  I 
built bash-stuff around that core (to take md5's, shut-down/start-up the 
two instances between runs, write info to log-files, etc).  But it would 
be good if someone else made that separately because if that then does 
not fail, it would prove that my test-harness is at fault (and not 
logical replication).


The idea is simple enough:

startup instance1
startup instance2 (on same machine)
primary: init pgbench tables
primary: add primary key to pgbench_history
copy empty tables to replica by dump/restore
primary: start publication
replica: start subscription
primary: run 1-minute pgbench
wait till the 4 md5's of primary pgbench tables
  are the same as the 4 md5's of replica pgbench
  tables (this will need a time-out).
log 'ok' or 'not ok'
primary: clean up publication
replica: clean up subscription
shutdown primary
shutdown replica

this whole thing 100x





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

Re: [HACKERS] logical replication - still unstable after all these months

[Simon Riggs]

On 26 May 2017 at 08:27, Erik Rijkers  wrote:
> On 2017-05-26 08:58, Simon Riggs wrote:
>>
>> On 26 May 2017 at 07:10, Erik Rijkers  wrote:
>>
>>> - Do you agree this number of failures is far too high?
>>> - Am I the only one finding so many failures?
>>
>>
>> What type of failure are you getting?
>
>
> The failure is that in the result state the replicated tables differ from
> the original tables.

An important point would be note that that this is time dependent.

> I would really like to know it you think that that doesn't amount to
> 'failure'.

Yes, your test has failed.

Even one record on one test is a serious problem and needs to be fixed.

If we can find out what the bug is with a repeatable test case we can fix it.

Could you provide more details? Thanks

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] logical replication - still unstable after all these months

[Erik Rijkers]

On 2017-05-26 08:58, Simon Riggs wrote:

On 26 May 2017 at 07:10, Erik Rijkers  wrote:


- Do you agree this number of failures is far too high?
- Am I the only one finding so many failures?


What type of failure are you getting?


The failure is that in the result state the replicated tables differ 
from the original tables.


For instance,

-- out_20170525_0944.txt
100 -- pgbench -c 90 -j 8 -T 60 -P 12 -n   --  scale 25
 93 -- All is well.
  7 -- Not good.

These numbers mean: the result state of primary and replica is not the 
same, in 7 out of 100 runs.


'not the same state' means:  at least one of the 4 md5's of the sorted 
content of the 4 pgbench tables on the primary is different from those 
taken from the replica.


So, 'failure' means: the 4 pgbench tables on primary and replica are not 
exactly the same after the (one-minute) pgbench-run has finished, and 
logical replication has 'finished'.  (plenty of time is given for the 
replica to catchup. The test only calls 'failure' after 20x waiting (for 
15 seconds) and 20x finding the same erroneous state (erroneous because 
not-same as on primary).



I would really like to know it you think that that doesn't amount to 
'failure'.




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

Re: [HACKERS] logical replication - still unstable after all these months

[Simon Riggs]

On 26 May 2017 at 07:10, Erik Rijkers  wrote:

> - Do you agree this number of failures is far too high?
> - Am I the only one finding so many failures?

What type of failure are you getting?

-- 
Simon Riggshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[HACKERS] logical replication - still unstable after all these months

[Erik Rijkers]

If you run a pgbench session of 1 minute over a logical replication 
connection and repeat that 100x this is what you get:


At clients 90, 64, 8, scale 25:

-- out_20170525_0944.txt
100 -- pgbench -c 90 -j 8 -T 60 -P 12 -n   --  scale 25
 93 -- All is well.
  7 -- Not good.
-- out_20170525_1426.txt
100 -- pgbench -c 64 -j 8 -T 60 -P 12 -n   --  scale 25
 82 -- All is well.
 18 -- Not good.
-- out_20170525_2049.txt
100 -- pgbench -c 8 -j 8 -T 60 -P 12 -n   --  scale 25
 90 -- All is well.
 10 -- Not good


At clients 90, 64, 8, scale 25:

-- out_20170526_0126.txt
100 -- pgbench -c 90 -j 8 -T 60 -P 12 -n   --  scale 5
 98 -- All is well.
  2 -- Not good.
-- out_20170526_0352.txt
100 -- pgbench -c 64 -j 8 -T 60 -P 12 -n   --  scale 5
 97 -- All is well.
  3 -- Not good.
-- out_20170526_0621.txt
 45 -- pgbench -c 8 -j 8 -T 60 -P 12 -n   --  scale 5
 41 -- All is well.
  3 -- Not good.

(That last one obviously not finished)


I think this is pretty awful, really, for a beta level.

The above installations (master+replica) are with Petr Jelinek's (and 
Michael Paquier's) last patches

 0001-Fix-signal-handling-in-logical-workers.patch
 0002-Make-tablesync-worker-exit-when-apply-dies-while-it-.patch
 0003-Receive-invalidation-messages-correctly-in-tablesync.patch
 Remove-the-SKIP-REFRESH-syntax-suggar-in-ALTER-SUBSC-v2.patch

Now, it could be that there is somehow something wrong with my 
test-setup (as opposed to some bug in log-repl).  I can post my test 
program, but I'll do that separately (but below is the core all my tests 
-- it's basically still that very first test that I started out with, 
many months ago...)



I'd like to find out/know more about:
- Do you agree this number of failures is far too high?
- Am I the only one finding so many failures?
- Is anyone else testing the same way (more or less continually, finding 
only succes)?
- Which of the Open Items could be resposible for this failure rate?  (I 
don't see a match.)
- What tests do others do?  Could we somehow concentrate results and 
method somewhere?



Thanks,


Erik Rijkers




PS

The core of the 'pgbench_derail' test (bash) is simply:

echo "drop table if exists pgbench_accounts;
drop table if exists pgbench_branches;
drop table if exists pgbench_tellers;
drop table if exists pgbench_history;" | psql -qXp $port1 \
&& echo "drop table if exists pgbench_accounts;
drop table if exists pgbench_branches;
drop table if exists pgbench_tellers;
drop table if exists pgbench_history;" | psql -qXp $port2 \
&& pgbench -p $port1 -qis $scale \
&& echo "alter table pgbench_history add column hid serial primary key;" 
\

 | psql -q1Xp $port1 && pg_dump -F c -p $port1 \
--exclude-table-data=pgbench_history  \
--exclude-table-data=pgbench_accounts \
--exclude-table-data=pgbench_branches \
--exclude-table-data=pgbench_tellers  \
  -t pgbench_history -t pgbench_accounts \
  -t pgbench_branches -t pgbench_tellers \
 | pg_restore -1 -p $port2 -d testdb
appname=derail2
echo "create publication pub1 for all tables;" | psql -p $port1 -aqtAX
echo "create subscription sub1 connection 'port=${port1}
  application_name=$appname' publication pub1 with(enabled=false);
alter subscription sub1 enable;" | psql -p $port2 -aqtAX

pgbench -c $clients -j $threads -T $duration -P $pseconds -n#  scale 
$scale


Now compare md5's of the sorted content of each of the 4 pgbench tables 
on primary and replica.  They should be the same.




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