Re: [HACKERS] [COMMITTERS] pgsql: Use gender-neutral language in documentation

[Erik Rijkers]

On 2015-09-22 04:59, Peter Eisentraut wrote:

Use gender-neutral language in documentation

Based on patch by Thomas Munro , 
although

I rephrased most of the initial work.

Branch
--
master

Details
---
http://git.postgresql.org/pg/commitdiff/741ccd5015f82e31f80cdc5d2ae81263ea92d794




I think this compulsive 'he'-avoiding is making the text worse.


-  environment variable); any user can make such a change for his 
session.
+  environment variable); any user can make such a change for their 
session.


Yuck.  even worse:

-   might not be the same as the database user he needs to connect as.
+   might not be the same as the database user that is to be connect as.


It is not an improvement.  I would like to see this change rolled back.


thanks,

Erik Rijkers



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

Re: [HACKERS] Speed up Clog Access by increasing CLOG buffers

[Jesper Pedersen]

On 09/18/2015 11:11 PM, Amit Kapila wrote:

I have done various runs on an Intel Xeon 28C/56T w/ 256Gb mem and 2 x
RAID10 SSD (data + xlog) with Min(64,).



The benefit with this patch could be seen at somewhat higher
client-count as you can see in my initial mail, can you please
once try with client count > 64?



Client count were from 1 to 80.

I did do one run with Min(128,) like you, but didn't see any difference 
in the result compared to Min(64,), so focused instead in the 
sync_commit on/off testing case.


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

Re: [HACKERS] tsvector work with citext

[Teodor Sigaev]

Fixed (9acb9007de30b3daaa9efc16763c3bc6e3e0a92d), but didn't backpatch because 
it isn't a critical bug.

For those on older versions, whatтАЩs the simplest workaround?

FWIW, I thought this would be a reasonable thing to back-patch.
It's not as though contrib/citext hasn't been around for awhile.


I'd like this idea, but does it look like a new feature in previous releses?

--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/


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

Re: [HACKERS] row_security GUC, BYPASSRLS

[Stephen Frost]

* Noah Misch (n...@leadboat.com) wrote:
> Right now, if a BYPASSRLS user creates a SECURITY DEFINER function, any caller
> can change that function's behavior by toggling the GUC.  Users won't test
> accordingly; better to have just one success-case behavior.

I agree that's not good, though the function definer could set the
row_security GUC on the function, no?  Similar to how we encourage
setting of search_path, we should be encouraging a similar approach to
anything which might be security relevant.

> On Wed, Jul 29, 2015 at 09:09:27AM -0400, Stephen Frost wrote:
> > For superuser (the only similar precedent that we have, I believe), we
> > go based on the view owner, but that isn't quite the same as BYPASSRLS.
> > 
> > The reason this doesn't hold is that you have to use a combination of
> > BYPASSRLS and row_security=off to actually bypass RLS, unlike the
> > superuser role attribute which is just always "on" if you've got it.  If
> > having BYPASSRLS simply always meant "don't do any RLS" then we could
> > use the superuser precedent to use what the view owner has, but at least
> > for my part, I'm a lot happier with BYPASSRLS and row_security than with
> > superuser and would rather we continue in that direction, where the user
> > has the choice of if they want their role attribute to be in effect or
> > not.
> 
> If I make BYPASSRLS GUC-independent, I should then also make it take effect
> when the BYPASSRLS role owns a view.  Barring objections, I will change both.

I agree that if it's GUC-independent then it should operate the same as
superuser does for views and security definer functions.

On the one hand, I don't like that BYPASSRLS roles will now behave
differently from non-BYPASSRLS roles, but on the other hand, the above
isn't good and having BYPASSRLS always enabled may make individuals shy
away from giving it out except when strictly necessary and treat it more
similar to superuser, which would be a good thing.

> I do share your wish for an ability to suppress privileges temporarily.  I
> have no specific design in mind, but privilege activation and suppression
> should be subject to the approval of roles affected.  GUCs probably can't
> serve here; apart from the grandfathered search_path, functions can ignore
> them.  GUCs are mostly a property of the whole session.

Perhaps GUCs won't work, but they own a pretty handy namespace
(SET X = Y) and we are able to attach specific GUC settings to
functions already.  I don't like the idea that we'd invent a whole new
syntax or bits of grammar to do the same for whatever approach we come
up to for suppressing privileges temporarily (such as in SECURITY
DEFINER functions).  The odd case here is really views, since they
operate somewhere inbetween regular queries and security definer
functions, regarding permissions.

> By the way, is there a reason for RI_Initial_Check() to hard-code the rules
> for RLS enablement instead of calling check_enable_rls(..., InvalidOid, true)
> twice?  I refer to this code:

I don't see a reason for it now, though I recall one existing when the
code was originally written.  That might have simply been a bit of extra
(though unnecessary) paranoia though, as returning 'false' is a safe
route.

Are you planning to handle the ALTER TABLE .. FORCE ROW SECURITY (#3) as
well?  I have no complaints if so; just want to make sure we aren't
doing double-work during this crunch time and didn't see your name
listed next to it, but the nearby thread seemed to imply you were
looking at it.

One item which wasn't discussed, that I recall, is just how it will work
without SECURITY_ROW_LEVEL_DISABLED, or something similar, to
differentiate when internal referencial integrity queries are being run,
which should still bypass RLS (even in the FORCE ROW SECURITY case), and
when regular or SECURITY DEFINER originated queries are being run.

The concensus, as I understood it, was that removing the ability to do
SET ROW_SECURITY = force is good, but if done, we need to support
ALTER TABLE .. FORCE ROW SECURITY.  I'm trying to figure out if that
means we end up not actually addressing the original concern you raised
regarding SECURITY_ROW_LEVEL_DISABLED.

Thanks!

Stephen


signature.asc
Description: Digital signature

[HACKERS] planstate_tree_walker oversight CustomScan

[Kouhei Kaigai]

Hi,

The planstate_tree_walker() oversight custom_ps of CustomScanState;
that should be a list of underlying PlanState object if any.

ExplainPreScanNode() treated ForeignScan and CustomScan in special
way (it is sufficient for ExplainPreScanNode() purpose), thus, it
didn't implement its recursive portion originally.

The job of ExplainPreScanNode() is know all the relids involved
in a particular subquery execution. On the other hands, fs_relids
of ForeignScan and custom_relids of CustomScan informs a set of
relids to be scanned by this Scan node without recursive, so it
did not have recursive walks on the underlying sub-plans.

However, planstate_tree_walker() will have different expectation.
It is general walker routine, thus, it is natural users to expect
the callback is also kicked towards the underlying planstate of
CustomScan (and ForeignScan; once EPQ recheck gets solved).

The attached patch adds support of CustomScan on the walker.

Thanks,
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei 



pgsql-planstate_tree_walker-oversight-custom-scan.v1.patch
Description: pgsql-planstate_tree_walker-oversight-custom-scan.v1.patch

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

Re: [HACKERS] jsonb_set array append hack?

[Thom Brown]

On 21 September 2015 at 22:21, Andrew Dunstan  wrote:

>
>
> On 09/21/2015 12:13 PM, Dmitry Dolgov wrote:
>
>> > I would expect some kind of error.  We're trying to address a position
>> in an array, and we're instead passing a key.  If it completes
>> successfully, the chances are it isn't what the user intended.
>>
>> Thanks for the explanation. So, basically, it should be like this, am I
>> right?
>>
>> postgres=# SELECT jsonb_set(
>> '{"name": "Joe", "vehicle_types": ["car", "van"]}'::jsonb,
>> '{vehicle_types, nonsense}',
>> '"motorcycle"', true);
>> ERROR:  path element at the position 2 is not an integer
>>
>
>
> That seems reasonable. For that matter, we should probably disallow NULL
> path elements also, shouldn't we?
>

I'd say yes.  If someone really wants to name a field "null", they'll just
have to quote it in the path. (e.g. '{contact,"null"}')

-- 
Thom

Re: [HACKERS] Obsolete use of volatile in walsender.c, walreceiver.c, walreceiverfuncs.c?

[Thomas Munro]

On Tue, Sep 22, 2015 at 8:19 AM, Alvaro Herrera
 wrote:
> Thomas Munro wrote:
>
>> In walsender.c, walreceiver.c, walreceiverfuncs.c there are several
>> places where volatile qualifiers are used apparently only to prevent
>> reordering around spinlock operations.
>
> In replication/slot.c there are a number of places (12, I think) that
> introduce a block specifically to contain a volatile cast on a variable
> for spinlock-protected access.  We could remove the whole thing and save
> at least 3 lines and one indentation level for each of them.

Right, see attached.

-- 
Thomas Munro
http://www.enterprisedb.com


replication-strip-volatile-v2.patch
Description: Binary data

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

Re: [HACKERS] row_security GUC, BYPASSRLS

[Noah Misch]

On Mon, Sep 21, 2015 at 09:30:15AM -0400, Stephen Frost wrote:
> * Noah Misch (n...@leadboat.com) wrote:
> > Right now, if a BYPASSRLS user creates a SECURITY DEFINER function, any 
> > caller
> > can change that function's behavior by toggling the GUC.  Users won't test
> > accordingly; better to have just one success-case behavior.
> 
> I agree that's not good, though the function definer could set the
> row_security GUC on the function, no?  Similar to how we encourage
> setting of search_path, we should be encouraging a similar approach to
> anything which might be security relevant.

Functions can do that.  New features should not mimic search_path in their
demands on SECURITY DEFINER authors.

> Are you planning to handle the ALTER TABLE .. FORCE ROW SECURITY (#3) as
> well?  I have no complaints if so; just want to make sure we aren't
> doing double-work during this crunch time and didn't see your name
> listed next to it, but the nearby thread seemed to imply you were
> looking at it.

I'm not.

> One item which wasn't discussed, that I recall, is just how it will work
> without SECURITY_ROW_LEVEL_DISABLED, or something similar, to
> differentiate when internal referencial integrity queries are being run,
> which should still bypass RLS (even in the FORCE ROW SECURITY case), and
> when regular or SECURITY DEFINER originated queries are being run.

If the table owner enables FORCE ROW SECURITY, policies will affect
referential integrity queries.  Choose policies accordingly.  For example,
given only ON UPDATE NO ACTION constraints, it would be no problem to set
owner-affecting policies for INSERT, UPDATE and/or DELETE.


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

Re: [HACKERS] [COMMITTERS] pgsql: Use gender-neutral language in documentation

[Peter Geoghegan]

On Mon, Sep 21, 2015 at 9:32 PM, Erik Rijkers  wrote:
> I think this compulsive 'he'-avoiding is making the text worse.
>
>
> -  environment variable); any user can make such a change for his
> session.
> +  environment variable); any user can make such a change for their
> session.

-1. It seems fine to me.

> Yuck.  even worse:
>
> -   might not be the same as the database user he needs to connect as.
> +   might not be the same as the database user that is to be connect as.
>
>
> It is not an improvement.  I would like to see this change rolled back.

I think that this should be reworded, since there is a grammatical
error as things stand. I suggest the whole sentence be modified to
read:

When using an external authentication system such as Ident or GSSAPI,
the name of the operating system user that initiated the connection
might not be the same as the intended corresponding database user.

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

Re: [HACKERS] Rework the way multixact truncations work

[Andres Freund]

On 2015-09-21 10:31:17 -0400, Robert Haas wrote:
> On Sun, Jul 5, 2015 at 3:16 PM, Andres Freund  wrote:
> >>On the other hand, in the common case, by the time we perform a
> >>restartpoint, we're consistent: I think the main exception to that is
> >>if we do a base backup that spans multiple checkpoints.  I think that
> >>in the new location, the chances that the legacy truncation is trying
> >>to read inconsistent data is probably higher.
> >
> > The primary problem isn't that we truncate too early, it's that we delay 
> > truncation on the standby in comparison to the primary by a considerable 
> > amount. All the while continuing to replay multi creations.
> >
> > I don't see the difference wrt. consistency right now, but I don't have 
> > access to the code right now. I mean we *have* to do something while 
> > inconsistent. A start/stop backup can easily span a day or four.
> 
> So, where are we with this patch?

Uh. I'd basically been waiting on further review and then forgot about
it.


> In my opinion, we ought to do something about master and 9.5 before
> beta, so that we're doing *yet another* major release with unfixed
> multixact bugs.  Let's make the relevant truncation changes in master
> and 9.5 and bump the WAL page magic, so that a 9.5alpha standby can't
> be used with a 9.5beta master.  Then, we don't need any of this legacy
> truncation stuff at all, and 9.5 is hopefully in a much better state
> than 9.4 and 9.3.

Hm.

> Now, that still potentially leaves 9.4 and 9.3 users hanging out to
> dry.  But we don't have a tremendous number of those people clamoring
> about this, and if we get 9.5+ correct, then we can go and change the
> logic in 9.4 and 9.3 later when, and if, we are confident that's the
> right thing to do.  I am still not altogether convinced that it's a
> good idea, nor am I altogether convinced that this code is right.
> Perhaps it is, and if we consensus on it, fine.

To me the current logic is much worse than what's in the patch, so I
don't think that's the best way to go. But I'm not not absolutely gung
ho on that.

> But regardless of that, we should not send a third major release to
> beta with the current broken system unless there is really no viable
> alternative.

Agreed. I'll update the patch.

Greetings,

Andres Freund


-- 
Sent 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: multiple psql option -c

[Adam Brightwell]

Pavel,

> with -1 option support

FWIW, I have tried to apply this patch against master (7f11724) and
there is a minor error, see below.

>From patch:

patching file src/bin/psql/settings.h
Hunk #2 FAILED at 135.
1 out of 2 hunks FAILED -- saving rejects to file src/bin/psql/settings.h.rej

>From settings.h.rej:

--- src/bin/psql/settings.h
+++ src/bin/psql/settings.h
@@ -135,6 +141,7 @@
const char *prompt2;
const char *prompt3;
PGVerbosity verbosity;  /* current error verbosity level */
+   GroupCommand *group_commands;
 } PsqlSettings;

 extern PsqlSettings pset;

-Adam

-- 
Adam Brightwell - adam.brightw...@crunchydatasolutions.com
Database Engineer - www.crunchydatasolutions.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] Bug in numeric multiplication

[Tom Lane]

I wrote:
> Dean Rasheed  writes:
>> The problem then arises in the final carry propagation pass. During
>> this phase of the computation, the carry from one digit (which can be
>> a shade under INT_MAX / NBASE) is added to the next digit, and that's
>> where the overflow happens.

> Nice catch!  I think the comment could use a little more work, but I'll
> adjust it and push.

After trying to rework the comment to explain what maxdig really meant
after your changes, I came to the conclusion that it'd be better to do
it as per attached.  Does this look sane to you?

regards, tom lane

diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 1bfa29e..d403554 100644
*** a/src/backend/utils/adt/numeric.c
--- b/src/backend/utils/adt/numeric.c
*** mul_var(NumericVar *var1, NumericVar *va
*** 5789,5796 
  	 * to avoid normalizing carries immediately.
  	 *
  	 * maxdig tracks the maximum possible value of any dig[] entry; when this
! 	 * threatens to exceed INT_MAX, we take the time to propagate carries. To
! 	 * avoid overflow in maxdig itself, it actually represents the max
  	 * possible value divided by NBASE-1.
  	 */
  	dig = (int *) palloc0(res_ndigits * sizeof(int));
--- 5789,5801 
  	 * to avoid normalizing carries immediately.
  	 *
  	 * maxdig tracks the maximum possible value of any dig[] entry; when this
! 	 * threatens to exceed INT_MAX, we take the time to propagate carries.
! 	 * Furthermore, we need to ensure that overflow doesn't occur during the
! 	 * carry propagation pass below either.  The carry value could be as much
! 	 * as INT_MAX/NBASE, so really we should normalize when digits threaten to
! 	 * exceed INT_MAX - INT_MAX/NBASE.
! 	 *
! 	 * To avoid overflow in maxdig itself, it actually represents the max
  	 * possible value divided by NBASE-1.
  	 */
  	dig = (int *) palloc0(res_ndigits * sizeof(int));
*** mul_var(NumericVar *var1, NumericVar *va
*** 5806,5812 
  
  		/* Time to normalize? */
  		maxdig += var1digit;
! 		if (maxdig > INT_MAX / (NBASE - 1))
  		{
  			/* Yes, do it */
  			carry = 0;
--- 5811,5817 
  
  		/* Time to normalize? */
  		maxdig += var1digit;
! 		if (maxdig > (INT_MAX - INT_MAX / NBASE) / (NBASE - 1))
  		{
  			/* Yes, do it */
  			carry = 0;
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index e6ee548..c1886fd 100644
*** a/src/test/regress/expected/numeric.out
--- b/src/test/regress/expected/numeric.out
*** SELECT * FROM num_input_test;
*** 1334,1339 
--- 1334,1366 
  (7 rows)
  
  --
+ -- Test some corner cases for multiplication
+ --
+ select 4790 * ;
+  ?column? 
+ --
+  4790999852090001
+ (1 row)
+ 
+ select 4789 * ;
+  ?column? 
+ --
+  47885211
+ (1 row)
+ 
+ select 4770 * ;
+  ?column? 
+ --
+  4770999852290001
+ (1 row)
+ 
+ select 

Re: [HACKERS] Bug in numeric multiplication

[Dean Rasheed]

On 21 September 2015 at 16:09, Tom Lane  wrote:
> I wrote:
>> Dean Rasheed  writes:
>>> The problem then arises in the final carry propagation pass. During
>>> this phase of the computation, the carry from one digit (which can be
>>> a shade under INT_MAX / NBASE) is added to the next digit, and that's
>>> where the overflow happens.
>
>> Nice catch!  I think the comment could use a little more work, but I'll
>> adjust it and push.
>
> After trying to rework the comment to explain what maxdig really meant
> after your changes, I came to the conclusion that it'd be better to do
> it as per attached.  Does this look sane to you?
>

Yes that looks better. It's still the same amount of extra headroom
(21), but I think it's clearer your way.

Regards,
Dean


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

Re: [HACKERS] Rework the way multixact truncations work

[Josh Berkus]

On 09/21/2015 07:36 AM, Andres Freund wrote:
> On 2015-09-21 10:31:17 -0400, Robert Haas wrote:
>> So, where are we with this patch?
> 
> Uh. I'd basically been waiting on further review and then forgot about
> it.

Does the current plan to never expire XIDs in 9.6 affect multixact
truncation at all?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] Rework the way multixact truncations work

[Andres Freund]

On 2015-09-21 10:30:59 -0700, Josh Berkus wrote:
> On 09/21/2015 07:36 AM, Andres Freund wrote:
> > On 2015-09-21 10:31:17 -0400, Robert Haas wrote:
> >> So, where are we with this patch?
> > 
> > Uh. I'd basically been waiting on further review and then forgot about
> > it.
> 
> Does the current plan to never expire XIDs in 9.6 affect multixact
> truncation at all?

I doubt that it'd in a meaningful manner. Truncations will still need to
happen to contain space usage.

Besides, I'm pretty sceptical of shaping the design of bug fixes to suit
some unwritten feature we only know the highest level design of as of
yet.

Greetings,

Andres Freund


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

[HACKERS] Streaming Replication clusters and load balancing

[James Sewell]

Hello all,

I have recently been working with PostgreSQL and HAProxy to provide
seamless load balancing to a group of database servers. This on it's own
isn't a hard thing: I have an implementation finished and am now thinking
about the best way to bring it to a production ready state which could be
used by others, and used in load-balancers other than HAProxy with minimal
config changes.

My initial requirements were:

Given a group of PostgreSQL servers check each x seconds and:

   - Allow read/write access only to the master server (via IPA / portA)
  - Disallow access if there are multiple master servers




   - Allow read access to all servers (via IPB / portB) as long as the
   following holds:
  - They are attached to the current master server via streaming
  replication (or they are the current master server)
  - They can currently contact the master server (safest option,
  disallow all access when master-less)
  - They are in the same timeline as the master server (do I need this
  check?)
  - The master server reports that they have less than x bytes lag



HAProxy can talk to PostgreSQL for a health check via TCP or PSQL
(connection check only). Neither of these allow the logic above - therefore
this logic has to be hosted outside of HAProxy. This might change in the
future if HAProxy gets the ability to send SQL statements (like an F5 can).

Today the best way to provide this information to  HAProxy (and many other
load balancers, application frameworks, proxies, monitoring solutions) is
via HTTP, where HTTP 200 is pass the check and HTTP 503 is fail the check
(and don't load balance to this node). In my case I have a script which
accepts HTTP requests to /read to check if this node is available for read
only and /write which checks if this node is available for read/writes.

The options as I see them are:

   - Implement a script / small app which connects to PostgreSQL and
   executes these checks
  - Implemented and proven today at many independent sites
  - Should it run on HAProxy server or PSQL server?
  - Integrated HTTP server  or x.inetd script?
  - Platform independence?
  - What if it dies?


   - Implement a custom PostgreSQL BGworker which provides this information
   over HTTP
  - No outside of PostgreSQL config needed
  - No reliance on another daemon / interface being up
  - libmicrohttpd or similar should help with  platform independence
  - Security / acceptance by community?
  - Only newer versions of PostgreSQL


   - Spend the time working on getting SQL checks into HAProxy
  - What about other platforms which only support HTTP?

I think all of the options would benefit from a PSQL extension which does
the following:

   - Encapsulates the check logic (easier to upgrade, manipulate)
   - Stores historic check data for a number of hours / days / months
   - Stores defaults (override via HTTP could be possible for things like
   lag)

Does anyone else have any thoughts on this topic?

Eventually many cool features could flow out of this kind of work:

   - Integration with High Availability products - I have this working with
   EnterpriseDB EFM now.
  - Locate the current master using the HA product
 - more than one master doesn't cause loss of service as long as HA
 state is sane
  - Locate all clustered standby servers using the HA product
 - if a standby is removed from the HA cluster, it is removed from
 load balancing
 - if a standby is not part of the cluster, it is removed from load
 balancing (even if it is part of streaming replication)


   - HTTP replication status requests which facilitate dynamically managing
   HAProxy (or other) PostgreSQL server pools
  - Add a node to streaming replication, it automatically shows up in
  the pool and starts being checked to see if it can service reads
  - Great for cloud scale out


   - Allocation of additional load balancer groups based on some criteria
   (?), for example
  - read/write (as above)
  - read only (as above)
  - data warehouse (reporting reads only)
  - DR (replica with no reads or writes - until it becomes a master)

Keen to hear comments.

Cheers,

James Sewell,
Solutions Architect
__


Level 2, 50 Queen St, Melbourne VIC 3000

*P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

[HACKERS] TEXT vs VARCHAR join qual push down diffrence, bug or expected?

[Jeevan Chalke]

Hi,

It is observed that, when we have one remote (huge) table and one local
(small) table and a join between them, then
 1. If the column type is text, then we push the join qual to the remote
server, so that we will have less rows to fetch, and thus execution time
is very less.
 2. If the column type is varchar, then we do not push the join qual to the
remote server, resulting into large number of data fetch and thus
execution time is very high.

Here is the EXPLAIN plan for such queries:

When VARCHAR column:

QUERY
PLAN
---
 Nested Loop  (cost=100.15..4594935.73 rows=230 width=120) (actual
time=0.490..291.339 rows=1 loops=1)
   Output: a.ename, d.dname
   Join Filter: ((a.deptno)::text = (d.deptno)::text)
   Rows Removed by Join Filter: 100099
   ->  Index Scan using emp2_pk on public.emp2 a  (cost=0.15..8.17 rows=1
width=76) (actual time=0.009..0.013 rows=1 loops=1)
 Output: a.empno, a.ename, a.deptno
 Index Cond: (a.empno = '7369'::numeric)
   ->  Foreign Scan on public.fdw_dept2 d  (cost=100.00..4594353.50
rows=45925 width=120) (actual time=0.466..274.990 rows=100100 loops=1)
 Output: d.deptno, d.dname
 Remote SQL: SELECT deptno, dname FROM public.dept2
 Planning time: 0.697 ms
 Execution time: 291.467 ms
(12 rows)


When TEXT column:

  QUERY
PLAN
--
 Nested Loop  (cost=100.57..216.63 rows=238 width=120) (actual
time=0.375..0.378 rows=1 loops=1)
   Output: a.ename, d.dname
   ->  Index Scan using emp3_pk on public.emp3 a  (cost=0.15..8.17 rows=1
width=70) (actual time=0.010..0.011 rows=1 loops=1)
 Output: a.empno, a.ename, a.deptno
 Index Cond: (a.empno = '7369'::numeric)
   ->  Foreign Scan on public.fdw_dept3 d  (cost=100.42..208.45 rows=1
width=114) (actual time=0.362..0.362 rows=1 loops=1)
 Output: d.deptno, d.dname
 Remote SQL: SELECT deptno, dname FROM public.dept3 WHERE
(($1::text = deptno))
 Planning time: 1.220 ms
 Execution time: 0.498 ms
(10 rows)


Attached test script to reproduce this theory.

I have observed that, since we do not have an equality operator for VARCHAR
type, we convert VARCHAR to TEXT using RelabelType and use texteq operator
function.
However in foreign_expr_walker(), for T_RelabelType case, we have these
conditions which do not allow us push the qual to remote.

/*
 * RelabelType must not introduce a collation not derived
from
 * an input foreign Var.
 */
collation = r->resultcollid;
if (collation == InvalidOid)
state = FDW_COLLATE_NONE;
else if (inner_cxt.state == FDW_COLLATE_SAFE &&
 collation == inner_cxt.collation)
state = FDW_COLLATE_SAFE;
else
state = FDW_COLLATE_UNSAFE;

I guess, since we do push qual to remote in case of TEXT, we should do the
same for VARCHAR too.

Also given that RelabelType are just dummy wrapper for binary compatible
types, can we simply set collation and state from its inner context instead
on above check block. Like

/*
 * Since RelabelType represents a "dummy" type coercion
between
 * two binary-compatible datatypes, set collation and state
got
 * from the inner_cxt.
 */
collation = inner_cxt.collation;
state = inner_cxt.state;

Inputs/Thought?


-- 
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
\c template1

-- Create database localdb and foreigndb;
CREATE DATABASE localdb;
CREATE DATABASE foreigndb;

-- Create tables in foreigndb
\c foreigndb

DROP TABLE IF EXISTS dept1; 

-- #Case 1, deptno datatype is NUMERIC. 
CREATE TABLE dept1 (
  deptno  NUMERIC(10) NOT NULL CONSTRAINT dept1_pk PRIMARY KEY,
  dname   VARCHAR(32) NOT NULL DEFAULT (md5(random()::VARCHAR))
);

INSERT INTO dept1 VALUES (generate_series(1,100100));

DROP TABLE IF EXISTS dept2; 

-- #Case 2, deptno datatype is VARCHAR.
CREATE TABLE dept2 (
  deptno  VARCHAR(10) NOT NULL CONSTRAINT dept2_pk PRIMARY KEY,
  dname   VARCHAR(32) NOT NULL DEFAULT (md5(random()::VARCHAR))
);

INSERT INTO dept2 VALUES (trim(to_char(generate_series(1,100100),'999')));

DROP TABLE IF EXISTS dept3; 

-- #Case 3, deptno datatype is TEXT.
CREATE TABLE dept3 (
  deptno  TEXT NOT NULL CONSTRAINT dept3_pk PRIMARY KEY,
  dname   VARCHAR(32) NOT NULL DEFAULT (md5(random()::VARCHAR))
);

INSERT INTO dept3 VALUES 

Re: [HACKERS] [patch] Proposal for \rotate in psql

[Marcin Mańk]

W dniu piątek, 18 września 2015 Daniel Verite 
napisał(a):

> Pavel Stehule wrote:
>
> > in the help inside your last patch, you are using "crosstab". Cannto be
> > crosstab the name for this feature?
>
> If it wasn't taken already by contrib/tablefunc, that would be a first
> choice. But now, when searching for crosstab+postgresql, pages of
> results come out concerning the crosstab() function.
>
How about transpose (or flip)?

Re: [HACKERS] jsonb_set array append hack?

[Dmitry Dolgov]

> I would expect some kind of error.  We're trying to address a position in
an array, and we're instead passing a key.  If it completes successfully,
the chances are it isn't what the user intended.

Thanks for the explanation. So, basically, it should be like this, am I
right?

postgres=# SELECT jsonb_set(
'{"name": "Joe", "vehicle_types": ["car", "van"]}'::jsonb,
'{vehicle_types, nonsense}',
'"motorcycle"', true);
ERROR:  path element at the position 2 is not an integer

On 20 September 2015 at 23:50, Thom Brown  wrote:

> On 20 September 2015 at 16:17, Dmitry Dolgov <9erthali...@gmail.com>
> wrote:
>
>> I'm sorry, but I'm not sure, what behavior is expected in this case?
>> Right now the following logic was implemented:
>> "we trying to set an element inside an array, but we've got a
>> non-integer path item
>> ("nonsense" in this particular case), so we're going to add a new
>> element at the end of array by default"
>>
>> If it's wrong, should we refuse to perform such kind of operations, or
>> should we replace
>> "vehicle_type": ["car", "van"]
>> to
>> "vehicle_type: {"nonsense": "motorcycle"}
>> ?
>>
>
> (please bottom-post)
>
> I would expect some kind of error.  We're trying to address a position in
> an array, and we're instead passing a key.  If it completes successfully,
> the chances are it isn't what the user intended.
>
> Thom
>


non_integer_in_path.patch
Description: Binary data

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

Re: [HACKERS] Bug in numeric multiplication

[Tom Lane]

Dean Rasheed  writes:
> On 21 September 2015 at 16:09, Tom Lane  wrote:
>> After trying to rework the comment to explain what maxdig really meant
>> after your changes, I came to the conclusion that it'd be better to do
>> it as per attached.  Does this look sane to you?

> Yes that looks better. It's still the same amount of extra headroom
> (21), but I think it's clearer your way.

OK, pushed (after further hacking on the comment ...)

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] Bug in numeric multiplication

[Dean Rasheed]

Hi,

By chance, while testing the nearby numeric log/exp/pow patch, I came
across the following case which generates an initially puzzling
looking error on HEAD -- (5.6-1e-500) ^ (3.2-1e-200). This computation
actually works OK with that other patch, but only by blind luck. It
turns out that the underlying problem is a bug in the low-level
numeric multiplication function mul_var(). It is possible to trigger
it directly with the following carefully crafted inputs:

select 
4790
* 
;

Result:
47909978523049530001

That answer is actually incorrect. Tweaking the input a little, it is
possible to generate a much more obviously nonsensical result:

select 
4789
* 
;

Result:
478999785231+0,*0001

Notice those garbage digits in the middle of the number returned.

The problem is that these examples trigger an overflow of the digits
in the accumulator array in mul_var().

The number on the left in the first example consists of 21 copies of
, preceded by 4790. Those are chosen so that when added together
they lead to a value for maxdig in mul_var() of 21* + 4790 =
214769, which is exactly equal to INT_MAX / (NBASE - 1). So this
doesn't quite trigger a normalisation of the accumulator array, and
leaves several of the digits in that array a little under INT_MAX at
the end of the main multiplication loop.

The problem then arises in the final carry propagation pass. During
this phase of the computation, the carry from one digit (which can be
a shade under INT_MAX / NBASE) is added to the next digit, and that's
where the overflow happens.

To fix that, the initial value for maxdig needs to be made larger to
leave headroom for the carry. The largest possible carry is INT_MAX /
NBASE, and maxdig is the maximum possible dig value divided by
NBASE-1, so maxdig needs to be initialised to

 (INT_MAX / NBASE) / (NBASE - 1)

which is 21 for NBASE = 1.

A new corner-case input that doesn't quite trigger an accumulator
normalisation is then 4769... The worst case inputs are now values
like this for which the sum of a sequence of input digits is INT_MAX /
(NBASE - 1) - 21 = 214769 - 21 = 214748. So in the worst case, the
accumulator's digits can be up to 214748 *  = 2147265252 in the
main multiplication loop. Then, during the carry propagation phase (or
any of the normalisation phases), the carry can be anything up to
INT_MAX / NBASE = 214748. So the maximum value that can be assigned to
any individual digit is now 2147265252 + 214748 = 214748, which is
now less than INT_MAX.

Patch attached.

Regards,
Dean
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
new file mode 100644
index 1bfa29e..4b39c7a
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -5792,9 +5792,13 @@ mul_var(NumericVar *var1, NumericVar *va
 	 * threatens to exceed INT_MAX, we take the time to propagate carries. To
 	 * avoid overflow in maxdig itself, it actually represents the max
 	 * possible value divided by NBASE-1.
+	 *
+	 * Note that the carry propagation steps may carry as much as INT_MAX/NBASE
+	 * from one digit to the next, so we have to leave headroom for that as
+	 * well.
 	 */
 	dig = (int *) palloc0(res_ndigits * sizeof(int));
-	maxdig = 0;
+	maxdig = (INT_MAX / NBASE) / (NBASE - 1);
 
 	ri = res_ndigits - 1;
 	for (i1 = var1ndigits - 1; i1 >= 0; ri--, i1--)
@@ -5824,7 +5828,7 @@ mul_var(NumericVar *var1, NumericVar *va
 			}
 			Assert(carry == 0);
 			/* Reset maxdig to indicate new worst-case */
-			maxdig = 1 + var1digit;
+			maxdig = 1 + var1digit + (INT_MAX / NBASE) / (NBASE - 1);
 		}
 
 		/* Add appropriate multiple of var2 into the accumulator */
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
new file mode 100644
index e6ee548..c1886fd
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -1334,6 +1334,33 @@ SELECT * FROM num_input_test;
 (7 rows)
 
 --
+-- Test some corner cases for multiplication
+--
+select 4790 * ;
+ ?column?  

Re: [HACKERS] [patch] Proposal for \rotate in psql

[Daniel Verite]

Pavel Stehule wrote:

> > So not using \crosstab is deliberate; it's to prevent confusion with
> > the server-side function.
> 
> I don't afraid about this - crosstab is a function in extension. Psql
> backslash commands living in different worlds. 

Sure, but the confusion would be assuming that \crosstab is some sort
of frontend for crosstab() queries,  like for example \copy is a frontend
for COPY.
That mistake seems plausible if the same name is reused, and much less
plausible otherwise.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


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

Re: [HACKERS] Rework the way multixact truncations work

[Robert Haas]

On Sun, Jul 5, 2015 at 3:16 PM, Andres Freund  wrote:
>>On the other hand, in the common case, by the time we perform a
>>restartpoint, we're consistent: I think the main exception to that is
>>if we do a base backup that spans multiple checkpoints.  I think that
>>in the new location, the chances that the legacy truncation is trying
>>to read inconsistent data is probably higher.
>
> The primary problem isn't that we truncate too early, it's that we delay 
> truncation on the standby in comparison to the primary by a considerable 
> amount. All the while continuing to replay multi creations.
>
> I don't see the difference wrt. consistency right now, but I don't have 
> access to the code right now. I mean we *have* to do something while 
> inconsistent. A start/stop backup can easily span a day or four.

So, where are we with this patch?

In my opinion, we ought to do something about master and 9.5 before
beta, so that we're doing *yet another* major release with unfixed
multixact bugs.  Let's make the relevant truncation changes in master
and 9.5 and bump the WAL page magic, so that a 9.5alpha standby can't
be used with a 9.5beta master.  Then, we don't need any of this legacy
truncation stuff at all, and 9.5 is hopefully in a much better state
than 9.4 and 9.3.

Now, that still potentially leaves 9.4 and 9.3 users hanging out to
dry.  But we don't have a tremendous number of those people clamoring
about this, and if we get 9.5+ correct, then we can go and change the
logic in 9.4 and 9.3 later when, and if, we are confident that's the
right thing to do.  I am still not altogether convinced that it's a
good idea, nor am I altogether convinced that this code is right.
Perhaps it is, and if we consensus on it, fine.  But regardless of
that, we should not send a third major release to beta with the
current broken system unless there is really no viable alternative.

-- 
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] Bug in numeric multiplication

[Tom Lane]

Dean Rasheed  writes:
> The problem then arises in the final carry propagation pass. During
> this phase of the computation, the carry from one digit (which can be
> a shade under INT_MAX / NBASE) is added to the next digit, and that's
> where the overflow happens.

Nice catch!  I think the comment could use a little more work, but I'll
adjust it and push.

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] Obsolete use of volatile in walsender.c, walreceiver.c, walreceiverfuncs.c?

[Alvaro Herrera]

Thomas Munro wrote:

> In walsender.c, walreceiver.c, walreceiverfuncs.c there are several
> places where volatile qualifiers are used apparently only to prevent
> reordering around spinlock operations.

In replication/slot.c there are a number of places (12, I think) that
introduce a block specifically to contain a volatile cast on a variable
for spinlock-protected access.  We could remove the whole thing and save
at least 3 lines and one indentation level for each of them.

-- 
Á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] LW_SHARED_MASK macro

[Andres Freund]

On 2015-09-21 22:34:46 +0300, Alexander Korotkov wrote:
> Great. BTW, are you going to commit this?

Yes, will do so tomorrow.

Thanks,

Andres


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

Re: [HACKERS] COPY planning

[Tom Lane]

Alvaro Herrera  writes:
> I noticed that COPY calls planner() (this was introduced in 85188ab88).
> I think it should be calling pg_plan_query() instead.

+1 --- AFAICS, this is the *only* place that is going directly to
planner() without going through pg_plan_query(); other utility
functions such as CREATE TABLE AS do the latter.

As far as the patch goes, do copy.c's #include's need adjustment?
I'm wondering if optimizer/planner.h could be removed, in particular.

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] COMPARE_POINTER_FIELD been dead 13 years after living 2 weeks

[Tom Lane]

Alvaro Herrera  writes:
> I happened to notice that we have a macro COMPARE_POINTER_FIELD in
> nodes/equalfuncs.c that Tom introduced in 2eafcf68d563d (25 Nov 2002)
> and then removed its only callers a0bf885f9ea (12 Dec 2002).

Hm.  I think it was meant to correspond to copyfuncs.c's
COPY_POINTER_FIELD() macro.  The reason it's unused at the moment is
that the only node types where that macro is needed are Plan subtypes,
and we don't have infrastructure for comparing plan trees.

If someone were to introduce a similar data representation into a
parsetree node type, then we'd need this macro ... but then we would
probably also need outfuncs.c and readfuncs.c infrastructure for the
representation, which very possibly explains why there are no such cases;
it's just a lot easier to use an OID list or suchlike, if you need to
provide such support.

> Should we just remove it?

Perhaps.  It's not really doing any harm ...

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

[Alvaro Herrera]

I noticed that COPY calls planner() (this was introduced in 85188ab88).
I think it should be calling pg_plan_query() instead.  The latter is a
very thin wrapper around the former which simply adds a couple of
logging entries, DTrace hooks for start/end, and a debugging cross-check
for plan node copying.

I came across this because I was considering adding some code to
pg_plan_query, so I would have needed to essentially duplicate it in the
COPY path, which seemed bad.  (I have since abandoned the idea, but this
seems a reasonable thing to change nonetheless.)


diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index e98f0fe..94b2f8f 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -1414,7 +1414,7 @@ BeginCopy(bool is_from,
Assert(query->utilityStmt == NULL);
 
/* plan the query */
-   plan = planner(query, 0, NULL);
+   plan = pg_plan_query(query, 0, NULL);
 
/*
 * With row level security and a user using "COPY relation TO", 
we

-- 
Álvaro Herrera33.5S 70.5W


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

[HACKERS] COMPARE_POINTER_FIELD been dead 13 years after living 2 weeks

[Alvaro Herrera]

I happened to notice that we have a macro COMPARE_POINTER_FIELD in
nodes/equalfuncs.c that Tom introduced in 2eafcf68d563d (25 Nov 2002)
and then removed its only callers a0bf885f9ea (12 Dec 2002).
Should we just remove it?

-- 
Álvaro Herrera  Developer, http://www.PostgreSQL.org/


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

Re: [HACKERS] TEXT vs VARCHAR join qual push down diffrence, bug or expected?

[Tom Lane]

Jeevan Chalke  writes:
> It is observed that, when we have one remote (huge) table and one local
> (small) table and a join between them, then
>  1. If the column type is text, then we push the join qual to the remote
> server, so that we will have less rows to fetch, and thus execution time
> is very less.
>  2. If the column type is varchar, then we do not push the join qual to the
> remote server, resulting into large number of data fetch and thus
> execution time is very high.

Hmm ...

> Also given that RelabelType are just dummy wrapper for binary compatible
> types, can we simply set collation and state from its inner context instead
> on above check block.

I think you're blaming the wrong code; RelabelType is handled basically
the same as most other cases.

It strikes me that this function is really going about things the wrong
way.  Rather than trying to determine the output collation per se, what
we ought to be asking is "does every operator in the proposed expression
have an input collation that can be traced to some foreign Var further
down in the expression"?  That is, given the example in hand,

RelabelType(ForeignVar) = RelabelType(LocalVar)

the logic ought to be like "the ForeignVar has collation X, and that
bubbles up without change through the RelabelType, and then the equals
operator's inputcollation matches that, so accept it --- regardless of
where the other operand's collation came from exactly".  The key point
is that we want to validate operator input collations, not output
collations, as having something to do with what the remote side would do.

This would represent a fairly significant rewrite of foreign_expr_walker's
collation logic; although I think the end result would be no more
complicated, possibly simpler, than it is now.

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] Streaming Replication clusters and load balancing

[Dmitry Vasilyev]

Hi!
By default, HAproxy configuration can not be changed without breaking a
connection with the client :)

--
Dmitry Vasilyev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Fri, 2015-09-18 at 12:27 +1000, James Sewell wrote:
> Hello all,
> 
> I have recently been working with PostgreSQL and HAProxy to provide
> seamless load balancing to a group of database servers. This on it's
> own isn't a hard thing: I have an implementation finished and am now
> thinking about the best way to bring it to a production ready state
> which could be used by others, and used in load-balancers other than
> HAProxy with minimal config changes.
> 
> My initial requirements were:
> 
> Given a group of PostgreSQL servers check each x seconds and:
> Allow read/write access only to the master server (via IPA / portA)
> Disallow access if there are multiple master servers
> Allow read access to all servers (via IPB / portB) as long as the
> following holds:
> They are attached to the current master server via streaming
> replication (or they are the current master server)
> They can currently contact the master server (safest option, disallow
> all access when master-less)
> They are in the same timeline as the master server (do I need this
> check?)
> The master server reports that they have less than x bytes lag
> HAProxy can talk to PostgreSQL for a health check via TCP or PSQL
> (connection check only). Neither of these allow the logic above -
> therefore this logic has to be hosted outside of HAProxy. This might
> change in the future if HAProxy gets the ability to send SQL
> statements (like an F5 can).
> 
> Today the best way to provide this information to  HAProxy (and many
> other load balancers, application frameworks, proxies, monitoring
> solutions) is via HTTP, where HTTP 200 is pass the check and HTTP 503
> is fail the check (and don't load balance to this node). In my case I
> have a script which accepts HTTP requests to /read to check if this
> node is available for read only and /write which checks if this node
> is available for read/writes.
> 
> The options as I see them are:
> Implement a script / small app which connects to PostgreSQL and
> executes these checks
> Implemented and proven today at many independent sites
> Should it run on HAProxy server or PSQL server? 
> Integrated HTTP server  or x.inetd script?
> Platform independence?
> What if it dies?
> Implement a custom PostgreSQL BGworker which provides this
> information over HTTP
> No outside of PostgreSQL config needed
> No reliance on another daemon / interface being up
> libmicrohttpd or similar should help with  platform independence
> Security / acceptance by community?
> Only newer versions of PostgreSQL
> Spend the time working on getting SQL checks into HAProxy
> What about other platforms which only support HTTP?
> I think all of the options would benefit from a PSQL extension which
> does the following:
> Encapsulates the check logic (easier to upgrade, manipulate)
> Stores historic check data for a number of hours / days / months
> Stores defaults (override via HTTP could be possible for things like
> lag)
> Does anyone else have any thoughts on this topic? 
> 
> Eventually many cool features could flow out of this kind of work:
> Integration with High Availability products - I have this working
> with EnterpriseDB EFM now.
> Locate the current master using the HA product 
> more than one master doesn't cause loss of service as long as HA
> state is sane
> Locate all clustered standby servers using the HA product
> if a standby is removed from the HA cluster, it is removed from load
> balancing
> if a standby is not part of the cluster, it is removed from load
> balancing (even if it is part of streaming replication)
> HTTP replication status requests which facilitate dynamically
> managing HAProxy (or other) PostgreSQL server pools
> Add a node to streaming replication, it automatically shows up in the
> pool and starts being checked to see if it can service reads
> Great for cloud scale out
> Allocation of additional load balancer groups based on some criteria
> (?), for example
> read/write (as above)
> read only (as above)
> data warehouse (reporting reads only)
> DR (replica with no reads or writes - until it becomes a master)
> Keen to hear comments.
> 
> Cheers,
> 
> James Sewell,
> Solutions Architect 
> __
>  
> 
> Level 2, 50 Queen St, Melbourne VIC 3000
> 
> P (+61) 3 8370 8000  W www.lisasoft.com ; F (+61) 3 8370 8099
>  
> 
> The contents of this email are confidential and may be subject to
> legal or professional privilege and copyright. No representation is
> made that this email is free of viruses or other defects. If you have
> received this communication in error, you may not copy or distribute
> any part of it or otherwise disclose its contents to anyone. Please
> advise the sender of your incorrect receipt of this correspondence.

Re: [HACKERS] jsonb_set array append hack?

[Andrew Dunstan]

On 09/21/2015 12:13 PM, Dmitry Dolgov wrote:
> I would expect some kind of error.  We're trying to address a 
position in an array, and we're instead passing a key.  If it 
completes successfully, the chances are it isn't what the user intended.


Thanks for the explanation. So, basically, it should be like this, am 
I right?


postgres=# SELECT jsonb_set(
'{"name": "Joe", "vehicle_types": ["car", "van"]}'::jsonb,
'{vehicle_types, nonsense}',
'"motorcycle"', true);
ERROR:  path element at the position 2 is not an integer



That seems reasonable. For that matter, we should probably disallow NULL 
path elements also, shouldn't we?


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] Streaming Replication clusters and load balancing

[Josh Berkus]

On 09/17/2015 07:27 PM, James Sewell wrote:
> Hello all,
> 
> I have recently been working with PostgreSQL and HAProxy to provide
> seamless load balancing to a group of database servers. This on it's own
> isn't a hard thing: I have an implementation finished and am now
> thinking about the best way to bring it to a production ready state
> which could be used by others, and used in load-balancers other than
> HAProxy with minimal config changes.

Funny, I've been working on this exact problem today to add to the
Patroni project: https://github.com/zalando/patroni

My solution will depend on patroni's included HTTP access, though, so
I'm not sure it will work for you.  Anyway, this isn't a topic for
pgsql-hackers mailing list, so reply offlist if you want to discuss further.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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