Re: [HACKERS] PATCH: Make pg_stop_backup() archive wait optional

[Tsunakawa, Takayuki]

> From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Michael Paquier
> On Fri, Mar 17, 2017 at 1:47 PM, Tsunakawa, Takayuki
> <tsunakawa.ta...@jp.fujitsu.com> wrote:
> > BTW, does the developer of each feature have to modify the catalog version
> in catversion.h?  It's a bit annoying to see the patch application failure
> on catversion.h.
> 
> Committers take care of this part.

I understood the committer modifies the catalog version based on the patch 
content, so the patch submitter doesn't have to modify it.  I'm relieved.

> > Isn't it enough to modify the catalog version only when
> alpha/beta/RC/final versions are released?
> 
> That's useful at least for developers to bump it even during a development
> cycle as you can notice with a hard failure at startup if a data folder
> you have created is compatible with the new binaries or not.

Oh, you're absolutely right.

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: Make pg_stop_backup() archive wait optional

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of David Steele
> The attached patch udpates the docs per your suggestion and has been rebased
> on master at d69fae2.

I made this ready for committer.  The patch applied except for catversion.h, 
the patch content looks good, and the target test passed as follows:

I set archive_command to 'sleep 10'.  pg_stop_backup() with archive wait took 
about 10 seconds, emitting NOTICE messages.

postgres=# select pg_stop_backup(false, true);
NOTICE:  pg_stop_backup cleanup done, waiting for required WAL segments to be 
archived

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
  pg_stop_backup
---
 (0/BF8,"START WAL LOCATION: 0/B28 (file 0001000B)+
 CHECKPOINT LOCATION: 0/B60   +
 BACKUP METHOD: streamed  +
 BACKUP FROM: master  +
 START TIME: 2017-03-17 13:26:47 JST  +
 LABEL: a +
 ","")
(1 row)


pg_stop_backup() without archive wait returned immediately without displaying 
any NOTICE messages.


postgres=# select pg_stop_backup(false, false);
  pg_stop_backup
---
 (0/D000130,"START WAL LOCATION: 0/D28 (file 0001000D)+
 CHECKPOINT LOCATION: 0/D60   +
 BACKUP METHOD: streamed  +
 BACKUP FROM: master  +
 START TIME: 2017-03-17 13:29:46 JST  +
 LABEL: a +
 ","")
(1 row)


BTW, does the developer of each feature have to modify the catalog version in 
catversion.h?  It's a bit annoying to see the patch application failure on 
catversion.h.  Isn't it enough to modify the catalog version only when 
alpha/beta/RC/final versions are released?


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] Crash on promotion when recovery.conf is renamed

[Tsunakawa, Takayuki]

From: David Steele [mailto:da...@pgmasters.net]
> Any idea when you'll have a chance to review?

I'll do it by early next week.

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] Defaulting psql to ON_ERROR_ROLLBACK=interactive

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Peter van
> Hardenberg
> I suggest we update the default of ON_ERROR_ROLLBACK to interactive for
> 10.0.

+1

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: Configurable file mode mask

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of David Steele
> > But it might be worth thinking about whether we want to encourage
> > people to do manual chmod's at all; that's fairly easy to get wrong,
> > particularly given the difference in X bits that should be applied to
> > files and directories.  Another approach that could be worth
> > considering is a PGC_POSTMASTER GUC with just two states (group access
> > or not) and make it the postmaster's responsibility to do the
> > equivalent of chmod -R to make the file tree match the GUC.  I think
> > we do a tree scan anyway for other purposes, so correcting any wrong
> > file permissions might not be much added work in the normal case.
> 
> The majority of scanning is done in recovery (to find and remove unlogged
> tables) and I'm not sure we would want to add that overhead to normal startup.

I'm on David's side, too.  I don't postmaster to always scan all files at 
startup.

On the other hand, just doing "chmod -R $PGDATA" is not enough, because chmod 
doesn't follow the symbolic links.  Symbolic links are used for pg_tblspc/* and 
pg_wal at least.  FYI, MySQL's manual describes the pithole like this:

https://dev.mysql.com/doc/refman/8.0/en/changing-mysql-user.html

2. Change the database directories and files so that user_name has privileges 
to read and write files in them (you might need to do this as the Unix root 
user): 
shell> chown -R user_name /path/to/mysql/datadir


If you do not do this, the server will not be able to access databases or 
tables when it runs as user_name. 

If directories or files within the MySQL data directory are symbolic links, 
chown -R might not follow symbolic links for you. If it does not, you will also 
need to follow those links and change the directories and files they point to. 



I think we also need to describe the procedure carefully.  That said, it would 
be best to make users aware of a configuration alternative (group access) with 
enough documentation when they first build the database or upgrade the database 
cluster.  Just describing the alternative only in initdb reference page would 
result in being unaware of the better configuration, like --data-checksum.

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: Configurable file mode mask

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of David Steele
> Sure, but having the private key may allow them to get new data from the
> server as well as the data from the backup.

You are right.  My rough intent was that the data is stolen anyway.  So, I 
thought it might not be so bad to expect to be able to back up the SSL key file 
in $PGDATA together with the database.  If it's bad, then the default value of 
ssl_key_file (=$PGDATA/ssl.key) should be disallowed.


> > https://www.postgresql.org/docs/devel/static/ssl-tcp.html
> >
> > "On Unix systems, the permissions on server.key must disallow any access
> to world or group; achieve this by the command chmod 0600 server.key.
> Alternatively, the file can be owned by root and have group read access
> (that is, 0640 permissions). That setup is intended for installations where
> certificate and key files are managed by the operating system. The user
> under which the PostgreSQL server runs should then be made a member of the
> group that has access to those certificate and key files."
> >
> > In the latter case, the file owner is root and the permission is 0640.
> At first I was a bit confused and misunderstood that the PostgreSQL user
> account and the backup OS user needs to belong to the same OS group.  But
> that's not the case.  The group of the key file can be, for example,
> "ssl_cert", the PostgreSQL user account belongs to the OS group "ssl_cert"
> and "dba", and the backup OS user only belongs to "backup."  This can prevent
> the backup OS user from reading the key file.  I think it would be better
> to have some explanation with examples in the above section.
> 
> If the backup user is in the same group as the postgres user and in the
> ssl_cert group then backups of the certs would be possible using group reads.
> Restores will be a little tricky, though, because of the need to set
> ownership to root.  The restore would need to be run as root or the
> permissions fixed up after the restore completes.

Yes, but I thought, from the following message,  that you do not recommend that 
the backup user be able to read the SSL key file.  So, I proposed to describe 
the example configuration to achieve that -- postgres user in dba and ssl_cert 
group, and a separate backup user in only dba group.

> >> It seems to me that it would be best to advise in the docs that these
> >> files should be relocated if they won't be readable by the backup user.
> >> In any event, I'm not convinced that backing up server private keys
> >> is a good idea.


> To be clear, the default for this patch is to leave permissions exactly
> as they are now.  It also provides alternatives that may or not be useful
> in all cases.

So you think there are configurations that may be useful or not, don't you?  
Adding a new parameter could possibly complicate what users have to consider.  
Maximal flexibility could lead to insecure misuse.  So I think it would be 
better to describe secure and practical configuration examples in the SSL 
section and/or the backup chapter.  The configuration factor includes whether 
the backup user is different from the postgres user, where the SSL key file is 
placed, the owner of the SSL key file, whether the backup user can read the SSL 
key file.

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: Configurable file mode mask

[Tsunakawa, Takayuki]

From: David Steele [mailto:da...@pgmasters.net]
> >> 3.The default location of the SSL key file is $PGDATA, so the permission
> of the key file is likely to become 0640.  But the current postgres requires
> it to be 0600.  See src/backend/libpq/be-secure-openssl.c.
> >
> > Yes, that needs to be addressed.  There was discussion on another
> > thread that it would be useful to support the SSL key file having
> > group read access, but since this patch is handling the other files it
> > seems like it would make sense to do that change here also.
> 
> Perhaps, but since these files are not setup by initdb I'm not sure if we
> should be handling their permissions.  This seems to be a distro-specific
> issue.
> 
> It seems to me that it would be best to advise in the docs that these files
> should be relocated if they won't be readable by the backup user.
> In any event, I'm not convinced that backing up server private keys is a
> good idea.

Maybe so, but it's convenient to be able to store the key and certificate files 
in $PGDATA and back them up together.  If the database backup were stolen 
through the compromised backup OS user, then the malicious person can read the 
data anyway regardless of whether the key file is there or not.  That's because 
the key is not for encrypting data at rest.

Related to this, please see:

https://www.postgresql.org/docs/devel/static/ssl-tcp.html

"On Unix systems, the permissions on server.key must disallow any access to 
world or group; achieve this by the command chmod 0600 server.key. 
Alternatively, the file can be owned by root and have group read access (that 
is, 0640 permissions). That setup is intended for installations where 
certificate and key files are managed by the operating system. The user under 
which the PostgreSQL server runs should then be made a member of the group that 
has access to those certificate and key files."

In the latter case, the file owner is root and the permission is 0640.  At 
first I was a bit confused and misunderstood that the PostgreSQL user account 
and the backup OS user needs to belong to the same OS group.  But that's not 
the case.  The group of the key file can be, for example, "ssl_cert", the 
PostgreSQL user account belongs to the OS group "ssl_cert" and "dba", and the 
backup OS user only belongs to "backup."  This can prevent the backup OS user 
from reading the key file.  I think it would be better to have some explanation 
with examples in the above section.


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: Configurable file mode mask

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of David Steele
> PostgreSQL currently requires the file mode mask (umask) to be 0077.
> However, this precludes the possibility of a user in the postgres group
> performing a backup (or whatever).  Now that
> pg_start_backup()/pg_stop_backup() privileges can be delegated to an
> unprivileged user, it makes sense to also allow a (relatively) unprivileged
> user to perform the backup at the file system level as well.

I'd like to help review this.  First, let me give some questions and comments.


1.What's the concrete use case of this feature?  Do you intend to extend the 
concept of multiple DBAs to the full range of administration of a single 
database instance, or just multiple OS users for database backup?
If you think that multiple OS user support is desirable to reduce the 
administration burdon on a single person, then isn't the automated backup 
sufficient (such as with cron)? 

2.Backup should always be considered with recovery.  If you allow another OS 
user to back up the database, can you allow him to recover the database as well?
For example, assume the PostgreSQL user account (the OS user who does initdb 
and pg_ctl start/stop) is dba1, and dba2 backs up the database using tar or 
cpio.
When dba2 restores the backup, the owner of the database cluster becomes dba2.  
If the file permission only allows one user to write the file, then dba1 can't 
start the instance.

3.The default location of the SSL key file is $PGDATA, so the permission of the 
key file is likely to become 0640.  But the current postgres requires it to be 
0600.  See src/backend/libpq/be-secure-openssl.c.

4.I've seen a few users to place .pgpass file in $PGDATA and set the 
environment variable PGPASSFILE to point to it.  They expect it to be back up 
with other database files.  So I'm afraid the permission of .pgpass file also 
becomes 0640 some time.  However, the current code requires it to be 0600.  See 
src/interface/libpq/fe-connect.c.

5.I think some explanation about the concept of multiple OS users is necessary, 
such as here:

16.1. Short Version
https://www.postgresql.org/docs/devel/static/install-short.html

18.2. Creating a Database Cluster
https://www.postgresql.org/docs/devel/static/creating-cluster.html


[FYI]
Oracle instructs the user, during the software installation, to put "umask 022" 
in ~/.bashrc or so.
MySQL's files in the data directory appears to be 0640.

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 Ashutosh Sharma
> To start with, I ran the regression test-suite and didn't find any failures.
> But, then I am not sure if huge_pages are getting used or not. However,
> upon checking the settings for huge_pages and I found it as 'on'. I am
> assuming, if huge pages is not being used due to shortage of large pages,
> it should have fallen back to non-huge pages.

You are right, the server falls back to non-huge pages when the large pages run 
short.

> I also ran the pgbench tests on read-only workload and here are the results
> I got.
> 
> pgbench -c 4 -j 4 - T 600 bench
> 
> huge_pages=on, TPS = 21120.768085
> huge_pages=off, TPS = 20606.288995

Thanks.  It's about 2% improvement, which is the same as what I got.


From: Thomas Munro [mailto:thomas.mu...@enterprisedb.com]
> The line beginning 'Huge pages are known as...' has been accidentally
> duplicated.

Oops, how careless I was.  Fixed.  As Ashutosh referred, I added a very simple 
suggestion to use Windows Group Policy tool.

Regards
Takayuki Tsunakawa



win_large_pages_v9.patch
Description: win_large_pages_v9.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] [bug fix] dblink leaks unnamed connections

[Tsunakawa, Takayuki]

Hello,

dblink fails to close the unnamed connection as follows when a new unnamed 
connection is requested.  The attached patch fixes this.



postgres=# select count(*) from pg_stat_activity;
 count
---
 1
(1 row)

postgres=# select dblink_connect('dbname=postgres');
 dblink_connect

 OK
(1 row)

postgres=# select count(*) from pg_stat_activity;
 count
---
 2
(1 row)

postgres=# select dblink_connect('dbname=postgres');
 dblink_connect

 OK
(1 row)

postgres=# select count(*) from pg_stat_activity;
 count
---
 3
(1 row)


Regards
Takayuki Tsunakawa



dblink_leak_unnamed_conn.patch
Description: dblink_leak_unnamed_conn.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] some dblink refactoring

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tsunakawa,
> Takayuki
> How about applying the attached small patch for another refactoring?  This
> merely changes makeStringInfo() to initStringInfo() at two sites just other
> places in the same file.  makeStringInfo() on the function local variables
> leaves memory for StringInfoData allocated unnecessarily (which may be
> automatically reclaimed some time after.)

Sorry, I forgot to attach the file.

Regards
Takayuki Tsunakawa




dblink_strinfo_refactor.patch
Description: dblink_strinfo_refactor.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] some dblink refactoring

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Peter Eisentraut
> Here is a patch to refactor some macro hell in dblink.
> 
> This patch was discussed in the background sessions thread as a prerequisite
> for some work there, but I figure I'll make a separate thread for it to
> give everyone interested in dblink a chance to respond separate from the
> other thread.

I changed the status to ready for committer.  The patch applied cleanly, passed 
the regression test (make installcheck in contrib/dblink/), and the code looks 
perfect.

How about applying the attached small patch for another refactoring?  This 
merely changes makeStringInfo() to initStringInfo() at two sites just other 
places in the same file.  makeStringInfo() on the function local variables 
leaves memory for StringInfoData allocated unnecessarily (which may be 
automatically reclaimed some time after.)

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] Statement-level rollback

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Robert Haas
> >> Can you provide some references on how other systems provide this feature?
> >
> > Oracle doesn't.
> 
> Really?

Sorry, my sentence was misleading.
I meant by "Oracle/MySQL doesn't" that they do not provide a configuration 
parameter or START TRANSACTION mode to choose between statement rollback and 
transaction rollback.  They just rolls back the failed statement.  I wish 
Postgres could behave the same way.

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] Statement-level rollback

[Tsunakawa, Takayuki]

From: David Steele [mailto:da...@pgmasters.net]
> Whatever the merits of this patch, it's a pretty major behavioral change
> with a large potential impact.  Even if what is enumerated here is the full
> list (which I doubt), it's pretty big.
> 
> Given that this landed on March 28 with no discussion beforehand, I recommend
> that we immediately move this patch to the 2017-07 CF.

OK, I moved it to 2017-7.  I will participate in the review of existing 
patches.  In parallel with that, I'll keep developing this feature and 
sometimes submit revised patches and new findings.  I'd be happy if anyone 
could give feedback then.

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] Statement-level rollback

[Tsunakawa, Takayuki]

From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> 1. The argument for this is mostly, if not entirely, "application
> compatibility".  But it won't succeed at providing that if every BEGIN has
> to be spelled differently than it would be on other DBMSes.
> Therefore there is going to be enormous pressure to allow enabling the
> feature through a GUC, or some other environment-level way, and as soon
> as we do that we've lost.

I thought so, too.  I believe people who want to migrate from other DBMSs would 
set the GUC in postgresql.conf, or with ALTER DATABASE/USER just for 
applications which are difficult to modify.

> 2. The proposed feature would affect the internal operation of PL functions,
> so that those would need to become bulletproof against being invoked in
> either operating environment.  Likewise, all sorts of intermediate tools
> like connection poolers would no doubt be broken if they don't know about
> this and support both modes.  (We would have to start by fixing postgres_fdw
> and dblink, for instance.)

Yes, I'm going to modify the PL's behavior.  I'll also check the dblink and 
postgres_fdw as well.  In addition, I'll have a quick look at the code of 
pgpool-II and pgBouncer to see how they depend on the transaction state.  I'll 
run the regression tests of contribs, pgpool-II and pgBouncer with 
default_transaction_rollback_scope set to 'statement'.

But I don't see how badly the statement-level rollback affects those features 
other than PL.  I think the only relevant thing to those client-side programs 
is whether the transaction is still running, which is returned with 
ReadyForQuery.  Both of statement-level rollback and the traditional behavior 
leave the transaction running when an SQL statement fails.  Server-side 
autocommit differs in that respect.

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] Statement-level rollback

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Peter Eisentraut
> On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
> > 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.
> 
> Can you provide some references on how other systems provide this feature?

Oracle doesn't.

SQL Server provides like this:

SET XACT_ABORT
https://msdn.microsoft.com/en-us/library/ms188792.aspx

MySQL doesn't.  BTW, MySQL enables changing autocommit mode with SET statement:

16.5.2.2 autocommit, Commit, and Rollback
https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html



And above all, I've found EnterpriseDB supports statement-level rollback with 
GUC!  So PostgreSQL should be able to do.

https://www.edbpostgres.com/docs/en/9.6/asguide/EDB_Postgres_Advanced_Server_Guide.1.17.html#pID0E0QUD0HA


edb_stmt_level_tx is set to TRUE, then an exception will not automatically roll 
back prior uncommitted database updates. If edb_stmt_level_tx is set to FALSE, 
then an exception will roll back uncommitted database updates.

Note: Use edb_stmt_level_tx set to TRUE only when absolutely necessary, as this 
may cause a negative performance impact.



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] Statement-level rollback

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane
> "Tsunakawa, Takayuki" <tsunakawa.ta...@jp.fujitsu.com> writes:
> > As I stated here and at the PGConf.ASIA developer meeting last year,
> > I'd like to propose statement-level rollback feature.
> 
> I do not really see how this would ever get past the compatibility problems
> that forced us to give up on server-side autocommit years ago.

Could you tell me more about that problem?  What kind of incompatibility would 
this feature introduce?

> If you want to provide a client-side facility for this, perhaps that could
> fly.

Do you mean a feature of psqlODBC that implicitly issues SAVEPOINT and RELEASE 
SAVEPOINT for each SQL statement?  One reason I want to implement the feature 
is to avoid eliminate those round-trips for performance.  Or, do you mean a 
client-side connection parameter like "rollback_scope={transaction | 
statement}?"  Yes, I'll implement it for major client drivers so that the 
driver issues "SET SESSION CHARACTERISTICS FOR TRANSACTION ROLLBACK SCOPE 
{TRANSACTION | STATEMENT}" upon connection.  psqlODBC has already a connection 
parameter, Protocol, for that purpose.

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] 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] 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

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

[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] 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

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 "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: 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

[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] 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] 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]

Hello, Amit, Magnus,

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


From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Amit Kapila
> PGSharedMemoryReAttach is called after the startup of new process whereas
> pgwin32_ReserveSharedMemoryRegion is called before the new process could
> actually start.  Basically, pgwin32_ReserveSharedMemoryRegion is used to
> reserve shared memory for each backend, so calling VirtualAlloc there should
> follow spec for huge pages.  If you have some reason for not using, then
> it is not clear from your reply, can you try to explain in detail.

OK.  The processing takes place in the following order:

1. postmaster calls CreateProcess() to start a child postgres in a suspended 
state.
2. postmaster calls VirtualAlloc(MEM_RESERVE) in 
pgwin32_ReserveSharedMemoryRegion() to reserve the virtual address space in the 
child to secure space for the shared memory.  This call just affects the 
virtual address space and does not allocate physical memory.  So the large page 
is still irrelevant.
3. postmaster resumes execution of the child postgres.
4. The child postgres calls VirtualFree(MEM_RESERVE)  in 
PGSharedMemoryReAttach() to release the reserved virtual address space.  Here, 
the effect of VirtualAlloc() is invalidated anyway.
5. The child process calls MapViewOfFile() in PGSharedMemoryReAttach() to map 
the shared memory at the same address.  Hereafter, the large page option 
specified in CreateFileMapping() call is relevant.


> + if (!LookupPrivilegeValue(NULL, SE_LOCK_MEMORY_NAME, )) {
> + CloseHandle(hToken); ereport(elevel, (errmsg("could not enable Lock
> + pages in memory user right: error
> code %lu", GetLastError()),
> + errdetail("Failed system call was LookupPrivilegeValue."))); return
> + FALSE; }
> 
> The order of closing handle and ereport is different here than other places
> in the same function.  If there is no specific reason for doing so, then
> keep the order consistent.

You are right, I modified the patch to call CloseHandle() after ereport() so 
that ereport() CloseHandle() wouldn't change the error code for ereport().  
That's the order used in postmaster.c.


> I have tried to test v4 version of the patch and it is always failing in
> below error after call to AdjustTokenPrivileges:
> 
> + if (GetLastError() != ERROR_SUCCESS)
> + {
> + if (GetLastError() == ERROR_NOT_ALL_ASSIGNED) ereport(elevel,
> + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
> + errmsg("could not enable Lock pages in memory user right"),
> + errhint("Assign Lock pages in memory user right to the Windows user
> account which runs PostgreSQL.")));
> 
> I have ensured that user used to run PostgreSQL has "Lock pages in memory"
> privilege/rights.  I have followed msdn tips [1] to do that (restarted the
> m/c after assigning privilege).  I am using Win7. Can you share the steps
> you have followed to test and your windows m/c details?
> 
> [1] -
> https://msdn.microsoft.com/en-us/library/windows/desktop/ff961911(v=vs
> .85).aspx

I succeeded by following the same procedure using secpol.msc on Win10, running 
64-bit PostgreSQL.  I started PostgreSQL as a Windows service because it's the 
normal way, with the service account being a regular Windows user account(i.e. 
not LocalSystem).

But... I failed to start PostgreSQL by running "pg_ctl start" from a command 
prompt, receiving the same error message as you.  On the other hand, I could 
start PostgreSQL on a command prompt with administrative privileges 
(right-click on "Command prompt" from the Start menu and select "Run as an 
administrator" in the menu.  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.

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?

Regards
Takayuki Tsunakawa



win_large_pages_v6.patch
Description: win_large_pages_v6.patch
#include 
#include 

int main()
{
HANDLE hToken;
PTOKEN_PRIVILEGES pTokenPrivileges;
char szPrivilegeName[256];
char szDisplayName[256];
DWORD dwLength;
DWORD dwLanguageId;
DWORD i;

if (!OpenProcessToken(GetCurrentProcess(), TOKEN_QUERY, )) {
return 1;
}

GetTokenInformation(hToken, TokenPrivileges, NULL, 0, );

pTokenPrivileges = (PTOKEN_PRIVILEGES)LocalAlloc(LPTR, dwLength);
if (pTokenPrivileges == NULL) {
CloseHandle(hToken);
return 1;
}

GetTokenInformation(hToken, TokenPrivileges, pTokenPrivileges, dwLength, 
);


for (i = 0; i < pTokenPrivileges->PrivilegeCount; i++) {

dwLength = 

Re: [HACKERS] Supporting huge pages on Windows

[Tsunakawa, Takayuki]

From: Thomas Munro [mailto:thomas.mu...@enterprisedb.com]
> In the Microsoft documentation I've seen, the privilege's name is always
> written as "Lock Pages in Memory" (note: "Pages" plural, and with initial
> capital letters).  It's quite hard to parse the sentence otherwise!  How
> about this?
> 
>  Huge pages are known as large pages on Windows.  To use them, you
> need to
>  assign the user right Lock Pages in Memory to the Windows user
> account
>  that runs PostgreSQL.
> 
> + ereport(elevel,
> + (errmsg("could not enable Lock pages in memory user right: error
> code %lu", GetLastError()),
> + errdetail("Failed system call was OpenProcessToken.")));
> 
> Same comment about capitalisation of the privilege name in this and other
> error messages.

Thanks, your sentences are surely better.  Fixed.

Regards
Takayuki Tsunakawa



win_large_pages_v5.patch
Description: win_large_pages_v5.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: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Magnus Hagander
> For the pg_ctl changes, we're going from removing all privilieges from the
> token, to removing none. Are there any other privileges that we should be
> worried about? I think you may be correct in that it's overkill to do it,
> but I think we need some more specifics to decide that.

This page lists the privileges.  Is there anyhing you are concerned about?

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



> Also, what happens with privileges that were granted to the groups that
> were removed? Are they retained or lost?

Are you referring to the privileges of Administrators and PowerUsers that 
pg_ctl removes?  They are lost.  The Windows user account who actually runs 
PostgreSQL needs SeLockMemory privilege.


> Should we perhaps consider having pg_ctl instead *disable* all the
> privileges (rather than removing them), using AdjustTokenPrivileges? As
> a middle ground?

Sorry, I may misunderstand what you are suggesting, but AdjustTokenPrivilege() 
cannot enable the privilege which is not assigned to the user.  Anyway, I think 
it's the user's responsibility (and freedom) to assign desired privileges, and 
pg_ctl's disabling all privileges is overkill.


> +errdetail("Failed system call was %s,
> error code %lu", "LookupPrivilegeValue", GetLastError(;
> 
> 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.

OK, modified.

> When AdjustTokenPrivileges() returns, you explicitly check for
> ERROR_NOT_ALL_ASSIGNED, which is good. But we should probably also
> explicitly check for ERROR_SUCCESS for that case. Right now that's the only
> two possible options that can be returned, but in a future version other
> result codes could be added and we'd miss them. Basically, "there should
> be an else branch".

OK, modified.

> Is there a reason the error messages for AdjustTokenPrivileges() returning
> false and ERROR_NOT_ALL_ASSIGNED is different?

As mentioned in the following page, the error cause is clearly defined.  So, I 
thought it'd be better to give a specific hint message to help users 
troubleshoot the error.


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

ERROR_NOT_ALL_ASSIGNED 
The token does not have one or more of the privileges specified in the NewState 
parameter. The function may succeed with this error value even if no privileges 
were adjusted. The PreviousState parameter indicates the privileges that were 
adjusted.


> There are three repeated blocks of
> +   if (huge_pages == HUGE_PAGES_ON || huge_pages == HUGE_PAGES_TRY)
> 
> It threw me off in the initial reading, until I realized the upper levels
> of them can change the value of huge_pages.

OK, I like your code.


> I don't think changing the global variable huge_pages like that is a very
> good idea.
 
Yes, actually, I was afraid that it might be controversial to change the GUC 
value.  But I thought it may be better for "SHOW huge_pages" to reflect whether 
the huge_pages feature is effective.  Otherwise, users don't know about that.  
For example, wal_buffers behaves similarly; if it's set to -1 (default), "SHOW 
wal_buffers" displays the actual wal buffer size, not -1.  What do you think?  
Surely, the Linux code for huge_pages doesn't do that.  I'm OK with either.


From: Amit Kapila [mailto:amit.kapil...@gmail.com]
> Your version of the patch looks better than the previous one.  Don't you
> need to consider MEM_LARGE_PAGES in VirtualAllocEx call (refer
> pgwin32_ReserveSharedMemoryRegion)?  At least that is what is mentioned
> in MSDN [1].  Another point worth considering is that currently for
> VirtualAllocEx() we use PAGE_READWRITE as flProtect value, shouldn't it
> be same as used in CreateFileMapping() by patch.
> 
> 
> [1] -
> https://msdn.microsoft.com/en-us/library/windows/desktop/aa366720(v=vs
> .85).aspx

No, it's not necessary.  Please see PGSharedMemoryReAttach(), where 
VirtualFree() is called to free the reserved address space and then call 
MapViewOfFile() to allocate the already created shared memory to that area.

Regards
Takayuki Tsunakawa




win_large_pages_v4.patch
Description: win_large_pages_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] Hooks

[Tsunakawa, Takayuki]

From: David Fetter [mailto:da...@fetter.org]
> > How about putting a descriptive comment at the location where each
> > hook variable is defined, using some convention (e.g. like
> > Javadoc-style)?  A separate document such as README and wiki can fail
> > to be updated.  OTOH, if someone wants to add a new hook, we can
> > expect him to add appropriate comment by following existing hooks.
> > Using a fixed tag, e.g. "", would facilitate finding all hooks.
> 
> I like this idea, but it's a much bigger one than mine because it's
> essentially inventing (or adopting, whatever we settle on) literate
> programming for the PostgreSQL project.
> 
> https://en.wikipedia.org/wiki/Literate_programming

I didn't intend to invent a new heavy rule or tool.  I just meant comments just 
like the existing function descriptions, something like

/*
 * Hook name: Authentication hook
 * Description: ...
 * Arguments: ...
 * Return value: ...
 * Note: ...
 */


> 
> In the realm of generated documentation, we do have a doxygen
> https://doxygen.postgresql.org/ for the project, but I haven't really found
> it helpful thus far.

Me, too.


> At the moment, our practice is that (most--hooks being an exception)
> user-facing features must come with with user-facing docs which are written
> separately from the source code implementing them.

OK.  Anyway, if we can see in the PostgreSQL documentation what hooks are 
available, it would be the best.  I imagine you meant adding a new chapter 
under the part "V. Server Programming".

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] Hooks

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Jim Nasby
> AFAIK there's no way to get a list of hooks today, short of something like
> `git grep hook`. I think a simple list of what hooks we have, when they
> fire and where to find them in code would be sufficient.

How about putting a descriptive comment at the location where each hook 
variable is defined, using some convention (e.g. like Javadoc-style)?  A 
separate document such as README and wiki can fail to be updated.  OTOH, if 
someone wants to add a new hook, we can expect him to add appropriate comment 
by following existing hooks.  Using a fixed tag, e.g. "", would 
facilitate finding all hooks.

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] Questions regarding signal handler of postmaster

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tatsuo Ishii
> In postmaster.c signal handler pmdie() calls ereport() and
> errmsg_internal(), which could call palloc() then malloc() if necessary.
> Because it is possible that pmdie() gets called while
> malloc() gets called in postmaster, I think it is possible that a deadlock
> situation could occur through an internal locking inside malloc(). I have
> not observed the exact case in PostgreSQL but I see a suspected case in
> Pgpool-II. In the stack trace #14, malloc() is called by Pgpool-II. It is
> interrupted by a signal in #11, and the signal handler calls malloc() again,
> and it is stuck at #0.

I encountered that problem with postmaster and fixed it in 9.4.0 (it's not 
back-patched to earlier releases because it's relatively complex).

https://www.postgresql.org/message-id/20DAEA8949EC4E2289C6E8E58560DEC0@maumau


[Excerpt from 9.4 release note]
During crash recovery or immediate shutdown, send uncatchable termination 
signals (SIGKILL) to child processes that do not shut down promptly (MauMau, 
Álvaro Herrera)
This reduces the likelihood of leaving orphaned child processes behind after 
postmaster shutdown, as well as ensuring that crash recovery can proceed if 
some child processes have become “stuck”.

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] Trivial ecpg bug which can cause memory overrun

[Tsunakawa, Takayuki]

Hello,

While investigating some other issue, we found a trivial bug of ecpg.  The 
attached is a fix for that.

If you specify an input file which ends with "." (e.g. run "ecpg file."), ecpg 
writes one byte past the end of the allocated memory.

In addition, the following statement is misleading.  Some people may think that 
file.ec.c will be converted to a.ec.c.  But the actual behavior is that it is 
converted to file.c.  So I clarified the paragraph a bit.

"If the extension of the input file is not .pgc, then the output file name is 
computed by appending .c to the full file name."

Regards
Takayuki Tsunakawa



ecpg_outfile.patch
Description: ecpg_outfile.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] Declarative partitioning - another take

[Tsunakawa, Takayuki]

From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp]
> On 2016/12/09 10:09, Tsunakawa, Takayuki wrote:
> > Another requirement was subpartitioning.  Will this be possible with the
> current infrastructure, or does this need drastic change?
> 
> It does support sub-partitioning, although the syntax is a bit different.

Super great!  I'm excited to try the feature when I have time.  I hope I can 
contribute to the quality by find any bug.

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] Declarative partitioning - another take

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Alexander
> Korotkov
> Yes. Getting at least some of this features committed to v10 would be great
> and improve partitioning usability a lot.

I'm sorry for not contributing to the real partitioning feature, but I'm really 
looking forward to seeing the efficient plan-time and run-time partition 
pruning implemented in v10.  Recently, we failed to acquire a customer because 
they could not achieve their performance goal due to the slow partition pruning 
compared to Oracle.  The batch app prepares a SELECT statement against a 
partitioned table, then executes it millions of time with different parameter 
values.  It took a long time to process Bind messages.

Another requirement was subpartitioning.  Will this be possible with the 
current infrastructure, or does this need drastic change?

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] UNDO and in-place update

[Tsunakawa, Takayuki]

From: Robert Haas [mailto:robertmh...@gmail.com]
> On Thu, Nov 24, 2016 at 2:32 AM, Tsunakawa, Takayuki
> <tsunakawa.ta...@jp.fujitsu.com> wrote:
> > IMHO, overall, there should be pros and cons of the current approach and
> the new UNDo one (like Oracle?), depending on the workload.  Under
> update-heavy workload, the UNDO method may be better.  OTOH, under the
> mostly-INSERT workload (like data warehouse?), the current method will be
> better because it writes no log for UNDO.
> 
> The foreground operation will complete more quickly, because it won't have
> to write UNDO.  On the other hand, you'll have to set hint bits later, as
> well as freeze, which may be more expensive than writing UNDO by the time
> all is said and done.  Whether it's better to do pay a foreground tax
> immediately or to do deferred work at a later time depends on things like
> whether you have quiet times during which you can catch up on the deferred
> work ... but the number of users who have gotten unpleasant surprises due
> to autovacuum kicking in during a busy period is not small.

I see.  autovacuum is certainly almost unpredictable, at least for those who 
are not aware of its existence and tuning.  Recently, one of our customers 
faced the inability to perform INSERTs because of xid wraparound.  
Their workload is INSERT-heavy, and (inefficiently) used autocommit to insert 
each row, which resulted in the xid consumption faster than the slow xid 
wraparound autovacuum.


> > Furthermore, it maybe the best to be able to switch the method for
> > each table and/or tablespace.  For example, in pgbench, history table
> uses the current method, and other tables use the UNDO method.  Is it time
> to introduce a pluggable storage system?
> 
> IMHO, it's past time for that.

Do you mean by "past time" that the community decided not to introduce 
pluggable storage manager?  If it's true, that's a pity.  But I remember that 
there was a discussion about pluggable storage manager at PGConf or some other 
event this year.  Or, do you mean that the current approach should be abandoned 
and the UNDO approach replace it?


> > Because PostgreSQL is a follower in the UNDO approach, I think it will
> be better to study other DBMSs well (Oracle and MySQL?).  That includes
> not only their manuals, but also whitepapers and books.  Especially, I
> expect good books to give deep knowledge on performance tuning and
> troubleshooting, from which we will be able to know the cons that Oracle's
> materials don't state.
> 
> I agree up to a point.  I think we need to design our own system as well
> as we can, not just copy what others have done.  For example, the design
> I sketched will work with all of PostgreSQL's existing index types.  You
> need to modify each AM in order to support in-place updates when a column
> indexed by that AM has been modified, and that's probably highly desirable,
> but it's not a hard requirement.  I believe that's a better approach for
> us than insisting that we have to do it in exactly the same way as some
> other system.  Now, that doesn't mean we shouldn't learn from what works
> well and poorly in other systems, but I think our goal here should be to
> chart the best way forward given PostgreSQL's existing architecture and
> its existing strengths and weaknesses, rather than to make it exactly like
> Oracle or MySQL or anything else.  Few people on this mailing list would
> say that either of those systems are categorically better than PostgreSQL;
> most, I suspect, would disagree somewhat vigorously.

Yes, agreed.  I didn't intend to just imitate Oracle/MySQL design.  I meant 
that it will be better to study in advance what trouble Oracle/MySQL design has 
caused their users, and avoid pitfalls as much as possible.  For example, when 
I ran TPC-B benchmark against Oracle and PostgreSQL, I was embarrassed by 
frequent deadlocks in Oracle.  It took some time for me to find out that 
INITRANS needs to be tuned with ALTER TABLE.  PostgreSQL ran smoothly without 
any tuning.

I find your UNDO approach attractive.  On the other hand, I sometimes wonder 
where PostgreSQL is headed for.  I'm sometimes asked by database users "How 
different is PostgreSQL from MySQL?"  If the UNDO approach is taken, PostgreSQL 
would appear more similar to MySQL.  I don't say that's bad, but I wonder 
whether we can appeal the new feature in a big picture.  For example, the 
current (VACUUM) approach would prevent PostgreSQL from becoming a database for 
OLTP/analytics mixed workload, because long-running analytics queries cause 
tale and index bloat regardless of whether those queries access the same data 
as the OLTP workload, wouldn't it?  Can we appeal the future of PostgreSQL and 
the difference from MySQL as "PostgreSQL is pursuing to handle multiple 
workloa

Re: [HACKERS] Broken SSL tests in master

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> sense to add support for multiple hostaddrs. For consitency's sake if
> nothing else.

Yes, consistency and performance.  The purpose of hostaddr is to speed up 
connection by eliminating DNS lookup, isn't it?  Then, some users should want 
to specify multiple IP addresses for hostaddr and omit host.

> By the way is comma separated hosts documented somewhere? It is not included
> in
> https://www.postgresql.org/docs/9.6/static/libpq-connect.html#LIBPQ-PA
> RAMKEYWORDS.

Specifying multiple hosts is a new feature to be introduced in v10, so that's 
here:

https://www.postgresql.org/docs/devel/static/libpq-connect.html

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] Broken SSL tests in master

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Andreas Karlsson
> On 11/24/2016 10:38 PM, Andreas Karlsson wrote:
> > To me it feels like the proper fix would be to make PQHost() return
> > the value of the host parameter rather than the hostaddr (maybe add a
> > new field in the pg_conn_host struct). But would be a behaviour change
> > which might break someones application. Thoughts?
> 
> I have attached a proof of concept patch for this. Remaining work is
> investigating all the callers of PQhost() and see if any of them are
> negatively affected by this patch and cleaning up the code some.

I agree that pg_conn_host should have hostaddr in addition to host, and 
PQhost() return host when host is specified with/without hostaddr specified.

However, I wonder whether the hostaddr parameter should also accept multiple IP 
addresses.  Currently, it accepts only one address as follows.  I asked Robert 
and Mithun about this, but I forgot about that.


static bool
connectOptions2(PGconn *conn)
{
/*
 * Allocate memory for details about each host to which we might possibly
 * try to connect.  If pghostaddr is set, we're only going to try to
 * connect to that one particular address.  If it's not, we'll use pghost,
 * which may contain multiple, comma-separated names.
 */

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] UNDO and in-place update

[Tsunakawa, Takayuki]

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

Furthermore, it maybe the best to be able to switch the method for each table 
and/or tablespace.  For example, in pgbench, history table uses the current 
method,
and other tables use the UNDO method.  Is it time to introduce a pluggable 
storage system?

Because PostgreSQL is a follower in the UNDO approach, I think it will be 
better to study other DBMSs well (Oracle and MySQL?).  That includes not only 
their manuals, but also whitepapers and books.  Especially, I expect good books 
to give deep knowledge on performance tuning and troubleshooting, from which we 
will be able to know the cons that Oracle's materials don't state.

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
> Maybe a workable compromise would be to leave the file present, and have
> the stats collector re-write it every (say) five minutes.  Then I'd be okay
> with having an immediate shutdown skip writing the file; you'd be losing
> up to five minutes' worth of activity, but not going completely nuts.  So
> the stats collector's normal activities would include writing the temp file
> on-demand and the permanent file on a timed cycle.
> 
> The other components of the fix (deleting on PITR rewind or stats collector
> crash) would remain the same.

The manual says:

"Also, the collector itself emits a new report at most once per 
PGSTAT_STAT_INTERVAL milliseconds (500 ms unless altered while building the 
server). So the displayed information lags behind actual activity."

Doesn't this mean that the stats collector writes files in pg_stat_tmp/ every 
500ms?  If true, how about just moving those files into appropriate locations 
during recovery, instead of removing the files?

I also find others's ideas woth considering -- WAL-logging the stats files, 
type-specific stats files, etc. -- but I'm afraid those ideas would only be 
employed in a new major release, not in released versions.  I'm asking for a 
remedy for a user (and potential users) who use older releases.  And, I don't 
yet understand why patch would make the situation for existing users, and why 
stop writing files during immediate/abnormal shutdown requires other efforts.

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

Re: [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 Amit Kapila
> Thanks for the clarification, I could reproduce the issue and confirms that
> patch has fixed it.  Find logs of cascading standby at  PG9.2 Head and Patch
> attached (I have truncated few lines at end of server log generated in Head
> as those were repetitive).  I think the way you have directly explained
> the bug steps in code comments is not right (think if we start writing bug
> steps for each bug fix, how the code will look like).  So I have modified
> the comment to explain the situation and reason of check,  see if you find
> that as okay?

Thank you, I'm happy with your comment.

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
> I am very strict about regressing the performance of things that we already
> have, but I try not to make a policy that a new feature must be as fast
> as it could ever be.  That could result in us having very few new features.

I see.  I like your attitude toward new features.  But I don't think now is the 
time to compromise this feature and rush to the commit.


> Also, I am not saying that we should not change this in time for v10.
> I'm saying that I don't think it should be a requirement for the next patch
> to be committed in this area to introduce a whole new mechanism for
> determining whether something is a master or a standby.  Love it or hate
> it, pgsql-jdbc has already implemented something in this area and it does
> something useful -- without requiring a wire protocol change.  Now you and
> Kevin are trying to say that what they did is all wrong, but I don't agree.
> There are very many users for whom the pgsql-jdbc approach will do exactly
> what they need, and no doubt some for whom it won't.  Getting a patch that
> mimics that approach committed is *progress*.  Improving it afterwards,
> whether for v10 or some later release, is also good.

transaction_read_only=on does not mean the standby.  As the manual article on 
hot standby says, they are different.

https://www.postgresql.org/docs/devel/static/hot-standby.html

[Excerpt]
--
In normal operation, “read-only” transactions are allowed to update sequences 
and to use LISTEN, UNLISTEN, and NOTIFY, so Hot Standby sessions operate under 
slightly tighter restrictions than ordinary read-only sessions. It is possible 
that some of these restrictions might be loosened in a future release.
...
Users will be able to tell whether their session is read-only by issuing SHOW 
transaction_read_only. In addition, a set of functions (Table 9.79, “Recovery 
Information Functions”) allow users to access information about the standby 
server. These allow you to write programs that are aware of the current state 
of the database. These can be used to monitor the progress of recovery, or to 
allow you to write complex programs that restore the database to particular 
states.
--


I'm afraid that if the current patch is committed, you will lose interest in 
the ideal solution.  Then if the current patch is out as v10, there would be a 
concern about incompatibility when we pursue the ideal solution in a later 
release.  That is, "should we continue to report that this server is standby 
even if it's actually a primary with transaction_read_only is on, to maintain 
compatibility with the older release."

If you want to connect to a server where the transaction is read-only, then 
shouldn't the connection parameter be something like 
"target_session_attrs=readonly"?  That represents exactly what the code does.


> There is a saying that one should not let the perfect be the enemy of the
> good.  I believe that saying applies here.

True, so I suggested not including the support for older servers for a while.  
Shall we find the real enemy -- what's preventing the ideal solution?  I know 
my knowledge is still far less than you, so I may be missing something 
difficult.  So, I'd like Mithun to share the difficulty.

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: Tom Lane [mailto:t...@sss.pgh.pa.us]
> The point I was trying to make is that I think the forced-removal behavior
> is not desirable, and therefore committing a patch that makes it be graven
> in stone is not desirable either.

I totally agree that we should pursue the direction for escaping from the 
complete loss of stats files.  Personally, I would like to combine that with 
the idea of persistent performance diagnosis information for long-term analysis 
(IIRC, someone proposed it.)  However, I don't think my patch will make 
everyone forget about the problem of stats file loss during recovery.  The 
problem exists with or without my patch, and my patch doesn't have the power to 
delute the importance of the problem.  If you are worried about memory, we can 
add an entry for the problem in TODO list that Bruce-san is maintaining.

Or, maybe we can just stop removing the stats files during recovery by keeping 
the files of previous generation and using it as the current one.  I haven't 
seen how fresh the previous generation is (500ms ago?).  A bit older might be 
better than nothing.

> The larger picture here is that Takayuki-san wants us to commit a patch
> based on a customer's objection to 9.2's behavior, without any real evidence
> that the 9.4 change isn't a sufficient solution.  I've got absolutely zero
> sympathy for that "the stats collector might be stuck in an unkillable state"
> argument --- where's the evidence that the stats collector is any more prone
> to that than any other postmaster child?

9.4 change may be sufficient.  But I don't think I can proudly explain the 
logic to a really severe customer.  I can't answer the question "Why does 
PostgreSQL write files that will be deleted, even during 'immediate' shutdown?  
Why does PostgreSQL use 5 seconds for nothing?"

Other children do nothing and exit immediately.  I believe they are behaving 
correctly.

> And for that matter, if we are stuck because of a nonresponding NFS server,
> how is a quicker postmaster exit going to help anything?
> You're not going to be able to start a new postmaster if the data directory
> is on a nonresponsive server.

NFS server can also be configured for HA, and the new postmaster can start as 
soon as the NFS server completes failover.

> I'd be willing to entertain a proposal to make the 5-second limit adjustable,
> but I don't think we need entirely new behavior here.

Then, I'm at a loss what to do for the 9.2 user.

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]
> So there are two questions here:
> 
> 1. Should we try to avoid having the stats collector write a stats file
> during an immediate shutdown?  The file will be removed anyway during crash
> recovery, so writing it is pointless.  I think you are right that 9.4's
> solution here is not perfect, because of the 5 second delay, and also because
> if the stats collector is stuck inside the kernel trying to write to the
> OS, it may be in a non-interruptible wait state where even SIGKILL has no
> immediate effect.  Anyway, it's stupid even from a performance point of
> view to waste time writing a file that we're just going to nuke.
> 
> 2. Should we close listen sockets sooner during an immediate shutdown?
>  I agree with Tom and Peter that this isn't a good idea.  People expect
> the sockets not to go away until the end - e.g. they use
> PQping() to test the server status, or they connect just to see what error
> they get - and the fact that a client application could hypothetically
> generate such a relentless stream of connection attempts that the dead-end
> backends thereby created slow down shutdown is not in my mind a sufficient
> reason to change the behavior.
> 
> So I think 001 should proceed and 002 should be rejected.

I'm happy with this conclusion, since I think 1 was the cause of slow shutdown, 
and 2 is just a hypothesis to pursue the completeness.  And I can understand 
the concern about PQping().

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: Craig Ringer [mailto:cr...@2ndquadrant.com]
> You meant CheckTokenMembership().

Yes, my typo in the mail.

> The proposed patch does need to be checked with:

I understood you meant by "refuse to run" that postgres.exe fails to start 
below.  Yes, I checked it on Win10.  I don't have access to WinXP/2003 - 
Microsoft ended their support.

if (pgwin32_is_admin())
{
write_stderr("Execution of PostgreSQL by a user with 
administrative permissions is not\n"
 "permitted.\n"
 "The server must be started under an 
unprivileged user ID to prevent\n"
 "possible system security compromises.  See the documentation 
for\n"
  "more information on how to properly start 
the server.\n");
exit(1);
}

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 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
> I have tried using attached script multiple times on latest 9.2 code, but
> couldn't reproduce the issue.  Please find the log attached with this mail.
> Apart from log file, below prints appear:
> 
> WARNING: enabling "trust" authentication for local connections You can
> change this by editing pg_hba.conf or using the option -A, or --auth-local
> and --auth-host, the next time you run initdb.
> 20075/20075 kB (100%), 1/1 tablespace
> NOTICE:  pg_stop_backup complete, all required WAL segments have been
> archived
> 20079/20079 kB (100%), 1/1 tablespace
> 
> Let me know, if some parameters need to be tweaked to reproduce the issue?
> 
> 
> It seems that the patch proposed is good, but it is better if somebody other
> than you can reproduce the issue and verify if the patch fixes the same.
> 

Thank you for reviewing the code and testing.  Hmm, we could reproduce the 
problem on PostgreSQL 9.2.19.  The script's stdout is attached as test.log, and 
the stderr is as follows:

WARNING: enabling "trust" authentication for local connections You can change 
this by editing pg_hba.conf or using the option -A, or --auth-local and 
--auth-host, the next time you run initdb.
20099/20099 kB (100%), 1/1 tablespace
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
20103/20103 kB (100%), 1/1 tablespace

The sizes pg_basebackup outputs is a bit different from yours.  I don't see a 
reason for this.  The test script explicitly specifies the database encoding 
and locale, so the encoding difference doesn't seem to be the cause.  The 
target problem occurs only when a WAL record crosses a WAL segment boundary, so 
subtle change in WAL record volume would prevent the problem from happening.

Anyway, could you retry with the attached test.sh?  It just changes 
restore_command.

If the problem occurs, the following pair of lines appear in the server log of 
the cascading standby.  Could you check it?

LOG:  restored log file "00020003" from archive
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 3, offset 0

Regards
Takayuki Tsunakawa




test.sh
Description: test.sh


test.log
Description: test.log

-- 
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]
> On Fri, Nov 18, 2016 at 4:12 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> > Alvaro Herrera <alvhe...@2ndquadrant.com> writes:
> >> Tom Lane wrote:
> >>> IMO it's not, and closer analysis says that this patch series is an
> >>> attempt to solve something we already fixed, better, in 9.4.
> >
> >> ... by the same patch submitter.
> >
> > [ confused ]  The commit log credits 82233ce7e to MauMau and yourself.
> 
> IIUC, MauMau = Tsunakawa Takayuki.

Yes, it's me.  I'm pleased that you remember me!

First, I understand that zapping the stats file during recovery can be a 
problem.  In fact, it's me who proposed adding a sentence in the manual that 
the stats file is reset after immediate shutdown.  I think addressing this 
problem is another topic in a new thread.

The reasons why I proposed this patch are:

* It happened in a highly mission-critical production system of a customer who 
uses 9.2.

* 9.4's solution is not perfect, because it wastes 5 seconds anyway, which is 
unexpected for users.  The customer's requirement includes failover within 30 
seconds, so 5 seconds can be seen as a risk.
Plus, I'm worried about the possibility that the SIGKILLed process wouldn't 
disappear if it's writing to a network storage like NFS.

* And first of all, the immediate shutdown should shut the server down 
immediately without doing anything heavy, as the name means.

So, I think this patch should also be applied to later releases.  The purpose 
of the patch in 9.4 was to avoid PostgreSQL's bug, where the ereport() in 
quickdie() gets stuck waiting for malloc()'s lock to be released.

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
> > shared_buffers  tps
> > 256MB  990
> > 512MB  813
> > 1GB  1189
> > 2GB  2258
> > 4GB  5003
> > 8GB  5062
> >
> > "512MB is the largest effective size" seems to be a superstition, although
> I don't know the reason for the drop at 512MB.
> >
> 
> It is difficult to say why the performance drops at 512MB, it could be
> run-to-run variation.  How long have you run each test?

5 minutes (-T 300).  I avoided 20-30 minutes runs for fear of wearing out and 
destroying my disk...

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
> On Thu, Nov 17, 2016 at 10:08 PM, Craig Ringer 
> wrote:
> > We can and probably should have both.
> >
> > If the server tells us on connect whether it's a standby or not, use that.
> >
> > Otherwise, ask it.
> >
> > That way we don't pay the round-trip cost and get the log spam when
> > talking to newer servers that send us something useful in the startup
> > packet, but we can still query it on older servers. Graceful fallback.
> >
> > Every round trip is potentially very expensive. Having libpq do them
> > unnecessarily is bad.
> 
> True, but raising the bar for this feature so that it doesn't get done is
> also bad.  It can be improved in a later patch.

I thought you are very strict about performance, so I hesitate to believe you 
forgive the additional round trip.  libpq failover is a new feature in v10, so 
I think it should provide the best user experience for v10 client+server users 
from the start.  If the concern is the time for development, then support for 
older releases can be added in a later patch.

There are still several months left for v10.  Why don't we try the best?  Could 
you share the difficulty?


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: Mithun Cy [mailto:mithun...@enterprisedb.com]
> > +   {"target_server_type", "PGTARGETSERVERTYPE",
> DefaultTargetServerType, NULL,
> > +   "Target-Server-Type", "", 6,
> 
> Thanks fixed.

+   {"target_server_type", "PGTARGETSERVERTYPE", NULL, NULL,

The default value field is still NULL.


> > Please avoid adding another round trip by using a GUC_REPORTed variable
> (ParameterStatus entry).  If you want to support this libpq failover with
> >pre-10 servers, you can switch the method of determining the primary based
> on the server version.  But I don't think it's worth supporting older
> servers > at the price of libpq code complexity.
> 
> Currently there is no consensus around this. For now, making this patch
> to address failover to next primary as similar to JDBC seems sufficient
> for me.
> On next proposal of patch I think we can try to extend as you have proposed

I don't think show transaction is correct, because it's affected by 
default_transaction_read_only and ALTER DATABASE/USER SET 
transaction_read_only.  transaction_read_only is a transaction attribute, not 
the server type.  What we want to use to determine the connection target should 
be not only whether the transaction is read only, but also other attributes 
such as whether temporary tables can be used (only standby), maintenance 
commands can be executed (VACUUM and ANALYZE can run when transaction_read_only 
is on, but not on standby), etc.  And how about other DBMSs?  Do we really want 
to determine the target based on transaction_read_only while e.g. Oracle is 
based on primary/standby?

If you really want the transaction_read_only attribute for your application, 
then your app should execute "SET default_transaction_read_only = on" upon 
connection, or ALTER DATABASE/USER SET default_transaction_read_only = on in 
advance.


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: Tsunakawa, Takayuki/綱川 貴之
> Thank you, I'll try the read-write test with these settings on the weekend,
> when my PC is available.  I understood that your intention is to avoid being
> affected by checkpointing and WAL segment creation.

The result looks nice as follows.  I took the mean value of three runs.

shared_buffers  tps
256MB  990
512MB  813
1GB  1189
2GB  2258
4GB  5003
8GB  5062

"512MB is the largest effective size" seems to be a superstition, although I 
don't know the reason for the drop at 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

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

[Tsunakawa, Takayuki]

Hello, Craig,

I'm sorry to be late to review your patch.  I've just been able to read the 
HTML doc first.  Can I get the latest .patch file for reading and running the 
code?

Here are some comments and questions.  I tried to avoid the same point as other 
reviewers, but there may be an overlap.


(1)
The example
 UPDATE mytable SET x = x + 1;
should be
 UPDATE mytable SET x = x + 1 WHERE id = 42;


(2)
"The server usually begins executing the batch before all commands in the batch 
are queued and the end of batch command is sent."

Does this mean that the app developer cannot control or predict how many TCP 
transmissions a batch is sent with?  For example, if I want to insert 10 rows 
into a table in bulk, can I send those 10 rows (and the end of batch command) 
efficiently in one TCP transmission, or are they split by libpq into multiple 
TCP transmissions?


(3)
"To avoid deadlocks on large batches the client should be structured around a 
nonblocking I/O loop using a function like select, poll, epoll, 
WaitForMultipleObjectEx, etc."

Can't we use some (new) platform-independent API instead of using poll() or 
WaitForMultipleObject()?  e.g. some thin wrapper around pqWait().  It seems a 
bit burdonsome to have to use an OS-specific API to just wait for libpq.  Apart 
from that, it does not seem possible to wait for the socket in 64-bit apps on 
Windows, because SOCKET is 64-bit while PQsocket() returns int.

[winsock2.h]
/*
 * The new type to be used in all
 * instances which refer to sockets.
 */
typedef UINT_PTRSOCKET;

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
> I am adding next version of the patch it have following fixes.
> Tsunakawa's comments
> 
> 1.  PGconn->target_server_type is now freed in freePGconn() 2.  Added
> PGTARGETSERVERTYPE.
> 
> 
> Additional comments from others
> 3.  Moved from SELECT pg_is_in_recovery() to SHOW transaction_read_only
> now should handle different kind of replication, as we recognise server
> to which writable connection can be made as primary. Very exactly like JDBC
> driver. Also documented about it.
> 4. renamed words from master to primary.

Thank you.  The following items need addressing.  Some of them require some 
more discussion to reach consensus, and I hope they will settle down soon.  
After checking the progress for a week or so, I'll mark the CommitFest entry as 
"ready for committer" or "waiting on author".

(1)
+server. Set this to any, if you want to connect to
+A server is recognized as a primary/standby by observering whether it

Typo.   , and "observering" -> "observing".


(2)
+   {"target_server_type", "PGTARGETSERVERTYPE", NULL, NULL,
+   "Target server type", "", 6,

Looking at existing parameters, the default value is defined as a macro, and 
the display label is a sequence of words separated by "-".  i.e.

+   {"target_server_type", "PGTARGETSERVERTYPE", DefaultTargetServerType, 
NULL,
+   "Target-Server-Type", "", 6,


(3)
Please avoid adding another round trip by using a GUC_REPORTed variable 
(ParameterStatus entry).  If you want to support this libpq failover with 
pre-10 servers, you can switch the method of determining the primary based on 
the server version.  But I don't think it's worth supporting older servers at 
the price of libpq code complexity.


(4)
Please consider supporting "standby" and "prefer_standby" like PgJDBC.  They 
are useful without load balancing when multiple standbys are used for HA.


(5)
I haven't tracked the progress of logical replication, but will 
target_server_type be likely to be usable with it?  How will target_server_type 
fit logical replication?

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
> I think it beginning of segment (aka the first page of the segment), even
> the comment indicates the same.
> 
> /*
> * Whenever switching to a new WAL segment, we read the first page of
> * the file and validate its header, even if that's not where the
> * target record is. ...
> ..
> */
> 
> However, on again looking at the code, it seems that part of code behaves
> similarly for both 9.2 and 9.3.

Yes, the code behaves similarly in 9.2 and later.  FYI, ValidXLogHeader() is 
called at two sites.  The earlier one checks the first page of a segment when 
the real target page is different, and the latter one checks any page including 
the first page of a segment.


> ..Because node3 found a WAL
> !  * record fragment at the end of segment 10, it expects to find the !
> * remaining fragment at the beginning of WAL segment 11 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 10 across !
> * to 11.
> 
> How does node3 ensure that the fragment of WAL in segment 11 is different?
> Isn't it possible that when node2 overwrites the last record in WAL segment
> 10, it writes a record of slightly different contents but which is of the
> same size as an original record in WAL segment 10?

That case is detected by checking the CRC value in the XLOG record header.

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
> > 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.
> >
> 
> If we don't get any benefit by increasing the shared_buffers on windows,
> then what advantage do you see in recommending higher value?

No, I'm not recommending a higher value, but just removing the doubtful 
sentences of 512MB upper limit.  The advantage is that eliminating this 
sentence will make a chance for users to try best setting.



> I generally run it for 20 to 30 mins for read-write tests.  Also, to ensure
> consistent data, please consider changing following parameters in
> postgresql.conf checkpoint_timeout = 35 minutes or so, min_wal_size = 5GB
> or so, max_wal_size = 20GB or so and checkpoint_completion_target=0.9.
> 
> Apart from above, ensure to run manual checkpoint (checkpoint command) after
> each test.

Thank you, I'll try the read-write test with these settings on the weekend, 
when my PC is available.  I understood that your intention is to avoid being 
affected by checkpointing and WAL segment creation.

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
> Hmm, let's go back to the JDBC method, then.  "show transaction_read_only"
> will return true on a standby, but presumably also on any other non-writable
> node.  You could even force it to be true artificially if you wanted to
> force traffic off of a node, using ALTER {SYSTEM|USER ...|DATABASE ..} SET
> default_transaction_read_only = on
> 
> I think that would address Alvaro's concern, and it's nicer anyway if libpq
> and JDBC are doing the same thing.

If you prefer consistency between libpq and JDBC, then we could correct JDBC.  
People here should know the server state well, and be able to figure out a good 
specification.

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 Tatsuo Ishii
> In my understanding pg_is_in_recovery() returns true if it's a standby node.
> However, even if it returns other than true, the server is not necessarily
> a primary. Even it's not configured as a streaming replication primary,
> it returns other than true.
> 
> So if your intention is finding a primary, I am not sure if
> pg_is_in_recovery() is the best solution.

Yes, I don't think pg_is_in_recovery() is the best, but there doesn't seem to 
be a better solution.  pg_is_in_recovery(), as its name clearly suggests, 
returns true if the server is performing recovery.  For example, it returns 
true if hot_standby=on is present in postgresql.conf and the recovery from 
backup is in progress.  It's not a standby.

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
> On Mon, Nov 14, 2016 at 8:09 PM, Tsunakawa, Takayuki
> <tsunakawa.ta...@jp.fujitsu.com> wrote:
> > 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.
> 
> True, but there is a concern about a newer libpq connecting to older servers.
> If we mimic what JDBC is already doing, we'll be compatible and you'll be
> able to use this feature with a v10 libpq without worrying about whether
> the target server is also v10.  If we invent something new on the server
> side, then you'll need to be sure you have both a v10 libpq and v10 server.

Do we really want to enable libpq failover against pre-V10 servers?  I don't 
think so, as libpq is a part of PostgreSQL and libpq failover is a new feature 
in PostgreSQL 10.  At least, as one user, I don't want PostgreSQL to sacrifice 
another round trip to establish a connection.  As a developer, I don't want 
libpq code more complex than necessary (the proposed patch adds a new state to 
the connection state machine.)  And I think it's natural for the server to 
return the server attribute (primary/standby, writable, etc.) as a response to 
the Startup message like server_version, standard_conforming_strings and 
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] [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] [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] 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] 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] 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] Patch: Implement failover on libpq connect level.

[Tsunakawa, Takayuki]

Hi, Mithun

Before going deeper into the patch, let me give you some findings.

(1)
PGconn->target_server_type is not freed in freePGconn().


(2)
Could you add PGTARGETSERVERTYPE environment variable?  Like other variables, 
it will ease testing, since users can change the behavior without altering the 
connection string here and there.


(3)
I think it would be better to expose the server state via ParameterStatus 
protocol message like standard_conforming_strings, instead of running "SELECT 
pg_is_in_recovery()".  We shouldn't want to add one round trip to check the 
server type (master, standby).  postmaster can return the server type based on 
its state (pmState); PM_RUN is master, and PM_HOT_STANDBY is standby.  In 
addition, as an impractical concern, DBA can revoke EXECUTE privilege on 
pg_is_in_recovery() from non-superusers.

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.

The attached patch fixes some bugs and make a clarification for doc.  Could you 
check and test the authentication stuff as I don't have an environment at hand?

(1) fe-connect.c
There was a memory leak.

(2) fe-secure_openssl.c, fe-auth.c
GSSAPI/SSPI/SSL authentication requires the target host name, but the code uses 
conn->pghost which contains a comma-separated list of host names.

(3) libpq.sgml
Added sentences to clarify connect_timeout when it is used with multiple hosts.

BTW, let me two questions.

Q1: Is there any reason why hostaddr doesn't accept multiple IP addresses?

Q2: pg_isready (and hence PQping() family) reports success when one host is 
alive and other hosts are down.  Is this intended?  I think this behavior is 
correct.
e.g. host1 is down and host2 is alive.
$ pg_isready -h host1,host2
host1,host2:5450 - accepting connections
$ echo $?
0

Regards
Takayuki Tsunakawa



libpq-failover-smallbugs.patch
Description: libpq-failover-smallbugs.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 Ashutosh Bapat
> I have changed some comments around this block. See attached patch.
> Let me know if that looks good.

Thanks, it looks good.

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] 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] 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] 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: 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
> 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: 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
> 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
> 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] 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] [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] 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] ECPG BUlk insert support using arrays

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Haribabu Kommi
> I didn't find any relevant thread about the discussion of Bulk insert support
> in ECPG using arrays. Oracle supports the same and details are available
> in [1].
> 
> 
> I see some performance benefits in supporting the same in ECPG also.
> Does any one worked on this area before? Are there any problems in preparing
> a patch to support the same?

Please see "batch/pipelining support for libpq" by Craig.  I said I'll use his 
API to implement the array insert for ECPG, but you can feel free to do 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] [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] [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] 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] [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

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

[Tsunakawa, Takayuki]

From: Michael Paquier [mailto:michael.paqu...@gmail.com]
> It would be a good idea to add that to next CF if nobody pops into the thread
> so as we don't forget about it.

Thanks for the notice, done.

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 Michael Paquier
> Oops. This could be a problem for some applications... As far as I can see
> and after playing with it, your patch looks correct.

Thank you for checking the patch.  I'm relieved.

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] [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

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

[Tsunakawa, Takayuki]

Hello,

>From our experience in handling customers' problems, I feel it's necessary to 
>evolve PostgreSQL's transaction management.  The concrete problems are:

1. PostgreSQL cannot end and begin transactions in PL/pgSQL and PL/Java stored 
functions.
This is often the reason people could not migrate to PostgreSQL.


2. PostgreSQL does not support statement-level rollback.
When some customer ran a batch app using psqlODBC, one postgres process used 
dozens of GBs of memory and crashed the OS.  The batch app prepares some SQL 
statements with parameters, execute it five millions of times with different 
parameter values in a single transaction.  They didn't experience a problem 
with Oracle.

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.  The total 
memory used becomes 40GB (8KB * 5 million subtransactions.)  This was avoided 
by setting the Protocol parameter to 7.4-1, which means transaction-level 
rollback.

The savepoint approach for supporting statement-level rollback is inefficient, 
because it adds two roundtrips (SAVEPOINT and RELEASE) for each statement.



I know autonomous transaction is also discussed, which seems to be difficult, 
so I hope some kind of transaction management overhaul can be discussed to 
cover all these transaction-related features.  How should I start?  I found the 
following item in the TODO list (but I haven't read it yet.)  What other 
discussions should I look at?

--
Implement stored procedures 
This might involve the control of transaction state and the return of multiple 
result sets 
PL/pgSQL stored procedure returning multiple result sets (SELECTs)? 
Proposal: real procedures again (8.4) 
http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php 
Gathering specs and discussion on feature (post 9.1) 
--


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]
> 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] 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] [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] 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] 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

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

[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