pganalyze setup

[Atul Kumar]

Hi,

Could you provide me any link or blog to install and configure
pganalyze setup on centos 7 machine.

Also please let me whether pganalyze is an open source or not.



Regards.

Re: pganalyze setup

[David G. Johnston]

On Wednesday, July 28, 2021, Atul Kumar  wrote:
>
> Could you provide me any link or blog to install and configure
> pganalyze setup on centos 7 machine.
>
> Also please let me whether pganalyze is an open source or not.
> .
>

 Here is their homepage:

https://pganalyze.com/

Re: PostgreSQL reference coffee mug

[Matthias Apitz]

El día martes, julio 27, 2021 a las 08:32:45p. m. +0200, Matthias Apitz 
escribió:

> Thank you, Pavel. This is ofc to much for a coffee mug. For using it as
> a Reference Card in paper form, it's a pity that it is not written in English.
> 
> I'm working on my own for the mug and will publish the PDF and
> libreoffice ODT version here. The max size of the image for the mug is
> 7.5cm high x 16cm around the body of the coffee mug.
> 

Attached is a first version as PDF. Bugs/comments are welcome. Thanks.

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Tear it down! Defund the Humboldt Forum!
https://www.jungewelt.de/artikel/406715.humboldt-forum-feudaler-themenpark.html


postgreSQL-coffee-mug.pdf
Description: Adobe PDF document

Re: PostgreSQL reference coffee mug

[Georg H.]

Hello Matthias,

Am 28.07.2021 um 15:40 schrieb Matthias Apitz:

El día martes, julio 27, 2021 a las 08:32:45p. m. +0200, Matthias Apitz 
escribió:


Thank you, Pavel. This is ofc to much for a coffee mug. For using it as
a Reference Card in paper form, it's a pity that it is not written in English.

I'm working on my own for the mug and will publish the PDF and
libreoffice ODT version here. The max size of the image for the mug is
7.5cm high x 16cm around the body of the coffee mug.


only typos:

\o file snnds  -> sends

\passwor  -> password

createdb ... dbna -> dbname



Attached is a first version as PDF. Bugs/comments are welcome. Thanks.

matthias


regards

Georg

Re: PostgreSQL reference coffee mug

[Adrian Klaver]

On 7/28/21 8:01 AM, Georg H. wrote:

Hello Matthias,

Am 28.07.2021 um 15:40 schrieb Matthias Apitz:
El día martes, julio 27, 2021 a las 08:32:45p. m. +0200, Matthias 
Apitz escribió:



Thank you, Pavel. This is ofc to much for a coffee mug. For using it as
a Reference Card in paper form, it's a pity that it is not written in 
English.


I'm working on my own for the mug and will publish the PDF and
libreoffice ODT version here. The max size of the image for the mug is
7.5cm high x 16cm around the body of the coffee mug.


only typos:

\o file snnds  -> sends

\passwor  -> password

createdb ... dbna -> dbname


To add to above:

\set\s allone -> alone





Attached is a first version as PDF. Bugs/comments are welcome. Thanks.

matthias


regards

Georg






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

Re: PostgreSQL reference coffee mug

[Alvaro Herrera]

On 2021-Jul-28, Adrian Klaver wrote:

> On 7/28/21 8:01 AM, Georg H. wrote:

> To add to above:
> 
> \set\s allone -> alone

Actually that's wrong, because \s prints history not variables.  This
needs to read
  \set   alone ...

I'm *not* going to get into this, because I know I'm capable of spending
days on it.  I would definitely look into using different font sizes and
colors and make it much denser, for one thing.

-- 
Álvaro Herrera PostgreSQL Developer  —  https://www.EnterpriseDB.com/

Re: PostgreSQL reference coffee mug

[Matthias Apitz]

Thanks to all hints about typos and bug. Attached is a corrected version
as PDF.

El día miércoles, julio 28, 2021 a las 11:38:22a. m. -0400, Alvaro Herrera 
escribió:


> 
> I'm *not* going to get into this, because I know I'm capable of spending
> days on it.  I would definitely look into using different font sizes and
> colors and make it much denser, for one thing.

I'm attaching as well the source for libreoffice calc, so you are free
to make a nicer version. Do not forget the final size in PDF as
7.5cm x 16cm.

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Tear it down! Defund the Humboldt Forum!
https://www.jungewelt.de/artikel/406715.humboldt-forum-feudaler-themenpark.html


postgreSQL-coffee-mug.ods
Description: application/vnd.oasis.opendocument.spreadsheet


postgreSQL-coffee-mug.pdf
Description: Adobe PDF document

DO like block for for anonymous procedures

[Vijaykumar Jain]

Hi,

PostgreSQL: Documentation: 13: DO

Is it possible to run a DO block for multiple transactions ?
I am not sure if i'll be able explain it more verbally, but

-- the entire DO block like a function block is a single tx
postgres=# do $$
declare x bigint;
begin
for i in 1..10 loop
select txid_current()::bigint into x;
raise notice '%', x;
end loop;
end
$$;
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
NOTICE:  779
DO


 -- is it possible for a DO block to execute multiple txs
postgres=# create or replace procedure pp() as $$
declare x bigint;
begin
for i in 1..10 loop
select txid_current()::bigint into x;
commit;
raise notice '%', x;
end loop;
end; $$ language plpgsql;
CREATE PROCEDURE
postgres=# call pp();
NOTICE:  781
NOTICE:  782
NOTICE:  783
NOTICE:  784
NOTICE:  785
NOTICE:  786
NOTICE:  787
NOTICE:  788
NOTICE:  789
NOTICE:  790
CALL

one of the use case would be batch inserts, but from within a single psql
session

create table t(id int primary key);
postgres=# truncate table t;
TRUNCATE TABLE
postgres=# do $$
declare valuelist int[] := ARRAY[1,2,3,4,5,1]; -- purposely inserting
duplicate that would rollback everything
declare i int;
begin
for i in select k from unnest(valuelist) p(k) loop
insert into t values(i);
raise notice 'trying to insert %', i;
end loop;
end; $$;
NOTICE:  trying to insert 1
NOTICE:  trying to insert 2
NOTICE:  trying to insert 3
NOTICE:  trying to insert 4
NOTICE:  trying to insert 5
ERROR:  duplicate key value violates unique constraint "t_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  SQL statement "insert into t values(i)"
PL/pgSQL function inline_code_block line 6 at SQL statement

--- so everything got rolled back, as duplicate key, table t empty

postgres=# create or replace procedure proc_ins() as $$
declare valuelist int[] := ARRAY[1,2,3,4,5,1];
declare i int;
begin
for i in select k from unnest(valuelist) p(k) loop
insert into t values(i);
raise notice 'trying to insert %', i;
commit; -- explict commit, every insert in a new tx.
end loop;
end; $$ language plpgsql;
CREATE PROCEDURE

postgres=# call proc_ins();
NOTICE:  trying to insert 1
NOTICE:  trying to insert 2
NOTICE:  trying to insert 3
NOTICE:  trying to insert 4
NOTICE:  trying to insert 5
ERROR:  duplicate key value violates unique constraint "t_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  SQL statement "insert into t values(i)"
PL/pgSQL function proc_ins() line 6 at SQL statement
--only the erroneous data insert failed, but earlier committed data was
successful.

postgres=# table t;
 id

  1
  2
  3
  4
  5
(5 rows)


Ok, there might be better ways to do this using insert on conflict,
handling exceptions etc, but I hope you got my point.

I would go on and say DO block to  waste transactions to simulate
wraparound with minimum concurrent connections, but that would divert the
discussion,
hence just keeping it simple.
you can point me to docs, if i am missing the obvious reference for what a
DO serves  or what it was created for.

-- 
Thanks,
Vijay
Mumbai, India

Re: DO like block for for anonymous procedures

[Vijaykumar Jain]

please ignore, i overlooked the obvious.

truncate table t;
TRUNCATE TABLE
postgres=# do $$
declare valuelist int[] := ARRAY[1,2,3,4,5,1]; -- purposely inserting
duplicate that would rollback everything
declare i int;
begin
for i in select k from unnest(valuelist) p(k) loop
insert into t values(i);
raise notice 'trying to insert %', i; commit;
end loop;
end; $$;
NOTICE:  trying to insert 1
NOTICE:  trying to insert 2
NOTICE:  trying to insert 3
NOTICE:  trying to insert 4
NOTICE:  trying to insert 5
ERROR:  duplicate key value violates unique constraint "t_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  SQL statement "insert into t values(i)"
PL/pgSQL function inline_code_block line 6 at SQL statement
postgres=# table t;
 id

  1
  2
  3
  4
  5
(5 rows)


sorry.

Re: PostgreSQL reference coffee mug

[Matthias Apitz]

El día miércoles, julio 28, 2021 a las 06:58:08p. m. +0200, Matthias Apitz 
escribió:

> > I'm *not* going to get into this, because I know I'm capable of spending
> > days on it.  I would definitely look into using different font sizes and
> > colors and make it much denser, for one thing.
> 
> I'm attaching as well the source for libreoffice calc, so you are free
> to make a nicer version. Do not forget the final size in PDF as
> 7.5cm x 16cm.

I printed the PDF on paper, cut it to the size of 7.5cm x 16cm and
wrapped it around the mugs I have here. 16cm is not correct. My mugs have
more or less 7-8cm as diameter which gives around:

$ bc -l
>>> 7.5 * pi(2)
23.550

I will go tomorrow to the copy shop and ask/measure it there...
Which means also, we have more space for more information.

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Tear it down! Defund the Humboldt Forum!
https://www.jungewelt.de/artikel/406715.humboldt-forum-feudaler-themenpark.html

Re: PostgreSQL reference coffee mug

[Steve Litt]

Matthias Apitz said on Wed, 28 Jul 2021 15:40:22 +0200

>El día martes, julio 27, 2021 a las 08:32:45p. m. +0200, Matthias
>Apitz escribió:
>
>> Thank you, Pavel. This is ofc to much for a coffee mug. For using it
>> as a Reference Card in paper form, it's a pity that it is not
>> written in English.
>> 
>> I'm working on my own for the mug and will publish the PDF and
>> libreoffice ODT version here. The max size of the image for the mug
>> is 7.5cm high x 16cm around the body of the coffee mug.
>> 
>
>Attached is a first version as PDF. Bugs/comments are welcome. Thanks.
>
>   matthias

Very, very nice! I'll be using this quite a bit.

Does there exist anywhere a Reference Card for PostgreSQL SQL commands?
My impression is that every database has its own slightly different
version of SQL that it responds to.

Thanks,

SteveT

Steve Litt 
Spring 2021 featured book: Troubleshooting Techniques of the Successful
Technologist http://www.troubleshooters.com/techniques

Re: How postgres is refreshing TLS certificates

[Michael Paquier]

On Wed, Jul 28, 2021 at 06:51:22AM +, M Tarkeshwar Rao wrote:
> We are working on a activity in which I need to refresh the TLS
> certificate without restarting the my application pod. 
> This feature is already there in Postgres. Can anyone please suggest
> us how postgres is implemented the same?

Hard to answer with so little detail, but if you are referring to the
backend server, aren't you looking for the fact that SSL contexts and
its surrounding applications can be reloaded?  That would apply after
a simple pg_ctl "reload" for example.
--
Michael


signature.asc
Description: PGP signature