1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

postgresql よく使うコマンドや小ネタ 備忘録

Last updated at Posted at 2024-03-13

どうでもいい話~psqlでは;がいらない~

psql -U postgres -c 'select * from sample' tm_agg_data
psql -U postgres -c 'select * from sample;' tm_agg_data

どちらでもsampleテーブルの結果が返ってくる。
PostgreSQLのターミナルにそのまま流しているのであれば上のほうは実行結果が表示されないはず。(下の行に連続して記述できるため)
「;」がなくてもコマンドを認識できるのか。

setコマンド

・実行時のパラメータを変更するもの。セッション(ユーザーで接続している間)内かトランザクションかは選ぶことができる。「set local」 or 「set session」。
・トランザクションがabortされるとset効力が失う。
・PostgreSQL ver8.0~8.2までset localの効果は以前のセーブポイントを開放することだった。
使用例)

set使用前
postgres=# show search_path;
   search_path   
-----------------
 "$user", public
seach_path変更
postgres=# create schema t_schena;
CREATE SCHEMA
postgres=# set search_path to 't_schema';
SET
set使用後
postgres=# show search_path;
 search_path 
-------------
 t_schema
sessionを切って、再確認
postgres=# \q (session切断)
root@9dc74f27d78a:~# psql -U postgres
postgres=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

もとに戻ってました。

テーブルの移動・型確認

ALTER TABLE 対象のテーブル名 SET SCHEMA 移動先のスキーマ名
ALTER TABLE テーブル名 ALTER COLUMN 列名 [SET DATA] TYPE 新しい型;
\d (テーブル名)

mysqlの場合はshow columns from ;でテーブルの列の型確認できる。

権限系

ALTER USER myuser NOSUPERUSER;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE <tablename> TO <username>;

設定系

create user superuser createdb <rolename>;
ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';

明示的ロック

テーブルロック

ロックモードの名前にrowとあっても表全体を指す。結局どのモードと競合するか問題。行レベルロックは別に存在する。

テーブルロックレベル(PostgreSQL 9.4.5文書より)
image.png

MAX:Access exclusiveモード
その他のトランザクションを完全にロック。ロック界の最強。
DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULLコマンドによって獲得される。

行レベルロック(PostgreSQL 9.4.5文書より)

行レベルロックは、同じ行に対する書き込みとロックだけをブロック
image.png

MAX:FOR UPDATE
現在のトランザクションが終わるまでは他のトランザクションがロック、変更、削除ができなくなる。

roleとuserの違い

postgres=# create role hida with login;
postgres=# create user hida;

create roleの場合、デフォルトでcannot login になるのをふせぐ。
もしくは、create userをつかう。
ネットに出てる話はこの程度の情報にとどまる。「緑本OSS-DB Silver ver3.0対応」をみると、解説は以下の通り。(P76)

postgresqlにもOSと同様にグループの概念がある。ユーザーとグループはPostgreSQL 8.1以前は異なるものだった。現在ではロールと呼ばれる概念に統合されている。PostgreSQLでは、このロールをユーザー、グループ、またはその両方として使われている。

create roleとuserがわざわざ2通り用意されているのは過去の遺物が残っているともいえる。

information_schema(情報スキーマ),pg_catalog(システムカタログ)

chatgptにきいてみた

情報スキーマ→SQL標準に準拠。異なるデータベースシステム間で互換性あり
システムカタログ→PostgreSQL特有。より詳細な情報を提供するため、互換性なし。
どちらもデータベースのメタデータを参照するために使用されるが、情報スキーマはより一般的な情報を、システムカタログはPostgreSQL固有の詳細な情報を提供する。

情報関数についての小ネタ

ふと情報関数の中でかっこつきとそうでないのがあったので気になったので調べてみた。
以下の2つのコマンドは何が違うだろう。

select version();
select current_timestamp;

postgresql 15.4文書によると、
https://www.postgresql.jp/docs/15/functions-info.html

image.png

とあった。となると上の2つのコマンドの例ではpostgresqlの制作者がversion()よりもcurrent_timestampやcurrent_user,session_userの情報を返すことのほうが重要だとみなしたことになる。
これは学びになった。

pg_hba.confについて

クライアント認証に関する設定を記述するファイル

type|database|user|address|mathod
host|all|all|127.0.0.1/32|md5

上から順に確認作業を始める。一致したところで認証作業を終え、接続の許可をする。ようするに先勝ち。読み込みに関してはいつも後勝ちで覚えてしまっていたので印象に残った。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?