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?
RobThe 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 blockForgot 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 19073Import Notes
Reply to msg id not found: 20030924211005.0375e927.Robert_Creager@LogicalChaos.org
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>
Show quoted text
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 blockForgot 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.
Show quoted text
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