Re: undeleteable records - Mailing list pgsql-general

Hi Tara,

Is it possible you have a RULE defined (CREATE RULE ...) which is
rewriting the query and preventing the record from being deleted?

At those times when creating entries in a table which need to be
permanently there (not to ever be deleted), I use RULEs to re-write the
query so it doesn't delete those entries I want protected.  What you are
experiencing is the result I get when using such rules.

i.e. SQL query that should work, but isn't.  :)

What do you think?

Regards and best wishes,

Justin Clift

Tara Cooper wrote:
>
> Has anyone encountered a problem where a delete sql statement works
> (no parser errors) but always returns a "DELETE 0" value, even when
> I'm sure the record I'm selecting for delete is in the table?  I have
> also tried deleting the same record by specifying a different field
> value, with the same results, as well as trying all of this with
> multiple records in the same table.  None of the records will delete.
> I've copied the table, the select statement showing the record I want
> to delete in that table, and the delete statement itself.  I'm
> currently using postgres-sql 7.1 rc2. (I know, I know...but it's a
> development server.) Thanks!
>
> -T
>
> DELETE FROM ec_gift_certificates WHERE gift_certificate_id = 9;
> DELETE 0
>
> SELECT gift_certificate_id FROM ec_gift_certificates WHERE
> gift_certificate_id = 9;
>
>  gift_certificate_id
> ---------------------
>                    9
> (1 row)
>
> \d ec_gift_certificates
>                   Table "ec_gift_certificates"
>        Attribute        |           Type           |  Modifier
> ------------------------+--------------------------+-------------
>  gift_certificate_id    | integer                  | not null
>  gift_certificate_state | character varying(50)    | not null
>  amount                 | numeric(30,6)            | not null
>  amount_remaining_p     | character(1)             | default 't'
>  issue_date             | timestamp with time zone |
>  authorized_date        | timestamp with time zone |
>  claimed_date           | timestamp with time zone |
>  issued_by              | integer                  |
>  purchased_by           | integer                  |
>  expires                | timestamp with time zone |
>  user_id                | integer                  |
>  claim_check            | character varying(50)    |
>  certificate_message    | character varying(200)   |
>  certificate_to         | character varying(100)   |
>  certificate_from       | character varying(100)   |
>  recipient_email        | character varying(100)   |
>  voided_date            | timestamp with time zone |
>  voided_by              | integer                  |
>  reason_for_void        | character varying(4000)  |
>  last_modified          | timestamp with time zone | not null
>  last_modifying_user    | integer                  | not null
>  modified_ip_address    | character varying(20)    | not null
> Indices: ec_gc_by_amount_remaining,
>          ec_gc_by_claim_check,
>          ec_gc_by_state,
>          ec_gc_by_user,
>          ec_gift_certificates_pkey
> Constraints: ((amount_remaining_p = 'f'::bpchar) OR
> (amount_remaining_p = 't'::bpchar))
>              ((user_id NOTNULL) OR (claim_check NOTNULL))
>
> ***********************************************
> Tara Cooper
> Payment Alliance, Inc.
> carat@pantz.com