jsonb: unwrapping text

Hi, I'm trying to extract text from a jsonb 'string'. Simply casting gives me the string wrapped in quotes: foo=# select '"foo"'::jsonb::text; text --- "foo" (1 row) This, of course, makes kind of sense, since it /is/ the JSON's textual representation. What is the canonical wa

Re: jsonb: unwrapping text

On Wed, Oct 27, 2021 at 04:18:20PM -0700, David G. Johnston wrote: > On Wed, Oct 27, 2021 at 11:58 AM wrote: > > > > > I've found out that one can treat a string singleton as if it > > were an array: > > > > foo=# select '"foo"'::jsonb ->> 0; > >?column? > > -- > >foo > > (1

PQexecParams, placeholders and variable lists of params

Hi, PQexecParams expects a query string with "$1", "$2"... placeholders, which refer to as many params in the param list. This keeps SQL injection at bay. Is there a way to express "variable length" lists? IOW, if I want to do a query like "SELECT * FROM customers WHERE id IN ($1, $2) AND name

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

On Tue, Nov 23, 2021 at 09:58:00AM +, Shaozhong SHI wrote: > Is there any regex for Word space Word space Word and more? It isn't very clear what you want to achieve. From the other mails in this thread I understand that your words start with an uppercase char and continue with lowercase chars

Re: PQexecParams, placeholders and variable lists of params

On Tue, Nov 23, 2021 at 06:39:27PM +0500, Дмитрий Иванов wrote: > Hi > A function cannot have an undefined signature, but can accept an array of > arguments: I see. So you propose passing an array as a single param to PQexecParams, in PostgreSQL's syntax for arrays, e.g.. "{42, 45, 50}". Makes se

Re: PQexecParams, placeholders and variable lists of params

On Tue, Nov 23, 2021 at 10:43:03AM -0500, Tom Lane wrote: > "David G. Johnston" writes: > > On Tue, Nov 23, 2021 at 7:21 AM wrote: > >> Makes sense. Problem is, that, again, the application would be > >> responsible of making sure the individual values don't contain nasty > >> stuff (for example,

Re: PQexecParams, placeholders and variable lists of params

On Tue, Nov 23, 2021 at 05:14:44PM +0100, Daniel Frey wrote: > > On 23. Nov 2021, at 16:43, Tom Lane wrote: > > > > PG's array quoting rules are odd enough that I can sympathize with not > > wanting to deal with them. (Although, if you only have to build an > > array and not parse one, taking th

Re: dirty_ratio & dirty_background_ratio settings with huge memory

m.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds fine. > It should be the other way around: dirty_background_bytes < dirty_bytes regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

ircuit_layout cl ON cl.segment_id = sg.id <http://sg.id> AND cl.ordinal > = 1 > JOIN circuit c ON c.id <http://c.id> = cl.circuit_id > JOIN vendor v ON v.id <http://v.id> = c.pop_support_vendor_id > ) seg ON seg.circuit_id = c.id <http://c.id> > JOIN vendor_gtt_pop on vendor_gtt_pop.g

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

On 03/21/2018 08:44 PM, Alessandro Aste wrote: > Thanks for your reply Tomas.  The query just got stuck for forever.  I > observed no CPU spikes, it is currently running and I see 89 of the CPU > idle. I'm using a relative powerfull machine (120 GB ram, 56 CPU).   > That does

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

On 03/22/2018 11:33 AM, Alessandro Aste wrote: > Tomas, thank you. This machine  is abare metal server running only a > staging postgresql 10.3  instance. Nobody is using it beside me.  > > I'm attaching 4 files.   > > every_30_seconds_top_stats_during_query.txt   

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

On 03/22/2018 11:29 PM, Alessandro Aste wrote: > Thanks Tomas. We're currently building postgres from source. In order to > enable symbols, you want me to re-configure postres with  --enable-debug > then run perf? > Yes. regards -- Tomas Vondra http://ww

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

---+-+ > > 684807 | 12346 | Y   | GTT/POP/LON1T | Active     | LON1T    > |   12288 | Telehouse UK | 14 Coriander Avenue | London   | E14 > > 2AA |   | GB  |    219 > > (1 row) >

Re: Seems like there is an issue with reltuples showing twice the number of rows

tuples to rather different estimates. That is fixed now and should be in the next minor release. Hard to say if this is a case of that, but judging by the number of dead tuples chances are it is. It's probably better to use n_live_tup instead, though. I'd say that's closer to th

Re: ERROR: found multixact from before relminmxid

t the data pages it's nearly impossible to determine what went wrong. We'd also need more information about what happened to the hardware and cluster before the issues started to appear - crashes, hardware issues. And so on. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: ERROR: found multixact from before relminmxid

On 04/06/2018 04:29 PM, Alexandre Arruda wrote: > 2018-04-06 9:39 GMT-03:00 Tomas Vondra <mailto:tomas.von...@2ndquadrant.com>>: > > > > On 04/06/2018 02:09 AM, Alexandre Arruda wrote: > > Hi, > > > > Some time ago, I had this e

Re: ERROR: found multixact from before relminmxid

On 04/09/2018 01:49 PM, Alexandre Arruda wrote: > > > 2018-04-06 13:11 GMT-03:00 Tomas Vondra <mailto:tomas.von...@2ndquadrant.com>>: > > > > On 04/06/2018 04:29 PM, Alexandre Arruda wrote: > > 2018-04-06 9:39 GMT-03:00 Tomas Vondra

Re: Shared buffers increased but cache hit ratio is still 85%

be granted to others.)|| > It might be better to note current values of the counters somewhere, and compute a delta later (and use that to compute the cache hit ratio). The issue is that pg_stat_reset() throws away all sorts of interesting and important stats, including those driving autovacuum/a

Re: is there any adverse effect on DB if I set autovacuum scale factor to zero?

be due to all the autovacuum workers being constantly busy, unable to cleanup all tables in a timely manner. In that case lowering the threshold is not going to help, on the contrary. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: is there any adverse effect on DB if I set autovacuum scale factor to zero?

On 08/13/2018 03:41 PM, Raghavendra Rao J S V wrote: Hi Tomas, Thank you very much for your response. As we  know table becomes a candidate for autovacuum  process based on below formula. *Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples

Re: Fwd: is there any adverse effect on DB if I set autovacuum scale factor to zero?

e done per second. Increasing the number of workers is akin to allowing more cars on a highway, but also lowering the speed limit. You need to increase the limit on amount of work, and lowering naptime is one way to do that. regards -- Tomas Vondra http://www.2ndQuadra

Re: Code of Conduct

of the people involved in both groups I'm not worried about this part. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Help to understand Actual Rows vs Plan Rows from the query planner output

t;Bitmap Index Scan” on “users_lower_idx”, I > see the plan rows are 50, but actual rows it got 1. In the second node > type of "Bitmap Index Scan” on “ lower_state_users_idx”, I see the plan > rows are 211, and actual rows 211. Both are same. Based on what > conditions pl

Re: Why the index is not used ?

does not really solve the issues with having to pass the password to the query, making it visible in pg_stat_activity, various logs etc. Which is why people generally use FDE for the whole disk, which is transparent and provides the same level of protection. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Why the index is not used ?

Hi, On 10/08/2018 04:10 PM, ROS Didier wrote: > Hi Tomas >     >     Thank you for your answer and recommendation which is very > interesting. I'm going to study the PCI DSS document right now. > > * Here are my answer to your question : > > />>/ >

Re: COPY command returns "ERROR: invalid XML content"

? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Install postgres on rhel 7

. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: PG 12 not yet for mac

x27;s not a project/page managed by the PostgreSQL community, you need to report the issues to the authors (most likely through github issues). regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pgcrypto question

eems like an interesting idea - I wonder if it could be done e.g. in psycopg as an extension, or something like that. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pgcrypto question

7;s no easy way to distinguish them. I'm no psycopg2 expert, but it does have some infrastructure for casting PostgreSQL types to Python types, and I guess that could be used for the encryption. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development,

Re: temporary files

size. That seems as if a single query was executed repeatedly. Maybe try looking into what query that is. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Table locking during backup

ystem view. And it does conflict with the ACCESS EXCLUSIVE mode, used by the second query. Could you suggest me which part of pg_dump (which query) might cause that behaviour. It's this line: https://github.com/postgres/postgres/blob/master/src/bin/pg_dump/pg_dump.c#L6676 regards -- To

Re: timescaleDB & WAL replication

timescaleDB extention on the master side? AFAIK timescale is "just" a regular extension, in which case yes, all you need to do is installing it on the master side. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA

Re: jsonb_set() strictness considered harmful to data

all. I do mostly agree with this. The json[b]_set behavior seems rather surprising, and I think I've seen a couple of cases running into exactly this issue. I've solved that with a simple CASE, but maybe changing the behavior would be better. That's unlikely to be back-patchable, though, so maybe a better option is to create a non-strict wrappers. But that does not work when the user is unaware of the behavior :-( regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Replication of Replication

topped, etc. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: releasing space

rsions (~30 months) of fixes behind. You might want to consider upgrading ... -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Has there been any discussion of custom dictionaries being defined in the database?

#x27;t think that's entirely necessary, thoug - we could use the table directly. Yes, that would be slower, but maybe it'd be sufficient. But I think the idea is ultimately that we'd implement a new dict type in core, and people would just specify which table to load data

Re: jsonb_set() strictness considered harmful to data

ot;...", NULL) should do the same thing as jsonb_set(..., "...", 'null':jsonb) I'm not entirely surprised it's what MySQL does ;-) but I'd say treating it as a deletion of the key (just like MSSQL) is somewhat more sensible. But I admit it's quit

Re: jsonb_set() strictness considered harmful to data

ferently. And at the same time one can argue, that people, who are not aware about this caveat with jsonb_set and NULL values, will most likely use it due to a bit simpler syntax (more similar to some popular programming languages). This seems like an entirely independent thing ... Right. Useful,

Re: jsonb_set() strictness considered harmful to data

On Sat, Oct 19, 2019 at 12:47:39PM -0400, Andrew Dunstan wrote: On 10/19/19 12:32 PM, David G. Johnston wrote: On Sat, Oct 19, 2019 at 9:19 AM Tomas Vondra mailto:tomas.von...@2ndquadrant.com>> wrote: > >We invented jsonb_set() (credit to Dmitry Dolgov). And we've

Re: releasing space

or version upgrade. FWIW Julie, please don't top post - it just makes it harder to follow the discussion. Also, this seems like a completely separate question, unrelated to the DROP DATABLASE one. It might be better to start a new thread instead of repurposing an existing one. regards

Re: jsonb_set() strictness considered harmful to data

onb_set and NULL in the archives), but I'm not sure that's enough to justify any changes in backbranches. I'd say no, but I have no idea how many people are affected by this but don't know about it ... regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: CPU SPIKE

execution is using CPU, so yes - it can cause a spike in CPU usage. The question is whether the query plan is efficient, or whether it's using the CPU unenecessarily. You need to show us EXPLAIN ANALYZE and perhaps explain why you think it shouldn't use this much CPU. regards -- To

Re: jsonb_set() strictness considered harmful to data

On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: On 10/20/19 4:18 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 03:48:05PM -0400, Andrew Dunstan wrote: On 10/20/19 1:14 PM, David G. Johnston wrote: On Sun, Oct 20, 2019 at 5:31 AM Andrew Dunstan mailto:andrew.duns

Re: jsonb_set() strictness considered harmful to data

On Mon, Oct 21, 2019 at 08:06:46AM -0700, Adrian Klaver wrote: On 10/20/19 11:07 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: True. And AFAIK catching exceptions is not really possible in some code, e.g. in stored procedures (because we can'

Re: Barman

io 1" in the barman docs, i.e. pg_basebackup (backup_method = postgres) and WAL streaming. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: How to import Apache parquet files?

ed and probably significantly less efficient than COPY. So if you have a lot of these parquet files, I'd probably use the COPY. But maybe the ability to query the parquet files directly (with FDW) is useful for you. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Devel

Re: BigSQL pgc alternative

ly you mean when you say "portable option". Can you explain? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: jsonb_set() strictness considered harmful to data

warnings check-world passed without any problems I'll mark this patch as ready for commiter Thank you for your work Thanks for the review. I propose to commit this shortly. Now that this was committed, I've updated the patch status accordingly. Thanks! -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Avoiding out of date statistics / planner

rming the root cause - you'll see the query plan, which should give you enough context. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: 1 Status of vertical clustered index - 2 Join using (fk_constraint) suggestion - 3 Status of pgsql's parser autonomization

cases you're thinking about? FWIW I think it's pretty bad idea to post questions about three very different topics into a single pgsql-hackers thread. That'll just lead to a lot of confusion. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: DB running out of memory issues after upgrade

ed) and do (gdb) p MemoryContextStats(TopMemoryContext) (gdb) q and show us the output printed into server log. If it's a backend running a query, it'd help knowing the execution plan. It would also help knowing the non-default configuration, i.e. stuff tweaked in postgresql.conf. rega

Re: Check constraints do not seem to be working!!!

(b it not null and b = true) and (c is not null) Or something like that. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Extended statistics for correlated columns, row estimates when values are not in MCVs list

es. That should consider the column-level correlation even when the combination of values is not in the MCV. It might make the "good" estimate worse, but that's an inherent trade-off. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Potential BRIN Index Corruption

s generated per day, or something like that. Maybe there was some I/O issue in that time period and a couple writes got lost, or something like that. Are there any other corrupted indexes on the table? regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Is there a way to dump schema to files on disk and keep them in sync

chema versioning tools available - we have a list on the wiki: https://wiki.postgresql.org/wiki/Change_management_tools_and_techniques I'm sure it's incomplete, but it's helpful nevertheless. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Reg: User creation script/List of privileges

On 30.03.22 05:09, Sai Ch wrote: Hi Experts, I am looking for a query or possibility to generate all the privileges a user/role has. I need this to create a user/role from one database to another with exact same privileges. Kindly, share the query or way to proceed further. Thanks & Regar

ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

Hi all, while doing `cat pg_dump.dump | psql` I get the above message. Note that `pg_dump.dump` contains: CREATE DATABASE some_db WITH TEMPLATE = my_own_template ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'; What is exactly the problem? I understand that en_US.U

Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

On 22.06.22 21:25, Adrian Klaver wrote: On 6/22/22 12:17, Tomas Pospisek wrote: Hi all, while doing `cat pg_dump.dump | psql` I get the above message. Note that `pg_dump.dump` contains: CREATE DATABASE some_db WITH TEMPLATE = my_own_template ENCODING = 'UTF8' LC_COLLATE = &#

Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

On 22.06.22 22:18, Tomas Pospisek wrote: On 22.06.22 21:25, Adrian Klaver wrote: On 6/22/22 12:17, Tomas Pospisek wrote: Hi all, while doing `cat pg_dump.dump | psql` I get the above message. Note that `pg_dump.dump` contains: CREATE DATABASE some_db WITH TEMPLATE = my_own_template

Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

On 23.06.22 20:21, Adrian Klaver wrote: On 6/23/22 10:11, Adrian Klaver wrote: On 6/23/22 00:37, Tomas Pospisek wrote: On 22.06.22 22:18, Tomas Pospisek wrote: On 22.06.22 21:25, Adrian Klaver wrote: On 6/22/22 12:17, Tomas Pospisek wrote: So I used both pg_dump and pg_restore from the

Re: PostgreSQL 14.4 ERROR: out of memory issues

gt; I think it's be interesting to get memory context stats from the processes consuming a lot of memory. If you know which processes are suspect (and it seems you know, bacause if a reconnect helps it's the backend handling the connection), you can attach a debugger and do $ gdb -p $PID call MemoryContextStats(TopMemoryContext) which will log info about memory contexts, just like autovacuum. Hopefully that tells us memory context is bloated, and that might point us to particular part of the code. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Expr. extended stats are skipped with equality operator

it could be build-dependent > and a bit hard to track down if one doesn't control the build flags > Yeah, this seems like a clear bug - we should not fail queries like this. It's a sign statext_is_compatible_clause() and the MCV code disagrees which clauses are compatible. Can you share an example triggering this? regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Expr. extended stats are skipped with equality operator

On 8/7/22 19:28, Tom Lane wrote: > Tomas Vondra writes: >> Yeah, this seems like a clear bug - we should not fail queries like >> this. It's a sign statext_is_compatible_clause() and the MCV code >> disagrees which clauses are compatible. > > Indeed. I at

can't get psql authentication against Active Directory working

Hello all, so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via Active Directory. psql (Linux) -> postgres (Linux) with authentication against Active Directory does work. However the same with psql.exe on Windows does not. I get: D:\>C:\OSGeo4W\bin\psql.exe servic

Re: can't get psql authentication against Active Directory working

On 18.02.23 17:16, Erik Wienhold wrote: On 18/02/2023 15:02 CET Tomas Pospisek wrote: so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via Active Directory. psql (Linux) -> postgres (Linux) with authentication against Active Directory does work. However th

Re: can't get psql authentication against Active Directory working

Hi Stephen, first: thanks a lot for replying! On 20.02.23 15:17, Stephen Frost wrote: * Tomas Pospisek (t...@sourcepole.ch) wrote: so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via Active Directory. Looks like you're trying to do a bit more than t

Re: can't get psql authentication against Active Directory working

Hi Stephen, again thanks a lot for trying to help me! On 21.02.23 16:29, Stephen Frost wrote: * Tomas Pospisek (t...@sourcepole.ch) wrote: On 20.02.23 15:17, Stephen Frost wrote: * Tomas Pospisek (t...@sourcepole.ch) wrote: so I'm trying to authenticate psql (on Windows) -> post

Re: can't get psql authentication against Active Directory working

Hi Stephen, On 25.02.23 00:52, Stephen Frost wrote: * Tomas Pospisek (t...@sourcepole.ch) wrote: >> On 21.02.23 16:29, Stephen Frost wrote: * Tomas Pospisek (t...@sourcepole.ch) wrote: On 20.02.23 15:17, Stephen Frost wrote: * Tomas Pospisek (t...@sourcepole.ch) wrote: >>

Re: Getting error while upgrading postgres from version 12 to 13

On 18.03.23 22:24, shashidhar Reddy wrote: and adding to this the following is also showing in the error, we tried changing the file permissions but still no luck FATAL:  could not load private key file "/etc/ssl/private/ssl-cert-snakeoil.key": key values mismatch This sounds like the private

Re: Getting error while upgrading postgres from version 12 to 13

On Sun, Mar 19, 2023 at 2:09 AM shashidhar Reddy mailto:shashidharreddy...@gmail.com>> wrote: Hello, I am in a process of upgrading postgresql from version 12 to 13 using pg_upgrdrade but I am getting error stating  FATAL:  database files are incompatible with server  DET

Re: Properly handle OOM death?

On 13.03.23 21:25, Joe Conway wrote: Hmm, well big +1 for having swap turned on, but I recommend setting "vm.overcommit_memory=2" even so. I've snipped out the context here, since my advice is very unspecific: do use swap only as a safety net. Once your system starts swapping performance goe

Re: backend crash on DELETE, reproducible locally

ticular thing makes it less extensible? That being said, we also have RELOPT_KIND_BRIN, and that seems to be missing from RELOPT_KIND_INDEX too (and AFAICS the optimization works for all index types). regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: integrate Postgres Users Authentication with our own LDAP Server

right thing is to send it to pgsql-general. Likewise, it makes little sense to send questions to the "owner". I've removed the other lists from CC. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Regarding SSL Enablement in PostgreSQL Database on different port

On 02.05.23 12:13, Magnus Hagander wrote: On Tue, May 2, 2023 at 11:43 AM sujay kadam wrote: Hi PostgreSQL Team, I want to enable SSL in PostgreSQL Database on a new port. I don’t want the default port that is 5432 SSL enabled, but I want to configure another port to enable SSL on it. As

Re: Question regarding specifics of GIN and pg_trgm performance and potential use of show_trgm to improve it

         Index Cond: (value % 'lorem'::text) >          Buffers: shared hit=109 >  Planning: >    Buffers: shared hit=3 >  Planning Time: 2.394 ms >  Execution Time: 2273.256 ms > (13 rows) > > > Thank you for any sort of insight into this.

Re: pb with join plan

e you into what to do instead. > >   > I know that page. obviously, as I have to kill the request, I cannot > provide a explain analyze...  > It's a bit weird the "victor" table is joined seemingly without any join conditions, leading to a cross join (which massively inflates the cost for joins above it). Maybe the anonymized plan mangles it somehow. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: move databases from a MySQL server to Postgresql.

questions about practical issues you run ran into. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: [Beginner Question]What is query jumbling?

jumble. So "jumbling" is a process to generate a "normalized" query. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Query take a long time and use no index

the DATE_TRUNC. > How can I optimize? > It's always going to be slow with the COUNT(DISTINCT), I'm afraid. Not sure how much you can modify the query / database, and how accurate results you need. If you're OK with estimates, you can try postgres-hll extension [2] which estimates count(distinct). For exact reaults, I wrote count_distinct extension [2] that uses hashtable. Might be worth a try, I guess. Another option is to precalculate stuff, and do queries on that. That is, you're not going to add data with old timestamps, so you can pre-aggregate that. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Query take a long time and use no index

On 7/17/23 13:20, Tomas Vondra wrote: > ... > > It's always going to be slow with the COUNT(DISTINCT), I'm afraid. > > Not sure how much you can modify the query / database, and how accurate > results you need. If you're OK with estimates, you can try postgres-hl

Re: How to grant read only functions execute permission to read only user

ege), then the function can't do any writes either. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: error: connection to server on socket...

On 01.08.23 12:22, Amn Ojee Uw wrote: Using the following command on my Debian 12 machine: sudo apt-get install postgresql postgresql-15 postgresql-client-common postgresql-common postgresql-contrib postgresql-doc phppgadmin and following the instruction on this

Re: PORT 5432 ERROR

On 01.08.23 01:18, David Beck wrote: I am receiving an error when I enter the server port number to listen to—installed on all available versions, both 32-bit and 64-bit when applicable, even the latest version, 15.3 x64 for Windows. I downloaded the latest version as an example, and as I was t

Re: Intermittent Issue with WAL Segment Removal in Logical Replication

n for the replication slot peerflow_slot_wal_testing_2 (especially the restart_lsn value). Also, maybe show the contents of pg_wal (especially for the segment referenced in the error message). Can you reproduce this outside Google cloud environment? regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Intermittent Issue with WAL Segment Removal in Logical Replication

debug > this further? > Hard to say. The best thing would be to have a reproducer script, ofc. If that's not possible, the information already requested seems like a good start. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: How do you optimize the disk IO when you cannot assume a file will start at a boundary then?

ter. The postgres 8kB page? The filesytem page? The storage block/sector size? FWIW I think for SSDs this matters way more than for HDD, because SSDs have to erase the space before a rewrite, which makes it much more expensive. But that's not just about the alignment, but about the page size

Re: Performance degradation after upgrading from 9.5 to 14

rade? 3) Did you transfer the configuration, or did you just create a new cluster with the default values? 4) What exactly is slower? Queries? Inserts? 5) Can you quantify the impact? Is it 2x slower? 100x slower? regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Unexpected data when subscribing to logical replication slot

t to "off". > > We need help to understand this unexpected behaviour. > Would be good to have some sort of reproducer - ideally a script that sets up an instance + replication, and demonstrates the issue. Or at least a sufficiently detailed steps to reproduce it without having to guess what exactly you did. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

apt-archive.postgresql.org a bit broken?

Hello, I get this when I try to access the archive of focal packages: ``` # apt-get update [...] Err:6 https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg InRelease 403 Forbidden [IP: 2600:9000:2761:4600:b:5965:3380:93a1 443] [...] E: Failed to fetch https://apt-archive.postgresql.

Re: apt-archive.postgresql.org a bit broken?

On 05.07.24 18:27, Magnus Hagander wrote: On Fri, Jul 5, 2024 at 6:07 PM Tomas Pospisek <mailto:t...@sourcepole.ch>> wrote: Hello, I get this when I try to access the archive of focal packages: ``` # apt-get update [...] Err:6 https://apt-archive.postgresq

Re: Alignment check

On 27.06.24 18:07, Marthin Laubscher wrote: I don’t intend dissing or plugging anyone’s efforts or start a flame war, but I’d like to get a sense of how the PostgreSQL community feels about: a) YugabyteDB, and b) PostgreSQL on Kubernetes. For my application I’m deeply vested in Kubernetes as

Re: page is not marked all-visible but visibility map bit is set in relation "pg_statistic"

igate issues on a proprietary product. regards [1] https://www.postgresql.org/message-id/CAH2-WznuNGSzF8v6OsgjaC5aYsb3cZ6HW6MLm30X0d65cmSH6A%40mail.gmail.com -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: PG Dump on 11 - Restore on 16 - is possible?

ion 9.2 are supported.) I'd probably use pg_dump from the target version (PG16), but it should not matter. regards -- Tomas Vondra

Re: About replication minimal disk space usage

g ends up spilling the decoded transaction to disk. If you want a better answer, I think you'll have to provide a lot more details. For example, which PostgreSQL version are you using, and how is it configured? What config parameters have non-default values? regards -- Tomas Vondra

Re: ERROR: could not open relation with OID XXXX

es it easier to hit, but it can happen without it. > Is there something I can do to avoid this? Is my understanding of how > the catalog tables work wrong? > I believe you could actually lock the pg_class rows for update. Just add FOR UPDATE at the end of the query. regards -- Tomas Vondra

Re: ERROR: could not open relation with OID XXXX

ve no idea what you mean - what would be filtered out, etc. But you can give it a try - you have a reproducer, so testing if it fails should not be that difficult. Other than that, I think you can do two things: 1) Make the application to simply retry in it sees this error. 2) Lock the objects using LOCK TABLE before running the query. regards -- Tomas Vondra

Re: BRIN index on timestamptz

dom data, because the whole idea is about eliminating large blocks of data (e.g. 1MB). But with random data that's not going to happen, because each such range will match anything. Which is why seqscan is a bit faster than when using BRIN index. regards -- Tomas Vondra EnterpriseDB: http:

very long secondary->primary switch time

Hello all, I maintain a postgresql cluster that does failover via patroni. The problem is that after a failover happens it takes the secondary too long (that is about 35min) to come up and answer queries. The log of the secondary looks like this: 04:00:29.777 [9679] LOG: received promote r

Re: very long secondary->primary switch time

Hi Tom, hi list participants, thanks a lot for replying Tom. On 27.04.21 22:16, Tom Lane wrote: Tomas Pospisek writes: I maintain a postgresql cluster that does failover via patroni. The problem is that after a failover happens it takes the secondary too long (that is about 35min) to come up

  1   2   >