Hi,

I've got a log-table that records events regarding other objects.
Those events have a state that shows the progress of further work on
this event.
They can be open, accepted or rejected.

I don't want to be able to insert addition events regarding an object X
as long there is an open or accepted event.
On the other hand as soon as the current event gets rejected a new event
should be possible.

So there may be several rejected events at any time but no more than 1
open or accepted entry.

Can I do this within the DB so I don't have to trust the client app?

The layout looks like this
Table : objects ( id serial, .... )

Table : event_log ( id serial, oject_id integer references objects.id,
state integer, date_created timestamp, ... )
where state is 0 = open, -1 = reject, 1 = accept

I can't simply move rejected events in an archive table and keep a
unique index on object_id as there are other descriptive tables that
reference the event_log.id.



--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Andreas <maps.on (AT) gmx (DOT) net> wrote:

test=# create table log (state int not null, check (state in (-1,0,1)));
CREATE TABLE
Time: 37,527 ms
test=*# commit;
COMMIT
Time: 0,556 ms
test=# create unique index on log((case when state in (0,1) then 1 else
null end));
CREATE INDEX
Time: 18,558 ms
test=*# insert into log values (-1);
INSERT 0 1
Time: 0,611 ms
test=*# insert into log values (-1);
INSERT 0 1
Time: 0,274 ms
test=*# insert into log values (-1);
INSERT 0 1
Time: 0,248 ms
test=*# insert into log values (1);
INSERT 0 1
Time: 0,294 ms
test=*# insert into log values (0);
ERROR: duplicate key value violates unique constraint "log_case_idx"
DETAIL: Key ((
CASE
WHEN state = ANY (ARRAY[0, 1]) THEN 1
ELSE NULL::integer
END))=(1) already exists.
test=!#

HTH.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Andreas Kretschmer <akretschmer (AT) spamfence (DOT) net> wrote:

Or this one:

test=*# create unique index on log((case when state = 0 then 0 when
state = 1 then 1 else null end));
CREATE INDEX


Now you can insert one '0' and one '1' - value - but no more.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Wed, Jul 11, 2012 at 12:50 AM, Andreas <maps.on (AT) gmx (DOT) net> wrote:

[...]

Would a multi-column index, unique on (id, state) meet your need?

rls

--
:wq

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Hi,

A partial index would do the same, but requires less space:

create unique index on log(state) WHERE state IN (0,1);

best regards,

Marc Mamin



--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Marc Mamin <M.Mamin (AT) intershop (DOT) de> wrote:

Right! ;-)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Am 12.07.2012 07:14, schrieb Andreas Kretschmer:

OK, nice

What if I have those states in a 3rd table?
So I can see a state-history of when a state got set by whom.


objects ( id serial PK, ... )
events ( id serial PK, object_id integer FK on objects.id, ... )

event_states ( id serial PK, event_id integer FK on events.id, state
integer )

There still should only be one event per object that has state 0 or 1.
Though here I don't have the object-id within the event_states-table.

Is it still possible to have a unique index that needs to span over a
join of events and event_states?

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Jul 12, 2012, at 4:44, Andreas <maps.on (AT) gmx (DOT) net> wrote:

No, all index columns must come from the same table. You would need to usea trigger-based system to enforce your constraint.

You can either have the triggers simply perform validation or you can create a materialized view and create the partial index on that. You could alsoconsider creating an updatable view and avoid directly interacting with the three individual tables.

You could also just turn event states into a history table and leave the current state on the event table.

David J.
--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql