Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Joel Jacobson]

On Sun, Mar 13, 2016 at 12:40 AM, Tom Lane  wrote:
> In short: we've already been over this territory, at length,
> and I am not excited by people trying to bikeshed it again
> after the fact, especially when no new arguments are being
> presented.  Can we call the discussion closed, please?

Closed, at least from my side.

I'm grateful to have learned at least a bit more about when it's OK
to sacrifice backwards-compatibility.

Sorry for spamming this thread on that topic,
I'll instead wade through the archives to see what more I can learn
to hopefully become less confused.

Thanks.


-- 
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: Provide much better wait information in pg_stat_activity.

[Tom Lane]

Robert Haas  writes:
> You could also argue that's a compatibility break, because people may
> have logic that assumes that a wait is always a heavyweight lock wait.
> If we keep the column but change the meaning, people who need to
> update their scripts may fail to notice.  Hard breaks aren't that fun,
> but at least you don't fail to notice that something needs to be
> changed.

Yes.  My recollection of the argument for the earlier renames of
pg_stat_activity columns is that it was basically the same thing:
we changed the semantics of these columns, you are very likely to
need to adjust your queries, so we'll change the column names to
make sure you notice.  There's always a tradeoff there.  Maybe you
won't need to adjust your queries, but maybe they'll break silently.

In this case I agree with the feeling that people probably took
waiting == true as an indication that there was a matching entry
in pg_locks, so the odds of subtle breakage if we keep the name
the same while changing the semantics are pretty high.  Or we
could keep the semantics the same (waiting is true only for
heavyweight-lock waits) but that was mighty ugly too.

In short: we've already been over this territory, at length,
and I am not excited by people trying to bikeshed it again
after the fact, especially when no new arguments are being
presented.  Can we call the discussion closed, please?

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] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Robert Haas]

On Fri, Mar 11, 2016 at 6:31 PM, Jim Nasby  wrote:
> On 3/10/16 8:36 PM, Robert Haas wrote:
>> 1. We make it true only for heavyweight lock waits, and false for
>> other kinds of waits.  That's pretty strange.
>> 2. We make it true for all kinds of waits that we now know how to
>> report.  That still breaks compatibility.
>
>
> I would absolutely vote for 2 here. You could even argue that it's a bug
> fix, since those were waits we technically should have been indicating.

You could also argue that's a compatibility break, because people may
have logic that assumes that a wait is always a heavyweight lock wait.
If we keep the column but change the meaning, people who need to
update their scripts may fail to notice.  Hard breaks aren't that fun,
but at least you don't fail to notice that something needs to be
changed.

-- 
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] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Amit Kapila]

On Sat, Mar 12, 2016 at 5:01 AM, Jim Nasby  wrote:
>
> On 3/10/16 8:36 PM, Robert Haas wrote:
>>
>> 1. We make it true only for heavyweight lock waits, and false for
>> other kinds of waits.  That's pretty strange.
>> 2. We make it true for all kinds of waits that we now know how to
>> report.  That still breaks compatibility.
>
>
> I would absolutely vote for 2 here. You could even argue that it's a bug
fix, since those were waits we technically should have been indicating.
>

I see it as reverse.  I think waiting=true for only heavyweight locks makes
sense in existing versions as user can still find whats actually going in
the system either by looking at "query" in pg_stat_activity or by referring
pg_locks, but OTOH if waiting is true for all kind of waits (lwlock,
heavyweight lock, I/O, etc) then I think it will be difficult for user to
make any sense out of it.  So I see going for option 2 can confuse users
rather than simplifying anything.

>
> Another random thought... changes like this would probably be easier to
handle if we provided backwards compatibility extensions that created views
> that mimicked the catalog for a specific Postgres version.
>

That makes sense to me if other people agree to it, but I think there will
be some maintenance overhead for it, but I see that as worth the effort in
terms of user convenience.

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

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Joel Jacobson]

On Sat, Mar 12, 2016 at 6:31 AM, Jim Nasby  wrote:
> On 3/10/16 8:36 PM, Robert Haas wrote:
>>
>> 1. We make it true only for heavyweight lock waits, and false for
>> other kinds of waits.  That's pretty strange.
>> 2. We make it true for all kinds of waits that we now know how to
>> report.  That still breaks compatibility.
>
>
> I would absolutely vote for 2 here. You could even argue that it's a bug
> fix, since those were waits we technically should have been indicating.
>
> The only way I can see #2 breaking anything is if you're using waiting=true
> to determine whether you look at pg_locks and your code will blow up if you
> get no rows back, but that seems like a pretty limited use case to me
> (Hello, LEFT JOIN).
>
> Dropping the column entirely though would break tons of things.

That was a very good point I hadn't thought about. In that case, +1 to keep it.

I also came to think of the renaming of pg_stat_activity.procpid ->
pg_stat_activity.pid,
which was renamed because "backwards compatibility was broken anyway"
(due to current_query -> query).

I wouldn't rule out there were users who didn't use the
"current_query" column but *did* use "procpid",
who wouldn't have been affected if the procpid column hadn't been
renamed as well.

Apparently in this case, it was OK to break even more things than
necessary, since another things was already broken.

I don't have any opinion whether doing so was a bad or good decision,
it all depends on what the project's objectives are.
Unfortunately, it feels like the case-by-case basis decision model
lead to conflicting arguments, if they would be compared side-by-side.

I really think the project need a policy here on how, why and when
it's OK to break backwards-compatibility.
Such a policy won't cover all cases of course, but if a consensus can
be made on the basic principles,
then discussion can be focused on the details and cases not covered by
the basic principles,
instead of "end up hashing it out on a case-by-case basis".


-- 
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: Provide much better wait information in pg_stat_activity.

[Jim Nasby]

On 3/10/16 8:36 PM, Robert Haas wrote:

1. We make it true only for heavyweight lock waits, and false for
other kinds of waits.  That's pretty strange.
2. We make it true for all kinds of waits that we now know how to
report.  That still breaks compatibility.


I would absolutely vote for 2 here. You could even argue that it's a bug 
fix, since those were waits we technically should have been indicating.


The only way I can see #2 breaking anything is if you're using 
waiting=true to determine whether you look at pg_locks and your code 
will blow up if you get no rows back, but that seems like a pretty 
limited use case to me (Hello, LEFT JOIN).


Dropping the column entirely though would break tons of things.

Another random thought... changes like this would probably be easier to 
handle if we provided backwards compatibility extensions that created 
views that mimicked the catalog for a specific Postgres version.

--
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] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Pavel Stehule]

2016-03-11 23:22 GMT+01:00 Joel Jacobson :

> On Sat, Mar 12, 2016 at 5:09 AM, Pavel Stehule 
> wrote:
> >> What we need is more input on proposed changes from other companies
> >> who are also heavy users of PL/pgSQL.
> >>
> >> Only then can we move forward. It's like Robert is saying, there is a
> >> risk for bikeshedding here,
> >> we must widen our perspectives and get better understanding for how
> >> other heavy users are using PL/pgSQL.
> >
> >
> > I disagree with this opinion - this is community sw, not commercial. We
> can
> > do nothing if we don't find a agreement.
>
> I disagree with your disagreement.
>
> The users are what matters, and many of them are of course not on this
> list (since this is a list for hackers), so we need to reach out to
> the users, and those are companies/websites/nonprofits/governments.
> So discussing proposed changes on this list will take us absolutely
> nowhere, without further input from actual heavy users.
> Once we do have input from the heavy users, then and only then can we
> continue discussing things on this list, but before then it's kind of
> pointless, because we don't know what the most commonly proposed
> changes are, not you, not me. The risk of bikeshedding is just too
> big, like Robert pointed out.
>

I sent a list of requested features. Really, I have not any request from
companies when I worked, did training, consultations for less verbosity or
significant changes in languages. The people miss the features from Oracle,
MSSQL.


>
> >> Pavel, do you know of any such companies?
> > Probably the biggest company with pretty large code of PL/pgSQL was
> Skype,
> > but I have not any info about current state.
>
> True! I had almost forgotten about them after Microsoft acquired them.
> Let's hope they are still on PostgreSQL. I'll check it out, thanks.
>

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Joel Jacobson]

On Sat, Mar 12, 2016 at 5:09 AM, Pavel Stehule  wrote:
>> What we need is more input on proposed changes from other companies
>> who are also heavy users of PL/pgSQL.
>>
>> Only then can we move forward. It's like Robert is saying, there is a
>> risk for bikeshedding here,
>> we must widen our perspectives and get better understanding for how
>> other heavy users are using PL/pgSQL.
>
>
> I disagree with this opinion - this is community sw, not commercial. We can
> do nothing if we don't find a agreement.

I disagree with your disagreement.

The users are what matters, and many of them are of course not on this
list (since this is a list for hackers), so we need to reach out to
the users, and those are companies/websites/nonprofits/governments.
So discussing proposed changes on this list will take us absolutely
nowhere, without further input from actual heavy users.
Once we do have input from the heavy users, then and only then can we
continue discussing things on this list, but before then it's kind of
pointless, because we don't know what the most commonly proposed
changes are, not you, not me. The risk of bikeshedding is just too
big, like Robert pointed out.

>> Pavel, do you know of any such companies?
> Probably the biggest company with pretty large code of PL/pgSQL was Skype,
> but I have not any info about current state.

True! I had almost forgotten about them after Microsoft acquired them.
Let's hope they are still on PostgreSQL. I'll check it out, thanks.


-- 
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: Provide much better wait information in pg_stat_activity.

[Pavel Stehule]

2016-03-11 22:48 GMT+01:00 Joel Jacobson :

> On Sat, Mar 12, 2016 at 4:41 AM, Pavel Stehule 
> wrote:
> > I afraid so you try to look on your use case as global/generic issue. The
> > PL/SQL, ADA. PL/pgSQL are verbose languages, and too shortcuts does the
> > languages dirty. In this point we have different opinion.
> >
> > I proposed some enhanced PLpgSQL API with a possibility to create a
> > extension that can enforce your requested behave. The implementation can
> not
> > be hard, and it can coverage some special/individual requests well.
>
> I'm not at all interested to discuss any of the proposed changes that
> have already been proposed,
> because we have already had lengthy discussions on them, and I doubt
> neither you nor me have nothing to add.
>
> What we need is more input on proposed changes from other companies
> who are also heavy users of PL/pgSQL.
>
> Only then can we move forward. It's like Robert is saying, there is a
> risk for bikeshedding here,
> we must widen our perspectives and get better understanding for how
> other heavy users are using PL/pgSQL.
>

I disagree with this opinion - this is community sw, not commercial. We can
do nothing if we don't find a agreement.


>
> Pavel, do you know of any such companies?
>

I know companies with pretty heavy PostgreSQL load and critical
applications, but with only ten thousands lines of PL/pgSQL. They has only
one request - stability. I talked with Oleg and with other people - and
common requests are

* session (global) variables
* global temp tables
* better checking of embedded SQL
* usual possibility to specify left part of assign statement

But these requests depends on development style - it is related to
classical usage of stored procedures - the people are interesting about it,
because they does porting from DB2 or Oracle to Postgres.

Probably the biggest company with pretty large code of PL/pgSQL was Skype,
but I have not any info about current state.

Regards

Pavel

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Joel Jacobson]

On Sat, Mar 12, 2016 at 4:48 AM, Joel Jacobson  wrote:
> neither you nor me have nothing to add.

Correction: neither you nor me have anything to add.


-- 
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: Provide much better wait information in pg_stat_activity.

[Joel Jacobson]

On Sat, Mar 12, 2016 at 4:41 AM, Pavel Stehule  wrote:
> I afraid so you try to look on your use case as global/generic issue. The
> PL/SQL, ADA. PL/pgSQL are verbose languages, and too shortcuts does the
> languages dirty. In this point we have different opinion.
>
> I proposed some enhanced PLpgSQL API with a possibility to create a
> extension that can enforce your requested behave. The implementation can not
> be hard, and it can coverage some special/individual requests well.

I'm not at all interested to discuss any of the proposed changes that
have already been proposed,
because we have already had lengthy discussions on them, and I doubt
neither you nor me have nothing to add.

What we need is more input on proposed changes from other companies
who are also heavy users of PL/pgSQL.

Only then can we move forward. It's like Robert is saying, there is a
risk for bikeshedding here,
we must widen our perspectives and get better understanding for how
other heavy users are using PL/pgSQL.

Pavel, do you know of any such companies?


-- 
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: Provide much better wait information in pg_stat_activity.

[Pavel Stehule]

2016-03-11 22:32 GMT+01:00 Joel Jacobson :

> On Sat, Mar 12, 2016 at 4:08 AM, Robert Haas 
> wrote:
> >
> > I don't think my experience in this area is as deep as you seem to
> > think.  I can tell you that most of the requests EnterpriseDB gets for
> > PL/pgsql enhancements involve wanting it to be more like Oracle's
> > PL/SQL, which of course has very little overlap with the stuff that
> > you're interested in.
>
> Do you know who could possibly be more experienced
> with companies who are heavy users of PL/pgSQL in the community?
>
> and/or,
>
> Do you know of any companies who officially are heavy users of PL/pgSQL?
>
> The only other company I can think of is Zalado, but of course there
> are many more,
> I just wish I knew their names, because I want to compile a wish list with
> proposed changes from as many companies who are heavy users of
> PL/pgSQL as possible.
>
> That's the only way to push this forward. As you say, we need a
> consensus and input
> from a broad range of heavy users, not just from people on this list
> with feelings
> and opinions who might not actually be heavy users themselves.
>
> Of course almost everybody on this list uses PL/pgSQL from time to
> time or even daily,
> but it's a completely different thing to write an entire backend
> system in the language,
> it's first then when you start to become really excited of e.g. not
> having to type
> at least 30 characters of text every time you do an UPDATE/INSERT
> to be sure you modified exactly one row.
>

I afraid so you try to look on your use case as global/generic issue. The
PL/SQL, ADA. PL/pgSQL are verbose languages, and too shortcuts does the
languages dirty. In this point we have different opinion.

I proposed some enhanced PLpgSQL API with a possibility to create a
extension that can enforce your requested behave. The implementation can
not be hard, and it can coverage some special/individual requests well.

Regards

Pavel

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Pavel Stehule]

2016-03-11 22:08 GMT+01:00 Robert Haas :

> On Fri, Mar 11, 2016 at 3:44 PM, Joel Jacobson  wrote:
> > On Fri, Mar 11, 2016 at 11:14 AM, Robert Haas 
> wrote:
> >> I'm not direly opposed to most of what's on that page,
> >> but I'm not excited about most of it, either.
> >
> > May I ask, what improvements of PL/pgSQL would you personally be most
> > excited about,
> > if you or someone else would have unlimited resources to hack on it?
> >
> >> I bet if we canvassed 10 different companies that made heavy use of
> PL/pgsql they'd all have
> >> a list of proposed changes like that, and I bet some of them would
> >> conflict with each other, and I bet if we did all that stuff the
> >> average PL/pgsql user's life would not be much better, but the manual
> >> would be much longer.
> >
> > You as a professional PostgreSQL consultant obviously have a lot of more
> > contact than me with other companies who make heavy use of PL/pgSQL.
> >
> > I'm assuming your bet on these proposed changes in conflict you talk
> about
> > are based on things you've picked up IRL from companies you've been
> > working with.
> >
> > What would you say are the top most commonly proposed changes
> > from companies that make heavy use of PL/pgSQL, and which of those are
> > in conflict?
>
> I don't think my experience in this area is as deep as you seem to
> think.  I can tell you that most of the requests EnterpriseDB gets for
> PL/pgsql enhancements involve wanting it to be more like Oracle's
> PL/SQL, which of course has very little overlap with the stuff that
> you're interested in.  But I'm not really commenting here based on
> that.  I'm just giving you my impression based on the discussion I've
> seen on the mailing list and my own personal feelings.  If there is an
> outcry for STRICT as you have proposed it, I'm not especially opposed
> to that.  I just think it needs a consensus that I haven't seen
> emerge.
>

This proposal is not bad from my perspective - but in detail these points
breaks compatibility, can have negative performance impacts or are ugly. I
understand to all points, but I am not sure, if the benefits are better
than costs (compatibility issues).

Can we talk about these points in separate thread? I can imagine few points
as extra checks. This is probably theme for 9.6, so we can discuss about it
in 9.6 release cycle.

Regards

Pavel

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

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Joel Jacobson]

On Sat, Mar 12, 2016 at 4:08 AM, Robert Haas  wrote:
>
> I don't think my experience in this area is as deep as you seem to
> think.  I can tell you that most of the requests EnterpriseDB gets for
> PL/pgsql enhancements involve wanting it to be more like Oracle's
> PL/SQL, which of course has very little overlap with the stuff that
> you're interested in.

Do you know who could possibly be more experienced
with companies who are heavy users of PL/pgSQL in the community?

and/or,

Do you know of any companies who officially are heavy users of PL/pgSQL?

The only other company I can think of is Zalado, but of course there
are many more,
I just wish I knew their names, because I want to compile a wish list with
proposed changes from as many companies who are heavy users of
PL/pgSQL as possible.

That's the only way to push this forward. As you say, we need a
consensus and input
from a broad range of heavy users, not just from people on this list
with feelings
and opinions who might not actually be heavy users themselves.

Of course almost everybody on this list uses PL/pgSQL from time to
time or even daily,
but it's a completely different thing to write an entire backend
system in the language,
it's first then when you start to become really excited of e.g. not
having to type
at least 30 characters of text every time you do an UPDATE/INSERT
to be sure you modified exactly one row.


-- 
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: Provide much better wait information in pg_stat_activity.

[Robert Haas]

On Fri, Mar 11, 2016 at 3:44 PM, Joel Jacobson  wrote:
> On Fri, Mar 11, 2016 at 11:14 AM, Robert Haas  wrote:
>> I'm not direly opposed to most of what's on that page,
>> but I'm not excited about most of it, either.
>
> May I ask, what improvements of PL/pgSQL would you personally be most
> excited about,
> if you or someone else would have unlimited resources to hack on it?
>
>> I bet if we canvassed 10 different companies that made heavy use of PL/pgsql 
>> they'd all have
>> a list of proposed changes like that, and I bet some of them would
>> conflict with each other, and I bet if we did all that stuff the
>> average PL/pgsql user's life would not be much better, but the manual
>> would be much longer.
>
> You as a professional PostgreSQL consultant obviously have a lot of more
> contact than me with other companies who make heavy use of PL/pgSQL.
>
> I'm assuming your bet on these proposed changes in conflict you talk about
> are based on things you've picked up IRL from companies you've been
> working with.
>
> What would you say are the top most commonly proposed changes
> from companies that make heavy use of PL/pgSQL, and which of those are
> in conflict?

I don't think my experience in this area is as deep as you seem to
think.  I can tell you that most of the requests EnterpriseDB gets for
PL/pgsql enhancements involve wanting it to be more like Oracle's
PL/SQL, which of course has very little overlap with the stuff that
you're interested in.  But I'm not really commenting here based on
that.  I'm just giving you my impression based on the discussion I've
seen on the mailing list and my own personal feelings.  If there is an
outcry for STRICT as you have proposed it, I'm not especially opposed
to that.  I just think it needs a consensus that I haven't seen
emerge.

-- 
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] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Joel Jacobson]

On Fri, Mar 11, 2016 at 11:14 AM, Robert Haas  wrote:
> I'm not direly opposed to most of what's on that page,
> but I'm not excited about most of it, either.

May I ask, what improvements of PL/pgSQL would you personally be most
excited about,
if you or someone else would have unlimited resources to hack on it?

> I bet if we canvassed 10 different companies that made heavy use of PL/pgsql 
> they'd all have
> a list of proposed changes like that, and I bet some of them would
> conflict with each other, and I bet if we did all that stuff the
> average PL/pgsql user's life would not be much better, but the manual
> would be much longer.

You as a professional PostgreSQL consultant obviously have a lot of more
contact than me with other companies who make heavy use of PL/pgSQL.

I'm assuming your bet on these proposed changes in conflict you talk about
are based on things you've picked up IRL from companies you've been
working with.

What would you say are the top most commonly proposed changes
from companies that make heavy use of PL/pgSQL, and which of those are
in conflict?


-- 
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: Provide much better wait information in pg_stat_activity.

[Pavel Stehule]

>
> Yes, I think we use this rubric quite often, and I agree it's a good one.
>
> > Trying to e.g. select a different number of columns into a different
> > number of variables in a PL/pgSQL function doesn't throw an error.
> > Bad. :(
>
> Yeah, I'm sympathetic to that request.  That seems like poor error
> checking and nothing else.
>
> (But note that I do not rule here.)
>

I am not sure, but maybe this issue is covered by plpgsql_check. But not
possible to check it when dynamic SQL is used.

Pavel


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

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Pavel Stehule]

2016-03-11 0:17 GMT+01:00 Tom Lane :

> Robert Haas  writes:
> > Or ... maybe this is intentional behavior?  Now that I think about it,
> > doesn't each backend cache this info the first time its transaction
> > reads the data?
>
> Your view of pg_stat_activity is supposed to hold still within a
> transaction, yes.  Otherwise it'd be really painful to do any complicated
> joins.  I think there may be a function to explicitly flush the cache,
> if you really need to see intratransaction changes.
>

I understand.

This behave has impact on PL functions that try to repeated check of
pg_stat_activity. But this use case is not frequent.

Thank you.

Regards

Pavel



> regards, tom lane
>

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Robert Haas]

On Thu, Mar 10, 2016 at 10:49 PM, Joel Jacobson  wrote:
> On Fri, Mar 11, 2016 at 9:36 AM, Robert Haas  wrote:
>> Well, this was discussed.  If we keep the Boolean "waiting" column, then 
>> either:
>
> Oh, sorry for missing out on that discussion.
>
>> 1. We make it true only for heavyweight lock waits, and false for
>> other kinds of waits.  That's pretty strange.
>> 2. We make it true for all kinds of waits that we now know how to
>> report.  That still breaks compatibility.
>
> Why not 3: We make it true for exactly the same type of situations as
> in previous versions. Or is it not possible to do so for some reason?

3 = 1.

> Off topic, but related to the backward-compatibility subject:
>
> Is there any written policy/wiki/thread/document on the topic "When
> breaking backward-compatibility is acceptable"?

Not to my knowledge.  We end up hashing it out on a case-by-case basis.

> It would be helpful to get a better understand of this, as some ideas
> on how to improve things can quickly be ruled out or ruled in
> depending on what is acceptable or not.
> For instance, there are some minor but annoying flaws in PL/pgSQL that
> I would love to get fixed,
> but the main arguments against doing so have been that it might break
> some users' code somewhere,
> even though doing so would probably be a good thing as the user could
> have a bug in the code.
> See: https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014)

I think that with respect to this particular set of improvements, the
problem is basically that there are just a lot of things that you
could hypothetically change, and it's not altogether clear which ones
of those individually would please more people than they displeased,
and it's not clear how much change we want to allow in total for the
sake of preserving backward compatibility, and then, too, the designs
for a lot of the individual features are fertile ground for
bikeshedding.  I'm not direly opposed to most of what's on that page,
but I'm not excited about most of it, either.  I bet if we canvassed
10 different companies that made heavy use of PL/pgsql they'd all have
a list of proposed changes like that, and I bet some of them would
conflict with each other, and I bet if we did all that stuff the
average PL/pgsql user's life would not be much better, but the manual
would be much longer.

(Also, I bet the variable assignments thing would break large amounts
of code that is working as designed.)

> I think one general rule should be "Breaking backward-compatibility is
> acceptable if the new major pg-version throws an error in a situation
> where the old major pg-version would conceal a bug or allow misuse of
> a feature".
> Trying to select the now removed "waiting" column throws an error.
> Good! That lead me as a user here to figure out why I can't and
> shouldn't use it. :)

Yes, I think we use this rubric quite often, and I agree it's a good one.

> Trying to e.g. select a different number of columns into a different
> number of variables in a PL/pgSQL function doesn't throw an error.
> Bad. :(

Yeah, I'm sympathetic to that request.  That seems like poor error
checking and nothing else.

(But note that I do not rule here.)

-- 
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] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Amit Kapila]

On Fri, Mar 11, 2016 at 9:19 AM, Joel Jacobson  wrote:
>
> On Fri, Mar 11, 2016 at 9:36 AM, Robert Haas 
wrote:
> > Well, this was discussed.  If we keep the Boolean "waiting" column,
then either:
>
> Oh, sorry for missing out on that discussion.
>
> > 1. We make it true only for heavyweight lock waits, and false for
> > other kinds of waits.  That's pretty strange.
> > 2. We make it true for all kinds of waits that we now know how to
> > report.  That still breaks compatibility.
>
> Why not 3: We make it true for exactly the same type of situations as
> in previous versions. Or is it not possible to do so for some reason?
>

Thats exactly the first point (1) of Robert.  One thing that will be
strange according to me is that in some cases where waiting will be false,
but still wait_event and wait_event_type contain some wait information and
I think that will look odd to anybody new looking at the view.

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

Re: [HACKERS] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Joel Jacobson]

On Fri, Mar 11, 2016 at 9:36 AM, Robert Haas  wrote:
> Well, this was discussed.  If we keep the Boolean "waiting" column, then 
> either:

Oh, sorry for missing out on that discussion.

> 1. We make it true only for heavyweight lock waits, and false for
> other kinds of waits.  That's pretty strange.
> 2. We make it true for all kinds of waits that we now know how to
> report.  That still breaks compatibility.

Why not 3: We make it true for exactly the same type of situations as
in previous versions. Or is it not possible to do so for some reason?

> I do understand that changing this is backward-incompatible and a lot
> of people are going to have to update their monitoring tools.  But I
> think that's the best alternative.  If we choose option #1, we're
> going to be saddled with a weird backward-compatibility column
> forever, and ten years from now we'll be explaining that even if
> waiting = false you might still be waiting depending on the value of
> some other column.  If we choose option #2, it won't be
> backward-compatible and some people's queries will still break, just
> less obviously.  Neither of those things seems very appealing.

I understand it's necessary to break backward-compatibility if the
it's not possible to return the same boolean value for the "waiting"
column in exactly the same situations.
Actually, even if it would be possible, I agree with you it's better
to force people to learn how to improve their tools by using the new
features.

Off topic, but related to the backward-compatibility subject:

Is there any written policy/wiki/thread/document on the topic "When
breaking backward-compatibility is acceptable"?

It would be helpful to get a better understand of this, as some ideas
on how to improve things can quickly be ruled out or ruled in
depending on what is acceptable or not.
For instance, there are some minor but annoying flaws in PL/pgSQL that
I would love to get fixed,
but the main arguments against doing so have been that it might break
some users' code somewhere,
even though doing so would probably be a good thing as the user could
have a bug in the code.
See: https://wiki.postgresql.org/wiki/Improving_PL/PgSQL_(September_2014)

I think one general rule should be "Breaking backward-compatibility is
acceptable if the new major pg-version throws an error in a situation
where the old major pg-version would conceal a bug or allow misuse of
a feature".
Trying to select the now removed "waiting" column throws an error.
Good! That lead me as a user here to figure out why I can't and
shouldn't use it. :)
Trying to e.g. select a different number of columns into a different
number of variables in a PL/pgSQL function doesn't throw an error.
Bad. :(
Here I would argue it's better to throw an error, just like when
trying to select from "waiting". It will hopefully save the day for
some users out there who can't find the bug in their complicated
PL/pgSQL application with millions of lines of code.

Sorry if this was completely off-topic, maybe I should start a new
thread or read some old thread in the archives on
backward-compatibility instead.


-- 
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: Provide much better wait information in pg_stat_activity.

[Robert Haas]

On Thu, Mar 10, 2016 at 8:31 PM, Joel Jacobson  wrote:
> This is an excellent feature, thanks!
> But can we please keep the old boolean waiting column?
> I see no reason to break backward-compatibility. Or maybe I'm missing 
> something.

Well, this was discussed.  If we keep the Boolean "waiting" column, then either:

1. We make it true only for heavyweight lock waits, and false for
other kinds of waits.  That's pretty strange.
2. We make it true for all kinds of waits that we now know how to
report.  That still breaks compatibility.

I do understand that changing this is backward-incompatible and a lot
of people are going to have to update their monitoring tools.  But I
think that's the best alternative.  If we choose option #1, we're
going to be saddled with a weird backward-compatibility column
forever, and ten years from now we'll be explaining that even if
waiting = false you might still be waiting depending on the value of
some other column.  If we choose option #2, it won't be
backward-compatible and some people's queries will still break, just
less obviously.  Neither of those things seems very appealing.

-- 
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] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Joel Jacobson]

This is an excellent feature, thanks!
But can we please keep the old boolean waiting column?
I see no reason to break backward-compatibility. Or maybe I'm missing something.

I just had to commit this to make our system run locally on 9.6:

commit 2e189f85fa56724bec5c5cab2fcf0d2f3a4ce22a
Author: Joel Jacobson 
Date:   Fri Mar 11 08:19:52 2016 +0700

Make Have_Queries_Waiting() work with both <9.6 and >=9.6.

Apparently pg_stat_activity.waiting was removed by this commit:
  commit 53be0b1add7064ca5db3cd884302dfc3268d884e
  Author: Robert Haas 
  Date:   Thu Mar 10 12:44:09 2016 -0500

  Provide much better wait information in pg_stat_activity.

This forces us to do some ugly version checking to know which column to use.
I for one can think it would have been better to keep the old
boolean column,
which is not entirely useless as sometimes you just want to know
if something is
waiting and don't care about the details, then it's convenient to
have a boolean column
instead of having to write "wait_event IS NOT NULL".

Let's hope they will add back our dear waiting column so we can avoid this
ugly hack before upgrading to 9.6.

diff --git a/public/FUNCTIONS/have_queries_waiting.sql
b/public/FUNCTIONS/have_queries_waiting.sql
index d83e7c8..b54caf5 100644
--- a/public/FUNCTIONS/have_queries_waiting.sql
+++ b/public/FUNCTIONS/have_queries_waiting.sql
@@ -3,9 +3,16 @@ SET search_path TO 'public', pg_catalog;
 CREATE OR REPLACE FUNCTION have_queries_waiting() RETURNS boolean
 SECURITY DEFINER
 SET search_path TO public, pg_temp
-LANGUAGE sql
+LANGUAGE plpgsql
 AS $$
-SELECT EXISTS (SELECT 1 FROM pg_stat_activity WHERE waiting)
+DECLARE
+BEGIN
+IF version() ~ '^PostgreSQL 9\.[1-5]' THEN
+RETURN EXISTS (SELECT 1 FROM pg_stat_activity WHERE waiting);
+ELSE
+RETURN EXISTS (SELECT 1 FROM pg_stat_activity WHERE wait_event IS
NOT NULL);
+END IF;
+END;
 $$;

On Fri, Mar 11, 2016 at 6:17 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> Or ... maybe this is intentional behavior?  Now that I think about it,
>> doesn't each backend cache this info the first time its transaction
>> reads the data?
>
> Your view of pg_stat_activity is supposed to hold still within a
> transaction, yes.  Otherwise it'd be really painful to do any complicated
> joins.  I think there may be a function to explicitly flush the cache,
> if you really need to see intratransaction changes.
>
> 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



-- 
Joel Jacobson

Mobile: +46703603801
Trustly.com | Newsroom | LinkedIn | Twitter


-- 
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: Provide much better wait information in pg_stat_activity.

[Tom Lane]

Robert Haas  writes:
> Or ... maybe this is intentional behavior?  Now that I think about it,
> doesn't each backend cache this info the first time its transaction
> reads the data?

Your view of pg_stat_activity is supposed to hold still within a
transaction, yes.  Otherwise it'd be really painful to do any complicated
joins.  I think there may be a function to explicitly flush the cache,
if you really need to see intratransaction changes.

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] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Robert Haas]

On Thu, Mar 10, 2016 at 5:07 PM, Robert Haas  wrote:
> On Thu, Mar 10, 2016 at 5:05 PM, Robert Haas  wrote:
>> On Thu, Mar 10, 2016 at 4:51 PM, Pavel Stehule  
>> wrote:
>>> Maybe it be clear from attached text file
>>
>> Uh, yikes, that looks messed up, but I wouldn't have thought this
>> commit would have changed anything there one way or the other.  The
>> current query is reported by pgstat_report_activity(), which I didn't
>> touch.  I think.
>
> I just tried this on 9.5 - changing the query only to "select pid,
> state, query from pg_stat_activity"  and doing everything else the
> same - and I see the same behavior there.  So it looks like this is a
> preexisting bug.

Or ... maybe this is intentional behavior?  Now that I think about it,
doesn't each backend cache this info the first time its transaction
reads the data?

-- 
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] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Robert Haas]

On Thu, Mar 10, 2016 at 5:05 PM, Robert Haas  wrote:
> On Thu, Mar 10, 2016 at 4:51 PM, Pavel Stehule  
> wrote:
>> Maybe it be clear from attached text file
>
> Uh, yikes, that looks messed up, but I wouldn't have thought this
> commit would have changed anything there one way or the other.  The
> current query is reported by pgstat_report_activity(), which I didn't
> touch.  I think.

I just tried this on 9.5 - changing the query only to "select pid,
state, query from pg_stat_activity"  and doing everything else the
same - and I see the same behavior there.  So it looks like this is a
preexisting bug.

-- 
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] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Robert Haas]

On Thu, Mar 10, 2016 at 4:51 PM, Pavel Stehule  wrote:
> Maybe it be clear from attached text file

Uh, yikes, that looks messed up, but I wouldn't have thought this
commit would have changed anything there one way or the other.  The
current query is reported by pgstat_report_activity(), which I didn't
touch.  I think.

-- 
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] [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.

[Pavel Stehule]

2016-03-10 22:24 GMT+01:00 Robert Haas :

>
> rhaas=# select query, state, wait_event, wait_event_type from
> pg_stat_activity;
>   query
>   | state  |  wait_event   | wait_event_type
>
> -++---+-
>  select query, state, wait_event, wait_event_type from
> pg_stat_activity; | active |   |
>  select * from foo for update;
>   | active | transactionid | Lock
> (2 rows)
>
> ...which looks right to me.
>
> > session two:
> > rollback; begin; select * from foo where a = 10 for update;
> > session two is waiting again
>
> I don't see how you can do this here - the session is blocked.
>
> There could well be a bug here, but I need a little more help to find it.
>

Maybe it be clear from attached text file

Regards



>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
=== <=== FIRST SESSION
postgres=# begin;
BEGIN
Time: 0.498 ms
postgres=# select * from foo for update;
┌┐
│ a  │
╞╡
│ 10 │
└┘
(1 row)

Time: 1.152 ms
==

** <=== SECOND SESSION
postgres=# begin;
BEGIN
Time: 0.426 ms
postgres=# select * from foo for update;
**

==
postgres=# select pid, wait_event, state, query from pg_stat_activity ;
┌───┬───┬┬──┐
│  pid  │  wait_event   │ state  │query 
│
╞═══╪═══╪╪══╡
│ 22870 │ transactionid │ active │ select * from foo for update;
│
│ 22874 │ ( null )  │ active │ select pid, wait_event, state, query from 
pg_stat_activity ; │
└───┴───┴┴──┘
(2 rows)

Time: 1.666 ms --- OOK
=

*
^CCancel request sent
ERROR:  57014: canceling statement due to user request
CONTEXT:  while locking tuple (0,1) in relation "foo"
LOCATION:  ProcessInterrupts, postgres.c:2977
Time: 121895.558 ms
postgres=# rollback;
ROLLBACK
Time: 0.648 ms
postgres=# begin;
BEGIN
Time: 0.461 ms
postgres=# select * from foo where a = 10 for update;
*

=
┌───┬───┬┬──┐
│  pid  │  wait_event   │ state  │query 
│
╞═══╪═══╪╪══╡
│ 22870 │ transactionid │ active │ select * from foo for update;
│ <=== expecting select * from foo where a = 10 for update
│ 22874 │ ( null )  │ active │ select pid, wait_event, state, query from 
pg_stat_activity ; │
└───┴───┴┴──┘
(2 rows)

Time: 1.421 ms


-- 
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: Provide much better wait information in pg_stat_activity.

[Robert Haas]

On Thu, Mar 10, 2016 at 3:44 PM, Pavel Stehule  wrote:
> I am trying to test this feature, and there I see not actual data. Maybe
> this behave is not related to this patch:
>
> create table foo(a int);
> insert into foo values(10);
>
> session one:
>
> begin; select * from foo for update;
>
> session two:
>
> begin; select * from foo for update;
> session two is waiting
>
> session one:
> select * from pg_stat_activity -- I don't see correct information about
> session two

At this point, I get:

rhaas=# select query, state, wait_event, wait_event_type from pg_stat_activity;
  query
  | state  |  wait_event   | wait_event_type
-++---+-
 select query, state, wait_event, wait_event_type from
pg_stat_activity; | active |   |
 select * from foo for update;
  | active | transactionid | Lock
(2 rows)

...which looks right to me.

> session two:
> rollback; begin; select * from foo where a = 10 for update;
> session two is waiting again

I don't see how you can do this here - the session is blocked.

There could well be a bug here, but I need a little more help to find it.

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


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