pg_dump & RLS
Hi all,
Sorry if this is not the appropriate list, I think so.
- ¿is posible to export using pg_dump only the rows that satisfy a rls
check?
- Of course, yes, use the --enable-row-security option in pg_dump
- Yes, but my RLS expression relies on a GUC:
CREATE POLICY my_policy ON my_table USING (company_id =
*current_setting('company_id')::int*);
Prior to starting dumping I need to set the company_id GUC into the
session, if not, there's no way to only export some rows.
Any ideas?
- Execute a command before starting the export
- Some kind of login trigger for a special user
- ...
Thank you!
Rows removed on child table when updating parent partitioned table.
Hi developers, We have a strange case where some rows are removed. I think it is a bug, but before notifying it I prefer to ask here where I am wrong. *Postgres 12* Given the following structure: create table parent ( id serial, constraint parent_pkey primary key (id)) partition by range (id); create table parent_10 partition of parent for values from (0) to (10); create table parent_20 partition of parent for values from (11) to (20); create table child ( id serial, parent_id int constraint parent_id_fk references parent(id) on update cascade on delete cascade); -- Notice the on update cascade on delete cascade. insert into parent values(0); insert into child values(1,0); -- Here are the rows postgres=# table parent; id 0 (1 row) postgres=# table child; id | parent_id +--- 1 | 0 (1 row) *-- Update the parent table id, with a value contained in the same partition* update parent set id = 5; postgres=# table parent; id 5 (1 row) postgres=# table child; id | parent_id +--- 1 | 5 (1 row) *-- Update the parent table, with a value contained into other partition* update parent set id = 15; postgres=# update parent set id = 15; UPDATE 1 postgres=# table parent; id 15 (1 row) *postgres=# table child; id | parent_id+---(0 rows)* No error or warning was thrown. The rows in the child table were removed. I think what has happened is: The update caused a DELETE in the table parent_10 (removing the rows from child table) and then the INSERT into parent_20. We've checked the documentation but didn't find something about this unexpected behaviour. Trying without "on delete cascade" clause throws a "parent key not found error". Thank you!
Re: Rows removed on child table when updating parent partitioned table.
On Thu, Oct 1, 2020 at 8:02 PM David G. Johnston wrote: > The convention on these lists is to inline or bottom-post, please do not > top-post. > > On Thu, Oct 1, 2020 at 10:41 AM Jonathan Strong > wrote: > >> I've been away from coding for several years, but dusting off my chops >> and getting back up to speed with PostgreSQL (love it!). So please forgive >> me if my early answers here come off as naive. But my understanding of this >> suggests that you shouldn't be using "update" on a serial field. >> > > Yes Jonathan, your present understanding is flawed. The OP has provided > a self-contained simple test case for the problem at hand - which even if > not "best practice" is indeed valid to do and demonstrates the problem > quite clearly. Without actually testing it out I would say that this is > likely indeed an oversight in the partition row movement feature - it > didn't take into account the ON UPDATE/ON DELETE clause. > > Adding Robert Hass who committed the row movement feature [1]. > > We document on the UPDATE reference page that such an update is performed > as a DELETE + INSERT. Given that implementation detail, the observed > behavior is what one would expect if no special consideration has been > given to make row movement between partitions preserve (via deferred > evaluation), or recreate the foreign key relationship. > > For now I would say you should consider the two features incompatible; and > we need to update the documentation to reflect that reality more directly, > barring a solution being proposed, and hopefully back-patched, instead. I > concur with the observation that one would expect these two features to > interact better with each other and think it could possibly be done as a > bug fix for the POLA violation. > > David J. > > [1] > https://github.com/postgres/postgres/commit/2f178441044be430f6b4d626e4dae68a9a6f6cec > > > Regardless of how postgres implement the updates: *Don’t think it’s a bug that executing an update, you are ending up with fewer rows than you initially had? * Is the perfect silent row-killer Even worse, the deleted rows are from another table without realizing it. If no one else gives an opinion I will open a bug for at least, force an update of the documentation.
