Re: ENABLE ROW LEVEL SECURITY cause huge produce of checkpoints - Mailing list pgsql-general

Hello,

thanks for reply, I investigated this and thanks to pg_xlog_dump i found:
/usr/pgsql-9.5/bin/pg_xlogdump 00000001000008700000007C 00000001000008700000007D | head -1
rmgr: Heap        len (rec/tot):      7/    53, tx:  284003096, lsn: 870/7C000030, prev 870/7BFFFFD0, desc: LOCK off 2: xid 284003096 LOCK_ONLY EXCL_LOCK KEYS_UPDATED , blkref #0: rel 1663/16404/191292060 blk 15561

whole xlog file contains only this rows  - its on table with enabled RLS
/usr/pgsql-9.5/bin/pg_xlogdump --stats=record 00000001000008700000007C 00000001000008700000007C
Type                                           N      (%)          Record size      (%)             FPI size      (%)        Combined size      (%)
----                                           -      ---          -----------      ---             --------      ---        -------------      ---
Transaction/COMMIT                             5 (  0.00)                  160 (  0.00)                    0 (  0.00)                  160 (  0.00)
Heap/INSERT                                    5 (  0.00)                  135 (  0.00)                    0 (  0.00)                  135 (  0.00)
Heap/LOCK                                 298674 ( 99.99)              9258894 ( 99.99)                    0 (  0.00)              9258894 ( 99.99)
Btree/INSERT_LEAF                             20 (  0.01)                  520 (  0.01)                    0 (  0.00)                  520 (  0.01)
Sequence/LOG                                   1 (  0.00)                  182 (  0.00)                    0 (  0.00)                  182 (  0.00)
                                       --------                      --------                      --------                      --------
Total                                     298705  

And thanks to xid i found transaction with
SELECT
/*lot joins*/
FOR UPDATE
there missed OF table name clause, but this not help much..., so i found i made wrong POLICY on table ... something like:
CREATE POLICY some_policy ON projects FOR ALL TO role_name USING (pg_has_role("current_user"(), 'some_role'::name, 'member'::text));
and made some subrole that have access without grant permisions to other tables but better solution was create new role and grant access right  and in policy use true instead expression for role that have access to all rows.
CREATE POLICY some_policy ON projects FOR ALL TO role_name USING (True);

So it was only bad idea, bad design.

Thanks not need solve this..., now not produce extra WAL records.


David

--
-------------------------------------
Ing. David TUROŇ
LinuxBox.cz, s.r.o.
28. rijna 168, 709 01 Ostrava

tel.:    +420 591 166 224
fax:    +420 596 621 273
mobil:  +420 732 589 152
www.linuxbox.cz

mobil servis: +420 737 238 656
email servis: servis@linuxbox.cz
-------------------------------------

Michael Paquier ---09.11.2016 07:10:44---On Wed, Nov 2, 2016 at 12:09 AM,  <david.turon@linuxbox.cz> wrote: > we tried new feature RLS - test

Od: Michael Paquier <michael.paquier@gmail.com>
Komu: david.turon@linuxbox.cz
Kopie: PostgreSQL mailing lists <pgsql-general@postgresql.org>
Datum: 09.11.2016 07:10
Předmět: Re: [GENERAL] ENABLE ROW LEVEL SECURITY cause huge produce of checkpoints