Pos

Вступление

Если выработаете сcrucial data, торано или поздно задумаетесь отом, что неплохобы поднять кластер отказоустойчивости. Даже если основной сервер сбазой улетит вглухой нокаут, show must goon, нетакли? При этом мыподразумеваем две вещи:

• база данных совсей ееструктурой нам по-прежнему доступна;

• все данные находятся всостоянии максимально близком ктому, которое было нагероически павшем лидере.

Patroni лишь одно изрешений проблемы. Донего япопробовал несколько других сервисов инаGithub одного изних (небуду показывать курсором) досих пор висит без ответа открытый мной баг репорт, хотя прошло уже несколько месяцев. Удругого была несамая полная документация. Какие-то мне показались недостаточно надежными.

ПоPatroniже действительно много инфы, ионкачественно поддерживается разработчиками. Также ониспользует DCS для синхронизации нод, что должно предотвращать ситуации соsplit brain.

Изминусов совершенно точно нужно назватьто, что это неout ofthe box solution. Как сказано вдоке:

Patroni is a template for you to create your own customized, high-availability solution using Python...

Иключевое здесь слово template. Тоесть все придется собирать самому. Новкаком-то смысле это плюс покрайней мере, мыдетально будем знать, что именно идёт впрод.

Поспециализации, кстати, янеDevOps инженер. Когда появилась необходимость поднять отказоустойчивый кластер мне пришлось собрать все ямы истолбы какие только были наэтом пути. Надеюсь, этот туториал поможет вам достичь результата, испытав вразы меньше страданий иболи, чем довелось мне.

Если выустали уже отвступлений ирветесь вбой, смело переходите кследующей главе.

Если нет, топредлагаю под спойлером прочесть короткие заметки поконфигурации кластера, которую явыбрал для деплоя.

Docker Swarm

Для быстрого иприближенного креальности теста, нам, насамом деле, достаточно одной ноды вSwarm кластере. Поскольку мыможем очень легко поднимать иронять сервисы, запущенные вSwarmе, мысможем имитировать падение сервера снодой, имея враспоряжении только одну локальную машину.

Яисхожу изпредположения, что увас уже установлен инастроен Docker Engine. Втаком случае, нужно только выполнить следующую команду:

docker swarm init//now check your single-node clusterdocker node lsID                            HOSTNAME       STATUS       AVAILABILITY          a9ej2flnv11ka1hencoc1mer2 *   floitet        Ready          Active       

Одна изважных фич Swarmа заключается втом, что теперь мыможем использовать нетолько обычные Docker контейнеры, ноитак называемые сервисы. Сервисы это посути дела абстракция над контейнерами. Если отталкиваться отаналогии сООП, тосервис это класс, аконтейнер конкретный объект класса. Параметры иправила сервиса задаются при деплое изyml-файла.

Рекомендую запомнить hostname ноды потом мыиспользуем его для указания constraint вконфигурационном файле.

Вцелом, это все приготовления вчасти Docker Swarmа, которые нужно сделать. Здесь никаких проблем быть недолжно. Так что двинемся дальше.

Zookeeper

Прежде чем мыначнем деплой самого Patroni, нам нужно сначала развернуть кластер сDCS (внашем случае, как мыпомним, это Zookeeper). Явзял версию 3.4, иона работает вполне стабильно. Далее идет docker-compose конфиг инекоторые комментарии помоментам, которые, как мне кажется, имеет смысл отдельно упомянуть.

• docker-compose-zookeeper.yml

version: '3.7'services:  zoo1:    image: zookeeper:3.4    hostname: zoo1    ports:      - 2191:2181    networks:      - patroni    environment:      ZOO_MY_ID: 1      ZOO_SERVERS: server.1=0.0.0.0:2888:3888 server.2=zoo2:2888:3888 server.3=zoo3:2888:3888    deploy:      replicas: 1      placement:        constraints:          - node.hostname == floitet      restart_policy:        condition: any  zoo2:    image: zookeeper:3.4    hostname: zoo2    networks:      - patroni    ports:      - 2192:2181    environment:      ZOO_MY_ID: 2      ZOO_SERVERS: server.1=zoo1:2888:3888 server.2=0.0.0.0:2888:3888 server.3=zoo3:2888:3888    deploy:      replicas: 1      placement:        constraints:          - node.hostname == floitet      restart_policy:        condition: any  zoo3:    image: zookeeper:3.4    hostname: zoo3    networks:      - patroni    ports:      - 2193:2181    environment:      ZOO_MY_ID: 3      ZOO_SERVERS: server.1=zoo1:2888:3888 server.2=zoo2:2888:3888 server.3=0.0.0.0:2888:3888    deploy:      replicas: 1      placement:        constraints:          - node.hostname == floitet      restart_policy:        condition: anynetworks:  patroni:    driver: overlay    attachable: true

zoo1:    image: zookeeper:3.4    hostname: zoo1    ports:      - 2191:2181

ZOO_SERVERS: server.1=0.0.0.0:2888:3888 server.2=zoo2:2888:3888 server.3=zoo3:2888:3888

     placement:        constraints:          - node.hostname == floitet

networks:  patroni:    driver: overlay// мы должны отметить сеть как attachable  // чтобы потом можно было присоединять к ней остальные сервисы    attachable: true

Итак, можно задеплоить Zookeeper:

sudo docker stack deploy --compose-file docker-compose-zookeeper.yml patroni

Теперь нужно проверить, что все работает. Первое что можно сделать это просто посмотреть список сервисов:

sudo docker service lsgxfj9rs3po7z        patroni_zoo1        replicated          1/1                 zookeeper:3.4         *:2191->2181/tcpibp0mevmiflw        patroni_zoo2        replicated          1/1                 zookeeper:3.4         *:2192->2181/tcpsrucfm8jrt57        patroni_zoo3        replicated          1/1                 zookeeper:3.4         *:2193->2181/tcp

Иследующим шагом можно сделать пинг сервисов спомощью специальной команды mntr:

echo mntr | nc localhost 2191// with the output being smth like thiszk_version3.4.14-4c25d480e66aadd371de8bd2fd8da255ac140bcf, built on 03/06/2019 16:18 GMTzk_avg_latency6zk_max_latency205zk_min_latency0zk_packets_received1745zk_packets_sent1755zk_num_alive_connections3zk_outstanding_requests0zk_server_statefollowerzk_znode_count16zk_watch_count9zk_ephemerals_count4zk_approximate_data_size1370zk_open_file_descriptor_count34zk_max_file_descriptor_count1048576zk_fsync_threshold_exceed_count0

Также можно проверить логи сервиса, если есть желание:

docker service logs $zookeeper-service-id // service-id comes from 'docker service ls' command. // in my case it could be docker service logs gxfj9rs3po7z

Отлично, вот мыиразобрались сZookeeperом. Теперь можно переходить ксамому Patroni.

Patroni

Мынаконец добрались доосновной части туториала, где нам предстоит поднимать кластер Patroni. Первое что нужно сделать это билд кастомного имейджа Patroni, чтобы нам было что деплоить. Мывключим всборку только самые необходимые вещи, ияпостараюсь объяснить все шаги как можно более подробно, чтобы вдальнейшем вам легко было работать сэтим образом иапгрейдить его посвоему желанию.

Сначала создадим отдельную директорию patroni-test иперейдем внеё. Для того чтобы успешно сбилдить рабочий имейдж нам понадобится пара дополнительных файлов, сних иначнем.

• patroni.yml

Это основной конфигурационный файл. Одна изособенностей Patroni, что мыможем задавать параметры для кластера изразных мест иpatroni.yml одно изних. Этот файл мыбудем копировать вкастомный имейдж, так что любые изменения, внесенные внего, требуют ребилда образа.

Явитоге пришел кидее, что буду хранить здесь только тепараметры, которые наверняка почти никогда небуду трогать, поэтому складываю здесь только самые необходимые настройки. Ниже япривожу базовый конфиг. Если захочется, томожно добавить внего каких-то параметров, допустим, для движка Posgtresа (например, max_connections ит.п.). Нодля тестового стенда этого вполне достаточно.

scope: patroninamespace: /service/bootstrap:    dcs:        ttl: 30        loop_wait: 10        retry_timeout: 10        maximum_lag_on_failover: 1048576        postgresql:            use_pg_rewind: true    postgresql:      use_pg_rewind: true    initdb:    - encoding: UTF8    - data-checksums    pg_hba:    - host replication all all md5    - host all all all md5zookeeper:  hosts:       - zoo1:2181      - zoo2:2181      - zoo3:2181postgresql:    data_dir: /data/patroni    bin_dir: /usr/lib/postgresql/11/bin    pgpass: /tmp/pgpass    parameters:        unix_socket_directories: '.'tags:    nofailover: false    noloadbalance: false    clonefrom: false    nosync: false

postgresql:    data_dir: /data/patroni    bin_dir: /usr/lib/postgresql/11/bin

zookeeper:  hosts:       - zoo1:2181      - zoo2:2181      - zoo3:2181

• patroni-entrypoint.sh

Изследующего файла подтягивается большая часть настроек вмоей конфигурации. Это небольшой скрипт, который будет выполнен, когда контейнер сервиса окажется создан.

#!/bin/shreadonly CONTAINER_IP=$(hostname --ip-address)readonly CONTAINER_API_ADDR="${CONTAINER_IP}:${PATRONI_API_CONNECT_PORT}"readonly CONTAINER_POSTGRE_ADDR="${CONTAINER_IP}:5432"export PATRONI_NAME="${PATRONI_NAME:-$(hostname)}"export PATRONI_RESTAPI_CONNECT_ADDRESS="$CONTAINER_API_ADDR"export PATRONI_RESTAPI_LISTEN="$CONTAINER_API_ADDR"export PATRONI_POSTGRESQL_CONNECT_ADDRESS="$CONTAINER_POSTGRE_ADDR"export PATRONI_POSTGRESQL_LISTEN="$CONTAINER_POSTGRE_ADDR"export PATRONI_REPLICATION_USERNAME="$REPLICATION_NAME"export PATRONI_REPLICATION_PASSWORD="$REPLICATION_PASS"export PATRONI_SUPERUSER_USERNAME="$SU_NAME"export PATRONI_SUPERUSER_PASSWORD="$SU_PASS"export PATRONI_approle_PASSWORD="$POSTGRES_APP_ROLE_PASS"export PATRONI_approle_OPTIONS="${PATRONI_admin_OPTIONS:-createdb, createrole}"exec /usr/local/bin/patroni /etc/patroni.yml

readonly CONTAINER_IP=$(hostname --ip-address)readonly CONTAINER_API_ADDR="${CONTAINER_IP}:${PATRONI_API_CONNECT_PORT}"readonly CONTAINER_POSTGRE_ADDR="${CONTAINER_IP}:5432"...export PATRONI_RESTAPI_CONNECT_ADDRESS="$CONTAINER_API_ADDR"export PATRONI_RESTAPI_LISTEN="$CONTAINER_API_ADDR"export PATRONI_POSTGRESQL_CONNECT_ADDRESS="$CONTAINER_POSTGRE_ADDR"

export PATRONI_approle_PASSWORD="$POSTGRES_APP_ROLE_PASS"export PATRONI_approle_OPTIONS="${PATRONI_admin_OPTIONS:-createdb, createrole}"

exec /usr/local/bin/patroni /etc/patroni.yml

• Dockerfile

Dockerfile ярешил сделать настолько простым, насколько это только возможно. Ноэтого вполне достаточно, чтобы сделать билд рабочего Docker-образа. Давайте глянем, что внем все-таки происходит.

FROM postgres:11 RUN apt-get update -y\     && apt-get install python3 python3-pip -y\    && pip3 install --upgrade setuptools\    && pip3 install psycopg2-binary \    && pip3 install patroni[zookeeper] \    && mkdir /data/patroni -p \    && chown postgres:postgres /data/patroni \    && chmod 700 /data/patroni COPY patroni.yml /etc/patroni.ymlCOPY patroni-entrypoint.sh ./entrypoint.shUSER postgresENTRYPOINT ["bin/sh", "/entrypoint.sh"]

// владелец должен быть 'postgres', а mode 700    mkdir /data/patroni -p \    chown postgres:postgres /data/patroni \    chmod 700 /data/patroni     ...// устанавливаем в кач-ве активного юзера внутри контейнера // юзера postgres        USER postgres

COPY patroni.yml /etc/patroni.ymlCOPY patroni-entrypoint.sh ./entrypoint.sh

ENTRYPOINT ["bin/sh", "/entrypoint.sh"]

Вот, пожалуй, ився основная подготовка. Теперь мыготовы создать наш кастомный Patroni имейдж.

docker build -t patroni-test .

Самое время обсудить последний посписку, нонеповажности файл для Patroni compose yml.

• docker-compose-patroni.yml

Правильно написанный compose файл важная часть общей картины. Разберем, что нам нужно иметь ввиду, настраивая конфигурацию.

version: "3.4"networks:    patroni_patroni:         external: trueservices:    patroni1:        image: patroni-test        networks: [ patroni_patroni ]        ports:            - 5441:5432            - 8091:8091        hostname: patroni1        volumes:          - /patroni1:/data/patroni        environment:            PATRONI_API_CONNECT_PORT: 8091            REPLICATION_NAME: replicator             REPLICATION_PASS: replpass            SU_NAME: postgres            SU_PASS: supass            POSTGRES_APP_ROLE_PASS: appass        deploy:          replicas: 1          placement:            constraints: [node.hostname == floitet]    patroni2:        image: patroni-test        networks: [ patroni_patroni ]        ports:            - 5442:5432            - 8092:8091        hostname: patroni2        volumes:          - /patroni2:/data/patroni        environment:            PATRONI_API_CONNECT_PORT: 8091            REPLICATION_NAME: replicator             REPLICATION_PASS: replpass            SU_NAME: postgres            SU_PASS: supass            POSTGRES_APP_ROLE_PASS: appass        deploy:          replicas: 1          placement:            constraints: [node.hostname == floitet]    patroni3:        image: patroni-test        networks: [ patroni_patroni ]        ports:            - 5443:5432            - 8093:8091        hostname: patroni3        volumes:          - /patroni3:/data/patroni        environment:            PATRONI_API_CONNECT_PORT: 8091            REPLICATION_NAME: replicator             REPLICATION_PASS: replpass            SU_NAME: postgres            SU_PASS: supass            POSTGRES_APP_ROLE_PASS: appass        deploy:          replicas: 1          placement:            constraints: [node.hostname == floitet]

networks:    patroni_patroni:         external: true

ports:    - 5441:5432    - 8091:8091...environment:    PATRONI_API_CONNECT_PORT: 8091// также нужно убедиться, что в PATRONI_API_CONNECT_PORT мы передаем// тот же самый, который мы открываем для сервиса   

volumes:   - /patroni3:/data/patroni

sudo mkdir /patroni3sudo chown 999:999 /patroni3sudo chmod 700 /patroni3// 999 это дефолтный uid для юзера postgres  // эти шаги нужно повторить для каждой ноды Patroni

Мы наконец готовы деплоить Patroni кластер:

sudo docker stack deploy --compose-file docker-compose-patroni.yml patroni

После деплоя влогах сервиса мыдолжны увидеть что-то втаком ключе:

INFO: Lock owner: patroni3; I am patroni1INFO: does not have lockINFO: no action.  i am a secondary and i am following a leader

Былобы печально, еслибы мымогли проверить статус кластера иноды только читая логи. Так что предлагаю коснуться немного способов проверки состояния кластера иначать ссамого простого patronictl. Для этого нужно сначала получить idлюбого контейнера Patroni:

sudo docker psCONTAINER ID        IMAGE                 COMMAND                  CREATED             STATUS              PORTS                          NAMESa0090ce33a05        patroni-test:latest   "bin/sh /entrypoint."   3 hours ago         Up 3 hours          5432/tcp                       patroni_patroni1.1.tgjzpjyuip6ge8szz5lsf8kcq...

Ипотом зайти вконтейнер спомощью exec команды:

sudo docker exec -ti a0090ce33a05 /bin/bash// при вызове команды мы должны передать имя кластера// это параметр 'scope' в patroni.yml ('patroni' в нашем случае)patronictl list patroni// и тут ошибка...Error: 'Can not find suitable configuration of distributed configuration store\nAvailable implementations: exhibitor, kubernetes, zookeeper'

Команда patronictl полагается наpatroni.yml, чтобы получить информацию осерверах Zookeeperа. Оннезнает, где мыэтот файл положили. Так что явно укажем ему путь:

patronictl -c /etc/patroni.yml list patroni// and here is the nice output with the current states+ Cluster: patroni (6893104757524385823) --+----+-----------+| Member   | Host      | Role    | State   | TL | Lag in MB |+----------+-----------+---------+---------+----+-----------+| patroni1 | 10.0.1.93 | Replica | running |  8 |         0 || patroni2 | 10.0.1.91 | Replica | running |  8 |         0 || patroni3 | 10.0.1.92 | Leader  | running |  8 |           |+----------+-----------+---------+---------+----+-----------+

PostgreSQL Connection

Готово! Теперь мыможем подключаться кPostgres ичто-то туда писать. Ноделать это мысможем только изнашей сети patroni_patroni. Так что сначала создаем контейнер сподходящим образом, ипотом изнего уже выполняем команды:

docker run --rm -ti --network=patroni_patroni postgres:11 /bin/bash// доступ к конкретной нодеpsql --host patroni3 --port 5432 -U approle -d postgres// доступ к лидеру через haproxy// так что нужно указать какую-либо через флаг '-d' 

Вот мыинастроили сам кластер Patroni. Нонаш сетап былбы неполным, еслибы мынаэтом иостановились. Есть ещё несколько важных моментов, которые мыдолжны закрыть, нообэтом вовторой части.

Все config файлы для первой части можно забрать отсюда.

В эфире снова Радио SQL, здравствуйте, согалактчики!

Сегодня у нас обещанный разбор задачи на поиск последней цены. Прошёл как раз земляной месяц. У вас же 60 солов в месяце, да? Я немного путаюсь во всех этих ваших неметрических то 12-ти, то 60-тиричных системах времени. Впрочем, перейдём к делу.

Напомню условие, а чтобы оно не занимало много места в и без того длинной статье, спрячу его под спойлер:

with price(stock_id, prod_id, start_date, kind, price1, cost1, bonus1) as (values (1,1,to_date('2000-01-01','YYYY-MM-DD'),'R',100.0,32.12,6.49),       (1,1,'2000-01-02','P', 80.0, 0,   0),       (1,1,'2000-01-03','P', 70.0, 0,   0),       (1,1,'2000-01-04','R',110.0,33.48,6.19),       (1,1,'2000-01-05','P', 90.0, 0,   0),       (1,1,'2000-01-06','R',120.0,41.22,6.19),       (1,1,'2000-01-07','P', 80.0, 0,   0),       (1,1,'2000-01-08','P', 90.0, 0,   0),       (1,1,'2000-01-09','R', 93.0,36.87,6.49),       (1,1,'2000-01-10','R', 94.0,36.85,6.99),       (1,2,'2000-01-01','R',101.0,52.06,9.00),       (1,2,'2000-01-02','P', 81.0, 0,   0),       (1,2,'2000-01-03','P', 71.0, 0,   0),       (1,3,'2000-01-04','R',111.0,64.96,4.50),       (1,3,'2000-01-05','P', 92.0, 0,   0),       (1,3,'2000-01-06','R',122.0,66.83,4.60),       (1,3,'2000-01-07','P', 82.0, 0,   0),       (1,3,'2000-01-08','P', 92.0, 0,   0))select ...

 stock_id | prod_id | start_date | kind | price1 | cost1 | bonus1 | price1x ----------+---------+------------+------+--------+-------+--------+---------        1 |       1 | 2000-01-01 | R    |  100.0 | 32.12 |   6.49 |   100.0        1 |       1 | 2000-01-02 | P    |   80.0 |     0 |      0 |   100.0        1 |       1 | 2000-01-03 | P    |   70.0 |     0 |      0 |   100.0        1 |       1 | 2000-01-04 | R    |  110.0 | 33.48 |   6.19 |   110.0        1 |       1 | 2000-01-05 | P    |   90.0 |     0 |      0 |   110.0        1 |       1 | 2000-01-06 | R    |  120.0 | 41.22 |   6.19 |   120.0        1 |       1 | 2000-01-07 | P    |   80.0 |     0 |      0 |   120.0        1 |       1 | 2000-01-08 | P    |   90.0 |     0 |      0 |   120.0        ...

Задачу эту можно решать разными способами. Начнём с самого простого:

Первый подход

Это решение в лоб. Так как у нас к каждой записи исходного набора данных нужно выбрать какие-то значения, то сделаем это подзапросом:

select * -- выбрать все поля из источника данных       -- а тут подзапрос, выбирающий нужную цену     , (select price1          from price sub         where sub.stock_id = p.stock_id -- те же склад           and sub.prod_id  = p.prod_id  -- и товар,           and sub.kind = 'R'            -- оставим только настоящие цены           and sub.start_date <= p.start_date  -- с датой более ранней или такой же,         order by start_date desc        -- отсортируем в порядке последние цены раньше         limit 1                         -- и возьмём только первую строку       ) as price1x  from price p;

Всё очень прямолинейно и просто, комментарии объясняют, что и как выбирается. Самое главное обеспечить, чтобы такой подзапрос возвращал не более одной записи, а то будет ошибка. Попутно отметим, что если записей не нашлось, что запрос вернёт NULL. В принципе это логичное поведение, но если дальше эти цены будут участвовать в каких-то расчётах, то это надо будет принять во внимание.

Отлично, мы сделали это! Запускаем! Можно расслабиться, прыснуть себе чего-нибудь зободробительного в жвалы, сделать первый (и самый вкусный) гулп... И пока запрос работает, давайте немного отвлечёмся и поразмышляем об его эффективности.

Итак, по условию у нас исходным набором данных была сборная вьюха. Что она там и как выбирает и не перелопачивает ли для этого полбазы неизвестно. Но сборная вьюха и эффективность это обычно понятия плохо совместимые. То есть ожидания от вьюхи что она тормознутая. И второй неутешительный вывод, который просто напрашивается: сборная вьюха практически не оставляет надежд на наличие индексов, так что в подзапросе, чтобы найти нужный склад и нужный товар на нём, скорее всего придётся прочитать всю вьюху целиком. На каждый подзапрос. А если она в самом деле полбазы вычитывает? Миллион строк, да для каждой полбазы перечитать... Вот так на ровном месте и без использования каких бы то ни было спецсредств можно поставить на колени практически любую базу. И мы получим highload на ровном месте. Вернее в highload это превратится, если оптимизатор найдёт способ распараллелить выполнение запроса, так что может быть всё ещё не так уж плохо. У меня на тестовых данных, кстати говоря, сумел.

А ну-ка запустим в соседнем окошке select count(*) from price и внимательно посмотрим как на количество записей, так и на время исполнения. Потому что именно эти два числа нужно будет умножить друг на друга, чтобы прикинуть общее время выполнения запроса. Наш запрос будет работать хоть сколько-нибудь обозримое время, только если количество записей не будет превышать десятков или сотен, ну максимум тысяч записей. Так что полюбовались на количество записей, и давай, тяни свои ложноножки (или ложноручки, что там у тебя) к консоли и прерывай запрос, пока не прилетели админы, чтобы убедительно объяснить на пальцах, что надо и что не надо делать на боевом сервере.

Подход второй

Второе, что приходит в голову, глядя на эту задачу это отфильтровать все реальные цены в отдельный набор данных и соединить (с-JOIN-ить) с самим исходным набором по условию, весьма смахивающему на то, которое мы использовали в подзапросе в подходе номер один.

Это в целом годный и рабочий способ, исходная вьюха скорее всего будет прочитана только два раза, если вдруг оптимизатор не найдёт более эффективный план выполнения (что вряд ли) или банально не ошибётся. Это даст хорошую производительность, но... Что же но? Почему не хочется так делать? А всё очень просто. Такая конструкция в виде соединения двух наборов данных с витиеватыми условиями, включая выборку только нужных данных, довольно капризна. В этих декартовых соединениях легко ошибиться и потерять часть исходных данных или наоборот, выбрать чего-то лишнего.

А ещё тяжело будет сопровождать и развивать такой запрос. Если появятся новые требования, понадобится что-то добавить или изменить, или поменять поведение в каких-то граничных случаях, то почти на любое такое изменение скорее всего придётся переписывать запрос заново.

Так что оставим этот способ напоследок, а сперва попробуем способ получше, и это...

Подход третий

Что там у нас сделать-то надо было? Сгруппировать и для каждой группы выбрать значение?.. Ба, да сюда же прямо просятся аналитические функции! Они как раз предназначены для подобных задач, когда нужно что-то сгруппировать и выбрать для каждой группы. Если при этом нужно оставить только значения для каждой группы, то используются аналитические функции, а если нужно сохранить весь исходный набор данных то оконные.

Ну-ка, попробуем. Для каждой строки нам нужно выбрать ближайшую реальную цену, то есть надо использовать именно оконные функции. Определить окно, в которое попадут только нужные записи, отсортировать и выбрать значение из нужной строчки окна:

select *     , agg_func(...) over (...) -- тут нужно будет определить окно и найти подходящую функцию  from price

Казалось бы всё просто. Функция будет из тех, которые возвращают одно из значений в группе, это first_value() или lag() или что-то подобное. А вот с определением окна нужно немного помудрить. Оконные функции позволяют определять окно, указав группировку и сортировку. Понятно, что в определении окна будет partition by stock_id, prod_id и что-то надо добавить, чтобы ближайшая предыдущая строчка с реальной ценой встала на фиксированное место в этой группе. Если это не получается сделать в лоб (как, например, если бы нам надо было выбрать просто самую первую или минимальную цену), то обычно помогает такой приём, когда определяют специальное вычислимое поле и по нему делают или группировку, или сортировку. Навскидку пишем case when kind='R' then, потому что от поля kind у нас точно есть зависимость, и задумываемся... Мнэ...

Как выразился один из комментаторов предыдущей статьи, неожиданно стало интересно. Сходу найти подходящее под условие выражение, чтобы отделить группу записей, начинающихся со строки 'R', от других, что-то не получилось. И не сходу тоже как-то не получилось...

Решение постепенно нашлось, и вот какой приём при этом был применён. Получилась двухходовка. На первом этапе формируем специальное вычислимое поле, по которому на втором этапе уже выбираем нужные значения. Поле (назовём его уровнем цены) формируем так: суммируется нарастающим итогом для каждого склада и товара в порядке даты начала действия следующее: единица для строк с kind='R' и ноль для всего остального. Получается как раз, что уровень цены перещёлкивается на следующий, как только мы встречаем реальную цену:

select *     , sum(case when kind='R' then 1 else 0 end) -- сумма нарастающим итогом       over(partition by stock_id, prod_id       -- в разрезе складов и товаров            order by start_date                  -- порядок важен!            rows between unbounded preceding and current row) as lvl -- нам нужно именно до CURRENT ROW  from price

Теперь осталось только оконной функцией выбрать самое первое значение в каждой такой группе:

select *     , first_value(price1) over (partition by prod_id, stock_id, lvl                                     order by start_date) as price1x  from (select ...)

Собрать вместе можно хоть через подзапрос, хоть с CTE. Смотрим на результаты выполнения вычисляется за один проход, то есть в высшей степени эффективно (достат кол!).

Итого, вот оно решение:

select p.*     , first_value(price1) over (partition by prod_id, stock_id, lvl                                     order by start_date) as price1x  from (select *             , sum(case when kind='R' then 1 else 0 end)               over(partition by stock_id, prod_id order by start_date                    rows between unbounded preceding and current row) as lvl  from price) p

Дальше, если надо будет вытащить остальные значения из нужной строчки, то можно повторить first_value() нужное количество раз. Для компактности можно вынести определение окна отдельно, чтобы не загромождать запрос:

select *     , first_value(price1) over w as price1x     , first_value(cost1)  over w as cost1x  from (select *             , sum(case when kind='R' then 1 else 0 end)               over(partition by stock_id, prod_id order by start_date                 rows between unbounded preceding and current row) as lvl          from price) pwindow w as (partition by prod_id, stock_id, lvl order by start_date)

Вот собственно и всё, на этом можно закончить, реализация второго подхода не понадобилась.

Выводы

1. Во время составления SQL-запроса имеет смысл думать про эффективность его работы.

2. Использовать оконные функции отличная идея.

3. Если оконную функцию не получается применить в лоб, то можно попробовать разбить задачу на две подзадачи генерация поля для группировки, последующий вызов оконной функции поверх группировки с предыдущего шага.

4. Если оконную функцию применить так и не удалось, то подход 2 даст нужные результаты, а если время работы запроса не является проблемой, то подход 1 даже лучше, потому что он прост и понятен.

Разбор решений, приведённых в комментариях

В статье с условием задачи я пообещал сделать разбор решений из комментариев с раздачей каких-нибудь слонов. Выполняю обещание.

Так или иначе я насчитал 11 разных вариантов решения от 10 представителей. Вообще запросов было приведено больше, но часто следующий SQL-запрос в цепочке комментариев был развитием или исправлением предыдущего. Так что количество сильно зависит от того, как считать. Пройдёмся по хронологии.

Статья с условием задачи была опубликована 16 марта 2021 в 17:12. Первое решение от @nice17 16 марта 2021 в 17:34 было предложено уже через 22 минуты после публикации статьи (22 минуты, Карл! и ещё же надо было хоть по диагонали прочитать условие). Решение было неверным, но подход к решению явно был уже в нужном направлении. Чуть позже автор довёл его до правильного. А первое более-менее работающее решение (от @ZMB138 16 марта 2021 в 19:07) появилось меньше чем через два часа(!) после публикации, это была реализация подхода 2.

Первое решение в третьем подходе от @Kilor появилось ещё спустя полчаса 16 марта 2021 в 19:39. Автор применил хитрый ход, использовав функцию array_agg(), которая допускает использование FILTER в определении окна, что позволило отфильтровать только записи с реальными ценами и избежать рассмотренной мной двухходовости. Также продемонстрирован интересный ход, что вся найденная для сопоставления строка таблицы упаковывалась в JSON, из которого дальше выбиралось нужное поле. Или все нужные поля. Не очень универсально в плане разных диалектов SQL, но изящно. Последняя версия запроса от 17 марта 2021 в 10:18 уже без упаковки/распаковки в JSON получилась компактной и эффективной.

(замечание в сторону) Воистину, вырисовывается универсальный способ, как можно решать сложные задачи на SQL публиковать их на хабре, и дальше остаётся только подождать пару часов!..

Аналогичный подход использовал @AngelloreA в запросе от 16 марта 2021 в 22:30. Сопоставляемая строка с реальными стоимостями упаковывалась правда не в JSON, а просто в текст, что потенциально чревато проблемами при распаковке (надо бы явно указать форматы), но это работает и тоже в один проход, то есть получилась реализация подхода 3.

@viras777 16 марта 2021 в 23:07 привёл необычное решение, где весьма остроумно для генерации уровней цены используется последовательность. В целом, как мне кажется, использовать последовательность для такого случая избыточно (да и Оккам не велел), плюс побочных эффектов в программировании обычно стараются избегать, но тем не менее оно работает.

@AlexKadetov уже 16 марта 2021 к 23:10 получил по сути такое же решение в подходе 3, к которому пришёл и я, пусть даже и с некоторыми огрехами в реализации. Вообще огрехи в реализации были много у кого, я старался не придираться, потому что в реальной жизни конечно всякий запрос ещё какое-то время доводится до кондиции, уточняется его работа и всё подобное вылавливается и исправляется.

Ночью @qvan 17 марта 2021 в 01:39 привёл несколько тяжеловесное, но рабочее решение в подходе 2. Утром @xxxcoltxxx 17 марта 2021 в 09:13 привёл более явное и ясное решение в реализации подхода 2. Чуть позже @jayrumi 17 марта 2021 в 13:11 тоже опубликовал своё пусть и несколько тяжеловесное, но работающее решение тоже в подходе 2.

Дальше опять вернулся @Kilor и 17 марта 2021 в 14:30 привёл пару красивых решений в подходе 2, продемонстрировав виртуозную технику JOIN-ов наборов данных. Ближе к вечеру @Miha_S7 17 марта 2021 в 18:19 привёл своё решение в подходе 3, повторяющее полученное мной в первой части статьи.

На этом активность в комментариях заглохла, статья скрылась из леныт сайта, и больше запросов никто не публиковал.

Итого, из интересного отмечу скорость, с которой тема была раскрыта со всех сторон, включая диалекты дружественных СУБД. Плюшкополучателем был избран @Kilor, как автор самого первого однопроходного решения (в подходе 3), а также за проявленную широту охвата техники владения SQL (использование оконных функций, JSON, FILTER, JOIN-ы) и ясную по форме реализацию с использованием всего перечисленного. С ним я уже связался. Специально отмечу самое первое работающее решение от @ZMB138, оригинальность и неожиданность использования последовательностей от @viras777, а также эталонное на мой взгляд решение от @AlexKadetov.

И отдельное большое спасибо всем, кто принял участие в обсуждении и поделился своими решениями.