Thread: SET NULL / SET NOT NULL
Hi all, This is the email I sent earlier - I think it was lost in the 7.2 release hammering the servers got. I'm hoping to implement SET NULL / SET NOT NULL for 7.3 I've been searching the SQL99 docs but I still can't figure out the syntax for it. (BTW, can anyone give me SQL92?) So I guess there are two possibilties - which is correct?: ALTER TABLE blah ADD CONSTRAINT "asfd" NOT NULL (field); ALTER TABLE blah DROP CONSRAINT "asdf"; or ALTER TABLE blah ALTER COLUMN field SET [ NOT NULL | NULL ]; My question is - does the parser already support the syntax? ie. Is there an empty function somewhere that says 'not implemented', or do I have to actually add the flex code for it? If it is there - where is it? Chris
Christopher Kings-Lynne writes: > I'm hoping to implement SET NULL / SET NOT NULL for 7.3 > > I've been searching the SQL99 docs but I still can't figure out the syntax > for it. There isn't really a syntax for it. SQL only allows you to add table constraints, not column constraints. A NOT NULL constraint is a shorthand notation for a CHECK constraint, so to add a NOT NULL constraint you'd have to recognize CHECK constraints of the form CHECK (col IS NOT NULL) and handle them specially. To drop NOT NULL constraints, you'd use the regular ALTER TABLE blah DROP CONSTRAINT foo; where foo is the name of the NOT NULL constraint. The drawback is that NOT NULL constraints currently don't have a name stored. -- Peter Eisentraut peter_e@gmx.net
> > ALTER TABLE blah ALTER COLUMN field SET [ NOT NULL | NULL ]; > > > > My question is - does the parser already support the syntax? > ie. Is there > > an empty function somewhere that says 'not implemented', or do I have to > > actually add the flex code for it? > > Did you miss my reply that said to use ALTER TABLE ... ADD NULL | NOT > NULL? I will resend. > > Are we losing emails. I sent one last night that hasn't appeared. I lost a whole bunch of mails with SET NULL / SET NOT NULL in the header - I found that if I put some chars in front of the subject, then it got thru straight away... Chris
> Then why are these emails getting through. They start with SET NULL? Well, no they start with "Re:", that appeared on the list today was actually sent about 2 weeks ago. Chris
this isn't making any sense, unless the qmail installation that Vince has running for relaying is hosed? The queue on postgresql.org itself is near empty, and looking at the full headers for recent messages, delays are quite small except for those postings that have to be approved ... The thing is, I know I saw a thread between you and Tom with a subject of elog ... I'm still looking ... On Tue, 19 Feb 2002, Bruce Momjian wrote: > Christopher Kings-Lynne wrote: > > > Then why are these emails getting through. They start with SET NULL? > > > > Well, no they start with "Re:", that appeared on the list today was actually > > sent about 2 weeks ago. > > This is getting bad. Marc, we need to know what is happening. Tom Lane > says he gets email when something is stalled for approval but I have > never gotten one. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 >
"Marc G. Fournier" <scrappy@hub.org> writes:
> ... looking at the full headers for recent messages, delays are
> quite small except for those postings that have to be approved ...
The real question is, what about notifications back to the posters
that postings have been delayed for approval?
I know that I get a notification back whenever I'm foolish enough
to use words like "c*nc*l" or "s*bscr*be" in PG-list postings.
But Bruce says he doesn't get any such notification, and Christopher
evidently isn't getting any, and we've seen plenty of past complaints
about postings being silently delayed. I think something's pretty
flaky about the your-mail-has-been-held-for-approval notification.
regards, tom lane
> I know that I get a notification back whenever I'm foolish enough > to use words like "c*nc*l" or "s*bscr*be" in PG-list postings. > But Bruce says he doesn't get any such notification, and Christopher > evidently isn't getting any, and we've seen plenty of past complaints > about postings being silently delayed. I think something's pretty > flaky about the your-mail-has-been-held-for-approval notification. Nope - whenever I accidentally post to -general, I get a little message about 'queued for approval'. Chris
> > I'm hoping to implement SET NULL / SET NOT NULL for 7.3 > > > > I've been searching the SQL99 docs but I still can't figure out the syntax > > for it. > > There isn't really a syntax for it. SQL only allows you to add table > constraints, not column constraints. A NOT NULL constraint is a shorthand > notation for a CHECK constraint, so to add a NOT NULL constraint you'd > have to recognize CHECK constraints of the form CHECK (col IS NOT NULL) > and handle them specially. To drop NOT NULL constraints, you'd use the > regular ALTER TABLE blah DROP CONSTRAINT foo; where foo is the name of the > NOT NULL constraint. The drawback is that NOT NULL constraints currently > don't have a name stored. While this would imho really be the intuitive approach, does the feature of dropping a not null constraint really justify storing a name for each and every one ? With the added complexity of automatically generating a name that fits and is guaranteed unique (and prbably predictable) ? I guess I would rather invent syntax that does without a name. On the other hand other db's have such syntax and still generate names for not null constraints, thus allowing both ways of dropping the constraint, so ... Andreas
On Wed, 20 Feb 2002, Marc G. Fournier wrote: > > this isn't making any sense, unless the qmail installation that Vince has > running for relaying is hosed? The queue on postgresql.org itself is near > empty, and looking at the full headers for recent messages, delays are > quite small except for those postings that have to be approved ... Just checked my stuff, it's pretty much idle. There are alot of undeliverable in the queue but those are mainly failed dns lookup and alot of the same addresses over and over. The only messages in the queue with "SET NULL" in them anywhere are the ones from the current discussion. Without seeing a FULL header on these messages there's no way to know what's going on, but qmail will bounce them if they're still undeliverable after a few days (4, I think). Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> ... we've seen plenty of past complaints
>> about postings being silently delayed. I think something's pretty
>> flaky about the your-mail-has-been-held-for-approval notification.
> Nope - whenever I accidentally post to -general, I get a little message
> about 'queued for approval'.
So it works for you some of the time. I still think there's something
flaky ...
regards, tom lane
On Wed, Feb 20, 2002 at 06:31:59AM -0500, Vince Vielhaber <vev@michvhf.com> wrote: > > Just checked my stuff, it's pretty much idle. There are alot of > undeliverable in the queue but those are mainly failed dns lookup > and alot of the same addresses over and over. The only messages in > the queue with "SET NULL" in them anywhere are the ones from the current > discussion. Without seeing a FULL header on these messages there's no > way to know what's going on, but qmail will bounce them if they're still > undeliverable after a few days (4, I think). A while back I had problems because of some bad routing tables. Your old ISP couldn't send traffic to my box. Then after postgresql.org moved, I could reach that server, but it appeared that the old dns cache was still being used and my email was rejected because it couldn't get a records for my domain name. During this period I could send messages using a domain literal instead of my normal domain name. The machine that sends out list mail was also able to use my domain name for the whole time. (I subscribed to a couple of the lists before an IP change moved me to the block with the routing issue.)