waltton

-- Update goes into a CTE, the CTE will become the source of data to our insert into product_log
WITH logs AS (
    UPDATE product
    SET price = price * 1.1
      , date_updated = NOW()
    RETURNING product.id, product.price
)
INSERT INTO product_log (product_id, price)
SELECT id, price
FROM logs;

waltton

BEGIN;

UPDATE product
SET price = price * 1.1
  , date_updated = NOW();

INSERT INTO product_log (product_id, price)
SELECT id, price
FROM product;

waltton

-- Let's create a table, product_log, to capture the price changes over time
CREATE TABLE product_log (
    product_id    UUID,
    price         NUMERIC,
    date_updated  TIMESTAMP(0) WITH TIME ZONE DEFAULT NOW()
);

waltton

-- A bit later...
SELECT pg_sleep(1);

-- I probably can get away with 10%, they pay anything
UPDATE product
SET price = price * 1.1
  , date_updated = NOW();

SELECT substring(id::text, 1, 5) || '...' AS id, name, to_char(price, '999D99') AS price, '... ' || substring(date_added::text, 12, 8) AS date_added, '... ' || substring(date_updated::text, 12, 8) AS date_updated FROM product;
--     id    |         name         |  price  |  date_added  | date_updated

waltton

-- after a while
SELECT pg_sleep(1);

-- I started to think about it again, cat owners will pay anything to make their cats happy
-- So let's make this at least 10% more expensive
UPDATE product
SET price = price * 1.1
  , date_updated = NOW();

SELECT substring(id::text, 1, 5) || '...' AS id, name, to_char(price, '999D99') AS price, '... ' || substring(date_added::text, 12, 8) AS date_added, '... ' || substring(date_updated::text, 12, 8) AS date_updated FROM product;

waltton

-- Create our product table, very simple, there's not much to it
CREATE TABLE product (
    id           UUID   DEFAULT gen_random_uuid() PRIMARY KEY,
    name         TEXT,
    price        NUMERIC,
    date_added   TIMESTAMP(0) WITH TIME ZONE DEFAULT NOW(),
    date_updated TIMESTAMP(0) WITH TIME ZONE DEFAULT NOW()
);

-- And we can fill it up with some very important items, cat toys

waltton

\set current_offset '0'
\set previous_week_offset '7'

-- EXPLAIN

WITH base AS (
    SELECT COUNT(*) FILTER (WHERE date_trunc('week', timestamp) = date_trunc('week', now())  - (:'current_offset' || ' days')::interval) as count_current_week
        , COUNT(*) FILTER (WHERE date_trunc('week', timestamp) = date_trunc('week', now()) - (:'previous_week_offset' || ' days')::interval) as count_last_week
        , CASE
            WHEN user_agent ~ '^(?!.*Edge).*Chrome' THEN 'Chrome'

waltton

\set current_offset '0'
\set previous_week_offset '7'

-- EXPLAIN

SELECT json_agg(
        json_build_object(
            'user_agent_group', user_agent_group,
            'count', count_current_week,
            'count_delta', COALESCE(count_current_week, 0) - COALESCE(count_last_week, 0),

waltton

watch -n 5 'psql -U postgres -h HOST DATABASE -c "select pg_class.relname, count(*), SUM(now() - pg_stat_activity.query_start) from pg_catalog.pg_locks  JOIN pg_catalog.pg_stat_activity ON pg_stat_activity.pid = pg_locks.pid JOIN pg_class ON pg_class.oid = pg_locks.relation GROUP BY pg_class.relname ORDER BY SUM(now() - pg_stat_activity.query_start) DESC;"'

waltton

/*PUC-Minas
 Programação de Computadores II - teoria
 Tipo abstrato de dados - pilha
 Objetivo: Escrever um programa C, estruturado com funções que apresente o menu abaixo e opere
com o TAD lista encadeada com ponteiros para processar os dados de produto (código –
inteiro, descrição - 40 caracteres). Cada opção do menu pode ser exercitada diversas vezes
até que ocorra a opção sair. Os itens do menu são:
1. Inserir produto na lista por ordem de chegada não permitindo códigos repetidos
2. Mostrar os produtos presentes na lista
3. Excluir um produto da lista com a filosofia PEPS (primeiro que entra primeiro que sai).