Re: autovacuum worker started without a worker entry
Most probably, it runs a wraparound process, however, if you may see what command was invoked by that worker, it would be helpful. Regards, Ninad Shah On Mon, 9 Aug 2021 at 01:48, Luca Ferrari wrote: > On Thu, Aug 5, 2021 at 6:27 PM Vijaykumar Jain > wrote: > > postgres/varsup.c at master · postgres/postgres (github.com) > > I think, this block when it is about to assign the next xid, it does the > math, and triggers an autolauncher start. > > I might be wrong, I did not run a backtrace though :) > > > > * Check to see if it's safe to assign another XID. This protects against > > * catastrophic data loss due to XID wraparound. The basic rules are: > > * If we're past xidVacLimit, start trying to force autovacuum cycles. > > * If we're past xidWarnLimit, start issuing warnings. > > * If we're past xidStopLimit, refuse to execute transactions, unless > > * we are running in single-user mode (which gives an escape hatch > > * to the DBA who somehow got past the earlier defenses). > > Seem reasonable as explaination, even if sounds to me xidVacLimit is 65536. > > Thanks, > Luca > > >
Re: JWT decoder
That was not the question, the question is how do you decode base64url (not base64) in postgres sql? On Monday, August 9, 2021, 02:08:44 PM GMT+10, rob stone wrote: Hello, On Mon, 2021-08-09 at 02:16 +, Masih Tavassoli wrote: > Hi experts, > > I am trying to find a way to decode a URL request header and extract > its JSON fields in postgreql . > > I can do this in Oracle sql using > > select (utl_raw.cast_to_varchar2 (utl_encode.base64_decode( > utl_raw.cast_to_raw (regexp_replace ( ( > 'eyJhbGciOiJSUzI1NiIsImtpZCI6InBhLWFwaS1zaWduIn0.eyJzY29wZSI6W10sInN1 > YiI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImNsaWVudCI6InB4Y > V9hY19zZXR0c3BsdXMiLCJ1c2VybmFtZSI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW > 5nLm5ldC5hdSIsImV4cCI6MTU1NDA4NzU2N30.OZwVETCP+HUDsSkudCfa52eQT2Fyt6B > gefFpYr5xpJROjZJeVnXtzsU6MS8aHauqyyOwQSz8iG6OC2YjYgrGiZ8YTwTh1uHptNji > cyCFmCkh++d0oGdK78ZOKIAvuslW3GX4V13hPUJADzGLkyO8hy01YF9Id4+7GrvmnRRkw > PO7C2eUvKTB8pxDAA94Jpw74s4BiNTmm1Uv+56AXqdI8m2KFkLNwGoUR3OSNvsifl5uxB > sGGmP4wbgNe/Dy1q2UsL2eBAW/GuWryHGCRz1/PR2jIV1v21ClQ8YLiFAzM3YxHb8m2ng > zRynKof+Nw3nZvuafRQ16MV5THz0ahGFYLA' > ), '[[:space:]]', '') from dual > > > > But there doesn't seem to be a way doing it in postgres. > > Has anyone got any suggesions? > > Thanks > Masih > Have a look at the orafce extension on github. It handles "select from dual", etc. Cheers, Rob
Re: JWT decoder
There are lots of decoders but I need to do it within postgresql. On Monday, August 9, 2021, 01:24:33 PM GMT+10, Mladen Gogala wrote: You could probably use Pl/Python. Python has base64 module and urllib module which can deal with all URL strings I am aware of. Pl/Perl would probably do as well. On 8/8/21 10:16 PM, Masih Tavassoli wrote: Hi experts, I am trying to find a way to decode a URL request header and extract its JSON fields in postgreql . I can do this in Oracle sql using select (utl_raw.cast_to_varchar2 (utl_encode.base64_decode( utl_raw.cast_to_raw (regexp_replace ( ('eyJhbGciOiJSUzI1NiIsImtpZCI6InBhLWFwaS1zaWduIn0.eyJzY29wZSI6W10sInN1YiI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImNsaWVudCI6InB4YV9hY19zZXR0c3BsdXMiLCJ1c2VybmFtZSI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImV4cCI6MTU1NDA4NzU2N30.OZwVETCP+HUDsSkudCfa52eQT2Fyt6BgefFpYr5xpJROjZJeVnXtzsU6MS8aHauqyyOwQSz8iG6OC2YjYgrGiZ8YTwTh1uHptNjicyCFmCkh++d0oGdK78ZOKIAvuslW3GX4V13hPUJADzGLkyO8hy01YF9Id4+7GrvmnRRkwPO7C2eUvKTB8pxDAA94Jpw74s4BiNTmm1Uv+56AXqdI8m2KFkLNwGoUR3OSNvsifl5uxBsGGmP4wbgNe/Dy1q2UsL2eBAW/GuWryHGCRz1/PR2jIV1v21ClQ8YLiFAzM3YxHb8m2ngzRynKof+Nw3nZvuafRQ16MV5THz0ahGFYLA' ), '[[:space:]]', '') from dual But there doesn't seem to be a way doing it in postgres. Has anyone got any suggesions? Thanks Masih -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: JWT decoder
Hello, On Mon, 2021-08-09 at 02:16 +, Masih Tavassoli wrote: > Hi experts, > > I am trying to find a way to decode a URL request header and extract > its JSON fields in postgreql . > > I can do this in Oracle sql using > > select (utl_raw.cast_to_varchar2 (utl_encode.base64_decode( > utl_raw.cast_to_raw (regexp_replace ( ( > 'eyJhbGciOiJSUzI1NiIsImtpZCI6InBhLWFwaS1zaWduIn0.eyJzY29wZSI6W10sInN1 > YiI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImNsaWVudCI6InB4Y > V9hY19zZXR0c3BsdXMiLCJ1c2VybmFtZSI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW > 5nLm5ldC5hdSIsImV4cCI6MTU1NDA4NzU2N30.OZwVETCP+HUDsSkudCfa52eQT2Fyt6B > gefFpYr5xpJROjZJeVnXtzsU6MS8aHauqyyOwQSz8iG6OC2YjYgrGiZ8YTwTh1uHptNji > cyCFmCkh++d0oGdK78ZOKIAvuslW3GX4V13hPUJADzGLkyO8hy01YF9Id4+7GrvmnRRkw > PO7C2eUvKTB8pxDAA94Jpw74s4BiNTmm1Uv+56AXqdI8m2KFkLNwGoUR3OSNvsifl5uxB > sGGmP4wbgNe/Dy1q2UsL2eBAW/GuWryHGCRz1/PR2jIV1v21ClQ8YLiFAzM3YxHb8m2ng > zRynKof+Nw3nZvuafRQ16MV5THz0ahGFYLA' > ), '[[:space:]]', '') from dual > > > > But there doesn't seem to be a way doing it in postgres. > > Has anyone got any suggesions? > > Thanks > Masih > Have a look at the orafce extension on github. It handles "select from dual", etc. Cheers, Rob
Re: JWT decoder
You could probably use Pl/Python. Python has base64 module and urllib module which can deal with all URL strings I am aware of. Pl/Perl would probably do as well. On 8/8/21 10:16 PM, Masih Tavassoli wrote: Hi experts, I am trying to find a way to decode a URL request header and extract its JSON fields in postgreql . I can do this in Oracle sql using select (utl_raw.cast_to_varchar2 (utl_encode.base64_decode( utl_raw.cast_to_raw (regexp_replace ( ( 'eyJhbGciOiJSUzI1NiIsImtpZCI6InBhLWFwaS1zaWduIn0.eyJzY29wZSI6W10sInN1YiI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImNsaWVudCI6InB4YV9hY19zZXR0c3BsdXMiLCJ1c2VybmFtZSI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImV4cCI6MTU1NDA4NzU2N30.OZwVETCP+HUDsSkudCfa52eQT2Fyt6BgefFpYr5xpJROjZJeVnXtzsU6MS8aHauqyyOwQSz8iG6OC2YjYgrGiZ8YTwTh1uHptNjicyCFmCkh++d0oGdK78ZOKIAvuslW3GX4V13hPUJADzGLkyO8hy01YF9Id4+7GrvmnRRkwPO7C2eUvKTB8pxDAA94Jpw74s4BiNTmm1Uv+56AXqdI8m2KFkLNwGoUR3OSNvsifl5uxBsGGmP4wbgNe/Dy1q2UsL2eBAW/GuWryHGCRz1/PR2jIV1v21ClQ8YLiFAzM3YxHb8m2ngzRynKof+Nw3nZvuafRQ16MV5THz0ahGFYLA' ), '[[:space:]]', '') from dual But there doesn't seem to be a way doing it in postgres. Has anyone got any suggesions? Thanks Masih -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
JWT decoder
Hi experts, I am trying to find a way to decode a URL request header and extract its JSON fields in postgreql . I can do this in Oracle sql using select (utl_raw.cast_to_varchar2 (utl_encode.base64_decode( utl_raw.cast_to_raw (regexp_replace ( ( 'eyJhbGciOiJSUzI1NiIsImtpZCI6InBhLWFwaS1zaWduIn0.eyJzY29wZSI6W10sInN1YiI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImNsaWVudCI6InB4YV9hY19zZXR0c3BsdXMiLCJ1c2VybmFtZSI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImV4cCI6MTU1NDA4NzU2N30.OZwVETCP+HUDsSkudCfa52eQT2Fyt6BgefFpYr5xpJROjZJeVnXtzsU6MS8aHauqyyOwQSz8iG6OC2YjYgrGiZ8YTwTh1uHptNjicyCFmCkh++d0oGdK78ZOKIAvuslW3GX4V13hPUJADzGLkyO8hy01YF9Id4+7GrvmnRRkwPO7C2eUvKTB8pxDAA94Jpw74s4BiNTmm1Uv+56AXqdI8m2KFkLNwGoUR3OSNvsifl5uxBsGGmP4wbgNe/Dy1q2UsL2eBAW/GuWryHGCRz1/PR2jIV1v21ClQ8YLiFAzM3YxHb8m2ngzRynKof+Nw3nZvuafRQ16MV5THz0ahGFYLA' ), '[[:space:]]', '') from dual But there doesn't seem to be a way doing it in postgres. Has anyone got any suggesions? ThanksMasih
Re: autovacuum worker started without a worker entry
On Thu, Aug 5, 2021 at 6:27 PM Vijaykumar Jain wrote: > postgres/varsup.c at master · postgres/postgres (github.com) > I think, this block when it is about to assign the next xid, it does the > math, and triggers an autolauncher start. > I might be wrong, I did not run a backtrace though :) > > * Check to see if it's safe to assign another XID. This protects against > * catastrophic data loss due to XID wraparound. The basic rules are: > * If we're past xidVacLimit, start trying to force autovacuum cycles. > * If we're past xidWarnLimit, start issuing warnings. > * If we're past xidStopLimit, refuse to execute transactions, unless > * we are running in single-user mode (which gives an escape hatch > * to the DBA who somehow got past the earlier defenses). Seem reasonable as explaination, even if sounds to me xidVacLimit is 65536. Thanks, Luca
Re: pgcrypto - real life examples to encrypt / decrypt
On Fri, Aug 6, 2021 at 8:27 PM Reid Thompson wrote: > > someone mentioned an alternative earlier that they couldn't remember -- i > think it may have been pgsodium that they were thinking of > https://github.com/michelp/pgsodium > Yes, thanks. For some reason I was remembering "selenium" something... Luca
max_worker_processes - Check How Many Worker Processes are Currently Active
Hi, Is it possible to check how many worker processes and logical replication workers are currently running I would like to find out how close I'm to the max limit. max_logical_replication_workers max_worker_processes Thanks IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
Re: Test mail for pgsql-general
Hi, I am running rocky-linux 8.4. I have successfully added the yum repo as specified at https://www.postgresql.org/download/linux/redhat/ . However, whenever I do `yum -q -C list` I always get the following error, Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project " Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]: y Error: Cache-only enabled but no cache for 'pgdg-common' Am I missing something? B. On Sun, 8 Aug 2021 at 14:27, pgsql-general Owner < pgsql-general-ow...@lists.postgresql.org> wrote: > Hello! > > This is a test mail for the list > > pgsql-general@lists.postgresql.org > > as requested. The mail has the same list-id header > as email delivered to the list, and can be used to > for example test local mail filters. >