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

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

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

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

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

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

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

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

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.

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

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

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

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

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