Using Common Table Expressions CTE or With Clause in SQL
https://www.postgresql.org/docs/current/queries-with.html
PostgreSQL Watch out timestamp or now() function returns timestamp with timezone offset not compatible with pl/pgsql timestamp data type
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 …
Manipulating PL/PGSQL or PL/SQL Procedures Requires Knowing the Call Specification or Signature
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
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
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
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
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 …
PostgreSQL – How big is my table
SELECT pg_size_pretty (pg_relation_size (‘table_1’)) ;
PostgreSQL optimization – the best of YouTube series
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
On-prem? PgBouncer AWS RDS Postgres or AWS RDS Aurora/PostgreSQL? AWS RDS Proxy