thg – Lots of Tech and a bit of Health

PostgreSQL Watch out timestamp or now() function returns timestamp with timezone offset not compatible with pl/pgsql timestamp data type

Leave a Comment / Amazon AWS, Aurora, PostgreSQL, PostgreSQL / By thg

Run the two commands below and you will see what I mean: select now() AT TIME ZONE current_setting(‘timezone’); 2023-09-30 09:07:31.251 select now(); 2023-09-30 09:17:51.338 -0400 select current_timestamp; select current_timestamp AT TIME ZONE current_setting(‘timezone’); select localtimestamp; So if you have a pl/pgsql procedure that accepts a TIMESTAMP data type, you can’t pass in just “now()” as …

PostgreSQL Watch out timestamp or now() function returns timestamp with timezone offset not compatible with pl/pgsql timestamp data type Read More »

Manipulating PL/PGSQL or PL/SQL Procedures Requires Knowing the Call Specification or Signature

Leave a Comment / PL/PGSQL, PL/SQL / By thg

when dropping, recreating, granting access to a pl/sql procedure the entire call specification (including parameters or parameter data types) is required to insure you are affecting the appropriate procedure – this is also referred to as footprint, and or procedure specification or signature.

ignore errors from bash “find” command

Leave a Comment / Amazon Linux 2, Linux RHEL/CentOS/OEL, Mac OS, OEL (Linux) / By thg

Just want to ignore all the permission denied and find the file? sudo cd / sudo find . -name connect-console-sink.properties 2>/dev/null The “2>/dev/null” just writes all error messages to the bitbucket/null device

PostgreSQL – How to get all roles that a user is a member of

Leave a Comment / PostgreSQL, PostgreSQL / By thg

select a.oid as user_role_id , a.rolname as user_role_name , b.roleid as other_role_id , c.rolname as other_role_name from pg_roles a inner join pg_auth_members b on a.oid=b.member inner join pg_roles c on b.roleid=c.oid where a.rolname = ‘postgres’

Implementing the equivalent of an autonomous transaction / Oracle pragma in postgresql

Leave a Comment / Oracle, PostgreSQL, PostgreSQL / By thg

https://aws.amazon.com/blogs/database/migrating-oracle-autonomous-transactions-to-postgresql/ in other words how to write a error log to a table and separately commit that record

The Most Important PostgreSQL Monitoring Views

Leave a Comment / PostgreSQL, PostgreSQL / By thg

Here is the entire list for native on-prem PostgreSQL: https://www.postgresql.org/docs/current/monitoring.html And here are my favorites from that list. PG_STAT_ACTIVITY – shows background activity for a running connection – not service by a connection pooler https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW PG_STAT_REPLICATION – the publication side view shows status of a publications subscribers – lag to them – must be queried …

The Most Important PostgreSQL Monitoring Views Read More »

PostgreSQL – How big is my table

Leave a Comment / PostgreSQL, PostgreSQL / By thg

SELECT pg_size_pretty (pg_relation_size (‘table_1’)) ;

PostgreSQL optimization – the best of YouTube series

Leave a Comment / PostgreSQL, PostgreSQL / By thg

The Best of Series momjian.us/presentations The presentation above addresses requirements open source (mostly free solutions) for PostgreSQL functionality to: – High Availability – No application reconfigure after fail-over/switch – RepMgr (an open source tool for managing PostgreSQL replication and failover) – RPO (recovery point objective / maximum amount of committed data lost) = 0 after …

PostgreSQL optimization – the best of YouTube series Read More »

PostgreSQL connection pooling for on premise or AWS

Leave a Comment / PostgreSQL, PostgreSQL / By thg

On-prem? PgBouncer AWS RDS Postgres or AWS RDS Aurora/PostgreSQL? AWS RDS Proxy

PostgreSQL New Features – well really just a feature matrix – but nicely done all in one place

Leave a Comment / PostgreSQL, PostgreSQL / By thg

https://www.postgresql.org/about/featurematrix/