Re: pg_basebackup -F plain -R overwrites postgresql.auto.conf

On 2020/02/11 0:28, Alvaro Herrera wrote: On 2020-Feb-10, Alvaro Herrera wrote: On 2020-Feb-10, Fujii Masao wrote: Yes! Thanks for pointing out that! So the patch needs to be applied only in master. Yikes, thanks. Pushing in a minute. Actually, if you want to push it, be my guest.

RE: POC: GUC option for skipping shared buffers in core dumps

From: Craig Ringer > Currently my options are "dump all shmem including shared_buffers" or > "dump no shmem". But I usually want "dump all shmem except > shared_buffers". It's tolerable to just dump s_b on a test system with > a small s_b, but if enabling coredumps to track down some >

Re: [PATCH] Replica sends an incorrect epoch in its hot standby feedback to the Master

On Fri, Feb 7, 2020 at 1:03 PM Palamadai, Eka wrote: > The below problem occurs in Postgres versions 11, 10, and 9.6. However, it > doesn’t occur since Postgres version 12, since the commit [6] to add basic > infrastructure for 64-bit transaction IDs indirectly fixed it. I'm happy that that

Re: [HACKERS] Moving relation extension locks out of heavyweight lock manager

On Tue, 11 Feb 2020 at 11:31, Amit Kapila wrote: > > On Wed, Feb 5, 2020 at 12:07 PM Masahiko Sawada wrote: > > > > > > Unfortunately the environment I used for performance verification is > > no longer available. > > > > I agree to run this test in a different environment. I've attached the > >

Re: Add A Glossary

> > It seems like this could be a good idea, still the patch has been > waiting on his author for more than two weeks now, so I have marked it > as returned with feedback. > In light of feedback, I enlisted the help of an actual technical writer (Roger Harkavy, CCed) and we eventually found the

Re: Error on failed COMMIT

On Tue, 11 Feb 2020 at 17:35, Tom Lane wrote: > Vik Fearing writes: > > There is a current discussion off-list about what should happen when a > > COMMIT is issued for a transaction that cannot be committed for whatever > > reason. PostgreSQL returns ROLLBACK as command tag but otherwise >

Re: pgsql: walreceiver uses a temporary replication slot by default

On Mon, Feb 10, 2020 at 01:46:04PM -0800, Andres Freund wrote: > I still architecturally don't find it attractive that the active > configuration between walreceiver and startup process can diverge > though. Imagine if we e.g. added the ability to receive WAL over > multiple connections from one

Re: WIP: WAL prefetch (another approach)

On Fri, Jan 3, 2020 at 5:57 PM Thomas Munro wrote: > On Fri, Jan 3, 2020 at 7:10 AM Tomas Vondra > wrote: > > Could we instead specify the number of blocks to prefetch? We'd probably > > need to track additional details needed to determine number of blocks to > > prefetch (essentially LSN for

Re: Adding a test for speculative insert abort case

Hi, On 2020-02-07 16:40:46 -0800, Andres Freund wrote: > I'm currently fighting with a race I'm observing in about 1/4 of the > runs. [...] > I think the issue here is that determines whether s1 can finish its > check_exclusion_or_unique_constraint() check with one retry is whether > it reaches

Re: POC: rational number type (fractions)

On Fri, 2020-02-07 at 22:25 -0600, Joe Nelson wrote: > Hi hackers, attached is a proof of concept patch adding a new base > type > called "rational" to represent fractions. Hi! > The primary motivation was as a column type to support user-defined > ordering of rows (with the ability to

Re: [HACKERS] Moving relation extension locks out of heavyweight lock manager

Hi, On 2020-02-11 08:01:34 +0530, Amit Kapila wrote: > I don't see much downside with the patch, rather there is a > performance increase of 3-9% in various scenarios. As I wrote in [1] I started to look at this patch. My problem with itis that it just seems like the wrong direction

Re: [Proposal] Add accumulated statistics for wait event

On Wed, 12 Feb 2020 at 12:36, imai.yoshik...@fujitsu.com wrote: > It seems performance difference is big in case of read only tests. The reason > is that write time is relatively longer than the > processing time of the logic I added in the patch. That's going to be a pretty difficult

Re: Getting rid of some more lseek() calls

On Tue, Feb 11, 2020 at 06:04:09PM +1300, Thomas Munro wrote: > lseek(SEEK_END) seems to be nearly twice as fast as fstat() if you > just call it in a big loop, on Linux and FreeBSD (though I didn't > investigate exactly why, mitigations etc, it certainly returns more > stuff so there's that).

Re: client-side fsync() error handling

On Tue, Feb 11, 2020 at 09:22:54AM +0100, Peter Eisentraut wrote: > Digging around through the call stack, I think changing fsync_fname() to > just call exit(1) on errors instead of proceeding would address most of > that. > > Thoughts? Doing things as you do in your patch sounds fine to me for

Re: Adding a test for speculative insert abort case

On Tue, Feb 11, 2020 at 4:45 PM Andres Freund wrote: > > I additionally restricted the controller_print_speculative_locks step to > the current database and made a bunch of debug output more > precise. Survived ~150 runs locally. > > Lets see what the buildfarm says... > > Thanks so much for

Re: [HACKERS] Moving relation extension locks out of heavyweight lock manager

On Wed, 12 Feb 2020 at 00:43, Tom Lane wrote: > > I took a brief look through this patch. I agree with the fundamental > idea that we shouldn't need to use the heavyweight lock manager for > relation extension, since deadlock is not a concern and no backend > should ever need to hold more than

RE: [Proposal] Add accumulated statistics for wait event

On Sat, Feb 1, 2020 at 5:50 AM, Pavel Stehule wrote: > today I run 120 5minutes pgbench tests to measure impact of this patch. > Result is attached. ... > Thanks to Tomas Vondra and 2ndq for hw for testing Thank you for doing a lot of these benchmarks! > The result is interesting - when I run

Re: Add %x to PROMPT1 and PROMPT2

On Tue, Feb 11, 2020 at 10:05:25AM -0500, Robert Haas wrote: > No objections here. I'm glad that we put in the effort to get more > opinions, but I agree that an overall vote of ~58 to ~8 is a pretty > strong consensus. Clearly, so done as dcdbb5a. -- Michael signature.asc Description: PGP

Re: Getting rid of some more lseek() calls

On Wed, Feb 12, 2020 at 6:42 PM Michael Paquier wrote: > On Tue, Feb 11, 2020 at 06:04:09PM +1300, Thomas Munro wrote: > > lseek(SEEK_END) seems to be nearly twice as fast as fstat() if you > > just call it in a big loop, on Linux and FreeBSD (though I didn't > > investigate exactly why,

client-side fsync() error handling

Continuing the discussion from [0], there are still a number of fsync() calls in client programs that are unchecked or where errors are treated non-fatally. Digging around through the call stack, I think changing fsync_fname() to just call exit(1) on errors instead of proceeding would address

Re: [PATCH] Erase the distinctClause if the result is unique by definition

On Tue, Feb 11, 2020 at 3:56 PM Julien Rouhaud wrote: > On Tue, Feb 11, 2020 at 10:57:26AM +0800, Andy Fan wrote: > > On Tue, Feb 11, 2020 at 12:22 AM Ashutosh Bapat < > > ashutosh.bapat@gmail.com> wrote: > > > > > I forgot to mention this in the last round of comments. Your patch was > > >

Re: Internal key management system

On Mon, Feb 10, 2020 at 05:57:47PM -0800, Andres Freund wrote: > Hi, > > On 2020-02-08 12:07:57 -0500, Tom Lane wrote: > > For the same reason, I don't think that an "internal key management" > > feature in the core code is ever going to be acceptable. It has to > > be an extension. (But, as

Re: Add PostgreSQL home page to --help output

> On 11 Feb 2020, at 08:41, Peter Eisentraut > wrote: > Autoconf already has a way to register the package home page and propagate > it, so I used that. That also makes it easier to change it (see http: -> > https:) or have third parties substitute their own contact information > without

open-source equivalent of golden-gate

Hi In the Oracle world we use the product "golden gate" to execute transactions from a source database (Oracle, Mysql) to a PostgreSQL instance. This allows 2 Oracle and PostgreSQL databases to be updated at the same time in real time. I would like to know if there is an equivalent open-source

custom postgres launcher for tests

This patch allow to use custom postgres launcher for tests (tap) by setting environment variable PGLAUNCHER. Other known methods (like: https://wiki.postgresql.org/wiki/Valgrind) requires to perform installation, build system modifications, executable replacement etc... And proposed way is

Re: [PATCH] Erase the distinctClause if the result is unique by definition

On Tue, Feb 11, 2020 at 3:56 PM Julien Rouhaud wrote: > > > > and if we prepare sql outside a transaction, and execute it in the > > transaction, the other session can't drop the constraint until the > > transaction is ended. > > And what if you create a view on top of a query containing a

Re: [PATCH] Erase the distinctClause if the result is unique by definition

On Tue, Feb 11, 2020 at 08:14:14PM +0800, Andy Fan wrote: > On Tue, Feb 11, 2020 at 3:56 PM Julien Rouhaud wrote: > > > > > > > and if we prepare sql outside a transaction, and execute it in the > > > transaction, the other session can't drop the constraint until the > > > transaction is ended.

Re: open-source equivalent of golden-gate

вт, 11 февр. 2020 г. в 12:23, ROS Didier : > In the Oracle world we use the product "golden gate" to execute > transactions from a source database (Oracle, Mysql) to a PostgreSQL > instance. > > This allows 2 Oracle and PostgreSQL databases to be updated at the same > time in real time. > > I

Re: open-source equivalent of golden-gate

On 02/11/20 07:51, Victor Yegorov wrote: > вт, 11 февр. 2020 г. в 12:23, ROS Didier : > >> In the Oracle world we use the product "golden gate" to execute >> transactions from a source database (Oracle, Mysql) to a PostgreSQL >> instance. > > Note, that PostgreSQL provides only infrastructure,

Re: open-source equivalent of golden-gate

ROS Didier schrieb am 11.02.2020 um 11:23: > In the Oracle world we use the product "golden gate" to execute > transactions from a source database (Oracle, Mysql) to a PostgreSQL > instance. > > This allows 2 Oracle and PostgreSQL databases to be updated at the > same time in real time. > > I

Re: [PATCH] Erase the distinctClause if the result is unique by definition

On Mon, Feb 10, 2020 at 10:57 PM Tom Lane wrote: > Ashutosh Bapat writes: > >> On Sat, Feb 8, 2020 at 12:53 PM Andy Fan > wrote: > >> Do you mean adding some information into PlannerInfo, and when we > create > >> a node for Unique/HashAggregate/Group, we can just create a dummy node? > > >

Re: [PATCH] Erase the distinctClause if the result is unique by definition

On Tue, Feb 11, 2020 at 8:27 AM Andy Fan wrote: > > > On Tue, Feb 11, 2020 at 12:22 AM Ashutosh Bapat < > ashutosh.bapat@gmail.com> wrote: > >> >> >>> >>> [PATCH] Erase the distinctClause if the result is unique by >>> definition >>> >> >> I forgot to mention this in the last round of

Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly

For your v7 patch, which handles REINDEX to a new tablespace, I have a few minor comments: + * the relation will be rebuilt. If InvalidOid is used, the default => should say "currrent", not default ? +++ b/doc/src/sgml/ref/reindex.sgml +TABLESPACE ... +new_tablespace => I saw you

Re: Just for fun: Postgres 20?

> > > From: Jose Luis Tallon > > > Musing some other date-related things I stumbled upon the thought > > that naming the upcoming release PostgreSQL 20 might be preferrable to > > the current/expected "PostgreSQL 13". > > +1 > > Users can easily know how old/new the release is that they are

Re: [HACKERS] Moving relation extension locks out of heavyweight lock manager

I took a brief look through this patch. I agree with the fundamental idea that we shouldn't need to use the heavyweight lock manager for relation extension, since deadlock is not a concern and no backend should ever need to hold more than one such lock at once. But it feels to me like this

Re: Change atoi to strtol in same place

On 2019-Dec-06, Joe Nelson wrote: > I see this patch has been moved to the next commitfest. What's the next > step, does it need another review? This patch doesn't currently apply; it has conflicts with at least 01368e5d9da7 and 7e735035f208; even in 7e735035f208^ it applies with fuzz. Please

Re: Add %x to PROMPT1 and PROMPT2

On Sun, Feb 9, 2020 at 7:45 PM Michael Paquier wrote: > On Mon, Feb 10, 2020 at 12:16:44AM +0100, Vik Fearing wrote: > > There is a little bit of overlap within those three groups but among the > > minuscule percentage of our users that responded, the result is > > overwhelmingly in favor of this

Sv: Just for fun: Postgres 20?

This project already tried that: https://www.postgresql.org/docs/12/history.html#HISTORY-POSTGRES95 Didn't last long... -- Andreas Joseph Krogh

Re: Portal->commandTag as an enum

On 2020-Feb-07, Mark Dilger wrote: > Andres, > > The previous patch set seemed to cause confusion, having separated > changes into multiple files. The latest patch, heavily influenced by > your review, is all in one file, attached. Cool stuff. I think is a little confused about what is source

Re: Fetching timeline during recovery

On Fri, 31 Jan 2020 15:12:30 +0900 Michael Paquier wrote: > On Thu, Jan 23, 2020 at 05:54:08PM +0100, Jehan-Guillaume de Rorthais wrote: > > Please find the new version of the patch in attachment. > > To be honest, I find the concept of this patch confusing. > pg_stat_wal_receiver is just a

Memory-comparable Serialization of Data Types

Hi Postgres Developers, We are currently integrating LSM-tree based storage engine RocksDB into Postgres. I am wondering is there any function that serialize data types in memory-comparable format, similar to MySQL and MariaDB. With that kind of function, we can directly store the serialized

pg_locks display of speculative locks is bogus

Hi, I noticed this when tightening up some races for [1] I noticed that the way speculative locks are displayed in pg_locks is completely bogus. As pg_locks has no branch specific to speculative locks, the object etc path is used: case LOCKTAG_OBJECT:

Re: Memory-comparable Serialization of Data Types

Yes, this is exactly what I mean. On Tue, 11 Feb 2020 at 15:01, Peter Geoghegan wrote: > On Tue, Feb 11, 2020 at 11:53 AM Shichao Jin wrote: > > We are currently integrating LSM-tree based storage engine RocksDB into > Postgres. I am wondering is there any function that serialize data types in

Re: pg_locks display of speculative locks is bogus

On Tue, Feb 11, 2020 at 12:03 PM Andres Freund wrote: > Doesn't seem great. > > It's trivial to put the xid in the correct place, but it's less obvious > what to do with the token? For master we should probably add a column, > but what about the back branches? Ignore it? Put it in classid or

Re: Portal->commandTag as an enum

> On Feb 11, 2020, at 12:50 PM, Alvaro Herrera wrote: > > On 2020-Feb-11, Mark Dilger wrote: > >> I thought about generating the files rather than merely checking them. >> I could see arguments both ways. I wasn’t sure whether there would be >> broad support for having yet another perl

Re: Memory-comparable Serialization of Data Types

On Tue, Feb 11, 2020 at 11:53 AM Shichao Jin wrote: > We are currently integrating LSM-tree based storage engine RocksDB into > Postgres. I am wondering is there any function that serialize data types in > memory-comparable format, similar to MySQL and MariaDB. With that kind of > function, we

Re: Portal->commandTag as an enum

On 2020-Feb-11, Mark Dilger wrote: > I thought about generating the files rather than merely checking them. > I could see arguments both ways. I wasn’t sure whether there would be > broad support for having yet another perl script generating source > files, nor for the maintenance burden of

Re: Portal->commandTag as an enum

> On Feb 11, 2020, at 1:02 PM, Alvaro Herrera wrote: > > On 2020-Feb-11, Mark Dilger wrote: > >>> No thanks. >> >> I’m not sure which option you are voting for: >> >> (Option #1) Have the perl script generate the .c and .h file from a .dat file >> >> (Option #2) Have the perl script

Re: Memory-comparable Serialization of Data Types

On Tue, Feb 11, 2020 at 12:19 PM Shichao Jin wrote: > Yes, this is exactly what I mean. PostgreSQL doesn't have this capability. It might make sense to have it for some specific data structures, such as tuples on internal B-Tree pages -- these merely guide index scans, so there some information

Re: Portal->commandTag as an enum

> On Feb 11, 2020, at 11:09 AM, Alvaro Herrera wrote: > > On 2020-Feb-07, Mark Dilger wrote: > >> Andres, >> >> The previous patch set seemed to cause confusion, having separated >> changes into multiple files. The latest patch, heavily influenced by >> your review, is all in one file,

Re: pg_locks display of speculative locks is bogus

Hi, On 2020-02-11 12:24:50 -0800, Peter Geoghegan wrote: > On Tue, Feb 11, 2020 at 12:03 PM Andres Freund wrote: > > Doesn't seem great. > > > > It's trivial to put the xid in the correct place, but it's less obvious > > what to do with the token? For master we should probably add a column, > >

Re: Portal->commandTag as an enum

On 2020-Feb-11, Mark Dilger wrote: > > No thanks. > > I’m not sure which option you are voting for: > > (Option #1) Have the perl script generate the .c and .h file from a .dat file > > (Option #2) Have the perl script validate but not generate the .c and .h files > > (Option #3) Have no perl

Re: Postgres 32 bits client compilation fail. Win32 bits client is supported?

On 2/10/20 7:13 AM, Ranier Vilela wrote: > > > Unfortunately, i will have to live with 32 bits clients for a long > time yet. > I still have customers using Windows XP yet ... > > AFAIK we don't support WinXP past Postgres Release 10 because of the lack of huge page support. That won't affect

Re: Error on failed COMMIT

On 11/02/2020 23:35, Tom Lane wrote: > Vik Fearing writes: >> There is a current discussion off-list about what should happen when a >> COMMIT is issued for a transaction that cannot be committed for whatever >> reason. PostgreSQL returns ROLLBACK as command tag but otherwise succeeds. > >> It

Re: Error on failed COMMIT

Vik Fearing writes: > On 11/02/2020 23:35, Tom Lane wrote: >> So I assume you're imagining that that would leave us still in >> transaction-aborted state, and the session is basically dead in >> the water until the user thinks to issue ROLLBACK instead? > Actually, I was imagining that it would

RE: open-source equivalent of golden-gate

From: Chapman Flack > I read the question as perhaps concerning the other direction, whether > there might be an open source foreign data wrapper installable in Oracle > for talking to PostgreSQL (which might, I suppose, also have a name like > "postgres_fdw", which helps explain the number of

Error on failed COMMIT

There is a current discussion off-list about what should happen when a COMMIT is issued for a transaction that cannot be committed for whatever reason. PostgreSQL returns ROLLBACK as command tag but otherwise succeeds. Here is an excerpt of Section 17.7 that I feel is relevant: <> 6) Case: a)

Re: Postgres 32 bits client compilation fail. Win32 bits client is supported?

Em ter., 11 de fev. de 2020 às 18:08, Andrew Dunstan < andrew.duns...@2ndquadrant.com> escreveu: > > On 2/10/20 7:13 AM, Ranier Vilela wrote: > > > > > > Unfortunately, i will have to live with 32 bits clients for a long > > time yet. > > I still have customers using Windows XP yet ... > > > > >

Re: Implementing Incremental View Maintenance

Takuma Hoshiai wrote > Hi, > > Attached is the latest patch (v12) to add support for Incremental > Materialized View Maintenance (IVM). > It is possible to apply to current latest master branch. > > Differences from the previous patch (v11) include: > * support executing REFRESH MATERIALIZED

Re: Memory-comparable Serialization of Data Types

On Tue, Feb 11, 2020 at 1:40 PM Alvaro Herrera wrote: > I think adding that would be too much of a burden, both for the project > itself as for third-party type definitions; I think we'd rather rely on > calling the BTORDER_PROC btree support function for the type. An operator class would still

Re: Error on failed COMMIT

Vik Fearing writes: > There is a current discussion off-list about what should happen when a > COMMIT is issued for a transaction that cannot be committed for whatever > reason. PostgreSQL returns ROLLBACK as command tag but otherwise succeeds. > It seems like [ trying to commit a failed

Re: Just for fun: Postgres 20?

I'd rather have releases being made when the software is ready and not when the calendar year mandates it. It seems like a terrible idea. On Tue, 11 Feb 2020 at 14:03, Andreas Joseph Krogh wrote: > This project already tried that: >

Re: Memory-comparable Serialization of Data Types

On 2020-Feb-11, Peter Geoghegan wrote: > On Tue, Feb 11, 2020 at 12:19 PM Shichao Jin wrote: > > Yes, this is exactly what I mean. > > PostgreSQL doesn't have this capability. It might make sense to have > it for some specific data structures, I think adding that would be too much of a burden,