Thread: 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 recordby specifying a different field value, with the same results, as well as trying all of this with multiple records inthe same table. None of the records will delete. I've copied the table, the select statement showing the record I wantto delete in that table, and the delete statement itself. I'm currently using postgres-sql 7.1 rc2. (I know, I know...butit's a development server.) Thanks!<br /><br /> -T<br /><br /><font face="Courier, Courier">DELETE FROM ec_gift_certificatesWHERE gift_certificate_id = 9;<br /> DELETE 0<br /><br /> SELECT gift_certificate_id FROM ec_gift_certificatesWHERE gift_certificate_id = 9;<br /><br /> gift_certificate_id <br /> ---------------------<br /> 9<br /> (1 row)<br /><br /><br /><br /> \d ec_gift_certificates<br /> Table "ec_gift_certificates"<br/> Attribute | Type | Modifier <br /> ------------------------+--------------------------+-------------<br/> gift_certificate_id | integer | not null<br /> gift_certificate_state | character varying(50) | not null<br /> amount | numeric(30,6) | not null<br /> amount_remaining_p | character(1) | default't'<br /> issue_date | timestamp with time zone | <br /> authorized_date | timestamp with timezone | <br /> claimed_date | timestamp with time zone | <br /> issued_by | integer | <br /> purchased_by | integer | <br /> expires | timestampwith time zone | <br /> user_id | integer | <br /> claim_check | charactervarying(50) | <br /> certificate_message | character varying(200) | <br /> certificate_to | charactervarying(100) | <br /> certificate_from | character varying(100) | <br /> recipient_email | charactervarying(100) | <br /> voided_date | timestamp with time zone | <br /> voided_by | integer | <br /> reason_for_void | character varying(4000) | <br /> last_modified | timestampwith time zone | not null<br /> last_modifying_user | integer | not null<br /> modified_ip_address | character varying(20) | not null<br /> Indices: ec_gc_by_amount_remaining,<br /> ec_gc_by_claim_check,<br/> ec_gc_by_state,<br /> ec_gc_by_user,<br /> ec_gift_certificates_pkey<br/> Constraints: ((amount_remaining_p = 'f'::bpchar) OR (amount_remaining_p = 't'::bpchar))<br/> ((user_id NOTNULL) OR (claim_check NOTNULL))<br /><br /></font><p> ***********************************************<br/> Tara Cooper<br /> Payment Alliance, Inc.<br /> carat@pantz.com
Tara Cooper <carat@pantz.com> writes:
> <html>
> 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.
Please don't send HTML mail to the lists...
I wonder whether you haven't got a foreign key relationship that forbids
the delete. Or a miswritten user-defined ON DELETE trigger.
regards, tom lane
What I noticed was that the select came up with one row, with an apparently
blank gift_certificate_id. That field was declared as INT NOT NULL, yet it
shows up blank. Wouldn't that indicate that something got in there that wasn't
supposed to?
Tom Lane <tgl%sss.pgh.pa.us@interlock.lexmark.com> on 07/17/2001 04:49:45 PM
To: Tara Cooper <carat%pantz.com@interlock.lexmark.com>
cc: pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] undeleteable records
Tara Cooper <carat@pantz.com> writes:
> <html>
> 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.
Please don't send HTML mail to the lists...
I wonder whether you haven't got a foreign key relationship that forbids
the delete. Or a miswritten user-defined ON DELETE trigger.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
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
Hey Justin - Tara and I figured out that a trigger on this table - which is supposed to archive deleted information - blocks deletes altogether. We lifted this trigger from open ACS... we are having some difficulties with all such triggers. When we re-create the table w/o the trigger all is well. Hope to get some time to figure out what is wrong with these damn triggers! ;) BTW, I read your post to Dr. Evil re: rules. I am just beginning to play around with rules - found your message to be helpful. Thanks! -Ryan Mahoney At 12:28 PM 7/18/01 +1000, Justin Clift wrote: >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 --- Outgoing 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
Ryan Mahoney wrote:
> Hey Justin - Tara and I figured out that a trigger on this table - which is
> supposed to archive deleted information - blocks deletes altogether. We
> lifted this trigger from open ACS... we are having some difficulties with
> all such triggers. When we re-create the table w/o the trigger all is
> well. Hope to get some time to figure out what is wrong with these damn
> triggers! ;)
All BEFORE triggers suppress the original operation when
returning NULL instead of NEW/OLD (NEW on INSERT/UPDATE, OLD
on DELETE). The return value of AFTER triggers is ignored.
Could it be that the triggers where designed to be AFTER
triggers and you accidentially declared them as BEFORE?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
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) Looks strange. Any delete triggers setup on ec_gift_certificates? What does your server log show? Regards, Ed Loehr