Thread: rule with multiple DELETE action part
Hello,
now, my only question is how I can make a rule on a view with multiple delete action part which
works well. I need a mechanism which deletes two rows from two tables
which are in a one-to-one join.
The example listed below is a quite different from the real tables I use.
The table in the same role as "t_two" references the other table ("t_one").
If rules with multiple action don't work correctly this would mean that
my only last chance is using the REFERENCES constraint with ON DELETE CASCADE option?
I hope not, because it is not for the same, I want to deny to delete rows
if it has a pair in the other table.
By the way, multiple inserts seem to work.
try=# CREATE TABLE t_one (c_i integer PRIMARY KEY, c_t text);
CREATE
try=# CREATE TABLE t_two (c_i integer REFERENCES t_one, c_d date);
CREATE
try=# CREATE VIEW v_one_two AS SELECT t_one.oid as c_oid, t_one.c_i, c_d, c_t
try-# FROM t_one, t_two WHERE t_one.c_i = t_two.c_i;
CREATE 81186 1
try=# CREATE RULE r_one_two_del AS ON DELETE TO v_one_two
try-# DO INSTEAD (DELETE FROM t_two WHERE c_i = old.c_i; DELETE FROM t_one WHERE c_i = old.c_i;);
CREATE 81187 1
try=# SELECT * FROM v_one_two;
c_oid | c_i | c_d | c_t
-------+-----+------------+-------
81157 | 1 | 2000-09-01 | hello
81158 | 2 | 1999-12-31 | world
81159 | 3 | 2000-08-12 | brave
(3 rows)
try=# DELETE FROM v_one_two WHERE c_i = 2;
DELETE 0
try=# SELECT * FROM t_one;
c_i | c_t
-----+-------
1 | hello
2 | world
3 | brave
4 | guy
(4 rows)
try=# SELECT * FROM t_two;
c_i | c_d
-----+------------
1 | 2000-09-01
3 | 2000-08-12
(2 rows)
Papp Gyozo
s7461pap@hszk.bme.hu, gerzson17@freemail.hu
I'm just wondering anybody read my question?
If not, here you are:
On Mon, 18 Sep 2000, Papp Gyozo wrote:
> Hello,
>
> now, my only question is how I can make a rule on a view with multiple delete action part which
> works well. I need a mechanism which deletes two rows from two tables
> which are in a one-to-one join.
>
> The example listed below is a quite different from the real tables I use.
> The table in the same role as "t_two" references the other table ("t_one").
> If rules with multiple action don't work correctly this would mean that
> my only last chance is using the REFERENCES constraint with ON DELETE CASCADE option?
> I hope not, because it is not for the same, I want to deny to delete rows
> if it has a pair in the other table.
>
> By the way, multiple inserts seem to work.
>
> try=# CREATE TABLE t_one (c_i integer PRIMARY KEY, c_t text);
> CREATE
> try=# CREATE TABLE t_two (c_i integer REFERENCES t_one, c_d date);
> CREATE
> try=# CREATE VIEW v_one_two AS SELECT t_one.oid as c_oid, t_one.c_i, c_d, c_t
> try-# FROM t_one, t_two WHERE t_one.c_i = t_two.c_i;
> CREATE 81186 1
> try=# CREATE RULE r_one_two_del AS ON DELETE TO v_one_two
> try-# DO INSTEAD (DELETE FROM t_two WHERE c_i = old.c_i; DELETE FROM t_one WHERE c_i = old.c_i;);
> CREATE 81187 1
>
> try=# SELECT * FROM v_one_two;
> c_oid | c_i | c_d | c_t
> -------+-----+------------+-------
> 81157 | 1 | 2000-09-01 | hello
> 81158 | 2 | 1999-12-31 | world
> 81159 | 3 | 2000-08-12 | brave
> (3 rows)
>
> try=# DELETE FROM v_one_two WHERE c_i = 2;
> DELETE 0
> try=# SELECT * FROM t_one;
> c_i | c_t
> -----+-------
> 1 | hello
> 2 | world
> 3 | brave
> 4 | guy
> (4 rows)
>
> try=# SELECT * FROM t_two;
> c_i | c_d
> -----+------------
> 1 | 2000-09-01
> 3 | 2000-08-12
> (2 rows)
Papp Gyozo <s7461pap@hszk.bme.hu> writes:
>> try=# CREATE TABLE t_one (c_i integer PRIMARY KEY, c_t text);
>> CREATE
>> try=# CREATE TABLE t_two (c_i integer REFERENCES t_one, c_d date);
>> CREATE
>> try=# CREATE VIEW v_one_two AS SELECT t_one.oid as c_oid, t_one.c_i, c_d, c_t
>> try-# FROM t_one, t_two WHERE t_one.c_i = t_two.c_i;
>> CREATE 81186 1
>> try=# CREATE RULE r_one_two_del AS ON DELETE TO v_one_two
>> try-# DO INSTEAD (DELETE FROM t_two WHERE c_i = old.c_i; DELETE FROM t_one WHERE c_i = old.c_i;);
>> CREATE 81187 1
I don't think you can make that work --- once you've deleted the t_two
row for a particular c_i value, there's no longer any row in the
v_one_two join for that c_i, and so of course the second DELETE doesn't
find anything to delete. "old.c_i" isn't some magical constant, it's
just a shorthand for referring to the current contents of the rule's
event table, ie the v_one_two join. We can't change that behavior of
rules without breaking other cases that are at least as useful as this.
What you need is something that will hang onto the specific value you're
deleting for long enough to hit both tables. I haven't tried it but I
think you could do it with a function. Perhaps
... ON DELETE TO v_one_two DO INSTEAD SELECT delboth(old.c_i);
where delboth() is a SQL or PLPGSQL function that does the deletions
and then returns some dummy value. This should work because the
specific c_i value is held as a parameter of the function.
regards, tom lane
First, thank youu for the accurate and exhausting answer. And a new idea... I've encountered a problem and I think rules would be the nicest and the best solutions for it. After SELECTing rows I need to update some statistic fields such as timestamp of last reference and number of all references up to now, etc. In pgsql 7.0.2, there is no way to create a rule which can perform an additional query after SELECT. What I'm writing about: CREATE RULE r_my_dream AS ON SELECT TO xxx DO UPDATE xxx SET c_lastref = current_timestamp, c_refnum = c_refnum + 1 WHERE xxx.oid = old.oid; I know that this could be solved with FOR UPDATE clause and then with an UPDATE query. But, it would be so nice if it was totally transparent. (_only_one_ query) Can my dream be realised? Papp Gyozo s7461pap@hszk.bme.hu, gerzson17@freemail.hu
Hi everyone, I have built a database that uses int4 as the unique identifier for a news database. Therefore an article has a identifier 1, the next one has 2 etc. Then I order them when they are displayed on the web reversely so that the last article added is at the top of the list. I now face a major problem. If we need to back dackdate an article - I can't. This is because the all the indexes are taken up ie 1, 2, 3......75 odd records. If I want to add one between 50 and 51 ie 50.5, I cannot because the field is an int4. One idea I had to get around this was to create a new table with this column as a float and read and write all therecords in with a PHP script. Is there an easier way such as converting the column data type from a int4 to a float. Thanks in advance. Abe.
Papp Gyozo <s7461pap@hszk.bme.hu> writes:
> In pgsql 7.0.2, there is no way to create a rule which can perform an
> additional query after SELECT. What I'm writing about:
> CREATE RULE r_my_dream AS ON SELECT TO xxx
> DO UPDATE xxx SET c_lastref = current_timestamp, c_refnum = c_refnum + 1
> WHERE xxx.oid = old.oid;
I don't think this is a real good idea. If it were enforced by the
database then you couldn't (just to take one example) produce a backup
dump without clobbering all your lastref information. And what of
queries like SELECT count(*) FROM table --- should that update the
timestamps of all the rows it reads, and if not why not?
I think you'll have to consider "access" (ie, an action that triggers
a lastref update) to be an application-defined concept, and that means
implementing the updates on the application side.
regards, tom lane
Abe,
How do you generate your article number? If you are using a SERIAL, then
you could change it to use a specific SEQUENCE and step the SEQUENCE by
some value other than one. Something like:
CREATE SEQUENCE article_sequence INCREMENT 100 MINVALUE 1 START 101;
CREATE TABLE article (
article_number int4 default nextval('article_sequence')
);
That would automatically generate article numbers 101, 201, 301, ...
thus leaving room for "inserted" articles.
On Fri, 22 Sep 2000, Abe Asghar wrote:
> Hi everyone,
>
> I have built a database that uses int4 as the unique identifier for a news
> database.
>
> Therefore an article has a identifier 1, the next one has 2 etc.
>
> Then I order them when they are displayed on the web reversely so that the
> last article added is at the top of the list.
>
> I now face a major problem. If we need to back dackdate an article - I
> can't. This is because the all the indexes are taken up ie 1, 2, 3......75
> odd records. If I want to add one between 50 and 51 ie 50.5, I cannot
> because the field is an int4.
>
> One idea I had to get around this was to create a new table with this column
> as a float and read and write all therecords in with a PHP script.
>
> Is there an easier way such as converting the column data type from a int4
> to a float.
>
> Thanks in advance.
> Abe.
>
On Fri, Sep 22, 2000 at 03:33:46PM +0100, Abe Asghar wrote:
> Hi everyone,
>
> I have built a database that uses int4 as the unique identifier for a news
> database.
>
> Therefore an article has a identifier 1, the next one has 2 etc.
>
> Then I order them when they are displayed on the web reversely so that the
> last article added is at the top of the list.
>
> I now face a major problem. If we need to back dackdate an article - I
> can't. This is because the all the indexes are taken up ie 1, 2, 3......75
> odd records. If I want to add one between 50 and 51 ie 50.5, I cannot
> because the field is an int4.
Perhaps you should store the date of the article in the database, and
then sort by that. You can even set the default for the field to be now() so
that it automatically gets set on insert, and you can override it during
backdate inserts or with later updates...
--
Adam Haberlach | A billion hours ago, human life appeared on
adam@newsnipple.com | earth. A billion minutes ago, Christianity
http://www.newsnipple.com | emerged. A billion Coca-Colas ago was
'88 EX500 | yesterday morning. -1996 Coca-Cola Ann. Rpt.
I'm looking for at least two good books on psql, are there any out ther, if so can someone suggest some to me? Mike
http://www.postgresql.org/docs/awbook.html Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Mike" <msears@vianet.ca> To: "Adam Haberlach" <adam@newsnipple.com>; <pgsql-general@postgresql.org> Sent: Friday, September 22, 2000 1:28 PM Subject: [GENERAL] PSQL Books > I'm looking for at least two good books on psql, are there any out ther, if so > can someone suggest some to me? > > Mike
ID shouldn't contain any additional informations (like order). It's ID. Use another field for sorting. At 16:33 22.9.2000 , Abe Asghar wrote: >Hi everyone, > >I have built a database that uses int4 as the unique identifier for a news >database. > >Therefore an article has a identifier 1, the next one has 2 etc. > >Then I order them when they are displayed on the web reversely so that the >last article added is at the top of the list. > >I now face a major problem. If we need to back dackdate an article - I >can't. This is because the all the indexes are taken up ie 1, 2, 3......75 >odd records. If I want to add one between 50 and 51 ie 50.5, I cannot >because the field is an int4. > >One idea I had to get around this was to create a new table with this column >as a float and read and write all therecords in with a PHP script. > >Is there an easier way such as converting the column data type from a int4 >to a float. > >Thanks in advance. >Abe.