Professional Web Applications Themes

A "cascade on delete" constraints deletes AFTER the source is gone?? - PostgreSQL / PGSQL

I noticed that when you do a constraint and tell it "cascade on delete", it will do so only AFTER that the source is deleted. Can I tell it somehow to cascade BEFORE the source is gone? -- ICQ: 1912453 AIM: VitalyB1984 MSN: [email]tmdagenthotmail.com[/email] Yahoo!: VitalyBe ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster...

  1. #1

    Default A "cascade on delete" constraints deletes AFTER the source is gone??

    I noticed that when you do a constraint and tell it "cascade on
    delete", it will do so only AFTER that the source is deleted. Can I
    tell it somehow to cascade BEFORE the source is gone?


    --
    ICQ: 1912453
    AIM: VitalyB1984
    MSN: [email]tmdagenthotmail.com[/email]
    Yahoo!: VitalyBe

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Vitaly Belman Guest

  2. #2

    Default Re: A "cascade on delete" constraints deletes AFTER the source is gone??

    On Sun, Dec 19, 2004 at 03:20:19PM +0200, Vitaly Belman wrote:
    > I noticed that when you do a constraint and tell it "cascade on
    > delete", it will do so only AFTER that the source is deleted. Can I
    > tell it somehow to cascade BEFORE the source is gone?
    What problem are you trying to solve?

    --
    Michael Fuhr
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Michael Fuhr Guest

  3. #3

    Default Re: A "cascade on delete" constraints deletes AFTER the source is gone??

    It's a bit complex.

    I have two tables. "Books" and "Book_Authors" (which links between
    book_id and author_id).
    Book_authors has a foreign key on book_id to the Books table. On key
    violation it is set to delete the rows (if a book is deleted, it
    should't be linked to any authors).

    In Book_Authors I also have a trigger on DELETE. When a book is
    unlinked from an author, then the author vote_count should be reduced
    (as the author vote_count is the sum of all votes of his books).

    The problem is that when a book is deleted and then the trigger tried
    to get the number of it votes, it returns NULL, as the book is already
    gone and so its data. If it was a simple matter of triggers I could
    play with BEFORE/AFTER, but since it is constraints issue, it seems to
    be all happening AFTER the deletion.

    On Sun, 19 Dec 2004 12:23:09 -0700, Michael Fuhr <mikefuhr.org> wrote:
    > On Sun, Dec 19, 2004 at 03:20:19PM +0200, Vitaly Belman wrote:
    >
    > > I noticed that when you do a constraint and tell it "cascade on
    > > delete", it will do so only AFTER that the source is deleted. Can I
    > > tell it somehow to cascade BEFORE the source is gone?
    >
    > What problem are you trying to solve?
    >
    > --
    > Michael Fuhr
    > [url]http://www.fuhr.org/~mfuhr/[/url]
    >

    --
    ICQ: 1912453
    AIM: VitalyB1984
    MSN: [email]tmdagenthotmail.com[/email]
    Yahoo!: VitalyBe

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Vitaly Belman Guest

  4. #4

    Default Re: A "cascade on delete" constraints deletes AFTER the source is gone??

    On Mon, Dec 20, 2004 at 09:56:35AM +0200, Vitaly Belman wrote:
    > I have two tables. "Books" and "Book_Authors" (which links between
    > book_id and author_id).
    Is there a third table, perhaps Authors?
    > Book_authors has a foreign key on book_id to the Books table. On key
    > violation it is set to delete the rows (if a book is deleted, it
    > should't be linked to any authors).
    >
    > In Book_Authors I also have a trigger on DELETE. When a book is
    > unlinked from an author, then the author vote_count should be reduced
    > (as the author vote_count is the sum of all votes of his books).
    Which table has vote_count -- the Authors table that I'm guessing
    exists? Where does vote_count's value come from? A field in Books?
    Is there a reason you're maintaining vote_count instead of querying
    for it with an aggregate like SUM or COUNT?
    > The problem is that when a book is deleted and then the trigger tried
    > to get the number of it votes, it returns NULL, as the book is already
    > gone and so its data. If it was a simple matter of triggers I could
    > play with BEFORE/AFTER, but since it is constraints issue, it seems to
    > be all happening AFTER the deletion.
    Is there a reason you can't maintain vote_count with a trigger on
    Books?

    Could you post a minimal but complete example (CREATE statements
    and INSERT or COPY statements with sample data) so we can get a
    better idea of what you're trying to do?

    --
    Michael Fuhr
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Michael Fuhr Guest

  5. #5

    Default Re: A "cascade on delete" constraints deletes AFTER the source is gone??

    Hi All!

    I need to make function which emulates table and returns setof columns
    of this "table"

    for example, i'm making query: "SELECT * FROM my_table(user_id)"

    and function
    CREATE OR REPLACE FUNCTION my_table (integer)
    RETURNS setof text
    AS '
    DECLARE
    check_date date;
    max_date date;
    r record;

    BEGIN
    max_date := $2;
    check_date := $3;

    WHILE (check_date >= max_date) LOOP
    table_nam := ''table_''||to_char(check_date, ''YYYYMMDD'');

    FOR r IN EXECUTE ''SELECT COUNT(*) as cnt,
    AVG(amount) as avg_amount, SUM(amount) as sum_amount
    FROM ''||table_nam||''
    WHERE user_id = ''||$1||'' ''
    LOOP
    RETURN NEXT r.cnt || r.avg_amount || r.sum_amount;
    END LOOP;

    check_date := check_date - interval ''1 day'';
    END LOOP;

    RETURN;
    END;'
    LANGUAGE 'plpgsql';


    As a result i need to get a records, and will be able to use each
    column from it

    But my function doesn't work
    What is wrong?

    Thanx


    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    ON.KG Guest

  6. #6

    Default Re: A "cascade on delete" constraints deletes AFTER the source is gone??

    On Mon, Dec 20, 2004 at 04:31:59PM +0300, ON.KG wrote:
    > I need to make function which emulates table and returns setof columns
    > of this "table"
    Please start a new thread with a descriptive subject -- the message
    you posted was a followup to a thread covering a different topic.

    --
    Michael Fuhr
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Michael Fuhr Guest

Similar Threads

  1. Objects Created Using "Unite" Action Crash Program When Trying to Delete
    By Christopher_Kirby@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 1
    Last Post: May 10th, 08:03 PM
  2. No "Delete Pages" Option in Bookmarks' Right-Click Context Menu
    By Jack Danniel in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 13th, 11:02 PM
  3. Replies: 2
    Last Post: September 15th, 04:35 AM
  4. How do I access the Mathias Vejerslev "How to delete/reset Photoshop preferences"?
    By Kevin Kelly in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 0
    Last Post: August 30th, 08:30 AM
  5. How do you simulate "." or "source" in a perl script ?
    By John Strauss in forum Perl / CGI
    Replies: 4
    Last Post: August 11th, 10:46 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139