PostgreSQL Newbies - Unable to Delete a Row

Author

Unable to Delete a Row

Kevin Crenshaw

2005-05-25, 9:23 am

I am running Postgres v8.0.1 on Win XP Pro. When I try to delete a
record, which I know exists, I simply get 'DELETE 0' as a response.
Please Help!

Here is a little more info:

DELETE FROM CONTACTS WHERE CONTACTID = 853;

Query returned successfully: 0 rows affected, 62 ms execution time.
-- Executing query:
Select * from contacts where contactid = 853;

Total query runtime: 15 ms.
Data retrieval runtime: 16 ms.
1 rows retrieved.

-- Executing query:
EXPLAIN ANALYSE Delete from contacts where contactid = 853;

Total query runtime: 63 ms.
Data retrieval runtime: 0 ms.
3 rows retrieved.

Thanks in advance

kevin crenshaw

Kevin Crenshaw

2005-05-25, 11:23 am

Paul,

Thanks for responding. I tried your suggestion and it didn't work.
However, I found that when I removed a trigger from the contacts table,
the deletion completed successfully. Here is the text for the trigger
function:

CREATE OR REPLACE FUNCTION tg_remove_contact() RETURNS TRIGGER AS $$
BEGIN
UPDATE synchronization SET db_updated = current_timestamp,
action = 'exch_del'
WHERE OLD.contactid = synchronization.contactid;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER tg_del_contact BEFORE DELETE
ON contacts
FOR EACH ROW
EXECUTE PROCEDURE tg_remove_contact();

Do you know why this trigger would prevent me from deleting a row from
the contacts table?

Thanks!

kevin crenshaw

_____

From: paul@entropia.co.uk & #91;mailto:paul@entr
opia.co.uk]
Sent: Wednesday, May 25, 2005 10:32 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Unable to Delete a Row

Try:
DELETE FROM CONTACTS WHERE CONTACTID = '853';

>
> I am running Postgres v8.0.1 on Win XP Pro. When I try to delete a
record, which I know exists,
> I simply get ~DELETE 0(tm) as a response. Please Help!
>
> Here is a little more info:
>
> DELETE FROM CONTACTS WHERE CONTACTID = 853;
>
> Query returned successfully: 0 rows affected, 62 ms execution time.
> -- Executing query:
> Select * from contacts where contactid = 853;
>
>
> Total query runtime: 15 ms.
> Data retrieval runtime: 16 ms.
> 1 rows retrieved.
>
> -- Executing query:
> EXPLAIN ANALYSE Delete from contacts where contactid = 853;
>
>
> Total query runtime: 63 ms.
> Data retrieval runtime: 0 ms.
> 3 rows retrieved.
>
> Thanks in advance
>
> kevin crenshaw
>
>

Steve Tucknott

2005-05-25, 11:23 am

Kevin,
Is it anything to do with the following from the trigger procedure
section:
........
A trigger function must return either null or a record/row value having
exactly the structure of the table the trigger was fired for.

Row-level triggers fired BEFORE may return null to signal the trigger
manager to skip the rest of the operation for this row (i.e., subsequent
triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for
this row). If a nonnull value is returned then the operation proceeds
with that row value. Returning a row value different from the original
value of NEW alters the row that will be inserted or updated (but has no
direct effect in the DELETE case). To alter the row to be stored, it is
possible to replace single values directly in NEW and return the
modified NEW, or to build a complete new record/row to return.
.......
And the fact that you're returning null?

On Wed, 2005-05-25 at 17:16, Kevin Crenshaw wrote:
> Paul,
>
> Thanks for responding. I tried your suggestion and it didn't work.
> However, I found that when I removed a trigger from the contacts table,
> the deletion completed successfully. Here is the text for the trigger
> function:
>
> CREATE OR REPLACE FUNCTION tg_remove_contact() RETURNS TRIGGER AS $$
> BEGIN
> UPDATE synchronization SET db_updated = current_timestamp,
> action = 'exch_del'
> WHERE OLD.contactid = synchronization.contactid;
> RETURN NULL;
> END;
> $$ LANGUAGE 'plpgsql';
>
> CREATE TRIGGER tg_del_contact BEFORE DELETE
> ON contacts
> FOR EACH ROW
> EXECUTE PROCEDURE tg_remove_contact();

>
> Do you know why this trigger would prevent me from deleting a row from
> the contacts table?
>
> Thanks!
>
>
> kevin crenshaw
>
>
> _____
>
> From: paul@entropia.co.uk & #91;mailto:paul@entr
opia.co.uk]
> Sent: Wednesday, May 25, 2005 10:32 AM
> To: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Unable to Delete a Row
>
> Try:
> DELETE FROM CONTACTS WHERE CONTACTID = '853';
>
> record, which I know exists,
>
--

Regards,

Steve Tucknott
ReTSol Ltd

DDI 01903 828769
MOBILE 07736715772

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Derrick Betts

2005-05-25, 11:23 am

I think that you need to "RETURN NEW;" in your tg_remove_contact() function rather than "RETURN NULL;"

Derrick
----- Original Message -----
From: Kevin Crenshaw
To: paul@entropia.co.uk ; pgsql-novice@postgresql.org
Sent: Wednesday, May 25, 2005 10:16 AM
Subject: Re: [NOVICE] Unable to Delete a Row

Paul,

Thanks for responding. I tried your suggestion and it didn't work. However, I found that when I removed a trigger from the contacts table, the deletion completed successfully. Here is the text for the trigger function:

CREATE OR REPLACE FUNCTION tg_remove_contact() RETURNS TRIGGER AS $$

BEGIN

UPDATE synchronization SET db_updated = current_timestamp, action = 'exch_del'

WHERE OLD.contactid = synchronization.contactid;

RETURN NULL;

END;

$$ LANGUAGE 'plpgsql';

CREATE TRIGGER tg_del_contact BEFORE DELETE

ON contacts

FOR EACH ROW

EXECUTE PROCEDURE tg_remove_contact();

Do you know why this trigger would prevent me from deleting a row from the contacts table?

Thanks!

kevin crenshaw

------------------------------------------------------------------------------

From: paul@entropia.co.uk & #91;mailto:paul@entr
opia.co.uk]
Sent: Wednesday, May 25, 2005 10:32 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Unable to Delete a Row

Try:

DELETE FROM CONTACTS WHERE CONTACTID = '853';

>

> I am running Postgres v8.0.1 on Win XP Pro. When I try to delete a record, which I know exists,

> I simply get ~DELETE 0T as a response. Please Help!

>

> Here is a little more info:

>

> DELETE FROM CONTACTS WHERE CONTACTID = 853;

>

> Query returned successfully: 0 rows affected, 62 ms execution time.

> -- Executing query:

> Select * from contacts where contactid = 853;

>

>

> Total query runtime: 15 ms.

> Data retrieval runtime: 16 ms.

> 1 rows retrieved.

>

> -- Executing query:

> EXPLAIN ANALYSE Delete from contacts where contactid = 853;

>

>

> Total query runtime: 63 ms.

> Data retrieval runtime: 0 ms.

> 3 rows retrieved.

>

> Thanks in advance

>

> kevin crenshaw

>

>

Tom Lane

2005-05-25, 8:24 pm

"Derrick Betts" <derrick@grifflink.com> writes:
> I think that you need to "RETURN NEW;" in your tg_remove_contact()
> function rather than "RETURN NULL;"

Actually, since it's a DELETE trigger, there is no NEW row;
he needs to RETURN OLD, instead, to give permission for the delete
to continue.

Given what the trigger is doing (ie, propagating the update to another
table), he might be better off to use an AFTER DELETE trigger instead
of BEFORE DELETE anyway --- in which case the return value doesn't
matter and so RETURN NULL is fine. The point of AFTER DELETE is that
you can be certain the delete really did happen, and wasn't cancelled
by some other BEFORE DELETE trigger.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Kevin Crenshaw

2005-05-30, 9:24 am

Thanks for all of your help on this one!

kevin crenshaw

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, May 25, 2005 5:26 PM
To: Derrick Betts
Cc: Kevin Crenshaw; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Unable to Delete a Row

"Derrick Betts" <derrick@grifflink.com> writes:
> I think that you need to "RETURN NEW;" in your tg_remove_contact()
> function rather than "RETURN NULL;"

Actually, since it's a DELETE trigger, there is no NEW row;
he needs to RETURN OLD, instead, to give permission for the delete
to continue.

Given what the trigger is doing (ie, propagating the update to another
table), he might be better off to use an AFTER DELETE trigger instead
of BEFORE DELETE anyway --- in which case the return value doesn't
matter and so RETURN NULL is fine. The point of AFTER DELETE is that
you can be certain the delete really did happen, and wasn't cancelled
by some other BEFORE DELETE trigger.

regards, tom lane

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

Sponsored Links