Re: [HACKERS] Statement-level rollback

[MauMau]

From: Thomas Munro
With your v2 patch "make docs" fails.  Here is a small patch to apply
on top of yours to fix that and some small copy/paste errors, if I
understood correctly.

Ouch, thanks.  I'd like to merge your fix when I submit the next
revision of my patch.

Regards
MauMau




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

[MauMau]

From: Simon Riggs
On 14 August 2017 at 23:58, Peter Eisentraut
 wrote:
> On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
>> 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.
>
> This patch needs to be rebased for the upcoming commit fest.

I'm willing to review this if the patch is going to be actively worked
on.


I'm very sorry I couldn't reply to your kind offer.  I rebased the
patch and will add it to CF 2017/11.  I hope I will complete the patch
in this CF.

Regards
Takayuki Tsunakawa




stmt_rollback_v2.patch
Description: Binary data

-- 
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] sync process names between ps and pg_stat_activity

[MauMau]

From: Peter Eisentraut
> The process names shown in pg_stat_activity.backend_type as of PG10
and
> the process names used in the ps display are in some cases
gratuitously
> different, so here is a patch to make them more alike.  Of course it
> could be debated in some cases which spelling was better.

(1)
In the following comment, it's better to change "wal sender process"
to "walsender" to follow the modified name.

- * postgres: wal sender process   
+ * postgres: walsender   
  *
  * To achieve that, we pass "wal sender process" as username and
username
  * as dbname to init_ps_display(). XXX: should add a new variant
of
  * init_ps_display() to avoid abusing the parameters like this.
  */


(2)
"WAL writer process" is used, not "walwriter", is used in postmaster.c
as follows.  I guess this is for natural language.  Is this intended?
I'm OK with either, though.

HandleChildCrash(pid, exitstatus,
 _("WAL writer process"));

case WalWriterProcess:
ereport(LOG,
(errmsg("could not fork WAL writer process:
%m")));


Personally, I prefer "wal writer", "wal sender" and "wal receiver"
that separate words as other process names.  But I don't mind leaving
them as they are now.  I'd like to make this as ready for committer
when I get some reply.

Regards
MauMau



-- 
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] What would be difficult to make data models pluggable for making PostgreSQL a multi-model database?

[MauMau]

From: Henry M
> This may be interesting... they implement cypher (unfortunately they
had to fork in order to have cypher be a first class query language
with SQL).
>
> https://github.com/bitnine-oss/agensgraph

I'm sorry for my very late reply.

Thanks for the information.  AgensGraph is certainly interesting, but
the problem is that it's a fork of PostgreSQL as you mentioned.  I
wish the data models, including query languages, to be pluggable
extensions, so that various people (especially database researchers?)
can develop them flexibly.  Of course, I want various data models to
be incorporated in the core as early as possible, but I'm afraid it's
not easy.  If new data models can be added as extensions, they can be
developed outside the PostgreSQL community process, get popular and
mature, and then be embraced in core like GiST/SP-Gist indexes and
full text search did.


Regards
MauMau



-- 
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] What would be difficult to make data models pluggable for making PostgreSQL a multi-model database?

[MauMau]

From: Chris Travers
> Why cannot you do all this in a language handler and treat as a user
defined function?
> ...
> If you have a language handler for cypher, why do you need in_region
or cast_region?  Why not just have a graph_search() function which
takes in a cypher query and returns a set of records?

The language handler is for *stored* functions.  The user-defined
function (UDF) doesn't participate in the planning of the outer
(top-level) query.  And they both assume that they are executed in SQL
commands.

I want the data models to meet these:

1) The query language can be used as a top-level session language.
For example, if an app specifies "region=cypher_graph" at database
connection, it can use the database as a graph database and submit
Cypher queries without embedding them in SQL.

2) When a query contains multiple query fragments of different data
models, all those fragments are parsed and planned before execution.
The planner comes up with the best plan, crossing the data model
boundary.  To take the query example in my first mail, which joins a
relational table and the result of a graph query.  The relational
planner considers how to scan the table, the graph planner considers
how to search the graph, and the relational planner considers how to
join the two fragments.

So in_region() and cast_region() are not functions to be executed
during execution phase, but are syntax constructs that are converted,
during analysis phase, into calls to another region's parser/analyzer
and an inter-model cast routine.

1. The relational parser finds in_region('cypher_graph', 'graph
query') and produces a parse node InRegion(region_name, query) in the
parse tree.

2. The relational analyzer looks up the system catalog to checks if
the specified region exists, then calls its parser/analyzer to produce
the query tree for the graph query fragment.  The relational analyzer
attaches the graph query tree to the InRegion node.

3. When the relational planner finds the graph query tree, it passes
the graph query tree to the graph planner to produce the graph
execution plan.

4. The relational planner produces a join plan node, based on the
costs/statistics of the relational table scan and graph query.  The
graph execution plan is attached to the join plan node.

The parse/query/plan nodes have a label to denote a region, so that
appropriate region's routines can be called.

Regards
MauMau



-- 
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] What would be difficult to make data models pluggable for making PostgreSQL a multi-model database?

[MauMau]

Hello,

Please forgive me for asking such a stupid and rough question.

I'm thinking of making PostgreSQL a multi-model database by supporting
data models other than the current relational model.  A data model
consists of a query language (e.g. SQL for relational model, Cypher
for graph model), a parser and analyzer to transform a query into a
query tree, a planner to transform the query tree into an execution
plan, an executor, and a storage engine.

To promote the data model development, I want to make data models
pluggable.  The rough sketch is:

1) A data model developer implements the parser, analyzer,
transformer, planner, executor, and storage engine functions in a
shared library.

2) The DBA registers the data model.

  CREATE QUERY LANGUAGE Cypher (
PARSER = 
  );

  CREATE DATA MODEL graph (
QUERY LANGUAGE = Cypher,
ANALYZER = ,
TRANSFORMER = ,
PLANNER = ,
EXECUTOR = ,
STORAGE ENGINE = ,
  );

  CREATE REGION cypher_graph (
QUERY LANGUAGE = Cypher,
DATA MODEL = graph
  );

The region is just a combination of a query language and a data model,
much like a locale is a combination of a language and a country.  This
is because there may be multiple popular query languages for a data
model.

3) The application connects to the database, specifying a desired
region.  The specified region's query language becomes the default
query language for the session.


The application can use the data of multiple data models in one query
by specifying another region and its query via in_region().  For
example, the following query combines the relational restaurant table
and a social graph to get the five chinese restaurants in Tokyo that
are most popular among friends of John and their friends.

  SELECT r.name, g.num_likers
  FROM restaurant r,
cast_region(
  in_region('cypher_graph',
'MATCH (:Person {name:"John"})-[:IS_FRIEND_OF*1..2]-(friend),
  (friend)-[:LIKES]->(restaurant:Restaurant)
  RETURN restaurant.name, count(*)'),
'relational', 'g', '(name text, num_likers int')
  WHERE r.name = g.name AND
r.city = 'Tokyo' AND r.cuisine = 'chinese'
  ORDER BY g.num_likers DESC LIMIT 5;


What do you think would be difficult to make data models pluggable,
especially related to plugging the parser, planner, executor, etc?
One possible concern is that various PostgreSQL components might be
too dependent on the data model being relational, and it would be
difficult to separate tight coupling.

Regards
MauMau



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

[HACKERS] How can I find a specific collation in pg_collation when using ICU?

[MauMau]

Hello,

I tried to find a particular collation name in pg_collation, but I
cannot understand the naming convention after reading the following
article.  Specifically, I want to find out whether there is some
collation equivalent to Japanese_CI_AS in SQL Server, which means
Japanese, case-insensitive, and accent sensitive.  Could you tell me
how to do this?  Is there any room to improve the PG manual?


https://www.postgresql.org/docs/devel/static/collation.html
--
23.2.2.2.2. ICU collations

With ICU, it is not sensible to enumerate all possible locale names.
ICU uses a particular naming system for locales, but there are many
more ways to name
a locale than there are actually distinct locales. (In fact, any
string will be accepted as a locale name.) See
http://userguide.icu-project.org/locale
for information on ICU locale naming.
--


Regards
MauMau



-- 
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] [Q] When should I use reg* types instead of oid in the system catalog?

[MauMau]

From: Tom Lane
It's probably mostly historical accident :-(.  There have been
suggestions
before to convert more system catalog columns to regfoo types, but
there's
serious stumbling blocks in the way:


Thank you so much for concise detailed explanation of the history and
current situation.  I understood that it would do more harm than good
to change existing plain oid columns to reg* types for various
clients, and it wouldn't very beneficial but also not so harmful to
make new catalogs/columns reg* types.

Regards
MauMau





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

[HACKERS] [Q] When should I use reg* types instead of oid in the system catalog?

[MauMau]

Hello,


Both pg_aggregate.aggfnoid and pg_trigger.tgfoid references
pg_proc.oid, but the data types of them are regproc and oid
respectively.  Is there any criterion on when to which in the system
catalog?  Is the regproc choice just for readability of the catalog
query output?  Should pg_trigger.tgfoid also have been changed to
regproc?

Regards
MauMau



-- 
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] Build pgoutput with MSVC

[MauMau]

From: Michael Paquier
Magnus has already pushed it as 28d1c8c.

+if exist src\backend\replication\libpqwalreceiver\win32ver.rc del /q
src\backend\replication\pgoutput\win32ver.rc
This is not right by the way, you need to check for the existence of
the file in pgoutput/, not libpqwalreceiver/.


I'm relieved to hear that's already committed.  Oh, how careless I
was, thanks.

Regards
MauMau



-- 
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] Build pgoutput with MSVC

[MauMau]

From: Magnus Hagander
If that's all that's required, I'll just go ahead and commit it right
away, including the clean.bat.

I think the problem with clean.bat isn't cleaning up pgoutput.dll -- 
that one goes in a different directory. But it does need to clean up
the win32ver.rc file that gets dropped there automaticaly.

The attached patch itself seems broken (it has some sort of byte order
marker at the beginning, but removing that still breaks with "patch
unexpectedly ends in middle of line patch:  Only garbage was found
in the patch input.". But I can just copy/paste it manually :)


Thanks, fixed clean.bat, too.  My original patch was in UTF-16
unexpectedly.  With Git Shell in GitHub Desktop on Windows, "git diff
> filename" seems to produce output in UTF-16.  I guess that's due to
PowerShell.  I think there's nothing else to do, so please commit
this.  Also, I added an item in the Open Items page.

Regards
MauMau


msvc_build_pgoutput_v2.patch
Description: Binary data

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

[HACKERS] [patch] Build pgoutput with MSVC

[MauMau]

Hello,

I tried logical replication on Windows, but it failed like this:

postgres=# create subscription mysub connection 'host=localhost
port=5433 user=tuna dbname=postgres' publication mypub with (nocopy
data);
NOTICE:  synchronized table states
ERROR:  could not create replication slot "mysub": ERROR:  could not
access file "pgoutput": No such file or directory
postgres=#

The pgoutput is not built with MSVC.  The attached patch fixes this.
I confirmed that a few INSERTs were replicated correctly.

Should I add this matter in the PostgreSQL 10 Open Items page?

Regards
MauMau


msvc_build_pgoutput.patch
Description: Binary data

-- 
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 #13755: pgwin32_is_service not checking if SECURITY_SERVICE_SID is disabled

[MauMau]

From: Heikki Linnakangas
So, I think we still need the check for Local System.


Thanks, fixed and confirmed that the error message is output in the
event log.

Regards
MauMau



win32-security-service-v7.patch
Description: Binary data

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

[MauMau]

Hi, Michael

As I guessed in the previous mail, both our patches cause
pgwin32_is_service() to return 1 even when SECURITY_SERVICE_RID is
disabled, if the service is running as a Local System.  The existing
logic of checking for Local System should be removed.  The attached
patch fixes this problem.

Regards
Takayuki Tsunakawa


win32-security-service-v5.patch
Description: Binary data

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

[MauMau]

From: Michael Paquier
Hm.. I have just tested HEAD, my patch and your patch using my patch
test on pg_ctl.c, but I am always getting pgwin32_is_service set to 0
when running pg_ctl start from a terminal, and set it to 1 when
running pg_ctl service to register the service startup. Could you
precise in which ways you started the Postgres instance and could you
post the patch of pg_ctl you used? I am afraid that I am taking it
incorrectly because I am not able to see any differences.

Also, did you test the patch I posted and were you able to see the
same differences as with your patch? I still think that my short patch
is logically correct but if the tests are not we are in a no-go
position for any fix posted on this thread.


Yes, I tested both your patch and mine.  I used the attached pg_ctl.c.
It adds -z option which disables SECURITY_SERVICE_RID.

I registered the service with "pg_ctl register -N pg -D
datadir -w -z -S demand -U myuser -P mypass", then started the service
with "net start pg".  The following messages were output in the server
log:

LOG:  pgwin32_is_admin = 0
LOG:  pgwin32_is_service = 0
LOG:  database system was shut down at 2016-11-07 22:04:46 JST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

Without -z, the message becomes "pgwin32_is_service = 1".  And without
the win32security.c patch, "pgwin32_is_service = 1" is output.

I guess you registered the service without specifying the service
account with -U.  Then the service runs as the Local System account,
whence pgwin32_is_service() returns 1.

Regards
Takayuki Tsunakawa

/*
-
 *
 * pg_ctl --- start/stops/restarts the PostgreSQL server
 *
 * Portions Copyright (c) 1996-2016, PostgreSQL Global Development
Group
 *
 * src/bin/pg_ctl/pg_ctl.c
 *
 *
-
 */

#ifdef WIN32
/*
 * Need this to get defines for restricted tokens and jobs. And it
 * has to be set before any header from the Win32 API is loaded.
 */
#define _WIN32_WINNT 0x0501
#endif

#include "postgres_fe.h"

#include "libpq-fe.h"
#include "pqexpbuffer.h"

#include 
#include 
#include 
#include 
#include 
#include 
#include 
#include 

#ifdef HAVE_SYS_RESOURCE_H
#include 
#include 
#endif

#include "getopt_long.h"
#include "miscadmin.h"

/* PID can be negative for standalone backend */
typedef long pgpid_t;


typedef enum
{
SMART_MODE,
FAST_MODE,
IMMEDIATE_MODE
} ShutdownMode;


typedef enum
{
NO_COMMAND = 0,
INIT_COMMAND,
START_COMMAND,
STOP_COMMAND,
RESTART_COMMAND,
RELOAD_COMMAND,
STATUS_COMMAND,
PROMOTE_COMMAND,
KILL_COMMAND,
REGISTER_COMMAND,
UNREGISTER_COMMAND,
RUN_AS_SERVICE_COMMAND
} CtlCommand;

#define DEFAULT_WAIT60

static bool do_wait = false;
static bool del_service_rid = false;
static bool wait_set = false;
static int  wait_seconds = DEFAULT_WAIT;
static bool wait_seconds_arg = false;
static bool silent_mode = false;
static ShutdownMode shutdown_mode = FAST_MODE;
static int  sig = SIGINT;   /* default */
static CtlCommand ctl_command = NO_COMMAND;
static char *pg_data = NULL;
static char *pg_config = NULL;
static char *pgdata_opt = NULL;
static char *post_opts = NULL;
static const char *progname;
static char *log_file = NULL;
static char *exec_path = NULL;
static char *event_source = NULL;
static char *register_servicename = "PostgreSQL";   /* FIXME: +
version ID? */
static char *register_username = NULL;
static char *register_password = NULL;
static char *argv0 = NULL;
static bool allow_core_files = false;
static time_t start_time;

static char postopts_file[MAXPGPATH];
static char version_file[MAXPGPATH];
static char pid_file[MAXPGPATH];
static char backup_file[MAXPGPATH];
static char recovery_file[MAXPGPATH];
static char promote_file[MAXPGPATH];

#ifdef WIN32
static DWORD pgctl_start_type = SERVICE_AUTO_START;
static SERVICE_STATUS status;
static SERVICE_STATUS_HANDLE hStatus = (SERVICE_STATUS_HANDLE) 0;
static HANDLE shutdownHandles[2];
static pid_t postmasterPID = -1;

#define shutdownEvent shutdownHandles[0]
#define postmasterProcess shutdownHandles[1]
#endif


static void write_stderr(const char *fmt,...) pg_attribute_printf(1,
2);
static void do_advice(void);
static void do_help(void);
static void set_mode(char *modeopt);
static void set_sig(char *signame);
static void do_init(void);
static void do_start(void);
static void do_stop(void);
static void do_restart(void);
static void do_reload(void);
static void do_status(void);
static void do_promote(void);
static void do_kill(pgpid_t pid);
static void print_msg(const char *msg);
static void adjust_data_dir(void);

#ifdef WIN32
#if (_MSC_VER >= 1800)
#include 
#else
static bool IsWindowsXPOrGreater(void);
static bool 

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

[MauMau]

Hello,

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.

From: Michael Paquier
(Moved to next CF, with same status "Ready for committer").

I reviewed and tested this patch after simplifying it like the
attached one.  The file could be reduced by about 110 lines.  Please
review and/or test it.  Though I kept the status "ready for
committer", feel free to change it back based on the result.

I tested as follows.  First, I confirmed that pg_is_admin() still
works by running postgres.exe from the Administrator command line:

--
G:\>postgres
Execution of PostgreSQL by a user with administrative permissions is
not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises.  See the documentation for
more information on how to properly start the server.

G:\>
--



Then, I added the following two elog() calls in postmaster.c so that
pg_is_admin() and pg_is_service() works fine.


--
maybe_start_bgworker();

elog(LOG, "pgwin32_is_admin = %d", pgwin32_is_admin());
elog(LOG, "pgwin32_is_service = %d", pgwin32_is_service());

status = ServerLoop();
--


To reproduce the OP's problem, I modified pg_ctl.c to disable
SECURITY_SERVICE_RID when spawning postgres.exe.  Without the patch,
starting the Windows service emit the following log, showing that
pg_is_service() misjudged that postgres is running as a Windows
service:

LOG:  pgwin32_is_admin = 0
LOG:  pgwin32_is_service = 1

With the patch, the log became correct:

LOG:  pgwin32_is_admin = 0
LOG:  pgwin32_is_service = 0


Regards
Takayuki Tsunakawa



win32-security_service-v3.patch
Description: Binary data

-- 
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] pg_recvlogical is missing in the Windows installation

[MauMau]

From: Robert Haas
On Sat, Sep 17, 2016 at 7:44 PM, Michael Paquier
<michael.paqu...@gmail.com> wrote:
> On Sun, Sep 18, 2016 at 7:01 AM, MauMau <maumau...@gmail.com> wrote:
>> pg_recvlogical is not included in the Windows client installation,
>> which is performed by running "install  client".  The
>> attached patch based on HEAD fixes this.  I confirmed nothing else
is
>> missing in the client installation.
>
> Good cacth. This has been missed for a couple of years.

OK, committed and back-patched to 9.4.  Not having a Windows build
environment, I did that blindly, so hopefully the BF won't blow up


Thank you for quick committing.

Regards
MauMau




-- 
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] pg_recvlogical is missing in the Windows installation

[MauMau]

Hello,

pg_recvlogical is not included in the Windows client installation,
which is performed by running "install  client".  The
attached patch based on HEAD fixes this.  I confirmed nothing else is
missing in the client installation.


Regards
MauMau (= Takayuki Tsunakawa)


missing_win_modules.patch
Description: Binary data

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

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

[MauMau]

From: Craig Ringer 
--
* Make a directory of software/services that can be used with PostgreSQL on the 
community web site (wiki.postgresql.org or www.postgresql.org).
Software/services vendors and PostgreSQL developers/users can edit this 
directory.

I thought we had that? Yep.

http://www.postgresql.org/download/product-categories/
--

There are 172 software listed here in total.  Excluding the following 
categories which can be considered part of a DBMS, the number is 121.  
Certainly not much.

•Drivers and interfaces
•PostgreSQL-derived servers
•PostgreSQL extensions
•Procedural languages

Speaking without fear of misunderstanding, there are few software products 
listed that migrators from commercial databases would want to know...  Even 
famous software like Pentaho, Microstrategy, Tableau, Eclipse and Visual Studio 
are not listed, which are known to work or should work with PostgreSQL.  This 
is a shame.

Why don't we enrich the catalog?  I'd like to hear ideas on how to enrich the 
catalog efficiently.  It's ideal for software vendors and users to voluntarily 
add to the catalog.


Regards
MauMau

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

[MauMau]

From: Craig Ringer 
--
We could help ORMs solve the N+1 SELECTs problem and help them avoid 
transferring vast join projections unnecessarily. That'd make PostgreSQL pretty 
compelling for exactly the users we're mostly too busy dismissing to consider.

I'd be interested in reaching out to some Hibernate/JPA and ActiveRecord folks 
about how the DB could help the ORM and have been meaning to explore this area 
for a while, but -ENOTIME. If anyone pursues it I'll be really interested in 
hearing how things go.
--

You have various ideas and experience, don't you?
Are those ideas on ORMs beneficial exclusively to PostgreSQL or to all DBMSs?  
I don't know the structure of ORMs allows for improvements to be advantageous 
to a specific DBMS.



From: Craig Ringer 
--
When you consider the adoption of PostgreSQL in the Rails/ActiveRecord crowd I 
think there's some interesting potential there. What there isn't is funding 
AFAIK.
--

Yes, this needs investigation.  But I'm not sure Ruby ecosystem is the world 
where PostgreSQL should go.  Do many users of commercial databases have assets 
based on Ruby which they want to continue to use?

Recently, my colleague, a Ruby committer, told me that Ruby may be losing 
momentum because developers are moving toward JavaScript+Node.js.  Addressing 
JavaScript+Node.js ecosystem may be more beneficial to PostgreSQL popularity.



From: Craig Ringer 
--
Besides, in practice, we probably should increase the number of software 
interoperable with PostgreSQL.  e.g. one customer asked us whether Arcserve can 
be used to back up PostgreSQL databases, but unfortunately we had to answer no. 
 They are using Arcserve to back up Oracle databases and other resources.  
"Then, you can use NetVault instead" is not the best answer; they just want to 
replace the database.

The "we" here is the problem. It's not likely to be folks focused on PostgreSQL 
core dev, but ... who, exactly?
--

I simply meant PostgreSQL developers and users (of course, including me) who 
wish PostgreSQL to become more popular and thrive for a long time.  Most users 
use databases combined with applications (not just the DBMS alone), so 
increasing the number of applications supporting PostgreSQL will attract more 
users.  PostgreSQL developers will gain wider market and business/job 
oppotunities.



From: Craig Ringer 
--
Provide technical assistance to those vendors as an organization so that they 
can support PostgreSQL smoothly.

This one is a help. That said, pgsql-general is pretty helpful already...
--

Certainly.



From: Craig Ringer 
--
* Make a directory of software/services that can be used with PostgreSQL on the 
community web site (wiki.postgresql.org or www.postgresql.org).
Software/services vendors and PostgreSQL developers/users can edit this 
directory.

I thought we had that? Yep.

http://www.postgresql.org/download/product-categories/

It's pretty invisible though, partly due to the postgresql.org landing page's 
need for a trim-down and tidy. (Don't even get me started on 
http://www.postgresql.org/about/ )
--

Thanks, I know the page.  It totally slipped off my head.  Yes, I'm afraid the 
path to the page (Home -> Download -> Software Catalog) is not good.



From: Craig Ringer 
--
* How/Where can we get the knowledge of expanding the software ecosystem?  Is 
there any OSS project that we can learn from?

Mongo.

http://www.postgresql.org/
https://www.mongodb.org/

... and tell me which you'd look over first if you were evaluating things.
--

Yes, I want something like a site map.  But I couldn't find information about 
software supporting MongoDB within a few minutes, and I gave up.  MongoDB 
website may not be necessarily nice.

In that respect, MySQL website is good, because "Partners" link is on the home 
page.  PostgreSQL website can also have "Related software" or something like 
that on the home page.




From: Craig Ringer 
--
How can we attract software vendors to support PostgreSQL?  What words are 
convincing to appeal the increasing potential of PostgreSQL as a good 
replacement for commercial databases?

Change the name :p
--

Sorry, I couldn't catch the implication.  Do you mean changing the name 
PostgreSQL to something else, or just a joke?

Regards
MauMau

[HACKERS] How can we expand PostgreSQL ecosystem?

[MauMau]

 and remarks about configuration, tuning, and 
whatever useful for users. 

PostgreSQL Enterprise Consortium (PGECons)
https://www.pgecons.org/en



CONSULTATION AND DISCUSSION
==

I'd like to discuss and hear opinions on how can we expand the ecosystem of 
PostgreSQL.  Example points are:

* How/Where can we get the knowledge of expanding the software ecosystem?  Is 
there any OSS project that we can learn from?
How can we attract software vendors to support PostgreSQL?  What words are 
convincing to appeal the increasing potential of PostgreSQL as a good 
replacement for commercial databases?

* How can we gain momentum for the activity?
Can we involve globally influential companies like Dell, HPE, and Red Hat?

* Do we need some kind of assistance from a foundation or establish a 
foundation?
There should be many, many software to address, so the ecosystem activity has 
to be long-lasting. Plus, money and expertise is necessary. 
Would we benefit if we join the Linux Foundation Collaborative Projects?

Linux Foundation Collaborative Projects
http://collabprojects.linuxfoundation.org/

* Which software/services in what category should we address preferentially?
What software would many users desire to be interoperable when migrating from 
commercial databases?
What is the effective way to absorb user requests for this?  Is it enough to 
make a questionnaire like the following?  What is the popular questionnaire 
site which can catch many users (SurveyMonkey?)

https://postgresql.uservoice.com/forums/21853-general


Regards
MauMau

Re: [HACKERS] Greeting for coming back, and where is PostgreSQL going

[MauMau]

Thankyou, Michael, Nagayasu-san, Ishii-san, and Joshua.  Your reply gave me 
energy!


I'm relieved to know that community people use Emacs for editing SGML/XML. 
My main editor on Linux is Emacs.



These days, there is a lot of discussion and activity to make Postgres
better at scaling out. There are discussions about backporting stuff
from XC/XL back to core, though that's a tough work. This thread is a
good summary of what is happening lately in this area:
http://www.postgresql.org/message-id/20160223164335.ga11...@momjian.us


Cool, exciting to know this!


Regards
MauMau



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

[HACKERS] Greeting for coming back, and where is PostgreSQL going

[MauMau]

Hello,


Long time no see.  I'm back.

Although you may not remember me, I was certainly here more than a year ago, 
submitting tiny patches for bug fixes and trivial functionalities, and 
reviewing/testing patches from others.  That was a fruitful and fun time for 
me.  Thank you a lot for helping me.

After that, I had to stay away from the community for some reason.  Now, I'd be 
happy if I can contribute to PostgreSQL again.  But please excuse me for my 
slow restart, as the blank period needs some rehabilitation.



Let me briefly introduce myself.  I'm MauMau, this is a nickname at home.  
And I'm Takayuki Tsunakawa, a male database engineer who works for Fujitsu in 
Japan.  I'm now able to participate in the community activity at work.

I've been visually impaired since birth, and now I'm almost blind (can only 
sense the light).  I'm using screen reader software to use PCs and smartphones. 
 As I'm using pgindent, I'm sure the source code style won't be bad.  But I 
might overlook some styling problems like indentation in the documentation 
patches.  I'd appreciate it if you could introduce a nice editor for editing 
SGML/XML documents.



I'm excited to join the great PostgreSQL community.  I'm dreaming PostgreSQL 
will evolve from the current "most advanced open source database" to "most 
popular and advanced database".  In that respect, I want to expand the 
PostgreSQL ecosystem (interoperability with other software), as well as adding 
new features.  Let me consult you about how to expand the ecosystem in another 
thread soon.

Finally, I'm wondering what direction PostgreSQL is headed for.  Especially, 
I'm curious about whether PostgreSQL will become a MPP database for OLTP and 
analytics by integrating with Postgres-XL/XC.  I don't yet figure out which 
segment PostgreSQL should aim for, now that Hadoop family is prominent in 
analytics and MySQL is still more popular in Web apps.  I'd like to know what 
community people are seeing in the future of PostgreSQL.


Regards
MauMau

Re: [HACKERS] pgaudit - an auditing extension for PostgreSQL

[MauMau]

, and alerting tool?  Is it possible and reasonably easy 
to integrate pgaudit with those tools?  The purpose of audit logging feature 
is not recording facts, but to enable timely detection of malicious actions. 
So, I think the ease of integration with those tools must be evaluated.  But 
I don't know about such tools.


I feel the current output format of pgaudit is somewhat difficult to treat:

* The audit log entries are mixed with other logs in the server log files, 
so the user has to extract the audit log lines from the server log files and 
save them elsewhere.  I think it is necessary to store audit logs in 
separate files.


* Does the command text need  around it in case it contains commas?


Regards
MauMau



--
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] pgaudit - an auditing extension for PostgreSQL

[MauMau]

Hello,

I had a quick look through the code and did some testing.  Let me give you 
some comments.  I will proceed with checking if pgaudit can meet PCI DSS 
requirements.


By the way, I'd like to use pgaudit with 9.2.  Is it possible with a slight 
modification of the code?  If it is, what features of pgaudit would be 
unavailable?  Could you support 9.2?



(1)
The build failed with PostgreSQL 9.5, although I know the README mentions 
that pgaudit supports 9.3 and 9.4.  The cause is T_AlterTableSpaceMoveStmt 
macro is not present in 9.5.  I could build and use pgaudit by removing two 
lines referring to that macro.  I tested pgaudit only with 9.5.



(2)
I could confirm that DECLARE is audit-logged as SELECT and FETCH/CLOSE are 
not.  This is just as expected.  Nice.



(3)
SELECT against a view generated two audit log lines, one for the view 
itself, and the other for the underlying table.  Is this intended?  I'm not 
saying that's wrong but just asking.



(4)
I'm afraid audit-logging DML statements on temporary tables will annoy 
users, because temporary tables are not interesting.  In addition, in 
applications which use the same temporary table in multiple types of 
transactions as follows, audit log entries for the DDL statement are also 
annoying.


BEGIN;
CREATE TEMPORARY TABLE mytemp ... ON COMMIT DROP;
DML;
COMMIT;

The workaround is CREATE TEMPORARY TABLE mytemp IF NOT EXIST ... ON COMMIT 
DELETE ROWS.  However, users probably don't (or can't) modify applications 
just for audit logging.



(5)
This is related to (4).  As somebody mentioned, I think the ability to 
select target objects of audit logging is definitely necessary.  Without 
that, huge amount of audit logs would be generated for uninteresting 
objects.  That would also impact performance.



(6)
What's the performance impact of audit logging?  I bet many users will ask 
about what percentage would the throughtput decrease by?  I'd like to know 
the concrete example, say, pgbench and DBT-2.



(7)
In README, COPY FROM/TO should be added to read and write respectively.


(8)
The code looks good.  However, I'm worried about the maintenance.  How can 
developers notice that pgaudit.c needs modification when they add a new SQL 
statement?  What keyword can they use to grep the source code to find 
pgaudit.c?



Regards
MauMau



--
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] [9.4 bug] The database server hangs with write-heavy workload on Windows

[MauMau]

From: MauMau maumau...@gmail.com
Thank you very much.  I didn't anticipate such a difficult complicated 
cause.  The user agreed to try the patch tonight.  I'll report back the 
result as soon as I got it from him.


The test ran successfully without hang for 24 hours.  It was run with your 
patch + the following:


BTW, in LWLockWaitForVar(), the first line of the following code fragment 
is not necessary, because lwWaitLink is set to head immediately.  I think 
it would be good to eliminate as much unnecessary code as possible from 
the spinlock section.


 proc-lwWaitLink = NULL;

 /* waiters are added to the front of the queue */
 proc-lwWaitLink = lock-head;



Regards
MauMau



--
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] [9.4 bug] The database server hangs with write-heavy workload on Windows

[MauMau]

From: Heikki Linnakangas hlinnakan...@vmware.com

Committed this.


Thank you very much.  I didn't anticipate such a difficult complicated 
cause.  The user agreed to try the patch tonight.  I'll report back the 
result as soon as I got it from him.


BTW, in LWLockWaitForVar(), the first line of the following code fragment is 
not necessary, because lwWaitLink is set to head immediately.  I think it 
would be good to eliminate as much unnecessary code as possible from the 
spinlock section.


 proc-lwWaitLink = NULL;

 /* waiters are added to the front of the queue */
 proc-lwWaitLink = lock-head;

Regards
MauMau



--
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] [9.4 bug] The database server hangs with write-heavy workload on Windows

[MauMau]

From: Craig Ringer cr...@2ndquadrant.com

It sounds like they've produced a test case, so they should be able to
with a bit of luck.

Or even better, send you the test case.


I asked the user about this.  It sounds like the relevant test case consists 
of many scripts.  He explained to me that the simplified test steps are:


1. initdb
2. pg_ctl start
3. Create 16 tables.  Each of those tables consist of around 10 columns.
4. Insert 1000 rows into each of those 16 tables.
5. Launch 16 psql sessions concurrently.  Each session updates all 1000 rows 
of one table, e.g., session 1 updates table 1, session 2 updates table 2, 
and so on.

6. Repeat step 5 50 times.

This sounds a bit complicated, but I understood that the core part is 16 
concurrent updates, which should lead to contention on xlog insert slots 
and/or spinlocks.




Your next step here really needs to be to make this reproducible against
a debug build. Then see if reverting the xlog scalability work actually
changes the behaviour, given that you hypothesised that it could be
involved.


Thank you, but that may be labor-intensive and time-consuming.  In addition, 
the user uses a machine with multiple CPU cores, while I only have a desktop 
PC with two CPU cores.  So I doubt I can reproduce the problem on my PC.


I asked the user to change S_UNLOCK to something like the following and run 
the test during this weekend (the next Monday is a national holiday in 
Japan).


#define S_UNLOCK(lock)  InterlockedExchange(lock, 0)

FYI, the user reported today that the problem didn't occur when he ran the 
same test for 24 hours on 9.3.5.  Do you see something relevant in 9.4?


Regards
MauMau



--
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] pgaudit - an auditing extension for PostgreSQL

[MauMau]

From: Simon Riggs si...@2ndquadrant.com

I hope we can get pgAudit in as a module for 9.5. I also hope that it
will stimulate the requirements/funding of further work in this area,
rather than squash it. My feeling is we have more examples of feature
sets that grow over time (replication, view handling, hstore/JSONB
etc) than we have examples of things languishing in need of attention
(partitioning).


I'm hoping PostgreSQL will have an audit trail feature.  I'd like to support 
your work by reviewing and testing, although I'm not sure I can fully 
understand the code soon.



Regards
MauMau



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

[HACKERS] [9.4 bug] The database server hangs with write-heavy workload on Windows

[MauMau]

Hello,

One user reported a hang problem with 9.4 beta2 on Windows.  The PostgreSQL 
is 64-bit version.  I couldn't find the cause, but want to solve the 
problem.  Could you help with this?


I heard that the user had run 16 concurrent psql sessions which executes 
INSERT and UPDATE statements, which is a write-intensive stress test.  He 
encountered the hang phenomenon twice, one of which occured several hours 
after the start of the test, and the other occured about an hour after the 
test launch.


The user gave me the stack traces, which I attached at the end of this mail. 
The problem appears to be related to the xlog insert scaling.  But I can't 
figure out where the root cause lies --- WAL slot handling, spinlock on 
Windows, or PGSemaphoreLock/UnLock on Windows?


The place I suspect is S_UNLOCK().  It doesn't use any memory barrier.  Is 
this correct on Intel64 processors?


#define S_UNLOCK(lock)  (*((volatile slock_t *) (lock)) = 0)


The rest of this mail is the stack trace:

`0043e0a8 7ff8`213d12ee : `0002 `0002 
`0001 ` : ntdll!ZwWaitForMultipleObjects+0xa
`0043e0b0 0001`401de68e : ` 7ff5`e000 
` `04fb6b40 : 
KERNELBASE!WaitForMultipleObjectsEx+0xe1
`0043e390 0001`4023cf11 : `02a55500 `1c117410 
80605042`36ad2501 0001`405546e0 : postgres!PGSemaphoreLock+0x6e 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\port\win32_sema.c @ 
145]
`0043e3e0 0001`4006203b : `f9017d56 `0022 
` `0400 : postgres!LWLockAcquireCommon+0x121 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\storage\lmgr\lwlock.c 
@ 625]
`0043e430 0001`4002c182 : `0005 ` 
`004e2f00 ` : postgres!XLogInsert+0x62b 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\access\transam\xlog.c 
@ 1110]
`0043e700 0001`400323b6 : ` ` 
`0a63 `0289de10 : postgres!log_heap_clean+0x102 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\access\heap\heapam.c @ 
6561]
`0043e7e0 0001`400320e8 : `040ec5c0 `0a63 
`0043f340 `040ec5c0 : postgres!heap_page_prune+0x2a6 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\access\heap\pruneheap.c 
@ 261]
`0043f2f0 0001`4002dc40 : `0057ea30 ` 
` `028d1810 : postgres!heap_page_prune_opt+0x148 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\access\heap\pruneheap.c 
@ 150]
`0043f340 0001`4002e7da : `028d1800 `0d26 
`0005 `0057ea30 : postgres!heapgetpage+0xa0 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\access\heap\heapam.c @ 
355]
`0043f3e0 0001`4002802c : ` ` 
` ` : postgres!heapgettup_pagemode+0x40a 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\access\heap\heapam.c @ 
944]
`0043f460 0001`40126507 : ` `001d 
` `001d : postgres!heap_getnext+0x1c 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\access\heap\heapam.c @ 
1478]
`0043f490 0001`401137f5 : `028d05b0 `028d06c0 
` `028a3d30 : postgres!SeqNext+0x27 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\executor\nodeseqscan.c 
@ 76]
`0043f4c0 0001`4010c7b2 : `0058dba0 `028d05b0 
` ` : postgres!ExecScan+0xd5 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\executor\execscan.c @ 
167]
`0043f520 0001`4012448d : `028d02e0 `028d02d8 
`028d02e0 `00585a00 : postgres!ExecProcNode+0xd2 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\executor\execprocnode.c 
@ 400]
`0043f550 0001`4010c772 : `00587bc0 `028d0110 
` `028d0258 : postgres!ExecModifyTable+0x10d 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\executor\nodemodifytable.c 
@ 926]
`0043f610 0001`4010bb6d : `028d0110 `00587bc0 
` `0056c740 : postgres!ExecProcNode+0x92 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\executor\execprocnode.c 
@ 377]
`0043f640 0001`401099d8 : `00570ff0 `0051e400 
`028d0110 `005831f0 : postgres!ExecutePlan+0x5d 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\executor\execmain.c @ 
1481]
`0043f680 0001`4024f813 : `00570ff0 `0051e468 
`0051c530 `005831f0 : postgres!standard_ExecutorRun+0xa8 
[d:\pginstaller.auto\postgres.windows-x64\src\backend\executor\execmain.c @ 
319]
`0043f6f0 0001`4024ff5a : 

Re: [HACKERS] [9.4 bug] The database server hangs with write-heavy workload on Windows

[MauMau]

From: Craig Ringer cr...@2ndquadrant.com

It'd be interesting and useful to run this test on a debug build of
PostgreSQL, i.e. one compiled against the debug version of the C library
and with full debuginfo not just minimal .pdb.


Although I'm not sure the user can do this now, I'll ask him anyway.


How were the stacks captured - what tool?


According to his mail, Windbg or userdump.exe.  I'll ask him about this.

Regards
MauMau



--
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] [9.4 bug] The database server hangs with write-heavy workload on Windows

[MauMau]

From: Andres Freund and...@2ndquadrant.com

What precisely do you mean with Intel64? 64bit x86 or Itanium?


64-bit x86, i.e. x86-64.



Also, what's the precise workload? Can you reproduce the problem?


IIUC, each client inserts 1000 records into one table, then repeats updating 
all those records.  I'll ask him again.


Regards
MauMau



--
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] pg_copy - a command for reliable WAL archiving

[MauMau]

From: Fujii Masao masao.fu...@gmail.com
What's the main purpose of this tool? If it's for WAL archiving, the tool 
name

pg_copy sounds too generic. We already have pg_archivecleanup, so maybe
pg_archivecopy or something is better for the consistency?

pg_copy in the patch copies the file to the destination in a
straightforward way,
i.e., directly copies the file to the dest file with actual name. This can 
cause
the problem which some people reported. The problem is that, when the 
server
crashes while WAL file is being archived by cp command, its 
partially-filled
WAL file remains at the archival area. This half-baked archive file can 
cause
various troubles. To address this, WAL file needs to be copied to the 
temporary
file at first, then renamed to the actual name. I think that pg_copy 
should

copy the WAL file in that way.


I intended to make pg_copy a straightforward replacement of cp/copy, which 
complements the missing sync.  Direct I/O and posix_fadvice() feature may be 
convenient but not essential for this utility.  cp/copy doesn't copy to a 
temporary file, and the problem can be solved easily by mv/move.  I wanted 
to keep pg_copy as generic as cp/copy, so that it can be used by some 
advanced features in the future, e.g. comprehensive backup/recovery 
management like RMAN (this example may not be best) when it's integrated 
into the core.


With that said, copying to a temporary file like dest.tmp and renaming it 
to dest sounds worthwhile even as a basic copy utility.  I want to avoid 
copying to a temporary file with a fixed name like _copy.tmp, because some 
advanced utility may want to run multiple instances of pg_copy to copy 
several files into the same directory simultaneously.  However, I'm afraid 
multiple dest.tmp files might continue to occupy disk space after 
canceling copy or power failure in some use cases, where the copy of the 
same file won't be retried.  That's also the reason why I chose to not use a 
temporary file like cp/copy.


Currently pg_copy always syncs the archive file, and there is no way to 
disable
that. But I'm sure that not everyone want to sync the archive file. So I 
think

that it's better to add the option specifying whether to sync the file
or not, into
pg_copy.


pg_copy is for copying a file reliably by syncing.  If sync is not 
necessary, people can use cp/copy.



Some users might want to specify whether to call posix_fadvise or not 
because

they might need to re-read the archvied files just after the archiving.
For example, network copy of the archived files from the archive area to
remote site for disaster recovery.


This sounds reasonable.  Do you have an idea on the switch name and the 
default behavior?



Do you recommend to use pg_copy for restore_command? If yes, it also 
should
be documented. And in the WAL restore case, the restored WAL files are 
re-read

soon by recovery, so posix_fadvise is not good in that case.

Direct I/O and posix_fadvise are used only for destination file. But why 
not

source file? That might be useful especially for restore_command case.


No, I don't think it's necessary to use pg_copy for restore_command.



At last, the big question is, is there really no OS command which provides
the same functionality as pg_copy does? If there is, I'd like to avoid 
duplicate

work basically.


If there exists such a command available in the standard OS installation, I 
want to use it.


Regards
MauMau



--
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 9.5: monitoring lock time for slow queries

[MauMau]

From: Alvaro Herrera alvhe...@2ndquadrant.com

Is this supposed to be session-local data, or is it visible from remote
sessions too?  How durable is it supposed to be?  Keep in mind that in
case of a crash, all pgstats data is erased.


I want it to be visible from other sessions.  I'm okay about the data 
erasure during recovery.  We can probably extend pg_statsinfo to save the 
new info for long-term trend analysis.  TBH, I want a feature like 
pg_statsinfo in core.


Regards
MauMau



--
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] Enable WAL archiving even in standby

[MauMau]

From: Fujii Masao masao.fu...@gmail.com

I'd propose the attached WIP patch which allows us to enable WAL archiving
even in standby. The patch adds always as the valid value of 
archive_mode.
If it's set to always, the archiver is started when the server is in 
standby
mode and all the WAL files that walreceiver wrote to the disk are archived 
by

using archive_command. Then, even after the server is promoted to master,
the archiver keeps archiving WAL files. The patch doesn't change the 
meanings

of the setting values on and off of archive_mode.

I think that this feature is useful for the case, e.g., where large 
database

needs to be replicated between remote servers. Imagine the situation where
the replicated database gets corrupted completely in the remote standby.
How should we address this problematic situation and restart the standby?

One approach is to take a fresh backup from the master and restore it onto
the standby. But since the database is large and there is long distance
between two servers, this approach might take a surprisingly long time.

Another approach is to restore the backup which was taken from the standby
before. But most of many WAL files which the backup needs might exist only
in the master (because WAL archiving cannot be enabled in the standby) and
they need to be transfered from the master to the standby via 
long-distance

network. So I think that this approach also would take a fairly long time.
To shorten that time, you may think that archive_command in the master can
be set so that it transfers WAL files from the master to the standby's
archival storage. I agree that this setting can accelerate the database 
restore

process. But this causes every WAL files to be transfered between remote
servers twice (one is by streaming replication, another is by 
archive_command),

and which is a waste of network bandwidth.


Great.  This is exactly what I hoped for disaster recovery, although I 
haven't looked at the patch yet.




Back to the patch. If archive_mode is set to always, archive_command is
always used to archive WAL files even during recovery. Do we need to 
separate

the command into two for master and standby, respectively? We can add
something like standby_archive_command parameter which is used to archive
only WAL files walreceiver writes. The other WAL files are archived by
archive_command. I'm not sure if it's really worth separating the command
that way. Is there any use case?


I don't see any reason to separate parameters.  I want the spec simple.



I've not included the update of document in the patch yet. If we agree to
support this feature, I will do the remaining work.


Could you consider adding a new section for disaster recovery that describes 
concrete parameter settings (e.g. how do we discard old archive WAL files 
after taking a base backup from standby, because backup label file is not 
created?).  Good luck!


Regards
MauMau




--
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] option -T in pg_basebackup doesn't work on windows

[MauMau]

Thank you.  The code looks correct.  I confirmed that the pg_basebackup 
could relocate the tablespace directory on Windows.


I marked this patch as ready for committer.

Regards
MauMau




--
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] Improvement of versioning on Windows, take two

[MauMau]

I confirmed that all issues are solved.  The patch content looks good, 
alghouth I'm not confident in Perl.  I marked this patch as ready for 
committer.  I didn't try the patch on MinGW.


Regards
MauMau



--
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 9.5: monitoring lock time for slow queries

[MauMau]

From: Pavel Stehule pavel.steh...@gmail.com

There are two relative independent tasks

a) monitor and show total lock time of living queries

b) monitor and log total lock time of executed queries.

I am interested by @b now. When we work with slow query log, then we would
to identify reason for long duration. Locks are important source of these
queries on some systems.


I'm interested in b, too.  I was thinking of proposing a performance 
diagnostics feature like Oracle's wait events (V$SYSTEM_EVENT and 
V$SESSION_EVENT).  So, if you do this, I'd like to contribute to the 
functional design, code and doc review, and testing.


The point is to collect as much information about bottlenecks as possible, 
including lock waits.  The rough sketch is:


What info to collect:
* heavyweight lock waits shown by pg_locks
* lightweight lock waits
* latch waits
* socket waits (mainly for client input)

How the info is delivered:
* pg_stat_system_events shows the accumulated total accross the server 
instance

* pg_stat_session_events shows the accumulated total for each session
* EXPLAIN ANALYZE and auto_explain shows the accumulated total for each 
query


We need to describe in the manual how to diagnose and tne the system with 
these event info.


Regards
MauMau



--
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 9.5: monitoring lock time for slow queries

[MauMau]

From: Pavel Stehule pavel.steh...@gmail.com

isn't it too heavy?


Are you concerned about the impactof collection overhead on the queries 
diagnosed?  Maybe not light, but I'm optimistic.  Oracle has the track 
record of long use, and MySQL provides performance schema starting from 5.6.


I have just terrible negative experience with Vertica, where this design 
is
used - almost all information about queries are available, but any query 
to

related tables are terrible slow, so I am inclined to more simple design
oriented to log based solution. Table based solutions is not practical 
when
you exec billions queries per day. I understand to motivation, but I 
afraid

so it can be very expensive and slow on highly load servers.


Which do you mean by query related to tables, the queries from 
applications being diagnosed, or the queries that diagnose the performance 
using statistics views?


Could you elaborate on your experience with Vertica?  That trouble may be 
just because Vertica's implementation is not refined.


I understand the feeling of being inclined to log based solution for its 
implementation simplicity.  However, the server log is difficult or 
impossible to access using SQL queries.  This prevents the development of 
performance diagnostics functionality in GUI administration tools.  Also, 
statistics views allow for easy access on PAAS like Amazon RDS and Heroku.


Regards
MauMau



--
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 9.5: monitoring lock time for slow queries

[MauMau]

From: Pavel Stehule pavel.steh...@gmail.com

2014-08-13 13:59 GMT+02:00 MauMau maumau...@gmail.com:

Are you concerned about the impactof collection overhead on the queries
diagnosed?  Maybe not light, but I'm optimistic.  Oracle has the track
record of long use, and MySQL provides performance schema starting from 
5.6.



partially, I afraid about total performance (about impact on IO) - when we
use a usual tables, then any analyses without indexes are slow, so you 
need

a indexes, and we cannot deferred index update. You should thinking about
retention policy - and without partitioning you got massive deletes. So I
cannot to imagine a usage of table based solution together with some 
higher
load. Our MVCC storage is not practical for storing only inserted data, 
and
some custom storage has no indexes - so this design is relative big 
project.


I prefer a possibility to read log via SQL (maybe some FDW) than use 
tables

for storing log. These tables can be relative very large in few days - and
we cannot to write specialized engine like MySQL simply.


I didn't mean performance statistics data to be stored in database tables. 
I just meant:


* pg_stat_system_events is a view to show data on memory, which returns one 
row for each event across the system.  This is similar to V$SYSTEM_EVENT in 
Oracle.


* pg_stat_session_events is a view to show data on memory, which returns one 
row for each event on one session.  This is similar to V$SESSION_EVENT in 
Oracle.


* The above views represent the current accumulated data like other 
pg_stat_xxx views.


* EXPLAIN ANALYZE and auto_explain shows all events for one query.  The lock 
waits you are trying to record in the server log is one of the events.


Regards
MauMau



--
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] option -T in pg_basebackup doesn't work on windows

[MauMau]

From: Amit Kapila amit.kapil...@gmail.com

During my recent work on pg_basebackup, I noticed that
-T option doesn't seem to work on Windows.
The reason for the same is that while updating symlinks
it doesn't consider that on Windows, junction points can
be directories due to which it is not able to update the
symlink location.
Fix is to make the code work like symlink removal code
in destroy_tablespace_directories.  Attached patch fixes
problem.


I could reproduce the problem on my Windows machine.

The code change appears correct, but the patch application failed against 
the latest source code.  I don't know why.  Could you confirm this?


patching file src/bin/pg_basebackup/pg_basebackup.c
Hunk #1 FAILED at 1119.
1 out of 1 hunk FAILED -- saving rejects to file 
src/bin/pg_basebackup/pg_basebackup.c.rej



On the following line, I think %d must be %u, because Oid is an unsigned 
integer.


 char*linkloc = psprintf(%s/pg_tblspc/%d, basedir, oid);

Regards
MauMau




--
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] pg_copy - a command for reliable WAL archiving

[MauMau]

I fixed some minor mistakes.

Regards
MauMau


pg_copy_v3.patch
Description: Binary data

-- 
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 smgrtruncate() avoid sending sinval message for temp relations

[MauMau]

Robert Haas robertmh...@gmail.com writes:

I'd support back-porting that commit to 9.1 and 9.2 as a fix for this
problem.  As the commit message says, it's dead simple.


From: Tom Lane t...@sss.pgh.pa.us

While I have no great objection to back-porting Heikki's patch, it seems
like a very large stretch to call this a root-cause fix.  At best it's
band-aiding one symptom in a rather fragile way.


Thank you, Robert san.  I'll be waiting for it to be back-ported to the next 
9.1/9.2 release.


Yes, I think this failure is only one potential symptom caused by the 
implemnentation mistake -- handling both latch wakeup and other tasks that 
wait on a latch in the SIGUSR1 handler.  Although there may be no such tasks 
now, I'd like to correct and clean up the implementation as follows to avoid 
similar problems in the future.  I think it's enough to do this only for 
9.5.  Please correct me before I go deeper in the wrong direction.


* The SIGUSR1 handler only does latch wakeup.  Any other task is done in 
other signal handlers such as SIGUSR2.  Many daemon postgres processes 
follow this style, but the normal backend, autovacuum daemons, and 
background workers don't now.


* InitializeLatchSupport() in unix_latch.c calls pqsignal(SIGUSR1, 
latch_sigusr1_handler).  Change the argument of latch_sigusr1_handler() 
accordingly.


* Remove SIGUSR1 handler registration and process-specific SIGUSR1 handler 
functions from all processes.  We can eliminate many SIGUSR1 handler 
functions which have the same contents.


Regards
MauMau





--
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] Improvement of versioning on Windows, take two

[MauMau]

From: Michael Paquier michael.paqu...@gmail.com

Oh yes, right. I don't really know how I missed this error when
testing v1. Adding an explicit call to
RemoveFile('src\timezone\win32ver.rc') for project postgres calms down
the build. Is the attached working for you?


Yes, the build succeeded.  I confirmed that the following files have version 
info.  However, unlike other files, they don't have file description.  Is 
this intended?


bin\isolationtester.exe
bin\pg_isolation_regress
bin\pg_regress.exe
bin\pg_regress_ecpg.exe
bin\zic.exe
lib\regress.dll


lib\dict_snowball.dll has no version properties.

Regards
MauMau




--
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] Improvement of versioning on Windows, take two

[MauMau]

From: Michael Paquier michael.paqu...@gmail.com

Please find attached a patch finishing the work begun during CF1. This
adds versioning support for all the remaining dll and exe files on
both MinGW and MSVC:
- regress.dll (MSVC only)
- isolationtester.exe
- pg_isolation_regress.exe
- pg_regress.exe
- pg_regress_ecpg.exe
- zic.exe
I will add this patch to CF2. Comments are welcome.


The patch applied cleanly to the latest source code.  But the build failed 
with MSVC 2008 Express due to the exact same LNK1104 error mentioned in:


http://www.postgresql.org/message-id/cab7npqrty1eikqgmz1_wbvhpvfyve9vma67ricepq6d8-eg...@mail.gmail.com

 LINK : fatal error LNK1104: ファイル 
'.\release\postgres\src_timezone_win32ver.obj' を開くことができません。


Regards
MauMau





--
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 smgrtruncate() avoid sending sinval message for temp relations

[MauMau]

I've tracked down the real root cause.  The fix is very simple.  Please 
check the attached one-liner patch.


The cause is that the temporary relations are truncated unconditionally 
regardless of whether they are accessed in the transaction or not.  That is, 
the following sequence of steps result in the hang:


1. A session creates a temporary table with ON COMMIT DELETE ROWS.  It adds 
the temporary table to the list of relations that should be truncated at 
transaction commit.


2. The session receives a sinval catchup signal (SIGUSR1) from another 
session.  It starts a transaction and processes sinval messages in the 
SIGUSR1 signal handler.  No WAL is output while processing the sinval 
messages.


3. When the transaction commits, the list of temporary relations are checked 
to see if they need to be truncated.


4. The temporary table created in step 1 is truncated.  To truncate a 
relation, Access Exclusive lock is acquired on it.  When hot standby is 
used, acquiring an Access Exclusive lock generates a WAL record 
(RM_STANDBY_ID, XLOG_STANDBY_LOCK).


5. The transaction waits on a latch for a reply from a synchronous standby, 
because it wrote some WAL.  But the latch wait never returns, because the 
latch needs to receive SIGUSR1 but the SIGUSR1 handler is already in 
progress from step 2.



The correct behavior is for the transaction not to truncate the temporary 
table in step 4, because the transaction didn't use the temporary table.


I confirmed that the fix is already in 9.3 and 9.5devel, so I just copied 
the code fragment from 9.5devel to 9.2.9.  The attached patch is for 9.2.9. 
I didn't check 9.4 and other versions.  Why wasn't the fix applied to 9.2?


Finally, I found a very easy way to reproduce the problem:

1. On terminal session 1, start psql and run:
 CREATE TEMPORARY TABLE t (c int) ON COMMIT DELETE ROWS;
Leave the psql session open.

2. On terminal session 2, run:
 pgbench -c8 -t500 -s1 -n -f test.sql dbname
[test.sql]
CREATE TEMPORARY TABLE t (c int) ON COMMIT DELETE ROWS;
DROP TABLE t;

3. On the psql session on terminal session 1, run any SQL statement.  It 
doesn't reply.  The backend is stuck at SyncRepWaitForLSN().


Regards
MauMau


sinval_catchup_hang_v3.patch
Description: Binary data

-- 
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 smgrtruncate() avoid sending sinval message for temp relations

[MauMau]

From: MauMau maumau...@gmail.com
I must add one thing.  After some client processes closed the connection 
without any hang, their server processes were stuck with a stack trace 
like this (I'll look for and show the exact stack trace tomorrow):


I found two kinds of stack traces:

#0  0x003199ec488f in poll () from /lib64/libc.so.6
#1  0x00609f24 in WaitLatchOrSocket ()
#2  0x0063ad92 in SyncRepWaitForLSN ()
#3  0x004ad474 in CommitTransaction ()
#4  0x004aef53 in CommitTransactionCommand ()
#5  0x0064b547 in shmem_exit ()
#6  0x0064b625 in proc_exit_prepare ()
#7  0x0064b6a8 in proc_exit ()
#8  0x00668a94 in PostgresMain ()
#9  0x00617f2c in ServerLoop ()
#10 0x0061ae96 in PostmasterMain ()
#11 0x005b2ccf in main ()

#0  0x003f4badf258 in poll () from /lib64/libc.so.6
#1  0x00619b94 in WaitLatchOrSocket ()
#2  0x00640c4c in SyncRepWaitForLSN ()
#3  0x00491c18 in RecordTransactionCommit ()
#4  0x00491d98 in CommitTransaction ()
#5  0x00493135 in CommitTransactionCommand ()
#6  0x00653fc5 in ProcessCatchupEvent ()
#7  0x006540ed in HandleCatchupInterrupt ()
#8  0x006533e3 in procsignal_sigusr1_handler ()
#9  signal handler called
#10 0x003f4bae96b0 in recv () from /lib64/libc.so.6
#11 0x005b75f6 in secure_read ()
#12 0x005c223b in pq_recvbuf ()
#13 0x005c263b in pq_getbyte ()
#14 0x0066e081 in PostgresMain ()
#15 0x00627d81 in PostmasterMain ()
#16 0x005c4803 in main ()


I'll try the fix tomorrow if possible.  What kind of problems do you hink 
of for back-patching?


I could reproduce the problem with 9.2.8, but have not yet with 9.5dev. 
I'll try with 9.2.9, and create the fix.


Regards
MauMau




--
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 smgrtruncate() avoid sending sinval message for temp relations

[MauMau]

From: Robert Haas robertmh...@gmail.com

I think the problem here is that it actually is possible for one
session to access the temporary objects of another session:
Now, we could prohibit that specific thing.  But at the very least, it
has to be possible for one session to drop another session's temporary
objects, because autovacuum does it eventually, and superusers will
want to do it sooner to shut autovacuum up.  So it's hard to reason
about whether and to what extent it's safe to not send sinval messages
for temporary objects.


I was a bit surprised to know that one session can access the data of 
another session's temporary tables.  That implenentation nay be complicating 
the situation -- extra sinval messages.




I think you might be approaching this problem from the wrong end,
though.  The question in my mind is: why does the
StartTransactionCommand() / CommitTransactionCommand() pair in
ProcessCatchupEvent() end up writing a commit record?  The obvious
possibility that occurs to me is that maybe rereading the invalidated
catalog entries causes a HOT prune, and maybe there ought to be some
way for a transaction that has only done HOT pruning to commit
asynchronously, just as we already do for transactions that only
modify temporary tables.  Or, failing that, maybe there's a way to
suppress synchronous commit for this particular transaction.


I could figure out what log record was output in the transaction started in 
ProcessCatchupEvent() by inserting elog() in XLogInsert().  The log record 
was (RM_STANDBY_ID, XLOG_STANDBY_LOCK).


The cause of the hang turned out clear.  It was caused as follows:

1. When a transaction commits which used a temporary table created with ON 
COMMIT DELETE ROWS, the sinval catchup signal (SIGUSR1) was issued from 
smgrtruncate().  This is because the temporary table is truncated at 
transaction end.


2. Another session, which is waiting for a client request, receives SIGUSR1. 
It calls ProcessCatchupEvent().


3. ProcessCatchupEvent() calls StartTransactionCommand(), emits the 
XLOG_STANDBY_LOCK WAL record, and then calls CommitTransactionCommand().


4. It then calls SyncRepWaitForLSN(), which in turn waits on the latch.

5. But the WaitLatch() never returns, because the session is already running 
inside the SIGUSR1 handler in step 2.  WaitLatch() needs SIGUSR1 to 
complete.


I think there is a problem with the latch and SIGUSR1 mechanism.  How can we 
fix this problem?


Regards
MauMau




--
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 smgrtruncate() avoid sending sinval message for temp relations

[MauMau]

From: Andres Freund and...@2ndquadrant.com

I think we should do what the first paragraph in
http://archives.postgresql.org/message-id/20140707155113.GB1136%40alap3.anarazel.de
outlined. As Tom says somewhere downthread that requires some code
review, but other than that it should get rid of a fair amount of
problems.


As mentioned in the mail I've just sent,  there seems to be a problem around 
the latch and/or sinval catchup implementation.


Or, is it bad that many things are done in SIGUSR1 handler?  If some 
processing in SIGUSR1 handler requires waiting on a latch, it hangs at 
WaitLatch().  Currently, the only processing in the backend which requires a 
latch may be to wait for the sync standby.  However, in the future, the 
latch may be used for more tasks.


Another problem is, who knows WaitLatch() can return prematurely (before the 
actual waited-for event does SetLatch()) due to the SIGUSR1 issued for 
sinval catchup event?


How should we tackle these problem?

Regards
MauMau



--
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 smgrtruncate() avoid sending sinval message for temp relations

[MauMau]

From: Tom Lane t...@sss.pgh.pa.us

[ sinval catchup signal - ProcessCatchupEvent - WaitLatch - deadlock ]


I must add one thing.  After some client processes closed the connection 
without any hang, their server processes were stuck with a stack trace like 
this (I'll look for and show the exact stack trace tomorrow):


WaitLatchOrSocket
SyncRepWaitForLSN
CommitTransaction
CommitTransactionCommand
ProcessCatchupEvent
...
shmem_exit
proc_exit_prepare
proc_exit
PostgresMain
...

The process appears to be hanging during session termination.  So, it's not 
the problem only during client request wait.




Another line of thought is that we've been way too uncritical about
shoving different kinds of events into the SIGUSR1 multiplexor.
It might be a good idea to separate high level interrupts from
low level ones, using say SIGUSR1 for the former and SIGUSR2
for the latter.  However, that doesn't sound very back-patchable,
even assuming that we can come up with a clean division.


This seems to be one step in the right direction.  There are two issues in 
the current implementation:


[Issue 1]
[ sinval catchup signal - ProcessCatchupEvent - WaitLatch - deadlock ]
This is (partly) because the latch wakeup and other processing use the same 
SIGUSR1 in normal backend, autovacuum launcher/worker, and the background 
worker with database access.  On the other hand, other background daemon 
processes properly use SIGUSR1 only for latch wakeup, and SIGUSR2 for other 
tasks.


[Issue 2]
WaitLatch() returns prematurely due to the sinval catchup signal, even 
though the target event (e.g. reply from standby) hasn't occurred and called 
SetLatch() yet.  This is because procsignal_sigusr1_handler() always calls 
latch_sigusr1_handler().

This is probably not related to the cause of the hang.

So, as you suggest, I think it would be a good idea to separate 
latch_sigusr1_handler() call into a different function solely for it like 
other daemon processes,  and leave the rest in procsignal_sigusr1_handler() 
and rename function to procsignal_sigusr2_handler() or procsignal_handler(). 
Originally, it's not natural that the current procsignal_sigusr1_handler() 
contains latch_sigusr1_handler() call, because latch processing is not based 
on the procsignal mechanism (SetLatch() doesn't call SendProcSignal()).


I'll try the fix tomorrow if possible.  What kind of problems do you hink of 
for back-patching?


Regards
MauMau




--
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 smgrtruncate() avoid sending sinval message for temp relations

[MauMau]

Hello,

I'm investigating a mysterious hang problem on PostgreSQL 9.2.8.  If many 
sessions use temporary tables whose rows are deleted on commit, the hang 
occurs.  I'd like to show you the stack trace, but I'm trying to figure out 
how to reproduce the problem.  IIRC, the stack trace was as follows.  The 
standby server was running normally.


...
SyncRepWaitForLSN
CommitTransaction
CommitTransactionCommand
ProcessCatchupEvent
HandleCatchupInterrupt
procsignal_sigusr1_handler
SIGUSR1 received
recv
...
ReadCommand
PostgresMain
...


Looking at smgrtruncate(), the sinval message is sent even when the 
truncated relation is a temporary relation.  However, I think the sinval 
message is not necessary for temp relations, because each session doesn't 
see the temp relations of other sessions.  So, the following code seems 
better.  This avoids sinval queue overflow which leads to SIGUSR1.  Is this 
correct?


if (SmgrIsTemp(reln))
   /* Do his on behalf of sinval message handler */
   smgrclosenode(reln-smgr_rnode);
else
   CacheInvalidateSmgr(reln-smgr_rnode);


Regards
MauMau



--
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 smgrtruncate() avoid sending sinval message for temp relations

[MauMau]

From: Tom Lane t...@sss.pgh.pa.us

This seems like a pretty unsafe suggestion, because the smgr level doesn't
know or care whether relations are temp; files are files.  In any case it
would only paper over one specific instance of whatever problem you're
worried about, because sinval messages definitely do need to be sent in
general.


I'm sorry I don't show the exact problem yet.  Apart from that, I understood 
that you insist it's not appropriate for smgr to be aware of whether the 
file is a temporary relation, in terms of module layering.  However, it 
doesn't seem so in the current implementation.  md.c, which is a layer under 
or part of smgr, uses SmgrIsTemp().  In addition, as the name suggests, 
SmgrIsTemp() is a function of smgr, which is defined in smgr.h.  So, it's 
not inappropriate for smgr to use it.


What I wanted to ask is whether and why sinval messages are really necessary 
for session-private objects like temp relations.  I thought shared inval is, 
as the name indicates, for objects shared among sessions.  If so, sinval 
for session-private objects doesn't seem to match the concept.


Regards
MauMau



--
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] Suppress autovacuum: found orphan temp table message

[MauMau]

From: Andres Freund and...@2ndquadrant.com

On 2014-07-22 10:09:04 +0900, MauMau wrote:

Is there any problem if we don't output the message?


Yes. The user won't know that possibly gigabytes worth of diskspace
aren't freed.


RemovePgTempFiles() frees the disk space by removing temp relation files at 
server start.  In addition, the temp relation files are not replicated to 
the standby server of streaming replication (this is the customer's case). 
So, those messages seem no more than just the noise.


With this, are those messages really necessary?

Regards
MauMau




--
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] Suppress autovacuum: found orphan temp table message

[MauMau]

From: Andres Freund and...@2ndquadrant.com

On 2014-07-22 17:05:22 +0900, MauMau wrote:
RemovePgTempFiles() frees the disk space by removing temp relation files 
at

server start.


But it's not called during a crash restart.


Yes, the comment of the function says:

* NOTE: we could, but don't, call this during a post-backend-crash restart
* cycle.  The argument for not doing it is that someone might want to 
examine

* the temp files for debugging purposes.  This does however mean that
* OpenTemporaryFile had better allow for collision with an existing temp
* file name.

But this is true if restart_after_crash = on in postgresql.conf, because the 
crash restart only occurs in that case.  However, in HA cluster, whether it 
is shared-disk or replication, restart_after_crash is set to off, isn't it?


Moreover, as the comment says, the behavior of keeping leftover temp files 
is for debugging by developers.  It's not helpful for users, isn't it?  I 
thought messages of DEBUG level is more appropriate, because the behavior is 
for debugging purposes.



In addition, the temp relation files are not replicated to
the standby server of streaming replication (this is the customer's
case).


So?


Yes, so nobody can convince serious customers that the current behavior 
makes real sense.


Could you please reconsider this?

Regards
MauMau



--
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] Suppress autovacuum: found orphan temp table message

[MauMau]

From: Andres Freund and...@2ndquadrant.com

On 2014-07-22 19:13:56 +0900, MauMau wrote:
But this is true if restart_after_crash = on in postgresql.conf, because 
the
crash restart only occurs in that case.  However, in HA cluster, whether 
it
is shared-disk or replication, restart_after_crash is set to off, isn't 
it?


In almost all setups I've seen it's set to on, even in HA scenarios.


I'm afraid that's because people don't notice the existence or purpose of 
this parameter.  The 9.1 release note says:


Add restart_after_crash setting which disables automatic server restart 
after a backend crash (Robert Haas)
This allows external cluster management software to control whether the 
database server restarts or not.


Reading this, I guess the parameter was introduced, and should be used, for 
HA environments controlled by the clusterware.  Restarting the database 
server on the same machine may fail, or the restarted server may fail again, 
due to the broken hardware components, so I guess it was considered better 
to let the clusterware determine what to do.



Moreover, as the comment says, the behavior of keeping leftover temp 
files

is for debugging by developers.  It's not helpful for users, isn't it?  I
thought messages of DEBUG level is more appropriate, because the behavior 
is

for debugging purposes.


GRR. That doesn't change the fact that there'll be files left over after
a crash restart.


Yes... that's a source of headache.  But please understand that there's a 
problem -- trying to leave temp relations just for debugging is causing a 
flood of messages, which the customer is actually concerned about.



I think you're making lots of noise over a trivial log message.


Maybe so, and I hope so.  I may be too nervous about what the customer will 
ask and/or request next.  If they request something similar to what I 
proposed here, let me consult you again.




Could you please reconsider this?


No. Just removing a warning isn't the way to solve this. If you want to
improve things you'll actually need to improve things not just stick
your head into the sand.



I have a few ideas below, but none of them seems better than the original 
proposal.  What do you think?


1. startup process deletes the catalog entries and data files of leftover 
temp relations at the end of recovery.
This is probably difficult, impossible or undesirable, because the startup 
process cannot access system catalogs.  Even if it's possible, it is against 
the developers' desire to leave temp relation files for debugging.


2. autovacuum launcher deletes the catalog entries and data files of 
leftover temp relations during its initialization.
This may be possible, but it is against the developers' desire to leave temp 
relation files for debugging.


3. Emit the orphan temp relation message only when the associated data 
file actually exists.
autovacuum workers check if the temp relation file is left over with stat(). 
If not, delete the catalog entry in pg_class silently.
This sounds reasonable because the purpose of the message is to notify users 
of potential disk space shortage.  In the streaming replication case, no 
data files should exist on the promoted new primary, so no messages should 
be emitted.
However, in the shared-disk HA cluster case, the temp relation files are 
left over on the shared disk, so this fix doesn't improve anything.


4. Emit the orphan temp relation message only when restart_after_crash is 
on.

i.e.
 ereport(restart_after_crash ? LOG : DEBUG1, ...


Regards
MauMau



--
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] Suppress autovacuum: found orphan temp table message

[MauMau]

Hello, Robert-san, Andres-san, Tom-san,

From: Andres Freund and...@2ndquadrant.com

a) There very well could be a backend reconnecting to that
  backendId. Then we potentially might try to remove the temp schema
  from two backends - I'm not sure that's always going to end up going
  well. There's already a race window, but it's pretty darn unlikely to
  hit it right now because the wraparound case pretty much implies that
  nothing has used that backendid slot for a while.
  I guess we could do something like:

  LockDatabaseObject(tempschema);
  if (SearchSysCacheExists1)
 /* bailout */
  performDeletion(...);

b) I think at the very least we also need to call RemovePgTempFiles()
  during crash restart.


Thank you for showing the direction.  I'll investigate the code.  But that 
will be tomorrow as it's already past midnight.


Could it be included in 9.2.9 if I could submit the patch tomorrow? (I'm not 
confident I can finish it...)  I'd really appreciate it if you could create 
the fix, if tomorrow will be late.


Regards
MauMau



--
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] Suppress autovacuum: found orphan temp table message

[MauMau]

From: Andres Freund and...@2ndquadrant.com

On 2014-07-18 23:38:09 +0900, MauMau wrote:
So, I propose a simple fix to change the LOG level to DEBUG1.  I don't 
know

which of DEBUG1-DEBUG5 is appropriate, and any level is OK.  Could you
include this in 9.2.9?


Surely that's the wrong end to tackle this from. Hiding actual problems
is a seriously bad idea.


No, there is no serious problem in the user operation in this situation. 
Server crash cannot be avoided, and must be anticipated.  The problem is 
that PostgreSQL makes users worried about lots of (probably) unnecessary 
messages.


Is there any problem if we don't output the message?  According the past 
discussion in this community, the user can just ignore this message.  If 
there is no problem, and the customer asks, I'd like to propose it as one 
temporary measure.


Regards
MauMau



--
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] Suppress autovacuum: found orphan temp table message

[MauMau]

From: Andres Freund and...@2ndquadrant.com

On 2014-07-18 23:38:09 +0900, MauMau wrote:

LOG:  autovacuum: found orphan temp table pg_temp_838.some_table in
database some_db
LOG:  autovacuum: found orphan temp table pg_temp_902.some_table in
database some_db


So they had server crashes of some form before - otherwise they
shouldn't see this because during ordinary shutdown the schema will have
been dropped. C.f. RemoveTempRelationsCallback().


Yes, they are using streaming replication, and experienced failover.



1. Why and when are these messages are output?  Do we have to do
something?


Yes, you should investigate how the situation came to be.


Yes, as mentioned before, I know the reason thanks to the past mails of this 
community.  The situation is like this:


1. The applications were using temporary tables.  The rows for temporary 
tables were created in pg_namespace (one row for pg_temp_n) and pg_class. 
Those rows were replicated to the standby.   The data files for the 
temporary tables were not replicated.

2. The server crashed the standby was promoted to the primary.
3. The new primary performed recovery, but the rows for temporary tables in 
the system catalog were left.
4. The applications resumed processing.  However, the workload got lighter, 
so the zonbie pg_temp_n entries were not recycled.
5. autovacuum workers found the zonbie temporary table entries in the system 
catalog, repeatedly emitting lots of messages.




3. Doesn't the output processing of these messages or its cause affect
performance?  We happen to be facing a performance problem, the cause of
which we haven't found yet.


Meh. If that's the bottleneck you've bigger problems.


I guess the performance problem they are facing is not due to this message 
output, but I don't have evidence.  Anyway, I think worrying users with lots 
of messages is evil itself.



So, I propose a simple fix to change the LOG level to DEBUG1.  I don't 
know

which of DEBUG1-DEBUG5 is appropriate, and any level is OK.  Could you
include this in 9.2.9?


Surely that's the wrong end to tackle this from. Hiding actual problems
is a seriously bad idea.


No, there is no serious problem in the user operation in this situation. 
Server crash cannot be avoided, and must be anticipated.  The problem is 
that PostgreSQL makes users worried about lots of (probably) unnecessary 
messages.





It'd be nice if we had infrastructure to do this at startup, but we
don't...


Yes, ideally so.  It is the responsibility of the database server to clean 
up the zombie metadata (catalog entries).  But I understand there's not such 
infrastracture now.  If it's not (easily) possible, the best and only thing 
is to not make users concerned.  Is there any reason to output the message 
in the viewpoint of users, not the viewpoint of developers?


The problem is pressing.  The customer is trying to use PostgreSQL for very 
mission-critical system, and I wish PostgreSQL will get high reputation. 
Could you include this in 9.2.9?


Regards
MauMau




--
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] pg_ctl always uses the same event source

[MauMau]

From: Magnus Hagander mag...@hagander.net
On Fri, Jul 18, 2014 at 5:33 AM, Amit Kapila amit.kapil...@gmail.com 
wrote:

On Thu, Jul 17, 2014 at 4:51 PM, Magnus Hagander mag...@hagander.net
wrote:


Did anyone actually test this patch? :)

I admit I did not build it on Windows specifically because I assumed
that was done as part of the development and review. And the changes
to pg_event.c can never have built, since the file does not include
the required header.


I have tested it on Windows and infact on Linux as well to see if there
is any side impact before marking it as Ready For Committer.

It seems to me that the required header is removed in last version
(pg_ctl_eventsrc_v11) where MessageBox() related changes have been
removed from patch as per recent discussion.  Sorry for not being able
to check last version posted.


Gotcha. Thanks for clarifying, and I apologize if I came across a bit
harsh even with the smiley.


I'm sorry to have caused both of you trouble.  I have to admit that I didn't 
compile the source when I removed the MessageBox()-related changes.  The 
attached patch fixes that.  I confirmed successful build this time.


Thank you for committing, Magnus-san, and thank you very much for kind and 
repeated review and help, Amit-san.



Regards
MauMau


pgevent.patch
Description: Binary data

-- 
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] pg_ctl always uses the same event source

[MauMau]

From: Magnus Hagander mag...@hagander.net

There's also the change to throw an error if the source is already
registered, which is potentially a bigger problem. Since the default
will be the same everywhere, do we really want to throw an error when
you install a second version, now that this is the normal state?

There's also definitely a problem in that that codepath fires up a
MessageBox, but it's just a function called in a DLL. It might just as
well be called from a background service or from within an installer
with no visible desktop, at which point the process will appear to be
hung... I'm pretty sure you're not allowed to do that.


I got what you mean.  I removed changes in pgevent.c except for the default 
name.  I attached the revised patch.




More importantly, isn't it wrong to claim it will only be used for
register and unregister? If we get an early failure in start, for
example, there are numerous codepaths that will end up calling
write_stderr(), which will use the eventlog when running as a service.
Shouldn't the -e parameter be moved under common options?



Yes, you are right.  -e is effective only if pg_ctl is invoked as a 
Windows

service.  So it is written at register mode.  That is, -e specifies the
event source used by the Windows service which is registered by pg_ctl
register.


Oh, right. I see what you mean now. That goes for all parameters
though, including -D, and we don't specify those as register mode
only, so I still think it's wrong to place it there. It is now grouped
with all other parameters that we specifically *don't* write to the
commandline of the service.


Sorry, but I'm probably not understanding your comment.  This may be due to 
my English capability.  -e is effective only on Windows, so it is placed in 
section Options for Windows.  And I could not find a section named Common 
options.  -e is currently meangful only with register mode, so it is placed 
at register mode in Synopsis section.  For example, -D is not attached to 
kill mode.


Do you suggest that -e should be attached to all modes in Synopsis section, 
or -e should be placed in the section Options instead of Options for 
Windows?



Regards
MauMau


pg_ctl_eventsrc_v11.patch
Description: Binary data

-- 
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] pg_ctl always uses the same event source

[MauMau]

From: Amit Kapila amit.kapil...@gmail.com
So as a conclusion, the left over items to be handled for patch are:

1. Remove the new usage related to use of same event source name
for registration from pgevent.
2. Document the information to prevent loss of messages in some
scenarios as explained above.


I noticed the continued discussion after I had prepared and submitted the 
revised patch.  OK, how about the patch in the previous mail, Magnus-san?


Regards
MauMau



--
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] pg_ctl always uses the same event source

[MauMau]

From: Magnus Hagander mag...@hagander.net

Is there a reason for there still being changes in guc.c, pgevent.c
etc? Shouldn't it all be confined to pg_ctl now? That's my
understanding from the thread that that's the only part we care about.


Yes, strictly speaking, those are useful for the original proposal. 
However, they are still useful as refactoring, since the current code has 
the default event source PostgreSQL in many places.  Defining the default 
name only at one location would make it easier for vendors like EnterpriseDB 
to change the default name for their products.  So I hope this will also be 
included if you don't want to reject it by all means.




More importantly, isn't it wrong to claim it will only be used for
register and unregister? If we get an early failure in start, for
example, there are numerous codepaths that will end up calling
write_stderr(), which will use the eventlog when running as a service.
Shouldn't the -e parameter be moved under common options?


Yes, you are right.  -e is effective only if pg_ctl is invoked as a Windows 
service.  So it is written at register mode.  That is, -e specifies the 
event source used by the Windows service which is registered by pg_ctl 
register.




I also think we should have the documentation specifically note that
regular postgres output still goes to whatever the backend is
configured to do. (and of course, any failure within the backend
*before* we have parsed the config file for example will still go to
the default source, and not the pg_ctl source - so we need to make it
really clear that this is *only* for output from pg_ctl).


I see.  I added this clarification to the description of -e.  I would 
appreciate it if you could correct my poor English when committing, if it 
needs improvement.


Regards
MauMau


pg_ctl_eventsrc_v10.patch
Description: Binary data

-- 
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] pg_ctl always uses the same event source

[MauMau]

From: Magnus Hagander mag...@hagander.net

Well, it does in a couple of places. I'm nto sure it's that important
(as nobody has AFAIK ever requested that change from for example EDB),
but it's not a bad thing. However, with a hardcoded service name, I
think the changes to pg_event.c are probably both not necessary and
actually bad - as you'll now start getting errors in more harmless
scenarios.


Thank you.  OK, in fact, all I want in pgevent.c is this:

! char  event_source[256] = PostgreSQL;
...
! char  event_source[256] = DEFAULT_EVENT_SOURCE;

But what kind of errors do we get with other changes in pgevent.c?  I made 
these changes according to Amit-san's notice (please look at his comment 
upthread), and I think he is right.




Oh, right. I see what you mean now. That goes for all parameters
though, including -D, and we don't specify those as register mode
only, so I still think it's wrong to place it there. It is now grouped
with all other parameters that we specifically *don't* write to the
commandline of the service.


OK, let me reconsider this tomorrow.  It's already after midnight here in 
Japan, and I have to go to bed so that I can wake up tomorrow.


Regards
MauMau



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

[MauMau]

Hello,

I've finished reviewing the code.  I already marked this patch as waiting on 
author.  I'll be waiting for the revised patch, then proceed to running the 
program when the patch seems reasonable.


(12)
Like worker_spi, save and restore errno in signal handlers.


(13)
Remove the following comment, and similar ones if any, because this module 
is to be included in 9.5 and above.


/*
 * In Postgres version 9.4 and above, we use the dynamic background worker
 * infrastructure for BlockReaders, and the BufferSaver process does the
 * legwork of registering the BlockReader workers.
 */


(14)
Expand the body following functions at their call sites and remove the 
function definition, because they are called only once.  It would be 
straightforward to see the processing where it should be.


* DefineGUCs
* CreateDirectory


(15)
I don't understand the use case of pg_hibernator.enabled.  Is this really 
necessary?  In what situations does this parameter really matter?  If it's 
not crucial, why don't we remove it and make the specification simpler?



(16)
As mentioned above, you can remove CreateDirectory().  Instead, you can just 
mkdir() unconditionally and check the result, without first stat()ing.



(17)
Use AllocateDir/ReadDir/FreeDir instead of opendir/readdir/closedir in the 
server process.  Plus, follow the error handling style of other source files 
using AllocateDir.



(18)
The local variable hibernate_dir appears to be unnecessary because it always 
holds SAVE_LOCATION as its value.  If so, remove it.



(19)
I think the severity below should better be WARNING, but I don't insist.

ereport(LOG, (errmsg(registration of background worker failed)));


(20)
iff should be if.

/* Remove the element from pending list iff we could register a worker 
successfully. */




(21)
How is this true?  Does the shared system catalog always have at least one 
shared buffer?


  /* Make sure the first buffer we save belongs to global object. */
  Assert(buf-database == InvalidOid);
...
   * Special case for global objects. The sort brings them to the
   * front of the list.


(22)
The format should be %u, not %d.

 ereport(log_level,
   (errmsg(writer: writing block db %d filenode %d forknum %d blocknum 
%d,

 database_counter, prev_filenode, prev_forknum, buf-blocknum)));


(23)
Why is the first while loop in BlockReaderMain() necessary?  Just opening 
the target save file isn't enough?




(24)
Use MemoryContextAllocHuge().  palloc() can only allocate chunks up to 1GB.

 * This is not a concern as of now, so deferred; there's at least one other
 * place that allocates (NBuffers * (much_bigger_struct)), so this seems to
 * be an acceptable practice.
 */

saved_buffers = (SavedBuffer *) palloc(sizeof(SavedBuffer) * NBuffers);


(25)
It's better for the .save files to be created per tablespace, not per 
database.  Tablespaces are more likely to be allocated on different storage 
devices for I/O distribution and capacity.  So, it would be more natural to 
expect that we can restore buffers more quickly by letting multiple Block 
Readers do parallel I/O on different storage devices.



(26)
Reading the below description in the documentation, it seems that Block 
Readers can exit with 0 upon successful completion, because bgw_restart_time 
is set to BGW_NEVER_RESTART.


If bgw_restart_time for a background worker is configured as 
BGW_NEVER_RESTART, or if it exits with an exit code of 0 or is terminated by 
TerminateBackgroundWorker, it will be automatically unregistered by the 
postmaster on exit.



(27)
As others said, I also think that Buffer Saver should first write to a temp 
file, say *.tmp, then rename it to *.save upon completion.  That prevents 
the Block Reader from reading possibly corrupted half-baked file that does 
not represent any hibernated state.



Regards
MauMau




--
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 or improvement?] Correctly place DLLs for ECPG apps in bin folder

[MauMau]

From: Alvaro Herrera alvhe...@2ndquadrant.com

I think the suggestion by Peter Eisentraut upthread was pretty
reasonable -- the Makefiles are already aware that they are building a
shared library, so scrape the info off them.  The less hardcoded stuff
in the msvc framework, the better.


I overlooked his mail.  Do you mean something like this? (I don't know yet 
how to express this in Perl)


for each Makefile in under top_dir_in_source_tree or src/interfaces
{
 if (Makefile contains SO_MAJOR_VERSION)
 {
   extract NAME value from Makefile
   move lib/lib${NAME}.dll to bin/
 }
}

But the source tree contains as many as 206 Makefiles.  I'm worried that it 
will waste the install time.  Should all these Makefiles be examined, or 16 
Makefiles in src/interfaces/?


Regards
MauMau





--
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: bug fix?] Connection attempt block forever when the synchronous standby is not running

[MauMau]

From: Amit Kapila amit.kapil...@gmail.com

On Fri, Jul 4, 2014 at 7:29 PM, MauMau maumau...@gmail.com wrote:

[Hypothesis]
Why does the connection processing emit WAL?

Probably, it did page-at-a-time vacuum during access to pg_database and

pg_authid for client authentication.  src/backend/access/heap/README.HOT
describes:

I agree with your analysis that it can happen during connection
attempt.


Thank you.  I'm relieved the cause seems correct.




But the customer could not reproduce the problem when he performed the

same archive recovery from the same base backup again.  Why?  I guess the
autovacuum daemon vacuumed the system catalogs before he attempted to
connect to the database.



One way to confirm could be to perform the archive recovery by
disabling autovacuum.


Yeah, I thought of that too.  Unfortunately, the customer deleted the the 
base backup for testing.




Another thing which I am wondering about is can't the same happen
even for Read Only transaction (incase someone does Select which
prunes the page).


I'm afraid about that, too.

Regards
MauMau



--
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: bug fix?] Connection attempt block forever when the synchronous standby is not running

[MauMau]

From: Rajeev rastogi rajeev.rast...@huawei.com
As of now there is no solution for this in PostgreSQL but I had submitted a 
patch Standalone synchronous master in
9.4 2014-01 CommitFest, which was rejected because of some issues. This 
patch was meant to degrade the synchronous

level of master, if all synchronous standbys are down.
I plan to resubmit this with better design sometime in 9.5.


Although I only read some mails of that thread, I'm sure your proposal is 
what many people would appreciate.  Your new operation mode is equivalent to 
the maximum availability mode of Oracle Data Guard, isn't it?  I'm looking 
forward to it.  Good luck.



==
Maximum availability
This protection mode provides the highest level of data protection that is 
possible without compromising the availability of a primary database. 
Transactions do not commit until all redo data needed to recover those 
transactions has been written to the online redo log and to at least one 
standby database. If the primary database cannot write its redo stream to at 
least one standby database, it effectively switches to maximum performance 
mode to preserve primary database availability and operates in that mode 
until it is again able to write its redo stream to a standby database.


This protection mode ensures zero data loss except in the case of certain 
double faults, such as failure of a primary database after failure of the 
standby database.


Maximum performance
This is the default protection mode. It provides the highest level of data 
protection that is possible without affecting the performance of a primary 
database. This is accomplished by allowing transactions to commit as soon as 
all redo data generated by those transactions has been written to the online 
log. Redo data is also written to one or more standby databases, but this is 
done asynchronously with respect to transaction commitment, so primary 
database performance is unaffected by delays in writing redo data to the 
standby database(s).


This protection mode offers slightly less data protection than maximum 
availability mode and has minimal impact on primary database performance.

==


Regards
MauMau



--
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: bug fix?] Connection attempt block forever when the synchronous standby is not running

[MauMau]

From: Tom Lane t...@sss.pgh.pa.us

problem, the user might not realize he's got one until he starts to wonder
why autovac/autoanalyze aren't working.


In autovacuum.c, autovacuum workers avoid waiting for the standby by doing:

/*
 * Force synchronous replication off to allow regular maintenance even if
 * we are waiting for standbys to connect. This is important to ensure we
 * aren't blocked from performing anti-wraparound tasks.
 */
if (synchronous_commit  SYNCHRONOUS_COMMIT_LOCAL_FLUSH)
 SetConfigOption(synchronous_commit, local,
 PGC_SUSET, PGC_S_OVERRIDE);

Regards
MauMau



--
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: bug fix?] Connection attempt block forever when the synchronous standby is not running

[MauMau]

From: Tom Lane t...@sss.pgh.pa.us

Andres Freund and...@2ndquadrant.com writes:

On 2014-07-07 09:57:20 -0400, Tom Lane wrote:

Well, see the comment that explains why the logic is like this now:



I think we should 'simply' make sequences assign a toplevel xid - then
we can get rid of that special case in RecordTransactionCommit(). And I
think the performance benefit of not having to wait on XLogFlush() for
readonly xacts due to hot prunes far outweighs the decrease due to the
xid assignment/commit record.  I don't think that nextval()s are called
overly much without a later xid assigning statement.


Yeah, that could well be true.  I'm not sure if there are any other cases
where we have non-xid-assigning operations that are considered part of
what has to be flushed before reporting commit; if there are not, I'd
be okay with changing nextval() this way.


Thank you all for letting me know your thoughts.  I understood and agree 
that read-only transactions, including the connection establishment one, 
should not wait for the standby nor the XLOG flush at commit, and the 
current documentation/specification should not be changed.


I'll consider how to fix this problem, learning the code, then I'll ask for 
review.  I'd like to submit the patch for next CF if possible.


From: Fujii Masao masao.fu...@gmail.com
Sounds good direction. One question is: Can RecordTransactionCommit() 
avoid

waiting for not only replication but also local WAL flush safely in
such read-only
transaction case?


I'd appreciate any opinion on this, too.

Regards
MauMau




--
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 or improvement?] Correctly place DLLs for ECPG apps in bin folder

[MauMau]

From: Asif Naeem anaeem...@gmail.com

Other than my pervious comments, patch looks good to me. Thanks.


Thanks for reviewing.  I understood that your previous comment was to 
suggest copying the desired DLLs directly from Release/Debug folder to bin. 
But I'm afraid it cannot be done cleanly...  CopySolutionOutput() copies all 
DLLS to lib folder with no exception as follows:


  if ($1 == 1)
  {
   $dir = bin;
   $ext = exe;
  }
  elsif ($1 == 2)
  {
   $dir = lib;
   $ext = dll;
  }

It seems like I have to do something like this, listing the relevant DLL 
names anyway.  I don't think this is not cleaner.


  if ($1 == 1)
  {
   $dir = bin;
   $ext = exe;
  }
  elsif ($1 == 2  /* the project is libpq, libecpg, etc. */)
  {
   $dir = bin;
   $ext = dll;
  }
  elsif ($1 == 2)
  {
   $dir = lib;
   $ext = dll;
  }

What do you think?  Am I misunderstanding your suggestion?

Regards
MauMau



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

[MauMau]

Hello,

I'm reviewing this patch.  I find this feature useful, so keep good work.

I've just begun the review of pg_hibernate.c, and finished reviewing other 
files.  pg_hibernate.c will probably take some time to review, so let me 
give you the result of my review so far.  I'm sorry for trivial comments.



(1)
The build on Windows with MSVC 2008 Express failed.  The error messages are 
as follows (sorry, they are in Japanese):




 .\contrib\pg_hibernator\pg_hibernator.c(50): error C2373: 
'CreateDirectoryA' : 再定義されています。異なる型修飾子です。
 .\contrib\pg_hibernator\pg_hibernator.c(196): error C2373: 
'CreateDirectoryA' : 再定義されています。異なる型修飾子です。
 .\contrib\pg_hibernator\pg_hibernator.c(740): error C2198: 
'CreateDirectoryA' : 呼び出しに対する引数が少なすぎます。



The cause is that CreateDirectory() is an Win32 API.  When I renamed it, the 
build succeeded.  I think you don't need to make it a function, because its 
processing is simple and it is used only at one place.



(2)
Please look at the following messages.  Block Reader read all blocks 
successfully but exited with 1, while the Buffer Reader exited with 0.  I 
think the Block Reader also should exit 0 when it completes its job 
successfully, because the exit code 0 gives the impression of success.


LOG:  worker process: Buffer Saver (PID 2984) exited with exit code 0
...
LOG:  Block Reader 1: all blocks read successfully
LOG:  worker process: Block Reader 2 (PID 6064) exited with exit code 1

In addition, the names Buffer Saver and Block Reader don't correspond, 
while they both operate on the same objects.  I suggest using the word 
Buffer or Block for both processes.



(3)
Please add the definition of variable PGFILEDESC in Makefile.  See 
pg_upgrade_support's Makefile for an example.  It is necessary for the 
versioning info of DLLs on Windows.  Currently, other contrib libraries lack 
the versioning info.  Michael Paquier is adding the missing versioning info 
to other modules for 9.5.



(4)
Remove the following #ifdef, because you are attempting to include this 
module in 9.5.


#if PG_VERSION_NUM = 90400


(5)
Add header comments at the beginning of source files like other files.


(6)
Add user documentation SGML file in doc/src/sgml instead of README.md.
For reference, I noticed the following mistakes in README.md:

instalation - installation
`Block Reader` threads - `Block Reader` processes


(7)
The message

could not open \%s\: %m

should better be:

could not open file \%s\: %m

because this message is already used in many places.  Please find and use 
existing messages for other places as much as possible.  That will reduce 
the translation efforts for other languages.



(8)
fileClose() never returns false despite its comment:

/* Returns true on success, doesn't return on error */


(9)
I think it would be better for the Block Reader to include the name and/or 
OID of the database in its success message:


LOG:  Block Reader 1: restored 14 blocks


(10)
I think the save file name should better be database OID.save instead of 
some arbitrary integer.save.  That also means %u.save instead of %d.save. 
What do you think?



(11)
Why don't you remove misc.c, removing unnecessary functions and keeping just 
really useful ones in pg_hibernator.c?  For example, I don't think 
fileOpen(), fileClose(), fileRead() and fileWrite() need not be separate 
functions (see src/backend/postmaster/pgstat.c).  And, there's only one call 
site of the following functions:


readDBName
getSavefileNameBeingRestored
markSavefileBeingRestored

Regards
MauMau



--
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: bug fix?] Connection attempt block forever when the synchronous standby is not running

[MauMau]

Hello,

My customer reported a strange connection hang problem.  He and I couldn't 
reproduce it.  I haven't been able to understand the cause, but I can think 
of one hypothesis.  Could you give me your opinions on whether my hypothesis 
is correct, and a direction on how to fix the problem?  I'm willing to 
submit a patch if necessary.



[Problem]
The customer is using synchronous streaming replication with PostgreSQL 
9.2.8.  The cluster consists of two nodes.


He performed archive recovery test like this:

1. Take a base backup.  At that time, some notable settings in 
postgresql.conf are:

synchronous_standby_names = 'node2'
autovacuum = on
# synchronous_commit is commented out, so it's on by default

2. Some update operations.  I don't know what.

3. Shutdown the primary and promote the standby.

4. Shutdown the new primary.

5. Perform archive recovery.  That is, restore the base backup, create 
recovery.conf, and do pg_ctl start.


6. Immediately after the archive recovery is complete, connect to the 
database server and perform some queries to check user data.


The steps 5 and 6 are done in some recovery script.

However, the connection attempt in step 6 got stuck for 12 hours, and the 
test was canceled.  The stack trace was:


#0  0x003f4badf258 in poll () from /lib64/libc.so.6
#1  0x00619b94 in WaitLatchOrSocket ()
#2  0x00640c4c in SyncRepWaitForLSN ()
#3  0x00491c18 in RecordTransactionCommit ()
#4  0x00491d98 in CommitTransaction ()
#5  0x00493135 in CommitTransactionCommand ()
#6  0x0074938a in InitPostgres ()
#7  0x0066ddd7 in PostgresMain ()
#8  0x00627d81 in PostmasterMain ()
#9  0x005c4803 in main ()

The connection attempt is waiting for a reply from the standby.  This is 
strange, because we didn't anticipate that the connection establishment (and 
subsequent SELECT queries) would update something and write some WAL.  The 
doc says:


http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION

When requesting synchronous replication, each commit of a write transaction 
will wait until confirmation is received that the commit has been written to 
the transaction log on disk of both the primary and standby server.

...
Read only transactions and transaction rollbacks need not wait for replies 
from standby servers. Subtransaction commits do not wait for responses from 
standby servers, only top-level commits.



[Hypothesis]
Why does the connection processing emit WAL?

Probably, it did page-at-a-time vacuum during access to pg_database and 
pg_authid for client authentication.  src/backend/access/heap/README.HOT 
describes:


Effectively, space reclamation happens during tuple retrieval when the
page is nearly full (10% free) and a buffer cleanup lock can be
acquired.  This means that UPDATE, DELETE, and SELECT can trigger space
reclamation, but often not during INSERT ... VALUES because it does
not retrieve a row.

But the customer could not reproduce the problem when he performed the same 
archive recovery from the same base backup again.  Why?  I guess the 
autovacuum daemon vacuumed the system catalogs before he attempted to 
connect to the database.


Is this correct?


[How to fix]
Of course, adding -o '-c synchronous_commit=local' or -o '-c 
synchronous_standby_names=' to pg_ctl start in the recovery script would 
prevent the problem.


But isn't there anything to fix in PostgreSQL?  I think the doc needs 
improvement so that users won't misunderstand that only write transactions 
would block at commit.


Do you think something else should be done?  I guess pg_basebackup, 
pg_isready, and PQping() called in pg_ctl -w start/restart would block 
likewise, and I'm afraid users don't anticipate it.  pg_upgrade appears to 
set synchronous_commit to local when starting the database server.


Regards
MauMau





--
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] pgaudit - an auditing extension for PostgreSQL

[MauMau]

I'm sorry to interrupt you, but I feel strong sympathy with Stephen-san.

From: Robert Haas robertmh...@gmail.com

I don't think that's a valid objection.  If we someday have auditing
in core, and if it subsumes what pgaudit does, then whatever
interfaces pgaudit implements can be replaced with wrappers around the
core functionality, just as we did for text search.


Won't it be burden and a headache to maintain pgaudit code when it becomes 
obsolete in the near future?




But personally, I think this patch deserves to be reviewed on its own
merits, and not the extent to which it satisfies your requirements, or
those of NIST 800-53.  As I said before, I think auditing is a
complicated topic and there's no guarantee that one solution will be
right for everyone.  As long as we keep those solutions out of core,
there's no reason that multiple solutions can't coexist; people can
pick the one that best meets their requirements.  As soon as we start
talking about something putting into core, the bar is a lot higher,
because we're not going to put two auditing solutions into core, so if
we do put one in, it had better be the right thing for everybody.  I
don't even think we should be considering that at this point; I think
the interesting (and under-discussed) question on this thread is
whether it even makes sense to put this into contrib.  That means we
need some review of the patch for what it is, which there hasn't been
much of, yet.


Then, what is this auditing capability for?  I don't know whether various 
regulations place so different requirements on auditing, but how about 
targeting some real requirements?  What would make many people happy?  PCI 
DSS?


I bet Japanese customers are severe from my experience, and I'm afraid they 
would be disappointed if PostgreSQL provides auditing functionality which 
does not conform to any real regulations like PCI DSS, NIST, etc, now that 
other major vendors provide auditing for years.  They wouldn't want to 
customize contrib code because DBMS development is difficult.  I wish for 
in-core serious auditing functionality.


Regards
MauMau




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

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

[MauMau]

Thanks, I marked it as ready for committer.  I hope Fujii san or another 
committer will commit this, refining English expression if necessary.


Regards
MauMau



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

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

[MauMau]

From: Pavel Stehule pavel.steh...@gmail.com

I modified description of setting system variables in dependency on O.S.


Thank you, it's almost OK.  As mentioned in my previous mail, I think 
determines should be determine to follow other messages.  I'll mark this 
patch as ready for committer when this is fixed.


+ printf(_(  COMP_KEYWORD_CASE  determines which letter case to use when 
completing an SQL key word\n));


Personally, I don't think we have to describe how to set environment 
variables, because it's preliminary knowledge and not specific to 
PostgreSQL.  However, I don't mind if you retain or remove the description.


Regards
MauMau





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

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

[MauMau]

Hello,

From: Pavel Stehule pavel.steh...@gmail.com

fixed


Thank you.  All is fine.



should be Environment variables.  And this section lacks description
for Windows, such as:

+ printf(_(  NAME=VALUE [NAME=VALUE] psql ...\n  or \\setenv NAME 
[VALUE]

in interactive mode\n\n));

+ printf(_(  PGPASSFILE password file (default ~/.pgpass)\n));



??? -


I meant that how to set environment variables on Windows command prompt is 
different from on UNIX/Linux, and the default password file path is also 
different on Windows.  Do we describe them in this help?



Lastly, to follow most of your descriptions, s at the end of the first 
verb in these messages should be removed.  For example, use set instead of 
sets.


+ printf(_(  COMP_KEYWORD_CASE  determines which letter case to use when 
completing an SQL key word\n));
+ printf(_(  columnssets the target width for the wrapped 
format\n));
+ printf(_(  linestyle  sets the border line drawing style [ascii, 
old-ascii, unicode]\n));
+ printf(_(  recordsep  specifies the record (line) separator to 
use in unaligned output format\n));
+ printf(_(  title  sets the table title for any subsequently 
printed tables\n));



This is all I noticed in the review.


Regards
MauMau



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

Re: [Fwd: Re: [HACKERS] proposal: new long psql parameter --on-error-stop]

[MauMau]

OK, let me help you, though I'm only a Japanese who is never confident in my 
English.


(1)
As Fujii-san pointed out, could you add explanation for --help-variables in 
doc/src/sgml/ref/psqlref.sgml?



(2)
+ printf(_(  --help-variables list of available configuration 
variables (options), then exit\n));


should better be:

+ printf(_(  --help-variables show A list of all specially treated 
variables, then exit\n));


This follows the psql manual page.  Similarly,

+ printf(_(List of variables (options) for use from command line.\n));

should be:

+ printf(_(List of specially treated variables.\n));


(3)
+ printf(_(  ECHO   control what input can be writtent to 
standard output [all, queries]\n));


writtent should be written.  controls should be control like other 
options.



(4)
+ printf(_(  ECHO_HIDDENdisplay internal queries (same as -E 
option)\n));


should better be:

+ printf(_(  ECHO_HIDDENdisplay internal queries executed by 
backslash commands\n));


I think (same as ...) can be omitted to keep the description short.  If 
you want to retain it, other variables should also accompany similar 
description, such as -a for ECHO.



(5)
+ printf(_(  FETCH_COUNTfetch many rows at a time (use less memory) 
(default 0 unlimited)\n));


should better be:

+ printf(_(  FETCH_COUNTthe number of result rows to fetch and 
display at a time (default: 0=unlimited)\n));



(6)
+ printf(_(  HISTCONTROLwhen set, controls history list 
[ignorespace, ignoredups, ignoreboth]\n));


should better be:

+ printf(_(  HISTCONTROLcontrol history list [ignorespace, 
ignoredups, ignoreboth]\n));



(7)
+ printf(_(  USER   the database user currently connected\n));

should add as at the end:

+ printf(_(  USER   the database user currently connected 
as\n));



(8)
Printing options section lack the following ones described in psql manual:

columns
expanded (or x)
footer
numericlocale
tableattr (or T)


(9)
+ printf(_(\nEnvironment options:\n));

should be Environment variables.  And this section lacks description for 
Windows, such as:


+ printf(_(  NAME=VALUE [NAME=VALUE] psql ...\n  or \\setenv NAME [VALUE] 
in interactive mode\n\n));

+ printf(_(  PGPASSFILE password file (default ~/.pgpass)\n));

Regards
MauMau



--
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] PostgreSQL in Windows console and Ctrl-C

[MauMau]

From: Christian Ullrich ch...@chrullrich.net
On non-Windows platforms, the --background option is not passed, and the 
option handling is unmodified except for an additional pair of braces. The 
postmaster does not pass the option to its children on any platform.
pg_ctl does not pass the option anywhere but on Windows, and postmaster.c 
does not recognize it anywhere else. If it is encountered on a platform 
where it does not make sense, it will be treated like any other (unknown) 
long option.


OK.



Restart is implemented as stop/start, so, yes.


Then, please mention restart mode as well like start and restart mode for 
clarification.


Regards
MauMau



--
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] Use a signal to trigger a memory context dump?

[MauMau]

From: Andres Freund and...@2ndquadrant.com

I wonder if it'd make sense to allow a signal to trigger a memory
context dump? I and others more than once had the need to examine memory
usage on production systems and using gdb isn't always realistic.


+1

It would be nice if there's a generic infrastructure on which the DBA can 
get information of running backends.I wish for a functionality to dump 
info of all backends with a single operation as well as one backend at a 
time, because it would be difficult to ask for users to choose a specific 
backend or operate on all backends, especially on Windows.  The candidate 
info are:


* memory context

* stack trace: I'd like to implement this.

* GUC settings: to know that backends are running with intended settings.

* prepared statements (= pg_prepared_statements): to know if applications 
are taking advantage of prepared statements for performance.


Regards
MauMau



--
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: new long psql parameter --on-error-stop

[MauMau]

From: Pavel Stehule pavel.steh...@gmail.com
pg_dumpall aligns all options left with each other, whether they are 
short

or long.

 -x, --no-privileges  do not dump privileges (grant/revoke)
 --binary-upgrade for use by upgrade utilities only
 --column-inserts dump data as INSERT commands with column
names



ok

I fixed it


Thank you.  I marked this patch as ready for committer.

Regards
MauMau




--
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] PostgreSQL in Windows console and Ctrl-C

[MauMau]

From: Christian Ullrich ch...@chrullrich.net

OK, here is the first draft against current master. It builds on Windows
with VS 2012 and on FreeBSD 10 with clang 3.3. I ran the regression
tests on Windows, they all pass.

The changed behavior is limited to Windows, where it now silently
ignores Ctrl-C and Ctrl-Break when started via pg_ctl start.

I don't think there is currently any support for switch-type long
options, so rather than invent my own, I squeezed the two lines I added
into postmaster.c where they fit best; unfortunately, the result is
quite ugly. I'll be happy to refine that if someone can give me a hint
on how to do it.


Overall, the patch seems good as it is based on the discussion.  I found a 
few problems:


(1)
The patch doesn't apply to HEAD.  Could you rebase your patch?

patching file src/bin/pg_ctl/pg_ctl.c
Hunk #1 FAILED at 453.
1 out of 1 hunk FAILED -- saving rejects to file src/bin/pg_ctl/pg_ctl.c.rej


(2)
Although I haven't tried, doesn't pg_ctl start fail on non-Windows platforms 
because of the following check?


!if (opt == '-')
! ereport(ERROR,
!   (errcode(ERRCODE_SYNTAX_ERROR),
!errmsg(--%s requires a value,
! optarg)));

And, in the postgres reference page,

http://www.postgresql.org/docs/devel/static/app-postgres.html

there's a paragraph:

The -- options will not work on FreeBSD or OpenBSD. Use -c instead. This is 
a bug in the affected operating systems; a future release of PostgreSQL will 
provide a workaround if this is not fixed.


Would --background work on FreeBSD and OpenBSD (i.e. would pg_ctl start 
succeed)?  I don't have access to those platforms.



(3)
--background will also be used by restart subcommand, won't it?

+ in a console window. It is used automatically by
+ commandpg_ctl/command when called with the
+ optionstart/option subcommand.

Regards
MauMau




--
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: new long psql parameter --on-error-stop

[MauMau]

eFrom: Pavel Stehule pavel.steh...@gmail.com

here is a prototype:


The patch applied and built with success.  There are a few minor things:


(1)
help_variables() lacks description of some variables such as SINGLELINE and 
SINGLESTEP.  I think this help should list all available variables, because 
users may want to know the existence of those missing variables.  Based on 
this, modify these lines: remove some from the first line and the entire 
second line.


+ printf(_(List of some variables (options) for use from command 
line.\n));
+ printf(_(Complete list you find in psql section in the PostgreSQL 
documentation.\n\n));



(2)
The indent is different from other lines.  Leave just two spaces at the 
beginning of the line.


+ printf(_(  --help-variables list of available configuration 
variables (options), then exit\n));



(3)
This change is unnecessary.  See src/bin/pg_dumpall.c for similar switches.

- while ((c = getopt_long(argc, argv, 
aAc:d:eEf:F:h:HlL:no:p:P:qR:sStT:U:v:VwWxXz?01,
or+ while ((c = getopt_long(argc, argv, 
aAc:d:eEf:F:h:HlL:no:p:P:qR:sStT:U:v:VwWxXz?001,


Regards
MauMau



--
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: new long psql parameter --on-error-stop

[MauMau]

From: Pavel Stehule pavel.steh...@gmail.com

I am not sure in this point. It is aligned left with all long options:

 -?, --help   show this help, then exit
 --help-variables list of available configuration variables
(options), then exit


pg_dumpall aligns all options left with each other, whether they are short 
or long.


 -x, --no-privileges  do not dump privileges (grant/revoke)
 --binary-upgrade for use by upgrade utilities only
 --column-inserts dump data as INSERT commands with column 
names


Regards
MauMau



--
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] Memory leak in dblink

[MauMau]

From: Tom Lane t...@sss.pgh.pa.us

In the first query, the MemoryContextReset is nearly free since there's
nothing to free (and we've special-cased that path in aset.c).  It's
certainly a measurement artifact that it measures out faster, which says
to me that these numbers can only be trusted within a couple percent;
but at least we're not taking much hit in cases where the patch isn't
actually conferring any benefit.  For the second query, losing 1% to avoid
memory bloat seems well worthwhile.

Barring objections I'll apply and back-patch this.


So this patch would solve memory leak issues if other modules had similar 
bugs, in addition to the original dblink problem, wouldn't this?  Definitely 
+1.  The original OP wants to use 9.2.  I'll report to him when you've 
committed this nce patch.  Thanks, Tom san.


Regards
MauMau





--
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] Removing dependency to wsock32.lib when compiling code on WIndows

[MauMau]

From: Michael Paquier michael.paqu...@gmail.com

You are right, I only though about the MS scripts when working on this
patch. Updated patch for a complete cleanup is attached (note I
updated manually ./configure for test purposes and did not re-run
autoconf).


I marked this patch as ready for committer.  I confirmed:

* The patch content is correct.

* The patch applies cleanly.  Running grep -lir wsock32 . after applying 
the patch shows that wsock32.lib is no longer used.


* The binaries can be built with MSVC 2008 Express.  I didn't try to build 
on MinGW.


* The regression tests pass (vcregress check).

However, I wonder if some DLL entries in dlls[] in 
src/interfaces/libpq/win32.c should be removed.  winsock.dll should 
definitely be removed, because it is for 16-bit applications.  I don't know 
about the rest.  Especially,  wsock32n.dll and mswsock.dll may also be 
obsolete libraries from their names.  Could you look into this and revise 
the patch if possible?  But I don't mind if you do so.


Regards
MauMau



--
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] Memory leak in dblink

[MauMau]

From: Joe Conway m...@joeconway.com

I think the context deletion was missed in the first place because
storeRow() is the wrong place to create the context. Rather than
creating the context in storeRow() and deleting it two levels up in
materializeQueryResult(), I think it should be created and deleted in
the interim layer, storeQueryResult(). Patch along those lines attached.

Any objections to me back-patching it this way?


I thought the same at first before creating the patch, but I reconsidered. 
If the query executed by dblink() doesn't return any row, the context 
creation and deletion is a waste of processing.  I think the original author 
wanted to eliminate this waste by creating the context when dblink() should 
return a row.  I'd like to respect his thought.


Regards
MauMau



--
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] Memory leak in dblink

[MauMau]

From: Joe Conway m...@joeconway.com

Fair enough -- this patch does it at that level in
materializeQueryResult()


I'm in favor of this.  TBH, I did this at first, but I was afraid this would 
be rejected due to the reason mentioned earlier.


if statement in PG_TRY block is not necessary like this, because sinfo is 
zero-cleared.



  PG_TRY();
  {
+   /* Create short-lived memory context for data conversions */
+   sinfo.tmpcontext = AllocSetContextCreate(CurrentMemoryContext,
+dblink temporary context,

Regards
MauMau



--
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] datistemplate of pg_database does not behave as per description in documentation

[MauMau]

From: Rajeev rastogi rajeev.rast...@huawei.com

Please find the attached patch with only documentation change.


I marked this as ready for committer.  The patch is good because it matches 
the description in the following page:


http://www.postgresql.org/docs/devel/static/manage-ag-templatedbs.html

Regards
MauMau



--
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] Removing dependency to wsock32.lib when compiling code on WIndows

[MauMau]

From: Michael Paquier michael.paqu...@gmail.com

When doing some work on Windows, I noticed that the mkvc specs in
src/tools/msvc use wsock32.lib, which is as far as I understand an
old, obsolete version of the Windows socket library. Wouldn't it make
sense to update the specs to build only with ws2_32.lib like in the
patch attached?


Yes, that makes sense, because wsock32.dll is an obsolete WinSock 1.1 
library, while ws2_32.dll is a successor WinSock 2.0 library which is fully 
compatible with the old one.


Doing grep -lir wsock32 . shows the following files.  Could you modify and 
test these files as well for code cleanup?


./configure
./configure.in
./contrib/pgcrypto/Makefile
./src/interfaces/libpq/Makefile
./src/interfaces/libpq/win32.c
./src/interfaces/libpq/win32.mak
./src/test/thread/README
./src/tools/msvc/Mkvcbuild.pm


Regards
MauMau



--
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] datistemplate of pg_database does not behave as per description in documentation

[MauMau]

From: Fujii Masao masao.fu...@gmail.com

On Wed, Jun 18, 2014 at 7:47 PM, MauMau maumau...@gmail.com wrote:
I marked this as ready for committer.  The patch is good because it 
matches

the description in the following page:

http://www.postgresql.org/docs/devel/static/manage-ag-templatedbs.html


ISTM that this patch has already been committed by Bruce. Please see
the commit 72590b3a69baaf24d1090a2c2ceb9181be34043e


Oh, the devel doc certainly reflects the change:

http://www.postgresql.org/docs/devel/static/catalog-pg-database.html

I marked this as committed.

I hope Magnus-san's enhancements to the CommitFest app will enable the 
CommitFest entry to be automatically updated at git commit.


Regards
MauMau




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

[HACKERS] [patch] pg_copy - a command for reliable WAL archiving

[MauMau]

Hello,

As I proposed before in the thread below, I've implemented a simple command 
for reliable WAL archiving.  I would appreciate it if you could review and 
test the patch.


http://www.postgresql.org/message-id/9C1EB95CA1F34DAB93DF549A51E3E874@maumau

Regards
MauMau


pg_copy.patch
Description: Binary data

-- 
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] pg_copy - a command for reliable WAL archiving

[MauMau]

From: Joe Conway m...@joeconway.com

That first hunk refers to dblink -- I'm guessing it does not belong with
this patch.


Ouch, what a careless mistake.  The attached one is clean.  I'll update the 
CommitFest entry when I'm back home from work.


Regards
MauMau



pg_copy_v2.patch
Description: Binary data

-- 
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] Memory leak in dblink

[MauMau]

From: Amit Kapila amit.kapil...@gmail.com

Is there a need to free memory context in PG_CATCH()?
In error path the memory due to this temporary context will get
freed as it will delete the transaction context and this
temporary context will definitely be in the hierarchy of it, so
it should also get deleted.  I think such handling can be
useful incase we use PG_CATCH() to suppress the error.


I thought the same, but I also felt that I should make an effort to release 
resources as soon as possible, considering the memory context auto deletion 
as a last resort.  However, looking at other places where PG_CATCH() is 
used, memory context is not deleted.  So, I removed the modification from 
PG_CATCH() block.  Thanks.


Regards
MauMau



dblink_memleak_v2.patch
Description: Binary data

-- 
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] Memory leak in dblink

[MauMau]

Hello,

I've fixed and tested a memory leak bug in dblink.  Could you review and 
commit this?  I'll add this to the CommitFest shortly.



[Problem]
A user reported a problem in pgsql-jp ML that he encountered a out of 
memory error when he ran the ran the following function on 32-bit 
PostgreSQL 9.3:


CREATE OR REPLACE FUNCTION aaa(
character varying)
  RETURNS character varying AS
$BODY$
DECLARE
...
BEGIN
 PERFORM (SELECT DBLINK_CONNECT('conn','dbname=DB-B user=postgres'));
   DELETE FROM tbl0010 dba
 WHERE EXISTS
  (
  SELECT tbl0010_cd FROM tbl0010
  INNER JOIN (
   SELECT * FROM DBLINK
   ('conn','
 SELECT tbl0411_cd FROM tbl0411
INNER JOIN(
...

The above query calls dblink() hundreds of thousands of times.  You should 
reproduce the problem with a simpler query like this:


CREATE TABLE mytable (col int);
INSERT INTO mytable ...;  /* insert many rows */
SELECT *
FROM mytable
WHERE EXISTS
 (SELECT *
  FROM dblink(
'con',
'SELECT * FROM mytable WHERE col = ' || col)
t(col));


[Cause]
Hundreds of thousands of the following same line were output in the server 
log:


dblink temporary context: 8192 total in 1 blocks; 8176 free (0 chunks); 16 
used


Each dblink function call creates an instance of this memory context, but it 
fails to delete it.  This bug seems to have been introduced in 9.2.0 by this 
performance improvement:


Improve efficiency of dblink by using libpq's new single-row 
processingmode(Kyotaro Horiguchi, Marko Kreen)



Regards
MauMau


dblink_memleak.patch
Description: Binary data

-- 
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] Memory leak in dblink

[MauMau]

From: Fabrízio de Royes Mello fabriziome...@gmail.com
I think there no need to add it to the commitfest, because it's a bugfix
and not a new feature. Or am I missing something?


The CommitFest app has an option bug fix in the list of topic choices.
I suppose the reason is that if the bug fix is only posted to pgsql-hackers 
and/or pgsql-bugs, it might be forgotten.


Regards
MauMau




--
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 Windows SChannel as OpenSSL replacement

[MauMau]

From: Heikki Linnakangas hlinnakan...@vmware.com
Thoughts? While we're at it, we'll probably want to refactor things so 
that it's easy to support other SSL implementations too, like gnutls.


That may be good because it provides users with choices.  But I wonder if it 
is worth the complexity and maintainability of PostgreSQL code.


* Are SChannel and other libraries more secure than OpenSSL?  IIRC, recently 
I read in the news that GnuTLS had a vulnerability.  OpenSSL is probably the 
most widely used library, and many people are getting more interested in its 
quality.  I expect the quality will improve thanks to the help from The 
Linux foundation and other organizations/researchers.


* Do other libraries get support from commercial vendor product support? 
For example, Safenet Inc., the famous HSM (hardware security module) vendor, 
supports OpenSSL to access the private key stored in its HSM product.  Intel 
offered AES-NI implementation code to OpenSSL community.  I guess OpenSSL 
will continue to be the most functional and obtain the widest adoption and 
support.


Regards
MauMau



--
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] pg_ctl always uses the same event source

[MauMau]

From: Amit Kapila amit.kapil...@gmail.com

I think it's bit late for this patch for 9.4, you might want to move it to
next CF.


Thanks, I've moved it.  It's a regret that this very small patch wasn't put 
in 9.4.


Regards
MauMau



--
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] pg_ctl always uses the same event source

[MauMau]

From: Amit Kapila amit.kapil...@gmail.com
Only one minor suggestion:
+Name of the event source for pg_ctl to use for event log.  The
+default is PostgreSQL.


From this description, it is not clear when the event log will be used

in pg_ctl.  For example, if user uses -e option with register, then he
might expect any failure during register command will be logged into
eventlog, but that is not true.  So I think we should improve the docs
to reflect the usage of -e.

On Linux, I am getting below build failure for pg_ctl

pg_ctl.c: In function ‘main’:
pg_ctl.c:2173: error: ‘event_source’ undeclared (first use in this 
function)

pg_ctl.c:2173: error: (Each undeclared identifier is reported only once
pg_ctl.c:2173: error: for each function it appears in.)
make[3]: *** [pg_ctl.o] Error 1
make[2]: *** [all-pg_ctl-recurse] Error 2
make[1]: *** [all-bin-recurse] Error 2
make: *** [all-src-recurse] Error 2


Thank you for reviewing and testing.  I changed the doc, but I'd appreciate 
it if you could improve my poor English and update the CommitFest if it can 
be better.


I rebased the patch to HEAD and removed the compilation error on Linux.  I 
made event_source variable on all platforms like register_servicename, 
although they are not necessary on non-Windows platforms.


Regards
MauMau


pg_ctl_eventsrc_v9.patch
Description: Binary data

-- 
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] 9.4 release notes

[MauMau]

From: Bruce Momjian br...@momjian.us

I have completed the initial version of the 9.4 release notes.  You can
view them here:

http://www.postgresql.org/docs/devel/static/release-9-4.html

Feedback expected and welcomed.  I expect to be modifying this until we
release 9.4 final.  I have marked items where I need help with question
marks.


Could you add the following item, client-only installation on Windows, if 
it's appropriate for release note?  It will be useful for those like 
EnterpriseDB who develop products derived from PostgreSQL.


https://commitfest.postgresql.org/action/patch_view?id=1326

Regards
MauMau



--
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] elog a stack trace

[MauMau]

From: Jeff Janes jeff.ja...@gmail.com

Does anyone have any hints on how to get a stack trace programmatically,
rather than trying to run ps to get the pid and then attach gdb to a
ephemeral process and hoping the situation has not been changed while you
are doing that?  I'd like to set

log_error_verbosity = stack

or

elog_stack(,...)

But those don't exist.


On Windows, you can use Stackwalk() or Stackwalk64() Win32 API.  Several 
years ago, for some software, I implemented a feature that outputs the stack 
trace of a crashing process to its server log file.


It would be very nice if PostgreSQL outputs the stack trace of a crashing 
postgres process in the server log.  That eliminates the need for users in 
many cases to send the huge core files to the support staff or to use the 
debugger to get the stack trace.  I've recently heard that MySQL has this 
feature.


Regards
MauMau



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