grantpullen

PostgreSQL related queries

-- Upsert issue with unique index where we want a returning...
   The ON CONFLICT needs to include the "constraint" columns and condition in the index (thus the 'where deleted = false' in the below example)
   
   
CREATE UNIQUE INDEX idx_manufacturer_active
  ON public.manufacturer
  USING btree
  (manufacturer COLLATE pg_catalog."default", organisation_id)

grantpullen

################################
Troubleshooting
################################
# get current public ip
http://whatismyip.akamai.com/

# MTR
https://www.digitalocean.com/community/tutorials/how-to-use-traceroute-and-mtr-to-diagnose-network-issues
http://winmtr.net/

grantpullen

Increases indentation on the file tree and adds some lines to each directory/file.

Works 15 levels deep, but you can expand it by just adding more of each line thats repeating, i.e.:

• add another box shadow
  • (n*-20px) 0 0 0 rgba(255, 255, 255, 0.4)
• add another padding-left
  • .monaco-tree-row[aria-level="n"] { padding-left: ((n-1)*20)px; }
• add another :before & :after with left positioning
  • .monaco-tree-row[aria-level="n"]:before { left: (((n-1)*20)-9)px; }
• .monaco-tree-row[aria-level="n"]:after { left: (((n-1)*20)-9)px; }

grantpullen

tcpdump port 4433 -w tls.pcap

grantpullen

# get default zone
firewall-cmd --get-default-zone

# get current rules
firewall-cmd --list-all

# open up port (4433) on zone public
firewall-cmd --zone=public --add-port=4433/tcp --permanent

# apply changes to firewalld by doing a reload

grantpullen

If you are like me you find yourself cloning a repo, making some proposed changes and then deciding to later contributing back using the GitHub Flow convention. Below is a set of instructions I've developed for myself on how to deal with this scenario and an explanation of why it matters based on jagregory's gist.

To follow GitHub flow you should really have created a fork initially as a public representation of the forked repository and the clone that instead. My understanding is that the typical setup would have your local repository pointing to your fork as origin and the original forked repository as upstream so that you can use these keywords in other git commands.

1. Clone some repo (you've probably already done this step).

   git clone git@github...some-repo.git

grantpullen

// number of times that pattern matches in file with VIM
:%s/pattern//gn

// number of LINES that pattern matches in file with VIM
:%s/pattern//n

grantpullen

-- numbackends
SELECT  datname, numbackends FROM pg_stat_database where numbackends > 0;
SELECT * FROM pg_stat_activity WHERE backend_type = 'client backend';

grantpullen

-- send notification to channel 'planet' with data 'bob'
select pg_notify('planet', 'bob')


-- notify statement (faster than per row)

CREATE OR REPLACE FUNCTION public.notify_statement_trigger()
  RETURNS trigger AS
$BODY$
begin

grantpullen

-- Show current settings...
show config_file
show max_connections
show log_min_duration_statement
show log_directory 
show data_directory

-- more detailed info on current settings...
select * from pg_settings where name='max_connections';