Re: error in trigger creation

On Sunday, April 21, 2024, yudhi s wrote: > so that it will be able to assign the privilege, so we will be able to > create the event trigger without need to run the event trigger script from > super user itself? > Write a security-definer function owned by superuser and grant app_user

Re: error in trigger creation

On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sunday, April 21, 2024, yudhi s wrote: > >> so that it will be able to assign the privilege, so we will be able to >> create the event trigger without need to run the event trigger script from >> super

error in trigger creation

Hi All, We are seeing privilege issues while creating event triggers. It says the user "*must be a superuser to create an event trigger*". So my question is , if we have application user as "app_user" which is responsible for creating database objects in schema "app_schema" and also we have all

Re: Logging statement having any threat?

On Sat, Apr 20, 2024 at 10:02 PM Adrian Klaver wrote: > > Have you tried?: > > > https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT > > " > log_statement (enum) > ><...> > > The default is none. Only superusers and users with the appropriate SET >

Re: Not able to grant access on pg_signal_backend on azure flexible server

Hi, I am afraid that's not the case with postgresql 16 since we also tested with postgresql 15 we are able to run this command( "GRANT pg_signal_backend To "our_admin_user") successfully with our admin user but that's not the case with postgresql 16 we keep getting the error message 'permission

Re: Not able to grant access on pg_signal_backend on azure flexible server

On Sunday, April 21, 2024, Saksham Joshi wrote: > Hi, > I am afraid that's not the case with postgresql 16 since we also tested > with postgresql 15 we are able to run this command( "GRANT > pg_signal_backend To "our_admin_user") successfully with our admin user but > that's not the case with

Re: Not able to grant access on pg_signal_backend on azure flexible server

Hi David, We have tried granting it with our admin user also but no avail. On Mon, 22 Apr 2024, 10:50 David G. Johnston, wrote: > On Sunday, April 21, 2024, Saksham Joshi wrote: > >> Hi, >> I am afraid that's not the case with postgresql 16 since we also tested >> with postgresql 15 we are

Re: error in trigger creation

"David G. Johnston" writes: > On Sunday, April 21, 2024, yudhi s wrote: >> Are you saying something like below, in which we first create the >> function from super user and then execute the grant? But doesn't that mean, >> each time we want to create a new event trigger we have to be again >>

Re: Logging statement having any threat?

On 4/21/24 02:35, Lok P wrote: On Sat, Apr 20, 2024 at 10:02 PM Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: Have you tried?: https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

Re: error in trigger creation

On Sunday, April 21, 2024, yudhi s wrote: > On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Sunday, April 21, 2024, yudhi s wrote: >> >>> so that it will be able to assign the privilege, so we will be able to >>> create the event trigger without

Re: error in trigger creation

On Sun, Apr 21, 2024 at 7:55 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sunday, April 21, 2024, yudhi s wrote: > >> On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> On Sunday, April 21, 2024, yudhi s wrote: >>> so that it

Re: error in trigger creation

On Sun, Apr 21, 2024 at 8:13 PM Tom Lane wrote: > "David G. Johnston" writes: > > On Sunday, April 21, 2024, yudhi s wrote: > >> Are you saying something like below, in which we first create the > >> function from super user and then execute the grant? But doesn't that > mean, > >> each time

Re: error in trigger creation

On Mon, Apr 22, 2024 at 12:02 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > > I suggest you share a script that demonstrates exactly what you are trying > to accomplish. Which event triggers you need to create from the > application and what the functions those triggers call do. >

Re: query multiple schemas

Hi Norbi, If the number of tenant schemas is reasonably static, you could write a plpgsql function to create a set of UNION ALL views with one view for each table in all tenant schemas. You could re-run the function each time a tenant schema is added. Having the set of views would allow you to

query multiple schemas

On Sunday, April 21, 2024, Norbert Sándor wrote: > > > The structure of each schema is identical, the tenant ID is the name of > the schema. > You’ve hit the main reason why the scheme you choose is usually avoided. Better to just add tenant_id to your tables in the first place. And use

Re: error in trigger creation

On Sun, Apr 21, 2024 at 11:20 AM yudhi s wrote: > > On Sun, Apr 21, 2024 at 8:13 PM Tom Lane wrote: > >> "David G. Johnston" writes: >> > On Sunday, April 21, 2024, yudhi s wrote: >> >> Are you saying something like below, in which we first create the >> >> function from super user and then

Re: error in trigger creation

On Sun, Apr 21, 2024 at 2:58 PM yudhi s wrote: > the partition drop from parent is taking longer as it scans all the > partitions of the child table > Does the relevant supporting index exist on the child table?

query multiple schemas

Hello, I have a database with multiple tenants with a separate schema for each tenant. The structure of each schema is identical, the tenant ID is the name of the schema. What I would like to achieve is to able to query tables in all schemas at once with the tenant IDs added to the result 

Re: error in trigger creation

On Sun, Apr 21, 2024 at 11:10 AM yudhi s wrote: > > On Sun, Apr 21, 2024 at 7:55 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Sunday, April 21, 2024, yudhi s wrote: >> >>> On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston < >>> david.g.johns...@gmail.com> wrote: >>>

Re: error in trigger creation

On Mon, 22 Apr, 2024, 1:34 am Ron Johnson, wrote: > On Sun, Apr 21, 2024 at 2:58 PM yudhi s > wrote: > >> the partition drop from parent is taking longer as it scans all the >> partitions of the child table >> > > Does the relevant supporting index exist on the child table? > Yes all the child

Re: error in trigger creation

On 4/21/24 11:20, yudhi s wrote: On Sun, Apr 21, 2024 at 8:13 PM Tom Lane > wrote: So do you mean , we should not create the event trigger using the "security definer" , rather have the super user do this each time we have to create the event trigger?

Re: CLUSTER vs. VACUUM FULL

On Sun, Apr 21, 2024 at 9:35 PM David Rowley wrote: > On Mon, 22 Apr 2024 at 12:16, Ron Johnson wrote: > > > > On Sun, Apr 21, 2024 at 6:45 PM Tom Lane wrote: > >> > >> Ron Johnson writes: > >> > Why is VACUUM FULL recommended for compressing a table, when CLUSTER > does > >> > the same thing

Missing PG_MODULE_MAGIC error

Hi all, I am trying to test an extension that links to rocksdb. When I try to load it in postgres, it complains "missing magic block" while I do have PG_MODULE_MAGIC in my src. Here is a minimal repro // pgtam.c #include "postgres.h" #include #include "fmgr.h" #include "access/tableam.h"

Re: query multiple schemas

Steve Baldwin writes: > If the number of tenant schemas is reasonably static, you could write a > plpgsql function to create a set of UNION ALL views with one view for each > table in all tenant schemas. You could re-run the function each time a > tenant schema is added. Having the set of views

Re: error in trigger creation

Adrian Klaver writes: > On 4/21/24 11:20, yudhi s wrote: >> So in this case i was wondering if "event trigger" can cause any >> additional threat and thus there is no such privilege like "create >> trigger" exist in postgres and so it should be treated cautiously? > An event trigger runs as a

Re: CLUSTER vs. VACUUM FULL

Ron Johnson writes: > Why is VACUUM FULL recommended for compressing a table, when CLUSTER does > the same thing (similarly doubling disk space), and apparently runs just as > fast? CLUSTER makes the additional effort to sort the data per the ordering of the specified index. I'm surprised

Re: CLUSTER vs. VACUUM FULL

On Sun, Apr 21, 2024 at 6:45 PM Tom Lane wrote: > Ron Johnson writes: > > Why is VACUUM FULL recommended for compressing a table, when CLUSTER does > > the same thing (similarly doubling disk space), and apparently runs just > as > > fast? > > CLUSTER makes the additional effort to sort the

Re: CLUSTER vs. VACUUM FULL

On Sun, Apr 21, 2024 at 6:45 PM Tom Lane wrote: > Ron Johnson writes: > > Why is VACUUM FULL recommended for compressing a table, when CLUSTER does > > the same thing (similarly doubling disk space), and apparently runs just > as > > fast? > > CLUSTER makes the additional effort to sort the

Re: error in trigger creation

On 4/21/24 14:21, Tom Lane wrote: Adrian Klaver writes: On 4/21/24 11:20, yudhi s wrote: So in this case i was wondering if "event trigger" can cause any additional threat and thus there is no such privilege like "create trigger" exist in postgres and so it should be treated cautiously? An

CLUSTER vs. VACUUM FULL

PG 14.11 on RHEL8 Why is VACUUM FULL recommended for compressing a table, when CLUSTER does the same thing (similarly doubling disk space), and apparently runs just as fast? My tests: Table: CDSLBXW.public.log Time 1 Time 2 Time 3 secssecssecs VACUUM FULL 44.2

Re: CLUSTER vs. VACUUM FULL

On Mon, 22 Apr 2024 at 12:16, Ron Johnson wrote: > > On Sun, Apr 21, 2024 at 6:45 PM Tom Lane wrote: >> >> Ron Johnson writes: >> > Why is VACUUM FULL recommended for compressing a table, when CLUSTER does >> > the same thing (similarly doubling disk space), and apparently runs just as >> >