Skip to content

Instantly share code, notes, and snippets.

View lesovsky's full-sized avatar
😉
Don't stop

Lesovsky Alexey lesovsky

😉
Don't stop
234 followers · 0 following
  • Postgres Pro
  • Yekaterinburg, Russian Federation
View GitHub Profile
@lesovsky
lesovsky / 3d-array-sorting-with-qsort.c
Created August 14, 2016 11:05
Selective sorting of 3d string array with qsort.
/*
* Special thanks to chqrlie for
* http://stackoverflow.com/questions/38940390/c-sorting-3d-string-array-by-specific-column
*/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
@lesovsky
lesovsky / pgss.cron
Created July 19, 2016 05:06
pg_stat_statements reports cronjobs
MAILTO=support-cron+leadermt@postgresql-consulting.com
# pg_stat_statements report
58 23 * * * /usr/bin/psql -XAt -U postgres -f ~/stuff/sql/global_reports/query_stat_total.sql | /usr/bin/mail -e -s "Daily report of pg_stat_statements for `/bin/date "+\%Y-\%m-\%d"` from `hostname` database at LeaderMT" support-cron+leadermt@postgresql-consulting.com
# pg stat statements reset
00 00 * * * /usr/bin/psql -t -c "select pg_stat_statements_reset(); " >/dev/null
@lesovsky
lesovsky / lesovsky_id_rsa.pub
Created July 17, 2016 17:53
Lesovsky public rsa key
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQC/4sndLEqgDqO3/JjNfr8wqT43+hanB7Ybtg7vWoBMhVkz6pz+Rrez9rq8kmSmdP/fl5ROsUHUOmXHp9hRxqB7DhQihPu8LGNTzedxM08oO/Db7HfDPBu9tRjbWNr7kv/Ak7bbq/tByK7TCvvhet8PrlNcbYEVj3H0cDMJhJQQPh/yC0BiuJgXtRo0qsdxGci4shCad0UZNMOCbOICVCMb0l8BHDmFW6bwV9xubceuautXhmIFGfbdIKzd6hwcvXGlYsaujVD9uO+Co0PORnU3aTJLYK+cSi7JOe5aYLDK/3ytFJDkiElOz/Fk87U/C2xdmjPENatII/YWj5tDL+4UWFWORtgarQbRDDAhbFEmGuOMx/J91j2Ek2NMPeUEwN/JT1ivis1NRiFF4oJSXBLMuzv/+jlxzR442IXraaRoqDIh0/n6UABBxofd1NPl2w6MbTXSUVCsyvj9y3AKqclUYy7NwEEz2M1TS0uQr74qm5KFR/+viSG4FZU9bCmeaw1YFDbDoMRquwZI4aBhdp1xqtG5sgN53OEPqC/LAsJBaDlH0+eDfx4DNjkOSGBa1h7TXV3LRAKdzTGsYNCf6enzkc5DvX6q21ix41LV/7NY+VpMJbVDhDgtij6zhVt7aepKF2zaIw3cwnIz8Vt6UpcDEHjAUPNi4p3dSJRwnljnsw== lesovsky@matanuii
@lesovsky
lesovsky / append-strings-with-snprintf.c
Created July 15, 2016 18:52
How-to append strings using snprintf (without strcat/strncat).
#include <stdio.h>
#include <string.h>
#define LOC_MAXLEN 13
int main (void)
{
char dest[LOC_MAXLEN];
snprintf(dest, LOC_MAXLEN, "%s%s", "abc", "def");
@lesovsky
lesovsky / create-trigger-function.sql
Last active June 7, 2016 16:46
Create trigger function
CREATE OR REPLACE FUNCTION test_versions_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
timeformat TEXT := '_yyyymm';
insert_sql TEXT;
BEGIN
EXECUTE 'INSERT INTO public.' || TG_TABLE_NAME || to_char(NEW.created_at,timeformat) || ' SELECT ($1).*' USING NEW;
RETURN NULL;
EXCEPTION
WHEN undefined_table THEN
@lesovsky
lesovsky / create-partition-function.sql
Created June 7, 2016 16:45
Create partition function
CREATE OR REPLACE FUNCTION create_temp_versions_partitions(part TEXT , init INT DEFAULT 0 ) RETURNS VOID AS $$
DECLARE
current_check TIMESTAMP;
next_check TIMESTAMP;
next_partition TEXT;
created_partition TEXT;
BEGIN
IF init > 0 THEN
SELECT date_trunc('month', current_timestamp) into current_check;
SELECT date_trunc('month',current_timestamp + interval '1 month') into next_check;
@lesovsky
lesovsky / simple-nicstat.c
Created December 2, 2015 18:50
Simple nicstat based on /proc/net/dev stats
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <stdbool.h>
#include <sys/socket.h>
#include <sys/ioctl.h>
#include <netinet/in.h>
#include <linux/sockios.h>
#include <net/if.h>
#include <linux/ethtool.h>
@lesovsky
lesovsky / t-shirt text
Last active December 15, 2015 07:37
postgresql t-shirt text
[postgres@pgdb-01m]$ psql
psql (9.4.5, server 9.4.5)
Type "help" for help.
postgres=# SELECT pid, client_addr, usename, datname,
date_trunc('seconds', clock_timestamp() - xact_start) AS xact_age,
query
FROM pg_stat_activity
WHERE ((clock_timestamp() - xact_start) > '00:01:00'
OR (clock_timestamp() - query_start) > '00:01:00'
@lesovsky
lesovsky / simple-iostat.c
Created November 23, 2015 09:07
Simple iostat based on /proc/diskstats
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <stdbool.h>
#include <unistd.h>
#include <errno.h>
#include <ncurse
@lesovsky
lesovsky / brokenconns.c
Last active October 29, 2015 08:27
Create broken conns to PostgreSQL.
#include <stdio.h>
#include <sys/types.h>
#include <sys/wait.h>
#include <stdlib.h>
#include <unistd.h>
#include "libpq-fe.h"
/* gcc -std=gnu99 -pedantic -I/usr/pgsql-9.4/include -L/usr/pgsql-9.4/lib -o broken-conns broken-conns.c -lpq */
#define CONNINFO "host = 127.0.0.1 port = 5433 dbname = hldemo user = postgres"