Re: Postgres SQL unable to handle Null values for Text datatype
> org.postgresql.util.PSQLException: ERROR: operator does not exist: character > varying = bytea This has been discussed on Stack Overflow[0]. The answer with the highest approval suggests to use coalesce[1]: ``` Select * from A where middle_name = coalesce(?1) ``` Lutz [0] https://stackoverflow.com/a/54223586 [1] https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL
Postgres SQL unable to handle Null values for Text datatype
Hi Team, We are migrating from Oracle 12C to Aurora Postgres 13 and running into query failures when the bind value of a Text datatype resolves to null. The same query works fine in Oracle without any issues. We use SpringDataJPA and Hibernate framework to connect and execute queries and the application uses native queries. Here is an example query: *Select * from A where middle_name=?1* The above query fails with the below exception when the value of ?1 resolves to null. *org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 64* We debugged through the Hibernate code comparing Oracle vs Postgres for the same query to understand if the framework was doing anything different when switched to Postgres and didn't notice any difference in the behaviour. We have also set *transform_null_equals *to ON in Postgres..but this doesn't help. Could you please let us know if there are any other configurations that need to be set in Postgres to make it work similar to Oracle? This issue is impacting multiple modules in our application and any help will be appreciated. -- Karthik klv
Re: Changing the admin/postgres user password
On Mon, Sep 5, 2022 at 7:40 PM Jeffrey Walton wrote: > > Or maybe better, what part of the manual discusses auth failures so I > can read about them? > The chapter named "Client Authentication ". https://www.postgresql.org/docs/current/client-authentication.html It even has a subchapter named "Authentication Problems" though it seems like discussion of (typical) default peer dynamics is missing. In particular, if you connect via local socket (also a default) the order of entries in (typical default) pg_hba.conf says you must login using peer, not password. David J.
Re: Changing the admin/postgres user password
> On Sep 5, 2022, at 19:40, Jeffrey Walton wrote: > And finally, try the new password: > >PGPASSWORD=hi...HS psql -U postgres >psql: error: connection to server on socket > "/var/run/postgresql/.s.PGSQL.5432" failed: >FATAL: Peer authentication failed for user "postgres" If it's complaining about peer authentication, you are most likely not logged in as the "postgres" Linux user. If you want other Linux users to be able to log in as the "postgres" PostgreSQL user, you'll need to edit your pg_hba.conf file to allow it; it's off by default.
Changing the admin/postgres user password
Hi Everyone, I'm struggling to set the admin/postgres user password and use it on Fedora 36. This is a fresh install on a new VM. The admin's name is postgres. We created the user, and used the passwd utility to set the Linux password 'hi...HS'. The ellipses are over 28 characters, so it is a 32-character password. Next, move onto Postgres auth. I followed https://www.postgresql.org/docs/current/install-short.html . We used 'su - postgres' and logged on with Linux password. I changed the postgres password with 'alter user postgres with password hi...HS'. The change appeared to be successful. Next, restart the service: sudo systemctl restart postgresql.service And finally, try the new password: PGPASSWORD=hi...HS psql -U postgres psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres" It appears the password is not working. I screwed something up somewhere. Ugh... Does anyone know what I did wrong in this process? Or maybe better, what part of the manual discusses auth failures so I can read about them? Thanks in advance. Jeff
Re: Missing query plan for auto_explain.
On Tue, Aug 30, 2022 at 1:38 PM Matheus Martin < matheus.mar...@voidbridge.com> wrote: > Our Postgres recently started reporting considerably different execution > times for the same query. When executed from our JDBC application the > Postgres logs report an average execution time of 1500 ms but when the query > is manually executed through `psql` it doesn't take longer than 50 ms. > > With a view to investigate discrepancies in the plan we enabled > `auto_explain` in `session_preload_libraries` with > `auto_explain.log_min_duration = '1s'`. All application servers were bounced > to ensure new connections were created and picked up the changes. However > this trouble query does not have an explain plan printed, even when its > execution time exceeds the threshold (other queries do though). > > Does anyone have ideas of why the explain plan is not being printed? > > Sample log entry for trouble query executed from application: > ``` > Aug 26 09:11:33 db-931 postgres[8106]: [66-1] 2022-08-26 09:11:33 GMT [8106]: > [5-1] db=betwave,user=betwave_app_readonly_user LOG: duration: 1423.481 ms > bind : > > My understanding of how to auto_explain work - it deals only for execution calls, but in your case duration: 1423.481 ms on BIND call, before query execution. At least in my understanding - auto_explain cannot work and will not help in case of a slow BIND call (because it's a time when the query is planned but not executed). According documentation: "Query planning typically occurs when the Bind message is processed. If the prepared statement has no parameters, or is executed repeatedly, the server might save the created plan and re-use it during subsequent Bind messages for the same prepared statement." Hard to say what the reason for slow planning, but one (there could be others) likely reason is JIT work. Do you have JIT enabled? -- Maxim Boguk Senior Postgresql DBA https://dataegret.com/ Phone UA: +380 99 143 Phone AU: +61 45 218 5678 LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b Skype: maxim.boguk "Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"
Re: Missing query plan for auto_explain.
`auto_explain.log_min_duration` is set to 500 ms. On Mon, 5 Sept 2022 at 12:35, Peter J. Holzer wrote: > On 2022-09-02 10:58:58 +0100, Matheus Martin wrote: > > Yes, we do see some plans logged by the auto_explain. We couldn't find a > > `auto_explain.log_min_duration_statements` setting > > This is weird as the documentation says: > > | Note that the default behavior is to do nothing, so you must set at > | least auto_explain.log_min_duration if you want any results. > > What does > show auto_explain.log_min_duration; > return? > > > but `log_min_duration_statement` as in > > https://www.postgresql.org/docs/current/ runtime-config-logging.html > > is set to 100 ms. > > I don't think this affects auto_explain. > > hp > > -- >_ | Peter J. Holzer| Story must make more sense than reality. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
RE: Unable to archive logs in standby server
Hi Kyotaro, This helped, thanks. Regards, Meera -Original Message- From: Kyotaro Horiguchi Sent: Monday, September 5, 2022 7:31 AM To: Meera Nair Cc: guilla...@lelarge.info; pgsql-general@lists.postgresql.org; Punit Pranesh Koujalgi Subject: Re: Unable to archive logs in standby server External email. Inspect before opening. At Tue, 30 Aug 2022 05:22:56 +, Meera Nair wrote in > Hi Guillaume/team, > > I set archive_mode = always in master and standby. > Archival to standby WAL directory completed when > > * standby server was restarted > * pg_stop_backup was executed in master > > But archival hangs when pg_stop_backup is executed in standby. > Could someone help to get this working? https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F12%2Fcontinuous-archiving.html&data=05%7C01%7Cmnair%40commvault.com%7C240c3d6f31074e32206c08da8ee277fe%7C40ed1e38a16e46229d7c45161b6969d5%7C0%7C0%7C637979400603662396%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=UHsS8ZG1Vll0z8p3ce6WPfm3IniqVYMtOPfin3Lahbg%3D&reserved=0 > In the same connection as before, issue the command: > > SELECT * FROM pg_stop_backup(false, true); > > This terminates backup mode. On a primary, it also performs an > automatic switch to the next WAL segment. On a standby, it is not > possible to automatically switch WAL segments, so you may wish to run > pg_switch_wal on the primary to perform a manual switch. The reason > for the switch is to arrange for the last WAL segment file written > during the backup interval to be ready to archive. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
Re: How to check if checkpoint is finished in sql script?
On Mon, Sep 05, 2022 at 04:55:28PM +0800, Yi Sun wrote: > How to check if the checkpoint is finished in sql script please? We know > that the log file will show it, but we want to check it in sql then can > easily be used by ansible, thanks Well, if the command "checkpoint" finished, and returned, and there is no error - then checkpoint has finished. Best regards, depesz
How to check if checkpoint is finished in sql script?
Hello all, We want to restart postgresql 3 nodes(2 replica nodes) by ansible as below steps: 1. Restart 2 replica nodes one by one 2. Run checkpoint in the leader node 3. Once checkpoint finished, restart the leader node How to check if the checkpoint is finished in sql script please? We know that the log file will show it, but we want to check it in sql then can easily be used by ansible, thanks Best regards Oliver Sun
