Re: [HACKERS] Surprising behaviour of \set AUTOCOMMIT ON

Just for information, PG current behavior, "\set AUTOCOMMIT OFF" implicitly does/open "BEGIN;" block So, "\set AUTOCOMMIT ON" has no effect once "\set AUTOCOMMIT OFF" is issued until "END;" or "COMMIT;" or "ROLLBACK;" however, I think if exit session release the transactions then change

Re: [HACKERS] dsm_unpin_segment

On Tue, Aug 9, 2016 at 12:53 PM, Tom Lane wrote: > The larger picture here is that Robert is exhibiting a touching but > unfounded faith that extensions using this feature will contain zero bugs. > IMO there needs to be some positive defense against mistakes in using the >

Re: [HACKERS] Wait events monitoring future development

From: pgsql-hackers-ow...@postgresql.org > I used to think of that this kind of features should be enabled by default, > because when I was working at the previous company, I had only few features > to understand what is happening inside PostgreSQL by observing production > databases. I needed

Re: [HACKERS] Wait events monitoring future development

From: pgsql-hackers-ow...@postgresql.org > If you want to know why people are against enabling this monitoring by > default, above is the reason. What percentage of people do you think would > be willing to take a 10% performance penalty for monitoring like this? I > would bet very few, but the

Re: [HACKERS] Declarative partitioning

On 2016/08/09 6:02, Robert Haas wrote: > On Mon, Aug 8, 2016 at 1:40 AM, Amit Langote > wrote: >>> +1, if we could do it. It will need a change in the way Amit's patch stores >>> partitioning scheme in PartitionDesc. >> >> Okay, I will try to implement this in the

Re: [HACKERS] Wait events monitoring future development

2016-08-07 21:03 GMT+09:00 Ilya Kosmodemiansky : > I've summarized Wait events monitoring discussion at Developer unconference > in Ottawa this year on wiki: > > https://wiki.postgresql.org/wiki/PgCon_2016_Developer_Unconference/Wait_events_monitoring

Re: [HACKERS] Wait events monitoring future development

2016-08-09 11:49 GMT+09:00 Joshua D. Drake : > On 08/08/2016 07:37 PM, Bruce Momjian wrote: >> >> On Tue, Aug 9, 2016 at 02:06:40AM +, Tsunakawa, Takayuki wrote: >>> >>> I hope wait event monitoring will be on by default even if the overhead >>> is not >>> almost zero,

Re: [HACKERS] Wait events monitoring future development

On 08/08/2016 07:37 PM, Bruce Momjian wrote: On Tue, Aug 9, 2016 at 02:06:40AM +, Tsunakawa, Takayuki wrote: I hope wait event monitoring will be on by default even if the overhead is not almost zero, because the data needs to be readily available for faster troubleshooting. IMO, the

Re: [HACKERS] Wait events monitoring future development

On Tue, Aug 9, 2016 at 02:06:40AM +, Tsunakawa, Takayuki wrote: > I hope wait event monitoring will be on by default even if the overhead is not > almost zero, because the data needs to be readily available for faster > troubleshooting. IMO, the benefit would be worth even 10% overhead. If

Re: [HACKERS] Wait events monitoring future development

From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ilya Kosmodemiansky I've summarized Wait events monitoring discussion at Developer unconference in Ottawa this year on wiki:

Re: [HACKERS] dsm_unpin_segment

Thomas Munro writes: > Yeah, I was considering unbalanced pin/unpin requests to be a > programming error. To be more defensive about that, how about I add a > boolean 'pinned' to dsm_control_item, and elog(ERROR, ...) if it's not > in the expected state when you

Re: [HACKERS] dsm_unpin_segment

On Tue, Aug 9, 2016 at 12:22 PM, Robert Haas wrote: > On Mon, Aug 8, 2016 at 7:33 PM, Tom Lane wrote: >> Thomas Munro writes: >>> Please find attached a patch to add a corresponding operation >>> 'dsm_unpin_segment'.

Re: [HACKERS] dsm_unpin_segment

On Mon, Aug 8, 2016 at 7:33 PM, Tom Lane wrote: > Thomas Munro writes: >> DSM segments have a concept of 'pinning'. Normally, segments are >> destroyed when they are no longer mapped by any backend, using a >> reference counting scheme. If you

Re: [HACKERS] dsm_unpin_segment

Thomas Munro writes: > DSM segments have a concept of 'pinning'. Normally, segments are > destroyed when they are no longer mapped by any backend, using a > reference counting scheme. If you call dsm_pin_segment(segment), that > is disabled so that the segment

Re: [HACKERS] Slowness of extended protocol

>> On the other hand, usage of some well-defined statement name to trigger >> the special case would be fine: all pgbouncer versions would pass those >> parse/bind/exec message as if it were regular messages. > > I do not accept this idea that retroactively defining special semantics > for

[HACKERS] dsm_unpin_segment

Hi hackers, DSM segments have a concept of 'pinning'. Normally, segments are destroyed when they are no longer mapped by any backend, using a reference counting scheme. If you call dsm_pin_segment(segment), that is disabled so that the segment won't be destroyed until the cluster is shut down.

Re: [HACKERS] Wait events monitoring future development

On Mon, Aug 8, 2016 at 11:47:11PM +0200, Ilya Kosmodemiansky wrote: > On Mon, Aug 8, 2016 at 7:03 PM, Bruce Momjian wrote: > > It seems asking users to run pg_test_timing before deploying to check > > the overhead would be sufficient. > > I'am not sure. Time measurement for

Re: [HACKERS] Wait events monitoring future development

On Mon, Aug 8, 2016 at 7:03 PM, Bruce Momjian wrote: > It seems asking users to run pg_test_timing before deploying to check > the overhead would be sufficient. I'am not sure. Time measurement for waits is slightly more complicated than a time measurement for explain analyze: a

Re: [HACKERS] Declarative partitioning

On Mon, Aug 8, 2016 at 1:40 AM, Amit Langote wrote: >> +1, if we could do it. It will need a change in the way Amit's patch stores >> partitioning scheme in PartitionDesc. > > Okay, I will try to implement this in the next version of the patch. > > One thing that

Re: [HACKERS] Heap WARM Tuples - Design Draft

On Mon, Aug 8, 2016 at 5:24 PM, Bruce Momjian wrote: > On Mon, Aug 8, 2016 at 03:36:12PM -0300, Claudio Freire wrote: >> I think I prefer a more thorough approach. >> >> Increment/decrement may get very complicated with custom opclasses, >> for instance. A column-change bitmap

Re: [HACKERS] Heap WARM Tuples - Design Draft

On Mon, Aug 8, 2016 at 03:36:12PM -0300, Claudio Freire wrote: > I think I prefer a more thorough approach. > > Increment/decrement may get very complicated with custom opclasses, > for instance. A column-change bitmap won't know how to handle > funcional indexes, etc. Hot does HOT handle them?

Re: [HACKERS] Heap WARM Tuples - Design Draft

On Mon, Aug 8, 2016 at 11:22:49PM +0530, Pavan Deolasee wrote: > What I am currently trying to do is to reuse at least the BlockNumber field in > t_ctid. For HOT/WARM chains, that field is really unused (except the last > tuple > when regular update needs to store block number of the new block).

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On Sun, Aug 7, 2016 at 11:53 PM, Alvaro Herrera wrote: >> Building on the has-property approach Andrew suggested, I wonder if >> we need something like pg_index_column_has_property(indexoid, colno, >> propertyname) with properties like "sortable", "desc", "nulls first".

[HACKERS] Parallel tuplesort, partitioning, merging, and the future

Over on the "Parallel tuplesort (for parallel B-Tree index creation)" thread [1], there has been some discussion of merging vs. partitioning. There is a concern about the fact the merge of the tuplesort used to build a B-Tree is not itself parallelized. There is a weak consensus that we'd be

Re: [HACKERS] Slowness of extended protocol

I'm sorry, we are discussing technical details with no real-life use case to cover that. I do not want to suck time for no reason. Please accept my sincere apologies for not asking the real-life case earlier. Shay, can you come up with a real-life use case when those "I claim the statement will

Re: [HACKERS] Slowness of extended protocol

Shay Rojansky : > Ah, I understand the proposal better now - you're not proposing encoding a > new message type in an old one, but rather a magic statement name in Parse > which triggers an optimized processing path in PostgreSQL, that wouldn't go > through the query cache etc. >

Re: [HACKERS] Slowness of extended protocol

Shay Rojansky writes: > Ah, I understand the proposal better now - you're not proposing encoding a > new message type in an old one, but rather a magic statement name in Parse > which triggers an optimized processing path in PostgreSQL, that wouldn't go > through the query cache

Re: [HACKERS] Heap WARM Tuples - Design Draft

On Mon, Aug 8, 2016 at 2:52 PM, Pavan Deolasee wrote: >> >> My guess is we would need one bit to mark a WARM chain, and perhaps >> reuse obsolete pre-9.0 HEAP_MOVED_OFF to indicate increment-only or >> decrement-only. > > > I am not convinced that the checking for

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

I wrote: > Having said all that, it is unfortunate that 9.6 is going to go out > without any good solution to this need. But as Robert already pointed > out, trying to fix it now would force delaying 9.6rc1 by several weeks > (and that's assuming that it doesn't take very long to get consensus >

Re: [HACKERS] Slowness of extended protocol

Vladimir wrote: > On the other hand, usage of some well-defined statement name to trigger >>> the special case would be fine: all pgbouncer versions would pass those >>> parse/bind/exec message as if it were regular messages. >>> >> >> Can you elaborate on what that means exactly? Are you

Re: [HACKERS] Slowness of extended protocol

Vladimir Sitnikov writes: > The point is "adding a message to current v3 protocol is not a backward > compatible change". > The problem with adding new message types is not only "client support", but > deployment issues as well: new message would require simultaneous

Re: [HACKERS] Heap WARM Tuples - Design Draft

On Mon, Aug 8, 2016 at 11:08 PM, Bruce Momjian wrote: > On Sun, Aug 7, 2016 at 12:55:01PM -0400, Bruce Momjian wrote: > > On Sun, Aug 7, 2016 at 10:49:45AM -0400, Bruce Momjian wrote: > > > OK, crazy idea time --- what if we only do WARM chain additions when > all > > >

Re: [HACKERS] Slowness of extended protocol

Shay Rojansky : > That's definitely a valid point. But do you think it's a strong enough > argument to avoid ever adding new messages? > The point is "adding a message to current v3 protocol is not a backward compatible change". The problem with adding new message types is not

Re: [HACKERS] Heap WARM Tuples - Design Draft

On Sun, Aug 7, 2016 at 12:55:01PM -0400, Bruce Momjian wrote: > On Sun, Aug 7, 2016 at 10:49:45AM -0400, Bruce Momjian wrote: > > OK, crazy idea time --- what if we only do WARM chain additions when all > > indexed values are increasing (with NULLs higher than all values)? (If > > a key is

Re: [HACKERS] Re: GiST optimizing memmoves in gistplacetopage for fixed-size updates [PoC]

06.08.2016 19:51, Andrew Borodin: Anastasia , thank you for your attentive code examine. 2016-08-05 21:19 GMT+05:00 Anastasia Lubennikova : First of all, shouldn't we use MAXALIGN(oldsize) instead of oldsize? Although, I'm quite sure that it was already aligned

Re: [HACKERS] Wait events monitoring future development

On Mon, Aug 8, 2016 at 10:03 AM, Bruce Momjian wrote: > On Mon, Aug 8, 2016 at 04:43:40PM +0530, Amit Kapila wrote: >> >According to developers, overhead is small, but many people have doubts >> > that it can be much more significant for intensive workloads. Obviously, it

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Vik Fearing writes: > On 08/08/16 15:38, Tom Lane wrote: >> Well, the next step is to fill in the blanks: what properties need to be >> queryable? > That's less urgent; adding missing properties does not require a > catversion bump. If the complaint is "I can do X before

Re: [HACKERS] Surprising behaviour of \set AUTOCOMMIT ON

On 08/08/16 17:02, Robert Haas wrote: > On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed wrote: >> Thank you for inputs everyone. >> >> The opinions on this thread can be classified into following >> 1. Commit >> 2. Rollback >> 3. Error >> 4. Warning >> >> As per opinion

Re: [HACKERS] Wait events monitoring future development

On Mon, Aug 8, 2016 at 04:43:40PM +0530, Amit Kapila wrote: > >According to developers, overhead is small, but many people have doubts > > that it can be much more significant for intensive workloads. Obviously, it > > is not an easy task to test, because you need to put doubtfully > >

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On 08/08/16 15:38, Tom Lane wrote: > Alvaro Herrera writes: >> Tom Lane wrote: >>> Building on the has-property approach Andrew suggested, I wonder if >>> we need something like pg_index_column_has_property(indexoid, colno, >>> propertyname) with properties like

Re: [HACKERS] Heap WARM Tuples - Design Draft

On Mon, Aug 8, 2016 at 06:34:46PM +0530, Amit Kapila wrote: > I think here expensive part would be recheck for the cases where the > index value is changed to a different value (value which doesn't exist > in WARM chain). You anyway have to add the new entry (key,TID) in > index, but each time

Re: [HACKERS] Surprising behaviour of \set AUTOCOMMIT ON

On Mon, Aug 8, 2016 at 11:17 AM, David G. Johnston wrote: > On Mon, Aug 8, 2016 at 11:02 AM, Robert Haas wrote: >> >> On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed >> wrote: >> > Thank you for inputs everyone. >> > >> >

Re: [HACKERS] Surprising behaviour of \set AUTOCOMMIT ON

On Mon, Aug 8, 2016 at 11:02 AM, Robert Haas wrote: > On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed > wrote: > > Thank you for inputs everyone. > > > > The opinions on this thread can be classified into following > > 1. Commit > > 2. Rollback > > 3.

Re: [HACKERS] Refactoring of heapam code.

Anastasia Lubennikova wrote: > By the way, I thought about looking at the mentioned patch and > probably reviewing it, but didn't find any of your patches on the > current commitfest. Could you point out the thread? Sorry, I haven't posted anything yet. > >Agreed. But changing its name while

Re: [HACKERS] Surprising behaviour of \set AUTOCOMMIT ON

On Mon, Aug 8, 2016 at 10:10 AM, Rahila Syed wrote: > Thank you for inputs everyone. > > The opinions on this thread can be classified into following > 1. Commit > 2. Rollback > 3. Error > 4. Warning > > As per opinion upthread, issuing implicit commit immediately after

Re: [HACKERS] Surprising behaviour of \set AUTOCOMMIT ON

Thank you for inputs everyone. The opinions on this thread can be classified into following 1. Commit 2. Rollback 3. Error 4. Warning As per opinion upthread, issuing implicit commit immediately after switching autocommit to ON, can be unsafe if it was not desired. While I agree that its

[HACKERS] extract text from XML

Hi, I have found a basic use case which is supported by the xml2 module, but is unsupported by the new XML API. It is not possible to correctly extract text (either from text nodes or attribute values) which contains the characters '<', '&', or '>'. xpath() (correctly) returns XML text nodes for

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Alvaro Herrera writes: > Tom Lane wrote: >> Building on the has-property approach Andrew suggested, I wonder if >> we need something like pg_index_column_has_property(indexoid, colno, >> propertyname) with properties like "sortable", "desc", "nulls first". > This seems

Re: [HACKERS] Heap WARM Tuples - Design Draft

On Fri, Aug 5, 2016 at 9:57 AM, Pavan Deolasee wrote: > > > On Fri, Aug 5, 2016 at 8:23 AM, Claudio Freire > wrote: >> >> On Thu, Aug 4, 2016 at 11:15 PM, Bruce Momjian wrote: >> >> > >> > OK, that's a lot of text, and I am

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

On 8 August 2016 at 03:49, Vladimir Sitnikov wrote: > > > Tom Lane : > >> FWIW, this thread started on 25-July, less than two weeks ago. > > > Technically speaking, there was a pgsql-jdbc thread started on May 14: >

Re: [HACKERS] [sqlsmith] Failed assertion in joinrels.c

On Mon, Aug 8, 2016 at 5:23 PM, Amit Kapila wrote: > > > > Your other options and the option you choose are same. > > > Sorry typo, I meant ERRCODE_INVALID_OBJECT_DEFINITION. > Did you consider to use ERRCODE_UNDEFINED_COLUMN with error messages > like: "type %u does

Re: [HACKERS] [sqlsmith] Failed assertion in joinrels.c

On Mon, Aug 8, 2016 at 5:11 PM, Amit Kapila wrote: > On Mon, Aug 8, 2016 at 4:58 PM, Dilip Kumar wrote: >> >> On Wed, Aug 3, 2016 at 5:35 AM, Robert Haas wrote: >>> >>> I think that's just making life difficult. If nothing

Re: [HACKERS] [sqlsmith] Failed assertion in joinrels.c

On Mon, Aug 8, 2016 at 4:58 PM, Dilip Kumar wrote: > > On Wed, Aug 3, 2016 at 5:35 AM, Robert Haas wrote: >> >> I think that's just making life difficult. If nothing else, sqlsmith >> hunts around for functions it can call that return internal

Re: [HACKERS] [sqlsmith] Failed assertion in joinrels.c

On Wed, Aug 3, 2016 at 5:35 AM, Robert Haas wrote: > I think that's just making life difficult. If nothing else, sqlsmith > hunts around for functions it can call that return internal errors, > and if we refuse to fix all of them to return user-facing errors, then > it's

Re: [HACKERS] Wait events monitoring future development

On Sun, Aug 7, 2016 at 5:33 PM, Ilya Kosmodemiansky wrote: > Hi, > > I've summarized Wait events monitoring discussion at Developer unconference > in Ottawa this year on wiki: > >

Re: [HACKERS] Slowness of extended protocol

> > We could call this "protocol 3.1" since it doesn't break backwards >> compatibility (no incompatible server-initiated message changes, but it >> does include a feature that won't be supported by servers which only >> support 3.0. This could be a sort of "semantic versioning" for the protocol

Re: [HACKERS] handling unconvertible error messages

On Mon, 08 Aug 2016 18:28:57 +0900 (Tokyo Standard Time) Kyotaro HORIGUCHI wrote: > > I don't see charset compatibility to be easily detectable, In the worst case we can hardcode explicit compatibility table. There is limited set of languages, which have

Re: [HACKERS] Refactoring of heapam code.

08.08.2016 03:51, Michael Paquier: On Sat, Aug 6, 2016 at 2:56 AM, Alvaro Herrera wrote: Anastasia Lubennikova wrote: So there is a couple of patches. They do not cover all mentioned problems, but I'd like to get a feedback before continuing. I agree that we could

Re: [HACKERS] handling unconvertible error messages

On Mon, 08 Aug 2016 18:11:54 +0900 (Tokyo Standard Time) Kyotaro HORIGUCHI wrote: > At Mon, 08 Aug 2016 17:18:21 +0900 (Tokyo Standard Time), Kyotaro > HORIGUCHI wrote in >

Re: [HACKERS] handling unconvertible error messages

At Mon, 08 Aug 2016 18:11:54 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20160808.181154.252052789.horiguchi.kyot...@lab.ntt.co.jp> > At Mon, 08 Aug 2016 17:18:21 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI >

Re: [HACKERS] Refactoring of heapam code.

05.08.2016 20:56, Alvaro Herrera: Anastasia Lubennikova wrote: Working on page compression and some other issues related to access methods, I found out that the code related to heap looks too complicated. Much more complicated, than it should be. Since I anyway got into this area, I want to

Re: [HACKERS] handling unconvertible error messages

On Mon, 08 Aug 2016 17:18:21 +0900 (Tokyo Standard Time) Kyotaro HORIGUCHI wrote: > > I'm not sure what messages may be raised before authentication > but it can be a more generic-solution. (Adding check during > on-session.) Definitely, there can be

Re: [HACKERS] handling unconvertible error messages

At Mon, 08 Aug 2016 17:18:21 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20160808.171821.100221089.horiguchi.kyot...@lab.ntt.co.jp> > Looking at check_client_encoding(), the comment says as following. > > | * If we are not within a transaction then

Re: [HACKERS] [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

05.08.2016 19:41, Robert Haas: 2. This inserts additional code in a bunch of really low-level places like heap_hot_search_buffer, heap_update, heap_delete, etc. I think what you've basically done here is create a new, in-memory heap AM and then, because we don't have an API for adding new

[HACKERS] per-statement-level INSTEAD OF triggers

Hi hackers, I'm asking out of curiosity, do anyone know why we don't have per-statement-level INSTEAD OF triggers? I looked into the standard SQL (20xx draft), but I can't find the restriction such that INSTEAD OF triggers must be row-level. Is there any technical difficulties, or other reasons

Re: [HACKERS] handling unconvertible error messages

At Mon, 8 Aug 2016 10:19:10 +0300, Victor Wagner wrote in <20160808101910.49bee...@fafnir.local.vm> > On Fri, 5 Aug 2016 11:21:44 -0400 > Peter Eisentraut wrote: > > > On 8/4/16 2:45 AM, Victor Wagner wrote: > > > 4. At the session startup

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Tom Lane : > FWIW, this thread started on 25-July, less than two weeks ago. Technically speaking, there was a pgsql-jdbc thread started on May 14: https://www.postgresql.org/message-id/nh72v6%24582%241%40ger.gmane.org 9.6beta1 was released on May 12 The fact that it wasn't

Re: [HACKERS] Possible duplicate release of buffer lock.

Hello, At Sat, 06 Aug 2016 12:45:32 -0400, Tom Lane wrote in <16748.1470501...@sss.pgh.pa.us> > Amit Kapila writes: > > Isn't the problem here is that due to some reason (like concurrent > > split), the code in question (loop -- > > while

Re: [HACKERS] handling unconvertible error messages

On Fri, 5 Aug 2016 11:21:44 -0400 Peter Eisentraut wrote: > On 8/4/16 2:45 AM, Victor Wagner wrote: > > 4. At the session startup try to reinitializie LC_MESSAGES locale > > category with the combination > > of the server (or better client-send) language and

Re: [HACKERS] handling unconvertible error messages

On Fri, 5 Aug 2016 11:23:37 -0400 Peter Eisentraut wrote: > On 8/4/16 9:42 AM, Tom Lane wrote: > > I'm inclined to think that we should reset the message locale > > to C as soon as we've forked away from the postmaster, and leave it > > that way until we've

Re: [HACKERS] Slowness of extended protocol

Shay Rojansky : > > That sounds right to me. As you say, the server version is sent early in > the startup phase, before any queries are sent to the backend, so frontends > know which server they're communicating with. > > We could call this "protocol 3.1" since it doesn't break

Re: [HACKERS] Declarative partitioning

> IIUC, this seems like a combination of 2 and 3 above: > > So, we have the following list partitions (as read from the catalog) > > Table 1: p1 {'b', 'f'}, p2 {'c', 'd'}, p3 {'a', 'e'} > Table 2: p1 {'c', 'd'}, p2 {'a', 'e'}, p3 {'b', 'f'} > > By applying the method of 3: > > Table 1: p3 {'a',