Re: [HACKERS] autonomous transactions

2016-08-31 Thread Joel Jacobson
On Wed, Aug 31, 2016 at 6:41 AM, Jaime Casanova wrote: > > On 30 August 2016 at 23:10, Joel Jacobson wrote: > > > > There should be a way to within the session and/or txn permanently > > block autonomous transactions. > > > > This will defeat one

[HACKERS] pg_sequence catalog

2016-08-31 Thread Peter Eisentraut
While I was hacking around sequence stuff, I felt the urge to look into an old peeve: That sequence metadata is not stored in a proper catalog. Right now in order to find out some metadata about sequences (min, max, increment, etc.), you need to look into the sequence. That is like having to

Re: [HACKERS] [COMMITTERS] pgsql: Fix pg_receivexlog --synchronous

2016-08-31 Thread Simon Riggs
On 29 August 2016 at 12:34, Tom Lane wrote: > Simon Riggs writes: >> Fix pg_receivexlog --synchronous > > The buildfarm says you broke the 9.5 branch. > > In general, pushing inessential patches just a few hours before a wrap > deadline is a dangerous

Re: [HACKERS] autonomous transactions

2016-08-31 Thread Greg Stark
On Wed, Aug 31, 2016 at 2:50 AM, Peter Eisentraut wrote: > - A API interface to open a "connection" to a background worker, run > queries, get results: AutonomousSessionStart(), AutonomousSessionEnd(), > AutonomousSessionExecute(), etc. The communication happens

Re: [HACKERS] pg_sequence catalog

2016-08-31 Thread Tom Lane
I wrote: > Personally, my big beef with the current approach to sequences is that > we eat a whole relation (including a whole relfilenode) per sequence. > I wish that we could reduce a sequence to just a single row in a > catalog, including the nontransactional state. Not sure how feasible >

Re: [HACKERS] pg_sequence catalog

2016-08-31 Thread Tom Lane
Craig Ringer writes: > On 31 August 2016 at 22:01, Tom Lane wrote: >> Personally, my big beef with the current approach to sequences is that >> we eat a whole relation (including a whole relfilenode) per sequence. >> I wish that we could reduce a

Re: [HACKERS] pg_sequence catalog

2016-08-31 Thread Craig Ringer
On 31 August 2016 at 22:01, Tom Lane wrote: > Personally, my big beef with the current approach to sequences is that > we eat a whole relation (including a whole relfilenode) per sequence. > I wish that we could reduce a sequence to just a single row in a > catalog, including

[HACKERS] less expensive pg_buffercache on big shmem

2016-08-31 Thread Ivan Kartyshov
Hi hackers, Recently I have finished my work on a patch for pg_buffercache contrib, I think it's time to share my results. Introduction I want to offer you the implementation that allows to decrease system workload by partially sacrificing (fully snapshot consistency) data

[HACKERS] proposal: psql \setfileref

2016-08-31 Thread Pavel Stehule
Hi I propose a new type of psql variables - file references. The content of file reference is specified by referenced file. It allows simple inserting large data without necessity of manual escaping or using LO api. When I wrote the patch, I used parametrized queries for these data instead

Re: [HACKERS] Missing checks when malloc returns NULL...

2016-08-31 Thread Tom Lane
Michael Paquier writes: > Which means that processes have an escape window when initializing > shared memory by cleaning up the index if an entry cannot be found and > then cannot be created properly. I don't think that it is a good idea > to change that by forcing

Re: [HACKERS] some requests on auditing

2016-08-31 Thread Pavel Stehule
2016-08-31 16:00 GMT+02:00 David Steele : > On 8/31/16 9:39 AM, David Steele wrote: > >> On 8/30/16 10:12 AM, Pavel Stehule wrote: >> > > #3 is not likely without changes to logging in Postgres. However, there >> are plenty of tools for log analysis (e.g. ELK) that might

[HACKERS] Optimizing aggregates

2016-08-31 Thread Heikki Linnakangas
I've been profiling simple aggregate queries, looking for any low-hanging fruit. For this query: -- setup create table floats as select g::float8 as a, g::float8 as b, g::float8 as c from generate_series(1, 1000) g; vacuum freeze floats; -- query select sum(a), sum(b+c) from floats;

Re: [HACKERS] pg_sequence catalog

2016-08-31 Thread Andres Freund
On 2016-08-31 11:23:27 -0400, Tom Lane wrote: > Another issue is what is the low-level interlock between nextvals > in different processes. Right now it's the buffer lock on the > sequence's page. With a scheme like this, if we just kept doing > that, we'd have a single lock covering probably

Re: [HACKERS] Optimizing aggregates

2016-08-31 Thread Andres Freund
Hi, On 2016-08-31 17:47:18 +0300, Heikki Linnakangas wrote: > # .. . > > # > 25.70% 0.00% postmaster [unknown] [k] > 14.23%13.75% postmaster postgres [.]

Re: [HACKERS] pg_sequence catalog

2016-08-31 Thread Petr Jelinek
On 31/08/16 16:10, Tom Lane wrote: I wrote: Personally, my big beef with the current approach to sequences is that we eat a whole relation (including a whole relfilenode) per sequence. I wish that we could reduce a sequence to just a single row in a catalog, including the nontransactional

Re: [HACKERS] autonomous transactions

2016-08-31 Thread Petr Jelinek
On 31/08/16 16:11, Craig Ringer wrote: On 31 August 2016 at 21:46, Greg Stark wrote: On Wed, Aug 31, 2016 at 2:50 AM, Peter Eisentraut wrote: - A API interface to open a "connection" to a background worker, run queries, get results:

Re: [HACKERS] make async slave to wait for lsn to be replayed

2016-08-31 Thread Craig Ringer
On 31 August 2016 at 22:16, Ivan Kartyshov wrote: > Our clients who deal with 9.5 and use asynchronous master-slave replication, > asked to make the wait-mechanism on the slave side to prevent the situation > when slave handles query which needs data (LSN) that was

Re: [HACKERS] [GENERAL] C++ port of Postgres

2016-08-31 Thread Peter Eisentraut
[trimmed cc list because of big attachments] On 8/16/16 4:22 PM, Jim Nasby wrote: > Joy, do you have an idea what a *minimally invasive* patch for C++ > support would look like? That's certainly the first step here. I developed a minimally invasive patch for C++ support a few years ago shortly

Re: [HACKERS] Optimizing aggregates

2016-08-31 Thread Heikki Linnakangas
On 08/31/2016 06:51 PM, Andres Freund wrote: On 2016-08-31 17:47:18 +0300, Heikki Linnakangas wrote: We actually used to call ExecEvalExpr() directly for each argument, but that was changed by the patch that added support for ordered set aggregates. It looks like that was a bad idea, from a

Re: [HACKERS] pg_sequence catalog

2016-08-31 Thread Andres Freund
Hi, On 2016-08-31 12:53:30 -0400, Tom Lane wrote: > Improving on the space wastage is exactly the point IMO. If it's still > going to be 8k per sequence on disk (*and* in shared buffers, remember), > I'm not sure it's worth all the work to change things at all. A separate file is a heck lot

Re: [HACKERS] pg_sequence catalog

2016-08-31 Thread Alvaro Herrera
Andres Freund wrote: > On 2016-08-31 11:23:27 -0400, Tom Lane wrote: > > Another issue is what is the low-level interlock between nextvals > > in different processes. Right now it's the buffer lock on the > > sequence's page. With a scheme like this, if we just kept doing > > that, we'd have a

Re: [HACKERS] Optimizing aggregates

2016-08-31 Thread Andres Freund
On 2016-08-31 19:07:00 +0300, Heikki Linnakangas wrote: > On 08/31/2016 06:51 PM, Andres Freund wrote: > > I've first combined the projection for all the aggregates, ordered set, > > or not, into one projetion. That got rid of a fair amount of overhead > > when you have multiple aggregates. I

[HACKERS] Proposal for changes to recovery.conf API

2016-08-31 Thread Simon Riggs
This is a summary of proposed changes to the recovery.conf API for v10. These are based in part on earlier discussions, and represent a minimal modification to current usage. Proposed changes (with reference to patches from Abhijit Menon-Sen and myself) * pg_ctl start -M

Re: [HACKERS] proposal: psql \setfileref

2016-08-31 Thread Pavel Stehule
2016-08-31 18:24 GMT+02:00 Corey Huinker : > On Wed, Aug 31, 2016 at 11:32 AM, Pavel Stehule > wrote: > >> Hi >> >> I propose a new type of psql variables - file references. The content of >> file reference is specified by referenced file. It

Re: [HACKERS] pg_sequence catalog

2016-08-31 Thread Tom Lane
Andres Freund writes: > On 2016-08-31 12:56:45 -0300, Alvaro Herrera wrote: >> I was thinking that nextval could grab a shared buffer lock and release >> immediately, just to ensure no one holds exclusive buffer lock >> concurrently (which would be used for things like

Re: [HACKERS] autonomous transactions

2016-08-31 Thread Greg Stark
On Wed, Aug 31, 2016 at 3:11 PM, Craig Ringer wrote: > > I suspect that there'll be way too much code that relies on stashing > xact-scoped stuff in globals for that to be viable. Caches alone. > Peter will be able to explain more, I'm sure. > > We'd probably need a new

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

2016-08-31 Thread Claudio Freire
On Wed, Aug 31, 2016 at 1:45 PM, Pavan Deolasee wrote: > We discussed a few ideas to address the "Duplicate Scan" problem. For > example, we can teach Index AMs to discard any duplicate (key, CTID) insert > requests. Or we could guarantee uniqueness by either only

Re: [HACKERS] pg_sequence catalog

2016-08-31 Thread Andres Freund
On 2016-08-31 12:56:45 -0300, Alvaro Herrera wrote: > I was thinking that nextval could grab a shared buffer lock and release > immediately, just to ensure no one holds exclusive buffer lock > concurrently (which would be used for things like dropping one seq tuple > from the page, when a sequence

Re: [HACKERS] proposal: psql \setfileref

2016-08-31 Thread Corey Huinker
On Wed, Aug 31, 2016 at 11:32 AM, Pavel Stehule wrote: > Hi > > I propose a new type of psql variables - file references. The content of > file reference is specified by referenced file. It allows simple inserting > large data without necessity of manual escaping or

Re: [HACKERS] autonomous transactions

2016-08-31 Thread Simon Riggs
On 31 August 2016 at 14:09, Joel Jacobson wrote: > On Wed, Aug 31, 2016 at 6:41 AM, Jaime Casanova > wrote: >> >> On 30 August 2016 at 23:10, Joel Jacobson wrote: >> > >> > There should be a way to within the session and/or txn

<    1   2