Re: [HACKERS] proposal: get oldest LSN - function

[Kartyshov Ivan]

On 27.02.2016 16:09, Michael Paquier wrote:

On Sat, Feb 27, 2016 at 3:52 PM, Kartyshov Ivan
 wrote:

Maybe i wasn't too accurate in terms, because I newbie, but:
We can get information about xlog, using big amout of support function
(pg_current_xlog_location(), pg_current_xlog_insert_location(),
pg_xlogfile_name_offset(), pg_xlogfile_name(),
pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), ... etc)
they helps to get get useful information about xlog files and its content.
So, this patch extends this amount of functions.
It is useful additional tool for DBA (we can get replicationSlotMinLSN, so
why not in master), it can show us, if xlog replication or wal-sender is
working properly or indicate if replication on startup can get up to date
with master, or after long turnoff must be recovered from archive.

What pg_ls_dir('pg_xlog') couldn't do here if you need to know the
last WAL segment present on master?


Hi Michael,

It will not satisfy our purposes and our administrators for three reasons.
1) DBA set me task to get the oldest number that present in WAL, not last
2) Surely we can choose the oldest segment from list 
"pg_ls_dir('pg_xlog')" of segments and calculate the first LSN by hand, 
but it is not what we want to do manually.
3) Soon will be commitfest and our administrators wants to enhance their 
tool case for debug with usable features.


Thank you for comment.

 


--
Ivan Kartyshov
Postgres Professional: www.postgrespro.com
Russian Postgres Company



--
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: get oldest LSN - function

[Kartyshov Ivan]

On 27.02.2016 22:36, Yury Zhuravlev wrote:

Hello.
You forgot to declare a GetXLogOldestLSNPtr function in a header file 
(in xlog.h I think).
Some compilers refuse to compile this code (MSVC for example). Now I 
see warning from GCC:
xlog.c:10627:1: warning: no previous prototype for 
«GetXLogOldestLSNPtr» [-Wmissing-prototypes]

GetXLogOldestLSNPtr(void)


Thanks.

Hi Yury,
thank for valuable remark.
Changes applied

 


--
Ivan Kartyshov
Postgres Professional: www.postgrespro.com
Russian Postgres Company

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f9eea76..f774233 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -16908,6 +16908,8 @@ SELECT set_config('log_statement_stats', 'off', false);


 pg_current_xlog_location
+   
+pg_oldest_xlog_location


 pg_start_backup
@@ -16981,6 +16983,13 @@ SELECT set_config('log_statement_stats', 'off', false);
   
   

+pg_oldest_xlog_location()
+
+   pg_lsn
+   Get the oldest WAL LSN (log sequence number)
+  
+  
+   
 pg_start_backup(label text , fast boolean )
 
pg_lsn
@@ -17096,6 +17105,7 @@ postgres=# select pg_start_backup('label_goes_here');

 

+pg_oldest_xlog_location displays the oldest WAL LSN.
 pg_current_xlog_location displays the current transaction log write
 location in the same format used by the above functions.  Similarly,
 pg_current_xlog_insert_location displays the current transaction log
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index 6cb690c..5a0e887 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -860,6 +860,8 @@ primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
  The last WAL receive location in the standby is also displayed in the
  process status of the WAL receiver process, displayed using the
  ps command (see  for details).
+ Also we can get the oldest WAL LSN (Log Sequence Number) 
+ pg_oldest_xlog_location, it can give us a useful tool for DBA, additionally it can show us, if xlog replication or wal-sender is working properly or indicate if replication on startup can can get up to date with master, or after long turnoff must be recovered from archive. 
 
 
  You can retrieve a list of WAL sender processes via the
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 94b79ac..067d51c 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -10669,6 +10669,18 @@ GetXLogWriteRecPtr(void)
 }
 
 /*
+ * Get oldest WAL write pointer
+ */
+XLogRecPtr
+GetXLogOldestLSNPtr(void)
+{
+	XLogRecPtr	result;
+
+	XLogSegNoOffsetToRecPtr(XLogGetLastRemovedSegno()+1, 1, result);
+	return result;
+}
+
+/*
  * Returns the redo pointer of the last checkpoint or restartpoint. This is
  * the oldest point in WAL that we still need, if we have to restart recovery.
  */
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 31cbb01..44e01e1 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -195,6 +195,27 @@ pg_current_xlog_location(PG_FUNCTION_ARGS)
 }
 
 /*
+ * Report the oldest WAL write location (same format as pg_start_backup etc)
+ *
+ * This is useful for determining the first LSN in existing sequences
+ */
+Datum
+pg_oldest_xlog_location(PG_FUNCTION_ARGS)
+{
+	XLogRecPtr	oldest_recptr;
+
+	if (RecoveryInProgress())
+		ereport(ERROR,
+(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("recovery is in progress"),
+ errhint("WAL control functions cannot be executed during recovery.")));
+
+	oldest_recptr = GetXLogOldestLSNPtr();
+
+	PG_RETURN_LSN(oldest_recptr);
+}
+
+/*
  * Report the current WAL insert location (same format as pg_start_backup etc)
  *
  * This function is mostly for debugging purposes.
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index ecd30ce..bc7ce6c 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -236,6 +236,7 @@ extern void GetXLogReceiptTime(TimestampTz *rtime, bool *fromStream);
 extern XLogRecPtr GetXLogReplayRecPtr(TimeLineID *replayTLI);
 extern XLogRecPtr GetXLogInsertRecPtr(void);
 extern XLogRecPtr GetXLogWriteRecPtr(void);
+extern XLogRecPtr GetXLogOldestLSNPtr(void);
 extern bool RecoveryIsPaused(void);
 extern void SetRecoveryPause(bool recoveryPause);
 extern TimestampTz GetLatestXTime(void);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index b24e434..3c2cefb 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5202,6 +5202,10 @@ DESCR("get an individual replication origin's replication progress");
 DATA(insert OID = 6014 ( pg_show_replication_origin_status PGNSP PGUID 12 1 100 0 0 f f f f f t v r 0 0 2249 "" 

Re: [HACKERS] exposing pg_controldata and pg_config as functions

[Joe Conway]

On 02/21/2016 05:30 AM, Michael Paquier wrote:
> Looking again at this thread I guess that this is consensus, based on
> the proposal from Josh and seeing no other ideas around. Another idea
> would be to group all the fields that into a single function
> pg_control_data().

I think a single function would be ridiculously wide. I like the four
separate functions better if we're going to do it this way at all.

> +   
> +pg_checkpoint_state
> +   
> +   
> +pg_checkpoint_state returns a record containing
> +checkpoint_location, prior_location, redo_location, redo_wal_file,
> +timeline_id, prev_timeline_id, full_page_writes, next_xid, next_oid,
> +next_multixact_id, next_multi_offset, oldest_xid, oldest_xid_dbid,
> +oldest_active_xid, oldest_multi_xid, oldest_multi_dbid,
> +oldest_commit_ts_xid, newest_commit_ts_xid, and checkpoint_time.
> +   
> This is bit unreadable. The only entry in the documentation that
> adopts a similar style is pg_stat_file, and with six fields that feels
> as being enough. I would suggest using a table instead with the type
> of the field and its name.

Ok, changed to your suggestion.


> Regarding the naming of the functions, I think that it would be good
> to get something consistent with the concept of those being "Control
> Data functions" by having them share the same prefix, say pg_control_
> - pg_control_checkpoint
> - pg_control_init
> - pg_control_system
> - pg_control_recovery

No issues -- changed.

> +   snprintf (controldata_name, CONTROLDATANAME_LEN,
> + "%s:", controldata[i].name);
> Nitpick: extra space.

I didn't understand this comment but it is moot now anyway...

> +static const char *const controldata_names[] =
> +{
> +   gettext_noop("pg_control version number"),
> +   gettext_noop("Catalog version number"),
> +   gettext_noop("Database system identifier"),
> Is this complication really necessary? Those identifiers are used only
> in the frontend and the footprint of this patch on pg_controldata is
> really large. What I think we should do is have in src/common the
> following set of routines that work directly on ControlFileData:
> - checkControlFile, to perform basic sanity checks on the control file
> (CRC, see for example pg_rewind.c)
> - getControlFile(dataDir), that simply returns a palloc'd
> ControlFileData to the caller after looking at global/pg_control.
> pg_rewind could perhaps make use of the one to check the control file
> CRC, to fetch ControlFileData there is some parallel logic for the
> source server if it is either remote or local so it would be better to
> not use getControlFile in this case.

I agree with the assessment that much of what had been moved based on
the original pg_controladata() SRF no longer needs to move. This version
only puts get_controlfile() into src/common, since that is the bit that
is still shared. If checkControlFile() or something similar is useful
for pg_rewind or some other extension, I'd say that should be a separate
patch.

Oh, and the entire thing is now rebased against a git pull from a few
hours ago. I moved this to the upcoming commitfest too, although I think
it is pretty well ready to go.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c0b94bc..4b5ee81 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*** SELECT collation for ('foo' COLLATE "de_
*** 16703,16708 
--- 16703,17064 
  
 
  
+
+ The functions shown in 
+ print information initialized during initdb, such
+ as the catalog version. They also show information about write-ahead
+ logging and checkpoint processing. This information is cluster-wide,
+ and not specific to any one database. They provide most of the same
+ information, from the same source, as
+ , although in a form better suited
+ to SQL functions.
+
+ 
+
+ Control Data Functions
+ 
+  
+   Name Return Type Description
+  
+ 
+  
+   
+
+ pg_control_checkpoint
+ pg_control_checkpoint()
+
+record
+
+ Returns information about current checkpoint state.
+
+   
+ 
+   
+
+ pg_control_system
+ pg_control_system()
+
+record
+
+ Returns information about current controldata file state.
+
+   
+ 
+   
+
+ pg_control_init
+ pg_control_init()
+
+record
+
+ Returns information about cluster initialization state.
+
+   
+ 
+   
+
+ pg_control_recovery
+ pg_control_recovery()
+
+record
+
+ Returns information about recovery state.
+
+   
+ 
+  
+ 
+
+ 
+
+ pg_control_checkpoint returns 

Re: [HACKERS] postgres_fdw vs. force_parallel_mode on ppc

[Noah Misch]

On Fri, Feb 26, 2016 at 04:16:58PM +0530, Robert Haas wrote:
> Committed these patches after revising the comment you wrote and
> adding documentation.

I've modified buildfarm member mandrill to use force_parallel_mode=regress and
max_parallel_degree=5; a full run passes.  We'll now see if it intermittently
fails the stats test, like Tom witnessed:
http://www.postgresql.org/message-id/30385.1456077...@sss.pgh.pa.us


-- 
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: "Causal reads" mode for load balancing reads without stale data

[Thom Brown]

On 27 February 2016 at 13:20, Michael Paquier  wrote:
> On Mon, Feb 22, 2016 at 9:39 AM, Thom Brown  wrote:
>> On 21 February 2016 at 23:18, Thomas Munro
>>  wrote:
>> The replay_lag is particularly cool.  Didn't think it was possible to
>> glean this information on the primary, but the timings are correct in
>> my tests.
>>
>> +1 for this patch.  Looks like this solves the problem that
>> semi-synchronous replication tries to solve, although arguably in a
>> more sensible way.
>
> Yeah, having extra logic at application layer to check if a certain
> LSN position has been applied or not is doable, but if we can avoid it
> that's a clear plus.
>
> This patch has no documentation. I will try to figure out by myself
> how the new parameters interact with the rest of the syncrep code
> while looking at it but if we want to move on to get something
> committable for 9.6 it would be good to get some documentation soon.

Could we rename "apply" to "remote_apply"?  It seems more consistent
with "remote_write", and matches its own enum entry too.

Thom


-- 
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] The plan for FDW-based sharding

[Kevin Grittner]

On Sat, Feb 27, 2016 at 3:57 PM, Simon Riggs  wrote:
> On 27 February 2016 at 17:54, Kevin Grittner  wrote:
>>
>> On a single database SSI can see whether a read has
>> caused such a problem.  If you replicate the transactions to
>> somewhere else and read them SSI cannot tell whether there is an
>> anomaly
>
> OK, I thought you were saying something else. What you're saying is that SSI
> doesn't work on replicas, yet, whether that is physical or logical.

Right.

> Row level locking (S2PL) can be used on logical standbys, so its actually a
> better situation.

Except that S2PL has the concurrency and performance problems that
caused us to rip out a working S2PL implementation in PostgreSQL
core.  Layering it on outside of that isn't going to offer better
concurrency or perform better than what we ripped out; but it does
work.

>> One possibility is to pass along information
>> about when things are in a state on the source that is known to be
>> free of anomalies if read; another would be to reorder the
>> application of transactions to match the apparent order of
>> execution.  The latter would not work for "physical" replication,
>> but should be fine for logical replication.  An implementation
>> might create a list in commit order, but not release the front of
>> the list for processing if it is a SERIALIZABLE transaction which
>> has written data until all overlapping SERIALIZABLE transactions
>> complete, so it can move any subsequently-committed SERIALIZABLE
>> transaction which read the "old" version of the data ahead of it.
>
> The best way would be to pass across "anomaly barriers", since they can
> easily be inserted into the WAL stream. The main issue seems to be how and
> when to detect them.

That, and how to choose whether to run right away with the last
known consistent snapshot, or wait for the next one.  There seem to
be use cases for both.  None of it seems extraordinarily hard; it's
just never been anyone's top priority.  :-/

> For logical replay, applying in batches is actually a good thing since it
> allows parallelism. We can remove them all from the target's procarray all
> at once to avoid intermediate states becoming visible. So that would be the
> preferred mechanism.

That could be part of a solution.  What I sketched out with the
"apparent order of execution" ordering of the transactions
(basically, commit order except when one SERIALIZABLE transaction
needs to be dragged in front of another due to a read-write
dependency) is possibly the simplest approach, but batching may
well give better performance.

> Collecting a list of transactions that must be applied before the current
> one could be accumulated during SSI processing and added to the commit
> record. But reordering the transaction apply is something we'd need to get
> some real clear theory on before we considered it.

Oh, there is a lot of very clear theory on it.  I even considered
whether it might work at the physical level, but that seems fraught
with potential land-mines due to the subtle ways in which we manage
race conditions at the detail level.  It's one of those things that
seems theoretically possible, but probably a really bad idea in
practice.  For logical replication, though, there is a clear way to
determine a reasonable order of applying changes that will never
yield a serialization anomaly -- if we do that, we dodge the choice
between using a "stale" safe snapshot or waiting an indeterminate
length of time for a "fresh" safe snapshot -- at the cost of
delaying logical replication itself at various points.

Anyway, we seem to be on the same page; just some minor
miscommunication at some point.  I apologize if I was unclear.

Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] The plan for FDW-based sharding

[Simon Riggs]

On 27 February 2016 at 17:54, Kevin Grittner  wrote:

> On a single database SSI can see whether a read has
> caused such a problem.  If you replicate the transactions to
> somewhere else and read them SSI cannot tell whether there is an
> anomaly


OK, I thought you were saying something else. What you're saying is that
SSI doesn't work on replicas, yet, whether that is physical or logical.

Row level locking (S2PL) can be used on logical standbys, so its actually a
better situation.

(at least, not without exchanging a lot of information that
> isn't currently happening), so some other mechanism would probably
> need to be used.  One possibility is to pass along information
> about when things are in a state on the source that is known to be
> free of anomalies if read; another would be to reorder the
> application of transactions to match the apparent order of
> execution.  The latter would not work for "physical" replication,
> but should be fine for logical replication.  An implementation
> might create a list in commit order, but not release the front of
> the list for processing if it is a SERIALIZABLE transaction which
> has written data until all overlapping SERIALIZABLE transactions
> complete, so it can move any subsequently-committed SERIALIZABLE
> transaction which read the "old" version of the data ahead of it.
>

The best way would be to pass across "anomaly barriers", since they can
easily be inserted into the WAL stream. The main issue seems to be how and
when to detect them.

For logical replay, applying in batches is actually a good thing since it
allows parallelism. We can remove them all from the target's procarray all
at once to avoid intermediate states becoming visible. So that would be the
preferred mechanism.

Collecting a list of transactions that must be applied before the current
one could be accumulated during SSI processing and added to the commit
record. But reordering the transaction apply is something we'd need to get
some real clear theory on before we considered it.

Anyway, next release.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] The plan for FDW-based sharding

[Kevin Grittner]

On Sat, Feb 27, 2016 at 1:14 PM, Konstantin Knizhnik
 wrote:

> We do not try to preserve transaction commit order at all nodes.
> But in principle it can be implemented using XTM API: it allows to redefine
> function which actually sets transaction status.  pg_dtm performs 2PC here.
> And in principle it is possible to enforce commits in any particular order.

That's encouraging.

> Concerning CSNs, may be you are right and it is not correct to use this
> notion in this case. Actually there are many "CSNs" involved in transaction
> commit.

Perhaps we should distinguish "commit sequence number" from "apply
sequence number"?  I really think we need to differentiate the
order to be applied from the order previously committed in order to
avoid long-term confusion.  Calling both "CSN" is going to cause
not only miscommunication but muddled thinking, IMO.

> First of all each transaction is assigned local CSN (timestamp) when it is
> ready to commit. Then CSNs of all nodes are exchanged and maximal CSN is
> chosen.
> This maximum is writen as final transaction CSN and is used in visibility
> check.

Is this an implementation of some particular formal technique?  If
so, do you have a reference to a paper on it?  I get the sense that
there has been a lot written about distributed transactions, and
that it would be a mistake to ignore it, but I have not (yet)
reviewed the literature for it.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Sanity checking for ./configure options?

[Andres Freund]

On 2016-02-27 14:15:45 -0600, Jim Nasby wrote:
> Yeah, and I don't see any reasonable way to do that... we don't require sed
> or the like, do we?

We actually do. Check the bottom of configure.in.


-- 
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] Sanity checking for ./configure options?

[Jim Nasby]

On 2/26/16 9:29 PM, Peter Eisentraut wrote:

To make this really robust, you might need to do pattern matching on the
value.


Yeah, and I don't see any reasonable way to do that... we don't require 
sed or the like, do we?


I'll look at the other things you mentioned.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
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] Sanity checking for ./configure options?

[Jim Nasby]

On 2/26/16 9:34 AM, Ivan Kartyshov wrote:

The following review has been posted through the commitfest application:
make installcheck-world:  tested, failed
Implements feature:   tested, failed
Spec compliant:   tested, failed
Documentation:tested, failed

Tested, I think it`s rather important to make cleanup work on that project.


Did you mean to mark all those items as tested, failed?

On another note, the other use case for allowing 1-1024 is if you run 
with listen_address=''.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
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] Convert pltcl from strings to objects

[Jim Nasby]

On 2/25/16 9:30 AM, Alvaro Herrera wrote:

Jim Nasby wrote:


Here we have another case. prodesc is a global thing. And it is shared
between different operations. Problem was that there is no partcular
owner, and we have to wait when last operation which deals with it
would finish. It looks like perfect job for reference counting.


I've just tried to wrap my head around what's going on with prodesc and
failed... specifically, I don't understand this claim in the comment:

* Add the proc description block to the hashtable.  Note we do not
* attempt to free any previously existing prodesc block.  !!This is
* annoying, but necessary since there could be active calls using
* the old prodesc.!!

What else could be referencing it? I realize it's stored in pltcl_proc_htab,
but AFAICT that's backend-local. So I don't understand what else could be
referencing it.


Try to open a cursor that uses the function, fetch a few tuples from it;
then change the function and fetch more rows from the cursor.  I suppose
the open cursor could contain a reference to the function's prodesc.

Refcounting the prodesc would let it live until the cursor's closed,
then free it.


Hadn't thought about cursors; I suspect you're right about that. I 
wounder if other PLs would handle that correctly.


I'm also not sure how the reference would get decremented... via 
ResourceOwner somehow?

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
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 extension API? Documentation?

[Chapman Flack]

On 02/27/16 14:11, Álvaro Hernández Tortosa wrote:

> Why would it be so much work? Creating a function list, and maybe
> documenting those, doesn't sound like a daunting task.
> 
> I wouldn't mind volunteering for this work, but I guess I would need
> some help to understand and identify the candidate parts of the API.

I guess one daunting part is that the first approximation to "candidate
parts of the API" is something like "that which is useful to extensions"
and there are a lot of those, adding a really wide variety of capabilities,
and not all of their maintainers may be close followers of -hackers or
in a position to promptly answer if you asked "what are all the PostgreSQL
interfaces your extension relies on and why?".

My experience in working on PL/Java has been, sort of recurringly, that
I may appear on -hackers needing to advocate that PGDLLIMPORT be put on
some recently-added variable, or that there be some way to hook into
the extension dependency mechanism (to cite a couple recent examples)
and face initial questions on why such a need crops up in an extension.
So it takes some more explaining, and I don't think that reflects in
any way on the perspicacity of the -hackers readership; it's just that
any piece you're not personally immersed in is likely to have details
that won't have jumped out at you.

Such things probably lurk in the corners of most existing extensions,
of which there are a lot.

-Chap


-- 
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: get oldest LSN - function

[Yury Zhuravlev]

Hello.
You forgot to declare a GetXLogOldestLSNPtr function in a header file (in 
xlog.h I think).
Some compilers refuse to compile this code (MSVC for example). 
Now I see warning from GCC:

xlog.c:10627:1: warning: no previous prototype for «GetXLogOldestLSNPtr» 
[-Wmissing-prototypes]
GetXLogOldestLSNPtr(void)


Thanks.
--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--
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 extension API? Documentation?

[Chapman Flack]

On 02/27/16 13:51, Álvaro Hernández Tortosa wrote:

> ... I still lack a list of functions that might be callable (I
> understand not even those labeled with PGDLLIMPORT are all good candidates
> and some good candidates might not be labeled as such) from an extension
> point of view. Have you come across such a list over any of these threads?

On my best understanding, there isn't really such a thing exactly.
If the formulation by Andres is persuasive ("We have a (mostly) proper API.
Just not an internal/external API split"), then the good references for
hacking an extension will be essentially the same as the good references
for hacking PostgreSQL, such as the "Hacking PostgreSQL Resources" found
on the "So, you want to be a developer?" wiki page:

https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F

Also, the PostgreSQL code repository has a lot of README files in
subdirectories where important pieces of the architecture happen,
and they are very informative and worth reading, and also the comments
are often quite comprehensive in the .h or .c files pertaining to the
parts of the system you need to interact with.

The extra ingredients for being an *extension* author, in the absence
of any formalized "this is the extension API" documentation, seem to be
those unformalized qualities like taste or restraint, in looking over
the available interfaces and judging which ones seem to be fundamental,
useful, stable, less likely to be whacked around later, etc. Those
qualities also can be called "enlightened self-interest" because you
are not looking forward to fixing your busted extension when something
you have relied on changes.

Another piece of the puzzle seems to be participating on -hackers
so that you may see what changes are coming, or possibly advocate
for why a particular interface really is useful to your extension
and is worth committing to.

If there is some subspace of possible extensions where you are
interested in working, taking on some maintenance of an existing
extension in that space, thereby getting familiar with what interfaces
it relies on and why, seems to be an effective baptism-by-fire. :)
The danger to avoid would be then drawing overbroad conclusions about
what should or shouldn't be extension API, based on what is useful
for the subspace of imaginable extensions in which you are working.

-Chap


-- 
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 extension API? Documentation?

[Yury Zhuravlev]

Álvaro Hernández Tortosa wrote:
I wouldn't mind volunteering for this work, but I guess I would 
need some help to understand and identify the candidate parts of 
the API. If anyone could help me here, please let me know.


When you write an extension often regret that someone specified this or 
that function as static. I am not sure that such a list ever be complete. 
In Postgres no clear boundaries between the subsystems. 
--

Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--
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] The plan for FDW-based sharding

[Konstantin Knizhnik]

Neither pg_dtm, neither pg_tsdtm supports serializable isolation level.
We implemented distributed snapshot isolation - repeatable-read isolation level.
We also do not support read-committed isolation level now.

We do not try to preserve transaction commit order at all nodes.
But in principle it can be implemented using XTM API: it allows to redefine 
function which actually sets transaction status.  pg_dtm performs 2PC here.
And in principle it is possible to enforce commits in any particular order.

Concerning CSNs, may be you are right and it is not correct to use this notion in this 
case. Actually there are many "CSNs" involved in transaction commit.
First of all each transaction is assigned local CSN (timestamp) when it is 
ready to commit. Then CSNs of all nodes are exchanged and maximal CSN is chosen.
This maximum is writen as final transaction CSN and is used in visibility check.


On 02/27/2016 01:48 AM, Kevin Grittner wrote:

On Fri, Feb 26, 2016 at 2:19 PM, Konstantin Knizhnik
 wrote:


pg_tsdtm  is based on another approach: it is using system time
as CSN

Which brings up an interesting point, if we want logical
replication to be free of serialization anomalies for those using
serializable transactions, we need to support applying transactions
in an order which may not be the same as commit order -- CSN (as
such) would be the wrong thing.  If serializable transaction 1 (T1)
modifies a row and concurrent serializable transaction 2 (T2) reads
the old version of the row, and modifies something based on that,
T2 must be applied to a logical replica first even if T1 commits
before it; otherwise the logical replica could see a state not
consistent with business rules and which could not have been seen
(due to SSI) on the source database.  Any DTM API which does not
support some mechanism to rearrange the order of transactions from
commit order to some other order (based on, for example, read-write
dependencies) is not complete.  If it does support that, it gives
us a way forward for presenting consistent data on logical
replicas.

To avoid confusion, it might be best to reserve CSN for actual
commit sequence numbers, or at least values which increase
monotonically with each commit.  The term of art for what I
described above is "apparent order of execution", so maybe we want
to use AOE or AOoE for the order we choose to use in a particular
implementation.  It doesn't seem to me to be outright inaccurate
for cases where the system time on the various systems is used.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



--
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 extension API? Documentation?

[Álvaro Hernández Tortosa]

On 27/02/16 15:43, Tom Lane wrote:

Chapman Flack  writes:

On 02/27/16 08:37, �lvaro Hernández Tortosa wrote:

In other words: what is the API surface exposed by PostgreSQL to
extension developers? The assumption is that no PostgreSQL code should be
modified, just adding your own and calling existing funcitons.

That's an excellent question that repeatedly comes up, in particular
because of the difference between the way the MSVC linker works on Windows,
and the way most other linkers work on other platforms.

Yeah.  It would be a fine thing to have a document defining what we
consider to be the exposed API for extensions.  In most cases we could
not actually stop extension developers from relying on stuff outside the
defined API, and I don't particularly feel a need to try.  But it would be
clear to all concerned that if you rely on something not in the API, it's
your problem if we remove it or whack it around in some future release.
On the other side, it would be clearer to core-code developers which
changes should be avoided because they would cause pain to extension
authors.

Unfortunately, it would be a lot of work to develop such a thing, and no
one has wanted to take it on.


Why would it be so much work? Creating a function list, and maybe 
documenting those, doesn't sound like a daunting task.


I wouldn't mind volunteering for this work, but I guess I would 
need some help to understand and identify the candidate parts of the 
API. If anyone could help me here, please let me know.


Álvaro


--
Álvaro Hernández Tortosa


---
8Kdata



--
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] Support for N synchronous standby servers - take 2

[Masahiko Sawada]

On Fri, Feb 26, 2016 at 10:53 AM, Kyotaro HORIGUCHI
 wrote:
> At Fri, 26 Feb 2016 10:38:22 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI 
>  wrote in 
> <20160226.103822.12680005.horiguchi.kyot...@lab.ntt.co.jp>
>> Hello, Thanks for the new patch.
>>
>>
>> At Fri, 26 Feb 2016 08:52:54 +0900, Masahiko Sawada  
>> wrote in 

Re: [HACKERS] [PATCH] fix DROP OPERATOR to reset links to itself on commutator and negator

[Yury Zhuravlev]

Roma Sokolov wrote:

See v2 of the patch attached.


Hello.
I have a stylistic comments. Sometimes you forget a space:
+  replaces[Anum_pg_operator_oprcom - 1] =true;

or use tab insted space:
+   if (OidIsValid(op->oprnegate) ||
+   (OidIsValid(op->oprcom)  && operOid != op->oprcom))
+   OperatorUpd(operOid,
+   operOid == op->oprcom ?  InvalidOid : 
op->oprcom,
+   op->oprnegate,
+   true);

And I think if you make this logic into a separate function,
it is possible to simplify the code. OperatorUpd function is too complex.

Also better to add comments to the tests.
The rest seems good.

PS I here thought it would be possible to print operators that have been 
changed?


--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--
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 extension API? Documentation?

[Álvaro Hernández Tortosa]

On 27/02/16 15:10, Chapman Flack wrote:

On 02/27/16 08:37, Álvaro Hernández Tortosa wrote:

 In other words: what is the API surface exposed by PostgreSQL to
extension developers? The assumption is that no PostgreSQL code should be
modified, just adding your own and calling existing funcitons.

That's an excellent question that repeatedly comes up, in particular
because of the difference between the way the MSVC linker works on Windows,
and the way most other linkers work on other platforms.

The issue there is ... on most non-Windows platforms, there are only the
general C rules to think about: if a symbol is static (or auto of course)
it is not visible to extensions, but otherwise it is.

For MSVC, in contrast, symbols need to have a certain decoration
(look for PGDLLIMPORT in various PostgreSQL .h files) for an MSVC-built
extension to be able to see it, otherwise it isn't accessible.

Well, that's not quite right. It turns out (and it may have taken some
work on the build process to make it turn out this way) ... *functions*
are accessible from MSVC (as long as they would be accessible under
normal C rules) whether or not they have PGDLLIMPORT. It's just
data symbols/variables that have to have PGDLLIMPORT or they aren't
available on Windows/MSVC.

And *that* arrangement is the result of a long thread in 2014 that
unfolded after discovering that what was really happening in MSVC
*before* that was that MSVC would silently pretend to link your
non-PGDLLIMPORT data symbols, and then give you the wrong data.

http://www.postgresql.org/message-id/flat/52fab90b.6020...@2ndquadrant.com

In that long thread, there are a few messages in the middle that probably
give the closest current answer to your API question. Craig Ringer has
consistently favored making other platforms work more like Windows/MSVC,
so that the PGDLLIMPORT business would serve to limit and more clearly
define the API surface:

http://www.postgresql.org/message-id/52ef1468.6080...@2ndquadrant.com

Andres Freund had the pragmatic reply:

http://www.postgresql.org/message-id/20140203103701.ga1...@awork2.anarazel.de


I think that'd be an exercise in futility. ... We'd break countless
extensions people have written. ... we'd need to have a really
separate API layer ... doesn't seem likely to arrive anytime soon,
if ever.

which was ultimately concurred in by Tom, and Craig too:

http://www.postgresql.org/message-id/29286.1391436...@sss.pgh.pa.us
http://www.postgresql.org/message-id/52efa654.8010...@2ndquadrant.com

Andres characterized it as "We have a (mostly) proper API. Just not
an internal/external API split."

http://www.postgresql.org/message-id/20140203142514.gd1...@awork2.anarazel.de

-Chap


Hi Chapman.

Thank you very much for your detailed message and all the 
references. They were very appropiate.


However, I still lack a list of functions that might be callable (I 
understand not even those labeled with PGDLLIMPORT are all good 
candidates and some good candidates might not be labeled as such) from 
an extension point of view. Have you come across such a list over any of 
these threads? I haven't been able to find it.


Thanks for your input!

Álvaro


--
Álvaro Hernández Tortosa


---
8Kdata



--
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: [COMMITTERS] pgsql: Respect TEMP_CONFIG when running contrib regression tests.

[John Gorman]

On Sat, Feb 27, 2016 at 9:25 AM, Robert Haas  wrote:

> On Sat, Feb 27, 2016 at 7:08 PM, Andrew Dunstan 
> wrote:
>
> > Perhaps what we need to do is modify pg_regress.c slightly to allow more
> > than one --temp-config argument. But that could be done later.
>
> Well, I'm pretty interested in using --temp-config for parallelism
> testing; I want to be able to run the whole regression test suite with
> a given --temp-config.  I'm in agreement with this change but if it
> doesn't play well with that need, I suppose I'll be writing that
> pg_regress.c patch sooner rather than later.


Here is a patch to allow pg_regress to include several --temp-config files.


pg_regress-temp-configs-v1.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] The plan for FDW-based sharding

[Kevin Grittner]

On Fri, Feb 26, 2016 at 5:37 PM, Simon Riggs  wrote:
> On 26 February 2016 at 22:48, Kevin Grittner  wrote:

>> if we want logical
>> replication to be free of serialization anomalies for those using
>> serializable transactions, we need to support applying transactions
>> in an order which may not be the same as commit order -- CSN (as
>> such) would be the wrong thing.  If serializable transaction 1 (T1)
>> modifies a row and concurrent serializable transaction 2 (T2) reads
>> the old version of the row, and modifies something based on that,
>> T2 must be applied to a logical replica first even if T1 commits
>> before it; otherwise the logical replica could see a state not
>> consistent with business rules and which could not have been seen
>> (due to SSI) on the source database.
>
> How would SSI allow that commit order?
>
> Surely there is a read-write dependency that would cause T2 to be
> aborted?

*A* read-write dependency does not cause an abort under SSI, it
takes a *pattern* of read-write dependencies which has been proven
to appear in any set of concurrent transactions which can cause a
serialization anomaly.  A read-only transaction can be part of that
pattern.  On a single database SSI can see whether a read has
caused such a problem.  If you replicate the transactions to
somewhere else and read them SSI cannot tell whether there is an
anomaly (at least, not without exchanging a lot of information that
isn't currently happening), so some other mechanism would probably
need to be used.  One possibility is to pass along information
about when things are in a state on the source that is known to be
free of anomalies if read; another would be to reorder the
application of transactions to match the apparent order of
execution.  The latter would not work for "physical" replication,
but should be fine for logical replication.  An implementation
might create a list in commit order, but not release the front of
the list for processing if it is a SERIALIZABLE transaction which
has written data until all overlapping SERIALIZABLE transactions
complete, so it can move any subsequently-committed SERIALIZABLE
transaction which read the "old" version of the data ahead of it.

>> Any DTM API which does not
>> support some mechanism to rearrange the order of transactions from
>> commit order to some other order (based on, for example, read-write
>> dependencies) is not complete.  If it does support that, it gives
>> us a way forward for presenting consistent data on logical
>> replicas.
>
> You appear to be saying that SSI allows transactions to commit in a
> non-serializable order.

Absolutely not.  If you want to understand this better, this paper
might be helpful:

http://vldb.org/pvldb/vol5/p1850_danrkports_vldb2012.pdf

> Do you have a test case?

There are a couple in this section of the Wiki page of examples:

https://wiki.postgresql.org/wiki/SSI#Read_Only_Transactions

Just picture the read-only transaction executing on a replica.

Thinking of commit sequence number as the right order to apply
transactions during replication seems to me to be a holdover from
the techniques initially developed for transaction in the 1960s --
specifically, strict two-phase locking (S2PL) is very easy to get
one's head around and when using it the apparent order of execution
always *does* match commit order.  Unfortunately S2PL performs so
poorly that it was ripped out of PostgreSQL years ago.  In general,
I think it is time we gave up on thinking that is based on it.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] VACUUM Progress Checker.

[大山真実]

Hi!

I'm interesting this patch and tested it. I found two strange thing.

* Incorrect counting

Reproduce:
  1. Client1 execute "VACUUM"
  2. Client2 execute "VACUUM"
  3. Client3 execute "SELECT * FROM pg_stat_vacuum_progress".
 pid  | relid | phase | total_heap_blks | current_heap_blkno |
total_index_pages | scanned_index_pages | index_scan_count |
percent_complete
--+---+---+-++---+-+--+--
 9267 | 16551 | Scanning Heap |  164151 |316 |
27422 |   7 |1 |0
 9764 | 16554 | Scanning Heap |   2 |  2 |
2 |   27422 |1 |  100
(2 rows)

  Client2 is waiting for Clinet1 "VACUUM" but percent_complete of Client2
"VACUUM" is 100.

* Not end VACUUM ANALYZE in spite of "percent_complete=100"

  Client_1 execute "VACUUM ANALYZE", then Client_2 execute "SELECT * FROM
pg_stat_vacuum_progress".

 pid  | relid | phase | total_heap_blks | current_heap_blkno |
total_index_pages | scanned_index_pages | index_scan_count |
percent_complete
--+---+---+-++---+-+--+--
 9277 | 16551 | Scanning Heap |  163935 | 163935 |
27422 |   7 |1 |  100
(1 row

  percent_complete is 100 but Client_1 "VACUUM ANALYZE" do not response yet.

  Of course, Client_1 is executing analyze after vacuum. But it seem to me
that this confuses users.
  If percent_complete becomes 100 that row should be deleted quickly.

Regards,
Masanori Ohyama
NTT Open Source Software Center

2016年2月27日(土) 13:54 Vinayak Pokale :

> Hello,
>
> On Fri, Feb 26, 2016 at 6:19 PM, Amit Langote <
> langote_amit...@lab.ntt.co.jp> wrote:
>
>>
>> Hi Vinayak,
>>
>> Thanks for updating the patch! A quick comment:
>>
>> On 2016/02/26 17:28, poku...@pm.nttdata.co.jp wrote:
>> >> CREATE VIEW pg_stat_vacuum_progress AS
>> >>   SELECT S.s[1] as pid,
>> >>  S.s[2] as relid,
>> >>  CASE S.s[3]
>> >>WHEN 1 THEN 'Scanning Heap'
>> >>WHEN 2 THEN 'Vacuuming Index and Heap'
>> >>ELSE 'Unknown phase'
>> >>  END,
>> >>
>> >>   FROM pg_stat_get_command_progress(PROGRESS_COMMAND_VACUUM) as S;
>> >>
>> >> # The name of the function could be other than *_command_progress.
>> > The name of function is updated as pg_stat_get_progress_info() and also
>> updated the function.
>> > Updated the pg_stat_vacuum_progress view as suggested.
>>
>> So, pg_stat_get_progress_info() now accepts a parameter to distinguish
>> different commands.  I see the following in its definition:
>>
>> +   /*  Report values for only those backends which are
>> running VACUUM
>> command */
>> +   if (cmdtype == COMMAND_LAZY_VACUUM)
>> +   {
>> +   /*Progress can only be viewed by role member.*/
>> +   if (has_privs_of_role(GetUserId(),
>> beentry->st_userid))
>> +   {
>> +   values[2] =
>> UInt32GetDatum(beentry->st_progress_param[0]);
>> +   values[3] =
>> UInt32GetDatum(beentry->st_progress_param[1]);
>> +   values[4] =
>> UInt32GetDatum(beentry->st_progress_param[2]);
>> +   values[5] =
>> UInt32GetDatum(beentry->st_progress_param[3]);
>> +   values[6] =
>> UInt32GetDatum(beentry->st_progress_param[4]);
>> +   values[7] =
>> UInt32GetDatum(beentry->st_progress_param[5]);
>> +   if (beentry->st_progress_param[1] != 0)
>> +   values[8] =
>> Float8GetDatum(beentry->st_progress_param[2] * 100 /
>> beentry->st_progress_param[1]);
>> +   else
>> +   nulls[8] = true;
>> +   }
>> +   else
>> +   {
>> +   nulls[2] = true;
>> +   nulls[3] = true;
>> +   nulls[4] = true;
>> +   nulls[5] = true;
>> +   nulls[6] = true;
>> +   nulls[7] = true;
>> +   nulls[8] = true;
>> +   }
>> +   }
>>
>> How about doing this in a separate function which takes the command id as
>> parameter and returns an array of values and the number of values (per
>> command id). pg_stat_get_progress_info() then creates values[] and nulls[]
>> arrays from that and returns that as result set.  It will be a cleaner
>> separation of 

Re: [HACKERS] Proposal: Generic WAL logical messages

[Artur Zakirov]

Hello,

On 27.02.2016 03:05, Andres Freund wrote:

Hi,

I'm not really convinced by RegisterStandbyMsgPrefix() et al. There's
not much documentation about what it actually is supposed to
acomplish. Afaics you're basically forced to use
shared_preload_libraries with it right now?  Also, iterating through a
linked list everytime something is logged doesn't seem very satisfying?



I have did some tests with a simple plugin. I have used event triggers 
to send messages. It works, but I agree with Andres. We have problems if 
plugin is not loaded. For example, if you will execute the query:


SELECT 'msg2' FROM pg_logical_send_message(false, 'test', 'msg2');

you will get the error (if plugin which should register a prefix is not 
loaded yet):


ERROR:  standby message prefix "test" is not registered

Some stylistic note (logical.c):


+static void message_cb_wrapper(ReorderBuffer *cache, ReorderBufferTXN *txn,
+  XLogRecPtr 
message_lsn,
+  bool transactional, 
const char *prefix,
+  Size sz, const char 
*message)
+{
+   LogicalDecodingContext *ctx = cache->private_data;
+   LogicalErrorCallbackState state;
+   ErrorContextCallback errcallback;


It should be written in the following way:

static void
message_cb_wrapper(ReorderBuffer *cache, ReorderBufferTXN *txn,
   XLogRecPtr message_lsn,
   bool transactional, const char *prefix,
   Size sz, const char *message)
{
LogicalDecodingContext *ctx = cache->private_data;
LogicalErrorCallbackState state;
ErrorContextCallback errcallback;

--
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


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

[HACKERS] Typo fix

[Amit Langote]

Attached fixes a typo:

/* the extra unit accounts for the autovacuum launcher */

MaxBackends = MaxConnections + autovacuum_max_workers + 1 +

-   +max_worker_processes;

+   max_worker_processes;

Thanks,
Amit


typo.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] Re: [COMMITTERS] pgsql: Respect TEMP_CONFIG when running contrib regression tests.

[Robert Haas]

On Sat, Feb 27, 2016 at 8:36 PM, Andrew Dunstan  wrote:
> "doesn't meet your need" is probably a better way of putting it. The
> facility's use has grown beyond what I originally envisaged, so I think we
> will need that patch.
>
> Would you like me to apply what I have?

Go for it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[HACKERS] Re: [COMMITTERS] pgsql: Respect TEMP_CONFIG when running contrib regression tests.

[Andrew Dunstan]

On 02/27/2016 09:25 AM, Robert Haas wrote:

On Sat, Feb 27, 2016 at 7:08 PM, Andrew Dunstan  wrote:

What I had in mind was something like the attached.

In testing this seems to do the right thing, and the nice part is that it
will be picked up by the buildfarm in the one case that's relevant, namely
the ecpg tests.

The only fly in the ointment is that there are a few places that set
--temp-config explicitly:

./contrib/test_decoding/Makefile:--temp-config
$(top_srcdir)/contrib/test_decoding/logical.conf \
./contrib/test_decoding/Makefile:--temp-config
$(top_srcdir)/contrib/test_decoding/logical.conf \
./src/test/modules/commit_ts/Makefile:REGRESS_OPTS =
--temp-config=$(top_srcdir)/src/test/modules/commit_ts/commit_ts.conf
./src/test/modules/test_rls_hooks/Makefile:REGRESS_OPTS =

--temp-config=$(top_srcdir)/src/test/modules/test_rls_hooks/rls_hooks.conf

Perhaps what we need to do is modify pg_regress.c slightly to allow more
than one --temp-config argument. But that could be done later.

Well, I'm pretty interested in using --temp-config for parallelism
testing; I want to be able to run the whole regression test suite with
a given --temp-config.  I'm in agreement with this change but if it
doesn't play well with that need, I suppose I'll be writing that
pg_regress.c patch sooner rather than later.



"doesn't meet your need" is probably a better way of putting it. The 
facility's use has grown beyond what I originally envisaged, so I think 
we will need that patch.


Would you like me to apply what I have?

cheers

andrew


--
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: index-only scans with partial indexes

[Robert Haas]

On Sat, Feb 27, 2016 at 6:19 PM, Michael Paquier
 wrote:
> On Sat, Feb 27, 2016 at 1:08 AM, Robert Haas  wrote:
>> On Fri, Feb 26, 2016 at 6:16 PM, Michael Paquier
>>  wrote:
>>> On Fri, Feb 26, 2016 at 4:18 PM, Kyotaro HORIGUCHI
>>>  wrote:
 I marked this as "ready for commiter" and tried to add me as the
 *second* author. But the CF app forces certain msyterious order
 for listed names. Is there any means to arrange the author names
 in desired order?
>>>
>>> Those are automatically classified by alphabetical order.
>>
>> Doh.
>
> Hm? Not sure I am getting that. My point if that they could appear in
> the order they have been entered by the user or the order they have
> been saved, in which case one could take advantage of that to define
> the a list of authors ordered by the amount of work they did.

Well it seems to me it ought to let you specify the order.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] [PATH] Correct negative/zero year in to_date/to_timestamp

[Robert Haas]

On Tue, Feb 23, 2016 at 6:23 AM, Thomas Munro
 wrote:
> This seems to be a messy topic.  The usage of "AD" and "BC" imply that
> TO_DATE is using the anno domini system which doesn't have a year 0,
> but in the DATE type perhaps we are using the ISO 8601 model[2] where
> 1 BC is represented as , leading to the difference of one in all
> years before 1 AD?

Well, the way to figure that out, I think, is to look at the
documentation.  I had a look at...

http://www.postgresql.org/docs/9.5/static/functions-formatting.html

...which says...

 year (4 or more digits)
IYYY ISO 8601 week-numbering year (4 or more digits)

I don't really understand ISO 8601, but if IYYY is giving us an ISO
8601 thing, then presumably  is not supposed to be giving us that.
  The same page elsewhere refers to Gregorian dates, and other parts
of the documentation seem to agree that's what we use.

But having said that, this is kind of a weird situation.  We're
talking about this:

rhaas=# SELECT y || '-06-01', to_date (y || '-06-01', '-MM-DD')
FROM (VALUES (2), (1), (0), (-1), (-2)) t(y);
 ?column? |to_date
--+---
 2-06-01  | 0002-06-01
 1-06-01  | 0001-06-01
 0-06-01  | 0001-06-01 BC
 -1-06-01 | 0002-06-01 BC
 -2-06-01 | 0003-06-01 BC
(5 rows)

Now, I would be tempted to argue that passing to_date('-1-06-01',
'-MM-DD') ought to do the same thing as to_date('pickle',
'-MM-DD') i.e. throw an error.  There's all kinds of what seems to
me to be shoddy error checking in this area:

rhaas=# select to_date('-3', ':MM');
to_date
---
 0004-01-01 BC
(1 row)

It's pretty hard for me to swallow the argument that the input matches
the provided format.

However, I'm not sure we ought to tinker with the behavior in this
area.  If -MM-DD is going to accept things that are not of the
format -MM-DD, and I'd argue that -1-06-01 is not in that format,
then I think it should probably keep doing the same things it's always
done.  If you want to supply a BC date, why not do this:

rhaas=# select to_date('0001-06-01 BC', '-MM-DD BC');
to_date
---
 0001-06-01 BC
(1 row)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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 extension API? Documentation?

[Tom Lane]

Chapman Flack  writes:
> On 02/27/16 08:37, Álvaro Hernández Tortosa wrote:
>> In other words: what is the API surface exposed by PostgreSQL to
>> extension developers? The assumption is that no PostgreSQL code should be
>> modified, just adding your own and calling existing funcitons.

> That's an excellent question that repeatedly comes up, in particular
> because of the difference between the way the MSVC linker works on Windows,
> and the way most other linkers work on other platforms.

Yeah.  It would be a fine thing to have a document defining what we
consider to be the exposed API for extensions.  In most cases we could
not actually stop extension developers from relying on stuff outside the
defined API, and I don't particularly feel a need to try.  But it would be
clear to all concerned that if you rely on something not in the API, it's
your problem if we remove it or whack it around in some future release.
On the other side, it would be clearer to core-code developers which
changes should be avoided because they would cause pain to extension
authors.

Unfortunately, it would be a lot of work to develop such a thing, and no
one has wanted to take it on.

regards, tom lane


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

[HACKERS] Re: [COMMITTERS] pgsql: Respect TEMP_CONFIG when running contrib regression tests.

[Robert Haas]

On Sat, Feb 27, 2016 at 7:08 PM, Andrew Dunstan  wrote:
> What I had in mind was something like the attached.
>
> In testing this seems to do the right thing, and the nice part is that it
> will be picked up by the buildfarm in the one case that's relevant, namely
> the ecpg tests.
>
> The only fly in the ointment is that there are a few places that set
> --temp-config explicitly:
>
>./contrib/test_decoding/Makefile:--temp-config
>$(top_srcdir)/contrib/test_decoding/logical.conf \
>./contrib/test_decoding/Makefile:--temp-config
>$(top_srcdir)/contrib/test_decoding/logical.conf \
>./src/test/modules/commit_ts/Makefile:REGRESS_OPTS =
>--temp-config=$(top_srcdir)/src/test/modules/commit_ts/commit_ts.conf
>./src/test/modules/test_rls_hooks/Makefile:REGRESS_OPTS =
>
> --temp-config=$(top_srcdir)/src/test/modules/test_rls_hooks/rls_hooks.conf
>
> Perhaps what we need to do is modify pg_regress.c slightly to allow more
> than one --temp-config argument. But that could be done later.

Well, I'm pretty interested in using --temp-config for parallelism
testing; I want to be able to run the whole regression test suite with
a given --temp-config.  I'm in agreement with this change but if it
doesn't play well with that need, I suppose I'll be writing that
pg_regress.c patch sooner rather than later.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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 extension API? Documentation?

[Álvaro Hernández Tortosa]

On 27/02/16 15:01, Fabrízio de Royes Mello wrote:



On Sat, Feb 27, 2016 at 10:37 AM, Álvaro Hernández Tortosa 
> wrote:

>
>
> Hi.
>
> I have a newbie question for extension development. Extensions 
provide an entry point, and are dynamically linked to PostgreSQL. But 
what APIs/functions are really available for extensions to call?

>
> The most obvious API is SPI. You could also implements hooks. Of 
course, functions, types, aggregates, whatever. But can an extension 
call other "internal" PostgreSQL functions? Is there any restriction 
to what could --or should-- call an extension? Is there any specific 
API, or any documentation which states what is available to use?

>
> In other words: what is the API surface exposed by PostgreSQL to 
extension developers? The assumption is that no PostgreSQL code should 
be modified, just adding your own and calling existing funcitons.

>

I don't know what kind of problem you want to solve, but maybe you 
should ask to yourself:


Good point. I don't know. More precisely: no specific problem as of 
today. But if I knew all the "exposed API" I could more clearly think of 
what problems could be solved.


In other words: I see it's not clear what an extension could 
"extend". And knowing that would help extension developers to create new 
solutions.




1) I need to change some current PostgreSQL behavior?


If that means not changing current code, might well be an option.



2) I need to add a new feature do PostgreSQL without change the 
current behavior?


Writing a C extension you can access a lot of internal code if it's 
available internally by .h headers. For example, some time ago I'm 
thinking to write an extension to show more internal information about 
autovacuum (internal queue, etc... some like pg_stat_autovaccuum) . 
But nowadays is impossible without change the core because some 
internal structures are not exposed, so we should define an internal 
API to expose this kind of information.


So, calling any code exposed by the headers is ok for an extension? 
Is then the set of all .h files the "exposed API"? Or are some of those 
functions that should never be called?




So depending what problem you want to solve you can write an extension 
to do that. Then unfortunately the short aswer is "depend".


Hope that we can find a more general answer :) Thanks for your opinion!

Álvaro



--
Álvaro Hernández Tortosa


---
8Kdata

Re: [HACKERS] PostgreSQL extension API? Documentation?

[Chapman Flack]

On 02/27/16 08:37, Álvaro Hernández Tortosa wrote:
> In other words: what is the API surface exposed by PostgreSQL to
> extension developers? The assumption is that no PostgreSQL code should be
> modified, just adding your own and calling existing funcitons.

That's an excellent question that repeatedly comes up, in particular
because of the difference between the way the MSVC linker works on Windows,
and the way most other linkers work on other platforms.

The issue there is ... on most non-Windows platforms, there are only the
general C rules to think about: if a symbol is static (or auto of course)
it is not visible to extensions, but otherwise it is.

For MSVC, in contrast, symbols need to have a certain decoration
(look for PGDLLIMPORT in various PostgreSQL .h files) for an MSVC-built
extension to be able to see it, otherwise it isn't accessible.

Well, that's not quite right. It turns out (and it may have taken some
work on the build process to make it turn out this way) ... *functions*
are accessible from MSVC (as long as they would be accessible under
normal C rules) whether or not they have PGDLLIMPORT. It's just
data symbols/variables that have to have PGDLLIMPORT or they aren't
available on Windows/MSVC.

And *that* arrangement is the result of a long thread in 2014 that
unfolded after discovering that what was really happening in MSVC
*before* that was that MSVC would silently pretend to link your
non-PGDLLIMPORT data symbols, and then give you the wrong data.

http://www.postgresql.org/message-id/flat/52fab90b.6020...@2ndquadrant.com

In that long thread, there are a few messages in the middle that probably
give the closest current answer to your API question. Craig Ringer has
consistently favored making other platforms work more like Windows/MSVC,
so that the PGDLLIMPORT business would serve to limit and more clearly
define the API surface:

http://www.postgresql.org/message-id/52ef1468.6080...@2ndquadrant.com

Andres Freund had the pragmatic reply:

http://www.postgresql.org/message-id/20140203103701.ga1...@awork2.anarazel.de

> I think that'd be an exercise in futility. ... We'd break countless
> extensions people have written. ... we'd need to have a really
> separate API layer ... doesn't seem likely to arrive anytime soon,
> if ever.

which was ultimately concurred in by Tom, and Craig too:

http://www.postgresql.org/message-id/29286.1391436...@sss.pgh.pa.us
http://www.postgresql.org/message-id/52efa654.8010...@2ndquadrant.com

Andres characterized it as "We have a (mostly) proper API. Just not
an internal/external API split."

http://www.postgresql.org/message-id/20140203142514.gd1...@awork2.anarazel.de

-Chap


-- 
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 extension API? Documentation?

[Fabrízio de Royes Mello]

On Sat, Feb 27, 2016 at 10:37 AM, Álvaro Hernández Tortosa 
wrote:
>
>
> Hi.
>
> I have a newbie question for extension development. Extensions
provide an entry point, and are dynamically linked to PostgreSQL. But what
APIs/functions are really available for extensions to call?
>
> The most obvious API is SPI. You could also implements hooks. Of
course, functions, types, aggregates, whatever. But can an extension call
other "internal" PostgreSQL functions? Is there any restriction to what
could --or should-- call an extension? Is there any specific API, or any
documentation which states what is available to use?
>
> In other words: what is the API surface exposed by PostgreSQL to
extension developers? The assumption is that no PostgreSQL code should be
modified, just adding your own and calling existing funcitons.
>

I don't know what kind of problem you want to solve, but maybe you should
ask to yourself:

1) I need to change some current PostgreSQL behavior?

2) I need to add a new feature do PostgreSQL without change the current
behavior?

Writing a C extension you can access a lot of internal code if it's
available internally by .h headers. For example, some time ago I'm thinking
to write an extension to show more internal information about autovacuum
(internal queue, etc... some like pg_stat_autovaccuum) . But nowadays is
impossible without change the core because some internal structures are not
exposed, so we should define an internal API to expose this kind of
information.

So depending what problem you want to solve you can write an extension to
do that. Then unfortunately the short aswer is "depend".

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello

[HACKERS] PostgreSQL extension API? Documentation?

[Álvaro Hernández Tortosa]

Hi.

I have a newbie question for extension development. Extensions 
provide an entry point, and are dynamically linked to PostgreSQL. But 
what APIs/functions are really available for extensions to call?


The most obvious API is SPI. You could also implements hooks. Of 
course, functions, types, aggregates, whatever. But can an extension 
call other "internal" PostgreSQL functions? Is there any restriction to 
what could --or should-- call an extension? Is there any specific API, 
or any documentation which states what is available to use?


In other words: what is the API surface exposed by PostgreSQL to 
extension developers? The assumption is that no PostgreSQL code should 
be modified, just adding your own and calling existing funcitons.


Thanks,

Álvaro


--
Álvaro Hernández Tortosa


---
8Kdata



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

[HACKERS] Re: [COMMITTERS] pgsql: Respect TEMP_CONFIG when running contrib regression tests.

[Andrew Dunstan]

On 02/26/2016 10:59 PM, Robert Haas wrote:

On Sat, Feb 27, 2016 at 9:00 AM, Andrew Dunstan  wrote:

Sure.  Saving three lines of Makefile duplication is hardly a
world-shattering event, so I thought there might be some other
purpose.  But I'm not against saving three lines of duplication
either, if it won't break anything.

The point is that we should do this for several other test sets as well as
contrib - isolation tests, PL tests and ecpg tests.

OK, I was wondering about that.  I can try to write a patch, or
someone else can, but if you already understand what needs to be done,
perhaps you should just go ahead.




What I had in mind was something like the attached.

In testing this seems to do the right thing, and the nice part is that 
it will be picked up by the buildfarm in the one case that's relevant, 
namely the ecpg tests.


The only fly in the ointment is that there are a few places that set 
--temp-config explicitly:


   ./contrib/test_decoding/Makefile:--temp-config
   $(top_srcdir)/contrib/test_decoding/logical.conf \
   ./contrib/test_decoding/Makefile:--temp-config
   $(top_srcdir)/contrib/test_decoding/logical.conf \
   ./src/test/modules/commit_ts/Makefile:REGRESS_OPTS =
   --temp-config=$(top_srcdir)/src/test/modules/commit_ts/commit_ts.conf
   ./src/test/modules/test_rls_hooks/Makefile:REGRESS_OPTS =
   --temp-config=$(top_srcdir)/src/test/modules/test_rls_hooks/rls_hooks.conf


Perhaps what we need to do is modify pg_regress.c slightly to allow more 
than one --temp-config argument. But that could be done later.



cheers

andrew
diff --git a/contrib/contrib-global.mk b/contrib/contrib-global.mk
index ba49610..6ac8e9b 100644
--- a/contrib/contrib-global.mk
+++ b/contrib/contrib-global.mk
@@ -1,9 +1,4 @@
 # contrib/contrib-global.mk
 
-# file with extra config for temp build
-ifdef TEMP_CONFIG
-REGRESS_OPTS += --temp-config=$(TEMP_CONFIG)
-endif
-
 NO_PGXS = 1
 include $(top_srcdir)/src/makefiles/pgxs.mk
diff --git a/src/Makefile.global.in b/src/Makefile.global.in
index e94d6a5..47b265e 100644
--- a/src/Makefile.global.in
+++ b/src/Makefile.global.in
@@ -524,14 +524,20 @@ ifdef NO_LOCALE
 NOLOCALE += --no-locale
 endif
 
+# file with extra config for temp build
+TEMP_CONF =
+ifdef TEMP_CONFIG
+TEMP_CONF += --temp-config=$(TEMP_CONFIG)
+endif
+
 pg_regress_locale_flags = $(if $(ENCODING),--encoding=$(ENCODING)) $(NOLOCALE)
 
-pg_regress_check = $(with_temp_install) $(top_builddir)/src/test/regress/pg_regress --inputdir=$(srcdir) --temp-instance=./tmp_check --bindir= $(pg_regress_locale_flags) $(EXTRA_REGRESS_OPTS)
+pg_regress_check = $(with_temp_install) $(top_builddir)/src/test/regress/pg_regress --inputdir=$(srcdir) --temp-instance=./tmp_check $(TEMP_CONF) --bindir= $(pg_regress_locale_flags) $(EXTRA_REGRESS_OPTS)
 pg_regress_installcheck = $(top_builddir)/src/test/regress/pg_regress --inputdir=$(srcdir) --bindir='$(bindir)' $(pg_regress_locale_flags) $(EXTRA_REGRESS_OPTS)
 
 pg_regress_clean_files = results/ regression.diffs regression.out tmp_check/ log/
 
-pg_isolation_regress_check = $(with_temp_install) $(top_builddir)/src/test/isolation/pg_isolation_regress --inputdir=$(srcdir) --temp-instance=./tmp_check --bindir= $(pg_regress_locale_flags) $(EXTRA_REGRESS_OPTS)
+pg_isolation_regress_check = $(with_temp_install) $(top_builddir)/src/test/isolation/pg_isolation_regress --inputdir=$(srcdir) --temp-instance=./tmp_check $(TEMP_CONF) --bindir= $(pg_regress_locale_flags) $(EXTRA_REGRESS_OPTS)
 pg_isolation_regress_installcheck = $(top_builddir)/src/test/isolation/pg_isolation_regress --inputdir=$(srcdir) $(pg_regress_locale_flags) $(EXTRA_REGRESS_OPTS)
 
 ##
diff --git a/src/interfaces/ecpg/test/Makefile b/src/interfaces/ecpg/test/Makefile
index a4ac021..4ed785b 100644
--- a/src/interfaces/ecpg/test/Makefile
+++ b/src/interfaces/ecpg/test/Makefile
@@ -78,11 +78,11 @@ endif
 REGRESS_OPTS = --dbname=regress1,connectdb --create-role=connectuser,connectdb $(EXTRA_REGRESS_OPTS)
 
 check: all
-	$(with_temp_install) ./pg_regress $(REGRESS_OPTS) --temp-instance=./tmp_check --bindir= $(pg_regress_locale_flags) $(THREAD) --schedule=$(srcdir)/ecpg_schedule
+	$(with_temp_install) ./pg_regress $(REGRESS_OPTS) --temp-instance=./tmp_check $(TEMP_CONF) --bindir= $(pg_regress_locale_flags) $(THREAD) --schedule=$(srcdir)/ecpg_schedule
 
 # the same options, but with --listen-on-tcp
 checktcp: all
-	$(with_temp_install) ./pg_regress $(REGRESS_OPTS) --temp-instance=./tmp_check --bindir= $(pg_regress_locale_flags) $(THREAD) --schedule=$(srcdir)/ecpg_schedule_tcp --host=localhost
+	$(with_temp_install) ./pg_regress $(REGRESS_OPTS) --temp-instance=./tmp_check $(TEMP_CONF) --bindir= $(pg_regress_locale_flags) $(THREAD) --schedule=$(srcdir)/ecpg_schedule_tcp --host=localhost
 
 installcheck: all
 	./pg_regress $(REGRESS_OPTS) --bindir='$(bindir)' $(pg_regress_locale_flags) $(THREAD) 

Re: [HACKERS] Proposal: "Causal reads" mode for load balancing reads without stale data

[Michael Paquier]

On Mon, Feb 22, 2016 at 9:39 AM, Thom Brown  wrote:
> On 21 February 2016 at 23:18, Thomas Munro
>  wrote:
> The replay_lag is particularly cool.  Didn't think it was possible to
> glean this information on the primary, but the timings are correct in
> my tests.
>
> +1 for this patch.  Looks like this solves the problem that
> semi-synchronous replication tries to solve, although arguably in a
> more sensible way.

Yeah, having extra logic at application layer to check if a certain
LSN position has been applied or not is doable, but if we can avoid it
that's a clear plus.

This patch has no documentation. I will try to figure out by myself
how the new parameters interact with the rest of the syncrep code
while looking at it but if we want to move on to get something
committable for 9.6 it would be good to get some documentation soon.
-- 
Michael


-- 
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] fix DROP OPERATOR to reset links to itself on commutator and negator

[Michael Paquier]

On Sat, Feb 27, 2016 at 12:46 AM, Roma Sokolov  wrote:
> Should this patch be added to CommitFest?

Yes please.
-- 
Michael


-- 
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: get oldest LSN - function

[Michael Paquier]

On Sat, Feb 27, 2016 at 3:52 PM, Kartyshov Ivan
 wrote:
> On 27.02.2016 03:07, Andres Freund wrote
>>
>> How does it help with any of that?
>
> Maybe i wasn't too accurate in terms, because I newbie, but:
> We can get information about xlog, using big amout of support function
> (pg_current_xlog_location(), pg_current_xlog_insert_location(),
> pg_xlogfile_name_offset(), pg_xlogfile_name(),
> pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), ... etc)
> they helps to get get useful information about xlog files and its content.
> So, this patch extends this amount of functions.
> It is useful additional tool for DBA (we can get replicationSlotMinLSN, so
> why not in master), it can show us, if xlog replication or wal-sender is
> working properly or indicate if replication on startup can get up to date
> with master, or after long turnoff must be recovered from archive.

What pg_ls_dir('pg_xlog') couldn't do here if you need to know the
last WAL segment present on master?
-- 
Michael


-- 
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: index-only scans with partial indexes

[Michael Paquier]

On Sat, Feb 27, 2016 at 1:08 AM, Robert Haas  wrote:
> On Fri, Feb 26, 2016 at 6:16 PM, Michael Paquier
>  wrote:
>> On Fri, Feb 26, 2016 at 4:18 PM, Kyotaro HORIGUCHI
>>  wrote:
>>> I marked this as "ready for commiter" and tried to add me as the
>>> *second* author. But the CF app forces certain msyterious order
>>> for listed names. Is there any means to arrange the author names
>>> in desired order?
>>
>> Those are automatically classified by alphabetical order.
>
> Doh.

Hm? Not sure I am getting that. My point if that they could appear in
the order they have been entered by the user or the order they have
been saved, in which case one could take advantage of that to define
the a list of authors ordered by the amount of work they did.
-- 
Michael


-- 
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] The plan for FDW-based sharding

[Álvaro Hernández Tortosa]

On 27/02/16 09:19, Konstantin Knizhnik wrote:

On 02/27/2016 06:54 AM, Robert Haas wrote:


[...]



So maybe the goal for the GTM isn't to provide true serializability

across the cluster but some lesser degree of transaction isolation.
But then exactly which serialization anomalies are we trying to
prevent, and why is it OK to prevent those and not others?

Absolutely agree. There are some theoretical discussion regarding CAP 
and different distributed level of isolation.
But at practice people want to solve their tasks. Most of PostgeSQL 
used are using default isolation level: read committed although there 
are alot of "wonderful" anomalies with it.
Serialazable transaction in Oracle are actually violating fundamental 
serializability rule and still Oracle is one of ther most popular 
database in the world...
The was isolation bug in Postgres-XL which doesn't prevent from using 
it by commercial customers...


I think this might be a dangerous line of thought. While I agree 
PostgreSQL should definitely look at the market and answer questions 
that (current and prospective) users may ask, and be more practical than 
idealist, easily ditching isolation guarantees might not be a good thing.


 That Oracle is the leader with their isolation problems or that 
most people run PostgreSQL under read committed is not a good argument 
to cut the corner and just go to bare minimum (if any) isolation 
guarantees. First, because PostgreSQL has always been trusted and 
understood as a system with *strong* guarantees (whatever that means). . 
Second, because what we may perceive as OK from the market, might change 
soon. From my observations, while I agree with you most people "don't 
care" or, worse, "don't realize", is rapidly changing. More and more 
people are becoming aware of the problems of distributed systems and the 
significant consequences they may have on them.


A lot of them have been illustrated in the famous Jepsen posts. As 
an example, and a good one given that you have mentioned Galera before, 
is this one: https://aphyr.com/posts/327-jepsen-mariadb-galera-cluster 
which demonstrates how Galera fails to provide Snapshot Isolation, even 
on healthy state --despite they claim that.


As of today, I would expect any distributed system to clearly state 
its guarantees in the documentation. And them adhere to them, like for 
instance proving it with tests such as Jepsen.




So I do not say that discussing all this theoretical questions is not 
need as formally proven correctness of distributed algorithm.


I would like to see work forward here, so I really appreciate all 
your work here. I cannot give an opinion on whether the DTM API is good 
or not, but I agree with Robert a good technical discussion on these 
issues is a good, and a needed, starting point. Feedback may also help 
you avoid pitfalls that may have gone unnoticed until tons of code are 
implemented.


Academical approaches are sometimes "very academical", but studying 
them doesn't hurt either :)



Álvaro


--
Álvaro Hernández Tortosa


---
8Kdata



--
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] The plan for FDW-based sharding

[Konstantin Knizhnik]

On 02/27/2016 06:54 AM, Robert Haas wrote:

On Fri, Feb 26, 2016 at 10:56 PM, Konstantin Knizhnik
 wrote:

We do not have formal prove that proposed XTM is "general enough" to handle
all possible transaction manager implementations.
But there are two general ways of dealing with isolation: snapshot based and
CSN  based.

I don't believe that for a minute.  For example, consider this article:


Well, I have to agree that saying that there are just two ways of providing 
distributed isolation I was not right.
There is at least one more method: conservative locking. But it will cause huge 
number of extra network messages which has to be exchanged.
Also I mostly considered solutions compatible with PostgreSQL MVCC model.

And definitely their are other approaches. Like preserving transaction commit 
order (as it is done in Galera).
Some other them can be implemented with XTM (preserving commit order), some - 
not (2PL).
I have already noticed that XTM is not allowing to implement ANY transaction 
manager.
But we have considered several approaches to distributed transaction management 
explained in the article related with really working systems.
Some of them are real production system as SAP HANA, some are just prototypes, 
but working prototypes for which authors have performed
some benchmarking and comparison with other approaches. The references you have 
mentioned are mostly theoretical description of the problem.
Nice to know it but it is hard to build some concrete implementation based on 
this articles.


Briefly answering other your questions:


For example, consider a table with a million rows spread across any number of 
servers.


It is sharding scenario, pg_tsdtm will work well in this case does not 
requiring sending a lot of extra messages.


Now consider another workload where each transaction reads a row one

one server, reads a row on another server,

It can be solved both with pg_dtm (central arbiter) and pg_tsdtm (no arbiter),
But actually you scenarios just once again proves that there can not be just 
one ideal distributed TM.


So maybe the goal for the GTM isn't to provide true serializability

across the cluster but some lesser degree of transaction isolation.
But then exactly which serialization anomalies are we trying to
prevent, and why is it OK to prevent those and not others?

Absolutely agree. There are some theoretical discussion regarding CAP and 
different distributed level of isolation.
But at practice people want to solve their tasks. Most of PostgeSQL used are using 
default isolation level: read committed although there are alot of "wonderful" 
anomalies with it.
Serialazable transaction in Oracle are actually violating fundamental 
serializability rule and still Oracle is one of ther most popular database in 
the world...
The was isolation bug in Postgres-XL which doesn't prevent from using it by 
commercial customers...

So I do not say that discussing all this theoretical questions is not need as 
formally proven correctness of distributed algorithm.
But I do not understand hot why it should prevent from providing extensible TM 
API.
Yes, we can tot do everything with it. But still we can implement many 
different approaches.
I think that it somehow proves that it is "general enough".






 






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

I think the neutrality of that article is *very* debatable, but it
certainly contradicts the idea that snapshots and CSNs are the only
methods of achieving global serializability.

Or consider this lecture:

http://hssl.cs.jhu.edu/~randal/416/lectures.old/ln5.2.pdf

That's a great introduction to the problem we're trying to solve here,
but again, snapshots are not mentioned, and CSNs certainly aren't
mentioned.

This write-up goes further, explaining three different methods for
ensuring global serializability, none of which mention snapshots or
CSNs:

http://heaven.eee.metu.edu.tr/~vision/LectureNotes/EE442/Ee442ch7.html

Actually, I think the second approach is basically a snapshot/CSN-type
approach, but it doesn't use that terminology and the connection to
what you are proposing is very unclear.

I think you're approaching this problem from a viewpoint that is
entirely too focused on the code that exists in PostgreSQL today.
Lots of people have done lots of academic research on how to solve
this problem, and you can't possibly say that CSNs and snapshots are
the only solution to this problem unless you haven't read any of those
papers.  The articles above aren't exceptional in mentioning neither
of the approaches that you are advocating - they are typical of the
literature in this area.  How can it be that the only solutions to
this problem are ones that are totally different from the approaches
that university professors who spend time doing research on
concurrency have spent time exploring?

I think we need to back up here and examine our underlying design
assumptions.  The goal here