Thread: SET NULL / SET NOT NULL

SET NULL / SET NOT NULL

From
"Christopher Kings-Lynne"
Date:
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



Re: SET NULL / SET NOT NULL

From
Peter Eisentraut
Date:
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



Re: SET NULL / SET NOT NULL

From
"Christopher Kings-Lynne"
Date:
> > 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



Re: SET NULL / SET NOT NULL

From
"Christopher Kings-Lynne"
Date:
> 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



Re: SET NULL / SET NOT NULL

From
"Marc G. Fournier"
Date:
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
>



Re: SET NULL / SET NOT NULL

From
Tom Lane
Date:
"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


Re: SET NULL / SET NOT NULL

From
"Christopher Kings-Lynne"
Date:
> 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



Re: SET NULL / SET NOT NULL

From
"Zeugswetter Andreas SB SD"
Date:
> > 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


Re: SET NULL / SET NOT NULL

From
Vince Vielhaber
Date:
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
==========================================================================





Re: SET NULL / SET NOT NULL

From
Tom Lane
Date:
"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


Re: SET NULL / SET NOT NULL

From
Bruno Wolff III
Date:
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.)