pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPrevent double entries ... no simple unique index
8+ messages / 5 participants
[nested] [flat]
* Prevent double entries ... no simple unique index
@ 2012-07-11 07:50 Andreas <maps.on@gmx.net>
2012-07-11 08:16 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 09:11 ` Re: Prevent double entries ... no simple unique index Rosser Schwarz <rosser.schwarz@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Andreas @ 2012-07-11 07:50 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Prevent double entries ... no simple unique index
2012-07-11 07:50 Prevent double entries ... no simple unique index Andreas <maps.on@gmx.net>
@ 2012-07-11 08:16 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 08:24 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
1 sibling, 1 reply; 8+ messages in thread
From: Andreas Kretschmer @ 2012-07-11 08:16 UTC (permalink / raw)
To: pgsql-sql
Andreas <maps.on@gmx.net> wrote:
> 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
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°
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Prevent double entries ... no simple unique index
2012-07-11 07:50 Prevent double entries ... no simple unique index Andreas <maps.on@gmx.net>
2012-07-11 08:16 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
@ 2012-07-11 08:24 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 10:53 ` Re: Prevent double entries ... no simple unique index Marc Mamin <M.Mamin@intershop.de>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas Kretschmer @ 2012-07-11 08:24 UTC (permalink / raw)
To: pgsql-sql
Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> Andreas <maps.on@gmx.net> wrote:
>
> > 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
>
> 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
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°
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Prevent double entries ... no simple unique index
2012-07-11 07:50 Prevent double entries ... no simple unique index Andreas <maps.on@gmx.net>
2012-07-11 08:16 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 08:24 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
@ 2012-07-11 10:53 ` Marc Mamin <M.Mamin@intershop.de>
2012-07-12 05:14 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 8+ messages in thread
From: Marc Mamin @ 2012-07-11 10:53 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-sql
>
> 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.
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Prevent double entries ... no simple unique index
2012-07-11 07:50 Prevent double entries ... no simple unique index Andreas <maps.on@gmx.net>
2012-07-11 08:16 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 08:24 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 10:53 ` Re: Prevent double entries ... no simple unique index Marc Mamin <M.Mamin@intershop.de>
@ 2012-07-12 05:14 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-12 08:44 ` Re: Prevent double entries ... no simple unique index Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas Kretschmer @ 2012-07-12 05:14 UTC (permalink / raw)
To: pgsql-sql
Marc Mamin <M.Mamin@intershop.de> 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.
>
> Hi,
>
> A partial index would do the same, but requires less space:
>
> create unique index on log(state) WHERE state IN (0,1);
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°
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Prevent double entries ... no simple unique index
2012-07-11 07:50 Prevent double entries ... no simple unique index Andreas <maps.on@gmx.net>
2012-07-11 08:16 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 08:24 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 10:53 ` Re: Prevent double entries ... no simple unique index Marc Mamin <M.Mamin@intershop.de>
2012-07-12 05:14 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
@ 2012-07-12 08:44 ` Andreas <maps.on@gmx.net>
2012-07-12 13:40 ` Re: Prevent double entries ... no simple unique index David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas @ 2012-07-12 08:44 UTC (permalink / raw)
To: pgsql-sql; +Cc: Kretschmer Andreas <akretschmer@spamfence.net>; M.Mamin@intershop.de
Am 12.07.2012 07:14, schrieb Andreas Kretschmer:
> Marc Mamin <M.Mamin@intershop.de> wrote:
>
>> A partial index would do the same, but requires less space:
>>
>> create unique index on log(state) WHERE state IN (0,1);
>
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Prevent double entries ... no simple unique index
2012-07-11 07:50 Prevent double entries ... no simple unique index Andreas <maps.on@gmx.net>
2012-07-11 08:16 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 08:24 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 10:53 ` Re: Prevent double entries ... no simple unique index Marc Mamin <M.Mamin@intershop.de>
2012-07-12 05:14 ` Re: Prevent double entries ... no simple unique index Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-12 08:44 ` Re: Prevent double entries ... no simple unique index Andreas <maps.on@gmx.net>
@ 2012-07-12 13:40 ` David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: David Johnston @ 2012-07-12 13:40 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql; Kretschmer Andreas <akretschmer@spamfence.net>; M.Mamin@intershop.de <M.Mamin@intershop.de>
On Jul 12, 2012, at 4:44, Andreas <maps.on@gmx.net> wrote:
> Am 12.07.2012 07:14, schrieb Andreas Kretschmer:
>> Marc Mamin <M.Mamin@intershop.de> wrote:
>>
>>> A partial index would do the same, but requires less space:
>>>
>>> create unique index on log(state) WHERE state IN (0,1);
>>
>
>
> 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?
>
No, all index columns must come from the same table. You would need to use a 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 also consider 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Prevent double entries ... no simple unique index
2012-07-11 07:50 Prevent double entries ... no simple unique index Andreas <maps.on@gmx.net>
@ 2012-07-11 09:11 ` Rosser Schwarz <rosser.schwarz@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Rosser Schwarz @ 2012-07-11 09:11 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On Wed, Jul 11, 2012 at 12:50 AM, Andreas <maps.on@gmx.net> wrote:
[...]
> 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.
Would a multi-column index, unique on (id, state) meet your need?
rls
--
:wq
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2012-07-12 13:40 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-11 07:50 Prevent double entries ... no simple unique index Andreas <maps.on@gmx.net>
2012-07-11 08:16 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 08:24 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-11 10:53 ` Marc Mamin <M.Mamin@intershop.de>
2012-07-12 05:14 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-07-12 08:44 ` Andreas <maps.on@gmx.net>
2012-07-12 13:40 ` David Johnston <polobo@yahoo.com>
2012-07-11 09:11 ` Rosser Schwarz <rosser.schwarz@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox