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@xxxxxxxxxxx
-------------------------------------
Michael Paquier ---09.11.2016 07:10:44---On Wed, Nov 2, 2016 at 12:09 AM, <david.turon@xxxxxxxxxxx> wrote: > we tried new feature RLS - test
Od: Michael Paquier <michael.paquier@xxxxxxxxx>
Komu: david.turon@xxxxxxxxxxx
Kopie: PostgreSQL mailing lists <pgsql-general@xxxxxxxxxxxxxx>
Datum: 09.11.2016 07:10
Předmět: Re: ENABLE ROW LEVEL SECURITY cause huge produce of checkpoints
On Wed, Nov 2, 2016 at 12:09 AM, <david.turon@xxxxxxxxxxx> wrote:
> we tried new feature RLS - tested on postgres 9.5.3 / CentOS6. When we turn
> on ENABLE RLS + FORCE RLS on normal workload cause huge produce checkpoints
> (about 30x or more), our disk partition for xlog was full and log shipping
> to replica maybe delayed removing old checkpoints. Have anybody same
> experiences after turn on RLS? Looks like more buffers set as dirty. Yes,
> we can provide more space for xlog, but it will take much more space for
> xlog backups. We do not know if it's worth it. We had log_checkpoints ON and
> I send log as attachment (RLS Turn ON at 13:26).
Interesting, I don't recall RLS generating a burst in activity. The
first heavier checkpoints happen 20 minutes after enabling RLS and
those are triggered by time. Then things cool down and 1 hour later
comes the real deal with a set of checkpoints triggered by volume. It
is difficult though to draw a conclusion without more idea about your
load, the WAL record generated, etc.
--
Michael
