Thread: How to keep the last row of a data set?
I am trying to implement a mechanism that prohibits the last row of a data set from being deleted.
CREATE TABLE t1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2));
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
My desired effect:
Case 1, Permit this SQL to be executed:
DELETE FROM t1 WHERE c1=1 AND c2 <> 2;
This SQL keeps one row whose column c1 holds value "1". It does not hurt.
Case 2, Raise exception if users attempt to run this SQL:
DELETE FROM t1 WHERE c1=1;
This SQL attempts to delete all rows having value "1" in column c1. It must be automatically aborted.
The following trigger protects nothing:
CREATE OR REPLACE FUNCTION tfd() RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE '%',(SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1);
IF (SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1) = 1 THEN
RAISE EXCEPTION 'Must keep at least 1 row for c1="%"',OLD.c1;
END IF;
RETURN OLD;
END $$ LANGUAGE PLPGSQL STABLE;
CREATE TRIGGER td BEFORE DELETE ON t1 FOR EACH ROW EXECUTE PROCEDURE tfd();
postgres@AMD64:/tmp$ psql -c 'DELETE FROM t1' test
Notice: 3
Notice: 3
Notice: 3
Notice: 3
Notice: 3
Notice: 3
DELETE 6
postgres@AMD64:/tmp$
Thank you in advance for helping me out!
Best Regards,
CN
Hi,
create an after delete trigger with
IF (SELECT 1 FROM t1 limit 1) is null THEN
RAISE EXCEPTION 'Must keep at least 1 row';
....
hth
Thomas
> -----Original Message-----
> From: Thomas Markus
> Sent: Thu, Dec 13 2012 23:14:21 CST
> To: seiliki@so-net.net.tw
> Subject: Re: [GENERAL] How to keep the last row of a data set?
>
> Hi,
>
> create an after delete trigger with
>
> IF (SELECT 1 FROM t1 limit 1) is null THEN
> RAISE EXCEPTION 'Must keep at least 1 row';
> ....
>
>
> hth
> Thomas
>
AFTER DELETE trigger does not prevent all rows from being deleted, either:
CREATE TABLE t1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2));
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
CREATE OR REPLACE FUNCTION tfd() RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE '%',(SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1);
--IF NOT EXISTS (SELECT 1 FROM t1 WHERE c1=OLD.c1) THEN
IF (SELECT 1 FROM t1 WHERE c1=OLD.c1 LIMIT 1) IS NULL THEN
RAISE EXCEPTION 'Must keep at least 1 row for c1="%"',OLD.c1;
END IF;
RETURN OLD;
END $$ LANGUAGE PLPGSQL STABLE;
CREATE TRIGGER td AFTER DELETE ON t1 FOR EACH ROW EXECUTE PROCEDURE tfd();
test:
DELETE FROM t1 WHERE c1=1;
Best Regards,
CN
seiliki@so-net.net.tw writes:
> I am trying to implement a mechanism that prohibits the last row of a data set from being deleted.
> The following trigger protects nothing:
> CREATE OR REPLACE FUNCTION tfd() RETURNS TRIGGER AS $$
> BEGIN
> RAISE NOTICE '%',(SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1);
> IF (SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1) = 1 THEN
> RAISE EXCEPTION 'Must keep at least 1 row for c1="%"',OLD.c1;
> END IF;
> RETURN OLD;
> END $$ LANGUAGE PLPGSQL STABLE;
The reason that doesn't work is you marked it "stable", so it always
sees the starting state of the outer query.
Mind you, even with that oversight fixed, this approach will do little
to save you from concurrent-update situations. That is, transaction A
could delete some of the rows with c1=1, and transaction B could
concurrently delete the rest, and neither transaction will see a reason
why it shouldn't commit.
regards, tom lane
Tom Lane wrote: > seiliki@so-net.net.tw writes: >> I am trying to implement a mechanism that prohibits the last row >> of a data set from being deleted. > The reason that doesn't work is you marked it "stable", so it > always sees the starting state of the outer query. > > Mind you, even with that oversight fixed, this approach will do > little to save you from concurrent-update situations. That is, > transaction A could delete some of the rows with c1=1, and > transaction B could concurrently delete the rest, and neither > transaction will see a reason why it shouldn't commit. Right, that is a form of write skew, where each transaction is writing to the database (in this case with deletes) based on reading a portion of the database written to by the other transaction. This will be handled automatically if all transactions are using transaction isolation level SERIALIZABLE. Otherwise you need to materialize the conflict (for example, by adding a separate table with one row per c1 value, and a count of matching t1 rows, maintained by triggers) or promote the conflict (changing the non-blocking read-write conflicts into write-write conflicts, by updating all the rows with the same c1 value which you are not deleting). Or you could use table-level blocking with LOCK TABLE statements, or develop some scheme to use advisory locks. These pages might help: http://www.postgresql.org/docs/9.2/interactive/mvcc.html http://wiki.postgresql.org/wiki/SSI -Kevin
On 12/13/2012 5:32 AM, seiliki@so-net.net.tw wrote: > I am trying to implement a mechanism that prohibits the last row of a data set from being deleted. > > CREATE TABLE t1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2)); > > INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3); Which row is the last row? relations are sets, not ordered lists.
Em 13/12/2012 18:22, John R Pierce escreveu: > On 12/13/2012 5:32 AM, seiliki@so-net.net.tw wrote: >> I am trying to implement a mechanism that prohibits the last row of a >> data set from being deleted. >> >> CREATE TABLE t1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2)); >> >> INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3); > > Which row is the last row? relations are sets, not ordered lists. > > Last row is not the row that remains, no matter the order? Edson
On Fri, Dec 14, 2012 at 7:22 AM, John R Pierce <pierce@hogranch.com> wrote: > On 12/13/2012 5:32 AM, seiliki@so-net.net.tw wrote: >> >> I am trying to implement a mechanism that prohibits the last row of a data >> set from being deleted. >> >> CREATE TABLE t1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2)); >> >> INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3); > > > Which row is the last row? relations are sets, not ordered lists. My understanding of the OP is that this is a constraint whereby there must always be at least one remaining row for a given value of c1. That is to say, you may delete any row from t1 as long as it's not the last row (temporally, not sequentially) with that c1. ChrisA