Re: [GENERAL] Installing 9.6 RC on Ubuntu [Solved]

[Adrian Klaver]

On 09/13/2016 06:17 PM, Steve Crawford wrote:



In case it is useful for reference, I beat my head on it a bit
more and
replaced "main" with "9.6":
deb http://apt.postgresql.org/pub/repos/apt/
 xenial-pgdg 9.6


Yes, that is an FAQ item:

https://wiki.postgresql.org/wiki/Apt/FAQ




Yes, but to quote Douglass Adams, "It was on display in the bottom of a
locked filing cabinet stuck in a disused lavatory with a sign on the
door saying ‘Beware of the Leopard'."


And now it is not, which was the purpose of the link. To show other 
folks that run across this thread where to go to get information.




If I might be so bold, one or more of the following would make life
easier on users and perhaps get more people to test:

1. Change the wording in the main postgresql.org 
announcement to be "Download RC1" and link directly
to https://www.postgresql.org/download/snapshots/

2. Bring non Red Hat releases up to parity.

By way of example, from the snapshots page clicking on the link under
Red Hat (and variants) takes you to http://yum.postgresql.org/ page
where 9.6 beta is right at the top. From there all you have to do is
select your release of choice.

By comparison, follow the https://apt.postgresql.org/ link under Debian,
Ubuntu Linux and you are redirected to the wiki where, if you are
fortunate, you will find "Have a look at the FAQ." nestled about
half-way down then dig through that page. I never got there but figured
it out by clicking through the file browser after wasting time seeing
lots of 9.6 utilities/addons already listed but broken due to the lack
of availability of the actual server and client.

I know this is open source. I know that people work on their "itch" or
what their employer sponsors. I'm just sharing the user experience
should it provide value and increase the number of testers.


About the 'itch' thing, it is a Wiki so you have the ability to scratch 
that itch. You can sign up for a community account:


https://www.postgresql.org/account/signup/

and then:

NOTE: due to recent spam activity "editor" privileges are granted 
manually for the time being. If you just created a new community account 
or if your current account used to have "editor" privileges ask on 
either the PostgreSQL -www Mailinglist or the PostgreSQL IRC Channel 
(direct your request to 'pginfra', multiple individuals in the channel 
highlight on that string) for help. Please include your community 
account name in those requests.






Cheers,
Steve




--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] select date between - PostgreSQL 9.5

[Patrick B]

2016-09-14 13:17 GMT+12:00 David Rowley :

> On 14 September 2016 at 12:20, Patrick B  wrote:
> > I want to select all rows that have been modified from now to 4 months
> ago.
> >
> > I've used these queries:
> >
> >> select
> >> modified_date,
> >> from
> >> clients
> >> WHERE
> >> modified_date BETWEEN '2016-06-13' AND '2016-09-13'
> >
>
> Going by my clock here 2016-06-13 was just over 3 months ago, not 4.
>
>
> >> select
> >> modified_date,
> >> from
> >> clients
> >> WHERE
> >> modified_date >='2016-06-13' AND modified_date < '2016-09-13'
> >
> >
> >
> > But it didn't work... it returns 0 rows but there are rows to be
> shown:
> >
> >
> >> select modified_date from clients ORDER BY modified_date ASC
> >
> >
> >
> >> modified_date
> >> ---
> >> 2015-07-11 17:23:40
> >> 2016-09-13 20:00:51
> >> 2016-09-13 20:00:51
> >> 2016-09-13 20:00:51
> >> 2016-09-13 20:00:51
> >
> >
> >
> > What am I doing wrong?
>
> None of those dates are between your specified date range. If you want
> to include all of 2016-09-13 timestamps, then you'd better do <
> '2016-09-14' since < '2016-09-13' will only cover timestamps on the
> 12th or before.
>
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>


Thanks guys...

I've used < and >

not sure why wasn't working before :(

Thanks!
Patrick

Re: [GENERAL] select date between - PostgreSQL 9.5

[David Rowley]

On 14 September 2016 at 12:20, Patrick B  wrote:
> I want to select all rows that have been modified from now to 4 months ago.
>
> I've used these queries:
>
>> select
>> modified_date,
>> from
>> clients
>> WHERE
>> modified_date BETWEEN '2016-06-13' AND '2016-09-13'
>

Going by my clock here 2016-06-13 was just over 3 months ago, not 4.


>> select
>> modified_date,
>> from
>> clients
>> WHERE
>> modified_date >='2016-06-13' AND modified_date < '2016-09-13'
>
>
>
> But it didn't work... it returns 0 rows but there are rows to be shown:
>
>
>> select modified_date from clients ORDER BY modified_date ASC
>
>
>
>> modified_date
>> ---
>> 2015-07-11 17:23:40
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>
>
>
> What am I doing wrong?

None of those dates are between your specified date range. If you want
to include all of 2016-09-13 timestamps, then you'd better do <
'2016-09-14' since < '2016-09-13' will only cover timestamps on the
12th or before.


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


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

Re: [GENERAL] Installing 9.6 RC on Ubuntu [Solved]

[Steve Crawford]

>
>> In case it is useful for reference, I beat my head on it a bit more and
>> replaced "main" with "9.6":
>> deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg 9.6
>>
>
> Yes, that is an FAQ item:
>
> https://wiki.postgresql.org/wiki/Apt/FAQ
>
>

Yes, but to quote Douglass Adams, "It was on display in the bottom of a
locked filing cabinet stuck in a disused lavatory with a sign on the door
saying ‘Beware of the Leopard'."

If I might be so bold, one or more of the following would make life easier
on users and perhaps get more people to test:

1. Change the wording in the main postgresql.org announcement to be
"Download RC1" and link directly to
https://www.postgresql.org/download/snapshots/

2. Bring non Red Hat releases up to parity.

By way of example, from the snapshots page clicking on the link under Red
Hat (and variants) takes you to http://yum.postgresql.org/ page where 9.6
beta is right at the top. From there all you have to do is select your
release of choice.

By comparison, follow the https://apt.postgresql.org/ link under Debian,
Ubuntu Linux and you are redirected to the wiki where, if you are
fortunate, you will find "Have a look at the FAQ." nestled about half-way
down then dig through that page. I never got there but figured it out by
clicking through the file browser after wasting time seeing lots of 9.6
utilities/addons already listed but broken due to the lack of availability
of the actual server and client.

I know this is open source. I know that people work on their "itch" or what
their employer sponsors. I'm just sharing the user experience should it
provide value and increase the number of testers.

Cheers,
Steve

Re: [GENERAL] select date between - PostgreSQL 9.5

[Vitaly Burovoy]

On 9/13/16, Patrick B  wrote:
> Hi guys,
>
> I got the following column:
>
> modified_date TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT
>> "statement_timestamp"(),
>
>
> I want to select all rows that have been modified from now to 4 months ago.
>
> I've used these queries:
>
> select
>> modified_date,
>> from
>> clients
>> WHERE
>> modified_date BETWEEN '2016-06-13' AND '2016-09-13'

Note that '2016-09-13' is not "now", it is converted to the data type
of a column (expression):
Your expression "modified_date BETWEEN '2016-06-13' AND '2016-09-13'"
means a little different:

The best way to understand it - to use explain:

postgres=# EXPLAIN select modified_date from clients WHERE
modified_date BETWEEN '2016-06-13' AND '2016-09-13';

  QUERY PLAN
-
 Seq Scan on clients  (cost=0.00..43.90 rows=11 width=8)
   Filter: ((modified_date >= '2016-06-13 00:00:00'::timestamp without
time zone) AND (modified_date <= '2016-09-13 00:00:00'::timestamp
without time zone))
(2 rows)

It is not good to use BETWEEN with timestamps (not dates) because in
your example only one exact value (exact to milliseconds) from the
'2016-09-13' will be returned.
2016-09-12 23:59:59.98 (yes)
2016-09-12 23:59:59.99 (yes)
2016-09-13 00:00:00.00 (yes)  <<< the only value from this date
2016-09-13 00:00:00.01 (no)
2016-09-13 00:00:00.02 (no)
etc.

Note that even if you rewrite as "modified_date BETWEEN
'2016-06-13'::date AND '2016-09-13'::date" you still get the same
result because less accuracy type is converting to a type with bigger
accuracy, i.e. to timestamp, not to date.

When you work with timestamps the best way is to use direct "min_value
<= column and column < max_value" (with open upper bound) rather than
"between" statement.

> and
>
>
>> select
>> modified_date,
>> from
>> clients
>> WHERE
>> modified_date >='2016-06-13' AND modified_date < '2016-09-13'
>
>
>
> But it didn't work... it returns 0 rows but there are rows to be shown:
>
>
> select modified_date from clients ORDER BY modified_date ASC
>
>
> modified_date
>> ---
>> 2015-07-11 17:23:40
  it is 2015 year, more than 1 year ago

vv because expression is rewritten as "modified_date <= 2016-09-13
00:00:00", less than your values
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>> 2016-09-13 20:00:51
>
> What am I doing wrong?
> Cheers
> Patrick

-- 
Best regards,
Vitaly Burovoy


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

Re: [GENERAL] select date between - PostgreSQL 9.5

[Adrian Klaver]

On 09/13/2016 05:20 PM, Patrick B wrote:

Hi guys,

I got the following column:

modified_date TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT
"statement_timestamp"(),


I want to select all rows that have been modified from now to 4 months ago.

I've used these queries:

select
modified_date,
from
clients
WHERE
modified_date BETWEEN '2016-06-13' AND '2016-09-13'


and


select
modified_date,
from
clients
WHERE
modified_date >='2016-06-13' AND modified_date < '2016-09-13'



But it didn't work... it returns 0 rows but there are rows to be shown:


select modified_date from clients ORDER BY modified_date ASC



modified_date
---
2015-07-11 17:23:40
2016-09-13 20:00:51
2016-09-13 20:00:51
2016-09-13 20:00:51
2016-09-13 20:00:51



What am I doing wrong?


test=> select '2016-09-13'::timestamp; 



  timestamp 



- 



 2016-09-13 00:00:00


So either:

test=> select '2016-09-13 20:00:51'::date between '09/11/2016' and 
'09/13/2016';

 ?column?
--
 t
(1 row)


or

test=> select '2016-09-13 20:00:51' between '09/11/2016'::timestamp and 
'09/14/2016'::timestamp;

 ?column?
--
 t




Cheers
Patrick



--
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] select date between - PostgreSQL 9.5

[Patrick B]

Hi guys,

I got the following column:

modified_date TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT
> "statement_timestamp"(),


I want to select all rows that have been modified from now to 4 months ago.

I've used these queries:

select
> modified_date,
> from
> clients
> WHERE
> modified_date BETWEEN '2016-06-13' AND '2016-09-13'


and


> select
> modified_date,
> from
> clients
> WHERE
> modified_date >='2016-06-13' AND modified_date < '2016-09-13'



But it didn't work... it returns 0 rows but there are rows to be shown:


select modified_date from clients ORDER BY modified_date ASC



modified_date
> ---
> 2015-07-11 17:23:40
> 2016-09-13 20:00:51
> 2016-09-13 20:00:51
> 2016-09-13 20:00:51
> 2016-09-13 20:00:51



What am I doing wrong?
Cheers
Patrick

Re: [GENERAL] Installing 9.6 RC on Ubuntu [Solved]

[Adrian Klaver]

On 09/13/2016 11:23 AM, Steve Crawford wrote:


On Tue, Sep 13, 2016 at 11:03 AM, Steve Crawford
>
wrote:

I'm trying to install 9.6 RC1 on Ubuntu 16.04 Xenial on my laptop
and it seems broken.

Installation of 9.6 RC1 on Centos was straightforward by comparison
- just add the 9.6 yum package and install. Unfortunately Ubuntu
seems second-class by comparison.

I already have /etc/apt.repos.d/pgdg.list with:
deb http://apt.postgresql.org/pub/repos/apt/
 xenial-pgdg main

This works and I previously installed the PGDG 9.5 package without
issue. Apt lists numerous 9.6 support packages (unit, repmgr,
repack, plv8, ...) but with unsatisfied dependencies due to the
lasck of postgresql-9.6.

I tried adding -testing to the repo but no joy:
deb http://apt.postgresql.org/pub/repos/apt/
 xenial-pgdg-testing main

Is packaging just not complete or am I missing something? (I had
hoped that getting configured for testing would be more friction-free.)

Cheers,
Steve



In case it is useful for reference, I beat my head on it a bit more and
replaced "main" with "9.6":
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg 9.6


Yes, that is an FAQ item:

https://wiki.postgresql.org/wiki/Apt/FAQ

I want to try the beta version of the next PostgreSQL release

WARNING: The data format changes between alpha releases, and 
occasionally beta releases. Be prepared to pg_dump the database contents 
before you upgrade the package to a newer alpha/beta or to a final 
release. Check the release notes before upgrading.


We ship packages for alpha and beta releases, but like in the previous 
FAQ entry, we have only one version of libpq5 in the main archive 
component. To use packages of postgresql-9.6, you need to add the 9.6 
component to your /etc/apt/sources.list.d/pgdg.list entry, so the 9.6 
version of libpq5 will be available for installation:


deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main 9.6




Life is good, again.

Cheers,
Steve



--
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] Event trigger and CREATE/ALTER ROLE/USER

[Tatsuo Ishii]

Simple question: Is there any reason for event trigger to not support
CREATE/ALTER ROLE/USER?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


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

Re: [GENERAL] Predicting query runtime

[Vinicius Segalin]

2016-09-13 17:12 GMT-03:00 Oleg Bartunov :
>
>
> FYI, please check https://pgconf.ru/2016/89977
>
>
 Interesting! Was this presentation filmed? Or would you have a post or
something else with more details?

Re: [GENERAL] Maximum number of exclusive locks

[Tom Lane]

Jeff Janes  writes:
> On Tue, Sep 13, 2016 at 6:21 AM, Tom Lane  wrote:
>> Having said that, the amount of slop involved is only enough for a
>> few hundred lock entries.  Not sure how you're managing to get to
>> nearly 2 extra entries.

> The code assumes every locked object will have 2 processes that hold it (or
> wait for it).  If you actually only have one holder for each locked object,
> that frees up a lot of memory to hold more locked objects.

Ah, right, the key is the difference between the init and max table sizes
in InitLocks.  So his test case is chewing up all the spare space but more
of it is going into the LOCK table than the PROCLOCK table, compared to
the estimates.  Possibly would leave him a bit starved for PROCLOCKs later.
I'm disinclined to change that initialization code though, since the point
is to try to adapt to variations in the holders-per-lock ratio for
different workloads.

regards, tom lane


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

Re: [GENERAL] Maximum number of exclusive locks

[Jeff Janes]

On Tue, Sep 13, 2016 at 6:21 AM, Tom Lane  wrote:

> "Daniel Verite"  writes:
> > Nothing to complain about, but why would the above formula
> > underestimate the number of object locks actually available
> > to a transaction? Isn't it supposed to be a hard cap for such
> > locks?
>
> No, it's a minimum not a maximum.  There's (intentionally) a fair amount
> of slop in the initial shmem size request.  Once everything that's going
> to be allocated has been allocated during postmaster startup, the rest is
> available for growth of shared hash tables, which in practice means the
> lock table; there aren't any other shared structures that grow at runtime.
> So there's room for the lock table to grow a bit beyond its nominal
> capacity.
>
> Having said that, the amount of slop involved is only enough for a
> few hundred lock entries.  Not sure how you're managing to get to
> nearly 2 extra entries.
>
>
The code assumes every locked object will have 2 processes that hold it (or
wait for it).  If you actually only have one holder for each locked object,
that frees up a lot of memory to hold more locked objects.

Cheers,


Jeff

Re: [GENERAL] Predicting query runtime

[Oleg Bartunov]

On Tue, Sep 13, 2016 at 2:54 PM, Istvan Soos  wrote:
> On Tue, Sep 13, 2016 at 2:06 AM, Vinicius Segalin  
> wrote:
>> 2016-09-12 18:22 GMT-03:00 Istvan Soos :
>>> At Heap we have non-trivial complexity in our analytical queries, and
>>> some of them can take a long time to complete. We did analyze features
>>> like the query planner's output, our query properties (type,
>>> parameters, complexity) and tried to automatically identify factors
>>> that contribute the most into the total query time. It turns out that
>>> you don't need to use machine learning for the basics, but at this
>>> point we were not aiming for predictions yet.
>>
>> And how did you do that? Manually analyzing some queries?
>
> In this case, it was automatic analysis and feature discovery. We were
> generating features out of our query parameters, out of the SQL
> string, and also out of the explain analyze output. For each of these
> features, we have examined the P(query is slow | feature is present),
> and measured its statistical properties (precision, recall,
> correlations...).
>
> With these we have built a decision tree-based partitioning, where our
> feature-predicates divided the queries into subsets. Such a tree could
> be used for predictions, or if we would like to be fancy, we could use
> the feature vectors to train a neural network.

FYI, please check https://pgconf.ru/2016/89977

>
> Hope this helps for now,
>   Istvan
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


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

Re: [GENERAL] Predicting query runtime

[Istvan Soos]

On Tue, Sep 13, 2016 at 2:06 AM, Vinicius Segalin  wrote:
> 2016-09-12 18:22 GMT-03:00 Istvan Soos :
>> At Heap we have non-trivial complexity in our analytical queries, and
>> some of them can take a long time to complete. We did analyze features
>> like the query planner's output, our query properties (type,
>> parameters, complexity) and tried to automatically identify factors
>> that contribute the most into the total query time. It turns out that
>> you don't need to use machine learning for the basics, but at this
>> point we were not aiming for predictions yet.
>
> And how did you do that? Manually analyzing some queries?

In this case, it was automatic analysis and feature discovery. We were
generating features out of our query parameters, out of the SQL
string, and also out of the explain analyze output. For each of these
features, we have examined the P(query is slow | feature is present),
and measured its statistical properties (precision, recall,
correlations...).

With these we have built a decision tree-based partitioning, where our
feature-predicates divided the queries into subsets. Such a tree could
be used for predictions, or if we would like to be fancy, we could use
the feature vectors to train a neural network.

Hope this helps for now,
  Istvan


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

Re: [GENERAL] Installing 9.6 RC on Ubuntu [Solved]

[Steve Crawford]

On Tue, Sep 13, 2016 at 11:03 AM, Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> I'm trying to install 9.6 RC1 on Ubuntu 16.04 Xenial on my laptop and it
> seems broken.
>
> Installation of 9.6 RC1 on Centos was straightforward by comparison - just
> add the 9.6 yum package and install. Unfortunately Ubuntu seems
> second-class by comparison.
>
> I already have /etc/apt.repos.d/pgdg.list with:
> deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
>
> This works and I previously installed the PGDG 9.5 package without issue.
> Apt lists numerous 9.6 support packages (unit, repmgr, repack, plv8, ...)
> but with unsatisfied dependencies due to the lasck of postgresql-9.6.
>
> I tried adding -testing to the repo but no joy:
> deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg-testing main
>
> Is packaging just not complete or am I missing something? (I had hoped
> that getting configured for testing would be more friction-free.)
>
> Cheers,
> Steve
>


In case it is useful for reference, I beat my head on it a bit more and
replaced "main" with "9.6":
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg 9.6

Life is good, again.

Cheers,
Steve

[GENERAL] Installing 9.6 RC on Ubuntu

[Steve Crawford]

I'm trying to install 9.6 RC1 on Ubuntu 16.04 Xenial on my laptop and it
seems broken.

Installation of 9.6 RC1 on Centos was straightforward by comparison - just
add the 9.6 yum package and install. Unfortunately Ubuntu seems
second-class by comparison.

I already have /etc/apt.repos.d/pgdg.list with:
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main

This works and I previously installed the PGDG 9.5 package without issue.
Apt lists numerous 9.6 support packages (unit, repmgr, repack, plv8, ...)
but with unsatisfied dependencies due to the lasck of postgresql-9.6.

I tried adding -testing to the repo but no joy:
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg-testing main

Is packaging just not complete or am I missing something? (I had hoped that
getting configured for testing would be more friction-free.)

Cheers,
Steve

Re: [GENERAL] Predicting query runtime

[Hu, Patricia]

I’ve been looking for this on postgres too.  Does Postgres have something 
similar to Oracle’s v$session_longops? It gives info on total unit of work, 
units done so far, last update time, and time remaining etc, and I found it 
valuable in providing an estimate to how long a certain query would keep 
running and whether or not to kill it if applicable. This should be relatively 
easy to implement in postgres too if it is not available yet?


Thanks,
Patricia

From: Oleksandr Shulgin [mailto:oleksandr.shul...@zalando.de]
Sent: Monday, September 12, 2016 11:08 AM
To: Vinicius Segalin
Cc: pgsql general
Subject: Re: Predicting query runtime

On Mon, Sep 12, 2016 at 4:03 PM, Vinicius Segalin 
> wrote:
Hi everyone,

I'm trying to find a way to predict query runtime (I don't need to be extremely 
precise). I've been reading some papers about it, and people are using machine 
learning to do so. For the feature vector, they use what the DBMS's query 
planner provide, such as operators and their cost. The thing is that I haven't 
found any work using PostgreSQL, so I'm struggling to adapt it.
My question is if anyone is aware of a work that uses machine learning and 
PostgreSQL to predict query runtime, or maybe some other method to perform this.

Hi,

I'm not aware of machine-learning techniques to achieve that (and I don't 
actually believe it's feasible), but there you might find this extension 
particularly useful: 
https://www.postgresql.org/docs/9.5/static/pgstatstatements.html[postgresql.org]

Can you share some links to the papers you are referring to (assuming these are 
publicly available)?

Regards,
--
Alex


Confidentiality Notice::  This email, including attachments, may include 
non-public, proprietary, confidential or legally privileged information.  If 
you are not an intended recipient or an authorized agent of an intended 
recipient, you are hereby notified that any dissemination, distribution or 
copying of the information contained in or transmitted with this e-mail is 
unauthorized and strictly prohibited.  If you have received this email in 
error, please notify the sender by replying to this message and permanently 
delete this e-mail, its attachments, and any copies of it immediately.  You 
should not retain, copy or use this e-mail or any attachment for any purpose, 
nor disclose all or any part of the contents to any other person. Thank you.

Re: [GENERAL] Maximum number of exclusive locks

[Tom Lane]

"Daniel Verite"  writes:
> Nothing to complain about, but why would the above formula
> underestimate the number of object locks actually available
> to a transaction? Isn't it supposed to be a hard cap for such
> locks?

No, it's a minimum not a maximum.  There's (intentionally) a fair amount
of slop in the initial shmem size request.  Once everything that's going
to be allocated has been allocated during postmaster startup, the rest is
available for growth of shared hash tables, which in practice means the
lock table; there aren't any other shared structures that grow at runtime.
So there's room for the lock table to grow a bit beyond its nominal
capacity.

Having said that, the amount of slop involved is only enough for a
few hundred lock entries.  Not sure how you're managing to get to
nearly 2 extra entries.

regards, tom lane


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

[GENERAL] Maximum number of exclusive locks

[Daniel Verite]

  Hi,

When deleting large objects, an exclusive lock is grabbed on each
object individually. As a result, a transaction that does it en
masse can encounter this error:

 ERROR:  out of shared memory
 HINT:  You might need to increase max_locks_per_transaction.

I would expect the maximum number of lo_unlink() in the same
transaction to be capped at:
 max_locks_per_transaction * (max_connections + max_prepared_transactions)
per documentation:
https://www.postgresql.org/docs/current/static/runtime-config-locks.html

  "The shared lock table tracks locks on max_locks_per_transaction *
  (max_connections + max_prepared_transactions) objects (e.g., tables);
  hence, no more than this many distinct objects can be locked at any
  one time"

But in practice, on an otherwise unused 9.5 instance, I've noticed
that this query:

  select count(lo_unlink(oid)) from (select oid
from pg_largeobject_metadata limit :LIMIT) s;

with these settings:

  max_locks_per_transaction | 512 
  max_connections | 30
  max_prepared_transactions | 5

starts failing at LIMIT=37133, although I'd expect this to
happen, in the best case, at LIMIT=512*(30+5)=17920.

Nothing to complain about, but why would the above formula
underestimate the number of object locks actually available
to a transaction? Isn't it supposed to be a hard cap for such
locks?


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


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