Re: current_role of caller of a DEFINER function
On Wed, Jun 26, 2024 at 2:25 PM Dominique Devienne wrote: > On Wed, Jun 26, 2024 at 11:08 AM Laurenz Albe > wrote: > > On Wed, 2024-06-26 at 10:35 +0200, Dominique Devienne wrote: > > > So I have two questions: > > > 1) Is there any way to know the current_role of the caller of a > > > DEFINER function. I fear the answer is no, but better be sure from > > > experts here. > > > > Just to be certain, let me ask a question back: > > > > If a SECURITY DEFINER function calls another SECURITY DEFINER function, > > which role would you like to get: > > 1. the invoker that called the 1st function > > 2. the owner of the 1st function (which is the user that called the 2nd > > function) > > Honestly Laurenz, I didn't think about it, and it does not matter too > much in my case. > Because what matters to me is the initial entry-point, from caller to > DEFINER function, > to accurately capture the role, and then I can pass it on explicitly > myself if needed. > This is for more knowledgeable people to decide on. Hi. Resurrecting this thread, 1 year later, to follow up on Laurenz question, and ask another of my own. It just so happens that I now have two layers of SECURITY DEFINER functions. The 1st (inner) layer is to encapsulate sensitive DDLs (and audit them), is one "DBA" schema that belong to a "DBA" role, that knows (almost) nothing of the application and is only concerned with pure-PostgreSQL stuff, and the 2nd (outer) layer more application specific, in another "App Admin" schema (that belongs to different ROLE yet). I'm using the DOMAIN type discussed here, on the outer layer, to capture the CURRENT_ROLE, i.e. the caller of that outer SECURITY DEFINER FUNCTION. This works great. But I also want to pass it down to the lower/inner layer, for auditing/logging purposes only. Initially I tried using the same DOMAIN type/value, but then I'd violate the CHECK constraints. The initial value was captured before entering the outer SECURITY DEFINER function, and when copying the value to pass it to the inner SECURITY DEFINER function, that value no longer matches the "new" CURRENT_ROLE from within the SECURITY DEFINER context. The work-around is to take it as text (or name) instead of the DOMAIN type, but that feels unsatisfactory, since then the caller could pass an arbitrary value, not something that comes from my DOMAIN type, which enforces the fact its value is the CURRENT_ROLE. So my question is whether my inner-procs can take another type, that can only be created from my DOMAIN type? I.e. I'd want to enforce the value I'm getting comes from my DOMAIN type, and only that type. Is that possible? Thanks, --DD
Combining scalar and row types in RETURNING
Can you combine scalar and row types in a RETURNING clause? My use-case is getting the results of a MERGE - I'd like to be able to capture both the action performed and the modified row, something like this (this is in a plpgsql function): declare m_action text; m_new_data record; begin merge into my_table t using ( ) s on (t.id = s.id) when matched then update . when not matched then insert . returning merge_action(), t.* into m_action, m_new_data; end; In my case, m_new_data is actually a table row type rather than plain "record". The row is passed on to another function which calculates the altered columns and logs the changes. I've tried it, and it doesn't seem to work; I get an error, "m_new_data is not a scalar variable", so I'm guessing it's not possible, but it's worth asking... I know I can list the returned columns individually in the RETURNING and then use a row constructor to construct the row but it'd be handier if I could just derive the row directly from the MERGE query. Thanks in advance, Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: Combining scalar and row types in RETURNING
"Ray O'Donnell" writes: > Can you combine scalar and row types in a RETURNING clause? I think so. > declare > m_action text; > m_new_data record; > begin > merge into my_table t > using ( > > ) s > on (t.id = s.id) > when matched then > update . > when not matched then > insert . > returning > merge_action(), t.* > into > m_action, m_new_data; I think the problem here is that "t.*" gets expanded into a list of all of t's columns, just as would happen in a SELECT's output list. Try returning merge_action(), t It might also be necessary to declare the target variable "m_new_data" as being of type my_table rather than generic "record"; not sure about that. regards, tom lane
Re: Combining scalar and row types in RETURNING
On 03/06/2025 17:53, Tom Lane wrote: "Ray O'Donnell" writes: Can you combine scalar and row types in a RETURNING clause? I think so. declare m_action text; m_new_data record; begin merge into my_table t using ( ) s on (t.id = s.id) when matched then update . when not matched then insert . returning merge_action(), t.* into m_action, m_new_data; I think the problem here is that "t.*" gets expanded into a list of all of t's columns, just as would happen in a SELECT's output list. Try returning merge_action(), t It might also be necessary to declare the target variable "m_new_data" as being of type my_table rather than generic "record"; not sure about that. Thanks a million for the explanation, Tom - that makes sense. I tried what you suggested, with mixed results: (i) Running the MERGE as a stand-alone query, with just RETURNING... , worked - I got a scalar and a row as expected. (ii) Running it in a function (actually a DO block), with m_new correctly declared as the table type, failed with the same error as before. (iii) Running (ii) but with the order of the items in RETURNING reversed - ... returning t, merge_action() into m_new, m_action - gave me a different error: ERROR: record variable cannot be part of multiple-item INTO list LINE 53: m, merge_action() into m_new, m_action ...which seems to answer my question definitively. Thanks once more, Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: Combining scalar and row types in RETURNING
On 6/3/25 11:18, Ray O'Donnell wrote: On 03/06/2025 17:53, Tom Lane wrote: Thanks a million for the explanation, Tom - that makes sense. I tried what you suggested, with mixed results: (i) Running the MERGE as a stand-alone query, with just RETURNING... , worked - I got a scalar and a row as expected. (ii) Running it in a function (actually a DO block), with m_new correctly declared as the table type, failed with the same error as before. (iii) Running (ii) but with the order of the items in RETURNING reversed - ... returning t, merge_action() into m_new, m_action - gave me a different error: ERROR: record variable cannot be part of multiple-item INTO list LINE 53: m, merge_action() into m_new, m_action ...which seems to answer my question definitively. This: ... returning t, merge_action() into m_new, m_action does not match this: LINE 53: m, merge_action() into m_new, m_action Is this a copy and paste error or two different invocations of the function? Thanks once more, Ray. -- Adrian Klaver adrian.kla...@aklaver.com
Sudden increase in n_dead_tup with no corresponding insert/update/delete
Hi all, While investigating some potential vacuum improvements to make to a table I happened to notice that one table (along with others) will suddenly increase the number of n_dead_tup reported in pg_stat_user_tables without a corresponding increase in the inserts, updates, or deletes. For instance, running this query in a 1 second loop select * from pg_stat_user_tables where relname = 'casino_account_history_lines'; I can see the n_dead_tup column increases until which time the autovacuum process finishes vacuuming the table. Example: -[ RECORD 1 ]---+-- relid | 33378 schemaname | public relname | casino_account_history_lines seq_scan| 1122 seq_tup_read| 178229588443 idx_scan| 456779105 idx_tup_fetch | 5539267637 n_tup_ins | 45093031 n_tup_upd | 47289203 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1646966715 n_dead_tup | 1356331 n_mod_since_analyze | 11498 n_ins_since_vacuum | 6288 last_vacuum | 2025-06-03 14:57:43.46009+00 last_autovacuum | 2025-06-03 19:09:21.595322+00 last_analyze| 2025-06-03 14:57:54.848185+00 last_autoanalyze| 2025-06-03 19:09:48.390396+00 vacuum_count| 2 autovacuum_count| 3973 analyze_count | 6 autoanalyze_count | 3078 -- -- At this point the table is no longer in pg_stat_progress_vacuum and `n_dead_tup` has dropped from 1356331 to 4302 -- -[ RECORD 1 ]---+-- relid | 33378 schemaname | public relname | casino_account_history_lines seq_scan| 1122 seq_tup_read| 178229588443 idx_scan| 456779364 idx_tup_fetch | 5539267804 n_tup_ins | 45093063 n_tup_upd | 47289232 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1646961282 n_dead_tup | 4302 n_mod_since_analyze | 11559 n_ins_since_vacuum | 2 last_vacuum | 2025-06-03 14:57:43.46009+00 last_autovacuum | 2025-06-03 19:12:48.107816+00 last_analyze| 2025-06-03 14:57:54.848185+00 last_autoanalyze| 2025-06-03 19:09:48.390396+00 vacuum_count| 2 autovacuum_count| 3974 analyze_count | 6 autoanalyze_count | 3078 -- This seems normal to me, however, while still looking at pg_stat_user_tables in a loop, `n_dead_tup` steadily increases to, in this latest run, `5038` at which point, one second later the number jumps to above 1.2 million: Tue 03 Jun 2025 07:13:11 PM UTC (every 1s) -[ RECORD 1 ]---+-- relid | 33378 schemaname | public relname | casino_account_history_lines seq_scan| 1122 seq_tup_read| 178229588443 idx_scan| 456784246 idx_tup_fetch | 5539271612 n_tup_ins | 45093719 n_tup_upd | 47289968 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1646961938 n_dead_tup | 5038 n_mod_since_analyze | 12951 n_ins_since_vacuum | 658 last_vacuum | 2025-06-03 14:57:43.46009+00 last_autovacuum | 2025-06-03 19:12:48.107816+00 last_analyze| 2025-06-03 14:57:54.848185+00 last_autoanalyze| 2025-06-03 19:09:48.390396+00 vacuum_count| 2 autovacuum_count| 3974 analyze_count | 6 autoanalyze_count | 3078 Tue 03 Jun 2025 07:13:12 PM UTC (every 1s) -[ RECORD 1 ]---+-- relid | 33378 schemaname | public relname | casino_account_history_lines seq_scan| 1122 seq_tup_read| 178229588443 idx_scan| 456784464 idx_tup_fetch | 5539271752 n_tup_ins | 45093746 n_tup_upd | 47289993 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1647255972 n_dead_tup | 1290579 n_mod_since_analyze | 2 n_ins_since_vacuum | 685 last_vacuum | 2025-06-03 14:57:43.46009+00 last_autovacuum | 2025-06-03 19:12:48.107816+00 last_analyze| 2025-06-03 14:57:54.848185+00 last_autoanalyze| 2025-06-03 19:13:12.125828+00 vacuum_count| 2 autovacuum_count| 3974 analyze_count | 6 autoanalyze_count | 3079 I don't understand where this large increase is coming from when there are no corresponding inserts, updates, or deletes (at the magnitude). This entire process repeats itself and, as mentioned, the same thing is happening on other observed tables. I'm running version 'PostgreSQL 15.6 (Ubuntu 15.6-1.pgdg22.04+1)' Thanks, Matt
Re: Combining scalar and row types in RETURNING
"Ray O'Donnell" writes: > (iii) Running (ii) but with the order of the items in RETURNING reversed - > ... returning t, merge_action() into m_new, m_action > - gave me a different error: > ERROR: record variable cannot be part of multiple-item INTO list > LINE 53: m, merge_action() into m_new, m_action > ...which seems to answer my question definitively. Ah, after looking at the source code in that area, plpgsql allows the INTO target to be either a single composite variable, or one or more non-composite variables; the argument being that otherwise it's too hard to decide which RETURNING items match which INTO items. But I think maybe there is still a solution: declare m_into record; ... returning merge_action() m, t into m_into; ... then fetch m_into.m and m_into.t (the latter will be a composite field). I didn't try this approach though. regards, tom lane
Re: Combining scalar and row types in RETURNING
On 03/06/2025 20:01, Adrian Klaver wrote: On 6/3/25 11:18, Ray O'Donnell wrote: On 03/06/2025 17:53, Tom Lane wrote: Thanks a million for the explanation, Tom - that makes sense. I tried what you suggested, with mixed results: (i) Running the MERGE as a stand-alone query, with just RETURNING... , worked - I got a scalar and a row as expected. (ii) Running it in a function (actually a DO block), with m_new correctly declared as the table type, failed with the same error as before. (iii) Running (ii) but with the order of the items in RETURNING reversed - ... returning t, merge_action() into m_new, m_action - gave me a different error: ERROR: record variable cannot be part of multiple-item INTO list LINE 53: m, merge_action() into m_new, m_action ...which seems to answer my question definitively. This: ... returning t, merge_action() into m_new, m_action does not match this: LINE 53: m, merge_action() into m_new, m_action Is this a copy and paste error or two different invocations of the function? Whoops, sorry, yes, that's a copy-and-paste error - the target table is aliased as "m" in my original. Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: Sudden increase in n_dead_tup with no corresponding insert/update/delete
On Wed, 4 Jun 2025 at 07:22, Matthew Tice wrote: > Tue 03 Jun 2025 07:13:11 PM UTC (every 1s) > n_dead_tup | 5038 > autoanalyze_count | 3078 > Tue 03 Jun 2025 07:13:12 PM UTC (every 1s) > n_dead_tup | 1290579 > autoanalyze_count | 3079 > I don't understand where this large increase is coming from when there are no > corresponding inserts, updates, or deletes (at the magnitude). This entire > process repeats itself and, as mentioned, the same thing is happening on > other observed tables. I imagine it's from the auto-analyze that ran. Analyze will try to estimate the live and dead rows, but since analyze only samples some blocks, it may come up with something that's not too accurate if the blocks it happened to sample don't contain similar percentages of dead rows than the entire table. See [1]. David [1] https://github.com/postgres/postgres/blob/REL_15_STABLE/src/backend/commands/analyze.c#L1318
