Refreshing all materialized views in the correct order with one command

Tagged dynamic, materialized, postgres, sql, view  Languages sql

The following code is from https://wiki.postgresql.org/wiki/Refresh_All_Materialized_Views:

CREATE OR REPLACE VIEW mat_view_dependencies AS
WITH RECURSIVE s(start_schemaname,start_relname,start_relkind,
         schemaname,relname,relkind,reloid,owneroid,ownername,depth)
         AS (
-- List of tables and views that mat views depend on
SELECT n.nspname AS start_schemaname, c.relname AS start_relname,
c.relkind AS start_relkind,
n2.nspname AS schemaname, c2.relname, c2.relkind,
c2.oid AS reloid,
au.oid AS owneroid,
au.rolname AS ownername,
0 AS depth
FROM pg_class c JOIN pg_namespace n
     ON c.relnamespace=n.oid AND c.relkind IN ('r','m','v','t','f')
JOIN pg_depend d ON c.oid=d.refobjid
JOIN pg_rewrite r ON d.objid=r.oid
JOIN pg_class c2 ON r.ev_class=c2.oid -- AND c2.relkind='m'
JOIN pg_namespace n2 ON n2.oid=c2.relnamespace
JOIN pg_authid au ON au.oid=c2.relowner

UNION

-- Recursively find all mat views depending on previous level
SELECT s.start_schemaname, s.start_relname, s.start_relkind,
n.nspname AS schemaname, c2.relname,
c2.relkind, c2.oid,
au.oid AS owneroid, au.rolname AS ownername,
s.depth+1 AS depth
FROM s
JOIN pg_depend d ON s.reloid=d.refobjid
JOIN pg_rewrite r ON d.objid=r.oid
JOIN pg_class c2 ON r.ev_class=c2.oid AND (c2.relkind IN ('m','v'))
JOIN pg_namespace n ON n.oid=c2.relnamespace
JOIN pg_authid au ON au.oid=c2.relowner

WHERE s.reloid <> c2.oid -- exclude the current MV which always depends on itself
)
SELECT * FROM s;

--------------------------------------------------
--- A view that returns the list of mat views in the
--- order they should be refreshed.
--------------------------------------------------
CREATE OR REPLACE VIEW mat_view_refresh_order AS
WITH b AS (
-- Select the highest depth of each mat view name
SELECT DISTINCT ON (schemaname,relname) schemaname, relname, ownername, depth
FROM mat_view_dependencies
WHERE relkind='m'
ORDER BY schemaname, relname, depth DESC
)
-- Reorder appropriately
SELECT schemaname, relname, ownername, depth AS refresh_order
FROM b
ORDER BY depth, schemaname, relname
;

We can now write a custom function to execute the refresh of all materialized views in the correct order:

CREATE OR REPLACE FUNCTION refresh_materialized_views () RETURNS VOID AS
$$
DECLARE
BEGIN
  EXECUTE (SELECT string_agg('REFRESH MATERIALIZED VIEW "' || schemaname || '"."' || relname || '";', '' ORDER BY refresh_order) AS script FROM mat_view_refresh_order);
END;
$$ LANGUAGE plpgsql;

We use a simple select to execute the dynamically generated SQL

SELECT refresh_materialized_views();

Fixing "go get" and "terminal prompts disabled" when pulling private and public repos from gitlab

Tagged github, gitlab, golang  Languages bash

This frustrating error when running go get can be fixed by following these instructions:

go: downloading gitlab.com/christianhellsten/go-xxxx v0.0.0-20200816045911-18af1d03e51a
cmd/go-xxx/main.go:4:2: gitlab.com/christianhellsten/[email protected]: verifying module: gitlab.com/christianhellsten/[email protected]: reading https://sum.golang.org/lookup/gitlab.com/christianhellsten/[email protected]: 410 Gone
    server response:
    not found: gitlab.com/christianhellsten/[email protected]: invalid version: git fetch -f origin refs/heads/*:refs/heads/* refs/tags/*:refs/tags/* in /tmp/gopath/pkg/mod/cache/vcs/1dd4008ad7804a122a243831f561ea1000480bb532c0388829d0f37bc7349fef: exit status 128:
        fatal: could not read Username for 'https://gitlab.com': terminal prompts disabled

Make sure you have the latest go and git versions.

Then run the following commands:

git config --global url.ssh://[email protected]/.insteadOf https://github.com/
git config --global url.ssh://[email protected]/.insteadOf https://gitlab.com/
go env -w GOPRIVATE="gitlab.com/christianhellsten,bitbucket.org/christianhellsten,github.com/christianhellsten"

Specify the Gitlab token in ~/.git-credentials or ~/.netrc.

Now you can enjoy simple things such as pulling your repos from the internet, but you need to remember to set GIT_TERMINAL_PROMPT too:

GIT_TERMINAL_PROMPT=1 go get

References:

Go modules

Tagged go, modules  Languages 

Go modules:

  • go mod init creates a new module, initializing the go.mod file that describes it.
  • go build, go test, and other package-building commands add new dependencies to go.mod as needed.
  • go list -m all prints the current module’s dependencies.
  • go get changes the required version of a dependency (or adds a new dependency).
  • go mod tidy removes unused dependencies.

Reference: https://blog.golang.org/using-go-modules

Simple Dockerfile for Ruby on Rails applications

Tagged docker, dockerfile, rails, ruby  Languages bash
#
# Ruby on Rails Dockerfile
#
# ## Features:
#
# - Ruby
# - Puma
# - Yarn
# - Node
# - PostgreSQL
#
# ## Configuration
#
# ```bash
# mkdir .docker
# cat << EOF > .docker/envrc.dev
# RAILS_ENV=development
# DB_HOST=localhost
# DB_NAME=app_development
# DB_USER=app
# DB_PASS=password
# EOF
# cat << EOF > .docker/envrc.prod
# RAILS_ENV=production
# DB_HOST=postgres
# DB_NAME=app_production
# DB_USER=app
# DB_PASS=password
# EOF
# ```
#
# ## Usage
#
# ```bash
# $ docker build . -t <image name>
#
# # Development
# $ docker run --network=host --env-file=.docker/envrc.dev <image name>
#
# # Production
# $ docker run -p 3000:3000 --env-file=.docker/envrc.prod <image name>
# ```
#
FROM ruby:2.6.3

# Add Yarn to apt
# RUN curl -o- -L https://yarnpkg.com/install.sh | bash
RUN curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add -
RUN echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list

# Install dependencies
RUN apt-get update -qq && apt-get install -y build-essential libpq-dev nodejs yarn

# Don't run as root
RUN useradd --user-group -m --home /app --shell /bin/false app
RUN chown app:app /app
RUN chmod -R 740 /app
USER app

# Set working dir
ENV RAILS_ROOT /app
WORKDIR $RAILS_ROOT

ARG RAILS_ENV
ARG DB_HOST
ARG DB_NAME
ARG DB_USER
ARG DB_PASS

# Set ENV variables
ENV RAILS_ENV ${RAILS_env}
ENV RACK_ENV ${RAILS_ENV}
ENV DB_HOST ${DB_HOST}
ENV DB_NAME ${DB_NAME}
ENV DB_USER ${DB_USER}
ENV DB_PASS ${DB_PASS}

# Add and cache Ruby & Javascript dependencies
COPY --chown=app Gemfile Gemfile
COPY --chown=app Gemfile.lock Gemfile.lock
ADD package.json yarn.lock /tmp/
# Install Ruby and Javascript dependencies
RUN gem install bundler:2.0.2
RUN bundle install --jobs 20 --retry 5 --with=$RAILS_ENV --deployment
RUN yarn install

# Add project files
COPY --chown=app . .
# Precompile assets
RUN bundle exec rake assets:precompile

RUN mkdir -p log tmp/pids

EXPOSE 3000
CMD ["bundle", "exec", "puma", "-C", "config/puma.rb"]

"ipaddr show" for MacOS

Tagged address, ip, ipaddr  Languages bash
alias wanip='dig +short myip.opendns.com @resolver1.opendns.com'
alias ipaddr='wanip && ipconfig getifaddr en1'

"netstat | grep LISTEN" for MacOS

Tagged macos, listen, netstat, osx  Languages bash

Add this alias to your shell to get the equivalent output of running “netstat | grep LISTEN” on Linux:

alias netstat-listen='lsof -iTCP -sTCP:LISTEN'

Avoiding "division by zero" in SQL queries with NULLIF

Tagged postgres, division, nullif, sql, zero  Languages sql

Use NULLIF to avoid “division by zero” in SQL queries when the divisor can have the value zero.

Example:

SELECT
  col_x / NULLIF(col_y, 0) as change,
...

The result will be NULL when col_y contains the value zero.

Total hours between start and end time - Google Spreadsheet

Tagged format, time report, google, spreadsheet, time  Languages 

I want to extract the total hours worked from a Google spreadsheet.

Given the following data in a Google spreadsheet:

  • End time in column D using time format
  • Start time in column C using time format

For example:

start,end,hours
10:00,12:00,2
9:00,12:00,3

To achieve this in a Google spreadsheet we can use the following formula:

=ARRAYFORMULA((HOUR(D496-C496))+(MINUTE((D496-C496)))/60)

Reference: https://infoinspired.com/google-docs/spreadsheet/payroll-hours-time-calculation-in-google-sheets/