schwartzmx

-- check if they own any databases
select d.datname as name,
	pg_catalog.pg_get_userbyid(d.datdba) as db_owner,
	'alter database '+d.datname+' owner to <user>;' as chg_owner
from pg_catalog.pg_database d
where pg_catalog.pg_get_userbyid(d.datdba) = '<user>'
order by d.datname;

-- check if they own any schemas
select nspname, usename, 'alter schema '+nspname+' owner to <user>;' as chg_owner

schwartzmx

$code = @"
    [DllImport("user32.dll")]
    public static extern bool BlockInput(bool fBlockIt);
"@

$userInput = Add-Type -MemberDefinition $code -Name UserInput -Namespace UserInput -PassThru

Function Disable-UserInput {
    param([int]$Seconds=5)
    $userInput::BlockInput($true) | out-null

schwartzmx

$code = @"
    [DllImport("user32.dll", SetLastError=true)]
    public static extern void SwitchToThisWindow(IntPtr hWnd, bool fAltTab);
    
    [DllImport("user32.dll")]
    [return: MarshalAs(UnmanagedType.Bool)]
    public static extern bool SetForegroundWindow(IntPtr hWnd);
"@

$win = Add-Type -MemberDefinition $code -Name SwitchWindow -Namespace SwitchWindow -PassThru

schwartzmx

Function Invoke-ParamQuery {
    param(
        [String]$Query,
        $Parameters=@{},
        [Data.SqlClient.SqlConnection]$Conn,
        [int]$Timeout=3,
        [switch]$CloseConn
    )
    if ($conn.State -eq "Closed") {
        $conn.Open()

schwartzmx

select u.usename, 
    q.userid, 
    q.query, 
    q.pid, 
    q.database, 
    q.querytxt, 
    listagg(q2.text, ' ') within group (order by q2.sequence) as querytxt2, 
    q.starttime, 
    q.endtime, 
    aborted, 

schwartzmx

-- check when autovacuum will trigger based on table > db autovacuum settings
WITH rel_set AS
(
    SELECT
        oid,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)::BIGINT
        END AS rel_av_vac_threshold,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)

schwartzmx

WITH all_tables AS (
    SELECT
        *
    FROM (
        SELECT
            'all'::text AS table_name,
            sum((coalesce(heap_blks_read, 0) + coalesce(idx_blks_read, 0) + coalesce(toast_blks_read, 0) + coalesce(tidx_blks_read, 0))) AS from_disk,
            sum((coalesce(heap_blks_hit, 0) + coalesce(idx_blks_hit, 0) + coalesce(toast_blks_hit, 0) + coalesce(tidx_blks_hit, 0))) AS from_cache
        FROM
            pg_statio_all_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)

schwartzmx

SELECT
       oid::regclass::text AS table,
       age(relfrozenxid) AS xid_age, 
       mxid_age(relminmxid) AS mxid_age, 
       least( 
(SELECT setting::int
            FROM    pg_settings
            WHERE   name = 'autovacuum_freeze_max_age') - age(relfrozenxid), 
(SELECT setting::int
            FROM    pg_settings

schwartzmx

1..100 | % {
  switch ($_) {
    { $_ % 3 -eq 0 -and $_ % 5 -eq 0 } { "fizzbuzz" }
    { $_ % 3 -eq 0 } { "fizz" }
    { $_ % 5 -eq 0 } { "buzz" }
    default { $_ }
  }
}

schwartzmx

import logging
from concurrent.futures import (
    ThreadPoolExecutor,
    wait,
    CancelledError,
    TimeoutError,
    ALL_COMPLETED,
)
import os