Re: undeleteable records

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01