Thread: Problem with domains
Sent for user who is having trouble posting to bugs list: --------------------------------------------------------------------------- > > Robert Creager wrote: > > > Sorry to send this to you directly, but I seem to be having a problem > > > posting to bugs, and I received no response from either Mark F. nor > > > majordomo-owner. > > > > > > I've sent the following mail twice to postgresql-bugs@postgresql.org, and > > > both times, it's neither showed up nor have I received any mail indicating > > > it's been held for moderation, and I am subscribed to the list. > > > > > > Thoughts? > > > Rob > > > > > > The problem: > > > > > > Using 7.4b2 from tar file, not cvs. > > > > > > PostgreSQL 7.4beta2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 > > > (Mandrake Linux 9.1 3.2.2-3mdk) > > > > > > While figuring out how to use DOMAIN's, I ran across this: > > > > > > begin; > > > create domain test as integer constraint check( value > 0 ); > > > -- ERROR: syntax error at or near "check" at character 42 > > > create domain test as integer check( value > 0 ); > > > -- ERROR: current transaction is aborted, queries ignored until end of > > > transaction block > > > > > > Forgot to name the constraint in the first form. The second form works fine > > > on it's own. > > > > > > Cheers, > > > Rob > > > > > > -- > > > 17:37:47 up 44 days, 10:13, 4 users, load average: 2.13, 2.04, 2.04 > > > > > > > > > > > -- End of PGP section, PGP failed! > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 359-1001 > > + If your life is a hard drive, | 13 Roberts Road > > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > > > > -- > 21:08:35 up 54 days, 13:40, 4 users, load average: 2.07, 2.03, 1.96 -- End of PGP section, PGP failed! -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Wed, 24 Sep 2003, Bruce Momjian wrote: > Sent for user who is having trouble posting to bugs list: Seems like the correct behavior to me. When CONSTRAINT is given, the name is not optional AFAICS. From SQL99: <domain definition> ::= CREATE DOMAIN <domain name> [ AS ] <data type> [ <default clause> ] [ <domain constraint>... ] [ <collate clause> ] <domain constraint> ::= [ <constraint name definition> ] <check constraint definition> [ <constraint characteristics> ] <constraint name definition> ::= CONSTRAINT <constraint name> <check constraint definition> ::= CHECK <left paren> <search condition> <right paren> <constraint name> ::= <schema qualified name> > > > > Using 7.4b2 from tar file, not cvs. > > > > > > > > PostgreSQL 7.4beta2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 > > > > (Mandrake Linux 9.1 3.2.2-3mdk) > > > > > > > > While figuring out how to use DOMAIN's, I ran across this: > > > > > > > > begin; > > > > create domain test as integer constraint check( value > 0 ); > > > > -- ERROR: syntax error at or near "check" at character 42 > > > > create domain test as integer check( value > 0 ); > > > > -- ERROR: current transaction is aborted, queries ignored until end of > > > > transaction block > > > > > > > > Forgot to name the constraint in the first form. The second form works fine > > > > on it's own.
Once upon a time (Wed, 24 Sep 2003 21:10:53 -0700 (PDT)) Stephan Szabo <sszabo@megazone.bigpanda.com> uttered something amazingly similar to: > > On Wed, 24 Sep 2003, Bruce Momjian wrote: > > > Sent for user who is having trouble posting to bugs list: > > Seems like the correct behavior to me. When CONSTRAINT is given, the > name is not optional AFAICS. True, but the second CREATE DOMAIN, which is a valid form, fails when preceded by the invalid form. This was the error I was referring to. Not the fact that I didn't know how to use domains ;-) If the transaction is bad after the first invalid CREATE DOMAIN, the error should say so, rather than waiting for a second valid statement to come along, shouldn't it? > > > > > begin; > > > > > create domain test as integer constraint check( value > 0 ); > > > > > -- ERROR: syntax error at or near "check" at character 42 > > > > > create domain test as integer check( value > 0 ); > > > > > -- ERROR: current transaction is aborted, queries ignored until end > > > > > of transaction block > > > > > Cheers, Rob -- 22:50:13 up 54 days, 15:22, 5 users, load average: 2.36, 2.37, 2.17
On Wed, 24 Sep 2003, Robert Creager wrote: > Once upon a time (Wed, 24 Sep 2003 21:10:53 -0700 (PDT)) > Stephan Szabo <sszabo@megazone.bigpanda.com> uttered something amazingly similar > to: > > > > > On Wed, 24 Sep 2003, Bruce Momjian wrote: > > > > > Sent for user who is having trouble posting to bugs list: > > > > Seems like the correct behavior to me. When CONSTRAINT is given, the > > name is not optional AFAICS. > > True, but the second CREATE DOMAIN, which is a valid form, fails when preceded > by the invalid form. This was the error I was referring to. Not the fact that > I didn't know how to use domains ;-) If the transaction is bad after the first > invalid CREATE DOMAIN, the error should say so, rather than waiting for a > second valid statement to come along, shouldn't it? All errors are considered unrecoverable ones by PostgreSQL so after any error the transaction is in an effectively unusable state and should be rolled back. > > > > > > begin; > > > > > > create domain test as integer constraint check( value > 0 ); > > > > > > -- ERROR: syntax error at or near "check" at character 42 > > > > > > create domain test as integer check( value > 0 ); > > > > > > -- ERROR: current transaction is aborted, queries ignored until end > > > > > > of transaction block > > > > > >
Robert Creager <Robert_Creager@LogicalChaos.org> writes:
>> Seems like the correct behavior to me. When CONSTRAINT is given, the
>> name is not optional AFAICS.
> True, but the second CREATE DOMAIN, which is a valid form, fails when
> preceded by the invalid form. This was the error I was referring to.
> Not the fact that I didn't know how to use domains ;-) If the
> transaction is bad after the first invalid CREATE DOMAIN, the error
> should say so, rather than waiting for a second valid statement to
> come along, shouldn't it?
Uh, which part of "queries ignored until end of transaction block"
didn't you understand?
> begin;
> create domain test as integer constraint check( value > 0 );
> -- ERROR: syntax error at or near "check" at character 42
> create domain test as integer check( value > 0 );
> -- ERROR: current transaction is aborted, queries ignored until end
> of transaction block
regards, tom lane
Once upon a time (Thu, 25 Sep 2003 01:28:29 -0400) Tom Lane <tgl@sss.pgh.pa.us> uttered something amazingly similar to: > Robert Creager <Robert_Creager@LogicalChaos.org> writes: > >> Seems like the correct behavior to me. When CONSTRAINT is given, the > >> name is not optional AFAICS. > > > True, but the second CREATE DOMAIN, which is a valid form, fails when > > preceded by the invalid form. This was the error I was referring to. > > Not the fact that I didn't know how to use domains ;-) If the > > transaction is bad after the first invalid CREATE DOMAIN, the error > > should say so, rather than waiting for a second valid statement to > > come along, shouldn't it? > > Uh, which part of "queries ignored until end of transaction block" > didn't you understand? I understand every word of it. Why doesn't that error come up after the first CREATE DOMAIN error, since I was in a transaction. Then it would of been perfectly clear that the transaction I was in needed to be rolled back. Rather that error doesn't occur until the second valid CREATE DOMAIN is executed. I was not aware of what Stephan indicated: "All errors are considered unrecoverable ones by PostgreSQL so after any error the transaction is in an effectively unusable state and should be rolled back". I know better now. It still seams that the "queries ignored until end of transaction block" should of been thrown after the first invalid CREATE DOMAIN. That's all. > > > begin; > > create domain test as integer constraint check( value > 0 ); > > -- ERROR: syntax error at or near "check" at character 42 > > create domain test as integer check( value > 0 ); > > -- ERROR: current transaction is aborted, queries ignored until end > > of transaction block Cheers, Rob -- 07:16:42 up 54 days, 23:48, 5 users, load average: 2.17, 2.06, 2.01