Skip to content

Instantly share code, notes, and snippets.

Michel Luiz Milezzi michelmilezzi

Block or report user

Report or block michelmilezzi

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@michelmilezzi
michelmilezzi / parameter_bind.rb
Last active Sep 18, 2019
PostgreSQL bind parameters
View parameter_bind.rb
# Usage: ruby parameter_bind.rb mylogfile.log
# Output: every sql command parsed with its parameters
sourceFileName = ARGV[0]
targetFile = File.open("#{sourceFileName}.sql", 'w')
lines = File.read(sourceFileName, encoding: 'utf-8').scan(/(duration:.*)\sexecute.+:\s(.+)(\n.+parameters:(.+))?/)
lines.each do |line|
if line[2] != nil then
@michelmilezzi
michelmilezzi / aws_glue_avoiding_duplicates.py
Last active Mar 5, 2019
AWS Glue script showing how to avoid duplicates during a job execution.
View aws_glue_avoiding_duplicates.py
import sys
import pydevd
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.dynamicframe import DynamicFrame
from pyspark.sql.functions import col
from awsglue.job import Job
@michelmilezzi
michelmilezzi / DataCopyManager.java
Created Nov 28, 2017
A simple java class to get PostgreSQL COPY content from STDOUT to compressed file
View DataCopyManager.java
package br.com.michelmilezzi.database.data;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
@michelmilezzi
michelmilezzi / idempotent_script.sql
Last active Jun 26, 2017
A simple script demonstrating PostgreSQL idempotent capabilities. You can run this script as many times as you wish (it will not give duplicate object error or similar).
View idempotent_script.sql
--Table
CREATE TABLE IF NOT EXISTS person (
id integer NOT NULL,
person_name character varying(40) NOT NULL,
updated_date date,
CONSTRAINT person_pkey PRIMARY KEY (id)
);
--Index
CREATE INDEX IF NOT EXISTS idx_person_name ON person (person_name);
@michelmilezzi
michelmilezzi / find_contraint_ref_fields.sql
Last active Jun 26, 2017
PostgreSQL query that finds columns and referenced columns from a constraint
View find_contraint_ref_fields.sql
WITH constraint_ref_fields AS (
SELECT
unnest(con1.conkey) AS local_key,
unnest(con1.confkey) AS ref_key,
con1.conrelid,
con1.confrelid
FROM
pg_class cl
JOIN pg_namespace ns ON cl.relnamespace = ns.oid
JOIN pg_constraint con1 ON con1.conrelid = cl.oid
@michelmilezzi
michelmilezzi / index_without_hit.sql
Last active Jun 26, 2017
PostgreSQL query that finds indexes without hits
View index_without_hit.sql
SELECT
relid::regclass AS table,
indexrelid::regclass AS index,
pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
idx_tup_read,
idx_tup_fetch,
idx_scan
FROM
pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
@michelmilezzi
michelmilezzi / overall_status_of_indexes.sql
Last active Jun 26, 2017
PostgreSQL query that calculates overall status of indexes
View overall_status_of_indexes.sql
SELECT
COUNT(*) FILTER (WHERE idx_scan = 0) AS no_hit,
COUNT(*) FILTER (WHERE idx_scan > 0) AS used,
COUNT(*) AS total
FROM
pg_stat_user_indexes s
JOIN pg_index i ON (i.indexrelid = s.indexrelid)
WHERE
NOT i.indisprimary;
You can’t perform that action at this time.