Re: Doubt in pgbouncer

[Rama Krishnan]

Yeah.  Rob

On Fri, 2 Oct, 2020, 05:14 Rob Sargent,  wrote:

>
>
> > On Oct 1, 2020, at 5:42 PM, Rama Krishnan  wrote:
> >
> > Hi Friends,
> >
> > By using pg bouncer can we split read and queries
> >
> > Thank
> > Rk
>
> Did you mean reads vs writes?
>

Re: Doubt in pgbouncer

[Rama Krishnan]

Yeah rob can  we spilt read/write queries using pgbouncer

On Fri, 2 Oct, 2020, 08:43 Rama Krishnan,  wrote:

> Yeah.  Rob
>
> On Fri, 2 Oct, 2020, 05:14 Rob Sargent,  wrote:
>
>>
>>
>> > On Oct 1, 2020, at 5:42 PM, Rama Krishnan  wrote:
>> >
>> > Hi Friends,
>> >
>> > By using pg bouncer can we split read and queries
>> >
>> > Thank
>> > Rk
>>
>> Did you mean reads vs writes?
>>
>

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Adrian Klaver]

On 10/1/20 4:08 PM, tutilu...@tutanota.com wrote:





Yeah, this isn't rude or insulting at all...



It would be refreshing to hear your be honest for once and just admit 
that you *want* it to be difficult. You *like* that there's a high 
threshold and it makes you feel superior to exclude "dumb" people who 
can't figure out all these cryptic (and downright broken) things. I 
truly believe that this is the reason for a lot of "weird" things which 
seem to make no sense on the surface.


Yes, if it makes you fell better we had a secret meeting where we 
decided to make your life and only your life a living hell when it came 
to dealing with Postgres.



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

Re: temp table same name real table

[David G. Johnston]

On Wed, Sep 30, 2020 at 7:41 AM Tom Lane  wrote:

> If you really really need to do this, I'd counsel using EXECUTE to
> ensure no caching happens.  But I concur with Michael that it's
> fundamentally a bad idea.
>

Agreed, though the documentation seems a bit loose here.  The fact that the
temp table hides the permanent one is a side-effect of pg_temp being placed
first in the default search_path.  If it is explicitly placed last the
permanent table would be found again.

Adding a reminder that search_path searching happens only during new plan
creation (even if we don't generally cover caching implementation in
detail, though I didn't look around for this one) seems like a good value.

I propose the following:

diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index 087cad184c..a400334092 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -171,8 +171,9 @@ WITH ( MODULUS numeric_literal, REM
   If specified, the table is created as a temporary table.
   Temporary tables are automatically dropped at the end of a
   session, or optionally at the end of the current transaction
-  (see ON COMMIT below).  Existing permanent
-  tables with the same name are not visible to the current session
+  (see ON COMMIT below).  The default
+  search_path includes the temporary schema first and so identically
+  named existing permanent tables are not chosen for new plans
   while the temporary table exists, unless they are referenced
   with schema-qualified names. Any indexes created on a temporary
   table are automatically temporary as well.

David J.

Re: Doubt in pgbouncer

[Rob Sargent]

> On Oct 1, 2020, at 5:42 PM, Rama Krishnan  wrote:
> 
> Hi Friends, 
> 
> By using pg bouncer can we split read and queries
> 
> Thank
> Rk

Did you mean reads vs writes?

Doubt in pgbouncer

[Rama Krishnan]

Hi Friends,

By using pg bouncer can we split read and queries

Thank
Rk

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[Christophe Pettus]

> On Oct 1, 2020, at 16:08, tutilu...@tutanota.com wrote:
> But of course I should be grateful no matter what because it doesn't cost 
> money.

No one is asking you to be grateful.  However, you are asking for other people 
to do things that important to you, but not them.  They are not required to do 
so.  If you cannot persuade them, and are not in a position to pay them, then 
that's a reality you'll just have to accept.

--
-- Christophe Pettus
   x...@thebuild.com

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

[tutiluren]

 

> On Thu, Sep 24, 2020 at 10:40 PM <> tutilu...@tutanota.com> > wrote:
>
>>
>>
>>> Well not partial as in incremental. Instead dump only some portion of the 
>>> schema with or without its associated data.
>>>
>> It's funny that you should bring that up, considering how it was one of my 
>> points... See the point about pg_dump's bug on Windows.
>>
>
> And you seem to have ignored the fact that one of the core developers pointed 
> out that it likely isn't a pg_dump bug - if your terminal is using the same 
> locale as the database, it should have no difficulty dealing with the 
> characters you are having trouble with.  It seems likely that you simply need 
> to learn how to get your terminal set up correctly for it to work.
>
Yeah, this isn't rude or insulting at all...

Funny how my "incorrectly set up terminal" works perfectly for all other 
programs and my own test scripts, but not for pg_dump specifically. And only 
when using "special" characters. As already pointed out multiple times, in 
great detail, to deaf ears. Very interesting how you can manage to twist and 
bend that into it still somehow being "my fault". Because of course it cannot 
be pg_dump's fault. Absolutely not. It is unthinkable. It's the "rude user"'s 
fault who had the audacity to point out yet another PG bug which more than 
likely won't *ever* be fixed, as it's not even recognized, much less cared 
about. Probably because they *want* PostgreSQL to be crippled on Windows, 
judging by the responses in the past and how incredibly broken the joke of an 
installer is.

You should call it "Linux software with minimal pre-alpha Windows support" 
instead of pretending that it's cross-platform, and that goes for many FOSS 
projects as well which think exactly the same as you. The fact that I still use 
this garbage OS (Windows) speaks volumes of how incredibly crappy Linux is, 
which is utterly *unusable*.

But of course I should be grateful no matter what because it doesn't cost 
money. Because my time and energy is worthless. And the competition is "even 
worse", so that means I cannot point out any fault, ever, no matter how serious 
or how easily it could be fixed. I should just shut up and thank everyone for 
insulting me through carelessness and words. Or "fix it myself", because that's 
obviously an option as I haven't done it so far...

I did read the rest of your e-mail, but it would be pointless to reply to it as 
you clearly have the mentality that everyone should dedicate their lives to 
configuring a database and buying books instead of using it, because everyone 
should be core developers and everything must always be cryptic and difficult 
and blablabla. I'm sick of this attitude, and especially of being called "rude" 
by such rude-beyond-words people.

It would be refreshing to hear your be honest for once and just admit that you 
*want* it to be difficult. You *like* that there's a high threshold and it 
makes you feel superior to exclude "dumb" people who can't figure out all these 
cryptic (and downright broken) things. I truly believe that this is the reason 
for a lot of "weird" things which seem to make no sense on the surface.

Re: Rows removed on child table when updating parent partitioned table.

[Jonathan Strong]

On Thu, Oct 1, 2020 at 2:02 PM David G. Johnston 
wrote:

> The convention on these lists is to inline or bottom-post, please do not
> top-post.
>
> On Thu, Oct 1, 2020 at 10:41 AM Jonathan Strong 
> wrote:
>
>> I've been away from coding for several years, but dusting off my chops
>> and getting back up to speed with PostgreSQL (love it!). So please forgive
>> me if my early answers here come off as naive. But my understanding of this
>> suggests that you shouldn't be using "update" on a serial field.
>>
>
> Yes Jonathan, your present understanding is flawed.  The OP has provided
> a self-contained simple test case for the problem at hand - which even if
> not "best practice" is indeed valid to do and demonstrates the problem
> quite clearly.  Without actually testing it out I would say that this is
> likely indeed an oversight in the partition row movement feature - it
> didn't take into account the ON UPDATE/ON DELETE clause.
>

 Understood - thx. I'll watch and learn for a while. Thanks as well for the
list etiquette advice. I'll endeavor to follow convention.

- Jon

Re: Rows removed on child table when updating parent partitioned table.

[David G. Johnston]

The convention on these lists is to inline or bottom-post, please do not
top-post.

On Thu, Oct 1, 2020 at 10:41 AM Jonathan Strong 
wrote:

> I've been away from coding for several years, but dusting off my chops and
> getting back up to speed with PostgreSQL (love it!). So please forgive me
> if my early answers here come off as naive. But my understanding of this
> suggests that you shouldn't be using "update" on a serial field.
>

Yes Jonathan, your present understanding is flawed.  The OP has provided a
self-contained simple test case for the problem at hand - which even if not
"best practice" is indeed valid to do and demonstrates the problem quite
clearly.  Without actually testing it out I would say that this is likely
indeed an oversight in the partition row movement feature - it didn't take
into account the ON UPDATE/ON DELETE clause.

Adding Robert Hass who committed the row movement feature [1].

We document on the UPDATE reference page that such an update is performed
as a DELETE + INSERT.  Given that implementation detail, the observed
behavior is what one would expect if no special consideration has been
given to make row movement between partitions preserve (via deferred
evaluation), or recreate the foreign key relationship.

For now I would say you should consider the two features incompatible; and
we need to update the documentation to reflect that reality more directly,
barring a solution being proposed, and hopefully back-patched, instead.  I
concur with the observation that one would expect these two features to
interact better with each other and think it could possibly be done as a
bug fix for the POLA violation.

David J.

[1]
https://github.com/postgres/postgres/commit/2f178441044be430f6b4d626e4dae68a9a6f6cec

Re: Rows removed on child table when updating parent partitioned table.

[Jonathan Strong]

I've been away from coding for several years, but dusting off my chops and
getting back up to speed with PostgreSQL (love it!). So please forgive me
if my early answers here come off as naive. But my understanding of this
suggests that you shouldn't be using "update" on a serial field. I'm
guessing that under the covers things are getting confused because your
update doesn't also address the sequence that's implicitly created when you
define a field as "serial". If you use "update" I'm guessing that nextval
in the corresponding sequence is *not* updated accordingly.

Have you tried this with setval() or nextval() rather than update? You can
compare the difference between these and "update" by checking currval()
after each. Again - I apologize for incomplete knowledge here, but I'm
speculating that use of "update" on an auto-incrementing serial field is
outside intended / supported behavior, and it may well just be that it
winds up being handled differently under the covers when the data table
and/or associated sequence are partitioned.



- Jon




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Thu, Oct 1, 2020 at 1:00 PM Eduard Català 
wrote:

> Hi developers,
> We have a strange case where some rows are removed. I think it is a bug,
> but before notifying it I prefer to ask here where I am wrong.
>
> *Postgres 12*
> Given the following structure:
>
> create table parent ( id serial, constraint parent_pkey primary key (id))
> partition by range (id);
> create table parent_10 partition of parent for values from (0) to (10);
> create table parent_20 partition of parent for values from (11) to (20);
>
>
> create table child (
>  id serial,
>  parent_id int constraint parent_id_fk references parent(id) on update
> cascade on delete cascade);
>
> -- Notice the on update cascade on delete cascade.
>
> insert into parent values(0);
> insert into child values(1,0);
>
> -- Here are the rows
>
> postgres=# table parent;
>  id
> 
>   0
> (1 row)
>
> postgres=# table child;
>  id | parent_id
> +---
>   1 | 0
> (1 row)
>
>
> *-- Update the parent table id, with a value contained in the same
> partition*
> update parent set id = 5;
>
> postgres=# table parent;
>  id
> 
>   5
> (1 row)
>
> postgres=# table child;
>  id | parent_id
> +---
>   1 | 5
> (1 row)
>
>
> *-- Update the parent table, with a value contained into other partition*
> update parent set id = 15;
>
> postgres=# update parent set id = 15;
> UPDATE 1
> postgres=# table parent;
>  id
> 
>  15
> (1 row)
>
>
>
>
> *postgres=# table child; id | parent_id+---(0 rows)*
>
> No error or warning was thrown. The rows in the child table were removed.
> I think what has happened is: The update caused a DELETE in the table
> parent_10 (removing the rows from child table) and then the INSERT into
> parent_20.
>
> We've checked the documentation but didn't find something about this
> unexpected behaviour.
>
> Trying without "on delete cascade" clause throws a "parent key not found
> error".
>
> Thank you!
>
>
>
>
>

Re: pg_upgrade issue upgrading 10 -> 13

[Bruce Momjian]

On Thu, Oct  1, 2020 at 09:03:31AM -0700, Adrian Klaver wrote:
> On 10/1/20 7:51 AM, Sean Brown wrote:
> > I’m having a little problem using pg_upgrade to move from 10 to 13, I’m 
> > assuming the issue is related to the removal of pg_pltemplate, but I can’t 
> > find anything related to how to handle it.
> > 
> > pg_upgrade —check reports that the clusters are compatible, but the actual 
> > upgrade fails starting the new cluster with -
> > 
> > ERROR: relation “pg_catalog.pg_pltemplate” does not exist
> > STATEMENT: GRANT SELECT ON TABLE “pg_catalog"."pg_pltemplate” TO "appuser”;
> 
> Which points to the dangers of doing things to the system tables. They can
> change/disappear between major versions.

And pg_dump (used by pg_upgrade) had little handling for such changes.

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Rows removed on child table when updating parent partitioned table.

[Eduard Català]

Hi developers,
We have a strange case where some rows are removed. I think it is a bug,
but before notifying it I prefer to ask here where I am wrong.

*Postgres 12*
Given the following structure:

create table parent ( id serial, constraint parent_pkey primary key (id))
partition by range (id);
create table parent_10 partition of parent for values from (0) to (10);
create table parent_20 partition of parent for values from (11) to (20);


create table child (
 id serial,
 parent_id int constraint parent_id_fk references parent(id) on update
cascade on delete cascade);

-- Notice the on update cascade on delete cascade.

insert into parent values(0);
insert into child values(1,0);

-- Here are the rows

postgres=# table parent;
 id

  0
(1 row)

postgres=# table child;
 id | parent_id
+---
  1 | 0
(1 row)


*-- Update the parent table id, with a value contained in the same
partition*
update parent set id = 5;

postgres=# table parent;
 id

  5
(1 row)

postgres=# table child;
 id | parent_id
+---
  1 | 5
(1 row)


*-- Update the parent table, with a value contained into other partition*
update parent set id = 15;

postgres=# update parent set id = 15;
UPDATE 1
postgres=# table parent;
 id

 15
(1 row)




*postgres=# table child; id | parent_id+---(0 rows)*

No error or warning was thrown. The rows in the child table were removed.
I think what has happened is: The update caused a DELETE in the table
parent_10 (removing the rows from child table) and then the INSERT into
parent_20.

We've checked the documentation but didn't find something about this
unexpected behaviour.

Trying without "on delete cascade" clause throws a "parent key not found
error".

Thank you!

Re: pg_upgrade issue upgrading 10 -> 13

[Adrian Klaver]

On 10/1/20 7:51 AM, Sean Brown wrote:

I’m having a little problem using pg_upgrade to move from 10 to 13, I’m 
assuming the issue is related to the removal of pg_pltemplate, but I can’t find 
anything related to how to handle it.

pg_upgrade —check reports that the clusters are compatible, but the actual 
upgrade fails starting the new cluster with -

ERROR: relation “pg_catalog.pg_pltemplate” does not exist
STATEMENT: GRANT SELECT ON TABLE “pg_catalog"."pg_pltemplate” TO "appuser”;


Which points to the dangers of doing things to the system tables. They 
can change/disappear between major versions.




Is there a way to deal with this that doesn’t include dropping the source table?




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

Re: pg_upgrade issue upgrading 10 -> 13

[Bruce Momjian]

On Thu, Oct  1, 2020 at 05:28:53PM +0200, Thomas Kellerer wrote:
> 
> 
> Sean Brown schrieb am 01.10.2020 um 16:51:
> > I’m having a little problem using pg_upgrade to move from 10 to 13,
> > I’m assuming the issue is related to the removal of pg_pltemplate,
> > but I can’t find anything related to how to handle it.
> > 
> > pg_upgrade —check reports that the clusters are compatible, but the
> > actual upgrade fails starting the new cluster with -
> > 
> > ERROR: relation “pg_catalog.pg_pltemplate” does not exist STATEMENT:
> > GRANT SELECT ON TABLE “pg_catalog"."pg_pltemplate” TO "appuser”;
> > 
> > Is there a way to deal with this that doesn’t include dropping the
> > source table?
> > 
> 
> Does revoking the privilege before running pg_upgrade help?
> 
> So in the v10 database:
> 
>   revoke select on pg_catalog.pg_pltemplate from appuser;

Yeah, there must be a reference to pg_catalog.pg_pltemplate somewhere
that was missed.  I think a simple dump/restore would also error on the
restore, but a normal restore might ignore the error, while pg_upgrade
will not.

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Re: pg_upgrade issue upgrading 10 -> 13

[Thomas Kellerer]

Sean Brown schrieb am 01.10.2020 um 16:51:

I’m having a little problem using pg_upgrade to move from 10 to 13,
I’m assuming the issue is related to the removal of pg_pltemplate,
but I can’t find anything related to how to handle it.

pg_upgrade —check reports that the clusters are compatible, but the
actual upgrade fails starting the new cluster with -

ERROR: relation “pg_catalog.pg_pltemplate” does not exist STATEMENT:
GRANT SELECT ON TABLE “pg_catalog"."pg_pltemplate” TO "appuser”;

Is there a way to deal with this that doesn’t include dropping the
source table?



Does revoking the privilege before running pg_upgrade help?

So in the v10 database:

  revoke select on pg_catalog.pg_pltemplate from appuser;

pg_upgrade issue upgrading 10 -> 13

[Sean Brown]

I’m having a little problem using pg_upgrade to move from 10 to 13, I’m 
assuming the issue is related to the removal of pg_pltemplate, but I can’t find 
anything related to how to handle it.

pg_upgrade —check reports that the clusters are compatible, but the actual 
upgrade fails starting the new cluster with -

ERROR: relation “pg_catalog.pg_pltemplate” does not exist
STATEMENT: GRANT SELECT ON TABLE “pg_catalog"."pg_pltemplate” TO "appuser”;

Is there a way to deal with this that doesn’t include dropping the source table?

Betr: Re: FATAL: terminating connection due to administrator command

[Alban Hertroys]

"Srinivasa T N"  wrote on 01/10/2020 11:47:33:

> On Thu, Oct 1, 2020 at 2:47 PM Alban Hertroys <
> alban.hertr...@apollovredestein.com> wrote:
> Hi all, 
> 
> We're seeing the FATAL error message from the subject pop up in our 
> logs at regular intervals, but I haven't been able to pinpoint what 
> is causing it. I'm hoping for some insights here. 
> 
> We run a PostgreSQL 11.9 server on CentOS 7, within a vmware 
environment: 
>  PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-39), 64-bit 
> 
> The package was installed from the PGDG repository. 
> 
> I'm not even sure I should be worried, there doesn't appear to be 
> any impact on the servers' functioning, but it does say 'FATAL'. 
> What we're seeing are lines like these two instances: 
> 
> 2020-09-30 22:27:56.446 CEST [30659]   STATEMENT:  select count(*) 
> from "dm_b2b"."prlwytzkofskiv1" 
> 2020-09-30 22:27:56.446 CEST [30658]   FATAL:  terminating 
> connection due to administrator command 
> 2020-09-30 22:27:56.446 CEST [30658]   STATEMENT:  select count(*) 
> from "dm_b2b"."prlwytzkofskiv1" 
> 2020-09-30 22:27:56.446 CEST [30657]   FATAL:  terminating 
> connection due to administrator command 
> 2020-09-30 22:27:56.446 CEST [30657]   STATEMENT:  select count(*) 
> from "dm_b2b"."prlwytzkofskiv1" 
> 2020-09-30 22:27:56.446 CEST [30656]   FATAL:  terminating 
> connection due to administrator command 
> 2020-09-30 22:27:56.446 CEST [30656]   STATEMENT:  select count(*) 
> from "dm_b2b"."prlwytzkofskiv1" 
> 2020-09-30 22:27:56.446 CEST [30655]   FATAL:  terminating 
> connection due to administrator command 
> 2020-09-30 22:27:56.446 CEST [30655]   STATEMENT:  select count(*) 
> from "dm_b2b"."prlwytzkofskiv1" 
> 2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker 
> "parallel worker" (PID 30655) exited with exit code 1 
> 2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker 
> "parallel worker" (PID 30656) exited with exit code 1 
> 2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker 
> "parallel worker" (PID 30657) exited with exit code 1 
> 2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker 
> "parallel worker" (PID 30658) exited with exit code 1 
> 2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker 
> "parallel worker" (PID 30659) exited with exit code 1 
> 2020-09-30 22:43:08.459 CEST [8055] 172.30.2.25 selfservice_prd 
> ERROR:  schema "somethingelse" does not exist at character 71 

> I am guessing that 6 background workers are started, 1 worker had 
> the result and hence killing the other 5 workers.  Maybe, some more 
> pg experts can comment.  Anyway, explain of your query helps.

I think you may have the right of it:

   QUERY PLAN
--
 Finalize Aggregate  (cost=3065970.74..3065970.75 rows=1 width=8)
   ->  Gather  (cost=3065970.21..3065970.72 rows=5 width=8)
 Workers Planned: 5
 ->  Partial Aggregate  (cost=3064970.21..3064970.22 rows=1 
width=8)
   ->  Nested Loop Left Join  (cost=2772.30..2743631.23 
rows=128535594 width=0)
 Join Filter: ((avl.xx)::text <> ''::text)
 ->  Parallel Hash Left Join  (cost=2772.01..943286.00 
rows=5574292 width=13)
   Hash Cond: (avl.x = 
(dc.x)::integer)
   ->  Parallel Seq Scan on  
avl  (cost=0.00..596772.71 rows=5574171 width=21)
   ->  Parallel Hash  (cost=2262.01..2262.01 
rows=40800 width=8)
 ->  Parallel Index Only Scan using 
 on  dc  (cost=0.42..2
 ->  Index Scan using ix_ 
on xxx dm  (cost=0.29..0.31 rows=1 width=19)
   Index Cond: ((avl.xx)::text = 
(xx)::text)
   Filter: ((xx)::text <> ''::text)
(14 rows)

So, apparently these FATAL errors are just caused by parallel workers 
being aborted because they're no longer needed. Good to know.

Regards,
Alban.


Alban  Hertroys 
D: +31 (0)53 4 888 888  | T: +31 (0)53 4888 888 | E: 
alban.hertr...@apollovredestein.com
Apollo Vredestein B.V.| Ir. E.L.C. Schiffstraat 370, 7547 RD Enschede, The 
Netherlands
Chamber of Commerce number: 34223268




 

 
The information contained in this e-mail is intended solely for the use of the 
individual or entity to whom it is addressed. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution 
or action in relation to the contents of this information is strictly 
prohibited and may be unlawful and request you to delete this message and any 
attachments and advise the sender by return e-mail. The 

Re: FATAL: terminating connection due to administrator command

[Srinivasa T N]

On Thu, Oct 1, 2020 at 2:47 PM Alban Hertroys <
alban.hertr...@apollovredestein.com> wrote:

> Hi all,
>
> We're seeing the FATAL error message from the subject pop up in our logs
> at regular intervals, but I haven't been able to pinpoint what is causing
> it. I'm hoping for some insights here.
>
> We run a PostgreSQL 11.9 server on CentOS 7, within a vmware environment:
>  PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-39), 64-bit
>
> The package was installed from the PGDG repository.
>
> I'm not even sure I should be worried, there doesn't appear to be any
> impact on the servers' functioning, but it does say 'FATAL'.
> What we're seeing are lines like these two instances:
>
> 2020-09-30 22:27:56.446 CEST [30659]   STATEMENT:  select count(*) from
> "dm_b2b"."prlwytzkofskiv1"
> 2020-09-30 22:27:56.446 CEST [30658]   FATAL:  terminating connection due
> to administrator command
> 2020-09-30 22:27:56.446 CEST [30658]   STATEMENT:  select count(*) from
> "dm_b2b"."prlwytzkofskiv1"
> 2020-09-30 22:27:56.446 CEST [30657]   FATAL:  terminating connection due
> to administrator command
> 2020-09-30 22:27:56.446 CEST [30657]   STATEMENT:  select count(*) from
> "dm_b2b"."prlwytzkofskiv1"
> 2020-09-30 22:27:56.446 CEST [30656]   FATAL:  terminating connection due
> to administrator command
> 2020-09-30 22:27:56.446 CEST [30656]   STATEMENT:  select count(*) from
> "dm_b2b"."prlwytzkofskiv1"
> 2020-09-30 22:27:56.446 CEST [30655]   FATAL:  terminating connection due
> to administrator command
> 2020-09-30 22:27:56.446 CEST [30655]   STATEMENT:  select count(*) from
> "dm_b2b"."prlwytzkofskiv1"
> 2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker "parallel
> worker" (PID 30655) exited with exit code 1
> 2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker "parallel
> worker" (PID 30656) exited with exit code 1
> 2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker "parallel
> worker" (PID 30657) exited with exit code 1
> 2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker "parallel
> worker" (PID 30658) exited with exit code 1
> 2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker "parallel
> worker" (PID 30659) exited with exit code 1
> 2020-09-30 22:43:08.459 CEST [8055] 172.30.2.25 selfservice_prd ERROR:
>  schema "somethingelse" does not exist at character 71
>
> I am guessing that 6 background workers are started, 1 worker had the
result and hence killing the other 5 workers.  Maybe, some more pg experts
can comment.  Anyway, explain of your query helps.

Regards,
Seenu.

Apparently, something is sending SIGTERM to our pg processes. I know that
> I'm not doing that, certainly not at those hours, and I'm the one who set
> up this system and am the only DBA of it.
>
> Advice I found on the Internet is to use systemtap with some tap-script,
> but the scripts that I found just displayed the PID's of processes without
> telling me their names, which I didn't find all that useful in figuring out
> who was responsible, so I made an attempt (I have no experience with stap)
> at modifying it to print process names of signal sender and target:
>
>
> *The information contained in this e-mail is intended solely for the use
> of the individual or entity to whom it is addressed. If you are not the
> intended recipient, you are hereby notified that any disclosure, copying,
> distribution or action in relation to the contents of this information is
> strictly prohibited and may be unlawful and request you to delete this
> message and any attachments and advise the sender by return e-mail. The
> confidentiality of this message is not warranted. Apollo Vredestein and its
> subsidiaries rule out any and every liability resulting from this or any
> other electronic transmission*
>Please consider the environment before printing this e-mail
>

FATAL: terminating connection due to administrator command

[Alban Hertroys]

Hi all,

We're seeing the FATAL error message from the subject pop up in our logs 
at regular intervals, but I haven't been able to pinpoint what is causing 
it. I'm hoping for some insights here.

We run a PostgreSQL 11.9 server on CentOS 7, within a vmware environment:
 PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
20150623 (Red Hat 4.8.5-39), 64-bit

The package was installed from the PGDG repository.

I'm not even sure I should be worried, there doesn't appear to be any 
impact on the servers' functioning, but it does say 'FATAL'.
What we're seeing are lines like these two instances:

2020-09-30 21:45:09.999 CEST [2375] 172.30.2.25 asdf STATEMENT:  select 
count(*) from "util_asdf"."v_something_something2"
2020-09-30 21:45:15.018 CEST [2375] 172.30.2.25 asdf ERROR:  canceling 
statement due to user request
2020-09-30 21:45:15.018 CEST [2375] 172.30.2.25 asdf STATEMENT:  select 
count(*) from "dm_asdf"."asdf_func"
2020-09-30 21:45:20.027 CEST [2375] 172.30.2.25 asdf ERROR:  canceling 
statement due to user request
2020-09-30 21:45:20.027 CEST [2375] 172.30.2.25 asdf STATEMENT:  select 
count(*) from "dm_asdf"."asdf_func"
2020-09-30 22:27:51.422 CEST [20270] 172.30.2.26 selfservice_sales ERROR: 
canceling statement due to user request
2020-09-30 22:27:51.422 CEST [20270] 172.30.2.26 selfservice_sales 
STATEMENT:  select count(*) from "dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:51.422 CEST [30649]   FATAL:  terminating connection due 
to administrator command
2020-09-30 22:27:51.422 CEST [30649]   STATEMENT:  select count(*) from 
"dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:51.422 CEST [30648]   FATAL:  terminating connection due 
to administrator command
2020-09-30 22:27:51.422 CEST [30648]   STATEMENT:  select count(*) from 
"dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:51.422 CEST [30647]   FATAL:  terminating connection due 
to administrator command
2020-09-30 22:27:51.422 CEST [30647]   STATEMENT:  select count(*) from 
"dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:51.422 CEST [30646]   FATAL:  terminating connection due 
to administrator command
2020-09-30 22:27:51.422 CEST [30646]   STATEMENT:  select count(*) from 
"dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:51.422 CEST [30645]   FATAL:  terminating connection due 
to administrator command
2020-09-30 22:27:51.422 CEST [30645]   STATEMENT:  select count(*) from 
"dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:51.435 CEST [6482]   LOG:  background worker "parallel 
worker" (PID 30646) exited with exit code 1
2020-09-30 22:27:51.435 CEST [6482]   LOG:  background worker "parallel 
worker" (PID 30645) exited with exit code 1
2020-09-30 22:27:51.435 CEST [6482]   LOG:  background worker "parallel 
worker" (PID 30647) exited with exit code 1
2020-09-30 22:27:51.435 CEST [6482]   LOG:  background worker "parallel 
worker" (PID 30648) exited with exit code 1
2020-09-30 22:27:51.435 CEST [6482]   LOG:  background worker "parallel 
worker" (PID 30649) exited with exit code 1
2020-09-30 22:27:56.446 CEST [20270] 172.30.2.26 selfservice_sales ERROR: 
canceling statement due to user request
2020-09-30 22:27:56.446 CEST [20270] 172.30.2.26 selfservice_sales 
STATEMENT:  select count(*) from "dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:56.446 CEST [30659]   FATAL:  terminating connection due 
to administrator command
2020-09-30 22:27:56.446 CEST [30659]   STATEMENT:  select count(*) from 
"dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:56.446 CEST [30658]   FATAL:  terminating connection due 
to administrator command
2020-09-30 22:27:56.446 CEST [30658]   STATEMENT:  select count(*) from 
"dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:56.446 CEST [30657]   FATAL:  terminating connection due 
to administrator command
2020-09-30 22:27:56.446 CEST [30657]   STATEMENT:  select count(*) from 
"dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:56.446 CEST [30656]   FATAL:  terminating connection due 
to administrator command
2020-09-30 22:27:56.446 CEST [30656]   STATEMENT:  select count(*) from 
"dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:56.446 CEST [30655]   FATAL:  terminating connection due 
to administrator command
2020-09-30 22:27:56.446 CEST [30655]   STATEMENT:  select count(*) from 
"dm_b2b"."prlwytzkofskiv1"
2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker "parallel 
worker" (PID 30655) exited with exit code 1
2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker "parallel 
worker" (PID 30656) exited with exit code 1
2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker "parallel 
worker" (PID 30657) exited with exit code 1
2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker "parallel 
worker" (PID 30658) exited with exit code 1
2020-09-30 22:27:56.459 CEST [6482]   LOG:  background worker "parallel 
worker" (PID 30659) exited with exit code 1
2020-09-30 22:43:08.459 CEST [8055] 172.30.2.25 selfservice_prd ERROR: 
schema "somethingelse" does not exist at character 71

Apparently, something is sending SIGTERM to our pg 

Re: Problem close curser after rollback

[Matthias Apitz]

El día miércoles, septiembre 30, 2020 a las 02:37:23p. m. -0400, Tom Lane 
escribió:

> Matthias Apitz  writes:
> > El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. +0200, Laurenz 
> > Albe escribió:
> >> On Wed, 2020-09-30 at 13:32 +, Wiltsch,Sigrid wrote:
> >>> What can I do so that the cursor is retained despite rollback?
> 
> >> You cannot start a transaction while you are reading a cursor; you probably
> >> get a warning "there is already a transaction in progress".
> 
> > I think we will prepare the ten-liner in ESQL/C for further discussion.
> 
> I don't think you really need to: the point seems clear enough.

I did wrote the ten-liner to play around with. Interestingly, there exists an
undocumented ESQL/C statement 'EXEC SQL START TRANSACTION' which gives
in the esqlc log:

ECPGtrans on line 48: action "start transaction"; connection "sisis"

What as well does work is the following sequence:

EXEC SQL PREPARE stmt1 FROM "SELECT tstchar25, tstint FROM dbctest 
WHERE tstint > ?";
EXEC SQL DECLARE foo_bar CURSOR WITH HOLD  FOR stmt1 ;

/* when end of result set reached, break out of while loop */
EXEC SQL WHENEVER NOT FOUND DO BREAK;

EXEC SQL OPEN foo_bar USING 1;

while (1)
{
EXEC SQL FETCH NEXT FROM foo_bar INTO :tstchar25, :tstint;

EXEC SQL COMMIT ;

EXEC SQL START TRANSACTION ;
printf("fetched: [%s] [%d] \n", tstchar25, tstint);

// ... do something with the fetched data and because
// it went wrong, we issue a ROLLBACK

EXEC SQL ROLLBACK ;
}

This fetches nicely through the table in the while-loop; without the
additional COMMIT, the START TRANSACTION gives

ECPGtrans on line 48: action "start transaction"; connection "sisis"
ECPGnoticeReceiver: there is already a transaction in progress

We will think now about what we have learned and how to repair our
application.

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин)
Without books no knowledge - without knowledge no communism (Vladimir Ilyich 
Lenin)
Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)