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: 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: 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: 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: 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: 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: 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

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: 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

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: 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: 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: 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: 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: 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: 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: 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: 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: [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: 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: 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: 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: 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: 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: 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: 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: 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: 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, 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

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

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: 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

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: 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: 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: 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 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 = &#

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: 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

Re: Detecting schema changes via WAL logs

On 19.02.22 17:42, Cal Mitchell wrote: Hello everyone, I’m building an open-source data integration tool (SQLpipe) and am now trying to incorporate CDC into the product. Is it possible to detect when schema changes happen via the WAL? The WAL doesn’t have to tell me /what/ was changed, it ju

Re: Strange results when casting string to double

On 18.02.22 22:42, Peter J. Holzer wrote: If there has been a glibc update (or a postgresql update) in those 480 days (Ubuntu 14.04 is out of its normal maintenance period but ESM is still available) the running processes may well run different code than a newly started program. So it could be a

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

0.48 rows=1000 width=183) ... So it seems *more* expensive than the first plan. Taken to the extreme the planner could theoretically have chosen to use the first plan (and delay the condition until after the join). regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

On 2/4/22 05:21, A Shaposhnikov wrote: Tomas, thank you! The query: select 1 from data as d, data_class as dc where dc.data_id = d.id and d.id > 205284974 and dc.data_id > 205284974 -- new condition order by d.id limit 1000; totally solved it - it is now fast und

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

data_pkey on data t (cost=0.57..21427806.53 rows=58785023 width=131) (actual time=0.024..0.482 rows=854 loops=1) Index Cond: (id > 205284974) It'd be interesting to see the plans without the LIMIT, as that makes the "actual" values low simply by terminating early.

Fwd: PgAdmin is struggling and can we configure it so that it works better

Forgot to include pgsql-general@lists.postgresql.org in the Cc... Forwarded Message Subject: Re: PgAdmin is struggling and can we configure it so that it works better Date: Fri, 21 Jan 2022 13:42:13 +0100 From: Tomas Pospisek To: Shaozhong SHI On 21.01.22 12:42, Shaozhong

Re: Create and access a dictionary type

r application to use what's available. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Create and access a dictionary type

On 1/5/22 17:24, Ron wrote: On 1/5/22 7:22 AM, Tomas Vondra wrote: On 1/5/22 14:17, Shaozhong SHI wrote: Any examples in Postgres to create a dictionary type to store and access key value pairs? I'd say JSONB can be used as an associative array, and e.g. in Python can map to dict

Re: Create and access a dictionary type

On 1/5/22 14:17, Shaozhong SHI wrote: Any examples in Postgres to create a dictionary type to store and access key value pairs? I'd say JSONB can be used as an associative array, and e.g. in Python can map to dict data type. regards -- Tomas Vondra EnterpriseDB:

Re: Postgis - geography function

//lists.osgeo.org/mailman/listinfo/postgis-users regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

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: 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 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: 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

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: Plans with bad estimates/paths

ny joins as possible until the issue disappears. The simpler the query, the easier it is to investigate. And yes, replacing parts of a query with a temporary table is a common solution, because it's possible to collect statistics on it, build indexes etc. That usually solves estimation i

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

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: GIN index

probably ask them. 4) Don't post backtraces as screenshots. Copy them as text, attach them as a text file. 5) Continue in the thread you already started, otherwise it's impossible to follow the discussion. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Avg/max size of these JSON docs in Postgres

s an explicit 1GB limit per value, but having rows close to the 1GB limit is going to cause all sorts of unpredictable and rather painful issues :-( regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: create a new GIN index for my own type

directly to the other person. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Problem in pg_basebackup of 1.7 TB database while replicating in PostgreSQL 9.4.( Master and slave both are in postgreSQL 9.4)

you looking for a faster / more efficient way to transfer the data? regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: create a new GIN index for my own type

ID (gdb) c 3) run the CREATE INDEX query 4) get backtrace from the debugger (gdb) bt regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: postgresql11: How to use publication/subscription on primary/standby setup

On 29.09.21 11:01, Abhishek B wrote: Is there a solution to use publication/subscription on a master-slave or primary-standby postgres setup without manual intervention? Currently the DB clusters are managed by Pacemaker. Once there is a failover, the pacemaker is able to promote the secondary

Re: ERROR: unrecognized node type

ause, but we can only speculate why and the query conditions look entirely reasonable. I suggest you do this: 1) start a session, identify the PID of the backend select pg_backend_pid(); 2) attach a debugger (e.g. gdb) to the pid gdb -p $PID 3) set breakpoint to the l

Re: ERROR: unrecognized node type

ion (file:line) where the error is printed, and then set a breakpoint to that place. Makes it easier to generate the backtrace. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Fastest option to transfer db?

I'm potentiall facing the same problem and would be interested in the solution. Is there any particular howto you followed? Also at some point I'd like to cut of the link between the two DBs promote the copy to be the master and delete the original DB. Have you figured out the correct step for

Re: How to hash a large amount of data within Postgres?

rfaces to read chunks of data from large objects - see this: 1) lo_get (loid, offset, length) https://www.postgresql.org/docs/13/lo-funcs.html 2) lo_seek() + lo_read() https://www.postgresql.org/docs/13/lo-interfaces.html Obviously, you can't do "loread(fd, size)" because that's going to attempt building one large bytea, failing because of the alloc limit. You have to stream the data into the hash. Doing that in plpgsql is possible, although possibly somewhat slow. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: WAL accumulating, Logical Replication pg 13

Hi Willy-Bas Loos, On 31.05.21 17:32, Willy-Bas Loos wrote: On Mon, May 31, 2021 at 4:24 PM Vijaykumar Jain > wrote: So I got it all wrong it seems :) Thank you for taking the time to help me! You upgraded to pg13 fine? , but while on pg13 y

Re: Strange behavior of function date_trunc

xecuted for each row while now() is executed only once. The functions are executed for each row in both cases, but now() is simply much cheaper - it just returns a value that is already calculated, while date_trunc has to parse and truncate the value, etc. You can use CTE to execute it just once, I think: with x as (select date_trunc('day', '2021-04-01'::timestamptz) as x) select * from t where a > (select x from x); regards Tomas

Re: Extended stats - value not in MCV list

of the columns, in which case we don't know how many groups we actually matched, etc. TBH I'm not sure how much of those implementation details we want to put into the user docs - it may be a bit too much, and we may need to change some of it. regards -- Tomas Vondra EnterpriseDB

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

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: 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:

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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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

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: 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

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: 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

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: 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: 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: Replication of Replication

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

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

connection timeout with psycopg2

Hi there, I have been testing out the following architecture for PostgreSQL HA. +-+ +-+ VIP ++ | +-+| || +--v---+ +--v---+ | pgBouncer | | pgBouncer | | + | |

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: 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: 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

  1   2   >