Skip to content

Instantly share code, notes, and snippets.

View fabriziomello's full-sized avatar
💭
Idle in transaction

Fabrízio de Royes Mello fabriziomello

💭
Idle in transaction
View GitHub Profile
@fabriziomello
fabriziomello / custom_create_chunk.sql
Created July 6, 2023 13:20
Function to create calendar-based chunks
CREATE OR REPLACE PROCEDURE custom_create_chunk(hypertable REGCLASS, trunc_field TEXT DEFAULT 'month') AS
$$
DECLARE
r RECORD;
range_start BIGINT;
range_end BIGINT;
slices JSONB;
BEGIN
SELECT
-- git@github.com:pramsey/pgsql-http.git
CREATE EXTENSION http;
WITH dados AS (
SELECT
content::jsonb AS obj, 1::integer AS turno
FROM
http_get('https://resultados.tse.jus.br/oficial/ele2022/544/dados-simplificados/br/br-c0001-e000544-r.json')
UNION
SELECT
content::jsonb AS obj, 2::integer AS turno
@fabriziomello
fabriziomello / envoy-postgres.yaml
Created February 23, 2021 13:53
Envoy config using original source listener
static_resources:
listeners:
- name: postgres_listener
address:
socket_address:
address: 0.0.0.0
port_value: 5432
listener_filters:
- name: envoy.filters.listener.original_src
typed_config:
@fabriziomello
fabriziomello / search_in_tables.sql
Last active March 23, 2022 20:23
search_in_tables
/*
* search_in_tables
*
* Usage example:
* SELECT * FROM search_in_tables('public', 'foo', '%pattern%');
*
*/
CREATE OR REPLACE FUNCTION search_in_tables(
IN ptable_schema TEXT,
IN ptable_name TEXT,
@fabriziomello
fabriziomello / desafio-hb.sql
Created December 7, 2019 14:15
Desafio proposto por Henrique Bastos no Instagram
/*
*
* Desafio do Henrique Bastos ()
*
* Dado uma lista de números e um número k, retorne se quaisquer dois números da lista
* somados resultam no número k.
*
* Por exemplo, dado [10, 15, 3, 7] e k = 17, retorne verdadeiro, já que 10 + 7 = 17.
*
* Passe pelos números da lista uma única vez.
CREATE OR REPLACE FUNCTION bytea_import(filename text) RETURNS bytea AS
$$
import os
with open(filename, 'r') as f:
s = f.read()
return s
$$
LANGUAGE 'plpythonu' VOLATILE;
@fabriziomello
fabriziomello / get_memory_info.sql
Last active January 25, 2022 21:35
Get memory info from a certain process
-- https://psutil.readthedocs.io/en/latest/#psutil.Process.memory_full_info
-- Linux: 0=rss, 1=vms, 2=shared, 3=text, 4=lib, 5=data, 6=dirty, 7=uss, 8=pss, 9=swap
CREATE OR REPLACE FUNCTION get_memory_info(pid INTEGER DEFAULT NULL, memtype INTEGER DEFAULT 0)
RETURNS BIGINT
AS
$$
import psutil
p = psutil.Process(pid)
return p.memory_full_info()[memtype]
$$
@fabriziomello
fabriziomello / pgbouncer-show-dblink-fdw.sql
Last active October 5, 2022 12:40
pgbouncer-show-dblink-fdw
@fabriziomello
fabriziomello / gapless_sequence.sql
Last active July 22, 2024 07:01
Gapless sequence example in PostgreSQL
CREATE TABLE public.gapless_sequence (
sequence_schema TEXT,
sequence_name TEXT,
last_value BIGINT DEFAULT 0,
step INTEGER DEFAULT 1
);
ALTER TABLE public.gapless_sequence
ADD CONSTRAINT gapless_sequence_pk PRIMARY KEY (sequence_schema, sequence_name);
ssl = off
maintenance_work_mem = 2GB
work_mem = 2GB
fsync = off
synchronous_commit = off
wal_level = minimal
max_wal_senders = 0
archive_mode = off
wal_buffers = -1
wal_writer_delay = 20ms