Re: [HACKERS] Prepared statements assume text type in PG10

2017-10-07 Thread Tom Lane
Jack Christensen writes: > Pre-version 10: > jack=# prepare ps as select $1; > ERROR:  could not determine data type of parameter $1 > But on PG10 the type defaults to text: > jack=# prepare ps as select $1; > PREPARE > I looked through the git log and couldn't find

Re: [HACKERS] Prepared statements assume text type in PG10

2017-10-07 Thread Peter Geoghegan
On Sat, Oct 7, 2017 at 4:27 PM, Peter Geoghegan wrote: > I suspect commit d8d32d9 is involved here, though I haven't verified that. Weirdly, there is a git hash collision here, so you'll have to put in d8d32d9a (8 characters -- the default of 7 for a short git hash isn't cutting

Re: [HACKERS] Prepared statements assume text type in PG10

2017-10-07 Thread Peter Geoghegan
On Sat, Oct 7, 2017 at 2:56 PM, Jack Christensen wrote: > The test suite for the Go PostgreSQL driver pgx > (https://github.com/jackc/pgx) found an unexpected behavior change in PG10. > Previously, it was impossible to prepare a statement with a unknown or > ambiguous

Re: [HACKERS] [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple

2017-10-07 Thread Alvaro Herrera
Peter Geoghegan wrote: > On Sat, Oct 7, 2017 at 1:31 AM, Alvaro Herrera > wrote: > >> As you must have seen, Alvaro said he has a variant of Dan's original > >> script that demonstrates that a problem remains, at least on 9.6+, > >> even with today's fix. I think it's

Re: [HACKERS] [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple

2017-10-07 Thread Peter Geoghegan
On Sat, Oct 7, 2017 at 1:31 AM, Alvaro Herrera wrote: >> As you must have seen, Alvaro said he has a variant of Dan's original >> script that demonstrates that a problem remains, at least on 9.6+, >> even with today's fix. I think it's the stress-test that plays with >>

Re: [HACKERS] tablespaces inside $PGDATA considered harmful

2017-10-07 Thread Mark Kirkwood
On 26/09/17 20:44, Mark Kirkwood wrote: $ pg_basebackup -D . WARNING:  could not read symbolic link "pg_tblspc/space1": Invalid argument pg_basebackup: directory "/data0/pgdata/11/pg_tblspc/space1" exists but is not empty pg_basebackup: removing contents of data directory "." Err -

[HACKERS] Prepared statements assume text type in PG10

2017-10-07 Thread Jack Christensen
The test suite for the Go PostgreSQL driver pgx (https://github.com/jackc/pgx) found an unexpected behavior change in PG10. Previously, it was impossible to prepare a statement with a unknown or ambiguous parameter type. Pre-version 10: jack=# prepare ps as select $1; ERROR:  could not

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Petr Jelinek
On 07/10/17 19:59, Tom Lane wrote: > Petr Jelinek writes: >> On 07/10/17 18:15, Tom Lane wrote: >>> No, I'm afraid you didn't read that comment closely enough. This will >>> flat out fail for cases like "select ... where x=x order by x", because >>> there will

Re: [HACKERS] Horrible CREATE DATABASE Performance in High Sierra

2017-10-07 Thread Tom Lane
I wrote: > Current status is that I've filed a bug report with Apple and am waiting > to see their response before deciding what to do next. If they fix the > issue promptly then there's little need for us to do anything. Not having heard a peep from Apple yet, I decided to do a bit more

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Nico Williams
On Fri, Oct 06, 2017 at 10:19:54PM -0400, Tom Lane wrote: > Andres Freund writes: > > On 2017-10-06 21:33:16 -0400, Adam Brusselback wrote: > >> The article in question is here: > >>

Re: [HACKERS] Slow synchronous logical replication

2017-10-07 Thread Konstantin Knizhnik
On 10/07/2017 10:42 PM, Andres Freund wrote: Hi, On 2017-10-07 22:39:09 +0300, konstantin knizhnik wrote: In our sharded cluster project we are trying to use logical relication for providing HA (maintaining redundant shard copies). Using asynchronous logical replication has not so much sense

Re: [HACKERS] Slow synchronous logical replication

2017-10-07 Thread Andres Freund
Hi, On 2017-10-07 22:39:09 +0300, konstantin knizhnik wrote: > In our sharded cluster project we are trying to use logical relication for > providing HA (maintaining redundant shard copies). > Using asynchronous logical replication has not so much sense in context of > HA. This is why we try to

[HACKERS] Slow synchronous logical replication

2017-10-07 Thread konstantin knizhnik
In our sharded cluster project we are trying to use logical relication for providing HA (maintaining redundant shard copies). Using asynchronous logical replication has not so much sense in context of HA. This is why we try to use synchronous logical replication. Unfortunately it shows very bad

[HACKERS] Help required to debug pg_repack breaking logical replication

2017-10-07 Thread Daniele Varrazzo
Hello, we have been reported, and I have experienced a couple of times, pg_repack breaking logical replication. - https://github.com/reorg/pg_repack/issues/135 - https://github.com/2ndQuadrant/pglogical/issues/113 In my experience, after the botched run, the replication slot was "stuck", and

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Tom Lane
Petr Jelinek writes: > On 07/10/17 18:15, Tom Lane wrote: >> No, I'm afraid you didn't read that comment closely enough. This will >> flat out fail for cases like "select ... where x=x order by x", because >> there will already be a single-element EC for x and so

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Petr Jelinek
On 07/10/17 18:15, Tom Lane wrote: > Petr Jelinek writes: >> On 07/10/17 04:19, Tom Lane wrote: >>> (edit: a few minutes later, I seem to remember that equivclass.c has >>> to do something special with the X=X case, so maybe it could do >>> something else special

Re: [HACKERS] Issue with logical replication: MyPgXact->xmin already is valid

2017-10-07 Thread Petr Jelinek
On 07/10/17 18:23, Konstantin Knizhnik wrote: > On 10/07/2017 04:26 PM, Petr Jelinek wrote: >> >> Hmm so you start transaction (you have to when running >> CREATE_REPLICATION_SLOT with USE_SNAPSHOT parameter). And while the slot >> is being created the config is reloaded. And since now you are in

Re: [HACKERS] separate serial_schedule useful?

2017-10-07 Thread Tom Lane
I wrote: > Robert Haas writes: >> There's no reason why pg_regress couldn't have a >> --bail-if-group-size-exceeds=N argument, or why we couldn't have a >> separate Perl script to validate the schedule file as part of the >> build process. > I'd go for the former approach;

Re: [HACKERS] Issue with logical replication: MyPgXact->xmin already is valid

2017-10-07 Thread Konstantin Knizhnik
On 10/07/2017 04:26 PM, Petr Jelinek wrote: Hmm so you start transaction (you have to when running CREATE_REPLICATION_SLOT with USE_SNAPSHOT parameter). And while the slot is being created the config is reloaded. And since now you are in transaction the tsearch hook for GUC processing tries to

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Tom Lane
David Rowley writes: > It would be much nicer if you'd at least wait for benchmarks before > shooting. Benchmarks of what? We'd have to expend quite a bit of effort just to get to a place where we'd have something to benchmark. I do not think it's unreasonable of

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Tom Lane
Petr Jelinek writes: > On 07/10/17 04:19, Tom Lane wrote: >> (edit: a few minutes later, I seem to remember that equivclass.c has >> to do something special with the X=X case, so maybe it could do >> something else special instead, with little new overhead.) > So I

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-10-07 Thread Maksim Milyutin
07.10.17 16:34, Robert Haas wrote: On Fri, Oct 6, 2017 at 12:37 PM, Alvaro Herrera wrote: One thing I'm a bit worried about is how to name these subordinate indexes. They have to have names because that's how pg_class works, and those names can't all be the same,

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread David Rowley
On 7 October 2017 at 15:19, Tom Lane wrote: >> 9. Unneeded Self JOIN > >> Can't remember discussions of this. > > I can't get very excited about that one either. > > In the end, what the article fails to consider is that all of these are > tradeoffs, not unalloyed goods. If

Re: [HACKERS] separate serial_schedule useful?

2017-10-07 Thread Tom Lane
Robert Haas writes: > On Fri, Oct 6, 2017 at 4:16 PM, Tom Lane wrote: >> The other routine mistake, which I see Robert just made again, >> is to break the at-most-twenty-parallel-tests-at-once convention. >> I wonder if we can get in some sort of

Re: [HACKERS] parallelize queries containing initplans

2017-10-07 Thread Robert Haas
On Fri, Oct 6, 2017 at 7:08 AM, Amit Kapila wrote: > I have fixed the other review comment related to using safe_param_list > in the attached patch. I think I have fixed all comments given by > you, but let me know if I have missed anything or you have any other >

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-10-07 Thread Robert Haas
On Fri, Oct 6, 2017 at 12:37 PM, Alvaro Herrera wrote: > 2. create one index for each existing partition. These would be >identical to what would happen if you created the index directly on >each partition, except that there is an additional dependency to the >

Re: [HACKERS] On markers of changed data

2017-10-07 Thread Stephen Frost
Alvaro, Michael, * Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote: > Michael Paquier wrote: > > That’s actually what pg_rman is doing for what it calls incremental > > backups (perhaps that would be differential backup in PG > > terminology?), and the performance is bad as you can imagine. We

Re: [HACKERS] Issue with logical replication: MyPgXact->xmin already is valid

2017-10-07 Thread Petr Jelinek
On 06/10/17 16:46, Konstantin Knizhnik wrote: > > > On 06.10.2017 15:29, Petr Jelinek wrote: >> On 06/10/17 12:16, Konstantin Knizhnik wrote: >>> When creating logical replication slots we quite often get the following >>> error: >>> >>> ERROR:  cannot build an initial slot snapshot when

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Petr Jelinek
On 07/10/17 04:19, Tom Lane wrote: > Andres Freund writes: >> On 2017-10-06 21:33:16 -0400, Adam Brusselback wrote: >>> The article in question is here: >>> https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/ > >> That's

Re: [HACKERS] separate serial_schedule useful?

2017-10-07 Thread Robert Haas
On Fri, Oct 6, 2017 at 4:16 PM, Tom Lane wrote: > The other routine mistake, which I see Robert just made again, > is to break the at-most-twenty-parallel-tests-at-once convention. > I wonder if we can get in some sort of automated check for that. Argh. We can argue about

Re: [HACKERS] [POC] hash partitioning

2017-10-07 Thread amul sul
On Fri, Oct 6, 2017 at 5:35 PM, Jesper Pedersen wrote: > Hi Amul, > > Could you rebase on latest master ? > Sure will post that soon, but before that, I need to test hash partitioning with recent partition-wise join commit (f49842d1ee), thanks. Regards, Amul --

[HACKERS] Predicate Locks for writes?

2017-10-07 Thread Simon Riggs
SERIALIZABLE looks for chains of rw cases. When we perform UPDATEs and DELETEs we search for rows and then modify them. The current implementation views that as a read followed by a write because we issue PredicateLockTuple() during the index fetch. Is it correct that a statement that only

Re: [HACKERS] parallel worker (PID ) exited with exit code 1

2017-10-07 Thread Andreas Seltenreich
Michael Paquier writes: > On Fri, Oct 6, 2017 at 9:19 PM, tushar wrote: >> ERROR: recovery is not in progress > > Perhaps there is a way to blacklist some functions depending on the > server context. This question may be better asked directly where the > project

Re: [HACKERS] On markers of changed data

2017-10-07 Thread Alvaro Herrera
Michael Paquier wrote: > That’s actually what pg_rman is doing for what it calls incremental > backups (perhaps that would be differential backup in PG > terminology?), and the performance is bad as you can imagine. We could > have a dedicated LSN map to do such things with 4 bytes per page. I am

Re: [HACKERS] [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple

2017-10-07 Thread Alvaro Herrera
Peter Geoghegan wrote: > On Fri, Oct 6, 2017 at 2:09 PM, Wong, Yi Wen wrote: > > Yesterday, I've been spending time with pg_visibility on the pages when I > > reproduce the issue in 9.6. > > None of the all-frozen or all-visible bits are necessarily set in > > problematic

Re: [HACKERS] On markers of changed data

2017-10-07 Thread Michael Paquier
> Le 6 oct. 2017 à 23:44, Alvaro Herrera a écrit : > > Michael Paquier wrote: > >> The only sane method for Postgres is really to scan the >> page header LSNs, and of course you already know that. > > I hope the idea is not to have to scan every single page in the >