Fast seralizable transactions starving slower ones

[Viktor Fougstedt]

Hello!

We have a system with a lot of integrity constraints that are not easily 
expressed as SQL constraints. We therefore run all writing transactions in 
serializable isolation, so that our code can make SELECT:s to check the 
constraints.

We’ve run in to a, well, it’s not a “problem”, because everything is working 
according to spec. But some of our transactions are much quicker than the 
other. Since the first COMMIT of colliding seralizable transactions always 
wins, the shorter transactions starve the slower ones, which are always 
restarted.

We’re now working on a solution where all transactions start by taking an 
advisory lock. Normally transactions release it immediately, but a transaction 
which has been restarted multiple times will keep it, preventing any others 
from starting. Thereby it will run to completion, normally within a maximum of 
one more restart.

Are there any other solutions to this starvation problem? 

Regards,
/Viktor

Re: Code of Conduct plan

[Jan Claeys]

On Wed, 2018-06-06 at 07:27 +0200, Chris Travers wrote:
> The real fear here is the code of conduct being co-opted as a weapon
> of world-wide culture war and that's what is driving a lot of the
> resistance here.  This is particularly an American problem here and
> it causes  a lot of resistance among people who were, until the
> second world war, subject to some pretty serious problems by colonial
> powers.

I don't see how this could happen any more than it already can, because
as far as I can tell the goal is not to discuss complaints in public;
the committee would handle cases in private.  And if committee members
would try to abuse their power, I'm pretty sure they would be removed.

> Putting a bunch of American lawyers, psychologists, sociologists,
> marketers etc on the board in the name of diversity would do way more
> harm than good.

I didn't say they have to be American, and I didn't say there has to be
a bunch of them.  I just said it would be good if there were also
people who aren't (just only) developers, DBAs or other very technical
people.


-- 
Jan Claeys

Re: Slow planning time for simple query

[Tom Lane]

Jerry Sievers  writes:
> Tom Lane  writes:
>> Oh, hmm, yeah it could be ye olde get_actual_variable_range() issue.
>> When this happens, are there perhaps a lot of recently-dead rows at either
>> extreme of the range of table1.source_id or table2.id?

> We noticed the cluster of interest had a rogue physical rep slot holding
> 71k WAL segments.
> Dropping same slot seemed to correlate with the problem going away.
> Does that sound like a plausible explanation for the observed slow
> planning times?

I believe the slot would hold back global xmin and thereby prevent
"recently-dead" rows from becoming just plain "dead", so yeah, this
observation does seem to square with the get_actual_variable_range
theory.  You'd still need to posit that something had recently deleted
a lot of rows at the end of the range of one of those columns, though.

regards, tom lane

Re: Which backend using which pg_temp_N schema?

[Adrian Klaver]

On 06/06/2018 02:00 PM, Jerry Sievers wrote:

Adrian Klaver  writes:


On 06/06/2018 08:54 AM, Jerry Sievers wrote:


Adrian Klaver  writes:


Yep thanks... but IMO something that simply exposes whatever internal
registry of temp schemas/PIDs (which I presume must exist) to DBA SQL
avoids any perhaps unreliable hackery such as having to scrape query
text from pg_stat_activity or similar.

To wit; A long standing session might have any number of temp objects
existing for which records of same in the aforementioned views has long
since been overwritten.


True assuming there is no activity against the objects or no open
transactions.

So what you interested in:

1) What created a temporary object?


Yes.  Which *session*.


Alright, finally got it through my thick skull:) I was fixated on the 
statement that created the table.




Thx



2) Temporary objects that are the playing at being permanent objects?

3) Both of the above?










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

Re: Slow planning time for simple query

[Jerry Sievers]

Tom Lane  writes:

> Pavel Stehule  writes:
>
>> 2018-06-06 18:59 GMT+02:00 Jeremy Finzel :
>>> We have an odd scenario on one of our OLTP systems, which behaves the same
>>> way on a streamer, of a 700-1000ms planning time for a query like this:
>>> SELECT *
>>> FROM table1
>>> WHERE  source_id IN (SELECT id FROM table2 WHERE customer_id = $1);
>
>> more times I seen similar issue based on bloated indexes on table - pg in
>> planning time detect min max from possible indexes
>
> Oh, hmm, yeah it could be ye olde get_actual_variable_range() issue.
> When this happens, are there perhaps a lot of recently-dead rows at either
> extreme of the range of table1.source_id or table2.id?

We noticed the cluster of interest had a rogue physical rep slot holding
71k WAL segments.

Dropping same slot seemed to correlate with the problem going away.

Does that sound like a plausible explanation for the observed slow
planning times?

Thx



> We made a fix last year to improve that:
> https://git.postgresql.org/gitweb/?p=postgresql.git=commitdiff=3ca930fc3
> but it wasn't back-patched.
>
>   regards, tom lane
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800

Re: Which backend using which pg_temp_N schema?

[Jerry Sievers]

Adrian Klaver  writes:

> On 06/06/2018 08:54 AM, Jerry Sievers wrote:
>
>> Adrian Klaver  writes:
>>
>>
>> Yep thanks... but IMO something that simply exposes whatever internal
>> registry of temp schemas/PIDs (which I presume must exist) to DBA SQL
>> avoids any perhaps unreliable hackery such as having to scrape query
>> text from pg_stat_activity or similar.
>>
>> To wit; A long standing session might have any number of temp objects
>> existing for which records of same in the aforementioned views has long
>> since been overwritten.
>
> True assuming there is no activity against the objects or no open
> transactions.
>
> So what you interested in:
>
> 1) What created a temporary object?

Yes.  Which *session*.

Thx

>
> 2) Temporary objects that are the playing at being permanent objects?
>
> 3) Both of the above?
>
>
>>
>>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800

Re: Doing a \set through perl DBI ?

[David Gauthier]

I think I found my own answer.  I wanted to use the current linux user's
uid as part of a query (again, this is a perl/DBI script).  I was thinking
I might be able to set a variable into the DB session somehow using \set
through DBI to accomplish this.  The solution that finally occurred to me
was to create a temporary table and have the perl script shove the uid into
a column in that table, then query against that.  Sort of like using a temp
table to store variables set from outside.

On Wed, Jun 6, 2018 at 11:46 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wednesday, June 6, 2018, David Gauthier 
> wrote:
>
>> Hi:
>>
>> Is there a way to do the equivalent of a "\set foo 1" through perl dbi ?
>> I tried...
>> $dbh->do("\\set foo 1");
>> and got a syntax error
>>
>> Of course, I'd also have to be able to access the value of foo once its
>> set.   I'm guessing the usual way ??? (select :foo)
>>
>>
> No.  Meta commands are psql client program only.  You'd need to explain
> why you want this to get reasonable server-side suggestions.
>
> David J.
>
>

Re: Which backend using which pg_temp_N schema?

[Adrian Klaver]

On 06/06/2018 08:54 AM, Jerry Sievers wrote:

Adrian Klaver  writes:


On 06/05/2018 04:49 PM, Jerry Sievers wrote:


Adrian Klaver  writes:


On 06/05/2018 02:53 PM, Jerry Sievers wrote:


Was just studying a legacy DB to learn about temp table activity.

Felt like being able to tie temp schemas to live backends s/b useful but
then didn't find a function/view for doing this.


I don't understand what the above is getting at.
Can you explain more about what you are trying to do?


Sure...  A backend may or not have a pg_temp_N schema assigned to it
depending whether or not it ever needs one for temp objects...

Suppose we query pg_class and pg_namespace to see what temp tables exist
at some particular time.  We find some tables and thus have info about
which role ownes them and the usual.

But it's a complex and monolithic app with too many aspects all running
as same role.

Having a way to relate PID to such a temp schema then gives us perhaps a
lot more info about the app behavior.  To wit; source IP might lead us
to know that this is a batching aspect of the app and not the OLTP
aspect etc.


Just thinking out loud here. The issues I see are:

1) A temporary table is tied to a session and therefore its existence
will be some degree of fleeting.

2) A PID will not exist unless an action is done against the table.

3) Said action maybe contain references to other objects which are in
the temporary schema and objects that are out of it. So you would have
to parse the action statement to determine whether the temporary
table/schema is actually involved.

To me the solution would be to work from the other direction. When you
query pg_class/pg_namespace to determine that temporary tables are
present, then query pg_stat_activity to see what statements are being
run:


Yep thanks... but IMO something that simply exposes whatever internal
registry of temp schemas/PIDs (which I presume must exist) to DBA SQL
avoids any perhaps unreliable hackery such as having to scrape query
text from pg_stat_activity or similar.

To wit; A long standing session might have any number of temp objects
existing for which records of same in the aforementioned views has long
since been overwritten.


True assuming there is no activity against the objects or no open 
transactions.


So what you interested in:

1) What created a temporary object?

2) Temporary objects that are the playing at being permanent objects?

3) Both of the above?






https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW


For a longer term view there is pg_stat_statements:

https://www.postgresql.org/docs/10/static/pgstatstatements.html




Yes of course there might be folks somewhere around this organization
that  can answer some of those questions but IMO  knowing which PID is
doing temp stuff in some schema tells us a lot that I am not sure can be
machine-gotten any other way.





A quic \df for functions with names likely to be fruitful revealed
nothing.  Did likewise for sysinfo views.

Am I missing it or does feature not exist?

Thx








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

Re: Slow planning time for simple query

[Adrian Klaver]

On 06/06/2018 09:59 AM, Jeremy Finzel wrote:

Hello -

We have an odd scenario on one of our OLTP systems, which behaves the 
same way on a streamer, of a 700-1000ms planning time for a query like this:


SELECT *
FROM table1
WHERE  source_id IN (SELECT id FROM table2 WHERE customer_id = $1);

The actual execution time is sub-ms.


I am trying to sort out the various references to times so could you:

Show the EXPLAIN ANALYZE?

and ?

psql> \timing

psql> SELECT *
FROM table1
WHERE  source_id IN (SELECT id FROM table2 WHERE customer_id = $1);




We initially thought maybe catalog bloat?  But we were able to reindex 
all of the highly churned catalog tables, and I even did VACUUM FULL on 
pg_attribute and pg_statistic, to no avail.


There are no custom settings for pg_attribute for the given tables either.

Interestingly, the problem goes away on a SAN snapshot of the target system.

Any ideas of what else we could try?  A PL function that caches the 
query plan works, but that is just a workaround.


Thanks!
Jeremy



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

Fw: Re: Out of memory error with PG10.3, 10.4 but not 9.3.19

[ChatPristi]

 Forgotten to CC the list, sorry...

>Well, instead of an explain output which takes 2.4MB compressed and
>9.6MB uncompressed (take it as unreadable), could you produce a
>self-contained test case with a glimpse of the schema you are using?
>Where does the OOM happen, and how did you change your partitioned table
>schema?  Are you using the native partitioning instead?
>Michael,
Thank you for your answer.

Sorry for the unreadable explain output.
I attached a SQL dump with 2 entities loaded in the database (2,872,265 
entities in the actual database), the actual query and the actual output.

The OOM is durin the query (SELECT) after ~9 minutes the memory of the postgres 
increase until 8GB and the OOM message.

Partitioning is done by inherhitance.
After a complete reload of the database in PG10.4 the OOM still exists.


--
Michael<>

Re: Using distinct in an aggregate prevents parallel execution?

[Thomas Kellerer]

Tom Lane schrieb am 06.06.2018 um 16:32:

Thomas Kellerer  writes:

Is this a known limitation?


Yes, unless somebody has done radical restructuring of the aggregation
code while I wasn't looking.

agg(DISTINCT ...) is currently implemented inside the Agg plan node,
so it's an indivisible black box to everything else.  That was a
simple, minimum-code-footprint method for implementing the feature
back when; but it's got lots of drawbacks, and one is that there's
no reasonable way to parallelize.

I'd anticipate that before we could even start to think of parallelizing,
we'd have to split out the distinct-ification processing into a separate
plan node.

agg(... ORDER BY ...) has got the same problem, and it'd likely be
advisable to fix that at the same time.


Thansk for the explanation.

Re: Code of Conduct plan

[Tom Lane]

"David G. Johnston"  writes:
> On the topic of privacy - who exactly, from an administrative aspect, has
> access to the systems that house these kinds of confidential
> communications?  Do these emails end up in PostgreSQL.org servers long-term
> or is it mainly transient distribution and only individual's personal email
> accounts, with whatever hosting provider they choose, hold the messages
> long-term?

The pginfra team, which has some overlap with core but is a separate
group (I'm not a member), are the guys with root on the servers.
So you have to trust them too as far as information security goes.
I don't know that the exact procedures for the CoC group have been
decided yet; but most likely it will work like the core team, for which
there's a closed mailing list that's not archived on the project servers.
The weakest link in the CoC traffic is likely to be the individual
committee members' email accounts --- I trust they'll take some suitable
precautions.

regards, tom lane

Re: Code of Conduct plan

[Joshua D. Drake]

On 06/06/2018 11:22 AM, Tom Lane wrote:

I wrote:

Yeah, somebody else made a similar point upthread.  I guess we felt that
the proper procedure was obvious given the structure, but maybe not.
I could support adding text to clarify this, perhaps along the line of


Hmm ... actually, there's another special case that's not discussed,
which is what happens if a committee or core member wants to file a
complaint against someone else?  They certainly shouldn't get to rule
on their own complaint.  So maybe change "complaint against" to
"complaint by or against" in my proposed addition, and then we're good.


Well that is a standard conflict of interest issue. Having simple 
language that says something such as:


A Member involved in complaints may not vote/rule on issues reported by 
the respective member.


JD



regards, tom lane




--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Alvaro Herrera]

On 2018-Jun-06, David G. Johnston wrote:

> On the topic of privacy - who exactly, from an administrative aspect, has
> access to the systems that house these kinds of confidential
> communications?  Do these emails end up in PostgreSQL.org servers long-term
> or is it mainly transient distribution and only individual's personal email
> accounts, with whatever hosting provider they choose, hold the messages
> long-term?

postgresql.org does not host personal email accounts, with a few
exceptions.  Most of these exceptions are actually just forwards to
mailboxes elsewhere, so the traffic stays in the relevant postgresql.org
server very briefly.  The few accounts that that are actual mailboxes in
postgresql.org are, as far as I know, only country-specific accounts for
advocacy, not personal points of contact.

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

Re: Code of Conduct plan

[David G. Johnston]

On Wednesday, June 6, 2018, Tom Lane  wrote:

> Jeremy Schneider  writes:
> > My main feedback on the CoC is that it doesn't really say anything about
> > what to do if the complaint is against a core team member. This was
> > mentioned elsewhere in the email thread and I'm a bit surprised there's
> > nothing explicit in the CoC. If someone feels they have been treated in
> > a grossly inappropriate manner by a core team member, is it worthwhile
> > to report this? I think they'd want to know a little more about what the
> > process will be for that special case.
>
> Yeah, somebody else made a similar point upthread.  I guess we felt that
> the proper procedure was obvious given the structure, but maybe not.
> I could support adding text to clarify this, perhaps along the line of
>
> In the event of a complaint against a CoC committee member, the
> process proceeds normally, but that person is excluded from the
> committee's discussions in the matter.  Similarly, in the event of
> a complaint against a core team member, the process proceeds
> normally, but that person is excluded from any core review that
> may occur.
>
> and maybe also
>
> In such cases, removal from the committee or core is another
> possible sanction, in addition to those mentioned above.
>

Yeah, while it is pretty much self-evident I would agree that stating it
explicitly would benefit the document.  Both parts.

On the topic of privacy - who exactly, from an administrative aspect, has
access to the systems that house these kinds of confidential
communications?  Do these emails end up in PostgreSQL.org servers long-term
or is it mainly transient distribution and only individual's personal email
accounts, with whatever hosting provider they choose, hold the messages
long-term?

David J.

Re: Code of Conduct plan

[Tom Lane]

I wrote:
> Yeah, somebody else made a similar point upthread.  I guess we felt that
> the proper procedure was obvious given the structure, but maybe not.
> I could support adding text to clarify this, perhaps along the line of

Hmm ... actually, there's another special case that's not discussed,
which is what happens if a committee or core member wants to file a
complaint against someone else?  They certainly shouldn't get to rule
on their own complaint.  So maybe change "complaint against" to 
"complaint by or against" in my proposed addition, and then we're good.

regards, tom lane

Re: Slow planning time for simple query

[Jeremy Finzel]

On Wed, Jun 6, 2018 at 1:13 PM, Jeremy Finzel  wrote:

>
>
> On Wed, Jun 6, 2018 at 12:12 PM, Tom Lane  wrote:
>
>> Jeremy Finzel  writes:
>> > We have an odd scenario on one of our OLTP systems, which behaves the
>> same
>> > way on a streamer, of a 700-1000ms planning time for a query like this:
>>
>> > SELECT *
>> > FROM table1
>> > WHERE  source_id IN (SELECT id FROM table2 WHERE customer_id = $1);
>>
>> Hm.  Is this the first query executed in a session?  If so maybe it's
>> got something to do with populating caches and other session spin-up
>> overhead.
>>
>> Another theory is it's some sort of locking issue.  Turning on
>> log_lock_waits, and setting deadlock_timeout to say 100ms, would help
>> in investigating that.
>>
>> regards, tom lane
>>
>
> I have run it over and over with no improvement in the planning time, so I
> don't thing it's first in session-related.  I can only make it faster with
> a pl function so far.
>
> We have log_lock_waits on and nothing shows, and turning down
> deadlock_timeout also doesn't do anything.
>
> Thanks,
> Jeremy
>

Forgot to mention, this is running on this version:
PostgreSQL 9.6.8 on x86_64-pc-linux-gnu (Ubuntu 9.6.8-1.pgdg16.04+1),
compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit


Thanks,
Jeremy

Re: Slow planning time for simple query

[Jeremy Finzel]

On Wed, Jun 6, 2018 at 12:12 PM, Tom Lane  wrote:

> Jeremy Finzel  writes:
> > We have an odd scenario on one of our OLTP systems, which behaves the
> same
> > way on a streamer, of a 700-1000ms planning time for a query like this:
>
> > SELECT *
> > FROM table1
> > WHERE  source_id IN (SELECT id FROM table2 WHERE customer_id = $1);
>
> Hm.  Is this the first query executed in a session?  If so maybe it's
> got something to do with populating caches and other session spin-up
> overhead.
>
> Another theory is it's some sort of locking issue.  Turning on
> log_lock_waits, and setting deadlock_timeout to say 100ms, would help
> in investigating that.
>
> regards, tom lane
>

I have run it over and over with no improvement in the planning time, so I
don't thing it's first in session-related.  I can only make it faster with
a pl function so far.

We have log_lock_waits on and nothing shows, and turning down
deadlock_timeout also doesn't do anything.

Thanks,
Jeremy

Re: Code of Conduct plan

[Tom Lane]

Jeremy Schneider  writes:
> My main feedback on the CoC is that it doesn't really say anything about
> what to do if the complaint is against a core team member. This was
> mentioned elsewhere in the email thread and I'm a bit surprised there's
> nothing explicit in the CoC. If someone feels they have been treated in
> a grossly inappropriate manner by a core team member, is it worthwhile
> to report this? I think they'd want to know a little more about what the
> process will be for that special case.

Yeah, somebody else made a similar point upthread.  I guess we felt that
the proper procedure was obvious given the structure, but maybe not.
I could support adding text to clarify this, perhaps along the line of

In the event of a complaint against a CoC committee member, the
process proceeds normally, but that person is excluded from the
committee's discussions in the matter.  Similarly, in the event of
a complaint against a core team member, the process proceeds
normally, but that person is excluded from any core review that
may occur.

and maybe also

In such cases, removal from the committee or core is another
possible sanction, in addition to those mentioned above.

regards, tom lane

Re: Which backend using which pg_temp_N schema?

[Jerry Sievers]

Tom Lane  writes:

> Jerry Sievers  writes:
>
>> Yep thanks... but IMO something that simply exposes whatever internal
>> registry of temp schemas/PIDs (which I presume must exist)
>
> Not really.  There are a couple of ways that one could identify a
> session's "BackendId", which is the "N" in the "pg_temp_N" name of
> the temp schema that it would use if it uses one.  But I do not think
> there's any terribly reliable way to tell from outside the session
> whether it has actually done anything with the temp schema.  If it
> hasn't, then at least in principle there could be objects in the
> schema that are left over from a crashed session that previously had
> the same BackendId.  We only make an effort to clean out such objects
> at the time that a session first creates a temp object.

Hmmm, interesting.

> In any case, I think you're right that this isn't exposed at the SQL
> level presently.

Roger that and what motivated the post was basically to explore that
issue.

Thx

>
>   regards, tom lane
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800

Slow planning time for simple query

[Jeremy Finzel]

Hello -

We have an odd scenario on one of our OLTP systems, which behaves the same
way on a streamer, of a 700-1000ms planning time for a query like this:

SELECT *
FROM table1
WHERE  source_id IN (SELECT id FROM table2 WHERE customer_id = $1);

The actual execution time is sub-ms.

We initially thought maybe catalog bloat?  But we were able to reindex all
of the highly churned catalog tables, and I even did VACUUM FULL on
pg_attribute and pg_statistic, to no avail.

There are no custom settings for pg_attribute for the given tables either.

Interestingly, the problem goes away on a SAN snapshot of the target system.

Any ideas of what else we could try?  A PL function that caches the query
plan works, but that is just a workaround.

Thanks!
Jeremy

Re: Which backend using which pg_temp_N schema?

[Tom Lane]

Jerry Sievers  writes:
> Yep thanks... but IMO something that simply exposes whatever internal
> registry of temp schemas/PIDs (which I presume must exist)

Not really.  There are a couple of ways that one could identify a
session's "BackendId", which is the "N" in the "pg_temp_N" name of
the temp schema that it would use if it uses one.  But I do not think
there's any terribly reliable way to tell from outside the session
whether it has actually done anything with the temp schema.  If it
hasn't, then at least in principle there could be objects in the
schema that are left over from a crashed session that previously had
the same BackendId.  We only make an effort to clean out such objects
at the time that a session first creates a temp object.

In any case, I think you're right that this isn't exposed at the SQL
level presently.

regards, tom lane

Re: Which backend using which pg_temp_N schema?

[Jerry Sievers]

Adrian Klaver  writes:

> On 06/05/2018 04:49 PM, Jerry Sievers wrote:
>
>> Adrian Klaver  writes:
>>
>>> On 06/05/2018 02:53 PM, Jerry Sievers wrote:
>>>
 Was just studying a legacy DB to learn about temp table activity.

 Felt like being able to tie temp schemas to live backends s/b useful but
 then didn't find a function/view for doing this.
>>>
>>> I don't understand what the above is getting at.
>>> Can you explain more about what you are trying to do?
>>
>> Sure...  A backend may or not have a pg_temp_N schema assigned to it
>> depending whether or not it ever needs one for temp objects...
>>
>> Suppose we query pg_class and pg_namespace to see what temp tables exist
>> at some particular time.  We find some tables and thus have info about
>> which role ownes them and the usual.
>>
>> But it's a complex and monolithic app with too many aspects all running
>> as same role.
>>
>> Having a way to relate PID to such a temp schema then gives us perhaps a
>> lot more info about the app behavior.  To wit; source IP might lead us
>> to know that this is a batching aspect of the app and not the OLTP
>> aspect etc.
>
> Just thinking out loud here. The issues I see are:
>
> 1) A temporary table is tied to a session and therefore its existence
> will be some degree of fleeting.
>
> 2) A PID will not exist unless an action is done against the table.
>
> 3) Said action maybe contain references to other objects which are in
> the temporary schema and objects that are out of it. So you would have
> to parse the action statement to determine whether the temporary
> table/schema is actually involved.
>
> To me the solution would be to work from the other direction. When you
> query pg_class/pg_namespace to determine that temporary tables are
> present, then query pg_stat_activity to see what statements are being
> run:

Yep thanks... but IMO something that simply exposes whatever internal
registry of temp schemas/PIDs (which I presume must exist) to DBA SQL
avoids any perhaps unreliable hackery such as having to scrape query
text from pg_stat_activity or similar.

To wit; A long standing session might have any number of temp objects
existing for which records of same in the aforementioned views has long
since been overwritten.

>
> https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
>
>
> For a longer term view there is pg_stat_statements:
>
> https://www.postgresql.org/docs/10/static/pgstatstatements.html
>
>
>>
>> Yes of course there might be folks somewhere around this organization
>> that  can answer some of those questions but IMO  knowing which PID is
>> doing temp stuff in some schema tells us a lot that I am not sure can be
>> machine-gotten any other way.
>>
>>>

 A quic \df for functions with names likely to be fruitful revealed
 nothing.  Did likewise for sysinfo views.

 Am I missing it or does feature not exist?

 Thx

>>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800

Re: Doing a \set through perl DBI ?

[David G. Johnston]

On Wednesday, June 6, 2018, David Gauthier  wrote:

> Hi:
>
> Is there a way to do the equivalent of a "\set foo 1" through perl dbi ?
> I tried...
> $dbh->do("\\set foo 1");
> and got a syntax error
>
> Of course, I'd also have to be able to access the value of foo once its
> set.   I'm guessing the usual way ??? (select :foo)
>
>
No.  Meta commands are psql client program only.  You'd need to explain why
you want this to get reasonable server-side suggestions.

David J.

Doing a \set through perl DBI ?

[David Gauthier]

Hi:

Is there a way to do the equivalent of a "\set foo 1" through perl dbi ?
I tried...
$dbh->do("\\set foo 1");
and got a syntax error

Of course, I'd also have to be able to access the value of foo once its
set.   I'm guessing the usual way ??? (select :foo)


Thanks for any help !

Re: Code of Conduct plan

[Joshua D. Drake]

On 06/05/2018 08:55 PM, Tom Lane wrote:

Adrian Klaver  writes:

On 06/05/2018 04:41 PM, Tom Lane wrote:

I'm getting a little tired of people raising hypothetical harms and
ignoring the real harms that we're hoping to fix.  Yes, this is an
experiment and it may not work, but we can't find out without trying.
If it turns out to be a net loss, we'll modify it or abandon it.



Good to hear this is considered an experiment.



To that end will there be quarterly/yearly reports, suitably anonymized,
that spell out the activity that took place with reference to the CoC?


That seems like a good idea from here.  I don't know exactly how much
can be reported without risking privacy issues, but surely we could at
least provide the number of incidents and how they were resolved.


Yeah I like it too. We don't have to give out any confidential 
information but it adds to the transparency and allows the community as 
a whole to see that.


regards, tom lane




--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Using distinct in an aggregate prevents parallel execution?

[Tom Lane]

Thomas Kellerer  writes:
> Is this a known limitation? 

Yes, unless somebody has done radical restructuring of the aggregation
code while I wasn't looking.

agg(DISTINCT ...) is currently implemented inside the Agg plan node,
so it's an indivisible black box to everything else.  That was a
simple, minimum-code-footprint method for implementing the feature
back when; but it's got lots of drawbacks, and one is that there's
no reasonable way to parallelize.

I'd anticipate that before we could even start to think of parallelizing,
we'd have to split out the distinct-ification processing into a separate
plan node.

agg(... ORDER BY ...) has got the same problem, and it'd likely be
advisable to fix that at the same time.

regards, tom lane

Re: Failover replication building a new master

[Adrian Klaver]

On 06/05/2018 05:43 AM, Tom Loder wrote:

I am using Postgres 10.4, and using replication, I have managed to set up four 
servers, with one running as a master and the other three running with 
streaming replication from the master.

I have used the  command:
psql -c "ALTER SYSTEM SET synchronous_standby_names TO  'FIRST 1(S2,S3,S4)';" 
so that at least one of the three slave clusters are SYNCED to the master.

If I run the following command I get the following back:

SELECT pid, usename, application_name as name,state, client_addr, sent_lsn, 
write_lsn, flush_lsn, replay_lsn, sync_priority, sync_state FROM 
pg_stat_replication;

   pid  | usename | name  |   state   | sent_lsn  | write_lsn | flush_lsn | 
replay_lsn | sync_priority | sync_state
---+-+---+---+-+---+---+---++---+
  26215 | replica | S3 | streaming | 0/600 | 0/600 | 0/600 | 
0/600  | 2 | potential
  26200 | replica | S4 | streaming | 0/600 | 0/600 | 0/600 | 
0/600  | 3 | potential
  26186 | replica | S2 | streaming |  0/600 | 0/600 | 0/600 | 
0/600  | 1 | sync

When I take out my master server (S1) I setup S2 as the master using a bash 
script and I update the recovery.conf files on the other two servers to change 
the replication to run from S2.


I am not sure what is going on here as I am not that familiar with 
synchronous replication. I do think it would aid those that can help if 
you provided the contents of :


1) The Bash script

2) The recovery.conf file.

Also are there relevant log entries for S2, S3 and S4?


I have changed the setting for synchronouse_standby_names using:

psql -c "ALTER SYSTEM SET synchronous_standby_names TO  'FIRST 1(S1,S3,S4)';" 
hoping that one of the remaining two or possibly the old master will SYNC to the new 
master (S2).

However when I check on S2 I get the following output from pg_stat_replication:

pid  | usename | name  |  state  | sent_lsn  | write_lsn | flush_lsn | 
replay_lsn | sync_priority | sync_state
--+-+---+-+-+---+---+---++---+
  6418 | replica | S4 | startup | 0/D98 | 0/D98 | 0/D98 | 0/D98 
 | 3 | potential
  6417 | replica | S3 | startup | 0/D98 | 0/D98 | 0/D98 | 0/D98 
 | 2 | potential

If I also get the original master (S1) running and set it up as a Slave this also will 
not "SYNC". How can I get the standby servers to SYNC to the new Master (S2) 
without doing a new BaseBackup from S2 to the other servers?

Thanks

Tom











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

Re: Which backend using which pg_temp_N schema?

[Adrian Klaver]

On 06/05/2018 04:49 PM, Jerry Sievers wrote:

Adrian Klaver  writes:


On 06/05/2018 02:53 PM, Jerry Sievers wrote:


Was just studying a legacy DB to learn about temp table activity.

Felt like being able to tie temp schemas to live backends s/b useful but
then didn't find a function/view for doing this.


I don't understand what the above is getting at.
Can you explain more about what you are trying to do?


Sure...  A backend may or not have a pg_temp_N schema assigned to it
depending whether or not it ever needs one for temp objects...

Suppose we query pg_class and pg_namespace to see what temp tables exist
at some particular time.  We find some tables and thus have info about
which role ownes them and the usual.

But it's a complex and monolithic app with too many aspects all running
as same role.

Having a way to relate PID to such a temp schema then gives us perhaps a
lot more info about the app behavior.  To wit; source IP might lead us
to know that this is a batching aspect of the app and not the OLTP
aspect etc.


Just thinking out loud here. The issues I see are:

1) A temporary table is tied to a session and therefore its existence 
will be some degree of fleeting.


2) A PID will not exist unless an action is done against the table.

3) Said action maybe contain references to other objects which are in 
the temporary schema and objects that are out of it. So you would have 
to parse the action statement to determine whether the temporary 
table/schema is actually involved.


To me the solution would be to work from the other direction. When you 
query pg_class/pg_namespace to determine that temporary tables are 
present, then query pg_stat_activity to see what statements are being run:


https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW


For a longer term view there is pg_stat_statements:

https://www.postgresql.org/docs/10/static/pgstatstatements.html




Yes of course there might be folks somewhere around this organization
that  can answer some of those questions but IMO  knowing which PID is
doing temp stuff in some schema tells us a lot that I am not sure can be
machine-gotten any other way.





A quic \df for functions with names likely to be fruitful revealed
nothing.  Did likewise for sysinfo views.

Am I missing it or does feature not exist?

Thx






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

Re: Setting up replication from 9.4 to 10.4

[Lionel Tressens]

Thanks Andreas,

pglogical seems really great.
My knowledge of replication was frozen at the time of Slony II and PG 8.4,
I didn't have the chance to use replication since that time.
I'll give a look at pglogical !

Best regards

Lionel

2018-06-06 8:40 GMT+02:00 Andreas Kretschmer :

>
>
> Am 06.06.2018 um 08:16 schrieb Lionel Tressens:
>
>> Hello,
>>
>> We are running a single PG 9.4 database node we are switching to PG 10.4
>> (which will run as master + setup of a slave)
>>
>> To minimize downtime when switching from the 9.4 server to the 10.4 one,
>> I would like to setup a replication stream instead of stopping the
>> production, dumping 9.4 and restoring in 10.4 which my take a lot of time.
>>
>> The next steps would be to reinstall the 9.4 server from scratch with
>> 10.4 and setup this host as a slave, ready to take the master role if
>> needed (hot standby).
>>
>> Can such a replication be established between 9.4 and 10.4 and what
>> replication tool/schema to use ? I have been through the documentation on
>> replication in 9.4 and 10.4 but things are very confusing to me about this
>> point.
>>
>>
> you can't use the in-build streaming replication between different major
> versions, but you can use trigger-based replications, for instance londiste
> or slony.
>
> You can also use use our extension pglogical, see
> https://www.2ndquadrant.com/en/resources/pglogical/. If i were you i
> would use the latter ;-)
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>


-- 
*Lionel TRESSENS*
Co-founder & CTO - LoungeUp

Mobile +33 6 61 34 01 42 <+33661340142>
Email lio...@loungeup.com
Website www.loungeup.com
  

Re: VBA to connect to postgresql from MS Access

[Łukasz Jarych]

Hi Adrian and Mike,

All is working fine, thank you !
Problem was with connection string and 32 bit computer.

My access is 32 bit so i should have odbc 32 bit driver for postgresql...

Best,
Jacek



2018-06-04 15:31 GMT+02:00 Adrian Klaver :

> On 06/03/2018 09:30 PM, Łukasz Jarych wrote:
>
>> Thank you Adrian,
>>
>> in answer to response in link:
>>
>> This connection string is not working for me.
>>
>
> How it is not working?
>
> Do you get error messages?
>
> And my conf file is in attachment.
>>
>> Only this strange DSN less conn string is working:
>>
>
> I am not following the below does use a DSN.
>
> What are the parameters for the DSN PostgreSQL35W?
>
>
>
>> ODBC;DSN=PostgreSQL35W;DATABASE=AccessTest;SERVER=localhost;
>> PORT=5432;*CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4*
>>
>>
>> Why?
>>
>> Best,
>> Jacek
>>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Setting up replication from 9.4 to 10.4

[Andreas Kretschmer]

Am 06.06.2018 um 08:16 schrieb Lionel Tressens:

Hello,

We are running a single PG 9.4 database node we are switching to PG 
10.4 (which will run as master + setup of a slave)


To minimize downtime when switching from the 9.4 server to the 10.4 
one, I would like to setup a replication stream instead of stopping 
the production, dumping 9.4 and restoring in 10.4 which my take a lot 
of time.


The next steps would be to reinstall the 9.4 server from scratch with 
10.4 and setup this host as a slave, ready to take the master role if 
needed (hot standby).


Can such a replication be established between 9.4 and 10.4 and what 
replication tool/schema to use ? I have been through the documentation 
on replication in 9.4 and 10.4 but things are very confusing to me 
about this point.




you can't use the in-build streaming replication between different major 
versions, but you can use trigger-based replications, for instance 
londiste or slony.


You can also use use our extension pglogical, see 
https://www.2ndquadrant.com/en/resources/pglogical/. If i were you i 
would use the latter ;-)



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Setting up replication from 9.4 to 10.4

[Lionel Tressens]

Hello,

We are running a single PG 9.4 database node we are switching to PG 10.4
(which will run as master + setup of a slave)

To minimize downtime when switching from the 9.4 server to the 10.4 one, I
would like to setup a replication stream instead of stopping the
production, dumping 9.4 and restoring in 10.4 which my take a lot of time.

The next steps would be to reinstall the 9.4 server from scratch with 10.4
and setup this host as a slave, ready to take the master role if needed
(hot standby).

Can such a replication be established between 9.4 and 10.4 and what
replication tool/schema to use ? I have been through the documentation on
replication in 9.4 and 10.4 but things are very confusing to me about this
point.

Thanks for your tips

Lionel