[HACKERS] [tiny doc fix] statistics are not retained across immediate shutdown

[Tsunakawa, Takayuki]

Hi,

In the following page, statistics are kept across server restarts:

http://www.postgresql.org/docs/current/static/monitoring-stats.html

When the server shuts down, a permanent copy of the statistics data is stored 
in the global subdirectory, so that statistics can be retained across server 
restarts.


However, statistics are not retained after immediate shutdown (pg_ctl stop 
-mi).  You may say pg_ctl stop -mi is not a shutdown but an abort, so the 
sentence is not wrong, but it's an immediate shutdown and one mode of 
shutdown.

I propose a tiny fix to clarify this.  Please find the attached patch.

I'd like this to be backported at least 9.2.  Thanks.


Regards, Takayuki Tsunakawa


stats_reset_in_recovery.patch
Description: stats_reset_in_recovery.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Question and suggestion about application binary compatibility policy

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> Sorry I fail to understand what you mean with "legal"? Are you wondering
> about license restrictions? There are none.

Sorry, I just meant "correct" or "valid".


> As for compatibility, that's what major version numbers are for. This is
> not a PostgreSQL topic, but a general system one as for instance the same
> holds for libc.

Yes, but Windows users probably don't understand or know it.  So, I suggested 
explicitly describing the application binary compatibility policy in the 
PostgreSQL manual.  What do you think about it?


Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Question and suggestion about application binary compatibility policy

[Tsunakawa, Takayuki]

> From: Michael Meskes [mailto:mes...@postgresql.org]
> > Yes, but Windows users probably don't understand or know it.  So, I
> > suggested explicitly describing the application binary compatibility
> > policy in the PostgreSQL manual.  What do you think about it?
> 
> Couldn't you point your customer to the system documentation?
> 
> Personally I don't think standard system behavior should be documented for
> each application relying on it but ymmv.

I couldn't find appropriate system documentation.  Regarding Linux, I remember 
I saw some HOWTO on tldp.org website which explains the concept of shared 
library soname, but it's not very friendly for users who just want to know the 
application binary compatibility policy of PostgreSQL.  And I don't think 
there's suitable documentation on Windows.  Even if there is any, users will 
not be sure whether PostgreSQL follows those platform-specific conventions.  
They may have doubts about it, because even the product version "PostgreSQL 
x.y.z" causes misconception that x is the major version and y is the minor one.

So, I suggested documenting the compatibility policy for clarification and user 
friendliness as in the Oracle Database documentation below.

http://docs.oracle.com/database/121/UPGRD/app.htm#UPGRD12547


BTW, although this may be a separate topic, it may be better that we add the 
major version in the library names like libpq5.dll and libecpg6.dll, so that 
the application can fail to run with the incompatible versions of libpq and 
libecpg.  FYI:

https://en.wikipedia.org/wiki/Side-by-side_assembly

[Excerpt]
Microsoft Visual C++ 2005 and 2008 employ SxS with all C runtime libraries. 
However, runtime libraries in Visual C++ 2010 no longer use this technology; 
instead, they include the version number of a DLL in its file name, which means 
that different versions of one DLL will technically be completely different 
DLLs now.


Any comments on these?  If there's no strong objection, I think I'll submit a 
documentation patch in the future.

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Question and suggestion about application binary compatibility policy

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer
While that's probably OK, it's not especially desirable. The typical Windows 
deployment model involves the application bundling all its direct dependencies 
except when those are provided by a runtime redistributable designed for that 
purpose.


I think so, too, but I'm not confident that's typical.  Some people may think 
of PostgreSQL binaries as a shared component for their applications and place 
it in one place, just like the PostgreSQL library package is in /usr/lib/pgsql.


- Use the app with newer PostgreSQL major versions without rebuilding the app.  
That is, the users just replaces the PostgreSQL client.

... especially since there isn't a client-only PostgreSQL distribution Windows.


There's a client-only installation method as follows, although I haven't 
checked whether EnterpriseDB, OpenSCG, or any other PostgreSQL-based products 
provide client-only installation.
https://www.postgresql.org/docs/devel/static/install-windows-full.html#AEN30192

[Excerpt]
--
If you want to install only the client applications and interface libraries, 
then you can use these commands:

install c:\destination\directory client
--


How about adding an article about application binary compatibility in the 
following section, as well as chapters for libpq, ECPG, etc?

It would be sensible to better define the binary compatibility expectations 
that clients may rely upon and, when they are broken, a managed way in which 
they're broken (soname bump, etc).

If you have an interest in the area it might be worth drafting a proposal after 
taking a look at past binary compatibility discussions on -hackers.

Sure, I'll submit a patch to pgsql-docs.  Thanks to Michael's confirmation, I 
could answer the customer's question, so this is not an immediate task now.  
But I'll do.


- On-disk format
- Wire protocol
- SQL-level (data types, syntax, encoding handling, settings, ...)

Yes, I recognize these items.  I omitted them because:

- On-disk format: this is handled in the PostgreSQL manual article about 
upgrading
- Wire protocol: I believe the compatibility will be retained
- SQL-level: ditto

But if you feel a need for their compatibility description for completeness, 
I'll add it.  ... Yes, the explicit explanation may be necessary so that users 
are assured that the PostgreSQL compatibility policy matches their expectation.


The simplest solution would be to incorporate the soname, so it becomes 
libpq0509.dll for example. Like VS does with the VS runtime. The main downside 
is that because it's not a true soname and the OS has no such concept, the 
linker for everything compiled against that DLL must specify the versioned DLL 
name, it can't just link to 'libpq' .

Although I haven’t examined yet, some directive in .def file might enable 
applications to specify libpq.lib at build time and to link with libpq5.dll at 
run time.


Regards
Takayuki Tsunakawa

Re: [HACKERS] Question and suggestion about application binary compatibility policy

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Marco Atzeri
> on cygwin the postgresql binary package already include the library
> versions:
> 
>/usr/bin/cygecpg-6.dll
>/usr/bin/cygecpg_compat-3.dll
>/usr/bin/cygpgtypes-3.dll
>/usr/bin/cygpq-5.dll
> 
> attached the patch used for the build.

Thanks for the information.   I didn't know there's a PostgreSQL binary package 
for Cygwin.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Question and suggestion about application binary compatibility policy

[Tsunakawa, Takayuki]

> From: Michael Meskes [mailto:mes...@postgresql.org]
> e.g. a random hit from google:=C2=A0https://www.bottomupcs.com/libra
> ries_and_the_linker.xhtml
> 
> There even is a wikipedia page about
> it:=C2=A0https://en.wikipedia.org/wiki/
> Soname

Thank you for good pointers.  The former is particularly nice.

> > BTW, although this may be a separate topic, it may be better that we
> > add the major version in the library names like libpq5.dll and
> > libecpg6.dll, so that the application can fail to run with the
> > incompatible versions of libpq and libecpg.=C2=A0=C2=A0FYI:
> 
> Does this mean you cannot have to versions of libpq installed on the same
> Windows system at the same time?

No, you can have different versions in separate folders, as in:

C:\Program Files\PostgreSQL\9.2
C:\Program Files\PostgreSQL\9.5

and change the PATH environment variable to point to a newer version when you 
want to use the existing application without rebuilding it.

However, the problem I pointed out is that when the new library is incompatible 
with the older one, say the major version of libpq.dll changes from 5 to 6, the 
application user and/or developer cannot notice the incompatibility immediately 
and easily.  On Unix/Linux, the application fails to start because the older 
library is not found.  On the other hand, the application will start on Windows 
and probably cause difficult trouble due to the incompatibility.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PATCH: Batch/pipelining support for libpq

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer
I'll follow this mood. Yeha.


BTW, I've publushed the HTML-ified SGML docs to 
http://2ndquadrant.github.io/postgres/libpq-batch-mode.html as a preview.


Sorry for my late reply.  Fantastic performance improvement, nice 
documentation, and amazing rapid development!  I think I’ll join the review & 
testing in 2016/9 CF.

Regards
Takayuki Tsunakawa

[HACKERS] Question and suggestion about application binary compatibility policy

[Tsunakawa, Takayuki]

Hello,

I'd like to ask you about the policy of application binary compatibility.  And 
have a suggestion as well.

QUESTION
==

My customer asked me if the following usage is correct.

- Build an embedded SQL C application with PostgreSQL 9.2.
- Distribute the app without ecpg nor libpq libraries.  Require users to 
install PostgreSQL client which includes ecpg and libpq libraries.
- Use the app with newer PostgreSQL major versions without rebuilding the app.  
That is, the users just replaces the PostgreSQL client.

I expect this is legal, because the so_major versions of ecpg and libpq are 6 
and 5 respectively for all PostgreSQL 9.x versions so far.  However, I could 
not find any description of this binary compatibility policy in the manual, so 
I haven't been able to answer the customer.

What's the official policy of application binary compatibility?  I found the 
same discussion in the below thread, but I'm afraid any clear answer wasn't 
given:

https://www.postgresql.org/message-id/522f0b6b.1040...@4js.com


SUGGESTION
==

How about adding an article about application binary compatibility in the 
following section, as well as chapters for libpq, ECPG, etc?

17.6. Upgrading a PostgreSQL Clust
https://www.postgresql.org/docs/devel/static/upgrading.html

There are three kinds of application assets that users are concerned about when 
upgrading.  Are there anything else to mention?

* libpq app
* ECPG app
* C-language user defined function (and other stuff dependent on it, such as 
extensions, UDTs, etc.)

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Question and suggestion about application binary compatibility policy

[Tsunakawa, Takayuki]

> From: Bruce Momjian [mailto:br...@momjian.us]
> We have this text in src/tools/RELEASE_CHANGES:
> ...
> This is saying running against a mismatched minor version should be fine
> for a binary.

Thanks for a good rationale.


> I know this thread is old but it bounced around a lot of ideas.  I think
> there are some open questions:
> 
> *  Will a new application link against an older minor-version libpq?
> *  Will an old application link against a newer minor-version libpq?

The former does not always hold true, if the application uses a new libpq 
function which is not in an old miner-version.  But I think the 
backward-compatibility is enough.


Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Feature suggestions: "dead letter"-savepoint.

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer
Now, what I do think we need is to give the client the ability to determine 
whether one of its xacts actually committed or not when it lost the session 
after dispatching COMMIT but before getting a confirmation from the server and 
persistently storing that knowledge. Right now if you want that you have to do 
full 2PC. You shouldn't need to, you should be able to get the xid when the 
xact is assigned it and store it somewhere locally. Then later, if you're 
unsure if that xid committed or not due to a client crash etc, you should be 
able to do some kind of SELECT pg_xact_is_committed(xid)to find out. Right 
now this is possible to write with a pretty simple extension, but adds an extra 
roundtrip for a SELECT txid_current() call (unless you pipeline it). I'd prefer 
that the server just tell you when an xid is assigned. And yes, I think xid is 
the right identifier for this; it's short, simple, and while it wraps around it 
takes long enough to do so that it's very well suited for this job.


This is interesting.  Oracle provides Transaction Guard for this.  Our 
customers also sometimes encounter the trouble of duplicate records in the 
database when, when their apps get disconnected during commit and reconnect to 
insert the same record again.

Regards
Takayuki Tsunakawa

Re: [HACKERS] initdb issue on 64-bit Windows - (Was: [pgsql-packagers] PG 9.6beta2 tarballs are ready)

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> Sent: Friday, June 24, 2016 11:37 AM
> On Fri, Jun 24, 2016 at 11:33 AM, Craig Ringer 
> wrote:
> It might be worth testing that out and adding an initdb startup flag
> > to create the directory, since initdb is such a PITA to debug.
> 
> I was more thinking about putting that under -DDEBUG for example.
> 

I think just the existing option -d (--debug) and/or -n (--no-clean) would be 
OK.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Parser extensions (maybe for 10?)

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Arcadiy Ivanov

Currently the parser and lexer are fully fixed at compile-time and not amenable 
to the extensions - extensions are only capable of introducing functions etc.

There is, however, an advantage to being able if not add or alter complete 
statements (which would be nice), but to at least augment portions of syntax 
for existing ones in some places.


I saw the following discussion in the past, but I haven’t read it:

Pluggable Parser
http://www.postgresql.org/message-id/bf2827dcce55594c8d7a8f7ffd3ab77159878...@szxeml521-mbs.china.huawei.com

I’m interested in the pluggable, extensible parser for two purposes.  One is to 
add compatibility for other databases.

The other is for the ODBC (and possibly JDBC) driver.
The ODBC/JDBC specs require some unique syntax constructs, e.g. {? = call 
funcname(arguments)} to call stored procs/functions.  Currently, the ODBC/JDBC 
drivers are forced to parse and convert SQL statements.  It is ideal for 
PostgreSQL itself to understand the ODBC/JDBC syntax, and eliminate the burdon 
of parsing statements from the JDBC/ODBC drivers.

Regards
Takayuki Tsunakawa

Re: [HACKERS] How can we expand PostgreSQL ecosystem?

[Tsunakawa, Takayuki]

Hello, Josh,

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Josh berkus> 
> Crossing this over to pgsql-advocacy list where it really belongs.
> That's what that list is *for*.
> 
> Especially since the discussion on -hackers has focused on new PostgreSQL
> Features, which while also good don't address the general question.
> 

Thank you for pointing me to the correct place.  I wondered which list is 
better, because I thought this topic whould be better discussed among hackers.  
I'll post subsequent mails only to pgsql-advocacy.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] How can we expand PostgreSQL ecosystem?

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Mark Kirkwood
> For cloud - in particular Openstack (which I am working with ATM), the
> biggest thing would be:
> 
> - multi-master replication
> 
> or failing that:
> 
> - self managing single master failover (voting/quorum etc)
> 
> so that operators can essentially 'set and forget'. We currently use
> Mysql+ Galera (multi master) and Mongodb (self managing single master)
> and the convenience and simplicity is just so important (Openstack is a
> huge complex collection of services - hand holding of any one service is
> pretty much a non starter).

Yes, I was also asked whether PostgreSQL has any optional functionality like 
Galera Cluster for MySQL.  He was planning a scalable PaaS service which 
performs heavy reads and writes.  Demand exists.

Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] foreign table batch inserts

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer
Well, there's FE/BE level batching/pipelining already. Just no access to it 
from libpq.

Oh, really.  The Bind ('B') appears to take one set of parameter values, not 
multiple sets (array).  Anyway, I had to say "I want batch update API in libpq" 
to use it in ODBC and ECPG.

Regards
Takayuki Tsunakawa

Re: [HACKERS] Is the unfair lwlock behavior intended?

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Alexander Korotkov
I've already observed such behavior, see [1].  I think that now there is no 
consensus on how to fix that.  For instance, Andres express opinion that this 
shouldn't be fixed from LWLock side [2].


Thank you for nice pointers.  I understood.


> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ants Aasma
> 9.5 had significant LWLock scalability improvements. This might
> improve performance enough so that exclusive lockers don't get
> completely starved. It would be helpful if you could test if it's
> still possible to trigger starvation with the new code.

Unfortunately, we cannot test anymore because the customer's system is now in 
production.  The heavy ProcArray contention was caused mainly by too many tuple 
visibility tests, which in turn were caused by unintended sequential scans.  
Then the customer avoided the contention problem by adding an index and 
reducing the number of concurrent active sessions.

> From: Andres Freund [mailto:and...@anarazel.de]
> Are you sure you're actually queued behind share locks, and not primarily
> behind the lwlock's spinlocks? The latter is what I've seen in similar cases.

I think so, because the stack trace showed that the backends were waiting in 
TransactionIsInProgress (or some function in the commit processing) -> 
LWLockAcquire -> PGSemaphoreLock -> semop(), not including spinlock-related 
functions.


> The problem is that half-way fair locks, which are frequently acquired both
> in shared and exclusive mode, have really bad throughput characteristics
> on modern multi-socket systems. We mostly get away with fair locking on
> object level (after considerable work re fast-path locking), because nearly
> all access are non-conflicting.  But prohibiting any snapshot acquisitions
> when there's a single LW_EXCLUSIVE ProcArrayLock waiter, can reduce
> throughput dramatically.

Thanks, I understood that you chose total throughput over stable response time. 
 I feel empathetic with the decision, and I think it's the way to go.

OTOH, maybe I'll object if I'm the pitiful waiter... I'll get out of the 
Disneyland if their staff said "Please stay in the line as long as there are 
efficient guests behind you.  That's the benefit for the whole Disneyland."

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] foreign table batch inserts

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer
On 19 May 2016 at 01:39, Michael Paquier  wrote:
On Wed, May 18, 2016 at 12:27 PM, Craig Ringer  wrote:
> On 18 May 2016 at 06:08, Michael Paquier  wrote:
>> > Wouldn’t it make sense to do the insert batch wise e.g. 100 rows ?
>>
>> Using a single query string with multiple values, perhaps, but after
>> that comes into consideration query string limit particularly for
>> large text values... The query used for the insertion is a prepared
>> statement since writable queries are supported in 9.3, which makes the
>> code quite simple actually.
>
> This should be done how PgJDBC does batches. It'd require a libpq
> enhancement, but it's one we IMO need anyway: allow pipelined query
> execution from libpq.

That's also something that would be useful for the ODBC driver. Since
it is using libpq as a hard dependency and does not speak the protocol
directly, it is doing additional round trips to the server for this
exact reason when preparing a statement.


Yes, I want FE-BE protocol-level batch inserts/updates/deletes, too.  I was 
just about to start thinking of how to implement it because of recent user 
question in pgsql-odbc.  The OP uses Microsoft SQL Server Integration Service 
(SSIS) to migrate data to PostgreSQL.  He asked for a method to speed up 
multi-row inserts, because the ODBC's multi-row insert API takes as long a time 
as when performing single-row inserts separately.  This may prevent the 
migration to PostgreSQL.

And it's also useful for ECPG.  Our customer wanted ECPG to support multi-row 
insert to migrate to PostgreSQL, because their embedded-SQL apps use the 
feature with a commercial database.

If you challenge this feature, I can help you by reviewing and testing, 
implementing the ODBC and ECPG sides, etc.

Regards
Takayuki Tsunakawa

[HACKERS] Is the unfair lwlock behavior intended?

[Tsunakawa, Takayuki]

Hello,

I encountered a strange behavior of lightweight lock in PostgreSQL 9.2.  That 
appears to apply to 9.6, too, as far as I examine the code.  Could you tell me 
if the behavior is intended or needs fix?

Simply put, the unfair behavior is that waiters for exclusive mode are 
overtaken by share-mode lockers who arrive later.


PROBLEM


Under a heavy read/write workload on a big machine with dozens of CPUs and 
hundreds of GBs of RAM, psql sometimes took more than 30 seconds to connect to 
the database (and actually, it failed to connect due to our connect_timeout 
setting.)  The backend corresponding to the psql was waiting to acquire 
exclusive mode lock on ProcArrayLock.  Some other backends took more than 10 
seconds to commit their transactions, waiting for exclusive mode lock on 
ProcArrayLock.

At that time, many backend processes (I forgot the number) were acquiring and 
releasing share mode lock on ProcArrayLock, most of which were from 
TransactionIsInProgress().


CAUSE


Going into the 9.2 code, I realized that those who request share mode don't pay 
attention to the wait queue.  That is, if some processes hold share mode lock 
and someone is waiting for exclusive mode in the wait queue, other processes 
who come later can get share mode overtaking those who are already waiting.  If 
many processes repeatedly request share mode, the waiters can't get exclusive 
mode for a long time.

Is this intentional, or should we make the later share-lockers if someone is in 
the wait queue?

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wait events monitoring future development

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> Lets put this in perspective: there's tons of companies that spend thousands
> of dollars per month extra by running un-tuned systems in cloud environments.
> I almost called that "waste" but in reality it should be a simple business
> question: is it worth more to the company to spend resources on reducing
> the AWS bill or rolling out new features?
> It's something that can be estimated and a rational business decision made.
> 
> Where things become completely *irrational* is when a developer reads
> something like "plpgsql blocks with an EXCEPTION handler are more expensive"
> and they freak out and spend a bunch of time trying to avoid them, without
> even the faintest idea of what that overhead actually is.
> More important, they haven't the faintest idea of what that overhead costs
> the company, vs what it costs the company for them to spend an extra hour
> trying to avoid the EXCEPTION (and probably introducing code that's far
> more bug-prone in the process).
> 
> So in reality, the only people likely to notice even something as large
> as a 10% hit are those that were already close to maxing out their hardware
> anyway.
> 
> The downside to leaving stuff like this off by default is users won't
> remember it's there when they need it. At best, that means they spend more
> time debugging something than they need to. At worse, it means they suffer
> a production outage for longer than they need to, and that can easily exceed
> many months/years worth of the extra cost from the monitoring overhead.

I'd rather like this way of positive thinking.  It will be better to think of 
the event monitoring as a positive feature for (daily) proactive improvement, 
not only as a debugging feature which gives negative image.  For example, 
pgAdmin4 can display 10 most time-consuming events and their solutions.  The 
DBA initially places the database and WAL on the same volume.  As the system 
grows and the write workload increases, the DBA can get a suggestion from 
pgAdmin4 that he can prepare for the system growth by placing WAL on another 
volume to reduce WALWriteLock wait events.  This is not debugging, but 
proactive monitoring.


> > As another idea, we can stand on the middle ground.  Interestingly, MySQL
> also enables their event monitoring (Performance Schema) by default, but
> not all events are collected.  I guess highly encountered events are not
> collected by default to minimize the overhead.
> 
> That's what we currently do with several track_* and log_*_stats GUCs,
> several of which I forgot even existed until just now. Since there's question
> over the actual overhead maybe that's a prudent approach for now, but I
> think we should be striving to enable these things ASAP.

Agreed.  And as Bruce said, it may be better to be able to disable collection 
of some events that have visible impact on performance.

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] [RFC] Change the default of update_process_title to off

[Tsunakawa, Takayuki]

Hello,

I'd like to propose changing the default value of update_process_title to off, 
at least on Windows.  I'll submit a patch if we see no big problem.


PROBLEM


Our customer is trying to certify PostgreSQL with their packaged software 
product.  Currently, the product supports a famous DBMS (let me call it DBMS-X 
hereafter).  They evaluated the performance of PostgreSQL and DBMS-X.

The performance of PostgreSQL was very bad on Windows.  The result was as 
follows (the unit is some request throughput).  These were measured on the same 
machine.

* DBMS-X on Windows: 750
* PostgreSQL on Windows: 250
* PostgreSQL on Linux: 870

The performance on Windows was considered unacceptable.  Using pgbench, we 
could see similar result -- the performance on Linux is about three times 
higher than on Windows.


CAUSE


The CreateEvent() and CloseHandle() Win32 API calls from postgres.exe was 
consuming much CPU time.  While stressing the system by running the select-only 
mode of pgbench, Windows performance monitor showed 50% User Time, 40% 
Privileged Time, and 10% Idle Time.  Windows Performance Toolkit, which 
corresponds to perf on Linux, revealed that half of the privileged time was 
used by CreateEvent() and CloseHandle() called from set_ps_display().  Those 
calls are performed when update_process_title is on.

With update_process_title off, the performance became much closer to Linux as 
follows.  The scaling factoris 300.  The pgbench client was run on a different 
Windows machine with 12 CPU cores.  The effect was minimal on Linux.

C:\> pgbench -h  -T 30 -c #clients -j 12 -S benchdb

[Windows]
#clients  onoff
12 29793  38169
24 31587  87237
48 32588  83335
96 34261  67668


[Linux]
#clients  onoff
12 52823  52976
24 90712  91955
48 108653  108762
96 107167  107140


PROPOSAL AND CONSIDERATIONS


I think we should change the default of update_process_title to off on Windows 
because:

1. The performance gain is huge.
2. It's almost useless because we can only see the postgres command line with 
Process Explorer, which the user must download from Microsoft and install.
3. I don't see the benefit of update_process_title=on at the expense of 
performance.
4. The default setting of PostgreSQL parameters should be friendly.  I'm afraid 
many users cannot track the cause of poor performance to update_process_title.  
I heard that MySQL's popularity was partly because it ran smoothly on Windows 
in the early days.  PostgreSQL should be, too.

The question is, do we want to change the default to off on other OSes?  Is the 
command line really useful?  If useful, does it need to be on by default?

Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC] Change the default of update_process_title to off

[Tsunakawa, Takayuki]

> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Yeah, I think I agree.  It would be bad to disable it by default on Unix,
> because ps(1) is a very standard tool there, but the same argument doesn't
> hold for Windows.

It seems that we could reach a consensus.  The patch is attached.  I'll add 
this to the next CommitFest.

> Another route to a solution would be to find a cheaper way to update the
> process title on Windows ... has anyone looked for alternatives?

I couldn't find an alternative solution after asking some Windows support staff.

Regards
Takayuki Tsunakawa




update_process_title_off_on_win.patch
Description: update_process_title_off_on_win.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wait events monitoring future development

[Tsunakawa, Takayuki]

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 those features enabled in the production databases when
> I was called.
> 
> However, now I have another opinion. When we release the next major release
> saying 10.0 with the wait monitoring, many people will start their benchmark
> test with a configuration with *the default values*, and if they see some
> performance decrease, for example around 10%, they will be talking about
> it as the performance decrease in PostgreSQL 10.0. It means PostgreSQL will
> be facing difficult reputation.
> 
> So, I agree with the features should be disabled by default for a while.

I understand your feeling well.  This is a difficult decision.  Let's hope for 
trivial overhead.

Regards
Takayuki Tsunakawa
 



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wait events monitoring future development

[Tsunakawa, Takayuki]

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 argument above doesn't seem to address the fact
> it is a small percentage.
> 
> In fact, the argument above goes even farther, saying that we should enable
> it all the time because people will be unwilling to enable it on their own.
> I have to question the value of the information if users are not willing
> to enable it.  And the solution proposed is to force the 10% default overhead
> on everyone, whether they are currently doing debugging, whether they will
> ever do this level of debugging, because people will be too scared to enable
> it.  (Yes, I think Oracle took this
> approach.)
> 
> We can talk about this feature all we want, but if we are not willing to
> be realistic in how much performance penalty the _average_ user is willing
> to lose to have this monitoring, I fear we will make little progress on
> this feature.

OK, 10% was an overstatement.  Anyway, As Amit said, we can discuss the default 
value based on the performance evaluation before release.

As another idea, we can stand on the middle ground.  Interestingly, MySQL also 
enables their event monitoring (Performance Schema) by default, but not all 
events are collected.  I guess highly encountered events are not collected by 
default to minimize the overhead.

http://dev.mysql.com/doc/refman/5.7/en/performance-schema-quick-start.html
--
Assuming that the Performance Schema is available, it is enabled by default.
...
[mysqld]
performance_schema=ON
...
Initially, not all instruments and consumers are enabled, so the performance 
schema does not collect all events. To turn all of these on and enable event 
timing, execute two statements (the row counts may differ depending on MySQL 
version): 
mysql> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
Query OK, 560 rows affected (0.04 sec)
mysql> UPDATE setup_consumers SET ENABLED = 'YES';
Query OK, 10 rows affected (0.00 sec)
--


BTW, I remember EnterpriseDB has a wait event monitoring feature.  Is it 
disabled by default?  What was the overhead?

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wait events monitoring future development

[Tsunakawa, Takayuki]

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:

https://wiki.postgresql.org/wiki/PgCon_2016_Developer_Unconference/Wait_events_monitoring

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 you 
disable it by default because of overhead, how can we convince users to enable 
it in production systems to solve some performance problem?  I’m afraid severe 
users would say “we can’t change any setting that might cause more trouble, so 
investigate the cause with existing information.”

We should positively consider the performance with wait event monitoring on as 
the new normal.  Then, we should develop more features that leverage the wait 
event data, so that wait event data is crucial.  The manual explains to users 
that wait event monitoring can be turned off for maximal performance but it’s 
not recommended.

BTW, taking advantage of this chance, why don’t we enrich the content of 
performance tuning in the manual?  At least it needs to be explained how to 
analyze the wait event data and tune the system.

Performance Tips
https://www.postgresql.org/docs/devel/static/performance-tips.html

Regards
Takayuki Tsunakawa

Re: [HACKERS] [RFC] Change the default of update_process_title to off

[Tsunakawa, Takayuki]

From: David Rowley [mailto:david.row...@2ndquadrant.com]
> But perhaps it's better written like:
> 
> + This value defaults to "off" on Windows platforms due to the
> platform's significant overhead for updating the process title.

Thank you, I copied this.  But I changed "off" to off because other places in 
config.sgml don't enclose on/off with quotes.


> From: Robert Haas [mailto:robertmh...@gmail.com]
> On Fri, Aug 5, 2016 at 12:19 PM, Jeff Janes  wrote:
> > Shouldn't we change the code which initdb uses to create the example
> > postgresql.conf, so that it shows the commented out value as being
> > 'off', when initdb is run on Windows?
> 
> +1.

Good idea.  Done.

Regards
Takayuki Tsunakawa



update_process_title_off_on_win_v2.patch
Description: update_process_title_off_on_win_v2.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Is a UDF binary portable across different minor releases and PostgreSQL distributions?

[Tsunakawa, Takayuki]

Hello,

While I was thinking of application binary compatibility between PostgreSQL 
releases, some questions arose about C language user-defined functions (UDFs) 
and extensions that depend on them.

[Q1]
Can the same UDF binary be used with different PostgreSQL minor releases?  If 
it is, is it a defined policy (e.g. written somewhere in the manual, wiki, 
documentation in the source code)?

For example, suppose you build a UDF X (some_extension.so/dll) with PostgreSQL 
9.5.0.  Can I use the binary with PostgreSQL 9.5.x without rebuilding?

Here, the UDF references the contents of server-side data structures, like 
pgstattuple accesses the members of HeapScanData.  If some bug fix of 
PostgreSQL changes the member layout of those structures, the UDF binary would 
possibly misbehave.  Basically, should all UDFs be rebuilt with the new minor 
release?  Or, are PostgreSQL developers aware of such incompatibility and 
careful not to change data structure layout?


[Q2]
Can the same UDF binary be used with different PostgreSQL distributions 
(EnterpriseDB, OpenSCG, RHEL packages, etc.)?  Or should the UDF be built with 
the target distribution?

I guess the rebuild is necessary if the distribution modified the source code 
of PostgreSQL.  That is, the UDF binary built with the bare PostgreSQL cannot 
be used with EnterpriseDB's advanced edition, which may modify various data 
structures.

How about other distributions which probably don't modify the source code?  
Should the UDF be built with the target PostgreSQL because configure options 
may differ, which affects data structures?


Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Is a UDF binary portable across different minor releases and PostgreSQL distributions?

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> On Fri, Jul 1, 2016 at 9:33 AM, Tsunakawa, Takayuki
> <tsunakawa.ta...@jp.fujitsu.com> wrote:
> > [Q1]
> > Can the same UDF binary be used with different PostgreSQL minor releases?
> If it is, is it a defined policy (e.g. written somewhere in the manual,
> wiki, documentation in the source code)?
> >
> > For example, suppose you build a UDF X (some_extension.so/dll) with
> PostgreSQL 9.5.0.  Can I use the binary with PostgreSQL 9.5.x without
> rebuilding?
> 
> Yes, that works properly. There could be problems with potential changes
> in the backend APIs in a stable branch, but this usually does not happen
> much.
> 
> > Here, the UDF references the contents of server-side data structures,
> like pgstattuple accesses the members of HeapScanData.  If some bug fix
> of PostgreSQL changes the member layout of those structures, the UDF binary
> would possibly misbehave.  Basically, should all UDFs be rebuilt with the
> new minor release?
> 
> Not necessarily.
> 
> > Or, are PostgreSQL developers aware of such incompatibility and careful
> not to change data structure layout?
> 
> Committers are aware and careful about that, that's why exposed APIs and
> structures are normally kept stable. At least that's what I see.
> 
> > [Q2]
> > Can the same UDF binary be used with different PostgreSQL distributions
> (EnterpriseDB, OpenSCG, RHEL packages, etc.)?  Or should the UDF be built
> with the target distribution?
> 
> Each distribution has usually its own compilation options (say page size,
> etc.) even if I recall that most of them use the defaults, so it clearly
> depends on what kind of things each of them uses. I would recommend a
> recompilation just to be safe. It may not be worth spending time at looking
> and checking each one's differences.

Thanks for sharing your experience, Michael.

I'd like to document the policy clearly in the upgrade section of PostgreSQL 
manual, eliminating any ambiguity, so that users can determine what they should 
do without fear like "may or may not work".  Which of the following policies 
should I base on?

Option 1:
Rebuild UDFs with the target PostgreSQL distribution and minor release.

Option 2:
Rebuild UDFs with the target PostgreSQL distribution.
You do not have to rebuild UDFs when you upgrade or downgrade the minor 
release.  (If your UDF doesn't work after changing the minor release, it's the 
bug of PostgreSQL.  You can report it to pgsql-bugs.)


Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Is a UDF binary portable across different minor releases and PostgreSQL distributions?

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> So perhaps the best answer, is not 1 nor 2. Just saying that the routines
> are carefully maintained with a best effort, though sometimes you may need
> to rebuild depending on unavoidable changes in routine signatures that had
> to be introduced.

Good, I'd like to use that "mild" expression in the manual.  Although the 
expression is mild, the reality for users is not, is it?
Because the UDF developers and users cannot easily or correctly determine if 
rebuilding is necessary, nervous (enterprise) users will rebuild their UDFs 
with each minor release for the maximum safety as Michael does.

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Is a UDF binary portable across different minor releases and PostgreSQL distributions?

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> On Fri, Jul 1, 2016 at 10:35 AM, Tsunakawa, Takayuki
> <tsunakawa.ta...@jp.fujitsu.com> wrote:
> > I'd like to document the policy clearly in the upgrade section of
> PostgreSQL manual, eliminating any ambiguity, so that users can determine
> what they should do without fear like "may or may not work".  Which of the
> following policies should I base on?
> >
> > Option 1:
> > Rebuild UDFs with the target PostgreSQL distribution and minor release.
> >
> > Option 2:
> > Rebuild UDFs with the target PostgreSQL distribution.
> > You do not have to rebuild UDFs when you upgrade or downgrade the
> > minor release.  (If your UDF doesn't work after changing the minor
> > release, it's the bug of PostgreSQL.  You can report it to
> > pgsql-bugs.)
> 
> That would not be a bug of PostgreSQL, the terms are incorrect. If there
> is an API breakage, the extension needs to keep up in this case, so it would
> be better to mention asking on the lists what may have gone wrong.

OK, I understood that your choice is option 2.  And the UDF developer should 
report the problem and ask for its reason on pgsql-bugs, possibly end up 
haveing to rebuild the UDF.  But if so, it sounds like option 1.  That is, "For 
safety, rebuild your UDF with each minor release.  That way, you can avoid 
severe problems that might take time to pop up above water."  I wonder if this 
is similar to the Linux's loadable kernel modules.

I'd like to hear opinions from other decision makers here before proceeding, as 
well as Michael.


Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Is a UDF binary portable across different minor releases and PostgreSQL distributions?

[Tsunakawa, Takayuki]

> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> "Tsunakawa, Takayuki" <tsunakawa.ta...@jp.fujitsu.com> writes:
> > Option 2:
> > Rebuild UDFs with the target PostgreSQL distribution.
> > You do not have to rebuild UDFs when you upgrade or downgrade the
> > minor release.  (If your UDF doesn't work after changing the minor
> > release, it's the bug of PostgreSQL.  You can report it to
> > pgsql-bugs.)
> 
> I do not like either of those.  We try hard not to break extensions in minor
> releases, but I'm not willing to state it as a hard-and-fast policy that
> we never will --- especially because there's no bright line as to which
> internal APIs extensions can rely on or not.  With sufficiently negative
> assumptions about what third-party authors might have chosen to do, it could
> become impossible to fix anything at all in released branches.

I feel empathy, but I think something needs to be documented for users to 
upgrade and/or change distributions with relief.  In practice, though it may be 
a shame, isn't option 1 the current answer?

Again, the current situation seems similar to the Linux loadable kernel 
modules.  So PostgreSQL is not alone.  See "Binary compatibility" section in:

https://en.wikipedia.org/wiki/Loadable_kernel_module


> In practice, extensions seldom need to be modified for new minor releases.
> But there's a long way between that statement and a promise that it won't
> ever happen for any conceivable extension.

I think so, too.

> To make this situation better, what we'd really need is a bunch of work
> to identify and document the specific APIs that we would promise won't change
> within a release branch.  That idea has been batted around before, but
> nobody's stepped up to do all the tedious (and, no doubt, contentious) work
> that would be involved.

I can't yet imagine if such API (including data structures) can really be 
defined so that UDF developers feel comfortable with its flexibility.  I wonder 
how other OSes provide such API and ABI.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Is a UDF binary portable across different minor releases and PostgreSQL distributions?

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer
There's no formal extension API. So there's no boundary between "internal stuff 
we might have to change to fix a problem" and "things extensions can rely on 
not changing under them". In theory anything that changed behaviour or changed 
a header file in almost any way could break an extension.

There's no deliberate breakage and some awareness of possible consequences to 
extensions, but no formal process. I would prefer that the manual explicitly 
recommend recompiling extensions against each minor update (or updating them 
along with the packages), and advise that packagers make their extensions 
depend on an = minor version in their package specifications, not a >= .


Yes, I think such recommendation in the manual is the best.


However, in practice it's fine almost all the time.

Maybe most extensions don’t use sensitive parts of the server…


I think making this more formal would require, as Tom noted, a formal extension 
API we can promise to maintain, likely incorporating:
- ... endlessly more

Endless (^^;)

The main thing is that it's a great deal of work for limited benefit. I don't 
know about you, but I'm not keen.

I’m not keen, either… I don’t think I can form the API that advanced extension 
developers will be satisfied with.  I’ll just document the compabibility 
article in the upgrade section.

Regards
Takayuki Tsunakawa

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Peter Geoghegan
> I think that the best thing about C++ is the ability to encapsulate and
> simplify some aspects of resource management quite well, which necessitates
> reimplementing PG_TRY/CATCH. The worst thing about C++ is that ABI
> compatibility is far messier. This makes a C++ port seem less compelling
> to me than the idea first appears.

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Christopher
> Further, it's not as if C++ is particularly newer than C.  C is about 45
> years old; C++, at 33, hardly seems like a "spry young whippersnapper"
> whose inclusion ought to lead to vast excitement.
>
> The would-be "spry young things" that head to my mind are Rust and Go.  I'm
> not sure it's terribly plausible to have parts of Postgres written in both
> C and (Rust|Go); they're different enough that I'm not sure what
> functionality would mix sensibly.  But I think that would be more
> interesting, all the same.  Perhaps it would work out well to be able to
> create background workers in Rust, or to implement a stored procedure
> language in Go.


First, I'm neither for nor against rewriting PostgreSQL in C++.  But I wonder 
whether it would really pay for the cost.  I'm worried about these, for example:

* Wouldn't it increase the coding and testing burdon?  Coding and testing in C, 
and coding and testing in C++.  PostgreSQL seem to have many features to 
develop, and I'm not sure C++ will help to speed up the development of them.

* Would it really attract more developers of PostgreSQL itself, not extensions? 
 FYI, Tiobe Index says C is nearly twice as popular as C++.

http://www.tiobe.com/tiobe-index/

* Wouldn't it distance some developers if they don't want to learn C++?  As 
Christopher said, C++ is old and there are many newer languages that attract 
developers -- C#, Swift, Go, Java, JavaScript, etc.  I wonder whether recent 
developers want to spend time in learning complex C++ now.  I learned C++ 
because it is still the most popular language in game development, but maybe I 
would not want to learn C++ anymore if I didn't know C++.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] [RFC] Should "SHOW huge_pages" display the effective value "off" when the huge page is unavailable?

[Tsunakawa, Takayuki]

Hello, all

Could you give me your opinions on whether the SHOW command should display the 
effective value or the specified value for huge_pages?  During the review of 
"Supporting huge_pages on Windows", which is now shifted to CommitFest 2017-3, 
Magnus gave me a comment that the huge_page variable should retain the value 
"try" when the huge page is not available on the machine and the server falls 
back to huge_page=off.  The Linux version does so.

I don't have a strong opinion on that, but I think a bit that it would be 
better to reflect the effective setting, i.e. SHOW displays huge_pages as off, 
not try.  Otherwise, the user cannot know whether the huge page setting is 
effective.

One parameter that behaves similarly is wal_buffers.  When wal_buffers is set 
to -1 (default), "SHOW wal_buffers" displays the actual size, not -1.  But I 
didn't find any other parameters like this.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC] Should I embed or parameterize syscall/Win32 function names from error messages?

[Tsunakawa, Takayuki]

From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> TBH, I think you are worried about the wrong thing here.  You could drop
> both of those errdetail calls altogether and be little worse off.  In the
> places where we have errdetail calls like "failed system call was xxx",
> the main point is to show the exact parameters that were given to the system
> call, and neither of these do that.  These errdetail messages would only
> be useful if the identical ereport errmsg might be issued for failures from
> different underlying Windows calls --- but I doubt that's what you're
> intending here.

Yes, that's what I'm intending to do.  To enable the user right "Lock pages in 
memory" on Windows, a few Win32 functions need to be called in turn.


> My problem with these messages is I am not sure what "memory user right"
> means.  Probably that just needs a bit of editing.  But I'd go for something
> like "could not do xxx: error code %lu", and not bother mentioning the system
> call name, unless failing to do so has some impact on whether we could
> understand what happened from a field report of this error message.

For the user, each step of enabling the user right is irrelevant.  It just 
matters to him that that the server could not enable the user right.  OTOH, the 
failed Win32 function may help us to talk with Microsoft to troubleshoot the 
problem.  So I used the same messages in those ereport() calls except for the 
function name to eliminate the translation work.


> (See the "Function Names" item in our message style guidelines for more
> about this issue.  Maybe we need to expand that item some more.)

The style guide does not necessarily require the function parameter values.

https://www.postgresql.org/docs/devel/static/error-style-guide.html

[Quote]
If it really seems necessary, mention the system call in the detail message. 
(In some cases, providing the actual values passed to the system call might be 
appropriate information for the detail message.)

postmaster.c doesn't display parameter values, too.

elog(LOG, "CreateProcess call failed: %m (error code %lu)",
 GetLastError());

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] [RFC] Should I embed or parameterize syscall/Win32 function names from error messages?

[Tsunakawa, Takayuki]

Hello, all

Could you give me your opinions on the message style?  Recently, I got 
different comments from Magnus and Alvaro during the review of "Supporting 
huge_pages on Windows", which is now shifted to CommitFest 2017-3.  To be more 
specific, I'm modifying src/backend/port/win32_shmem.c 
b/src/backend/port/win32_shmem.c.  This file has existing messages like this:

[Existing message]
ereport(FATAL,
(errmsg("could not create shared memory segment: error code %lu", 
GetLastError()),
errdetail("Failed system call was CreateFileMapping(size=%zu, 
name=%s).",
size, szShareMem)));


I added a few ereport() calls that emit the same message except for the Win32 
API name.  Which of the following do you think is the best?  I'd like to follow 
the majority.

[Option 1]
ereport(elevel,
(errmsg("could not enable Lock pages in memory user right"),
errdetail("Failed system call was %s, error code %lu", 
"OpenProcessToken", GetLastError(;

[Option 2]
ereport(elevel,
(errmsg("could not enable Lock Pages in Memory user right: error code 
%lu", GetLastError()),
errdetail("Failed system call was OpenProcessToken.")));

Alvaro thinks that Option 1 is better because it eliminates redundant 
translation work.  Magnus says Option 2 is better because it matches the style 
of existing messages in the same file.

[Magnus's comment]
this seems to be a new pattern of code -- for other similar cases it 
just writes LookupPrivilegeValue inside the patch itself. I'm guessing 
the idea was for translatability, but I think it's better we stick to 
the existing pattern.

[Alvaro's comment]
There are two reasons for doing things this way.  One is that you reduce the 
chances of a translator making a mistake with the function name (say just a 
typo, or in egregious cases they may even translate the function name).  The 
other is that if you have many of these messages, you only translate the 
generic part once instead of having the same message a handful of times, 
exactly identical but for the function name.
So please do apply that kind of pattern wherever possible.  We already have the 
proposed error message, twice.  No need for two more occurrences of it.


I'm rather inclined to choose Option 1 to reduce message translation work.  
Actually, is the Option 3 the best so that it aligns with the existing messages 
by putting the error code in the primary message?

[Option 3]
ereport(elevel,
(errmsg("could not enable Lock pages in memory user right: error code 
%lu", GetLastError()),
errdetail("Failed system call was %s", "OpenProcessToken")));

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC] Should I embed or parameterize syscall/Win32 function names from error messages?

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> I find it hard to have an opinion on the matter as a non-translator.
> Why not asking translators directly on pgsql-translators?
> 

I didn't think of pgsql-translators.  I'll ask the same question there.  Thanks.

Anyway, this is also a matter of source code style, and those who commit the 
code live here, so I think I need to hear opinions here, too.

Regards
Takayuki Tsunakawa
 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Commit fest 2017-01 will begin soon!

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> - Cascading standby cannot catch up and get stuck emitting the same message
> repeatedly, a 9.3-only bug fix.

Thank you for your hard work as a CFM.  For a trivial note, this is for 9.2 
only, not 9.3.

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Checksums by default?

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Magnus Hagander
> Is it time to enable checksums by default, and give initdb a switch to turn
> it off instead?
> 
> I keep running into situations where people haven't enabled it, because
> (a) they didn't know about it, or (b) their packaging system ran initdb
> for them so they didn't even know they could. And of course they usually
> figure this out once the db has enough data and traffic that the only way
> to fix it is to set up something like slony/bucardo/pglogical and a whole
> new server to deal with it.. (Which is something that would also be good
> to fix -- but having the default changed would be useful as well)

+10
I was wondering why the community had decided to turn it off by default.  IIRC, 
the reason was that the performance overhead was 20-30% when the entire data 
directory was placed on the tmpfs, but it's not as important as the data 
protection by default.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Supporting huge pages on Windows

[Tsunakawa, Takayuki]

From: Amit Kapila [mailto:amit.kapil...@gmail.com]
> Hmm.  It doesn't work even on a command prompt with administrative
> privileges. It gives below error:
> 
> waiting for server to start2017-01-17 11:20:13.780 IST [4788] FATAL:
> could not create shared memory segment: error code 1450
> 2017-01-17 11:20:13.780 IST [4788] DETAIL:  Failed system call was
> CreateFileMap ping(size=148897792,
> name=Global/PostgreSQL:E:/WorkSpace/PostgreSQL/master/Data)
> .
> 2017-01-17 11:20:13.780 IST [4788] LOG:  database system is shut down
> stopped waiting
> pg_ctl: could not start server
> Examine the log output.
> 
> 
> Now, error code 1450 can occur due to insufficient system resources, so
> I have tried by increasing the size of shared memory (higher value of
> shared_buffers) without your patch and it works.  This indicates some
> problem with the patch.

Hmm, the large-page requires contiguous memory for each page, so this error 
could occur on a long-running system where the memory is heavily fragmented.  
For example, please see the following page and check the memory with RAMMap 
program referred there.

http://blog.dbi-services.com/large-pages-and-memory_target-on-windows/

BTW, is your OS or PostgreSQL 32-bit?


> >  It seems that Windows removes many privileges, including "Lock Pages
> in Memory", when starting the normal command prompt.  As its evidence, you
> can use the attached priv.c to see what privileges are assigned and and
> enabled/disabled.  Build it like "cl priv.c" and just run priv.exe on each
> command prompt.  Those runs show different privileges.
> >
> 
> This is bad.
> 
> > Should I need to do something, e.g. explain in the document that the user
> should use the command prompt with administrative privileges when he uses
> huge_pages?
> >
> 
> I think it is better to document in some way if we decide to go-ahead with
> the patch.

Sure, I added these sentences. 

+To start the database server on the command prompt as a standalone 
process,
+not as a Windows service, run the command prompt as an administrator or
+disable the User Access Control (UAC). When the UAC is enabled, the 
normal
+command prompt revokes the user right Lock Pages in Memory.

Regards
Takayuki Tsunakawa



win_large_pages_v7.patch
Description: win_large_pages_v7.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [doc fix] Really trivial fix for BRIN documentation

[Tsunakawa, Takayuki]

From: Simon Riggs [mailto:si...@2ndquadrant.com]
> Pushed, but using "heap" rather than "table", for clarity. Thanks for the
> patch.

Thank you for responding so quickly.  I'm comfortable with "heap."  On the 
other hand, src/backend/access/brin/README uses "table" as follows.  Second, I 
thought users would feel more familiar with the general term "table."  Third, I 
supposed PostgreSQL might add support for other structures for tables than heap 
in the future, like SQL Server provides heap (non-clustered table) and 
clustered tables.

At index creation time, the whole table is scanned; for each page range the
summarizing values of each indexed column and nulls bitmap are collected and
stored in the index.

I should have written the reason I chose "table."  Anyway, I'm OK with heap.

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Adding new output parameter of pg_stat_statements to identify operation of the query.

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of
> husttrip...@vip.sina.com
>  When using pg_stat_statements to collect running SQL of PG, we
> find it is hard for our program to get exact operation type of the SQL,
> such as SELECT, DELETE, UPDATE, INSERT, and so on.
>So we modify the the source code of pg_stat_statements and add another
> output parameter to tell us the operation type.
> Of course some application know their operation type, but for us and many
> public databases, doing this is hard.
> The only way is to reparse the SQL, obviously it is too expensive for a
> monitoring or diagnosis system.
> We have done the job and are willing to post a patch.
> I sent one through my work mail, but it seems that my mail didn't reach
> the maillist, so I try again by using my personal mail account.

A view for counting the number of executions per operation type is being 
developed for PostgreSQL 10, which is expected to be released this year.

https://commitfest.postgresql.org/13/790/

Would this fit your need?  If not, what's the benefit of getting the operation 
type via pg_stat_statements?

Regards
Takayuki Tsunakawa





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] [doc fix] Really trivial fix for BRIN documentation

[Tsunakawa, Takayuki]

Hello,

This is just a correction from "index" to "table".  I was a bit confused when I 
first read this part.


Regards
Takayuki Tsunakawa



brin_trivial_doc_fix.patch
Description: brin_trivial_doc_fix.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Supporting huge pages on Windows

[Tsunakawa, Takayuki]

From: Amit Kapila [mailto:amit.kapil...@gmail.com]
> > Hmm, the large-page requires contiguous memory for each page, so this
> error could occur on a long-running system where the memory is heavily
> fragmented.  For example, please see the following page and check the memory
> with RAMMap program referred there.
> >
> 
> I don't have RAMMap and it might take some time to investigate what is going
> on, but I think in such a case even if it works we should keep the default
> value of huge_pages as off on Windows.  I request somebody else having
> access to Windows m/c to test this patch and if it works then we can move
> forward.

You are right.  I modified the patch so that the code falls back to the 
non-huge page when CreateFileMapping() fails due to the shortage of large 
pages.  That's what the Linux version does.

The other change is to parameterize the Win32 function names in the messages in 
EnableLockPagePrivileges().  This is to avoid adding almost identical messages 
unnecessarily.  I followed Alvaro's comment.  I didn't touch the two existing 
sites that embed Win32 function names.  I'd like to leave it up to the 
committer to decide whether to change as well, because changing them might make 
it a bit harder to apply some bug fixes to earlier releases.

FYI, I could reproduce the same error as Amit on 32-bit Win7, where the total 
RAM is 3.5 GB and available RAM is 2 GB.  I used the attached largepage.c.  
Immediately after the system boot, I could only allocate 8 large pages.  When I 
first tried to allocate 32 large pages, the test program produced:

large page size = 2097152
allocating 32 large pages...
CreateFileMapping failed: error code = 1450

You can build the test program as follows:

cl largepage.c advapi32.lib

Regards
Takayuki Tsunakawa



#include 
#include 
#include 

static void EnableLockPagesPrivilege(void);

void main(int argc, char *argv[])
{
SIZE_T  largePageSize = 0;
HANDLE hmap;
int pages = 1;

largePageSize = GetLargePageMinimum();
printf("large page size = %u\n", largePageSize);

EnableLockPagesPrivilege();

if (argc > 1)
pages = atoi(argv[1]);
printf("allocating %d large pages...\n", pages);

hmap = CreateFileMapping(INVALID_HANDLE_VALUE, NULL,
PAGE_READWRITE | SEC_COMMIT | SEC_LARGE_PAGES,
0, largePageSize * pages,
"myshmem");
if (hmap)
printf("allocated large pages successfully\n");
else
printf("CreateFileMapping failed: error code = %u", 
GetLastError());
}

static void
EnableLockPagesPrivilege(void)
{
HANDLE hToken;
TOKEN_PRIVILEGES tp;
LUID luid;

if (!OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES | 
TOKEN_QUERY, ))
{
printf("OpenProcessToken failed: error code = %u", 
GetLastError());
exit(1);
}

if (!LookupPrivilegeValue(NULL, SE_LOCK_MEMORY_NAME, ))
{
printf("LookupPrivilegeValue failed: error code = %u", 
GetLastError());
exit(1);
}
tp.PrivilegeCount = 1;
tp.Privileges[0].Luid = luid;
tp.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;

if (!AdjustTokenPrivileges(hToken, FALSE, , 0, NULL, NULL))
{
printf("AdjustTokenPrivileges failed: error code = %u", 
GetLastError());
exit(1);
}

if (GetLastError() != ERROR_SUCCESS)
{
if (GetLastError() == ERROR_NOT_ALL_ASSIGNED)
printf("could not enable Lock Pages in Memory user 
right");
else
printf("AdjustTokenPrivileges failed: error code = %u", 
GetLastError());
exit(1);
}

CloseHandle(hToken);
}


win_large_pages_v8.patch
Description: win_large_pages_v8.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Statement-level rollback

[Tsunakawa, Takayuki]

Hello,

As I stated here and at the PGConf.ASIA developer meeting last year, I'd like 
to propose statement-level rollback feature.  To repeat myself, this is 
requested for users to migrate from other DBMSs to PostgreSQL.  They expect 
that a failure of one SQL statement should not abort the entire transaction and 
their apps (client programs and stored procedures) can continue the transaction 
with a different SQL statement.


SPECIFICATION
==

START TRANSACTION ROLLBACK SCOPE { TRANSACTION | STATEMENT };

This syntax controls the behavior of the transaction when an SQL statement 
fails.  TRANSACTION (default) is the traditional behavior (i.e. rolls back the 
entire transaction or subtransaction).  STATEMENT rolls back the failed SQL 
statement.

Just like the isolation level and access mode, 
default_transaction_rollback_scope GUC variable is also available.


DESIGN
==

Nothing much to talk about... it merely creates a savepoint before each 
statement execution and destroys it after the statement finishes.  This is done 
in postgres.c for top-level SQL statements.

The stored function hasn't been handled yet; I'll submit the revised patch soon.


CONSIDERATIONS AND REQUESTS
==

The code for stored functions is not written yet, but I'd like your feedback 
for the specification and design based on the current patch.  I'll add this 
patch to CommitFest 2017-3.

The patch creates and destroys a savepoint for each message of the extended 
query protocol (Parse, Bind, Execute and Describe).  I'm afraid this will add 
significant overhead, but I don't find a better way, because those messages 
could be send arbitrarily for different statements, e.g. Parse stmt1, Parse 
stmt2, Bind stmt1, Execute stmt1, Bind stmt2, Execute stmt2.


Regards
Takayuki Tsunakawa



stmt_rollback.patch
Description: stmt_rollback.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_stat_lwlock wait time view

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Haribabu Kommi
> calculations may cause performance problem. Is there any need of writing
> this stats information to file? As this just provides the wait time
> information.

Yes, saving the workload diagnosis information would be nice like Oracle AWR.  
I mean not the current accumulated total, but a series of snapshots taken 
periodically.  It would enable:

* Daily monitoring across database restarts.  e.g. The response times of 
applications degraded after applying a patch.  What's the difference between 
before and after the patch application?

* Hint on troubleshooting a crash failure.  e.g. Excessive memory use by 
PostgreSQL crashed the OS.  What was the workload like just before the crash?

The point of discussion may be whether PostgreSQL itself provides the feature 
to accumulate performance diagnosis information on persistent storage.  
pg_statsinfo will be able to take on it, but it wouldn't be convenient nor 
efficient.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] increasing the default WAL segment size

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas
> Considering those three factors, I think we should consider pushing the
> default value up somewhat higher for v10.  Reverting to the 64MB size that
> we had prior to 47937403676d913c0e740eec6b85113865c6c8ab
> sounds pretty reasonable.

+1
The other downside is that the response time of transactions may degrade when 
they have to wait for a new WAL segment to be created.  Tha might pop up as 
occasional slow or higher maximum response time, which is a mystery to users.  
Maybe it's time to use posix_fallocate() to create WAL segments.


> Possibly it would make sense for this to be configurable at initdb time
> instead of requiring a recompile; we probably don't save any significant
> number of cycles by compiling this into the server.

+1

> 3. archive_timeout is no longer a frequently used option.  Obviously, if
> you are frequently archiving partial segments, you don't want the segment
> size to be too large, because if it is, each forced segment switch
> potentially wastes a large amount of space (and bandwidth).
> But given streaming replication and pg_receivexlog, the use case for
> archiving partial segments is, at least according to my understanding, a
> lot narrower than it used to be.  So, I think we don't have to worry as
> much about keeping forced segment switches cheap as we did during the 8.x
> series.

I'm not sure about this.  I know (many or not) users use continuous archiving 
with archive_command and archive_timeout for backups, and don't want to use 
streaming replication, because the system is not worth the cost and trouble of 
HA.  I heard from a few users that they were surprised when they knew that 
PostgreSQL generates WAL even when no update transaction is happening.  Is this 
still true?

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Supporting SJIS as a database encoding

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Heikki
> But one thing that would help a little, would be to optimize the UTF-8
> -> SJIS conversion. It uses a very generic routine, with a binary search
> over a large array of mappings. I bet you could do better than that, maybe
> using a hash table or a radix tree instead of the large binary-searched
> array.

That sounds worth pursuing.  Thanks!


Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Supporting SJIS as a database encoding

[Tsunakawa, Takayuki]

From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> "Tsunakawa, Takayuki" <tsunakawa.ta...@jp.fujitsu.com> writes:
> > Before digging into the problem, could you share your impression on
> > whether PostgreSQL can support SJIS?  Would it be hopeless?
> 
> I think it's pretty much hopeless.  Even if we were willing to make every
> bit of code that looks for '\' and other specific at-risk characters
> multi-byte aware (with attendant speed penalties), we could expect that
> third-party extensions would still contain vulnerable code.  More, we could
> expect that new bugs of the same ilk would get introduced all the time.
> Many such bugs would amount to security problems.  So the amount of effort
> and vigilance required seems out of proportion to the benefits.

Hmm, this sounds like a death sentence.  But as I don't have good knowledge of 
character set handling yet, I'm not completely convinced about why PostgreSQL 
cannot support SJIS.  I wonder why and how other DBMSs support SJIS and what's 
the difference of the implementation.  Using multibyte-functions like mb... to 
process characters would solve the problem?  Isn't the current implementation 
blocking the support of other character sets that have similar characteristics? 
 I'll learn the character set handling...

> Most of the recent discussion about allowed backend encodings has run more
> in the other direction, ie, "why don't we disallow everything but
> UTF8 and get rid of all the infrastructure for multiple backend encodings?".
> I'm not personally in favor of that, but there are very few hackers who
> want to add any more overhead in this area.

Personally, I totally agree.  I want non-Unicode character sets to disappear 
from the world.  But the real business doesn't seem to forgive the lack of 
SJIS...

Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Supporting SJIS as a database encoding

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Kyotaro
> HORIGUCHI
Implementing radix tree code, then redefining the format of mapping table
> to suppot radix tree, then modifying mapping generator script are needed.
> 
> If no one oppse to this, I'll do that.

+100
Great analysis and your guts.  I very much appreciate your trial!

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Supporting SJIS as a database encoding

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii
> > But what I'm wondering is why PostgreSQL doesn't support SJIS.  Was there
> any technical difficulty?  Is there anything you are worried about if adding
> SJIS?
> 
> Yes, there's a technical difficulty with backend code. In many places it
> is assumed that any string is "ASCII compatible", which means no ASCII
> character is used as a part of multi byte string. Here is such a random
> example from src/backend/util/adt/varlena.c:
> 
>   /* Else, it's the traditional escaped style */
>   for (bc = 0, tp = inputText; *tp != '\0'; bc++)
>   {
>   if (tp[0] != '\\')
>   tp++;
> 
> Sometimes SJIS uses '\' as the second byte of it.

Thanks, I'll try to understand the seriousness of the problem as I don't have 
good knowledge of character sets.  But your example seems to be telling 
everything about the difficulty...

Before digging into the problem, could you share your impression on whether 
PostgreSQL can support SJIS?  Would it be hopeless?  Can't we find any 
direction to go?  Can I find relevant source code by searching specific words 
like "ASCII", "HIGH_BIT", "\\" etc?

Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Supporting SJIS as a database encoding

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Kyotaro
> HORIGUCHI
> 
> $ time psql postgres -c 'select t.a from t, generate_series(0, )' >
> /dev/null
> 
> real  0m22.696s
> user  0m16.991s
> sys   0m0.182s>
> 
> Using binsearch the result for the same operation was
> real  0m35.296s
> user  0m17.166s
> sys   0m0.216s
> 
> Returning in UTF-8 bloats the result string by about 1.5 times so it doesn't
> seem to make sense comparing with it. But it takes real = 47.35s.

Cool, 36% speedup!  Does this difference vary depending on the actual 
characters used, e.g. the speedup would be greater if most of the characters 
are ASCII?

Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tsunakawa,
> Our customer hit a problem of cascading replication, and we found the cause.
> They are using the latest PostgreSQL 9.2.18.  The bug seems to have been
> fixed in 9.4 and higher during the big modification of xlog.c, but it's
> not reflected in older releases.
> 
> The attached patch is for 9.2.18.  This just borrows the idea from 9.4 and
> higher.
> 
> But we haven't been able to reproduce the problem.  Could you review the
> patch and help to test it?  I would very much appreciate it if you could
> figure out how to reproduce the problem easily.

We could successfully reproduce the problem and confirm that the patch fixes 
it.  Please use the attached script to reproduce the problem.  Place it in an 
empty directory and just run "./test.sh" with no argument.  It creates three 
database clusters (primary, standby, and cascading standby) in the current 
directory.

Could you review the patch and commit it for the next release?  If you think I 
should register the patch with the CommitFest even if the problem occurs in 9.2 
and 9.3, please say so.  I'll do so if there's no comment.

Regards
Takayuki Tsunakawa




test.sh
Description: test.sh

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Supporting SJIS as a database encoding

[Tsunakawa, Takayuki]

Hello,

I'd like to propose adding SJIS as a database encoding.  You may wonder why 
SJIS is still necessary in the world of Unicode.  The purpose is to achieve 
comparable performance when migrating legacy database systems from other DBMSs 
without little modification of applications.

Recently, we failed to migrate some customer's legacy database from DBMS-X to 
PostgreSQL.  That customer wished for PostgreSQL, but PostgreSQL couldn't meet 
the performance requirement.

The system uses DBMS-X with the database character set being SJIS.  The main 
applications are written in embedded SQL, which require SJIS in their host 
variables.  They insisted they cannot use UTF8 for the host variables because 
that would require large modification of applications due to character 
handling.  So no character set conversion is necessary between the clients and 
the server.

On the other hand, PostgreSQL doesn't support SJIS as a database encoding.  
Therefore, character set conversion from UTF-8 to SJIS has to be performed.  
The batch application runs millions of SELECTS each of which retrieves more 
than 100 columns.  And many of those columns are of character type.

If PostgreSQL supports SJIS, PostgreSQL will match or outperform the 
performance of DBMS-X with regard to the applications.  We confirmed it by 
using psql to run a subset of the batch processing.  When the client encoding 
is SJIS, one FETCH of 10,000 rows took about 500ms.  When the client encoding 
is UTF8 (the same as the database encoding), the same FETCH took 270ms.

Supporting SJIS may somewhat regain attention to PostgreSQL here in Japan, in 
the context of database migration.  BTW, MySQL supports SJIS as a database 
encoding.  PostgreSQL used to be the most popular open source database in 
Japan, but MySQL is now more popular.


But what I'm wondering is why PostgreSQL doesn't support SJIS.  Was there any 
technical difficulty?  Is there anything you are worried about if adding SJIS?

I'd like to write a patch for adding SJIS if there's no strong objection.  I'd 
appreciate it if you could let me know good design information to add a server 
encoding (e.g. the URL of the most recent patch to add a new server encoding)

Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] autonomous transactions

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer
> Of course, if we could decrease the startup cost of a bgworker

For this use in autonomous tx's we could probably pool workers. Or at least 
lazily terminate them so that the loop cases work better by re-using an 
existing bgworker.



Though I may say something odd, isn’t the bgworker approach going to increase 
context switches?  I thought PostgreSQL has made efforts to decrease context 
switches, e.g.



* Each backend itself writes WAL to disk unlike Oracle requests LGWR process to 
write REDO to disk.



* Releasing and re-acquiring a lwlock appears to try to avoid context switches.



   /*

   * Loop here to try to acquire lock after each time we are signaled by

   * LWLockRelease.

   *

   * NOTE: it might seem better to have LWLockRelease actually grant us 
the

   * lock, rather than retrying and possibly having to go back to 
sleep. But

   * in practice that is no good because it means a process swap for 
every

   * lock acquisition when two or more processes are contending for the 
same

   * lock.  Since LWLocks are normally used to protect not-very-long

   * sections of computation, a process needs to be able to acquire and

   * release the same lock many times during a single CPU time slice, 
even

   * in the presence of contention.  The efficiency of being able to do 
that

   * outweighs the inefficiency of sometimes wasting a process dispatch

   * cycle because the lock is not free when a released waiter finally 
gets

   * to run.  See pgsql-hackers archives for 29-Dec-01.

*/



I’m not sure whether to be nervous about the context switch cost in the use 
cases of autonomous transactions.



Regards

Takayuki Tsunakawa

Re: [HACKERS] [RFC] Change the default of update_process_title to off

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Thomas Munro
> Another database vendor recommends granting SeLockMemoryPrivilege so that
> it can use large pages on Windows when using several GB of buffer pool.
> I wonder if that might help Postgres on Windows.  This could be useful as
> a starting point to test that theory:
> 
> https://www.postgresql.org/message-id/CAEepm%3D075-bgHi_VDt4SCAmt%2Bo_
> %2B1XaRap2zh7XwfZvT294oHA%40mail.gmail.com

Sorry for my late reply, and thank you.  I've created a patch based on yours, 
and I'll submit it shortly in a separate thread.

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Supporting huge pages on Windows

[Tsunakawa, Takayuki]

Hello,

The attached patch implements huge_pages on Windows.  I'll add this to the 
CommitFest.

The performance improvement was about 2% with the following select-only 
pgbench.  The scaling factor is 200, where the database size is roughly 3GB.  I 
ran the benchmark on my Windows 10 PC with 6 CPU cores and 16GB of RAM.

　　pgbench -c18 -j6 -T60 -S bench

Before running pgbench, I used pg_prewarm to cache all pgbench tables and 
indexes (excluding the history table) in the 4GB shared buffers.  The averages 
of running pgbench three times are:

  huge_pages=off: 70412 tps
  huge_pages=on : 72100 tps

The purpose of pg_ctl.c modification is to retain "Lock pages in memory" 
Windows user right in postgres.  That user right is necessary for the 
large-page support.  The current pg_ctl removes all privileges when spawning 
postgres, which is overkill.  The system administrator should be able to assign 
appropriate privileges to the PostgreSQL service account.

Credit: This patch is based on Thomas Munro's one.


Regards
Takayuki Tsunakawa



win_large_page.patch
Description: win_large_page.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Is the last 9.1 release planned?

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Magnus Hagander
> On Oct 5, 2016 5:42 AM, "Tsunakawa, Takayuki"
> <tsunakawa.ta...@jp.fujitsu.com> wrote:
> > Thanks for clarification.  Then, I understood that the expression "stop
> releases in September" in the release note and a pgsql-announce mail was
> not correct.
> 
> 
> It basically means stop guaranteeing that we do. As of a couple of days
> ago, bug fixes won't necessarily be back ported to 9.1 if they are difficult.
> But there will be one wrap-up release in November with any patches that
> have already been applied but have not yet been in a release. And after
> November, we will stop doing that as well.

I see.  I simply took the phrases in pgsql-announce "September is EOL" and 
"only expects one more release" as meaning "only expects one more release in 
September", because I didn't imagine a minor version is released after EOL.

If possible, I was happy if I saw "only expects one more release in November" 
or "on a regular schedule".

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Switch to unnamed POSIX semaphores as our preferred sema code?

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane
> I've gotten a bit tired of seeing "could not create semaphores: No space
> left on device" failures in the buildfarm, so I looked into whether we should
> consider preferring unnamed POSIX semaphores over SysV semaphores.

+100
Wonderful decision and cautious analysis.  This will make PostgreSQL more 
friendly to users, especially newcomers, by eliminating the need to tune kernel 
resources.  I wish other kernel resources (files, procs) will need no tuning 
like Windows, but that's just a daydream.

Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: Robert Haas [mailto:robertmh...@gmail.com]
> I have no opinion on this patch, because I haven't reviewed it, but note
> recent commit 3b90e38c5d592ea8ec8236287dd5c749fc041728, which appears to
> be semi-related.

Thank you for interesting information.  Maybe Tom-san experienced some trouble 
in creating this patch.  Fortunately, this doesn't appear to be related to my 
patch, because the patch changed the timing of closing listen ports in 
postmaster children, whereas my patch explicitly closes listen ports in 
postmaster.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane
FWIW, I'm pretty much -1 on messing with the timing of the socket close
> actions.  I broke that once within recent memory, so maybe I'm gun-shy,
> but I think that the odds of unpleasant side effects greatly outweigh any
> likely benefit there.

I couldn't find any relevant mails in pgsql-hackers.  I found no problem with 
the attached patch.  Do you think this is OK?

Regards
Takayuki Tsunakawa




02_close_listen_ports_early.patch
Description: 02_close_listen_ports_early.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Is the last 9.1 release planned?

[Tsunakawa, Takayuki]

From: Michael Paquier [mailto:michael.paqu...@gmail.com]
> 9.1.24 will be the last in the 9.1 series as far as I know. And it is still
> to come at the beginning of November:
> https://www.postgresql.org/developer/roadmap/

But the release note for 9.1.23 says:

"The PostgreSQL community will stop releasing updates for the 9.1.X release 
series in September 2016. Users are encouraged to update to a newer release 
branch soon."


OTOH, the 9.0.22 release note said:

"The PostgreSQL community will stop releasing updates for the 9.0.X release 
series in September 2015. Users are encouraged to update to a newer release 
branch soon."

and the 9.0.23, which is the last release for 9.0 said:

"This is expected to be the last PostgreSQL release in the 9.0.X series. Users 
are encouraged to update to a newer release branch soon."

and 9.0.23 was released in October 8.  So I guessed 9.1.24 will be released in 
a week or so.

Regards
Takayuki Tsunakawa

 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Is the last 9.1 release planned?

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Jaime Casanova
> Well, no. We normally don't give special treatment to any minor release
> not even if it is going to die.
> What normally happens is that all minor releases are released the same day.
> 
> Taken your example, that same day were released: 9.0.23, 9.1.19, 9.2.14,
> 9.3.10 and 9.4.5
> 

Thanks for clarification.  Then, I understood that the expression "stop 
releases in September" in the release note and a pgsql-announce mail was not 
correct.

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Is the last 9.1 release planned?

[Tsunakawa, Takayuki]

Hello,

(Please point me to the appropriate ML if this is not the right one.)

According to the following mail, I thought one more release for 9.1 (9.1.24) 
was scheduled in September.  Is there any release plan for the 9.1 last 
release?  If there's, I want to wait for it, and apply 9.1.23 otherwise.

https://www.postgresql.org/message-id/1470924187.12735.59.ca...@gunduz.org

[Excerpt]
PostgreSQL version 9.1 will be End-of-Life in September 2016.  The project 
expects to only release one more update for that version.


Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Remove the comment on the countereffectiveness of large shared_buffers on Windows

[Tsunakawa, Takayuki]

Hello,

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Magnus Hagander
> On Wed, Aug 24, 2016 at 4:35 AM, Tsunakawa, Takayuki
> <tsunakawa.ta...@jp.fujitsu.com> wrote:
>   As a similar topic, I wonder whether the following still holds true,
> after many improvements on shared buffer lock contention.
> 
>   https://www.postgresql.org/docs/devel/static/runtime-config-re
> source.html
> 
>   "The useful range for shared_buffers on Windows systems
> is generally from 64MB to 512MB."
> 
> 
> 
> 
> Yes, that may very much be out of date as well. A good set of benchmarks
> around that would definitely be welcome.


I'd like to propose the above-mentioned comment from the manual.  The patch is 
attached.

I ran read-only and read-write modes of pgbench, and could not see any apparent 
decrease in performance when I increased shared_buffers.  The scaling factor is 
200, where the database size is roughly 3GB.  I ran the benchmark on my Windows 
10 PC with 6 CPU cores and 16GB of RAM.  The database and WAL is stored on the 
same HDD.

<>
@echo off
for %%s in (256MB 512MB 1GB 2GB 4GB) do (
  pg_ctl -w -o "-c shared_buffers=%%s" start
  pgbench -c18 -j6 -T60 -S bench >> g:\b.txt 2>&1
  pg_ctl -t 3600 stop
)

<>
shared_buffers  tps
256MB  63056
512MB  63918
1GB  65520
2GB  66840
4GB  68270

<>
shared_buffers  tps
256MB  1138
512MB  1187
1GB  1571
2GB  1650
4GB  1598

Regards
Takayuki Tsunakawa



win_shrdbuf_perf.patch
Description: win_shrdbuf_perf.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

Hello,

Please let me ask you about possible causes of a certain problem, slow shutdown 
of postmaster when a backend crashes, and whether to fix PostgreSQL.

Our customer is using 64-bit PostgreSQL 9.2.8 on RHEL 6.4.  Yes, the PostgreSQL 
version is rather old but there's no relevant bug fix in later 9.2.x releases.


PROBLEM
==

One backend process (postgres) for an application session crashed due to a 
segmentation fault and dumped a core file.  The cause is a bug of 
pg_dbms_stats.  Another note is that restart_after_crash is off to make 
failover happen.

The problem here is that postmaster took as long as 15 seconds to terminate 
after it had detected a crashed backend.  The messages were output as follows:

20:12：35.004に
LOG:  server process (PID 31894) was terminated by signal 11: Segmentation fault
DETAIL:  Failed process was running: DELETE...(snip)
LOG:  terminating any other active server processes

>From 20：12：35.013 to 20：12：39.074, the following message was output 80 times.

FATAL:  the database system is in recovery mode

20:12:50
The custom monitoring system detected the death of postmaster as a result of 
running "pg_ctl status".

That's it.  You may say the following message should also have been emitted, 
but there's not.  This is because we commented out the ereport() call in 
quickdie() in tcop.c.  That ereport() call can hang depending on the timing, 
which is fixed in 9.4.

WARNING:  terminating connection because of crash of another server process

The customer insists that PostgreSQL takes longer to shut down than expected, 
which risks exceeding their allowed failover time.


CAUSE
==

There's no apparent evidence to indicate the cause, but I could guess a few 
reasons.  What do you think these are correct and should fix PostgreSQL? (I 
think so)

1) postmaster should close the listening ports earlier
As cited above, for 4 seconds, postmaster created 80 dead-end child processes 
which just output "FATAL:  the database system is in recovery mode".  This 
indicates that postmaster is busy handling re-connection requests from 
disconnected applications, preventing postmaster from reaping dead children as 
fast as possible.  This is a waste because postmaster will only shut down.

I think the listening ports should be closed in HandleChildCrash() when the 
condition "(RecoveryError || !restart_after_crash)" is true.


2) make stats collector terminate immediately
stats collector seems to write the permanent stats file even when it receives 
SIGQUIT.  But it's useless because the stat file is reset during recovery.  And 
Tom claimed that writing stats file can take long:

https://www.postgresql.org/message-id/11800.1455135...@sss.pgh.pa.us


3) Anything else?
While postmaster is in PM_WAIT_DEAD_END state, it leaves the listening ports 
open but doesn't call select()/accept().  As a result, incoming connection 
requests are accumulated in the listen queue of the sockets.  Does the OS have 
any bug to slow the process termination when the listen queue is not empty?


Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas
> On Tue, Sep 20, 2016 at 2:20 AM, Tsunakawa, Takayuki
> <tsunakawa.ta...@jp.fujitsu.com> wrote:
> > There's no apparent evidence to indicate the cause, but I could guess
> > a few reasons.  What do you think these are correct and should fix
> > PostgreSQL? (I think so)
> 
> I think that we shouldn't start changing things based on guesses about what
> the problem is, even if they're fairly smart guesses.  The thing to do would
> be to construct a test rig, crash the server repeatedly, and add debugging
> instrumentation to figure out where the time is actually going.

We have tried to reproduce the problem in the past several days with much more 
stress on our environment than on the customer's one -- 1,000 tables aiming for 
a dozens of times larger stats file and repeated reconnection requests from 
hundreds of clients -- but we could not succeed.



> I do think your theory about the stats collector might be worth pursuing.
> It seems that the stats collector only responds to SIGQUIT, ignoring SIGTERM.
> Making it do a clean shutdown on SIGTERM and a fast exit on SIGQUIT seems
> possibly worthwhile.

Thank you for giving confidence for proceeding.  And I also believe that 
postmaster should close the listening ports earlier. Regardless of whether this 
problem will be solved not confident these will solve the, I think it'd be 
better to fix these two points so that postmaster doesn't longer time than 
necessary.  I think I'll create a patch after giving it a bit more thought.

Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Supporting SJIS as a database encoding

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Kyotaro
> Thanks, by the way, there's another issue related to SJIS conversion.  MS932
> has several characters that have multiple code points. By converting texts
> in this encoding to and from Unicode causes a round-trop problem. For
> example,
> 
> 8754(ROMAN NUMERICAL I in NEC specials)
>   => U+2160(ROMAN NUMERICAL I)
> => FA4A (ROMAN NUMERICA I in IBM extension)
> 
> My counting said that 398 characters are affected by this kind of replacement.
> Addition to that, "GAIJI" (Private usage area) is not allowed. Is this meet
> your purpose?

Supporting GAIJI is not a requirement as far as I know.  Thank you for sharing 
information.

# I realize my lack of knowledge about character sets...

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC] Change the default of update_process_title to off

[Tsunakawa, Takayuki]

From: Magnus Hagander [mailto:mag...@hagander.net]
Applied and backpatched to 9.6.

Thank you very much.  I didn’t expect 9.6 to be patched, so I’m very happy.

Regards
Takayuki Tsunakawa

Re: [HACKERS] [RFC] Change the default of update_process_title to off

[Tsunakawa, Takayuki]

From: Peter Geoghegan [mailto:p...@heroku.com]
> On Tue, Aug 23, 2016 at 1:44 PM, Bruce Momjian  wrote:
> >> [Windows]
> >> #clients  onoff
> >> 12 29793  38169
> >> 24 31587 87237
> >> 48 32588 83335
> >> 96 34261  67668
> >
> > This ranges from a 28% to a 97% speed improvement on Windows!  Those
> > are not typos!  This is a game-changer for use of Postgres on Windows
> > for certain workloads!
> 
> While I don't care all that much about performance on windows, it is a little
> sad that it took this long to fix something so simple. Consider this exchange,
> as a further example of our lack of concern here:
> 
> https://www.postgresql.org/message-id/30619.1428157...@sss.pgh.pa.us

Probably, the useful Windows Performance Toolkit, which is a counterpart of 
perf on Linux, was not available before.  Maybe we can dig deeper into 
performance problems with it now.

As a similar topic, I wonder whether the following still holds true, after many 
improvements on shared buffer lock contention.

https://www.postgresql.org/docs/devel/static/runtime-config-resource.html

"The useful range for shared_buffers on Windows systems is generally 
from 64MB to 512MB."

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

Hello,

Our customer hit a problem of cascading replication, and we found the cause.  
They are using the latest PostgreSQL 9.2.18.  The bug seems to have been fixed 
in 9.4 and higher during the big modification of xlog.c, but it's not reflected 
in older releases.

The attached patch is for 9.2.18.  This just borrows the idea from 9.4 and 
higher.

But we haven't been able to reproduce the problem.  Could you review the patch 
and help to test it?  I would very much appreciate it if you could figure out 
how to reproduce the problem easily.


PROBLEM


The customer's configuration consists of three nodes: node1 is a primary, node2 
is a synchronous standby, and node3 is a cascading standby.  The primary 
archives WAL to a shared (network?) storage and the standbys read archived WAL 
from there with restore_command.  recovery_target_timeline is set to 'latest' 
on the standbys.

When node1 dies and node2 is promoted to a primary, node3 cannot catch up node2 
forever, emitting the following message repeatedly:

LOG:  out-of-sequence timeline ID 140 (after 141) in log file 652, segment 117, 
offset 0

The expected behavior is that node3 catches up node2 and keeps synchronization.


CAUSE


The processing went as follows.

1. node1's timeline is 140.  It wrote a WAL record at the end of WAL segment 
117.  The WAL record didn't fit the last page, so it was split across segments 
117 and 118.

2. WAL segment 117 was archived.

3. node1 got down, and node2 was promoted.

4. As part of the recovery process, node2 retrieves WAL segment 117 from 
archive.  It found a WAL record fragment at the end of the segment but could 
not find the remaining fragment in segment 118, so node2 stops recovery there.

LOG:  restored log file "008C028C0075" from archive
LOG:  received promote request
LOG:  redo done at 28C/75FFF738

5. node2 becomes the primary, and its timeline becomes 118.  node3 is 
disconnected by node2 (but later reconnectes to node2).

LOG:  terminating all walsender processes to force cascaded standby(s) to 
update timeline and reconnect

6. node3 retrieves and applies WAL segment 117 from archive.

LOG:  restored log file "008C028C0075" from archive

7. node3 found .history file for time line 141 and renews its timeline to 141.

8. Because node3 found a WAL record fragment at the end of segment 117, it 
expects to find the remaining fragment at the beginning of WAL segment 118 
streamed from node2.  But there was a fragment of a different WAL record, 
because node2 overwrote a different WAL record at the end of segment 117 across 
to 118.

LOG:  invalid contrecord length 5892 in log file 652, segment 118, offset 0

9. node3 then retrieves segment 117 from archive again to get the WAL record at 
the end of segment 117.  However, as node3's timeline is already 141, it 
complains about the older timeline when it sees the timeline 140 at the 
beginning of segment 117.

LOG:  out-of-sequence timeline ID 140 (after 141) in log file 652, segment 117, 
offset 0



Regards
Takayuki Tsunakawa



cascading_standby_stuck.patch
Description: cascading_standby_stuck.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> 9.3 has addressed that by allowing streaming standbys to perform timeline
> jumps via the replication protocol. Doesn't this problem enter in this area?

IIUC, that new feature enables timeline switch without disconnecting the 
standby and without WAL archive.  I think 9.2 can perform timeline switch with 
WAL archive.  In fact, the customer said they hit the problem only once in many 
occurrences of the same test.  The bug seems to emerge depending on the timing.

Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Supporting huge pages on Windows

[Tsunakawa, Takayuki]

From: Thomas Munro [mailto:thomas.mu...@enterprisedb.com]
> >  huge_pages=off: 70412 tps
> >  huge_pages=on : 72100 tps
> 
> Hmm.  I guess it could be noise or random code rearrangement effects.

I'm not the difference was a random noise, because running multiple set of 
three runs of pgbench (huge_pages = on, off, on, off, on...) produced similar 
results.  But I expected a bit greater improvement, say, +10%.  There may be 
better benchmark model where the large page stands out, but I think pgbench is 
not so bad because its random data access would cause TLB cache misses.



> I saw your recent post[2] proposing to remove the sentence about the 512MB
> effective limit and I wondered why you didn't go to larger sizes with a
> larger database and more run time.  But I will let others with more
> benchmarking experience comment on the best approach to investigate Windows
> shared_buffers performance.

Yes, I could have gone to 8GB of shared_buffers because my PC has 16GB of RAM, 
but I felt the number of variations was sufficient.  Anyway, positive comments 
on benchmarking would be appreciated.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Supporting huge pages on Windows

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas
> On Sun, Sep 25, 2016 at 10:45 PM, Tsunakawa, Takayuki
> <tsunakawa.ta...@jp.fujitsu.com> wrote:
> > Credit: This patch is based on Thomas Munro's one.
> 
> How are they different?

As Thomas mentioned, his patch (only win32_shmem.c) might not have been able to 
compile (though I didn't try.)  And it didn't have error processing or 
documentation.  I added error handling, documentation, comments, and a little 
bit of structural change.  The possibly biggest change, though it's only 
one-liner in pg_ctl.c, is additionally required.  I failed to include it in the 
first patch.  The attached patch includes that.


Regards
Takayuki Tsunakawa



win_large_page_v2.patch
Description: win_large_page_v2.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane
> Allowing SIGQUIT to prompt fast shutdown of the stats collector seems sane,
> though.  Try to make sure it doesn't leave partly-written stats files
> behind.

The attached patch based on HEAD does this.  I'd like this to be back-patched 
because one of our important customers uses 9.2.

I didn't remove partially written stat files on SIGQUIT for the following 
reasons.  Is this OK?

1. The recovery at the next restart will remove the stat files.
2. SIGQUIT processing should be as fast as possible.
3. If writing stats files took long due to the OS page cache flushing, removing 
files might be forced to wait likewise.


> FWIW, I'm pretty much -1 on messing with the timing of the socket close
> actions.  I broke that once within recent memory, so maybe I'm gun-shy,
> but I think that the odds of unpleasant side effects greatly outweigh any
> likely benefit there.

Wasn't it related to TouchSocketFiles()?  Can I see the discussion on this ML?  
I don't see any problem looking at the code...

Regards
Takayuki Tsunakawa





01_pgstat_avoid_writing_on_sigquit.patch
Description: 01_pgstat_avoid_writing_on_sigquit.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Supporting huge pages on Windows

[Tsunakawa, Takayuki]

From: Thomas Munro [mailto:thomas.mu...@enterprisedb.com]
> Your ~2.4% number is similar to what was reported for Linux with 4GB
> shared_buffers:
> 
> https://www.postgresql.org/message-id/20130913234125.GC13697%40roobarb
> .crazydogs.org

I'm relieved to know that a similar figure was gained on Linux.  Thanks for the 
info.


> Later in that thread there was a report of a dramatic ~15% increase in "best
> result" TPS, but that was with 60GB of shared_buffers on a machine with
> 256GB of RAM:
> 
> https://www.postgresql.org/message-id/20131024060313.GA21888%40toroid.
> org

From: Andres Freund [mailto:and...@anarazel.de]
> FWIW, I've seen 2-3x increases with ~60GB of s_b.

Wow, nice figures.  It's unfortunate that I don't have such a big machine 
available at hand.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ashutosh Bapat
> In pgstat_quickdie(), I think a call to sigaddset(, SIGQUIT) is
> missing before PG_SETMASK(). Although there are some SIGQUIT handlers which
> do not have that call. But I guess, it will be safer to have it.

I didn't add it because pgstat_quickdie() just exits, like some other 
postmaster children.  I thought those processes which are concerned about their 
termination processing call sigaddset(SIGQUIT), so I went after the processes 
who aren't.  Is this really necessary?

> Also, many other SIGQUIT handlers like bgworker_quickdie() call
> on_exit_reset() followed by exit(2) instead of just exit(1) in
> pgstat_quickdie(). Why is this difference?

As Robert and Tom said, either exit(1) or exit(2) is OK because reaper() 
handles non-zero exit code the same.  Regarding on_proc_reset(), stats 
collector is not attached to the shared memory and does not register 
on_proc_exit() callbacks.  These situations are the same as the archiver 
process, so I followed it.

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] [bug fix] Stats collector is not restarted on the standby

[Tsunakawa, Takayuki]

Hello,

If the stats collector is forcibly terminated on the standby in streaming 
replication configuration, it won't be restarted until the standby is promoted 
to the primary.  The attached patch restarts the stats collector on the standby.

FYI, when the stats collector is down, SELECTs against the statistics views get 
stale data with the following message.

LOG:  using stale statistics instead of current ones because stats collector is 
not responding
STATEMENT:  select * from pg_stat_user_tables

Regards
Takayuki Tsunakawa



stats_collector_not_restarted.patch
Description: stats_collector_not_restarted.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal : For Auto-Prewarm.

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Mithun Cy
> # pg_autoprewarm.
> 
> This a PostgreSQL contrib module which automatically dump all of the
> blocknums present in buffer pool at the time of server shutdown(smart and
> fast mode only, to be enhanced to dump at regular interval.) and load these
> blocks when server restarts.

I welcome this feature!  I remember pg_hibernate did this.   I wonder what 
happened to pg_hibernate.  Did you check it?

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [bug fix] Stats collector is not restarted on the standby

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas
> The delay is intentional.  Per pgstat_start():

It's kind of you to tell the reason.


> Committed and back-patched all the way.

Thanks again!

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: Ashutosh Bapat [mailto:ashutosh.ba...@enterprisedb.com]
> Ok. In that case, I think we shouldn't even call PG_SETMASK() similar to
> pgarch_exit(). Attached patch removes PG_SETMASK(). Let me know if it looks
> good.

It looks good.  Thanks.

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Ashutosh Bapat
> I am not sure if following condition is a good idea in ServerLoop()
> 1650 if (pmState == PM_WAIT_DEAD_END || ClosedSockets)
> 
> There are no sockets to listen on, so select in the else condition is going
> to sleep for timeout determined based on the sequence expected.
> Just before we close sockets in pmdie() it sets AbortStartTime, which
> determines the timeout for the sleep here. So, it doesn't make sense to
> ignore it. Instead may be we should change the default 60s sleep to 100ms
> sleep in DetermineSleepTime().

That sounds better.  I modified cleaned ServerLoop() by pushing the existing 
100ms logic into DetermineSleepTime().


> While the postmaster is terminating children, a new connection request may
> arrive. We should probably close listening sockets before terminating
> children in pmdie().

I moved ClosePostmasterSocket() call before terminating children in the 
immediate shutdown case.  I didn't change the behavior of smart and fast 
shutdown modes, because they may take a long time to complete due to 
checkpointing etc.  Users will want to know what's happening during shutdown or 
after pg_ctl stop times out, by getting the message "FATAL:  the database 
system is shutting down" when they try to connect to the database.  The 
immediate shutdown or crash should better be as fast as possible.


> Otherwise this patch looks good to me. It applies and compiles cleanly.
> make check-world doesn't show any failures.

Thank you for reviewing and testing.  The revised patch is attached.

Regards
Takayuki Tsunakawa



02_close_listen_ports_early_v2.patch
Description: 02_close_listen_ports_early_v2.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Remove the comment on the countereffectiveness of large shared_buffers on Windows

[Tsunakawa, Takayuki]

From: amul sul [mailto:sula...@gmail.com]
> IMHO, I think we could remove third paragraph completely and generalised
> starting of second paragraph, somewhat looks likes as
> follow:
> 
> 
> -If you have a dedicated database server with 1GB or more of RAM,
> a
> -reasonable starting value for shared_buffers
> is 25%
> -of the memory in your system.  There are some workloads where even
> +A reasonable starting value for
> shared_buffers is 25%
> +   of the RAM in your system.  There are some workloads where even
>  large settings for shared_buffers are
> effective, but
>  because PostgreSQL also relies on 
the
>  operating system cache, it is unlikely that an allocation of more
> than

The third paragraph may be redundant, I'm a bit inclined to leave it for 
kindness and completeness.  The attached revised patch just correct the 
existing typo (large -> larger).

I'll change the status to needs review.

Regards
Takayuki Tsunakawa





win_shrdbuf_perf_v2.patch
Description: win_shrdbuf_perf_v2.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: BUG #13755: pgwin32_is_service not checking if SECURITY_SERVICE_SID is disabled

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> On Sun, Nov 6, 2016 at 6:30 PM, MauMau  wrote:
> > Sorry, I may have had to send this to pgsql-hackers.  I just replied
> > to all, which did not include pgsql-hackers but pgsql-bugs because
> > this discussion was on pgsql-bugs.  CommitFest app doesn't seem to
> > reflect the mails on pgsql-bugs, so I'm re-submitting this here on
> > pgsql-hackers.
> 
> No problem, I still see a unique thread so that's not an issue seen from
> here.

You are right.  A while after I sent the second mail, I noticed the CommitFest 
app collected both of my mails.  I was just impatient.



> So you see the same behavior with the patch I sent and your refactoring,
> right? If yes, backpatching the one-liner is the safest bet to me. We could
> keep the refactoring for HEAD if it makes sense.

Yes.  And It's fine to me that your patch will be applied to previous releases 
and my patch to HEAD only.  This is a good (rare?) chance to reduce the 
Windows-specific code, so I want to take advantage of it.




> Something is wrong with the format of your patch by the way. My Windows
> and even OSX environments recognize it as a binary file, though I can read
> it in any editor and I cannot apply it cleanly with a simple patch command.
> Could you send it again and double-check?

Ouch, the Git shell included in GitHub Desktop for Windows produced the diff in 
UTF-16 and CR/LF line terminators.  I haven't found how to fix it, so I 
generated the attached patch on Linux.  Please check it.


> > To reproduce the OP's problem, I modified pg_ctl.c to disable
> > SECURITY_SERVICE_RID when spawning postgres.exe.
> 
> So basically you allocated a SID to drop via AllocateAndInitializeSid,
> called _CreateRestrictedToken and let the process being spawned? I think
> that this is the patch attached (win32-disable-service-rid.patch). Could
> you confirm? I want to be sure that we are testing the same things.

Yes, I did the same.

Regards
Takayuki Tsunakawa



win32-security-service-v4.patch
Description: win32-security-service-v4.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Mithun Cy
> Among the remaining things I have worked on failover to new master idea.
> Below patch implement that idea. This is taken from Victors patch but
> rewritten by me to do some cleanup. As in Victor's patch we have a new
> connection parameter "target_server_type", It can take 2 values 1. "any"
> 2. "master" with DEFAULT as "any". If it's has the value "any" we can connect
> to any of the host server (both master(primary) and slave(standby)). If
> the value is "master" then we try to connect to master(primary) only.
> NOTE: Parameter name is inspired and taken from PostgreSql JDBC Driver
>  .

I'm interested to review this patch (but I haven't read it yet, I'm reading 
Robert's patch now.)  Are you planning a new CommitFest entry?

Why don't you add "standby" and "prefer_standby" as the target_server_type 
value?  Are you thinking that those values are useful with load balancing 
feature?


> The main difference between Victor's and this new patch is Default value
> of parameter target_server_type. In Victor's patch if number of host in
> connection string is 1 then default value is "any" (This was done to make
> sure old psql connect to standby as it is now). If it is greater than 1
> then default value is set as "master". For me this appeared slightly
> inconsistent having default value as "any" for any number of connection
> appeared more appropriate which is also backward compatible. And, if user
> want failover to master he should ask for it by setting
> target_server_type=master in connection string.

That's sensible, I agree.


Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas
> OK.  I agree that's a problem.  However, your patch adds zero new comment
> text while removing some existing comments, so I can't easily tell how it
> solves that problem or whether it does so correctly.  Even if I were smart
> enough to figure it out, I wouldn't want to rely on the next person also
> being that smart.  This is obviously a subtle problem in tricky code, so
> a clear explanation of the fix seems like a very good idea.

The comment describes what the code is trying to achieve.  Actually, I just 
imitated the code and comment of later major releases.  The only difference 
between later releases and my patch (for 9.2) is whether the state is stored in 
XLogReaderStruct or as global variables.  Below is the comment from 9.6, where 
the second paragraph describes what the two nested if conditions mean.  The 
removed comment lines are what became irrelevant, which is also not present in 
later major releases.

/*
 * Since child timelines are always assigned a TLI greater than their
 * immediate parent's TLI, we should never see TLI go backwards across
 * successive pages of a consistent WAL sequence.
 *
 * Sometimes we re-read a segment that's already been (partially) read. 
So
 * we only verify TLIs for pages that are later than the last remembered
 * LSN.
 */

Regards
Takayuki Tsunakawa




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: BUG #13755: pgwin32_is_service not checking if SECURITY_SERVICE_SID is disabled

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> Things are this way since b15f9b08 that introduced pgwin32_is_service().
> Still, by considering what you say, you definitely have a point that if
> postgres is started by another service running as Local System logs are
> going where they should not. Let's remove the check for LocalSystem but
> still check for SE_GROUP_ENABLED.
> So, without any refactoring work, isn't the attached patch just but fine?
> That seems to work properly for me.

Just taking a look at the patch, I'm sure it will work.

Committer (Heikki?),
v5 is refactored for HEAD, and v6 is for previous releases without refactoring. 
 I'd like v5 to be applied to at least HEAD, as it removes a lot of unnecessary 
code.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: BUG #13755: pgwin32_is_service not checking if SECURITY_SERVICE_SID is disabled

[Tsunakawa, Takayuki]

From: Michael Paquier [mailto:michael.paqu...@gmail.com]
> I just looked more deeply at your refactoring patch, and I didn't know about
> CheckTokenMembership()... The whole logic of your patch depends on it.
> That's quite a cleanup that you have here. It looks that the former
> implementation just had no knowledge of this routine or it would just have
> been used.

Yes, Microsoft recommends GetTokenMembership() because it's simpler.


> +if (IsAdministrators || IsPowerUsers)
> +return 1;
> +else
> +return 0;
> I would remove the else here.

IIRC, I sometimes saw this style of code in PostgreSQL (or in psqlODBC 
possibly...)  I'd like to leave the style choice to the committer.

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: BUG #13755: pgwin32_is_service not checking if SECURITY_SERVICE_SID is disabled

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> https://msdn.microsoft.com/ja-jp/library/windows/desktop/ms684190(v=vs
> > .85).aspx
> 
> That's what I looked at as well :) And this part is what caught my attention,
> meaning that it is not used by anything else than the SCM:
> "The LocalSystem account is a predefined local account used by the service
> control manager."

The same thing is said about other two special accounts, so they need to be 
checked if we really believe we need to check for LocalSystem.

"The LocalService account is a predefined local account used by the service 
control manager."
"The NetworkService account is a predefined local account used by the service 
control manager."

But, in practice, SECURITY_SERVICE_RID has turned out to be enough.


> And this implies, at least it seems to me, that trying to run Postgres as
> this user is actually not something you'd want to do.

Yes, I think people should avoid using LocalSystem for user services like 
PostgreSQL for security reasons.  But the Services applet in the Control Panel 
allows to select LocalSystem, and pg_ctl register creates a service with 
LocalSystem account when -U is omitted.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: BUG #13755: pgwin32_is_service not checking if SECURITY_SERVICE_SID is disabled

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> Hm... See here:
> http://stackoverflow.com/questions/6084547/how-to-check-whether-a-proc
> ess-is-running-as-a-windows-service
> And particularly this quote:
> "No, that is not reliable because if a service is started from command line
> for example it will not have this token. "

Is there any Microsoft document that states this?  I don't think the above 
comment is correct, because SECURITY_SERVICE_RID was present when I started the 
service from command line with "net start".

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: BUG #13755: pgwin32_is_service not checking if SECURITY_SERVICE_SID is disabled

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> Meh. Local System accounts are used only by services (see comments of
> pgwin32_is_service), so I'd expect pgwin32_is_service() to return true in
> this case, contrary to what your v5 is doing. v4 is doing it better I think
> at quick glance.
> Not relying on the fact that local system accounts are only used by services
> looks bad to me.

I believe v5 is correct for two reasons:


(1) 
SECURITY_SERVICE_RID is enough to check, because the process gets 
SECURITY_SERVICE_RID when it runs as a service.

https://msdn.microsoft.com/ja-jp/library/windows/desktop/aa379649(v=vs.85).aspx

SECURITY_SERVICE_RID
Accounts authorized to log on as a service. This is a group identifier added to 
the token of a process when it was logged as a service. The corresponding logon 
type is LOGON32_LOGON_SERVICE.


I saw descriptions that LocalSystem is used by the SCM, but didn't find a 
statement that LocalSystem is used only by SCM and services.  In addition, if 
the check for LocalSystem is really necessary, LocalService and NetworkService 
also need to be checked.

https://msdn.microsoft.com/ja-jp/library/windows/desktop/ms684190(v=vs.85).aspx

(Japanese article)
http://www.atmarkit.co.jp/ait/articles/0905/08/news095.html


(2)
The OP wants to explicitly run postgres.exe outside the service even when his 
app runs as a service, so that the app can read postgres's messages from its 
stdout/stderr.  So, he disabled SECURITY_SERVICE_RID when starting 
postgres.exe.  His users may run his app as a service under LocalSystem.

[Excerpt]
--
We ship PG with our own product, which may or may not be
installed as a service.  When running PG, we run postgres.exe directly via a
Tcl-based wrapper script so that we can monitor the output in real time. 

When our product is installed as a service, we use CreateRestrictedToken to
disable all admin rights as well as the SECURITY_SERVICE_RID, and use the
returned token with CreateProcessAsUser, for which we also specify
CREATE_NEW_CONSOLE.  This process then calls our wrapper script.  Inside
this wrapper, I can call GetStdHandle (via Twapi) and get valid handles for
all 3: in, out, and err.  Yet when the script calls postgres.exe, nothing is
received on the output.  As mentioned above, nothing is logged in the event
log, either.
--


Regards
Takayuki Tsunakawa

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Mithun Cy
> Yes this patch will only address failover to new master, values "master"
> and "any" appeared sufficient for that case.

Do you mean that unlike pgJDBC "standby" and "prefer_standby" are useless, or 
they are useful but you don't have time to implement it and want to do it in 
the near future?  Do you mind if I do it if time permits me?  I think they are 
useful without load balancing feature, when the user has multiple standbys for 
HA.

Could you add a new entry in CommitFest 2017-1? I'm afraid we can't track the 
status of your patch because the original patch in this thread has already been 
committed.

Regards
Takayuki Tsunakawa
 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas
> Great, committed.  There's still potentially more work to be done here,
> because my patch omits some features that were present in Victor's original
> submission, like setting the failover timeout, optionally randomizing the
> order of the hosts, and distinguishing between master and standby servers;
> Victor, or anyone, please feel free to submit separate patches for those
> things.

I did a few tests with ECPG.  I'm satisfied with the current behavior, but 
someone says different.  I'd like to share the result.

The following literal connection strings succeeded.  host1 is a server where 
PostgreSQL is not running, and host2 is where it's running.  I could connect to 
the database server on host2.

EXEC SQL CONNECT TO 'tcp:postgresql://host1,host2:5450/postgres';
EXEC SQL CONNECT TO 'tcp:postgresql://host1,host2:5450,5450/postgres';

EXEC SQL CONNECT TO 'postgres@host1,host2:5450';
EXEC SQL CONNECT TO 'postgres@host1,host2:5450,5450';


EXEC SQL CONNECT TO 'tcp:postgresql://?service=my_service';

~/.pg_service.conf
[my_service]
host=host1,host2
port=5450  # and port=5450,5450 case
dbname=postgres


But this one makes PQconnectdbParams() fail, because the passed "host" is 
"host1:5450,host2" and "port" is "5450".  ECPGconnect()'s parser is different 
from libpq's.  However, the tcp:postgresql:// syntax is not described a URL in 
the manual, so I think it's sufficient to just describe the syntax in the ECPG 
article.

EXEC SQL CONNECT TO 'tcp:postgresql://host1:5450,host2:5450/postgres';


And without the single quote like below, ecpg fails to precompile the source 
file.  I also think it's enough to state in the manual "quote the connection 
target if you specify multiple hosts or ports".

EXEC SQL CONNECT TO tcp:postgresql://host1,host2:5450,5450/postgres;

connect.ec:12: ERROR: syntax error at or near ","


Comments?


Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Remove the comment on the countereffectiveness of large shared_buffers on Windows

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Magnus Hagander
Okay and I think partially it might be because we don't have
> writeback
>   optimization (done in 9.6) for Windows.  However, still the broader
>   question stands that whether above data is sufficient to say that
> we
>   can recommend the settings of shared_buffers on Windows similar
> to
>   Linux?
> 
> 
> 
> 
> Based on this optimization we might want to keep the text that says large
> shared buffers on Windows aren't as effective perhaps, and just remove the
> sentence that explicitly says don't go over 512MB?

Just removing the reference to the size would make users ask a question "What 
size is the effective upper limit?"

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [RFC] Transaction management overhaul is necessary?

[Tsunakawa, Takayuki]

From: Craig Ringer [mailto:cr...@2ndquadrant.com]
> >> This was because psqlODBC starts and ends a subtransaction for each
> >> SQL statement by default to implement statement-level rollback.  And
> >> PostgreSQL creates one CurTransactionContext memory context, which is
> >> 8KB, for each subtransaction and retain them until the top transaction
> ends.
> 
> Surely that's where to start then. Find a way to pool and re-use, fully
> release, or otherwise be done with transaction contexts for released
> savepoints.

Yes, I'll investigate this.  Any reference information would be appreciated on 
why the CurTransactionContexts had to be retained, and whether it's difficult 
to circumvent.


> You can control transaction level rollback in psqlODBC directly. You do
> not need to fall back to the old protocol. Check the driver options.

That driver option is Protocol=7.4-1.  The name is misleading, as the driver 
now ignores version part (7.4), and interprets 1 as transaction-rollback.


> Right. We can't just fire off each statement wrapped in SAVEPOINT and RELEASE
> SAVEPOINT because we need to get the result of the statement and decide
> whether to ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT. It only requires
> two round trips if you shove the SAVEPOINT in with the intended statement,
> but it's still messy.
> 
> I'd like to see an alternative statement with semantics more akin to COMMIT
> - which automatically into ROLLBACK if the tx is aborted.
> COMMIT SAVEPOINT would be too confusing since it's not truly committed.
> I don't know what to call it. But basically something that does RELEASE
> SAVEPOINT [named savepoint] unless the subxact is in aborted state, in which
> case it does ROLLBACK TO [named savepoint].
> Bonus points for letting it remember the last savepoint created and use
> that.
> 
> Furthermore, we should really add it on the protocol level so drivers can
> send subtransaction control messages more compactly, without needing to
> go through the parser etc, and without massively spamming the logs. For
> this purpose savepoint names would be internally generated so the driver
> wouldn't have to send them. We'd log savepoint boundaries when transaction
> logging was enabled. Since the client would send the first such protocol
> request we could do it on the sly without a protocol version bump; clients
> could just check server version and not use the new messages for older
> servers. If they send it to an older server they get a protocol error, which
> is fine.

I'm simply thinking of proposing a new GUC, something like "SET auto_rollback = 
{none | statement | transaction}", where none is the default and traditional 
behavior.


> > You should to implement a CALL statement - that can be independent on
> > outer transaction. The behave inside procedure called by CALL
> > statement should be same like client side - and there you can controll
> > transactions explicitly without nesting.
> 
> I agree that'd be desirable. Top level "procedures" are necessary for this,
> really.
> 
> This would also enable us to return multiple result sets.
> 
> We'd probably have to start at least one small read-only tx for the initial
> cache access to look up the proc and set everything up, but if we don't
> allocate xids local transactions are super cheap.

OK, that would be a very big challenge... I can't imagine how difficult it will 
be now.  But supporting the stored procedure with CALL statement would be a 
wall to overcome.


> However, I think trying to tackle the memory context bloat reported upthread
> would be a more effective starting point since it immediately targets the

Yes, I think I'll address this.  Maybe I'll start different threads for each 
topic:

1. Memory context bloat
2. Statement-level rollback
3. Stored procedures where transactions can be ended and started

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Remove the comment on the countereffectiveness of large shared_buffers on Windows

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Amit Kapila
> Okay, not a problem.  However, I am not sure the results in this thread
> are sufficient proof as for read-only tests, there is no noticeable win
> by increasing shared buffers and read-write tests seems to be quite short
> (60 seconds) to rely on it.

I think the reason why increasing shared_buffers didn't give better performance 
for read-only tests than you expect is that the relation files are cached in 
the filesystem cache.  The purpose of this verification is to know that the 
effective upper limit is not 512MB (which is too small now), and I think the 
purpose is achieved.  There may be another threshold, say 32GB or 128GB, over 
which the performance degrades due to PostgreSQL implementation, but that's 
another topic which also applies to other OSes.

How about 3 minutes for read-write tests?  How long do you typically run?

Regards
Takayuki Tsunakawa





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas
> Let me try to be more clear.  I will not commit this patch if it is not
> properly commented.  I doubt that anyone else will, either.
> 
> The fact that those code changes already exist in 9.4+ is not a reason to
> back-port them to earlier releases without a proper explanation of why we
> are doing it.  Very possibly, we should also improve the comments in newer
> branches so that future authors don't reintroduce whatever bugs were fixed
> by these changes.  But whether we do that or not, I am not going to commit
> uncommented patches to complex code in order to fix obscure bugs in
> 3+-year-old branches.  I think that is a non-starter.
> 

OK, although I'm not perfectly sure what to add as a comment, I added an 
example scenario as a comment because I thought a concrete situation helps to 
understand the existing two paragraphs.  Is this good?

Regards
Takayuki Tsunakawa



cascading_standby_stuck_v2.patch
Description: cascading_standby_stuck_v2.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Mithun Cy
> Thanks, my concern is suppose you have 3 server in cluster A(new version),
> B(new version), C(old version). If we implement as above only new servers
> will send ParameterStatus message to indicate what type of server we are
> connected. Server C will not send same. So we will not be able to use new
> feature "failover to new master" for such a kind of cluster.

No, the streaming replication requires the same major release for all member 
servers, so there's no concern about the mixed-version cluster.

Sorry, pmState can only be used in postmaster.  In our context, postgres can 
use RecoveryInProgress().  Anyway, in addition to the reduced round trip, the 
libpq code would be much simpler.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Amit Kapila
> It looks like the code in 9.3 or later version uses the recptr as the target
> segment location
> (targetSegmentPtr) whereas 9.2 uses recptr as beginning of segment (readOff
> = 0;).  If above understanding is right then it will set different values
> for latestPagePtr in 9.2 and 9.3 onwards code.
> 

In 9.2, the relevant variable is not recptr but recaddr.  recaddr in 9.2 and 
recptr in later releases point to the beginning of a page just read, which is 
not always the beginning of the segment (targetSegmentPtr).

Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Mithun Cy
> If you are suggesting me to change in protocol messages, I think that would
> not be backward compatible to older version servers. I also think such level
> of protocol changes will not be allowed. with connection status
> CONNECTION_SETENV used for protocol version 2.0 setup, we sent some query
> like "select pg_catalog.pg_client_encoding()" for same. So I think using
> "SELECT pg_is_in_recovery()" should be fine.

No, there's no concern about compatibility.  Please look at this:

https://www.postgresql.org/docs/devel/static/protocol-flow.html#PROTOCOL-ASYNC

[Excerpt]

ParameterStatus messages will be generated whenever the active value changes 
for any of the parameters the backend believes the frontend should know about. 
Most commonly this occurs in response to a SET SQL command executed by the 
frontend, and this case is effectively synchronous — but it is also possible 
for parameter status changes to occur because the administrator changed a 
configuration file and then sent the SIGHUP signal to the server. Also, if a 
SET command is rolled back, an appropriate ParameterStatus message will be 
generated to report the current effective value.

At present there is a hard-wired set of parameters for which ParameterStatus 
will be generated: they are server_version, server_encoding, client_encoding, 
application_name, is_superuser, session_authorization, DateStyle, 
IntervalStyle, TimeZone, integer_datetimes, and standard_conforming_strings. 
(server_encoding, TimeZone, and integer_datetimes were not reported by releases 
before 8.0; standard_conforming_strings was not reported by releases before 
8.1; IntervalStyle was not reported by releases before 8.4; application_name 
was not reported by releases before 9.0.) Note that server_version, 
server_encoding and integer_datetimes are pseudo-parameters that cannot change 
after startup. This set might change in the future, or even become 
configurable. Accordingly, a frontend should simply ignore ParameterStatus for 
parameters that it does not understand or care about.




Regards
Takayuki Tsunakawa


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Tsunakawa, Takayuki]

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 down to
> > disk, not in writing out shared_buffers itself.  The latter is
> > probably a single-digit number of gigabytes, or maybe double-digit.
> > The former might be a lot more, and the write of the pgstat file may
> > back up behind it.  I've seen cases where an 8kB buffered write from
> > Postgres takes tens of seconds to complete because the OS buffer cache
> > is already saturated with dirty data, and the stats files could easily
> > be a lot more than that.
> 
> I think this is mostly FUD, because we don't fsync the stats files.  Maybe
> we should, but we don't today.  So even if we have managed to get the system
> into a state where physical writes are heavily backlogged, that's not a
> reason to assume that the stats collector will be unable to do its thing
> promptly.  All it has to do is push a relatively small amount of data into
> kernel buffers.

I'm sorry for my late reply, yesterday was a national holiday in Japan.

It's not FUD.  I understand you hit the slow stats file write problem during 
some regression test.  You said it took 57 seconds to write the stats file 
during the postmaster shutdown.  That caused pg_ctl stop to fail due to its 60 
second timeout.  Even the regression test environment suffered from the trouble.

Regards
Takayuki Tsunakawa






-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers