Re: block-level incremental backup

On Fri, Aug 9, 2019 at 11:56 PM Jeevan Ladhe wrote: > Hi Robert, > > On Fri, Aug 9, 2019 at 6:40 PM Robert Haas wrote: > >> On Thu, Aug 8, 2019 at 8:37 PM Jeevan Ladhe >> wrote: >> > + if (!XLogRecPtrIsInvalid(previous_lsn)) >> > + appendStringInfo(labelfile, "PREVIOUS WAL

Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)

On Wed, Jul 10, 2019 at 08:07:49PM -0400, Bruce Momjian wrote: > On Thu, Jul 11, 2019 at 12:18:47AM +0200, Tomas Vondra wrote: > > On Wed, Jul 10, 2019 at 06:04:30PM -0400, Stephen Frost wrote: > > > Greetings, > > > > > > * Tomas Vondra (tomas.von...@2ndquadrant.com) wrote: > > > > On Wed, Jul

Re: [survey] New "Stable" QueryId based on normalized query text

On Mon, Aug 12, 2019 at 4:01 PM Evgeniy Efimkin wrote: > > > One problem with pg_stat_statement's normalized query is that it's not > > stable, it's storing the normalized version of the first query string > > passed when an entry is created. So you could have different strings > > depending on

Re: errbacktrace

Hi so I agree with unconditionally defining that symbol. > > Nitpicking dept: I think in these tests: > > + if (!edata->backtrace && > + edata->funcname && > + backtrace_function[0] && > + strcmp(backtrace_function, edata->funcname) == 0) > + set_backtrace(edata, 2); > >

Re: [survey] New "Stable" QueryId based on normalized query text

On Mon, Aug 12, 2019 at 2:40 PM Jim Finnerty wrote: > > If hashing names instead of using OIDs is too expensive for some workload, > then that workload would need to be able to turn statement hashing off. So > it needs to be optional, just like queryId is optionally computed today. > For many

Re: block-level incremental backup

On Fri, Aug 9, 2019 at 6:36 PM Robert Haas wrote: > On Wed, Aug 7, 2019 at 5:46 AM Jeevan Chalke > wrote: > > So, do you mean we should just do fread() and fwrite() for the whole > file? > > > > I thought it is better if it was done by the OS itself instead of > reading 1GB > > into the memory

Re: block-level incremental backup

On Mon, Aug 12, 2019 at 7:57 AM Jeevan Chalke wrote: > Agree that we can certainly use open(), read(), write(), and close() here, but > given that pg_basebackup.c and basbackup.c are using file operations, I think > using fopen(), fread(), fwrite(), and fclose() will be better here, at-least >

Re: [survey] New "Stable" QueryId based on normalized query text

On Mon, Aug 12, 2019 at 2:52 PM Evgeniy Efimkin wrote: > > Hi! > What about adding new column in pg_stat_statements e.g. sql_id it's hash from > normalized query. Аnd add function which get that hash (using raw_parser, > raw_expression_tree_walker) for any query > ` > postgres=# select

Re: [survey] New "Stable" QueryId based on normalized query text

> One problem with pg_stat_statement's normalized query is that it's not > stable, it's storing the normalized version of the first query string > passed when an entry is created. So you could have different strings > depending on whether the query was fully qualified or relying on > search

Re: block-level incremental backup

On Mon, Aug 12, 2019 at 5:29 PM Jeevan Chalke < jeevan.cha...@enterprisedb.com> wrote: > > > On Fri, Aug 9, 2019 at 11:56 PM Jeevan Ladhe < > jeevan.la...@enterprisedb.com> wrote: > >> Hi Robert, >> >> On Fri, Aug 9, 2019 at 6:40 PM Robert Haas wrote: >> >>> On Thu, Aug 8, 2019 at 8:37 PM Jeevan

Re: Add "password_protocol" connection parameter to libpq

On 8/11/19 3:56 PM, Peter Eisentraut wrote: > On 2019-08-11 21:46, Jonathan S. Katz wrote: >> On 8/11/19 1:00 PM, Peter Eisentraut wrote: >>> On 2019-08-09 23:56, Jeff Davis wrote: 1. Hierarchical semantics, where you specify the least-secure acceptable method:

Re: [survey] New "Stable" QueryId based on normalized query text

Hi! What about adding new column in pg_stat_statements e.g. sql_id it's hash from normalized query. Аnd add function which get that hash (using raw_parser, raw_expression_tree_walker) for any query ` postgres=# select get_queryid('select 1'); get_queryid - 680388963 (1 row) `

Re: Add "password_protocol" connection parameter to libpq

Greetings, * Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote: > On 2019-08-12 18:02, Jeff Davis wrote: > > https://postgr.es/m/daf0017a1a5c2caabf88a4e00f66b4fcbdfeccad.camel%40j-davis.com > > > > The weakness of proposal #1 is that it's not very "future-proof" and we > > would likely

Re: clean up obsolete initdb locale handling

Peter Eisentraut writes: > OK, let's do it like that. Updated patch attached. LGTM, but I don't have the ability to test it on Windows. regards, tom lane

Re: Improve search for missing parent downlinks in amcheck

On Fri, Jul 19, 2019 at 3:21 AM Peter Geoghegan wrote: > On Tue, Apr 30, 2019 at 5:58 PM Peter Geoghegan wrote: > > I will think about a simple fix, but after the upcoming point release. > > There is no hurry. > > Attached draft patch uses RelationGetNumberOfBlocks() to size each of > the two

Re: POC: converting Lists into arrays

Alvaro Herrera writes: > On 2019-Aug-09, Tom Lane wrote: >> I poked at this, and attached is a patch, but again I'm not seeing >> that there's any real performance-based argument for it. > I'm confused. I thought that the point of doing this wasn't that we > wanted to improve performance, but

Re: Global temporary tables

po 12. 8. 2019 v 18:19 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal: > Hi, > > On 11.08.2019 10:14, Pavel Stehule wrote: > > > Hi > > >> There is one more problem with global temporary tables for which I do not >> know good solution now: collecting statistic. >> As far as

Re: [survey] New "Stable" QueryId based on normalized query text

my understanding is * pg_stat_statements.track = 'none' or 'top' (default) or 'all' to make queryId optionally computed * a new GUC: pg_stat_statements.queryid_based = 'oids' (default) or 'names' or 'fullnames' to choose the queryid computation algorithm am I rigth ? -- Sent from:

Re: Add "password_protocol" connection parameter to libpq

On Sun, 2019-08-11 at 19:00 +0200, Peter Eisentraut wrote: > On 2019-08-09 23:56, Jeff Davis wrote: > > 1. Hierarchical semantics, where you specify the least-secure > > acceptable method: > > > > password_protocol = {any,md5,scram-sha-256,scram-sha-256-plus} > > What would the hierarchy be if

Re: errbacktrace

po 12. 8. 2019 v 19:06 odesílatel Peter Eisentraut < peter.eisentr...@2ndquadrant.com> napsal: > On 2019-08-12 13:19, Pavel Stehule wrote: > > If I understand well, backtrace is displayed only when edata->funcname > > is same like backtrace_function GUC. Isn't it too strong limit? > > > > For

Re: Add "password_protocol" connection parameter to libpq

Stephen Frost writes: > I'm not really thrilled with approach #2 because it means the user > will have to know which of the PG authentication methods involve, eg, > sending the password in the clear to the server, and which don't, if > what they're really looking for is "don't send my password in

Re: Problem with default partition pruning

v3-0001 still seems to leave things a bit duplicative. I think we can make it better if we move the logic to set RelOptInfo->partition_qual to a separate routine (set_baserel_partition_constraint mirroring the existing set_baserel_partition_key_exprs), and then call that from both places that

Re: Add "password_protocol" connection parameter to libpq

On 2019-08-12 19:26, Tom Lane wrote: > What problem do we actually need to solve here? > > If the known use-case is just "don't send my password in the clear", > maybe we should just change libpq to refuse to do that, ie reject > plain-password auth methods unless SSL is on (except maybe over >

Re: POC: converting Lists into arrays

David Rowley writes: > On Fri, 9 Aug 2019 at 04:24, Tom Lane wrote: >> Has anyone got further thoughts about naming around list_concat >> and friends? >> If not, I'm inclined to go ahead with the concat-improvement patch as >> proposed in [1], modulo the one improvement David spotted. >> [1]

Re: Add "password_protocol" connection parameter to libpq

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > I'm not really thrilled with approach #2 because it means the user > > will have to know which of the PG authentication methods involve, eg, > > sending the password in the clear to the server, and which don't, if > >

Re: Problem with default partition pruning

On Mon, 12 Aug 2019 at 18:45, Alvaro Herrera wrote: > I think that should appease > Simon's performance concern for the most common case of default > partition not existing. > Much appreciated, thank you. -- Simon Riggshttp://www.2ndQuadrant.com/

Re: clean up obsolete initdb locale handling

On 2019-08-08 17:51, Tom Lane wrote: > However, I don't much like the choice to set LC_COLLATE and LC_CTYPE > differently. That seems to be risking weird behavior, and for what? > I'd be inclined to just remove the WIN32 stanza, initialize all > three of these variables with "", and explain it

Re: Removing unneeded downlink field from nbtree stack struct

16.07.2019 2:16, Peter Geoghegan wrote: Attached patch slightly simplifies _bt_getstackbuf() by making it accept a child BlockNumber argument, rather than requiring that callers store the child block number in the parent stack item's bts_btentry field. We can remove the bts_btentry field from

Re: SegFault on 9.6.14

On 2019-Aug-12, Thomas Munro wrote: > That's possibly relevant because it means we'd have a ParallelContext > or some new overarching object that has a lifetime that is longer than > the individual Gather nodes' processes and instrumentation data. I'm > not saying we need to discuss any details

Re: Global temporary tables

Hi, On 11.08.2019 10:14, Pavel Stehule wrote: Hi There is one more problem with global temporary tables for which I do not know good solution now: collecting statistic. As far as each backend has its own data, generally them may need different query plans. Right now if

Re: Add "password_protocol" connection parameter to libpq

On 2019-08-12 18:02, Jeff Davis wrote: > https://postgr.es/m/daf0017a1a5c2caabf88a4e00f66b4fcbdfeccad.camel%40j-davis.com > > The weakness of proposal #1 is that it's not very "future-proof" and we > would likely need to change something about it later when we support > new methods. That wouldn't

Re: errbacktrace

On 2019-08-12 13:19, Pavel Stehule wrote: > If I understand well, backtrace is displayed only when edata->funcname > is same like backtrace_function GUC. Isn't it too strong limit? > > For example, I want to see backtrace for all PANIC level errors on > production, and I would not to limit the

Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)

Masahiko Sawada wrote: > Attached the draft version patch sets of per tablespace transparent > data at rest encryption. The patch doesn't support full functionality, > it includes: > > * Per tablespace encryption > * Encryption and decryption buffer data when disk I/O. > * 2 tier key hierarchy

Do not check unlogged indexes on standby

Hi hackers! Currently, if we check indexes on standby we often get man-psbpshn0skhsxynd/xiva_xtable_testing_01 R # select bt_index_check('xiva_loadtest.pk_uid'); ERROR: 58P01: could not open file "base/16453/125407": No such file or directory I think that we should print warning and that's

Asymmetric partition-wise JOIN

Hello, PostgreSQL optimizer right now considers join pairs on only non-partition - non-partition or partition-leaf - partition-leaf relations. On the other hands, it is harmless and makes sense to consider a join pair on non-partition - partition-leaf. See the example below. ptable is

Re: POC: Cleaning up orphaned files using undo logs

On Tue, Jul 30, 2019 at 12:21 PM Thomas Munro wrote: > > Hi Dilip, > > > commit 2f3c127b9e8bc7d27cf7adebff0a355684dfb94e > > Author: Dilip Kumar > > Date: Thu May 2 11:28:13 2019 +0530 > > > >Provide interfaces to store and fetch undo records. > > +#include "commands/tablecmds.h" >

Re: progress report for ANALYZE

Hi Robert and All! On 2019/08/02 2:48, Robert Haas wrote:> On Thu, Aug 1, 2019 at 4:45 AM Thomas Munro wrote: On Tue, Jul 23, 2019 at 4:51 PM Tatsuro Yamada wrote: Attached v4 patch file only includes this fix. I've moved this to the September CF, where it is in "Needs review" state.

Re: [HACKERS] CLUSTER command progress monitor

Hi Alvaro! On 2019/08/02 3:43, Alvaro Herrera wrote: Hmm, I'm trying this out now and I don't see the index_rebuild_count ever go up. I think it's because the indexes are built using parallel index build ... or maybe it was the table AM changes that moved things around, not sure. There's a

Re: Global temporary tables

On Tue, 13 Aug 2019 at 00:47, Pavel Stehule wrote: > But Postgres is not storing this information now anywhere else except >> statistic, isn't it? >> > > not only - critical numbers are reltuples, relpages from pg_class > That's a very good point. relallvisible too. How's the global temp table

Re: SegFault on 9.6.14

On Tue, Aug 13, 2019 at 7:07 AM Alvaro Herrera wrote: > On 2019-Aug-12, Thomas Munro wrote: > > That's possibly relevant because it means we'd have a ParallelContext > > or some new overarching object that has a lifetime that is longer than > > the individual Gather nodes' processes and

Re: SegFault on 9.6.14

Robert Haas writes: > Being able to do that sort of thing was one of my goals in designing > the ExecShutdownNode stuff. Unfortunately, it's clear from this bug > report that it's still a few bricks short of a load, and Tom doesn't > seem real optimistic about how easy it will be to buy those

Re: Do not check unlogged indexes on standby

On Mon, Aug 12, 2019 at 2:58 AM Andrey Borodin wrote: > BTW I really want to enable rightlink-leftlink invariant validation on > standby.. That seems very hard. My hope was that bt_check_index() can detect the same problem a different way. The bt_right_page_check_scankey() cross-page check

Re: Regression test failure in regression test temp.sql

On Sun, Aug 11, 2019 at 03:59:06PM -0400, Tom Lane wrote: > I hacked temp.sql to print a couple different plans (doing it that way, > rather than manually, just to ensure that I was getting plans matching > what would actually happen right there). And what I see, as attached, > is that IOS and

Re: pg_stat_replication lag fields return non-NULL values even with NULL LSNs

On Tue, Aug 13, 2019 at 11:15:42AM +1200, Thomas Munro wrote: > Hmm. It's working as designed, but indeed it's not very newsworthy > information in this case. If you run pg_receivewal --synchronous then > you get sensible looking flush_lag times. Without that, flush_lag > only goes up, and of

Re: Add "password_protocol" connection parameter to libpq

On Mon, Aug 12, 2019 at 07:05:08PM +0200, Peter Eisentraut wrote: > In this context, I would prefer #2, but I would expand that to cover all > authentication methods, not only password methods. I tend to prefer #2 as well and that's the kind of approach we were tending to agree on when we

Re: SegFault on 9.6.14

On Tue, Aug 13, 2019 at 3:18 AM Tom Lane wrote: > > Robert Haas writes: > > Being able to do that sort of thing was one of my goals in designing > > the ExecShutdownNode stuff. Unfortunately, it's clear from this bug > > report that it's still a few bricks short of a load, and Tom doesn't > >

small improvement of the elapsed time for truncating heap in vacuum

Hi, I got following log messages when measured the heap truncating duration in a vacuum. = INFO: "dst": suspending truncate due to conflicting lock request INFO: "dst": truncated 550073 to 101472 pages DETAIL: CPU: user: 0.35 s, system: 4.92

Re: POC: Cleaning up orphaned files using undo logs

On Thu, Jul 18, 2019 at 4:58 PM Amit Kapila wrote: > > On Tue, Jul 16, 2019 at 2:20 PM Dilip Kumar wrote: > > > > Few comments on the new patch: > > 1. > Additionally, > +there is a mechanism for multi-insert, wherein multiple records are prepared > +and inserted at a time. > > Which mechanism

Re: Problem with default partition pruning

Hi Alvaro, On Tue, Aug 13, 2019 at 2:45 AM Alvaro Herrera wrote: > v3-0001 still seems to leave things a bit duplicative. I think we can > make it better if we move the logic to set RelOptInfo->partition_qual to > a separate routine (set_baserel_partition_constraint mirroring the > existing

Re: Problem while updating a foreign table pointing to a partitioned table on foreign server

On Mon, Aug 12, 2019 at 05:32:08PM -0400, Alvaro Herrera wrote: > So do we have an updated patch for this? It's been a while since this > patch saw any movement ... Please note that this involves a couple of people in Japan, and this week is the Obon vacation season for a lot of people. So

Re: Regression test failure in regression test temp.sql

On Tue, Aug 13, 2019 at 02:51:03PM +1200, Thomas Munro wrote: > Here's another one that seems to fit that pattern. > https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink=2019-08-11%2007%3A33%3A39 Indeed. Good catch! Perhaps you would like to fix it? There are two queries in need of an

Re: Small patch to fix build on Windows

On Fri, Aug 09, 2019 at 11:21:52AM +0300, Dmitry Igrishin wrote: > Personally I don't care. I used || notation only in order to be > consistent, since this notation is already used in Solution.pm. If > this consistency is not required let me provide a patch with {} > notation. What do you think?

Re: POC: Cleaning up orphaned files using undo logs

On Fri, Jul 19, 2019 at 2:28 PM Amit Kapila wrote: > > On Thu, Jul 11, 2019 at 9:17 AM Dilip Kumar wrote: > > > > On Thu, Jul 11, 2019 at 12:38 AM Robert Haas wrote: > > > > > > I don't like the fact that undoaccess.c has a new global, > > > undo_compression_info. I haven't read the code

Re: Fix typos and inconsistencies for HEAD (take 10)

On Sun, Aug 11, 2019 at 11:00:00AM +0300, Alexander Lakhin wrote: > 10.44. serendipitiously -> serendipitously I didn't know that this even was a word: https://www.thefreedictionary.com/serendipitously But it seems to come from Horace Walpole. > 10.50. sigsetmask -> pgsigsetmask It should be

Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)

On Sat, Aug 10, 2019 at 08:06:17AM -0400, Bruce Momjian wrote: > So, I just had an indea if we use separate encryption keys for > heap/index and for WAL --- we already know we will have an offline tool > that can rotate the passphrase or encryption keys. If we allow the > encryption keys to be

Re: Regression test failure in regression test temp.sql

On Tue, Aug 13, 2019 at 1:58 PM Michael Paquier wrote: > On Sun, Aug 11, 2019 at 03:59:06PM -0400, Tom Lane wrote: > > I hacked temp.sql to print a couple different plans (doing it that way, > > rather than manually, just to ensure that I was getting plans matching > > what would actually happen

Re: Add "password_protocol" connection parameter to libpq

On Fri, Aug 09, 2019 at 09:28:50AM -0400, Stephen Frost wrote: > I don't really care for auth_protocol as that's pretty close to > "auth_method" and that isn't what we're talking about here- this isn't > the user picking the auth method, per se, but rather saying which of the > password-based

Re: pg_stat_replication lag fields return non-NULL values even with NULL LSNs

On Tue, Aug 13, 2019 at 2:20 PM Michael Paquier wrote: > On Tue, Aug 13, 2019 at 11:15:42AM +1200, Thomas Munro wrote: > > One thing I noticed in passing is that you always get the same times > > in the write_lag and flush_lag columns, in --synchronous mode, and the > > times updates

Re: POC: Cleaning up orphaned files using undo logs

On Wed, Jul 24, 2019 at 11:28 AM Rushabh Lathia wrote: > > Hi, > > I have stated review of > 0008-Provide-interfaces-to-store-and-fetch-undo-records.patch, here are few > quick comments. > > 1) README.undointerface should provide more information like API details or > the sequence in which API

Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)

On Thu, Jul 25, 2019 at 11:30:55PM -0400, Alvaro Herrera wrote: > On 2019-Jul-25, Alvaro Herrera wrote: > > On the other hand if the Key and IV are reused between messages then > > the same plaintext will lead to the same ciphertext, so you can > > potentially decrypt a message using a

Re: Duplicated LSN in ReorderBuffer

On 2019-Aug-07, Andres Freund wrote: > I think we would need to do this for all values of > SnapBuildCurrentState() - after all the problem occurs because we > *previously* didn't assign subxids to the toplevel xid. Compared to the > cost of catalog changes, ReorderBufferAssignChild() is really

Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)

On Wed, Aug 7, 2019 at 08:56:18AM -0400, Sehrope Sarkuni wrote: > On Mon, Aug 5, 2019 at 9:02 PM Bruce Momjian wrote: > I don't think we want to add a MAC at this point since the MAC for 8k > pages seems unattainable. > > Even without a per-page MAC, a MAC at some level for WAL has its

Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)

On Tue, Jul 30, 2019 at 04:48:31PM -0400, Bruce Momjian wrote: > I am not even clear if pg_upgrade preserving relfilenode is possible --- > when we wrap the relfilenode counter, does it start at 1 or at the > first-user-relation-oid? If the former, it could conflict with oids > assigned to new

Re: Do not check unlogged indexes on standby

On Mon, Aug 12, 2019 at 2:58 AM Andrey Borodin wrote: > Currently, if we check indexes on standby we often get > > man-psbpshn0skhsxynd/xiva_xtable_testing_01 R # select > bt_index_check('xiva_loadtest.pk_uid'); > ERROR: 58P01: could not open file "base/16453/125407": No such file or >

Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)

On Wed, Jul 31, 2019 at 09:43:00AM -0400, Sehrope Sarkuni wrote: > On Wed, Jul 31, 2019 at 2:32 AM Masahiko Sawada wrote: > For WAL encryption,  before flushing WAL we encrypt whole 8k WAL page > and then write only the encrypted data of the new WAL record using > pg_pwrite() rather

Re: SegFault on 9.6.14

On Mon, Aug 12, 2019 at 5:48 PM Tom Lane wrote: > To clarify my position --- I think it's definitely possible to improve > the situation a great deal. We "just" have to pass down more information > about whether rescans are possible. What I don't believe is that that > leads to a bug fix that

Re: Support for jsonpath .datetime() method

On Thu, Aug 1, 2019 at 1:31 PM Thomas Munro wrote: > On Sat, Jul 27, 2019 at 2:43 AM Andrew Dunstan > wrote: > > On 7/23/19 6:48 PM, Nikita Glukhov wrote: > > > Some concrete pieces of review: > > >> + > > >> +FF1 > > >> +decisecond (0-9) > > >> + > > >> > > >>

Re: pg_stat_replication lag fields return non-NULL values even with NULL LSNs

On Wed, Jul 17, 2019 at 1:52 PM Michael Paquier wrote: > I got surprised by the following behavior from pg_stat_get_wal_senders > when connecting for example pg_receivewal to a primary: > =# select application_name, flush_lsn, replay_lsn, flush_lag, > replay_lag from pg_stat_replication; >

Re: Problem while updating a foreign table pointing to a partitioned table on foreign server

On 2018-Nov-16, Tom Lane wrote: > Etsuro Fujita writes: > > [ fix-foreign-modify-efujita-2.patch ] > > Um ... wow, I do not like anything about this. Adding a "tableoid = X" > constraint to every remote update query seems awfully expensive, > considering that (a) it's useless for

Re: SegFault on 9.6.14

On Mon, Aug 12, 2019 at 3:07 PM Alvaro Herrera wrote: > How likely is it that we would ever be able to release memory from a > Sort (or, say, a hashjoin hash table) when it's done being read, but > before completing the whole plan? As I understand, right now we hold > onto a lot of memory after