Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Thu, Oct 26, 2017 at 4:11 PM, Masahiko Sawada wrote: >> Why do we want the the backend to linger behind, once it has added its >> foreign transaction entries in the shared memory and informed resolver >> about it? The foreign connections may take their own time and even

Re: [HACKERS] WIP: BRIN bloom indexes

hi, On 10/28/2017 02:41 AM, Nico Williams wrote: > On Fri, Oct 27, 2017 at 10:06:58PM +0200, Tomas Vondra wrote: >>> + * We use an optimisation that initially we store the uint32 values >>> directly, >>> + * without the extra hashing step. And only later filling the bitmap space, >>> + * we

Re: [HACKERS] WIP: BRIN bloom indexes

On Fri, Oct 27, 2017 at 10:06:58PM +0200, Tomas Vondra wrote: > > + * We use an optimisation that initially we store the uint32 values > > directly, > > + * without the extra hashing step. And only later filling the bitmap space, > > + * we switch to the regular bloom filter mode. > > > > I

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Oct 27, 2017 at 02:13:27PM -0700, srielau wrote: > While the standard may not require a unique index for the ON clause I have > never seen a MERGE statement that did not have this property. So IMHO this > is a reasonable restrictions. I don't understand how one could have a conflict upon

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Oct 27, 2017 at 9:00 AM, Robert Haas wrote: > On Fri, Oct 27, 2017 at 4:41 PM, Simon Riggs wrote: >> I didn't say it but my intention was to just throw an ERROR if no >> single unique index can be identified. >> >> It could be possible to

Re: [HACKERS] Reading timeline from pg_control on replication slave

On Fri, Oct 27, 2017 at 1:04 AM, Andrey Borodin wrote: > I'm working on backups from replication salve in WAL-G [0] > Backups used to use result of pg_walfile_name(pg_start_backup(...)). Call to > pg_start_backup() works nice, but "pg_walfile_name() cannot be executed >

Re: [HACKERS] MERGE SQL Statement for PG11

via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=dx=9.8.79=10.12.6=email_footer_2] On Fri, Oct 27, 2017 at 2:42 PM, Peter Geoghegan wrote: On Fri, Oct 27, 2017 at 2:13 PM, srielau wrote: > While the standard may not require a unique index for the ON clause

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Oct 27, 2017 at 2:13 PM, srielau wrote: > While the standard may not require a unique index for the ON clause I have > never seen a MERGE statement that did not have this property. So IMHO this > is a reasonable restrictions. The Oracle docs on MERGE say nothing about

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

Robert Haas writes: > On Fri, Oct 27, 2017 at 7:27 PM, Alvaro Herrera > wrote: >> I noticed that RelationBuildPartitionKey is generating a partition key >> in a temp context, then creating a private context and copying the key >> into that. That

Re: [HACKERS] Causal reads take II

On Sun, Oct 1, 2017 at 10:03 AM, Thomas Munro wrote: >> I tried few more times, and I've got it two times from four attempts on a >> fresh >> installation (when all instances were on the same machine). But anyway I'll >> try >> to investigate, maybe it has something

Re: [HACKERS] MERGE SQL Statement for PG11

Simon, Nice writeup. While the standard may not require a unique index for the ON clause I have never seen a MERGE statement that did not have this property. So IMHO this is a reasonable restrictions. In fact I have only ever seen two flavors of usage: * Single row source (most often simply a

[HACKERS] git down

git.postgresql.org is down/unreachable ( git://git.postgresql.org/git/postgresql.git ) -- 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] Parallel safety for extern params

On Mon, Oct 16, 2017 at 12:59 PM, Amit Kapila wrote: >> I think the Param case should be mentioned after "... but" not before >> - i.e. referencing the child node's output... but setrefs.c might also >> have copied a Const or Param is-is. > > I am not sure if we can write

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

On Fri, Oct 27, 2017 at 7:27 PM, Alvaro Herrera wrote: > I noticed that RelationBuildPartitionKey is generating a partition key > in a temp context, then creating a private context and copying the key > into that. That seems leftover from some previous iteration of some

Re: [HACKERS] WIP: BRIN bloom indexes

Hi, On 10/27/2017 05:22 PM, Sokolov Yura wrote: > > Hi, Tomas > > BRIN bloom index is a really cool feature, that definitely should be in > core distribution (either in contrib or builtin)!!! > > Small suggestion for algorithm: > > It is well known practice not to calculate whole hash

Re: [HACKERS] WIP: BRIN bloom indexes

Hi, On 10/27/2017 07:17 PM, Nico Williams wrote: > On Thu, Oct 19, 2017 at 10:15:32PM +0200, Tomas Vondra wrote: > > A bloom filter index would, indeed, be wonderful. > > Comments: > > + * We use an optimisation that initially we store the uint32 values directly, > + * without the extra

Re: [HACKERS] logical decoding of two-phase transactions

On 2017-10-26 22:01, Sokolov Yura wrote: On 2017-09-27 14:46, Stas Kelvich wrote: On 7 Sep 2017, at 18:58, Nikhil Sontakke wrote: Hi, FYI all, wanted to mention that I am working on an updated version of the latest patch that I plan to submit to a later CF. Cool!

Re: [HACKERS] WIP: BRIN bloom indexes

On 2017-10-27 20:17, Nico Williams wrote: On Thu, Oct 19, 2017 at 10:15:32PM +0200, Tomas Vondra wrote: A bloom filter index would, indeed, be wonderful. Comments: + * We use an optimisation that initially we store the uint32 values directly, + * without the extra hashing step. And only

Re: [HACKERS] Index only scan for cube and seg

Robert Haas writes: > On Thu, Oct 26, 2017 at 12:22 PM, Andrey Borodin wrote: >> For cube there is new default opclass. > I seem to recall that changing the default opclass causes unsolvable > problems with upgrades. You might want to check the

[HACKERS] ALTER COLUMN TYPE vs. domain constraints

I found out that altering a column's type does not play nicely with domain constraints: tablecmds.c expects that only table constraints could depend on a column. Now, it's easy to hit that with domains over composite, so I propose to fix it in HEAD with the attached patch. However, if you really

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

Alvaro Herrera wrote: > I'm now working on the ability to build unique indexes (and unique > constraints) on top of this. So I think there's not a lot of additional code required to support unique indexes with the restrictions mentioned; proof-of-concept (with several holes still) attached. As

Re: [HACKERS] WIP: BRIN bloom indexes

On Thu, Oct 19, 2017 at 10:15:32PM +0200, Tomas Vondra wrote: A bloom filter index would, indeed, be wonderful. Comments: + * We use an optimisation that initially we store the uint32 values directly, + * without the extra hashing step. And only later filling the bitmap space, + * we switch to

Re: [HACKERS] Index only scan for cube and seg

On Thu, Oct 26, 2017 at 12:22 PM, Andrey Borodin wrote: > For cube there is new default opclass. I seem to recall that changing the default opclass causes unsolvable problems with upgrades. You might want to check the archives for previous discussions of this issue;

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Oct 27, 2017 at 4:41 PM, Simon Riggs wrote: > I didn't say it but my intention was to just throw an ERROR if no > single unique index can be identified. > > It could be possible to still run MERGE in that situaton but we would > need to take a full table lock at

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Oct 27, 2017 at 6:24 AM, Robert Haas wrote: > I think one of the reasons why Peter Geoghegan decided to pursue > INSERT .. ON CONFLICT UPDATE was that, because it is non-standard SQL > syntax, he felt free to mandate a non-standard SQL requirement, namely > the

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Oct 27, 2017 at 7:41 AM, Simon Riggs wrote: > Good points. > > I didn't say it but my intention was to just throw an ERROR if no > single unique index can be identified. You'd also throw an error when there was no "upsert compatible" join quals, I take it? I don't

Re: [HACKERS] WIP: BRIN bloom indexes

On 2017-10-19 23:15, Tomas Vondra wrote: Hi, The BRIN minmax opclasses work well only for data where the column is somewhat correlated to physical location in a table. So it works great for timestamps in append-only log tables, for example. When that is not the case (non-correlated columns) the

Re: [HACKERS] MERGE SQL Statement for PG11

On 27 October 2017 at 15:24, Robert Haas wrote: > On Fri, Oct 27, 2017 at 10:55 AM, Simon Riggs wrote: >> Questions? > > I think one of the reasons why Peter Geoghegan decided to pursue > INSERT .. ON CONFLICT UPDATE was that, because it is

Re: [HACKERS] Linking libpq statically to libssl

On Fri, Oct 27, 2017 at 2:37 PM, Tom Lane wrote: > Daniele Varrazzo writes: >> I have a problem building binary packages for psycopg2. Binary >> packages ship with their own copies of libpq and libssl; however if >> another python package links to

Re: [HACKERS] proposal: schema variables

2017-10-27 15:38 GMT+02:00 Gilles Darold : > Le 26/10/2017 à 09:21, Pavel Stehule a écrit : > > Hi, > > > > I propose a new database object - a variable. The variable is > > persistent object, that holds unshared session based not transactional > > in memory value of

[HACKERS] bgwriter_lru_maxpages range in postgresql.conf

With v10, the upper limit on bgwriter_lru_maxpages was changed from 1000 to INT_MAX / 2, but the postgresql.conf.sample was not updated. #bgwriter_lru_maxpages = 100# 0-1000 max buffers written/round I don't see any precedence for including INT_MAX-type limits in the sample config file,

Re: [HACKERS] proposal: schema variables

Le 26/10/2017 à 09:21, Pavel Stehule a écrit : > Hi, > > I propose a  new database object - a variable. The variable is > persistent object, that holds unshared session based not transactional > in memory value of any type. Like variables in any other languages. > The persistence is required for

Re: [HACKERS] Linking libpq statically to libssl

Daniele Varrazzo writes: > I have a problem building binary packages for psycopg2. Binary > packages ship with their own copies of libpq and libssl; however if > another python package links to libssl the library will be imported > twice with conflicting symbols,

Re: [HACKERS] [GENERAL] Postgres 10 manual breaks links with anchors

Peter Eisentraut writes: > On 10/26/17 16:10, Tom Lane wrote: >> In view of commit 1ff01b390, aren't we more or less locked into >> lower-case anchors going forward? > The details are more complicated. ... Ah. I'd imagined that we were using the original case

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Oct 27, 2017 at 10:55 AM, Simon Riggs wrote: > Questions? I think one of the reasons why Peter Geoghegan decided to pursue INSERT .. ON CONFLICT UPDATE was that, because it is non-standard SQL syntax, he felt free to mandate a non-standard SQL requirement, namely

Re: [HACKERS] Re: Burst in WAL size when UUID is used as PK while full_page_writes are enabled

On Fri, Oct 27, 2017 at 5:46 PM, Thomas Kellerer wrote: > akapila wrote: > >> You might want to give a try with the hash index if you are planning >> to use PG10 and your queries involve equality operations. > > But you can't replace the PK index with a hash index, because

Re: [HACKERS] Burst in WAL size when UUID is used as PK while full_page_writes are enabled

On Fri, Oct 27, 2017 at 5:36 PM, Alvaro Herrera wrote: > Amit Kapila wrote: > >> You might want to give a try with the hash index if you are planning >> to use PG10 and your queries involve equality operations. > > So, btree indexes on monotonically increasing sequences

Re: [HACKERS] WIP: BRIN bloom indexes

On Fri, Oct 27, 2017 at 2:55 PM, Alvaro Herrera wrote: > I was rather thinking that if we can make this very robust against the > index growing out of proportion, we should consider ditching the > original minmax and replace it with multirange minmax, which seems like >

Re: [HACKERS] Re: protocol version negotiation (Re: Libpq PGRES_COPY_BOTH - version compatibility)

On Thu, Oct 19, 2017 at 1:35 AM, Badrul Chowdhury wrote: > The new functionality is for sending 64bit ints. I think 32bits is sufficient > for the information we want to pass around in the protocol negotiation phase, > so I left this part unchanged. No, it isn't. That

Re: [HACKERS] WIP: BRIN bloom indexes

Tomas Vondra wrote: > Not sure "a number of in-core opclasses" is a good reason to (not) add > new ones. Also, we already have two built-in BRIN opclasses (minmax and > inclusion). > > In general, "BRIN bloom" can be packed as a contrib module (at least I > believe so). That's not the case for

Re: [HACKERS] parallel.c oblivion of worker-startup failures

On Tue, Sep 19, 2017 at 8:47 AM, Amit Kapila wrote: > On Mon, Sep 18, 2017 at 10:00 PM, Tom Lane wrote: >> Amit Kapila writes: >>> Attached patch fixes these problems. >> >> Hmm, this patch adds a kill(notify_pid) after one

Re: [HACKERS] MERGE SQL Statement for PG11

Simon Riggs wrote: > Earlier thoughts on how this could/could not be done were sometimes > imprecise or inaccurate, so I have gone through the command per > SQL:2011 spec and produced a definitive spec in the form of an SGML > ref page. This is what I intend to deliver for PG11. Nice work. I

[HACKERS] Linking libpq statically to libssl

Hello, I have a problem building binary packages for psycopg2. Binary packages ship with their own copies of libpq and libssl; however if another python package links to libssl the library will be imported twice with conflicting symbols, likely resulting in a segfault (see

[HACKERS] Re: Burst in WAL size when UUID is used as PK while full_page_writes are enabled

akapila wrote: > You might want to give a try with the hash index if you are planning > to use PG10 and your queries involve equality operations. But you can't replace the PK index with a hash index, because hash indexes don't support uniqueness. -- Sent from:

Re: [HACKERS] Burst in WAL size when UUID is used as PK while full_page_writes are enabled

Amit Kapila wrote: > You might want to give a try with the hash index if you are planning > to use PG10 and your queries involve equality operations. So, btree indexes on monotonically increasing sequences don't write tons of full page writes because typically the same page is touched many times

Re: [HACKERS] Burst in WAL size when UUID is used as PK while full_page_writes are enabled

On Fri, Oct 27, 2017 at 11:26 AM, sanyam jain wrote: > Hi, > > I was reading the blog > https://blog.2ndquadrant.com/on-the-impact-of-full-page-writes . > > My queries: > > How randomness of UUID will likely to create new leaf page in btree index? > In my understanding as

Re: [HACKERS] [GENERAL] Postgres 10 manual breaks links with anchors

On 10/26/17 16:10, Tom Lane wrote: > Peter Eisentraut writes: >> On 10/16/17 03:19, Thomas Kellerer wrote: >>> I don't know if this is intentional, but the Postgres 10 manual started to >>> use lowercase IDs as anchors in the manual. > >> Here is a patch that

Re: [HACKERS] inconsistency in process names - bgworker: logical replication launcher

2017-10-27 13:03 GMT+02:00 Peter Eisentraut < peter.eisentr...@2ndquadrant.com>: > On 10/27/17 04:06, Pavel Stehule wrote: > > Why buildin process has prefix bgworker? > > Implementation detail. This has been changed in master already. > ok Thank you Pavel > > -- > Peter Eisentraut

Re: [HACKERS] Add support for tuple routing to foreign partitions

On 2017/10/26 16:40, Etsuro Fujita wrote: Other changes I made to the executor are: (1) currently, we set the RT index for the root partitioned table to ri_RangeTableIndex of partitions' ResultRelInfos, but the proposed EXPLAIN requires that the partition's ri_RangeTableIndex is set to the RT

Re: [HACKERS] inconsistency in process names - bgworker: logical replication launcher

On 10/27/17 04:06, Pavel Stehule wrote: > Why buildin process has prefix bgworker? Implementation detail. This has been changed in master already. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via

Re: [HACKERS] Partition-wise aggregation/grouping

Hi, Attached new patch-set here. Changes include: 1. Added separate patch for costing Append node as discussed up-front in the patch-set. 2. Since we now cost Append node, we don't need partition_wise_agg_cost_factor GUC. So removed that. The remaining patch hence merged into main implementation

Re: [HACKERS] Parallel safety for extern params

On Mon, Oct 16, 2017 at 4:29 PM, Amit Kapila wrote: > On Sat, Oct 14, 2017 at 1:51 AM, Robert Haas wrote: > >> I think the bug is in ExecGather(Merge): it assumes that if we're in >> parallel mode, it's OK to start workers. But actually, it

Re: [HACKERS] path toward faster partition pruning

On Fri, Oct 27, 2017 at 2:41 PM, Amit Langote wrote: > 0001: added some new tests > 0002: no change > 0003: fixed issue that Rajkumar reported (cope with Params properly) > 0004: no change > 0005: fix the case to prune the default partition when warranted (the >

Re: [HACKERS] MERGE SQL Statement for PG11

Hey, It looks quite nice. Personally I'd like to also have the returning statement, and have the number of deleted and inserted rows as separate numbers in the output message. regards Szymon Lipiński pt., 27.10.2017, 10:56 użytkownik Simon Riggs napisał: > I'm working on

Re: [HACKERS] WIP: BRIN bloom indexes

hi, On 10/27/2017 09:34 AM, Simon Riggs wrote: > On 27 October 2017 at 07:20, Robert Haas wrote: >> On Thu, Oct 19, 2017 at 10:15 PM, Tomas Vondra >> wrote: >>> Let's see a query like this: >>> >>> select * from bloom_test >>> where

Re: [HACKERS] Burst in WAL size when UUID is used as PK while full_page_writes are enabled

On 10/27/2017 07:56 AM, sanyam jain wrote: > Hi, > > I was reading the > blog https://blog.2ndquadrant.com/on-the-impact-of-full-page-writes . > For the record, I assume you're referring to this part: With BIGSERIAL new values are sequential, and so get inserted to the same leaf

[HACKERS] MERGE SQL Statement for PG11

I'm working on re-submitting MERGE for PG11 Earlier thoughts on how this could/could not be done were sometimes imprecise or inaccurate, so I have gone through the command per SQL:2011 spec and produced a definitive spec in the form of an SGML ref page. This is what I intend to deliver for PG11.

[HACKERS] inconsistency in process names - bgworker: logical replication launcher

Hi Why buildin process has prefix bgworker? 1907 ?Ss13:00 postgres: ides ides_immaj_prac 192.168.1.50(3524) idle 1941 ?Ss 0:05 postgres: ides ides_immaj_prac 192.168.1.50(3527) idle 3706 ?Ss 0:00 postgres: ides ides_immaj_prac 192.168.1.50(4012) idle 11924

Re: [HACKERS] path toward faster partition pruning

On Thu, Oct 26, 2017 at 4:47 PM, Amit Langote wrote: > > Meanwhile, attached updated set of patches including fixes for the typos > you reported in the other message. Updated 0005 fixes the first bug (the > Case 1 in your email), while other patches 0002-0004 are

[HACKERS] Reading timeline from pg_control on replication slave

Hi, hackers! I'm working on backups from replication salve in WAL-G [0] Backups used to use result of pg_walfile_name(pg_start_backup(...)). Call to pg_start_backup() works nice, but "pg_walfile_name() cannot be executed during recovery." This function has LSN as argument and reads TimeLineId

Re: [HACKERS] Re: Is anything preventing us from allowing write to foreign tables from standby?

On Thu, Oct 26, 2017 at 11:14 AM, Ashutosh Bapat wrote: > In a nearby thread, we are discussing about atomic commit of > transactions involving foreign transactions. For maintaining > consistency, atomicity of transactions writing to foreign server, we > will need

[HACKERS] Typos in src/backend/optimizer/README

Hi, This sentence in the section of Partition-wise joins in src/backend/optimizer/README should be fixed: "This technique of breaking down a join between partition tables into join between their partitions is called partition-wise join." (1) s/a join between partition tables/a join between

Re: [HACKERS] WIP: BRIN bloom indexes

On 27 October 2017 at 07:20, Robert Haas wrote: > On Thu, Oct 19, 2017 at 10:15 PM, Tomas Vondra > wrote: >> Let's see a query like this: >> >> select * from bloom_test >> where id = '8db1d4a6-31a6-e9a2-4e2c-0e842e1f1772'; >> >> The

Re: [HACKERS] Implementing pg_receivewal --no-sync

On Thu, Oct 26, 2017 at 10:46 PM, Robert Haas wrote: > On Wed, Oct 25, 2017 at 10:03 PM, Michael Paquier > wrote: >> This sentence is actually wrong, a feedback message is never sent with >> the feedback message. > > Eh? "A feedback message is

Re: [HACKERS] CurTransactionContext freed before transaction COMMIT ???

Thanks for your responses Michael and Amit Kapila, Yes, we have included your suggestions in our code and started testing to reproduce the same issue. In case we encounter this issue again we will get back here. Regards G. Sai Ram

Re: [HACKERS] proposal: schema variables

2017-10-27 7:47 GMT+02:00 Tsunakawa, Takayuki < tsunakawa.ta...@jp.fujitsu.com>: > From: pgsql-hackers-ow...@postgresql.org > > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Pavel Stehule > > I propose a new database object - a variable. The variable is persistent > > object, that

Re: [HACKERS] path toward faster partition pruning

On 2017/10/27 13:57, Robert Haas wrote: > On Fri, Oct 27, 2017 at 3:17 AM, Amit Langote > wrote: >>> I don't think we really want to get into theorem-proving here, because >>> it's slow. >> >> Just to be clear, I'm saying we could use theorem-proving (if at all)