Month: August 2022

Simple SQL windowing functions

Here is an example function showing the maximum difference between a timestamp field in row for a given partition (the SQL dialect is old Oracle 11gR2): cat fgreat.sql set line 232 SELECT process_running, run_number, exec_seq, exec_timestamp as station_time, lead(exec_timestamp) OVER (partition by run_number order by exec_timestamp) – exec_timestamp as time_to_next_row FROM benchmark where run_number=&1 order …

Simple SQL windowing functions Read More »

linux determine your logical and physical volume partitioning

https://www.thegeekdiary.com/centos-rhel-how-to-find-logical-volumes-lvs-that-are-part-of-a-physical-volume-pv-in-lvm/#:~:text=Using%20lvdisplay%20command,along%20with%20their%20physical%20volumes. Then creating a boot partition on RHEL 6 or centos 6 https://docs.hytrust.com/DataControl/Admin_Guide-4.0/Content/Books/Admin-Guide/Linux-Root-Swap-Drive-Encryption/Creating-Boot-Partition-RHEL-CentOS-6.htm Then if you have to fix a broken /boot/grub.conf

Bulk loading data with AWS RDS PostgreSQL

Note that optimizing bulk loads with AWS RDS postgresql is somewhat different than using on-prem or self-hosted postgresql as you cannot directly login to the RDS host and using the local postgresql copy command. Here are some very good references on the subject. https://aws.amazon.com/blogs/database/optimized-bulk-loading-in-amazon-rds-for-postgresql/ Prerequisite setup: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.IAMPolicy.html

Postgresql – How to change the default schema used by modifying search_path

What is the built-in postgresql variable “search_path”? The PostgreSQL search_path variable allows you to control what order schemas are searched and which schemas do not require schema qualification to use tables/views/functions in the schema. For example, if I simply type: create table junk as select current_user as curusr; What database and schema will this table …

Postgresql – How to change the default schema used by modifying search_path Read More »

postgresql roles what they are and what you can do with them

The following link provides access to an excellent blog on the subject. https://www.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1 see pg_has_role select pg_has_role(<role-name>); — returns true of false Assuming a Role Gaining access to a given role may only be gain by “using” that role directly, which can be accomplished one of three ways: 1.) SET ROLE to switch that role 2.) SET …

postgresql roles what they are and what you can do with them Read More »

Don’t use these ( inexact ) postgresql data types if you need exact calculations

Here’s the definitive postgresql docs. on the all simple (not compound) numeric datatypes subject (V 14) https://www.postgresql.org/docs/current/datatype-numeric.html So why do “real” and “double precision” datatypes exist if they are not accurate. Sometimes close is good enough 😉 Name Storage Size Description Range smallint 2 bytes small-range integer -32768 to +32767 integer 4 bytes typical choice …

Don’t use these ( inexact ) postgresql data types if you need exact calculations Read More »

Using RDS postgresql and terminating other processes

Sometimes it may be necessary to terminate a postgres users process, for example if it were holding a lock an excessive amount of time that is blocking an important process from running. select pg_terminate_backend(<pid>); — or select pg_cancel_backend(<pid>); Something else you can do to find blocking locks, that processes/PID are holding where you may want …

Using RDS postgresql and terminating other processes Read More »