Re: [HACKERS] [PATCH] Refactoring of LWLock tranches

2015-12-31 Thread Amit Kapila 
On Thu, Dec 31, 2015 at 5:06 PM, Amit Kapila 
wrote:
>
> On Tue, Dec 29, 2015 at 2:26 PM, Amit Kapila 
wrote:
>>
>> On Wed, Dec 16, 2015 at 12:26 AM, Robert Haas 
wrote:
>> >
>> >

I have moved this patch to new CF as the work is still in
progress.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] RFC: replace pg_stat_activity.waiting with something more descriptive

2015-12-31 Thread Amit Kapila 
On Thu, Dec 24, 2015 at 8:02 AM, Michael Paquier 
wrote:
>
> On Tue, Nov 17, 2015 at 8:36 PM, Vladimir Borodin 
wrote:
> >
> > 14 нояб. 2015 г., в 10:50, Amit Kapila 
написал(а):
> >
> > On Wed, Sep 16, 2015 at 11:22 PM, Robert Haas 
wrote:
> >> On Wed, Sep 16, 2015 at 12:29 PM, Alexander Korotkov
> >>  wrote:
> >
> > One thing that occurred to me in this context is that if we store the
wait
> > event information in PGPROC, then can we think of providing the info
> > about wait events in a separate view pg_stat_waits (or
pg_stat_wait_info or
> > any other better name) where we can display wait information about
> > all-processes rather than only backends?  This will avoid the confusion
> > about breaking the backward compatibility for the current 'waiting'
column
> > in pg_stat_activity.
> >
> > pg_stat_waits can have columns:
> > pid  - Process Id
> > wait_class_name - Name of the wait class
> > wait class_event  - name of the wait event
> >
> > We can extend it later with the information about timing for wait event.
> >
> > Also, if we follow this approach, I think we don't need to store this
> > information in PgBackendStatus.
> >
> >
> > Sounds like exactly the same that was proposed by Ildus in this thead
[0].
> > Great to be thinking in the same direction. And on the rights of
> > advertisements I’ve somehow described using all those views here [1].
> >
> > [0] http://www.postgresql.org/message-id/559d4729.9080...@postgrespro.ru
> > [1] https://simply.name/pg-stat-wait.html
>
> This thread has stalled a bit and is waiting for new patches for some
> time now, hence I have switched it as "returned with feedback" on the
> CF app.
>

The reason for not updating the patch related to this thread is that it is
dependent on the work for refactoring the tranches for LWLocks [1]
which is now coming towards an end, so I think it is quite reasonable
that the patch can be updated for this work during commit fest, so
I am moving it to upcoming CF.

[1] -
http://www.postgresql.org/message-id/caa4ek1kjshwp0dfcq6chpnq_wjqwleapmfd4z6kusltqtuz...@mail.gmail.com

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] proposal: PL/Pythonu - function ereport

2015-12-31 Thread Pavel Stehule 
Hi

here is new version.

Now I use a common ancestor "plpy.BaseError" for plpy builtin classes. So
plpy.SPIError isn't descendant of plpy.Error and then there are not
possible incompatibility issues.

Instead modification builtin function plpy.debug, plpy.info, ... and
introduction incompatibility I wrote new set of functions with keyword
parameters (used mainly  for elevel < ERROR):

plpy.raise_debug, plpy.raise_info, plpy.raise_notice, plpy.raise_warning,
plpy.raise_error and plpy.raise_fatal.

With this patch we can write:

plpy.raise_warning('some is wrong', hint = 'bla bla')
raise plpy.Error(some is wrong', sqlcode = 'XX543')

Regards

Pavel
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
new file mode 100644
index 015bbad..089b143
*** a/doc/src/sgml/plpython.sgml
--- b/doc/src/sgml/plpython.sgml
*** $$ LANGUAGE plpythonu;
*** 1205,1210 
--- 1205,1228 
  approximately the same functionality
 

+ 
+   
+Raising Errors
+ 
+
+ A plpy.Error can be raised from PL/Python, the constructor accepts
+ keyword parameters:
+ plpy.Error([ message [, detail [, hint [, sqlstate  [, schema  [, table  [, column  [, datatype  [, constraint ]).
+
+
+ An example of raising custom exception could be written as:
+ 
+ DO $$
+   raise plpy.Error('custom message', hint = 'It is test only');
+ $$ LANGUAGE plpythonu;
+ 
+
+   
   
  
   
*** $$ LANGUAGE plpythonu;
*** 1367,1372 
--- 1385,1421 

  

+The plpy module also provides the functions
+plpy.raise_debug(args),
+plpy.raise_log(args),
+plpy.raise_info(args),
+plpy.raise_notice(args),
+plpy.raise_warning(args),
+plpy.raise_error(args), and
+plpy.raise_fatal(args).elogin PL/Python
+plpy.raise_error and
+plpy.raise_fatal actually raise a Python exception
+which, if uncaught, propagates out to the calling query, causing
+the current transaction or subtransaction to be aborted.
+raise plpy.Error(msg) and
+raise plpy.Fatal(msg) are
+equivalent to calling
+plpy.raise_error and
+plpy.raise_fatal, respectively.
+The other functions only generate messages of different
+priority levels.
+Whether messages of a particular priority are reported to the client,
+written to the server log, or both is controlled by the
+ and
+ configuration
+variables. See  for more information.
+These functions allows to using keyword parameters:
+[ message [, detail [, hint [, sqlstate  [, schema  [, table  [, column  [, datatype  [, constraint ].
+ 
+   
+ 
+ 
+   
 Another set of utility functions are
 plpy.quote_literal(string),
 plpy.quote_nullable(string), and
diff --git a/src/pl/plpython/expected/plpython_error.out b/src/pl/plpython/expected/plpython_error.out
new file mode 100644
index 1f52af7..cb792eb
*** a/src/pl/plpython/expected/plpython_error.out
--- b/src/pl/plpython/expected/plpython_error.out
*** EXCEPTION WHEN SQLSTATE 'SILLY' THEN
*** 422,424 
--- 422,486 
  	-- NOOP
  END
  $$ LANGUAGE plpgsql;
+ /* the possibility to set fields of custom exception
+  */
+ DO $$
+ raise plpy.Error('This is message text.',
+ detail = 'This is detail text',
+ hint = 'This is hint text.')
+ $$ LANGUAGE plpythonu;
+ ERROR:  plpy.Error: This is message text.
+ DETAIL:  This is detail text
+ HINT:  This is hint text.
+ CONTEXT:  Traceback (most recent call last):
+   PL/Python anonymous code block, line 4, in 
+ hint = 'This is hint text.')
+ PL/Python anonymous code block
+ \set VERBOSITY verbose
+ DO $$
+ raise plpy.Error('This is message text.',
+ detail = 'This is detail text',
+ hint = 'This is hint text.',
+ sqlstate = 'SILLY',
+ schema = 'any info about schema',
+ table = 'any info about table',
+ column = 'any info about column',
+ datatype = 'any info about datatype',
+ constraint = 'any info about constraint')
+ $$ LANGUAGE plpythonu;
+ ERROR:  SILLY: plpy.Error: This is message text.
+ DETAIL:  This is detail text
+ HINT:  This is hint text.
+ CONTEXT:  Traceback (most recent call last):
+   PL/Python anonymous code block, line 10, in 
+ constraint = 'any info about constraint')
+ PL/Python anonymous code block
+ SCHEMA NAME:  any info about schema
+ TABLE NAME:  any info about table
+ COLUMN NAME:  any info about column
+ DATATYPE NAME:  any info about datatype
+ CONSTRAINT NAME:  any info about constraint
+ LOCATION:  PLy_elog, plpy_elog.c:122
+ \set VERBOSITY default
+ DO $$
+ raise plpy.Error(detail = 'This is detail text')
+ $$ LANGUAGE plpythonu;
+ ERROR:  plpy.Error: 
+ DETAIL:  This is detail text
+ CONTEXT:  Traceback (most recent call last):
+   PL/Python anonymous code block, line 2, in 
+ raise plpy.Error(detail =

Re: [HACKERS] [PATCH] Refactoring of LWLock tranches

2015-12-31 Thread Amit Kapila 
On Tue, Dec 29, 2015 at 2:26 PM, Amit Kapila 
wrote:

> On Wed, Dec 16, 2015 at 12:26 AM, Robert Haas 
> wrote:
> >
> >
> > In terms of this project overall, NumLWLocks() now knows about only
> > four categories of stuff: fixed lwlocks, backend locks (proc.c),
> > replication slot locks, and locks needed by extensions.  I think it'd
> > probably be fine to move the backend locks into PGPROC directly, and
> > the replication slot locks into ReplicationSlot.
> >
>
> IIdus has written a patch to move backend locks into PGPROC which
> I am reviewing and will do performance tests once he responds to
> my review comments and I have written a patch to move replication
> slot locks into ReplicationSlot which is attached with this mail.
>
>
Going further on this work, I have written a patch for separating the
tranches for extensions.  The basic idea is to expose two new API's,
first to request a new tranche and second to assign a lock from that
tranche.
RequestAddinLWLockTranche(const char *tranche_name, int num_lwlocks)
will be used by extensions to request a new tranche with specified number
of locks, this will be used instead of current API RequestAddinLWLocks().
We need to remember this information for each extension and then
during startup we need to create separate tranches and still have locks
for extensions in the MainLWLockArray such that the base for each
tranche will point to the locks corresponding to that tranche.  As for
each proc/backend, we need to register the tranche separately, the
information of newly formed tranches needs to be passed to backends
via save/restore_backend_variables mechanism for EXEC_BACKEND
builds.
LWLock *LWLockAssignFromTranche(const char *tranche_name) will
assign a lock for the specified tranche.  This also ensures that no
more than requested number of LWLocks can be assigned from a
specified tranche.

Also I have retained NUM_USER_DEFINED_LWLOCKS in
MainLWLockArray so that if any extensions want to assign a LWLock
after startup, it can be used from this pool.  The tranche for such locks
will be reported as main.

This is based on the suggestions by Robert in the mail:
http://www.postgresql.org/message-id/CA+TgmoashjaQeSK1bEm-GGc8OWFyLhvOrH=4kjfvkrft9yk...@mail.gmail.com

Thoughts?


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


separate_tranche_extensions_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

[HACKERS] 2016-01 Commitfest

2015-12-31 Thread Alvaro Herrera 
Hello,

As discussed, I will be managing the commitfest process this time
around.  Please, everyone, make sure patches are registered in the app
today.  As of this instant, we have:

 Needs review: 73.
 Waiting on Author: 5.
 Ready for Committer: 6.
 Committed: 5.
Total: 89. 

Thanks,

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] Relation extension scalability

2015-12-31 Thread Dilip Kumar 
On Fri, Dec 18, 2015 at 10:51 AM, Dilip Kumar  wrote:

> On Sun, Jul 19 2015 9:37 PM Andres Wrote,
>
> > The situation the read() protect us against is that two backends try to
> > extend to the same block, but after one of them succeeded the buffer is
> > written out and reused for an independent page. So there is no in-memory
> > state telling the slower backend that that page has already been used.
>
> I was looking into this patch, and done some performance testing..
>
> Currently i have done testing my my local machine, later i will perform on
> big machine once i get access to that.
>
> Just wanted to share the current result what i get i my local machine
> Machine conf (Intel(R) Core(TM) i7-4870HQ CPU @ 2.50GHz, 8 core and 16GM
> of RAM).
>
> Test Script:
> ./psql -d postgres -c "COPY (select g.i::text FROM generate_series(1,
> 1) g(i)) TO '/tmp/copybinarywide' WITH BINARY";
>
> ./psql -d postgres -c "truncate table data"
> ./psql -d postgres -c "checkpoint"
> ./pgbench -f copy_script -T 120 -c$ -j$ postgres
>

This time i have done some testing on big machine with* 64 physical cores @
2.13GHz and 50GB of RAM*

There is performance comparison of base, extend without
RelationExtensionLock patch given by Andres and
multi-extend patch (this will extend the multiple blocks at a time based on
a configuration parameter.)


*Problem Analysis:*
1. With base code when i try to observe the problem using perf and other
method (gdb), i found that RelationExtensionLock is main bottleneck.
2. Then after using RelationExtensionLock Free patch, i observed now
contention is FileWrite (All backends are trying to extend the file.)


*Performance Summary and
Analysis:*
1. In my performance results Multi Extend shown best performance and
scalability.
2. I think by extending in multiple blocks we solves both the
problem(Extension Lock and Parallel File Write).
3. After extending one Block immediately adding to FSM so in most of the
cases other backend can directly find it without taking extension lock.

Currently the patch is in initial stage, i have done only test performance
and pass the regress test suit.



*Open problems -*
1. After extending the page we are adding it directly to FSM, so if vacuum
find this page as new it will give WARNING.
2. In RelationGetBufferForTuple, when PageIsNew, we are doing PageInit,
same need to be consider for index cases.



*Test Script:-*
./psql -d postgres -c "COPY (select g.i::text FROM generate_series(1,
1) g(i)) TO '/tmp/copybinarywide' WITH BINARY";

./psql -d postgres -c "truncate table data"
./psql -d postgres -c "checkpoint"
./pgbench -f copy_script -T 120 -c$ -j$ postgres

*Performanec Data:*
--
*There are Three code base for performance*
1. Base Code

2. Lock Free Patch : patch given in below thread
*http://www.postgresql.org/message-id/20150719140746.gh25...@awork2.anarazel.de
*

3. Multi extend patch attached in the mail.
*#extend_num_pages : *This this new config parameter to tell how many extra
page extend in case of normal extend..
may be it will give more control to user if we make it relation property.

I will work on the patch for this CF, so adding it to CF.


*Shared Buffer 48 GB*


*Clients* *Base (TPS)*
*Lock Free Patch* *Multi-extend **extend_num_pages=5* 1 142 138 148 2 251
253 280 4 237 416 464 8 168 491 575 16 141 448 404 32 122 337 332



*Shared Buffer 64 MB*


*Clients* *Base (TPS)* *Multi-extend **extend_num_pages=5*
1 140 148
2 252 266
4 229 437
8 153 475
16 132 364


Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
*** a/src/backend/access/brin/brin_pageops.c
--- b/src/backend/access/brin/brin_pageops.c
***
*** 771,776  brin_getinsertbuffer(Relation irel, Buffer oldbuf, Size itemsz,
--- 771,781 
  			UnlockRelationForExtension(irel, ExclusiveLock);
  
  		page = BufferGetPage(buf);
+ 		if (PageIsNew(page))
+ 		{
+ 			MarkBufferDirty(buf);
+ 			PageInit(page, BufferGetPageSize(buf), 0);
+ 		}
  
  		/*
  		 * We have a new buffer to insert into.  Check that the new page has
*** a/src/backend/access/heap/hio.c
--- b/src/backend/access/heap/hio.c
***
*** 393,398  RelationGetBufferForTuple(Relation relation, Size len,
--- 393,404 
  		 * we're done.
  		 */
  		page = BufferGetPage(buffer);
+ 		if (PageIsNew(page))
+ 		{
+ 			MarkBufferDirty(buffer);
+ 			PageInit(page, BufferGetPageSize(buffer), 0);
+ 		}
+ 
  		pageFreeSpace = PageGetHeapFreeSpace(page);
  		if (len + saveFreeSpace <= pageFreeSpace)
  		{
*** a/src/backend/storage/buffer/bufmgr.c
--- b/src/backend/storage/buffer/bufmgr.c
***
*** 90,95  int			effective_io_concurrency = 0;
--- 90,96 
   * effective_io_concurrency parameter set.
   */
  int

Re: [HACKERS] [PATCH] Refactoring of LWLock tranches

2015-12-31 Thread Jesper Pedersen 

On 12/31/2015 06:36 AM, Amit Kapila wrote:

Going further on this work, I have written a patch for separating the
tranches for extensions.  The basic idea is to expose two new API's,
first to request a new tranche and second to assign a lock from that
tranche.
RequestAddinLWLockTranche(const char *tranche_name, int num_lwlocks)
will be used by extensions to request a new tranche with specified number
of locks, this will be used instead of current API RequestAddinLWLocks().
We need to remember this information for each extension and then
during startup we need to create separate tranches and still have locks
for extensions in the MainLWLockArray such that the base for each
tranche will point to the locks corresponding to that tranche.  As for
each proc/backend, we need to register the tranche separately, the
information of newly formed tranches needs to be passed to backends
via save/restore_backend_variables mechanism for EXEC_BACKEND
builds.
LWLock *LWLockAssignFromTranche(const char *tranche_name) will
assign a lock for the specified tranche.  This also ensures that no
more than requested number of LWLocks can be assigned from a
specified tranche.

Also I have retained NUM_USER_DEFINED_LWLOCKS in
MainLWLockArray so that if any extensions want to assign a LWLock
after startup, it can be used from this pool.  The tranche for such locks
will be reported as main.

This is based on the suggestions by Robert in the mail:
http://www.postgresql.org/message-id/CA+TgmoashjaQeSK1bEm-GGc8OWFyLhvOrH=4kjfvkrft9yk...@mail.gmail.com

Thoughts?



+1 for the idea.

However, RequestAddinLWLocks and LWLockAssign are used by extensions 
outside of the main tree, so I think it would be better to deprecate the 
methods for starters with a log statement.


NUM_USER_DEFINED_LWLOCKS aren't enough in many cases, so the existing 
functionality needs to be maintained during the deprecation period.


If extensions needs to upgrade to the new API I think LWLockAssign 
should be removed.


doc/src/sgml/xfunc.sgml needs an update on how the new API should be used.

Best regards,
 Jesper



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

[HACKERS] Keyword classifications

2015-12-31 Thread Tom Lane 
I did a bit of initial poking at the problem complained of in bug #13840,
namely that if you use "none" as the value of a reloption, you get a
syntax error when trying to reload a dump containing the table or index
declaration.  The submitter blames this on pg_dump but it is surely not
pg_dump's fault; it's just printing what pg_get_indexdef() gave it.
And what pg_get_indexdef() prints is just verbatim what is in each array
element of pg_class.reloptions.

Now, one line of thought here is that flatten_reloptions() is out of its
mind to not be worrying about quoting the reloption values.  And perhaps
it is, but I think if we go that direction, we may be fighting similar
fires for awhile to come.  psql's describe.c, for example, doesn't worry
about quoting anything when printing reloptions, and there's likely
similar code in third-party clients.  Also, a solution like this would
do nothing for existing dump files.

The other line of thought is that we're already making an effort to allow
any keyword to appear as the value of a def_arg, and maybe we should try
to make that work 100% instead of only 90%.  The existing code in
gram.y is:

def_arg:func_type   { $$ = (Node *)$1; }
| reserved_keyword  { $$ = (Node 
*)makeString(pstrdup($1)); }
| qual_all_Op   { $$ = (Node *)$1; }
| NumericOnly   { $$ = (Node *)$1; }
| Sconst{ $$ = (Node *)makeString($1); }
;

so we already allow any keyword that can be a type name, as well as
all fully reserved keywords.  The only thing that's missing is
col_name_keyword (which, not coincidentally, includes NONE).  Now,
if you just try to add a production for col_name_keyword like the one
for reserved_keyword, you get a bunch of reduce-reduce errors because
col_name_keyword includes several words that can also be type names, and
Bison can't decide whether those should go through the func_type or
col_name_keyword paths.  But some experimentation suggests that we could
fix that by subdividing col_name_keyword into two categories, one being
the keywords that can also be type names (BIGINT, BIT, etc) and one
being those that can't (BETWEEN, COALESCE, etc).  Everywhere
col_name_keyword is used now, just mention both sub-categories.
And in def_arg, add a production for only the second sub-category.

I think such a categorization would actually be cleaner than what we have
now; if you read the comments for col_name_keyword, they're pretty squishy
about whether these keywords can be type or function names or not, and
this subdivision would make that a little clearer.  Interestingly, it
also seems that the grammar tables become slightly smaller, suggesting
that Bison also finds this more regular.

However, we have a problem, which is that in the distant past somebody
had the bright idea of exposing the keyword categories to userspace via
pg_get_keywords().  That means we have to either add a new category code
to pg_get_keywords' output, or have it print the same code for both of
these new categories, neither of which seems exactly ideal.

Another question is whether we'd risk back-patching such a change.
It'd be relatively self-contained in terms of our own code, but I'm
wondering whether there's any third-party code with dependencies
on what keyword categories exist.

Thoughts?

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

Re: [HACKERS] [PROPOSAL] Backup and recovery of pg_statistic

2015-12-31 Thread Bruce Momjian 
On Thu, Dec 24, 2015 at 05:28:11PM +0300, Dmitry Ivanov wrote:
> Suppose you want to upgrade from 9.4 to 9.6. In that case you would use the 
> pg_upgrade utility provided by the release 9.6, which means that it's the 
> pg_dump who would have to connect to the older instance and to prepare tuples 
> to be inserted to the pg_statistic of the newer instance. The pg_dump utility 
> would have to convert statistical data to the new format (for example, add 
> placeholders for new columns), so generated INSERT statements would be fine 
> provided that the pg_dump would be up-to-date.
> 
> The documentation states that we should always run the pg_upgrade binary of 
> the new server, not the old one [http://www.postgresql.org/docs/9.5/static/
> pgupgrade.html, Usage, #9]. This means that the pg_upgrade will definitely 
> use 
> a fresh version of pg_dump utility that is aware of all possible pitfalls.
> 
> Furthermore, each INSERT statement consists of textually-serialized columns 
> of 
> pg_statistic. Columns of 'anyarray' type are deserialized using the 
> 'array_in' 
> procedure which performs various sanity checks, including the element type 
> check. Thus it is not possible to insert an anyarray object which will cause 
> server death.

My idea was to do the insert via a function, with a version number at
the head:

SELECT pg_stats_insert('1.0', '{row value}');

When the pg_stats format is changed, the version number is bumped.  The
backend would know the pg_stats version it supports and either remap or
ignore pg_stats_insert() calls for older versions.

To get more complicated, you could have a version number for data types
too and just invalidate inserts for data type format changes, rather
than requiring the entire pg_stats version to be bumped.  I am not sure
how we would consistently record the data type name.  pg_upgrade
preserves pg_type.oid, so that would work for it, but pg_type.oid is not
preserved for non-pg_upgrade usage of non-builtin data types.  For those
cases, I guess the type name would be sufficient.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


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

Re: [HACKERS] New ALTER OPERATOR command fails to update dependencies

2015-12-31 Thread Tom Lane 
Alvaro Herrera  writes:
> Tom Lane wrote:
>> While this isn't exactly a stop-ship class of problem, it still seems like
>> it'd be good to fix before 9.5.0.

> I wonder if a note to packagers to inform them of the new file added
> after the RC would be a good idea.

I doubt they care; I've not seen any packaging recipes that try to
enumerate every single .h file.  The maintenance would be too painful.

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

Re: [HACKERS] PATCH: postpone building buckets to the end of Hash (in HashJoin)

2015-12-31 Thread Tomas Vondra 

Hi,

attached is v2 of the patch, with a bugfix and two significant improvements:

1) bugfix: forgotten memset() in ExecHashIncreaseNumBatches()

   Caused segfaults whenever we started with a single batch and then
   had to increase the number of batches.

2) 0002: postpone the batching (not just build of buckets)

   When ExecChooseHashTableSize finds out we'll probably need batching,
   we start with nbatch=1 anyway, and only switch to batching (with the
   estimated number of batches) after filling work_mem.

   This helps in two basic cases - firstly, when we do over-estimates
   we may end up doing batching even when we could do with a single
   batch (i.e. without writing to temp files at all). That's really
   expensive, and this helps with that - not entirely, because we can
   only distinguish "no batching vs. batching" case and not the number
   of batches, but that's the more interesting case anyway (the
   difference between batching with 16 or 32 batches is not large, at
   least in my experience).

   The other use case is the patch adding bloom filters, because it
   allows with properly sizing the bloom filter, which needs the number
   of distinct values. So while the filter might be sized using the
   estimated number of tuples passed to the Hash, it's likely to be
   larger than needed and thus more expensive. So sizing the bloom
   filter is crucial, and this change allows first accumulating enough
   data to estimate the size of bloom filter first. More discussion in
   the other patch.

3) 0003: eliminate the MaxAlloc limit

   Currently the size of buckets is limited my MaxAlloc, which means it
   can't exceed 512MB (assuming I've done my math correctly), which
   means ~67M buckets. This removes the MaxAlloc limit and just keeps
   the (INT_MAX/2) limit, so ~2B rows. I don't think it's worth
   extending further at this point.

   There was a discussion about this, quoting the f2fc98fb message:

  Limit the size of the hashtable pointer array to not more than
  MaxAllocSize, per reports from Kouhei Kaigai and others of
  "invalid memory alloc request size" failures.  There was
  discussion of allowing the array to get larger than that by using
  the "huge" palloc API, but so far no proof that that is actually
  a good idea, and at this point in the 9.5 cycle major changes
  from old behavior don't seem like the way to go.

   I believe the objections were correct, because it'd really waste a
   lot of memory in case of over-estimates. I do however think that
   doing (1) and (2) fixes this issue, because with those changes we
   never allocate the buckets based on the initial estimate. We pretty
   much get the exact number of buckets necessary.

I haven't done any performance testing yet (well, I did, but not 
reliable enough for sharing here). I'll post more data early January, 
once the machine completes other tests it's running right now.


I've also been thinking about what other optimizations might be 
possible, and the one thing I'm wondering about is adding HyperLogLog 
counter. The thing is that we do size buckets based on number of rows, 
but that may be nonsense - to size the hash table, we actually need 
number of distinct values. So when the Hash contains duplicate rows (say 
10 rows for each value), we end up with only ~10% of buckets containing 
any data (about 10 tuples in a list).


If we knew the number of distinct values, we could make the buckets 
smaller and thus reduce the memory requirements significantly.


I mention this because the patch with bloom filters actually uses HLL to 
size the bloom filters, so this would not be really introducing any new 
code.



regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 3aaf27f722e6c90702af8f51fd11a95b8c173c38 Mon Sep 17 00:00:00 2001
From: Tomas Vondra 
Date: Sun, 27 Dec 2015 18:25:51 +0100
Subject: [PATCH 1/5] delayed build of hash buckets v2

Removes forgotten memset in ExecHashIncreaseNumBatches() causing
crashes when we start without batching and find out we need to
do batching later.
---
 src/backend/commands/explain.c  |  8 +---
 src/backend/executor/nodeHash.c | 94 +
 src/include/executor/hashjoin.h |  5 ---
 3 files changed, 32 insertions(+), 75 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 12dae77..8fd9c9f 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -2116,21 +2116,17 @@ show_hash_info(HashState *hashstate, ExplainState *es)
 		if (es->format != EXPLAIN_FORMAT_TEXT)
 		{
 			ExplainPropertyLong("Hash Buckets", hashtable->nbuckets, es);
-			ExplainPropertyLong("Original Hash Buckets",
-hashtable->nbuckets_original, es);
 			ExplainPropertyLong("Hash Batches", hashtable->nbatch, es);
 			ExplainPropertyLong("Original Hash Batches",

Re: [HACKERS] New ALTER OPERATOR command fails to update dependencies

2015-12-31 Thread Alvaro Herrera 
Tom Lane wrote:
> I noticed $subject while chasing a different issue.
> 
> The way to fix this is to export pg_operator.c's makeOperatorDependencies
> function so that AlterOperator can call it.  But since that declaration
> uses HeapTuple, I doubt we want to put it into pg_operator.h (which'd
> require further growth in that file's include list, and possibly break
> client-side code that wants to #include it).  I'm inclined to split out
> pg_operator.h's function declarations into a new header pg_operator_fn.h,
> as we've already done in related cases.

Hmm, yeah, pg_operator.h defines a lot of operator OIDs.  I think
including htup.h wouldn't be terribly problematic nowadays, actually.
But I can get behind a plan to move CreateOperator to the new file as
well, and get rid of objectaddress.h and pglist.h from pg_operator.h.

> While this isn't exactly a stop-ship class of problem, it still seems like
> it'd be good to fix before 9.5.0.

I wonder if a note to packagers to inform them of the new file added
after the RC would be a good idea.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] dynloader.h missing in prebuilt package for Windows?

2015-12-31 Thread Bruce Momjian 
On Thu, Dec 31, 2015 at 12:50:13AM -0500, Bruce Momjian wrote:
> On Wed, Dec 30, 2015 at 11:57:45PM -0500, Tom Lane wrote:
> > Bruce Momjian  writes:
> > > Oops.  Once this patch is applied, it is only going to take effect when
> > > someone _installs_ Postgres.  Even an initdb will not add the file. 
> > > This means that upgrading to the next minor release will _not_ fix this.
> > 
> > Uh, what?  Surely an upgrade to a new package *would* fix it, because
> > that is a reinstall at the filesystem level.  This patch has nothing
> > to do with system catalog contents, which is what initdb would be
> > concerned with.
> 
> Uh, do we install hew lib files and stuff in a minor upgrade?  I guess
> we do, yeah.

Let's hold this for 9.5.1 and all minor releases will get it at the same
time.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


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

Re: [HACKERS] Better detail logging for password auth failures

2015-12-31 Thread Edson - Amplosoft Software 


Em 31 de dezembro de 2015 04:56:55 BRST, Noah Misch  
escreveu:
>On Wed, Dec 30, 2015 at 10:18:35AM -0500, Tom Lane wrote:
>> Andres Freund  writes:
>> > On 2015-12-29 11:07:26 -0500, Tom Lane wrote:
>> >> In passing, the patch gets rid of a vestigial
>CHECK_FOR_INTERRUPTS()
>> >> call; it was added by e710b65c and IMO should have been removed
>again
>> >> by 6647248e.  There's certainly no very good reason to have one
>right
>> >> at that spot anymore.
>> 
>> > Why? Doesn't seem like the worst place for an explicit interrupt
>check?
>> > I think we don't really have a problem with too many such checks...
>We
>> > surely could move it, but I don't really see how it's related to
>the
>> > topic at hand nor do I think it's really worth pondering about
>> > extensively.
>
>Agreed.
>
>> The only reason there was one there at all was that e710b65c added
>> code like this:
>> 
>> +   /*
>> +* Disable immediate interrupts while doing database access. 
>(Note
>> +* we don't bother to turn this back on if we hit one of the
>failure
>> +* conditions, since we can expect we'll just exit right away
>anyway.)
>> +*/
>> +   ImmediateInterruptOK = false;
>> 
>> ... some catalog access here ...
>> 
>> +   /* Re-enable immediate response to SIGTERM/SIGINT/timeout
>interrupts */
>> +   ImmediateInterruptOK = true;
>> +   /* And don't forget to detect one that already arrived */
>> +   CHECK_FOR_INTERRUPTS();
>> 
>> In 6647248e you got rid of nine of these ten lines, leaving something
>> that's both pointless and undocumented.  There are more than enough
>> CHECK_FOR_INTERRUPTS calls already in the auth code; there's not a
>> reason to expend code space on one here.  (If MD5 ran long enough to
>> be worth interrupting, there would be an argument for a check inside
>> its hashing loop, but that still wouldn't be this check.)
>
>I see no general benefit from being parsimonious with
>CHECK_FOR_INTERRUPTS
>calls or documenting them.  As you explain, it's probably fine to
>remove the
>two calls that commit e710b65 had added.  However, the sole connection
>to
>$SUBJECT is one of those two calls sharing a screenful with lines
>$SUBJECT
>changed.  The removal, if worthwhile, is worth a freestanding patch.
>Squashing the changes makes both topics harder to review.
>
>nm
>
>
>-- 
>Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-hackers

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

[HACKERS] New ALTER OPERATOR command fails to update dependencies

2015-12-31 Thread Tom Lane 
I noticed $subject while chasing a different issue.

The way to fix this is to export pg_operator.c's makeOperatorDependencies
function so that AlterOperator can call it.  But since that declaration
uses HeapTuple, I doubt we want to put it into pg_operator.h (which'd
require further growth in that file's include list, and possibly break
client-side code that wants to #include it).  I'm inclined to split out
pg_operator.h's function declarations into a new header pg_operator_fn.h,
as we've already done in related cases.

While this isn't exactly a stop-ship class of problem, it still seems like
it'd be good to fix before 9.5.0.

Any objections?

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

Re: [HACKERS] WIP: bloom filter in Hash Joins with batches

2015-12-31 Thread Tomas Vondra 

Hi,

attached is v2 of the patch, with a number of improvements:

0) This relies on the the other hashjoin patches (delayed build of
   buckets and batching), as it allows sizing the bloom filter.

1) enable_hashjoin_bloom GUC

   This is mostly meant for debugging and testing, not for committing.

2) Outer joins should be working fine now. That is, the results should
   be correct and faster as the outer rows without matches should not
   be batched at all.

3) The bloom filter is now built for all hash joins, not just when
   batching is happening. I've been a bit skeptical about the
   non-batched cases, but it seems that I can get a sizable speedup
   (~20-30%, depending on the selectivity of the join).

4) The code is refactored quite a bit, adding BloomFilterData instead
   of just sprinkling the fields on HashJoinState or HashJoinTableData.

5) To size the bloom filter, we now use HyperLogLog couter, which we
   now have in core thanks to the sorting improvements done by Peter
   Geoghegan. This allows making the bloom filter much smaller when
   possible.

   The patch also extends the HyperLogLog API a bit (which I'll submit
   to the CF independently).


There's a bunch of comments in the code, mostly with ideas about more 
possible improvements.


The main piece missing in the patch (IMHO) is optimizer code making 
decisions whether to enable bloom filters for the hash join, based on 
cardinality estimates. And also the executor code disabling the bloom 
filter if they turn inefficient. I don't think that's a major issue at 
this point, though, and I think it'll be easier to do based on testing 
the current patch.


regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 8f207693faa65e65e8a1e5e894c2ad96ad1f3cea Mon Sep 17 00:00:00 2001
From: Tomas Vondra 
Date: Mon, 28 Dec 2015 14:20:17 +0100
Subject: [PATCH 1/2] extend the HyperLogLog API a bit by adding two more
 methods

- initHyperLogLogError (initialize the counter for error rate)

- freeHyperLogLog (release the memory allocated for counter state)
---
 src/backend/lib/hyperloglog.c | 29 +
 src/include/lib/hyperloglog.h |  2 ++
 2 files changed, 31 insertions(+)

diff --git a/src/backend/lib/hyperloglog.c b/src/backend/lib/hyperloglog.c
index 718afb8..2949a8d 100644
--- a/src/backend/lib/hyperloglog.c
+++ b/src/backend/lib/hyperloglog.c
@@ -108,6 +108,35 @@ initHyperLogLog(hyperLogLogState *cState, uint8 bwidth)
 }
 
 /*
+ * Initialize HyperLogLog track state
+ */
+void
+initHyperLogLogError(hyperLogLogState *cState, double error)
+{
+	uint8 bwidth = 4;
+
+	while (bwidth < 16)
+	{
+		double m = (Size)1 << bwidth;
+		if (1.04 / sqrt(m) < error)
+			break;
+		bwidth++;
+	}
+
+	initHyperLogLog(cState, bwidth);
+}
+
+/*
+ * Free HyperLogLog track state
+ */
+void
+freeHyperLogLog(hyperLogLogState *cState)
+{
+	Assert(cState->hashesArr != NULL);
+	pfree(cState->hashesArr);
+}
+
+/*
  * Adds element to the estimator, from caller-supplied hash.
  *
  * It is critical that the hash value passed be an actual hash value, typically
diff --git a/src/include/lib/hyperloglog.h b/src/include/lib/hyperloglog.h
index fd8280c..004490a 100644
--- a/src/include/lib/hyperloglog.h
+++ b/src/include/lib/hyperloglog.h
@@ -60,8 +60,10 @@ typedef struct hyperLogLogState
 } hyperLogLogState;
 
 extern void initHyperLogLog(hyperLogLogState *cState, uint8 bwidth);
+extern void initHyperLogLogError(hyperLogLogState *cState, double error);
 extern void addHyperLogLog(hyperLogLogState *cState, uint32 hash);
 extern double estimateHyperLogLog(hyperLogLogState *cState);
 extern void mergeHyperLogLog(hyperLogLogState *cState, const hyperLogLogState *oState);
+extern void freeHyperLogLog(hyperLogLogState *cState);
 
 #endif   /* HYPERLOGLOG_H */
-- 
2.1.0

>From fbb2baf9b60d9f57182be3b58b3678cfe52b2931 Mon Sep 17 00:00:00 2001
From: Tomas Vondra 
Date: Sun, 27 Dec 2015 18:26:34 +0100
Subject: [PATCH 2/2] hash bloom filters v2

- extend bloom filters to unbatched case

- always start with nbatch=1 even when we assume we'll need to batch
  (helps with over-estimates and also sizing of the bloom filter)

- use HyperLogLog to count distinct values and size the bloom filter
  (we can do this thanks to always starting with nbatch=1)

- refactorings and cleanups
---
 src/backend/executor/nodeHash.c | 259 +
 src/backend/executor/nodeHashjoin.c |  24 ++-
 src/backend/utils/adt/Makefile  |   2 +-
 src/backend/utils/adt/murmur3.c | 315 
 src/backend/utils/misc/guc.c|   9 ++
 src/include/executor/hashjoin.h |  18 +++
 src/include/optimizer/cost.h|   1 +
 src/include/utils/murmur3.h |  29 
 8 files changed, 655 insertions(+), 2 deletions(-)
 create mode 100644 src/backend/utils/adt/murmur3.c
 create mode 100644 src/include/utils/murmur3.h

diff

[HACKERS] IMPORT FOREIGN SCHEMA return create foreign table commands are those further filtered in LIMIT and EXCEPT cases?

2015-12-31 Thread Paragon Corporation 
I've implemented IMPORT FOREIGN SCHEMA support for an fdw called ogr_fdw
against the PostgreSQL 9.5RC1 code base.

Code is here:  https://github.com/robe2/pgsql-ogr-fdw

It works great except in the Case of LIMIT TO  clause  (and possible EXCEPT
though I have to retest EXCEPT to see if there is a case it doesn't work).  
In LIMIT case sometimes works and it sometimes doesn't and when it doesn't
no foreign tables are created.

Key bit starts here:
https://github.com/robe2/pgsql-ogr-fdw/blob/master/ogr_fdw.c#L1389

When it doesn't work, no foreign tables are created, however when I inspect
the out list coming from the function, I see the create foreign table
statements 
And the right number as well but for some reason they never get executed.

I thought perhaps that I have a memory leak somewhere, but then I discovered
that if I take out my logic for LIMIT  and EXCEPT handling,
It still doesn't work.  In the sense that I have a ton of create foreign
table statements but none of them ever get run.

-- ogr_all is a catchall schema just to say - don't bother checking schema.

So I'm guessing there is some other filtering going on that I'm not aware
of.

Here is basic logic I have to exercise this behavior.
I should note: the two laundering options I have in place are used to
preserve or not preserve the table name and column names
When not specified, the import tries to change the table and column names so
that they don't require quoting when used in SQL.

DROP SERVER IF EXISTS northwind  CASCADE;

CREATE SERVER northwind
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
datasource 'C:/fdw_data/northwind.mdb',
format 'ODBC' );

CREATE SCHEMA IF NOT EXISTS northwind;

-- doesn't work - no tables created, but I see the two create table
statements coming out of my import function
IMPORT FOREIGN SCHEMA ogr_all 
LIMIT TO("Orders", "Products")   
FROM SERVER northwind INTO northwind
OPTIONS(launder_table_names 'false', launder_column_names 'false');

-- works and I get the 2 tables created
IMPORT FOREIGN SCHEMA ogr_all 
LIMIT TO("Orders", "Products")   
FROM SERVER northwind INTO northwind;


If I use the EXCEPT clause, it works in both these cases and produces 24
tables (with these two missing) as expected.

I tried exercising the same kind of issue with the postgres_fdw wrapper, but
was not successful replicating the issue with that one.


Thanks,
Regina Obe










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

Re: [HACKERS] IMPORT FOREIGN SCHEMA return create foreign table commands are those further filtered in LIMIT and EXCEPT cases?

2015-12-31 Thread Tom Lane 
"Paragon Corporation"  writes:
> I've implemented IMPORT FOREIGN SCHEMA support for an fdw called ogr_fdw
> against the PostgreSQL 9.5RC1 code base.
> Code is here:  https://github.com/robe2/pgsql-ogr-fdw
> It works great except in the Case of LIMIT TO  clause  (and possible EXCEPT
> though I have to retest EXCEPT to see if there is a case it doesn't work).  
> In LIMIT case sometimes works and it sometimes doesn't and when it doesn't
> no foreign tables are created.

Case-folding issue, perhaps?  Are you taking care to double-quote the
table names in the generated CREATE FOREIGN TABLE statements?  Because
if you don't, they'll be smashed to lower case and then won't match the
quoted table names in your example.

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] PATCH: Extending the HyperLogLog API a bit

2015-12-31 Thread Tomas Vondra 

Hi,

while working on the bloom filters for hashjoins, I've started using the 
HLL library committed as part of the sorting improvements for 9.5. I 
propose adding two more functions to the API, which I think are quite 
useful:


1) initHyperLogLogError(hyperLogLogState *cState, double error)

   Instead of specifying bwidth (essentially the number of bits used
   for addressing in the counter), this allows specifying the expected
   error rate for the counter, which is

  error_rate = 1.04 / sqrt(2^bwidth)

   So for 5% we get bwidth=5, and so on. This makes the API a bit easier
   the use, because there are pretty much no comments about the meaning
   of bwidth, and the existing callers simply use 10 without details.

2) freeHyperLogLog(hyperLogLogState *cState)

   I think it's a good idea to provide function "undoing" what init
   does, i.e. freeing the internal memory etc. Currently that's trivial
   to do, but perhaps we'll make the structure more complicated in the
   future (albeit that might be unlikely).

FWIW I've already used this in the patch marrying hash joins and bloom 
filters.


regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent 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: Extending the HyperLogLog API a bit

2015-12-31 Thread Tomas Vondra 

Meh, of course I forgot to actually attach the patch.

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 8f207693faa65e65e8a1e5e894c2ad96ad1f3cea Mon Sep 17 00:00:00 2001
From: Tomas Vondra 
Date: Mon, 28 Dec 2015 14:20:17 +0100
Subject: [PATCH 1/2] extend the HyperLogLog API a bit by adding two more
 methods

- initHyperLogLogError (initialize the counter for error rate)

- freeHyperLogLog (release the memory allocated for counter state)
---
 src/backend/lib/hyperloglog.c | 29 +
 src/include/lib/hyperloglog.h |  2 ++
 2 files changed, 31 insertions(+)

diff --git a/src/backend/lib/hyperloglog.c b/src/backend/lib/hyperloglog.c
index 718afb8..2949a8d 100644
--- a/src/backend/lib/hyperloglog.c
+++ b/src/backend/lib/hyperloglog.c
@@ -108,6 +108,35 @@ initHyperLogLog(hyperLogLogState *cState, uint8 bwidth)
 }
 
 /*
+ * Initialize HyperLogLog track state
+ */
+void
+initHyperLogLogError(hyperLogLogState *cState, double error)
+{
+	uint8 bwidth = 4;
+
+	while (bwidth < 16)
+	{
+		double m = (Size)1 << bwidth;
+		if (1.04 / sqrt(m) < error)
+			break;
+		bwidth++;
+	}
+
+	initHyperLogLog(cState, bwidth);
+}
+
+/*
+ * Free HyperLogLog track state
+ */
+void
+freeHyperLogLog(hyperLogLogState *cState)
+{
+	Assert(cState->hashesArr != NULL);
+	pfree(cState->hashesArr);
+}
+
+/*
  * Adds element to the estimator, from caller-supplied hash.
  *
  * It is critical that the hash value passed be an actual hash value, typically
diff --git a/src/include/lib/hyperloglog.h b/src/include/lib/hyperloglog.h
index fd8280c..004490a 100644
--- a/src/include/lib/hyperloglog.h
+++ b/src/include/lib/hyperloglog.h
@@ -60,8 +60,10 @@ typedef struct hyperLogLogState
 } hyperLogLogState;
 
 extern void initHyperLogLog(hyperLogLogState *cState, uint8 bwidth);
+extern void initHyperLogLogError(hyperLogLogState *cState, double error);
 extern void addHyperLogLog(hyperLogLogState *cState, uint32 hash);
 extern double estimateHyperLogLog(hyperLogLogState *cState);
 extern void mergeHyperLogLog(hyperLogLogState *cState, const hyperLogLogState *oState);
+extern void freeHyperLogLog(hyperLogLogState *cState);
 
 #endif   /* HYPERLOGLOG_H */
-- 
2.1.0


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

Re: [HACKERS] pgsql: Further tweaking of print_aligned_vertical().

2015-12-31 Thread Bruce Momjian 
On Wed, Dec  2, 2015 at 01:16:09AM +, Greg Stark wrote:
> 
> On 1 Dec 2015 19:48, "Tom Lane"  wrote:
> >
> > In passing, avoid possible calculation of log10(0).  Probably that's
> > harmless, given the lack of field complaints, but it seems risky:
> > conversion of NaN to an integer isn't well defined.
> 
> Am I going to have to fire up the emulator again?

Greg's lightning talk in Vienna about how he got the emulator working
was priceless.  I know he posted the VAX results, but how he got them
was amazing.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


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

Re: CurrentExtensionObject was Re: [HACKERS] dynloader.h missing in prebuilt package for Windows?

2015-12-31 Thread Bruce Momjian 
On Thu, Dec 31, 2015 at 04:41:44PM -0500, Chapman Flack wrote:
> I suppose there really won't be a way to do this with reliability
> unless someday extensions can hook the dependency infrastructure,
> as you mentioned in passing in an earlier message.
> 
> That sounds like a longer discussion. But I wondered if it might
> not be too hard to put PGDLLIMPORT on CurrentExtensionObject as
> a stopgap.
> 
> ... though perhaps it doesn't matter that much, because I still
> have to write a circuitous workaround anyway, and keep it in the
> code until 9.1 through 9.4 all vanish from the earth.

I think we decided that only older _minor_ versions would need your
workaround, so anyone doing a minor upgrade after 9.5.1 and friends
would be fine.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


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

Re: CurrentExtensionObject was Re: [HACKERS] dynloader.h missing in prebuilt package for Windows?

2015-12-31 Thread Chapman Flack 
On 12/31/15 16:13, Tom Lane wrote:
>> I see that 9.5.0 already adds PGDLLIMPORT on the global variable
>> creating_extension, but CurrentExtensionObject on the very next
>> line of extension.h still doesn't have it.
> 
> Why would you need to access that?

This returns to the earlier question about extensions whose purpose
is to enable other extensions. I'm thinking a bit ahead. At the moment,
I am only working on nudging PL/Java itself into the extension framework,
so you can install *it* with CREATE EXTENSION. For that, I can get along
without the extension Oid.

Down the road, it would be quite natural for PL/Java users to develop
functionality in Java, package it in a jar file, and want to install that
using CREATE EXTENSION. So they'd distribute their foo.jar file with a
foo.control file (requires = 'pljava'), and a very short foo--1.0.0.sql file
  SELECT sqlj.install_jar('file:foo.jar', 'foo', true);
and most of the right stuff will automagically happen ... the
associated system objects (created by the deployment script inside
the jar, executed by install_jar) will be captured as extension
members. But the jar itself, stashed by install_jar into a PL/Java
managed table that can't participate in pg_depend, somehow still
needs to be associated with the extension too.

I suppose there really won't be a way to do this with reliability
unless someday extensions can hook the dependency infrastructure,
as you mentioned in passing in an earlier message.

That sounds like a longer discussion. But I wondered if it might
not be too hard to put PGDLLIMPORT on CurrentExtensionObject as
a stopgap.

... though perhaps it doesn't matter that much, because I still
have to write a circuitous workaround anyway, and keep it in the
code until 9.1 through 9.4 all vanish from the earth.

-Chap


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

[HACKERS] pglogical - logical replication contrib module

2015-12-31 Thread Petr Jelinek 

Hi,

I'd like to submit the replication solution which is based on the 
pglogical_output [1] module (which is obviously needed for this to compile).


The pglogical contrib module provides extension which does the 
master-slave logical replication based on the logical decoding.


The basic documentation is in README.md, I didn't bother making sgml 
docs yet since I expect that there will be ongoing changes happening and 
it's easier for me to update the markdown docs than sgml. I will do the 
conversion once we start approaching committable state.


What it implements
- logical replication
- partial replication (replication sets)
- multiple sources for single subscriber
- origin filtering (so that if replication is setup both ways, there is 
no cyclic replication)


It currently doesn't do multi-master or automatic DDL. I think DDL 
should be relatively easy if somebody finishes the deparse extension as 
the infrastructure for replicating arbitrary commands is present in this 
patch.


It's rather large patch so I will just go very briefly over high level 
overview of how it works, the details need to be discussed separately IMHO:

Catalogs:
 - node - stores information about "nodes" (postgresql databases)
 - node_interface - represents connection string(s) to nodes, we 
separate interfaces to different catalog mainly to allow for setups 
where different subscribers see different address of the provider server
 - local_node - stores exactly one tuple which points to the nodes 
catalog tuple that represents the local node of the current database
 - subscription - represents subscription between two nodes, it 
includes configuration of the subscription like replication set and 
origin filters


Upstream:
 - basically just implements the pglogical_output hooks according to 
the catalogs


Downstream:
 - several background workers
 - supervisor is worker which manages all the other workers
 - manager is per database worker which manages individual database 
(node) and its subscriptions
 - apply does the actual replication, one apply process is started per 
subscription, connects to the walsender on the other side and applies 
the changes received from there



[1] https://commitfest.postgresql.org/8/418/

--
 Petr Jelinek  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


0001-pglogical-v1.patch.gz
Description: application/gzip

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

Re: [HACKERS] tracking owner of extension-managed objects

2015-12-31 Thread Chapman Flack 
On 12/31/15 19:06, Jim Nasby wrote:
> Those columns are there to tell you what *kind* of objects are involved in
> the dependency. For example, a function will have two entries. classid will
> be 1255 (pg_proc) for both of them. refclassid will be 2612 (pg_language)
> for one and 2615 (pg_namespace) for the other.

Right, well, the classid for a function is 1255 because 1255 is the Oid
of a row in pg_class ... and that row happens to describe a relation
named pg_proc, which is the relation in which you find functions.

Now, if you go to that relation, and look for a row whose Oid is the
objid part of the address, that row is the specific function you're
looking for.

So, if I give you the object address (1255, 1397, 0), these are the two
steps you will use to learn what I'm talking about:


SELECT relname FROM pg_class WHERE oid = 1255;
-
 pg_proc


SELECT * FROM pg_proc WHERE oid = 1397;
-+-...-+-+-...
 abs | ... | int4abs | ...



Ok, now what if I give you the object address (17001, 17270, 0) ?
Of course, these oids aren't predefined so they won't be the same
from one database to the next. But in the test database I'm logged
in to right now, you can follow the very same two steps:


SELECT relname FROM pg_class WHERE oid = 17001;
   relname

 jar_repository


SELECT * FROM jar_repository WHERE oid = 17270;
 jarid | jarname  | jarowner |jarmanifest| ...
---+--+--+---+-...
 2 | examples | chap | Manifest-Versio...| ...


Nothing new has happened here. The algorithm is unchanged.
The object address (17001, 17270, 0) means "the PL/Java examples jar",
in exactly the same way that (1255, 1397, 0) means "the int4abs
function".

(I had to cheat a little and ALTER TABLE jar_repository SET WITH OIDS
because in stock PL/Java it doesn't have them, but that was easy enough.)

The only thing that stops me at present from passing
an ObjectAddress like (17001, 17270, 0) to recordDependencyOn(...)
is that the *documentation* says the classid can't be the Oid
of just *any* old row in pg_class, is has to be the oid of a row
in pg_class *that describes a system catalog*.

As far as the actual code, if I tried that right now I don't actually
think anything would stop me from recording the dependency.
Things would break when I tried to drop something though, because
getObjectClass() would be called on my dependency, not recognize that
Oid among the ones it knows about, and throw an error.

How about having a default case there, saying "hmm, jar_repository,
not a table I recognize, it must belong to some extension. Well, I'll
just go and delete its row with oid = 17270 anyway, and let the extension
that owns it handle the DELETE trigger if any, and we're good to go." ?

To a first approximation, it's as easy as that.  All the rest of my
earlier message was about proposing rules to enforce reasonable
restrictions so the dependency jazz isn't opened up to every old user
table with triggers calling arbitrary code to get all tangled up in
PostgreSQL's DROP internals, but it is opened up in a controlled way
to extensions that create new classes of things to be managed.

-Chap


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

CurrentExtensionObject was Re: [HACKERS] dynloader.h missing in prebuilt package for Windows?

2015-12-31 Thread Chapman Flack 
While on the subject of things that could make it or not into 9.5.?,
I see that 9.5.0 already adds PGDLLIMPORT on the global variable
creating_extension, but CurrentExtensionObject on the very next
line of extension.h still doesn't have it.

The simplest way I've come up with in Windows to identify the extension
being created is to create some temporary object, call
getExtensionOfObject() on it, then drop it. A bit circuitous when on
any other platform I can just look at CurrentExtensionObject

-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] IMPORT FOREIGN SCHEMA return create foreign table commands are those further filtered in LIMIT and EXCEPT cases?

2015-12-31 Thread Tom Lane 
"Paragon Corporation"  writes:
> So I am guessing you guys are filtering the list so that it works for all
> FDWs even if the FDW author doesn't directly handle LIMIT / EXCEPT clause?

Yes.  The FDW author is not expected to implement those clauses; where
do you see documentation saying otherwise?

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

Re: [HACKERS] IMPORT FOREIGN SCHEMA return create foreign table commands are those further filtered in LIMIT and EXCEPT cases?

2015-12-31 Thread Paragon Corporation 
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us] 
Sent: Thursday, December 31, 2015 3:36 PM
To: Paragon Corporation 
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] IMPORT FOREIGN SCHEMA return create foreign table
commands are those further filtered in LIMIT and EXCEPT cases?

"Paragon Corporation"  writes:
>> I've implemented IMPORT FOREIGN SCHEMA support for an fdw called 
>>  ogr_fdw against the PostgreSQL 9.5RC1 code base.
>> Code is here:  https://github.com/robe2/pgsql-ogr-fdw
>> It works great except in the Case of LIMIT TO  clause  (and possible 
>> EXCEPT though I have to retest EXCEPT to see if there is a case it
doesn't work).
>> In LIMIT case sometimes works and it sometimes doesn't and when it 
>> doesn't no foreign tables are created.

> Case-folding issue, perhaps?  Are you taking care to double-quote the
table names in the generated CREATE FOREIGN TABLE statements?  Because if
you don't, they'll be smashed to lower case and then won't match the quoted
table names in your > example.

>   regards, tom lane


Tom,

Yes using quote identifier etc. 

I just rewrote my logic to go by the resulting table name instead of the
remote data source name and that seems to do the trick.

So I now write:

IMPORT FOREIGN SCHEMA ogr_all 
LIMIT TO(orders, products)
FROM SERVER northwind INTO northwind;

But with the laundering options off, I would do this:

IMPORT FOREIGN SCHEMA ogr_all 
LIMIT TO("Orders", "Products")
FROM SERVER northwind INTO northwind  OPTIONS(launder_table_names
'false', launder_column_names 'false') ;

So I am guessing you guys are filtering the list so that it works for all
FDWs even if the FDW author doesn't directly handle LIMIT / EXCEPT clause?
But not sure where that is being done.

So instead of doing this check in my code (which is what I was doing)

Relname = layer_name(where in my case layer_name is the remote
datasource which can be pretty crazy for spatial data like web service
layers )

I'm doing this

Relname = table_name 

In the case where laundering is on which is the default  table_name !=
layer_name.  In case where user chooses to preserve layer name then
table_name == layer_name

Thanks,
Regina




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

Re: CurrentExtensionObject was Re: [HACKERS] dynloader.h missing in prebuilt package for Windows?

2015-12-31 Thread Tom Lane 
Chapman Flack  writes:
> I see that 9.5.0 already adds PGDLLIMPORT on the global variable
> creating_extension, but CurrentExtensionObject on the very next
> line of extension.h still doesn't have it.

Why would you need to access that?

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

Re: [HACKERS] IMPORT FOREIGN SCHEMA return create foreign table commands are those further filtered in LIMIT and EXCEPT cases?

2015-12-31 Thread Paragon Corporation 

"Paragon Corporation"  writes:
>> So I am guessing you guys are filtering the list so that it works for 
>> all FDWs even if the FDW author doesn't directly handle LIMIT / EXCEPT
clause?

> Yes.  The FDW author is not expected to implement those clauses; where do
you see documentation saying otherwise?

> regards, tom lane

I didn't. I was copying the postgres_fdw and noticed it implemented them
directly.

Is there an issue with handling it ahead of time?  In the case of ogr_fdw,
there is an extra call after getting list of tables to get the data
structure for a specific table,
So I thought implementing it directly would save some cycles, especially
since some data sources require inspection of the data to infer the
structure so could be very expensive.


Thanks,
Regina






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

Re: [HACKERS] New ALTER OPERATOR command fails to update dependencies

2015-12-31 Thread Tom Lane 
Alvaro Herrera  writes:
> Tom Lane wrote:
>> While this isn't exactly a stop-ship class of problem, it still seems like
>> it'd be good to fix before 9.5.0.

> I wonder if a note to packagers to inform them of the new file added
> after the RC would be a good idea.

On closer look, the busted ALTER OPERATOR functionality is only in HEAD
anyway.

But having said that, I wonder whether we ought not backpatch the header
file splitup anyhow.  I observe that pg_operator.h's inclusion of
objectaddress.h is new in 9.5, and it sure looks to me like
objectaddress.h would be problematic for clients to include.  So if
we don't do this, we might well get reports of client build failures.

(pg_operator.h is not the only catalog header that includes
objectaddress.h now, but it's the only one that does that and
seems like a plausible file for clients to include.)

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

Re: [HACKERS] IMPORT FOREIGN SCHEMA return create foreign table commands are those further filtered in LIMIT and EXCEPT cases?

2015-12-31 Thread Tom Lane 
"Paragon Corporation"  writes:
>> Yes.  The FDW author is not expected to implement those clauses; where do
>> you see documentation saying otherwise?

> I didn't. I was copying the postgres_fdw and noticed it implemented them
> directly.

Ah.  I believe that's mostly just an efficiency hack (and a fairly trivial
one too, at least in postgres_fdw's case).  I suppose there might be an
argument that if we don't exclude tables at this stage, we could get a
parse-time failure from features not supported on the local server, which
would be unexpected if the user had told us to skip such tables.  But that
seems a bit implausible.

I'll go add a comment about this though, to clarify matters for the
next person.

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

Re: [HACKERS] tracking owner of extension-managed objects

2015-12-31 Thread Jim Nasby 

On 12/31/15 3:49 PM, Chapman Flack wrote:

On 12/23/15 15:02, Jim Nasby wrote:

>BTW, I've been pondering a very similar problem to this. I'm working on a
>metacoding framework, and it's inevitable that at some point it will want to
>know what objects it's created
>
>I was planning on just making a best possible attempt and solving this in an
>extension via a combination of event triggers, reg* and other voodoo,

In the voodoo department, I guess PL/Java could accompany each loading of
a jar 'foo' with a 'CREATE TYPE sqlj.voodoo_foo AS ()' which is about as
lightweight as it gets, and an extra regtype column in the jar_repository
table could carry the Oid of that type.


Hmm, interesting idea.


How well will pg_dump/restore handle that voodoo? I suppose they'll
reliably recreate the types before loading the table with a regtype
column, and by typing the column as regtype, the dump will refer to
the type by name, and therefore work even if pg_dump is not given
the --oids option?


Yes, assuming the type hasn't been dropped. Otherwise regtype spits out 
the OID that used to be assigned, which AFAICT is completely useless for 
a non-system object.


I've thought about creating a datatype that worked the same as the reg* 
types, but also implemented a "foreign key" back to the relevant catalog 
so things couldn't just vanish on you. (Since you obviously can't do an 
FK to a catalog this would have to be simulated with event triggers).



On 12/21/15 12:46, Tom Lane wrote:

>(I guess there are problems with extensions trying to do such things at
>all, since we don't provide a way for extensions to hook into the DROP
>mechanisms.  Perhaps that should be fixed.)


Ok, how numerous would be the problems with this:


- The classid and refclassid columns (in both pg_shdepend and pg_depend)
   are currently Oid columns referencing pg_class.oid.  The catalog
   definition would not preclude putting the oid of a non-system table
   there.  The*documentation*  says it has to be the oid of a system
   catalog, and surely there is code that currently would be surprised by
   an entry that wasn't (for example, default: elog(ERROR, "unrecognized
   object class..." in getObjectClass).
   But even now, looking at recordDependencyOn or shdepAddDependency,
   I don't see that the code would prevent such an entry being added.


Either I'm not understanding what you're saying here, or you're confused 
about those two columns.


Those columns are there to tell you what *kind* of objects are involved 
in the dependency. For example, a function will have two entries. 
classid will be 1255 (pg_proc) for both of them. refclassid will be 2612 
(pg_language) for one and 2615 (pg_namespace) for the other.


Oh, were you thinking of having a new classid that represented files on 
disk?

--
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] tracking owner of extension-managed objects

2015-12-31 Thread Chapman Flack 
On 12/23/15 15:02, Jim Nasby wrote:
> BTW, I've been pondering a very similar problem to this. I'm working on a
> metacoding framework, and it's inevitable that at some point it will want to
> know what objects it's created
> 
> I was planning on just making a best possible attempt and solving this in an
> extension via a combination of event triggers, reg* and other voodoo,

In the voodoo department, I guess PL/Java could accompany each loading of
a jar 'foo' with a 'CREATE TYPE sqlj.voodoo_foo AS ()' which is about as
lightweight as it gets, and an extra regtype column in the jar_repository
table could carry the Oid of that type. The type would be in pg_shdepend for
the owner, and its ACL could even be used to implement the SQL/JRT
requirement for jars to have an ACL (and be automatically represented
in pg_shdepend for any roles mentioned in the ACL). The type would also
end up in pg_depend for the owning extension, if the jar was installed
by an extension script. And (in 9.3+ anyway), I could have an sql_drop
event trigger to detect when the type goes away for any reason, and
remove the corresponding jar_repository entry.

How well will pg_dump/restore handle that voodoo? I suppose they'll
reliably recreate the types before loading the table with a regtype
column, and by typing the column as regtype, the dump will refer to
the type by name, and therefore work even if pg_dump is not given
the --oids option?

But that's all voodoo. What if we wanted to not need voodoo?

On 12/21/15 12:46, Tom Lane wrote:
> (I guess there are problems with extensions trying to do such things at
> all, since we don't provide a way for extensions to hook into the DROP
> mechanisms.  Perhaps that should be fixed.)


Ok, how numerous would be the problems with this:


- The classid and refclassid columns (in both pg_shdepend and pg_depend)
  are currently Oid columns referencing pg_class.oid.  The catalog
  definition would not preclude putting the oid of a non-system table
  there.  The *documentation* says it has to be the oid of a system
  catalog, and surely there is code that currently would be surprised by
  an entry that wasn't (for example, default: elog(ERROR, "unrecognized
  object class..." in getObjectClass).
  But even now, looking at recordDependencyOn or shdepAddDependency,
  I don't see that the code would prevent such an entry being added.

- It still would make no sense as a refclassid in pg_shdepend. All three
  other cases (classid in pg_shdepend, classid or refclassid in pg_depend)
  can make sense in a world of extension-managed objects.

- So, what would be needed to make those 3 cases supportable?  For starters,
  how about a strict rule for *when* a non-system-catalog classid or
  refclassid can be allowed into either of those tables:

  IF an ObjectAddress.classId IS NOT a system catalog
(currently detectable by getObjectClass(classId) throwing an error),
  THEN:
  - it MUST be the Oid of an existing (non-system) relation
  - that relation MUST be WITH OIDS (so the ObjectAddress.objectId can
identify a row in it)
... alternatively, must have an int unique key, and the objSubId can
be what identifies the row
  - that relation MUST have a DELETE FOR EACH ROW trigger that calls
pg_extension_check_depend, a system-provided trigger function to enforce
reference integrity for any pg_depend/pg_shdepend mentions of the row
  - that relation MAY have another DELETE trigger that was allowed to be
created by the restrictions on triggers below.

- The pg_extension_check_depend trigger function has two duties:
  a. to ereport(ERROR) and prevent deletion in some circumstances
 (for example, when the row to be deleted is mentioned on the
 classid side of an 'e' dependency, and the extension isn't being
 dropped, or on the refclassid side of a normal dependency, and
 the dependent object isn't being dropped),
  b. in all other cases, to allow the deletion, while also removing
 associated pg_depend/pg_shdepend entries.
  That's why no non-system table is allowed to be mentioned in pg_depend
  or pg_shdepend unless it has a trigger that calls this function.

- CREATE TRIGGER refuses to create a trigger that calls
  pg_extension_check_depend UNLESS:
  - creating_extension is true, AND
  - the trigger is being created on a table belonging to the current
extension, AND
  - no other DELETE trigger exists on the table, unless the next rule
would allow it.

- CREATE TRIGGER refuses to create any other DELETE trigger on a table
  that has a pg_extension_check_depend DELETE trigger, UNLESS:
  - creating_extension is true, AND
  - the table and the trigger function both belong to the current extension.


With that set of rules, extensions (and only extensions) are able to
invent and manage new kinds of dependency-managed objects, representing
them as rows in a table with appropriate triggers. When doDeletion, for
example, encounters a pg_depend record with a

Re: [HACKERS] PATCH: Extending the HyperLogLog API a bit

2015-12-31 Thread Peter Geoghegan 
On Thu, Dec 31, 2015 at 12:48 PM, Tomas Vondra
 wrote:
> 1) initHyperLogLogError(hyperLogLogState *cState, double error)
>
>Instead of specifying bwidth (essentially the number of bits used
>for addressing in the counter), this allows specifying the expected
>error rate for the counter, which is
>
>   error_rate = 1.04 / sqrt(2^bwidth)
>
>So for 5% we get bwidth=5, and so on. This makes the API a bit easier
>the use, because there are pretty much no comments about the meaning
>of bwidth, and the existing callers simply use 10 without details.

Fair, but you didn't add any better comments!

> 2) freeHyperLogLog(hyperLogLogState *cState)
>
>I think it's a good idea to provide function "undoing" what init
>does, i.e. freeing the internal memory etc. Currently that's trivial
>to do, but perhaps we'll make the structure more complicated in the
>future (albeit that might be unlikely).

Seems reasonable.

-- 
Peter Geoghegan


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

[HACKERS] Proposal: Generic WAL logical messages

2015-12-31 Thread Petr Jelinek 

Hello,

I would like to introduce concept of generic WAL logical messages.

These messages just create WAL record with arbitrary data as specified 
by user. For standard WAL reply, these are basically noop, but in 
logical decoding they are be decoded and the special callback of the 
output plugin is be called for them.


These messages can be both transactional (decoded on commit) or 
non-transactional (decoded immediately). Each message has prefix to 
differentiate between individual plugins. The prefix has to be 
registered exactly once (in similar manner as security label providers) 
to avoid conflicts between plugins.


There are three main use-cases for these:
a) reliable communication between two nodes in multi-master setup - WAL 
already handles correctly the recovery etc so we don't have to invent 
new complex code to handle crashes in middle of operations


b) out of order messaging in logical replication - if you want to send 
something to other node immediately without having to wait for commit 
for example to acquire remote lock, this can be used for that


c) "queue tables" - combination of this feature (there is SQL interface) 
and before triggers can be used to create tables for which all inserts 
only go to the WAL so they can behave like queue without having to store 
the data in the table itself (kind of the opposite of unlogged tables)


Initial implementation of this proposal is attached.

--
 Petr Jelinek  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


WAL-Messages-2016-01-01.patch
Description: binary/octet-stream

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

[HACKERS] Copy-pasto in logical decoding docs

2015-12-31 Thread Petr Jelinek 

Hi,

I noticed that the filter callback is documented as 
LogicalDecodeChangeCB in the logical decoding docs. Here is one-line 
patch to fix it.


--
 Petr Jelinek  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


logical-decoding-filter-typo.patch
Description: binary/octet-stream

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

Re: [HACKERS] WIP: Rework access method interface

2015-12-31 Thread Petr Jelinek 

On 2015-12-24 14:57, Alexander Korotkov wrote:


What do you think about
​"​
System Administration Functions
​" chapter?
http://www.postgresql.org/docs/devel/static/functions-admin.html

Other than that I am happy with the patch.



Sounds good to me.

One last tiny gripe, I think the amroutine in RelationData should be 
named rd_amroutine for consistency.


--
 Petr Jelinek  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


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

[HACKERS] Welcome to 2016, time to run src/tools/copyright.pl

2015-12-31 Thread Michael Paquier 
Hi all,

Happy new year to all and best wishes!

I guess that the following command followed by a commit is the common
happy-new-year thing to do:
perl src/tools/copyright.pl
This would update the copyright headers to 2016 :)

Regards,
-- 
Michael


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