Re: [HACKERS] Patch: add --if-exists to pg_recvlogical

On Fri, Sep 29, 2017 at 3:06 PM, Peter Eisentraut wrote: > On 9/22/17 15:31, Peter Eisentraut wrote: >> I suggest you create a patch that focuses on the --create part. >> >> You can create a separate follow-on patch for the --start part if you >> wish, but I

Re: [HACKERS] Statement-level rollback

From: Craig Ringer [mailto:cr...@2ndquadrant.com] > The example often cited is some variant of > > BEGIN; > CREATTE TABLE t2 AS SELECT * FROM t1; > DROP TABLE t1; > ALTER TABLE t2 RENAME TO t1; > COMMIT; > > Right now, we do the right thing here. With default statement level rollback, > you just

Re: [HACKERS] Statement-level rollback

From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] > The difference is how error recovery works. So this will necessarily be > tied to how the client code or other surrounding code is structured or what > the driver or framework is doing in the background to manage transactions. >

Re: [HACKERS] Account for cost and selectivity of HAVING quals

On Wed, Nov 1, 2017 at 8:41 PM, Tom Lane wrote: > Ashutosh Bapat writes: >> On Wed, Nov 1, 2017 at 3:15 AM, Tom Lane wrote: >>> here's a patch to fix the planner so that eval costs and selectivity of >>> HAVING quals are

Re: [HACKERS] How to implement a SP-GiST index as a extension module?

Ok, more questions. I've been studying the implementation Alexander Korotkov sent, and I'm not seeing how to map some of the components onto the changes in the SP-GiST system that occured between Postgresql 9.2 and 9.3. The changes at that point seem to have been to change xxx_inner_consistent

Re: [HACKERS] Explicit relation name in VACUUM VERBOSE log

On Mon, Oct 2, 2017 at 4:33 PM, Daniel Gustafsson wrote: >> On 29 Aug 2017, at 17:21, Robert Haas wrote: >> >> On Tue, Aug 22, 2017 at 2:23 AM, Simon Riggs wrote: >>> Yes, we can. I'm not sure why you would do this only for VACUUM

[HACKERS] ucs_wcwidth vintage

Hi hackers, src/backend/utils/mb/wchar.c contains a ~16 year old wcwidth implementation that originally arrived in commit df4cba68, but the upstream code[1] apparently continued evolving and there have been more Unicode revisions since. It probably doesn't matter much: the observation made by

Re: [HACKERS] list of credits for release notes

On 10/2/17 18:34, Bruce Momjian wrote: > My smaller question is how will this list be generated in PG 11? From > the commit log when the release notes are created, or some other method? I don't think it should be done at the same time as the release notes. Specifically, we have recently put an

Re: [HACKERS] Try to fix endless loop in ecpg with informix mode

Diff from the head: (use the following if to decide true or false) ``` diff --git a/src/interfaces/ecpg/ecpglib/data.c b/src/interfaces/ecpg/ecpglib/data.c index 5375934..1621e7b 100644 --- a/src/interfaces/ecpg/ecpglib/data.c +++ b/src/interfaces/ecpg/ecpglib/data.c @@ -57,8 +57,7 @@

Re: [HACKERS] Try to fix endless loop in ecpg with informix mode

Thanks for commit. I am afraid the changes may separate "7.a" to "7" and "a", then error out with "invalid input syntax for type int" for "a". How about changes as below? (use following the if to decide true or false) ``` -} while (**scan_length != ' ' && **scan_length != '\0'); -

Re: [HACKERS] MERGE SQL Statement for PG11

On 1 November 2017 at 01:55, Peter Geoghegan wrote: > The problem here is: Iff the first statement uses ON CONFLICT > infrastructure, doesn't the absence of WHEN NOT MATCHED imply > different semantics for the remaining updates and deletes in the > second version of the query?

Re: [HACKERS] WIP: long transactions on hot standby feedback replica / proof of concept

On Tue, Oct 31, 2017 at 2:47 PM, Alexander Korotkov wrote: > However, from user prospective of view, current behavior of > hot_standby_feedback is just broken, because it both increases bloat and > doesn't guarantee that read-only query on standby wouldn't be cancelled

Re: [HACKERS] Statement-level rollback

On 2 November 2017 at 09:33, Peter Eisentraut wrote: > If you turned the autocommit setting off, then this code would > effectively silently do nothing, and that is obviously quite bad. Right. The example often cited is some variant of BEGIN; CREATTE TABLE t2

Re: [HACKERS] Dynamic result sets from procedures

On Wed, Nov 1, 2017 at 2:38 AM, Peter Eisentraut wrote: > So this is what it can do: > > CREATE PROCEDURE pdrstest1() > LANGUAGE SQL > AS $$ > DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM cp_test2; > DECLARE c2 CURSOR WITH RETURN FOR SELECT * FROM cp_test3; >

Re: [HACKERS] SQL/JSON in PostgreSQL

Could someone clarify the status of this patch set? It has been in "Waiting" mode since the previous CF and no new patch, just a few questions from the author. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --

Re: [HACKERS] Enhancements to passwordcheck

On 11/1/17, 9:33 PM, "Peter Eisentraut" wrote: > On 9/25/17 14:04, Bossart, Nathan wrote: >> I'd like to use this thread to gauge community interest in adding this >> functionality to this module. If there is interest, I'll add it to the next >> commitfest. > >

Re: [HACKERS] PATCH: enabling parallel execution for cursors explicitly (experimental)

On Thu, Nov 2, 2017 at 8:01 AM, Craig Ringer wrote: > That forces materialization, and I'm guessing part of Tomas's goal > here is to prevent the need to materialize into a temp table / > tuplestore / etc. I get that, but if you're running a query like "SELECT * FROM

Re: [HACKERS] Enhancements to passwordcheck

On 9/25/17 14:04, Bossart, Nathan wrote: > I'd like to use this thread to gauge community interest in adding this > functionality to this module. If there is interest, I'll add it to the next > commitfest. This thread has no patch, which is not really the intended use of the commit fest, so I'm

Re: [HACKERS] PATCH: enabling parallel execution for cursors explicitly (experimental)

On 2 November 2017 at 10:01, Robert Haas wrote: > I think that still leaves a fair number of scenarios to consider, and > the error handling by itself seems pretty thorny. Plus it's kind of a > weird mode and, like Craig, I'm not really sure what it gets you. > Maybe if

Re: [HACKERS] Partition-wise aggregation/grouping

On Wed, Nov 1, 2017 at 6:20 PM, Jeevan Chalke wrote: > Yep. > But as David reported earlier, if we remove the first part i.e. adding > cpu_operator_cost per tuple, Merge Append will be preferred over an Append > node unlike before. And thus, I thought of better

Re: [HACKERS] pg_basebackup fails on Windows when using tablespace mapping

On 11/1/17 10:29, Michael Paquier wrote: > On Wed, Nov 1, 2017 at 2:24 PM, Peter Eisentraut > wrote: >> Committed to master. I suppose this should be backpatched? > > Thanks! Yes this should be back-patched. OK, done for 10, 9.6, and 9.5. The tablespace

Re: [HACKERS] PATCH: enabling parallel execution for cursors explicitly (experimental)

On Wed, Nov 1, 2017 at 3:47 AM, Tomas Vondra wrote: > But maybe there's a simpler option - what if we only allow fetches from > the PARALLEL cursor while the cursor is open? That is, this would work: > > BEGIN; > ... > DECLARE x PARALLEL CURSOR FOR SELECT

Re: [HACKERS] PATCH: enabling parallel execution for cursors explicitly (experimental)

On Wed, Nov 1, 2017 at 7:49 AM, Craig Ringer wrote: > If the client wants to fetch in chunks it can use a portal and limited > size fetches. That shouldn't (?) be parallel-unsafe, since nothing > else can happen in the middle anyway. I believe sending a limited-size fetch

Re: [HACKERS] Statement-level rollback

On 10/31/17 13:47, MauMau wrote: > I'm very sorry I couldn't reply to your kind offer. I rebased the > patch and will add it to CF 2017/11. I hope I will complete the patch > in this CF. I've been thinking about this a little bit. Many are worried about repeating the mistakes of the autocommit

Re: [HACKERS] proposal: schema variables

Le 01/11/2017 à 05:15, Pavel Stehule a écrit : > > > 2017-10-31 22:28 GMT+01:00 srielau >: > > Pavel, > > There is no > DECLARE TEMP CURSOR > or > DECLARE TEMP variable in PLpgSQL > and > > > sure .. DECLARE TEMP has no sense, I

Re: [HACKERS] index-only count(*) for indexes supporting bitmap scans

Alexander Kuzmenkov writes: > On 04.09.2017 15:17, Alexey Chernyshov wrote: >> make check-world fails on contrib/postgres_fdw because of Subquery Scan on >> ... Probably, query plan was changed. > Thanks for testing! This is the same problem as the one in >

Re: [HACKERS] Custom compression methods

On 9/12/17 10:55, Ildus Kurbangaliev wrote: >> The patch also includes custom compression method for tsvector which >> is used in tests. >> >> [1] >> https://www.postgresql.org/message-id/CAPpHfdsdTA5uZeq6MNXL5ZRuNx%2BSig4ykWzWEAfkC6ZKMDy6%3DQ%40mail.gmail.com > Attached rebased version of the

Re: [HACKERS] proposal: schema variables

2017-11-01 19:03 GMT+01:00 Mark Dilger : > > > Comments, notes? > > How would variables behave on transaction rollback? > > CREATE TEMP VARIABLE myvar; > SET myvar := 1; > BEGIN; > SET myvar := 2; > COMMIT; > BEGIN; > SET myvar := 3; > ROLLBACK; > SELECT myvar; > > How

Re: [HACKERS] [PATCH] Pageinspect - add functions on GIN and GiST indexes from gevel

On 9/13/17 03:45, Alexey Chernyshov wrote: > On Sat, 9 Sep 2017 13:53:35 +0530 > Ashutosh Sharma wrote: > >> >> Finally, i got some time to look into this patch and surprisingly I >> didn't find any function returning information at page level instead >> all the SQL

Re: [HACKERS] Add some const decorations to prototypes

> On Oct 31, 2017, at 7:46 AM, Peter Eisentraut > wrote: > > Here is a patch that adds const decorations to many char * arguments in > functions. It should have no impact otherwise; there are very few code > changes caused by it. Some functions have a

Re: [HACKERS] [PATCH] Add two-arg for of current_setting(NAME, FALLBACK)

> On Nov 1, 2017, at 1:02 PM, Nico Williams wrote: > > On Thu, Mar 19, 2015 at 05:41:02PM -0500, David Christensen wrote: >> The two-arg form of the current_setting() function will allow a >> fallback value to be returned instead of throwing an error when an >> unknown

Re: [HACKERS] MERGE SQL Statement for PG11

On Wed, Nov 1, 2017 at 10:19 AM, Simon Riggs wrote: >> The problem here is: Iff the first statement uses ON CONFLICT >> infrastructure, doesn't the absence of WHEN NOT MATCHED imply >> different semantics for the remaining updates and deletes in the >> second version of the

Re: [HACKERS] proposal: schema variables

> Comments, notes? How would variables behave on transaction rollback? CREATE TEMP VARIABLE myvar; SET myvar := 1; BEGIN; SET myvar := 2; COMMIT; BEGIN; SET myvar := 3; ROLLBACK; SELECT myvar; How would variables behave when modified in a procedure that aborts rather than returning cleanly?

Re: [HACKERS] [PATCH] Add two-arg for of current_setting(NAME, FALLBACK)

On Thu, Mar 19, 2015 at 05:41:02PM -0500, David Christensen wrote: > The two-arg form of the current_setting() function will allow a > fallback value to be returned instead of throwing an error when an > unknown GUC is provided. This would come in most useful when using > custom GUCs; e.g.:

Re: [HACKERS] [PATCH] Add two-arg for of current_setting(NAME, FALLBACK)

On Thu, Mar 19, 2015 at 3:41 PM, David Christensen wrote: > The two-arg form of the current_setting() function will allow a > fallback value to be returned instead of throwing an error when an > unknown GUC is provided. This would come in most useful when using > custom

Re: [HACKERS] ALTER COLUMN TYPE vs. domain constraints

Michael Paquier writes: > On Fri, Oct 27, 2017 at 11:15 AM, Tom Lane wrote: >> We could consider back-patching the attached to cover this, but >> I'm not entirely sure it's worth the trouble, because I haven't >> thought of any non-silly use-cases

Re: [HACKERS] [PATCH] Add two-arg for of current_setting(NAME, FALLBACK)

Hi better to send it as attachment Regards Pavel 2015-03-19 23:41 GMT+01:00 David Christensen : > The two-arg form of the current_setting() function will allow a > fallback value to be returned instead of throwing an error when an > unknown GUC is provided. This would

[HACKERS] [PATCH] Add two-arg for of current_setting(NAME, FALLBACK)

The two-arg form of the current_setting() function will allow a fallback value to be returned instead of throwing an error when an unknown GUC is provided. This would come in most useful when using custom GUCs; e.g.: -- errors out if the 'foo.bar' setting is unset SELECT

Re: [HACKERS] MERGE SQL Statement for PG11

On 31 October 2017 at 18:55, Peter Geoghegan wrote: > On Tue, Oct 31, 2017 at 2:25 AM, Simon Riggs wrote: >> If there are challenges ahead, its reasonable to ask for test cases >> for that now especially if you think you know what they already are. >> Imagine

Re: [HACKERS] Mapping MERGE onto CTEs (Re: MERGE SQL Statement for PG11)

Nico Williams wrote: > As an aside, I'd like to be able to control which CTEs are view-like and > which are table-like. In SQLite3, for example, they are all view-like, > and the optimizer will act accordingly, whereas in PG they are all > table-like, and thus optimizer barriers. There was a

[HACKERS] Mapping MERGE onto CTEs (Re: MERGE SQL Statement for PG11)

Is it possible to map MERGE onto a query with CTEs that does the the various DMLs, with all but the last RETURNING? Here's a sketch: WITH matched_rows AS ( SELECT FROM t WHERE ), updated_rows AS ( UPDATE t SET ... WHERE ... AND t in (SELECT j FROM

Re: [HACKERS] Re: PANIC: invalid index offnum: 186 when processing BRIN indexes in VACUUM

Alvaro Herrera wrote: > Tomas Vondra wrote: > > > FWIW I can reproduce this on 9.5, and I don't even need to run the > > UPDATE part. That is, INSERT + VACUUM running concurrently is enough to > > produce broken BRIN indexes :-( > > Hmm, I'm pretty sure we stress-tested brin in pretty much the

Re: [HACKERS] Account for cost and selectivity of HAVING quals

Ashutosh Bapat writes: > On Wed, Nov 1, 2017 at 3:15 AM, Tom Lane wrote: >> here's a patch to fix the planner so that eval costs and selectivity of >> HAVING quals are factored into the appropriate plan node numbers. >> ... >> + /* Add

Re: [HACKERS] [PATCH] Document the order of changing certain settings when using hot-standby servers

On 9/1/17 13:00, Robert Haas wrote: > Now you're proposing to add: > > If you want to increase these values you > should do so on all standby servers first, before applying the changes to > the primary. If you instead want to decrease these values you should do so > on the primary

Re: [HACKERS] Walsender timeouts and large transactions

Hi, sorry for the delay but I didn't have much time in past weeks to follow this thread. On 02/10/17 05:44, Kyotaro HORIGUCHI wrote: > Hello Sokolov. > > At Fri, 29 Sep 2017 15:19:23 +0300, Sokolov Yura > wrote in >

Re: [HACKERS] Commit fest 2017-11

On Wed, Nov 1, 2017 at 9:04 AM, Michael Paquier wrote: > Anybody willing to take the hat of the commit fest manager? If nobody, > I am fine to take the hat as default choice this time. And now it is open. Let's the fest begin. -- Michael -- Sent via pgsql-hackers

Re: [HACKERS] pg_basebackup fails on Windows when using tablespace mapping

On Wed, Nov 1, 2017 at 2:24 PM, Peter Eisentraut wrote: > Committed to master. I suppose this should be backpatched? Thanks! Yes this should be back-patched. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to

Re: [HACKERS] pg_basebackup fails on Windows when using tablespace mapping

On 9/10/17 00:39, Michael Paquier wrote: >> Okay. I have once again reviewed your patch and tested it on Windows >> as well as Linux. The patch LGTM. I am now marking it as Ready For >> Committer. Thanks. > > Thanks for the review, Ashutosh. Committed to master. I suppose this should be

Re: [HACKERS] strange relcache.c debug message

Alvaro Herrera writes: > While messing with BRIN bugs, I noticed this debug message in the server > log: > 2017-11-01 12:33:24.042 CET [361429] DEBUG: rehashing catalog cache id 14 > for pg_opclass; 17 tups, 8 buckets en carácter 194 > notice that at the end it says

Re: [HACKERS] proposal: extend shm_mq to support more use cases

On 1 November 2017 at 21:24, Ildus Kurbangaliev wrote: > Hello! Apparently the current version of shm_mq supports only one sender > and one receiver. I think it could be very useful to add possibility to > change senders and receivers. It could be achieved by adding

[HACKERS] PostgreSQL 10 parenthesized single-column updates can produce errors

Between 9.6.5 and 10, the handling of parenthesized single-column UPDATE statements changed. In 9.6.5, they were treated identically to unparenthesized single-column UPDATES. In 10, they are treated as multiple-column updates. This results in this being valid in Postgres 9.6.5, but an error in

[HACKERS] proposal: extend shm_mq to support more use cases

Hello! Apparently the current version of shm_mq supports only one sender and one receiver. I think it could be very useful to add possibility to change senders and receivers. It could be achieved by adding methods that remove sender or receiver for mq. As one of actual use cases can be some

Re: [HACKERS] Partition-wise aggregation/grouping

On Sat, Oct 28, 2017 at 3:07 PM, Robert Haas wrote: > On Fri, Oct 27, 2017 at 1:01 PM, Jeevan Chalke > wrote: > > 1. Added separate patch for costing Append node as discussed up-front in > the > > patch-set. > > 2. Since we now cost Append

Re: [HACKERS] Oracle to PostGre

As a brief note, this is probably not the best list for this. You would do better to ask questions like this on -general where you have more application developers and so forth. This is more of an SQL question so asking people who are hacking the codebase may not be the best way to get it

Re: [HACKERS] Account for cost and selectivity of HAVING quals

On Wed, Nov 1, 2017 at 3:15 AM, Tom Lane wrote: > Pursuant to the discussion at > https://www.postgresql.org/message-id/20171029112420.8920b5f...@mx.zeyos.com > here's a patch to fix the planner so that eval costs and selectivity of > HAVING quals are factored into the

Re: [HACKERS] Try to fix endless loop in ecpg with informix mode

> Any comments? Sorry, I've been working through the backlog of three weeks of traveling. > > I tried some tests with ecpg informix mode. > > When trying to store float data into a integer var, I got endless > > loop. > > > > The reason is: > > In informix mode, ecpg can accept > > string form

Re: [HACKERS] Try to fix endless loop in ecpg with informix mode

On Wed, Nov 1, 2017 at 12:22 PM, 高增琦 wrote: > Any comments? > Hi, You should register these patches for the next commitfest at https://commitfest.postgresql.org/15/. As Michael pointed out earlier, this commitfest will start soon so you should add your patches quickly.

[HACKERS] Oracle to PostGre

Hi, App is moving to Postgre from Oracel . After migrating the store procedure is throwing an error with collection type. *Oracle :* create or replace PROCEDURE"PROC1" ( , REQ_CURR_CODE IN VARCHAR2 , IS_VALID OUT VARCHAR2 , ERROR_MSG OUT VARCHAR2 ) AS TYPE INV_LINES_RT IS RECORD(

Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed

Hi In general, this approach looks good for me. > Regarding current state of patch, I'd like to see new options documented. > Also, it would be better to replace "bool sort_size" with enum assuming > there could be other sorting orders in future. > I am sending updated patch with some basic doc

[HACKERS] strange relcache.c debug message

While messing with BRIN bugs, I noticed this debug message in the server log: 2017-11-01 12:33:24.042 CET [361429] DEBUG: rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets en carácter 194 notice that at the end it says "at character 194". I suppose that's because of some

Re: [HACKERS] Try to fix endless loop in ecpg with informix mode

Any comments? 2017-10-26 16:03 GMT+08:00 高增琦 : > Hi, > > I tried some tests with ecpg informix mode. > When trying to store float data into a integer var, I got endless loop. > > The reason is: > In informix mode, ecpg can accept > string form of float number when processing

Re: [HACKERS] Dynamic result sets from procedures

2017-10-31 22:08 GMT+01:00 Peter Eisentraut < peter.eisentr...@2ndquadrant.com>: > This patch is more of a demo of what could be done, not my primary > focus, but if there is interest and some assistance, maybe we can make > something out of it. This patch also goes on top of "SQL procedures" >

Re: [HACKERS] Adding column_constraint description in ALTER TABLE synopsis

Hi all, Thanks Stephen for the suggestion. I wan't thinking globally enough. I was planning to look at it today but Amit was faster. So thanks Amit too! Have a nice day (UGT), Lætitia 2017-11-01 1:35 GMT+01:00 Amit Langote : > On 2017/10/31 21:31, Stephen Frost

[HACKERS] Commit fest 2017-11

Hi all, At the moment of writing this email, it is 9PM AoE (Anywhere on Earth) 31st of October. This means that the next commit fest will begin in 3 hours, and that any hackers willing to register patches for this commit fest have roughly three hours to do so (plus/minus N hours). This current

Re: [HACKERS] WIP: Restricting pg_rewind to data/wal dirs

Attached is the patch as submitted for commitfest. Please note, I am not adverse to adding an additional --Include-path directive if that avoids backwards-compatibility problems. However the patch is complex enough I would really prefer review on the rest of it to start first. This doesn't

Re: [HACKERS] Patch: restrict pg_rewind to whitelisted directories

On Tue, Oct 31, 2017 at 1:38 PM, Robert Haas wrote: > On Mon, Oct 30, 2017 at 6:44 PM, Chris Travers > wrote: > > The attached patch is cleaned up and filed for the commit fest this next > > month: > > It's generally better to post the patch on

Re: [HACKERS] Account for cost and selectivity of HAVING quals

Hello David, On Tue, October 31, 2017 7:54 pm, David G. Johnston wrote: > On Tue, Oct 31, 2017 at 4:31 PM, Tels > wrote: > >> >> ​​ >> That looks odd to me, it first uses output_tuples in a formula, then >> overwrites the value with a new value. Should these lines