Обсуждение: unique & update

Hi All

Im into SQL trees trying to work with CELKO way to do it:
http://www.intelligententerprise.com/001020/celko.shtml

The problem is that if I try to add a new descendent which is not the most
right sibling I get UNIQUE error.

This occur ( if im right ) when the update try to update the lft column
which is UNIQUE

Example of table:

CREATE TABLE areas (
lft INT UNIQUE
);

I inserted 3 rows with value: 1, 2, 3

now im trying to update:
update areas set lft = lft +1;

Then I get the UNIQUE problem.

Is there a way to do UNIQUE check after the whole table got updated ?
If not, any ideas to walk around the problem ?

--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
http://sites.canaan.co.il
--------------------------

On Tue, 2002-01-29 at 13:38, Ben-Nes Michael wrote:
> Hi All
>
> Im into SQL trees trying to work with CELKO way to do it:
> http://www.intelligententerprise.com/001020/celko.shtml
>
> The problem is that if I try to add a new descendent which is not the most
> right sibling I get UNIQUE error.
>
> This occur ( if im right ) when the update try to update the lft column
> which is UNIQUE
>
> Example of table:
>
> CREATE TABLE areas (
> lft INT UNIQUE
> );

O.K. ... we used a column constraint ...

> I inserted 3 rows with value: 1, 2, 3

... everything is unique, so all is fine ...

> now im trying to update:
> update areas set lft = lft +1;

... update works on the first row, so the table would be 2, 2, 3 ...

> Then I get the UNIQUE problem.

... what is exectly what the unique constraint is for.

> Is there a way to do UNIQUE check after the whole table got updated ?
> If not, any ideas to walk around the problem ?

Try:
CREATE TABLE areas (
    UNIQUE ( lft ),
    lft    INT
);

Then we have a table with a table constraint.
I'm only guessing ( using ``\h CREATE TABLE'' on the psql prompt ) and
I'm a PostgreSQL -- and in fact SQL -- newbie, but if I'm interpreting
the syntax diagram and MY mind about these constraints right, this
should do what you need.

Regards
Frank

> On Tue, 2002-01-29 at 13:38, Ben-Nes Michael wrote:
> > Hi All
> >
> > Im into SQL trees trying to work with CELKO way to do it:
> > http://www.intelligententerprise.com/001020/celko.shtml
> >
> > The problem is that if I try to add a new descendent which is not the
most
> > right sibling I get UNIQUE error.
> >
> > This occur ( if im right ) when the update try to update the lft column
> > which is UNIQUE
> >
> > Example of table:
> >
> > CREATE TABLE areas (
> > lft INT UNIQUE
> > );
>
> O.K. ... we used a column constraint ...
>
> > I inserted 3 rows with value: 1, 2, 3
>
> ... everything is unique, so all is fine ...
>
> > now im trying to update:
> > update areas set lft = lft +1;
>
> ... update works on the first row, so the table would be 2, 2, 3 ...
>
> > Then I get the UNIQUE problem.
>
> ... what is exectly what the unique constraint is for.
>
> > Is there a way to do UNIQUE check after the whole table got updated ?
> > If not, any ideas to walk around the problem ?
>
> Try:
> CREATE TABLE areas (
> UNIQUE ( lft ),
> lft INT
> );

I tried your syntax and the following with no success ( same problem)
CREATE TABLE areas (
lft INT,
CONSTRAINT testunique UNIQUE ( lft )
);

>
> Then we have a table with a table constraint.
> I'm only guessing ( using ``\h CREATE TABLE'' on the psql prompt ) and
> I'm a PostgreSQL -- and in fact SQL -- newbie, but if I'm interpreting
> the syntax diagram and MY mind about these constraints right, this
> should do what you need.
>
> Regards
> Frank

To do this create the table in the system without the unique.

Do all the insertions that you are wanting to do then use the create index
and create a unique index. This will solve the unique problem. Postgres is
working fine just now because the command you run gives a duplicate and so
the unique field flagged an error.

Syntax:
CREATE UNIQUE INDEX index_name ON areas(lft);

Hope this helps

Darren Ferguson

On Tue, 29 Jan 2002, Ben-Nes Michael wrote:

>
> > On Tue, 2002-01-29 at 13:38, Ben-Nes Michael wrote:
> > > Hi All
> > >
> > > Im into SQL trees trying to work with CELKO way to do it:
> > > http://www.intelligententerprise.com/001020/celko.shtml
> > >
> > > The problem is that if I try to add a new descendent which is not the
> most
> > > right sibling I get UNIQUE error.
> > >
> > > This occur ( if im right ) when the update try to update the lft column
> > > which is UNIQUE
> > >
> > > Example of table:
> > >
> > > CREATE TABLE areas (
> > > lft INT UNIQUE
> > > );
> >
> > O.K. ... we used a column constraint ...
> >
> > > I inserted 3 rows with value: 1, 2, 3
> >
> > ... everything is unique, so all is fine ...
> >
> > > now im trying to update:
> > > update areas set lft = lft +1;
> >
> > ... update works on the first row, so the table would be 2, 2, 3 ...
> >
> > > Then I get the UNIQUE problem.
> >
> > ... what is exectly what the unique constraint is for.
> >
> > > Is there a way to do UNIQUE check after the whole table got updated ?
> > > If not, any ideas to walk around the problem ?
> >
> > Try:
> > CREATE TABLE areas (
> > UNIQUE ( lft ),
> > lft INT
> > );
>
> I tried your syntax and the following with no success ( same problem)
> CREATE TABLE areas (
> lft INT,
> CONSTRAINT testunique UNIQUE ( lft )
> );
>
> >
> > Then we have a table with a table constraint.
> > I'm only guessing ( using ``\h CREATE TABLE'' on the psql prompt ) and
> > I'm a PostgreSQL -- and in fact SQL -- newbie, but if I'm interpreting
> > the syntax diagram and MY mind about these constraints right, this
> > should do what you need.
> >
> > Regards
> > Frank
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

----- Original Message -----
From: "Ben-Nes Michael" <miki@canaan.co.il>
To: "Frank Schafer" <frank.schafer@setuza.cz>;
<pgsql-general@postgresql.org>
Sent: Tuesday, January 29, 2002 2:52 PM
Subject: Re: [GENERAL] unique & update


>
> > On Tue, 2002-01-29 at 13:38, Ben-Nes Michael wrote:
> > > Hi All
> > >
> > > Im into SQL trees trying to work with CELKO way to do it:
> > > http://www.intelligententerprise.com/001020/celko.shtml
> > >
> > > The problem is that if I try to add a new descendent which is not the
> most
> > > right sibling I get UNIQUE error.
> > >
> > > This occur ( if im right ) when the update try to update the lft
column
> > > which is UNIQUE
> > >
> > > Example of table:
> > >
> > > CREATE TABLE areas (
> > > lft INT UNIQUE
> > > );
> >
> > O.K. ... we used a column constraint ...
> >
> > > I inserted 3 rows with value: 1, 2, 3
> >
> > ... everything is unique, so all is fine ...
> >
> > > now im trying to update:
> > > update areas set lft = lft +1;
> >
> > ... update works on the first row, so the table would be 2, 2, 3 ...
> >
> > > Then I get the UNIQUE problem.
> >
> > ... what is exectly what the unique constraint is for.
> >
> > > Is there a way to do UNIQUE check after the whole table got updated ?
> > > If not, any ideas to walk around the problem ?
> >
> > Try:
> > CREATE TABLE areas (
> > UNIQUE ( lft ),
> > lft INT
> > );
>
> I tried your syntax and the following with no success ( same problem)
> CREATE TABLE areas (
> lft INT,
> CONSTRAINT testunique UNIQUE ( lft )
> );
>
> >
> > Then we have a table with a table constraint.
> > I'm only guessing ( using ``\h CREATE TABLE'' on the psql prompt ) and
> > I'm a PostgreSQL -- and in fact SQL -- newbie, but if I'm interpreting
> > the syntax diagram and MY mind about these constraints right, this
> > should do what you need.
> >
> > Regards
> > Frank
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Sorry for the last email...

Hi!.
Try to use the deferred type of constraint, i'm not pretty sure about the
exact syntax.

http://www.ninthwonder.com/info/postgres/user/sql-createtable.htm

UNIQUE clause
SQL92 specifies some additional capabilities for UNIQUE:

Table Constraint definition

      [ CONSTRAINT name ]
      UNIQUE ( column [, ...] )
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]

Column Constraint definition

      [ CONSTRAINT name ]
      UNIQUE
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]

Also try with
SET CONSTRAINTS ALL DEFERRED;

In a transaction. I'm not 100% if it can be the way. I'm sorry, if it can't
help you.

Best regards

Juan Carlos

P.D.: Sorry about my english :)




> To do this create the table in the system without the unique.
>
> Do all the insertions that you are wanting to do then use the create index
> and create a unique index. This will solve the unique problem. Postgres is
> working fine just now because the command you run gives a duplicate and so
> the unique field flagged an error.
>
> Syntax:
> CREATE UNIQUE INDEX index_name ON areas(lft);
>
> Hope this helps
>
> Darren Ferguson
>
> On Tue, 29 Jan 2002, Ben-Nes Michael wrote:
>
> >
> > > On Tue, 2002-01-29 at 13:38, Ben-Nes Michael wrote:
> > > > Hi All
> > > >
> > > > Im into SQL trees trying to work with CELKO way to do it:
> > > > http://www.intelligententerprise.com/001020/celko.shtml
> > > >
> > > > The problem is that if I try to add a new descendent which is not
the
> > most
> > > > right sibling I get UNIQUE error.
> > > >
> > > > This occur ( if im right ) when the update try to update the lft
column
> > > > which is UNIQUE
> > > >
> > > > Example of table:
> > > >
> > > > CREATE TABLE areas (
> > > > lft INT UNIQUE
> > > > );
> > >
> > > O.K. ... we used a column constraint ...
> > >
> > > > I inserted 3 rows with value: 1, 2, 3
> > >
> > > ... everything is unique, so all is fine ...
> > >
> > > > now im trying to update:
> > > > update areas set lft = lft +1;
> > >
> > > ... update works on the first row, so the table would be 2, 2, 3 ...
> > >
> > > > Then I get the UNIQUE problem.
> > >
> > > ... what is exectly what the unique constraint is for.
> > >
> > > > Is there a way to do UNIQUE check after the whole table got updated
?
> > > > If not, any ideas to walk around the problem ?
> > >
> > > Try:
> > > CREATE TABLE areas (
> > > UNIQUE ( lft ),
> > > lft INT
> > > );
> >
> > I tried your syntax and the following with no success ( same problem)
> > CREATE TABLE areas (
> > lft INT,
> > CONSTRAINT testunique UNIQUE ( lft )
> > );
> >
> > >
> > > Then we have a table with a table constraint.
> > > I'm only guessing ( using ``\h CREATE TABLE'' on the psql prompt ) and
> > > I'm a PostgreSQL -- and in fact SQL -- newbie, but if I'm interpreting
> > > the syntax diagram and MY mind about these constraints right, this
> > > should do what you need.
> > >
> > > Regards
> > > Frank
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Hello,

I would know if it's possible to use the EXECUTE Command in PSQL as SQL
command in Pg 7.2 release.

In fact, this can make possible the dynamic query execution by using PSQL. I
tried to use PGPLSQL function that returns RECORD type without success. For
example when I create the following function :

Create function Foo (TEXT) returns RECORD As'

declare

query alias for $1;

begin

return (execute query); -- query is a sql select command (in general it's
created dynamiquelly).

end;

' language 'plpgsql';

I receive this message

NOTICE: ProcedureCreate: return type 'record' is only a shell.

the execution of this function prompts the following error:

ERROR: fmgr_info: function 0: cache lookup failed.

I think that de record type is created to contain table tuples regardless of
their structure. Is-this right? So, why we can't use it to contain any query
result?

Thanks for your help.

Mourad.

I Think you must use transaction.
transaction can start by Begin work and finish by end.
There is no constraint checks within a trasaction. Try this it does work.
Mourad.
----- Original Message -----
From: "Ben-Nes Michael" <miki@canaan.co.il>
To: "Frank Schafer" <frank.schafer@setuza.cz>;
<pgsql-general@postgresql.org>
Sent: Tuesday, January 29, 2002 2:52 PM
Subject: Re: [GENERAL] unique & update


>
> > On Tue, 2002-01-29 at 13:38, Ben-Nes Michael wrote:
> > > Hi All
> > >
> > > Im into SQL trees trying to work with CELKO way to do it:
> > > http://www.intelligententerprise.com/001020/celko.shtml
> > >
> > > The problem is that if I try to add a new descendent which is not the
> most
> > > right sibling I get UNIQUE error.
> > >
> > > This occur ( if im right ) when the update try to update the lft
column
> > > which is UNIQUE
> > >
> > > Example of table:
> > >
> > > CREATE TABLE areas (
> > > lft INT UNIQUE
> > > );
> >
> > O.K. ... we used a column constraint ...
> >
> > > I inserted 3 rows with value: 1, 2, 3
> >
> > ... everything is unique, so all is fine ...
> >
> > > now im trying to update:
> > > update areas set lft = lft +1;
> >
> > ... update works on the first row, so the table would be 2, 2, 3 ...
> >
> > > Then I get the UNIQUE problem.
> >
> > ... what is exectly what the unique constraint is for.
> >
> > > Is there a way to do UNIQUE check after the whole table got updated ?
> > > If not, any ideas to walk around the problem ?
> >
> > Try:
> > CREATE TABLE areas (
> > UNIQUE ( lft ),
> > lft INT
> > );
>
> I tried your syntax and the following with no success ( same problem)
> CREATE TABLE areas (
> lft INT,
> CONSTRAINT testunique UNIQUE ( lft )
> );
>
> >
> > Then we have a table with a table constraint.
> > I'm only guessing ( using ``\h CREATE TABLE'' on the psql prompt ) and
> > I'm a PostgreSQL -- and in fact SQL -- newbie, but if I'm interpreting
> > the syntax diagram and MY mind about these constraints right, this
> > should do what you need.
> >
> > Regards
> > Frank
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Tue, 29 Jan 2002, Ben-Nes Michael wrote:

> Im into SQL trees trying to work with CELKO way to do it:
> http://www.intelligententerprise.com/001020/celko.shtml
>
> The problem is that if I try to add a new descendent which is not the most
> right sibling I get UNIQUE error.
>
> This occur ( if im right ) when the update try to update the lft column
> which is UNIQUE
>
> Example of table:
>
> CREATE TABLE areas (
> lft INT UNIQUE
> );
>
> I inserted 3 rows with value: 1, 2, 3
>
> now im trying to update:
> update areas set lft = lft +1;
>
> Then I get the UNIQUE problem.
>
> Is there a way to do UNIQUE check after the whole table got updated ?
> If not, any ideas to walk around the problem ?

Unfortunately no, we don't support deferred unique constraint afaik and
the unique constraint we have isn't quite correct according to spec (yes,
the above should work).  Technically we should be checking the after all
updates have occurred, but iirc we do it on the insert into the index
which is why this happens.

The closest thing I could think of would be a constraint trigger that did
a uniqueness check but that'll probably be somewhat slower (a trigger that
looks for something like: select lft from areas group by lft having
count(*)>1;)

On Tuesday 29 January 2002 19:13, Stephan Szabo wrote:
> On Tue, 29 Jan 2002, Ben-Nes Michael wrote:
> > Im into SQL trees trying to work with CELKO way to do it:
> > http://www.intelligententerprise.com/001020/celko.shtml
> >
> > The problem is that if I try to add a new descendent which is not the
> > most right sibling I get UNIQUE error.
> >
> > This occur ( if im right ) when the update try to update the lft column
> > which is UNIQUE
> >
> > Example of table:
> >
> > CREATE TABLE areas (
> > lft INT UNIQUE
> > );
> >
> > I inserted 3 rows with value: 1, 2, 3
> >
> > now im trying to update:
> > update areas set lft = lft +1;
> >
> > Then I get the UNIQUE problem.
> >
> > Is there a way to do UNIQUE check after the whole table got updated ?
> > If not, any ideas to walk around the problem ?
>
> Unfortunately no, we don't support deferred unique constraint afaik and
> the unique constraint we have isn't quite correct according to spec (yes,
> the above should work).  Technically we should be checking the after all
> updates have occurred, but iirc we do it on the insert into the index
> which is why this happens.
Will 7.2 support it ?
If not do you have estimate time to when it will be supported ?

Can you tip me how to create trigger thats do uniqueness check ?

>
> The closest thing I could think of would be a constraint trigger that did
> a uniqueness check but that'll probably be somewhat slower (a trigger that
> looks for something like: select lft from areas group by lft having
> count(*)>1;)

> > Unfortunately no, we don't support deferred unique constraint afaik and
> > the unique constraint we have isn't quite correct according to spec (yes,
> > the above should work).  Technically we should be checking the after all
> > updates have occurred, but iirc we do it on the insert into the index
> > which is why this happens.
> Will 7.2 support it ?
> If not do you have estimate time to when it will be supported ?

Unfortunately not in 7.2.  I don't know how big a change it'd be.  Since
I'm not personally planning to work on it (seems more low level than I'm
ready for), I don't know of an ETA.

> Can you tip me how to create trigger thats do uniqueness check ?

Hmm, actually it looks like it might be a bit complicated to get the
locking right for concurrent connections (might require something like
the foreign key constraints) although the single connection one is easy.

> > Is there a way to do UNIQUE check after the whole table got updated ?
> > If not, any ideas to walk around the problem ?
> The closest thing I could think of would be a constraint trigger that did
> a uniqueness check but that'll probably be somewhat slower (a trigger that
> looks for something like: select lft from areas group by lft having
> count(*)>1;)

How can I rollback in a function ?
I mean after update has been done, I do some checking in the Function and
then decide that I want to quit and rollback all what inserted/updated in
the start of the function ?

Also if I attach trigger that check column uniqueness to an update will it
check it per updated row or at the end of the action after all the table got
updated ?

Cheer

--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
http://sites.canaan.co.il
--------------------------

> How can I rollback in a function ?
> I mean after update has been done, I do some checking in the Function and
> then decide that I want to quit and rollback all what inserted/updated in
> the start of the function ?

Just do:
  RAISE EXCEPTION ''I did some checking, and decided to quit and rollback'';

> Also if I attach trigger that check column uniqueness to an update will it
> check it per updated row or at the end of the action after all the table
got
> updated ?

By default, it checks after the row was updated, but you can make it
INITIALLY DEFERRED which will wait until after the transaction to check. To
be honest, I'm not sure if you have to declare it DEFERRABLE as well, or if
the INITIALLY DEFERRED will handle both.

Greg

"Ben-Nes Michael" <miki@canaan.co.il> writes:

> How can I rollback in a function ?
> I mean after update has been done, I do some checking in the Function and
> then decide that I want to quit and rollback all what inserted/updated in
> the start of the function ?

A function is always executed inside a transaction.  You can roll back
the whole transaction by throwing an error, but you can't roll back
just what you did in the function.

There is talk of adding savepoints to PG (possibly in 7.3) which would
allow this, but right now there is no mechanism for it.

> Also if I attach trigger that check column uniqueness to an update will it
> check it per updated row or at the end of the action after all the table got
> updated ?

Why not just create a unique index rather than using a trigger?

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

> By default, it checks after the row was updated, but you can make it
> INITIALLY DEFERRED which will wait until after the transaction to check.

Er, by after the transaction, I meant until the end of the transaction.
Minor but important difference...

Greg

On 31 Jan 2002, Doug McNaught wrote:

> "Ben-Nes Michael" <miki@canaan.co.il> writes:
>
> > How can I rollback in a function ?
> > I mean after update has been done, I do some checking in the Function and
> > then decide that I want to quit and rollback all what inserted/updated in
> > the start of the function ?
>
> A function is always executed inside a transaction.  You can roll back
> the whole transaction by throwing an error, but you can't roll back
> just what you did in the function.
>
> There is talk of adding savepoints to PG (possibly in 7.3) which would
> allow this, but right now there is no mechanism for it.
>
> > Also if I attach trigger that check column uniqueness to an update will it
> > check it per updated row or at the end of the action after all the table got
> > updated ?
>
> Why not just create a unique index rather than using a trigger?

The issue is that unique indexes check their uniqueness at the "wrong"
time.  Constraints should AFAICS be checked after the statement, not
during, so cases like foo=foo+1 (where foo is unique) should succeed, but
in postgres it will only succeed if the values of foo are checked in an
order such that you never have an overlapping value (where one's been
changed and the other hasn't).

Use a trigger and if the trigger fails then it should rollback everything
that you had done.

Triggers can be fired per row instead of the whole table once the update
is completed

Darren Ferguson

On Thu, 31 Jan 2002, Ben-Nes Michael wrote:

> > > Is there a way to do UNIQUE check after the whole table got updated ?
> > > If not, any ideas to walk around the problem ?
> > The closest thing I could think of would be a constraint trigger that did
> > a uniqueness check but that'll probably be somewhat slower (a trigger that
> > looks for something like: select lft from areas group by lft having
> > count(*)>1;)
>
> How can I rollback in a function ?
> I mean after update has been done, I do some checking in the Function and
> then decide that I want to quit and rollback all what inserted/updated in
> the start of the function ?
>
> Also if I attach trigger that check column uniqueness to an update will it
> check it per updated row or at the end of the action after all the table got
> updated ?
>
> Cheer
>
> --------------------------
> Canaan Surfing Ltd.
> Internet Service Providers
> Ben-Nes Michael - Manager
> Tel: 972-4-6991122
> http://sites.canaan.co.il
> --------------------------
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>