Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is it possible to set a NOT NULL constraint deferrable?

Is it possible to set a NOT NULL constraint deferrable?

From
Olivier Hubaut
Date:
I can put all the other constaints deferrable, but the *NOT NULL* one 
seems to be undeferrable.

Is ther a way to by-pass this or is do you know if this is planned in 
the future versions?

-- 
Ci-git une signature avortee.
** RIP **

Index not recognized

From
"Grace C. Unson"
Date:
Hello.

Why is it that my index for text[] data type is not recognized by the
Planner?

I did these steps:

1. create function textarr(text[]) returns text language sql as 'select
$1[1]' strict immutable
2. create index org_idx on EmpData (textarr(org));

3. vacuum full
4. explain analyze select name from EmpData where org *= 'math';

Result:
=========
Seq Scan on EmpData (cost=0.00..3193.20 rows=102 width=488)
(actual time=3.71.35..371.35 rows=0 loops=1)

Filter: (org[0]='math'::text)

Total runtime: 371.47 msec
(3 rows)

==========

I have 20,496 records. My postgres version is 7.3.

Any help is greatly appreciated.

Thank you..

Ace

Re: Is it possible to set a NOT NULL constraint deferrable?

From
Bruce Momjian
Date:
Olivier Hubaut wrote:
> I can put all the other constaints deferrable, but the *NOT NULL* one 
> seems to be undeferrable.
> 
> Is ther a way to by-pass this or is do you know if this is planned in 
> the future versions?

Only foreign key constraints are deferrable.  Many want UNIQUE to be
deferrable, but you are the first to ask for NOT NULL.

Not sure when this will be done.  Sorry.

--  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,
Pennsylvania19073

Re: Index not recognized

From
Greg Stark
Date:
"Grace C. Unson" <gracec@ntsp.nec.co.jp> writes:

> Why is it that my index for text[] data type is not recognized by the
> Planner?
> 
> I did these steps:
> 
> 1. create function textarr(text[]) returns text language sql as 'select
> $1[1]' strict immutable
> 2. create index org_idx on EmpData (textarr(org));

This index will only be used if you use the expression textarr(org) in your
query. You would probably have some success if you did:
select * from empdata where textarr(org) = 'math' 

> 3. vacuum full
> 4. explain analyze select name from EmpData where org *= 'math';

Is this *= operator from the contrib/array directory? It's not an indexable
operator at all using standard btree indexes.

The GiST indexing does make indexable operators that can do things like *= but
that's a whole other ball of wax.

What are you really trying to do?

> Result:
> =========
> Seq Scan on EmpData (cost=0.00..3193.20 rows=102 width=488)
> (actual time=3.71.35..371.35 rows=0 loops=1)
> 
> Filter: (org[0]='math'::text)

Well that's awfully odd. I don't know how that expression came out of the
query you gave. You'll have to give a lot more information about how you're
defining *= and why you think it's related to the function you used to define
the index.

-- 
greg

Re: Index not recognized

From
"Ace"
Date:
Hello Greg, thanks for replying.

I definitely lacked the expression you suggested in my 'SELECT'
statement.

> Is this *= operator from the contrib/array directory? It's not an
indexable
> operator at all using standard btree indexes.

Yes, it is from contrib/array directory.

> The GiST indexing does make indexable operators that can do things like *=
but
> that's a whole other ball of wax.

I tried the btree_gist from contrib/, but I know I missed something because
I got
this error message:
data type text[] has no default operator class for access method "gist". You
must
specify an operator class for the index or define a default operator class
for the
data type.

> What are you really trying to do?
I have tables with attributes whose datatype is TEXT[].
I'm interested to find out the time it will take to finish an array search
with and
without an index.


---
Grace

----- Original Message ----- 
From: "Greg Stark" <gsstark@mit.edu>
To: "Grace C. Unson" <gracec@ntsp.nec.co.jp>
Cc: "PgSQL SQL" <pgsql-sql@postgresql.org>
Sent: Sunday, December 07, 2003 8:36 AM
Subject: Re: [SQL] Index not recognized


>
> "Grace C. Unson" <gracec@ntsp.nec.co.jp> writes:
>
> > Why is it that my index for text[] data type is not recognized by the
> > Planner?
> >
> > I did these steps:
> >
> > 1. create function textarr(text[]) returns text language sql as 'select
> > $1[1]' strict immutable
> > 2. create index org_idx on EmpData (textarr(org));
>
> This index will only be used if you use the expression textarr(org) in
your
> query. You would probably have some success if you did:
>
>  select * from empdata where textarr(org) = 'math'
>
> > 3. vacuum full
> > 4. explain analyze select name from EmpData where org *= 'math';
>
> Is this *= operator from the contrib/array directory? It's not an
indexable
> operator at all using standard btree indexes.
>
> The GiST indexing does make indexable operators that can do things like *=
but
> that's a whole other ball of wax.
>
> What are you really trying to do?
>
> > Result:
> > =========
> > Seq Scan on EmpData (cost=0.00..3193.20 rows=102 width=488)
> > (actual time=3.71.35..371.35 rows=0 loops=1)
> >
> > Filter: (org[0]='math'::text)
>
> Well that's awfully odd. I don't know how that expression came out of the
> query you gave. You'll have to give a lot more information about how
you're
> defining *= and why you think it's related to the function you used to
define
> the index.
>
> -- 
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>