Re: Need Help On Upgrade

2023-07-11 Thread Johnathan Tiamoh 
Thank you very much @Adrian Klaver  .

On Tue, Jul 11, 2023 at 7:32 PM Adrian Klaver 
wrote:

> On 7/11/23 14:45, Johnathan Tiamoh wrote:
> > Hello Adrian
> >
> > 1) PgBouncer version.
> >
> > pgbouncer.1.7.2
>
> PgBouncer most recent version is from here:
>
> http://www.pgbouncer.org/changelog.html
>
> PgBouncer 1.19.x
>
> 2023-05-31 - PgBouncer 1.19.1 - “Sunny Spring”
>
>
>
>
> And since 1.7.x there have been these related auth changes:
>
> PgBouncer 1.11.x
>
> Add support for SCRAM authentication for clients and servers. A new
> authentication type scram-sha-256 is added.
>
> PgBouncer 1.12.x
>
> Accept SCRAM channel binding enabled clients. Previously, a client
> supporting channel binding (that is, PostgreSQL 11+) would get a
> connection failure when connecting to PgBouncer in certain situations.
> (PgBouncer does not support channel binding. This change just fixes
> support for clients that offer it.)
>
> PgBouncer 1.14.x
>
> Add SCRAM authentication pass-through. This allows using encrypted SCRAM
> secrets in PgBouncer (either in userlist.txt or from auth_query) for
> logging into servers
>
> PgBouncer 1.16.x
>
> Mixed use of md5 and scram via hba has been fixed.
>
> PgBouncer 1.17.x
>
> Don’t apply fast-fail at connect time. This is part of the
> above-mentioned change to not report server errors before
> authentication. It also fixes a particular situation with SCRAM
> pass-through authentication, where we need to allow the client-side
> authentication exchange in order to be able to fix the server-side
> connection by re-authenticating. The fast-fail mechanism still applies
> right after authentication, so the effective observed behavior will be
> the same in most situations.
>
>
> I think an update is in order.
>
> >
> > 2) Did you change the settings when going from 9.5 to 14.8?
> >
> > No. I did not do any configuration changes on the bouncers
> >
> > 3) Does the PgBouncer log show anything relevant?
> >
> > No. It does not show anything
> >
> > 4) Does '...through the  pgbouncers...' mean there is more then one
> > PgBouncer instance in use?
> >
> > Yes, I have more than 3 pgbouncers for different connections.
> >
> >
> >
> > On Tue, Jul 11, 2023 at 12:19 PM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 7/11/23 08:53, Johnathan Tiamoh wrote:
> >  > I can connect.
> >  >
> >  > All applications and other users that connect from to the
> databases
> >  > through the  pgbouncers can't connect.
> >
> > That would have been a good thing to have mentioned in your first
> post.
> > I don't use PgBouncer so I am not going to be of much use going
> > forward.
> > For those that do use it and can help answers to the following would
> be
> > helpful:
> >
> > 1) PgBouncer version.
> >
> > 2) Did you change the settings when going from 9.5 to 14.8?
> >
> > 3) Does the PgBouncer log show anything relevant?
> >
> > 4) Does '...through the  pgbouncers...' mean there is more then one
> > PgBouncer instance in use?
> >
> >  >
> >  > On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver
> >  > mailto:adrian.kla...@aklaver.com>
> >  > >> wrote:
> >  >
> >  > On 7/10/23 20:45, Laurenz Albe wrote:
> >  >  > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
> >  >  >> Below is the full error message.
> >  >  >>
> >  >  >> 2023-06-27 05:01:27.385 CDT| XXX
> (60930)|
> >  > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1|
> >  > authentication| PGE-28P01: FATAL:  password authentication
> failed
> >  > for user
> >  >  >> "grafana"
> >  >  >> 2023-06-27 05:01:27.385 CDT| XXX
> (60930)|
> >  > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2|
> >  > authentication| PGE-28P01: DETAIL:  Connection matched
> > pg_hba.conf
> >  > line 113:
> >  >  >> "hostall all 0.0.0.0/0 
> >  > > md5"
> >  >  >
> >  >  > Then you must have entered the wrong password.
> >  >  >
> >  >  > If in doubt, change the password.
> >  >
> >  > Can you connect to the database at all or is this specific to
> >  > certain users?
> >  >
> >  > What client(s) are you using and is the problem coming from a
> > specific
> >  > client?
> >  >
> >  >
> >  >
> >  >
> >  >  >
> >  >  > Yours,
> >  >  > Laurenz Albe
> >  >
> >  > --
> >  > Adrian Klaver
> >  > adrian.kla...@aklaver.com 
> >  >>
> >  >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com

Re: psql -af > out, is possible to also have raise notice, raise info part.

2023-07-11 Thread jian he 
On Wed, Jul 12, 2023 at 12:06 PM David G. Johnston
 wrote:
>
> On Tue, Jul 11, 2023 at 8:52 PM jian he  wrote:
>>
>> hi.
>>
>> test.sql content:
>> --
>> do $$
>> begin
>>   raise info 'information message %', now() ;
>>   raise debug 'debug message %', now();
>>   raise notice 'notice message %', now();
>> end $$;
>> --
>> psql -af test.sql > test.out
>>
>
> You've only redirected stdout (file # 1 - implied), the "raise" stuff goes to 
> stderr (file # 2)
>
> IIRC you can do:
>
> psql -af test.sql > test.out 2>&1
>
> (order matters, left-to-right)
>
> But you can search online for "output redirection in Linux" or some such if 
> you want to learn the Linux command line better.
>
> David J.
>

thanks.
I don't know that "raise" stuff goes to stderr.
To get rid of the line numbers, I use "psql -a < test.sql > test.out
2>&1 " to get the expected result.

Re: psql -af > out, is possible to also have raise notice, raise info part.

2023-07-11 Thread David G. Johnston 
On Tue, Jul 11, 2023 at 8:52 PM jian he  wrote:

> hi.
>
> test.sql content:
>
> --
> do $$
> begin
>   raise info 'information message %', now() ;
>   raise debug 'debug message %', now();
>   raise notice 'notice message %', now();
> end $$;
>
> --
> psql -af test.sql > test.out
>
>
You've only redirected stdout (file # 1 - implied), the "raise" stuff goes
to stderr (file # 2)

IIRC you can do:

psql -af test.sql > test.out 2>&1

(order matters, left-to-right)

But you can search online for "output redirection in Linux" or some such if
you want to learn the Linux command line better.

David J.

psql -af > out, is possible to also have raise notice, raise info part.

2023-07-11 Thread jian he 
hi.

test.sql content:
--
do $$
begin
  raise info 'information message %', now() ;
  raise debug 'debug message %', now();
  raise notice 'notice message %', now();
end $$;
--
psql -af test.sql > test.out

current result:
--
do $$
begin
  raise info 'information message %', now() ;
  raise notice 'notice message %', now();
end $$;
DO
--
expected result in test.out

do $$
begin
  raise info 'information message %', now() ;
  raise notice 'notice message %', now();
end $$;
INFO:  information message 2023-07-12 11:49:27.894126+08
NOTICE:  notice message 2023-07-12 11:49:27.894126+08
DO

Re: Need Help On Upgrade

2023-07-11 Thread Benedict Holland 
This also sounds like a fairly advanced setup and a corporate environment.
Postgresql offers paid support and you probably want that.

Thanks,
Ben

On Tue, Jul 11, 2023, 4:33 PM Adrian Klaver 
wrote:

> On 7/11/23 14:45, Johnathan Tiamoh wrote:
> > Hello Adrian
> >
> > 1) PgBouncer version.
> >
> > pgbouncer.1.7.2
>
> PgBouncer most recent version is from here:
>
> http://www.pgbouncer.org/changelog.html
>
> PgBouncer 1.19.x
>
> 2023-05-31 - PgBouncer 1.19.1 - “Sunny Spring”
>
>
>
>
> And since 1.7.x there have been these related auth changes:
>
> PgBouncer 1.11.x
>
> Add support for SCRAM authentication for clients and servers. A new
> authentication type scram-sha-256 is added.
>
> PgBouncer 1.12.x
>
> Accept SCRAM channel binding enabled clients. Previously, a client
> supporting channel binding (that is, PostgreSQL 11+) would get a
> connection failure when connecting to PgBouncer in certain situations.
> (PgBouncer does not support channel binding. This change just fixes
> support for clients that offer it.)
>
> PgBouncer 1.14.x
>
> Add SCRAM authentication pass-through. This allows using encrypted SCRAM
> secrets in PgBouncer (either in userlist.txt or from auth_query) for
> logging into servers
>
> PgBouncer 1.16.x
>
> Mixed use of md5 and scram via hba has been fixed.
>
> PgBouncer 1.17.x
>
> Don’t apply fast-fail at connect time. This is part of the
> above-mentioned change to not report server errors before
> authentication. It also fixes a particular situation with SCRAM
> pass-through authentication, where we need to allow the client-side
> authentication exchange in order to be able to fix the server-side
> connection by re-authenticating. The fast-fail mechanism still applies
> right after authentication, so the effective observed behavior will be
> the same in most situations.
>
>
> I think an update is in order.
>
> >
> > 2) Did you change the settings when going from 9.5 to 14.8?
> >
> > No. I did not do any configuration changes on the bouncers
> >
> > 3) Does the PgBouncer log show anything relevant?
> >
> > No. It does not show anything
> >
> > 4) Does '...through the  pgbouncers...' mean there is more then one
> > PgBouncer instance in use?
> >
> > Yes, I have more than 3 pgbouncers for different connections.
> >
> >
> >
> > On Tue, Jul 11, 2023 at 12:19 PM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 7/11/23 08:53, Johnathan Tiamoh wrote:
> >  > I can connect.
> >  >
> >  > All applications and other users that connect from to the
> databases
> >  > through the  pgbouncers can't connect.
> >
> > That would have been a good thing to have mentioned in your first
> post.
> > I don't use PgBouncer so I am not going to be of much use going
> > forward.
> > For those that do use it and can help answers to the following would
> be
> > helpful:
> >
> > 1) PgBouncer version.
> >
> > 2) Did you change the settings when going from 9.5 to 14.8?
> >
> > 3) Does the PgBouncer log show anything relevant?
> >
> > 4) Does '...through the  pgbouncers...' mean there is more then one
> > PgBouncer instance in use?
> >
> >  >
> >  > On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver
> >  > mailto:adrian.kla...@aklaver.com>
> >  > >> wrote:
> >  >
> >  > On 7/10/23 20:45, Laurenz Albe wrote:
> >  >  > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
> >  >  >> Below is the full error message.
> >  >  >>
> >  >  >> 2023-06-27 05:01:27.385 CDT| XXX
> (60930)|
> >  > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1|
> >  > authentication| PGE-28P01: FATAL:  password authentication
> failed
> >  > for user
> >  >  >> "grafana"
> >  >  >> 2023-06-27 05:01:27.385 CDT| XXX
> (60930)|
> >  > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2|
> >  > authentication| PGE-28P01: DETAIL:  Connection matched
> > pg_hba.conf
> >  > line 113:
> >  >  >> "hostall all 0.0.0.0/0 
> >  > > md5"
> >  >  >
> >  >  > Then you must have entered the wrong password.
> >  >  >
> >  >  > If in doubt, change the password.
> >  >
> >  > Can you connect to the database at all or is this specific to
> >  > certain users?
> >  >
> >  > What client(s) are you using and is the problem coming from a
> > specific
> >  > client?
> >  >
> >  >
> >  >
> >  >
> >  >  >
> >  >  > Yours,
> >  >  > Laurenz Albe
> >  >
> >  > --
> >  > Adrian Klaver
> >  > adrian.kla...@aklaver.com 
> >

Re: Is anyone using db_user_namespace?

2023-07-11 Thread Nathan Bossart 
On Tue, Jul 11, 2023 at 10:24:55AM +0900, Ian Lawrence Barwick wrote:
> Never used it myself; funnily enough I stumbled on it a few weeks back
> and wondered if it was some nifty feature I'd overlooked, then I realized
> I've stumbled across it before several times and each time promptly
> forgotten about it.
> 
> Moreover a quick Google search (in quotation marks) brings up a
> few pages mainly of documentation copies, configuration file snippets
> happening to contain "db_user_namespace", a few mostly very non-recent
> mailing list/forum questions, but nothing which screams "this feature
> is used widely".

Thanks for taking a look!

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Re: Need Help On Upgrade

2023-07-11 Thread Adrian Klaver 

On 7/11/23 14:45, Johnathan Tiamoh wrote:

Hello Adrian

1) PgBouncer version.

pgbouncer.1.7.2


PgBouncer most recent version is from here:

http://www.pgbouncer.org/changelog.html

PgBouncer 1.19.x

2023-05-31 - PgBouncer 1.19.1 - “Sunny Spring”




And since 1.7.x there have been these related auth changes:

PgBouncer 1.11.x

Add support for SCRAM authentication for clients and servers. A new 
authentication type scram-sha-256 is added.


PgBouncer 1.12.x

Accept SCRAM channel binding enabled clients. Previously, a client 
supporting channel binding (that is, PostgreSQL 11+) would get a 
connection failure when connecting to PgBouncer in certain situations. 
(PgBouncer does not support channel binding. This change just fixes 
support for clients that offer it.)


PgBouncer 1.14.x

Add SCRAM authentication pass-through. This allows using encrypted SCRAM 
secrets in PgBouncer (either in userlist.txt or from auth_query) for 
logging into servers


PgBouncer 1.16.x

Mixed use of md5 and scram via hba has been fixed.

PgBouncer 1.17.x

Don’t apply fast-fail at connect time. This is part of the 
above-mentioned change to not report server errors before 
authentication. It also fixes a particular situation with SCRAM 
pass-through authentication, where we need to allow the client-side 
authentication exchange in order to be able to fix the server-side 
connection by re-authenticating. The fast-fail mechanism still applies 
right after authentication, so the effective observed behavior will be 
the same in most situations.



I think an update is in order.



2) Did you change the settings when going from 9.5 to 14.8?

No. I did not do any configuration changes on the bouncers

3) Does the PgBouncer log show anything relevant?

No. It does not show anything

4) Does '...through the  pgbouncers...' mean there is more then one
PgBouncer instance in use?

Yes, I have more than 3 pgbouncers for different connections.



On Tue, Jul 11, 2023 at 12:19 PM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 7/11/23 08:53, Johnathan Tiamoh wrote:
 > I can connect.
 >
 > All applications and other users that connect from to the databases
 > through the  pgbouncers can't connect.

That would have been a good thing to have mentioned in your first post.
I don't use PgBouncer so I am not going to be of much use going
forward.
For those that do use it and can help answers to the following would be
helpful:

1) PgBouncer version.

2) Did you change the settings when going from 9.5 to 14.8?

3) Does the PgBouncer log show anything relevant?

4) Does '...through the  pgbouncers...' mean there is more then one
PgBouncer instance in use?

 >
 > On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver
 > mailto:adrian.kla...@aklaver.com>
>> wrote:
 >
 >     On 7/10/23 20:45, Laurenz Albe wrote:
 >      > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
 >      >> Below is the full error message.
 >      >>
 >      >> 2023-06-27 05:01:27.385 CDT| XXX (60930)|
 >     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1|
 >     authentication| PGE-28P01: FATAL:  password authentication failed
 >     for user
 >      >> "grafana"
 >      >> 2023-06-27 05:01:27.385 CDT| XXX (60930)|
 >     APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2|
 >     authentication| PGE-28P01: DETAIL:  Connection matched
pg_hba.conf
 >     line 113:
 >      >> "host    all all 0.0.0.0/0 
 >     > md5"
 >      >
 >      > Then you must have entered the wrong password.
 >      >
 >      > If in doubt, change the password.
 >
 >     Can you connect to the database at all or is this specific to
 >     certain users?
 >
 >     What client(s) are you using and is the problem coming from a
specific
 >     client?
 >
 >
 >
 >
 >      >
 >      > Yours,
 >      > Laurenz Albe
 >
 >     --
 >     Adrian Klaver
 > adrian.kla...@aklaver.com 
>
 >

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



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

Re: Need Help On Upgrade

2023-07-11 Thread Johnathan Tiamoh 
Hello Adrian

1) PgBouncer version.

pgbouncer.1.7.2

2) Did you change the settings when going from 9.5 to 14.8?

No. I did not do any configuration changes on the bouncers

3) Does the PgBouncer log show anything relevant?

No. It does not show anything

4) Does '...through the  pgbouncers...' mean there is more then one
PgBouncer instance in use?

Yes, I have more than 3 pgbouncers for different connections.



On Tue, Jul 11, 2023 at 12:19 PM Adrian Klaver 
wrote:

> On 7/11/23 08:53, Johnathan Tiamoh wrote:
> > I can connect.
> >
> > All applications and other users that connect from to the databases
> > through the  pgbouncers can't connect.
>
> That would have been a good thing to have mentioned in your first post.
> I don't use PgBouncer so I am not going to be of much use going forward.
> For those that do use it and can help answers to the following would be
> helpful:
>
> 1) PgBouncer version.
>
> 2) Did you change the settings when going from 9.5 to 14.8?
>
> 3) Does the PgBouncer log show anything relevant?
>
> 4) Does '...through the  pgbouncers...' mean there is more then one
> PgBouncer instance in use?
>
> >
> > On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 7/10/23 20:45, Laurenz Albe wrote:
> >  > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
> >  >> Below is the full error message.
> >  >>
> >  >> 2023-06-27 05:01:27.385 CDT| XXX (60930)|
> > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1|
> > authentication| PGE-28P01: FATAL:  password authentication failed
> > for user
> >  >> "grafana"
> >  >> 2023-06-27 05:01:27.385 CDT| XXX (60930)|
> > APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2|
> > authentication| PGE-28P01: DETAIL:  Connection matched pg_hba.conf
> > line 113:
> >  >> "hostall all 0.0.0.0/0
> >  md5"
> >  >
> >  > Then you must have entered the wrong password.
> >  >
> >  > If in doubt, change the password.
> >
> > Can you connect to the database at all or is this specific to
> > certain users?
> >
> > What client(s) are you using and is the problem coming from a
> specific
> > client?
> >
> >
> >
> >
> >  >
> >  > Yours,
> >  > Laurenz Albe
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: How to add function schema in search_path in option definitio

2023-07-11 Thread David G. Johnston 
On Sat, Jul 8, 2023 at 10:00 AM Lorusso Domenico 
wrote:

> Hello Adrian,
> I've created a schema to handle some specific features.
> In the schema there are a couple of tables used by many functions (more
> than 20).
> In other words, I've created a schema as a package (as suggested in many
> points).
>
> I wish, in a function of this schema, to be able to call each other
> functions of this schema without adding the schema name in the call
> instruction.
>

PostgreSQL isn't really designed for that - especially if you aren't
formally creating an extension but simply putting stuff into a schema.

The script code you use to install your makeshift package should handle
dynamic schema naming.  It's a pain, do you really need to allow the name
of the schema to be unknown at installation time?

You should read up on how extensions are implemented - you are basically
writing your own CREATE EXTENSION implementation.

https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION

In any case, there really aren't any smarts here: explicitly schema qualify
your function calls and forget that search_path even exists.  Unless you
are writing custom operators, and even then, consider search_path to be
evil.

David J.

Re: How to add function schema in search_path in option definitio

2023-07-11 Thread Adrian Klaver 

On 7/11/23 12:47, Lorusso Domenico wrote:

Adrian I have read your suggestions, but:
1) I need to change path inside a group of function (like a package) not 
for any connection

2) Yes inside function is clear I can do that
3) some like 1 and, moreover, I can't be sure to create my own role, 
because the target db will be cloud sql


First mention that you are using a fork of the community Postgres.

In any case Cloud SQL does allow creating roles:

https://cloud.google.com/sql/docs/postgres/users

My guess though is you will need to do 2).



My question doesn't aim just to fix a specific issue, but to learn the 
best way (if exist) to solve the class of issues (raised by the case) in 
postgresql.




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

Re: How to add function schema in search_path in option definitio

2023-07-11 Thread Lorusso Domenico 
Adrian I have read your suggestions, but:
1) I need to change path inside a group of function (like a package) not
for any connection
2) Yes inside function is clear I can do that
3) some like 1 and, moreover, I can't be sure to create my own role,
because the target db will be cloud sql

My question doesn't aim just to fix a specific issue, but to learn the best
way (if exist) to solve the class of issues (raised by the case) in
postgresql.

Il giorno mar 11 lug 2023 alle ore 21:41 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:

> On 7/11/23 12:35, Lorusso Domenico wrote:
> > yes, but this is a code inside each function.
> >   having 20 functions I've to start executing this instruction 20 times,
> > one per function.
> > I know it could work, I looking for something of more smart :-)
>
> Here:
>
>
> https://www.postgresql.org/message-id/66677cb3-17b3-20d1-1c22-9a7ac3208921%40aklaver.com
>
> I provide options 1) and 3) to use instead of including the path change
> in the functions(s).
>
> >
> > Il giorno dom 9 lug 2023 alle ore 23:21 Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> ha
> scritto:
> >
> > On 7/9/23 13:47, Lorusso Domenico wrote:
> >  > Hello Adrian and Peter,
> >  > yes the set parameter in function definition is also my preferred
> >  > choice, but
> >  >
> >  > I need to add the schema to path, not to substitute the path,
> > this is my
> >  > problem
> >  >
> >
> > This post:
> >
> >
> https://www.postgresql.org/message-id/9f3db764-804e-7154-3f8c-f5f19628178e%40aklaver.com
> <
> https://www.postgresql.org/message-id/9f3db764-804e-7154-3f8c-f5f19628178e%40aklaver.com
> >
> >
> > showed a way to do that.
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
> >
> >
> > --
> > Domenico L.
> >
> > per stupire mezz'ora basta un libro di storia,
> > io cercai di imparare la Treccani a memoria... [F.d.A.]
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

-- 
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

Re: Trigger Function question

2023-07-11 Thread Lorusso Domenico 
I've just finish to do the same thing.
For my needs, I decided to create a table with the information I need for
each view (yes I set trigger on view not on table).

anyway, hstore is more performant rather than jsonb but both of them could
treat NEW and OLD as hashmap (or associative array)


Il giorno lun 10 lug 2023 alle ore 20:53 Christophe Pettus 
ha scritto:

>
>
> > On Jul 10, 2023, at 11:46, DAVID ROTH  wrote:
> >
> > Is there a way to get new.* into a jsonb column?
>
> The to_jsonb() function accepts a row type like NEW.*, and returns a JSONB
> object with the keys as column names.
>
>

-- 
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

Re: How to add function schema in search_path in option definitio

2023-07-11 Thread Adrian Klaver 

On 7/11/23 12:35, Lorusso Domenico wrote:

yes, but this is a code inside each function.
  having 20 functions I've to start executing this instruction 20 times, 
one per function.

I know it could work, I looking for something of more smart :-)


Here:

https://www.postgresql.org/message-id/66677cb3-17b3-20d1-1c22-9a7ac3208921%40aklaver.com

I provide options 1) and 3) to use instead of including the path change 
in the functions(s).




Il giorno dom 9 lug 2023 alle ore 23:21 Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> ha scritto:


On 7/9/23 13:47, Lorusso Domenico wrote:
 > Hello Adrian and Peter,
 > yes the set parameter in function definition is also my preferred
 > choice, but
 >
 > I need to add the schema to path, not to substitute the path,
this is my
 > problem
 >

This post:


https://www.postgresql.org/message-id/9f3db764-804e-7154-3f8c-f5f19628178e%40aklaver.com
 


showed a way to do that.

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]


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

Re: How to add function schema in search_path in option definitio

2023-07-11 Thread Lorusso Domenico 
yes, but this is a code inside each function.
 having 20 functions I've to start executing this instruction 20 times, one
per function.
I know it could work, I looking for something of more smart :-)

Il giorno dom 9 lug 2023 alle ore 23:21 Adrian Klaver <
adrian.kla...@aklaver.com> ha scritto:

> On 7/9/23 13:47, Lorusso Domenico wrote:
> > Hello Adrian and Peter,
> > yes the set parameter in function definition is also my preferred
> > choice, but
> >
> > I need to add the schema to path, not to substitute the path, this is my
> > problem
> >
>
> This post:
>
>
> https://www.postgresql.org/message-id/9f3db764-804e-7154-3f8c-f5f19628178e%40aklaver.com
>
> showed a way to do that.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

-- 
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

Re: Moving data from huge table slow, min() query on indexed column taking 38s

2023-07-11 Thread Lorusso Domenico 
Hello Dimitry,
at the end, a table is a file with many other functionalities, these
functionalities consume resources.

If the DBMS (like oracle or db2) allow us to disable the functionalities so
we can perform a copy between tables, otherwise (and often also for oracle
and db2) the best approach is to use an export.

because export /import functionalities are very optimized to do their job.

Anyway, when you approach as DBA you must block the db or at least a table.
Don't try to  reorg schema or db with connected users.

Il giorno lun 10 lug 2023 alle ore 17:58 Dimitrios Apostolou 
ha scritto:

> Thanks! I have completed the transfer by taking down the database and
> working exclusively on it, but I still wonder how one could do it in
> production without exclusive locks. The loop with small DELETE...INSERT
> transactions I posted on the parent post bloats the table fast.
>
> The link you posted contains very useful info, I was not expecting that
> the constraints could blow up the memory like that. Comments from me:
>
> Disabling and then re-enabling the foreign key constraints is easily done
> with ALTER TABLE.
>
> Unfortunately it doesn't seem to be the same case for indices. One has to
> create the table without indices, and then create the indices separately.
> With such a process there is a risk of ending up with non-identical
> table schemas.
>
> By the way, with COPY one must use an intermediate file, right? There is
> no way to COPY from table to table directly?
>
>
> Thanks,
> Dimitris
>
> On Thu, 6 Jul 2023, Lorusso Domenico wrote:
>
> > Hello,
> > this is a standard problem during bulk copy.
> >
> > here some suggestions; for example disable indexes.
> >
> > The main issue is related to index, lock escalation and log writing.
> > In other dbms you should set log off on the table, but postgresql does
> not seem to have this feature.
> >
> > Anyway, using an explicit lock table exclusive should prevent lock
> escalation.
> >
> > So: disable indexes in target table
> > lock exclusive both table
> > insert data
> > truncate old table
> >
> > If this doesn't work you can consider using the copy command.
> >
> >
> >
> >
> >
> >
> >
> > Il giorno gio 6 lug 2023 alle ore 18:12 Dimitrios Apostolou <
> ji...@gmx.net> ha scritto:
> >   On Thu, 6 Jul 2023, Dimitrios Apostolou wrote:
> >
> >   > + First I chose the method to DELETE...INSERT everything.
> >
> >   Just to clarify, the query looked more like
> >
> >  WITH rows AS ( DELETE FROM tbl_legacy RETURNING * )
> >INSERT INTO tbl
> >  SELECT * FROM rows;
> >
> >   >   I noticed that the postgres process was growing without
> bounds, up to
> >   >   the point that it consumed almost all of swap space and I had
> to kill
> >   >   the command. Any idea why that happens?
> >
> >   Also note that my settings for work_mem, temp_buffers,
> shared_buffers etc
> >   are all well below the RAM size and postgres has never shown
> unbound
> >   growth again. Postgres version is 15.2.
> >
> >
> >   Dimitris
> >
> >
> >
> >
> >
> > --
> > Domenico L.
> >
> > per stupire mezz'ora basta un libro di storia,
> > io cercai di imparare la Treccani a memoria... [F.d.A.]
> >
> >
>


-- 
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

Re: BI Reports and Postgres

2023-07-11 Thread Gurjeet Singh 
On Tue, Jul 11, 2023 at 1:45 AM KK CHN  wrote:
>
> List,
> 1. For generating BI reports, which  Databases are more suitable ( RDBMS like 
> Postgres  OR NoSQL like MongoDB ) ? Which is best? Why ?
>
> 2. Is NoSQL DBs like MongoDB et all useful in which scenarios  and 
> application context ? or NoSQLs are losing the initial hype ?
>
> 3. Could someone point out which BI report tool (  OpenSource tool / Free 
> Software tool )  available for  generating BI reports from Postgres ?   What 
> does the community use ?
>
> 4. For Generating BI reports does it make sense to keep your data in RDBMS or 
> do we need to port data to MongoDB or similar NoSQLs ?
>
> Any hints are much appreciated.

Each one of your questions has multiple answers, and which answer you
choose will depend on many factors, like cost, tools you currently
use, talent you have access to, etc.

I believe what you need is a consultant or company that provides
professional services, who can evaluate your needs, and then suggest
solutions and tools that will work for you.

Postgres website has a section [1] that lists companies that provide
professional services and support for Postgres and its ecosystem.

[1]: https://www.postgresql.org/support/professional_support/

Best regards,
Gurjeet
http://Gurje.et

Re: Need Help On Upgrade

2023-07-11 Thread Adrian Klaver 

On 7/11/23 08:53, Johnathan Tiamoh wrote:

I can connect.

All applications and other users that connect from to the databases 
through the  pgbouncers can't connect.


That would have been a good thing to have mentioned in your first post. 
I don't use PgBouncer so I am not going to be of much use going forward. 
For those that do use it and can help answers to the following would be 
helpful:


1) PgBouncer version.

2) Did you change the settings when going from 9.5 to 14.8?

3) Does the PgBouncer log show anything relevant?

4) Does '...through the  pgbouncers...' mean there is more then one 
PgBouncer instance in use?




On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 7/10/23 20:45, Laurenz Albe wrote:
 > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
 >> Below is the full error message.
 >>
 >> 2023-06-27 05:01:27.385 CDT| XXX (60930)|
APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1|
authentication| PGE-28P01: FATAL:  password authentication failed
for user
 >> "grafana"
 >> 2023-06-27 05:01:27.385 CDT| XXX (60930)|
APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2|
authentication| PGE-28P01: DETAIL:  Connection matched pg_hba.conf
line 113:
 >> "host    all all 0.0.0.0/0
 md5"
 >
 > Then you must have entered the wrong password.
 >
 > If in doubt, change the password.

Can you connect to the database at all or is this specific to
certain users?

What client(s) are you using and is the problem coming from a specific
client?




 >
 > Yours,
 > Laurenz Albe

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



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

Re: Need Help On Upgrade

2023-07-11 Thread Johnathan Tiamoh 
I can connect.

All applications and other users that connect from to the databases through
the  pgbouncers can't connect.

On Tue, Jul 11, 2023 at 11:46 AM Adrian Klaver 
wrote:

> On 7/10/23 20:45, Laurenz Albe wrote:
> > On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:
> >> Below is the full error message.
> >>
> >> 2023-06-27 05:01:27.385 CDT| XXX (60930)|
> APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| authentication|
> PGE-28P01: FATAL:  password authentication failed for user
> >> "grafana"
> >> 2023-06-27 05:01:27.385 CDT| XXX (60930)|
> APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2| authentication|
> PGE-28P01: DETAIL:  Connection matched pg_hba.conf line 113:
> >> "hostall all 0.0.0.0/0 md5"
> >
> > Then you must have entered the wrong password.
> >
> > If in doubt, change the password.
>
> Can you connect to the database at all or is this specific to certain
> users?
>
> What client(s) are you using and is the problem coming from a specific
> client?
>
>
>
>
> >
> > Yours,
> > Laurenz Albe
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Need Help On Upgrade

2023-07-11 Thread Adrian Klaver 

On 7/10/23 20:45, Laurenz Albe wrote:

On Mon, 2023-07-10 at 20:38 -0400, Johnathan Tiamoh wrote:

Below is the full error message.

2023-06-27 05:01:27.385 CDT| XXX (60930)| 
APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 1| authentication| 
PGE-28P01: FATAL:  password authentication failed for user
"grafana"
2023-06-27 05:01:27.385 CDT| XXX (60930)| 
APPNAME=[unknown]| DB=postgres| USER=grafana| PID=9061| 2| authentication| 
PGE-28P01: DETAIL:  Connection matched pg_hba.conf line 113:
"host    all all 0.0.0.0/0 md5"


Then you must have entered the wrong password.

If in doubt, change the password.


Can you connect to the database at all or is this specific to certain users?

What client(s) are you using and is the problem coming from a specific 
client?







Yours,
Laurenz Albe


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

Re: Need Help On Upgrade

2023-07-11 Thread Johnathan Tiamoh 
Hello KK,

In the beginning, it was a little challenging, but as the process went on
it became easy. No, it was not a multistage upgrade. It was possible to
upgrade straight from 9.5 to 14.

We did not change anything on the schema. For data type, we alter all
tables that were created with OIDs to remove the OIDs. We remove all data
with abs time ( absolute time). We equally remove all data with
sql_identifier. We also created a script to check for unknown data type and
another to remove them.

For documentation, we did not use any specific documentation. We just
followed postgresql documentation on upgrading.  We created our one plan
and steps based on our clusters, ensuring we can safely roll back in case
of any challenges.


Thank you.
Johnathan T


On Tue, Jul 11, 2023 at 5:17 AM KK CHN  wrote:

> Hi Johnathan,
>
> 1. How complex was it upgrading from an old Postgres 9.5 to 14.8 ?   Is it
> multistage upgrading or single shot to 14.8 ?
>
> 2. What about the old schema ( data types of 9.8 when the database   was
> designed couple of years ago? and  the datatypes supported in 14.8  is
> there any manual work was involved in this migration , please let me know
> the manual works needed to perform while migration from 9.8 to 14.8)
>
> 3. Which documentation needs to be  followed to perform this upgrade ??
>
> Any hints much appreciated..
> Krishane
>
> On Mon, Jul 10, 2023 at 11:46 PM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> Hello,
>>
>> I upgraded from postgresql 9.5 to 14.8. After the upgrade users were not
>> able to connect due to password hash.
>>
>> The password encryption was on scram-sha-256. The password hash was on
>> md5. in the pg_hba.conf file the authentication method was md5. I do not
>> know why users could not connect because as per postgresql documentation,
>> that should work.
>>
>> I have previously done a similar upgrade and did not face similar issues.
>>
>>
>> Kind regards
>> Johnathan Tiamoh
>>
>

Re: search_path for replica-mode

2023-07-11 Thread André Kutepow 

Thanks David G. Johnston

Am 10.07.2023 um 15:11 schrieb David G. Johnston:

On Monday, July 10, 2023, André Kutepow  wrote:

There is a trigger in the database

SET search_path TO "$user", public;

CREATE OR REPLACE FUNCTION art__a_iu_func()
BEGIN
  INSERT INTO table_z...   --//»table_z« is in schema public//
END;

CREATE OR REPLACE TRIGGER art__a_iu
    AFTER INSERT OR UPDATE OF ak_nr
    ON art
    FOR EACH ROW
    EXECUTE FUNCTION art__a_iu_func();

In a regular trigger, it works great!
But, if I announce it, as:

ALTER TABLE art ENABLE REPLICA TRIGGER art__a_iu;
then I get an error:

/FEHLER:  Relation »table_z« existiert nicht bei Zeichen xxx//
//ANFRAGE:  INSERT INTO table_z...

/why does'nt work the search_path for replica-mode?
is it made specifically or is it a bug?
How else can I set the search_path for replica-mode?


Relying on external search_path for system executed objects is 
problematic.  Don’t do it. Either attach a SET to the function or 
schema-qualify references.


David J.

BI Reports and Postgres

2023-07-11 Thread KK CHN 
List,
1. For generating BI reports, which  Databases are more suitable ( RDBMS
like Postgres  OR NoSQL like MongoDB ) ? Which is best? Why ?

2. Is NoSQL DBs like MongoDB et all useful in which scenarios  and
application context ? or NoSQLs are losing the initial hype ?

3. Could someone point out which BI report tool (  OpenSource tool / Free
Software tool )  available for  generating BI reports from Postgres ?
 What does the community use ?

4. For Generating BI reports does it make sense to keep your data in RDBMS
or do we need to port data to MongoDB or similar NoSQLs ?

Any hints are much appreciated.
Krishane

Re: Problematic enforcement of "ERROR: functions in index predicate must be marked IMMUTABLE"

2023-07-11 Thread Kyotaro Horiguchi 
At Mon, 10 Jul 2023 21:28:12 -0400, Tom Lane  wrote in 
> In the end, adding such restrictions would just give a false sense
> of security, because there would always be gaps in whatever we did.
> As you quote from the documentation, volatility markings are a promise
> by the user to the system, not vice versa.  If you break your promise,
> you get to keep both pieces of whatever trouble ensues.

I agree to you, as I mentioned a-bit-too-late message..

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: how to return data from insert into ... on conflict ... returning ... into

2023-07-11 Thread Gurjeet Singh 
On Tue, Jun 20, 2023 at 1:26 PM Les  wrote:
>
> https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
>
> and the only possible actions are "do nothing" and "do update". The "do 
> nothing" does not update the record, that is clear. But it also does not 
> return any data, and that was not obvious to me. Why can't it return the data 
> from the conflicted record? After the instruction is completed, "GET 
> DIAGNOSTICS" could still be used to check the number of updates.

Would it be fair to summarize that you'd like a feature where, upon
conflict, the command behaves as SELECT, and returns row that caused
the conflict?

The docs say:  "If the INSERT command contains a RETURNING clause, the
result will be similar to that of a SELECT statement containing the
columns and values defined in the RETURNING list, computed over the
row(s) inserted or updated by the command."

So in your desired behaviour, the RETURNING list would be computed
over the rows that cause the conflict.

> I understand that changing this behaviour may break backward compatibility. 
> Would it be possible to introduce a "DO RETURN" clause that returns the data, 
> even when there was a conflict?

If this behaviour is introduced with a new syntax, then there won't be
any fears of breaking backwards compatibility. I think DO SELECT as
the new syntax will make the intent clear. Taking one of your queries
as an example, after the new syntax it will look as follows:

insert into tbl(d) values ('1') on conflict(h) DO SELECT returning id into aid;

Best regards,
Gurjeet
http://Gurje.et