Git: branching

Git Branching - Basic Branching and Merging

PostgreSQL: size of a relation

Relation w/o inheritance:

select pg_size_pretty(pg_total_relation_size('radius_db.radacct'));

Relation w/ inheritance:

select pg_size_pretty(sum(pg_total_relation_size(inhrelid))::bigint + pg_total_relation_size('ods_db.radius_cdr_pdp')) from pg_inherits where inhparent='ods_db.radius_cdr_pdp'::regclass;

PostgreSQL: detect duplicates

Detect duplicates in at_file after a given date:

select * from monitor_db.at_file
 where exists ( 
 select 'x' 
                  from monitor_db.at_file i
                 where i.file_id = at_file.file_id
                   and i.process_id = at_file.process_id
                   and i.ctid > at_file.ctid
                   and log_date > '2017-01-31'
             )

Linux: Run script as user without login shell

Source : http://serverfault.com/questions/351046/run-script-as-user-who-has-nologin-shell

su -s /bin/bash -c '/tmp/site.pl' apache

Perl: epoch(now)

perl -MTime::Local -e 'print timegm(gmtime)'

PostgreSQL: size of a row

Source: Measure the size of a PostgreSQL table row

  SELECT l.what, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / x.ct END          AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- same as 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   radius_db.radacct t  -- provide table name *once*
   ) x
 , LATERAL (
   VALUES
      (true , 'core_relation_size'               , pg_relation_size(tbl))
    , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
    , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
    , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
    , (true , 'indexes_size'                     , pg_indexes_size(tbl))
    , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
    , (true , 'live_rows_in_text_representation' , txt_len)
    , (false, '------------------------------'   , NULL)
    , (false, 'row_count'                        , ct)
    , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
    , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, what, nr);
 

mod_perl: how to use environment variable

In httpd.conf:

PerlSetEnv PG_FLG 1

In mod_perl code:

sub is_pure_pg {
    return exists $ENV{PG_FLG};
}

PostgreSQL: rotate pgbouncer logs

Simplest method: switch to syslog

vi /etc/pgbouncer/pgbouncer.ini

; logfile = /var/log/pgbouncer.log
syslog = 1

psql -p6432 -Upostgres -d pgbouncer -c reload

PostgreSQL: split record returned by function into columns

Source: PostgreSQL function or stored procedure that outputs multiple columns?

SELECT a, (func(a)).* FROM table1;

Git: undo push

Source: How to undo (almost) anything with Git

git revert <SHA>

MacOS 10.12: auto mount network drive

Since 10.12, auto-mounting a network drive via Login Items asks to confirm the login and password each time we log in. Very unpleasant.
To get rid of that behavior:

1. Remove the network drive from the Login Items
2. Drag and drop the network drive to the dock

Then, network drive automatically mounted and no more password asked at each login!

[EDIT] The login / password dialog showing up whenever we log in has been fixed in 10.12.2 at last.

MacOS 10.12: screensaver stuck at: Loading photos

rm ~/Library/Preferences/com.apple.screensaver.plist

Git: list files changed in a branch

git checkout my_branch
git diff --name-only master...

Git: branching and merging for newbies

Git Branching - Basic Branching and Merging

Git: merge --no-ff

Source: What is the difference between `git merge` and `git merge --no-ff`?

Git: visualize

http://kentnguyen.com/development/visualized-git-practices-for-team/

PostgeSQL: pool sizing

About Pool Sizing

Git: use external diff command

git difftool --extcmd=diff sql/mbqt_prov_api.sql

PostgreSQL: remove all Bucardo triggers from a schema

do
$$
declare
    _row record;
    _query text;
begin
    for _row in SELECT tgname, tgrelid::regclass as table_name FROM pg_trigger where tgname ~ 'bucardo' and tgrelid::regclass::text ~ 'shared_db' loop
        _query := format('drop trigger %s on %s', _row.tgname, _row.table_name);
        raise notice '%', _query;
        execute _query;
    end loop;
end
$$;

Google Drive web hosting is shutting down

An alternative is to use Google Firebase.

Create new project on Firebase console :

Select Storage :

Import your files :

Edit your template in Blogger :

Copy the Firebase URL of the file you want to use in Blogger :

Replace your Google Drive URL :

Save your template and you will get an error about token :

To workaround the token issue, go to the Google URL shortener, and paste the Firebase URL :

Copy the short URL :

Replace the Firebase URL with the shortened one in Blogger :

[2021-09-05 Edit]
syntaxhighlighter has stopped working for some time now. The Web Dev Tools console in FF shows the following error:
The resource from “https://goo.gl/kQztrh” was blocked due to MIME type (“text/html”) mismatch (X-Content-Type-Options: nosniff).
So, it seems as if something changed either in the Google shortener behavior, or maybe in FF itself.
Let's try to re-use the original URLs instead of the shortened ones. As the Blogspot UI changed, you now need to go to "Themes", and click the "Customize" button.

Then select the "Edit HTML" menu item.

But using, e.g. "https://firebasestorage.googleapis.com/v0/b/syntaxhighlighter-2b128.appspot.com/o/scripts%2FshBrushPhp.js?alt=media&token=1533c104-e33b-43fa-b299-c2dd8c8f81ab" instead of "https://goo.gl/kQztrh" brings back the old error:

which is actually caused by the use of "&token=" instead of "&token=".
Once all the URLs have been substituted and the "&token=" occurences replaced, we're back in tracks :)