Re: Delete rule does not prevent truncate
От | Adrian Klaver |
---|---|
Тема | Re: Delete rule does not prevent truncate |
Дата | |
Msg-id | 55AF9CC1.9050106@aklaver.com обсуждение исходный текст |
Ответ на | Re: Delete rule does not prevent truncate (Tim Smith <randomdev4+postgres@gmail.com>) |
Список | pgsql-general |
On 07/22/2015 06:24 AM, Tim Smith wrote: > Adrian, > > It still doesn't make much sense, especially as given the rather > obscure and questionable design decision of allowing triggers to refer > to truncate ops, but not allowing rules to refer to truncate ops !!! > > Surely either you say "look, truncate is truncate, its there for one > purpose and one purpose only". Or otherwise, you should handle it > consistently across the database, i.e. if you're going to allow > triggers interact with truncates, then you should allow rules to > interact with truncates. It really doesn't make much sense to adopt > a pick and choose mentality ! All I know is that TRUNCATE is a shortcut and RULEs do not understand it and TRIGGERs do. My guess is the answer somewhere in here: http://www.postgresql.org/docs/9.4/interactive/rules.html Word of advice, take two aspirin before reading above. At any rate, I have personally found using triggers results in less surprises then using rules. > > On 22 July 2015 at 14:19, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> On 07/22/2015 06:13 AM, Tim Smith wrote: >>> >>> Melvin, >>> >>> May I point out that the manual states : >>> "TRUNCATE quickly removes all rows from a set of tables. It has the same >>> effect as an unqualified DELETE on each table" >>> >>> Thus, if you are telling me to effectively think of TRUNCATE as an alias >>> to DELETE, then I would think its not entirely unreasonable of me to >>> expect a rule preventing DELETE to also cover truncate, since the rule >>> would no doubt prevent an unqualified DELETE, would it not ?!? >> >> >> If you go further down into the Notes section you find: >> >> "TRUNCATE will not fire any ON DELETE triggers that might exist for the >> tables. But it will fire ON TRUNCATE triggers. If ON TRUNCATE triggers are >> defined for any of the tables, then all BEFORE TRUNCATE triggers are fired >> before any truncation happens, and all AFTER TRUNCATE triggers are fired >> after the last truncation is performed and any sequences are reset. The >> triggers will fire in the order that the tables are to be processed (first >> those listed in the command, and then any that were added due to cascading). >> Warning >> >> TRUNCATE is not MVCC-safe (see Chapter 13 for general information about >> MVCC). After truncation, the table will appear empty to all concurrent >> transactions, even if they are using a snapshot taken before the truncation >> occurred. This will only be an issue for a transaction that did not access >> the truncated table before the truncation happened — any transaction that >> has done so would hold at least an ACCESS SHARE lock, which would block >> TRUNCATE until that transaction completes. So truncation will not cause any >> apparent inconsistency in the table contents for successive queries on the >> same table, but it could cause visible inconsistency between the contents of >> the truncated table and other tables in the database. >> >> " >> >> >> TRUNCATE is when you want fast over safety. >> >>> >>> On 22 July 2015 at 14:03, Melvin Davidson <melvin6925@gmail.com >>> <mailto:melvin6925@gmail.com>> wrote: >>> >>> Actually, if you use a TRIGGER instead of rule, you can handle this. >>> The manual states event can be: >>> >>> INSERT >>> UPDATE [ OFcolumn_name [, ... ] ] >>> DELETE >>> *TRUNCATE <-----* >>> >>> http://www.postgresql.org/docs/9.4/interactive/sql-createtrigger.html >>> >>> I suggest you review carefully. >>> >>> On Wed, Jul 22, 2015 at 8:53 AM, Tim Smith >>> <randomdev4+postgres@gmail.com >>> <mailto:randomdev4+postgres@gmail.com>> wrote: >>> >>> Hi, >>> >>> I very much hope this is an accidental bug rather than a >>> deliberate feature ! >>> >>> PostgreSQL 9.4.4 >>> >>> create rule no_auditupd as on update to app_security.app_audit do >>> instead nothing; >>> create rule no_auditdel as on delete to app_security.app_audit do >>> instead nothing; >>> >>> \d+ app_security.app_audit >>> <snip> >>> Rules: >>> no_auditdel AS >>> ON DELETE TO app_security.app_audit DO INSTEAD NOTHING >>> no_auditupd AS >>> ON UPDATE TO app_security.app_audit DO INSTEAD NOTHING >>> >>> The truncate trashes the whole table ;-( >>> >>> According to the FabulousManual(TM) : >>> event : The event is one of SELECT, INSERT, UPDATE, or DELETE. >>> >>> Thus I can't create a rule to "do nothing" on truncates, thus I >>> am stuck ! >>> >>> >>> -- >>> Sent via pgsql-general mailing list >>> (pgsql-general@postgresql.org >>> <mailto: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. >>> >>> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com > > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления: