Re: [HACKERS] [PATCH] Reload SSL certificates on SIGHUP

On Sat, Nov 12, 2016 at 3:42 AM, Andreas Karlsson wrote: > On 11/11/2016 07:40 PM, Andreas Karlsson wrote: >> Here is a new version of the patch with the only differences; >> >> 1) The SSL tests have been changed to use reload rather than restart Did you check if the tests

Re: [HACKERS] Push down more full joins in postgres_fdw

> >> Sorry. I think the current version is better than previous one. The >> term "subselect alias" is confusing in the previous version. In the >> current version, "Get the relation and column alias for a given Var >> node," we need to add word "identifiers" like "Get the relation and >> column

Re: [HACKERS] Push down more full joins in postgres_fdw

On 2016/11/23 0:30, Ashutosh Bapat wrote: You wrote: The comment below says "get the aliases", but what the function really returns is the identifiers used for creating aliases. Please correct the comment. +/* + * Get the relation and column alias for a given Var node, which belongs to + *

Re: [HACKERS] UNDO and in-place update

On Wed, Nov 23, 2016 at 11:32 PM, Tsunakawa, Takayuki wrote: > IMHO, overall, there should be pros and cons of the current approach and the > new UNDo one (like Oracle?), depending on the workload. Under update-heavy > workload, the UNDO method may be better.

Re: [HACKERS] UNDO and in-place update

IMHO, overall, there should be pros and cons of the current approach and the new UNDo one (like Oracle?), depending on the workload. Under update-heavy workload, the UNDO method may be better. OTOH, under the mostly-INSERT workload (like data warehouse?), the current method will be better

Re: [HACKERS] Re: [bug fix] Cascading standby cannot catch up and get stuck emitting the same message repeatedly

On Thu, Nov 24, 2016 at 10:29 AM, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-ow...@postgresql.org >> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Amit Kapila >> Thanks for the clarification, I could reproduce the issue and confirms that >> patch

Re: [HACKERS] Declarative partitioning - another take

On Thu, Nov 24, 2016 at 12:02 PM, Amit Langote wrote: > On 2016/11/24 15:10, Ashutosh Bapat wrote: >> On Thu, Nov 24, 2016 at 11:34 AM, Amit Langote wrote: >>> On 2016/11/24 14:35, Ashutosh Bapat wrote: IIUC, it should allow "create table t1_p1 partition of t1

Re: [HACKERS] Declarative partitioning - another take

On 2016/11/24 15:10, Ashutosh Bapat wrote: > On Thu, Nov 24, 2016 at 11:34 AM, Amit Langote wrote: >> On 2016/11/24 14:35, Ashutosh Bapat wrote: >>> IIUC, it should allow "create table t1_p1 partition of t1 (a primary >>> key) ...", (a primary key) is nothing but "column_name >>>

Re: [HACKERS] Declarative partitioning - another take

On Thu, Nov 24, 2016 at 11:34 AM, Amit Langote wrote: > > Hi Ashutosh, > > On 2016/11/24 14:35, Ashutosh Bapat wrote: >> I am trying to create a partitioned table with primary keys on the >> partitions. Here's the corresponding syntax as per documentation >> CREATE

Re: [HACKERS] Declarative partitioning - another take

Hi Ashutosh, On 2016/11/24 14:35, Ashutosh Bapat wrote: > I am trying to create a partitioned table with primary keys on the > partitions. Here's the corresponding syntax as per documentation > CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ > IF NOT EXISTS ] table_name >

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > Maybe a workable compromise would be to leave the file present, and have > the stats collector re-write it every (say) five minutes. Then I'd be okay > with having an immediate shutdown

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane > Robert Haas writes: > > I agree. However, in many cases, the major cost of a fast shutdown is > > getting the dirty data already in the operating system buffers

Re: [HACKERS] Declarative partitioning - another take

Unsurprisingly ALTER TABLE worked alter table t1_p1 add primary key(a); ALTER TABLE postgres=# \d+ t1_p1 Table "public.t1_p1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description

Re: [HACKERS] Declarative partitioning - another take

I am trying to create a partitioned table with primary keys on the partitions. Here's the corresponding syntax as per documentation CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name PARTITION OF parent_table [ ( { column_name WITH OPTIONS [

Re: [HACKERS] patch: function xmltable

2016-11-24 5:52 GMT+01:00 Pavel Stehule : > Hi > > 2016-11-24 0:13 GMT+01:00 Alvaro Herrera : > >> Oh my, I just noticed we have a new xpath preprocessor in this patch >> too. Where did this code come from -- did you write it all from >>

Re: [HACKERS] Patch to implement pg_current_logfile() function

On Wed, 23 Nov 2016 03:21:05 -0600 "Karl O. Pinc" wrote: > On Sat, 19 Nov 2016 12:58:47 +0100 > Gilles Darold wrote: > > > ... attached v14 of the patch. > patch_pg_current_logfile-v14.diff.retry_current_logfiles-part3 > Re-try the write of

Re: [HACKERS] patch: function xmltable

2016-11-24 0:29 GMT+01:00 Alvaro Herrera : > Here's another version. Not there yet: need to move back the function > to create the tupdesc, as discussed. Not clear what's the best place, > however. I modified the grammar a bit (added the missing comma, removed > PATH

Re: [HACKERS] Re: [bug fix] Cascading standby cannot catch up and get stuck emitting the same message repeatedly

From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Amit Kapila > Thanks for the clarification, I could reproduce the issue and confirms that > patch has fixed it. Find logs of cascading standby at PG9.2 Head and Patch > attached (I have truncated

Re: [HACKERS] patch: function xmltable

Hi 2016-11-24 0:13 GMT+01:00 Alvaro Herrera : > Oh my, I just noticed we have a new xpath preprocessor in this patch > too. Where did this code come from -- did you write it all from > scratch? > I wrote it from scratch - libxml2 has not any API for iteration over

Re: [HACKERS] Calculation of param_source_rels in add_paths_to_joinrel

Thanks Tom. On Thu, Nov 24, 2016 at 2:58 AM, Tom Lane wrote: > Ashutosh Bapat writes: >> On Sat, Nov 5, 2016 at 2:16 AM, Tom Lane wrote: >>> Ashutosh Bapat writes: Also, the way this

Re: [HACKERS] Re: [bug fix] Cascading standby cannot catch up and get stuck emitting the same message repeatedly

On Tue, Nov 22, 2016 at 8:48 AM, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-ow...@postgresql.org > > If the problem occurs, the following pair of lines appear in the server log > of the cascading standby. Could you check it? > > LOG: restored log file

[HACKERS] pg_background contrib module proposal

Hi All, I would like to take over pg_background patch and repost for discussion and review. Initially Robert Haas has share this for parallelism demonstration[1] and abandoned later with summary of open issue[2] with this pg_background patch need to be fixed, most of them seems to be addressed

Re: [HACKERS] patch: function xmltable

Alvaro Herrera wrote: > If you use "PATH '/'" for a column, you get the text for all the entries > in the whole XML, rather than the text for the particular row being > processed. Isn't that rather weird, or to put it differently, completely > wrong? I didn't find a way to obtain the whole XML

Re: [HACKERS] Physical append-only tables

On Mon, Nov 14, 2016 at 08:43:12PM +, Greg Stark wrote: > That said, I don't think the "maintain clustering a bit better using > BRIN" is a bad idea. It's just the bit about turning a table > append-only to deal with update-once data that I think is overreach. What if we used BRIN to find

Re: [HACKERS] [bugfix] commit timestamps ERROR on lookup of FrozenTransactionId

On 24 November 2016 at 02:32, Andres Freund wrote: > Hi, > > On 2016-11-23 20:58:22 +0800, Craig Ringer wrote: >> Today I ran into an issue where commit timestamp lookups were failing with >> >> ERROR: cannot retrieve commit timestamp for transaction 2 >> >> which is

Re: [HACKERS] patch: function xmltable

On 11/23/2016 06:31 PM, Alvaro Herrera wrote: Sorry, here's the patch. Power loss distracted me here. By the way, the pgindent you did is slightly confused because you failed to add the new struct types you define to typedefs.list. Tips on how to use pgindent for developers:

Re: [HACKERS] patch: function xmltable

Here's another version. Not there yet: need to move back the function to create the tupdesc, as discussed. Not clear what's the best place, however. I modified the grammar a bit (added the missing comma, removed PATH as an unreserved keyword and just used IDENT, removed the "Opt" version for

Re: [HACKERS] patch: function xmltable

Oh my, I just noticed we have a new xpath preprocessor in this patch too. Where did this code come from -- did you write it all from scratch? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via

Re: [HACKERS] amcheck (B-Tree integrity checking tool)

On Fri, Nov 18, 2016 at 12:17 PM, Peter Geoghegan wrote: > So, the Btree you show is supposed to be good? You've left it up to me > to draw T2, the corrupt version? If so, here is a drawing of T2 for > the sake of clarity: > > Btree =[a|b] >

Re: [HACKERS] UNDO and in-place update

On Wed, Nov 23, 2016 at 6:01 PM, Peter Geoghegan wrote: > * Our behavior with many duplicates in secondary indexes is pretty bad > in general, I suspect. >From the pie-in-the-sky department: I believe there are snapshot-based systems that don't ever have more than one entry for

Re: [HACKERS] DROP FUNCTION of multiple functions

Alvaro Herrera writes: > Peter Eisentraut wrote: >> Here is a patch series that implements several changes in the internal >> grammar and node representation of function signatures. They are not >> necessarily meant to be applied separately, but they explain the >>

Re: [HACKERS] Calculation of param_source_rels in add_paths_to_joinrel

Ashutosh Bapat writes: > On Sat, Nov 5, 2016 at 2:16 AM, Tom Lane wrote: >> Ashutosh Bapat writes: >>> Also, the way this code has been written, the declaration of variable >>> sjinfo masks the earlier

Re: [HACKERS] DISTINCT with btree skip scan

On Wed, Oct 12, 2016 at 4:19 PM, Thomas Munro wrote: > Here it is, now split into two parts: one patch > to add an amskip operation, and another to consider using it to > implement DISTINCT when it was already has an index only scan path on > an index that supports

Re: [HACKERS] Google Cloud Compute + FreeBSD + PostgreSQL = timecounter issue

On 23.11.16 20:43, Maeldron T. wrote: pg_test_timing doesn’t show the problem, or I read the output wrong. Or it does. I checked another output than the one I attached at the end. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

[HACKERS] Google Cloud Compute + FreeBSD + PostgreSQL = timecounter issue

In short: The available timecounters on Google Compute Instances seem to be random. The setting in the official FreeBSD image is wrong (not available on any of my test instances). FreeBSD will pick up a timecounter at random. When either the TSC or the TSC-low counter is used, explain

Re: [HACKERS] [bugfix] commit timestamps ERROR on lookup of FrozenTransactionId

Hi, On 2016-11-23 20:58:22 +0800, Craig Ringer wrote: > Today I ran into an issue where commit timestamp lookups were failing with > > ERROR: cannot retrieve commit timestamp for transaction 2 > > which is of course FrozenTransactionId. > > TransactionIdGetCommitTsData(...) ERRORs on

Re: [HACKERS] postgres 9.3 postgres_fdw ::LOG: could not receive data from client: Connection reset by peer

On Mon, Nov 21, 2016 at 6:32 AM, Vladimir Svedov wrote: > Hi, > I have this question. Looked for a help on http://dba.stackexchange.com/ > No success. > Maybe you can answer?.. > Thank you in advance > > > "FOREIGN_TABLE" created with postgres_fdw. LOCAL_TABLE is just a local

Re: [HACKERS] Patch to implement pg_current_logfile() function

On Wed, 23 Nov 2016 10:39:28 -0500 Tom Lane wrote: > Robert Haas writes: > > On Wed, Nov 23, 2016 at 4:21 AM, Karl O. Pinc > > wrote: > >> The bool types on the stack in logfile_rotate() are > >> my work. Bools on the stack don't

Re: [HACKERS] patch: function xmltable

Alvaro Herrera wrote: > I'm still wondering how this works. I'll continue to explore the patch > in order to figure out. Ah, so it's already parsed as a "range function". That part looks good to me. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7

Re: [HACKERS] postgres 9.3 postgres_fdw ::LOG: could not receive data from client: Connection reset by peer

On Wed, Nov 23, 2016 at 3:08 AM, Vladimir Svedov wrote: > No, I select from it OK. > The bug(?) is that when I do it in oppened psql session it produces no log, > and when I run same select as psql -c "SELECT..." it gives the above OK, that's pretty weird. -- Robert Haas

Re: [HACKERS] patch: function xmltable

I tried to see if a following RTE was able to "see" the entries created by XMLTABLE, and sure it can: SELECT X.*, generate_series FROM emp, XMLTABLE ('//depts/dept/employee' passing doc COLUMNS empIDINTEGER PATH '@id', firstnamevarchar(25) PATH 'name/first',

Re: [HACKERS] Patch: Implement failover on libpq connect level.

On Tue, Nov 22, 2016 at 8:38 AM, Tsunakawa, Takayuki wrote: > transaction_read_only=on does not mean the standby. As the manual article on > hot standby says, they are different. > I'm afraid that if the current patch is committed, you will lose interest in >

Re: [HACKERS] Typo in comment in file analyze.c [master branch]

Kostiantyn Nemchenko writes: > I've found a typo in a comment in function do_analyze_rel() in > file analyze.c [line 348, master branch]. No, "iff" is intentional there. It means "if and only if". regards, tom lane -- Sent via

Re: [HACKERS] Patch to implement pg_current_logfile() function

Le 23/11/2016 à 16:26, Tom Lane a écrit : > "Karl O. Pinc" writes: >> Maybe on the 2nd call to strtok() you could pass "" >> as the 2nd argument? That'd be a little wonky but >> the man page does not say you can't have an empty >> set of delimiters. On the other hand strtok() is

[HACKERS] Typo in comment in file analyze.c [master branch]

I've found a typo in a comment in function do_analyze_rel() in file analyze.c [line 348, master branch]. Attached a patch. minor_typo_analyze_c.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

Tomas Vondra writes: > This would also reduce the amount of data that we need to write to WAL, > although I'm not sure the amount is actually a problem. I've seen > instances with ~500MB stat files, but those were instances with hundreds > of databases, each with

Re: [HACKERS] Patch to implement pg_current_logfile() function

Robert Haas writes: > On Wed, Nov 23, 2016 at 4:21 AM, Karl O. Pinc wrote: >> The bool types on the stack in logfile_rotate() are >> my work. Bools on the stack don't make sense as far >> as hardware goes, so the compiler's optimizer should change >> them

Re: [HACKERS] Patch to implement pg_current_logfile() function

On Wed, Nov 23, 2016 at 4:21 AM, Karl O. Pinc wrote: > patch_pg_current_logfile-v14.diff.bool_to_int > > Do not include in "main" patch, submit to maintainers separately. > > Applies on top of patch_pg_current_logfile-v14.diff > > The bool types on the stack in logfile_rotate() are

Re: [HACKERS] Patch to implement pg_current_logfile() function

"Karl O. Pinc" writes: > Maybe on the 2nd call to strtok() you could pass "" > as the 2nd argument? That'd be a little wonky but > the man page does not say you can't have an empty > set of delimiters. On the other hand strtok() is > not a perfect choice, you don't want to

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On 11/23/2016 12:24 PM, Michael Paquier wrote: On Wed, Nov 23, 2016 at 6:15 PM, Magnus Hagander wrote: There's also the consideration of what to do with stats *on the standby*. If we WAL log the stats file, then when it replays onthe standby, the stats there will be

Re: [HACKERS] UNDO and in-place update

On Tue, Nov 22, 2016 at 11:18 PM, Tom Lane wrote: > Peter Geoghegan writes: >> On Tue, Nov 22, 2016 at 7:31 PM, Tom Lane wrote: >>> Oracle spends a lot of time on this, and it's really cache-inefficient >>> because the data is spread all

Re: [HACKERS] Patch to implement pg_current_logfile() function

On Wed, 23 Nov 2016 03:21:05 -0600 "Karl O. Pinc" wrote: > But also, you can't use strtok() to parse lbuffer because > the path you're returning can contain a space. Maybe on the 2nd call to strtok() you could pass "" as the 2nd argument? That'd be a little wonky but the man

Re: [HACKERS] Logical decoding on standby

On 23 November 2016 at 03:55, Robert Haas wrote: > On Tue, Nov 22, 2016 at 1:49 AM, Craig Ringer wrote: >> On 22 November 2016 at 10:20, Craig Ringer wrote: >>> I'm currently looking at making detection of replay conflict with

Re: [HACKERS] [bugfix] commit timestamps ERROR on lookup of FrozenTransactionId

On 23 November 2016 at 20:58, Craig Ringer wrote: > Hi all > > Today I ran into an issue where commit timestamp lookups were failing with > > ERROR: cannot retrieve commit timestamp for transaction 2 > > which is of course FrozenTransactionId. > >

[HACKERS] [bugfix] commit timestamps ERROR on lookup of FrozenTransactionId

Hi all Today I ran into an issue where commit timestamp lookups were failing with ERROR: cannot retrieve commit timestamp for transaction 2 which is of course FrozenTransactionId. TransactionIdGetCommitTsData(...) ERRORs on !TransactionIdIsNormal(), which I think is wrong. Attached is

Re: [HACKERS] Improvements in psql hooks for variables

I wrote: > So I went through the psql commands which don't fail on parse errors > for booleans > [...] Here's a v5 patch implementing the suggestions mentioned upthread: all meta-commands calling ParseVariableBool() now fail when the boolean argument can't be parsed successfully. Also

Re: [HACKERS] Creating a DSA area to provide work space for parallel execution

On Wed, Oct 5, 2016 at 10:32 AM, Thomas Munro wrote: > One obvious problem is that this patch results in at least *two* DSM > segments being created for every parallel query execution: the main > segment used for parallel execution, and then the initial segment >

Re: [HACKERS] Push down more UPDATEs/DELETEs in postgres_fdw

On Tue, Nov 22, 2016 at 6:24 PM, Etsuro Fujita wrote: > Hi Rushabh, > > On 2016/11/22 19:05, Rushabh Lathia wrote: > >> I started reviewing the patch and here are few initial review points and >> questions for you. >> > > Thanks for the review! > > 1) >> -static void

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Wed, Nov 23, 2016 at 6:15 PM, Magnus Hagander wrote: > There's also the consideration of what to do with stats *on the standby*. If > we WAL log the stats file, then when it replays onthe standby, the stats > there will be overwritten. And stats like number of index vs seq

Re: [HACKERS] UNDO and in-place update

Robert Haas wrote: > To implement this in PostgreSQL, I believe we would need support for > UNDO. > - Reading a page that has been recently modified gets significantly > more expensive; it is necessary to read the associated UNDO entries > and do a bunch of calculation that is significantly more

Re: [HACKERS] Patch to implement pg_current_logfile() function

Hi Gilles, On Sat, 19 Nov 2016 12:58:47 +0100 Gilles Darold wrote: > ... attached v14 of the patch. Attached are patches for your consideration and review. (Including your latest v14 patch for completeness.) Some of the attached patches (like the GUC symbol patch

Re: [HACKERS] [RFC] Should we fix postmaster to avoid slow shutdown?

On Tue, Nov 22, 2016 at 10:26 PM, Alvaro Herrera wrote: > Andres Freund wrote: > > On 2016-11-22 16:15:58 -0500, Tom Lane wrote: > > > > Maybe a workable compromise would be to leave the file present, and > have > > > the stats collector re-write it every (say) five

Re: [HACKERS] Performance degradation in Bitmapscan (commit 75ae538bc3168bf44475240d4e0487ee2f3bb376)

On 2016-11-18 08:00:40 -0500, Robert Haas wrote: > On Tue, Nov 15, 2016 at 2:28 PM, Andres Freund wrote: > > I've a working fix for this, and for a similar issue Robert found. I'm > > still playing around with it, but basically the fix is to make the > > growth policy a bit

Re: [HACKERS] postgres 9.3 postgres_fdw ::LOG: could not receive data from client: Connection reset by peer

No, I select from it OK. The bug(?) is that when I do it in oppened psql session it produces no log, and when I run same select as psql -c "SELECT..." it gives the above 2016-11-22 20:18 GMT+00:00 Robert Haas : > On Tue, Nov 22, 2016 at 5:05 AM, Vladimir Svedov