[pgcluster: 26] regress test (constraint s.sql)の結果

[kokamoto ＠ itg.hitachi.co.jp]

--GMAILSMTPBOUND01040109164834
Content-Type: text/plain; charset=ISO-2022-JP

岡本＠日立です。お世話になっております。

regress test の constraintsd.sql の結果です。
現象：テーブルにシーケンスを定義してある場合に制約条件があると
シーケンスが正しく更新されません。

regression=# CREATE TABLE CHECK2_TBL (x int, y text, z int,
regression(#         CONSTRAINT SEQUENCE_CON
regression(#         CHECK (x > 3 and y <> 'check failed' and z < 8));

この間にいくつかの操作をしてnextval でシーケンスの更新をしていますが、
制約条件zの境界条件を満たしたときに登録できない条件になった場合に、
シーケンスが更新されません。

regression=# SELECT 'seven' AS one, nextval('insert_seq');
  one  | nextval
-------+---------
 seven |       7
(1 row)

regression=# INSERT INTO INSERT_TBL(y) VALUES ('Y');
ERROR:  ExecInsert: rejected due to CHECK constraint "insert_con" on "insert_tbl"
regression=# SELECT 'eight' AS one, currval('insert_seq');
  one  | currval
-------+---------
 eight |       7
(1 row)

ここでシーケンスの現在値は８になっているはずです。

-- 
岡本　一幸
（株）日立製作所
プラットフォームシステムソリューション本部（PSS本）
共通技術プラットフォームＳＩ部（共通PSI）Ｌｉｎｕｘビジネス推進センタ(LxG)


--GMAILSMTPBOUND01040109164834
Content-Type: application/octet-stream; 
	name="oss242_constraints_step.txt"
Content-Disposition: attachment; 
	filename="oss242_constraints_step.txt"
Content-Transfer-Encoding: base64

W3Bvc3RncmVzQG9zczI0MiBwZ3NxbF0kIHBzcWwgLWggbG9jYWxob3N0IHJlZ3Jlc3Npb24N
CldlbGNvbWUgdG8gcHNxbCA3LjMuNC1SSCwgdGhlIFBvc3RncmVTUUwgaW50ZXJhY3RpdmUg
dGVybWluYWwuDQoNClR5cGU6ICBcY29weXJpZ2h0IGZvciBkaXN0cmlidXRpb24gdGVybXMN
CiAgICAgICBcaCBmb3IgaGVscCB3aXRoIFNRTCBjb21tYW5kcw0KICAgICAgIFw/IGZvciBo
ZWxwIG9uIGludGVybmFsIHNsYXNoIGNvbW1hbmRzDQogICAgICAgXGcgb3IgdGVybWluYXRl
IHdpdGggc2VtaWNvbG9uIHRvIGV4ZWN1dGUgcXVlcnkNCiAgICAgICBccSB0byBxdWl0DQoN
CnJlZ3Jlc3Npb249IyBDUkVBVEUgVEFCTEUgREVGQVVMVF9UQkwgKGkgaW50IERFRkFVTFQg
MTAwLA0KcmVncmVzc2lvbigjICAgICAgICAgeCB0ZXh0IERFRkFVTFQgJ3ZhZGltJywgZiBm
bG9hdDggREVGQVVMVCAxMjMuNDU2KTsNCkNSRUFURSBUQUJMRQ0KcmVncmVzc2lvbj0jDQpy
ZWdyZXNzaW9uPSMgSU5TRVJUIElOVE8gREVGQVVMVF9UQkwgVkFMVUVTICgxLCAndGhvbWFz
JywgNTcuMDYxMyk7DQpJTlNFUlQgMTcwNTkgMQ0KcmVncmVzc2lvbj0jIElOU0VSVCBJTlRP
IERFRkFVTFRfVEJMIFZBTFVFUyAoMSwgJ2JydWNlJyk7DQpJTlNFUlQgMTcwNjAgMQ0KcmVn
cmVzc2lvbj0jIElOU0VSVCBJTlRPIERFRkFVTFRfVEJMIChpLCBmKSBWQUxVRVMgKDIsIDk4
Ny42NTQpOw0KSU5TRVJUIDE3MDYxIDENCnJlZ3Jlc3Npb249IyBJTlNFUlQgSU5UTyBERUZB
VUxUX1RCTCAoeCkgVkFMVUVTICgnbWFyYycpOw0KSU5TRVJUIDE3MDYyIDENCnJlZ3Jlc3Np
b249IyBJTlNFUlQgSU5UTyBERUZBVUxUX1RCTCBWQUxVRVMgKDMsIG51bGwsIDEuMCk7DQpJ
TlNFUlQgMTcwNjMgMQ0KcmVncmVzc2lvbj0jIFNFTEVDVCAnJyBBUyBmaXZlLCAqIEZST00g
REVGQVVMVF9UQkw7DQoNCiBmaXZlIHwgIGkgIHwgICB4ICAgIHwgICAgZg0KLS0tLS0tKy0t
LS0tKy0tLS0tLS0tKy0tLS0tLS0tLQ0KICAgICAgfCAgIDEgfCB0aG9tYXMgfCA1Ny4wNjEz
DQogICAgICB8ICAgMSB8IGJydWNlICB8IDEyMy40NTYNCiAgICAgIHwgICAyIHwgdmFkaW0g
IHwgOTg3LjY1NA0KICAgICAgfCAxMDAgfCBtYXJjICAgfCAxMjMuNDU2DQogICAgICB8ICAg
MyB8ICAgICAgICB8ICAgICAgIDENCig1IHJvd3MpDQoNCkNSRUFURSBTRVFVRU5DRSBERUZB
VUxUX1NFUTsNCnJlZ3Jlc3Npb249Iw0KcmVncmVzc2lvbj0jIENSRUFURSBTRVFVRU5DRSBE
RUZBVUxUX1NFUTsNCkNSRUFURSBTRVFVRU5DRQ0KcmVncmVzc2lvbj0jDQpyZWdyZXNzaW9u
PSMgQ1JFQVRFIFRBQkxFIERFRkFVTFRFWFBSX1RCTCAoaTEgaW50IERFRkFVTFQgMTAwICsg
KDIwMC0xOTkpICogMiwNCnJlZ3Jlc3Npb24oIyAgICAgICAgIGkyIGludCBERUZBVUxUIG5l
eHR2YWwoJ2RlZmF1bHRfc2VxJykpOw0KQ1JFQVRFIFRBQkxFDQpyZWdyZXNzaW9uPSMNCnJl
Z3Jlc3Npb249IyBJTlNFUlQgSU5UTyBERUZBVUxURVhQUl9UQkwgVkFMVUVTICgtMSwgLTIp
Ow0KSU5TRVJUIDE3MDcwIDENCnJlZ3Jlc3Npb249IyBJTlNFUlQgSU5UTyBERUZBVUxURVhQ
Ul9UQkwgKGkxKSBWQUxVRVMgKC0zKTsNCklOU0VSVCAxNzA3MSAxDQpyZWdyZXNzaW9uPSMg
SU5TRVJUIElOVE8gREVGQVVMVEVYUFJfVEJMIChpMikgVkFMVUVTICgtNCk7DQpJTlNFUlQg
MTcwNzIgMQ0KcmVncmVzc2lvbj0jIElOU0VSVCBJTlRPIERFRkFVTFRFWFBSX1RCTCAoaTIp
IFZBTFVFUyAoTlVMTCk7DQpJTlNFUlQgMTcwNzMgMQ0KcmVncmVzc2lvbj0jDQpyZWdyZXNz
aW9uPSMgU0VMRUNUICcnIEFTIGZvdXIsICogRlJPTSBERUZBVUxURVhQUl9UQkw7DQogZm91
ciB8IGkxICB8IGkyDQotLS0tLS0rLS0tLS0rLS0tLQ0KICAgICAgfCAgLTEgfCAtMg0KICAg
ICAgfCAgLTMgfCAgMQ0KICAgICAgfCAxMDIgfCAtNA0KICAgICAgfCAxMDIgfA0KKDQgcm93
cykNCg0KcmVncmVzc2lvbj0jIENSRUFURSBUQUJMRSBlcnJvcl90YmwgKGkgaW50IERFRkFV
TFQgKDEwMCwgKSk7DQpFUlJPUjogIHBhcnNlcjogcGFyc2UgZXJyb3IgYXQgb3IgbmVhciAi
LCIgYXQgY2hhcmFjdGVyIDQzDQpyZWdyZXNzaW9uPSMgQ1JFQVRFIFRBQkxFIGVycm9yX3Ri
bCAoYjEgYm9vbCBERUZBVUxUIDEgSU4gKDEsIDIpKTsNCkVSUk9SOiAgcGFyc2VyOiBwYXJz
ZSBlcnJvciBhdCBvciBuZWFyICJJTiIgYXQgY2hhcmFjdGVyIDQzDQpyZWdyZXNzaW9uPSMg
Q1JFQVRFIFRBQkxFIGVycm9yX3RibCAoYjEgYm9vbCBERUZBVUxUICgxIElOICgxLCAyKSkp
Ow0KQ1JFQVRFIFRBQkxFDQpyZWdyZXNzaW9uPSMgRFJPUCBUQUJMRSBlcnJvcl90Ymw7DQpE
Uk9QIFRBQkxFDQpyZWdyZXNzaW9uPSMgQ1JFQVRFIFRBQkxFIENIRUNLX1RCTCAoeCBpbnQs
DQpyZWdyZXNzaW9uKCMgICAgICAgICBDT05TVFJBSU5UIENIRUNLX0NPTiBDSEVDSyAoeCA+
IDMpKTsNCkNSRUFURSBUQUJMRQ0KcmVncmVzc2lvbj0jIElOU0VSVCBJTlRPIENIRUNLX1RC
TCBWQUxVRVMgKDUpOw0KSU5TRVJUIDE3MDgwIDENCnJlZ3Jlc3Npb249IyBJTlNFUlQgSU5U
TyBDSEVDS19UQkwgVkFMVUVTICg0KTsNCklOU0VSVCAxNzA4MSAxDQpyZWdyZXNzaW9uPSMg
SU5TRVJUIElOVE8gQ0hFQ0tfVEJMIFZBTFVFUyAoMyk7DQpFUlJPUjogIEV4ZWNJbnNlcnQ6
IHJlamVjdGVkIGR1ZSB0byBDSEVDSyBjb25zdHJhaW50ICJjaGVja19jb24iIG9uICJjaGVj
a190YmwiDQpyZWdyZXNzaW9uPSMgSU5TRVJUIElOVE8gQ0hFQ0tfVEJMIFZBTFVFUyAoMik7
DQpFUlJPUjogIEV4ZWNJbnNlcnQ6IHJlamVjdGVkIGR1ZSB0byBDSEVDSyBjb25zdHJhaW50
ICJjaGVja19jb24iIG9uICJjaGVja190YmwiDQpyZWdyZXNzaW9uPSMgSU5TRVJUIElOVE8g
Q0hFQ0tfVEJMIFZBTFVFUyAoNik7DQpJTlNFUlQgMTcwODIgMQ0KcmVncmVzc2lvbj0jIElO
U0VSVCBJTlRPIENIRUNLX1RCTCBWQUxVRVMgKDEpOw0KRVJST1I6ICBFeGVjSW5zZXJ0OiBy
ZWplY3RlZCBkdWUgdG8gQ0hFQ0sgY29uc3RyYWludCAiY2hlY2tfY29uIiBvbiAiY2hlY2tf
dGJsIg0KcmVncmVzc2lvbj0jIFNFTEVDVCAnJyBBUyB0aHJlZSwgKiBGUk9NIENIRUNLX1RC
TDsNCiB0aHJlZSB8IHgNCi0tLS0tLS0rLS0tDQogICAgICAgfCA1DQogICAgICAgfCA0DQog
ICAgICAgfCA2DQooMyByb3dzKQ0KDQpyZWdyZXNzaW9uPSMgQ1JFQVRFIFNFUVVFTkNFIENI
RUNLX1NFUTsNCkNSRUFURSBTRVFVRU5DRQ0KcmVncmVzc2lvbj0jDQpyZWdyZXNzaW9uPSMg
Q1JFQVRFIFRBQkxFIENIRUNLMl9UQkwgKHggaW50LCB5IHRleHQsIHogaW50LA0KcmVncmVz
c2lvbigjICAgICAgICAgQ09OU1RSQUlOVCBTRVFVRU5DRV9DT04NCnJlZ3Jlc3Npb24oIyAg
ICAgICAgIENIRUNLICh4ID4gMyBhbmQgeSA8PiAnY2hlY2sgZmFpbGVkJyBhbmQgeiA8IDgp
KTsNCkNSRUFURSBUQUJMRQ0KcmVncmVzc2lvbj0jIElOU0VSVCBJTlRPIENIRUNLMl9UQkwg
VkFMVUVTICg0LCAnY2hlY2sgb2snLCAtMik7DQpJTlNFUlQgMTcwOTEgMQ0KcmVncmVzc2lv
bj0jIElOU0VSVCBJTlRPIENIRUNLMl9UQkwgVkFMVUVTICgxLCAneCBjaGVjayBmYWlsZWQn
LCAtMik7DQpFUlJPUjogIEV4ZWNJbnNlcnQ6IHJlamVjdGVkIGR1ZSB0byBDSEVDSyBjb25z
dHJhaW50ICJzZXF1ZW5jZV9jb24iIG9uICJjaGVjazJfdGJsIg0KcmVncmVzc2lvbj0jIElO
U0VSVCBJTlRPIENIRUNLMl9UQkwgVkFMVUVTICg1LCAneiBjaGVjayBmYWlsZWQnLCAxMCk7
DQpFUlJPUjogIEV4ZWNJbnNlcnQ6IHJlamVjdGVkIGR1ZSB0byBDSEVDSyBjb25zdHJhaW50
ICJzZXF1ZW5jZV9jb24iIG9uICJjaGVjazJfdGJsIg0KcmVncmVzc2lvbj0jIElOU0VSVCBJ
TlRPIENIRUNLMl9UQkwgVkFMVUVTICgwLCAnY2hlY2sgZmFpbGVkJywgLTIpOw0KRVJST1I6
ICBFeGVjSW5zZXJ0OiByZWplY3RlZCBkdWUgdG8gQ0hFQ0sgY29uc3RyYWludCAic2VxdWVu
Y2VfY29uIiBvbiAiY2hlY2syX3RibCINCnJlZ3Jlc3Npb249IyBJTlNFUlQgSU5UTyBDSEVD
SzJfVEJMIFZBTFVFUyAoNiwgJ2NoZWNrIGZhaWxlZCcsIDExKTsNCkVSUk9SOiAgRXhlY0lu
c2VydDogcmVqZWN0ZWQgZHVlIHRvIENIRUNLIGNvbnN0cmFpbnQgInNlcXVlbmNlX2NvbiIg
b24gImNoZWNrMl90YmwiDQpyZWdyZXNzaW9uPSMgSU5TRVJUIElOVE8gQ0hFQ0syX1RCTCBW
QUxVRVMgKDcsICdjaGVjayBvaycsIDcpOw0KSU5TRVJUIDE3MDkyIDENCnJlZ3Jlc3Npb249
IyBTRUxFQ1QgJycgQVMgdHdvLCAqIGZyb20gQ0hFQ0syX1RCTDsNCiB0d28gfCB4IHwgICAg
eSAgICAgfCB6DQotLS0tLSstLS0rLS0tLS0tLS0tLSstLS0tDQogICAgIHwgNCB8IGNoZWNr
IG9rIHwgLTINCiAgICAgfCA3IHwgY2hlY2sgb2sgfCAgNw0KKDIgcm93cykNCg0KcmVncmVz
c2lvbj0jIENSRUFURSBTRVFVRU5DRSBJTlNFUlRfU0VROw0KQ1JFQVRFIFNFUVVFTkNFDQpy
ZWdyZXNzaW9uPSMgQ1JFQVRFIFRBQkxFIElOU0VSVF9UQkwgKHggSU5UIERFRkFVTFQgbmV4
dHZhbCgnaW5zZXJ0X3NlcScpLA0KcmVncmVzc2lvbigjICAgICAgICAgeSBURVhUIERFRkFV
TFQgJy1OVUxMLScsDQpyZWdyZXNzaW9uKCMgICAgICAgICB6IElOVCBERUZBVUxUIC0xICog
Y3VycnZhbCgnaW5zZXJ0X3NlcScpLA0KcmVncmVzc2lvbigjICAgICAgICAgQ09OU1RSQUlO
VCBJTlNFUlRfQ09OIENIRUNLICh4ID49IDMgQU5EIHkgPD4gJ2NoZWNrIGZhaWxlZCcgQU5E
IHggPCA4KSwNCnJlZ3Jlc3Npb24oIyAgICAgICAgIENIRUNLICh4ICsgeiA9IDApKTsNCkNS
RUFURSBUQUJMRQ0KcmVncmVzc2lvbj0jIElOU0VSVCBJTlRPIElOU0VSVF9UQkwoeCx6KSBW
QUxVRVMgKDIsIC0yKTsNCkVSUk9SOiAgRXhlY0luc2VydDogcmVqZWN0ZWQgZHVlIHRvIENI
RUNLIGNvbnN0cmFpbnQgImluc2VydF9jb24iIG9uICJpbnNlcnRfdGJsIg0KcmVncmVzc2lv
bj0jIFNFTEVDVCAnb25lJyBBUyBvbmUsIG5leHR2YWwoJ2luc2VydF9zZXEnKTsNCiBvbmUg
fCBuZXh0dmFsDQotLS0tLSstLS0tLS0tLS0NCiBvbmUgfCAgICAgICAxDQooMSByb3cpDQoN
CnJlZ3Jlc3Npb249IyBJTlNFUlQgSU5UTyBJTlNFUlRfVEJMKHkpIFZBTFVFUyAoJ1knKTsN
CkVSUk9SOiAgRXhlY0luc2VydDogcmVqZWN0ZWQgZHVlIHRvIENIRUNLIGNvbnN0cmFpbnQg
Imluc2VydF9jb24iIG9uICJpbnNlcnRfdGJsIg0KcmVncmVzc2lvbj0jIElOU0VSVCBJTlRP
IElOU0VSVF9UQkwoeSkgVkFMVUVTICgnWScpOw0KSU5TRVJUIDE3MTA1IDENCnJlZ3Jlc3Np
b249IyBJTlNFUlQgSU5UTyBJTlNFUlRfVEJMKHgseikgVkFMVUVTICgxLCAtMik7DQpFUlJP
UjogIEV4ZWNJbnNlcnQ6IHJlamVjdGVkIGR1ZSB0byBDSEVDSyBjb25zdHJhaW50ICIkMSIg
b24gImluc2VydF90YmwiDQpyZWdyZXNzaW9uPSMgSU5TRVJUIElOVE8gSU5TRVJUX1RCTCh6
LHgpIFZBTFVFUyAoLTcsICA3KTsNCklOU0VSVCAxNzEwNiAxDQpyZWdyZXNzaW9uPSMgSU5T
RVJUIElOVE8gSU5TRVJUX1RCTCBWQUxVRVMgKDUsICdjaGVjayBmYWlsZWQnLCAtNSk7DQpF
UlJPUjogIEV4ZWNJbnNlcnQ6IHJlamVjdGVkIGR1ZSB0byBDSEVDSyBjb25zdHJhaW50ICJp
bnNlcnRfY29uIiBvbiAiaW5zZXJ0X3RibCINCnJlZ3Jlc3Npb249IyBJTlNFUlQgSU5UTyBJ
TlNFUlRfVEJMIFZBTFVFUyAoNywgJyFjaGVjayBmYWlsZWQnLCAtNyk7DQpJTlNFUlQgMTcx
MDcgMQ0KcmVncmVzc2lvbj0jIElOU0VSVCBJTlRPIElOU0VSVF9UQkwoeSkgVkFMVUVTICgn
LSFOVUxMLScpOw0KSU5TRVJUIDE3MTA4IDENCnJlZ3Jlc3Npb249IyBTRUxFQ1QgJycgQVMg
Zm91ciwgKiBGUk9NIElOU0VSVF9UQkw7DQogZm91ciB8IHggfCAgICAgICB5ICAgICAgIHwg
eg0KLS0tLS0tKy0tLSstLS0tLS0tLS0tLS0tLS0rLS0tLQ0KICAgICAgfCAzIHwgWSAgICAg
ICAgICAgICB8IC0zDQogICAgICB8IDcgfCAtTlVMTC0gICAgICAgIHwgLTcNCiAgICAgIHwg
NyB8ICFjaGVjayBmYWlsZWQgfCAtNw0KICAgICAgfCA0IHwgLSFOVUxMLSAgICAgICB8IC00
DQooNCByb3dzKQ0KDQpyZWdyZXNzaW9uPSMgSU5TRVJUIElOVE8gSU5TRVJUX1RCTCh5LHop
IFZBTFVFUyAoJ2NoZWNrIGZhaWxlZCcsIDQpOw0KRVJST1I6ICBFeGVjSW5zZXJ0OiByZWpl
Y3RlZCBkdWUgdG8gQ0hFQ0sgY29uc3RyYWludCAiJDEiIG9uICJpbnNlcnRfdGJsIg0KcmVn
cmVzc2lvbj0jIElOU0VSVCBJTlRPIElOU0VSVF9UQkwoeCx5KSBWQUxVRVMgKDUsICdjaGVj
ayBmYWlsZWQnKTsNCkVSUk9SOiAgRXhlY0luc2VydDogcmVqZWN0ZWQgZHVlIHRvIENIRUNL
IGNvbnN0cmFpbnQgImluc2VydF9jb24iIG9uICJpbnNlcnRfdGJsIg0KcmVncmVzc2lvbj0j
IElOU0VSVCBJTlRPIElOU0VSVF9UQkwoeCx5KSBWQUxVRVMgKDUsICchY2hlY2sgZmFpbGVk
Jyk7DQpJTlNFUlQgMTcxMDkgMQ0KcmVncmVzc2lvbj0jIElOU0VSVCBJTlRPIElOU0VSVF9U
QkwoeSkgVkFMVUVTICgnLSFOVUxMLScpOw0KSU5TRVJUIDE3MTEwIDENCnJlZ3Jlc3Npb249
IyBTRUxFQ1QgJycgQVMgc2l4LCAqIEZST00gSU5TRVJUX1RCTDsNCiBzaXggfCB4IHwgICAg
ICAgeSAgICAgICB8IHoNCi0tLS0tKy0tLSstLS0tLS0tLS0tLS0tLS0rLS0tLQ0KICAgICB8
IDMgfCBZICAgICAgICAgICAgIHwgLTMNCiAgICAgfCA3IHwgLU5VTEwtICAgICAgICB8IC03
DQogICAgIHwgNyB8ICFjaGVjayBmYWlsZWQgfCAtNw0KICAgICB8IDQgfCAtIU5VTEwtICAg
ICAgIHwgLTQNCiAgICAgfCA1IHwgIWNoZWNrIGZhaWxlZCB8IC01DQogICAgIHwgNiB8IC0h
TlVMTC0gICAgICAgfCAtNg0KKDYgcm93cykNCg0KcmVncmVzc2lvbj0jIFNFTEVDVCAnc2V2
ZW4nIEFTIG9uZSwgbmV4dHZhbCgnaW5zZXJ0X3NlcScpOw0KICBvbmUgIHwgbmV4dHZhbA0K
LS0tLS0tLSstLS0tLS0tLS0NCiBzZXZlbiB8ICAgICAgIDcNCigxIHJvdykNCg0KcmVncmVz
c2lvbj0jIElOU0VSVCBJTlRPIElOU0VSVF9UQkwoeSkgVkFMVUVTICgnWScpOw0KRVJST1I6
ICBFeGVjSW5zZXJ0OiByZWplY3RlZCBkdWUgdG8gQ0hFQ0sgY29uc3RyYWludCAiaW5zZXJ0
X2NvbiIgb24gImluc2VydF90YmwiDQpyZWdyZXNzaW9uPSMgU0VMRUNUICdlaWdodCcgQVMg
b25lLCBjdXJydmFsKCdpbnNlcnRfc2VxJyk7DQogIG9uZSAgfCBjdXJydmFsDQotLS0tLS0t
Ky0tLS0tLS0tLQ0KIGVpZ2h0IHwgICAgICAgNw0KKDEgcm93KQ0K

--GMAILSMTPBOUND01040109164834--