Re: [HACKERS] Patent warning about the Greenplum source code

2015-11-01 Thread Bruce Momjian 
On Fri, Oct 30, 2015 at 04:47:35AM -0400, Bruce Momjian wrote:
> Therefore, I caution people from viewing the Greenplum source code as
> you might see patented ideas that could be later implemented in
> Postgres, opening Postgres up to increased patent violation problems.  I
> am also concerned about existing community members who work for
> Pivotal/Greenplum and therefore are required to view the patented source
> code.  The license issue might eventually be improved by
> Pivotal/Greenplum, but, for now, I think caution is necessary.
> 
> Of course, never mention known-patented ideas in any community forum,
> including this email list.

I just found out that Citus Data has patent applications pending, so
viewing Citus Data source code has the same problems as Greenplum.

-- 
  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] Patent warning about the Greenplum source code

2015-11-01 Thread Bruce Momjian 
On Sun, Nov  1, 2015 at 01:27:13AM -0500, Bruce Momjian wrote:
> On Fri, Oct 30, 2015 at 04:47:35AM -0400, Bruce Momjian wrote:
> > Therefore, I caution people from viewing the Greenplum source code as
> > you might see patented ideas that could be later implemented in
> > Postgres, opening Postgres up to increased patent violation problems.  I
> > am also concerned about existing community members who work for
> > Pivotal/Greenplum and therefore are required to view the patented source
> > code.  The license issue might eventually be improved by
> > Pivotal/Greenplum, but, for now, I think caution is necessary.
> > 
> > Of course, never mention known-patented ideas in any community forum,
> > including this email list.
> 
> I just found out that Citus Data has patent applications pending, so
> viewing Citus Data source code has the same problems as Greenplum.

Actually, it might only be their closed source software that contains
patents, i.e. not pg_shard.  I will check and report back when I can
unless someone else reports here first.

-- 
  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] Proposal: Trigonometric functions in degrees

2015-11-01 Thread Dean Rasheed 
On 27 October 2015 at 08:24, Dean Rasheed  wrote:
> I think it's still feasible to have sind(30) = 0.5 exactly and keep
> monotonicity
>

Here's a patch along those lines. It turned out to be fairly
straightforward. It's still basically a thin wrapper on top of the
math library trig functions, with a bit of careful scaling to ensure
that curves join together to form continuous functions that are
monotonic in the expected regions and return exact values in all the
special cases 0,30,45,60,90,...

I also modified some of the CHECKFLOATVAL() checks which didn't look
right to me, unless there's some odd platform-specific behaviour that
I'm not aware of, functions like sin and asin should never return
infinity.

Regards,
Dean
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 2946122..434fb88
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -993,9 +993,9 @@
Finally,  shows the
available trigonometric functions.  All trigonometric functions
take arguments and return values of type double
-   precision. Trigonometric functions arguments are expressed
-   in radians. Inverse functions return values are expressed in
-   radians.  See unit transformation functions
+   precision.  Each of the trigonometric functions comes in
+   two varieties, one which works in radians and one which works in
+   degrees.  See unit transformation functions
radians() and
degrees() above.
   
@@ -1003,10 +1003,11 @@

 Trigonometric Functions
 
-
+
  
   
-   Function
+   Function (radians)
+   Function (degrees)
Description
   
  
@@ -1018,6 +1019,11 @@
  acos
 acos(x)

+   
+
+ acosd
+acosd(x)
+   
inverse cosine
   
 
@@ -1028,6 +1034,12 @@
 
 asin(x)

+   
+
+ asind
+
+asind(x)
+   
inverse sine
   
 
@@ -1038,6 +1050,12 @@
 
 atan(x)

+   
+
+ atand
+
+atand(x)
+   
inverse tangent
   
 
@@ -1049,6 +1067,13 @@
 atan2(y,
 x)

+   
+
+ atan2d
+
+atan2d(y,
+x)
+   
inverse tangent of
 y/x
   
@@ -1060,6 +1085,12 @@
 
 cos(x)

+   
+
+ cosd
+
+cosd(x)
+   
cosine
   
 
@@ -1070,6 +1101,12 @@
 
 cot(x)

+   
+
+ cotd
+
+cotd(x)
+   
cotangent
   
 
@@ -1080,6 +1117,12 @@
 
 sin(x)

+   
+
+ sind
+
+sind(x)
+   
sine
   
 
@@ -1090,6 +1133,12 @@
 
 tan(x)

+   
+
+ tand
+
+tand(x)
+   
tangent
   
  
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
new file mode 100644
index 4e927d8..d2318f7
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -1535,7 +1535,7 @@ dacos(PG_FUNCTION_ARGS)
 (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
  errmsg("input is out of range")));
 
-	CHECKFLOATVAL(result, isinf(arg1), true);
+	CHECKFLOATVAL(result, false, true);
 	PG_RETURN_FLOAT8(result);
 }
 
@@ -1556,7 +1556,7 @@ dasin(PG_FUNCTION_ARGS)
 (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
  errmsg("input is out of range")));
 
-	CHECKFLOATVAL(result, isinf(arg1), true);
+	CHECKFLOATVAL(result, false, true);
 	PG_RETURN_FLOAT8(result);
 }
 
@@ -1577,7 +1577,7 @@ datan(PG_FUNCTION_ARGS)
 (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
  errmsg("input is out of range")));
 
-	CHECKFLOATVAL(result, isinf(arg1), true);
+	CHECKFLOATVAL(result, false, true);
 	PG_RETURN_FLOAT8(result);
 }
 
@@ -1599,7 +1599,7 @@ datan2(PG_FUNCTION_ARGS)
 (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
  errmsg("input is out of range")));
 
-	CHECKFLOATVAL(result, isinf(arg1) || isinf(arg2), true);
+	CHECKFLOATVAL(result, false, true);
 	PG_RETURN_FLOAT8(result);
 }
 
@@ -1620,7 +1620,7 @@ dcos(PG_FUNCTION_ARGS)
 (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
  errmsg("input is out of range")));
 
-	CHECKFLOATVAL(result, isinf(arg1), true);
+	CHECKFLOATVAL(result, false, true);
 	PG_RETURN_FLOAT8(result);
 }
 
@@ -1642,7 +1642,7 @@ dcot(PG_FUNCTION_ARGS)
  errmsg("input is out of range")));
 
 	result = 1.0 / result;
-	CHECKFLOATVAL(result, true /* cotan(pi/2) == inf */ , true);
+	CHECKFLOATVAL(result, true /* cot(0) == Inf */ , true);
 	PG_RETURN_FLOAT8(result);
 }
 
@@ -1663,7 +1663,7 @@ dsin(PG_FUNCTION_ARGS)
 (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
  errmsg("input is out of range")));
 
-	CHECKFLOATVAL(result, isinf(arg1), true);
+	CHECKFLOATVAL(result, false, true);
 	PG_RETURN_FLOAT8(result);
 }
 
@@ -1688,6

Re: [HACKERS] ParallelContexts can get confused about which worker is which

2015-11-01 Thread Amit Kapila 
On Sat, Oct 31, 2015 at 11:35 AM, Robert Haas  wrote:
>
> On Fri, Oct 30, 2015 at 11:24 AM, Robert Haas 
wrote:
> > The other way to fix this is to pass down the index
> > that the leader assigns to any given worker, and have the worker use
> > that index instead of allocating its own separate index after
> > connecting to the DSM segment.  Unfortunately, there's not really a
> > good way to pass that additional information down to the worker right
> > now, but we could fix that pretty easily by adding an additional field
> > to the BackgroundWorker structure, which the worker would then be able
> > to access via MyBgworkerEntry.
>
> Here's a patch implementing that.
>

If we are going to add a new parameter to BackgroundWorker structure,
then the same needs to be updated in docs [1] as well.  I think adding
a new parameter to this structure might require some updations in
client applications. It seems worth to add a note for the same in commit
message, so that same could be reflected in Release Notes.

Also, I don't know why BGW_EXTRALEN needs to be 128 and not 64?
I guess you kept it so that in future if we need to pass more information,
then the same could be used which seems reasonable considering that
this is an exposed structure and we don't want to change it again.

/* Mutex protects remaining fields. */
slock_t mutex;


/* Maximum XactLastRecEnd of any worker. */
XLogRecPtr last_xlog_end;

} FixedParallelState;

Now as above mutex will be used to just protect last_xlog_end, do you
think it is better to modify the comment above mutex declaration?

[1] - http://www.postgresql.org/docs/devel/static/bgworker.html

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

Re: [HACKERS] September 2015 Commitfest

2015-11-01 Thread Michael Paquier 
On Sun, Nov 1, 2015 at 1:53 AM, Marko Tiikkaja  wrote:
> On 10/31/15 12:42 AM, Michael Paquier wrote:
>>
>> So, seeing nothing happening I have done the above, opened 2015-11 CF
>> and closed the current one.
>
>
> Are we doing these in an Australian time zone now?  It was quite unpleasant
> to find that the 2015-11 is "in progress" already and two of my patches will
> not be in there.  AFAIR the submission deadline used to be around UTC
> midnight of the first day of the month the CF nominally begins on.

Er, well. Sorry for that... I did all this stuff on Friday evening
before leaving back for Japan with not much time on the table. I have
switched the CF back to an open status for now. And I'll switch it
back to in-progress in 24 hours. If there are patches you would like
attached to the CF app don't hesitate to ping me.
-- 
Michael


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

Re: [HACKERS] Request: pg_cancel_backend variant that handles 'idle in transaction' sessions

2015-11-01 Thread Magnus Hagander 
On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila 
wrote:

> On Sat, Oct 31, 2015 at 2:50 AM, Merlin Moncure 
> wrote:
> >
> > Idle hanging transactions from poorly written applications are the
> > bane of my existence.  Several months back one of them took down one
> > of hour production websites for several hours.
> >
> > Unfortunately, the only way to deal with them is to terminate the
> > backend which is heavy handed and in some cases causes further damage.
> >   Something like pg_cancel_transaction(pid) would be nice; it would
> > end the transaction regardless if in an actual statement or not.
> >
>
> Why pg_cancel_backend(pid) is not sufficient for the above use case?
> Basically you want to rollback current transaction, I think that can be
> achieved by pg_cancel_backend.
>

Not when the session is idle in transaction, only when it's actually doing
something.

IIRC one of the reasons is that when idle in transaction, the client is not
expecting any response, and would get out of sync. I know this has been
discussed a number of times, so a better explanation can probably be found
in the archives :)

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] fortnight interval support

2015-11-01 Thread Tom Lane 
David Fetter  writes:
> On Tue, Oct 27, 2015 at 07:30:13PM +, Nathan Wagner wrote:
>> On Wed, Oct 28, 2015 at 08:17:25AM +1300, Gavin Flower wrote:
>> What, it's a "fortnight", not a "quinzaine".
>> 
>> You have no idea how hard it was to resist updating the patch...

> Well, if you won't do it, I will.

Please tell me this is a joke.

(FWIW, I don't have a problem with "fortnight", but I draw the line
at units that are not used in English.)

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] POC: Cache data in GetSnapshotData()

2015-11-01 Thread Jim Nasby 

On 5/25/15 10:04 PM, Amit Kapila wrote:

On Tue, May 26, 2015 at 12:10 AM, Andres Freund > wrote:
 >
 > On 2015-05-20 19:56:39 +0530, Amit Kapila wrote:
 > > I have done some tests with this patch to see the benefit with
 > > and it seems to me this patch helps in reducing the contention
 > > around ProcArrayLock, though the increase in TPS (in tpc-b tests
 > > is around 2~4%) is not very high.
 > >
 > > pgbench (TPC-B test)
 > > ./pgbench -c 64 -j 64 -T 1200 -M prepared postgres
 >
 > Hm, so it's a read mostly test.

Write not *Read* mostly.

 > I probably not that badly contended on
 > the snapshot acquisition itself. I'd guess a 80/20 read/write mix or so
 > would be more interesting for the cases where we hit this really bad.
 >

Yes 80/20 read/write mix will be good test to test this patch and I think
such a load is used by many applications (Such a load is quite common
in telecom especially their billing related applications) and currently
we don't
have such a test handy to measure performance.

On a side note, I think it would be good if we can add such a test to
pgbench or may be use some test which adheres to TPC-C specification.
Infact, I remember [1] people posting test results with such a workload
showing ProcArrayLock as contention.


[1] -
http://www.postgresql.org/message-id/e8870a2f6a4b1045b1c292b77eab207c77069...@szxema501-mbx.china.huawei.com


Anything happen with this?
--
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] fortnight interval support

2015-11-01 Thread David Fetter 
On Tue, Oct 27, 2015 at 07:30:13PM +, Nathan Wagner wrote:
> On Wed, Oct 28, 2015 at 08:17:25AM +1300, Gavin Flower wrote:
> > You trying to get PostgreSQL banned in France???  :-)
> > 
> > When I was learning French many years ago, I was told that the French
> > consider their fortnight to be 15 days!!!
> 
> What, it's a "fortnight", not a "quinzaine".
> 
> You have no idea how hard it was to resist updating the patch...

Well, if you won't do it, I will.

Please find attached.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 926358e..8a62d65 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -186,6 +186,8 @@ static const datetkn deltatktbl[] = {
{DDECADE, UNITS, DTK_DECADE},   /* "decade" relative */
{"decades", UNITS, DTK_DECADE}, /* "decades" relative */
{"decs", UNITS, DTK_DECADE},/* "decades" relative */
+   {DFORTNIGHT, UNITS, DTK_FORTNIGHT}, /* "fortnights" relative */
+   {"fortnights", UNITS, DTK_FORTNIGHT}, /* "fortnights" relative */
{"h", UNITS, DTK_HOUR}, /* "hour" relative */
{DHOUR, UNITS, DTK_HOUR},   /* "hour" relative */
{"hours", UNITS, DTK_HOUR}, /* "hours" relative */
@@ -214,6 +216,8 @@ static const datetkn deltatktbl[] = {
{"msecs", UNITS, DTK_MILLISEC},
{"qtr", UNITS, DTK_QUARTER},/* "quarter" relative */
{DQUARTER, UNITS, DTK_QUARTER}, /* "quarter" relative */
+   {DQUINZAINE, UNITS, DTK_QUINZAINE}, /* "quinzaines" relative */
+   {"quinzaines", UNITS, DTK_QUINZAINE}, /* "quinzaines" relative */
{"s", UNITS, DTK_SECOND},
{"sec", UNITS, DTK_SECOND},
{DSECOND, UNITS, DTK_SECOND},
@@ -3281,6 +3285,18 @@ DecodeInterval(char **field, int *ftype, int nf, int 
range,
tmask = DTK_M(DAY);
break;
 
+   case DTK_FORTNIGHT:
+   tm->tm_mday += val * 14;
+   AdjustFractDays(fval, tm, fsec, 
14);
+   tmask = DTK_M(WEEK);
+   break;
+
+   case DTK_QUINZAINE:
+   tm->tm_mday += val * 15;
+   AdjustFractDays(fval, tm, fsec, 
15);
+   tmask = DTK_M(WEEK);
+   break;
+
case DTK_WEEK:
tm->tm_mday += val * 7;
AdjustFractDays(fval, tm, fsec, 
7);
diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h
index e9a1ece..3944912 100644
--- a/src/include/utils/datetime.h
+++ b/src/include/utils/datetime.h
@@ -52,6 +52,8 @@ struct tzEntry;
 #define DHOUR  "hour"
 #define DDAY   "day"
 #define DWEEK  "week"
+#define DFORTNIGHT "fortnight"
+#define DQUINZAINE "quinzaine"
 #define DMONTH "month"
 #define DQUARTER   "quarter"
 #define DYEAR  "year"
@@ -181,6 +183,8 @@ struct tzEntry;
 #define DTK_TZ_MINUTE  35
 #define DTK_ISOYEAR36
 #define DTK_ISODOW 37
+#define DTK_FORTNIGHT  38
+#define DTK_QUINZAINE  39
 
 
 /*
diff --git a/src/test/regress/expected/interval.out 
b/src/test/regress/expected/interval.out
index c873a99..350f097 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -40,6 +40,18 @@ SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours";
  10 days 12:00:00
 (1 row)
 
+SELECT INTERVAL '1 fortnight' AS "Fourteen days";
+ Fourteen days 
+---
+ 14 days
+(1 row)
+
+SELECT INTERVAL '1 quinzaine' AS "Fifteen days";
+ Fifteen days 
+--
+ 15 days
+(1 row)
+
 SELECT INTERVAL '1.5 months' AS "One month 15 days";
  One month 15 days 
 ---
diff --git a/src/test/regress/sql/interval.sql 
b/src/test/regress/sql/interval.sql
index 789c3de..ae884bc 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -12,6 +12,8 @@ SELECT INTERVAL '-08:00' AS "Eight hours";
 SELECT INTERVAL '-1 +02:03' AS "22 hours ago...";
 SELECT INTERVAL '-1 days +02:03' AS "22 hours ago...";
 SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours";
+SELECT INTERVAL '1 fortnight' AS "Fourteen days";
+SELECT INTERVAL

Re: [HACKERS] POC: Cache data in GetSnapshotData()

2015-11-01 Thread Amit Kapila 
On Sun, Nov 1, 2015 at 8:46 PM, Jim Nasby  wrote:

> On 5/25/15 10:04 PM, Amit Kapila wrote:
>
>> On Tue, May 26, 2015 at 12:10 AM, Andres Freund > > wrote:
>>  >
>>  > On 2015-05-20 19:56:39 +0530, Amit Kapila wrote:
>>  > > I have done some tests with this patch to see the benefit with
>>  > > and it seems to me this patch helps in reducing the contention
>>  > > around ProcArrayLock, though the increase in TPS (in tpc-b tests
>>  > > is around 2~4%) is not very high.
>>  > >
>>  > > pgbench (TPC-B test)
>>  > > ./pgbench -c 64 -j 64 -T 1200 -M prepared postgres
>>  >
>>  > Hm, so it's a read mostly test.
>>
>> Write not *Read* mostly.
>>
>>  > I probably not that badly contended on
>>  > the snapshot acquisition itself. I'd guess a 80/20 read/write mix or so
>>  > would be more interesting for the cases where we hit this really bad.
>>  >
>>
>> Yes 80/20 read/write mix will be good test to test this patch and I think
>> such a load is used by many applications (Such a load is quite common
>> in telecom especially their billing related applications) and currently
>> we don't
>> have such a test handy to measure performance.
>>
>> On a side note, I think it would be good if we can add such a test to
>> pgbench or may be use some test which adheres to TPC-C specification.
>> Infact, I remember [1] people posting test results with such a workload
>> showing ProcArrayLock as contention.
>>
>>
>> [1] -
>>
>> http://www.postgresql.org/message-id/e8870a2f6a4b1045b1c292b77eab207c77069...@szxema501-mbx.china.huawei.com
>>
>
> Anything happen with this?
>

No.  I think one has to study the impact of this patch on latest code
especially after commit-0e141c0f.


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

Re: [HACKERS] eXtensible Transaction Manager API

2015-11-01 Thread Craig Ringer 
On 31 October 2015 at 17:22, konstantin knizhnik
 wrote:

> Waiting for your feedback

For anyone wondering about performance impact, there are some graphs
on page 23 of the PDF presentation. I didn't see anything else, and
the graphs don't seem to cover comparison of Pg with the XTM
transaction manager hooks and no DTM enabled vs Pg without the hooks,
i.e. the hook overhead its self.

Have you done much work on that? Personally I wouldn't expect to see
any meaningful overhead, but I'd really like to have numbers behind
that.

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

Re: [HACKERS] September 2015 Commitfest

2015-11-01 Thread Tom Lane 
Jim Nasby  writes:
> On 10/31/15 11:19 AM, Nathan Wagner wrote:
>> I think it would be very useful to just be able to tell the system "fire
>> this up for me so I can test it".  I don't think it needs to handle
>> every possible testing scenario, just making it easier to leave up the
>> test postmaster from make check would be very useful, at least to me.

> I've wished that the cluster setup and teardown behavior of pg_regress 
> was available outside of pg_regress itself. Would that mostly suffice 
> for what you're looking for?

I should think not.  pg_regress is not merely not encouraging of outside
connections to the started postmaster; if such is even possible, it's
likely to be regarded as a security bug.  What Nathan is looking for is
arguably useful, but I do not think pg_regress should be expected to
support it.  It needs to be a different tool, with different security
parameters.

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] Request: pg_cancel_backend variant that handles 'idle in transaction' sessions

2015-11-01 Thread Amit Kapila 
On Sun, Nov 1, 2015 at 11:34 PM, Tom Lane  wrote:
>
> Magnus Hagander  writes:
> > On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila 
> > wrote:
> >> Why pg_cancel_backend(pid) is not sufficient for the above use case?
> >> Basically you want to rollback current transaction, I think that can be
> >> achieved by pg_cancel_backend.
>
> > Not when the session is idle in transaction, only when it's actually
doing
> > something.
>

Okay, thats right and the reason is that while reading message from client,
if an error occurs, it can loose track of previous and next messages and
that
could lead to an unrecoverable state.

>
> I think in principle it could be done by transitioning the backend into
> a new xact.c state, wherein we know that the active transaction has been
> canceled (at least to the extent of releasing externally visible resources
> such as locks and snapshots), but this fact hasn't been reported to the
> connected client.  Then the next command submitted by the client would get
> a "transaction cancelled" error and we'd go into the normal transaction-
> failed state.
>

That sounds to be a solution for this problem or otherwise for such a case
can't we completely abort the active transaction and set a flag like
PrevCommandFailed/PrevTransFailed and on receiving next message if
such a flag is set, then throw an appropriate error.


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

Re: [HACKERS] Patent warning about the Greenplum source code

2015-11-01 Thread Bruce Momjian 
On Sun, Nov  1, 2015 at 12:12:48PM -0800, Josh Berkus wrote:
> Do let me point out that *code* isn't patented.  *techniques* are.  So
> those techniques are patented whether or not you read the code.  It's
> just that if you read the code, copy the technique directly, and put it
> in Postgres, that's considered "willful" instead of "innocent"
> infringement and the penalties are different.  Its effect on our project
> is the same, though: we have to rip out the code in a hurry.
> 
> Maybe we should just relicense PostgreSQL as Apache and cover all of the
> patent issues ;-)

LOL, but the existing Apache 2.0 license doesn't extend the patent grant
to derived works, including closed source ones, so that would not seem to
help.  If it did extend to all derived works, the patents would be
unenforceable.

> >>> Of course, never mention known-patented ideas in any community forum,
> >>> including this email list.
> >>
> >> I just found out that Citus Data has patent applications pending, so
> >> viewing Citus Data source code has the same problems as Greenplum.
> > 
> > Actually, it might only be their closed source software that contains
> > patents, i.e. not pg_shard.  I will check and report back when I can
> > unless someone else reports here first.
> 
> I will ask Citus Data for an official statement.  I will point out that
> cstore_fdw is Apache-licensed, which also contains a patent grant.

Good point.  So we have CitusDB, cstore_fdw, and pg_shared, the later
two are open source.  Ideally only the closed source CitusDB implements
patented ideas they own.

Let me add that this is more than hypothetical.  While we don't think
any of these companies would sue the community for patent infringement,
they could sue users, and the company could be bought by a sinister
company that could enforce those patents.  For example, few had problems
with Sun's control over Java, but when Oracle bought Sun, more people
were concerned.  Someone could buy the company _just_ to sue for patent
infringement --- happens all the 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] fortnight interval support

2015-11-01 Thread David Fetter 
On Sun, Nov 01, 2015 at 01:06:39PM -0500, Tom Lane wrote:
> David Fetter  writes:
> > On Tue, Oct 27, 2015 at 07:30:13PM +, Nathan Wagner wrote:
> >> On Wed, Oct 28, 2015 at 08:17:25AM +1300, Gavin Flower wrote:
> >> What, it's a "fortnight", not a "quinzaine".
> >> 
> >> You have no idea how hard it was to resist updating the patch...
> 
> > Well, if you won't do it, I will.
> 
> Please tell me this is a joke.

Yes, it's a joke.

> (FWIW, I don't have a problem with "fortnight", but I draw the line
> at units that are not used in English.)

It's used in English, but not in this context.

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

As to localization, I think we need to consider carefully whether
PostgreSQL is to be a US-only RDBMS with a few concessions to usage
elsewhere, or one usable by all the world's peoples, and no, we should
probably not continue this thread to work out the decisions implicit
in whatever direction we settle on.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
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: Fix parallel workers connection bug in pg_dump (Bug #13727)

2015-11-01 Thread Marko Tiikkaja 

On 10/25/15 10:55 PM, Zeus Kronion wrote:

Parallel workers were failing to connect to the database when running
pg_dump with a connection string. The first of the following two commands
runs without errors, while the second one fails:
pg_dump "postgres://my-user:my-passw...@my.hostname.com:5432/my-db" -Fd -f
my-dump
pg_dump "postgres://my-user:my-passw...@my.hostname.com:5432/my-db" -Fd
--jobs=9 -f my-dump

The error message:
pg_dump: [parallel archiver] connection to database "my-db" failed:
fe_sendauth: no password supplied

The password is not being stored correctly in the PGconn object when
connecting with a connection string.


Yeah, the current code is definitely broken for this case.  However, I 
don't feel like this patch is quite there yet, either.  _connectDB has 
similar logic in it which might be hit in case e.g. a a user's HBA is 
changed from a non-password-requiring method to a password-requiring one 
after the one or more connections has been initiated.  That one needs 
changing as well.


However, I don't quite like the way the password cache is kept up to 
date in the old *or* the new code.  It seems to me that it should 
instead look like:


   if (PQconnectionUsedPassword(AH->connection))
   AH->savedPassword = PQpass(AH->connection);

What do you think?


.m


--
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 (INSERT/UPDATE/DELETE .. RETURNING ..)

2015-11-01 Thread Michael Paquier 
On Sun, Nov 1, 2015 at 2:49 AM, Marko Tiikkaja wrote:
> I can't add this to November's commit fest, but I'm posting this anyway in
> case someone is thinking about implementing this feature.

Note: this one has been added to CF:
https://commitfest.postgresql.org/7/414/
-- 
Michael


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

Re: [HACKERS] September 2015 Commitfest

2015-11-01 Thread Marko Tiikkaja 

On 11/1/15 11:36 AM, Michael Paquier wrote:

On Sun, Nov 1, 2015 at 1:53 AM, Marko Tiikkaja  wrote:

Are we doing these in an Australian time zone now?  It was quite unpleasant
to find that the 2015-11 is "in progress" already and two of my patches will
not be in there.  AFAIR the submission deadline used to be around UTC
midnight of the first day of the month the CF nominally begins on.


Er, well. Sorry for that... I did all this stuff on Friday evening
before leaving back for Japan with not much time on the table. I have
switched the CF back to an open status for now. And I'll switch it
back to in-progress in 24 hours. If there are patches you would like
attached to the CF app don't hesitate to ping me.


Thanks.  I managed to add the two patches just fine now.


.m


--
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] to build docs on Mac OS X El Capitan with MacPorts

2015-11-01 Thread Neil Tiffin 
The attached patch was required to get the docs to build on Mac OS X 10.11.1 
(15B42) with MacPorts 2.3.4.  After changing docbook.m4 ‘autoreconf’ has to be 
run.  This patch does not include the new version of ‘configure'

As explained in the devel documentation (J.2.6) the docs could be made to build 
by adding ‘export DOCBOOKSTYLE=/opt/local/share/dsssl/docbook-dsssl'

However, in J.2.4 it implies that just using MacPorts will work without 
additional setup.  This patch makes that happen.

diff --git a/config/docbook.m4 b/config/docbook.m4
index 4304fa7..f9d 100644
--- a/config/docbook.m4
+++ b/config/docbook.m4
@@ -55,7 +55,7 @@ AC_CACHE_VAL([pgac_cv_path_stylesheets],
 [if test -n "$DOCBOOKSTYLE"; then
   pgac_cv_path_stylesheets=$DOCBOOKSTYLE
 else
-  for pgac_prefix in /usr /usr/local /opt /sw; do
+  for pgac_prefix in /usr /usr/local /opt /opt/local /sw; do
 for pgac_infix in share lib; do
   for pgac_postfix in \
 sgml/stylesheets/nwalsh-modular \
@@ -65,7 +65,8 @@ else
 sgml/docbook/dsssl/modular \
 sgml/docbook/stylesheet/dsssl/modular \
 sgml/docbook/dsssl-stylesheets \
-sgml/dsssl/docbook-dsssl-nwalsh
+sgml/dsssl/docbook-dsssl-nwalsh \
+dsssl/docbook-dsssl
   do
 pgac_candidate=$pgac_prefix/$pgac_infix/$pgac_postfix
 if test -r "$pgac_candidate/html/docbook.dsl" \





-- 
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] Request: pg_cancel_backend variant that handles 'idle in transaction' sessions

2015-11-01 Thread Tom Lane 
Magnus Hagander  writes:
> On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila 
> wrote:
>> Why pg_cancel_backend(pid) is not sufficient for the above use case?
>> Basically you want to rollback current transaction, I think that can be
>> achieved by pg_cancel_backend.

> Not when the session is idle in transaction, only when it's actually doing
> something.

> IIRC one of the reasons is that when idle in transaction, the client is not
> expecting any response, and would get out of sync. I know this has been
> discussed a number of times, so a better explanation can probably be found
> in the archives :)

I think in principle it could be done by transitioning the backend into
a new xact.c state, wherein we know that the active transaction has been
canceled (at least to the extent of releasing externally visible resources
such as locks and snapshots), but this fact hasn't been reported to the
connected client.  Then the next command submitted by the client would get
a "transaction cancelled" error and we'd go into the normal transaction-
failed state.

I don't think this would be exactly trivial, but it's probably doable.

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] Patent warning about the Greenplum source code

2015-11-01 Thread Josh Berkus 
On 10/31/2015 11:47 PM, Bruce Momjian wrote:
> On Sun, Nov  1, 2015 at 01:27:13AM -0500, Bruce Momjian wrote:
>> On Fri, Oct 30, 2015 at 04:47:35AM -0400, Bruce Momjian wrote:
>>> Therefore, I caution people from viewing the Greenplum source code as
>>> you might see patented ideas that could be later implemented in
>>> Postgres, opening Postgres up to increased patent violation problems.  I
>>> am also concerned about existing community members who work for
>>> Pivotal/Greenplum and therefore are required to view the patented source
>>> code.  The license issue might eventually be improved by
>>> Pivotal/Greenplum, but, for now, I think caution is necessary.

Do let me point out that *code* isn't patented.  *techniques* are.  So
those techniques are patented whether or not you read the code.  It's
just that if you read the code, copy the technique directly, and put it
in Postgres, that's considered "willful" instead of "innocent"
infringement and the penalties are different.  Its effect on our project
is the same, though: we have to rip out the code in a hurry.

Maybe we should just relicense PostgreSQL as Apache and cover all of the
patent issues ;-)

>>>
>>> Of course, never mention known-patented ideas in any community forum,
>>> including this email list.
>>
>> I just found out that Citus Data has patent applications pending, so
>> viewing Citus Data source code has the same problems as Greenplum.
> 
> Actually, it might only be their closed source software that contains
> patents, i.e. not pg_shard.  I will check and report back when I can
> unless someone else reports here first.

I will ask Citus Data for an official statement.  I will point out that
cstore_fdw is Apache-licensed, which also contains a patent grant.

-- 
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] September 2015 Commitfest

2015-11-01 Thread Jim Nasby 

On 10/31/15 11:19 AM, Nathan Wagner wrote:

>You could imagine putting something into the standard makefiles
>that did some subset of this, but I think it would be too rigid
>to be useful.

I think it would be very useful to just be able to tell the system "fire
this up for me so I can test it".  I don't think it needs to handle
every possible testing scenario, just making it easier to leave up the
test postmaster from make check would be very useful, at least to me.


I've wished that the cluster setup and teardown behavior of pg_regress 
was available outside of pg_regress itself. Would that mostly suffice 
for what you're looking for?

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

[HACKERS] Why not to use 'pg_ctl start -D ../data' to register posgtresql windows service

2015-11-01 Thread YuanyuanLiu 
Hi, Hackers!

  Nowadays, I always got problem about postgresql windows service.
I find, postgresql usually use the following command to register windows
services:
/"D:\Program Files (x86)\PostgreSQL\9.4\bin\pg_ctl.exe" runservice -N
"postgresql-9.4" -D "D:\Program Files (x86)\PostgreSQL\9.4\data" -w/
   But this command always failed, beacause of user privilege, just as a
topic described:
  
http://postgresql.nabble.com/pg-ctl-could-not-start-service-quot-pgsql-8-2-quot-error-code-1063-td1880801.html#a1880802
  While 'pg_ctl start -D ../data' works well, why not to use this command to
register postgresql windows service? why to use 'pg_ctl runservice -N "psql"
-D ".../data"' ? This is really confused me when I use postgresql in
windows.

   Hope to hear from you!
   Regards!
   
   Liu Yuanyuan




--
View this message in context: 
http://postgresql.nabble.com/Why-not-to-use-pg-ctl-start-D-data-to-register-posgtresql-windows-service-tp5872282.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] remove wal_level archive

2015-11-01 Thread Michael Paquier 
On Mon, Nov 2, 2015 at 2:21 PM, Craig Ringer  wrote:
> On 1 September 2015 at 10:39, Peter Eisentraut  wrote:
>> So we've had several rounds of discussions about simplifying replication
>> configuration in general and the wal_level setting in particular. [0][1]
>>
>> [snip]
>>
>> Bike-shedding:  In this patch, I removed "archive" and kept
>> "hot_standby", because that's what the previous discussions suggested.
>> Historically and semantically, it would be more correct the other way
>> around.  On the other hand, keeping "hot_standby" would probably require
>> fewer configuration files to be changed.  Or we could keep both, but
>> that would be confusing (for users and in the code).
>
> We need to keep both, IMO, with 'archive' as an obsolete synonym for
> hot_standby.
>
> Otherwise pg_upgrade will get grumpy, and so will users who migrate
> their configurations.

pg_upgradecluster has some logic to switch a parameter value (see
strrepl), and pg_upgrade does not handle parameter name switches by
itself, so the price to pay would be more maintenance annoyance for
existing upgrade scripts, which happens at more or less each major
release (checkpoint_segments removed in 9.5, unix_socket_directory
renamed in 9.3, etc.).
-- 
Michael


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

Re: [HACKERS] Why not to use 'pg_ctl start -D ../data' to register posgtresql windows service

2015-11-01 Thread Michael Paquier 
On Mon, Nov 2, 2015 at 2:54 PM, YuanyuanLiu  wrote:
>   Nowadays, I always got problem about postgresql windows service.
> I find, postgresql usually use the following command to register windows
> services:
> /"D:\Program Files (x86)\PostgreSQL\9.4\bin\pg_ctl.exe" runservice -N
> "postgresql-9.4" -D "D:\Program Files (x86)\PostgreSQL\9.4\data" -w/
>But this command always failed, beacause of user privilege, just as a
> topic described:
> http://postgresql.nabble.com/pg-ctl-could-not-start-service-quot-pgsql-8-2-quot-error-code-1063-td1880801.html#a1880802
>   While 'pg_ctl start -D ../data' works well, why not to use this command to
> register postgresql windows service? why to use 'pg_ctl runservice -N "psql"
> -D ".../data"' ? This is really confused me when I use postgresql in
> windows.

runservice is not a command that is aimed to be run manually, and
that's what your link above refers to. A service should be registered
using register, then you should use sc to start the service if you are
doing it manually (you may do it automatically that's the default
behavior then there is no need to use sc):
pg_ctl register -N pg_service_name -D %PGDATA% -S demand
sc start pg_service_name

Now we could make the code more complicated in the start path to
support that. And some users may want to be able to start a server on
Windows without having to set up a service that lasts only for the
time of a session. For example sometimes I do so for testing purposes.
So that's quite handy to not rely on the Windows SCM in this case.
-- 
Michael


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

Re: [HACKERS] remove wal_level archive

2015-11-01 Thread Craig Ringer 
On 1 September 2015 at 10:39, Peter Eisentraut  wrote:
> So we've had several rounds of discussions about simplifying replication
> configuration in general and the wal_level setting in particular. [0][1]
>
> [snip]
>
> Bike-shedding:  In this patch, I removed "archive" and kept
> "hot_standby", because that's what the previous discussions suggested.
> Historically and semantically, it would be more correct the other way
> around.  On the other hand, keeping "hot_standby" would probably require
> fewer configuration files to be changed.  Or we could keep both, but
> that would be confusing (for users and in the code).

We need to keep both, IMO, with 'archive' as an obsolete synonym for
hot_standby.

Otherwise pg_upgrade will get grumpy, and so will users who migrate
their configurations.

-- 
 Craig Ringer   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] Re: BUG #13685: Archiving while idle every archive_timeout with wal_level hot_standby

2015-11-01 Thread Jeff Janes 
The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:   tested, passed
Spec compliant:   not tested
Documentation:not tested

Simple patch, applies and makes cleanly, does what it says and says what it 
does.

If a transaction holding locks aborts on an otherwise idle server, perhaps it 
will take a very long time for a log-shipping standby to realize this.  But I 
have hard time believing that anyone who cares about that would be using 
log-shipping (rather than streaming) anyway.

Marking it ready for committer.

The new status of this patch is: Ready for Committer


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

Re: [HACKERS] Re: BUG #13685: Archiving while idle every archive_timeout with wal_level hot_standby

2015-11-01 Thread Michael Paquier 
On Mon, Nov 2, 2015 at 2:58 PM, Jeff Janes  wrote:
> Simple patch, applies and makes cleanly, does what it says and says what it 
> does.
>
> If a transaction holding locks aborts on an otherwise idle server, perhaps it 
> will take a very long time for a log-shipping standby to realize this.  But I 
> have hard time believing that anyone who cares about that would be using 
> log-shipping (rather than streaming) anyway.
>
> Marking it ready for committer.
>
> The new status of this patch is: Ready for Committer

Thanks! That was deadly fast.

Just wondering: shouldn't we keep the discussion around this patch on
-bugs instead? Not saying you are wrong, Jeff, I am just not sure what
would be the best practice regarding patches related to bugs. I would
think that it is at least necessary to keep the person who reported
the bug in CC to let him know the progress though.
-- 
Michael


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

[HACKERS] Re: Why not to use 'pg_ctl start -D ../data' to register posgtresql windows service

2015-11-01 Thread YuanyuanLiu 
Hi, Michael!
Thanks for your reply!

I knew that postgresql windows service is made by "pg_ctl register -N
serviceName -D dataPath".
And  command from service, like "D:\Program Files
(x86)\PostgreSQL\9.4\bin\pg_ctl.exe" runservice -N "postgres-9.4" -D
"D:\Program Files (x86)\PostgreSQL\9.4\data", cannot run manually like
command line.

I have a conclusion:
When we registered postgresql windows service named "postgres-9.4",
we can start the postgresql server by manually  starting the windows service
, runing command "net stop postgres-9.4" (need user with administrator
privilege), or runing command "pg_ctl start -D ../path/to/data".
The started postgresql service must be stopped in the same session.
Is this right ?

Looking forward to your reply!
Regard!
Liu Yuanyuan






--
View this message in context: 
http://postgresql.nabble.com/Why-not-to-use-pg-ctl-start-D-data-to-register-posgtresql-windows-service-tp5872282p5872294.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Re: BUG #13685: Archiving while idle every archive_timeout with wal_level hot_standby

2015-11-01 Thread Jeff Janes 
On Sun, Nov 1, 2015 at 11:09 PM, Michael Paquier
 wrote:
> On Mon, Nov 2, 2015 at 2:58 PM, Jeff Janes  wrote:
>> Simple patch, applies and makes cleanly, does what it says and says what it 
>> does.
>>
>> If a transaction holding locks aborts on an otherwise idle server, perhaps 
>> it will take a very long time for a log-shipping standby to realize this.  
>> But I have hard time believing that anyone who cares about that would be 
>> using log-shipping (rather than streaming) anyway.
>>
>> Marking it ready for committer.
>>
>> The new status of this patch is: Ready for Committer
>
> Thanks! That was deadly fast.
>
> Just wondering: shouldn't we keep the discussion around this patch on
> -bugs instead? Not saying you are wrong, Jeff, I am just not sure what
> would be the best practice regarding patches related to bugs. I would
> think that it is at least necessary to keep the person who reported
> the bug in CC to let him know the progress though.

I wasn't sure about -bugs vs -hackers either, but in this case I used
the review form built into the commit-fest app, and the app is what
sent the email.  As far as I know I can't change its destination or
its CC list, even if I had thought ahead to do so.

I think the bug reporter should certainly be CCed when the bug is
closed, I don't know about intermediate steps in the "sausage making"
process.  Something to think about for a bug-tracker we might
implement in the future.  I think most bugs are summarily handled by
committers, so don't go through the commitfest process at all.

Cheers,

Jeff


-- 
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] Request: pg_cancel_backend variant that handles 'idle in transaction' sessions

2015-11-01 Thread Pavel Stehule 
2015-11-02 5:23 GMT+01:00 Amit Kapila :

> On Sun, Nov 1, 2015 at 11:34 PM, Tom Lane  wrote:
> >
> > Magnus Hagander  writes:
> > > On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila 
> > > wrote:
> > >> Why pg_cancel_backend(pid) is not sufficient for the above use case?
> > >> Basically you want to rollback current transaction, I think that can
> be
> > >> achieved by pg_cancel_backend.
> >
> > > Not when the session is idle in transaction, only when it's actually
> doing
> > > something.
> >
>
> Okay, thats right and the reason is that while reading message from client,
> if an error occurs, it can loose track of previous and next messages and
> that
> could lead to an unrecoverable state.
>
> >
> > I think in principle it could be done by transitioning the backend into
> > a new xact.c state, wherein we know that the active transaction has been
> > canceled (at least to the extent of releasing externally visible
> resources
> > such as locks and snapshots), but this fact hasn't been reported to the
> > connected client.  Then the next command submitted by the client would
> get
> > a "transaction cancelled" error and we'd go into the normal transaction-
> > failed state.
> >
>
> That sounds to be a solution for this problem or otherwise for such a case
> can't we completely abort the active transaction and set a flag like
> PrevCommandFailed/PrevTransFailed and on receiving next message if
> such a flag is set, then throw an appropriate error.
>

This is only partial solution - when some application is broken, then there
will be orphaned sessions. It is less wrong, than orphaned connections, but
it can enforce some issues too. The solution of this problem should to work
well with session pool sw like pgbouncer and similar.

Regards

Pavel


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