subject:"\[GENERAL\] DELETE"

   Hi Ioana and David.

   Thank you. Yes, I can see the problem. I will look into your suggestions.

 Leif


- Original Message -
> On Friday, September 18, 2015, Leif Jensen  wrote:
> 
> >Hello Laurenz,
> >
> >Thank you for you suggestion. I really want to aviod that someone
> > 'accidentally' deletes too much by typing (programming) a not full
> > qualified DELETE ... statement. In your case one would have to always use
> > the delete function, but no restrictions on using the DELETE statement.
> >
> >
> There is no way you can prevent a superuser from shooting themselves in the
> foot.  For anyone else you can enforce use of the function to perform the
> delete.
> 
> You could make a field called ok-to-delete and add a partial unique index
> on it so that only a single record can be marked ok to delete at a time and
> then have your trigger abort if it tries to delete a field without the ok
> to delete field set to true.
> 
> David J.
> 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete trigger

2015-09-18 Thread David G. Johnston

On Friday, September 18, 2015, Leif Jensen  wrote:

>Hello Laurenz,
>
>Thank you for you suggestion. I really want to aviod that someone
> 'accidentally' deletes too much by typing (programming) a not full
> qualified DELETE ... statement. In your case one would have to always use
> the delete function, but no restrictions on using the DELETE statement.
>
>
There is no way you can prevent a superuser from shooting themselves in the
foot.  For anyone else you can enforce use of the function to perform the
delete.

You could make a field called ok-to-delete and add a partial unique index
on it so that only a single record can be marked ok to delete at a time and
then have your trigger abort if it tries to delete a field without the ok
to delete field set to true.

David J.

Re: [GENERAL] Delete trigger

2015-09-18 Thread Ioana Danes

It depends on the size of the table and the frequency of updates, deletes
but cold consider an audit table with triggers for update, delete and
truncate. At least you have a way to recover deleted records.

Ioana


On Fri, Sep 18, 2015 at 5:52 AM, Leif Jensen  wrote:

>Hello Laurenz,
>
>Thank you for you suggestion. I really want to aviod that someone
> 'accidentally' deletes too much by typing (programming) a not full
> qualified DELETE ... statement. In your case one would have to always use
> the delete function, but no restrictions on using the DELETE statement.
>
>  Leif
>
>
> - Original Message -
> > Leif Jensen wrote:
> > >If I do "DELETE FROM devicegroup WHERE group=1" I do not want to
> delete
> > >anything. I only want to
> > > delete if I do "DELETE FROM devicegroup WHERE groupid=x AND ctrlid=y
> AND
> > > userid=z". I don't wanna let
> > > anyone delete more than 1 row at a time.
> >
> > I can't think of a way to do that with a trigger.
> >
> > I'd write a
> >   FUNCTION delete_devicegroup(groupid integer, ctrlid integer, userid
> >   integer)
> > RETURNS void CALLED ON NULL INPUT VOLATILE SECURITY DEFINER
> > that enables the user to delete a row and checks that all arguments
> > are NOT NULL.  The user doesn't get privileges to DELETE from the table
> > directly.
> >
> > Yours,
> > Laurenz Albe
> >
> >
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Delete trigger

   Hello Laurenz,

   Thank you for you suggestion. I really want to aviod that someone 
'accidentally' deletes too much by typing (programming) a not full qualified 
DELETE ... statement. In your case one would have to always use the delete 
function, but no restrictions on using the DELETE statement.

 Leif


- Original Message -
> Leif Jensen wrote:
> >If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete
> >anything. I only want to
> > delete if I do "DELETE FROM devicegroup WHERE groupid=x AND ctrlid=y AND
> > userid=z". I don't wanna let
> > anyone delete more than 1 row at a time.
> 
> I can't think of a way to do that with a trigger.
> 
> I'd write a
>   FUNCTION delete_devicegroup(groupid integer, ctrlid integer, userid
>   integer)
> RETURNS void CALLED ON NULL INPUT VOLATILE SECURITY DEFINER
> that enables the user to delete a row and checks that all arguments
> are NOT NULL.  The user doesn't get privileges to DELETE from the table
> directly.
> 
> Yours,
> Laurenz Albe
> 
> 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete trigger

2015-09-18 Thread Albe Laurenz

Leif Jensen wrote:
>If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete 
> anything. I only want to
> delete if I do "DELETE FROM devicegroup WHERE groupid=x AND ctrlid=y AND 
> userid=z". I don't wanna let
> anyone delete more than 1 row at a time.

I can't think of a way to do that with a trigger.

I'd write a
  FUNCTION delete_devicegroup(groupid integer, ctrlid integer, userid integer)
RETURNS void CALLED ON NULL INPUT VOLATILE SECURITY DEFINER
that enables the user to delete a row and checks that all arguments
are NOT NULL.  The user doesn't get privileges to DELETE from the table 
directly.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete trigger

   Hi Charles,

   If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete 
anything. I only want to delete if I do "DELETE FROM devicegroup WHERE 
groupid=x AND ctrlid=y AND userid=z". I don't wanna let anyone delete more than 
1 row at a time.

 Leif


- Original Message -
> Hello
> 
> Not sure I get it right, but all three fields are not nullable. So they will
> always have a value, which is what I understand of "are specified".
> What do you need the trigger for in that case?
> 
> Bye
> Charles
> 
> > -Original Message-
> > From: pgsql-general-ow...@postgresql.org
> > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Leif Jensen
> > Sent: Freitag, 18. September 2015 10:23
> > To: pgsql-general 
> > Subject: [GENERAL] Delete trigger
> > 
> >Hi,
> > 
> >I am running PostgreSQL 9.3.4. I have a simple table with 3 fields:
> > 
> > CREATE TABLE devicegroup (
> > groupid integer NOT NULL,
> > ctrlid integer NOT NULL,
> > userid integer NOT NULL
> > );
> > ALTER TABLE ONLY devicegroup
> > ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid);
> > 
> >I want to make sure that records are only deleted when all 3 fields are
> >specified, so I tried make a trigger:
> > 
> > CREATE TRIGGER userDev_trig INSTEAD OF DELETE ON admdevgrid FOR EACH
> > STATEMENT
> >   EXECUTE PROCEDURE deleteUserDev();
> > 
> > which could check for NOT NULL on the 3 fields before actual doing the
> > delete. Unfortunately this is not possible to
> > do FOR EACH STATEMENT, and FOR EACH ROW would not give me the chance to
> > check for NOT NULL.
> > 
> >Any ideas ?
> > 
> >  Leif
> > 
> > 
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> 
> 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete trigger

2015-09-18 Thread Charles Clavadetscher

Hello

Not sure I get it right, but all three fields are not nullable. So they will 
always have a value, which is what I understand of "are specified".
What do you need the trigger for in that case?

Bye
Charles

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Leif Jensen
> Sent: Freitag, 18. September 2015 10:23
> To: pgsql-general 
> Subject: [GENERAL] Delete trigger
> 
>Hi,
> 
>I am running PostgreSQL 9.3.4. I have a simple table with 3 fields:
> 
> CREATE TABLE devicegroup (
> groupid integer NOT NULL,
> ctrlid integer NOT NULL,
> userid integer NOT NULL
> );
> ALTER TABLE ONLY devicegroup
> ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid);
> 
>I want to make sure that records are only deleted when all 3 fields are 
> specified, so I tried make a trigger:
> 
> CREATE TRIGGER userDev_trig INSTEAD OF DELETE ON admdevgrid FOR EACH STATEMENT
> EXECUTE PROCEDURE deleteUserDev();
> 
> which could check for NOT NULL on the 3 fields before actual doing the 
> delete. Unfortunately this is not possible to
> do FOR EACH STATEMENT, and FOR EACH ROW would not give me the chance to check 
> for NOT NULL.
> 
>Any ideas ?
> 
>  Leif
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Delete trigger

   Hi,

   I am running PostgreSQL 9.3.4. I have a simple table with 3 fields:

CREATE TABLE devicegroup (
groupid integer NOT NULL,
ctrlid integer NOT NULL,
userid integer NOT NULL
);
ALTER TABLE ONLY devicegroup
ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid);

   I want to make sure that records are only deleted when all 3 fields are 
specified, so I tried make a trigger:

CREATE TRIGGER userDev_trig INSTEAD OF DELETE ON admdevgrid FOR EACH STATEMENT
  EXECUTE PROCEDURE deleteUserDev();

which could check for NOT NULL on the 3 fields before actual doing the delete. 
Unfortunately this is not possible to do FOR EACH STATEMENT, and FOR EACH ROW 
would not give me the chance to check for NOT NULL.

   Any ideas ?

 Leif


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-27 Thread Melvin Davidson

For crying out loud GET OVER IT! You've been given a very reasonable and
quick solution to your problem.
You can either
1. Keep crying and moaning until someone changes the rules.
2. Give up and port to another database.
3. Write the triggers and solve your problem!

On Fri, Jul 24, 2015 at 5:27 AM, Tim Smith  wrote:

> On 23 July 2015 at 19:25, Scott Marlowe  wrote:
> > stick to triggers, they're faster
>
>
> Erm, not according to your beloved manual !!!
>
> 38.7. Rules Versus Triggers
>
> " a rule issuing one extra command is likely to be faster than a trigger"
> "The summary is, rules will only be significantly slower than triggers
> if their actions result in large and badly qualified joins, a
> situation where the planner fails."
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-27 Thread Tim Smith

>
> Just in case it has not been made obvious yet, rules are silently
> deprecated. They still exist because views depend on them, but it is
> generally considered best practices to not use them outside that realm.


Well, the manual doesn't seem to reflect that fact.

If that's how the developers feel about rules, i.e they can't be
bothered to make any changes to the rules code any more, no matter how
minor (e.g. this TRUNCATE issue), then you should explicitly state in
the manual that they are depreciated, and stop making stupid
statements like "so if many rows are affected in one statement, a rule
issuing one extra command is likely to be faster than a trigger" that
encourage people to use rules !


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-27 Thread Tim Smith

On 23 July 2015 at 19:25, Scott Marlowe  wrote:
> stick to triggers, they're faster


Erm, not according to your beloved manual !!!

38.7. Rules Versus Triggers

" a rule issuing one extra command is likely to be faster than a trigger"
"The summary is, rules will only be significantly slower than triggers
if their actions result in large and badly qualified joins, a
situation where the planner fails."


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-24 Thread Joshua D. Drake



On 07/24/2015 02:32 AM, Andres Freund wrote:


On 2015-07-24 10:29:21 +0100, Tim Smith wrote:

That's not the point.  Backups are important, but so is the concept of
various layers of anti-fat-finger protection.   Restoring off backups
should be last resort, not first.


Oh, comeon. Install a TRUNCATE trigger and let this thread die.

Andres



Please god, +1.

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-24 Thread Scott Marlowe

On Fri, Jul 24, 2015 at 3:27 AM, Tim Smith  wrote:
> On 23 July 2015 at 19:25, Scott Marlowe  wrote:
>> stick to triggers, they're faster
>
>
> Erm, not according to your beloved manual !!!
>
> 38.7. Rules Versus Triggers
>
> " a rule issuing one extra command is likely to be faster than a trigger"
> "The summary is, rules will only be significantly slower than triggers
> if their actions result in large and badly qualified joins, a
> situation where the planner fails."

So, that's the sum total of what you took away from my post?
Nevermind, I'll leave you alone.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-24 Thread Adrian Klaver


On 07/24/2015 02:24 AM, Tim Smith wrote:


Just in case it has not been made obvious yet, rules are silently
deprecated. They still exist because views depend on them, but it is
generally considered best practices to not use them outside that realm.



Well, the manual doesn't seem to reflect that fact.

If that's how the developers feel about rules, i.e they can't be
bothered to make any changes to the rules code any more, no matter how
minor (e.g. this TRUNCATE issue), then you should explicitly state in
the manual that they are depreciated, and stop making stupid
statements like "so if many rows are affected in one statement, a rule
issuing one extra command is likely to be faster than a trigger" that
encourage people to use rules !



Seems that section needs editing to make it clearer that this depends on 
what type of trigger you use. Or as it states here:


http://www.postgresql.org/docs/9.4/interactive/sql-createtrigger.html
"
A trigger that is marked FOR EACH ROW is called once for every row that 
the operation modifies. For example, a DELETE that affects 10 rows will 
cause any ON DELETE triggers on the target relation to be called 10 
separate times, once for each deleted row. In contrast, a trigger that 
is marked FOR EACH STATEMENT only executes once for any given operation, 
regardless of how many rows it modifies (in particular, an operation 
that modifies zero rows will still result in the execution of any 
applicable FOR EACH STATEMENT triggers).

"

At any rate you know what the situation is. Getting increasingly 
belligerent is not going to help that situation and the energy would 
seem to me better spent on adapting to reality and moving on.


--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-24 Thread Andrew Sullivan

On Fri, Jul 24, 2015 at 10:24:55AM +0100, Tim Smith wrote:
> 
> If that's how the developers feel about rules, i.e they can't be
> bothered to make any changes to the rules code any more, no matter how
> minor (e.g. this TRUNCATE issue)

Who is this "they"?  As I think I suggested in another mail, if you
think this is trivial and easy then I think you should propose the
patch to solve it.  I understand what you're saying; I think the
solution is self-evident (add a statement trigger that captures
TRUNCATE and DO INSTEAD NOTHING), so I just wouldn't be bothered to
fix this.  But I suspect things are the way they are partly because
nobody proposed or implemented a patch for this behaviour before.

The manual is also quite clear in what statements you can
write rules about; by implication, other statements are not covered,
so I'm not actually sure why you think the manual is misleading.

Best regards,

A
-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-24 Thread Andres Freund

On 2015-07-24 10:29:21 +0100, Tim Smith wrote:
> That's not the point.  Backups are important, but so is the concept of
> various layers of anti-fat-finger protection.   Restoring off backups
> should be last resort, not first.

Oh, comeon. Install a TRUNCATE trigger and let this thread die.

Andres


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-24 Thread Tim Smith

On 24 July 2015 at 01:37, Rob Sargent  wrote:

> Fair enough but both blackhats and the authorized are just as likely to drop
> the database as truncate something (intentionally or not) and backups
> stashed everywhere is the first order of business.


That's not the point.  Backups are important, but so is the concept of
various layers of anti-fat-finger protection.   Restoring off backups
should be last resort, not first.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate


On 07/23/2015 05:37 PM, Rob Sargent wrote:

On 07/23/2015 06:27 PM, Adrian Klaver wrote:

On 07/23/2015 05:08 PM, Rob Sargent wrote:

On 07/23/2015 04:15 PM, Karsten Hilbert wrote:

On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote:


I'm suggesting OP might find changing truncate statements to deletes
(without a where clause) a simpler solution. Something has to change.

Well, OP isn't looking for a solution to "delete all rows"
but rather to _prevent_ deletion.

Tim can't go forth and tell Blackhats to "please use DELETE
rather than TRUNCATE", right ?

AFAICT it'd be more useful to advise OP to revoke TRUNCATE
rights on tables.

Karsten

Not sure about Tim and the Blackhats (there's a band name in there
somewhere) but Wouldn't OP have exact same code to fix, one way or
another?



I think the point was, the OP(Tim) might not have access to the code
that is trying to TRUNCATE. This could be because it is coming from
authorized users who are writing their own code or unauthorized
users(Blackhats) who are trying to sneak code in.



Fair enough but both blackhats and the authorized are just as likely to
drop the database as truncate something (intentionally or not) and
backups stashed everywhere is the first order of business.


Well that is a different crisis and not covered by rules or triggers:)


--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate


On 07/23/2015 06:27 PM, Adrian Klaver wrote:

On 07/23/2015 05:08 PM, Rob Sargent wrote:

On 07/23/2015 04:15 PM, Karsten Hilbert wrote:

On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote:


I'm suggesting OP might find changing truncate statements to deletes
(without a where clause) a simpler solution. Something has to change.

Well, OP isn't looking for a solution to "delete all rows"
but rather to _prevent_ deletion.

Tim can't go forth and tell Blackhats to "please use DELETE
rather than TRUNCATE", right ?

AFAICT it'd be more useful to advise OP to revoke TRUNCATE
rights on tables.

Karsten

Not sure about Tim and the Blackhats (there's a band name in there
somewhere) but Wouldn't OP have exact same code to fix, one way or 
another?




I think the point was, the OP(Tim) might not have access to the code 
that is trying to TRUNCATE. This could be because it is coming from 
authorized users who are writing their own code or unauthorized 
users(Blackhats) who are trying to sneak code in.



Fair enough but both blackhats and the authorized are just as likely to 
drop the database as truncate something (intentionally or not) and 
backups stashed everywhere is the first order of business.

Re: [GENERAL] Delete rule does not prevent truncate


On 07/23/2015 05:08 PM, Rob Sargent wrote:

On 07/23/2015 04:15 PM, Karsten Hilbert wrote:

On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote:


I'm suggesting OP might find changing truncate statements to deletes
(without a where clause) a simpler solution. Something has to change.

Well, OP isn't looking for a solution to "delete all rows"
but rather to _prevent_ deletion.

Tim can't go forth and tell Blackhats to "please use DELETE
rather than TRUNCATE", right ?

AFAICT it'd be more useful to advise OP to revoke TRUNCATE
rights on tables.

Karsten

Not sure about Tim and the Blackhats (there's a band name in there
somewhere) but Wouldn't OP have exact same code to fix, one way or another?



I think the point was, the OP(Tim) might not have access to the code 
that is trying to TRUNCATE. This could be because it is coming from 
authorized users who are writing their own code or unauthorized 
users(Blackhats) who are trying to sneak code in.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate


On 07/23/2015 04:15 PM, Karsten Hilbert wrote:

On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote:


I'm suggesting OP might find changing truncate statements to deletes
(without a where clause) a simpler solution. Something has to change.

Well, OP isn't looking for a solution to "delete all rows"
but rather to _prevent_ deletion.

Tim can't go forth and tell Blackhats to "please use DELETE
rather than TRUNCATE", right ?

AFAICT it'd be more useful to advise OP to revoke TRUNCATE
rights on tables.

Karsten
Not sure about Tim and the Blackhats (there's a band name in there 
somewhere) but Wouldn't OP have exact same code to fix, one way or another?

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-23 Thread Karsten Hilbert

On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote:

> I'm suggesting OP might find changing truncate statements to deletes
> (without a where clause) a simpler solution. Something has to change.

Well, OP isn't looking for a solution to "delete all rows"
but rather to _prevent_ deletion.

Tim can't go forth and tell Blackhats to "please use DELETE
rather than TRUNCATE", right ?

AFAICT it'd be more useful to advise OP to revoke TRUNCATE
rights on tables.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 12:25 PM, Adrian Klaver wrote:

On 07/23/2015 11:15 AM, Rob Sargent wrote:

On 07/23/2015 12:09 PM, Adrian Klaver wrote:

On 07/23/2015 04:57 AM, Tim Smith wrote:

Andrew,

From the manual:

It is important to realize that a rule is really a command
transformation mechanism, or command macro. The transformation happens
before the execution of the command starts. If you actually want an
operation that fires independently for each physical row, you probably
want to use a trigger, not a rule

Thus, I should not have to use a trigger for TRUNCATE because the
"each
row" concept does not apply. Plus it makes perfect sense to
want to

transform the truncate command and transform into ignore

Just in case it has not been made obvious yet, rules are silently
deprecated. They still exist because views depend on them, but it is
generally considered best practices to not use them outside that
realm. So if you want the rule behavior to change for TRUNCATE(if that
is even possible) you are fighting an uphill battle. You may pursue
that fight of course, but I would think you will get a quicker return
on your time if you just forget about using a RULE and stick to a
TRIGGER instead.

Or change to using delete instead of truncate?

Well Tim has an ON DELETE rule:

http://www.postgresql.org/message-id/CA+HuS5G2bZYYOGTJrw+VosjUPO298swxuU=jorfav54ut7v...@mail.gmail.com

His expectation was that would also catch a TRUNCATE based on this:

"... It has the same effect as an unqualified DELETE on each table, ..."

from here:

http://www.postgresql.org/docs/9.4/interactive/sql-truncate.html

It was then explained that while TRUNCATE had the same end result as
'DELETE FROM some_table' it was actually a separate command and
action. Tim wants to catch a TRUNCATE and turn it into an ignore.

I'm suggesting OP might find changing truncate statements to deletes
(without a where clause) a simpler solution. Something has to change.

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-23 Thread Scott Marlowe

On Thu, Jul 23, 2015 at 12:09 PM, Adrian Klaver
 wrote:
> On 07/23/2015 04:57 AM, Tim Smith wrote:
>>
>> Andrew,
>>
>>  From the manual:
>>
>> It is important to realize that a rule is really a command
>> transformation mechanism, or command macro. The transformation happens
>> before the execution of the command starts. If you actually want an
>> operation that fires independently for each physical row, you probably
>> want to use a trigger, not a rule
>>
>>
>> Thus, I should not have to use a trigger for TRUNCATE because the "each
>> row" concept does not apply. Plus it makes perfect sense to want to
>> transform the truncate command and transform into ignore
>>
>
> Just in case it has not been made obvious yet, rules are silently
> deprecated. They still exist because views depend on them, but it is
> generally considered best practices to not use them outside that realm. So
> if you want the rule behavior to change for TRUNCATE(if that is even
> possible) you are fighting an uphill battle. You may pursue that fight of
> course, but I would think you will get a quicker return on your time if you
> just forget about using a RULE and stick to a TRIGGER instead.
>


Also OP needs to know that COPY commands are ignored by rules as well.
I agree, stick to triggers, they're faster and less error prone.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 11:15 AM, Rob Sargent wrote:

On 07/23/2015 12:09 PM, Adrian Klaver wrote:

On 07/23/2015 04:57 AM, Tim Smith wrote:

Andrew,

From the manual:

Thus, I should not have to use a trigger for TRUNCATE because the "each
row" concept does not apply. Plus it makes perfect sense to want to
transform the truncate command and transform into ignore

Or change to using delete instead of truncate?

Well Tim has an ON DELETE rule:

http://www.postgresql.org/message-id/CA+HuS5G2bZYYOGTJrw+VosjUPO298swxuU=jorfav54ut7v...@mail.gmail.com

His expectation was that would also catch a TRUNCATE based on this:

"... It has the same effect as an unqualified DELETE on each table, ..."

from here:

http://www.postgresql.org/docs/9.4/interactive/sql-truncate.html

It was then explained that while TRUNCATE had the same end result as
'DELETE FROM some_table' it was actually a separate command and action.
Tim wants to catch a TRUNCATE and turn it into an ignore.

--
Adrian Klaver
adrian.kla...@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete rule does not prevent truncate


On 07/23/2015 12:09 PM, Adrian Klaver wrote:

On 07/23/2015 04:57 AM, Tim Smith wrote:

Andrew,

 From the manual:

It is important to realize that a rule is really a command
transformation mechanism, or command macro. The transformation happens
before the execution of the command starts. If you actually want an
operation that fires independently for each physical row, you probably
want to use a trigger, not a rule


Thus, I should not have to use a trigger for TRUNCATE because the "each
row" concept does not apply. Plus it makes perfect sense to want to
transform the truncate command and transform into ignore



Just in case it has not been made obvious yet, rules are silently 
deprecated. They still exist because views depend on them, but it is 
generally considered best practices to not use them outside that 
realm. So if you want the rule behavior to change for TRUNCATE(if that 
is even possible) you are fighting an uphill battle. You may pursue 
that fight of course, but I would think you will get a quicker return 
on your time if you just forget about using a RULE and stick to a 
TRIGGER instead.



Or change to using delete instead of truncate?

Re: [GENERAL] Delete rule does not prevent truncate