Sv: PostgreSQL Read-only mode usage

eports. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

Re: PostgreSQL Read-only mode usage

de. Right, we have PowerBI connected to a standby-DB, streaming-replication. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

Sv: Support for dates before 4713 BC

to implement it in the near future? https://www.postgresql.org/message-id/flat/ca438ff8331c4e109aa1b75a130948ac%40oeaw.ac.at <https://www.postgresql.org/message-id/flat/ca438ff8331c4e109aa1b75a130948ac%40oeaw.ac.at> -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +

Parallel GIN index?

Any plans for $subject? -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

RE: Performance degradation after upgrading from 9.5 to 14

nd so wonder why this default is on? I can confirm this, even in v16 we've turned JIT off. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

prevent users from SELECT-ing from pg_roles/pg_database

Hi, is there a way to prevent a user/role from SELECT-ing from certain system-tables? I'd like the contents of pg_{user,roles,database} to not be visible to all users. Thanks. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mai

Re: prevent users from SELECT-ing from pg_roles/pg_database

På fredag 24. mai 2024 kl. 19:02:13, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh writes: > Hi, is there a way to prevent a user/role from SELECT-ing from certain > system-tables? > I'd like the contents of pg_{user,roles,database} to not be visible

Re: prevent users from SELECT-ing from pg_roles/pg_database

På mandag 27. mai 2024 kl. 11:10:10, skrev Laurenz Albe < laurenz.a...@cybertec.at <mailto:laurenz.a...@cybertec.at>>: On Mon, 2024-05-27 at 09:33 +0200, Andreas Joseph Krogh wrote: > I tried: > > REVOKE SELECT ON pg_catalog.pg_database FROM public; > > But that do

Re: prevent users from SELECT-ing from pg_roles/pg_database

På tirsdag 28. mai 2024 kl. 01:48:17, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Laurenz Albe writes: > On Mon, 2024-05-27 at 09:33 +0200, Andreas Joseph Krogh wrote: >> I tried: >> REVOKE SELECT ON pg_catalog.pg_database FROM public; >> But that doesn't pre

AI for query-planning?

Hi, are there any plans for using some kind of AI for query-planning? Can someone with more knowledge about this than I have please explain why it might, or not, be a good idea, and what the challenges are? Thanks. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56

Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

CHEMA pg_catalog FROM PUBLIC; REVOKE SELECT ON ALL TABLES IN SCHEMA information_schema FROM PUBLIC; Will this affect “normal behaviour”, ie. prevent the planner, or other internal mechanisms, from working properly for sessions logged in with the ‘reporting’-role? Thanks. -- Andreas Joseph Krog

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

like pg_user, pg_shadow, pg_tables etc. will suffice. If read-access (SELECT) on views in public-schema will still works, and pg_dump/restore etc. also works, this sounds like a solution to me. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mai

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

På torsdag 12. september 2024 kl. 15:05:48, skrev Greg Sabino Mullane < htamf...@gmail.com <mailto:htamf...@gmail.com>>: On Thu, Sep 12, 2024 at 12:52 AM Andreas Joseph Krogh mailto:andr...@visena.com>> wrote: I know PG is not designed for this, but I have this requirement no

Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC

for them to take. We're probably going down the postgres_fdw route, that seems to do the job. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

array_cat in PG-14 changed signature breaks my custom aggregate

anycompatiblearray, but that doesn't really tell me anything. Do I have to change the signature of my aggregate to take anycompatiblearray as argument? -- Andreas Joseph Krogh

Re: array_cat in PG-14 changed signature breaks my custom aggregate

75bd17add66 Ok, thanks. -- Andreas Joseph Krogh

Re: PostgreSQL - Ordering Table based of Foreign Key

ETEs will be cascaded. Unfortunately, there is no DELETE FROM ... CASCADE option (similar to DROP) There is TRUNCATE ... CASCADE https://www.postgresql.org/docs/14/sql-truncate.html -- Andreas Joseph Krogh

Re: Incremental backup

plement. Repeating "other databases have it" doesn't change that. -- Andreas Joseph Krogh

Re: Regex for Word space Word space Word ....

e unicode-characters, which [A-Z] approach doesn't handle well. How about: select regexp_matches('Åge is a Man', E'[[:upper:]]\\w+', 'g'); -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:an

Re: Detecting repeated phrase in a string

but just one word. Do you want repeated phrase (list of words) ore repeated words? For repeated words (including unicode-chars) you can do: (\b\p{L}+\b)(?:\s+\1)+ I'm not quite sure how to translate this to PG, but in JAVA it works. -- Andreas Joseph Krogh CTO / Partner - Visena AS

Re: Best Strategy for Large Number of Images

folder, size, filenames etc.) in DB. It has excellent HA and backup mechanisms. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

Getting json-value as varchar

SELECT ('{"key":"value"}'::jsonb)->> 'key'; ┌──┐ │ ?column? │ ├──┤ │ value │ └──┘ (1 row) How to I use the subscript syntax and get the result as varchar instead of JSONB, assuming Iknow the JSON-field is a Stri

Re: Getting json-value as varchar

På torsdag 06. januar 2022 kl. 13:31:19, skrev Thomas Markus < t.mar...@proventis.net <mailto:t.mar...@proventis.net>>: Hi, Am 06.01.22 um 13:28 schrieb Andreas Joseph Krogh: Hi, in PG-14 this query returns "value" (with double-quotes): SELECT ('{&

Sv: Recommended storage hardware

nes/9300 ...with software RAID10 on Linux and XFS. Works very well! -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

Re: Getting json-value as varchar

På torsdag 06. januar 2022 kl. 14:13:40, skrev David G. Johnston < david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>>: On Thursday, January 6, 2022, Andreas Joseph Krogh mailto:andr...@visena.com>> wrote: I think you misread my message. What I want is for the

Re: Getting json-value as varchar

ase. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

Re: Getting json-value as varchar

På torsdag 06. januar 2022 kl. 14:42:21, skrev Pavel Stehule < pavel.steh...@gmail.com <mailto:pavel.steh...@gmail.com>>: Hi čt 6. 1. 2022 v 14:33 odesílatel Andreas Joseph Krogh mailto:andr...@visena.com>> napsal: På torsdag 06. januar 2022 kl. 14:29:12, skrev

Will Barman support restore of single database?

b-include> for additional information and caveats. Are there any plans to add support for this to Barman? -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

Removing INNER JOINs

rhoVLqOqOxVXe%3DNjxw%40mail.gmail.com 3. Removing INNER JOINs http://www.postgresql-archive.org/Removing-INNER-JOINs-td5828650i40.html 4.  https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/#top3   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile:

Sv: Re: Removing INNER JOINs

På fredag 01. desember 2017 kl. 02:20:19, skrev David Rowley < david.row...@2ndquadrant.com <mailto:david.row...@2ndquadrant.com>>: On 1 December 2017 at 02:52, Andreas Joseph Krogh wrote: > > I came across this from Oracle: https://oracle-base.com/articles/misc/join-elim

Sv: Re: Removing INNER JOINs

På fredag 01. desember 2017 kl. 03:30:21, skrev Simon Riggs < si...@2ndquadrant.com <mailto:si...@2ndquadrant.com>>: On 1 December 2017 at 12:20, David Rowley wrote: > On 1 December 2017 at 02:52, Andreas Joseph Krogh wrote: >> >> I came across this from Oracl

Sv: Re: Re: Removing INNER JOINs

På fredag 01. desember 2017 kl. 03:53:29, skrev David Rowley < david.row...@2ndquadrant.com <mailto:david.row...@2ndquadrant.com>>: On 1 December 2017 at 15:34, Andreas Joseph Krogh mailto:andr...@visena.com>> wrote:  Can someone please explain, in layman-terms, what the pro

Sv: Re: Does PostgreSQL check database integrity at startup?

p on (c.relnamespace = nsp.oid)   WHERE  relfilenode <> 0    AND nsp.nspname !~ '^pg_'  AND nsp.nspname <> 'information_schema'; ERROR:  could not stat file "/home/andreak/programs/postgresql-10/data/base/22039391/22039392": No such file or directory     │ public.biginttuple2 │ /home/andreak/programs/postgresql-10/data/base/22039391/22039392 │       -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>  

Sv: Re: Sv: Re: Does PostgreSQL check database integrity at startup?

På søndag 31. desember 2017 kl. 00:49:31, skrev Stephen Frost < sfr...@snowman.net <mailto:sfr...@snowman.net>>: * Andreas Joseph Krogh (andr...@visena.com) wrote: >  SELECT >    quote_ident(nsp.nspname) || '.' || quote_ident(c.relname), >   

Sv: Re: [GENERAL] pg_dump streaming fails - PostgreSQL 9.2

tandby running 10.1 (both on primary and standby)   Isn't this supposed to work? Isn't some of the point of having a hot-standby like this to be able to backup from it?   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>  

Sv: Re: data-checksums

ents.   Does PG use HW-accellerated crc if CPU supports it[1]?   [1] https://en.wikipedia.org/wiki/SSE4   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>  

Sv: Re: Sv: Re: data-checksums

På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund mailto:and...@anarazel.de>>: Hi, On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote: > Does PG use HW-accellerated crc if CPU supports it[1]? Yes we do, for WAL checksums. The page checksums are a different algorit

Sv: Re: Sv: Re: Sv: Re: data-checksums

På tirsdag 09. januar 2018 kl. 23:42:45, skrev Rob Sargent < robjsarg...@gmail.com <mailto:robjsarg...@gmail.com>>:     On 01/09/2018 03:30 PM, Andreas Joseph Krogh wrote: På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund < and...@anarazel.de <mailto:and...@anara

Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

På onsdag 10. januar 2018 kl. 01:01:26, skrev Andres Freund mailto:and...@anarazel.de>>: On 2018-01-10 00:25:08 +0100, Andreas Joseph Krogh wrote: > På tirsdag 09. januar 2018 kl. 23:42:45, skrev Rob Sargent < > robjsarg...@gmail.com <mailto:robjsarg...@gmail.com>>: &g

Sv: Re: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

guide you, you might want to engage a professional at this kind of thing if the data is very important to you.   Btrfs actually fixes (some) corruption once it detects it, so Thomas' question is not all that far-fetched.   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47

ORDER BY custom type

d) AS responsible_person fromonp_crm_activity act ORDER BY responsible_person.initials ASC ; How do I do that?   Thanks.   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

Sv: ORDER BY custom type

På torsdag 11. januar 2018 kl. 12:00:55, skrev Andreas Joseph Krogh < andr...@visena.com <mailto:andr...@visena.com>>: Hi all.   I have this custom-type:   CREATE TYPE PERSONTYPE AS ( id BIGINT, firstname VARCHAR, lastname VARCHAR, initialsVARCHAR );   I'm returning a column wit

Sv: ADD OR ALTER column

desirable?   It seems to me to be eminently useful and not overly difficult, while potentially saving a significant amount of effort on the part of users.   Geoff   What about using ALTER TABLE tab ADD COLUMN IF NOT EXISTS mycol mytype;   I don't think there's an ALTER COLUMN IF EXISTS

Is ORDER BY in sub-query preserved when outer query is only projection?

tance OPEN, IN_PROGRESS, CLOSED), then order by company-name so I get results for each status sorted by company-name.   Is this kind of sort stable, can I assume the pre-sorted result's order is preserved so I achieve what I want?   Thanks.   -- Andreas Joseph Krogh CTO / Partner - Visena AS M

Sv: Re: Is ORDER BY in sub-query preserved when outer query is only projection?

På søndag 14. januar 2018 kl. 13:30:29, skrev Francisco Olarte < fola...@peoplecall.com <mailto:fola...@peoplecall.com>>: Andreas: On Sun, Jan 14, 2018 at 1:03 PM, Andreas Joseph Krogh wrote: > SELECT q.* FROM ( >   SELECT comp.id, comp.name >   FROM company comp JOIN

Sv: Re: Re: Is ORDER BY in sub-query preserved when outer query is only projection?

På mandag 15. januar 2018 kl. 19:04:51, skrev David G. Johnston < david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>>: On Sun, Jan 14, 2018 at 5:40 AM, Andreas Joseph Krogh mailto:andr...@visena.com>> wrote: På søndag 14. januar 2018 kl. 13:30:29, skrev Franc

List all columns referencing an FK

pieces missing in the standard holding this back?   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>

<    1   2