All articles in postgresql

トランザクション

JDBCのデフォルト動作はautocommitである.Connectionオブジェクトのメソッド呼び出しにより,autocommitのon/offやCOMMIT,ROLLBACKができる.また,きれいな方法ではないがJDBCによるDBアクセスプログラムは,基本的にプログラムでSQL文を生成して発行するだけなので,BEGINとかCOMMITというSQL文を発行すればトランザクション処理ができる.概要サンプルConnectionオブジェクトによるトランザクション操作BEGIN,COMMIT等のSQL文を発行する方法トランザクションブロックSELECTにおけるターゲットリスト 概要 JDBCではデフォルトではautocommitとなっている.すなわち1SQL文で1トランザクションとなり自動でCOMMITがかかる.これはautocommitのon/offはConnectionオブジェクトで設定する.すなわち接続単位に設定することになる.さらにConnectionオブジェクトのcommit,rollbackメソッドによりCOMMITとROLLBACKを行う. サンプル 以下にautocommitをoffとしてトランザクション処理を行うサンプルを示す. このサンプルでは以下を行う.DB接続テーブル作成.COMMIT.作成したテーブルのレコード数を問い合わせる1トランザクションブロック内でインサート.COMMIT.作成したテーブルのレコード数を問い合わせるテーブル削除.COMMIT.DB切断これを実行してみる. Connectionオブジェクトによるトランザクション操作 BEGIN,COMMIT等のSQL文を発行する方法 Connectionオブジェクトのメソッド呼び出しではなく,JDBCの上位プログラムでBEGIN,COMMITなどのSQL文を発行することができた.その方法を以下に示すが推奨されない方法である.さて,JDBCのデフォルトではautocommitなので,トランザクションブロックはSQL文BEGIN;で開始しCOMMIT;あるいはROLLBACK;で終了する必要がある.いずれのSQL文もレコードが選択されることはないので,executeUpdateによって発行する.JDBCを利用してトランザクション処理を行うサンプルを以下に示す. トランザクションブロック SELECTにおけるターゲットリスト トランザクションから話題が離れるが,本頁の二つのサンプルでは以下のようなSELECT文を発行した.SELECT count(*) AS record_count from jdbcsample ;このSQL文では集約関数countの列にあえてrecord_countという名前をつけている.そして以下のようにその名前を使用して値を取得している.System.out.println (“Got record … More トランザクション

接続

まずlibpqを使用するための環境を整えた.で,接続しないと何にもできないので,libpqを使用してPostgreSQLへ接続する方法を調査してみた.環境構築サンプル接続関数PQconnectdb接続情報の取得切断環境構築Debian GNU/Linuxを使用している私は以下をした.apt-get install libpgsql2 postgresql-dev他の環境ではどうしたらよいのかは知らない.とりあえず以下があればよい(たぶん).libpq-fe.h私のマシンでは以下にあった./usr/include/postgresql/libpq-fe.hlibpq.so私のマシンでは以下にあった./usr/lib/libpq.so サンプル libpqでPostgreSQLに接続するプログラムでの処理概要は以下となる.PostgreSQLに接続PostgreSQLに要求切断本頁では,もっともPostgreSQLへの接続方法のうち,もっとも基本的なものと切断方法について記述する.また,DB接続プログラムでのもっとも基本的な注意点として,接続状態や接続情報の取得,チェックがある.これらの方法についても記述する.以下はPostgreSQLに接続して接続情報を取得,表示し,切断するサンプルである. 接続関数PQconnectdb libpqには,いくつかのDB接続関数がある.前節のサンプルでは以下を使用した.PGconn *PQconnectdb(const char *conninfo)引数は一つのcharへのポインタであり,先述の実行例のようにスペース区切りでキーワード=値を繋げたものである.主なキーワードは以下である.hostDBサーバ名hostaddrDBサーバのIPアドレスuserDB接続時のユーザ名passworduserに対するパスワードdbnameDB名PQconnectDBは内部でPGconnの領域を確保し,それへのポインタを返す.NULLを返すのは,PGconnへの領域確保に失敗した場合のみで,接続に失敗しても返す.PGconnはサーバへの接続情報であり,これによりサーバに対して複数接続し管理することができるようになっている.PGconnは直接操作,参照してはいけない(普通にはできないようになっている).接続状態は以下で取得する.ConnStatusType PQstatus(const PGconn *conn)返り値には多くのパターンがあるが,同期接続を行なうPQconnectdbの場合(※)は以下の二種類がある.CONNECTION_OK正常CONNECTION_BAD異常PQconnectDBを実行したあと,さまざまな要求をサーバに投げる前には,PQstatusで接続情報を確認する必要がある.※ サーバへの接続要求が(成功,失敗を問わず)完了するまでブロックする. 接続情報の取得 PGconnには接続状況だけでなく,サーバやユーザ名などの接続情報が格納されている.PGconnの内部は直接参照できないので,取り出したい情報に応じた関数を使用する.以下に上記のサンプルで使用した接続情報取得関数の一覧を示す.サーバからのエラーメッセージ以下の関数は,引数で渡す接続においてサーバから最後に取得したDB操作のエラーメッセージを返す.char PQerrorMessage(const PGconn … More 接続

SQLによるユーザ定義関数

PostgreSQLには多くのbuiltin関数があるが,自分で関数を定義することもできる.その定義はいろいろなプログラミング言語で行なうことができるが,ここではSQLによる定義について試してみた.CREATE FUNCTION行を一つ返す関数の例行を複数返す関数の例引数にレコードを取る関数の例 CREATE FUNCTION 行を一つ返す関数の例 行を複数返す関数の例 引数にレコードを取る関数の例

トランザクションと隔離レベルとロック

PostgreSQLでのトランザクションについて調査してみた.さらにトランザクションの並列実行時の問題とそれに対処するためのトランザクション隔離レベルとロックについても調査した.トランザクションブロックの指定トランザクションの例ACID特性と隔離レベルREAD COMMITTEDSERIALIZABLEとMVCCSERIALIZABLEの問題ロックSELECT FOR UPDATEによるロックテーブルロックロックモードどのロックモードを採用すべきか トランザクションブロックの指定 あるトランザクションの範囲,すなわちそのトランザクションの開始から終了までをトランザクションブロックと呼ぶ.で,PostgreSQLでは明示しなければ1SQL文で1トランザクションが完結する(※).PostgreSQLでは,トランザクションブロックの開始と終了を指定するSQL文がある.トランザクションブロックを開始するには,BEGIN;を発行する.SQL92ではトランザクションの開始は暗黙,すなわち連鎖モードなのでBEGINはない.BEGINの後にWORKまたはTRANSACTIONを記述してもよい(意味は同じ).そしてUPDATEやINSERTなどのいくつかのSQL文を発行し,結果を実際に反映したければ,END;またはCOMMIT WORK;を発行する.これでBEGINしてからのSQL文の結果をDBに反映し,トランザクションブロックを終了することができる.両者は同じ意味だが,後者はSQL92の書式らしい.さらにWORKは省略可能である.BEGINしてからいくつかのSQL文を発行し,途中で失敗等して結果を破棄したい場合はABORT;またはROLLBACK WORK;を発行する.これでBEGINしてからのSQL文の結果をDBに反映せずに破棄し,トランザクションブロックを終了することができる.両者は同じ意味だが,後者はSQL92の書式らしい.さらにWORKは省略可能である.※ これを非連鎖モードと呼ぶ.自動コミットとも呼ばれるらしい.逆にトランザクションの開始を明示しなくても,COMMITしない限りトランザクションが完結しないモードを連鎖モードと呼ぶ.PostgreSQL7.4対応のpsqlでは自動コミットモードのon,offを切替えることができるようだ. トランザクションの例 以下の例は思わずresult_linpackの全レコードをDELETEしてしまったが,ROLLBACKで復旧できたというものである.benchmark=> BEGIN;BEGINbenchmark=> SELECT count(*) FROM result_linpack; count (1 row) benchmark=> … More トランザクションと隔離レベルとロック

PostgreSQL

PostgreSQLをやってみたいと思って早一年半,ようやく重い腰をあげてみた.引っかかっていたのは,DBで遊ぶためのデータがない,ということだ.この問題は依然残っているが,その内いいネタがなんか見付かるだろう.導入編PostgreSQLでSQL入門応用編メンテナンスlibpqを使用したDBアクセスプログラミングecpgを使用したDBアクセスプログラミングJDBCを使用したDBアクセスプログラミング 導入編PostgreSQLの導入についてインストールと設定2003-07-20T15:13+09:00 matsuPostgreSQLのインストールと設定について.psql2003-07-20T15:13+09:00 matsuPostgreSQLのフロントエンドpsqlについて.ちょっと使ってみる2003-07-20T15:13+09:00 matsupsqlでPostgreSQLに接続していろいろ試してみる.GRANTとREVOKE2003-07-20T15:13+09:00 matsuテーブル毎にユーザのアクセス権を設定できる.アクセス権を与えるのがGRANTで奪うのがREVOKEである.PostgreSQLでSQL入門恥かしながら,SQLに関してほとんど忘れてしまったので,PostgreSQLを実際に使用しながらSQL入門してみる.問題作成2003-12-07T00:00+09:00 matsuこのページでは練習用DBの問題作成を行なって,以降のページでそれをPostgreSQL上に構築し,その過程でSQL入門していこうと思う.テーブル作成2003-12-29T14:35+09:00 matsu制約とその指定方法について簡単に記述し,作成した問題に対して,テーブルを作成してみる.データ投入2003-12-13T20:50+09:00 matsuようやく準備ができたので,データを投入してみた.SELECT2003-12-14T11:15+09:00 matsuDBの構築ができたので,ここから本格的にSQLを試していこうと思う.まずはSELECT.SELECTその22003-12-14T21:50+09:00 matsuSELECTに関して前頁で書ききれなかったことを.表の演算2003-12-23T18:00+09:00 matsuなんかアレなタイトルだが,とにかく表を結合したりしてみた.UPDATE2003-12-23T20:30+09:00 matsuDBのレコードの更新にはUPDATEを使用する.DELETE2003-12-23T21:00+09:00 matsuDBのレコードの削除にはDELETEを使用する.VIEWとRULE2003-12-27T11:00+09:00 matsu仮想的なテーブルVIEWを使用すると頻繁に使用する複雑なSQL文の記述が楽になったりする.RULEはVIEWに対するINSERT,UPDATE,DELETEを行なうのに使用する.トランザクションと隔離レベルとロック2003-12-28T16:00+09:00 matsuPostgreSQLでのトランザクションについて調査してみた.さらにトランザクションの並列実行時の問題とそれに対処するためのトランザクション隔離レベルとロックについても調査した.カーソル2003-12-29T13:30+09:00 matsuSELECTではLIMITを使用しなければ,選択レコードを一気に出力する.これを任意の件数ずつ取り出すようにするには,カーソルを使用する.テーブルに関するTips2003-12-29T15:50+09:00 matsu一時テーブル,SELECT文の結果からテーブルを作成,既存テーブルの属性変更などをしてみた.参照整合性制約について2003-12-29T22:25+09:00 matsu参照整合性制約に関して,チェックの遅延と複数の外部キーがある場合の注意点について.シーケンスとシリアル型2003-12-30T17:00+09:00 matsuシーケンスという連番自動生成機能とシーケンスによるシリアル型について.継承2004-10-30T17:00+09:00 … More PostgreSQL

動的SQL

埋め込みで動的なSQLというのはなんだか矛盾するような何かをはらんでいるような気がするのは私だけだろうか…とにかくやってみた.動的SQLって何だ?サンプルecpgのDEALLOCATEにおけるバグ?雛型を使用しない動的SQL雛型を使用した動的SQL 動的SQLって何だ? 思うに埋め込みSQLというのは,文字通りプログラムが発行するSQLをソースコードに埋め込んでしまうというものだろう.で,動的SQL?動的?何を埋め込むんだっけ?…動的SQLでは大体雛型を埋め込むということになるという認識でよいだろうか.とにかくコーディングの段階で確定するSQLを静的SQLとして,動的SQLはプログラムの実行段階で確定するSQLのことを言うらしい.EXEC SQL INSERT INTO hoge VALUES (:i, ‘aaa’);上の埋め込みSQLは変数:iを使用しており,コーディング次第でその値は実行時に動的に変更することができる.ということはプログラム実行段階でSQLが確定することになり,動的SQLになるような気がする.だがこういうものは動的SQLと呼ばないらしい.上の埋め込みSQLをecpgで処理した結果は以下のようになる.{ ECPGdo(LINE, 0, 1, NULL, “insert into hoge values( ? , … More 動的SQL

シーケンスとシリアル型

シーケンスという連番自動生成機能とシーケンスによるシリアル型について.シーケンスの作成と削除シーケンス操作関数シリアル型 シーケンスの作成と削除 シーケンスを作成するには,CREATE SEQUENCEを実行する(※).firstdb=> \h CREATE SEQUENCECommand: CREATE SEQUENCEDescription: define a new sequence generatorSyntax:CREATE [ TEMPORARY | TEMP ] SEQUENCE … More シーケンスとシリアル型