[BUGS] Problem with domains

#1

Sent for user who is having trouble posting to bugs list:

---------------------------------------------------------------------------

Quote:

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 (AT) postgresql (DOT) 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 (AT) candle (DOT) 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 (AT) candle (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#2

On Wed, 24 Sep 2003, Bruce Momjian wrote:

Quote:

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>

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#3

--Multipart_Wed__24_Sep_2003_22_54_26_-0600_=.QCXjZTRA3b'Rvj
Content-Type: text/plain; charset=US-ASCII
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

Once upon a time (Wed, 24 Sep 2003 21:10:53 -0700 (PDT))
Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com> uttered something amazingly similar
to:

Quote:

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?

Quote:

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

--Multipart_Wed__24_Sep_2003_22_54_26_-0600_=.QCXjZTRA3b'Rvj
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iEYEARECAAYFAj9ydQIACgkQgy51bQc2FFnHdwCeKiy+WcksYI JVmnZJ5nESoapB
J94AoMWVky10GIsfC5EZZ3RA9vfoFLqH
=jrxx
-----END PGP SIGNATURE-----

--Multipart_Wed__24_Sep_2003_22_54_26_-0600_=.QCXjZTRA3b'Rvj--

#4

Robert Creager <Robert_Creager (AT) LogicalChaos (DOT) org> writes:

Quote:

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?

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#5

--Multipart_Thu__25_Sep_2003_07_23_42_-0600_=.djDbEzij1KMqpP
Content-Type: text/plain; charset=US-ASCII
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

Once upon a time (Thu, 25 Sep 2003 01:28:29 -0400)
Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> uttered something amazingly similar to:

Quote:

Robert Creager <Robert_Creager (AT) LogicalChaos (DOT) 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.

Quote:

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

--Multipart_Thu__25_Sep_2003_07_23_42_-0600_=.djDbEzij1KMqpP
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iEYEARECAAYFAj9y7GQACgkQgy51bQc2FFlQBgCg3Bb3yGZVek I08O9etmg0DAMY
E3cAoORMotli6UW/j5bVfz1fhUXoSAPT
=+3Bp
-----END PGP SIGNATURE-----

--Multipart_Thu__25_Sep_2003_07_23_42_-0600_=.djDbEzij1KMqpP--

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[BUGS] Problem with domains

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

[BUGS] Problem with domains - 09-24-2003 , 10:33 PM

Re: [BUGS] Problem with domains - 09-24-2003 , 11:13 PM

Re: [BUGS] Problem with domains - 09-25-2003 , 12:16 AM

Re: [BUGS] Problem with domains - 09-25-2003 , 12:38 AM

Re: [BUGS] Problem with domains - 09-25-2003 , 08:37 AM