PostgreSQL Çorbası

3 Aralık 2020 Perşembe

TIMESTAMP metodu

Giriş

Precision verilebilir. Açıklaması şöyle.

Postgres allows you to specify precision(0 to 6) while casting to TIMESTAMP

Örnek

select (now() at time zone 'utc') normalde şu çıktıyı verir

2020-12-03 09:39:28.992948

Şöyle yaparız

select (now() at time zone 'utc') :: timestamp(3)

Çıktı olarak 972 ile biten şu değeri alırız.

2019-01-29 08:54:28.972

Örnek - String Girdi

Şöyle yaparız.

select timestamp '2012-08-31 01:00:00';

Çıktı olarak şunu alırız

2012-08-31 01:00:00

Çıkartma İşlemi
Açıklaması şöyle.
Subtracting timestamps produces an INTERVAL data type. INTERVALs are a special data type for representing the difference between two TIMESTAMP types. When subtracting timestamps, Postgres will typically give an interval in terms of days, hours, minutes, seconds, without venturing into months. This generally makes life easier, since months are of variable lengths.

11 Kasım 2020 Çarşamba

pg_notify metodu - LISTEN ve NOTIFY

Giriş

Not : Java gerçekleştirimi burada

Açıklaması şöyle

The NOTIFY command sends a notification event together with an optional "payload" string to each client application that has previously executed LISTEN channel for the specified channel name in the current database. Notifications are visible to all users.

Açıklaması şöyle

PostgreSQL has got the LISTEN/NOTIFY commands. Set up a trigger on each table that calls [NOTIFY ‘tableName_changed’]. Any process connected to the database can wait using [LISTEN ‘tableName_changed’].

Örnek

Şöyle yaparız. table_name yerine istediğimiz tablo ismi gelir. Burada kendi trigger metodumuzu belirtiyoruz.

CREATE TRIGGER table_change 
    AFTER INSERT OR UPDATE OR DELETE ON table_name
    FOR EACH ROW EXECUTE PROCEDURE notify_change();

Trigger metodumuz şöyler. pg_notify() çağrısı yapılıyor. Burada değişen tablo ismi TG_TABLE_NAME ile elde edilir.

CREATE OR REPLACE FUNCTION notify_change() RETURNS TRIGGER AS $$
    BEGIN
        SELECT pg_notify('test', TG_TABLE_NAME);
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

Örnek

Şöyle yaparız

CREATE TABLE PUBLIC.TBLEXAMPLE
(
  KEY1 CHARACTER VARYING(10) NOT NULL,
  KEY2 CHARACTER VARYING(14) NOT NULL,
  VALUE1 CHARACTER VARYING(20),
  VALUE2 CHARACTER VARYING(20) NOT NULL,
  CONSTRAINT TBLEXAMPLE_PKEY PRIMARY KEY (KEY1, KEY2)
);

CREATE OR REPLACE FUNCTION PUBLIC.NOTIFY() RETURNS TRIGGER AS
$BODY$
BEGIN
  PERFORM pg_notify('myevent', row_to_json(NEW)::text);
  RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;


CREATE TRIGGER TBLEXAMPLE_AFTER
 AFTER insert or update or delete ON PUBLIC.TBLEXAMPLE
FOR EACH ROW
EXECUTE PROCEDURE PUBLIC.NOTIFY();

1 Kasım 2020 Pazar

Docker ve PostgreSQL

Giriş

Image ismi olarak

postgres:12

postgres:alpine kullanılabilir

1. docker pull ile İndirmek

PostgreSQL'i indirmek için şöyle yaparız

docker pull postgres

Belli bir PostgreSQL sürümünü indirmek için şöyle yaparız

docker pull postgres:12

Bu işlemden sonra image dosyasını kontrol etmek için şöyle yaparız

docker images

2. docker run ile Veri tabanını Başlatmak

Bazı ortam değişkenleri isimleri şöyle. Bu değişkenler -e seçeneği ile birlikte kullanılırlar

POSTGRES_USER

POSTGRES_PASSWORD

POSTGRES_DB

PGDATA

Ayrıca ilk indirilen postgre her yerden bağlantı kabul etmez. Bu ayarları değiştirmek için pg_hba.conf ve postgresql.conf dosyalarına ayarlar yapmak gerekir

Örnek

PostgreSQL'i çalıştırmak için şöyle yaparız. Burada -v ile bir volume postgre'nin kullanması için mount ediliyor. O an bulunduğumuz dizin yani PWD, postgre açısından /var/lib/postgresql/data dizini oluyor
Burada kullanıcı ismi belirtilmiyor. Dolayısıyla kullanıcı ismi "postgre" olacak ancak şifresi POSTGRES_PASSWORD ile belirtiliyor. Şifre "docker" olacak

docker run --name pg-docker -e POSTGRES_PASSWORD=docker -e POSTGRES_DB=sampledb
  -e PGDATA=/tmp -d -p 5433:5432 -v ${PWD}:/var/lib/postgresql/data postgres:11

Açıklaması şöyle

Container data is gone once it is stopped and this is useful for certain situations (e.g. if you are running some database/integration testing and want to get rid of test data then it's great). But if we want to persist data generated by the Postgres instance running inside a container beyond the container’s lifecycle, we need to map a local mount point as a data volume to an appropriate path inside the container.

Örnek

Şöyle yaparız. Burada image ismi olarak postgres:alpine kullanılıyor. Daha sonra veri tabanına kabul açılıyor

docker run \
  --name postgres-spring \
  -e POSTGRES_PASSWORD=password \
  -d \
  -p 5432:5432 \
  postgres:alpine

docker exec -it postgres-spring bin/bash
> psql
veya
> psql -U postgres

Örnek

Şöyle yaparız. Burada Linux ve Windows arasındaki çoklu satır farkı görülebilir. Linux'ta \ karakteri kullanılır, Windows'ta ^ karakteri kullanılır.

#Linux
$ docker run --rm \
--name ewallet-db \
-e POSTGRES_DB=ewalletdb \
-e POSTGRES_USER=ewallet \
-e POSTGRES_PASSWORD=xxxxxxxxxx \
-e PGDATA=/var/lib/postgresql/data/pgdata \
-v "$PWD/ewalletdb-data:/var/lib/postgresql/data" \
-p 5432:5432 \
postgres:14

#Windows
docker run --rm ^
--name ewallet-db ^
-e POSTGRES_DB=ewalletdb ^
-e POSTGRES_USER=ewallet^
-e POSTGRES_PASSWORD=xxxxxxxxxx ^
-e PGDATA=/var/lib/postgresql/data/pgdata ^
-v “%cd%\ewalletdb-data:/var/lib/postgresql/data” ^
-p 5432:5432 ^
postgres:14

Bağlanmak için şöyle yaparız

psql -h 127.0.0.1 -U ewallet ewalletdb

Örnek

Şöyle yaparız. Burada Debezium için hazır hale getiriliyor.

docker run -d --name postgres -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 postgres -c wal_level=logical

Örnek

Şöyle yaparız

docker run -d -p 5432:5432 
  --rm \
  -e POSTGRES_PASSWORD=postgres \
  -e PGDATA=/var/lib/postgresql/data/pgdata \
  -v /home/user/postgres/data:/var/lib/postgresql/data \
  --name postgres \
  postgres

3. docker exe ile SQL Çalıştırmak

Örnek

Şöyle yaparız. Burada container ismi pg_docker. Çalıştırılacak komut psql ve parametreleri

docker exec -it pg-docker psql -U postgres -c "CREATE DATABASE testdb;"

Örnek

Şöyle yaparız. Burada container ismi pg_docker. Çalıştırılacak komut psql ve parametreleri

docker exec -it pg-docker psql -U postgres -f /opt/scripts/test_script.sql

Örnek

Yine bash açarak dockerized postgresql data dosyalarına bakmak için şöyle yaparız. Burada docker'a geçince prompt'un $ karakterinden root@xyx şeklinde değiştiği görülebilir.

$ docker exec -it my-postgres-db-container bash
root@c7d61efe2a5d:/# cd /var/lib/postgresql/data/
root@c7d61efe2a5d:/var/lib/postgresql/data# ls -lh
total 56K
drwx------. 7 postgres postgres   71 Apr  5  2018 base
drwx------. 2 postgres postgres 4.0K Nov  2 02:42 global
drwx------. 2 postgres postgres   18 Dec 27  2017 pg_clog
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_commit_ts
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_dynshmem
-rw-------. 1 postgres postgres 4.4K Dec 27  2017 pg_hba.conf
-rw-------. 1 postgres postgres 1.6K Dec 27  2017 pg_ident.conf
drwx------. 4 postgres postgres   39 Dec 27  2017 pg_logical
drwx------. 4 postgres postgres   36 Dec 27  2017 pg_multixact
drwx------. 2 postgres postgres   18 Nov  2 02:42 pg_notify
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_replslot
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_serial
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_snapshots
drwx------. 2 postgres postgres    6 Sep 16 21:15 pg_stat
drwx------. 2 postgres postgres   63 Nov  8 02:41 pg_stat_tmp
drwx------. 2 postgres postgres   18 Oct 24  2018 pg_subtrans
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_tblspc
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_twophase
-rw-------. 1 postgres postgres    4 Dec 27  2017 PG_VERSION
drwx------. 3 postgres postgres   92 Dec 20  2018 pg_xlog
-rw-------. 1 postgres postgres   88 Dec 27  2017 postgresql.auto.conf
-rw-------. 1 postgres postgres  21K Dec 27  2017 postgresql.conf
-rw-------. 1 postgres postgres   37 Nov  2 02:42 postmaster.opts
-rw-------. 1 postgres postgres   85 Nov  2 02:42 postmaster.pid

4. Veri tabanını Doldurarak Çalıştırmak

Örnek

Elimizde şöyle bir DockerFile olsun

FROM postgre:11
LABEL author="Jawad Hasan"

ENV POSTGRES_PASSWORD sasa
ENV POSTGRES_DB sampledb

COPY dbscriptOrder/ /docker-entrypoint-initdb.d/

dbscriptOrder/ dizininde ismi rakam ile başlayan iki tane dosya olsun

2-createtable

3-insertdata

docker imajı için şöyle yaparız

docker image build -t postgresbasic .

Daha sonra docker'ı doldurulmuş veri tabanı ile başlatmak için şöyle yaparız

docker run --name pg-docker -e PGDATA=/tmp -d -p 5433:5432
  -v ${PWD}:/var/lib/postgresql/data postgresbasic

Örnek

Elimizde şöyle bir DockerFile olsun. Burada veri tabanı ayar dosyaları da yeni image içine dahil ediliyor.

FROM postgres:12
ADD pg_hba.conf /var/lib/postgresql/data/
ADD postgresql.conf /var/lib/postgresql/data/
COPY init.sql /docker-entrypoint-initdb.d/

Yeni image build edip çalıştırmak için şöyle yaparız

docker build --t medium/database:latest .

docker run --rm -e POSTGRES_PASSWORD=P@ssword1 
  -v /opt/workspace/nerdcode/docker/data/medium:/var/lib/postgresql/data:rw 
  -p 5432:5432 medium/database:latest

13 Ekim 2020 Salı

Transaction Isolation Level - Repeatable Read Seviyesi

Repeatable Read Transaction Başlatmak

Örnek

Şöyle yaparız

=# SET default_transaction_isolation TO 'Repeatable Read';

Repeatable Read Başladıktan Sonra - SELECT Hep Aynı Sonucu Döndürür

Örnek

Elimizde şöyle bir kod olsun. TX1 a tablosunda b tablosundaki toplam satır sayısını yazıyor. TX2 ise b tablosuna a tablosundaki toplam satır sayısını yazıyor.

TX2 a tablosundan select yapar. TX1 a tablosunda insert yapıp, commitlese bile, TX2 yine a tablosundan select yaparsa boş sonuç elde eder.

-- Tx1                              -- Tx2
=# BEGIN;                           =# BEGIN;
BEGIN                               BEGIN

=# INSERT INTO a
   SELECT count(*) FROM b;
INSERT 0 1
                                    =# INSERT INTO b
                                       SELECT count(*) FROM a;
                                    INSERT 0 1
=# COMMIT;
COMMIT
                                    =# SELECT COUNT(*) FROM a;
                                     count 
                                    -------
                                         0
                                    (1 row)
                                    
                                    =# COMMIT;
                                    COMMIT

Örnek

Elimizde şöyle iki transaction olsun

-- Tx1:
simple_bank> select * from accounts;
 id | owner | balance | currency |          created_at
---------+-------+---------+----------+-------------------------------
  2 | two   |     100 | USD      | 2020-09-06 15:06:44.666424+00
  3 | three |     100 | USD      | 2020-09-06 15:06:44.666424+00
  1 | one   |      80 | USD      | 2020-09-06 15:06:44.666424+00
(3 rows)

-- Tx2:
simple_bank> select * from accounts where id = 1;
 id | owner | balance | currency |          created_at
---------+-------+---------+----------+-------------------------------
  1 | one   |      80 | USD      | 2020-09-06 15:06:44.666424+00
(1 row)

simple_bank> select * from accounts where balance >= 80;
 id | owner | balance | currency |          created_at
---------+-------+---------+----------+-------------------------------
  2 | two   |     100 | USD      | 2020-09-06 15:06:44.666424+00
  3 | three |     100 | USD      | 2020-09-06 15:06:44.666424+00
  1 | one   |      80 | USD      | 2020-09-06 15:06:44.666424+00
(3 rows)

TX1 hesaptan 10 TL çekerek güncelleme yapar

-- Tx1:
simple_bank> update accounts set balance = balance - 10 where id = 1 returning *;
 id | owner | balance | currency |          created_at
---------+-------+---------+----------+-------------------------------
  1 | one   |      70 | USD      | 2020-09-06 15:06:44.666424+00
(1 row)

UPDATE 1

TX1 commitler ancak TX2 halen ilk okuduğu veriyi görmektedir.

-- Tx1:
simple_bank> commit;
COMMIT

-- Tx2:
simple_bank> select * from accounts where id = 1;
 id | owner | balance | currency |          created_at
---------+-------+---------+----------+-------------------------------
  1 | one   |      80 | USD      | 2020-09-06 15:06:44.666424+00
(1 row)

Hatta TX2'nin eski veriyi gördüğü bile tekrar kontrol edilebilir

-- Tx2:
simple_bank> select * from accounts where balance >= 80;
 id | owner | balance | currency |          created_at
---------+-------+---------+----------+-------------------------------
  2 | two   |     100 | USD      | 2020-09-06 15:06:44.666424+00
  3 | three |     100 | USD      | 2020-09-06 15:06:44.666424+00
  1 | one   |      80 | USD      | 2020-09-06 15:06:44.666424+00
(3 rows)

Ama TX2 update yapamaz, çünkü yaparsa normalde Lost Update problemi olur. PostgreSQL buna izin vermediği için hata verir

-- Tx2:
simple_bank> update accounts set balance = balance - 10 where id = 1 returning *;
ERROR:  could not serialize access due to concurrent update

Örnek

Elimizde şöyle iki tane sql olsun. TX1 foo tablosunda yeni satır yaratır

INSERT INTO foo (name) VALUES ('BAZ')

TX2 foo tablosunu sorgular ve bar tablosuna yazar

INSERT INTO bar (foo_name, foo_id) VALUES ('BAZ', (SELECT id FROM foo WHERE name = 'BAZ'))

Eğer iki cümle Read Committed olarak eşzamanlı (concurrent) çalıştırılıyorsa ikinci cümlenin id sütununun bazen null olduğu görülebilir. Açıklaması şöyle

it is possible under certain circumstances to end up inserting a row into bar where foo_id is NULL. The two queries are executed in different transactions, by two completely different processes.

Ancak eğer TX2'de Repeatable Read kullanırsak TX1 ile aynı anda başlarsa TX1'in hiçbir yazdığını göremez. Açıklaması şöyle

The subselect in the INSERT INTO bar cannot see the new row concurrently inserted in foo because the latter is not committed yet.

But by the time that the query that checks the foreign key constraint is executed, the INSERT INTO foo has committed, so the foreign key constraint doesn't report an error.

A simple way to work around that is to use the REPEATABLE READ isolation level for the INSERT INT bar. Then the foreign key check uses the same snapshot as the INSERT, it won't see the newly committed row, and a constraint violation error will be thrown.

Repeatable Read Başladıktan Sonra - Lost Update Problemi Olmaz

Eğer bir başka transaction bizim istediğimiz satırı değiştirir ve commit'lerse "could not serialize access due to concurrent update" hatası alırız. Açıklaması şöyle.

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the repeatable read transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the repeatable read transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message.

ERROR: could not serialize access due to concurrent update

Örnek

Elimizde şöyle bir kod olsun. Burada TX1 update işlemine başlıyor. TX2 is select işlemine başlıyor. Birbirlerini engellemezler. Ancak TX'de aynı anda yazmak isterse Lost Update Problem olacağı için TX2 rollback edilir.

-- Tx1                                 -- Tx2
=# BEGIN;                              =# BEGIN;
BEGIN                                  BEGIN

=# UPDATE list SET x=x-1;              =# SELECT * FROM list; 
                                        x                     
                                       ---                    
                                        1                     
                                        2                     
                                        3                     
                                        4                     
                                       (4 rows)             
                                       
                                       =# DELETE FROM list WHERE x=4;
                                       -- (Tx2 gets blocked)

=# COMMIT
COMMIT
                                        -- Tx2 ends in error
                                        ERROR:  could not serialize access
                                        due to concurrent update
                                        
                                        =# \echo :SQLSTATE
                                        40001
                                        
                                        =# ROLLBACK;
                                        ROLLBACK

Örnek

Şöyle yaparız. TXA lost update problemine maruz kalacağı için rollback edilir.

process A: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
process B: BEGIN;
process B: UPDATE purchases SET value = 500 WHERE id = 1;
process A: UPDATE purchases SET value = 600 WHERE id = 1;
-- process A wants to update the value while process B is changing it
-- process A is blocked until process B commits

process B: COMMIT;
process A: ERROR:  could not serialize access due to concurrent update
-- process A immediately errors out when process B commits

7 Ekim 2020 Çarşamba

SHOW ...

Giriş

postgresql.conf dosyasındaki değerleri gösterir

all

Şöyle yaparız

SHOW all;

data_directory

Şöyle yaparız

SHOW data_directory;

Benim sistemimde çıktı olarak "C:/Program Files/PostgreSQL/11/data" veriyor.

max_connections

Şöyle yaparız

> SHOW max_connections;

| max_connections |
|-----------------|
| 300             |