[HACKERS] Bug when dumping "empty" operator classes

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.

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

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

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

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

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

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

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

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

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

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

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

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

[HACKERS] Extra Vietnamese unaccent rules

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

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

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

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

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

Re: [HACKERS] Extra Vietnamese unaccent rules

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?

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

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,

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

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

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

> 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

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

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

Re: [HACKERS] Logical replication & corrupted pages recovery

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]

Re: [HACKERS] Extra Vietnamese unaccent rules

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

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

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

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

"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

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

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

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

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

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

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

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

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.

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

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? >>>

[HACKERS] logical replication busy-waiting on a lock

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

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

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

Re: [HACKERS] Logical replication & corrupted pages recovery

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

Re: [HACKERS] Extra Vietnamese unaccent rules

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 >

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

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

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

> "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 ...

Re: [HACKERS] Extra Vietnamese unaccent rules

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

Re: [HACKERS] Broken hint bits (freeze)

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

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

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

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

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

Re: [HACKERS] Broken hint bits (freeze)

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 >

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

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

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

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

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

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

Re: [HACKERS] Broken hint bits (freeze)

> 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

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

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

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

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

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

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 >

Re: [HACKERS] Extra Vietnamese unaccent rules

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

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

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

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

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

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

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"

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

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

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

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

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

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,

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

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

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

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

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

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

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

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

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

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

[HACKERS] Logical replication & corrupted pages recovery

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