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 :)

PostgreSQL: Find a table columns type

From http://dba.stackexchange.com/questions/75015/query-to-return-output-column-names-and-data-types-of-a-query-table-or-view:

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM   pg_attribute
WHERE  attrelid = 'foo'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

OSX: Installing Sierra over El Capitan

Information found here:

• Direct Update to macOS Sierra using Clover
• ​NVIDIA GeForce GTX 260 + Sierra 
• Sierra Desktop/Realtek AppleHDA Audio 

Install Sierra:

• Update Clover from Clover Configurator, then reboot

• Mount EFI partition like in OSX: use Clover Configurator 

• Copy kext to inject from 10.11

cp -R /Volumes/EFI/EFI/CLOVER/kexts/10.11/* /Volumes/EFI/EFI/CLOVER/kexts/Other

• Copy NVDAStartup.kext from El Capitan, to avoid a kernel panic on Sierra install

cp -R /Volumes/osx/System/Library/Extensions/NVDAStartup.kext /Volumes/EFI/EFI/CLOVER/kexts/Other

• Download Sierra from Mac App Store 

• Reboot and choose Boot macOS Install option. Before running the install, press spacebar and tick Without cache + Inject kexts. This will install and start Sierra. 

• When Sierra up, re-mount EFI partition 
• Remove Sierra NVDAStartup.kext

rm -R /Volumes/osx/System/Library/Extensions/NVDAStartup.kext

• Copy NVDAStartup.kext from EFI partition to Desktop 

• Install NVDAStartup.kext with KextBeast 

• Reboot

Then install audio drivers:

• Re-mount EFI partition
• Run audio_cloverALC command to install Toleda AppleHDA 
• Reboot, et voila

Emacs: disable auto-indent of new lines

This is a new feature from Emacs 24.4.
To revert to the old behavior, you need to disable electric-indent-mode.

(when (fboundp 'electric-indent-mode) (electric-indent-mode -1))

See http://emacs.stackexchange.com/questions/5939/how-to-disable-auto-indentation-of-new-lines

CentOS 6 / wkhtmltopdf: extra spaces added in strings when generating PDF from HTML

The tip on this page solved it for me:

sudo wget http://pastebin.com/raw.php?i=AmfYN3er -O /etc/fonts/conf.d/10-wkhtmltopdf.conf

grep extract

Suppose you want 150 lines after the line containing "2016-05-31 11:06:39.265" from file postgresql-31.csv:

grep -A150 '2016-05-31 11:06:39.265' postgresql-31.csv > /tmp/postgresql-31.csv.extract

PostgreSQL: pgq

Ticker uses following rules by default:

1. Batch is made when there are more than 500 new events (ticker_max_count)
2. Batch is made when there are any number of new events and 3 seconds have passed since last batch (ticker_max_lag)
3. Batch is made if there are no new events, but 1 minute has passed since last batch (ticker_idle_period)

https://kaiv.wordpress.com/2007/10/19/skytools-database-scripting-framework-pgq/
Show pgq status

crmmbqt=# select * from pgq.get_consumer_info() where queue_name = 'londiste3_queue';
┌─────────────────┬───────────────────┬─────────────────┬─────────────────┬───────────┬───────────────┬───────────┬────────────────┐
│   queue_name    │   consumer_name   │       lag       │    last_seen    │ last_tick │ current_batch │ next_tick │ pending_events │
├─────────────────┼───────────────────┼─────────────────┼─────────────────┼───────────┼───────────────┼───────────┼────────────────┤
│ londiste3_queue │ .global_watermark │ 02:12:20.5365   │ 00:01:16.560372 │     14376 │          NULL │      NULL │          13333 │
│ londiste3_queue │ londiste3_slave   │ 00:04:48.404077 │ 00:00:00.014105 │     14630 │       5070555 │     14631 │            252 │
│ londiste3_queue │ .slave.watermark  │ 02:09:20.494378 │ 00:00:07.537384 │     14381 │          NULL │      NULL │          10910 │
└─────────────────┴───────────────────┴─────────────────┴─────────────────┴───────────┴───────────────┴───────────┴────────────────┘
(3 rows)

PostgreSQL: deleting duplicates example

delete from resource_db.ht_sim_party where sim_party_id in (
 select sim_party_id from (
  select sim_party_id, sim_id, party_id, rnum from (
   select distinct sim_party_id, sim_id, party_id, row_number() over (partition by party_id) as rnum from resource_db.ht_sim_party 
   where ht_sim_party.from_date <= localtimestamp(0) and localtimestamp(0) < ht_sim_party.to_date
   and '2016-05-30' < ht_sim_party.from_date
  ) as foo 
  where rnum > 1
 ) as bar
)

PostgreSQL: who is locking my table?

See http://stackoverflow.com/questions/17605511/why-would-alter-table-drop-constraint-on-an-empty-table-take-a-long-time

select 
  c.relname,
  l.*,
  psa.*
from pg_locks l
inner join pg_stat_activity psa ON (psa.pid = l.pid)
left outer join pg_class c ON (l.relation = c.oid)
where l.relation = 'prov_db.ht_request_status'::regclass;

PostgreSQL exception stack trace

http://dba.stackexchange.com/questions/96743/how-to-get-exception-context-for-a-manually-raised-exception-in-pl-pgsql

PostgreSQL: xmin / xmax explanation

http://blog.lerner.co.il/in-postgresql-as-in-life-dont-wait-too-long-to-commit/

PostgreSQL: size of a table

select pg_size_pretty(pg_relation_size('foo'));

PostgreSQL: Passing an array of record to a stored procedure

create type my_type as (val1 int, val2 int);

create function my_function(arr my_type[]) returns text language plpgsql as
$$
begin
  return arr::text;
end;
$$;

select my_function(array[row(1,2)::my_type, row(3,4)::my_type]);

    my_function
-------------------
 {"(1,2)","(3,4)"}
(1 row)

PostgreSQL: join opposite

select
t1.*
from table1 t1
left join table2 t2 on t1.id=t2.id
where t2.id is null;

PostgreSQL: select ip range with max masklen

            with
            radius_cdr_pdp(sgsn_address, cdr_src_id) as (
                values ('212.183.144.0'::inet, 1), ('212.183.144.0'::inet, 2), (null, 3)
            ),
            dt_ip_range(ip_range) as (
                values ('212.183.144.0/16'::inet), ('212.183.144.0/24'::inet), ('212.182.0.0/16'::inet), (null)
            ),
            radius_cdr_pdp_max as (
                select max(masklen(dt_ip_range.ip_range)) as max_masklen, cdr_src_id
                from radius_cdr_pdp
                left join dt_ip_range on radius_cdr_pdp.sgsn_address <<= dt_ip_range.ip_range
                group by cdr_src_id
            )
            select radius_cdr_pdp.sgsn_address, dt_ip_range.ip_range, radius_cdr_pdp.cdr_src_id, radius_cdr_pdp_max.max_masklen
            from radius_cdr_pdp
            join radius_cdr_pdp_max using (cdr_src_id)
            left join dt_ip_range
                on radius_cdr_pdp.sgsn_address <<= dt_ip_range.ip_range
                and radius_cdr_pdp_max.max_masklen = masklen(dt_ip_range.ip_range);

PostgreSQL: Create temp table from values

WITH  temp (k,v) AS (VALUES (0,-9999), (1, 100)) SELECT * FROM temp;

PostgreSQL: check element contained in array

Contained:

select 1 = any (array[2, 4, 6]);

NOT contained:

select 1 != all (array[2, 4, 6]);

perl: append line on top of file

perl -pi -e 'print "use \\MBQT\\framework\\Session;\n" if $. == 1' <file>

PostgreSQL: get / set sequence value

Get latest sequence value:

select last_value from identity_id_seq;

Set sequence:

select setval('identity_id_seq', 2000041);

PostgreSQL: Convert table into array

See http://stackoverflow.com/questions/32647297/convert-table-into-array-of-custom-types
Any PostgreSQL table has a virtual column named as table of record type with fields related to table's columns.
E.g.
_param_array := array(select foo from prov_db.get_rz_addon_param_list(i_co_id) as foo);

Linux: Display the top ten running processes sorted by memory usage

ps auxw --sort rss | head

PostgreSQL: execute plpgsql block

do
$$
begin
raise notice 'merde';
end
$$;

PostgreSQL: any (values) syntax

select ht_sim_profile_mno.*
from resource_db.ht_sim_profile_mno join resource_db.dt_host_mno using (host_mno_id)
where host_mno_name = any (values ('MBQT'), ('ORANGE'));

PostgreSQL: setup pgbouncer on CentOS

https://opensourcedbms.com/dbms/setup-pgbouncer-connection-pooling-for-postgresql-on-centosredhatfedora/

PostgreSQL: Materialized View Strategies

https://hashrocket.com/blog/posts/materialized-view-strategies-using-postgresql

PostgreSQL: Pattern matching with LIKE, SIMILAR TO or regular expressions

http://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql

Remove  from the beginning of a file

This is the BOM.

vi my_file
set nobomb
wq

Oracle: how to post to https address

The normal way to achieve that is to push the certificates into the Oracle wallet via orapki.
However, if using 11.2.0.1 or XE, this is not feasible because SHA-2 certificates are not supported, and you will get

ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1722
ORA-28857: Unknown SSL error
ORA-06512: at line 1

A workaround is to use Apache Reverse Proxy:
Make sure that you have mod_ssl installed, and add into httpd.conf

SSLProxyEngine on
ProxyPass /oneagain https://oneagain.net
ProxyPassReverse /oneagain https://oneagain.net

Then replace:

utl_http.begin_request(‘https://oneagain.net’,’POST’);

with:

utl_http.begin_request(‘http://apache-host/oneagain/’,’POST’);

And forget about the wallet.

Git: see only one user's commits

git lol --author='John Smith' --name-only

Git: cancel a git stash apply

If you haven't pushed:

git reset --hard [last_good_commit]

or if you have pushed:

git revert [last_good_commit]

OSX: no more sleep after 10.11 to 10.11.2 upgrade

Symptoms: Apple menu | Sleep causes the monitor to sleep but not the UC anymore
Diagnosis:

centurion:~ jerome$ pmset -g assertions
2016-01-07 08:07:40 +0100 
Assertion status system-wide:
   BackgroundTask                 0
   ApplePushServiceTask           0
   UserIsActive                   0
   PreventUserIdleDisplaySleep    0
   PreventSystemSleep             0
   ExternalMedia                  0
   PreventUserIdleSystemSleep     0
   NetworkClientActive            1
Listed by owning process:
   pid 245(cupsd): [0x0000048000110227] 00:01:13 NetworkClientActive named: "org.cups.cupsd" 
Kernel Assertions: 0x104=USB,MAGICWAKE
   id=500  level=255 0x4=USB mod=01/01/70 01:00 description=com.apple.usb.externaldevice.1d110000 owner=IOUSBHostDevice
   id=501  level=255 0x4=USB mod=01/01/70 01:00 description=com.apple.usb.externaldevice.1d113000 owner=Dell USB Keyboard Hub
   id=502  level=255 0x4=USB mod=01/01/70 01:00 description=com.apple.usb.externaldevice.1d114000 owner=Optical USB Mouse
   id=503  level=255 0x100=MAGICWAKE mod=01/01/70 01:00 description=en0 owner=en0
Idle sleep preventers: IODisplayWrangler

We can see that the culprit is cupsd, which is related to the printer driver.
Solution:
Uninstall the printer, then re-install it again and the problem is gone.